MySQL

SQL 语句分类

  • DDL：数据定义语句 【create 表，库...】
  • DML：数据操作语句 【增加 insert，修改 update，删除 delete】
  • DQL：数据查询语句 【select】
  • DCL：数据控制语句 【管理数据库：比如用户权限 grant revoke】

1. 创建数据库

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

# 创建一个使用utf8字符集的hsp_db02数据库
CREATE DATABASE hsp_db02 CHARACTER SET utf8
# 创建一个使用utf8字符集，并带校对规则的hsp_db03数据库
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin
# 校对规则 utf8_bin 区分大小 默认utf8_general_ci 不区分大小写

# 下面是一条查询的sql，select 查询 * 表示所有字段
# WHERE 从哪个字段 NAME = 'tom' 查询名字是tom
SELECT * 
	FROM t1
	WHERE NAME = 'tom'

2. 查看、删除数据库

# 显示数据库语句
SHOW DATABASES

# 显示数据库创建语句
SHOW CREATE DATABASE db_name
# 数据库删除语句
DROP DATABASE [IF EXISTS] db_name

# 演示删除和查询数据库
# 查看当前数据库服务器中的所有数据库
SHOW DATABASES
# 查看前面创建的 hsp_db01 数据库的定义信息
SHOW CREATE DATABASE `hsp_db01`
# 在创建数据库，表的时候，为了规避关键字，可以使用反引号解决

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

3. 备份恢复数据库

#备份，要在DOS下执行 mysqldump指令其实在mysql安装目录 \bin
mysqldump -u 用户名 -p密码 -B 数据库1 数据库2 数据库n > 文件名.sql
# 恢复数据库(注意：进入MySQL命令行再执行)
source 文件名.sql

# 备份数据库的表
mysqldump -u 用户名 -p密码 数据库 表1 表2 表n > 文件名.sql

4. 创建表

CREATE TABLE table_name
(
    field1 datatype,
    field2 datatype,
    field3 datatype
)character set 字符集 collate 校对规则 engine 引擎
field：指定列名 datatype：指定列类型（字段类型）
character set：如不指定则为所在数据库字符集
collate：如不指定则为所在数据校对规则
engine：引擎

#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;

5. MySQL常用的数据类型（列类型）

5.1 数值型（整数）的基本使用

CREATE TABLE t3(
	id TINYINT);
CREATE TABLE t4(
	id TINYINT UNSIGNED);

INSERT INTO t3 VALUES(127);
SELECT * FROM t3

INSERT INTO t4 VALUES(255);
SELECT * FROM t4

5.2 数值型（bit）的使用

create table t05(num bit(8));
insert into t05(1); # b'1'
insert into t05 values(255); # b'11111111'


insert into t05 values(7);
select * from t05 where num = b'111';
select * from t05 where num = 7;
# 使用上述两种方式都能查到
  • bit 字段显示时，按照位的方式显示。
  • 查询的时候仍然可以用使用 添加的数值
  • 如果一个值只有 0, 1 可以考虑使用 bit(1) 可以节约空间
  • 位类型。M 指定位数，默认值为1，范围 1 - 64

5.3 数值型（小数）的基本使用

  1. FLOAT / DOUBLE [UNSIGNED]
  2. DECIMAL[M, D] [UNSIGNED]
    • 可以支持更加精确的小数位，M是小数位数（精度）的总数，D是小数点（标度）后面的位数
    • 如果D是0，则值没有小数点或者分数部分。M最大是65，D最大是30，如果D被省略，默认是0，如果M被省略，默认是10

5.4 字符串的基本使用

-- CHAR(size)
-- 固定长度字符串 最大 255字符
-- VARCHAR(size) 0~65535 字节
-- 可变长字符串 最大 65532字节【utf8 编码最大 21844字符 1-3字节用于记录大小】
-- 如果表的编码是 utf8 varchar(size) size = (65535 - 3) / 3 = 21844
-- 如果表的编码是 utf8 gbk varchar(size) size = (65535 - 3) / 2 = 32766

5.5 字符串使用细节

  • char(4) // 这个4 表示字符数（最大255），不是字节数，不管是中文还是字母都是放4个，按字符计算

  • varchar(4) 这个4 表示字符数，不管是字母还是中文都以定义好的表的编码来存放数据

    • 不管是中文还是英文字母，都是最多存放4个，是按照字符来存放的

  • char(4) 是定长（固定的大小），就是说，即使插入 'aa' ，也会占用分配的4个字符的空间

  • varchar(4) 是变长（变化的大小），就是说，如果你插入了 'aa' ，实际占用空间大小并不是4个字符，而是按照占用空间来分配

 注意：varchar 本身还需要占用 1-3个字节来记录存放的内容长度

  • 什么时候使用 char，什么时候使用 varchar

    1. 如果数据是定长，推荐使用 char，比如md5的密码，邮编，手机号，身份证号码等，char(32)
    2. 如果一个字段的长度是不确定的，我们使用varchar，比如留言文章

  • 在存放文本时，也可以使用 Text 数据类型，可以将Text列视为 varchar 列，注意 Text不能有默认值，大小 0-2^16 字节，如果希望存放更多父，可以选择 MEDIUMTEXT 0-2^24 LONGTEXT 0-2^32

5.6 日期类型的基本使用

CREATE TABLE birthday6(
	t1 DATE,
	t2 DATETIME,
	t3 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); 

-- timestamp 时间戳
-- TimeStamp 在 insert 和 update时，自动更新

5.7 修改表-基本介绍

使用 ALTER TABLE 语句追加，修改，或删除列的语法

# 添加列
ALTER TABLE tablename
ADD (column datatype [DEFAULT expr]
   [, column datatype] ...);

# 修改
ALTER TABLE tablename
MODIFY (column datatype [DEFAULT expr]
	[, column datatype] ...);

# 删除列
ALTER TABLE tablename
DROP (column); 

CREATE TABLE `emp`(
	id INT,
	`name` VARCHAR(32),
	sex CHAR(1),
	birthday DATE,
	entry_date DATETIME,
	job VARCHAR(32),
	salary DOUBLE,
	`resume` TEXT) CHARSET utf8 COLLATE utf8_bin ENGINE INNODB;

-- 添加一条数据
INSERT INTO `emp` VALUES(100, '小妖怪', '男', '2000-11-11', '2010-11-11 10:10:10', '巡山', 3000, '大王叫我来巡山');


SELECT * FROM emp

-- 员工表 emp的上增加一个 image列，varchar类型（要求在resume后面）
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;

-- 列名 name修改为 user_name
ALTER TABLE employee
	CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT '';

DESC employee;

6. C[create]R[read]U[update]D[delete]

7. insert

7.1 使用 insert 语句向表中插入数据。

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

-- 创建一张商品表goods (id int, goods_name varchar(10), price double);
CREATE TABLE `goods` (
	id INT,
	goods_name VARCHAR(10),
	price DOUBLE);

-- 添加数据
INSERT INTO `goods` (id, goods_name, price)
	VALUES(10, '华为手机', 2000);
