使用命令行连接MySQL

通过以下指令连接：

1
mysql -h 主机名 -P 端口 -u 用户名 -p密码

注意使用该指令时，必须启动MySQL服务

1
2
net stop mysql # 停止
net start mysql # 启动

注意：

  1. -p密码中间不能有空格
  2. -p后面没有写密码，回车会要求输入密码
  3. 如果没有写-h 主机名，默认是本机
  4. 如果没有写-P 端口号，默认是3306
  5. 在实际工作中，3306一般都会被修改

MySQL的三层结构

  1. 第一层是数据库管理系统（DBMS)
  2. 第二层是数据库（database）
  3. 第三层是表（table）

数据库管理系统下拥有多个数据库，而每个数据库下又拥有多个表，数据库在操作系统中体现成文件夹，而表在操作系统下体现为文件

SQL语句的分类

DDL：数据库定义语句[create 表, 数据库…]

DML：数据操作语句[增加 insert, 修改 update, 删除 delete]

DQL：数据查询语句[select]

DCL：数据控制语句[管理数据库：比如用户权限 grant revoke]

数据库

创建数据库

1
CREATE DATABASE [IF NOT EXISTS] db_name [create_specification[, create_specification]...]

若db_name为关键字，可以使用反引号规避该错误，例如：CREATE DATABASE CREATE;

create_specificatin：

  • CHARACTER SET charset_name：指定数据库采用的字符集，如果不指定字符集，默认utf8
  • COLLATE collation_name：指定数据库字符集的校对规则（常用的 uft8_bin[区分大小写]、utf8_general_ci[不区分大小写] 注意默认是utf8_general_ci）
1
2
3
4
5
6
# 创建按一个名称为cz_db01的数据库
CREATE DATABASE cz_db01;
# 创建一个使用utf8字符集的cz_db02的数据库
CREATE DATABASE cz_db02 CHARACTER SET utf8;
# 创建一个使用utf8字符集，并带校对规则的cz_db03的数据库
CREATE DATABASE cz_db03 CHARACTER SET utf8 COLLATE utf8_bin;

显示数据库

1
SHOW DATABASES;

显示数据库的创建语句

1
SHOW CREATE DATABASE db_name;

删除数据库

1
DROP DATABASE [IF EXISTS] db_name
1
2
3
4
5
6
# 查看当前数据库服务器中的所有数据库
SHOW DATABASES;
# 查看数据库cz_db01的定义信息
SHOW CREATE DATABASE cz_db01;
# 删除数据库cz_db01
DROP DATABASE IF EXISTS cz_db01;

备份数据库或者数据库的某些表

  1. 备份数据库：（在windows命令行窗口执行）
1
mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > 文件名.sql
  1. 备份数据库的某些表：（在windows命令行窗口执行）
1
mysqldump -u 用户名 -p 数据库 表1 表2 表n > 文件名.sql;
  1. 恢复数据：（在mysql命令行窗口执行）
1
2
# 如果是备份的数据库，则直接执行，如果备份的是表，那么需要先使用数据库，再执行
SOURCE 文件名.sql;

表

创建表

1
2
3
4
5
6
CREATE TABLE table_name 
(
  field1 datatype,
  field2 datatype,
  field3 datatype
) CHARACTER SET 字符集 COLLATE 校对规则 ENGINE 引擎

field：指定列名 datatype：指定列类型（字段类型）

character set：如不指定则为所在数据库字符集

collate：如不指定则为所在数据库校对规则

engine：存储引擎，默认InnoDB

删除表

1
DROP TABLE table_name;

修改表

  1. 添加列：
1
2
3
4
ALTER TABLE table_name ADD
(
  column datatype [DEFAULT expr] [, column datatype] ...
);
  1. 修改列：
1
2
3
4
ALTER TABLE table_name MODIFY
(
  column datatype [DEFAULT expr] [, column datatype]...
);
  1. 删除列：
1
2
3
4
ALTER TABLE table_name DROP
(
  column
);
  1. 修改表名：
1
RENAME TABLE 表名 to 新表名;
  1. 查看表的结构：
1
desc 表名;
  1. 修改表字符集：
1
ALTER TABLE 表名 CHARACTER SET 字符集;
  1. 修改列名：
1
ALTER TABLE 表名 CHANGE 旧列名 新列名 datatype;
1
2
3
4
5
6
7
8
9
10
11
12
# 员工表emp增加一个image列，varchar类型（要求在resume列的后面
ALTER TABLE emp ADD image VARCHAR(255) NOT NULL DEFAULT '' AFTER RESUME;
# 修改job列，使其长度为60
ALTER TABLE emp MODIFY job VARCHAR(60);
# 删除sex列
ALTER TABLE emp DORP sex;
# 修改表名为employee
RENAME TABLE emp TO employee;
# 修改表的字符集为utf8
ALTER TABLE employee CHARACTER SET utf8;
# 列名name修改为user_name
ALTER TABLE employee CHANGE name user_name VARCHAR(20) NOT NULL DEFAULT '';

