Mysql For Navicate (老韩)

Navicate创建数据库

1. 先创建一个数据库;
2. 然后在数据库中创建一张表;
3. 在表格当中填入相应的属性字段;
4. 打开表, 然后填入相应的实例字段; – 使用数据库图形化App和使用指令来进行操作各有各的好处和利弊;

数据库的三层结构(破除MySQL神秘)

1. 所谓安装Mysql数据库, 就是在主机安装一个数据库管理系统(DBMS), 这个管理系统可以管理多个数据库;
2. 一个数据库中可以创建多个表, 以保存数据(信息);
3. 数据库管理系统(DBMS)、数据库和表的关系如图所示: 示意图;
   
   注意⚠️:

• 每个数据库当中有很多种结构,不只是表格还有很多函数等等;
• Mysql数据库-普通表的本质仍然是文件

对于某一条指令的操作;
比如: SELECT * FROM users;
执行原理: 本质是通过3306这个端口号进入到DBMS,然后进入到相应的数据库中,找到对应的某一张表格进行查询操作;

为什么要学习sql语句呢?

• 因为如果你不学习sql语句的话, java程序需要书写操作; 一般通过jdbc来进行相关的操作

数据库的相关操作

创建数据库

# 演示创建数据库操作
CREATE DATABASE hsp_db01;
# 删除数据库指令
DROP DATABASE hsp_db01;

查看数据库&删除数据库

# 演示删除和查询数据库
# 查看当前数据库服务器中的所有数据库
SHOW DATABASES;

# 查看前面创建的hsp_db01数据库的定义信息; 反引号的作用:规避关键字;
SHOW CREATE DATABASE hsp_db01;

# 删除前面创建的hsp_db01数据库
# DROP DATABASE hsp_db01;

注意⚠️: WHERE和HAVING作用的区别:HAVING是用来指定组的条件,WHERE是用来指定行的条件;

备份数据库

给了一张sql表怎么备份到自己项目中: 打开sql语句, 拷贝重新执行一遍; 也有相应的语句供操作, 见资料图片

创建表

CREATE TABLE `users` (
		id INT, 
		`name` VARCHAR(255), 
		`password` VARCHAR(255), 
		`birthday` DATE)
		CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

解释:

1. CHARACTER SET utf8; 设置字符集为utf8
2. COLLATE utf8_bin;
   • COLLATE关键字用于指定数据在进行比较和排序时所使用的规则。
   • 在创建表格时使用COLLATE utf8_bin，意味着对于该表格中的文本数据，在比较和排序时会严格按照UTF-8编码区分大小写。
3. ENGINE INNODB; 使用了InnoDB引擎，这是MySQL数据库的一种存储引擎，提供了事务处理和外键约束等功能。

Mysql列类型

1. 数值类型; 在满足需求的情况下,尽量选择占用空间小的类型;
2. 文本类型(字符串类型)
3. 二进制数据类型
4. 日期类型

数据类型的演示操作TINYINT

# 如果没有指定unsinged, 则表示有符号, 否则表示无符号;
# 演示整形的使用
CREATE TABLE t3 (
		id TINYINT); 
		
CREATE TABLE t4 (
		id TINYINT UNSIGNED);  
		
INSERT INTO t3 VALUES(-128); 
INSERT INTO t4 VALUES(-128); 

数据类型的演示操作; 小数型号

# 演示decimal、float、double的使用
# 创建表; DECIMAL可以存放非常大的数据;
CREATE TABLE t6 (
		num1 FLOAT, 
		num2 DOUBLE, 
		num3 DECIMAL(30, 20));
	
# 添加数据
INSERT INTO t6 VALUES(88.1234567891234, 88.1234567891234, 88.1234567891234); 

字符串数据类型的基本使用

# 字符串的基本使用 CHAR、VARCHAR
CREATE TABLE t7 (
		`name` CHAR(255));
		
CREATE TABLE t8 (
		`name` VARCHAR(10000));
		
DROP TABLE t8; 

注意⚠️: 如果varchar不够用,可以尝试使用mediumtext,或者longtext, 如果想简单点,可以直接使用text;

日期类型的基本使用

