MySQL数据库基础

MySQL

数据库操作

命令行连接数据库

mysql -h 主机IP -P 端口 -u 用户名 -p密码

-- 连接数据库
mysql -h 127.0.0.1 -P 3306 -u root -p123456
mysql -u root -p
-- 显示所有已经存在的数据库
show databases; 
-- 创建名字为test的数据库
create database test； 
-- 删除名字为test的数据库
drop database test； 
-- 使用名字为test的数据库
use test；
-- 显示这个数据库中的所有数据表
show tables； 

启动和关闭MySQL服务

-- 启动服务
net start mysql;
-- 关闭服务
net stop mysql;

创建数据库

-- 创建数据库 da_name 是数据库名
create database db_name;
-- 删除数据库：
drop database db_name;
-- 创建一个使用utf8字符集的数据库：
create database db_name character set utf8;
-- 创建一个使用utf8字符集，并带校对规则utf8_bin的数据库：
create database db_name character set utf8 collate utf8_bin;
-- utf8_bin 区分大小写，默认 uf8_general_ci 不区分大小写
-- 查看当前数据库服务器中的所有数据库
show database;
-- 查看创建的数据库的定义信息
show create database db_name;

备份和恢复数据库

-- 备份数据库(注意：在DOS)命令行
-- mysqldump -u 用户名 -p -B 数据库1 数据库2 > 文件名.sql
mysqldump -u root -p -B db_name > d:\\bak.sql
-- 恢复数据库(注意：进入Mysql命令行再执行)
-- source 文件名.sql 
source d:\\bak.sql

MySQL数据类型

整型

tinyint 范围：有符号 -128~127，无符号 0~255
unsinged 用来指定有无符号，默认有符号

浮点型

float，double，decimal（可以存放很大的数）

字符串类型

注释快捷键 shift+ctrl+c 取消注释 shift+ctrl+r
char(size)
固定长度字符串 最大为255字符
varchar(size) 0~65535字节
可变长度字符串 最大65532字节 【utf8编码最大21844字符 1-3个字节用来记录大小】
utf8编码 size=(65535-3)/3 = 21844
gbk编码 size=(65535-3)/2 = 32766
char(4)和varchar(4) 这个4表示的是字符，而不是字节
如果varchar不够用，可以考虑使用mediumtext 或者 longtext

#演示字符串类型使用char varchar
CREATE TABLE t9 (
	‘name‘ CHAR(255));
CREATE TABLE t10 (
	‘name‘ VARCHAR(21844));
#演示字符串类型的使用细节
#char(4)和varchar(4) 这个4表示的是字符，而不是字节
CREATE TABLE t11 (
	‘name‘ CHAR(4));
INSERT INTO t11 VALUES('abcd');
SELECT * FROM t11;
INSERT INTO t11 VALUES('王战鹏好');

日期类型

#时间 date  datetime  timestamp
CREATE TABLE t7 (
	birthday DATE,
	job_time DATETIME,
	login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
); 
-- 自动更新时间 NOT NULL DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP
INSERT INTO t7(birthday,job_time) VALUES('2022-10-23','2022-03-12 12:23:34');

表的创建与修改

创建表

/*
create table table_name (
    field1 datatype,
    field2 datatype,
    field3 datatype,
) character set 字符集 collate 校对规则 engine 
field1：列名 datatype：字段类型
*/

修改表

#修改表
-- add 添加列
ALTER TABLE emp 
	ADD image VARCHAR(32) NOT NULL DEFAULT ''
	AFTER RESUME
-- 显示表结构，可以查看表的所有列
DESC employee;
-- modify 修改
ALTER TABLE emp 
	MODIFY job VARCHAR(60) NOT NULL DEFAULT '';
-- drop 删除
ALTER TABLE emp 
	DROP sex;
-- rename 修改表名
RENAME TABLE emp TO employee;
-- character 修改表的字符集为utf8
ALTER TABLE employee CHARACTER SET utf8;
-- change 修改列名
ALTER TABLE employee 
	CHANGE `name` `user_name` VARCHAR(64) NOT NULL DEFAULT ''

