数据库操作(DDL)

数据库的创建

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] db_name [[DEFAULT] CHARACTER SET [=] charset_name]

例如：

CREATE DATABASE IF NOT EXISTS  test_db DEFAULT CHARACTER SET utf8

查看当前服务器下的数据库列表

SHOW { DATABASES | SCHEMAS }

查看数据库的定义

SHOW CREATE  { DATABASE | SCHEMA } db_name

查看上一步操作的警告信息

SHOW WARNINGS

删除数据库

DROP  { DATABASE | SCHEMA } [IF EXISTS] db_name

打开数据库

USE db_name

修改数据库的编码方式

ALTER { DATABASE | SCHEMA }  db_name [DEFAULT] CHARACTER SET [=] charset_name

数据表操作

MYSQL中的数据类型

整数类型

数据类型	存储范围	字节
TINYINT	有符号值-128～127;无符号值 0～255;	1
SMALLINT	有符号值-32768～32767;无符号值 0～65535;	2
MEDIUMINT	有符号值-8388608～8388607;无符号值 0～16777215;	3
INT	有符号值-2147483648～2147483647;无符号值 0～4294967295;	4
BIGINT	有符号值-9223372036854775808～9223372036854775807;无符号值 0～18446744073709551615L;	8
BOOL,BOOLEAN	等同于TINYINT(1),0为FALSE ,其余为TRUE	1

浮点类型

数据类型	存储范围	字节
FLOAT[(M,D)]	负数取值范围为-3.40E+38~-1.17E-38,0,1.175E-38~3.40E38;M是数字总位数,D是小数点后面的位数,若M,D被省略，根据硬件限制来保存值。	4
DOUBLE[(M,D)]	-1.79E+308~2.22E-308,0,2.22E-308~1.79E308	8
DECIMAL[(M,D)]	和DOUBLE一样，内部以字符串形式存储数值	M+2

字符串类型

数据类型	存储需求	备注
CHAR(M)	M个字节，0<=M<=255
VARCHAR(M)	L+1个字节，L<=M且0<=M<=65535
TINYTEXT	L+1个字节，L<2^8
TEXT	L+2个字节，L<2^16
MEDIUMTEXT	L+3个字节，L<2^24
LONGTEXT	L+4个字节，L<2^32
ENUM('Value1','Value2',......)	1或2个字节，取决于枚举值的个数(最多65535个值)	只能选择一项，可通过数值插入，第一项为1，第二项为2...
SET('Value1','Value2',......)	1,2,3,4或8个字节，取决于SET成员的数目(最多64个成员)	可选择多项，选中的不同值由","分隔，且可通过数值设置(第一项为1,第二项为2，第三项为8，第四项为16......选中前三项值为11)

日期时间类型

数据类型	存储范围	存储需求	备注
TIME	-838:59:59 ~ 838:59:59	3
DATE	1000-1-1 ~ 9999-12-31	3
DATETIME	1000-1-1 00:00:01 UTC ~ 9999-12-31 23:59:59	8
TIMESTAMP	1970-1-1 00:00:01 UTC ~ 2038-1-19 3:14:07	4
YEAR	1901~2155	1	值可为数字或者字符串,70~99=>1970~1999,00~69=>2000~2069,数字0转换为0000

二进制类型

JSON类型(ver5.7新增)

存储引擎

查看MYSQL支持的存储引擎

SHOW ENGINES

查看支持的存储引擎信息

SHOW VARIABLES LIKE 'have%'

查看默认的存储引擎

SHOW VARIABLES LIKE '%storage_engine%'

常用存储引擎(注意各引擎优缺点)

• InnoDB
• MyISAM
• Memory

约束条件

一个表只能有一个主键(PRIMATY KEY),但是能够有多个唯一(UNIQUE KEY)，唯一的字段的值不允许出现重复，但是NULL值不算做重复的值

