mysql基础篇-语法/数据类型/运算符/常用函数

sql分类

SQL语言共分为三大类：数据操纵语言DML（数据查询语言DQL），数据定义语言DDL，数据控制语言DCL。
  1. 数据查询语言DQL
    数据查询语言DQL基本结构是由SELECT子句，FROM子句，WHERE
    子句组成的查询块：
    SELECT <字段名表>
    FROM <表或视图名>
    WHERE <查询条件>
    数据操纵语言DML
    数据操纵语言DML主要有三种形式：插入：INSERT，更新：UPDATE，删除：DELETE
  2. 数据定义语言DDL
    数据定义语言DDL用来创建数据库中的各种对象-----表、视图、
    索引、同义词、聚簇等如：
    CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
    DDL操作是隐性提交的！不能rollback
  3. 数据控制语言DCL
    数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制
    数据库操纵事务发生的时间及效果，对数据库实行监视等。如：
    GRANT：授权，revoke：回收。
    ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一点。
    回滚---ROLLBACK
    回滚命令使数据库状态回到上次最后提交的状态。其格式为：
    SQL>ROLLBACK;
    COMMIT [WORK]：提交。
    在数据库的插入、删除和修改操作时，只有当事务在提交到数据
    库时才算完成。在事务提交前，只有操作数据库的这个人才能有权看
    到所做的事情，别人只有在最后提交完成后才可以看到。
    提交数据有三种类型：显式提交、隐式提交及自动提交。下面分
    别说明这三种类型：
    1）显式提交
    用COMMIT命令直接完成的提交为显式提交。其格式为：
    SQL>COMMIT；
    2）隐式提交
    用SQL命令间接完成的提交为隐式提交。这些命令是：
    ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
    EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。
    3）自动提交
    若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，
    系统将自动进行提交，这就是自动提交。其格式为：
    SQL>SET AUTOCOMMIT ON；
dcl语句主要是运维同时用的比较多，私下了解就行
ddl语句汇总

1.创建数据库 CREATE DATABASE dbname
2.选择数据库 USE dbname
3.数据库中创建的所有数据表 show tables
4.删除数据库 drop database dbname（慎用）
5.创建表

CREATE TABLE tablename (column_name_1 column_type_1 constraints,column_name_2 column_type_2 constraints)

6.查看表定义 DESC tablename / show create table tal_name
7.删除表 DROP TABLE tablename（慎用）
8.修改表

（1）修改表字段类型，语法如下：ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTERcol_name]
（2）增加表字段，语法如下：ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name]
（3）删除表字段，语法如下：ALTER TABLE tablename DROP [COLUMN] col_name
（4）字段改名，语法如下：ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition[FIRST|AFTER col_name]
（5）更改表名，语法如下：ALTER TABLE tablename RENAME [TO] new_tablename

tips > change 与 modify的区别是什么？

dml语句汇总
1．插入记录INSERT INTO 
tablename (field1,field2,…,fieldn) VALUES(value1,value2,…,valuen);
可以一次性插入多条记录(节省网络开销提高效率)，语法如下：
INSERT INTO tablename (field1, field2, …, fieldn)VALUES(record1_value1, record1_value2, …, record1_valuesn),(record2_value1, record2_value2, …, record2_valuesn)
2．更新记录
UPDATE tablename SET field1=value1,field2.=value2,…,fieldn=valuen [WHERECONDITION]
update命令可以同时更新多个表中数据，语法如下：
UPDATE t1,t2,…,tn set t1.field1=expr1,tn.fieldn=exprn [WHERE CONDITION]
3．删除记录（慎用，注意where条件）
DELETE FROM tablename [WHERE CONDITION]
在MySQL中可以一次删除多个表的数据，语法如下：
DELETE t1,t2,…,tn FROM t1,t2,…,tn [WHERE CONDITION]
4．查询记录（条件查询）
SELECT * FROM tablename [WHERE CONDITION]
不重复查询 在重复字段前加 distinct 关键字
排序及限制查询 ORDER BY后面可以跟多个不同的排序字段，并且每个排序字段可以有不同的排序顺序，按照排序字段依次排序展示。limit限制展示数量。
5.聚合
SELECT [field1,field2,…,fieldn] fun_nameFROM tablename[WHERE where_contition][GROUP BY field1,field2,…,fieldn[WITH ROLLUP]][HAVING where_contition]
6.子查询
某些情况下，当进行查询的时候，需要的条件是另外一个select语句的结果，这个时候，就要用到子查询。用于子查询的关键字主要包括 in、not in、=、!=、exists、not exists等。（子查询和表连接的转换）
7.记录联合
SELECT * FROM t1UNION|UNION ALLSELECT * FROM t2