字段类型

数值类型

DECIMAL(M,D)中M是小数位（精度）的总数，D是小数点（标度）后面的位数，如果D是0，则值没有小数点或分数部分。M最大65。D最大是30。如果D被省略，默认是0。如果M被省略，默认是10

文本、二进制类型

VARCHAR最大可以选择65535个字节，但是由于字符集不一样，进而导致存储汉字所需要的字节数不一样，导致能选择的size会减小，并且还需要1到3个字节记录当前字段的大小。例如使用utf8编码，那么就需要使用3个字节来表示一个字符，那么它最大可以设置的size为 （65535 - 3）/ 3 = 21844

使用细节：

  1. char、varchar 后面所带的size都是代表的字符数，不是字节数，不管是中文还是字母都是按照字符来进行存放
  2. char是定长字符串，即如果存放的字符数没有达到最大字符数，他在底层依旧占用分配的最大字符数的空间。varchar是变长字符串，即如果存放的字符数没有达到最大字符数，他在底层会占用相应的字符数的空间
  3. 对于一些定长存储的数据（例如：md5加密后的密码为32位），推荐使用char进行存储，因为char的查询效率高于varchar
  4. 如果存储的字符数超过了varchar的最大字符数，那么可以采用text，mediumtext，longtext进行存储

日期类型

TIMESTAMP在INSERT和UPDATE时，会自动更新

1
2
3
4
5
6
7
8
CREATE TABLE birtday (
  # 日期类型
  t1 DATE,
  # 日期时间类型
  t2 DATETIME,
  # 时间戳类型，会随着当前行的更新而变为当前时间，插入行时默认是当前时间
  t3 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
)

CRUD

添加数据

1
INSERT INTO table_name [(column [, column...])] VALUES (value [, value...]);

细节：

  1. 插入的数据英语字段的数据类型相同
  2. 数据的长度应在列规定的范围内
  3. 再values中列出的数据位置必须与被加入的列的排列位置相对应
  4. 字符和日期型数据应包含在单引号中
  5. 列可以插入空值[前提是该字段允许为空]
  6. 可以使用 insert into tab_name (列名…) values (), (), () …. 的形式添加多条记录
  7. 如果是给表中的所有字段添加数据，可以不写前面的字段名称
  8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错
1
INSERT INTO employee values (4, '赵六', '2014-5-12', '2015-5-6 12:10:10', '架构师', 50000, '系统架构', 'D:\\a.png', '');

修改数据

1
UPDATE table_name SET col_name1 = expr1 [, col_name2 = expr2 ...] [WHERE where_definition]

细节：

  1. UPDATE语句可以用新值更新原有表行中的各列
  2. SET子句知识要修改哪些列和要给予哪些值
  3. WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行
  4. 如果需要修改多个字段，可以通过 SET 字段1 = 值1, 字段2 = 值2 ….
1
2
3
4
5
6
# 将所有员工的薪水修改为5000
UPDATE emp SET salary = 5000;
# 将姓名为张三的员工薪水改为3000
UPDATE emp SET salary = 3000 WHERE name = '张三';
# 将姓名为李四的员工薪水在原有基础上上涨1000
UPDATE emp SET salary = salary + 1000 WHERE name = '李四';

查询数据

  1. 基本语法：
1
SELECT [DISTINCT] * | {column1, column2, column3 ...} FROM table_name;

注意：

  1. Select 指定查询哪些列的数据。
  2. column指定列名。
  3. *号代表查询所有列。
  4. From指定查询哪张表。
  5. DISTINCT可选,指显示结果时,是否去掉重复数据
1
2
# 查询学生表中的姓名和英语成绩
SELECT DISTINCT name, english FROM student;
  1. 使用表达式对查询的列进行运算：
1
SELECT * | {column1 | expression, column2 | expression...} FROM table_name;
1
2
3
4
# 统计每个学生的总分
SELECT name, (chinese + english + math) FROM student;
# 统计每个学生的总分 + 10分的情况
SELECT name, (chinese + english + math + 10) FROM student;
  1. 在select语句中可使用as语句：
1
2
3
4
# 给字段取别名
SELECT column_name [AS] 别名 FROM table_name;
# 给表名取别名
SELECT * FROM table_name [AS] 别名;
1
SELECT name as '姓名', (chinese + english + math) as 'total_score' FROM student;

给表或者字段取别名时，可以省略AS关键字

  1. 在where子句中经常使用的运算符如下：
比较运算符 >、<、<=、>=、=、<>、!= 大于、小于、大于(小于)等于、不等于
BETWEEN …. END … 显示在某一区间的值
IN(set) 显示在in列表中的值,例:in(100,200)
LIKE ‘str’、NOT LIKE ‘str’ 模糊查询
IS NULL 判断是否为空
逻辑运算符 AND 多个条件同时成立
OR 多个条件任一成立
NOT 不成立,例:where not(salary>100);
  1. 比较运算符中可以对日期进行对比
  2. LIKE操作符中 %表示0到多个字符 _表示单个字符，例如显示第三个字符为大写O的所有员工信息：SELECT * FROM emp WHERE name LIKE ‘__O%’;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 查询数学成绩大于60分，并且id大于5的学生信息