INSERT INTO `goods` (id, goods_name, price)
	VALUES(20, '苹果手机', 3000);
SELECT * FROM goods

7.2 使用细节

# 说明 insert语句的细节
-- 1. 插入的数据应与字段的数据类型相同。
--	比如 把 'abc' 添加到 int 类型会错误
INSERT INTO `goods` (id, goods_name, price)
	VALUES('abc', '小米手机', 2000); -- 添加失败。'abc' 数据库会尝试将'abc' 转为int类型，

INSERT INTO `goods` (id, goods_name, price)
	VALUES('30', '小米手机2', 3000); -- 添加成功。'30' 能被转成 int类型的30
	
-- 2. 数据的长度应在列的规定范围内，例如：不能将一个长度为80的字符串加入到长度为40的列中。

-- 3.  在values中列出的数据位置比如与被加入的列的排列位置相对应。

-- 4. 字符和日期型数据应包含在单引号中。

-- 5. 列可以插入空值[前提是该字段允许为空]，insert into table value(null)

-- 6. insert into tab name (列名...) values (), (), () 形式添加多条记录

-- 7. 如果是表中的所有字段添加数据，可以不写前面的字段名称

-- 8. 默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错。

8. update

8.1 使用 update语句修改表中数据

UPDATE tb1_name
	SET col_name1 = exper1 [, col_name2 = exper2 ...]
	[WHERE where_definition]

-- 在上面创建的 employee 表中修改表的记录
-- 1. 将所有的员工薪水改为 5000元
-- 2. 将姓名为小妖怪的员工薪水修改为 3000元
-- 3. 小妖怪的薪水在原有的基础上增加 1000元

-- 1. [如果没有带where条件，会修改所有的记录，因此要小心]
UPDATE employee SET salary = 5000;

-- 2.
UPDATE employee
	SET salary = 3000
	WHERE user_name = '小妖怪';
	
-- 3. 
UPDATE employee
	SET salary = salary + 1000
	WHERE user_name = '小妖怪';
	
-- 可以修改多个列的值
UPDATE employee
	SET salary = salary + 1000, job = '出主意'
	WHERE user_name = '小妖怪';

SELECT * FROM employee;

8.2 使用细节

  1. UPDATE 语法可以用新值更新原有表中的各列
  2. SET 子句指示要修改哪些列和药给予哪些值
  3. WHERE 子句指定应更新哪些行，如没有 WHERE子句，则更新所有行（记录）
  4. 如果需要修改多个字段，可以通过 set 字段1 = 值1, 字段2 = 值 2...

9. delete

9.1 使用 delete语句删除表中数据

delete from tb1_name	[WHERE where_definition]

-- 删除表中名称为 '老妖怪' 的记录DELETE FROM employee	WHERE user_name = '老妖怪';-- 删除表中所有记录DELETE FROM employee;-- delete 语句不能删除某一列的值（可以使用update 设为 null 或 ''）UPDATE employee SET job = '' WHERE user_name = '小妖怪';SELECT * FROM employee;-- 要删除这个表DROP TABLE employee;

9.2 使用细节

  1. 如果不使用 where 子句，将删除表中的所有数据
  2. Delete 语句不能删除某一列的值（可以使用update 设为 null 或 ''）
  3. 使用 delete 语句仅删除记录，不删除表本身，如果要删除表，使用 drop table 表名 语句，

10. select

SELECT [DISTINCT] *| {column1, column2, column3..}
	FROM table_name;

10.1 基本使用

  1. select 指定查询那些列的数据
  2. column 指定列名
  3. * 代表查询所有列
  4. from 指定查询哪张表
  5. distinct 可选，指显示结果时，去掉重复数据
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
);

SELECT * FROM student;
	
INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'欧阳锋',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(8,'韩信',45,65,99);

SELECT * FROM student;

-- 查询表中所有学生的信息
SELECT * FROM student;

-- 查询表中所有学生的姓名和对应的英语成绩
SELECT DISTINCT `name`, english FROM student;

-- 过滤表中重复数据 distinct
SELECT DISTINCT english FROM student;

-- 要查询的记录，每个字段都相同，才会去重
SELECT DISTINCT `name`, english FROM student;

10.2 使用表达式对查询列进行运算

-- select 语句的使用
SELECT `name`, (chinese + english + math) FROM student;

-- 统计每个学生的总分加10分
SELECT `name`, (chinese + english + math + 10) FROM student;

10.3 as 语句

-- 使用别名表示学生分数
SELECT `name` AS `student_name` FROM student;

-- 统计每个学生的总分
SELECT `name`, (chinese + english + math) FROM student;

-- 在所有学生总分加10的情况
SELECT `name`, (chinese + english + math + 10) FROM student;

-- 使用别名表示学生分数
SELECT `name`, (chinese + english + math) AS total_score FROM student;

10.4 where常用的运算符

10.5 使用where子句，进行过滤查询

-- 查询总分大于200分 并且数学成绩小于语文成绩的赵姓学生
SELECT * FROM student
	WHERE (chinese + english + math > 200) AND NAME LIKE '赵%'

-- 查询英语成绩在 80-90之间的同学
SELECT * FROM student
	WHERE english >= 80 AND english <= 90;

SELECT * FROM student
	WHERE english BETWEEN 80 AND 90; -- between... and... 是闭区间

-- 查询数学分数是89, 90, 91的同学
SELECT * FROM student
	WHERE math = 89 OR math = 90 OR math = 91;

SELECT * FROM student
	WHERE math IN(89, 90, 91);

10.6 order by 子句排序查询结果

SELECT column1, column2, column3...
	FROM table;
	order by column asc|desc, ...

SELECT * FROM student
	ORDER BY math;

-- 对数学成绩排序后输出【降序】
SELECT * FROM student
	ORDER BY math DESC;

-- 对总分按从高到低输出【降序】-- 使用别名排序
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 ASC;

11. 合计/统计函数

11.1 count

select count(*) | count(列名) from table_name
	[WHERE where_definition]

-- 统计一个班共有多少学生
SELECT COUNT(*) FROM student;

-- 统计数学成绩大于90的学生有多少个
SELECT COUNT(*) FROM student
	WHERE math > 90;

-- 统计总分大于 250的人数有多少
SELECT COUNT(*) FROM student
	WHERE (math + english + chinese) > 250;

-- count(*) 和 count(列)的区别
-- count(*) 返回满足条件的记录的行数
-- count(列) 统计满足条件的某列有多少个，但是会排除 null的情况


CREATE TABLE t15(
	`name` VARCHAR(20));
	
INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('jack');
INSERT INTO t15 VALUES('mary');
INSERT INTO t15 VALUES(NULL);

SELECT * FROM t15;

SELECT COUNT(*) FROM t15; -- 4
SELECT COUNT(`name`) FROM t15; -- 3

11.2 sum

select sum(列名) {, sum(列名)...} from tablename
	[WHERE where_definition]

-- 统计一个班级数学总成绩
SELECT SUM(math) FROM student;