对数据增删改查

插入语句 insert

#练习insert语句
CREATE TABLE `goods` (
	id INT,
	goods_name VARCHAR(10),
	price DOUBLE);
-- 查询表
SELECT * FROM goods;
-- 添加数据
INSERT INTO `goods` (id,goods_name,price) VALUES(1,'苹果',5.2);
INSERT INTO `goods` (id,goods_name,price) VALUES(2,'香蕉',4.3);

更新语句 update

#演示update 语句
-- 1.将所有员工薪水修改为5000
UPDATE employee SET salary = 5000;
-- 修改
UPDATE employee SET salary = 3000
	WHERE user_name = '小明';
UPDATE employee SET salary = salary + 1000
	WHERE user_name = '小红';
UPDATE employee SET job = '销售',image = 10
	WHERE user_name = '小明';
UPDATE employee SET user_name = '小张'
	WHERE id = 3;
SELECT * FROM employee;

删除语句 delete

-- 演示delete 语句
-- 删除名字为 小明 的一行
DELETE FROM employee
	WHERE user_name = '小明';
-- 删除所有
DELETE 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,'小明',78,90,84),(2,'小红',56,70,99),
		  (3,'张三',89,74,93),(4,'李四',94,95,82),
		  (5,'张飞',67,23,84),(6,'关羽',65,99,76),
		  (7,'刘备',96,67,87);
-- 查询表中所有数据
SELECT * FROM student;
-- 查询名字和数学成绩
SELECT `name`,math FROM student;
-- 过滤表中重复的数据用 distinct 每个字段都相同，才会去重
SELECT DISTINCT `name`,english FROM student;
-- 统计学生总分
SELECT `name`,(chinese+english+math) FROM student;
-- 所有学生总分加10分
SELECT `name`,(chinese+english+math+10) FROM student;
-- 使用别名表示学生总分
SELECT `name`,(chinese+english+math+10) AS toatl_score FROM student;
-- 英语成绩大于90
SELECT * FROM student
	WHERE english > 90;
-- 查询总分大于200的学生
SELECT * FROM student
	WHERE (chinese+english+math) > 200;
-- 查询math大于60并且id大于90的学生成绩
SELECT * FROM student
	WHERE math > 60 AND id > 3;
-- 查询英语成绩大于语文成绩的学生
SELECT * FROM student
	WHERE english > chinese; 
-- 查询总分大于200分 并且 数学成绩大于语文成绩的姓 关 的同学
SELECT * FROM student
	WHERE (chinese + english + math) > 200 AND math > chinese AND `name` LIKE '关%';
-- 查询英语成绩在80-90之间的学生
SELECT * FROM student
	WHERE english BETWEEN 80 AND 90;
-- 查询数学分数为84，99，87的学生
SELECT * FROM student
	WHERE math = 84 OR math = 99 OR math = 87;
SELECT * FROM student
	WHERE math IN (84,99,87);
-- 查询所有姓小的学生成绩
SELECT * FROM student
	WHERE `name` LIKE '小%';
-- 查询数学分数>80,语文分数>80的学生
SELECT * FROM student 
	WHERE math > 80 AND chinese > 80;
-- 查询总分
SELECT `name`,(chinese+english+math) FROM student;
-- 查询总分为225，174，240的学生
SELECT * FROM student
	WHERE (chinese + english + math) IN (225,174,240);
-- 查询姓李 或者 姓关的学生成绩
SELECT * FROM student
	WHERE `name` LIKE '李%' OR `name` LIKE '关%';

排序 （ order by）

-- 演示order by使用  升序：asc 降序：desc
-- 对数学成绩排序【升序】
SELECT * FROM student
	ORDER BY math ASC;
-- 对总分排序【降序】
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;

条件查询（group by + having）

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

函数

统计函数（count，sum，avg，max，min）

