学习MySQL的笔记

最编程 2024-01-17 11:37:26

...

版本：mysql5.7.35

以下笔记中，均是在SQLyog中实现，在SQLyog中使用关键字时，会自动变成大写字母，但在命令行中是使用小写字母，在#中会单独写出命令行中的小写字母用法

连接MySQL

连接到MySQL指令
连接前需要开启 MySQL服务 net start mysql 停止服务：net stop mysql
格式：mysql -h 主机IP -P 端口 -u 用户名 -p密码
-p和密码之间没有空格
-p后没有输入密码，回车后会要求输入密码
没有写-h 默认是本机IP
没有写-P 默认是端口3306
详细的：mysql -h 127.0.0.1 -P 3306 -u root -p123456
简化的：mysql -u root -p123456

数据库基本指令

#select * from 查询表
SELECT * FROM users
#create database 创建数据库
#在创建数据库时，为了规避关键字，可以使用反引号`包住要创建的数据库名字`
CREATE DATABASE xsp_db01
#character set 设置基本字符集 utf8 实际上character set可以改成charset
CREATE DATABASE xsp_db02 CHARACTER SET utf8
#collate设置数据库校对规则 utf8_bin(区分大小写) utf8_general_ci(不区分大小写)
CREATE DATABASE xsp_db03 CHARACTER SET utf8 COLLATE utf8_bin
#select(查询) *(表示所有字段) from(从哪个表) t1(表名) where(从哪个字段) name(表内的字段/属性) tom(查询的name)
SELECT * FROM t1 WHERE NAME = 'tom'
#drop database 删除数据库
DROP DATABASE xsp_db01
#show databases 查看当前数据库服务器中所有数据库
SHOW DATABASES
#show create database 查看 xsp_db01 数据库的定义信息
SHOW CREATE DATABASE xsp_db01

备份恢复数据库/表

备份恢复数据库的指令只能在Dos命令行下执行

备份数据
mysqldump指令在Dos下执行，并且不能登入MySQL
备份数据库
格式： mysqldump -u root -p(密码) -B 数据库名(可多个) > 文件放置的位置及名字
mysqldump -u root -p -B xsp_db01 xsp_db02 > e:\\bak1.sql
备份数据表
mysqldump -u root -p(密码) 数据库名表名(可多个) > 文件放置的位置及名字
mysqldump -u root -p xsp_db01 t1 t2 > e:\\bak2.sql
备份的文件内实际是一条条的指令，因此可以将指令粘贴到SQLyog的查询编辑器运行进行恢复
恢复数据库
source指令需要登入mysql执行
格式：source 备份文件路径
source e:\\bak1.sql
另一备份方式：将备份的文件内所有内容粘贴到 SQLyog的查询编辑器(命令行)，全部执行
实践发现，通过Dos将恢复表无法成功恢复，使用SQLyog的查询编辑器运行才成功恢复

创建表

#由于某些名字是关键词，因此用``包裹
#create table 创建表user
#属性/字段 id 类型是int
#属性name，类型是varchar，长度限制是255
#属性password，类型是varchar，长度限制是255
#属性birthday，类型是date
#character set 设置字符为utf8  
#collate 设置数据库校对规则
#engine 设置引擎
#haracter set、collate、engine没有设置则默认为数据库设置的
#数据库如果没设置，则有默认值 utf8、utf8_general_ci、innodb
CREATE TABLE `user`(
	id INT,
	`name` VARCHAR(255),
	`password` VARCHAR(255),
	`birthday` DATE)
	CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

列类型(数据类型)

MySQL笔记_数据

使用规范：在能够满足需求的情况下，尽量使用占用空间小的类型

常用( [] 内是字节/字符数)

数值类型
整型
bit[M] M:1-64
tinyint[1]
smallint[2]
mediumint[3]
int[4]
bigint[8]
小数类型
float[4] 单精度
double[8] 双精度
decimal[M,D] 大小可自定
文本类型(字符串类型)
char [字符数0-255]
varchar[0-65535]
text[0->2^16-1]
longtext[0->2^31-1]
二进制数据类型
blob[0->2^16-1]
longblob[0->2^32-1]
日期类型
date[年月日]
time[时分秒]
datetime[年月日时分秒 YYYY-MM-DD HH:mm:ss]
timestamp[时间戳]
year[年]

bit[M]

有参数M，范围：1-64
bit[1]占1个位，即 0/1 如果一个值只有0/1，可以考虑用bit[1]节约空间
是二进制存储显示的，可用十进制数查询
数据范围按照给的字节数决定，bit[8]范围是0-255

decimal[M.D]

参数M：总位数
省略时，默认是10
最大：65
参数D：小数位数(小数点后的)
省略时，默认是0
最大：30
当数据不足小数位数时，会自动补0

varchar[size]

size 是可变的 0-65535字节
实际最大字节是 65532 ，有1-3个编码用于记录大小，超过65532系统会提示超过65535字节
存储字符时，按照编码字符的大小存储
utf8编码1个字符占3个字节，因此最大可以存储 (65535-3)/3 个字符 = 21844个字符
create tanble t1(name varchar(24844)) charset utf8_bin;
gbk编码1个字符占2个字节，因此最大可以存储 (65535-3)/2 个字符 = 32766
create tanble t2(name varchar(32766)) charset gbk);

字符串使用细节

char(M)和varchr(M)的4表示字符数，表示能放M个字符
char(M)是定长，无论存入多少个字符(M以内)，都会占用M个字符的空间
char(4)，存入 'aa' 2个字符，占用的是4个字符空间
varchar(M)是变长，存入M以内的字符，按照实际的字符空间存储
varchar(4)，存入 'aa' 2个字符，占用的是2个字符空间
注意：varchar本身需占用1-3个字节用来记录存放内容的长度
因此最终的数据大小是：实际存入字节大小 + (1->3)个字节
某些固定的长度可用char 身份证、手机号、md5加密(加密完都是32位) 等【速度会比varchar快】
不固定的长度用varchar
存放文本时，可用text、mediumtext、longtext，没有默认值

unsigned

意思是：无符号
unsigned 属性只针对整型
unsigned 为非负数，用此类型可以增加数据长度
tinyint最大是127，那 tinyint unsigned 最大就可以到 127 * 2
使用时加在整形后面即可
不要定义unsigned float和unsigned double类型

日期

CREATE TABLE t14(birthday DATE, -- 生日
		job_time DATETIME, -- 记录年月日 时分秒
		login_time TIMESTAMP -- 时间戳
		-- not null default current_timestamp on update current_timestamp
		-- 使得时间戳自动更新，并且显示年月日时分秒
		NOT NULL DEFAULT CURRENT_TIMESTAMP 
		ON UPDATE CURRENT_TIMESTAMP);

-- 设置t14表的birthday和job_time，login_time会自动更新进去，并且当某条记录被更新，其时间戳也会更新(insert和update )
INSERT INTO t14(birthday,job_time) VALUES ('2022-11-11','2022-11-11 10:10:10')
    
-- select * from 查看表 
SELECT * FROM t14

-- drop table 删除表
DROP TABLE t14

修改表

-- emp表添加列image，varchar是10，在resume列后面
ALTER TABLE emp ADD `image` VARCHAR(10) AFTER `resume`
-- emp表修改列job，varchar是60，不能是空，默认值为 '' (空的字符)
ALTER TABLE emp MODIFY `job` VARCHAR(60) NOT NULL DEFAULT ''
-- emp表删除列sex
ALTER TABLE emp DROP `sex`
-- emp表修改名字为employee
RENAME TABLE `emp` TO `employee`
-- employee表修改字符集为utf8
ALTER TABLE employee CHARSET utf8
-- 修改列name名字为user_name 
-- varchar为32个字符
-- 字符集为utf8
-- 不能是空 默认值是 ''
ALTER TABLE `employee` 
	CHANGE `name` `user_name` 
	VARCHAR(32) 
	CHARSET utf8 
	NO NULL DEFAULT ''