-- 统计一个班级语文、英语、数学各科的总成绩
SELECT SUM(math) AS math_total_score, SUM(english), SUM(math) FROM student;

-- 统计一个班级语文、英语、数学的成绩总和
SELECT SUM(math + english + math) FROM student;

11.3 avg

返回满足where条件的一列的平均值

select avg(列名) {, avg(列名)...} from tablename
	[WHERE where_definition]

-- 求一个班级数据平均分
SELECT AVG(math) FROM student;

-- 求一个班级总分平均分
SELECT AVG(math + english + chinese) FROM student;

11.4 max/min

返回满足where条件的一列的最大/最小值

select max(列名) from tablename
	[WHERE where_definition]

-- 求班级最高分和最低分
SELECT MAX(math + english + chinese), MIN(math + english + chinese) FROM student;
-- 求出班级数学最高分和最低分
SELECT MAX(math) AS max_math_score, MIN(math) AS min_math_score FROM student;

11.5 使用 group by子句对列进行分组

select column1, column2, column3.. from table
	group by column

11.6 使用 having子句对分组后的结果过滤

select column1, column2, column3..
	from table
	group by column having ...

样例

-- 部门表
CREATE TABLE dept(
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
	dname VARCHAR(20) NOT NULL DEFAULT '',
	loc VARCHAR(13) NOT NULL DEFAULT '');

DESC dept;

INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'),
	(20, 'RESEARCH', 'DALLAS'),
	(30, 'SALES', 'CHICAGO'),
	(40, 'OPERATIONS', 'BOSTON');

SELECT * FROM dept;

-- 员工表

CREATE TABLE emp(
	empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
	ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
	job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
	mgr MEDIUMINT UNSIGNED ,/*上级编号*/
	hiredate DATE NOT NULL,/*入职时间*/
	sal DECIMAL(7,2) NOT NULL,/*薪水*/
	comm DECIMAL(7,2) ,/*红利 奖金*/
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/);


INSERT INTO emp VALUES
(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20),
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL, 10);

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

SELECT * FROM salgrade;
SELECT * FROM dept;
SELECT * FROM emp;

-- 显示每个部门的平均工资和最高工资
SELECT AVG(sal), MAX(sal), deptno FROM emp
	GROUP BY deptno;
	
-- 对小数点进行处理
SELECT FORMAT(AVG(sal), 2), MAX(sal), deptno FROM emp
	GROUP BY deptno;
	
-- 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal), deptno, job FROM emp
	GROUP BY deptno, job;

-- 显示平均工资低于2000的部门号和它的平均工资//别名
SELECT AVG(sal) AS avg_sal, deptno FROM emp
	GROUP BY deptno
	HAVING avg_sal < 2000;

12. 字符串相关函数

-- charset(str)  返回字符串字符集
SELECT CHARSET(ename) FROM emp;

-- concat(string[,...]) 连接字串，将多个列拼接成一列
SELECT CONCAT(ename,' 工作是 ', job) FROM emp;

-- insert(string, substring) 返回substring 在string中出现的位置，没有返回0
-- dual 亚元表，系统表 可以作为测试表使用
SELECT INSTR('hanshunping', 'ping') FROM DUAL;

-- ucase(string2) 转成大写
SELECT UCASE(ename) FROM emp;

-- lcase(string2) 转成小写
SELECT LCASE(ename) FROM emp;

-- left(string2, length) 从string2中的左边起取length个字符
-- right(string2, length) 从string2中的右边起取length个字符

SELECT LEFT(ename, 2) FROM emp;

-- length(string) string长度[按照字节]
SELECT LENGTH(ename) FROM emp;

-- replace(str, search_str, replace_str)
-- 在str中用 replace_str 替换 search_str
-- 如果是manager 就替换成经历
SELECT ename, REPLACE(job, 'MANAGER', '经理') FROM emp;

-- strcmp(string1, string2) 逐字符比较两字串大小
SELECT STRCMP('hsp', 'hspa') FROM DUAL;

-- substring(str, position [, length])
-- 从 str的 position开始【从1开始计算】，取length个字符
-- 从 ename列的第一个位置开始取出2个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp;

-- ltrim(string2) rtrim(string2) trim(string)
-- 去除前端空格或后端空格
SELECT LTRIM('  韩顺平教育  ') FROM DUAL;
SELECT RTRIM('  韩顺平教育  ') FROM DUAL;
SELECT TRIM('  韩顺平教育  ') FROM DUAL;

-- 以首字母小写的方式显示素有员工 emp表的姓名
SELECT CONCAT(LCASE(LEFT(ename, 1)), SUBSTRING(ename, 2)) FROM emp;

13. 数学相关函数

rand() 返回一个随机浮点数 v, 范围在[0.0, 1.0] ，若已指定一个整数参数 N，则它被用做种子值，用来产生重复序列。

-- abs(num) 绝对值
SELECT ABS(-10) FROM DUAL;

-- bin(decimal_number) 十进制转二进制
SELECT BIN(10) FROM DUAL;

-- ceiling(num2) 向上取整，得到比 num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL;

-- conv(num2, from_base, to_base) 进制转换
-- 下面的含义是 8是十进制的8，转成2进制输出
SELECT CONV(8, 10, 2) FROM DUAL;

-- 下面的含义是 8是16进制的8，转成2进制输出
SELECT CONV(8, 16, 2) FROM DUAL;

-- floor(num2) 向下取整，得到比 num2小的最大整数
SELECT FLOOR(-1.1) FROM DUAL;

-- format(number, decimal_places) 保留小数位（四舍五入）
SELECT FORMAT(78.5362, 2) FROM DUAL;

-- hex(decimalNumber) 转十六机制

-- least(number, number2 [, ...]) 求最小值
SELECT LEAST(0, 1, -10, 4) FROM DUAL;

-- mod(numerator, denominator) 求余
SELECT MOD(10, 3) FROM DUAL;

-- rand([seed]) rand([seed]) 返回随机数，其范围[0.0, 1.0]
-- 如果使用 rand(seed) 返回随机数，范围 [0.0, 1.0]，如果 seed不变，该随机数也不变

SELECT RAND() FROM DUAL;

14. 时间日期相关函数

-- 日期时间相关函数
-- current_date() 当前日期
SELECT CURRENT_DATE() FROM DUAL;

-- current_time() 当前时间
SELECT CURRENT_TIME() FROM DUAL;

-- current_timestamp() 当前时间戳
SELECT CURRENT_TIMESTAMP() 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;
SELECT NOW() FROM DUAL;

-- 显示所有新闻信息，发布日期只显示日期，不用显示时间
SELECT id, content, DATE(send_time) FROM mes;

-- 查询在10分钟内发布的的新闻
SELECT * FROM mes
	WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();

SELECT * FROM mes
	WHERE send_time >= DATE_SUB(NOW(), INTERVAL 10 MINUTE);