select * from student where math > 60 and id > 5;
# 查询英语成绩大于语文成绩的学生信息
select * from student where english > chinese;
# 查询总分大于200分，数学成绩小于语文成绩，并且姓名为李的学生信息
select * from student where (chinese + math + english) > 200 and math < chinese and name LIKE '李%';
# 查询语文成绩在70到80之间的学生信息
select * from student where chinese between 70 and 80;
# 查询总分在189，190，191的学生信息
select * from student where (chinese + math + english) in (189, 190, 191);
# 查询姓李或者姓宋的学生信息
select * from student where name like '李%' or '宋%';
# 查询数学成绩比语文成绩多30分的学生信息
select * from student where (math - chinese) = 30;
  1. 使用order by子句排序查询结果：
1
SELECT column1, column2, column3 ... FROM table_name ORDER BY column asc | desc;
  1. ORDER BY 指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的别名
  2. Asc 升序（默认）、Desc 降序
  3. ORDER BY 子句应位于SELECT语句的结尾
  4. ORDER BY 后面可以跟多个列，查询的结果会按照ORDER BY 后面的列的要求依次排除，例如：按照每个部门号进行排序，后按照部门内部的薪水进行排序，SELECT * FROM dept ORDER BY dept_no DESC, salary DESC;
1
2
3
4
5
6
# 查询数学成绩从低到高排序的学生信息
SELECT * FROM student ORDER BY math asc;
# 查询总分从高到低排序的学生信息
SELECT name, (chinese + math + english) as total_score FROM student ORDER BY total_score DESC;
# 查询姓李的学生信息，并且按照总分从高到低排序
SELECT name, (chinese + math + english) as total_score FROM student WHERE name LIKE '李%' ORDER BY total_score;

统计函数

  1. count(*)返回表中满足条件的总行数，count(列)返回表中满足条件的总行数（但是不会统计该列元素为null的某行）
1
SELECT COUNT(*) | COUNT(列名) FROM table_name [WHERE where_definition];
1
2
3
4
5
# 查询学生总数
SELECT COUNT(*) FROM student;
# 查询数学成绩为90的学生总数
SELECT * FROM student WHERE math = 90;
SELECT COUNT(name) FROM student WHERE math = 90;
  1. SUM函数返回满足where条件的行的和，一般用在数值列
1
SELECT SUM(列名) {, sum(列名)....} FROM table_name [WHERE where_definition];
1
2
3
4
5
6
7
8
# 查询数学成绩的总和
SELECT SUM(math) FROM student;
# 查询语文、英语、数学成绩的总和
SELECT SUM(chinese), SUM(math), SUM(english) FROM student;
# 查询总分总和
SELECT SUM(chinese + math + english) FROM student;
# 查询语文成绩的平均分
SELECT AVG(chinese) FROM student;
  1. AVG函数返回满足WHERE条件的一列的平均值
1
SELECT AVG(列名) {, AVG(列名)....} FROM table_name [WHERE where_definition];
1
2
3
4
# 查询数学成绩的平均分
SELECT AVG(math) FROM student;
# 查询总分平均分
SELECT AVG(math + chinese + english) FROM student;
  1. MAX/MIN函数返回满足WHERE条件的一列的最大/最小值
1
SELECT MAX(列名) FROM table_name [WHERE where_definition];
1
2
# 查询总分最高和最低的学生信息
SELECT MAX(math + chinese + english), MIN(math + chinese + english) FROM student;

分组查询

  1. 使用GROUP BY子句对列进行分组
1
SELECT column1, column2, column3 .... FROM table_name GROUP BY COLUMN
  1. 使用HAVING子句对分组后的结果进行过滤
1
SELECT column1, column2, column3 ... FROM table_name GROUP BY column HAVING .....
1
2
3
4
5
6
7
# 查询每个部门的平均工资、最高工资和部门编号
SELECT AVG(salary), MAX(salary), dept_no FROM dept GROUP BY dept_no;
# 查询每个部门和职位的平均工资、最高工资和部门编号
# 先根据部门分组，再根据职位分组
SELECT AVG(salary), MAX(salary), dept_no, job FROM dept GROUP BY dept_no, job;
# 查询每个部门的平均工资大于5000的部门编号和最高工资
SELECT AVG(salary) AS avg_salary, MIN(salary), dept_no FROM dept GROUP BY dept_no HAVING avg_salary > 5000;

字符串相关函数