-- 查看emp表 在emp改名后需要修改
SELECT * FROM emp
-- 查看emp表的列结构，可以看到所有列
DESC emp

insert

插入方式

-- 创建表goods
CREATE TABLE `goods` ( id INT ,
			goods_name VARCHAR(10), 
			price DOUBLE NOT NULL DEFAULT 100 );
-- 添加数据
-- 方式一
INSERT INTO `goods` (id, goods_name, price) VALUES (01, '华为手机', 2000);
-- 方式二
INSERT INTO `goods` VALUES(02, '苹果手机', 3000);
-- 方式三 (这里的price因为有默认值，可以不设置，不设置的情况下会给默认值100)
INSERT INTO `goods` (id, goods_name) VALUES (03, '联想手机');
SELECT * FROM goods;

细节

插入的数据应和字段的数据类型相同
- 'abc'插入int类型会报错
- '123'插入int类型是可以的，系统实际会尝试将字符串转化成int
数据的长度应在列的规定范围内 (字符数/字节数)
values后的数据位置应与列的排序位置一致
日期和字符串类型数据在单引号中
列可以插入空值(允许为空时)，一般是允许的，除非使用 not null
可以一次添加多条记录
- insert into t1 values (1,"a"),(2,"b"),(3,"c");
如果给所有字段添加数据，values前可不列出字段名称
当不给某个字段值时，其若有设置默认值则会添加，若没有则报错

update

使用方式

-- 演示 update 语句
-- 1. 将所有员工薪水修改为 5000 元。[如果没有带 where 条件，会修改所有的记录，因此要小心] 
-- 	UPDATE employee SET salary = 5000
-- 2. 将姓名为 小妖怪 的员工薪水修改为 3000 元。

-- 会将所有的salary设置为5000
UPDATE employee SET salary = 5000
-- 将 user_name 为 小妖怪 的 salary 设置为 3000
UPDATE employee SET salary = 3000
	WHERE user_name = '小妖怪'
-- 将 妖怪 的薪水在原有基础上增加 1000 元
UPDATE employee
	SET salary = salary + 1000 WHERE user_name = '小妖怪'
-- 修改小妖怪的多个列的值
UPDATE employee
	SET salary = salary + 1000 , job = '出主意的' 
	WHERE user_name = '小妖怪'
	
SELECT * FROM employee;

细节

upadte可以用新值更新某列/某列某行的值
set后跟的是要修改的值
where后跟的是与要修改值相关的行的某个值
- where与set形成交叉，能确定修改某个位置的值
- 一般where跟着的是名字、id等独一无二的
修改多个字段
- set salary = 1000 , job = '工作'

delete

使用

-- 删除表中所有记录，小心使用！！！
DELETE FROM employee;
-- 删除表中名称为’老妖怪’的记录。
DELETE FROM employee WHERE user_name = '老妖怪';
-- 删除表
DROP TABLE employee;

细节

如果不适用where子句，将删除表中所有数据
delete不能删除某列,但alter可以
- alter table t(表名) drop sex 删除t表的sex列
delete仅删除记录，不删除表本身
- 即：delete from employee;后，其内数据都没有了，但是表依旧存在
- 删除表本身可用 drop table 表名

select

基础使用

-- 查询 student表的 name 和 english 列
SELECT `name`,`english` FROM student
-- distinct 过滤重复项
-- 查询 english 列 , 过滤掉重复项
SELECT DISTINCT `english` FROM student 
-- 查询 name列 和 (chinese+english+math)列
SELECT `name`,(chinese+english+math) FROM student
-- as 命名一个别名，后续的操作可以使用别名操作
-- 查询name列 和 (chinese+english+math)列，给 (chinese+english+math)列一个别名total
SELECT `name`,(chinese+english+math) AS total FROM student
-- where 进行条件过滤 
-- and 并且
-- 查询 math列 ，过滤条件为：math列 大于 60 的项，并且 id 大于1
SELECT `math` FROM student WHERE math > 60 AND id > 1
-- 查询 name列，过滤条件为：english列 大于 chinese列 的项
SELECT `name` FROM student WHERE english > chinese
-- like 模糊查询 ，如果需要查询姓氏的话，可以在姓后面加%，表示第一个字是姓氏，后面的不知道
-- 查询 所有列，过滤条件为：(chinese+english+math)列 大于 200，并且 math列 小于 chinese列 ，并且 name列 模糊查询 赵%
SELECT * FROM student WHERE (chinese+english+math)> 200 AND math<chinese AND `name` LIKE '赵%'

#注意：where子句不能使用函数

练习

SELECT * FROM student-- 英语成绩在80-90的同学SELECT `name` FROM student WHERE english >= 80 AND english <= 90-- 数学分数为84,85,86的同学SELECT `name` FROM student WHERE english IN(84,85,86)-- 所有姓 韩 的学生成绩SELECT * FROM student WHERE `name` LIKE '韩%'-- 数学>80，语文>80的同学SELECT `name` FROM student WHERE math >= 80 AND chinese >= 80-- 语文在70-80的同学SELECT `name` FROM student WHERE chinese BETWEEN 70 AND 80 -- 总分为170,221,233的同学SELECT `name` FROM student WHERE (chinese + english + math) IN(170,221,233)-- 姓赵或姓宋的同学SELECT * FROM student WHERE `name` LIKE '赵%' OR `name` LIKE '宋%'-- 数学比语文多30分的同学SELECT `name` FROM student WHERE (math-chinese) > 30

where的运算符