-- 请在mysql 的sql语句中求出 2011-11-11 和 1990-1-1相差多少天SELECT DATEDIFF('2011-11-11', '1990-1-1') FROM DUAL; -- 7984-- 和 DATE_ADD、DATE_SUB搭配使用。-- 请用mysql 的sql语句求出活了多少天。[1986-11-11]SELECT DATEDIFF(NOW(), '1986-11-11') FROM DUAL;SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL;

SELECT TIMEDIFF('10:11:11', '06:10:10') FROM DUAL;

-- YEAR | MONTH | DAY | DATE(datetime)
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2013-11-10') FROM DUAL;

-- unix_timestamp(): 返回的是 1970-1-1 到现在的描述
SELECT UNIX_TIMESTAMP() FROM DUAL;

-- FROM_UNIXTIME(): 可以把一个 unix_timestamp 秒数[时间戳]，转成指定格式的日期
-- %Y-%m-%d 格式是规定好的
-- 在开发中，可以存放一个整数表示时间，通过 from_unixtime 转换

SELECT FROM_UNIXTIME(1631966380, '%Y-%m-%d %H:%i:%s') FROM DUAL;

15. 加密和系统函数

-- 加密函数 系统函数
-- 可以查看登录到mysql的哪些用户，以及登录用户
SELECT USER() FROM DUAL; -- 用户@ip地址

-- database() 查询当前使用数据库名称
SELECT DATABASE();

-- md5(str) 为字符串算出一个md5
SELECT MD5('nihaoya');
SELECT LENGTH(MD5('hsp')) FROM DUAL;

-- password(str) 
SELECT PASSWORD('password');
-- select * from mysql.user \G  用户权限

16. 流程控制函数

-- 流程控制
-- IF(expr1, expr2, expr3) 如果expr1 为True，则返回 expr2，否则返回 expr3
SELECT IF(TRUE, '北京', '上海');

-- IFNULL(expr1, expr2) 如果expr1不为空NULL，则返回expr1，否则返回 expr2
SELECT IFNULL(NULL, '韩顺平教育') FROM DUAL;

-- SELECT CASE 
--	WHEN expr1 THEN expr2
--	WHEN expr3 THEN expr4 
--	ELSE expr5 END; [类似多重分支.]
-- 如果 expr1 为 TRUE,则返回 expr2,如果 expr2 为 t, 返回 expr4, 否则返回 expr5


SELECT CASE
	WHEN TRUE THEN 'jack'
	WHEN FALSE THEN 'tom'
	ELSE 'mary' END;

SELECT CASE
	WHEN FALSE THEN 'jack'
	WHEN TRUE THEN 'tom'
	ELSE 'mary' 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;

17. mysql 表查询--加强

17.1 查询加强

-- 使用where子句
-- 如何查找1992.1.1 后入职的员工
-- 在mysql中，日期类型可以直接比较，需要注意格式

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;

-- 使用 order by子句
-- 按照工资的从低到高的顺序【升序】，显示雇员信息
SELECT * FROM emp
	ORDER BY sal;

-- 按照部门号升序而雇员的工资降序排列，显示雇员信息
SELECT * FROM emp
	ORDER BY deptno ASC, sal DESC;

17.2 分页查询

-- 分页查询
-- 1. 按雇员的id号升序取出，每页显示3条记录，请分别显示第1页
-- 2. select ... limit start, rows 表示从 start + 1行开始取，取出rows行，start 从0开始开始

-- 第1页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 1, 3;

-- 第2页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 3, 3;

-- 推导一个公式
SELECT * FROM emp
	ORDER BY empno
	LIMIT 每页显示记录数 * (第几页 - 1), 每页显示记录数

-- 测试
SELECT job, COUNT(*) FROM emp GROUP BY job;

-- 显示雇员总数，以及获得补助的雇员数
SELECT COUNT(*) FROM emp
	WHERE mgr IS NOT NULL;

SELECT MAX(sal) - MIN(sal) FROM emp;

17.3 使用分组函数和分组子句 group by

-- 显示每种岗位的雇员总数、平均工资
SELECT COUNT(*), AVG(sal), job FROM emp
	GROUP BY job;

-- 显示雇员总数、以及获得补助的雇员数
-- 获得补助的雇员就是 comm列为非null，就是count(列)
SELECT COUNT(*), COUNT(comm) FROM emp;

-- 统计没有获得补助的
SELECT COUNT(*), COUNT(IF(comm IS NULL, 1, NULL)) FROM emp;

-- 显示管理者的总人数
SELECT COUNT(DISTINCT mgr) FROM emp;

-- 显示雇员工资的最大差额
SELECT MAX(sal) - MIN(sal) FROM emp;

-- 统计各个部门 group by的平均工资 avg
SELECT deptno, AVG(sal) AS avg_sal FROM emp
	GROUP BY deptno;

-- 并且是大于2000的having，并且按照平均工资从高到低排序
-- 取出前两行记录
SELECT deptno, AVG(sal) AS avg_sal FROM emp
	GROUP BY deptno
	HAVING avg_sal > 2000
	ORDER BY avg_sal DESC
	LIMIT 0, 2;

18. 多表查询

18.1 基本使用

-- 多表查询
-- 显示雇员名，雇员工资以及所在部门
-- 默认情况下：当两个表查询时，规则
-- 1. 从第一张表中，取出一行 和第二张表的每一行进行组合，【返回结果含有两张表的所有列】
-- 2. 一共返回的记录数 第一张表行数 * 第二张表的行数
-- 3. 这样多表查询默认处理返回的结果，称为笛卡尔集
-- 解决这个多表的关键就是写出正确的过滤条件 where

SELECT * FROM emp, dept;

SELECT ename, sal, dname, dept.deptno /*明确是哪个表的deptno*/
	FROM emp, dept
	WHERE emp.deptno = dept.deptno;

-- 多表查询的条件不能少于表的个数 -1, 否则会出现笛卡尔集
-- 也是就条件个数 >= 表的个数 - 1


-- 如何显示部门号为10的部门名、员工名和工资

SELECT emp.deptno, ename, sal
	FROM emp, dept
	WHERE emp.deptno = dept.deptno AND dept.deptno = 10;

-- 如何显示各个员工的姓名、工资以及工资的级别

SELECT * FROM salgrade;
SELECT * FROM emp;

SELECT ename, sal, grade
	FROM emp, salgrade
	WHERE sal BETWEEN losal AND hisal;

18.2 自连接

自连接是指同一张表的链接查询【将同一张表看做两张表】

-- 多表查询的自连接
-- 显示公司员工名字和他上级的名字
-- 员工名字在 emp, 上级的名字也在 emp
-- 员工和上级是通过 emp表的 mgr列关联
SELECT worder.ename AS '职员', boss.ename AS '上级'
	FROM emp worder, emp boss -- 表 表别名
	WHERE worder.mgr = boss.empno;

18.3 mysql表子查询

  • 什么是子查询
    • 子查询是指嵌入在其他sql语句中的 select语句，也叫嵌套查询
  • 单行子查询
    • 单行子查询是指只返回一行数据的子查询语句
  • 多行子查询
    • 多行子查询返回多行数据的子查询，使用关键词 in