CHARSET(str) 返回字串字符集
CONCAT(str[, ….]) 链接字串
INSTR(string, substring) 返回substring在string中出现的位置，没有返回0
UCASE(string) 转换成大写
LCASE(string) 转换成小写
LEFT(string, length) 从string中的左边提取length个字符
RIGHT(string, length) 从string中的右边提取length个字符
LENGTH(string) 返回string长度（按照字节统计）
REPLACE(str, search_str, replace_str) 在str中用replace_str替换search_str
STRCMP(string1, string2) 逐字符比较两个字串大小
SUBSTRNG(str, position[, length]) 从str的position开始【从1开始计算】，取length个字符
LTRIM(string) RTRIM(string) TRIM(string) 去除前端或后端空格，去除前后空格 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# CHARSET(str)	返回字串字符集
SELECT CHARSET(name) FROM emp;
# CONCAT(str[, ....])	链接字串
SELECT CONCAT(name, '的工资是', salary) FROM emp;
# INSTR(string, substring)	返回substring在string中出现的位置，没有返回0
# dual是MySQL中的一个虚拟表，用于在不查询任何实际数据的情况下执行SQL语句。它可以用来测试表达式或计算值等操作
SELECT INSTR('aoshudingli', 'ding') FROM dual;
# UCASE(string)	转换成大写
SELECT UCASE(name) FROM emp;
# LCASE(string)	转换成小写
SELECT LCASE(name) FROM emp;
# LEFT(string, length)	从string中的左边提取length个字符
SELECT LEFT(name, 3) FROM emp;
# RIGHT(string, length)	从string中的右边提取length个字符
SELECT RIGHT(name, 3) FROM emp;
# LENGTH(string)	返回string长度（按照字节统计）
SELECT LENGTH(name) FROM emp;
# REPLACE(str, search_str, replace_str)	在str中用replace_str替换search_str
SELECT REPLACE(name, 'li', 'wa') FROM emp;
# STRCMP(string1, string2)	逐字符比较两个字串大小
SELECT STRCMP('abc', 'abd') FROM dual;
# SUBSTRNG(str, position[, length])	从str的position开始【从1开始计算】，取length个字符
SELECT SUBSTRING('aoshudingli', 3, 5) FROM dual;
# LTRIM(string)  RTRIM(string)   TRIM(string)	去除前端或后端空格，去除前后空格
SELECT LTRIM('   aoshudingli') FROM dual;
SELECT RTRIM('aoshudingli   ') FROM dual;
SELECT TRIM('   aoshudingli   ') FROM dual;

数学函数

ABS(num) 绝对值
BIN (decimal_number ) 十进制转二进制
CEILING (number2 ) 向上取整,得到比num2大的最小整数
CONV(number2,from_base,to_base) 进制转换
FLOOR (number2 ) 向下取整,得到比num2小的最大整数
FORMAT (number,decimal_places ) 保留小数位数
HEX (DecimalNumber ) 转十六进制
LEAST (number , number2 [, .. ]) 求最小值
MOD (numerator , denominator ) 求余
RAND([seed]) RAND([seed])其范围为0≤v≤1.0 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# ABS(num)	绝对值
SELECT ABS(-10) FROM DUAL;
# BIN (decimal_number )	十进制转二进制
SELECT BIN(10) FROM DUAL;
# CEILING (number2 )	向上取整,得到比num2大的最小整数
SELECT CEILING(10.5) FROM DUAL;
# CONV(number2,from_base,to_base)	进制转换
SELECT CONV(10, 10, 2) FROM DUAL;
# FLOOR (number2 )	向下取整,得到比num2小的最大整数
SELECT FLOOR(10.5) FROM DUAL;
# FORMAT (number,decimal_places )	保留小数位数
SELECT FORMAT(10.54564, 2) FROM DUAL;
# HEX (DecimalNumber )	转十六进制
SELECT HEX(10) FROM DUAL;
# LEAST (number , number2 [, .. ])	求最小值
SELECT LEAST(10, 20, 23, 43) FROM DUAL;
# MOD (numerator , denominator )	求余
SELECT MOD(10, 3) FROM DUAL;
# RAND([seed])	RAND([seed])其范围为0≤v≤1.0
SELECT RAND() FROM DUAL;
# RAND(seed)    RAND(seed)其范围为0≤v≤1.0, 如果send保持不变，则每次生成的随机数相同。
SELECT RAND(10) FROM DUAL;

时间函数

CURRENT_DATE ( ) 当前日期
CURRENT_TIME ( ) 当前时间
CURRENT_TIMESTAMP ( ) 当前时间戳
DATE (datetime ) 返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_valued_type ) 在date2中加上日期或时间
DATE_SUB (date2 , INTERVAL d_valued_type ) 在date2上减去一个时间
DATEDIFF (date1 ,date2 ) 两个口期差(结果是天)
TIMEDIFF(date1,date2) 两个时间差(多少小时多少分钟多少秒)
NOW ( ) 当前时间
YEAR Month
UNIX_TIMESTAMP() 返回1970-1-1到现在的秒数
FROM_UNIXTIME() 将FROM_UNIXTIME()返回的秒数转换为指定日期格式
  1. DATE ADD()中的 interval后面可以是 year minute second hour day 等
  2. DATE SUB()中的 interval后面可以是 year minute second hour day 等
  3. DATEDIFF(date1,date2)得到的是天数,而且是date1-date2的天数,因此可以取负数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
