MySQL语句
一、修改表
#创建一个员工表，选用适当的数据类型
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,'韩帅比','男','1999-03-11',
'2021-11-10 11:11:11','后端',30000,'大帅逼一个');
SELECT * FROM `emp`;
#修改表的操作练习
-- 1.员工表emp增加一个image列，varchar类型（要求再resume后面）。
ALTER TABLE emp
ADD image VARCHAR(32) NOT NULL DEFAULT ''
AFTER `resume`
-- 2.修改job列，使其长度为60
ALTER TABLE emp
MODIFY job VARCHAR(60) NOT NULL DEFAULT ''
-- 3.删除sex列
ALTER TABLE emp
DROP sex
-- 4.表名改为employee
RENAME TABLE emp TO employee
-- 5.修改表的字符集为utf8
ALTER TABLE employee CHARACTER SET utf8
-- 6.列名name修改为user_name
ALTER TABLE employee
CHANGE `name` `user_name` VARCHAR(32) NOT NULL DEFAULT ''
-- 7.显示表结构，可以查看表的所有列
DESC employee
二、insert语句
#练习insert语句
-- 创建一张商品表goods
-- 添加两条记录
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;
#说明insert语句的细节
-- 1.插入的数据应与字段的数据类型相同
-- 2.数据的长度应在列的规定范围内
-- 3.在values中列出的数据位置必须与被加入的列的排列位置相对应
-- 4.字符和日期型数据应包含在单引号中
INSERT INTO `goods`(id,goods_name,price)
VALUES(10,华为手机,2000); -- 错误的写法
SELECT * FROM goods;
-- 5.列可以插入空值[前提是该字段允许为空],insert into table value(null)
-- 6. insert into tab_name(列名...) values(),(),() 形式添加多条记录
INSERT INTO `goods`(id,goods_name,price)
VALUES(15,'三星手机',2300),(60,'海尔空调',1900);
-- 7.如果是给表中的所有字段添加数据，可以不写前面的字段名称
-- 8.默认值的使用，当不给某个字段值时，如果有默认值就会添加，否则报错
-- 如果某个列没有指定not null，那么当添加数据时，没有给定值会默认给null
INSERT INTO `goods` (id, goods_name)
VALUES(80,'格力手机');-- 可以
INSERT INTO `goods` (id, goods_name,price)
VALUES(80,'格力手机');-- 不可以
三、update语句
#演示update语句
-- 1.将所有员工薪水修改为50000
UPDATE employee SET salary = 50000;-- 不写where表示修改所有记录
SELECT * FROM employee;
-- 2.将姓名为韩帅比的员工薪水修改为60000
UPDATE employee
SET salary = 60000
WHERE user_name = '韩帅比';
-- 3.将老妖怪的薪水在原基础增加1000
INSERT INTO employee
VALUES(200,'老妖怪','1990-11-11','2000-11-11 10:10:10','捶背的',5000,'给大王捶背','E:\临时文件存储地\\1.png');
UPDATE employee
SET salary = salary + 1000;
WHERE user_name = '老妖怪';
-- 4.可以修改多列
UPDATE employee
SET salary = salary + 1000,job = '出主意的'
WHERE user_name = '老妖怪';
四、delete语句
#delete语句演示
-- 1.删除表中名称为老妖怪的记录
DELETE FROM employee
WHERE user_name = '老妖怪';
SELECT * FROM employee
-- 2.删除表中所有记录
DELETE FROM employee
-- delete语句不能删除某一列的值（只能用update设为null或者''）
-- delete删除的是记录，不是表，删除表需要使用 drop table 表名
五、select语句
-- select语句
-- 创建新的表student
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,'帅比',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,67);
INSERT INTO student(id,`name`,chinese,english,math) VALUES(5,'赵云',82,84,90);
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);
-- 1.查询表中所有学生的信息
SELECT * FROM student
-- 2.查询表中所有学生的姓名和对应的英语成绩
SELECT `name`,english FROM student
-- 3.过滤表中重复数据distinct
SELECT DISTINCT `name`,english FROM student -- 要查询所有内容相同
SELECT DISTINCT english FROM student-- 这样才能筛选掉重复数据
-- 4.统计每个学生的总分
SELECT `name`,(chinese + english + math) FROM student;
-- 5.在所有学生总分加上10分
SELECT `name`,(chinese + english + math + 10) FROM student;
-- 6.使用别名表示学生分数
SELECT `name` AS '名字',
(chinese + english + math + 10) AS '总分'
FROM student;
-- 7.查询姓名为赵云的学生成绩
SELECT * FROM student
WHERE `name` = '赵云'
-- 8.查询英语成绩大于90的同学
SELECT * FROM student
WHERE english > 90;
-- 9.查询总分大于200的学生
SELECT * FROM student
WHERE (chinese + english + math) > 200;
-- 10.查询math大于60并且(and) id小于9的学生成绩
SELECT * FROM student
WHERE math > 60 AND id < 9
-- 11.查询英语成绩大于语文成绩的学生
SELECT * FROM student
WHERE english > chinese;
-- 12.查询总分大于200，并且数学大于语文成绩的姓帅的学生
-- 帅%表示 名字以帅开头的就可以
SELECT * FROM student
WHERE (chinese + math + english) > 200 AND
math > chinese AND `name` LIKE '帅%'
-- 13.查询英语分数在80-90之间的学生
SELECT * FROM student
WHERE english BETWEEN 80 AND 90;-- 闭区间，包含两头
SELECT * FROM student
WHERE english >= 80 AND english <= 90;
-- 14.查询数学分数为89，90，91的同学
SELECT * FROM student
WHERE math = 89 OR math = 90 OR math = 91;
SELECT * FROM student
WHERE math IN (89,90,91);
-- 15.查询所有姓李的学生成绩
SELECT * FROM student
WHERE `name` LIKE '赵%'
-- 16.查询数学>80,语文>80
SELECT * FROM student
WHERE math > 80 AND chinese > 80
-- 17.Order by指定排序的列，排序的列既可以是表中的列名，也可以是select语句后指定的列名
-- Asc升序（默认），Desc降序，order by子句应位于select语句的结尾
-- 18.对数学成绩排序后输出（升序）
SELECT * FROM student
ORDER BY math;
-- 19.对总分按从高到低的顺序输出（降序）
SELECT `name`, (chinese + english + math) AS total_score FROM student
ORDER BY total_score DESC;
-- 20.对姓赵的学生总成绩排序输出（降序）
INSERT INTO student VALUES(8,'赵子龙',76,67,98);
SELECT `name`, (chinese + english + math) AS total_score FROM student
WHERE `name` LIKE '赵%'
ORDER BY total_score DESC;
六、合计/统计函数
#合计/统计函数 count,sum,avg
-- count
-- count返回行的总数
-- 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
-- count(*)和count(列)的区别
-- 解释：count（*）返回满足条件的记录的行数
-- count（列）：统计满足条件的某列有多少个，但是会排除为null的
CREATE TABLE t15(
`name` VARCHAR(20));
INSERT INTO t15 VALUES('tom');
INSERT INTO t15 VALUES('jkl');
INSERT INTO t15 VALUES(NULL);
SELECT COUNT(*) FROM t15
SELECT COUNT(`name`) FROM t15
-- sum函数
-- 1.统计一个班数学总成绩
SELECT SUM(math) FROM student;
-- 2.统计一个班级三科各科总成绩
SELECT SUM(math),SUM(chinese),SUM(english) FROM student;
-- 3.统计三科的成绩总和
SELECT SUM(math + chinese + english) FROM student;
-- 4.统计语文平均分
SELECT SUM(chinese) / COUNT(*) FROM student;
-- 演示AVG的使用
-- 1.求一个班数学平均分
SELECT AVG(math) FROM student;
-- 2.总分平均分
SELECT AVG(math + chinese + english) FROM student;
-- max/min
-- 1.求班级最高分和最低分
SELECT MAX(math + chinese + english), MIN(math + chinese + english) FROM student;
-- 2.求出班级数学最高分和最低分
SELECT MAX(math) AS high, MIN(math) AS low FROM student
七、group by和having
-- 部门表
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');
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',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);
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;
-- 演示group by的使用 + having
-- 1.显示每个部门的平均工资和最高工资
SELECT FORMAT(AVG(sal),2),MAX(sal),deptno -- 显示的列
FROM emp GROUP BY deptno; -- 分组的标准
-- 2.显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal), deptno, job
FROM emp GROUP BY deptno , job;
-- 3.显示平均工资低于2000的部门号和他的平均工资
-- 分析 1.显示部门的平均工资；
-- 2.筛选低于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;
-- 增强group by的使用
-- 1.显示每种岗位的雇员总数、平均工资
SELECT COUNT(*),AVG(sal),job
FROM emp
GROUP BY job;
-- 2.显示雇员的总数，以及获得补助的雇员数
SELECT COUNT(*), COUNT(comm) FROM emp-- comm列如果有null，该记录不会被统计进去
SELECT COUNT(*), COUNT(IF(comm IS NULL,NULL,1)) FROM emp
-- 3.显示管理者的人数
SELECT COUNT(DISTINCT mgr)
FROM emp;
-- 4.显示雇员工资的最大差额
SELECT MAX(sal) - MIN(sal)
FROM emp
-- 数据分组总结:
-- group by -> having -> order by -> limit
-- 应用案例：统计各个部门的工资，并且时大于1000的，并且按照平均工资从高到低排序，取出前两行记录
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
HAVING AVG(sal) > 1000
ORDER BY avg_sal DESC
LIMIT 0,2
八、string字符串函数
-- 演示字符串相关函数的使用
-- 1.charset(str)返回字符集
SELECT CHARSET(ename) FROM emp;
-- 2.concat(string2 [,...])连接子串
SELECT CONCAT(ename,' job is ',job) FROM emp;
-- 3.instr(string,substring)返回substring再string中出现的位置，没有就返回0
SELECT INSTR('hanlipengdashuaibi','peng') FROM DUAL; -- dual亚元表，系统表
-- 4.ucase(string2) 转为大写
SELECT UCASE(ename) FROM emp;
-- 5.lcase(string2) 转为小写
SELECT LCASE(ename) FROM emp;-- 只是查询为小写，不修改表本身
SELECT * FROM emp
-- 6.left(string2,length) 从string2中的左边起取length个字符,right同理
SELECT LEFT(ename,2) FROM emp;
-- 7.length(string) string 长度[按照字节]统计长度，utf8中一个汉字3个字节
SELECT LENGTH (ename) FROM emp;
-- 8.replace(str, search_str, replace_str) 在str中用replace_str替换search_str
-- 如果是manage就替换成经理
SELECT ename, REPLACE(job,'MANAGER','经理') FROM emp;
-- 9.strcmp(string1,string2) 逐个字符比较两子串大小,1<2返回-1，相等返回0，1>2返回1
SELECT STRCMP('hlp','hlp') FROM DUAL;
SELECT STRCMP('hz','hwp') FROM DUAL;
-- 10.substring(str, position [, length]) 从str的position开始，取length个字符
SELECT SUBSTRING(ename,2,2) FROM emp;-- 从ename列的第二个位置开始去除两个字符
-- 11.ltrim(string2) rtrim(string2) trim(string2)去掉空格
SELECT LTRIM(' 1era sf ') FROM DUAL;
SELECT RTRIM(' 1era sf ') FROM DUAL;
SELECT TRIM(' 1era sf ') FROM DUAL;
-- 练习：以首字母小写的方式显示所有员工emp表的姓名
-- 方法一：1.先取出ename的第一个字符转成小写的
-- 2.把它和后面的内容进行拼接输出
SELECT CONCAT ( LCASE(SUBSTRING(ename,1,1)) , SUBSTRING(ename,2) ) AS new_name
FROM emp;
-- 方法二：
SELECT CONCAT ( LCASE(LEFT(ename,1)) , SUBSTRING(ename,2) ) AS new_name
FROM emp;
九、math数学函数
-- 数学相关函数
-- 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(num2, from_base, to base) 进制转换
SELECT CONV(10,10,2) FROM DUAL;
-- 5.floor(number2) 向下取整，得到比num2小的最大整数
SELECT FLOOR(-1.1) FROM DUAL;
-- 6.format(number, decimal_places) 保留小数位数,四舍五入
SELECT FORMAT(78.12351412,3) FROM DUAL
-- 7.hex(DecimalNumber) 转十六进制
-- 8.least(number, number2 [,...]) 求最小值,greatest求最大
SELECT LEAST(0,1,-10,4);
SELECT GREATEST(0,1,-10,4);
-- 9.mod(numerator,denominator) 求余
SELECT MOD(10,3) FROM DUAL;
-- 10.rand([seed]) rand([seed]) 返回随机数，
-- 范围 0 ≤ v ≤ 1.0;加上seed随机完就会固定下来
SELECT RAND(3) FROM DUAL;
十、date日期函数
-- 演示日期时间相关函数
-- 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.date(datetime) 返回datetime的日期部分
-- 5.date_add(date2, interval d_value d_type) 在date2中加上日期或者时间
-- 6.date_sub(date2, interval d_value d_type) 在date2中减去一个时间
-- 7.datediff(date1,date2) 两个日期差（结果是天）
-- 8.timediff(date1,date2) 两个时间差（多少时分秒）
-- 9.now() 当前时间
-- 10.year|month|date(datetime)
SELECT YEAR(NOW()) FROM DUAL
SELECT MONTH(NOW()) FROM DUAL
-- 11.unix_timestamp() 返回1970-1-1到现在的秒数
SELECT UNIX_TIMESTAMP()/365/24/3600 FROM DUAL;
-- 12.from_unixtime() 年月日 可以把一个unix_timestamp秒数转成指定格式的日期
SELECT FROM_UNIXTIME(1618483484,'%Y-%M-%D') FROM DUAL;-- '%Y-%m-%d' 表示年月日
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y-%m-%d %h:%i:%s') FROM DUAL;
#注意分钟用%i表示，不是%m
-- 创建测试表
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;
-- 应用实例
-- a.显示所有新闻信息，发布日期只显示日期，不显示时间
SELECT id,content, DATE(send_time) FROM mes
-- b.请查询十分钟内发布的新闻
SELECT * FROM mes
WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();
SELECT * FROM mes
WHERE DATE_SUB(NOW(), INTERVAL 10 MINUTE) <= send_time
-- c.请在mysql的sql语句中求出2011-11-11和1990-1-1相差多少天
SELECT DATEDIFF('2011-11-11','1990-1-1') FROM DUAL
-- d.请用mysql的sql语句中求出你出生了多少天
SELECT DATEDIFF('2021-9-14','1999-3-11') FROM DUAL
-- e.如果你能活80岁，求出你还能活多少天
SELECT DATEDIFF(DATE_ADD('1999-3-11', INTERVAL 80 YEAR), NOW()) FROM DUAL;
十一、密码和系统函数
-- 演示加密函数和系统函数
-- 1.user() 查询用户
SELECT USER() FROM DUAL; -- 用户@IP地址,可以查看登录到MySQL的有哪些用户，以及登录的IP
-- 2.database() 查询当前使用的数据库名称
SELECT DATABASE() FROM DUAL;
-- 3.MD5(str) 为字符串算出一个md5 32的字符串，常用于（用户密码）加密
SELECT MD5('hlp') FROM DUAL;
SELECT LENGTH(MD5('hlp')) FROM DUAL;
-- 演示用户表，存放密码时，是md5
CREATE TABLE hlp_user(
id INT,
`name` VARCHAR(32) NOT NULL DEFAULT '',
pwd CHAR(32) NOT NULL DEFAULT '');
INSERT INTO hlp_user VALUES(100,'韩立鹏',MD5('hlp'));
SELECT * FROM hlp_user;
SELECT * FROM hlp_user -- sql注入
WHERE `name` = '韩立鹏' AND pwd = MD5('hlp');
-- 4.password（str）-- 也是加密函数,MySQL数据库的用户密码就是password函数加密
SELECT PASSWORD('hlp') FROM DUAL;
SELECT PASSWORD('hlp') FROM DUAL; -- *FBCBE462218D2243E98CA7B3CEEAB551571A3E99
-- 5.select * from mysql.user \G 从原文密码str 计算并返回密码字符串
-- 通常用于对mysql数据库的用户密码加密
-- mysql.user 表示数据库.表
SELECT * FROM mysql.user
十二、流程控制函数
-- 流程控制函数
-- 1.if(expr1,expr2,expr3) 如果expr1为true，则返回expr2，否则返回expr3
SELECT IF(TRUE,'北京','上海') FROM DUAL
-- 2.ifnull(expr1,expr2) 如果expr1不为空null，则返回expr1，否则返回expr2
SELECT IFNULL(NULL,'天津') FROM DUAL
-- 3.select case when expr1 then expr2
-- when expr3 then expr4
-- else expr5 end
-- 如果expr1为true，则返回expr2;如果expr1为false，且expr3为true，则返回expr4；其余返回expr5
-- a.查询emp表，如果comm是null，则显示0.0
SELECT ename,IF(comm IS NULL,0.0,comm) FROM emp;
SELECT ename,IFNULL(comm,0.0) FROM emp;
-- b.如果emp表的job是clerk则显示职员，如果是manager则显示经理，如果是salesman则显示销售人员，其他正常显示
SELECT ename, (SELECT CASE
WHEN job LIKE 'CLERK' THEN '职员'
WHEN job = 'MANAGER' THEN '经理'
WHEN job = 'SALESMAN' THEN '销售人员'
ELSE job END
) AS 'job', job
FROM emp
十三、查询加强
-- 查询加强
#1.使用where子句
-- a.查找1991.1.1后入职的员工
SELECT * FROM emp
WHERE hiredate > '1991-1-1';
#2.使用like操作符。 %：表示0到多个任意字符 _:表示单个字符;
-- a.显示首字符为s的员工姓名和工资
SELECT ename,sal FROM emp
WHERE ename LIKE 'S%' -- s%,s开头; %s,s结尾
-- b.显示第三个字符为O的所有员工的姓名和工资
SELECT ename,sal FROM emp
WHERE ename LIKE '__O%';
-- c.显示没有上级的员工
SELECT * FROM emp
WHERE mgr IS NULL;
-- d.查询表结构
DESC emp;
#3.使用order by子句
-- a.按照工资的从低到高顺序，显示雇员信息
SELECT * FROM emp
ORDER BY sal ASC -- 默认升序asc，可以不写
-- b.按照部门号升序而雇员的工资降序排列，显示雇员信息
SELECT * FROM emp
ORDER BY deptno ASC,sal DESC;
十四、分页查询
#分页查询
-- 基本语法select ...limit start, rows,
-- 表示从start+1 行开始取，取出rows行，start从0开始计算
-- 1.按雇员的id号升序取出，每页显示3条记录，请分别显示第一页，第二页，第三页
-- 第1页
SELECT * FROM emp
ORDER BY empno
LIMIT 0,3;
-- 第2页
SELECT * FROM emp
ORDER BY empno
LIMIT 3,3;
-- 第3页
SELECT * FROM emp
ORDER BY empno
LIMIT 6,3;
十五、多表查询
#多表查询
-- 1.显示雇员名，雇员工资及所在部门的名字。 默认显示[笛卡尔集]，表1 * 表2
-- 分析：雇员名，雇员工资来自emp表；部门名字来自dept表
-- 当我们需要指定显示某个表的列时，需要使用语法 --> 表.列表
SELECT ename,sal,dname,emp.deptno
FROM emp,dept
WHERE emp.deptno = dept.deptno
-- 2.显示部门号为10的部门名、员工名和工资
SELECT ename,sal,dname
FROM emp,dept
WHERE emp.deptno = dept.deptno AND emp.deptno = 10
-- 或者把and语句替换为 -> having emp.deptno = 10
-- 3.显示各个员工的姓名，工资及其工资的级别
SELECT ename,sal,grade
FROM emp,salgrade
WHERE emp.sal BETWEEN losal AND hisal
#多表查询的自连接
-- 思考题：显示公司员工和他的上级的名字
-- 自连接特点：1.把同一张表当作两张表来用
-- 2.需要给表取别名 -> 表名 表别名 不需要as
-- 3.列名不明确，可以指定列的别名 -> 列名 as 列的别名
SELECT worker.ename AS '职员名', boss.ename AS '上级名'
FROM emp worker, emp boss
WHERE worker.mgr = boss.empno;
十六、子查询
#子查询
#A.单行子查询
-- 1.显示和smith同一部门的所有员工
SELECT *
FROM emp
WHERE deptno = (SELECT deptno
FROM emp
WHERE ename = 'SMITH')
#B.多行子查询,使用关键字in
-- 2.查询和10号部门的工作相同的雇员的名字、岗位、工资、部门号，但是不包含10号部门
SELECT ename, job, sal, deptno
FROM emp
WHERE job IN (SELECT DISTINCT job
FROM emp
WHERE deptno = 10
) AND deptno != 10;
#C.多列子查询
-- 查询与ALLEN的部门和岗位完全相同发的所有雇员（不包括ALLEN）
SELECT * FROM emp
WHERE (deptno, job) = (SELECT deptno, job
FROM emp
WHERE ename = 'ALLEN'
)AND ename != 'ALLEN';
#D.临时表
-- 查询ecshop中各个类别中，价格最高的商品
-- 把子查询当作一张临时表可以解决很多复杂的查询
-- 这里缺少韩老师的文件，只写了查询语句没创建表，表内容太多了懒得打
-- 临时表：
SELECT cat_id, MAX(shop_price)
FROM ecs_goods
GROUP BY cat_id
-- 完整答案：
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
-- 子查询练习
-- 查找每个部门工资高于本部门平均工资的人的资料
-- 1.先得到每个部门的部门号和平均工资
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
-- 2.把上面的结果当作子查询，和emp进行多表查询
SELECT ename, sal, temp.avg_sal, emp.deptno
FROM emp,(SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno) temp
WHERE emp.deptno = temp.deptno AND emp.sal > temp.avg_sal
-- 3.查找每个部门最高工资的人
SELECT ename, sal, temp.max_sal, emp.deptno
FROM emp,(SELECT deptno, MAX(sal) AS max_sal
FROM emp
GROUP BY deptno) temp
WHERE emp.deptno = temp.deptno AND emp.sal = temp.max_sal
-- 4.查询每个部门的信息（部门名，编号，地址）和人员数量
SELECT dname, dept.deptno, loc, tmp.人数
FROM dept,(SELECT COUNT(*) AS '人数', deptno
FROM emp
GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno
-- 还有一种写法, 表.* 表示将该表所有列都显示出来
SELECT tmp.*, dname, loc
FROM dept,(SELECT COUNT(*) AS '人数', deptno
FROM emp
GROUP BY deptno
) tmp
WHERE tmp.deptno = dept.deptno
十七、all和any的使用
-- all和any的使用
-- 1.显示工资比部门30所有员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
FROM emp
WHERE sal > ALL(
SELECT sal
FROM emp
WHERE deptno = 30)
-- 或者：
SELECT ename, sal, deptno
FROM emp
WHERE sal > (
SELECT MAX(sal)
FROM emp
WHERE deptno = 30)
-- 显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号
SELECT ename, sal, deptno
FROM emp
WHERE sal > ANY(
SELECT sal
FROM emp
WHERE deptno = 30)
十八、表复制（蠕虫复制）
-- 表复制（蠕虫复制）
-- 为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据
CREATE TABLE my_tab01
(id INT,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT);
DESC my_tab01
SELECT * FROM my_tab01
-- 1.自我复制
-- a.先把emp表的记录复制到 my_tab01
INSERT INTO my_tab01
(id, `name`, sal, job, deptno)
SELECT empno, ename, sal, job, deptno FROM emp;
-- b.自我复制
INSERT INTO my_tab01
SELECT * FROM my_tab01;
SELECT COUNT(*) FROM my_tab01;
-- 思考题：删除表中重复数据
-- a.先创建一张表my_tab02
CREATE TABLE my_tab02 LIKE emp; -- 这个语句时把emp的表结构复制到my_tab02
-- b.让my_tab02有重复的记录
INSERT INTO my_tab02
SELECT * FROM emp;
INSERT INTO my_tab02
SELECT * FROM my_tab02;
-- c.考虑去重
-- (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掉临时表
DROP TABLE my_tmp;
-- (6)验证结果
SELECT * FROM my_tab02;
十九、合并查询
-- 合并查询
SELECT ename, sal, job FROM emp WHERE sal > 2500;
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
-- 1.union all 就是将两个查询结果合并，不会去重
SELECT ename, sal, job FROM emp WHERE sal > 2500
UNION ALL
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
-- 2.union 会去重
SELECT ename, sal, job FROM emp WHERE sal > 2500
UNION
SELECT ename, sal, job FROM emp WHERE job = 'MANAGER';
二十、外连接
-- 外连接
-- 左外连接（左侧的表完全显示，即左表没有右表匹配的内容，也会全部显示）
-- 右外连接（右侧的表完全显示）
-- 思考题：列出部门名称和这些部门的员工名称和工作，同时要求显示出那些没有员工的部门
-- a.使用我们学习过的多表查询的sql
SELECT dname, ename, job
FROM emp, dept
WHERE emp.deptno = dept.deptno
ORDER BY dname -- 达不到要求，效果并不好，因为40部门没有员工，最终不显示
-- 学生表
CREATE TABLE stu(id INT,`name` VARCHAR(32));
INSERT INTO stu VALUES(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono')
SELECT * FROM stu
-- 考试表
CREATE TABLE exam(id INT, grade INT)
INSERT INTO exam VALUES(1,56),(2,76),(11,8)
SELECT * FROM exam
-- 1.使用左连接（显示所有人成绩，如果没有成绩，也显示该人的姓名和id,成绩显示为空）
SELECT `name`, stu.id, grade
FROM stu, exam
WHERE stu.id = exam.id;
-- 改成左外连接
SELECT `name`, stu.id, grade
FROM stu LEFT JOIN exam
ON stu.id = exam.id;
-- 2.使用右外连接（显示所有成绩，如果没有名字匹配，显示空）
SELECT `name`,grade
FROM stu RIGHT JOIN exam
ON stu.id = exam.id;
-- 3.列出部门名称和这些部门的员工信息（名字和工作），同时列出没有员工的部门名
SELECT * FROM emp
SELECT * FROM dept
-- a.左外连接
SELECT dname, ename, job
FROM dept LEFT JOIN emp
ON dept.deptno = emp.deptno
ORDER BY dname
-- b.右外连接
SELECT dname, ename, job
FROM emp RIGHT JOIN dept
ON dept.deptno = emp.deptno
ORDER BY dname
二十一、主键
-- 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@sohu.com')
INSERT INTO t17
VALUES(2,'tom','tom@sohu.com')
#INSERT INTO t17 VALUES(1,'kack','@sohu.com')错误，id重复
-- 主键使用的细节
-- 1.主键的值也不能为空
-- 2.一张表最多只能有一个主键，但可以是复合主键：
CREATE TABLE t18
(id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id,`name`) -- 复合主键 -> id和name 同时不能相同，1,'tom'和1,'jack'不会报错
);
INSERT INTO t18
VALUES(1,'jack','jack@sohu.com')
INSERT INTO t18
VALUES(1,'tom','tom@sohu.com')
SELECT * FROM t18
-- 3.主键的指定方式有两种
-- a.直接在字段名后指定：字段名 primary key
-- b.在表定义最后写parimary key(列名)
CREATE TABLE t19
(id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(`name`) -- 可以写复合主键，也可以写单个主键
);
-- 4.使用 desc 表名 可以看到primary key的情况
DESC t18
DESC t19
二十二、Unique
-- unique的使用
CREATE TABLE t20
(id INT UNIQUE, -- 表示id列不可重复
`name` VARCHAR(32),
email VARCHAR(32)
);
INSERT INTO t20
VALUES(1,'jack','jack@sohu.com');
SELECT * FROM t20;
-- unique使用的细节：
-- 1.如果没有指定not null，则unique字段可以有多个null
INSERT INTO t20
VALUES(NULL,'tom','tom@sohu.com');
-- 2.一张表可以有多个unique字段
CREATE TABLE t21
(id INT UNIQUE, -- 表示id列不可重复
`name` VARCHAR(32) UNIQUE, -- 表示name也不可以重复
email VARCHAR(32)
);
DESC t21
二十三、Foreign key外键
-- foreign key外键演示
-- 用于定义主表和从表之间的关系：
-- 外键约束要定义在从表上，
-- 主表则必须具有主键约束或是unique约束，
-- 当定义外键约束后，要求外键列数据必须在主表的主键列存在或为null。
-- 细节：
-- 1.外键指向的表的字段，要是primary key或者是unique
-- 2.表是innodb，才支持外键
-- 3.外键类型可以和主键一样，但长度可以不一样
-- 4.外键字段的值，必须在主键字段中出现过，或者为null(前提是外键字段允许为null)
-- 5.一旦建立主外键，就不能随意删除数据了
-- 创建 主表 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');
INSERT INTO my_stu
VALUES(1,'tom',100),(2,'jack',100),(3,'jerry',200)
SELECT * FROM my_class;
SELECT * FROM my_stu;
二十四、Check
-- 演示check的使用
-- mysql 5.7 目前还不支持 check ,只做语法校验，但不会生效
-- 学习 oracle, sql server, 这两个数据库是真的生效
CREATE TABLE t22(
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 t22
VALUES(1,'jack','mid',1);
二十五、自增长
-- 自增长
CREATE TABLE t23
(id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT '');
INSERT INTO t23
VALUES(NULL,'jack@qq.com','jack');
INSERT INTO t23
VALUES(NULL,'tom@qq.com','tom');
INSERT INTO t23 (email,`name`)
VALUES('hlp@sohu.com','hlp')
SELECT * FROM t23
-- 自增长的细节：
-- 1.一般和primary key配合使用
-- 2.自增长也可以单独使用（但是需要配合一个unique）
-- 3.自增长修饰的字段为整数型的（虽然小数也可以但是很少）
-- 4.自增长默认从1开始，你也可以通过如下命令修改 alter table 表名 auto_increment == xxx;
-- 5.如果添加数据时，给自增长字段（列）指定的值，则以指定的值为准
CREATE TABLE t24
(id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT '');
ALTER TABLE t24 AUTO_INCREMENT = 100;
INSERT INTO t24 (email,`name`)
VALUES('hlp@sohu.com','hlp')
SELECT * FROM t24
INSERT INTO t24
VALUES(666,'hlp@sohu.com','hlp')
INSERT INTO t24
VALUES(NULL,'hlp@sohu.com','hlp')
二十六、索引
-- 索引
-- 1.主键索引，主键自动的为主索引(primary key)
-- 2.唯一索引(unique)
-- 3.普通索引(index)
-- 4.全文索引(dulltext)[适用于MyISAM]开发中考虑使用：全文搜索Solr和ElasticSearch(ES)
drop table t25
-- 创建索引
create table t25(
id1 int, id2 int, id3 int, id4 int,
`name` varchar(32));
-- 查询表是否有索引
show indexes from t25
-- a.添加索引（如果某列的值是不重复的，则优先考虑unique这个索引，否则使用普通索引）
-- 添加唯一索引
create unique index id1_index on t25 (id1);
-- 添加普通索引
CREATE INDEX id2_index ON t25 (id2);
-- 添加普通索引方式2
alter table t25 add index id3_index (id3);
-- 添加主键索引（也可以直接创建表的时候指定）
alter table t25 add primary key (id4);
-- b.删除索引
drop index id1_index on t25
-- c.删除主键索引
alter table t25 drop primary key
-- d.修改索引，先删除，再添加
-- e.查询索引
-- 1.
show index from t25
-- 2.
show indexes from t25
-- 3.
show keys from t25
-- 4.
desc t25
二十七、事务
#事务的几个重要概念和具体操作
-- 创建一张测试表
CREATE TABLE t26
(id INT,
`name` VARCHAR(32));
-- 1.start transaction 开始一个事务
START TRANSACTION
-- 2.savepoint 设置保存点
INSERT INTO t26 VALUE(3,'qsc')
SAVEPOINT a
-- 执行dml操作
INSERT INTO t26 VALUE(1,'tom');
SELECT * FROM t26;
SAVEPOINT b
-- 执行dml操作
INSERT INTO t26 VALUE(2,'jack');
-- 3.rollback to 回退事务
-- 回退到b
ROLLBACK TO b
-- 回退到a
ROLLBACK TO a
-- 4.rollback 回退全部事务,表示直接回退到事务开始的状态
ROLLBACK
-- 5.commit 提交事务
COMMIT
#事务细节：
-- 1.如果不开始事务，默认情况dml操作是自动提交的，无法回滚
INSERT INTO t26 VALUES(200,'milan');
SELECT * FROM t26
-- 2.如果开始一个事务，你没有创建保存点，你可以执行rollback，
-- 默认就是回退到你事务开始的状态
START TRANSACTION
INSERT INTO t26 VALUES(4,'qwe');
INSERT INTO t26 VALUES(6,'aim');
ROLLBACK
COMMIT
-- 3.可以在事务中，创建多个保存点
-- 4.可以在事务没有提交前，选择回退到哪个保存点
-- 5.mysql事务机制需要innodb的存储引擎才可以使用，MyISAM不支持
-- 6.开始一个事务两种方法:
-- a. start transaction b. set autocommit=off
SET autocommit = off
#事务隔离级别
-- a.开了两MySQL的控制台
-- b.查看当前MySQL的隔离级别
select @@tx_isolation;
-- mysql> SELECT @@tx_isolation;
-- +-----------------+
-- | @@tx_isolation |
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+
-- c.把其中一个控制台的隔离级别设置为read uncommitted
set session transaction isolation level read uncommitted;
-- d.创建表
create table `account`(
id int,
`name` varchar(32),
money int);
-- 查看当前会话隔离级别
select @@tx_isolation;
-- 查看系统当前隔离级别
select @@global.tx_isolation;
-- 设置当前会话隔离几倍
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- READ UNCOMMITTED是隔离级别，可以是其他类型
-- 设置系统当前隔离级别
SET globa TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- mysql默认的事务隔离级别是repeatable read，一般情况下，没有特殊要求，没有必要修改
-- 1.多个连接开启各自事务操作数据库中数据时，
-- 数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。
-- 2.如果不考虑隔离性，可能会引发如下问题：
-- a.脏读(dirty read):当一个事务读取另一个事务尚未提交的修改时，产生脏读
-- b.不可重复读(nonrepeatable read):同一查询在同一事务中多次进行，
-- 由于其他提交事务所作的修改或删除，每次返回不同的结果集，此时发生不可重复读
-- c.幻读(phantom read):同一查询在同一事务中多次进行，由于其他提交事务所作的插入操作，
-- 每次返回不同的结果集，此时发生幻读
二十八、表类型和存储引擎
-- 表类型和存储引擎
-- 查看所有的存储引擎
SHOW ENGINES
-- 修改存储引擎类型
ALTER TABLE `t28` ENGINE = INNODB;
-- 1.MyISAM不支持事务，也不支持外键，但其访问速度快，对事务完整性没有要求
-- 2.InnoDB存储引擎提供了具有提交，回滚和崩溃恢复能力的事务安全。
-- 但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以数据和索引
-- 3.MEMORY存储引擎使用存在内存中的内容来创建表。每个MEMORY表只实际对应一个磁盘文件。
-- MEMORY类型的表访问速度非常快，因为它的数据是放在内存中的，并且默认使用HASH索引。
-- 但是一旦服务关闭，表中的数据就会丢失，但表的结构还在。
-- a.InnoDB
-- 1.支持事务 2.支持外键 3.支持行级锁
-- b.MyISAM
-- 1.添加速度快 2.不支持外键和事务 3.支持表级锁
CREATE TABLE t27(
id INT,
`name` VARCHAR(32)) ENGINE MYISAM
-- c.MEMORY
-- 1.数据存储在内存中(关闭mysql，数据丢失) 2.执行速度很快(没有IO读写) 3.默认支持索引(hash表)
CREATE TABLE t28(
id INT,
`name` VARCHAR(32)) ENGINE MEMORY
INSERT INTO t28
VALUES(1,'tom'),(2,'jack'),(3,'hlp');
SELECT * FROM t28
二十九、视图
#视图
-- 视图是一个虚拟表，其内容由查询定义。同真实的表一样，试图包含列，其数据来自对应的真实表（基表）
-- 练习：创建一个视图 emp_view01，只能查询 emp 表的(empno、ename, job 和 deptno ) 信
-- 1.创建视图：create view 视图名 as select 语句
CREATE VIEW emp_view01
AS
SELECT empno, ename, job, deptno FROM emp;
-- 2.查看视图
DESC emp_view01
SELECT empno, job FROM emp_view01
-- 3.alter view 视图名 as select 语句
-- 4.查看创建视图的指令：show create view 视图名
SHOW CREATE VIEW emp_view01
-- 5.drop view 视图名1, 视图名2
DROP VIEW emp_view01;
#试图细节讨论：
-- 1. 创建视图后，到数据库去看，对应视图只有一个视图结构文件(形式: 视图名.frm)
-- 2. 视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete ]
-- 修改视图会影响基表
UPDATE emp_view01
SET job = 'MANAGER'
WHERE empno = 7369;
SELECT * FROM emp;
SELECT * FROM emp_view01;
-- 修改基表会影响视图
UPDATE emp
SET job = 'SALESMAN'
WHERE empno = 7369;
-- 3.视图中还可以再使用视图,数据仍然来自于基表
CREATE VIEW emp_view02
AS
SELECT empno, ename FROM emp_view01;
DESC emp_view01;
DESC emp_view02;
SELECT * FROM emp_view02;
#视图的课堂练习：
-- 针对 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 (emp.sal BETWEEN losal AND hisal)
ORDER BY dname;
DESC emp_view03;
SELECT * FROM emp_view03;
DROP VIEW emp_view03
三十、用户管理
# mysql用户管理
-- 当我们做项目开发时，可以根据不同的开发人员，赋给他相应的mysql操作权限
-- 所以，mysql数据库管理人员（root）根据需要创建不同的用户，赋给相应的权限，供开发，维护人员使用
-- 不同的数据库用户，操作的库和表不相同
-- 1.创建新的用户
-- a.'hlp_edu'@'localhost' 表示用户的完整信息：用户名 @ 登录的ip
-- b.123456 密码，但是存放到mysql.user表时，是password加密后的密码
CREATE USER 'hlp_edu'@'localhost' IDENTIFIED BY '123456'
SELECT `host`, `user`, `authentication_string`
FROM mysql.user
-- 2.删除用户
DROP USER 'hlp_edu'@'localhost';
-- 3.登录:断开左上角链接，随后输入登录的用户名和登录IP以及登录密码
-- 4.root用户修改hlp_edu@localhost的密码
SET PASSWORD FOR 'hlp_edu'@'localhost' = PASSWORD('123456');
三十一、用户管理——授权,回收权限等操作
-- 1.给用户授权
-- 基本语法:
/* grant 权限列表(比如select，delete，all [privileges])
on 库.对象名(比如*.* 表示全部库的全部对象(表，视图，存储过程等);库.* 表示该库的全部对象)
to '用户名'@'登录位置' [identified by '密码']
identified by 可以省略，也可以写出：若用户存在，修改密码；若不存在，创建该用户
*/
-- 2.回收用户权限
/* revoke 权限列表 on 库.对象名 from '用户名'@'登录位置'
*/
-- 3.权限生效指令
/* 如果权限没有生效，可以执行下面命令：flush privileges;
*/
#演示用户权限的管理
-- 1.创建用户lipeng 密码123
CREATE USER 'lipeng'@'localhost' IDENTIFIED BY '123'
-- 2.使用root用户创建testdb，表news
CREATE DATABASE testdb
CREATE TABLE news(
id INT,
content VARCHAR(32));
-- 添加测试数据
INSERT INTO news VALUES(100,'北京新闻');
SELECT * FROM news
-- 3.给lipeng用户分配 查看news表和添加权限
GRANT SELECT, INSERT
ON testdb.news
TO 'lipeng'@'localhost'
-- 可以增加权限
GRANT UPDATE
ON testdb.news
TO 'lipeng'@'localhost'
-- 4.修改lipeng密码为abc
SET PASSWORD FOR 'lipeng'@'localhost' = PASSWORD('abc');
-- 5.回收lipeng用户在testdb.news表的所有权限
REVOKE SELECT, UPDATE, INSERT ON testdb.news FROM 'lipeng'@'localhost';
REVOKE ALL ON testdb.news FROM 'lipeng'@'localhost';
-- 6.删除用户
DROP USER 'lipeng'@'localhost'
#mysql管理的细节说明
-- 1.在创建用户的时候，如果不指定Host，则为%，%表示所有ip都有连接权限（即可以远程连接）
CREATE USER jack
SELECT `host`, `user` FROM mysql.user
-- 2.你也可以这样指定：
-- create user 'xxx'@'192.168.1.%' 表示 xxx 用户在 192.168.1.*的 ip 可以登录 mysql
CREATE USER 'smith'@'192.168.1.%'
-- 3.在删除用户的时候，如果 host 不是 %, 需要明确指定 '用户'@'host 值
DROP USER jack -- 默认就是drop user 'jack'@'%'
DROP USER 'smith'@'192.168.1.%'
参考来源：【韩顺平讲MySQL】零基础一周学会MySQL -sql mysql教程 mysql视频 mysql入门_哔哩哔哩_bilibili
318
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