-- 子查询的演示
-- 如何显示与 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号部门自己的雇员

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; 

-- 不等 !=  <>
  • 子查询当做临时表使用
-- 查询 ecshop中各个类别中，价格最高的商品
-- 查询 商品表
-- 先得到 各个类别中, 价格最高的商品 max + group by cat_id, 当做临时表

SELECT cat_id, MAX(shop_price)
	FROM ecs_goods
	GROUP BY cat_id
	
SELECT goods_id, temp.cat_id, goods_name, shop_price
	FROM (
		SELECT cat_id, MAX(shop_price) AS max_price
		FROM ecs_goods
		GROUP BY cat_id
	) temp, ecs_goods
	WHERE temp.cat_id = ecs_goods.cat_id AND temp.max_price = ecs.shop_price
  • 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);

-- 显示工资比部门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 ...)

SELECT deptno, job FROM emp
	WHERE ename = 'ALLEN';

SELECT ename, deptno, job FROM emp
	WHERE (deptno, job) = (SELECT deptno, job FROM empwhere ename = 'ALLEN') 
		AND ename != 'ALLEN';

-- 查询和宋江数学，英语，语文成绩完全相同的学生
SELECT * 
	FROM student
	WHERE (math, english, chinese) = (SELECT math, english, chinese FROM student WHERE `name` = '宋江');
  • 子查询练习
-- 查询每个部门工资高于本部门平均工资的人的资料
SELECT deptno, AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptno;

SELECT ename, sal, temp.avg_sal, emp.deptno 
	FROM (
		SELECT deptno, AVG(sal) AS avg_sal
		FROM emp
		GROUP BY deptno
	) temp, emp
	WHERE emp.sal > temp.avg_sal AND emp.deptno = temp.deptno;

-- 查找每个部门工资最高的人的详细资料

SELECT deptno, AVG(sal) AS avg_sal
	FROM emp
	GROUP BY deptno;

SELECT ename, sal, temp.max_sal, emp.deptno
	FROM (
		SELECT deptno, MAX(sal) AS max_sal
		FROM emp
		GROUP BY deptno
	) temp, emp
	WHERE emp.sal = temp.max_sal AND emp.deptno = temp.deptno;

-- 查询每个部门的信息（包括：部门号，编号，地址）和人员数量
-- 1. 部门号，编号，地址 来自dept表
-- 2. 各个部门的人员数量 -> 构建一个临时表

SELECT COUNT(*), deptno
	FROM emp
	GROUP BY deptno;

SELECT dept.deptno, dname, loc, temp.num AS '人数'
	FROM (
		SELECT COUNT(*) AS num, deptno
		FROM emp
		GROUP BY deptno
	) temp, dept
	WHERE temp.deptno = dept.deptno;
-- 表.* 可以将该表所有列都显示出来
-- 在多表查询中，当多个表的列不重复时，才可以直接写出列名
SELECT dname, loc, temp.*
	FROM (
		SELECT COUNT(*) AS num, deptno
		FROM emp
		GROUP BY deptno
	) temp, dept
	WHERE temp.deptno = dept.deptno;

19. 表复制

  • 自我复制数据（蠕虫复制）

有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据

CREATE TABLE my_tab01(
	id INT,
	`name` VARCHAR(32),
	sal DOUBLE,
	job VARCHAR(32),
	deptno INT);

-- 演示如何自我复制
-- 1. 先把emp表的记录复制到my_tab01
INSERT INTO my_tab01
	(id, `name`, sal, job, deptno)
	SELECT empno, ename, sal, job, deptno
		FROM emp;

-- 2. 自我复制
INSERT INTO my_tab01
	SELECT * FROM my_tab01;
  • 删除表中的重复数据
/*
去重 my_tab02的记录
1. 先创建一张临时表 my_tmp, 该表结构和 my_tab02一样
2. 把my_tmp的记录通过 distinct关键字处理后把记录复制到 my_tab02
3. 把my_tab02 复制到 my_tmp
4. 把my_tmp 表的记录复制到 my_tab02
5. drop 把临时表 my_tmp
*/

CREATE TABLE my_tmp LIKE my_tab02;

INSERT INTO my_tmp
	SELECT DISTINCT * FROM my_tab02;

SELECT * FROM my_tmp;

DELETE FROM my_tab02;

INSERT INTO my_tab02
	SELECT * FROM my_tmp;

DROP TABLE my_tmp;

SELECT * FROM my_tab02;

20. 合并查询

为了合并多个 select 语句的结果，可以使用集合操作符号 union，union all

-- 合并查询-- 1. union allSELECT ename, sal, job FROM emp	WHERE sal > 2500 -- 5UNION ALLSELECT ename, sal, job FROM emp	WHERE job = 'MANAGER' -- 3-- union all 就是将两个查询结果合并，不会去重-- 2. unionSELECT ename, sal, job FROM emp	WHERE sal > 2500 -- 5UNIONSELECT ename, sal, job FROM emp	WHERE job = 'MANAGER' -- 3-- union 就是将两个查询结果合并，会去重

21. MySQL表外连接

  1. 前面我们学习的查询，是利用where子句对两张表或者多张表，形成的笛卡尔积进行筛选，根据关联条件，显示所有匹配的记录，匹配不上的，不显示
  2. 比如：列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门。

21.1 左外连接

21.2 右外连接

-- 外连接
-- 创建stu
CREATE TABLE stu(
	id INT,
	`name` VARCHAR(32));

INSERT INTO stu VALUES(1, 'jack'), (2, 'tom'), (3, 'kity'), (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 ...from 表1 left jion 表2 on [表1就是左表，表2是右表]
SELECT `name`, stu.id, grade
	FROM stu
	WHERE stu.id = exam.id;

SELECT `name`, stu.id, grade
	FROM stu LEFT JOIN exam ON stu.id = exam.id;
	

-- 右连接（显示所有成绩，如果没有名字匹配，显示空）
-- select ...from 表1 right jion 表2 on [表1就是左表，表2是右表]
SELECT `name`, stu.id, grade
	FROM stu RIGHT JOIN exam ON stu.id = exam.id;

-- 列出部门名称和这些部门员工的信息，同时列出那些没有员工的部门
SELECT empno, ename, job, dept.deptno
	FROM dept LEFT JOIN emp ON emp.deptno = dept.deptno;

SELECT empno, ename, job, dept.deptno
	FROM emp RIGHT JOIN dept ON emp.deptno = dept.deptno;

22. MySQL 约束

22.1 基本介绍

约束 用于确保数据库的数据满足特定的商业规则。在mysql中，约束包括: not null、unique、primary key、foreign key、check。

22.2 primary key（主键）

字段名 字段类型 primary key

-- 主键使用
CREATE TABLE t17(
	id INT PRIMARY KEY, -- 表示id列是主键
	`name` VARCHAR(32),
	email VARCHAR(32));

-- 主键列的值是不可以重复
INSERT INTO t17
	VALUES(1, 'jack', 'jack@qq.com');
INSERT INTO t17
	VALUES(2, 'tom', 'tom@qq.com');
INSERT INTO t17
	VALUES(1, 'hsp', 'hsp@qq.com'); -- 添加失败
	
SELECT * FROM t17;

-- 主键使用的细节讨论
-- primary key 不能重复且不能为null
INSERT INTO t17
	VALUES(NULL, 'hsp', 'hsp@qq.com'); -- 添加失败

-- 一张表最多只能有一个主键，但可以是复合主键（比如 Id + name)