# CURRENT_DATE ( )	当前日期
SELECT CURRENT_DATE() FROM DUAL;
# CURRENT_TIME ( )	当前时间
SELECT CURRENT_TIME() FROM DUAL;
# CURRENT_TIMESTAMP ( )	当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL;
# DATE (datetime )	返回datetime的日期部分
SELECT DATE('2018-01-01 12:00:00') FROM DUAL;
# DATE_ADD (date2 , INTERVAL d_valued_type )	在date2中加上日期或时间
SELECT DATE_ADD('2018-01-01 12:00:00', INTERVAL 1 DAY) FROM DUAL;
# DATE_SUB (date2 , INTERVAL d_valued_type )	在date2上减去一个时间
SELECT DATE_SUB('2018-01-01 12:00:00', INTERVAL 1 DAY) FROM DUAL;
# DATEDIFF (date1 ,date2 )	两个口期差(结果是人)
SELECT DATEDIFF('2018-01-31', '2018-01-01') FROM DUAL;
# TIMEDIFF(date1,date2)	两个时间差(多少小时多少分钟多少秒)
SELECT TIMEDIFF('2018-01-31 12:00:00', '2018-01-01 12:00:00') FROM DUAL;
# NOW ( )	当前时间
SELECT NOW() FROM DUAL;
# YEAR|Month|DATE (datetime ) FROM_UNIXTIME()	年月日
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DATE(NOW()) FROM DUAL;
# UNIX_TIMESTAMP()	返回1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL;
# FROM_UNIXTIME()	将FROM_UNIXTIME()返回的秒数转换为指定日期格式
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d %H:%i:%s') FROM DUAL;

加密和系统函数

USER() 查询用户
DATABASE() 数据库名称
MD5(str) 为字符串算出一个 MD5 32的字符串,(用户密码)加密
PASSWORD(str) 从原文密码str计算并返回密码字符串,通常用于对mysql数据库的用户密码加密 
1
2
3
4
5
6
7
8
# MySQL 查询当前用户, 用户名@IP地址
SELECT USER() FROM DUAL;
# 查询当前数据库
SELECT DATABASE() FROM DUAL;
# 对密码加密
SELECT MD5('123456') FROM DUAL;
# 对密码加密（MySQL 5.7.6之前的版本）
SELECT PASSWORD('123456') FROM DUAL;

流程控制语句

IF(expr1,expr2,expr3) 如果expr1为True,则返叫expr2 杏则返回expr3
IFNULL(expr1,expr2) 如果expr1不为空NULL,则返叫expr1,则返回expr2
SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END; 如果expr1为TRUE , 则返叫expr2 , 如果expr2为TRUE , 返回expr4 , 否则返回expr5 
1
2
3
4
5
6
7
# IF(expr1,expr2,expr3) 如果expr1为True,则返叫expr2 杏则返回expr3
SELECT IF(1>2, 'yes', 'no') FROM DUAL;  # 返回'no'
# IFNULL(expr1,expr2)   如果expr1不为空NULL,则返叫expr1,则返回expr2
SELECT IFNULL(null, 'no') FROM DUAL;    # 返回'no'
SELECT IF(NULL IS NULL, 'yes', 'no') FROM DUAL;
# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END;   如果expr1为TRUE , 则返叫expr2 , 如果expr2为TRUE , 返回expr4 , 否则返回expr5
SELECT CASE WHEN 1>2 THEN 'yes' WHEN 2>1 THEN 'no' ELSE 'maybe' END FROM DUAL;  # 返回'no'

分页查询

1
2
3
4
# 表示从start + 1行开始取，取出rows行，start从0开始计算
SELECT * FROM table_name ..... limit start, rows
# 常用公式
SELECT * FROM table_name LIMIT 每页显示记录数 * (第几页 - 1), 每页显示记录数

多表查询

双表查询

1
SELECT * FROM table_name1, table_name WHERE where_difinition;

当多表进行查询时，默认会从第一张表中取出一行和第二张表的每一行进行组合，一共返回的记录数 = 第一张表行数 * 第二张表的行数，这种默认处理返回的结果称为笛卡尔积

1
2
3
# 显示雇员名，雇员工资以及所在部门的名字，并按部门进行降序排列
SELECT name, salary, dept_name FROM emp, dept 
        WHERE emp.dept_no = dept.dept_no ORDER BY emp.dept_no DESC;

自连接

概念：将同一张表当作两张表进行使用

1
2
3
# 显示公司员工名字和他的上级名字(员工表中包含了公司的所有员工和上级)
SELECT worker.name AS '职员名', boss.name AS '上级名' 
        FROM emp worker, emp boss WHERE worker.managerId = boss.id;

单行子查询