# 演示时间相关的类型
CREATE TABLE t9(
		birthday DATE, -- 生日
		job_time DATETIME, -- 记录年月日,时分秒
		login_time TIMESTAMP 
		NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 
		
SELECT * FROM t9;

INSERT INTO t9(birthday, job_time) VALUES('2022-11-11', '2022-11-11 10:10:10'); 

创建表练习

# 创建表练习
CREATE table `emp` (
		id INT, 
		`name` VARCHAR(32), 
		sex CHAR(1), 
		birthday DATE, 
		entry_data DATETIME, 
		job VARCHAR(32),
		salary DOUBLE, 
		`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB; 

# 添加一条
INSERT INTO `emp` VALUES(100, 'hk', '男', '2000-11-11', '2010-11-10 11:11:11', '开心的', 20000, '自信');

修改表练习; 修改表的字段: alter

# 修改表的操作练习
ALTER TABLE emp ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER resume; 
# 查看表的所有列
DESC emp;
# 修改job列, 使其长度为60
ALTER TABLE emp MODIFY job VARCHAR(60) NOT NULL DEFAULT ''; 
# 删除sex列
ALTER TABLE emp DROP 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(64) NOT NULL DEFAULT ''; 
	
DESC employee; 

修改表的相关操作总结

1. 添加某一列; ALTER + ADD
2. 修改某一列; ALTER + MODIFY
3. 删除某一列; ALTER + DROP
4. 修改表名; RENAME
5. 修改表里面的列属性; ALTER + CHANGE

注意⚠️: 插入操作的话,可以写具体的列也可以不写具体的列(需要一一对应清楚);

Mysql语句的增删改查

Insert语句; 插入

CREATE TABLE `goods` (
		id INT, 
		goods_name VARCHAR(10), 
		price DOUBLE NOT NULL DEFAULT 100);  -- 给该列一个固定的字段,并给予默认值,如果没有设置列值的话就使用默认值

DROP TABLE goods; 
		
-- 添加数据
INSERT INTO `goods` (id, goods_name, price)
		VALUES(10, '华为手机', 2000);
		
INSERT INTO `goods` (id, goods_name, price)
		VALUES(20, '苹果手机', 5999);

SELECT * FROM goods;

update语句; 更新

通常为update+set+where(来进行限制约束操作)

-- 1. 将所有员工薪水修改为5000元[如果没有带where条件,会修改所有记录,因此要小心]
UPDATE employee SET salary = 5000; 
-- 2. 将姓名为小妖怪的员工薪水修改为3000
UPDATE employee SET salary = 3000
		WHERE user_name = 'hk'; 
-- 3. 将kb的薪水在原有基础上增加1000元
INSERT INTO employee 
	VALUES(2, 'kb', '1990-11-11', '2023-11-11 11:11:11', '打球的', 5000, 'hello', '图片路径'); 

UPDATE employee SET salary = salary + 1000 WHERE user_name = 'kb';

SELECT * FROM employee; 

delete删除语句;

通常表现为 delete+from+where

-- 删除语句
-- 1. 删除表中名称为 'kb'的记录
DELETE FROM employee WHERE user_name = 'kb'; 
-- 2. 删除表中所有记录,提醒: 一定要小心; 并非删除表
DELETE FROM employee; 
-- 3. DELETE语句不能删除某一列的值(可以使用update设为null或者'')
UPDATE employee SET job = '' WHERE user_name = 'hk'; 
-- 4. 要删除某张表, 而不是某张记录
DROP TABLE employee; 

SELECT * FROM employee; 

select语句; 查询

-- 创建一张新的学生表
CREATE TABLE student(
	id INT NOT NULL DEFAULT 1, 
	`name` VARCHAR(20) NOT NULL DEFAULT '', 
	chinese FLOAT NOT NULL DEFAULT 0.0, 
	english FLOAT NOT NULL DEFAULT 0.0, 
	math FLOAT NOT NULL DEFAULT 0.0
); 
INSERT INTO student(id, name, chinese, english, math) VALUES(1, 'hk', 99, 99, 99); 
INSERT INTO student(id, name, chinese, english, math) VALUES(2, 'hkk', 11, 22, 33); 
INSERT INTO student(id, name, chinese, english, math) VALUES(3, 'kk', 44, 55, 66); 
INSERT INTO student(id, name, chinese, english, math) VALUES(4, 'mm', 77, 88, 99); 
INSERT INTO student(id, name, chinese, english, math) VALUES(5, 'nn', 67, 68, 69); 
INSERT INTO student(id, name, chinese, english, math) VALUES(6, 'jj', 19, 39, 99); 
INSERT INTO student(id, name, chinese, english, math) VALUES(7, 'zz', 79, 99, 99); 

DELETE FROM student; // 从某一张表当中删除; 

SELECT * FROM student; 
-- SELECT语句的使用1
-- 1. 查询表中所有学生的信息
SELECT * FROM student; 
-- 2. 查询表中所有学生的姓名和对应的英语成绩
SELECT `name`, english from student; 
-- 3. 过滤掉表中重复数据distinct; 使用distinct来表示唯一的字段操作; 
SELECT DISTINCT english FROM student; 
-- 4. 要查询的记录,每个字段都相同,才会去重
SELECT DISTINCT `name`, english FROM student; 

-- SELECT语句的使用2
-- 1. 统计每个学生的总分
SELECT `name`, (chinese+english+math) FROM student;
-- 2. 在所有学生总分加10分的情况
SELECT `name`, (chinese+english+math+10) FROM student;
-- 3. 使用别名表示学生分数; 别名设置 AS
SELECT `name`, (chinese+english+math+10) AS total_score FROM student;

-- SELECT语句的使用3
-- 1. 查询姓名为hk的学生成绩
SELECT * FROM student WHERE `name` = 'hk'; 
-- 2. 查询英语成绩大于90分的同学
SELECT * FROM student WHERE english > 90;
-- 3. 查询总分大于200分的所有同学
SELECT * FROM student WHERE (chinese + english + math) > 200; 

-- SELECT语句的使用4
-- 1. 查询math大于60并且(and)id大于4的学生成绩
SELECT * FROM student WHERE math > 60 AND id > 4; 
-- 2. 查询英语成绩大于语文成绩的同学
SELECT * FROM student WHERE english > chinese;
-- 3. 查询总分大于200分并且数学成绩大于语文成绩的姓n的学生
SELECT * FROM student WHERE (chinese + english + math) > 200 AND (math > chinese) AND `name` LIKE 'n%'; 

LIKE表示一种模糊查询的操作; 在这个例子中，'n%' 使用了通配符 %，它匹配以字母 'n' 开头的任何文本。

-- SELECT语句的使用5
-- 1. 查询英语分数在80-90之间的学生成绩
SELECT * FROM student WHERE english >= 80 AND english <= 90; 
SELECT * FROM student WHERE english BETWEEN 80 AND 90;  -- BETWEEN...AND...是闭区间
-- 2. 查询数学分数为89,90,91的的同学
SELECT * FROM student WHERE math = 89 OR math = 90 OR math = 91;
SELECT * FROM student WHERE math IN (89, 90, 91);
-- 3. 查询所有姓h的同学
SELECT * FROM student WHERE `name` LIKE 'h%'; 

-- SELECT语句的使用6
-- ORDER BY 的使用
-- 1. 对数学成绩排序后输出[升序]
SELECT * FROM student ORDER BY math; 
-- 2. 对总分按从高到低的顺序输出[降序]; 还可以通过别名排序
SELECT `name`, (chinese+english+math) AS total_score FROM student ORDER BY total_score DESC; 
-- 3. 对姓h的学生成绩[总分]排序输出(升序) WHERE + ORDER BY
SELECT `name`, (chinese+english+math) AS total_score FROM student
	WHERE `name` LIKE 'h%' ORDER BY total_score; 

-- SELECT语句的使用7
-- 统计函数的使用
-- 1. 统计一个班级有多少学生
SELECT COUNT(*) FROM student; 
-- 2. 统计数学成绩大于90的学生有多少个?
SELECT COUNT(*) FROM student WHERE math > 90; 
-- 3. 统计总分大于250的人数有多少?
SELECT COUNT(*) FROM student WHERE (math + english + chinese) > 250; 
-- 4. 解释count(*) 和 count(列)的区别	
	-- count(*)返回满足条件的记录的行数, count(列)统计满足条件的列有多少个;
-- sum函数的使用
-- 1. 统计一个班级数学总成绩?
SELECT SUM(math) FROM student; 
-- 2. 统计一个班级语文、数学、英语各科的总成绩
SELECT SUM(math) AS math_total_score, SUM(english), SUM(chinese) FROM student; 
-- 3. 统计一个班语文、英语、数学的成绩总和
SELECT SUM(math + english + chinese) FROM student; 
-- 4. 统计一个班级语文成绩平均分
SELECT SUM(chinese) / COUNT(*) FROM student; 

-- Avg函数的使用
-- 1. 求一个班级数学平均分
SELECT AVG(math) from student; 
-- 2. 求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student; 

-- max和min的使用
-- 求班级最高分和最低分
SELECT MAX(math + english + chinese), MIN(math + english + chinese) FROM student; 

-- 部门表 
CREATE TABLE dept(
    deptno MEDIUMINT 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','BOSHTON');  
SELECT * FROM dept;

-- 员工表
CREATE TABLE `emp` (
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `ename` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '""',
  `job` varchar(9) COLLATE utf8_bin NOT NULL DEFAULT '""',
  `mgr` mediumint(8) unsigned DEFAULT NULL,
  `hiredate` date NOT NULL,
  `sal` decimal(7,2) NOT NULL,
  `comm` decimal(7,2) DEFAULT NULL,
  `deptno` mediumint(8) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- 添加测试数据
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',7968,'1991-2-22',1250.00,500.00,30),
    (7566,'JONES','MANAGER',7839,'1991-4-2',2975.00,NULL,20),
    (7654,'MARTIN','SALESMAN',7968,'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,'1991-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,'1991-1-23',1300.00,NULL,10);
		
SELECT * FROM emp;

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),
(2, 1201, 1400),
(3, 1401, 2000),
(4, 2001, 3000),
(5, 3001, 9999)

-- 演示GROUP BY + HAVING
-- 1. 如何显示每个部门的平均工资和最高工资
-- 2. 按照部分来分组查询
SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP BY deptno; 

-- 显示每个部门的每种岗位的平均工资和最低工资; 
	-- 1. 显示每个部门的平均工资和最低工资
	-- 2. 显示每个部门的每种岗位的平均工资和最低工资
    -- 不仅是每个部门,每种岗位也需要纳入考量范围; 
SELECT AVG(sal), MIN(sal), deptno, job FROM emp GROUP BY deptno, job; 
-- 显示平均工资低于2000的部门号和它的平均工资 思路: 化繁为简, 各个击破
	-- 1. 显示各个部门的平均工资和部门号
	-- 2. 在1的结果基础上, 进行过滤, 保留AVG(sal) < 2000
-- SELECT AVG(sal), deptno FROM emp GROUP BY deptno; 
-- SELECT AVG(sal), deptno FROM emp GROUP BY deptno HAVING AVG(sal) < 2000; 
-- 取别名; 使用别名能更加简化相应的步骤及操作;
SELECT AVG(sal) AS avg_sal, deptno FROM emp GROUP BY deptno HAVING avg_sal < 2000; 

注意点⚠️

1. 过滤掉重复的数据, 使用关键字 DISTINCT;
2. select后面可以带上表达式;
3. where条件后面可以有多个, 使用and 或 or来连接;
4. 模糊查询通配符使用 % 来连接;
5. 通过某个字段来排序, 使用order by来相关操作;
6. count(*)是用来统计满足某个列的总数;
7. select挑选可以使用表达式等;
8. group by通常是根据某一个部门来进行相关的分组操作;

字符串的相关函数

-- 演示字符串相关函数的使用, 使用emp表来演示
-- 1; CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp; 
-- 2; CONCAT(str1,str2,...), 连接字串,  将多个列拼接成一列
SELECT CONCAT(ename, '工作是', job) FROM emp;   
-- 3. INSTR(str,substr); 返回substr在str中出现的位置, 没有返回0
-- 从位置1开始; 
-- dual为亚元表, 系统表, 可以作为测试表使用; 
SELECT INSTR('hk', 'h') FROM DUAL; 
-- 4. UCASE(str); 转换成大写
SELECT UCASE(ename) FROM emp; 
-- 5. LCASE(str); 转换成小写
SELECT LCASE(ename) FROM emp; 
-- 6. LEFT(str,len); 从str中的左边起取length个字符
SELECT LEFT(ename,2) FROM emp; 
-- 7. LENGTH(str); str长度(按照字节)
SELECT LENGTH(ename) FROM emp;  
SELECT LENGTH('hk') FROM DUAL; -- 2; 按照字节来返回操作
-- 8. REPLACE(str,from_str,to_str); 在str中, 使用to_str来替换from_str
SELECT * FROM emp; 
-- 如果是manager, 就替换成经理; SELECT 后面可以添加多个字段, 
SELECT ename, REPLACE(job, 'MANAGER', '经理') FROM emp; 
-- 9. STRCMP(expr1,expr2); 逐字符比较两字串大小
SELECT STRCMP('hk', 'hkk') FROM DUAL; -- -1表示不相等, 1表示相等
-- 10. SUBSTRING(str,pos,len); 从str的pos位置开始[从1开始计算], 取length个字符
-- 从ename列的第一个位置开始取出2个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp; 
-- 11. LTRIM(str), RTRIM(str), TRIM([remstr FROM] str) 去除空格
SELECT LTRIM(' hk') FROM DUAL; 

练习

-- 练习: 以首字母小写的方式显示所有员工emp表的姓名; 
SELECT * FROM emp; 
-- 方法1  
-- 思路: 先取出ename的第一个字符, 转成小写的, 把他和后面的字符串进行拼接输出即可
SELECT CONCAT(LCASE(SUBSTRING(ename, 1, 1)), SUBSTRING(ename, 2)) AS new_name FROM emp; 

数学相关函数

-- 数学相关函数 
-- 1. ABS(num) 绝对值
SELECT ABS(-10) FROM DUAL; 
-- 2. BIN(N) 十进制转位二进制
SELECT BIN(10) FROM DUAL; 
-- 3. CEILING(X)) 向上取整, 得到比num2大的最小整数
SELECT CEILING(10.1) FROM DUAL; 
SELECT CEILING(-1.11) FROM DUAL; -- -1
-- 4. CONV(N,from_base,to_base) 进制转换
SELECT CONV(10,10,2) FROM DUAL; -- 十进制的十转换为二进制
-- 5. FLOOR(X) 向下取整, 得到比x小的最大整数
SELECT FLOOR(-1.1) FROM DUAL; -- -2
-- 6. FORMAT(X,D[,locale]) 保留小数点后面几位[四舍五入]
SELECT FORMAT(78.112345,2) FROM DUAL; 
-- 7. HEX(N_or_S) 转换成十六进制
-- 8. LEAST(value1,value2,...)。求最小值
SELECT LEAST(0,-1,10, 11) FROM DUAL; 
-- 9. MOD(N,M) 求余
-- 10. RAND(); 说明如果rand(seed)加了种子,后面就是固定的随机数
SELECT RAND() FROM DUAL; 

时间日期相关函数 & 额外函数

-- 日期时间相关函数; 对于时间表的相关操作添加时间等可以直接使用; 
-- 1. CURRENT_DATE 当前日期
SELECT CURRENT_DATE() FROM DUAL; 
-- 2. CURRENT_TIME 当前时间
SELECT CURRENT_TIME() FROM DUAL; 
-- 3. CURRENT_TIMESTAMP 当前时间戳
SELECT CURRENT_TIMESTAMP() FROM DUAL; 
-- 4. NOW() 当前日期和时间
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; 
-- 应用实例
-- 1. 显示所有新闻信息, 发布日期只显示日期, 不用显示时间
SELECT id, content, DATE(send_time) FROM mes; 
-- 2. 请查询在10分钟内发布的新闻
SELECT * FROM mes WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE) >= NOW(); 
SELECT * FROM mes WHERE send_time >= DATE_SUB(NOW(),INTERVAL 1000 MINUTE); 
-- 3. 请在mysql的sql语句中求出2011-11-11和1990-1-1相差多少天;
SELECT DATEDIFF('2011-11-11','1990-1-1') FROM DUAL; 
-- 4. 请用mysql的sql语句求出你活了多少天; 
SELECT DATEDIFF(NOW(),'1999-05-17') FROM DUAL; 
SELECT DATEDIFF(NOW(),'1999-05-17')/365 FROM DUAL; 
-- 5. 如果你能活80岁, 求出你还能活多少天
-- 先求出活到80岁的时候,是什么日期;
-- 然后使用datediff(x, NOW())
-- YEAR可以是day等等
SELECT DATEDIFF(DATE_ADD('1999-05-17',INTERVAL 80 YEAR), NOW()) FROM DUAL; 
-- 6. 相差时间
SELECT TIMEDIFF('10:11:11', '14:15:16') FROM DUAL; -- -04:04:05

-- 额外的相关函数
-- YEAR() MONTH() DAY()
SELECT YEAR(NOW()) FROM DUAL; 
SELECT MONTH(NOW()) FROM DUAL; 
SELECT DAY(NOW()) FROM DUAL; 
-- 两个注意点
-- UNIX_TIMESTAMP(); 返回的是 1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP() FROM DUAL; 
-- FROM_UNIXTIME(); 可以把一个unix_timestamp秒数, 转成指定格式的日期
-- 意义: 在实际开发中, 通常使用int来保存一个unix时间戳, 然后使用from_unixtime()进行转换, 这是非常有实用价值的;
SELECT FROM_UNIXTIME(1702535284,'%Y-%m-%d %H:%i:%s') FROM DUAL; 

加密和系统函数

-- 演示加密和系统函数
-- 1. USER() 查询用户; 以及登陆的IP
SELECT USER() FROM DUAL; -- 用户@IP地址; root@localhost
-- 2. DATABASE() 查询当前使用数据库的名称; 
SELECT DATABASE() FROM DUAL; 
-- 3. MD5(str) 为字符串算出一个MD5 32的字符串, 常用(用户密码)加密
-- root密码是hsp -> 加密md5 -> 在数据库中存放的是加密后的密码; 
SELECT MD5('175511') FROM DUAL; 
SELECT LENGTH(MD5('hsp')) FROM DUAL; 

-- 演示用户表
CREATE TABLE hsp_user(
	id INT, 
	`name` VARCHAR(32) NOT NULL DEFAULT '', 
	pwd CHAR(32) NOT NULL DEFAULT ''); 

INSERT INTO hsp_user VALUES(100,'hk',MD5('hk')); 
SELECT * FROM hsp_user; 
-- 查询用户
SELECT * FROM hsp_user WHERE `name`='hk' AND pwd = MD5('hk'); 
-- 4. PASSWORD(str) -- 加密函数; 8以上版本已经移除了; 
-- SELECT PASSWORD('hk') FROM DUAL; 

流程控制函数

-- 流程控制函数
SELECT * FROM emp; 
-- 1. IF(expr1,expr2,expr3)
SELECT IF(TRUE,'北京','上海');
-- 2. IFNULL(expr1,expr2); 如果expr1不为空,则返回expr1, 否则返回expr2
SELECT IFNULL(NULL,'hk');
-- 3. select CASE  -- 类似于多分枝
-- 	WHEN when_value THEN
-- 		statement_list
-- 	ELSE
-- 		statement_list
-- END CASE;
SELECT CASE 
	WHEN TRUE THEN
		'hk'
	ELSE
		'kk'
END;
-- 具体实践; 
-- 1. 查询emp表, 如果comm是null,则显示0.0
-- 判断是否为NULL, 要使用is null, 判断不为空 使用is not
SELECT ename, IF(comm IS NULL,0.0,comm) FROM emp;
SELECT ename, IFNULL(comm,0.0) FROM emp;
-- 2. 如果emp表的job是clerk则显示职员, 如果是manager则显示经理,
-- 如果是salesman则显示销售人员,其他正常显示
SELECT ename, (SELECT CASE 
	WHEN job = 'CLERK' THEN
		'职员'
	WHEN job = 'MANAGER' THEN
	  '经理'
	WHEN job = 'SALESMAN' THEN
    '销售人员'
	ELSE
		job
END) AS 'job' FROM emp; 

单表查询增强

-- 单表查询; 查询加强
-- 1. 使用where子句, 如何查询1992.1.1后入职的员工
-- 说明: 在mysql中, 日期类型可以直接比较; 
SELECT * FROM emp WHERE hiredate > '1991-01-01'; 

-- 2. 使用like操作符进行模糊查询; 
-- % 表示0个到多个任意字符; _ 表示单个任意字符
-- 如何显示首字母为S的员工姓名和工资
SELECT ename, sal FROM emp WHERE ename LIKE 'S%'; 
-- 如何显示第三个字符为大写O的所有员工的姓名和工资
SELECT ename, sal FROM emp WHERE ename LIKE '__O%'; 
-- 3. 如何显示没有上级的雇员的情况
SELECT * FROM emp WHERE mgr IS NULL; 
-- 4. 查询表结构
DESC emp; 
-- 5. ORDER BY 
-- 使用按照工资的从低到高的顺序, 显示雇员信息
SELECT * FROM emp ORDER BY sal; 
-- 按照部门升序而雇员的工资降序排列, 显示雇员信息; 多个排序条件需要清楚; 
SELECT * FROM emp ORDER BY deptno ASC, sal DESC; 

分页查询

-- 分页查询
-- 按雇员的id号升序取出, 每页显示3条记录, 请分别显示第1页, 第2页, 第3页
-- SELECT... LIMIT start,rows; 表示从start+1行开始取,取出rows行,start从0开始计算
-- 第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), 每页显示记录数
-- 练习
-- 按雇员的empno号降序取出, 每页显示5条记录, 请分别显示第3页, 第5页对应的sql语句
SELECT * FROM emp ORDER BY empno desc LIMIT 20, 5; 

group by的使用

-- 增强 GROUP BY 的使用
-- 1. 显示每种岗位的雇员总数、平均工资
SELECT COUNT(*), AVG(sal), job FROM emp GROUP BY job; 
-- 2. 显示雇员总数, 以及获得补助的雇员数
-- 注意: count(列); 如果该列的值为null, 是不会统计的
SELECT COUNT(*), COUNT(comm) FROM emp; 
-- 老师的一个扩展要求: 统计没有获取到补助的雇员数
SELECT COUNT(*), COUNT(IF(comm IS NULL,1,NULL)) FROM emp; 
SELECT COUNT(*), COUNT(*)-COUNT(comm) FROM emp; 
-- 3. 显示管理者的总人数
SELECT COUNT(DISTINCT mgr) FROM emp; 
-- 4. 显示雇员工资的最大差额; 
-- 思路: max(sal) - min(sal)
SELECT MAX(sal) - MIN(sal) FROM emp; 
SELECT * FROM emp; 

group by的解释

1. 是一种用于结构化查询语言的子句, 用于对结果集进行分组;
2. 当你使用group by子句时, 当你指定一个或多个列名作为分组的依据,数据库将按照这些列中的值 将结果集分成多个组;

注意⚠️: 1、count(); 括号中可以书写表达式; 2、书写顺序: group by; having; order by; limit;

数据分组的综合案例

• 数据分组的总结案例 – 统计各个部门(group by), 并且是大于1000(having)的,并且按照平均工资从高到低排序 ORDER BY – 取出前两行记录; LIMIT

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后面的内容作为后面条件判断的条件来使用;

多表

多表查询

-- 多表查询
-- 显示雇员名, 雇员工资以及所在部门的名字[笛卡尔积]
-- 分析: 雇员名、雇员工资来自emp表; 部门名字来自dept表
-- 需求: 对emp和dept进行查询操作; 
-- 注意: 当我们需要指定某个列时, 需要 表.列表的形式来处理
-- 小技巧: 多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔积
SELECT ename, sal, dname, emp.deptno 
	FROM emp, dept
	WHERE emp.deptno = dept.deptno; 

-- 如何显示部门号为10的部门名、员工名和工资
SELECT ename, sal, dname, emp.deptno 
	FROM emp, dept
	WHERE emp.deptno = dept.deptno AND dept.deptno = 10; 
	
-- 显示各个员工的姓名, 工资, 以及工资的级别; 
-- 思路: 姓名、工资来自emp 13; 工资级别来自salgrade 5
-- 写 sql, 由简单到复杂; 
SELECT ename, sal, grade 
	FROM emp, salgrade 
	WHERE sal BETWEEN losal AND hisal;

多表查询自连接

-- 多表查询之自连接; 
-- 思考题: 显示公司员工名字和他的上级的名字
-- 分析: 员工名字在emp; 上级的名字的名字 emp
-- 员工和上级是通过emp表的mgr列关联; 注意中英文
-- 自连接特点: 1、把同一张表当作两张表来使用 2、需要给表取别名; 表名 表别名 3、列名不明确可以指定列的别名
SELECT worker.ename AS '职员名', boss.ename AS '上级名'
	FROM emp worker, emp boss
	WHERE worker.mgr = boss.empno;

注意⚠️: 多表自连接; 即把同一张表格当作两张表来使用;

Mysql表子查询

• 子查询: 指嵌入在其他sql语句中的select语句, 也叫嵌套查询; 多表子查询: 有多个条件, 通过and连接;

-- mysql表子查询
/*
	请思考: 如何显示于SMITH同一部门的所有员工
	1. 先查询到smith的部门号得到
	2. 把上面的select语句当作一个子查询来使用; 
*/
-- SELECT deptno FROM emp WHERE ename = 'SMITH'; 
-- 扩了起来, 里面只有一行, 也被称为单行子查询
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SMITH');

-- 多行子查询
/*
	 如何查询和部门10的工作相同的雇员的
	 名字、岗位、工资、部门号; 
	 但是不含10号部门自己的雇员
*/
-- 1. 查询到10号部门有哪些工作 
SELECT DISTINCT job FROM emp WHERE deptno = 10; 
-- 2. 把上面查询的结果当作子查询使用
-- 下面语句完整; 
SELECT ename, job, sal, deptno FROM emp
	WHERE job IN (SELECT DISTINCT job FROM emp WHERE deptno = 10) AND deptno <> 10; 

• 子查询作为临时表

-- 子查询作为临时表
-- 查询 商品表
/*
	查询商品表, 先得到各个类别中, 价格最高的商品 max
*/

all和any操作符的使用

-- all和any的使用
-- 1. 请思考: 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号; 
SELECT ename, sal, deptno 
	FROM emp
	WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30); 
	
SELECT ename, sal, deptno 
	FROM emp
	WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); 
-- any的使用
-- 2. 请思考: 显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号; 
SELECT ename, sal, deptno 
	FROM emp
	WHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30); 
SELECT ename, sal, deptno 
	FROM emp
	WHERE sal > (SELECT MIN(sal) FROM emp WHERE deptno = 30); 

多列子查询

• 指的是; 查询返回多个列数据的子查询语句;

/*
	如何查询与ALLEN的部门和岗位完全相同的所有雇员(并且不含ALLEN本人)
	(字段1, 字段2, ...) = (select 字段1, 字段2, from ...)
*/
-- 分析1: 得到ALLEN的部门和岗位
SELECT deptno, job FROM emp WHERE ename = 'ALLEN'; 
-- 分析2: 把上面的查询当作子查询来使用, 并且使用多列子查询的语法来进行
SELECT * FROM emp
	WHERE (deptno, job) = (SELECT deptno, job FROM emp WHERE ename = 'ALLEN') 
	AND ename != 'ALLEN';  

多列子查询练习

-- 多列子查询的练习
/*
	查找每个部门工资高于本部门平均工资的人的资料
	这里要用到数据查询的小技巧, 把一个子查询当作一个临时表使用
*/
-- 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 * FROM dept; 
SELECT COUNT(*), deptno FROM emp GROUP BY deptno;  
 
SELECT dname, dept.deptno, loc, temp.per_sum AS '人数'
	FROM dept, (SELECT COUNT(*) AS per_sum, deptno FROM emp GROUP BY deptno) temp
	WHERE dept.deptno = temp.deptno; 
-- 还有一种写法; 表.* 表示将该表所有列都显示出来
-- 注意: 在多表查询中, 当多个表的列不重复时,才可以直接写列名
SELECT temp.*, dname,loc
	FROM dept, (SELECT COUNT(*) AS per_sum, deptno FROM emp GROUP BY deptno) temp
	WHERE dept.deptno = temp.deptno; 

表的复制

相关数据记的使用distinct进行去重操作

-- 表的复制
-- 为了对某个sql语句进行效率测试, 我们需要海量数据时, 可以使用此表创建海量数据
CREATE TABLE my_tab01(
	id INT, 
	`name` VARCHAR(32), 
	sal DOUBLE, 
	job VARCHAR(32),
	deptno INT); 
DESC my_tab01; 
-- 演示如何自我复制
-- 1. 先把emp表的记录复制到my_tab01
INSERT INTO my_tab01
	(id, `name`, sal, job, deptno) 
	SELECT empno, ename, sal, job, deptno FROM emp; 
SELECT * FROM my_tab01; 
-- 2. 自我复制
INSERT INTO my_tab01
	SELECT * FROM my_tab01; 
/*
	如何删除掉一张表中的重复记录
	1. 先创建一张表my_tab02, 
	2. 让my_tab02有重复的记录
*/
CREATE TABLE my_tab02 LIKE emp; -- 把emp表的结构(列), 复制到my_tab02; 
INSERT INTO my_tab02 SELECT * FROM emp; -- 插入两次,会出现重复纪律的表
SELECT * FROM my_tab02; 
/*
	考虑去重的问题; 
	思路: 
	1. 先创建一张临时表my_tmp, 该表的结构和my_tab02一样
	2. 把my_tab02的记录 通过 DISTINCT 关键字处理后 把记录复制到my_tmp
	3. 清除掉 my_tab02记录
	4. 把my_tmp表的记录复制到my_tab02
	5. drop掉临时表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 my_tab02; 
-- 4. 把my_tmp表的记录复制到my_tab02
INSERT INTO my_tab02 SELECT * FROM my_tmp; 
-- 5. drop掉临时表my_tmp
DROP TABLE my_tmp; 
SELECT * FROM my_tab02; 

合并查询

• 把多条语句的查询结果最后合在一起;

-- 合并查询
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 ALL
SELECT 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条记录

外表连接

1. 左外连接看左边那张表;
2. 右外连接看右边那张表;
3. 外连接肯定需要两张表在某一个字段上有所联系;
   通过on关键字来书写两张表之间的条件

-- 外表连接
/*
	例如:列出部门名称和这些部门的员工名称和工作
	同时要求 显示出那些没有员工的部门
*/
-- 创建stu
CREATE TABLE stu(
	id INT, 
	`name` VARCHAR(32)); 
INSERT INTO stu VALUES(1, 'jack'), (2, 'tom'),(3, 'kitty'),(4, 'nono');
SELECT * FROM stu; 

-- 创建exam
CREATE TABLE exam(
	id INT, 
	grade INT); 
INSERT INTO exam VALUES(1, 56), (2, 76),(11, 8);
SELECT * FROM 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;
	 
/*
	课堂练习
	列出部门名称和这些部门的员工信息(名字和工作)
	同时列出那些没有员工的部门名
*/
-- 1. 使用左外连接实现; 把左表没有完全匹配的显示出来
SELECT dname, ename, job FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno;   
-- 2. 使用右外连接实现; 
SELECT dname, ename, job FROM emp RIGHT JOIN dept ON dept.deptno = emp.deptno;   
-- 总结: 在实际的开发中,绝大多数情况下使用的是前面学过的连接; 

Mysql约束

主键的使用以及相关细节

-- 主键使用
/*
	细节
	1. PRIMARY KEY不能重复而且不能为null
	2. 一张表最多只能🈶一个主键,但可以是复合主键; 
	3. 主键的定义方式有两种: 
		- 直接在字段名后指定: 字段名 PRIMARY KEY
		- 在表定义最后写 PRIMARY KEY(列名)
	4. 使用desc 表名,可以看到
	5. 在实际开发中,每个表往往都会设计一个主键
*/
CREATE TABLE t10(
	id INT PRIMARY KEY, -- 表示id列是主键
	`name` VARCHAR(32), 
	email VARCHAR(32)); 
-- !!!主键列的值是不可以重复的
INSERT INTO t10 VALUES(1, 'jack', 'hello@kang.com'); 
INSERT INTO t10 VALUES(1, 'kang', 'hello@kang.com'); -- 报错

DESC t10; 

UNIQUE

-- UNIQUE 唯一; 
/*
	细节
	1. 如果没有指定not null, 则unique字段可以有多个null
	2. 一张表中可以有多个unique; 
*/
CREATE TABLE t11(
	id INT UNIQUE, -- 表示id列是不可以重复的
	`name` VARCHAR(32), 
	email VARCHAR(32)); 
INSERT INTO t11 VALUES(1, 'jack', 'hello@kang.com'); 
INSERT INTO t11 VALUES(1, 'kang', 'hello@kang.com'); -- 报错

外键约束

-- 外键的使用
-- 创建 主表 my_class
CREATE TABLE my_class(
	id INT PRIMARY KEY, 
  `name` VARCHAR(32) NOT NULL DEFAULT ''); 
-- 创建从表 my_stu
CREATE TABLE my_stu(
	id INT PRIMARY KEY, -- 学生编号
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	class_id INT, 
	-- 下面指定外键关系
	FOREIGN KEY(class_id) REFERENCES my_class(id)); 

-- 测试数据
INSERT INTO my_class VALUES(100, 'java'), (200, 'web'); 
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);  -- 失败,因为300班级不存在;  
SELECT * FROM my_stu;
-- 一旦建立主外键的关系, 数据不能随意删除了
DELETE FROM my_class WHERE id = 100; -- 删除失败; 

check约束

-- check约束
-- 测试
CREATE TABLE t12(
	id INT PRIMARY KEY, 
	`name` VARCHAR(32),
	sex VARCHAR(6) CHECK (sex IN('man', 'woman')), 
	sal DOUBLE CHECK (sal > 1000 AND sal < 2000)
	); 

INSERT INTO t12 VALUES(1, 'hk', 'man', 1500); 
SELECT * FROM t12; 

注意⚠️: check()在括号里面写的是表达式;

外键约束的综合练习题

/*
	现有一个商店的数据库shop_db，记录客户及其购物情况，由下面三个表组成:
		商品goods(商品号goods_id，商品名goods_name，单价unitprice，商品类别category,供应商provider);
	客户customer(客户号customer id,姓名name,住址address,电邮email性别sex,身份证card Id);购买purchase(购买订单号order_id，客户号customer_id,商品号goods_id,购买数量nums);
	1. 建表，在定义中要求声明[进行合理设计]:
		(1)每个表的主外键;
		(2)客户的姓名不能为空值;
		(3)电邮不能够重复;
		(4)客户的性别[男|女]check 枚举..
		(5)单价unitprice 在1.0- 9999.99之间check
*/
CREATE DATABASE shop_db; 

-- 商品表 goods
CREATE TABLE goods(
	goods_id INT PRIMARY KEY, 
	goods_name VARCHAR(64) NOT NULL DEFAULT '', 
	unitprice DECIMAL(10, 2) NOT NULL DEFAULT 0, -- 默认就是INT类型;
	CHECK(unitprice >= 1.0 AND unitprice <= 9999.99),
	category INT NOT NULL DEFAULT 0, 
	provider VARCHAR(64) NOT NULL DEFAULT ''); 

-- 客户表 customer
CREATE TABLE customer(
	customer_id CHAR(8) PRIMARY KEY, 
	`name` VARCHAR(64) NOT NULL DEFAULT '', 
	address VARCHAR(64) NOT NULL DEFAULT '', 
	email VARCHAR(64) UNIQUE NOT NULL, 
	sex ENUM('男', '女') NOT NULL, -- 这里使用的是枚举类型, 是生效的,
	card_id CHAR(18)); 
	
-- 购买表 purchase
CREATE TABLE purchase(
	order_id INT UNSIGNED PRIMARY KEY, 
	customer_id CHAR(8) NOT NULL DEFAULT '', -- 外键约束在后
	goods_id INT NOT NULL DEFAULT 0, -- 外键约束在后
	nums INT NOT NULL DEFAULT 0, 
	FOREIGN KEY(customer_id) REFERENCES customer(customer_id), 
	FOREIGN KEY(goods_id) REFERENCES goods(goods_id)); 

自增长的使用

-- 演示自增长的使用
-- 创建表
CREATE TABLE t13(
	id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT '', 
	`name` VARCHAR(32) NOT NULL DEFAULT ''); 
DESC t13; 
-- 测试自增长的使用
-- 没有特意表明id的值, 其会自己自动设置
-- 1. 第一种形式
INSERT INTO t13 VALUES(NULL, 'kk@qq.com', 'tom'); 
-- 2. 第二种形式
INSERT INTO t13 (email, `name`) VALUES('hk@qq.com', 'hk'); 
SELECT * FROM t13; 

索引

/*
	在没有创建索引前, emp.ibd文件大小是524M
	创建索引后, emp.ibd文件大小是655M[索引本身也会占用空间]
*/
/*
	解释:
	1. empno_index; 索引名称
	2. ON emp(empno); 表示在emp表的empno列创建索引; 
*/
CREATE INDEX empno_index ON emp(empno); 
SELECT * FROM emp WHERE empno = 1234456; -- 创建索引后, 检索数据巨大提升; 
-- 注意: 创建索引后, 只对创建了索引的列有效; 

创建索引

-- 演示索引的使用
CREATE TABLE t14(
	id INT, 
	`name` VARCHAR(32)); 
-- 查询表是否有索引
SHOW INDEXES FROM t14; 
-- 1. 添加索引
-- 添加唯一索引; 
CREATE UNIQUE INDEX id_index ON t14 (id); 
-- 添加普通索引
CREATE INDEX id_index ON t14 (id); 
/*
	如何选择
	- 如果某列的值不会重复的, 则优先使用unique索引,否则使用普通索引
	- unique索引,即唯一索引
*/
--  添加普通索引方式2
ALTER TABLE t14 ADD INDEX id_index(id); 
-- 添加主键索引
CREATE TABLE t15(
	id INT PRIMARY KEY, -- 添加主键索引的方式1
	`name` VARCHAR(32)); 
ALTER TABLE t15 ADD PRIMARY KEY (id); -- 方式2

删除索引、修改索引、查询索引

SHOW INDEXES FROM t14; 
-- 删除索引
DROP INDEX id_index ON t14; 
-- 删除主键索引
ALTER TABLE t15 DROP PRIMARY KEY; 
-- 修改索引: 先删除、再添加新的索引; 

-- 查询索引
-- 方式1
SHOW INDEX FROM t14; 
-- 方式2
SHOW INDEXES FROM t14; 
-- 方式3
SHOW KEYS FROM t14; 
-- 方式4
DESC t14; -- 信息没有前面的全面

事务

注意⚠️: DML语句; 数据的增删改; – 数据库中90%都是查询操作;

事务的相关操作

-- 事务
-- 事务的重要概念和具体操作
-- 看一个图
-- 演示; 创建一张测试表
CREATE TABLE t16(
	id INT, 
	`name` VARCHAR(32)); 
-- 1. 开始一个事务
START TRANSACTION; 
-- 2. 设置保存点
SAVEPOINT a; 
-- 3. 执行dml操作
INSERT INTO t16 VALUES(100, 'hk'); 
SELECT * FROM t16; 
SAVEPOINT b; 
-- 4. 执行dml操作
INSERT INTO t16 VALUES(200, 'jack'); 
-- 5. 回退到b
ROLLBACK TO b; 
-- 继续回退到a
ROLLBACK TO a; 
ROLLBACK; -- 表示直接回退到事务开始的状态; 
COMMIT; 

一张图了解事务

事务隔离的相关操作

-- 演示mysql的事务隔离级别
-- 1. 开了两个mysql控制台
-- 2. 查看当前mysql的隔离级别
SELECT @@transaction_isolation;
-- mysql> SELECT @@transaction_isolation;
-- +-------------------------+ 
-- | @@transaction_isolation |
-- +-------------------------+
-- | REPEATABLE-READ         |
-- +-------------------------+
-- 1 row in set (0.00 sec)

-- 3. 把其中一个控制台的隔离级别设置Read UNCOMMITTED
-- 设置隔离级别的具体语句; SET SESSION TRANSACTION ISOLATION LEVEL
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
-- 隔离级别是和事务相关的, 两边都要开启一个事务; 

-- 4. 创建表
CREATE TABLE `account`(
	id INT, 
	`name` VARCHAR(32), 
	money INT); 
	
	-- 加锁是一个事务操作时另一个事务不能操作该数据库; 
	
-- 隔离的相关指令
-- 1. 查看当前会话的隔离级别; 
SELECT @@transaction_isolation;
-- 2. 查看系统当前隔离级别; 
SELECT @@global.transaction_isolation;
-- 3. 设置当前会话隔离级别; 
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 
-- 4. 设置系统当前隔离级别; 
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的隔离级别]; 

表类型和存储引擎

-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES;
-- innodb; 存储引擎, 前面一直使用过的; 
-- 1、支持事务; 2、支持外键; 3、支持行级锁; 

-- myisam 存储引擎;  
CREATE TABLE t17(
	id INT, 
	`name` VARCHAR(32)) ENGINE MYISAM; 
-- 1、添加速度快; 2、不支持外键和事务; 3、支持表级锁

-- memory 存储引擎
-- 1、数据存储在内存中[关闭了Mysql服务, 数据丢失, 但是表结构还在]
-- 2、执行速度很快; 3、默认支持索引(hash表)
CREATE TABLE t18(
	id INT, 
	`name` VARCHAR(32)) ENGINE MEMORY; 

-- 通过指令修改引擎 
ALTER TABLE t18 ENGINE = INNODB; 

视图

视图的使用

-- 视图的使用
/*
	要求: 创建一个视图emp_view01, 只能查询emp表的(empno、ename、job和deptno)信息
*/
-- 创建视图
CREATE VIEW emp_view01 AS SELECT empno, ename, job, deptno FROM emp; 
-- 查看视图
SELECT * FROM emp_view01; 
SELECT empno, job FROM emp_view01; 
-- 修改视图; 更新成新的视图
-- alter VIEW 视图名 AS SELECT 语句; 
-- 查看创建视图的指令; 
SHOW CREATE VIEW emp_view01; 
-- 删除视图
DROP VIEW emp_view01; 

视图的练习

-- 视图练习
/*
	针对emp、dept、salgrade三张表, 
	创建一个视图emp_view03, 可以显示雇员编号,雇员名,雇员部门名称和薪水级别
	即 使用三张表,构建一个视图
	
	分析:
	- 使用三表联合查询, 得到结果[三表查询至少需要两个查询条件才不会出现笛卡尔积];
  - 将得到的结果, 构建成视图
*/
CREATE VIEW emp_view03 
	AS
	SELECT empno, ename, dname, grade
		FROM emp, dept, salgrade
		WHERE emp.deptno = dept.deptno AND (sal BETWEEN losal AND hisal); 
DESC emp_view03; 
SELECT * FROM emp_view03; 

Mysql管理

Mysql用户管理

/*
	Mysql用户管理
	原因: 1、当我们做项目开发时, 可以根据不同的开发人员, 赋给它们相应的Mysql操作权限
	2. Mysql数据库管理人员(root),根据需要创建不同的用户,赋给相应的权限,供人员使用
*/
-- 1. 创建新的用户
/*
	解释; CREATE USER 'hsp_edu'@'localhost' IDENTIFIED BY '123456'; 
	1. 'hsp_edu'@'localhost' ; 
		- 表示用户的完整信息; 
		- 'hsp_edu'; 用户名, 'localhost' 登陆的IP
	2. 123456; 密码; 但是注意 存放到mysql.user表时, 是经过password(‘123456’)加密后的密码; 
*/
CREATE USER 'hsp_edu'@'localhost' IDENTIFIED BY '123456';
SELECT * FROM mysql.user;  
SELECT `host`, `user` FROM mysql.user; 
-- 2. 删除用户
DROP USER 'hsp_edu'@'localhost'; 
-- 3. 登录; 参照sqlyog登录

-- 修改自己密码; 没问题
SET PASSWORD = PASSWORD('abcdef'); 
-- 修改其他人的密码, 需要权限; 
-- root用户修改 'hsp_edu'@'localhost'密码,是可以成功的; 权限够; 

演示用户权限管理

-- 演示 用户权限的管理
-- 创建用户 shunping 密码 123
CREATE USER 'shunping'@'localhost' IDENTIFIED BY '123'; 
-- 使用root用户创建testdb, 表news
CREATE DATABASE testdb; 
CREATE TABLE news02(
	id INT, 
	content VARCHAR(32)); 
-- 添加一条测试数据
INSERT INTO news02 VALUES(100, '北京新闻'); 
SELECT * FROM news02; 

-- 给 shunping 分配查看news表 和 添加news的权限
GRANT SELECT, INSERT ON hsp_db01.news02 TO 'shunping'@'localhost'; 
-- 添加update权限
GRANT UPDATE ON hsp_db01.news02 TO 'shunping'@'localhost'; 
-- 修改shunping的密码为abc
SET PASSWORD FOR  'shunping'@'localhost' = PASSWORD('abc'); 
-- 回收shunping用户在testdb.news表的所有权限; 
REVOKE SELECT, UPDATE, INSERT ON hsp_db01.news02 FROM 'shunping'@'localhost'; 
REVOKE ALL ON hsp_db01.news02 FROM 'shunping'@'localhost'; 
-- 删除 shunping
DROP USER 'shunping'@'localhost'; 

作业

-- 作业2
-- 2. 写出查看DEPT表和EMP表的结构的sql语句
DESC dept; 
DESC emp; 
-- 3.使用简单查询语句完成:
-- (1)显示所有部门名称。
SELECT dname FROM dept; 
-- (2)显示所有雇员名及其全年收入13月(工资+补助),并指定列别名"年收入"
-- 注意: 某些数值为NULL需要进行判空操作
SELECT ename, (sal+IFNULL(comm,0)) * 13 AS "年收入" FROM emp; 
-- 4.限制查询数据。
-- (1)显示工资超过2850的雇员姓名和工资。
SELECT ename, sal FROM emp WHERE sal > 2850; 
-- (2)显示工资不在1500到2850之间的所有雇员名及工资。
SELECT ename, sal FROM emp WHERE sal < 1500 OR sal > 2850; 
-- (3)显示编号为7566的雇员姓名及所在部门编号。
SELECT ename, deptno FROM emp WHERE empno = 7566;  
-- (4)显示部门10和30中工资超过1500的雇员名及工资。
SELECT ename, deptno FROM emp WHERE (deptno = 10 OR deptno = 30) AND sal > 1500; 
-- (5)显示无管理者的雇员名及岗位。 
SELECT ename, job FROM emp WHERE mgr IS NULL; 
-- 5.排序数据。
-- (1)显示在1991年2月1日到1991年5月1日之间雇用的雇员名,岗位及雇佣日期,并以雇佣日期进行排序。
SELECT ename, job, hiredate FROM emp 
	WHERE hiredate >= '1991-02-01' AND hiredate <= '1991-05-01'
	ORDER BY hiredate; 
-- (2)显示获得补助的所有雇员名,工资及补助,并以工资降序排序
SELECT ename, sal, comm FROM emp 
	ORDER BY sal DESC; 

-- 根据: emp员工表 写出正确SQL
-- 1.选择部门30中的所有员工.
SELECT * FROM emp WHERE deptno = 30; 
-- 2.列出所有办事员(CLERK)的姓名，编号和部门编号. 
SELECT ename, empno, deptno FROM emp WHERE job = 'CLERK'; 
-- 3.找出佣金高于薪金的员工.
SELECT * FROM emp WHERE IFNULL(comm,0) > sal; 
-- 4.找出佣金高于薪金60%的员工.
SELECT * FROM emp WHERE IFNULL(comm,0) > sal * 0.6; 
-- 5.找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料.
SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') 
	OR (deptno = 20 AND job = 'CLERk');
-- 6.找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),还有既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料. 
SELECT * FROM emp WHERE (deptno = 10 AND job = 'MANAGER') 
	OR (deptno = 20 AND job = 'CLERk')
	OR (job != 'MANAGER' AND job != 'CLERk' AND sal >= 2000); 
-- 7.找出收取佣金的员工的不同工作. 
SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL; 
-- 8.找出不收取佣金或收取的佣金低于100的员工.
SELECT * FROM emp WHERE comm IS NULL OR IFNULL(comm,0) < 100; 
-- 9.找出各月倒数第3天受雇的所有员工.
-- LAST_DAY(date); 可以返回该日期所在月份的最后一天
-- LAST_DAY(date)-2; 得到日期所在月份的倒数第3天;
SELECT * FROM emp WHERE LAST_DAY(hiredate)-2 = hiredate; 
-- 10.找出早于12年前受雇的员工. (入职时间超过12年)
SELECT * FROM emp WHERE DATE_ADD(hiredate,INTERVAL 12 YEAR) < NOW(); 
-- 11.以首字母小写的方式显示所有员工的姓名.
SELECT CONCAT(LCASE((SUBSTRING(ename, 1, 1))), SUBSTRING(ename, 2)) FROM emp; 
-- 12.显示正好为5个字符的员工的姓名.
SELECT * FROM emp WHERE LENGTH(ename) = 5; 

-- 6.根据: emp员工表 写出正确SQL 
-- 13.显示不带有"R"的员工的姓名.
SELECT * FROM emp WHERE ename NOT LIKE '%R%';
-- 14.显示所有员工姓名的前三个字符.
SELECT LEFT(ename,3) FROM emp; 
-- 15.显示所有员工的姓名,用a替换所有"A"
SELECT REPLACE(ename,'A','a') FROM emp; 
-- 16.显示满10年服务年限的员工的姓名和受雇日期. 
SELECT ename, hiredate FROM emp WHERE DATE_ADD(hiredate,INTERVAL 10 YEAR) <= NOW(); 
-- 17.显示员工的详细资料,按姓名排序.
-- 18.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
SELECT ename, hiredate FROM emp ORDER BY hiredate;  
-- 19.显示所有员工的姓名、工作和薪金,按工作降序排序,若工作相同则按薪金排序.
-- 排序的时候可以根据多个条件来进行排序操作
SELECT ename, job, sal FROM emp ORDER BY job DESC, sal;  
-- 20.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面. 
SELECT ename, CONCAT(YEAR(hiredate),'-',MONTH(hiredate)) FROM emp
	ORDER BY MONTH(hiredate), YEAR(hiredate); 
-- 21.显示在一个月为30天的情况所有员工的日薪金,忽略余数.
SELECT ename, FLOOR(sal/30), sal/30 FROM emp; 
-- 22.找出在(任何年份的)2月受聘的所有员工。
SELECT * FROM emp WHERE MONTH(hiredate) = 2; 
-- 23.对于每个员工,显示其加入公司的天数.
SELECT ename, DATEDIFF(NOW(),hiredate) FROM emp; 
-- 24.显示姓名字段的任何位置包含"A"的所有员工的姓名. 
SELECT * FROM emp WHERE ename LIKE '%A%'; 
-- 25.以年月日的方式显示所有员工的服务年限.(大概)
/*
	思路1; 先求出工作了多少天
*/
SELECT ename, FLOOR(DATEDIFF(NOW(),hiredate)/365) AS '工作年', 
	FLOOR((DATEDIFF(NOW(),hiredate)%365)/31) AS '工作月', 
	DATEDIFF(NOW(),hiredate) % 31 AS '工作天'
	FROM emp; 

-- 作业5; 
-- 本章作业
-- 7.根据: emp员工表，dept部门表，工资 = 薪金sal + 佣金 comm 写出正确SQL homework04.sql10min
-- (1).列出至少有一个员工的所有部门
/*
	思路: 先查出各个部门有多少人, 使用having子句过滤
*/
SELECT COUNT(*) AS c, deptno FROM emp
	GROUP BY deptno HAVING c > 1; 
-- (2).列出薪金比“SMITH”多的所有员工。
/*
	思路: 先查出smith的sal(作为子查询。), 然后其他员工的sal大于smith即可
*/
SELECT sal FROM emp WHERE ename = 'SMITH'; 
SELECT * FROM emp; 
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH'); 
-- (3).列出受雇日期晚于其直接上级的所有员工。
/*
	思路: 先把emp表当作两张表; worker, leader
	条件: 1. worker.hiredate > leader.hiredate
		   2. worker.mgr = leader.empno; 
*/
SELECT worker.ename AS '员工名', worker.hiredate AS '员工入职时间', 
  leader.ename AS '上级名', leader.hiredate AS '上级入职时间' 
  FROM emp worker, emp leader
	WHERE worker.hiredate > leader.hiredate
	AND worker.mgr = leader.empno; 
-- (4).列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
/*
	思路: 这里因为需要显示所有部门, 因此考虑使用外连接(左外连接)
*/
SELECT dname, emp.* FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno; 
-- (5).列出所有“CLERK” (办事员)的姓名及其部门名称。
SELECT ename, dname, job FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno; 
-- (6)列出最低薪金大于1500的各种工作。
/*
	思路: 查询各个部门的最低工资; 
	使用having子句进行过滤
*/
SELECT MIN(sal) AS min_sal, job FROM emp GROUP BY job HAVING min_sal > 1500; 
-- (7).列出在部门“SALES”(销售部)工作的员工的姓名。
SELECT ename, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND dname = 'SALES'; 
-- (8).列出薪金高于公司平均薪金的所有员工。
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp); 

-- 作业6: 根据: emp员工表，dept部门表，工资 = 薪金 +佣金 
-- 列出与“SCOTT”从事相同工作的所有员工。
-- SELECT job FROM emp WHERE ename = 'SCOTT'; 
SELECT * FROM emp 
	WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT') AND ename != 'SCOTT'; 
-- 列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
-- 先查询出30部门的最高工资
SELECT ename, sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); 
-- 列出在每个部门工作的员工数量、平均工资和平均服务期限。
-- 还可以通过format(, 2)保留小数点后面两位
SELECT COUNT(*) AS '部门工作的员工数量', 
	deptno, AVG(sal) AS '部门平均工资',
	AVG(DATEDIFF(NOW(),hiredate)/365) AS '平均服务期限(年)'
	FROM emp GROUP BY deptno; 
-- 列出所有员工的姓名、部门名称和工资。
/*
	思路: 就是emp表和dept联合查询, 连接条件就是 emp.deptno = dept.deptno
*/
-- 列出所有部门的详细信息和部门人数。
/*
	1. 先得到各个部门人数, 把下面的结果看成临时表和dept表的联合查询
*/
SELECT COUNT(*) AS c, deptno FROM emp GROUP BY deptno; 
/*
	2. 多余的信息单独列出来即可; 
*/
SELECT dept.*, tmp.c FROM dept, (SELECT COUNT(*) AS c, deptno FROM emp GROUP BY deptno) tmp
	WHERE dept.deptno = tmp.deptno; 
-- 列出各种工作的最低工资。
SELECT MIN(sal), job FROM emp GROUP BY job; 
-- 列出MANAGER(经理)的最低薪金。
SELECT MIN(sal), job FROM emp WHERE job = 'MANAGER'; 
-- 列出所有员工的年工资,按年薪从低到高排序。
/*
	1. 先得到员工的年工资
*/
SELECT ename, (sal + IFNULL(comm, 0))*12 year_sal FROM emp ORDER BY year_sal; 

-- 作业7
/*
8.设学校环境如下:一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。
现要建立关于系、学生、班级的数据库，关系模式为:
班CLASS (班号classid，专业名subject，系名deptname，入学年份enrolltime，人数num)学生STUDENT (学号studentid，姓名name，年龄age，班号classid)
系 DEPARTMENT(系号departmentid，系名deptname) STUDENT ( 
试用SQL语言完成以下功能:homework05.sql 8101，张三，18,101; 
8102，钱四,16，121;
(2)插入如下数据 003, 004, 005， (1)每个表的主外码。 DEPARTMENT(001, (1)建表，在定义中要求声明: (3)学生姓名不能为空。 (2) deptname是唯一约束。 002，计算机; 中文; 化学; 经济;) 数学; CLASS ( 101，软件，计算机，1995，20; 142，国际金融，经济，1996，14; 141，国际贸易，经济，1997，30; 111，无机化学，化学，1995，29; 102，微电子，计算机，1996，30; 131，现代语言，中文，1996，20; 121，统计数学，数学，1995，20; 112，高分子化学，化学，1996，25; 8110，李可，20，142; 8201，张飞，18，111; 8103，王玲，17，131; 8109，赵叫，18，141; 8105，李飞，19，102; 8305，董庆，19，102; 8302，周瑜，16，112; 8203，王亮，17，111; 8409，赵龙，18，101; 8510，李丽，20，142)
*/
-- 创建表 系 DEPARTMENT(系号departmentid，系名deptname)
CREATE TABLE DEPARTMENT(
	departmentid VARCHAR(32) PRIMARY KEY, 
	deptname VARCHAR(32) UNIQUE NOT NULL); 
	
-- 班CLASS (班号classid，专业名subject，系名deptname，入学年份enrolltime，人数num)
CREATE TABLE `class` (
	classid INT PRIMARY KEY, 
	`subject` VARCHAR(32) NOT NULL DEFAULT '', 
	deptname VARCHAR(32), -- 外键字段, 在表定义后指定
	enrolltime INT NOT NULL DEFAULT 2000, 
	num INT NOT NULL DEFAULT 0, 
	FOREIGN KEY (deptname) REFERENCES DEPARTMENT(deptname)); 

-- 学生STUDENT (学号studentid，姓名name，年龄age，班号classid)
CREATE TABLE hsp_student(
	studentid INT PRIMARY KEY, 
	`name` VARCHAR(32) NOT NULL DEFAULT '', 
	age INT NOT NULL DEFAULT 0, 
	classid INT, -- 外键
	FOREIGN KEY (classid) REFERENCES `class`(classid)); 
	
-- 添加测试数据
INSERT INTO DEPARTMENT VALUES('001', '数学');
INSERT INTO DEPARTMENT VALUES('002', '计算机'); 
INSERT INTO DEPARTMENT VALUES('003', '化学'); 
INSERT INTO DEPARTMENT VALUES('004', '中文'); 
INSERT INTO DEPARTMENT VALUES('005', '经济');
SELECT * FROM department; 

INSERT INTO class VALUES(101, '软件', '计算机', 1995, 20); 
INSERT INTO class VALUES(102, '微电子', '计算机', 1996, 30); 
INSERT INTO class VALUES(111, '无机化学', '化学', 1995, 29); 
INSERT INTO class VALUES(112, '高分子化学', '化学', 1996, 25); 
INSERT INTO class VALUES(121, '统计数学', '数学', 1995, 20); 
INSERT INTO class VALUES(131, '现代语言', '中文', 1996, 20); 
INSERT INTO class VALUES(141, '国际贸易', '经济', 1997, 30); 
INSERT INTO class VALUES(142, '国际金融', '经济', 1996, 14); 
SELECT * FROM class; 

INSERT INTO hsp_student VALUES(8101, '张三', 18, 101); 
INSERT INTO hsp_student VALUES(8102, '钱四', 16, 121); 
INSERT INTO hsp_student VALUES(8103, '王玲', 17, 131); 
INSERT INTO hsp_student VALUES(8105, '李飞', 19, 102); 
INSERT INTO hsp_student VALUES(8109, '赵四', 18, 141); 
INSERT INTO hsp_student VALUES(8110, '李可', 20, 142); 
INSERT INTO hsp_student VALUES(8201, '张飞', 18, 111); 
INSERT INTO hsp_student VALUES(8302, '周瑜', 16, 112); 
INSERT INTO hsp_student VALUES(8203, '王亮', 17, 111); 
INSERT INTO hsp_student VALUES(8305, '董庆', 19, 102); 
INSERT INTO hsp_student VALUES(8409, '赵龙', 18, 101); 
SELECT * FROM hsp_student; 

-- (3)完成以下查询功能
-- 3.1 找出所有姓李的学生。
-- 查表 hsp_student,like
SELECT * FROM hsp_student WHERE `name` LIKE '李%'; 
-- 3.2 列出所有开设超过1个专业的系的名字。
-- 先查询各个系有多少个专业; 
SELECT COUNT(*) AS nums, deptname FROM class GROUP BY deptname HAVING nums > 1; 
-- 3.3 列出人数大于等于30的系的编号和名字。
-- 1. 先查出各个系有多少人; 并过滤
SELECT SUM(num) AS nums, deptname FROM class GROUP BY deptname HAVING nums >= 30; 
-- 2. 将上述表作为临时表,和department联合查询即可
SELECT tmp.*, department.departmentid 	
	FROM department,
	(SELECT SUM(num) AS nums, deptname FROM class GROUP BY deptname HAVING nums >= 30) tmp
	WHERE department.deptname = tmp.deptname; 

-- 3.4 学校又新增了一个物理系, 编号为006
INSERT INTO department VALUES('006', '物理系'); 
-- 3.5 学生张三退学, 请更新相关的表; 
/*
	1. 张三所在班级人数-1
	2. 将张三从学生表删除
	3. 需要使用事务控制
*/
SELECT * FROM hsp_student; 
SELECT * FROM class; 
-- 开启事务
START TRANSACTION; 
-- 张三所在班级的人数-1
UPDATE class  SET num = num-1 
	WHERE classid = (SELECT classid FROM hsp_student WHERE `name` = '张三'); 
DELETE FROM hsp_student WHERE `name` = '张三'; 
-- 提交事务
COMMIT; 

Github地址

更加完整笔记请点击跳转访问: Learn_Mysql笔记