-- 演示count统计函数使用
-- 统计一个班有多少学生
SELECT COUNT(*) FROM student;
-- 统计数学成绩大于90的学生有多少个
SELECT COUNT(*) FROM student
	WHERE math > 90;
-- 统计总分大于250的学生有多少
SELECT COUNT(*) FROM student
	WHERE (chinese + english + math) > 250;
-- count(列) 统计满足这个条件的所有行
SELECT COUNT(chinese) FROM student;

-- 演示sum函数的使用
-- 统计一个班的数学总成绩
SELECT SUM(math) FROM student;
-- 统计一个班的语文、英语、数学总成绩
SELECT SUM(chinese),SUM(english),SUM(math) FROM student;
-- 统计一个班的语文、英语、数学的成绩总和
SELECT SUM(chinese + english + math) FROM student;
-- 统计一个班的语文成绩平均分
SELECT SUM(chinese) / COUNT(*) FROM student;

-- 演示avg函数的使用
-- 求一个班级数学平均分
SELECT AVG(math) FROM student;
-- 求一个班级总分的平均分
SELECT AVG(chinese + english + math) FROM student;

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

字符串函数

charset(str)：返回字符集
concat(string2 , string3…)：连接字符串
instr(string,substring)：返回substring在string中出现的位置，没有返回0
ucase(string)：转换成大写
lcase(string)：转换成小写
left(strinng, length)：从string中的左边起去length个字符
length(string)：字符串长度[按照字节]
replace(str,search_str, replace_str)：在str中用replace_str替换search_str
strcmp(string1, string2)：逐字符比较两字符串大小
substring(str, position [,length])：从str的position开始[从1开始计算]，取length个字符
ltrim(string) rtrim(string) trim：去除前端空格或后端空格