概念：一个select语句查询结果为单列单行，并把该返回结果作为另一个select语句的条件

1
2
3
4
5
6
# 显示和张三相同部门的所有员工
# 1.先获取张三的部门号
# 2.根据该部门号再查询员工表
SELECT * FROM emp WHERE dept_no = (
  SELECT dept_no FROM emp WHERE name = '张三'
);

多行子查询

  1. 概念：一个select语句查询结果为单列多行，并把该返回结果作为另一个select语句的条件
1
2
3
4
5
6
# 显示职位在10号部门中存在的其他部门的员工的姓名，工资, 职位
# 1.先获取10号部门中的所有职位（注意要去除重复的职位）
# 2.获取职位在10号部门中存在的其他部门员工
SELECT name, salary, job FROM emp WHERE job in (
  SELECT job FROM emp WHERE dept_no = 10
) AND dept_no != 10;
  1. ALL 关键字的使用
1
2
3
4
5
6
# 显示比10号部门中任意一个员工工资都要高的其他部门的员工的姓名、工资和部门号
SELECT name, salary, dept_no FROM dept 
      WHERE salary > ALL(SELECT salary FROM dept WHERE dempt_no = 10) AND dept_no != 10;
或者
SELECT name, salary, dept_no FROM dept
      WHERE salary > (SELECT MAX(salary) FROM dept WHERE dempt_no = 10) AND dept_no != 10;
  1. ANY 关键字的使用
1
2
3
4
5
6
# 显示比10号部门其中一个员工工资高的其他部门的员工的姓名、工资和部门号
SELECT name, salary, dept_no FROM dept 
      WHERE salary > ANY(SELECT salary FROM dept WHERE dempt_no = 10) AND dept_no != 10;
或者
SELECT name, salary, dept_no FROM dept
      WHERE salary > (SELECT MIN(salary) FROM dept WHERE dempt_no = 10) AND dept_no != 10;

子查询临时表

概念：将一个select语句的查询结果作为一张临时表，另一个select语句对该临时表进行查询

1
2
3
4
5
6
7
8
9
# 查询每个商品类型中价格最高的商品的商品编号，商品类型编号，名字，价格
# 1.先查询每个商品类型中的最高价格的商品的商品类型编号和价格，作为临时表
# 2.再查询商品表中商品类型、价格和临时表中商品类型、价格都相同的商品
SELECT goods_id, category_id, name, price FROM (
  SELECT category_id, MAX(price) AS max_price FROM shop GROUP BY category
) temp, shop WHERE temp.category_id = shop.category_id AND temp.max_price = shop.price;

# 查询每个部门工资高于本部门平均工资的人的资料
SELECT * FROM dept

多列子查询

概念：一个select语句查询结果为单行多列，并把该返回结果作为另一个select语句的条件

1
2
3
4
# 查询和张三在同一个部门并且他们职位也一样的其他员工
SELECT * FROM emp 
    WHERE (dept_no, job) = (SELECT dept_no, job FROM emp WHERE name = '张三')
    AND name != '张三';

复制表和表去重

  1. 复制表：
1
2
3
# 根据一张表的结构创建表
CREATE TABLE table_name1 LIKE table_name2;
INSERT INTO table_name1 SELECT * FROM table_name2;
  1. 自身表复制：
1
INSERT INTO table_name1 SELECT * FROM table_name1;
  1. 去除表中重复数据：
1
2
3
4
5
6
7
8
9
10
（1）根据原表结构创建一个临时表
CREATE TABLE my_temp LIKE table_name;
（2）将原表中的去重后的数据复制到临时表
INSERT INTO my_temp SELECT DISTINCT * FROM table_name;
（3）清除原表数据
DELETE FROM table_name;
（4）将临时表中的数据全部复制到原表中
INSERT INTO table_name SELECT * FROM my_temp;
（5）删除临时表
DROP TABLE my_temp;

合并查询

  1. UNION ALL 关键字：该关键字可以用来合并两个查询语句的结果，即取两个结果的并集，但是不会去除重复的结果
  2. UNION 关键字：效果与 UNION ALL 关键字相似，但是会去除重复的数据
1
2
3
select * from emp where sal > 5000
UNION ALL
select * from emp where emp_no = 10;

外连接

  1. 左外连接：查询table_name1表和table_name2中满足条件的数据以及table_name1中不满足条件的数据
1
SELECT * FROM table_name1 LEFT JOIN table_name2 ON where_definition;
  1. 右外连接：查询table_name1表和table_name2中满足条件的数据以及table_name2中不满足条件的数据
1
2
3
# 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT * FROM dept LEFT JOIN emp ON dept.dept_no = emp.dept_no;
SELECT * FROM emp RIGHT JOIN dept ON dept.dept_no = emp.dept_no;

删除数据

1
DELETE FROM table_name [WHERE where_definition];