CREATE TABLE t18(
	id INT PRIMARY KEY, -- 表示id列是主键
	`name` VARCHAR(32) PRIMARY KEY, -- 错误的
	email VARCHAR(32));

-- 演示复合主键（id 和 name做成复合主键）
INSERT INTO t18 VALUES(1, 'tom', 'tom@qq.com');

INSERT INTO t18 VALUES(1, 'jack', 'jack@qq.com');

INSERT INTO t18 valeus(1, 'tom', 'xx@qq.com'); -- 这里就违反了复合主键

SELECT * FROM t18;

主键的指定方式有两种

  1. 直接在字段名后指定：字段名 primary key
  2. 在表定义最后写 primary key(列名)

22.3 not null (非空)

如果在列上定义了 not null 那么当插入数据时，必须为列提供数据。

字段名 字段类型 not null

22.4 unique (唯一)

  1. 如果没有指定 not null，则 unique 字段可以有多个 null
  2. 一张表可以有多个 unique 字段
-- unique
CREATE TABLE t21(
	id INT UNIQUE, -- 表示id列是不可以重复的
	`name` VARCHAR(32),
	email VARCHAR(32));

INSERT INTO t21 VALUES(1, 'jack', 'jacl@qq.com');

INSERT INTO t21 VALUES(1, 'tom', 'tom@qq.com'); -- 添加失败

SELECT * FROM t21;


-- unique 使用细节
-- 1. 如果没有指定 not null, 则unique字段可以有多个null
-- 如果一个列(字段)，是unique not null 使用效果类似 primary key


INSERT INTO t21 VALUES(NULL, 'tom', 'tom@qq.com'); -- 添加失败

-- 2. 一张表可以有多个 unique字段

CREATE TABLE t22(
	id INT UNIQUE,
	`name` VARCHAR(32) UNIQUE, -- 表示name不可以重复
	email VARCHAR(32));

DESC t22;

22.5 foreign key (外键)

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

-- 创建班级表 主表 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班级不存在
  1. 外键指向的表的字段，要求是 primary key 或者是 unique
  2. 表的类型是 innodb，这样的表才支持外键
  3. 外键字段的类型要和主键字段的类型一致（长度可以不同）
  4. 外键字段的值，必须在主键字段中出现过，或者为 null（前提是外键字段允许为null）
  5. 一旦建立主外键的关系，数据就不能随意删除了。

22.6 check

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

老师提示：oracle 和 sql server 均支持 check，但是mysql5.7 目前还不支持 check，只做语法校验，但不会生效。

列名 类型 check (check条件)

CREATE TABLE t23(
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	sex VARCHAR(6) CHECK (sex IN ('man', 'woman')),
	sal DOUBLE CHECK (sal > 1000 AND sal < 2000)
	);

-- 添加数据
INSERT INTO t23 VALUES(1, 'jack', 'mid', 1);

SELECT * FROM t23;

22.7 案例

现有一个商店的数据库shop_db，记录客户及其购物情况，由下面三个表组成:商品goods(商品号goods_id，商品名goods_name.单价unitprice,商品类别category，供应商provider);
客户customer(客户号customer_id,姓名name,住址address,电邮email性别sex,身份证card_ld);
购买purchase(购买订单号order_id，客户号customer_id,商品号goods_id,购买数量nums);

(1)每个表的主外键

⑵客户的姓名不能为空值

(3)电邮不能够重复;

(4)客户的性别[男女] check枚举..

(5)单价unitprice在1.0 - 9999.99之间check

CREATE DATABASE shop_db;

CREATE TABLE goods(
	goods_id INT PRIMARY KEY,
	goods_name VARCHAR(32) NOT NULL DEFAULT '',
	unitprice DECIMAL(10, 2) NOT NULL DEFAULT 0
		CHECK (unitprice > 1.0 AND unitprice < 9999.99),
	category INT NOT NULL DEFAULT 0,
	provider VARCHAR(32) NOT NULL DEFAULT '');

CREATE TABLE customer(
	customer_id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT '',
	address VARCHAR(32) NOT NULL DEFAULT '',
	email VARCHAR(32) UNIQUE NOT NULL,
	sex ENUM('男', '女') NOT NULL
	card_id VARCHAR(32));

CREATE TABLE purchase(
	order_id INT PRIMARY KEY,
	customer_id INT NOT NULL DEFAULT 0,
	goods_id INT NOT NULL DEFAULT 0,
	nums INT DEFAULT 0,
	FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
	FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
	);

23. 自增长

在某张表中，存在一个 id列（整数）我们希望在添加记录的时候，该列从1开始，自动的增长

字段名 整型 primary key auto_increment

添加自增长的字段方式

insert into xx (字段1, 字段2 ...) values(null, '值' ...);
insert into xx (字段2 ...) valeus('值1', '值2' ...);
insert into xx values(null, '值1' ...);
  • 自增长细节

  1. 一般来说自增长和 primary key 配合使用

  2. 自增长也可以单独使用【但是需要配合一个unique】

  3. 自增长修饰的字段为整数型的（虽然小数也可以，但是基本不这么使用）

  4. 自增长默认从1开始， 也可以通过如下命令修改默认初始值

    alter table 表名 auto_increment = 新的开始值;

  5. 如果你添加数据时，给自增长字段（列）指定的有值，则以指定的值为准，如果指定了自增长，一般来说，就按照自增长的规则 来添加数据。

-- 自增长
CREATE TABLE t24(
	id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT '',
	`name` VARCHAR(32) NOT NULL DEFAULT '');

DESC t24;

INSERT INTO t24 VALUES(NULL, 'tom@qq.com', 'tom');

INSERT INTO t24(email, `name`) VALUES('hsp@qq.com', 'hsp');

INSERT INTO t24(id, email, `name`) VALUES(4, 'xx@qq.com', 'xx');

SELECT * FROM t24;

-- 修改默认的自增长开始值
CREATE TABLE t25(
	id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT '',
	`name` VARCHAR(32) NOT NULL DEFAULT '');

ALTER TABLE t25 AUTO_INCREMENT = 100;

INSERT INTO t25 VALUES(NULL, 'mary@qq.com', 'mary');

INSERT INTO t25 VALUES(666, 'hsp@qq.com', 'hsp');

INSERT INTO t25(email, `name`) VALUES('xx@qq.com', 'xx');
SELECT * FROM t25;
/*
100 mary@qq.com mary
666 hsp@qq.com  hsp
667 xx@qq.com   xx
*/