tips > where 与 having的区别？count（*）与 count（ID）的区别？union 与 union all的区别？

dml表连接

当需要同时显示多个表中的字段时，就可以用表连接来实现这样的功能。从大类上分，表连接分为内连接和外连接，它们之间的最主要区别是，内连接仅选出两张表中互相匹配的记录，而外连接会选出其他不匹配的记录。我们最常用的是内连接。


image.png
参考连接：
https://blog.csdn.net/github_36849773/article/details/76940987?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.nonecase

数据类型

每一个常量、变量和参数都有数据类型，它用来指定一定的存储格式、约束和有效范围。MySQL提供了多种数据类型，主要包括数值型、字符串类型、日期和时间类型。

数值类型

1.整数类型，如果超出类型范围的操作，会发生“Out of range”错误提示。MySQL还支持在类型名称后面的小括号内指定显示宽度，例如int(5)表示当数值宽度小于5位的时候在数字前面填满宽度，如果不显示指定宽度则默认为int(11)。一般配合zerofill使用，顾名思义，zerofill就是用“0”填充的意思，也就是在数字位数不够的空间用字符“0”填满。整数类型还有一个属性：AUTO_INCREMENT。在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型。AUTO_INCREMENT值一般从1开始，每行增加1。在插入NULL到一个AUTO_INCREMENT列时，MySQL插入一个比该列中当前最大值大1 的值。


image.png

tips>int（2）输入10000，能正常存入吗?

2.浮点数和定点数。浮点数包括 float（单精度）和double（双精度），而定点数则只有decimal一种表示。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。浮点数和定点数都可以用类型名称后加“(M,D)”的方式来进行表示，float和double在不指定精度时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示，而decimal在不指定精度时，默认的整数位为10，默认的小数位为0。


image.png

3.BIT（位）类型，用于存放位字段值，BIT(M)可以用来存放多位二进制数，M范围从1～64，如果不写则默认为1位。对于位字段，直接使用SELECT命令将不会看到结果，可以用bin()（显示为二进制格式）或者hex()（显示为十六进制格式）函数进行读取。


image.png

tips>对于插入数据先转换再存储。

日期时间类型
image.png

如果要用来表示年月日，通常用DATE来表示。如果要用来表示年月日时分秒，通常用DATETIME表示。如果只用来表示时分秒，通常用TIME来表示。如果需要经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP来表示。TIMESTAMP值返回后显示为“YYYY-MM-DD HH:MM:SS”格式的字符串，显示宽度固定为19个字符。如果想要获得数字值，应在TIMESTAMP 列添加“+0”。如果只是表示年份，可以用YEAR来表示，它比DATE占用更少的空间。YEAR有2位或4位格式的年。默认是4位格式。在4位格式中，允许的值是1901～2155和0000。如果超出这个范围，在默认的SQLMode下，系统会进行错误提示，并将以零值来进行存储。


image.png

tips>我们用int存储的时间戳有什么问题？

  • 注意，可以发现，系统给TIMESTAMP属性的列自动创建了默认值CURRENT_TIMESTAMP（系统日期），MySQL只给表中的第一个TIMESTAMP字段设置默认值为系统日期，如果有第二个TIMESTAMP类型，则默认值设置为0值。TIMESTAMP还有一个重要特点，就是和时区相关。
  • 日期类型的插入格式有很多，包括整数（如2100）、字符串（如 2038-01-19 11:14:08）、函数（如NOW()）。
字符串类型
image.png
  • CHAR和VARCHAR很类似，都用来保存MySQL中较短的字符串。二者的主要区别在于存储方式的不同：CHAR列的长度固定为创建表时声明的长度，长度可以为从0～255的任何值；而VARCHAR列中的值为可变长字符串，长度可以指定为0～255（MySQL 5.0.3版本以前）或者65535（MySQL 5.0.3版本以后）之间的值。在检索的时候，CHAR列删除了尾部的空格，而VARCHAR则保留这些空格。

