一、数据库和SQL概述
1.1 数据库的好处
① 实现数据持久化
② 使用完整的管理系统统一管理，易于查询
1.2 数据库的概念
① DB
数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。
② DBMS
数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器。
③ SQL
结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言。
1.3 数据库管理系统
常见的数据库管理系统：
MySQL、Oracle、DB2、SqlServer等
1.4 SQL语言概述
SQL的优点：
① 不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL；
② 简单易学；
③ 虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。
1.5 SQL语言分类
① DML（Data Manipulation Language):数据操纵语句，用于添加、删除、修改、查询数据库记录，并检查数据完整性；
② DDL（Data Definition Language):数据定义语句，用于库和表的创建、修改、删除；
③ DCL（Data Control Language):数据控制语句，用于定义用户的访问权限和安全级别。
1.5.1 DML（数据操作语句）
DML用于查询与修改数据记录，包括如下SQL语句：
① INSERT：添加数据到数据库中；
② UPDATE：修改数据库中的数据；
③ DELETE：删除数据库中的数据；
④ SELECT：选择（查询）数据；
⑤ SELECT是SQL语言的基础，最为重要。
1.5.2 DDL（数据定义语句）
DDL用于定义数据库的结构，比如创建、修改或删除数据库对象，包括如下SQL语句：
① CREATE TABLE：创建数据库表；
② ALTER TABLE：更改表结构、添加、删除、修改列长度；
③ DROP TABLE：删除表；
④ CREATE INDEX：在表上建立索引；
⑤ DROP INDEX：删除索引；
1.5.3 DCL（数据控制语句）
DCL用来控制数据库的访问，包括如下SQL语句：
① GRANT：授予访问权限
② REVOKE：撤销访问权限
③ COMMIT：提交事务处理
④ ROLLBACK：事务处理回退
⑤ SAVEPOINT：设置保存点
⑥ LOCK：对数据库的特定部分进行锁定
二、MySQL数据库
2.1 MySQL三层结构
2.1.1 如何查看本地mysql的服务器名
- 按下“win+r”，打开运行面板，输入“services.msc”,回车
- 打开本地服务，鼠标在红框内随意点击一下（让其在活动页面），直接在英文状态打mysql，会自动跳转到指定位置（若无此名称，不会有任何反应）
- 红框中即为mysql服务器名
2.1.2 如何启动和关闭mysql数据库
net start mysqlnet stop mysql
reference
2.2 创建数据库
# 演示数据库的操作# 创建一个名称为victor_db01的数据库。[指令演示]# 使用指令创建数据库#说明:在创建数据库、表的时候，为了规避关键字，可以使用反引号解决CREATE DATABASE 'victor_db01';# 删除数据库指令DROP DATABASE victor_db01;# 创建一个使用utf8字符集的victor_db02数据库CREATE DATABASE victor_db02 CHARACTER SET utf8;# 创建一个使用utf8字符集，并带校对规则的victor_db03数据库CREATE DATABASE victor_db03 CHARACTER SET utf8 COLLATE utf8_bin;# 校对规则utf8_bin区分大小,默认校对规则utf8_general_ci不区分大小写# 下面是一条查询的sql, select表示查询，*表示所有字段，FROM表示从哪个表，# WHERE从哪个字段，NAME = 'tom’查询名字是tomSELECT * FROM t1 WHERE NAME = 'tom'
2.3 查看、删除数据库
# 演示删除和查询数据库# 查看当前数据库服务器中的所有数据库SHOW DATABASES# 查看前面创建的victor_db02数据库的定义信息SHOW CREATE DATABASE victor_db02 # 删除数据库victor_db01DROP DATABASE victor_db01;
2.4 数据库备份与恢复
# 数据库备份,需要root权限，要在Dos下执行，mysqldump指令其实在mysql安装目录in# -B： 用于备份多个数据库，如果没有该选项，mysqldump把第一个名字参数作为数据库名，# 后面的作为表名。使用该选项，mysqldump把每个名字都当作为数据库名。mysqldump -u root -p -B victor_db02 victor_db03 > d:\mysqlsql_commandday01ak.sql# 恢复数据库(注意:进入Mysql命令行再执行,mysql -u root -p)SOURCE d:\mysqlsql_commandday01ak.sql# 第二个恢复方法,直接将bak.sql的内容放到查询编辑器中，执行# 数据库备份,需要root权限，要在Dos下执行,小心转义字符，尽量使用/mysqldump -u root -p victor_db02 t1 t2 > d:\mysqlsql_commandday01ak1.sql
2.5 创建表
#************创建表******************# 注意：变量名如果与mysql关键字冲突，需要用反引号``将变量包住，变量名不区分大小写# id 整形# name 字符串# password 字符串# birthday 日期CREATE TABLE `user`( id INT, `name` VARCHAR(255), `password` VARCHAR(255), `birthday` DATE) CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
2.6 修改表
#************修改表******************CREATE TABLE emp ( id INT, `name` VARCHAR(50), sex CHAR(5), birthday DATE, entry_date DATETIME, job VARCHAR(50), salary FLOAT, `resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB; INSERT INTO emp VALUES(12, 'steven jobs', 'man', '1954-10-10', '1978-09-06 10:10:10', 'CEO', 20000.123, 'I work in apple.inc' ); # 添加image列 ALTER TABLE emp ADD image VARCHAR(100) NOT NULL DEFAULT '' AFTER RESUME;# 修改job列 ALTER TABLE emp MODIFY job VARCHAR(60) NOT NULL DEFAULT '';# 删除sex列ALTER TABLE emp DROP sex;# 修改表名RENAME TABLE emp TO employee;# 修改表的字符集为gbkALTER TABLE employee CHARACTER SET gbk;# 修改列名name为user_nameALTER TABLE employee CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''; DESC employee; -- 显示表结构SELECT * FROM employee; -- 显示表DROP TABLE employee; -- 删除表
三、MySQL数据类型（列类型）
3.1 列类型之int
# 整型测试，使用tinyint来演示范围，表的字符集，校验规则，存储引擎，均使用默认配置# 1．如果没有指定unsinged ,则TINYINT就是有符号 -128~127# 2．如果指定unsinged ,则TINYINT就是无符号0-255CREATE TABLE t2 ( id TINYINT); INSERT INTO t2 VALUES(127); # 成功INSERT INTO t2 VALUES(128); # 失败INSERT INTO t2 VALUES(-127); # 成功SELECT * FROM t2CREATE TABLE t3 ( id TINYINT UNSIGNED);INSERT INTO t3 VALUES(128); # 成功INSERT INTO t3 VALUES(-1); # 失败SELECT * FROM t3
3.2 列类型之bit
#bit类型使用#1. bit(m),m在1~64#2．添加数据范围按照m的位数来确定，比如m = 8表示一个字节0~255#3．显示按照bit#4．查询时,仍然可以按照数字来查询CREATE TABLE t4 (num BIT(8));INSERT INTO t4 VALUES(255);SELECT * FROM t4 WHERE num = 255; # b'11111111'
3.3 列类型之小数型
注意
- 默认的float类型和float（m）都只能存6个数字（包括小数点前后的位数），整数超过6位就被科学计数表示，小数位超出则需要四舍五入，比如123.12345会被保存为123.123；
- float(m,d)：小数点后位数为d，即整数位数为（m-d），整数位超出则整数为(m-d)个999，小数点后位数为(d)个9999，优先满足小数位，不用科学计计数法表示；若小数位超出，则需要四舍五入；比如123456在float（7,4）中表示为999.9999；
# 小数类型使用# deciaml可以存储精度非常高的小数CREATE TABLE t5 ( num1 FLOAT, num2 DOUBLE, num3 DECIMAL(10,4)); # DECIMAL(10,4)规定了存储的值将不会超过10位数字，并且小数点后面有4位数字 INSERT INTO t5 VALUES(123.123, 13.123456789, 123.12);SELECT * FROM t5;
3.4 列类型之字符串
注意
- CHAR（size）里面的的size表示字符数量，最大字符数为255；
- VARCHAR（size）里面的的size表字符数，换算成字符数得根据字符编码集计算，在utf8字符集下可以表示65532/3=21844个字符。在GBK字符集下可以表示65532/2=32766个字符；
# ************字符串类型使用************# CHAR（size）固定长度字符串，最大255字符# VARCHAR (size)：可变长度字符串，最大长度0~65535字节，换算成字符数得根据字符编码集计算# 可变长度字符串最大65532字节【utf8编码最大21844字符，1-3个字节用于记录大小】# 如果表的编码是utf8： varchar(size)，size = (65535-3)/ 3 = 21844字符# 如果表的编码是gbk： varchar(size)， size = (65535-3)/ 2 = 32766字符CREATE TABLE t6 (`name` CHAR(255)); # 成功CREATE TABLE t7 (`name` VARCHAR(32766) CHARSET gbk); # 成功CREATE TABLE t8 (`name` VARCHAR(21844) CHARSET utf8); # 成功
# 字符串的使用细节# char(4)和varchar(4)这个4表示的是字符，而不是字节，不区分字符是汉字还是字母CREATE TABLE t8 (`name` CHAR(4));CREATE TABLE t9 (`name` VARCHAR(4));INSERT INTO t8 VALUES('中文ab');INSERT INTO t9 VALUES('abc中');SELECT * FROM t8; # 中文abSELECT * FROM t9; # abc中
3.5 列类型之日期类型
# ************日期类型使用************CREATE TABLE t10 ( birthday DATE, job_time DATETIME, login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); INSERT INTO t10 (birthday, job_time) VALUES('2022-10-10','2022-10-10 10:10:10');SELECT * FROM t10;
3.6 创建表练习
CREATE TABLE emp ( id INT, `name` VARCHAR(50), sex CHAR(5), birthday DATE, entry_date DATETIME, job VARCHAR(50), salary FLOAT, `resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB; INSERT INTO emp VALUES(12, 'steven jobs', 'man', '1954-10-10', '1978-09-06 10:10:10', 'CEO', 20000.123, 'I work in apple.inc' ); SELECT * FROM emp;
四、增删改查（CRUD）
4.1 Insert语句
#***********insert语句**********CREATE TABLE `goods` ( id INT, goods_name VARCHAR(10), price DOUBLE) CHARACTER SET utf8 COLLATE utf8_general_ci; # 添加数据INSERT INTO `goods`(id, goods_name, price) VALUES(1, 'iphone', 5499);# 添加数据INSERT INTO `goods`(id, goods_name, price) VALUES(2, 'xiaomi', 3299); SELECT * FROM `goods`;
4.1.1 insert语句细节
#insert语句的细节-- 1.插入的数据应与字段（列）的数据类型相同。比如把'abc’添加到int类型会错误INSERT INTO `goods`(id, goods_name, price) VALUES('abc', 'gree', 2000); -- 报错，列类型不匹配-- 2．数据的长度应在列的规定范围内，例如:不能将一个长度为80的字符串加入到长度为40的列中.INSERT INTO `goods`(id, goods_name, price) VALUES(3, 'aaaaaaaaaaaaaa', 1000); -- 报错，字符串长度超过列类型的范围 -- 3．在values中列出的数据位置必须与被加入的列的排列位置相对应。INSERT INTO `goods`(id, goods_name, price) VALUES('abc', 4, 2000); -- 报错，列类型不匹配 -- 4.字符和日期型数据应包含在单引号中。-- 5．列可以插入空值[前提是该字段允许为空]，insert into table value(null)INSERT INTO`goods`(id, goods_name, price) VALUES(5, 'vivo', 1500); -- 6. insert into tab_name (列名..) values (),()，()形式添加多条记录INSERT INTO `goods`(id, goods_name, price) VALUES(6, 'nokia', 2500), (7, 'numbia', 3500); -- 7．如果是给表中的所有字段添加数据，可以不写前面的字段名称INSERT INTO `goods` VALUES(8, 'oneplus', 4000); -- 8．默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错INSERT INTO `goods` (id, goods_name) VALUES(9, 'meizu'); SELECT * FROM `goods`;
4.2 Update语句
#***********update语句**********INSERT INTO `employee` (id, user_name, birthday, entry_date, job, salary, `resume`, image) VALUES(13, '小妖怪', '2000-01-04', '2005-10-01 12:12:12', '巡山', 200.1, '大王叫我来巡山', 'd:/a.jpg'), (14, '老妖怪', '1901-01-04', '1950-10-01 12:12:12', '安排小妖怪', 1500.15, '我是老妖怪', 'd:/b.jpg');-- 1．将所有员工薪水修改为5000元。[如果没有带where条件，会修改所有的记录，因此要小心]UPDATE employee SET salary = 5000;-- 2．将姓名为小妖怪的员工薪水修改为3000元UPDATE employee SET salary = 3000 WHERE user_name = '小妖怪'; -- 3．将老妖怪的薪水在原有基础上增加1000元UPDATE employee SET salary = salary + 1000 WHERE user_name = '老妖怪';
4.2.1 Update语句细节
-- 4. 同时修改多个列UPDATE `employee` SET salary = 4000, job = '军师' WHERE user_name = '老妖怪';
4.3 Delete语句
#***********Delete语句**********-- 删除表中名称为′老妖怪′的记录。DELETE FROM `employee` WHERE user_name = '老妖怪'; -- 删除表中所有记录。一定要小心！！！！！！！！DELETE FROM `employee`;-- 删除表DROP TABLE `employee`;
4.4 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` VALUES(1, '曹操', 89, 78, 90), (2, '张飞', 67, 98, 56), (3, '宋江', 87, 78, 77), (4, '关羽', 88, 98, 90), (5, '赵云', 82, 84, 67), (6, '欧阳锋', 55, 85, 45), (7, '黄蓉', 75, 65, 30); SELECT * FROM `student`;
4.4.1 distinct去重
-- 查询表中所有学生的信息;SELECT * FROM `student`;-- 查询表中所有学生的姓名和对应的英语成绩;SELECT `name`, english FROM `student`;-- 过滤表中重复数据, 使用distinct;SELECT DISTINCT english FROM `student`;-- 要查询的记录，每个字段都相同，才会去重;SELECT `name`, english FROM `student`;
4.4.2 对列进行运算，使用as语句，as可以省略
MySQL中不允许使用列别名作为查询条件
# 在赵云的总分上增加60%SELECT `name`, (chinese+english+math)*1.6 AS total_score FROM student WHERE `name` = '赵云'
-- 统计每个学生的总分;SELECT `name`, (chinese + english + math) FROM `student`;
-- 在所有学生总分加10分的情况SELECT `name`, (chinese + english + math + 10) FROM `student`;
-- 使用别名表示学生分数。SELECT `name`, (chinese + english + math + 10) AS total_score FROM `student`;
-- 使用多个别名表示字段名。SELECT `name` AS '名字', (chinese + english + math + 10) AS total_score FROM `student`;
4.4.3 在where子句中使用的运算
-- 查询姓名为赵云的学生成绩SELECT * FROM `student` WHERE `name` = '赵云'; -- 查询英语成绩大于90分的同学SELECT * FROM `student` WHERE english > 90; -- 查询总分大于200分的所有同学SELECT * FROM `student` WHERE (chinese + english + math) > 200; -- 查询math大于60并且(and) id大于4的学生成绩SELECT * FROM `student` WHERE math > 60 AND id > 4; -- 查询英语成绩大于语文成绩的同学SELECT * FROM `student` WHERE english > chinese; -- 查询总分大于200分并且数学成绩小于语文成绩,的姓赵的学生.SELECT * FROM `student` WHERE (chinese + english + math) > 200 AND math < chinese AND `name` LIKE '赵%';
练习
#-----------------练习------------------------ 1. 查询英语分数在80-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; WHERE math IN (89, 90, 91); -- 3.查询所有姓赵的学生成绩SELECT * FROM `student` WHERE `name` LIKE '赵%'; -- 4.查询数学分>80，语文分>80的同学SELECT * FROM `student` WHERE math > 80 AND chinese > 80;-- 5.查询语文分数在70 - 80之间的同学SELECT * FROM `student` WHERE chinese BETWEEN 70 AND 80; -- 6.查询总分为242,276,191的同学SELECT * FROM `student` WHERE (chinese + english + math) IN (242, 276, 191); -- 7.查询所有姓李或者姓宋的学生成绩SELECT * FROM `student` WHERE `name` LIKE '李%' OR `name` LIKE '宋%'; -- 8.查询数学比语文多1分的同学SELECT * FROM `student` WHERE (math - chinese) >= 1;
4.4.4 使用order by子句排序查询结果
order by通常放在select语句的最后
#----------------order by排序------------------------- 对数学成绩排序后输出【升序】SELECT * FROM `student` ORDER BY math; -- 对总分按从高到低的顺序输出[降序]SELECT `name`, (chinese + english + math) AS `total_score` FROM `student` ORDER BY `total_score` DESC;-- 对姓张的学生成绩排序输出(升序)SELECT `name`, (chinese + english + math) AS `total_score` FROM `student` WHERE `name` LIKE '张%' ORDER BY `total_score`; -- order by语句必须位于结尾
五、MySQL常用函数
5.1 统计函数
5.1.1 count()函数
-- ***********count()************-- 统计一个班级共有多少学生?SELECT COUNT(*) FROM `student`;-- 统计数学成绩大于90的学生有多少个?SELECT COUNT(*) FROM `student` WHERE math > 90; -- 统计总分大于250的人数有多少?SELECT COUNT(*) FROM `student` WHERE (chinese + english + math) > 250; -- COUNT(*)和COUNT(列)的区别 -- count(*)返回满足条件的记录的行数 -- count(列):统计满足条件的某列有多少个，但是会排除列为null的行SELECT COUNT(*) FROM `student`; -- 9SELECT COUNT(math) FROM `student`; -- 8
5.1.2 sum()函数
-- ***********sum()************-- 统计一个班级数学总成绩?SELECT SUM(math) FROM `student`; -- 654-- 统计一个班级语文、英语、数学各科的总成绩SELECT SUM(chinese), SUM(english), SUM(math) FROM `student`; -- 678, 701, 654-- 统计一个班级语远、英语、数学的成绩总和SELECT SUM(chinese + english + math) FROM `student`; -- 2033-- 统计一个班级语文成绩平均分SELECT SUM(chinese) / COUNT(*) FROM `student`; -- 75.33333
5.1.3 avg()函数
-- ***********avg()************-- 求一个班级数学平均分?SELECT AVG(math) FROM `student`; -- 72.6666-- 求一个班级总分平均分SELECT AVG(chinese + english + math) FROM `student`; -- 225.8888
5.1.4 max()/min()函数
-- ***********max/min***********-- 求班级最高分和最低分（数值范围在统计中特别有用)SELECT MAX(chinese + english + math), MIN(chinese + english + math) FROM `student`; -- 276, 170-- 求出班级数学最高分和最低分SELECT MAX(math), MIN(math) FROM `student`; -- 100, 30
5.2 分组统计(重要)
5.2.1 group by + having
emp表
(1). 如何显示每个部门的平均工资和最高工资?
-- 如何显示每个部门的平均工资和最高工资?SELECT AVG(sal), MAX(sal), deptno FROM emp GROUP BY deptno;
(2). 如何显示每个部门的每种岗位的平均工资和最低工资?
-- 显示每个部门的每种岗位的平均工资和最低工资?-- 思路：化繁为简-- 1. 显示每个部门的平均工资和最低工资SELECT AVG(sal), MIN(sal), deptno FROM emp GROUP BY deptno;-- 2. 在1的基础上，显示每个部门的每种岗位的平均工资和最低工资SELECT AVG(sal), MIN(sal), deptno, job FROM emp -- deptno, job可以省略 GROUP BY deptno, job; -- 这里的顺序非常关键
(3). 如何显示平均工资低于2000的部门号和它的平均工资?
-- 显示平均工资低于2000的部门号和它的平均工资//别名?-- 思路：-- 1. 显示各个部门的平均工资SELECT AVG(sal), deptno FROM emp GROUP BY deptno;-- 2. 在1的结果基础上进行过滤，保留 AVG(sal) < 2000结果SELECT AVG(sal) AS avg_sal, deptno FROM emp GROUP BY deptno HAVING avg_sal < 2000;
5.3 字符串函数
5.3.1 charset(), concat(), insert(), instr(), ucase(), lcase(), left(), right(), length(), replace(), strcmp(), substring(), ltrim(), rtrim(), trim()
-- ***********演示字符串相关函数的使用***********-- 1. CHARSET(str)返回字符串字符集SELECT CHARSET(ename) FROM emp;-- 2. CONCAT (string[ ,...]) 连接字符串SELECT CONCAT(ename, ' job is ', job) FROM emp;-- 3. INSTR (STRING, SUBSTRING)返回substring在string中出现的位置,没有返回0-- dual 亚元表，系统表可以作为测试表使用SELECT INSTR('helloworld', 'world') FROM DUAL; -- 字符串开始位置为1，与java、python等编程语言中从0开始不同-- 4. UCASE (string)转换成大写SELECT UCASE(ename) FROM emp;-- 5. LCASE(string)转换成小写SELECT LCASE(ename) FROM emp;-- 6. LEFT (string, LENGTH)从string中的左边起取length个字符SELECT LEFT(ename, 2) FROM emp;-- 7. LENGTH (STRING) string长度[按照字节], utf8:英文字母1字节，汉字3字节SELECT LENGTH('hello') FROM DUAL; -- 5-- 8. REPLACE (str, search_str, replace_str ）在str中用replace_str替换search_str ,没有改变表的内容SELECT ename, REPLACE(job, 'MANAGER', 'driver') FROM emp;-- 9. STRCMP (string1 ,string2 ) 逐字符比较两字串大小，SELECT STRCMP('hello', 'world') FROM DUAL; -- -1-- 10. SUBSTRING(str, position [,LENGTH])从str的position开始【从1开始计算】,取lengthSELECT SUBSTRING('helloworld', 2, 3) FROM DUAL; -- ell-- 11. LTRIM (string )， RTRIM (string )，TRIM() 去除前端空格或后端空格, 或两端空格SELECT TRIM(' hello world ') FROM DUAL;
练习: 以首字母小写的方式显示emp表的所有员工姓名，使用两种方式
#练习:以首字母小写的方式显示所有员工emp表的姓名，使用两种方式# 方式一SELECT ename, CONCAT(LCASE(LEFT(ename, 1)), SUBSTRING(ename, 2)) AS new_name FROM emp;# 方式二SELECT ename, CONCAT(LCASE(SUBSTRING(ename, 1, 1)), SUBSTRING(ename, 2)) AS new_name FROM emp;
5.4 数学函数
5.4.1 ABS(), BIN(), CEILING(), CONV(), FLOOR(), FORMAT(), HEX(), LEAST(), MOD(), RAND()
-- ***********数学相关函数的使用***********-- 1. ABS (num) 绝对值0SELECT ABS(-10) FROM DUAL; -- 10-- 2. BIN(decimal number)十进制转二进制SELECT BIN(10) FROM DUAL; -- 1010-- 3. CEILING(number)向上取整，得到比number大的最小整数 SELECT CEILING(-1.1) FROM DUAL; -- -1-- 4. CONV (number, from base, to base) 进制转换SELECT CONV(8, 10, 2) FROM DUAL; -- 1000-- 5. FLOOR(number) 向下取整，得到比number小的最大整数SELECT FLOOR(-1.1) FROM DUAL; -- -2-- 6. FORMAT(number, decimal places) 保留小数位数SELECT FORMAT(1.12356, 3) FROM DUAL; -- 1.124 -- 7. HEX(DecimalNumber)转十六进制SELECT HEX(20) FROM DUAL; -- 14-- 8. LEAST(number, number2[ ,..]) 求最小值SELECT LEAST(8, 10, 1) FROM DUAL; -- 1-- 9. MOD(numerator ,denominator) 求余SELECT MOD(10, 3) FROM DUAL; -- 1-- 10. RAND([seed]) 返回随机数，其范围为 0<= v <= 1.0， seed变，随机数变，seed不变，随机数不变SELECT RAND(3) FROM DUAL;
5.5 日期函数1
5.5.1 CURRENT_DATE() , CURRENT_TIME(), CURRENT_TIMESTAMP()
-- ***********日期时间相关函数的使用1***********-- CURRENT_DATE() 当前日期SELECT CURRENT_DATE() FROM DUAL; -- 2021-05-05-- CURRENT_TIME() 当前时间SELECT CURRENT_TIME() FROM DUAL; -- 17:38:54-- CURRENT_TIMESTAMP() 当前时间戳SELECT CURRENT_TIMESTAMP() FROM DUAL; -- 2021-05-05 17:39:11# 创建一张测试表CREATE TABLE t1 ( id INT, content VARCHAR(30), send_time DATETIME); INSERT INTO t1 VALUES(1, 'bejing news', CURRENT_TIMESTAMP());SELECT * FROM t1;
5.5.2 DATE(), DATE_ADD(), DATE_SUB(), DATEDIFF()
mes表
#***********日期时间相关函数的使用2***********-- 显示所有留言信息，发布日期只显示日期，不用显示时间.SELECT id, content, DATE(send_time) FROM mes;-- 请查询在15分钟内发布的帖子SELECT * FROM mes WHERE DATE_ADD(send_time, INTERVAL 15 MINUTE) >= NOW();-- 请在mysql的sql语句中求出2011-11-11和1990-1-1相差多少天SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL; -- 7984-- 请用mysgl的sql语句求出你活了多少天?[练习]SELECT DATEDIFF(NOW(), '1990-08-14') FROM DUAL; -- 11222-- 如果你能活80岁，求出你还能活多少天.[练习]# 方法一，误差较大，不推荐SELECT 80 * 365 - DATEDIFF(NOW(), '1990-08-14') FROM DUAL; -- 17978# 方法二-- INTERVAL 80 YEAR : YEAR可以是年月日，时分秒-- '1990-08-14'可以date, datetime, timestampSELECT DATEDIFF(DATE_ADD('1990-08-14', INTERVAL 80 YEAR), NOW()) FROM DUAL; -- 17998
5.6 日期函数2
#***********日期时间相关函数的使用3***********-- YEAR|Month|DAY DATE(datetime)SELECT YEAR(NOW()) FROM DUAL; -- 2021SELECT MONTH(NOW()) FROM DUAL; -- 5SELECT DAY(NOW()) FROM DUAL; -- 5SELECT YEAR('1984-06-04') FROM DUAL; -- 1984-- unix_timestamp(): 返回的是1970-1-1到现在的秒数SELECT UNIX_TIMESTAMP() FROM DUAL; -- 1620224148-- FROM_UNIXTIME(): 可以把一个unix timestamp秒数，转成指定格式的日期SELECT FROM_UNIXTIME(1620224148) FROM DUAL; -- 2021-05-05 22:15:48SELECT FROM_UNIXTIME(1620224148, '%Y-%m-%d') FROM DUAL; -- 2021-05-05SELECT FROM_UNIXTIME(1620224148, '%H:%i:%s') FROM DUAL; -- 22:15:48
5.7 加密函数
-- ***********加密函数和系统函数***********-- USER()查询用户SELECT USER() FROM DUAL; -- 用户@IP地址: root@localhost-- DATABASE()数据厍名称SELECT DATABASE() FROM DUAL; -- db03-- MD5(str)为字符串算出一个MD5 32的字符串，(用户密码)加密, -- 最新的mysql版本中用md5()替代了PASSWORD(str)SELECT MD5('mysql') FROM DUAL; -- 81c3b080dad537de7e10e0987a4bf52e
5.8 流程控制函数(有点绕)
-- ***********流程控制函数***********-- IF(expr1, expr2, expr3)如果expr1为True，则返回expr2，否则返回expr3SELECT IF(FALSE, 'new york', 'tokyo') FROM DUAL; -- tokyo-- IF(expr1, expr2)如果expr1不为空NULL,则返回expr1,否则返回expr2SELECT IFNULL(NULL, 'london') FROM DUAL; -- london -- SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END;[类似多重分支.]-- 如果expr1为TRUE,则返回expr2,如果expr2为t，返回expr4，否则返回expr5SELECT CASE WHEN TRUE THEN 'a' -- a WHEN TRUE THEN 'b' ELSE 'c' END;-- 1．查询emp表，如果comm是null, 则显示0.0 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 = 'CLERK' THEN '经理' WHEN job = 'CLERK' THEN '销售人员' ELSE job END) AS 'job' FROM emp;
六、Select多表查询
emp表
6.1 查询增强
6.1.1 where 加强
-- ***********使用where子句查询加强***********-- 如何查找1992.1.1后入职的员工[在mysgl中，日期类型可以直接比较]SELECT * FROM emp WHERE hiredate > '1992-01-01';-- 如何使用like操作符: %表示0到多个字符, _表示单个字符-- 如何显示首字符为s的员工姓名和工资SELECT ename, sal FROM emp WHERE ename LIKE 's%'; -- 如何显示第三个字符为大写O的所有员工的姓名和工资SELECT ename, sal FROM emp WHERE ename LIKE '__O%'; -- 如何显示没有上级的雇员的情况SELECT * FROM emp WHERE mgr IS NULL; -- 查询表结构DESC emp;
6.1.2 order by加强
-- ***********使用order by语句排序***********-- 如何按照工资的从低到高的顺序，显示雇员的信息SELECT * FROM emp ORDER BY sal ASC;-- 按照部门号升序而雇员的工资降序排列，显示雇员信息SELECT * FROM emp ORDER BY deptno ASC, sal DESC;
6.1.3 分页查询加强
-- ***********分页查询***********-- 按雇员的id号升序取出，每页显示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)，每页显示记录数
练习
-- 按雇员的empno号降序取出，每页显示5条记录。请分别显示第3页，第5页对应的sql语句SELECT * FROM emp ORDER BY empno DESC LIMIT 10, 5; SELECT * FROM emp ORDER BY empno DESC LIMIT 20, 5;
6.1.4 group by加强
-- 显示雇员总数，以及获得补助的雇员数。-- count(列)统计非null的行数SELECT COUNT(*), COUNT(comm) FROM emp; -- 13, 3-- 显示没有获得补助的雇员数-- 方法1SELECT COUNT(IF(comm IS NULL, 1, NULL)) FROM emp; -- 10-- 方法2SELECT COUNT(*) - COUNT(comm) FROM emp; -- 10-- 显示管理者的总人数。mgr为上级编号，代表上级，即管理者SELECT COUNT(DISTINCT mgr) FROM emp; -- 5-- 显示雇员工资的最大差额。SELECT MAX(sal) - MIN(sal) FROM emp; -- 4200
6.1.5 查询增强语句的综合使用
-- ***********增强语句的综合使用***********-- 应用案例: 请统计各个部门(group by)的平均工资(avg),-- 并且是大于1000的(having)，并且按照平均工资从高到低排序(order by)-- 取出前两行记录limit 0, 2SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno HAVING avg_sal >= 1000 ORDER BY avg_sal DESC LIMIT 0, 2;
6.2 多表查询
emp表
dept表
salgrade表
显示雇员名,雇员工资及所在部门的名字【笛卡尔集】
-- 显示雇员名,雇员工资及所在部门的名字【笛卡尔集】# 方法一SELECT ename, sal, dname, emp.deptno FROM emp, dept WHERE emp.deptno = dept.deptno; # 方法二，内连接SELECT emp.ename, emp.sal, dept.dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno
如何显示部门号为10的部门名、员工名和工资
-- 提示:多表查询的条件不能少于表的个数-1，否则会出现笛卡尔集-- 如何显示部门号为10的部门名、员工名和工资# 方法一SELECT ename, sal, dname, emp.deptno FROM emp, dept WHERE emp.deptno = dept.deptno AND emp.deptno = 10; # 方法二，内连接SELECT emp.ename, emp.sal, dept.dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno AND emp.deptno = 10
显示各个员工的姓名，工资，及其工资的级别
# 思路：化繁为简，先查emp，salgrade， 然后再过滤# 方法一SELECT ename, sal, grade FROM emp, salgrade WHERE sal BETWEEN losal AND hisal; # 方法二，内连接SELECT emp.ename, emp.sal, salgrade.grade FROM emp INNER JOIN salgrade ON emp.sal BETWEEN salgrade.losal AND salgrade.hisal
练习: 显示雇员名,雇员工资及所在部门的名字,并按部门排序[降序排].
# 练习:显示雇员名,雇员工资及所在部门的名字,并按部门排序[降序排].# 方法一SELECT ename, sal, dname, emp.deptno FROM emp, dept WHERE emp.deptno = dept.deptno ORDER BY emp.deptno DESC;# 方法二，内连接SELECT emp.ename, emp.sal, emp.deptno, dept.dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno ORDER BY emp.deptno DESC
6.3 自连接
emp表
-- 思考题:显示公司员工和他的上级的名字-- 分析:员工名字在emp,上级的名字也在emp, 员工和上级是通过emp表的mgr列关联-- 自连接的特点 -- 1．把同一张表当做两张表使用-- 2．需要给表取别名，表名 表别名-- 3．列名不明确，可以指定列的别名，列名 as 列的别名SELECT worker.empno, worker.ename AS 'worker', worker.mgr, boss.empno, boss.ename AS 'manager' FROM emp worker, emp boss WHERE worker.mgr = boss.empno;
6.4 子查询
6.4.1 单行子查询
-- 单表子查询-- 请思考:如何显示与SMITH同一部门的所有员工?-- 思路：1．先查询到SMITH的部门号得到-- 2．把上面的select语句当做一个子查询来使用# 方法一SELECT * FROM emp WHERE deptno = ( SELECT deptno FROM emp WHERE ename = 'SMITH' );# 方法二SELECT * FROM emp, (SELECT deptno FROM emp WHERE ename = 'SMITH') AS t WHERE emp.deptno = t.deptno
6.4.2 多行子查询
-- 课堂练习:如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不含10自己的.-- 第一步：SELECT DISTINCT job FROM emp WHERE deptno = 10;-- 第二步：SELECT ename, job, sal, deptno FROM emp WHERE job IN ( SELECT DISTINCT job FROM emp WHERE deptno = 10 ) AND deptno != 10; # 方法二，多行子查询SELECT * FROM emp, (SELECT job FROM emp WHERE deptno = 10) AS t WHERE emp.job = t.job AND emp.deptno != 10
6.4.3 all和any
all
-- all和any的使用-- 请思考:显示工资比部门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
-- 请思考:如何显示工资比部门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);
6.5 多列子查询
-- 多列子查询：(字段1，字段2 ...) = (select子段1，子段2 from ....-- 请思考如何查询与ALLEN的部门和岗位完全相同的所有雇员(并且不含ALLEN本人)-- 第一步：得到ALLEN的部门和岗位SELECT deptno, job FROM emp WHERE ename = 'ALLEN'; -- 第二步：把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配SELECT * FROM emp WHERE (deptno, job) = ( SELECT deptno, job FROM emp WHERE ename = 'ALLEN') AND ename != 'ALLEN' ; # 方法二SELECT * FROM emp, (SELECT job, deptno FROM emp WHERE ename = 'ALLEN') AS t WHERE emp.job = t.job AND emp.deptno = t.deptno AND emp.ename != 'ALLEN'
练习：查询和宋江数学，英语，语文成绩完全相同的学生
SELECT * FROM student WHERE (chinese, english, math) = ( SELECT chinese, english, math FROM student WHERE `name` = '宋江');
6.6 子查询练习
# 1. 查找每个部门工资高于本部门平均工资的人的资料，# 这里要用到数据查询的小技巧，把一个子查询当作一个临时表使用# 第一步：先得到每个部门的部门号和对应的平均工资SELECT AVG(sal) AS avg_sal, deptno FROM emp GROUP BY deptno;# 第二步：把上面的结果当做子查询，和emp进行多表查询SELECT ename, sal, avg_sal, emp.deptno FROM emp, ( SELECT AVG(sal) AS avg_sal, deptno FROM emp GROUP BY deptno) temp WHERE emp.sal > avg_sal AND emp.deptno = temp.deptno;
# 2. 查找每个部门工资最高的人的详细资料# 第一步：先得到每个部门的部门号和对应的最高工资SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno# 第二步：把上面的结果当做子查询，和emp进行多表查询SELECT ename, sal, 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; # 方法二SELECT * FROM emp WHERE sal IN (SELECT MAX(sal)FROM emp GROUP BY deptno)
# 3. 查询每个部门的信息(包括:部门名,编号,地址)和人员数量# 思路:# 第一步：先将人员信息和部门信息关联显示SELECT COUNT(*), deptno FROM emp GROUP BY deptno;-- 还有一种写法，.*表示将该表所有列都显示出来-- 在多表查询中，当多个表的列不重复时，才可以直接写列名# 第二步：然后统计SELECT dept.deptno, dname, loc, person_num FROM dept, ( SELECT COUNT(*) AS person_num, deptno FROM emp GROUP BY deptno) temp WHERE dept.deptno = temp.deptno # 方法二SELECT * FROM dept, (SELECT deptno, COUNT(deptno) AS people FROM emp GROUP BY deptno) AS t WHERE dept.deptno = t.deptno
6.7 表复制和去重
# ***********表的复制***********-- 为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据CREATE TABLE tab01( id INT, `name` VARCHAR(32), sal DOUBLE, job VARCHAR(32), deptno INT); -- 演示如何自我复制-- 1．先把emp表的记录复制到tab01INSERT INTO tab01(id, `name`, sal, job, deptno) SELECT empno, ename, sal, job, deptno FROM emp;-- 2. 将tab01表的记录插入到tab01表INSERT INTO tab01 SELECT * FROM tab01;-- 使用like构造相同的表结构CREATE TABLE tab02 LIKE emp;INSERT INTO tab02 SELECT * FROM emp;-- 如何去重/* 思路 (1)先创建一张临时表temp，该表的结构和tab02一样 (2)把temp的记录通过distinct关键字处理后,把记录复制到temp (3)清除掉tab02记录 (4)把temp表的记录复制到tab02 (5)drop掉临时表temp*/#1CREATE TABLE temp LIKE tab02;#2INSERT INTO temp SELECT DISTINCT * FROM tab02; #3DELETE FROM tab02;#4INSERT INTO tab02 SELECT * FROM temp;#5DROP TABLE temp;
6.8 合并查询
# ***********合并查询***********SELECT ename, sal, job FROM emp WHERE sal > 2500UNION -- 去重SELECT ename, sal ,job FROM emp WHERE job = 'MANAGER'SELECT ename, sal, job FROM emp WHERE sal > 2500UNION ALL -- 不去重SELECT ename, sal ,job FROM emp WHERE job = 'MANAGER'
6.9 外连接
stu表
exam表
# 使用左外连接-- （显示所有人的成绩，如果没有成绩，也要显示该人的姓名和id号,成绩显示为空)-- 即:左边的表(stu)和右表没有匹配的记录，也会把左表的记录显示出来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, sal, job FROM dept LEFT JOIN emp ON emp.deptno = dept.deptno -- 2.使用右外连接实现SELECT dname, ename, sal, job FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno
6.10 内连接
# 使用内连接# inner join(等值连接) 只返回两个表中联结字段相等的行SELECT `name`, stu.id, grade FROM stu inner JOIN exam ON stu.id = exam.id;
七、约束
7.1 primary主键
# ***********约束***********CREATE TABLE t1 ( id INT PRIMARY KEY, -- 表示id列是主键 `name` VARCHAR(32), email VARCHAR(32)); INSERT INTO t1 VALUES(1, 'jack', 'jack@sohu.com');INSERT INTO t1 VALUES(1, 'tom', 'jack@sohu.com'); -- 主键重复，添加失败-- 主键使用的细节讨论-- primary key不能重复而且不能为nul1。INSERT INTO t1 VALUES (NULL, 'tony', 'tony@ohu.com');-- 一张表最多只能有一个主键，但可以是复合主键CREATE TABLE t2( id INT PRIMARY KEY, `name` VARCHAR (32) PRIMARY KEY, # 只能有一个主键 email VARCHAR(32));-- 复合主键CREATE TABLE t3( id INT, `name` VARCHAR (32), email VARCHAR(32), PRIMARY KEY(id, `name`));INSERT INTO t3 VALUES (1, 'tony', 'tony@sohu.com');INSERT INTO t3 VALUES (1, 'tony', 'xx@sohu.com'); -- 复合主键重复，添加失败-- 使用desc表名,可以看到primary key的情况DESC t3
7.2 Unique关键字
# ***********unique***********CREATE TABLE t4( id INT UNIQUE, -- 表示id列是不可以重复的. `name` VARCHAR(32), email VARCHAR(32)); INSERT INTO t4 VALUES (1, 'tony', 'tony@sohu.com');INSERT INTO t4 VALUES (1, 'ammy', 'ammy@sohu.com'); -- id重复，添加失败-- unique使用细节-- 1. 如果没有指定not null, 则unique字段可以有多个nullINSERT INTO t4 VALUES(NULL, 'tom', 'tom@sohu.com');INSERT INTO t4 VALUES(NULL, 'tom', 'tom@sohu.com');-- 2. 一张表可以有多个unique字段CREATE TABLE t5( id INT UNIQUE, `name` VARCHAR(32) UNIQUE， email VARCHAR(32));
7.3 foreign key外键
CREATE TABLE class( id INT PRIMARY KEY, -- 班级编号 `name` VARCHAR(32) NOT NULL DEFAULT ''); CREATE TABLE stu( id INT PRIMARY KEY, -- 学生编号 `name` VARCHAR(32) NOT NULL DEFAULT '', class_id INT, -- 学生所在班级的编号 FOREIGN KEY (class_id REFERENCES class(id));# 测试数据INSERT INTO class VALUES(100, 'java'), (200, 'web');INSERT INTO stu VALUES(1, 'tom', 100), (2, 'jack', 200);INSERT INTO stu VALUES(3, 'bob', 300); -- 这里会失败, 因为300班级不存在
7.4 check
# ***********check***********# mysql 8.0以上的版本生效了CREATE TABLE t5( 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 t5 VALUES(1, 'jack', 'mid', 1); -- 这里会失败
7.5 约束练习
# ***********约束练习***********# goodsCREATE TABLE goods( goods_id INT PRIMARY KEY, goods_name VARCHAR(32) NOT NULL DEFAULT '', unitprice DOUBLE NOT NULL DEFAULT 0.0 CHECK(unitprice >= 1.0 AND unitprice <= 9999.99), category INT NOT NULL DEFAULT 0, provider VARCHAR(32) NOT NULL DEFAULT ''); # customerCREATE TABLE customer( customer_id VARCHAR(32) PRIMARY KEY, `name` VARCHAR(32) NOT NULL DEFAULT '', address VARCHAR(100) NOT NULL DEFAULT '', email VARCHAR(50) UNIQUE NOT NULL DEFAULT '', sex ENUM('man', 'woman') NOT NULL, card_id VARCHAR(100) ); # purchaseCREATE TABLE purchase( order_id INT PRIMARY KEY, customer_id VARCHAR(32) 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));DROP TABLE goodsDROP TABLE customerDROP TABLE purchase
7.6 自增长
# ***********自增长***********CREATE TABLE t6( id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(32) NOT NULL DEFAULT '', `name` VARCHAR(32) NOT NULL DEFAULT ''); INSERT INTO t6 VALUES(NULL, 'jack@gmail.com', 'jack');INSERT INTO t6(email, `name`) VALUES('lsp@qq.com', 'mark');
八、索引
8.1 创建索引
# ***********索引***********CREATE TABLE large_tab( id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(32) NOT NULL DEFAULT '', `name` VARCHAR(32) NOT NULL DEFAULT ''); INSERT INTO large_tab VALUES(NULL, 'jack@gmail.com', 'jack');SELECT * FROM large_tab WHERE id = 100;# 创建索引CREATE INDEX id_index ON large_tab(id);
8.2 索引类型
# ***********索引类型***********-- 如何选择-- 1．如果某列的值，是不会重复的，则优先考虑使用unique索引，否则使用普通索引CREATE TABLE t7( id INT, `name` VARCHAR(32));-- 查询表是否有索引 SHOW INDEXES FROM t7;-- 查询索引 SHOW INDEXES FROM t7;-- 添加唯一索引 CREATE UNIQUE INDEX id_index ON t7(id);-- 添加普通索引-- 方式一：CREATE INDEX id_index ON t7(id);-- 方式二：ALTER TABLE t7 ADD INDEX id_index(id);# 添加主键索引CREATE TABLE t8( id INT, `name` VARCHAR(32)); ALTER TABLE t8 ADD PRIMARY KEY (id);SHOW INDEXES FROM t8# 删除索引DROP INDEX id_index ON t7;# 删除主键索引ALTER TABLE t8 DROP PRIMARY KEY;-- 查询索引-- 方式1SHOW INDEX FROM t8;-- 方式2SHOW INDEXES FROM t8;-- 方式3SHOW KEYS FROM t8;-- 方式4DESC t8;
8.3 练习
***********练习***********# 1. 创建主键索引# 方式一CREATE TABLE `order1` ( id INT PRIMARY KEY NOT NULL DEFAULT 0, goods_name VARCHAR(32) NOT NULL DEFAULT '', customer_id INT NOT NULL DEFAULT 0, nums INT NOT NULL DEFAULT 0);SHOW INDEXES FROM `order1` # 方式二CREATE TABLE `order2` ( id INT NOT NULL DEFAULT 0, goods_name VARCHAR(32) NOT NULL DEFAULT '', customer_id INT NOT NULL DEFAULT 0, nums INT NOT NULL DEFAULT 0);ALTER TABLE `order2` ADD PRIMARY KEY (id);SHOW INDEXES FROM `order2`;#drop index id_index on `order`# 方式三CREATE TABLE `order3` ( id INT UNIQUE NOT NULL DEFAULT 0, goods_name VARCHAR(32) NOT NULL DEFAULT '', customer_id INT NOT NULL DEFAULT 0, nums INT NOT NULL DEFAULT 0);SHOW INDEXES FROM `order3`;
# 2. 创建唯一索引# 方式一CREATE TABLE `menu1` ( id INT UNIQUE NOT NULL DEFAULT 0, menu_name VARCHAR(32) NOT NULL DEFAULT '', cook VARCHAR(32) NOT NULL DEFAULT '', customer_id INT NOT NULL DEFAULT 0, price INT NOT NULL DEFAULT 0);SHOW INDEXES FROM `menu1`;# 方式二CREATE TABLE `menu2` ( id INT NOT NULL DEFAULT 0, menu_name VARCHAR(32) NOT NULL DEFAULT '', cook VARCHAR(32) NOT NULL DEFAULT '', customer_id INT NOT NULL DEFAULT 0, price INT NOT NULL DEFAULT 0);CREATE UNIQUE INDEX id_index ON `menu2`(id);SHOW INDEXES FROM `menu2`;
# 3. 创建普通索引# 方式一CREATE TABLE `sportman1` ( id INT PRIMARY KEY NOT NULL DEFAULT 0, `name` VARCHAR(32) NOT NULL DEFAULT '', specialty VARCHAR(32) NOT NULL DEFAULT '');CREATE INDEX index_name ON sportman1(`name`);SHOW INDEXES FROM sportman1;# 方式二CREATE TABLE `sportman2` ( id INT PRIMARY KEY NOT NULL DEFAULT 0, `name` VARCHAR(32) NOT NULL DEFAULT '', specialty VARCHAR(32) NOT NULL DEFAULT '');ALTER TABLE sportman2 ADD INDEX name_index(`name`);SHOW INDEXES FROM sportman2;
8.4 索引小结
九、事务
9.1 事务操作
# ***********练习***********-- 事务的一个重要的概念和具体操作-- 演示-- 1．创建一张测试表CREATE TABLE t9 ( id INT, `name` VARCHAR(32));-- 2. 开始事务START TRANSACTION;-- 3. 设置保存点SAVEPOINT a;-- 4. 执行dml操作INSERT INTO t9 VALUES(100, 'tom');SELECT * FROM t9;-- 5. 设置保存点SAVEPOINT b;-- 6. 执行dml操作INSERT INTO t9 VALUES(200, 'jack'); -- 7. 回退到bROLLBACK TO b;-- 8. 继续回退到aROLLBACK TO a;ROLLBACK -- 表示直接回退到事务开始的状态.COMMIT
事务细节
9.2 隔离级别
（1）脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
（2）不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
（3）幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A更改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表
9.2.1 读未提交
(1) 产生脏读
(2) 产生不可重复读、幻读
9.2.2 读已提交
(1) 没有产生脏读
(2) 产生不可重复读、幻读
9.2.3 可重复读：没有产生脏读、不可重复读、幻读
9.2.4 可串行化
-- 演示mysql的事务隔离级别-- 1．开了两个mysql的控制台-- 2．查看当前mysql的隔离级别SELECT @@transaction_isolation;-- +-------------------------+-- | @@transaction_isolation |-- +-------------------------+-- | REPEATABLE-READ |-- +-------------------------+-- 3. 把其中一个控制台的隔离级别设置Read uncommittedSET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 4. 创建表CREATE TABLE `account` ( id INT, `name` VARCHAR(32), money INT);INSERT INTO `account` VALUES(100, 'tom', 1000);# 5UPDATE `account` SET money = 800 WHERE id = 100;INSERT INTO `account` VALUES(200, 'jack', 2000);# 6SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;INSERT INTO `account` VALUES(300, 'scott', 8000);UPDATE `account` SET money = 1800 WHERE id = 200;# 7SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;INSERT INTO `account` VALUES(400, 'milan', 6000);UPDATE `account` SET money = 100 WHERE id = 300;# 8SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;INSERT INTO `account` VALUES(500, 'terry', 80000);UPDATE `account` SET money = 900 WHERE id = 300;
reference:
9.3 设置隔离
# 设置隔离级别-- 查看当前会话隔离级别SELECT @@tx_isolation;-- 查看系统当前隔离级别SELECT @@global.tx_isolation;-- 设置当前会话隔离级别SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED-- 设置系统当前隔离级别SET GLOBAL TRANSACTION ISOLATION LEVEL[你设置的级别]
9.4 练习
# 练习# ACREATE TABLE dog ( id INT, `name` VARCHAR(32));START TRANSACTION;INSERT INTO dog VALUES(1, 'a'), (2, 'b');# BSTART TRANSACTION;SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;# AUPDATE dog SET `name` = 'poodle' WHERE id = 2;# CSTART TRANSACTION;SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;# AUPDATE dog SET `name` = 'geden' WHERE id = 1;
9.5 存储引擎
十、视图
10.1 视图原理
-- 视图的使用-- 创建一个视图emp view01，只能查询emp表的(empno、ename，job和 deptno ）信息-- 创建视图CREATE VIEW emp_view01 AS SELECT empno, ename, job, deptno FROM emp; -- 查看视图SELECT * FROM emp_view01;-- 修改视图ALTER VIEW emp_view01 AS SELECT empno, ename, job FROM emp; -- 查看创建视图的指令SHOW CREATE VIEW emp_view01;-- 删除视图DROP VIEW emp_view01;
10.2 视图最佳实践
10.3 练习
# *********练习***********# 针对emp, dept, 和salgrade张三表,创建一个视图emp_view02# 三表联合查询SELECT empno, ename, dname, sal, grade FROM emp, dept, salgrade WHERE emp.deptno = dept.deptno AND (sal BETWEEN losal AND hisal);# 创建视图CREATE VIEW emp_view02 AS SELECT empno, ename, dname, sal, grade FROM emp, dept, salgrade WHERE emp.deptno = dept.deptno AND (sal BETWEEN losal AND hisal);SELECT * FROM emp_view02;
十一、MySql用户管理
11.1 创建、删除用户、修改密码
# 1．创建新的用户-- 解读-- (1)'brook'@'localhost'表示用户的完整信息,'brook'用户名,'localhost’登录的IP-- (2) 1234密码，但是注意存放到mysql.user表时，是password('123456')加密后的密码CREATE USER 'brook'@'localhost' IDENTIFIED BY '1234';SELECT `host`, `user`, 'authentication_string' FROM USER;
# 2.删除用户DROP USER 'brook'@'localhost';
# 3.修改密码, mysql 8.0 以上版本ALTER USER 'root'@'localhost' IDENTIFIED BY 'abcd';ALTER USER 'brook'@'localhost' IDENTIFIED BY 'abcd';
11.2 权限管理
# 练习-- 创建用户genius密码123，从本地登录CREATE USER 'genius'@'localhost' IDENTIFIED BY '123';-- 使用root用户创建testdb库，news表CREATE DATABASE testdb;CREATE TABLE news( id INT, content VARCHAR(32));INSERT INTO news VALUES(1, 'shanghai'), (2, 'beijing');SELECT * FROM news;-- 给用户genius分配权限select, insertGRANT SELECT, INSERT ON testdb.news TO 'genius'@'localhost'-- 给用户genius增加权限updateGRANT UPDATE ON testdb.news TO 'genius'@'localhost'-- 修改genius用户密码为'abcd'ALTER USER 'genius'@'localhost' IDENTIFIED BY 'abcd';-- 回收genius用户权限-- 方式一：REVOKE SELECT, INSERT, UPDATE ON testdb.news FROM 'genius'@'localhost';-- 方式二：REVOKE ALL ON testdb.news FROM 'genius'@'localhost';-- 删除用户geniusDROP USER 'genius'@'localhost';
11.3 权限管理细节
# 说明：用户管理的细节-- 在创建用户的时候，如果不指定Host，则为%，%表示表示所有用户都有连接权限-- create user xxx;CREATE USER jack;SELECT `host`, `user` FROM mysql.user;-- 也可以这样指定-- create user 'xxx'@'192.168.1.%表示xxx用户在192.168.1.*的ip可以登录mysql-- 在删除用户的时候，如果host不是%，需要明确指定'用户'@'host值'CREATE USER 'smith'@'192.168.1.%';DROP USER jack; -- 默认就是DROP USER 'jack'@'名'DROP USER 'smith'@'192.168.1.%';
开始时间：2021.4.30
完结时间：2021.5.13
十二、作业
12.1 作业一
答案：D,B,C# 1. 语句正确SELECT ename, sal*12 "Annual Salary" FROM emp; # 2. 语句正确SELECT ename, sal, comm FROM emp WHERE comm IS NOT NULL; # 3. 语句正确SELECT ename, sal salary FROM emp ORDER BY sal;
DESC dept;DESC emp;
# (1)显示所有部门名称。SELECT dname FROM dept;SELECT * FROM emp;# (2)显示所有雇员名及其全年收入13月(工资+补助),并指定列别名"年收入"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;# 法二SELECT ename, sal FROM emp WHERE NOT (sal >= 1500 AND sal <= 2850); # (3)显示编号为7566的雇员姓名及所在部门编号。SELECT ename, deptno FROM emp WHERE empno = 7566;# (4)显示部门10和30中工资超过1500的雇员名及工资。SELECT ename, sal FROM emp WHERE sal > 1500 AND (deptno = 10 OR deptno = 30);# (5) 显示无管理者的雇员名及岗位。SELECT ename, job FROM emp WHERE mgr IS NULL;
# 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 WHERE comm IS NOT NULL ORDER BY sal DESC;
12.2 作业二
-- 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)，还有既不是经理又不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 ename, comm FROM emp WHERE (comm IS NULL) OR (IFNULL(comm, 0) < 100); -- 9.找出各月倒数第3天受雇的所有员工.(难题）SELECT * FROM emp WHERE LAST_DAY(hiredate) - 2 = hiredate;-- 10.找出早于12年前受雇的员工．(难题）SELECT * FROM emp WHERE DATE_ADD(hiredate, INTERVAL 12 YEAR) < NOW(); -- 11.以首字母小写的方式显示所有员工的姓名.SELECT ename, CONCAT(LCASE(SUBSTRING(ename, 1, 1)), SUBSTRING(ename, 2)) AS new_name FROM emp;-- 12.显示正好为5个字符的员工的姓名.SELECT ename FROM emp WHERE LENGTH(ename) = 5; -- 13.显示不带有"R"的员工的姓名.①SELECT ename FROM emp WHERE INSTR(ename, 'R') = 0;② SELECT ename FROM emp WHERE ename NOT LIKE '%R%'; -- 14.显示所有员工姓名的前三个字符.①SELECT SUBSTRING(ename, 1, 3) FROM emp;② 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.显示员工的详细资料,按姓名排序.SELECT * FROM emp ORDER BY ename; -- 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 FLOOR(sal / 30) FROM emp;-- 22.找出在(任何年份的)2月受聘的所有员工。SELECT * FROM emp WHERE MONTH(hiredate) = 2; -- 23.对于每个员工,显示其加入公司的天数SELECT DATEDIFF(NOW(), hiredate) FROM emp;-- 24.显示姓名字段的任何位置包含"A"的所有员工的姓名.SELECT ename FROM emp WHERE INSTR(ename, 'A') != 0; -- (难题)25.以年,月,日的方式显示所有员工的服务年限．(大概)SELECT FLOOR(DATEDIFF(NOW(), hiredate) / 365) AS 'year', FLOOR(DATEDIFF(NOW(), hiredate) % 365 / 31) AS 'month', FLOOR(DATEDIFF(NOW(), hiredate) % 365 % 31) AS 'day' FROM emp;
12.3 作业三
# 难度上升-- (1)．列出至少有一个员工的所有部门(易错）SELECT COUNT(*) AS c, deptno FROM emp GROUP BY deptno HAVING c > 1; -- (2)．列出薪金比"SM工TH”多的所有员工。SELECT sal FROM emp WHERE ename = 'SMITH'SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH'); -- (3)．列出受雇日期晚于其直接上级的所有员工。（自连接）SELECT worker.ename AS 'worker', worker.hiredate AS 'worker-hiredate', boss.ename AS 'manager', boss.hiredate AS 'boss-hiredate' FROM emp worker, emp boss WHERE worker.mgr = boss.empno AND worker.hiredate > boss.hiredate;-- (4)．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(外连接）SELECT dname, ename, job, sal, emp.deptno FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno -- (5)．列出所有"CLERK”(办事员）的姓名及其部门名称。SELECT ename, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND job = 'CLERK'; -- (6)．列出最低薪金大于1500的各种工作。(易错）SELECT MIN(sal) AS min_sal, job FROM emp GROUP BY job HAVING min_sal > 1500; -- (7)．列出在部门"SALES”(销售部）工作的员工的姓名。SELECT ename FROM emp, dept WHERE emp.deptno = dept.deptno AND dname = 'SALES'; -- (8)．列出薪金高于公司平均薪金的所有员工。（易错，子查询）SELECT ename, sal FROM emp WHERE sal > (SELECT AVG(sal) FROM emp); -- (9)．列出与"SCOTT"从事相同工作的所有员工。SELECT ename FROM emp WHERE job = (SELECT job FROM emp WHERE ename = 'SCOTT') AND ename != 'SCOTT'; -- (10)．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。SELECT ename, sal FROM emp WHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); -- (11)．列出在每个部门工作的员工数量、平均工资和平均服务期限SELECT deptno, COUNT(*) AS nums, AVG(sal), FLOOR(AVG(DATEDIFF(NOW(), hiredate))) FROM emp GROUP BY deptno -- (12)．列出所有员工的姓名、部门名称和工资。SELECT ename, dname, sal FROM emp, dept WHERE emp.deptno = dept.deptno -- (13)．列出所有部门的详细信息和部门人数。-- 方法一：SELECT emp.deptno, dname, loc, COUNT(ename) AS nums FROM emp, dept WHERE emp.deptno = dept.deptno GROUP BY emp.deptno;-- 方法二：SELECT dept.*, temp.c AS nums FROM dept, (SELECT COUNT(*) AS c, deptno FROM emp GROUP BY deptno) temp WHERE dept.deptno = temp.deptno GROUP BY dept.deptno -- (14).列出各种工作的最低工资。SELECT job, MIN(sal) FROM emp GROUP BY job; -- (15).列出MANAGER(经理)的最低薪金。SELECT MIN(sal) FROM emp WHERE job = 'MANAGER'; -- (16)．列出所有员工的年工资，按年薪从低到高排序。SELECT ename,((sal + IFNULL(comm, 0)) * 12) AS year_sal FROM emp ORDER BY year_sal;
12.4 作业四
# (1)(2)CREATE TABLE department( departmentid VARCHAR(32) PRIMARY KEY NOT NULL DEFAULT '', deptname VARCHAR(32) UNIQUE NOT NULL DEFAULT '');INSERT INTO department VALUES ('001', '数学'), ('002', '计算机'), ('003', '化学'), ('004', '中文'), ('005', '经济');CREATE TABLE class( classid INT PRIMARY KEY NOT NULL DEFAULT 0, `subject` VARCHAR(32) NOT NULL DEFAULT '', deptname VARCHAR(32) NOT NULL DEFAULT '', enrolltime INT NOT NULL DEFAULT 0, num INT NOT NULL DEFAULT 0, FOREIGN KEY (deptname) REFERENCES department(deptname));INSERT INTO class VALUES (101, '软件', '计算机', 1995, 20), (102, '微电子', '计算机', 1996, 30), (111, '无机化学', '化学', 1995, 29), (112, '高分子化学', '化学', 1996, 25), (121, '统计数学', '数学', 1995, 20), (131, '现代语言', '中文', 1996, 20), (141, '国际贸易', '经济', 1997, 30), (142, '国际金融', '经济', 1996, 14); CREATE TABLE student( studentid INT PRIMARY KEY NOT NULL DEFAULT 0, `name` VARCHAR(32) NOT NULL DEFAULT '', age INT NOT NULL DEFAULT 0, classid INT NOT NULL DEFAULT 0, FOREIGN KEY (classid) REFERENCES class(classid));INSERT INTO student VALUES (8101, '张三', 18, 101), (8102, '钱四', 16, 121), (8103, '王玲', 17, 131), (8105, '李飞', 19, 102), (8109, '赵四', 18, 141), (8110, '李可', 20, 142), (8201, '张飞', 18, 111), (8302, '周瑜', 16, 112), (8203, '王亮', 17, 111), (8305, '董庆', 19, 102), (8409, '赵龙', 18, 101), (8510, '李丽', 20, 142); -- (3)完成以下查询功能-- 3.1 找出所有姓李的学生。SELECT * FROM student WHERE `name` LIKE '李%'; -- 3.2 列出所有开设超过1个专业的系的名字。SELECT deptname, COUNT(deptname) AS subject_num FROM class GROUP BY deptname HAVING subject_num > 1; -- 3.3 列出人数大于等于30的系的编号和名字。# 方法一SELECT departmentid, class.deptname, SUM(num) AS student_num FROM class, department WHERE class.deptname = department.deptname GROUP BY deptname HAVING student_num >= 30;# 方法二SELECT department.*, temp.student_num FROM department, ( SELECT deptname, SUM(num) AS student_num FROM class GROUP BY deptname HAVING student_num >= 30) temp WHERE department.deptname = temp.deptname -- (4)学校又新增加了一个物理系，编号为006'INSERT INTO department VALUES('006', '物理系');-- (5）学生张三退学，请更新相关的表-- 开启事务START TRANSACTION;-- 张三所在班级的人数-1UPDATE class SET num = num - 1 WHERE classid = (SELECT classid FROM student WHERE `name` = '张三')-- 删除张三DELETE FROM student WHERE `name` = '张三';-- 提交事务COMMIT;
十三、MySql增删改查
13.1 增
语法：INSERT INTO 表名（字段名1，字段名2，…）
VALUES（值1，值2，…）；
举例：INSERT INTO student(id,name,grade)
VALUES(1,'zhangshan',98);
13.2 删
方法一
语法：DELETE FROM 表名 [WHERE 条件表达式]
方法二
语法：TRUNCTE [TABLE ] 表名
举例：TRUNCATE TABLE student；即可删除student表
注意：
（1）DELETE 后面可以跟WHERE子句指定删除部分记录，TRUNCATE只能删除整个表的所有记录
（2）使用TRUNCATE语句删除记录后，新添加的记录时，自动增长字段（如本文中student表中的 id 字段）会默认从1开始，而使用DELETE删除记录后，新添加记录时，自动增长字段会从删除时该字段的的最大值加1开始计算（即原来的id最大为5，则会从6开始计算）。所以如果是想彻底删除一个表的记录而且不会影响到重新添加记录，最好使用TRUNCATE来删除整个表的记录。
13.3 改
语法：UPDATE 表名
SET 字段名1=值1，[ ，字段名2=值2，…]
[ WHERE 条件表达式 ]
13.4 查
MySQL从数据表中查询数据最基本的语句是SELECT语句，在前面的“增删查”已经使用过：SELECT * FROM 表名，也就是查询指定数据表中的所有数据
reference
疑问
1. CHARSET 与CHARACTER SET有什么区别？