24. MySQL索引

索引提高数据库性能

  • 创建海量数据标
CREATE DATABASE tmp;

-- 索引
CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;

#创建表EMP雇员
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;

#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);

#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

DELIMITER $$

#创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
#定义了一个变量 chars_str， 类型  varchar(100)
#默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 DECLARE chars_str VARCHAR(100) DEFAULT
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0; 
 WHILE i < n DO
    # concat 函数 : 连接函数mysql函数
   SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
   SET i = i + 1;
   END WHILE;
  RETURN return_str;
  END $$


 #这里我们又自定了一个函数,返回一个随机的部门号
CREATE FUNCTION rand_num( )
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(10+RAND()*500);
RETURN i;
END $$

 #创建一个存储过程， 可以添加雇员
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit设置成0
 #autocommit = 0 含义: 不要自动提交
 SET autocommit = 0; #默认不提交sql语句
 REPEAT
 SET i = i + 1;
 #通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
 INSERT INTO emp VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
  UNTIL i = max_num
 END REPEAT;
 #commit整体提交所有sql语句，提高效率
   COMMIT;
 END $$

 #添加8000000数据
CALL insert_emp(100001,8000000)$$

#命令结束符，再重新设置为;
DELIMITER ;
  • 创建索引
SELECT COUNT(*) FROM emp;

-- 在没有创建索引时，我们的查询一条记录
SELECT *
	FROM emp
	WHERE empno = 1234567 -- 耗时4s

-- 使用索引优化
-- empno_index 索引名称
-- ON emp(empno) 表示在 emp表的empno列创建索引[索引本身也占用空间]
-- 没有创建索引之前 emp.ibd 524,288KB
-- 创建索引之后，655,360KB
CREATE INDEX empno_index ON emp (empno)

-- 创建索引后，查询的速度

SELECT * FROM emp
	WHERE empno = 6234568 -- 0.002s
	
-- 创建索引，只对创建了索引的列有效

24.1 索引的原理

默认全表扫描，使用索引会形成一个索引的数据结构，比如二叉树

索引的代价

  1. 磁盘的占用
  2. 对 dml(update delete insert) 语句的效率影响，需要重新维护二叉树

24.2 索引的类型

  1. 主键索引，主键自动的为主索引

  2. 唯一索引 （unique）

  3. 普通索引（index）

  4. 全文索引（fulltext）【适用于myisam】

    一般开发，不使用mysql自带的全文索引，而是使用：全文搜索 Solr 和 ElasticSearch（ES）

CREATE TABLE t1(
	id INT PRIMARY KEY, -- 主键，同时也是索引，称为主键索引
	`name` VARCHAR(32));

CREATE TABLE t2(
	id INT UNIQUE, -- id 是唯一的，同时也是索引，称为unique
	`name` VARCHAR(32));

24.3 创建索引

CREATE TABLE t25(
	id INT,
	`name` VARCHAR(32));

-- 查询表是否有索引
SHOW INDEXES FROM t25;

-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id);

-- 添加普通索引
CREATE INDEX id_index ON t25 (id);

-- 如何选择
-- 1. 如果某列的值，是不会重复的，则优先考虑使用 unique索引，否则使用普通索引


-- 添加普通索引方式2
ALTER TABLE t25 ADD INDEX id_index (id)

-- 添加主键索引
CREATE TABLE t26(
	id INT,
	`name` VARCHAR(32));

ALTER TABLE t26 ADD PRIMARY KEY (id);

SHOW INDEX FROM t26;

24.4 删除索引

-- 删除索引
SHOW INDEX FROM t25;

DROP INDEX id_index ON t25;

-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY;

24.5 修改、查询索引

-- 修改索引，先删除，再添加新的索引
-- 查询索引
-- 1. 方式
SHOW INDEX FROM t25;
-- 2. 方式
SHOW INDEXES FROM t26;
-- 3. 方式
SHOW KEYS FROM t25;

-- 4 方式
DESC t25;

24.6 哪些列上适合使用索引

  1. 较频繁的作为查询条件字段应该创建索引

    select * from emp where empno = 1

  2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

    select * from emp where sex = '男'

  3. 更新非常频繁的字段不适合创建索引

    select * from emp where logincount = 1

  4. 不会出现在 where子句中字段不该创建索引

25. MySQL事务

25.1 什么是事务

事务用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败。

25.2 事务和锁

当执行事务操作时（dml语句），mysql会在表上加锁，防止其他用户改表的数据。

  • mysql 数据库控制台事务的几个重要操作
    1. start transaction 开始一个事务
    2. savepoint 保存点名 设置保存点
    3. rollback to 保存点名 回退事务
    4. rollback 回退全部事务
    5. commit 提交事务，所有的操作生效，不能回退
-- 1. 创建一张测试表
CREATE TABLE t27(
	id INT,
	`name` VARCHAR(32));

-- 2. 开始事务
START TRANSACTION

-- 3. 设置保存点
SAVEPOINT a

-- 执行 dml操作
INSERT INTO t27 VALUES(100, 'tom');
SELECT * FROM t27;

-- 
SAVEPOINT b

-- 执行dml操作
INSERT INTO t27 VALUES(200, 'jack');
SELECT * FROM t27;

-- 回退到b
ROLLBACK TO b

-- 回退到a
ROLLBACK TO a

-- 如果rollback, 表示直接回退到事务开始的状态
ROLLBACK

COMMIT -- 提交事务, 所有的操作生效无法回退
  • 回退事务

保存点（savepoint）保存点是事务中的点，用于取消部分事务，当结束事务时（commit），会自动的删除该事务所定义的所有保存点，当执行回退事务时，通过指定保存点可以回退到指定的点。a——b——c 当从c点回退到a点后，就无法退到b点。

  • 提交事务

使用 commit语句可以提交事务，当执行了 commit语句后，会确认事务的变化、结束事务、删除保存点、释放锁、数据生效。当使用 commit语句结束事务之后，其他会话【其他连接】将可以查看到事务变化后的新数据

  • 事务细节讨论
  1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
  2. 如果开始一个事务，你没有创建保存点，你可以执行 rollback，默认就是回退到你事务开始的状态
  3. 你也可以在这个事务中（还没有提交时），创建多个保存点，比如 savepoint aaa 执行 dml，savepoint bbb
  4. 你可以在事务没有提交前，选择回退到哪个保存点
  5. mysql的事务机制需要 innodb的存储引擎还可以使用，myisam不好使
  6. 开始一个事务 start transaction ,set autocommit = off

26. MySQL事务隔离级别

26.1 事务隔离级别介绍

  1. 多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
  2. 如果不考虑隔离性，可能会引发如下问题
    • 脏读
    • 不可重复读
    • 幻读

26.2 查看事务隔离级别

  • 脏读（dirty read）：当一个事务读取另一个事务尚未提交的改变（update，insert，delete）时，产生脏读
  • 不可重读读（nonrepeatable read）：同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的结果集，此时发生不可重复读。
  • 幻读（phantom read）：同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的结果集，此时发生幻读

