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 employeeCHANGE `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 = 3000WHERE 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 studentWHERE `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'销售人员'ELSEjob
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 > 1000ORDER BY avg_sal DESCLIMIT 0,2; 

解释: 一般select后面的内容作为后面条件判断的条件来使用;

多表

多表查询

-- 多表查询
-- 显示雇员名, 雇员工资以及所在部门的名字[笛卡尔积]
-- 分析: 雇员名、雇员工资来自emp表; 部门名字来自dept表
-- 需求: 对emp和dept进行查询操作; 
-- 注意: 当我们需要指定某个列时, 需要 表.列表的形式来处理
-- 小技巧: 多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔积
SELECT ename, sal, dname, emp.deptno FROM emp, deptWHERE emp.deptno = dept.deptno; -- 如何显示部门号为10的部门名、员工名和工资
SELECT ename, sal, dname, emp.deptno FROM emp, deptWHERE 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 bossWHERE 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 empWHERE 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 empWHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30); SELECT ename, sal, deptno FROM empWHERE sal > (SELECT MAX(sal) FROM emp WHERE deptno = 30); 
-- any的使用
-- 2. 请思考: 显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号; 
SELECT ename, sal, deptno FROM empWHERE sal > ANY(SELECT sal FROM emp WHERE deptno = 30); 
SELECT ename, sal, deptno FROM empWHERE 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 empWHERE (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.deptnoFROM emp, (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) tempWHERE emp.deptno = temp.deptno AND emp.sal > temp.avg_sal; 
/*查找每个部门工资最高的人的详细资料
*/
SELECT ename, sal, temp.max_sal, emp.deptnoFROM emp, (SELECT deptno, MAX(sal) AS max_sal FROM emp GROUP BY deptno) tempWHERE 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) tempWHERE dept.deptno = temp.deptno; 
-- 还有一种写法; 表.* 表示将该表所有列都显示出来
-- 注意: 在多表查询中, 当多个表的列不重复时,才可以直接写列名
SELECT temp.*, dname,locFROM dept, (SELECT COUNT(*) AS per_sum, deptno FROM emp GROUP BY deptno) tempWHERE 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_tab01SELECT * 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_tmp3. 清除掉 my_tab02记录4. 把my_tmp表的记录复制到my_tab025. 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, examWHERE 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不能重复而且不能为null2. 一张表最多只能🈶一个主键,但可以是复合主键; 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', '[e-mail]'); 
INSERT INTO t10 VALUES(1, 'kang', '[e-mail]'); -- 报错DESC t10; 

UNIQUE

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

外键约束

-- 外键的使用
-- 创建 主表 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, '[e-mail]', 'tom'); 
-- 2. 第二种形式
INSERT INTO t13 (email, `name`) VALUES('[e-mail]', '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 ASSELECT empno, ename, dname, gradeFROM emp, dept, salgradeWHERE 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' 登陆的IP2. 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 empORDER 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 empGROUP 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.hiredate2. worker.mgr = leader.empno; 
*/
SELECT worker.ename AS '员工名', worker.hiredate AS '员工入职时间', leader.ename AS '上级名', leader.hiredate AS '上级入职时间' FROM emp worker, emp leaderWHERE worker.hiredate > leader.hiredateAND 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) tmpWHERE 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) tmpWHERE department.deptname = tmp.deptname; -- 3.4 学校又新增了一个物理系, 编号为006
INSERT INTO department VALUES('006', '物理系'); 
-- 3.5 学生张三退学, 请更新相关的表; 
/*1. 张三所在班级人数-12. 将张三从学生表删除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笔记