TIPS> v varchar(4),c char(4) 分别存入"ab", select length(v),length(v)?

  • BINARY和VARBINARY类型，BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。当保存BINARY值时，在值的最后通过填充“0x00”（零字节）以达到指定的字段定义长度。

  • ENUM类型，ENUM中文名称叫枚举类型，它的值范围需要在创建表时通过枚举方式显式指定，对1～255个成员的枚举需要1个字节存储；对于255～65535个成员，需要2个字节存储。最多允许有65535个成员。ENUM类型是忽略大小写的，在存储“M”、“f”时将它们都转成了大写，对于插入不在 ENUM 指定范围内的值时，并没有返回警告，而是插入了enum('M','F')的第一个值“M”。ENUM类型只允许从值集合中选取单个值，而不能一次取多个值。

  • SET类型SET和ENUM类型非常类似，也是一个字符串对象，里面可以包含0～64个成员。根据成员的不同，存储上也有所不同。1～8成员的集合，占1个字节。9～16成员的集合，占2个字节。17～24成员的集合，占3个字节。25～32成员的集合，占4个字节。33～64成员的集合，占8个字节。SET 和 ENUM 除了存储之外，最主要的区别在于 SET 类型一次可以选取多个成员，而ENUM则只能选一个。对于超出允许值范围的值，将不允许注入到上面例子中设置的SET类型列中，而对于（'a,d,a'）这样包含重复成员的集合将只取一次，写入后的结果为“a,d”。

MySQL中的运算符

算术运算符
image.png

在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。对于模运算，还有另一种表达方式，使用MOD(a,b)函数与a%b效果一样。

比较运算符
image.png

image.png
  • 比较运算符可以用于比较数字、字符串和表达式。数字作为浮点数比较，而字符串以不区分大小写的方式进行比较。NULL不能用=/<>,但是却可以用<=>安全等于比较。

tips LIKE "%c" 与 "%c%"的区别？

逻辑运算符
image.png
  • “NOT”或“！”表示逻辑非。返回和操作数相反的结果：当操作数为0（假），则返回值为 1，否则值为 0。但是有一点除外，那就是NOT NULL的返回值为NULL
    -“XOR”表示逻辑异或。当任意一个操作数为NULL时，返回值为NULL。对于非NULL的操作数，如果两个的逻辑真假值相异，则返回结果1，否则返回0
位运算符 常用的
image.png
  • “位与”对多个操作数的二进制位做逻辑与操作，例如2&3，因为2的二进制数是10， 3是11，所有10&11的结果是10，十进制数字还是2

常用函数

常用的函数有字符串函数、日期函数和数值函数。在MySQL数据库中，函数可以用在SELECT语句及其子句（例如WHERE、ORDER BY、HAVING等）中，也可以用在UPDATE、DELETE语句及其子句中

字符串函数常用的
image.png

image.png
  • CONCAT(S1,S2,…Sn)函数：把传入的参数连接成为一个字符串。把“aaa”、“bbb”、“ccc”3个字符串连接成了一个字符串“aaabbbccc”。另外，任何字符串与NULL进行连接的结果都将是NULL。
  • INSERT(str,x,y,instr)函数：将字符串str从第x位置开始，y个字符长的子串替换为字符串instr。下面的例子把字符串“beijing2008you”中从第12个字符开始以后的3个字符替换成“me”。mysql> select INSERT('beijing2008you',12,3, 'me') ;
  • LOWER(str)和UPPER(str)函数：把字符串转换成小写或大写。
  • LEFT(str,x)和RIGHT(str,x)函数：分别返回字符串最左边的x个字符和最右边的x个字符。如果第二个参数是NULL，那么将不返回任何字符串。下例中显示了对字符串“beijing2008”应用函数后的结果。mysql> SELECT LEFT('beijing2008',7),LEFT('beijing',null),RIGHT('beijing2008',4);
  • LPAD(str,n,pad)和RPAD(str,n,pad)函数：用字符串pad对str最左边和最右边进行填充，直到长度为n个字符长度。
  • LTRIM(str)和RTRIM(str)函数：去掉字符串str左侧和右侧空格。
  • REPEAT(str,x)函数：返回str重复x次的结果。
  • REPLACE(str,a,b)函数：用字符串b替换字符串str中所有出现的字符串a。
  • STRCMP(s1,s2)函数：比较字符串s1和s2的ASCII码值的大小。如果s1比s2小，那么返回-1；如果s1与s2相等，那么返回0；如果s1比s2大，那么返回1，如下例所示。
  • TRIM(str)函数：去掉目标字符串的开头和结尾的空格。
  • SUBSTRING(str,x,y)函数：返回从字符串str中的第x位置起y个字符长度的字串。
数值函数常用的
image.png
  • ABS(x)函数：返回x的绝对值。
  • CEIL(x)函数：返回大于x的最小整数。
  • FLOOR(x)函数：返回小于x的最大整数，和CEIL的用法刚好相反。
  • MOD(x,y)函数：返回x/y的模。和x%y的结果相同，模数和被模数任何一个为NULL结果都为NULL
  • RAND()函数：返回0～1内的随机值。