-- charset(str) 返回字符集
SELECT CHARSET(ename) FROM emp; 
-- concat(string2 [,... ]) 连接字串, 将多个列拼接成一列
SELECT CONCAT(ename, ' 工作是 ', job) FROM emp; 
-- instr(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', 'hsp') 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;

数学相关函数

-- 演示数学相关函数
#1. abs(num) 绝对值
SELECT ABS(-10) FROM DUAL; 
#2. bin(decimal_number )十进制转二进制
SELECT BIN(10) FROM DUAL; 
#3. ceiling(number2 ) 向上取整, 得到比 num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL;
#4. conv(number2,from_base,to_base) 进制转换
-- 下面的含义是 8 是十进制的 8, 转成 2 进制输出
SELECT CONV(8, 10, 2) FROM DUAL; 
-- 下面的含义是 8 是 16 进制的 8, 转成 2 进制输出
SELECT CONV(16, 16, 10) FROM DUAL;
#5. floor(number2 ) 向下取整,得到比 num2 小的最大整数
SELECT FLOOR(-1.1) FROM DUAL; 
#6. format(number,decimal_places ) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL; 
#7. HEX (DecimalNumber ) 转十六进制
#8. least(number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL; 
#9. mod(numerator ,denominator ) 求余
SELECT MOD(10, 3) FROM DUAL; 
#10. rand([seed]) RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
-- 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变，
-- 该随机数也不变了
SELECT RAND() FROM DUAL;
SELECT CURRENT_TIMESTAMP() 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; 
-- 创建测试表 信息表
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; 
-- 4. DATE_ADD(date1,INTERVAL d_values d_type) 在data1中加上日期或时间
-- 5. DATE_SUB(date1,INTERVAL d_values d_type) 在data1中减去一个时间
-- 请查询在 10 分钟内发布的新闻, 思路一定要梳理一下. 
SELECT * FROM mes
	WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();
-- 6. DATEDIFF(date1,date2) 两个日期差（结果是天）
-- 请在 mysql 的 sql 语句中求出 2011-11-11 和 1990-1-1 相差多少天
SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;
-- 请用 mysql 的 sql 语句求出你活了多少天? [练习] 1999-03-11 出生
SELECT DATEDIFF(CURRENT_DATE,'1999-03-11') FROM DUAL;
-- 如果你能活 80 岁，求出你还能活多少天.[练习] 1986-11-11 出生
SELECT DATEDIFF(DATE_ADD('1999-03-11', INTERVAL 80 YEAR),'1999-03-11') FROM DUAL;
-- 返回日期 年/月/日
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()秒数，转成指定格式的日期
SELECT FROM_UNIXTIME(1655961893,'%y-%m-%d') FROM DUAL;
SELECT FROM_UNIXTIME(1655961893,'%y-%m-%d %h:%i:%S') FROM DUAL;

加密函数

-- user() 查询用户
SELECT USER() FROM DUAL;
-- database() 查询当前数据库名称
SELECT DATABASE();
-- md5(str) 为字符串算出一个md5 32的字符串，常用(用户密码)加密
SELECT MD5('12345') FROM DUAL;
-- password(str) 加密函数  MySQL8.0删除了
SELECT PASSWORD('qwd') FROM DUAL;

流程控制函数

# IF(expr1,expr2,expr3) 如果 expr1 为 True ,则返回 expr2 否则返回 expr3
SELECT IF(TRUE,'北京','郑州') FROM DUAL;
# IFNULL(expr1,expr2) 如果 expr1 不为空 NULL,则返回 expr1,否则返回 expr2
SELECT IFNULL(NULL,'tom') 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
-- 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;

加强查询

查询

-- ■ 使用 where 子句
-- ?如何查找 1992.1.1 后入职的员工
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;

分页查询

-- 按雇员的 id 号升序取出， 每页显示 3 条记录，请分别显示 第 1 页，第 2 页
-- 第 1 页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 0,3; 
-- 第 2 页
SELECT * FROM emp
	ORDER BY empno
	LIMIT 3,3;

分组函数

数据分组顺序：group by , having , order by , limit

-- (1) 显示每种岗位的雇员总数、平均工资
SELECT COUNT(*), AVG(sal), job FROM emp
	GROUP BY job;
-- (2) 显示雇员总数，以及获得补助的雇员数
SELECT COUNT(*), COUNT(comm) FROM emp; 
-- (3) 显示管理者的总人数
SELECT COUNT(DISTINCT mgr) FROM emp;
-- (4) 显示雇员工资的最大差额
SELECT MAX(sal)- MIN(sal) FROM emp;
/*请统计各个部门(group by) 的平均工资(avg)，并且是大于 1000的(having)，并且按照平均工资从高到低排序(order by)，
取出前两行记录(limit 0, 2)*/
SELECT deptno, AVG(sal) AS avg_sal FROM emp
	GROUP BY deptno
	HAVING avg_sal > 1000
	ORDER BY avg_sal DESC
	LIMIT 0,2;

多表查询

当两个表查询时：

1. 从第一张表取出一行，和第二张表的每一行进行组合，返回结果是 笛卡尔集。
2. 一共返回记录数：第一张表行数 * 第二张表行数。
3. 多表查询的条件不能少于 表的个数-1, 否则会出现 笛卡尔集，过滤条件用 where。

-- 显示部门号、员工名和工资
SELECT emp.deptno, ename, sal
	FROM emp, dept
	WHERE emp.deptno = dept.deptno;
-- 显示部门号为 10 的部门名、员工名和工资
SELECT ename,sal,dname,emp.deptno
	FROM emp, dept
	WHERE emp.deptno = dept.deptno AND emp.deptno = 10;
-- 显示各个员工的姓名，工资，及其工资的级别
SELECT ename, sal, grade
	FROM emp, salgrade
	WHERE sal BETWEEN losal AND hisal;

自连接:

-- 显示公司员工名字和他的上级的名字
SELECT worker.ename AS '职员名', boss.ename AS '上级名'
	FROM emp worker, emp boss
	WHERE worker.mgr = boss.empno;

子查询

子查询：指嵌入在其它 sql 语句中的 select 语句,也叫嵌套查询
单行子查询：指只返回一行数据的子查询语句
多行子查询：返回多行数据的子查询，使用关键字 in

-- 查询SMITH 的部门号
SELECT deptno FROM emp
	WHERE ename = 'SMITH';
-- 如何显示与 SMITH 同一部门的所有员工?
SELECT * FROM emp
	WHERE deptno = (SELECT deptno FROM emp
				WHERE ename = 'SMITH');
-- 查询部门为 10 的工作
SELECT job FROM emp
	WHERE deptno = 10;
-- 如何查询和部门 10 的工作相同的雇员的名字、岗位、工资、部门号, 但是不含 10 号部门自己的雇员
SELECT ename, job, sal, deptno FROM emp
	WHERE job IN (SELECT job FROM emp
				WHERE deptno = 10)
				AND deptno != 10;

子查询当作临时表使用

-- 查询 ecs_goods 各个类别中最高的价格
SELECT cat_id, MAX(shop_price) AS max_price
	FROM ecs_goods
	GROUP BY cat_id;
-- 查询ecs_goods 中各个类别中，价格最高的商品
SELECT goods_id, ecs_goods.cat_id, goods_name, shop_price
	FROM (
		SELECT cat_id, MAX(shop_price) AS max_price
		FROM ecs_goods
		GROUP BY cat_id
	) temp, ecs_goods
	WHERE temp.cat_id = ecs_goods.cat_id
	AND temp.max_price = ecs_goods.shop_price

all 和 any 的使用

-- 显示工资比部门 30 的所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno 
	FROM emp
	WHERE sal > ALL(
		SELECT sal FROM emp
			WHERE deptno = 30);
-- 如何显示工资比部门 30 的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
	FROM emp
	WHERE sal > ANY(
		SELECT sal FROM emp
		WHERE deptno = 30);

多列子查询

-- 请思考如何查询与 allen 的部门和岗位完全相同的所有雇员(并且不含 allen 本人)
SELECT * FROM emp
	WHERE (deptno,job)=(
		SELECT deptno,job
		FROM emp
		WHERE ename = 'allen'
		)AND ename != 'allen';

去重

-- 1.先创建一张临时表 my_tmp , 该表的结构和 my_tab02 一样
CREATE TABLE my_tab LIKE my_tab02;
-- 2.把 my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
INSERT INTO my_tab
	SELECT DISTINCT * FROM my_tab02;
-- 3.清除掉 my_tab02 记录
DELETE FROM my_tab02;
-- 4.把 my_tmp 表的记录复制到 my_tab02
INSERT INTO my_tab02
	SELECT * FROM my_tab;
-- 5.drop 掉临时表 my_tmp
DROP TABLE my_tab;

-- 合并查询
-- union all 就是将两个查询结果合并，不会去重
-- union 就是将两个查询结果合并，会去重
SELECT ename,sal,job FROM emp WHERE sal>2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER' -- 3

外连接

1. 左外连接：如果左侧的表完全显示我们就说是左外连接。
2. 右外连接：如果右侧的表完全显示我们就说是右外连接。

-- 创建 stu
CREATE TABLE stu (
	id INT, `name` VARCHAR(32));
INSERT INTO stu VALUES(1, 'jack'),(2,'tom'),(3, 'kity'),(4, 'nono');
-- 创建 exam
CREATE TABLE exam(
	id INT, grade INT);
INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);
-- 使用左连接
-- （显示所有人的成绩，如果没有成绩，也要显示该人的姓名和 id 号,成绩显示为空）
SELECT `name`, stu.id, grade
	FROM stu LEFT JOIN exam
	ON stu.id = exam.id;