• PRIMARY KEY 主键，PRIMARY可省略 (注意：PRIMARY KEY(a,b,c,...) 来定义一个主键时,由a,b,c,...所有的值才能确定一个主键，即一个表包括a,b,c...等字段，其中单独的a,b,c字段相等，是可以的，但不能全部相等，全部相等表明主键相等，不可插入。）
• AUTO_INCREMENT 自增,需要和主键(PRIMARY KEY)搭配使用。可在创建表时自定义从某个数开始,如CREATE TABLE tbl_name(id INT key AUTO_INCREMENT,......)AUTO_INCREMENT=数值,表示内部自增字段从此数值开始。(也可通过ALTER TABLE tbl_name AUTO_INCREMENT=数值修改)
• FOREIGN KEY 外键
• NOT NULL 非空
• UNIQUE KEY 唯一，KEY 可省略
• DEFAULT 默认值 插入数据时可使用DEFAULT

数据表的创建

CREATE TABLE [IF NOT EXISTS] tbl_name (
     字段名称 字段类型 [完整性约束条件] ##完整性约束条件顺序为: [UNSIGNED | ZEROFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY | UNIQUE [KEY]] AUTO_INCREMENT
     ......
)ENGINE = 引擎名称 CHARSET = 编码方式；

查看当前数据库下的数据表

SHOW TABLES;

查看表结构及创建信息

DESC tbl_name
DESCRIBE tbl_name
SHOW COLUMNS FROM tbl_name
SHOW CREATE TABLE tbl_name

修改表结构

修改表名

ALTER TABLE tbl_name RENAME [TO|AS] new_name

或者

RENAME TABLE tbl_name TO new_name

添加字段

ALTER TABLE tbl_name ADD 字段名称 字段类型 [完整性约束条件]  [ FIRST | (AFTER 字段名称)] 

若需要添加多个字段，需要多个ADD操作，以“，”号分隔

删除字段

ALTER TABLE tbl_name DROP 字段名称

若需要删除多个字段，需要多个DROP操作，以“，”号分隔。

注意:添加字段(ADD)与删除字段(DROP)可以在同一个语句里面(ALTER TABLE tbl_name ADD .....,DROP .....)

修改字段

ALTER TABLE tbl_name MODIFY 字段名称 字段类型 [完整性约束条件] [FIRST | (AFTER  字段名称)]

修改字段名称

ALTER TABLE tbl_name CHANGE 旧字段名称 新字段名称 字段类型 [完整性约束条件] [FIRST | (AFTER  字段名称)]

添加默认值

ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值

删除默认值

ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT

添加主键

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (字段名称,...) 

删除主键

ALTER TABLE tbl_name DROP PRIMARY KEY

添加唯一

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [索引名称] (字段名称,...)

删除唯一

ALTER TABLE tbl_name DROP  {INDEX| KEY} index_name

修改表的存储引擎

ALTER TABLE tbl_name ENGINE=存储引擎名称

修改表的自增长值

ALTER TABLE tbl_name AUTO_INCREMENT= 值

删除数据表

DROP TABLE [IF EXISTS] tbl_name[,tbl_name,...]

数据操作(DML)

插入数据

不指定具体的字段

INSERT [INTO] tbl_name VALUE | VALUES (值,......)

列出指定字段

INSERT [INTO] tbl_name (字段名称1,......) VALUE | VALUES (值1,......)

同时插入多条记录

INSERT [INTO] tbl_name   [(字段名称1,......)]  VALUE | VALUES (值,......), (值,......),......

通过SET形式插入记录

INSERT [INTO] tbl_name SET 字段名称=值,......

将查询结果插入到表中

INSERT [INTO] tbl_name   [(字段名称1,......)]  SELECT 字段名称 FROM tbl_name  [WHERE 条件]

更新数据

UPDATE tbl_name  SET 字段名称=值,...... [WHERE 条件] [ORDER BY 字段名称] [LIMIT 限制条件]

删除数据

(DELETE FROM tbl_name  [WHERE 条件] [ORDER BY 字段名称] [LIMIT 限制条件])

或者彻底清空数据表

(TRUNCATE [TABLE ] tbl_name)

查询数据操作(DQL)

查询记录

SELECT select_expr[,select_expr,...]
[ 
    FROM table_reference
    [WHERE 条件]
    [GROUP BY {col_name | position } [ ASC | DESC], ... 分组]
    [HAVING 条件 对分组结果进行二次筛选]
    [ORDER BY {col_name | position } [ ASC | DESC], ... 排序]
    [LIMIT 限制显示条数]
]

查询表达式

每个表达式表示想要的一列，必须至少有一列，多个列以","号分隔； "*" 表示所有列，tbl_name.*表示命名表的所有列。查询表达式可以使用 [AS] alias_name 为其赋予别名。

WHERE条件

查询条件	符号	示例
比较	=,<,>,<=,>=,!=,<>,!>,!<,<=>	SELECT * FROM table_name WHHERE id = 1
指定范围	BETWEEN AND,NOT BETWEEN AND	SELECT * FROM table_name WHHERE id BETWEEN 1 AND 2
指定集合	IN,NOT IN	SELECT * FROM table_name WHHERE id IN(1,3,5,7,9) 集合内忽略大小写
匹配字符	LIKE, NOT LIKE	SELECT * FROM table_name WHHERE id LIKE '_2%'
是否为空	IS NULL,IS NOT NULL	SELECT * FROM table_name WHHERE id IS NULL
多个查询条件	AND,OR	SELECT * FROM table_name WHHERE user = 'admin' AND password = 'admin'

模糊查询

• %：代表0个，1个或者多个任意字符
• _ ：代表1个字符

GROUP BY 查询结果分组

默认情况下，GROUP BY 查询结果分组得到每组的第一个值
配合GROUP_CONCAT()得到分组详情
配合聚合函数

• COUNT() NULL值不算一条记录
• MAX()
• MIN()
• AVG()
• SUM()

配合WITH ROLLUP记录上面所有记录的总和

5.通过HAVING子句对分组结果进行二次筛选

配合GROUP BY 实现，比如：

SELECT Region,COUNT(*),MAX(Population),MIN(Population),SUM(Population),AVG(Population) FROM country GROUP BY Region HAVING MAX(Population) > 1000000

通过ORDER BY 进行排序

默认升序排列(ASC),可省略。降序排列(DESC),可排序多个字段通过","号连接

SELECT * FROM country ORDER BY Population ASC,SurfaceArea ASC
SELECT * FROM country ORDER BY 3 ASC
SELECT * FROM country ORDER BY RAND() #随机记录

LIMIT限制查询结果显示条数

• LIMIT 显示条数
• LIMIT 偏移量，显示条数

(SELECT * FROM country LIMIT 2,1)

连接查询

什么是链接查询

连接查询是将两个或者两个以上的表按照某个条件连接起来，从中选取需要的数据。连接查询是同时查询两个或者两个以上的表时使用的。当不同的表中存在相同意义的字段时，可通过该字段连接这几个表。

内连接查询

1. JOIN | CROSS JOIN | INNER JOIN
2. 通过ON 连接条件
3. 显示两个表中符合连接条件的记录

注意：三张表或者三张表以上查询可以直接加入更多的JOIN ...ON ...来连接多张表，注意找准表之间的连接条件

例如:

SELECT 
    user_info.id, user_info.name, city_info.pro_name
FROM
    user_info
        JOIN
    city_info ON user_info.pro_id = city_info.pro_id;

外连接查询

左外连接

(LEFT [OUTER] JOIN)

显示左表的全部记录及右表符合连接条件的记录

右外连接

(RIGHT [OUTER] JOIN)

显示右表的全部记录及左表符合连接条件的记录

外键

概览

外键是表的一个特殊字段，被参照的表是主表，外键所在字段的表为子表。设置外键的原则需要记住，就是依赖于数据库中已存在的表的主键。外键的作用是建立该表与其父表的关联关系。父表中对记录做操作时，子表中与之对应的信息也应有相应的改变。
外键的作用是保持数据库的一致性和完整性。
可以实现一对一或者一对多的关系

注意

1. 父表和子表必须使用相同的存储引擎，而且禁止使用临时表。
2. 数据库的存储引擎只能为InnoDB。
3. 外键列和参照列必须具有相似的数据类型其中数字的长度或是否有符号位必须相同：而字符的长度则可以不同。
4. 外键列和参照列必须创建索引。如果外键不存在索引的话，MYSQL将自动创建索引。

外键约束的参照操作：

• CASCADE:从父表删除或者更新且自动删除或者更新子表中匹配的行。
• SET NULL:从父表删除或者更新行，并设置子表的中的外键列为NULL。如果使用该选项必须保证子表列中没有指定NOT NULL。
• RESTRICT:拒绝对父表的更新或者删除操作。
• NOT ACTION: 标准SQL的关键字，在MYSQL中与RESTRICT相同

创建外键(先有主表)

CREATE TABLE [IF NOT EXISTS] tbl_name (
 字段名称 字段类型 [完整性约束条件] ##完整性约束条件顺序为: [UNSIGNED | ZEROFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY | UNIQUE [KEY]] AUTO_INCREMENT
...
[CONSTRAINT 外键名称] FOREIGN KEY(字段名) REFERENCES 父表(字段名) [ON {DELETE | UPDATE } {CASCADE | SET NULL }] [ON {DELETE | UPDATE } {CASCADE | SET NULL}]
)

删除外键

(ALTER TABLE tbl_name DROP FOREIGN KEY)

添加外键

(ALTER TABLE tbl_name ADD [CONSTRAINT 外键名称]  FOREIGN KEY(字段名) REFERENCES 父表(字段名))

示例：

部门表(父表)

id	depName
1	教学部
2	技术部
3	运营部
4	市场部

CREATE TABLE IF NOT EXISTS department (id TINYINT UNSIGNED AUTO_INCREMENT KEY,depName VARCHAR(20) NOT NULL UNIQUE)ENGINE=INNODB;

员工表(子表)

id	username	depId
1	King	1
2	Zhang	3
3	Ling	2
4	Jack	4

CREATE TABLE IF NOT EXISTS employee (id TINYINT UNSIGNED AUTO_INCREMENT KEY,userName VARCHAR(20) NOT NULL UNIQUE,depId TINYINT UNSIGNED)ENGINE=INNODB;

如果只删除父表中部门4，那么员工表中还有属于部门4的员工存在，这显然是不合适的，这时，可以使用外键来解决

创建外键(先有主表)：

CREATE TABLE IF NOT EXISTS employee (id TINYINT UNSIGNED AUTO_INCREMENT KEY,userName VARCHAR(20) NOT NULL UNIQUE,depId TINYINT UNSIGNED ， FOREIGN KEY(depId) REFERENCES deparment(id) )ENGINE=INNODB;

联合查询

UNION

(SELECT 语句 UNION SELECT 语句)

UNION ALL

(SELECT 语句 UNION SELECT 语句)

注意：UNION 和 UNION ALL 的区别是UNION 去掉相同记录，UNION是简单的合并到一起

子查询

什么是子查询

子查询是将一个查询语句嵌套在另一个查询语句之中。内层查询语句的查询结果，可以为外层查询语句提供条件

引发子查询的情况

• 使用 [NOT] IN 的子查询
• 使用比较运算符的子查询 = > < >= <= <> != <=>
• 使用 [NOT] EXISTS 的子查询
• 使用 ANY | SOME 或者 ALL的子查询

符号	ANY	SOME	ALL
>,>=	最小值	最小值	最大值
<,<=	最大值	最大值	最小值
=	任意值	任意值
!=,<>			任意值

示例：

SELECT name FROM user where depId IN(SELECT id FROM ids)
SELECT name FROM user where depId >=(SELECT id FROM ids)
SELECT name FROM user where EXISTS(SELECT id FROM ids)

将查询结果写入到数据表

(INSERT [INTO] tbl_name  [(col_name),......] SELECT ...)

创建数据表的同时将查询结果写入到数据表

CREATE TABLE [IF NOT EXISTS] tbl_name (
  [(creat_defination)]
  select_statement

正则表达式查询

REGEXP '匹配方式'

(SELECT * FROM user WHERE REGEXP '^[1-9]$')

常用匹配方式

模式字符	含义
^	匹配字符串开始
$	匹配字符串结尾
.	匹配字符串任一字符，包括换行和回车
[字符集和]	匹配字符集合的任一字符
[^字符集和]	匹配除了字符集合以外的任一字符
s1 | s2 | s3	匹配s1、s2、s3任一字符串
*	代表0个、1个或者多个其前的字符
+	代表1个或者多个其前的字符
字符串{N}	字符串出现N次
字符串{M,N}	字符串至少出现M次,最多N次

运算符

算数运算符

符号	表达式	作用
+	X1+X2	加法
-	X1-X2	减法
*	X1*X2	乘法
/	X1 / X2	除法
DIV	X1 DIV X2	除法
%	X1 % X2	取余
MOD	X1 MOD X2	取余

比较运算符

符号	形式	作用
=	X1=X2	判断是否相等
!=或<>	X1!=X2或X1<>X2	判断是否不相等
<=>	X1<=>X2	判断是否相等,可以判断是否等于NULL
>,>=	X1>X2,X1>=X2	判断是否大于等于
<,<=	X1<X2,X1<=X2	判断是否小于等于
IS NULL 或 IS NOT NULL	x1 IS [NOT] NULL	判断是否等于NULL
BETWEEN ... AND ... 或者 NOT BETWEEN ... AND...	X1 BETWEEN m AND n	判断是否在范围内
IN 或 NOT IN	X1 IN (值1,...)	判断是否在一个固定范围内
LIKE 或 NOT LIKE	X1 LIKE 表达式	判断是否匹配
REGEXP	REGEXP 正则表达式	判断是否正则匹配

逻辑运算符

符号	形式	作用
&& 或 AND	与	并且
||或 OR	或	或者
! 或 NOT	非	取反
XOR	异或	不同为真

运算符的优先级

优先级	运算符	优先级	运算符
1	!	8	|
2	~	9	=,<=>,>>=,<,<=,!=,<>,LIKE,IN,IS NULL,REGEXP
3	^	10	BETWEEN AND,CASE,THEN,WHEN,ELSE
4	*,/,DIV,%,MOD	11	NOT
5	+,-	12	&&,AND
6	>>,<<	13	||,OR,XOR
7	&	14	;=

注意: 可以使用()改变优先级

MYSQL中的函数

数学函数

名称	描述
CELL(x)	进一取整
FLOOR(x)	舍一取整
MOD(x,y)	取余数(取模)
POWER(x,y)	幂运算
ROUND(x)	四舍五入
TRUNCATE(x,y)	数字截取
ABS()	取绝对值
PI()	圆周率
RAND()和RAND(X)	返回0~1之间的随机数，RANX(X)
SIGN(X)	返回X的符号，-1为负数，0，1为正数
EXP(X)	计算e的几次方

字符串函数

名称	描述
CHAR_LENGTH(x)	返回字符串字符数
LENGTH(x)	返回字符串长度
CONCAT(s1,s2,...)	合并字符串
CONCAT_WS(x,s1,s2,...)	以指定分隔符连接连接字符串
UPPER(x)/UCASE(x)	将字符串转化为大写
LOWER(x)/LCASE(x)	将字符串转化为小写
LEFT(S,N)/RIGHT(S,N)	返回字符串的前/后N个字符
LPAD(S1,LEN,S2)/RPAD(s1,LEN,s2)	将字符串s1用s2 填充到指定的LEN
LTRIM(s)/RTRIM(s)/TRIM(s)	去掉字符串空格
TRIM(s1 FROM s)	去掉字符串S中结尾处和开始处的s1
REPEAT(S,N)	重复字符串的指定次数
SPACE(N)	返回N个空格
REPLACE(s,s1,s2)	从字符串s中搜索s1,替换s2
STRCMP(s1,s2)	比较字符串，>=<分别返回1，0，-1
SUBSTRING(S,N,LEN)	截取字符串
REVERSE(S)	反转字符串
ELT(N,S1,S2)	返回指定位置的字符串

日期时间函数

名称	描述
CURDATE(),CURRENT_DATE()	返回当前日期
CURTIME(),CURRENT_TIME()	返回当前时间
NOW()	返回当前日期时间
MONTH(D)	返回日期中月份的值
MONTHNAME(D)	返回日期中月份的名字
DAYNAME(D)	返回日期中的星期几
DAYOFWEEK(D)	返回一周内的第几天，1代表星期日
WEEKDAY(D)	返回日期是星期几，0代表星期一
WEEK(D)	一年中的多少个星期
YEAR(D)	返回日期中年份的值
HOUR(T)	返回时间中的小时
MINUTE(T)	返回时间中的分钟数
SECOND(T)	返回时间中的秒数
DATEDIFF(D1,D2)	返回D1,D2的间隔天数

条件判断函数

名称	描述
IF(EXPR,V1,V2)	如果EXPR成立，返回V1,否则V2
IFNULL(V1,V2)	如果V1 不为空，显示V1的值；否则V2
CASE WHEN expr1 THEN v1 [WHEN expr2 THEN v2 ] [ ELSE vn] END	CASE表示函数开始，END表示函数结束。如果表达式expr1成立时，返回v1.如果表达式expr2成立时，返回v2.以此类推，最后遇到ELSE时，返回vn的值。

系统信息函数

名称	描述
VERSIOn()	返回数据库版本号
CONNECTION_ID()	返回数据库连接数
DATABASE(),SCHEMA()	返回当前数据库名
USER(),SYSTEM_USER	返回当前用户
CURRENT_USER()	返回当前用户
CHARSET(s)	返回字符串s 的字符集
COLLATION(s)	返回字符串s 的检验字符集
LAST_INSERT_ID()	返回最近生成的AUTO_INCREMENT的值

加密函数

名称	描述
MD5(str)	信息摘要算法
PASSWORD(str)	密码算法(ver5.6过时)
ENCODE(str,pwd_str)	加密结果是一二进制数，必须使用Blob字段保存
DECODE(crypt_str,pwd_str)	对通过ENCODE加密之后的内容解密

其他常用函数

名称	描述
FORMAT(x,n)	将数字x进行格式化，将x保留到小数点后n位
ASCII(x)	返回字符串x的第一个字符的ASCII码
BIN(x)	返回x的二进制编码
HEX(x)	返回x的十六进制编码
OCT(X)	返回x的八进制编码
CONV(x,f1,f2)	返回将x从f1进制数变成f2进制数
INET_ATON(ip)	将ip地址转换为数字
INET_NTOA(n)	将数字转化为IP地址
GET_LOCK(name,time)	定义锁
RELEASE_LOCK(name)	释放锁

索引

什么是索引

• 索引由数据库中一列或者多列组成，其作用是提高对表中数据的查询速度
• 索引的优点是可以提高检索数据的速度
• 索引的缺点是创建和维护索引需要耗费时间
• 索引可以提高查询速度，减慢写入速度

索引分类

• 普通索引
• 唯一索引(UNIQUE)
• 全文索引(FULLTEXT)
• 单列索引
• 多列索引
• 空间索引(数据类型为GEOMETRY,存储引擎为MyISAM)SPATIAL

创建索引

创建表的时候创建索引

CREATE TABLE [IF NOT EXISTS] tbl_name (
     字段名称 字段类型 [完整性约束条件] ##完整性约束条件顺序为: [UNSIGNED | ZEROFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY | UNIQUE [KEY]] AUTO_INCREMENT
     ......
 [UNIQUE | FULLTEXT | SPATIAL ] INDEX|KEY [索引名称](字段名称[(长度)] [ASC|DESC]) 
)ENGINE = 引擎名称 CHARSET = 编码方式；

在已存在的表上创建索引

(CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX  索引名称 ON 表名 {字段名称[(长度)]  [ASC|DESC] ) })

或者

(ALTER TABLE tbl_name ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名称(字段名称[(长度)] [ASC|DESC]) )

删除索引

(DROP INDEX 索引名称 ON tbl_name)

其他

注释

• 以‘-- ’ 为前缀
• 以'#' 为前缀
• 字段注释通过COMMENT '.....'进行注释