比较运算符
- ` > < <= >= = <> !=
- between...and...
- in(set) set是一个集合
  - eg: `number` in(1,2,3,4)
- like ` 蔡 ` 模糊查询有蔡关键字的
  - eg: `name` like '蔡%' 加%表示后面的不知道，但是第一个字是蔡
  - 注意：like后面的字符串是加单引号，不是反引号
- like ` 蔡 ` no like ` 王 ` 模糊查询有蔡关键字，没有王关键字的
逻辑运算符
- and
- or
- not

order by ... (asc/desc)

-- order by-- 默认是 asc 升序(从低到高) -- 按math列升序排序SELECT * FROM student ORDER BY math;-- desc是降序(从高到低)-- 按chiese列降序排序SELECT * FROM student ORDER BY chinese DESC;-- 按 total列 降序排序，注意：这里的total是别名SELECT `name`,(chinese + math + english) AS total FROM student ORDER BY total DESC;-- 筛选出 姓韩 的同学，按 total列 升序排序SELECT `name`,(chinese + math + english) AS total FROM student WHERE `name` LIKE '韩%' ORDER BY total ASC;

函数

count

count(*)
- 返回满足条件记录的行数
count(列名)
- 返回满足条件某列的行数，会排除null的行

-- 统计全部有多少学生SELECT COUNT(*) FROM student-- 统计 math > 90 的 有多少学生SELECT COUNT(*) FROM student WHERE math > 90-- 统计 总分 > 90 的 有多少学生SELECT COUNT(*) FROM student WHERE (chinese + english + math) > 250

sum

sum(列名) 返回某列数值的和
只对数值有用
可以对多列同时求和，逗号隔开即可

-- 统计math总和SELECT SUM(math) FROM student-- 统计各科成绩综合SELECT SUM(chinese),SUM(english),SUM(math) FROM student -- 统计全部成绩总和SELECT SUM(chinese + english + math) FROM student-- 统计语文平均分SELECT SUM(chinese)/COUNT(chinese) FROM student

avg

-- 语文平均分SELECT AVG(chinese) FROM student-- 总分平均分SELECT AVG(chinese + english + math) FROM student

max/min

-- 语文分数最大值，最小值SELECT MAX(chinese),MIN(chinese) FROM student-- 总分最大值SELECT MAX(chinese + english + math) FROM student

group by

group by的作用是分组，但实际就是排序，需要配合函数使用
order by 也是排序
区别：
- order by
  - 后面必须列出排序的字段名，可以是多个字段名
- group by
  - 分组必须有"聚合函数"来配合才能使用，使用时至少需要一个分组标志字段
  - 有having子句，用来过滤分组的结果
  - 聚合函数：sum、count、avg等
扩展
- where子句和group by可以一起用，但是where必须在group by前
- 结合用法：
  先对select xx from xx的记录集合用where进行筛选，然后再使用group by 对筛选后的结果进行分组使用having字句对分组后的结果进行筛选
- 注意：where后的条件表达式里不能用聚合函数，但having可以

练习前需要创建的表

CREATE TABLE dept( /*部门表*/deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, dname VARCHAR(20)  NOT NULL  DEFAULT "",loc VARCHAR(13) NOT NULL DEFAULT "");INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');#创建表EMP雇员CREATE TABLE emp(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/mgr MEDIUMINT UNSIGNED ,/*上级编号*/hiredate DATE NOT NULL,/*入职时间*/sal DECIMAL(7,2)  NOT NULL,/*薪水*/comm DECIMAL(7,2) ,/*红利*/deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/); INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), (7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  (7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  (7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  (7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  (7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  (7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  (7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  (7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  (7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  (7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  (7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  (7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);#工资级别表CREATE TABLE salgrade(grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,losal DECIMAL(17,2)  NOT NULL,hisal DECIMAL(17,2)  NOT NULL);INSERT INTO salgrade VALUES (1,700,1200);INSERT INTO salgrade VALUES (2,1201,1400);INSERT INTO salgrade VALUES (3,1401,2000);INSERT INTO salgrade VALUES (4,2001,3000);INSERT INTO salgrade VALUES (5,3001,9999);

使用

-- 未分组：所有 平均工资和最高工资SELECT AVG(sal),MAX(sal) FROM emp-- 按照部门分组 计算平均工资和最高工资，可降序升序排列SELECT AVG(sal),MAX(sal),deptno FROM emp GROUP BY deptno DESC;-- 按照部门和岗位分组，计算 平均工资和最低工资SELECT AVG(sal),MIN(sal),deptno,job FROM emp GROUP BY deptno DESC,job DESC;-- having 用来过滤group by的结果-- 显示平均工资低于2000的部门号和它的平均工资SELECT deptno,AVG(sal) AS `avg` FROM emp GROUP BY deptno HAVING `avg` < 2000

字符串函数

# 所有的FROM DUAL(from dual)可以不加-- 演示字符串相关函数的使用  ， 使用emp表来演示-- charset(str)	返回字串字符集SELECT CHARSET(ename) FROM emp;-- concat(string[,... ])连接字串, 将多个列拼接成一列SELECT CONCAT(ename,' 工作是 ', job) FROM emp;-- instr (string ,substring )返回substring在string中出现的位置,没有返回0-- dual 亚元表, 系统表 可以作为测试表使用SELECT INSTR('hanshunping', 'ping') FROM DUAL; -- ucase(string)转换成大写SELECT UCASE(ename) FROM emp;-- lcase (string)转换成小写SELECT LCASE(ename) FROM emp;-- left (string,length )从string中的左边起取length个字符-- right (string,length )从string中的右边起取length个字符SELECT LEFT(ename, 2) FROM emp;-- length(string)返回string长度[按照字节]SELECT LENGTH(ename) FROM emp;-- replace(str,search_str,replace_str) 	-- 将str中的search_str替换成replace_str-- job列中如果是MANAGER就替换成 经理SELECT ename, REPLACE(job,'MANAGER', '经理') FROM emp;-- strcmp(string1,string2)逐字符比较两字串大小-- string1>string2返回正数；string1<string2返回负数；string1=string2返回0SELECT STRCMP('wiopp', 'hspp') FROM DUAL;-- substring (str , position  [,length ])	-- 从str的position开始【从1开始计算】,取length个字符-- 从ename 列的第一个位置开始取出2个字符SELECT SUBSTRING(ename, 1, 2) FROM emp;-- ltrim (string2 ) 去除前端空格-- rtrim (string2 ) 去除后端空格-- trim(string) 去除前后端空格SELECT LTRIM('  韩顺平教育') FROM DUAL;SELECT RTRIM('韩顺平教育   ') FROM DUAL;SELECT TRIM('    韩顺平教育   ') FROM DUAL;-- 练习: 以首字母小写的方式显示所有员工emp表的姓名-- 思路：取出ename第一个字符，转成小写-- 	 把他和后面的字符串进行拼接输出-- 方式一：SELECT CONCAT(LCASE(SUBSTRING(ename,1,1)),  SUBSTRING(ename,2)) AS new_name	FROM emp;  -- 方式二：SELECT CONCAT(LCASE(LEFT(ename,1)),  SUBSTRING(ename,2)) AS new_name	FROM emp;

数学函数

# 所有的FROM DUAL(from dual)可以不加-- 演示数学相关函数-- abs(num)绝对值SELECT ABS(-10) FROM DUAL;-- bin (num)十进制转二进制SELECT BIN(10) FROM DUAL;-- celling(num)	向上取整, 得到比num大的最小整数SELECT CEILING(-1.1) FROM DUAL; -- -1-- floor (num)	向下取整,得到比num小的最大整数SELECT FLOOR(-1.1) FROM DUAL; -- -2-- conv(num,from_base,to_base)	进制转换--  8是16进制, 转成2进制输出SELECT CONV(8,16, 2) FROM DUAL;--  11是16进制, 转成10进制输出SELECT CONV(11, 16, 10) FROM DUAL;-- format (number,decimals)保留小数位数(四舍五入)-- 78.125458保留2位小数SELECT FORMAT(78.125458,2) FROM DUAL;-- hex (DecimalNumber)	转十六进制SELECT HEX(11);-- least (number , number2  [,..])求 括号内的最小值SELECT LEAST(0,1, -10, 4) FROM DUAL;-- mod (num1,num2)求余 num1%num2SELECT MOD(10, 3) FROM DUAL;-- rand([seed])返回随机数 其范围为 0 ≤ v ≤ 1.0 seed是种子值-- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0SELECT RAND() FROM DUAL;-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0--    如果seed不变，该随机数也不变了;--    即：如果后续使用相同的seed，会按顺序产生同样的随机数SELECT empno,RAND(1) FROM emp;SELECT job,RAND(1) FROM emp;SELECT * FROM emp

日期函数

# 所有的FROM DUAL(from dual)可以不加-- 日期时间相关函数-- current_date (  )	当前日期SELECT CURRENT_DATE() FROM DUAL;-- current_time (  )	当前时间SELECT CURRENT_TIME()  FROM DUAL;-- current_timestamp () 当前时间戳SELECT CURRENT_TIMESTAMP()  FROM DUAL;-- 返回当前日期和时间SELECT NOW() FROM DUAL;-- 创建测试表 信息表CREATE TABLE mes(	id INT , 	content VARCHAR(30), 	send_time DATETIME);		-- 添加记录INSERT INTO mes VALUES(1, '北京新闻', CURRENT_TIMESTAMP()); INSERT INTO mes VALUES(2, '上海新闻', NOW());INSERT INTO mes VALUES(3, '广州新闻', NOW());-- 查表SELECT * FROM mes;-- 应用实例-- 显示所有新闻信息，发布日期只显示 日期，不用显示时间-- date返回datetime的日期部分，最终只显示日期，不显示时间SELECT id, content, DATE(send_time) FROM mes;-- 请查询在10分钟内发布的新闻-- 思路一：如果 发布时间加上10分钟 大于等于 当前时间，说明是10分钟内的-- date_add(date,interval value) 在date中加上value【日期/时间】-- interval value 后的值可以是 year、minute、second、day等SELECT * FROM mes WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) >= NOW();-- 思路二：如果 当前时间减去10分钟 小于等于 发布时间，说明是10分钟内的-- date_sub(date,interval value) 在date中减去value【日期/时间】SELECT * FROM mes WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE) -- 求出 2011-11-11 和 1990-1-1 相差多少天-- datediff(data1,data2) 返回 两个日期的差SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;-- 求出活了多少天? 1986-11-11 出生SELECT DATEDIFF(NOW(), '1986-11-11') FROM DUAL;-- 如果活80岁，求还能活多少天 1986-11-11 出生-- 1.活80岁时,是什么日期x-- 2.使用datediff(x, now()); 用未来80岁的日期减去现在的日期-- interval value 后的值可以是 year、minute、second、day等-- '1986-11-11'是date(年月日) 也可以是datetime(年月日时分秒)、timestamp(时间戳)SELECT DATEDIFF(	DATE_ADD('1986-11-11', INTERVAL 80 YEAR), -- 出生日期+80年=80年后的日期	NOW() -- 现在的时间	) FROM DUAL; -- datediff 80年后的日期-现在的日期=剩下的天数-- 计算两个时间的差，返回的是一个时间(时分秒)SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL;SELECT YEAR(NOW()) FROM DUAL;-- 现在的年SELECT MONTH(NOW()) FROM DUAL;-- 现在的月SELECT DAY(NOW()) FROM DUAL;-- 现在的日SELECT MONTH('2013-11-10') FROM DUAL;-- 这个时间的月-- unix_timestamp()返回1970-1-1到现在的秒数SELECT UNIX_TIMESTAMP() FROM DUAL;-- from_unixtime(时间戳,格式)可以把一个unix_timestamp秒数[时间戳]，转成指定格式的日期-- %Y-%m-%d 表示年月日 %H:%i:%s 表示时分秒-- 意义：在开发中，可以存放一个整数用来表示时间，通过FROM_UNIXTIME转换 SELECT FROM_UNIXTIME(1618483484, '%Y-%m-%d') FROM DUAL;SELECT FROM_UNIXTIME(1618483100, '%Y-%m-%d %H:%i:%s') FROM DUAL;

加密函数

#所有的select语句没有from的，后面可以加FROM DUAL【from dual】-- 演示加密函数和系统函数-- user()查看当前登录用户以及登录的IPSELECT USER() ; -- 用户@IP地址-- DATABASE()	查询当前使用数据库名称SELECT DATABASE();-- MD5(str)为字符串算出一个 MD5 32的字符串，常用(用户密码)加密-- root 密码是 hsp -> 加密md5 -> 在数据库中存放的是加密后的密码SELECT MD5('hsp');-- length计算长度，md5加密后都是32位SELECT LENGTH(MD5('hsp'));DROP TABLE xsp_user-- 演示用户表CREATE TABLE xsp_user	(id INT , 	`name` VARCHAR(32) NOT NULL DEFAULT '', 	pwd CHAR(32) NOT NULL DEFAULT '');-- 插入一行，pwd用md5加密INSERT INTO xsp_user 	VALUES(1, '小蛇皮', MD5('xsp'));-- 查表	SELECT * FROM xsp_user;-- 查询pwd时，依旧需要通过md5加密的方式查找 能防止SQL注入问题SELECT * FROM xsp_user  	WHERE `name`='小蛇皮' AND pwd = MD5('xsp'); -- password(str)加密函数, MySQL数据库的用户密码就是使用 password函数 进行加密SELECT PASSWORD('123456'); -- 123456加密后是：*DB7F29433326688718E5FAD1F935ED0FC9DFCAAF-- select * from mysql.user \G-- password函数通常用于对mysql数据库的用户密码加密-- mysql.user 表示 mysql数据库的user表，这里存放了用户权限和密码等-- authentication_string列 的root行显示的是123456经过password函数加密后的值-- 这就是我们自己设置的数据库密码123456加密后的值SELECT * FROM mysql.user

流程控制语句

# 演示流程控制语句# if(expr1,expr2,expr3)	如果expr1为True ,则返回expr2，否则返回 expr3SELECT IF(TRUE, '北京', '上海');# ifnull(expr1,expr2)	如果expr1不为空NULL,则返回expr1,否则返回expr2SELECT IFNULL(NULL,'韩顺平教育');# 类似if...if else..else...# select case when ... then ... when ... then... else ... end;# when...then可以有多个，但else...只能有一个，不管有没有else，都必须有 end 表示结束-- 1.如果expr1为true,返回expr2,结束-- 2.如果expr3为true,返回expr4,结束-- 3.如果前面的条件都不成立，返回expr5-- SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END;SELECT CASE 	WHEN TRUE THEN 'jack'  -- jack	WHEN FALSE THEN 'tom' 	ELSE 'mary' END-- 查询emp 表, 如果 comm 是null , 则显示0.0-- 判断是否为null要使用 is null, 判断不为空 使用 is not null-- 如果comm是空，则返回0.0，不为空则返回comm(即原值)SELECT ename, IF(comm IS NULL, 0.0, comm) FROM emp;-- 如果comm不是空，则返回comm(即原值)，comm是空则返回0.0SELECT ename, IFNULL(comm, 0.0) FROM emp;-- 如果emp 表的 job 是 CLERK 则显示 职员， 如果是 MANAGER 则显示经理-- 如果是 SALESMAN 则显示 销售人员，其它正常显示、-- 方式一：-- 注意：else后要写job，否则返回的是空，因为这里显示的列实际是新的一列，不是原来的job列，没有赋值就是nullSELECT ename, (SELECT CASE 		WHEN job = 'CLERK' THEN '职员' 		WHEN job = 'MANAGER' THEN '经理'		WHEN job = 'SALESMAN' THEN '销售人员' 		ELSE job END) AS 'job' FROM emp; -- 方式二：SELECT ename, (CASE job WHEN 'CLERK' THEN '职员' WHEN 'MANAGER' THEN '经理' 		WHEN 'SALESMAN' THEN '销售人员' ELSE job END) AS job FROM emp;SELECT * FROM emp;SELECT * FROM dept;SELECT * FROM salgrade;

分页查询

-- 分页查询-- limit start,rows  (start是开始取的行，rows是要取多少行)-- 按雇员的empno号升序取出， 每页显示3条记录，请分别显示 第1页，第2页，第3页-- 第1页SELECT * FROM emp 	ORDER BY empno 	LIMIT 0, 3;-- 第2页SELECT * FROM emp 	ORDER BY empno 	LIMIT 3, 3;-- 第3页SELECT * FROM emp 	ORDER BY empno 	LIMIT 6, 3;-- 推导一个公式 SELECT * FROM emp	ORDER BY empno 	LIMIT 每页显示记录数 * (第几页-1) , 每页显示记录数

执行顺序

以下按顺序执行，他们出现的位置不能提前或提后
- group by
- having
- order by
- limit
select * from ... group by ... having ... order by ... limit ...
只有group by和order by可以升序asc降序desc

-- 统计各部门的平均工资，并且是大于1000的，按照平均工资降序排序，取出前两行记录SELECT deptno,AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING avg_sal>1000 ORDER BY avg_sal DESC LIMIT 0,2

多表查询和笛卡尔集

笛卡尔集

一般直接查询两张表时，会出现笛卡尔集
- select * from emp,dept;
笛卡尔集
- 将表1的行1 跟表2的所有行拼接，形成多个行；然后表1的行2 继续拼接...
- 最终的行数是表1行数 * 表二行数
- 返回的结果包含了两张表的所有列
- 解决笛卡尔集的办法就是用where子句增加过滤条件
- 过滤条件最少是 （表数-1） 即：有5个表时，最少得有4个过滤条件才不会出现笛卡尔集
- 过滤条件实际是对笛卡尔集的过滤，没有过滤就是笛卡尔集
- 笛卡尔集出现在多表查询下，要注意两个表的共同列名，要用 表名.列名 表示各表的列

多表查询

多表查询时，如果两个表有共同的列，一般会通过表1.列名 = 表2.列名作为筛选条件之一
当有临时表时，临时表如果使用了group by进行分组，分组的列一般是两表共同列，这个列也可作为筛选条件之一

-- 显示雇员名ename,雇员工资sal,所在部门名deptno/*ename、sal在emp表 dname在dept表deptno在两个表都有，可以设置过滤条件，注意使用不同的表名调用deptno列由于deptno在两个表都有，因此在查看列时需要当我们需要指定显示某个表的列时，用： 表名.列名*/SELECT ename,sal,dname,emp.deptno 	FROM emp, dept 	WHERE emp.deptno = dept.deptno	-- 多表查询的条件不能少于表的个数减1, 否则会出现笛卡尔集-- 显示部门号为10的部门名、员工名和工资 SELECT ename,sal,dname,emp.deptno	FROM emp, dept 	WHERE emp.deptno = dept.deptno AND emp.deptno = 10-- 显示各个员工的姓名，工资，及其工资的级别SELECT ename, sal, grade 	FROM emp , salgrade	WHERE sal BETWEEN losal AND hisal; 		-- 显示雇员名、雇员工资、所在部门名字、按部门排序-- 这里无法使用group by，group by需要配合聚合函数使用SELECT ename,sal,dname FROM emp,dept WHERE emp.deptno = dept.deptno ORDER BY dnameSELECT * FROM emp;SELECT * FROM dept;SELECT * FROM salgrade;

自连接

-- 多表查询的 自连接-- 思考题: 显示公司员工名字和他的上级名字-- 这里表中，公司成员有对应的编号，每个成员的上级也是用编号表示的,此表是mgr列-- 员工编号和上级编号在同一个表，可以使用自连接/*自连接的特点  1. 把同一张表当做两张表使用               2. 必须给表取别名，否则系统无法区分，后续也无法调用不同表的列   			   3. 列名不明确，可以指定列的别名 列名 as 列的别名*/SELECT worker.ename AS '职员名' ,  boss.ename AS '上级名' -- 给不同表的ename取别名，方便区分	FROM emp worker, emp boss -- 给表取别名，不需要用as	WHERE worker.mgr = boss.empno; -- 条件：员工表的上级编号 = 上级表的员工编号时SELECT * FROM emp;

多行子查询

#子查询指select在其它sql语句中使用，实际就是select语句的嵌套#多行子查询指 select语句返回的是多行的#单行子查询指 select语句返回的是单行的#多行其实是单列的-- 单行子查询#如何显示与SMITH同一部门的所有员工-- 1. 先查询到 SMITH的部门号得到-- 2. 把上面的select 语句当做一个子查询来使用-- 1.SELECT deptno FROM emp WHERE ename = 'SMITH' -- 20-- 2.SELECT * FROM emp	 	WHERE deptno = (		SELECT deptno 		FROM emp 		WHERE ename = 'SMITH' 	 ) -- 这里实际是 deptno=20-- 多行子查询#查询跟10号部门的工作相同的雇员的 名字、岗位、工资、部门号, 但不含10号部门的成员.-- 思路：先查出部门10有哪些工作 再找出对应的雇员 再排除10号部门的成员SELECT DISTINCT job FROM emp WHERE deptno=10 -- 会返回三个(行)不同的job-- in是运算符，in(set),set是集合，上面语句返回三个job，实际就是返回了一个集合-- 因此这里，只要job包含了 返回的三个job其中一个 条件则成立    <>是不等于号，也可用 != SELECT ename , job , sal , deptno FROM emp WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno=10) AND deptno<>10SELECT * FROM emp

all和any

all和any不算是运算符，in是运算符
any和in很相似
但在进行比较时，使用>和<等，已经用了运算符，不能使用in，而是使用any

#all 和 any的使用#显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号-- 方法一： 使用all，all的意思是 sal需要跟括号内所有项进行比较，都成立才返回trueSELECT ename, sal, deptno	FROM emp	WHERE sal > ALL(		SELECT sal 			FROM emp			WHERE deptno = 30		) -- 方法二：使用max，直接获得最大值SELECT ename, sal, deptno	FROM emp	WHERE sal > (		SELECT MAX(sal) 			FROM emp			WHERE deptno = 30		) #显示工资比部门30的 其中一个员工 的工资高的员工的姓名、工资和部门号-- 方法一： 使用any，any的意思是 sal只需要比括号内任意一项大即可返回trueSELECT ename, sal, deptno	FROM emp	WHERE sal > ANY(		SELECT sal 			FROM emp			WHERE deptno = 30		)-- 方法二： 使用min，直接取得最小值 SELECT ename, sal, deptno	FROM emp	WHERE sal > (		SELECT MIN(sal) 			FROM emp			WHERE deptno = 30		)

子查询临时表

#将一个select当作一个临时表#查询ecshop中各个类别中，价格最高的商品-- 查询 各个类别 最高的价格,当做临时表-- 把子查询当做一张临时表可以解决很多很多复杂的查询SELECT cat_id , MAX(shop_price) 	FROM ecs_goods	GROUP BY cat_id -- 返回的结果可以当作一张表-- 使用多表查询，将ecs_goods表和临时表进行过滤-- from后面是临时表，起了别名temp-- 过滤条件：临时表的cat_id等于ecs_goods表的cat_id (使得 临时表 和 ecs_goods 表的商品类别匹配上)-- 			并且 临时表的max_price等于ecs_goods表的shop_price(实际就是最高价格等于商品价格，以此来筛选出最高价格商品的名字)SELECT goods_id, ecs_goods.cat_id, goods_name, shop_price 	FROM (		SELECT cat_id , MAX(shop_price) AS max_price		FROM ecs_goods		GROUP BY cat_id	) temp , ecs_goods	WHERE  temp.cat_id = ecs_goods.cat_id 	AND temp.max_price = ecs_goods.shop_price

多列子查询

#多列子查询指 select语句返回的是多列的-- 查询与ALLEN的部门和岗位完全相同的所有雇员(并且不含ALLEN本人)-- 多列查询：where (字段1， 字段2 ...) = (select 字段 1，字段2 from 。。。。)-- 先查出allen的部门和岗位 SELECT deptno,job FROM emp WHERE ename = 'ALLEN' -- 返回了两列，分别是部门和岗位-- 把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配-- 多列子查询：where (deptno,job) = (...) 等号前的两列需要 和 等号后返回的两列 对应-- 找出部门和岗位相同的人 然后排除ALLENSELECT ename,deptno,job FROM emp WHERE (deptno,job) = (SELECT deptno,job FROM emp WHERE ename = 'ALLEN') AND ename != 'ALLEN'#查询和宋江 语文、英语、数学 成绩完全相同的学生SELECT * FROM studentSELECT chinese,english,math FROM student WHERE `NAME` = '宋江'SELECT `NAME` FROM student WHERE (chinese,english,math) = (SELECT chinese,english,math FROM student WHERE `NAME` = '宋江')

练习

-- 子查询练习#查找每个部门工资高于本部门平均工资的人的资料-- 这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用-- 1. 先得到每个部门的 部门号和 对应的平均工资SELECT deptno, AVG(sal) AS avg_sal	FROM emp GROUP BY deptno	-- 2. 把上面的结果当做子查询, 和 emp 进行多表查询SELECT ename, sal, temp.avg_sal, emp.deptno	FROM emp, (		SELECT deptno, AVG(sal) AS avg_sal		FROM emp 		GROUP BY deptno	) temp 	where emp.deptno = temp.deptno and emp.sal > temp.avg_sal	#查找每个部门工资最高的人的详细资料SELECT ename, sal, temp.max_sal, emp.deptno	FROM emp, (		SELECT deptno, max(sal) AS max_sal		FROM emp 		GROUP BY deptno	) temp 	WHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal	#查询每个部门的信息(包括：部门名,编号,地址)和人员数量,我们一起完成。-- 1. 部门名,编号,地址 来自 dept表-- 2. 各个部门的人员数量 -》 构建一个临时表select count(*), deptno 	from emp	group by deptno;	select dname, dept.deptno, loc , tmp.per_num as '人数'	from dept, (		SELECT COUNT(*) as per_num, deptno 		FROM emp		GROUP BY deptno	) tmp 	where tmp.deptno = dept.deptno-- 还有一种写法 表.* 表示将该表所有列都显示出来, 可以简化sql语句-- 在多表查询中，当多个表的列不重复时，才可以直接写列名SELECT tmp.* , dname, loc	FROM dept, (		SELECT COUNT(*) AS per_num, deptno 		FROM emp		GROUP BY deptno	) tmp 	WHERE tmp.deptno = dept.deptno

表复制和去重

#表的复制#为了对某个sql语句进行效率测试，需要海量数据时，可以使用此法为表创建海量数据（自我复制最快）-- 先创建一个表CREATE TABLE my_tab01	( id INT,	  `name` VARCHAR(32),	  sal DOUBLE,	  job VARCHAR(32),	  deptno INT);DESC my_tab01SELECT * FROM my_tab01;-- 表的复制  将emp表复制到my_table表 (使用select语句将查询到的结果插入到my_tab01中)-- 前提：两个表的复制的列应该是对应的 -- 如果两个表的结构是完全相同的，可以不写对应的列 insert into my_tab01 select * from emp;-- 可以直接创建结构一样的表 create table my_tab01 LIKE emp;INSERT INTO my_tab01(id, `name`, sal, job,deptno)SELECT empno, ename, sal, job, deptno FROM emp;-- 自我复制INSERT INTO my_tab01 SELECT * FROM my_tab01;SELECT COUNT(*) FROM my_tab01;-- 查看复制后的数据量#如何删除掉一张表重复记录-- 1. 先创建一张表 my_tab02CREATE TABLE my_tab02 LIKE emp; -- 把emp表的结构(列)，复制到my_tab02-- 2. 让 my_tab02 有相同的记录INSERT INTO my_tab02 SELECT * FROM emp;-- 3. 考虑去重 my_tab02的记录/*思路 (1) 创建表 my_tmp，结构和 my_tab02一样(2) 把my_tab02数据复制到my_tmp中并且用distinct去重(3) 清除my_tab02记录(4) 把 my_tmp 记录复制到 my_tab02(5) 删除my_tmp*/-- (1) 创建表 my_tmp，结构和 my_tab02一样CREATE TABLE my_tmp LIKE my_tab02-- (2) 把my_tab02数据用distinct去重后复制到my_tmp中INSERT INTO my_tmp SELECT DISTINCT * FROM my_tab02;-- (3) 清除my_tab02数据 (清除记录用delete from，会清空数据，留下结构，表依旧存在)DELETE FROM my_tab02;-- (4) 把 my_tmp 数据复制到 my_tab02INSERT INTO my_tab02 SELECT * FROM my_tmp;-- (5) 删除my_tmp(直接把表删除，结构和数据都不存在)DROP TABLE my_tmp;SELECT * FROM my_tmp;SELECT * FROM my_tab02;

select合并查询

合并查询是将两条sql语句的结果合并在一起(放在一起)
union all 合并结果不会去重
union 合并结果会自动去重

#合并查询SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5条数据SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3条数据-- union all 就是将两个查询结果合并，不会去重SELECT ename,sal,job FROM emp WHERE sal>2500 UNION ALLSELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 合并后8条数据-- union  就是将两个查询结果合并，会去重SELECT ename,sal,job FROM emp WHERE sal>2500 UNION SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 合并后6条数据，有两天重复的自动去重了

外连接

为什么有外连接？
- 在使用多表查询时，两个表中不匹配的项不显示(一般是null的)
- 如果使用外连接，即使是没有相匹配数据的项，也会显示，找不到匹配值的地方会显示为null
- 如图所示，找不到的成绩会显示为null，此时是将有grade的表进行外连接
外连接详解
- 分为 左外连接和右外连接
- 左外连接
  - 表示左边的表所有的项都会跟右边的表进行匹配，即使该项是null，最后也会显示出来
  - 格式：select ... from 左表 left join 右表 on 条件
- 右外连接
  - 表示右边的表所有的项都会跟左边的表进行匹配，即使该项是null，最后也会显示出来
  - 格式：select ... from 左表 right join 右表 on 条件
- on实际是多表查询中的where，作用是相同的
- 左右外连接作用是相同的，如果将左外连接中两个表的位置换一下，就是右外连接，反之亦然

-- 创建 stuCREATE TABLE stu (	id INT,	`name` VARCHAR(32));INSERT INTO stu VALUES(1, 'jack'),(2,'tom'),(3, 'kity'),(4, 'nono');SELECT * FROM stu;-- 创建 examCREATE TABLE exam(	id INT,	grade INT);INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);SELECT * FROM exam;#左连接#显示所有人的姓名、id、成绩,如果没有成绩显示为空-- stu表 有姓名、id ，exam表有id、成绩-- 正常的多表情况SELECT `name`, stu.id, grade	FROM stu, exam	WHERE stu.id = exam.id;-- 使用左外连接SELECT `name`, stu.id, grade	FROM stu LEFT JOIN exam	ON stu.id = exam.id;-- 使用右外连接（显示所有成绩，如果没有名字匹配，显示空)-- 即：右边的表(exam) 和左表没有匹配的记录，也会把右表的记录显示出来SELECT `name`, stu.id, grade	FROM stu RIGHT JOIN exam	ON stu.id = exam.id;#列出部门名称和部门的员工信息(名字和工作)，-- 需要列出没有员工的部门名-- 使用左外连接实现  -- 在dept表中有 部门名dname ，使用左外连接后，所有的部门名都会显示出来，即使它没有员工信息，没有的员工信息会显示为nullSELECT dname, ename, job	FROM dept LEFT JOIN emp	ON dept.deptno = emp.deptno	-- 使用右外连接实现，实际就是换了一下表的位置SELECT dname, ename, job	FROM emp RIGHT JOIN dept	ON dept.deptno = emp.deptno

约束

主键(primary key)

主键不能重复和null
一张表最多只有一个主键/复合主键
复合主键
- 将一个以上的列作为主键
- 复合主键实际是一个主键
- eg：id和name是复合主键，如果插入的是已有id和已有name，插入失败；如果插入的是已有id，未有name，成功插入；如果插入的是未有id，已有name，成功插入。
desc 表名 可以看到主键的情况，如果显示有多个，则是复合主键

#主键 -- 格式一 CREATE TABLE t17	(id INT PRIMARY KEY, -- primary key表示id列是主键 	`name` VARCHAR(32));-- 格式二CREATE TABLE t17	(id INT	`name` VARCHAR(32),     PRIMARY KEY(id));-- 复合主键CREATE TABLE t17	(id INT	`name` VARCHAR(32),     PRIMARY KEY(id,`name`)); -- 将id和name设为复合主键INSERT INTO t17	VALUES(1, 'jack', 'jack@sohu.com');-- 插入成功INSERT INTO t17	VALUES(2, 'tom', 'tom@sohu.com');-- 插入成功INSERT INTO t17	VALUES(1, 'jack', 'hsp@sohu.com');-- 插入失败 因为 1 和 jack 都是已存在的

非空(not null)

创建列时定义了not null，则插入数据时必须给该列提供数据
格式：id int not null

唯一(unique)

创建列时定义unique后该列的值不能重复
格式：id int unique
可以有重复的null
一张表可以有多个列设置unique
unique not null 实际类似主键，不能重复且不能为空

外键

介绍

用来定义主表和从表之间的关系
外键约束在从表上定义
主表必须具有primary key(主键约束)或者unique约束
定义外键约束后，要求外键该列存入的数据必须是主表绑定列已存在的值/null
格式 foreign key(从表列) references 主表名(主表列)

细节

外键指向的主表字段，必须是主键(primary key)或者unique
只有innodb类型表才支持外键
主从表的字段类型要一致(长度可不同)
从表外键字段的值，必须在主表绑定的字段中出现或者是null
- 如果主表设置绑定的字段设置为not null ，从表外键字段依旧可以是null
- 如果从表外键字段设置为为not null，从表外键字段则不能为null
一旦建立主外键的关系，数据不能随意删除

-- 外键演示-- 创建 主表 my_classCREATE TABLE my_class (	id INT PRIMARY KEY , -- 班级编号	`name` VARCHAR(32) NOT NULL DEFAULT '');-- 创建 从表 my_stuCREATE TABLE my_stu (	id INT PRIMARY KEY , -- 学生编号	`name` VARCHAR(32) NOT NULL DEFAULT '',	class_id INT , -- 学生所在班级的编号	--  指定外键关系 从表的class_id关联主表的id	FOREIGN KEY (class_id) REFERENCES my_class(id));	-- 测试数据INSERT INTO my_class 	VALUES(100, 'java'), (200, 'web');INSERT INTO my_class 	VALUES(300, 'php');	SELECT * FROM my_class;INSERT INTO my_stu 	VALUES(1, 'tom', 100);INSERT INTO my_stu 	VALUES(2, 'jack', 200);INSERT INTO my_stu 	VALUES(3, 'hsp', 300);INSERT INTO my_stu 	VALUES(4, 'mary', 400); -- 这里会失败...因为400班级不存在INSERT INTO my_stu 	VALUES(5, 'king', NULL); -- 可以, 外键没有写not null(即从表该字段允许是null，不受主表的not null影响)SELECT * FROM my_class;-- 一旦建立主外键的关系，数据不能随意删除了DELETE FROM my_class WHERE id = 100; -- 删除失败delete from my_stu where class_id = 100;delete from my_class where id = 100;-- 先删除主表的class_id，从表的id没有任何关联后，就能够删除

check

用于强制数据必须满足某条件
在mysql中实现check的功能，一般是在程序中控制或者通过触发器完成

-- mysql5.7不支持check ,只做语法校验，但不会生效 -- mysql8.0开始、oracle和sql server生效.-- 测试表CREATE TABLE t23 (	id INT PRIMARY KEY,	`name` VARCHAR(32) ,	sex VARCHAR(6) CHECK (sex IN('man','woman')),	sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)	);		-- 添加数据   插入成功，因为mysql5.7不会生效INSERT INTO t23 	VALUES(1, 'jack', 'mid', 1);SELECT * FROM t23;

自增长

跟主键(primary key)/unique配合使用
自增长修饰字段一般为整形(少数情况下用double)
默认从1开始，可以修改初始值（两种方法）
- ALTER TABLE t25 AUTO_INCREMENT = 100
- INSERT INTO t25 VALUES(100, 'h', 'hsp');
使用自增长时
- 自增长的列填入null即可
- 固定设置其他字段的值，不设置自增长字段的值(会默认为null)

-- 演示自增长的使用-- 创建表CREATE TABLE t24	(id INT PRIMARY KEY AUTO_INCREMENT,	 email VARCHAR(32)NOT NULL DEFAULT '',	 `name` VARCHAR(32)NOT NULL DEFAULT ''); DESC t24-- 测试自增长的使用INSERT INTO t24 -- 插入null则是自增长 从1开始	VALUES(NULL, 'tom@qq.com', 'tom');INSERT INTO t24	(email, `name`) VALUES('hsp@sohu.com', 'hsp');SELECT * FROM t24;#  修改默认的自增长开始值-- 方法一：ALTER TABLE t25 AUTO_INCREMENT = 100-- 方法二：插入时不使用null，给定开始值-- 创建测试表CREATE TABLE t25	(id INT PRIMARY KEY AUTO_INCREMENT,	 email VARCHAR(32)NOT NULL DEFAULT '',	 `name` VARCHAR(32)NOT NULL DEFAULT ''); -- 插入一个值，自增长初始值是1INSERT INTO t25	VALUES(NULL, 'mary@qq.com', 'mary');-- 插入一个值，自增强值为666INSERT INTO t25	VALUES(666, 'hsp@qq.com', 'hsp');SELECT * FROM t25;

索引

细节
- 创建索引能提高查询速度
- 代价
  - 提高表的占用大小
  - 降低更新表的速度，使用dml(update delete insert)语句后不仅要保存数据，还要保存索引文件,导致效率降低
- select语句不影响索引
分类
- 主键索引(primary key) 主键自动为主键索引
- 唯一索引(unique index)
- 普通索引(index)
- 全文索引(fulltext) 适用于MyISAM 开发中使用：全文所有Solr和ElasticSearch(ES)
场景
- 频繁作为查询条件的字段应创建索引
- 唯一性太差的不适合单独创建索引，即使频繁作为查询条件 eg： sex = '男'；
- 更新非常频繁的字段不适合创建索引
- 不会出现在where子句中的字段不要创建索引(用不到的不用创建)

#mysql的索引的使用-- 创建表CREATE TABLE t25 (	id INT ,	`name` VARCHAR(32));# 查询表是否索引SHOW INDEXES FROM t25;#  添加索引-- 如果某列的值，是不会重复的，则优先考虑使用unique索引, 否则使用普通索引-- 添加唯一(unique)索引 CREATE UNIQUE INDEX id_index ON t25 (id);-- 添加普通索引方式1CREATE INDEX id_index ON t25 (id);-- id_index是我们为索引起的名字-- 添加普通索引方式2ALTER TABLE t25 ADD INDEX id_index (id) -- 添加主键索引方式1CREATE TABLE t26 (	id INT ,	`name` VARCHAR(32));ALTER TABLE t26 ADD PRIMARY KEY (id)-- 添加主键索引方式2 (在建表时直接给字段primary key即可)CREATE TABLE t26 (	id INT PRIMARY KEY,	`name` VARCHAR(32));SHOW INDEX FROM t26# 删除索引DROP INDEX id_index ON t25-- 删除主键索引ALTER TABLE t26 DROP PRIMARY KEY# 修改索引 ， 先删除，在添加新的索引# 查询索引-- 方式1SHOW INDEX FROM t25-- 方式2SHOW INDEXES FROM t25-- 方式3SHOW KEYS FROM t25-- 方式4  在key中显示为MUL，此方法查询不够清晰DESC t25

事务

什么是事务

用于保证数据的一致性，由一组相关的dml语句组成，dml语句是一个整体，要么都成功，要么都失败
eg：转账需要使用事务，保证数据的一致性
还可以通过设置保存点，回退到保存点的数据状态(相当于临时快照)

事务和锁

事务的基本操作
- start transaction 开始一个事务
- savepoint 自定义保存点名字 设置保存点
- rollback to 保存点名 回退事务(回到保存点的数据，保存点后的操作全部失效，如果后面有其他保存点，也会一并删除)
- rollback 回退全部事务
- commit提交事务(所有的操作都生效，再也不能回退)(提交会自动删除所有保存点)
锁
- 在事务中dml语句操作时，mysql会在表上加锁，防止其他用户改变表的数据(此时其他用户不能对表进行修改)
- 只有在可串行化serializable隔离级别才会加锁

-- 演示-- 创建一张测试表CREATE TABLE t27	( id INT,	  `name` VARCHAR(32));-- 开始事务START TRANSACTION -- 设置保存点aSAVEPOINT a-- 执行dml 操作INSERT INTO t27 VALUES(100, 'tom');SELECT * FROM t27;-- 设置保存点bSAVEPOINT b-- 执行dml操作INSERT INTO t27 VALUES(200, 'jack');-- 回退到 bROLLBACK TO b-- 继续回退到 aROLLBACK TO a-- 回退到事务开始的状态ROLLBACK -- 提交事务COMMIT

事务细节

不开启事务，默认情况下dml操作是自动提交，不能回滚
开始一个事务不设置保存点，使用rollback会回到初始事务开始的状态
提交事务(所有的操作都生效，再也不能回退)(提交会自动删除所有保存点)
回退事务(回到保存点的数据，保存点后的操作全部失效，如果后面有其他保存点，也会一并删除)
mysql的事务机制只有innodb存储引擎能使用
开始事务有两种方式
- start transaction
- set autocommit = off;

事务隔离级别

介绍

多个连接开启各自的事务操作时，数据库系统要负责隔离操作，保证各个连接在获取数据时的准确性
如果不考虑隔离性，可能引发以下问题：
- 脏读
- 不可重复度
- 幻读
系统的默认隔离级别是可重复度(repeatable read)，即不会发生脏读、不可重复度、幻读，也不会加锁
- 如果需要修改默认隔离级别，需修改配置文件

4种隔离级别

隔离级别引发的情况

脏读(dirty read)
- 当一个事务读取另一个事务尚未提交的dml改变时，会产生脏读
- 用户1在事务期间，对表进行dml操作时，用户2查到的数据是随用户1的操作而改变的，即使用户1没有提交事务
不可重复度(nonrepeatbale read)
- 在本事务中进行查询，由于其他事务提交了所做的修改/删除，本事务查询返回的结果会改变，此时发生不可重复度
- 用户1在事务中进行查询，用户2在事务中进行了修改/删除并提交，用户1再次进行查询会出现用户2修改/删除后的结果
幻读(phantom read)
- 由于其他事务提交了所做的插入，本事务再次查询返回的结果会改变，此时产生幻读
- 用户2在事务中进行了插入并提交，用户1再次进行查询会出现用户2插入后的结果
serializable的加锁
- 当用户1在事务期间，用户2无法进行任何操作(即使不在事务中)(即使只是select)，只有用户1进行了commit提交，用户2才能操作

常用指令

-- 查看当前会话隔离级别 SELECT @@tx_isolation-- 查看系统当前隔离级别SELECT @@global.tx_isolation-- 设置当前会话隔离级别SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED-- 设置系统当前隔离级别SET GLOBAL TRANSACTION ISOLATION LEVEL [要设置的级别]-- 如果需要修改默认级别，需修改配置文件

事务的特性(ACID)

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

引擎

介绍

mysql表类型由存储引擎决定，主要包括innoDB、MyISAM、Memory等
mysql支持六种引擎：CSV Memory ARCHIVE MRG_MYISAM MYISAM InnoDB
六种分两类
- 第一类：“事务安全型” 有InnoDB
- 第二类：“非事务安全型” 除了InnoDB其他5种都是

主要的存储引擎/表类型特点

细节

MyISAM不支持事务，不支持外键，但访问速度快，对事务完整性没有要求
InnoDB支持事务，处理效率差一点，会占用更多的磁盘空间来保留数据和索引
MRMORY将内存存在内存中来创建表，每个MRMORY表实际对应一个磁盘文件，MRMORY表访问速度非常快，因为数据存放在内存中，并且默认使用hash索引，但一旦mysql服务关闭，表数据就会全部丢失，只存在表结构

#表类型/存储引擎-- 查看所有的存储引擎SHOW ENGINES-- innodb 存储引擎-- 1. 支持事务 2. 支持外键 3. 支持行级锁-- myisam 存储引擎-- 1. 添加速度快 2. 不支持外键和事务 3. 支持表级锁CREATE TABLE t28 (	id INT,	`name` VARCHAR(32)) ENGINE MYISAM-- 开始事务START TRANSACTION;SAVEPOINT t1INSERT INTO t28 VALUES(1, 'jack');SELECT * FROM t28;ROLLBACK TO t1 -- 回滚失败，因为不支持事务-- memory 存储引擎-- 1. 数据存储在内存中[关闭了Mysql服务，数据丢失, 但是表结构还在] -- 2. 执行速度很快(没有IO读写) 3. 默认支持索引(hash表)CREATE TABLE t29 (	id INT,	`name` VARCHAR(32)) ENGINE MEMORYDESC t29INSERT INTO t29	VALUES(1,'tom'), (2,'jack'), (3, 'hsp');SELECT * FROM t29-- 指令修改存储引擎ALTER TABLE `t29` ENGINE = INNODB

如何选择

应用不需要事务，只用基本的curd，选择myisam速度快
需要事务，选择innodb

上一篇：力扣 1218题：找出具有最长定差的子序列

下一篇：每日开源 | 推荐几款优秀的开源博客系统

学习MySQL的笔记

连接MySQL

数据库基本指令

备份恢复数据库/表

创建表

列类型(数据类型)

常用( [] 内是 字节/字符 数)

bit[M]

decimal[M.D]

varchar[size]

字符串使用细节

unsigned

日期

修改表

insert

插入方式

细节

update

使用方式

细节

delete

使用

细节

select

基础使用

练习

where的运算符

order by ... (asc/desc)

函数

count

sum

avg

max/min

group by

练习前需要创建的表

使用

字符串函数

数学函数

日期函数

加密函数

流程控制语句

分页查询

执行顺序

多表查询和笛卡尔集

笛卡尔集

多表查询

自连接

多行子查询

all和any

子查询临时表

多列子查询

练习

表复制和去重

select合并查询

外连接

约束

主键(primary key)

非空(not null)

唯一(unique)

外键

介绍

细节

check

自增长

索引

事务

什么是事务

事务和锁

事务细节

事务隔离级别

介绍

4种隔离级别

隔离级别引发的情况

常用指令

事务的特性(ACID)

引擎

介绍

主要的 存储引擎/表类型 特点

细节

如何选择

常用( [] 内是字节/字符数)

主要的存储引擎/表类型特点

用于 Python 机器学习的函数极限和导数高等数学文章