-- 列出部门名称和这些部门的员工信息(名字和工作)，同时列出那些没有员工的部门名(左外连接)
SELECT dname,ename,job
	FROM dept LEFT JOIN emp
	ON emp.deptno = dept.deptno;

约束

约束包括：not null、unique(唯一)、primary key、foreign key、check

主键

1. primary key 不能重复而且不能为null
2. 一张表最多只能有一个主键，但可以是复合主键
3. 主键的指定方式有两种
   • 直接在字段名后指定
   • 在表定义最后写 primary key(列名)

唯一(unique)

字段名 字段类型 unique

foreign key(外键)

foreign key （本表字段名） referenges 主标名(主键名或unique字段名)

1. 外键指向的表的字段，要求是primary key 或者是 unique
2. 表的类型是 innodb，这样的表才支持外键
3. 外键字段的类型要和主键字段的类型一致(长度可以不一致)
4. 外键字段的值，必须在主键字段出现过，或者为null[前提是外键字段允许为null]
5. 一旦建立主外键的关系，数据不能随意删除了

check：用于强制行数据必须满足的条件

自增长

字段名 整型 primary key auto_increment

1. 一般来说自增长是和primary key配合使用的
2. 自增长也可以单独使用，需要配合unique使用
3. 自增长修饰的字段为整数型的（小数少用）
4. 自增长默认从1开始，也可以用 alter table 表名 auto_increment = 新的开始值
5. 如果你添加数据时，给自增长字段指定了值，则以指定的值为准。