26.3 事务隔离级别

26.4 设置事务隔离级别

-- 演示mysql的事务隔离级别

-- 1. 查看当前会话隔离级别
SELECT @@tx_isolation;

-- 2. 查看系统当前隔离级别
SELECT @@global.tx_isolation;

-- 3. 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- 4. 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 5. mysql默认的事务隔离级别是 repeatable read, 一般情况下，没有特殊要求，没有必要修改
  • 全局修改，修改my.ini 配置文件，在最后加上
# 设置默认隔离级别
# 可选参数有：READ-UNCOMMITTED  READ-COMMITTED  REPEATABLE-READ  SERIALIZABLE
transaction-isolation = REPEATABLE-READ

27. MySQL 事务 ACID

  • 原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
  • 一致性（Consistency）：事务必须使数据库从一个一致性状态换到另一个一致性状态。
  • 隔离性（Isolation）：事务的隔离性宿舍多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
  • 持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

28. MySQL表类型和存储引擎

28.1 基本介绍

  1. MySQL的表类型由存储引擎（Storage Engines）决定，主要包括MyISAM、innoDB、Memory等。
  2. MySQL数据表主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、InnoDB。
  3. 这六种又分为两类，一类是“事务安全型”（transaction-safe），比如InnoDB；其余都属于第二类，称为“非事务安全型”（non-transaction-safe）[mysiam和memory]。

28.2 主要的引擎/表类型的特点

28.3 细节说明

  1. MyISAM不支持事务，也不支持外键，但其访问速度快，对事务完整性没有要求
  2. InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占有更多的磁盘空间以保留数据和索引。
  3. MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦MYSQL服务关闭，表中的数据就会丢失掉，表的结构还在

28.4 如何选择表的存储引擎

  1. 如果你的应用不需要事务，处理的只是基本的CRUD操作，那么MyISAM是最佳选择，速度快
  2. 如果需要支持事务，选择InnoDB
  3. Memory存储引擎就是将数据存储在内存中，由于没有磁盘I/O的等待，速度极快，但是由于内存存储引擎，所做的任何修改在服务器重启后都将消失。（经典用法：用户的在线状态）
ALTER TABLE `表名` ENGINE = 储存引擎;

29. 视图

29.1 基本概念

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含列，其数据来自对应的真实表（基表）

视图总结

  1. 视图是根据基表（可以是多个基表）来创建的，视图是虚拟的表
  2. 视图也有列，数据来自基表
  3. 通过视图可以改变基表的数据
  4. 基表的改变，也会影响到视图的数据

29.2 视图的基本使用

  1. create view 视图名 as select 语句
  2. alter view 视图名 as select 语句
  3. show create view 视图名
  4. drop view 视图名1, 视图名2
-- 视图的使用
-- 创建一个视图 emp_view01, 只能查询 emp表的 (empno, ename, job, deptno) 信息

-- 创建视图
CREATE VIEW emp_view01
	AS
	SELECT empno, ename, job, deptno FROM emp;

-- 查看视图
DESC emp_view01

SELECT * FROM emp_view01;

SELECT empno, job FROM emp_view01;

-- 查看创建视图的执行
SHOW CREATE VIEW emp_view01;

-- 删除视图
DROP VIEW emp_view01;

-- 修改视图
UPDATE emp_view01
	SET job = 'MANAGER'
	WHERE empno = 7369;

-- 视图的课堂练习

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;

29.3 细节讨论

  1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件（形式：视图.frm）
  2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete]
  3. 视图中可以再使用视图，数据仍来自基表

29.4 视图最佳实践

  1. 安全。一些数据表有着重要的信息。有些字段是保密的，不能让用户直接看到。这时就可以创建一个视图，在这张视图中只保留一部分字段。这样，用户就可以查询自己需要的字段，不能查看保密的字段。
  2. 性能。关系数据库的数据常常会分表存储，使用外键建立这些表的之间关系。这时,数据库查询通常会用到连接(JOIN)。这样做不但麻烦,效率相对也比较低。如果建立一个视图，将相关的表和字段组合在一起，就可以避免使用JOIN查询数据。
  3. 灵活。如果系统中有一张旧的表，这张表由于设计的问题，即将被废弃。然而，很多应用都是基于这张表，不易修改。这时就可以建立一张视图，视图中的数据直接映射到新建的表。这样，就可以少做很多改动，也达到了升级数据表的目的。

30. MySQL管理

30.1 MySQL用户

-- mysql用户管理
-- 原因：当我们做项目开发时，可以根据不同开发人员赋予相应的mysql权限
-- 所以，MySQL数据库管理人员(root),根据需要创建不同的用户，赋予相应的用户权限

-- 1. 创建新的用户
-- 'hsp_edu'@'localhost' 表示用户的完整信息, hsp_edu 用户名 localhost表示可以登录的ip
-- 123456就是密码，存到到mysql.user表中是password()加密后的
CREATE USER 'hsp_edu'@'localhost' IDENTIFIED BY '123456'	

SELECT * FROM mysql.user;

SELECT `host`, `user`, authentication_string
	FROM mysql.user;

-- 2. 删除用户
DROP USER 'hsp_edu'@'localhost'

-- 3. 修改自己的密码
SET PASSWORD = PASSWORD('abcdef')

-- 修改其他人的密码，需要权限
SET PASSWORD FOR 'hsp_edu'@'localhost' = PASSWORD('abcd');

30.2 给用户授权

30.3 回收用户授权

rovoke 权限列表 on 库.对象名 from '用户名'@'登录位置';

30.4 权限生效指令

如果权限没有生效，可以执行下面命令

FLUSH PRIVILEGES

-- 演示
-- 创建用户，密码123
CREATE USER 'jle'@'localhost' IDENTIFIED BY '123'

-- 在这里默认情况下，jle 用户只能看到一个默认的系统数据库

CREATE DATABASE testdb;

CREATE TABLE news(
	id INT,
	`title` VARCHAR(32));

SELECT * FROM news;

INSERT INTO news VALUES(2, 'pku news');

-- 给jle 分配查看 news表和 添加news的权限
GRANT SELECT, INSERT
	ON testdb.news
	TO 'jle'@'localhost'
	
-- 可以增加update权限
GRANT UPDATE
	ON testdb.news
	TO 'jle'@'localhost'

REVOKE SELECT, UPDATE, INSERT ON testdb.news FROM 'jle'@'localhost'

REVOKE ALL ON testdb.news FROM 'jle'@'localhost'

-- 刷新
FLUSH PRIVILEGES;

DROP USER 'jle'@'localhost'

-- 细节
-- create user 'xxx'@'192.168.1.%' 表示 xxx用户在 192.168.1.* 的ip可以登录

-- 在删除用户的时候，如果 host不是 %，需要明确 '用户'@'host值'

CREATE USER jack
DROP USER jack -- 默认就是 drop user 'jacl'@'%'
posted @ 2021-09-23 13:50  ChenWeihan  阅读(73)  评论(0)    收藏  举报