tips 10/100/1000 如何获取

  • ROUND(x,y)函数：返回参数x的四舍五入的有y位小数的值。
  • TRUNCATE(x,y)函数：返回数字x截断为y位小数的结果。注意TRUNCATE和ROUND的区别在于TRUNCATE仅仅是截断，而不进行四舍五入。
日期和时间函数常用函数
image.png
  • CURDATE()函数：返回当前日期，只包含年月日
  • CURTIME()函数：返回当前时间，只包含时分秒。
  • NOW()函数：返回当前的日期和时间，年月日时分秒全都包含。
  • UNIX_TIMESTAMP(date)函数：返回日期date的UNIX时间戳。
  • FROM_UNIXTIME(unixtime)函数：返回 UNIXTIME 时间戳的日期值，和UNIX_TIMESTAMP(date)互为逆操作。
  • WEEK(DATE)和 YEAR(DATE)函数：前者返回所给的日期是一年中的第几周，后者返回所给的日期是哪一年。
  • HOUR(time)和MINUTE(time)函数：前者返回所给时间的小时，后者返回所给时间的分钟。
  • MONTHNAME(date)函数：返回date的英文月份名称。mysql> select MONTHNAME(now());

  • DATE_FORMAT(date,fmt)函数：按字符串 fmt 格式化日期 date 值，此函数能够按指定的格式显示日期，mysql> select DATE_FORMAT(now(),'%M,%D,%Y');


    image.png

  • DATE_ADD(date,INTERVAL expr type)函数：返回与所给日期date相差 INTERVAL时间段的日期。其中INTERVAL是间隔类型关键字，expr是一个表达式，这个表达式对应后面的类型， type是间隔类型。mysql> select now() current,date_add(now(),INTERVAL 31 day) after31days,date_add(now(),INTERVAL '1_2' year_month) after_oneyear_twomonth;


    image.png
  • 同样也可以用负数让它返回之前的某个日期时间，如下第1列返回了当前日期时间，第2列返回距离当前日期31天前的日期时间，第3列返回距离当前日期一年两个月前的日期时间。mysql> select now() current,date_add(now(),INTERVAL -31 day) after31days, date_add(now(),INTERVAL '-1_-2' year_month) after_oneyear_twomonth;
  • DATEDIFF（date1，date2）函数：用来计算两个日期之间相差的天数。

距离北京冬奥会开幕时间？

流程函数 常用函数
image.png
  • IF(value,t,f)函数：这里认为月薪在2000元以上的职员属于高薪，用“high”表示；而2000元以下的职员属于低薪，用“low”表示。
  • IFNULL(value1,value2)函数：这个函数一般用来替换 NULL 值，我们知道 NULL 值是不能参与数值运算的。
  • CASE WHEN [value1] THEN[result1]…ELSE[default]END函数：这里也可以用 case when…then函数。
  • CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END函数：这里还可以分多种情况把职员的薪水分多个档次，比如下面的例子分成高、中、低3种情况。
其他函数 常用函数
image.png
  • INET_ATON(IP)函数：返回IP地址的网络字节序表示。
  • INET_NTOA(num)函数：返回网络字节序代表的IP地址。

tips
按照正常的思维，应该用字符串来进行比较，下面是字符串的比较结果：mysql> select * from t where ip>='192.168.1.3' and ip<='192.168.1.20';
结果没有如我们所愿，竟然是个空集。其实原因就在于字符串的比较是一个字符一个字符的比较，当对应字符相同时，就比较下一个，直到遇到能区分出大小的字符才停止比较，后面的字符也将忽略。显然，在此例中，“192.168.1.3”其实比“192.168.1.20”要“大”，因为“3”比“2”大，而不能用我们日常的思维 3<20，所以“ip>='192.168.1.3' and ip<='192.168.1.20'”必然是个空集。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 194,242评论 5赞 459
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 81,769评论 2赞 371
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 141,484评论 0赞 319
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 52,133评论 1赞 263
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,007评论 4赞 355
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 46,080评论 1赞 272
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,496评论 3赞 381
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,190评论 0赞 253
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,464评论 1赞 290
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,549评论 2赞 309
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,330评论 1赞 326
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,205评论 3赞 312
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 37,567评论 3赞 298
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 28,889评论 0赞 17
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,160评论 1赞 250
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 41,475评论 2赞 341
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 40,650评论 2赞 335