-- 创建表
CREATE TABLE t1(
	id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT '',
	`name` VARCHAR(32) NOT NULL DEFAULT '');
INSERT INTO t1
	VALUES(NULL, 'tom@qq.com', 'tom');
INSERT INTO t1
	(email, `name`) VALUES('hsp@sohu.com', 'hsp');
SELECT * FROM t1;
-- 修改默认的自增长开始值
ALTER TABLE t1 AUTO_INCREMENT = 100;

索引

索引的类型

1. 主键索引，主键自动的为主索引(类型primary key)

2. 唯一索引（unique）

3. 普通索引（index）

4. 全文索引（fulltext）[适用于MyISAM]

   常作为查询条件的需要创建索引

-- 演示 mysql 的索引的使用
-- 创建表
CREATE TABLE t8(
	id INT,
	`name` VARCHAR(32));
-- 查询表是否有索引
SHOW INDEXES FROM t8;
-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t8(id);
-- 添加普通索引 方式1
CREATE INDEX id_index ON t8(id);
-- 添加普通索引 方式 2
ALTER TABLE t8 ADD INDEX id_index(id);
-- 添加主键索引
ALTER TABLE t8 ADD PRIMARY KEY (id);
-- 删除索引
DROP INDEX id_index ON t8;
-- 删除主键索引
ALTER TABLE t8 DROP PRIMARY KEY;
-- 查询索引
SHOW INDEX FROM t8;
SHOW INDEXES FROM t8;
SHOW KEYS FROM t8;
DESC t8;

事务

事务的几个重要操作

1. start transaction：开始一个事务
2. savepoint 保存点名：设置保存点
3. rollback to 保存点名：回退事务
4. rollback：回退全部事务
5. commit：提交事务，所有的操作生效，不能回退

事务细节

1. 如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚
2. 如果开始一个事务，没有保存点，执行rollback，默认回退到事务开始的状态
3. 在事务中可以创建多个保存点，然后可以选择回退到哪个保存点
4. 事务机制需要innodb的存储引擎才可以使用，不能用myisam

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

隔离级别

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

隔离级别	脏读	不可重复读	幻读	加锁读
读未提交(read uncommitted)	√	√	√	不加锁
读已提交(read committed)	×	√	√	不加锁
可重复读(repeatable read)	×	×	×	不加锁
可穿行化(serializable)	×	×	×	加锁

设置事务隔离级别

1. 查看当前会话隔离级别

   select @@transaction_isolation;

2. 查看系统当前隔离级别

   SELECT @@global.transaction_isolation;

3. 设置当前会话隔离级别

   set session transaction isolation level 隔离级别;

4. 设置系统当前隔离级别

   set global transaction isolation level 隔离级别;