使用细节：

  1. 如果不使用WHERE子句，将删除表中所有数据
  2. DELETE语句不能删除某一列的值（可使用UPDATE将该列的值设置为 null 或者 ‘’）
  3. 使用DELETE语句仅删除记录，不删除表本身。如要删除表，使用DROP TABLE table_name;
1
2
3
4
# 删除表中姓名为张三的数据
DROP FORM emp WHERE name = '张三';
# 删除表中所有数据
DROP FROM emp;

约束

主键

  1. 概念：用于唯一的标识表行的数据，当定义主键约束后，该列不能重复，在创建表时可以给列加上该约束
1
2
3
4
5
6
7
8
9
10
11
# 方式一
CREATE TABLE table_name (
  column_name column_type PRIMARY KEY,
  .....
);
# 方式二
CREATE TABLE table_name (
  column_name column_type,
  .....
  PRIMARY KEY(column_name, ...) # 此处如果是多个字段，则这多个字段组合成了复合主键
);
  1. 使用细节：
  • primary key标识的字段不能重复而且不能为NULL
  • 一张表最多只能有一个主键，但可以是复合主键
  • 主键的指定方式有两种
    • 直接在字段名后指定：字段名 字段类型 PRIMARY KEY
    • 在表定义最后写 PRIMARY KEY(列名)
  • 使用DESC 表名; 可以看到PRIMARY KEY的情况

UNIQUE

  1. 概念：该约束用于字段，即不允许字段内容重复，即值必须唯一
1
2
3
4
CREATE TABLE table_name (
  column_name column_type UNIQUE,
  .....
);
  1. 使用细节：
  • 如果没有指定 NOT NULL，则 UNIQUE 修饰的字段可以有多个NULL
  • 一张表可以有多个UNIQUE修饰的字段

外键

概念：用于定义主表和从表之间的关系，外键约束要定义在从表上，主表则必须具有主键约束或者UNIQUE约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为NULL

1
2
3
4
5
6
7
8
9
10
CREATE TABLE table_name1 (
  column_name1 PRIMARY KEY,
  .....
);

CREATE TABLE table_name2 (
  column_name2,
  .....
  FOREIGN KEY (本表字段名) REFERENCES 主表名(主键名或UNIQUE字段名)
)
1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE class (
  id INT PRIMARY KEY,
  `name` VARCHAR(32) NOT NULL DEFAULT ''
);

CREATE TABLE student (
  id INT PRIMARY KEY, -- 学生编号
  `name` VARCHAR(32) NOT NULL DEFAULT '',
  calss_id INT,
  -- 下面为外键约束定义
  FOREIGN KEY (class_id) REFERENCES class(id)
);
  1. 外键指向的表的字段，要求是PRIMARY KEY或者是UNIQUE
  2. 表的类型是innodb，这样的表才支持外键
  3. 外键字段的类型要和主键字段类型一致（长度可以不同）
  4. 外键字段的值，必须在主键字段中出现过或者为NULL（前提是外键字段允许为NULL）
  5. 一旦简历主外键的关系，数据就不能随意删除

CHECK

概念：用于强制行数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在10002000之间，如果不在10002000之间就会提示出错

1
2
3
4
CREATE TABLE table_name (
  column_name1 column_type CHECK (check条件),
  ......
);
1
2
3
4
5
6
7
8
CREATE TABLE customer (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL DEFAULT '未知',
  address VARCHAR(255),
  email VARCHAR(255) UNIQUE,
  sex CHAR(1) CHECK (sex IN ('男', '女')),
  card_id INT
);

自增长

概念：在表中，允许存储一个id列（整数），在添加数据时，该列从1开始自动增长

1
字段名 INT PRIMARY KEY auto_increment
  1. 一般来说自增长是和PRIMARY KEY配合使用
  2. 自增长也可以单独使用（但是需要配合一个UNIQUE）
  3. 自增长修饰的字段为整型
  4. 自增长默认从1开始，也可以通过 ALTER TABLE 表名 AUTO_INCREMENT = 新的开始值; 进行修改
  5. 添加数据时，如果给自增长字段指定有值，则以指定值为准，如果指定了自增长，一般来说，就按照自增长的规则添加数据

索引

概念：当表中数据量较大时，根据条件进行查询时，默认会全表扫描，此时查询数据极慢，创建索引后，MYSQL底层会创建类似于排序二叉树的形式的数据结构，此时查询速度会得到极大提升，但是索引结构会占用一定的空间，并且会影响增删改语句的执行效率

索引类型

  1. 唯一索引：主键自动的为主索引
  2. 普通索引：UNIQUE
  3. 主键索引：INDEX
1
2
3
4
5
6
7
8
CREATE TABLE t1 (
  id INT PRIMARY KEY, -- 主键，同时也是索引，称为主键索引
  name VARCHAR(32)
);
CREATE TABLE t2 (
  id INT UNIQUE, -- id 是唯一的，同时也是索引，称为唯一索引
  name VARCHAR(32)
);

创建索引

  1. 查询表是否有索引：
1
SHOW INDEXES FROM table_name;
  1. 添加索引：