5. mysql默认的事务隔离级别是 repeatable read

   -- 查看当前会话隔离级别
   SELECT @@transaction_isolation;
   -- 查看系统当前隔离级
   SELECT @@global.transaction_isolation;
   -- 设置当前会话隔离级别
   SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
   -- 设置系统当前隔离级别
   SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
   

存储引擎

主要的存储引擎特点

特点	Myisam	InnoDB	Memory	Archive
批量插入的速度	高	低	高	非常高
事务安全		支持
全文索引	支持
锁机制	表锁	行锁	表锁	行锁
存储限制	没有	64TB	有	没有
B树索引	支持	支持	支持
哈希索引		支持	支持
集群索引		支持
数据缓存		支持	支持
索引缓存	支持	支持	支持
数据可压缩	支持			支持
空间使用	低	高	N/A	非常低
内存使用	低	高	中等	低
支持外键		支持

三大索引

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

如何选择存储引擎

1. 如果不需要事务，处理的只是基本的CRUD操作，选择MyISAM，速度快。
2. 如果需要支持事务选择InnoDB。
3. Memory存储引擎是将数据存储在内存中，由于没有磁盘I/O的等待，速度极快。

修改存储引擎：select table 表名 engine = 引擎;

ALTER TABLE `t29` ENGINE = INNODB;
-- 在建表时中设置引擎
CREATE TABLE t29 (
	id INT, 
    `name` VARCHAR(32)) ENGINE MEMORY;

视图

视图的总结

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

-- 视图的使用
-- 创建一个视图 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;
-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01;
-- 删除视图
DROP VIEW emp_view01;
-- 修改视图 会影响到基表
UPDATE emp_view01
	SET job = 'MANAGER'
	WHERE empno = 7369;
-- 修改基本表， 会影响到视图
UPDATE emp 
	SET job = 'SALESMAN'
	WHERE empno = 7369;
-- 视图中可以再使用视图
CREATE VIEW emp_view02 AS
	SELECT empno, ename FROM emp_view01;
-- 针对 emp ，dept , 和 salgrade 张三表.创建一个视图 emp_view03，
-- 可以显示雇员编号，雇员名，雇员部门名称和薪水级别
CREATE VIEW emp_view03
	AS
	SELECT empno, ename, dname, grade
	FROM emp, dept, salgrade
	WHERE emp.deptno = dept.deptno AND
	(sal BETWEEN losal AND hisal);

MySQL管理

用户

1. host：允许登录的“位置”，localhost表示该用户只允许本机登录，也可以指定IP地址，例如：192.168.1.100
2. user：用户名
3. authentication_string：密码

创建用户：create user ‘用户名’@‘允许登录位置’ identified by ‘密码’

删除用户：drop user ‘用户名’@‘允许登录位置’

修改密码：alter user ‘用户名’@‘允许登录位置’ identified with mysql_native_password by ‘新密码’;

授权

1. 用户授权：grant 权限列表 on 库.对象名 to ‘用户名’@‘登录位置’
2. 回收授权：revoke 权限列表 on 库.对象名 from ‘用户名’@‘登录位置’

-- 创建用户
CREATE USER 'wang'@'localhost' IDENTIFIED BY '123456';
-- 删除用户
DROP USER 'wang'@'localhost';
-- 用户修改密码
ALTER USER 'wang'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
-- 使用 root 用户创建 testdb ,表 news
CREATE DATABASE testdb;
CREATE TABLE news (
	id INT , 
	content VARCHAR(32));
-- 添加一条测试数据
INSERT INTO news VALUES(100, '北京新闻');
SELECT * FROM news;
-- 给 wang 分配查看 news 表和 添加 news 的权限
GRANT SELECT,INSERT
	ON testdb.news
	TO 'wang'@'localhost';
-- 可以增加 update 权限
GRANT UPDATE
	ON testdb.news
	TO 'wang'@'localhost';
-- 回收 shunping 用户在 testdb.news 表的所有权限
REVOKE SELECT,UPDATE,INSERT ON testdb.news FROM 'wang'@'localhost';
REVOKE ALL ON testdb.news FROM 'wang'@'localhost';