1
2
3
4
5
6
# 添加唯一索引
CREATE UNIQUE INDEX index_name ON table_name(字段名);
# 添加普通索引
CREATE INDEX index_name ON table_name(字段名);
# 添加普通索引
ALTER TABLE table_name ADD INDEX index_name (字段名);

如果某列的值不会重复，则优先考虑使用唯一索引，因为该索引的效率高，否则使用普通索引

删除索引

  1. 删除索引：
1
DROP INDEX index_name ON table_name;
  1. 删除主键索引：
1
ALTER TABLE table_name DROP PRIMARY KEY;

事务

概念：事物用于保证数据的一致性，它由一组相关的DML语句组成，该组的DML语句要么全部成功，要么全部失败，当执行事务操作时，MYSQL会在表上加锁，防止其他用于修改表中数据

重要操作：

  • start transaction：开始一个事务
  • savepoint 保存点名：设置保存点
  • rollback to 保存点名：回退事务
  • rollback：回退全部事务
  • commit：提交事务，所有操作生效，不能回退
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE IF NOT EXISTS temp
(
    id INT PRIMARY KEY,
    name VARCHAR(255)
);
# 开启事务
START TRANSACTION;
# 设置保存点a
savepoint a;
# 插入数据
INSERT INTO TEMP VALUES (1, 'Tom');
# 设置保存点b
SAVEPOINT b;
# 插入数据
INSERT INTO TEMP VALUES (2, 'Jerry');
# 回滚到保存点b
ROLLBACK TO SAVEPOINT b;
# 回滚到保存点a
ROLLBACK TO SAVEPOINT a;
# 提交事务
COMMIT;
# 查询数据
SELECT * FROM TEMP;
  1. 如果不开始事务,默认情况下,dml操作是自动提交的,不能回滚
  2. 如果开始一个事务,你没有创建保存点.你可以执行rollback,默认就是回退到你事务开始的状态.
  3. 可以在这个事务中(还没有提交时),创建多个保存点
  4. 你可以在事务没有提交前,选择回退到哪个保存点.
  5. mysql的事务机制需要innodb的存储引擎才可以使用,myisam不好使

回退事务

概念：在介绍回退事务前,先介绍一下保存点(savepoint).保存点是事务中的点.用于取消部分事务,当结束事务时(commit),会自动的删除该事务所定义的所有保存点，当执行回退事务时,通过指定保存点可以回退到指定的点,这里我们作图说明

提交事务

概念：使用commit语句可以提交事务.当执行了commit语句子后,会确认事务的变化、结束事务、删除保存点、释放锁,数据生效。当使用commit语句结束事务子后,其它会话[其他连接]将可以查看到事务变化后的新数据[所有数据就正式生效.]

事务隔离级别

概念：多个连接开启各自事务操作数据中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性

问题：如果不考虑隔离性，可能不引发下列问题

  • 脏读：当一个事务读取到另一个事务尚未提交的操作（增加、删除、修改）时,产生脏读
  • 不可重复读：同一查询在同一事务中多次进行,由于其他提交事务所做的修改或删除,每次返回不同的结果集,此时发生不可重复读。
  • 幻读：同一查询在同一事务中多次进行,由于其他提交事务所做的插入操作,每次返回不同的结果集,此时发生幻读。
  1. 查看当前会话隔离级别的指令：SELECT @@transaction_isolation;
  2. 更改当前会话隔离级别的指令：SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别名;
  3. 查看系统隔离级别的指令：SELECT @@global.transaction_isolation;
  4. 设置系统隔离级别的指令：SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别名;
  5. 可串行化的隔离级别的客户端，在查询时，会判断是否有其他表正在操作该表，如果有则该客户端的查询操作将会被堵塞，并且它会有个最长等待时间，如果超过了该时间就会返回超时的错误

事务的特性（ACID）

  1. 原子性：事务是一个不可分隔的工作单位，事务中的操作要么都发生，要么都不发生
  2. 一致性：事务必须使数据库从一个一致性状态转变为另一个一致性状态
  3. 隔离性：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离
  4. 持久性：事务一旦提交，它对数据库的更改就是永久性的

存储引擎

  1. InnoDB：MySQL 5.5 之后的默认引擎，支持事务、行级锁、外键，MVCC也有，适合高并发的 OLTP 场景。数据按聚簇索引组织，主键查询贼快。
  2. MyISAM：老版本的默认引擎，不支持事务，只有表级锁，但读性能不错。适合写少读多、对一致性要求不高的场景，比如早年的一些报表系统。
  3. MEMORY：数据全放在内存里，速度快但 MySQL 重启数据就没了。一般拿来做临时表或者会话级缓存。
  4. Archive：专门存归档数据的，只支持 INSERT 和 SELECT，不支持索引，但压缩率高。日志归档、历史订单这种场景用得上。
  5. NDB：MySQL Cluster 用的引擎，支持分布式和高并发，数据自动分片，适合电信级别的大规模集群。