MySQL数据库编程基础入门3

全栈工程师修炼指南

发布于 2022-09-29 15:37:00

1.6K0

发布于 2022-09-29 15:37:00

文章被收录于专栏：全栈工程师修炼之路

[TOC]

0.基础SQL操作

1.库操作

一、数据库的创建：

-- 1.查看utf8字符集以及校队规则
SHOW CHARACTER SET LIKE 'UTF8%';
Charset  Description    Default collation   Maxlen  
-------  -------------  ------------------  --------
utf8     UTF-8 Unicode  utf8_general_ci            3
utf8mb4  UTF-8 Unicode  utf8mb4_0900_ai_ci         4

SHOW COLLATION LIKE 'UTF8%';
Collation                   Charset      Id  Default  Compiled  Sortlen  Pad_attribute  
--------------------------  -------  ------  -------  --------  -------  ---------------
utf8mb4_0900_ai_ci          utf8mb4     255  Yes      Yes             0  NO PAD         
utf8mb4_0900_as_ci          utf8mb4     305           Yes             0  NO PAD 
....

-- 2.创建一个使用utf8字符集，并带比较规则的mydb1/2数据库。
CREATE DATABASE mydb1 CHARACTER SET utf8 COLLATE=utf8_general_ci;
CREATE DATABASE mydb2 CHARACTER SET utf8 COLLATE=utf8_general_ci;

二、数据库的修改：

-- 修改mydb2字符集为gbk;
ALTER DATABASE mydb2 CHARACTER SET gbk COLLATE=gbk_chinese_ci;

三、数据库的删除：

-- 删除数据库mydb1
DROP DATABASE mydb1;

四、数据库查看：

-- 1.查看所有数据库
SHOW DATABASES;
-- Database            
-- --------------------
-- information_schema  
-- mydb2               
-- mysql               
-- performance_schema       
-- sys   

-- 2.查看数据库mydb2的字符集
SHOW CREATE DATABASE mydb2;
-- Database  Create Database     
-- mydb2     CREATE DATABASE `mydb2` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */

2.表操作

一、创建表

-- 1、创建一张员工表employee
--   字段		类型
--   id			整形
--   name		字符型
--   gender		字符型
--   birthday    日期型
--   entry_date  日期型
--   job			字符型
--   salary		小数型
--   resume		文本
-- 要求：把id 设置成主键，并且自动增长。name不允许为空。

CREATE TABLE employee (
 `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 `name` VARCHAR(32) NOT NULL COMMENT '姓名' ,
 `gender` VARCHAR(2) COMMENT '性别',
 `birthday` DATE COMMENT '生日', 
 `entry_data` DATETIME COMMENT '转正时间',
 `job` CHAR COMMENT '职位',
 `salary` FLOAT COMMENT '薪水',
 `resumen` TEXT COMMENT '摘要'
);


-- 2.复制表结构并建立新表
CREATE TABLE employee2 LIKE employee;

二、删除表:

-- 删除employee2表
DROP TABLE employee2;

三、数据表的结构的修改：

-- 1、在上面员工表的基本上增加一个image和sex列(采用枚举方式)。
ALTER TABLE employee ADD image VARCHAR(255) DEFAULT '/images/default.png' COMMENT '个人图片';
ALTER TABLE employee ADD sex ENUM('F','M','UN') COMMENT '性别:Male/Female' AFTER `name`;  -- 插入在name字段之后

-- 2、修改job列，使其长度为60。
ALTER TABLE employee MODIFY job VARCHAR(60)COMMENT '职位';

--  3、列名name修改为username
ALTER TABLE employee CHANGE `name` `username` VARCHAR(32) NOT NULL COMMENT '姓名';

-- 4、删除gender列。
ALTER TABLE employee DROP gender;  

-- 5、表名改为users。
ALTER TABLE employee RENAME users;	  

-- 6、修改表的字符集为utf8MB4
ALTER TABLE users CHARACTER SET 'utf8MB4';

四、查看表结构

-- 1、查看数据库内的所有表
SHOW TABLES;
-- users            

-- 2、查看users的建表语句
SHOW CREATE TABLE users;
-- CREATE TABLE `users` (
--   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
--   `username` varchar(32) CHARACTER SET gbk NOT NULL COMMENT '姓名',
--   `sex` enum('F','M','UN') CHARACTER SET gbk DEFAULT NULL COMMENT '性别:Male/Female',
--   `birthday` date DEFAULT NULL COMMENT '生日',
--   `entry_data` datetime DEFAULT NULL COMMENT '转正时间',
--   `job` varchar(60) CHARACTER SET gbk DEFAULT NULL COMMENT '职位',
--   `salary` float DEFAULT NULL COMMENT '薪水',
--   `resumen` text CHARACTER SET gbk COMMENT '摘要',
--   `image` varchar(255) CHARACTER SET gbk DEFAULT '/images/default.png' COMMENT '个人图片',
--   PRIMARY KEY (`id`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8

-- 3、查看users的表结构
DESCRIBE users; -- 可简写 desc
-- Field       Type                Null    Key     Default              Extra           
-- id          int(11)             NO      PRI     (NULL)               auto_increment  
-- username    varchar(32)         NO              (NULL)
-- sex         enum('F','M','UN')  YES             (NULL)    
-- birthday    date                YES             (NULL) 
-- entry_data  datetime            YES             (NULL) 
-- job         varchar(60)         YES             (NULL) 
-- salary      float               YES             (NULL)
-- resumen     text                YES             (NULL)         
-- image       varchar(255)        YES             /images/default.png
```	  

<br>

##### 3.表记录操作

**一、插入语句:--insert**
```SQL
-- 1、两种方式向user中插入三个员工信息，要求员工姓名分别是zs,ls,wangwu;
INSERT INTO users SET
username='张三',
sex='F',
birthday='1995-12-25',
entry_data='2020-2-12 23:35:49',
job='后端研发工程师',
salary=9236.00,
resumen='JAVA Pragraming';

-- 方式2:常用
INSERT INTO users(`username`,`sex`,`birthday`,`entry_data`,`job`,`salary`,`resumen`)
VALUE 
('李四','M','1992-01-01',NOW(),'DBA工程师',9612.00,'MySQL 从入门到删库'),
('王娜','F','1998-01-11','2020-02-11 15:43:01','前端实习生',4612.00,'BootStrap Vue.js Javascript');

-- 2.复制表中数据到新表
CREATE TABLE employee LIKE users;
INSERT INTO employee SELECT * FROM users;

-- 3.数据查看
SELECT * FROM users;
-- id  username  birthday    entry_data           job                    salary  resumen                      image                sex   
-- 1  张三        1995-12-25  2020-02-12 23:35:49  后端研发工程师                  9336  JAVA Pragraming              /images/default.png  F       
-- 2  李四        1992-01-01  2020-02-12 15:43:01  DBA运维工程师                 9236  MySQL 从入门到删库                 /images/default.png  M       
-- 3  王娜        1998-01-11  2020-02-11 15:43:01  前端实习生                    9000  BootStrap Vue.js Javascript  /images/default.png  F

二、更新语句:–update

-- 1、将所有员工薪水修改为10000元。
UPDATE users SET salary = 10000;

-- 2、将姓名为张三的员工薪水修改为9336.00元。
UPDATE users SET salary = 9336.00 WHERE username = '张三';

-- 3、将姓名为李四的员工薪水修改为9236.00元,job改为DBA运维工程师。
UPDATE users SET salary = 9236.00,job = 'DBA运维工程师' WHERE username = '李四';
    
-- 4、将王娜的薪水在原有基础上减去1000元。
UPDATE users SET salary = salary-1000 WHERE username = '王娜';

三、删除语句:–delete

-- 1、删除employee表中名称为张三的记录。
DELETE FROM employee WHERE username = '张三';
SELECT * FROM employee;

-- 2、删除employee表中所有记录。
DELETE FROM employee;

1.单表查询

(1)条件查询语句

-- 字段 比较运算符 列值;
> < = => <= != 

-- 逻辑运算符
AND  -- 并
OR  -- 或
NOT -- 非表示查询否定

-- 排序
ORDER BY [ASC | DESC]

-- 分组查询和条件过滤
GROUP BY 
HAVING


-- 别名
AS

-- 关键字条件
IS NULL , IS NOT NULL -- 为空或者不为空
IN (列值1,列值2), NOT IN (列值1,列值2); -- 在IN范围中值都会执行查询，NOT IN 表示不再其中的;
BETWEEN 10 AND 20;  -- 查询指定范围记录 10 <= x <=20
LIKE '%Nord_'  -- 查询匹配字符的记录模糊匹配或者其他匹配
NOT LIKE '%Nord_'  -- 带NOT的多条件查询
LIMIT 0,10  -- 限制查询结果行数量 从1开始 10行数据
REGEXP '正则表达式' -- 正则表达式查询 ^ $ . (单个字符) * +（1<=匹配次数） | [] [^0-9] {1,3}

(2) 聚合函数一览表 1.集合函数:

COUNT([*|字段]) -- 统计数据或者满足条件的行数，*默认匹配最多的一行，对于某列字段的空值NULL是不统计的
SUM(字段)  -- 统计该列数字之和，注意字段的类型必须是整形或者浮点型
AVG(字段)  --统计该字段的平均值; 
MAX(字段)  -- 统计该列字段值的最大值，默认都是NULL不会走索引，表中数据量大的时候不建议使用
MIN(字段)  -- 统计该列字段值的最大值，默认都是NULL不会走索引，表中数据量大的时候不建议使用

2.判断函数:

IFNULL(字段,0) -- 判断字段是否为NULL是则设置为0

3.过滤函数:

DISTINCT (*) -- 过滤重复行

注意事项:

聚合函数使用字段根据条件不同进行选择是否查询走索引;
在表中数据量大的时候慎用集合函数

单表查询操作

建立实例表数据:

-- 表1;
create table exam(
  id int primary key auto_increment,
  name varchar(20) not null,
  chinese double,
  math double,
  english double
);

insert into exam values 
(null,'关羽',85,76,70), 
(null,'张飞',70,75,70),
(null,'赵云',90,65,95),
(null,'刘备',97,50,50),
(null,'曹操',90,89,80),
(null,'司马懿',90,67,65);


-- 表2
create table orders(
  id int,
  product varchar(20),
  price float
);

insert into orders(id,product,price) values 
(1,'电视',900),
(2,'洗衣机',100),
(3,'洗衣粉',90),
(4,'桔子',9),
(5,'洗衣粉',90);

练习：

-- 1、查询表中所有学生的信息。
SELECT * FROM exam;

-- 2、查询表中所有学生的姓名和对应的英语成绩。
SELECT NAME,english FROM exam;
 
-- 3、过滤表中重复数据。
SELECT DISTINCT * FROM exam;

-- 4、在所有学生分数上加10分特长分并且使用别名。
SELECT `name`,chinese+10 AS 'chinese_tc',math+10 AS 'math_tc',english+10 AS 'english_tc' FROM exam;

-- 5、统计每个学生的总分并且使用别名。
SELECT `name`,chinese + math + english AS 'Total' FROM exam;

-- 6、使用别名表示学生分数。
SELECT `name` AS '姓名',chinese AS '语文',math AS '数学',english AS '英语' FROM exam;
  
-- 使用where子句
-- 7、查询姓名为刘备的学生成绩
SELECT * FROM exam WHERE `name`='刘备'; 
    
-- 8、查询英语成绩大于90分的同学
SELECT * FROM exam WHERE english > 90;

-- 9、查询总分小于200分的所有同学
SELECT * FROM exam WHERE chinese+math+english <= 200;
  
-- 10、查询英语分数在 80－90之间的同学。
SELECT * FROM exam WHERE english BETWEEN 80 AND 90;  -- 注意点关键字是AND不是TO
  
-- 11、查询数学分数为89,75,91的同学。
SELECT * FROM exam WHERE math IN (89,75,91); 

-- 12、查询所有姓刘的学生成绩。
SELECT * FROM exam WHERE `name` LIKE '刘%'; 

-- 13、查询所有姓刘两个字的学生成绩。
SELECT * FROM exam WHERE `name` LIKE '刘_';  -- 关键点 是_不是 ? 或者 . 

-- 14、查询数学分>80 并且 语文分>80的同学。
SELECT * FROM exam WHERE math > 80 AND chinese > 80; 
    
-- 15、查询数学分>80 或者 语文分>80的同学。
SELECT * FROM exam WHERE math > 80 OR chinese > 80;
    
-- 使用order by  排序
-- 16、对数学成绩排序后输出。
SELECT * FROM exam ORDER BY math DESC;

-- 17、对总分排序按从高到低的顺序输出
SELECT `name`,chinese + math + english AS 'Total' FROM exam ORDER BY chinese + math + english DESC;

-- 18、对姓刘的学生成绩排序输出
SELECT * FROM exam WHERE NAME LIKE '刘%' ORDER BY chinese + math + english DESC;

-- 使用count（函数）慎用 * 
-- 19、统计一个班级共有多少学生？
SELECT COUNT(id) FROM exam; -- 注意不统计NULL 或者 空值列，默认最多匹配

-- 20、统计数学成绩大于或等于80的学生有多少个？
SELECT COUNT(id) FROM exam WHERE math >= 80;
    
-- 21、统计总分大于200的人数有多少？
SELECT COUNT(id) FROM exam WHERE chinese + math + english > 200;   

-- 使用sum函数
-- 22、统计一个班级数学总成绩？
SELECT SUM(math) AS '数学总成绩' FROM exam;

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

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

-- 25、统计一个班级语文成绩平均分
SELECT SUM(chinese) / COUNT(id) AS '语文成绩平均分' FROM exam;

-- 使用avg函数
-- 26、求一个班级数学平均分？
SELECT AVG(ifnull(math,0)) AS '数学平均分' FROM exam;

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


-- 使用max，min函数
-- 28、求班级最高分和最低分（数值范围在统计中特别有用）
SELECT MAX(math+english+chinese) AS '最高分',MIN(math+english+chinese) AS '最低分' FROM exam;

-- 29、查询orders表购买了几类商品，并且每类总价大于100的商品
SELECT DISTINCT(product) FROM orders WHERE price >= 100;  -- 关键点

2.多表查询

描述:多表查询就是从多张表中查找所需的数据并且整合在一起显示出来，多表查询有多种实现方式如内连接(交集)、外连接(全集)、子查询；

-- 导入测试数据库
mysql -uroot -p -e "CREATE DATABASE Test";
mysql -uroot -p Test < Test.sql

(0) 交叉连接查询笛卡尔积 描述:笛卡尔积全匹配所产生问题的多行重复产生是由于没有WHERE条件导致基准表中的数据和连接表的数据进行重复拼凑连接显示如果表1有4条 * 表2有4条 = 16 条数据例如：SELECT Join1.id,Name,City,Unit FROM Join1,Join2;;

(1) 内连接查询|INNER JOIN 描述:内连接查询使用比较运算符进行表间列数据进行比较，并输入符合的条件的结果; 内连接原理:采用了笛卡尔积的集合方式进行实现,左侧基准表而右侧是连接表,分为两种写法;

隐式内连接:在查询出结果的基础上去做WHERE条件过滤;
显式内连接:带着条件去查询结构，执行效率要高；

WeiyiGeek.

-- City 表
ID NAME CountryCode District Population
-- Country表
NAME CountryCode LifeExpecatancy;

基础示例:

-- MYSQL简单写法
SELECT ID,City.NAME,City.Population,LifeExpecatancy FROM 
City,Country
WHERE
ID < 10 AND City.CountryCode = Country.Code

-- SQL标准写法
SELECT ID,City.NAME,City.Population,LifeExpecatancy FROM 
City INNER JOIN Country
WHERE
ID < 10 AND City.CountryCode = Country.Code

-- 隐式内连接:
SELECT * FROM product AS p,category AS c WHERE p.cno = c.cid

-- 显式内连接:
SELECT * FROM product p INNER JOIN category c ON p.cno = c.cid;

注意事项:

注:在数据库查询中对于单表查询默认是FROM后的表，但是对于多表查询如果两个表不存在重复字段则可以简写，如果存在重复字段必须进行要显示数据的表.字段;

(2) 外连接查询|OUTER JOIN 描述:为了将两张表完整的链接到一起显示数据这时候我们采用外连接查询而它又分为:

LEFT (左连接) 表现形式大致差不多LEFT OUTER JOIN:会将在左表中所有数据都查询出来，如果右表中没有对应的数据则用NULL代替;
RIGHT (右连接) RIGHT OUTER JOIN:会将在右表中所有数据都查询出来，如果左表中没有对应的数据则用NULL代替; 两种连接方式区别说白了就是以谁为准；并且此处采用ON关键字替换了WHERE表示在什么(右或者左表)之上进行匹配;

基础语法:

-- 左表:SQL标准写法(以City表为基准)
SELECT ID,City.NAME,City.Population,LifeExpecatancy FROM 
City LEFT OUTER JOIN Country
ON
ID < 10 AND City.CountryCode = Country.Code

-- 右表:SQL标准写法(以Country表为基准)
SELECT ID,City.NAME,City.Population,LifeExpecatancy FROM 
City RIGHT OUTER JOIN Country
ON
ID < 10 AND City.CountryCode = Country.Code

（3）子查询

ANY 和 SOME 关键字子查询:它们同义词的是关键字，表示满足其中任意条件;
EXISTS 和 NOT EXISTS 子查询:它们是一种判断子查询;
IN 子查询:对子查询结果进行验证是否有返回的行，如果有则执行外部查询语句否则不执行
UNION 和 UNION ALL 子查询:用于合并查询结果，可以将多条SELECT语句查询的结果组合成单个结果集(重复的行去除-ALL则不去除)，但是两张表的列数必须相同(实际数据类型可以不同);

基础示例:

-- 通过表达式对返回的结果进行比较，并且输出符合条件的结果
-- 显示t2表中年龄大同大于t1表中的年龄字段;
SELECT name,age FROM t2
WHERE
age > ANY (SELECT age FROM t1);

-- 系统对子查询进行运算，先判断是否有返回行，如果有则执行外层语句中的查询，如果没有则不进行查询。
-- 判断NGINX状态是否产生错误，错误则执行语句
SELECT * FROM log WHERE category = 'Nginx';
AND EXISTS
(SELECT * FROM state WHERE Nginx='Fail');

-- 外部查询返回的结果匹配的子查询的结果
SELECT * FROM person WHERE name IN (SELECT name FROM blacklist);

-- 使用ALL关键拼接两张表
SELECT * FROM t1 UNION SELECT * FROM t2; -- 剔除重复的行
SELECT * FROM t1 UNION ALL SELECT * FROM t2;

总结:

1.多表查询时候应该注意字段名称的唯一性，如果不是唯一的则要明确写明表名;
2.内连接比外连接效率要高许多，但是需要注意其笛卡尔积的问题;
3.当表中的数据量很大时候，连接查询所使用的字段最好有索引;
4.在多表中可以利用表.*显示表中的所有字段;

多表查询操作

测试表:

-- 子查询表1
CREATE TABLE emp(
  empno		INT,
  ename		VARCHAR(50),
  job		VARCHAR(50),
  mgr		INT,
  hiredate	DATE,
  sal		DECIMAL(7,2),
  comm		DECIMAL(7,2),
  deptno		INT
);

INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);


-- 子查询表2
CREATE TABLE dept(
  deptno		INT,
  dname		VARCHAR(14),
  loc		VARCHAR(13)
);
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON');

基础实例:

-- 1.单行子查询(> < >= <= = <>):查询出 高于10号部门的平均工资 的员工信息 
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno=10) AND deptno!=10;
 
 
-- 2.多行子查询(in  not in any all):查询出 比10号部门任何员工薪资高的员工 信息
SELECT * FROM emp WHERE sal > ANY(SELECT sal FROM emp WHERE deptno=10) AND deptno!=10;


-- 3.多列子查询(实际使用较少):和10号部门同名同工作的员工信息
SELECT * FROM emp WHERE ename IN (SELECT ename FROM emp WHERE deptno=10) AND job IN (SELECT job FROM emp WHERE deptno=10) AND deptno!=10;
SELECT * FROM emp WHERE (ename,job) IN(SELECT ename,job FROM emp WHERE deptno=10)AND deptno!=10; -- 推荐方式


-- 4.select 后面接子查询:获取员工的名字和部门的名字
SELECT
  emp.`ename`,
  dept.`dname`
FROM
  emp,
  dept
WHERE emp.`deptno` = dept.`deptno`;


-- 5.from 后面接子查询：查询emp表中经理信息
SELECT * FROM emp AS e,(SELECT DISTINCT mgr FROM emp) AS manger WHERE e.empno=manger.mgr; 


-- 6.where 后面接子查询：薪资 高于10号部门平均工资 的所有员工信息
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 10);

  
-- 7.group by 后面接子查询：有哪些部门的平均工资高于30号部门的平均工资
SELECT emp.deptno,dept.`dname` FROM emp,dept
WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno=30) AND emp.deptno != 30  AND emp.`deptno`=dept.`deptno`
deptno  dname       
------  ------------
    20  RESEARCH    
    10  ACCOUNTING  
    20  RESEARCH    
    10  ACCOUNTING  
    20  RESEARCH    

SELECT deptno,COUNT(deptno) AS '部门超过平均工资人数' FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno=30) AND emp.deptno != 30 
GROUP BY deptno;
deptno  部门超过平均工资人数  
------  ----------------------
    20   3
    10   2

SELECT deptno, AVG(sal) AS bumen FROM emp GROUP BY deptno HAVING bumen > (SELECT AVG(sal) FROM emp WHERE deptno=30); -- 标准答案


-- 8.工资>JONES工资
SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = 'JONES');


-- 9.查询与SCOTT同一个部门的员工
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'SCOTT') AND ename != 'SCOTT';


-- 10.查询工作和工资与MARTIN完全相同的员工信息
SELECT * FROM emp WHERE (job,sal) = (SELECT job,sal FROM emp WHERE ename = 'MARTIN') AND ename NO IN 'MARTIN';
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename = 'MARTIN');


-- 11.有两个以上直接下属的员工信息 值得学习
SELECT mgr,COUNT(mgr) FROM emp GROUP BY mgr HAVING COUNT(mgr) > 2; -- 得出两个以上的经理mgr值
SELECT * FROM emp WHERE empno IN (SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr) >2);


-- 12.查询员工编号为7788的员工名称,员工工资,部门名称,部门地址
SELECT
  emp.`ename`,
  emp.`sal`,
  dept.`dname`,
  dept.`loc`
FROM
  emp,
  dept
WHERE (empno, dept.`deptno`) IN
  (SELECT
    empno,
    deptno
  FROM
    emp
  WHERE empno = 7788);

SELECT e.*,d.loc FROM emp e,dept d WHERE e.empno=7788 AND e.deptno=d.deptno; -- 值得学习

SQL查询的综合案例

查询出高于本部门平均工资的员工信息
列出达拉斯加工作的人中,比纽约平均工资高的人
查询7369员工编号,姓名,经理编号和经理姓名
查询出各个部门薪水最高的员工所有信息

-- 13. 查询出高于本部门平均工资的员工信息-
SELECT * FROM emp e WHERE sal > (SELECT AVG(sal) AS 平均工资 FROM emp d GROUP BY deptno HAVING e.deptno=d.deptno);
SELECT * FROM emp e1 WHERE e1.sal > (SELECT AVG(e2.sal) FROM emp e2 WHERE e1.deptno=e2.deptno GROUP BY e2.deptno);


-- 14.列出达拉斯加工作的人中,比纽约平均工资高的人(内连接)
SELECT AVG(sal) FROM emp e INNER JOIN dept d ON e.deptno=d.deptno WHERE d.loc='NEW YORK';

-- 方法1
SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno WHERE d.loc='DALLAS' 
AND sal>(SELECT AVG(sal) FROM emp e INNER JOIN dept d ON e.deptno=d.deptno WHERE d.loc='NEW YORK');
-- 方法2
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc='DALLAS')
AND sal > (SELECT AVG(sal) FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE loc='NEW YORK'));


-- 15.查询7369员工编号,姓名,经理编号和经理姓名
SELECT mgr FROM emp WHERE empno=7369;
SELECT ename FROM emp WHERE empno =(SELECT mgr FROM emp WHERE empno=7369);
-- 方法1
SELECT empno,ename,(SELECT mgr FROM emp WHERE empno=7369) AS 经理编号 
,(SELECT ename FROM emp WHERE empno =(SELECT mgr FROM emp WHERE empno=7369)) AS 经理姓名 FROM emp WHERE empno=7369;
-- 方法2（值得学习-两表示使用）
SELECT e1.empno,e1.ename,e1.mgr,mgrtable.ename FROM emp e1,emp mgrtable WHERE e1.mgr = mgrtable.empno AND e1.empno=7369; 


-- 16.查询出各个部门薪水最高的员工所有信息（值得学习）
SELECT * FROM emp  WHERE sal=MAX(sal) GROUP BY deptno;
SELECT * FROM emp e WHERE sal = (SELECT MAX(sal) AS 最高薪水 FROM emp e2 WHERE e.deptno=e2.deptno GROUP BY deptno );

3.多表删除

描述:在实际DBA运维中您可能有过这样的场景就是删除一个用户的时候需要将user和其他表中间表中的值进行删除,这时就需要用到MySQL多表删除;

示例表:

CREATE DATABASE ceshi;
use ceshi;
-- 表1
CREATE TABLE `grade` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
 
INSERT INTO `grade` VALUES (1, '一年级');
INSERT INTO `grade` VALUES (2, '二年级');
INSERT INTO `grade` VALUES (3, '三年级');
 
-- 表2
CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `grade_id` bigint(20) NOT NULL COMMENT '年纪id',
  `name` varchar(8) DEFAULT NULL,
  `sex` tinyint(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
 
INSERT INTO `student` VALUES (1, 1, '张三', 1);
INSERT INTO `student` VALUES (2, 2, '李四', 2);
INSERT INTO `student` VALUES (3, 3, '网2', 1);

实际案例:

-- 1. 从student表中把那些grade_id值在grade表里有匹配的记录全删除掉，即删除所有年级的学生。
DELETE student FROM student, grade WHERE student.grade_id=grade.id;
DELETE FROM student USING student, grade WHERE student.grade_id=grade.id;
-- MySQL [ceshi]> SELECT * FROM student;
-- Empty set (0.07 sec)


-- 2.删除所有没有年级的学生。
DELETE student FROM student LEFT JOIN grade ON student.grade_id = grade.id WHERE grade.id IS NULL;
DELETE FROM student USING student LEFT JOIN grade ON student.grade_id = grade.id WHERE grade.id IS NULL;


-- 3.删除三年级和所有三年级的学生。
DELETE grade,student FROM grade LEFT JOIN student ON grade.id = student.grade_id WHERE grade.id=3; -- 显式
SELECT * FROM grade LEFT JOIN student ON grade.id = student.grade_id WHERE grade.id=3; --可以理解成从查询的结果中删除：
-- +----+--------+------+----------+------+------+
-- | id | name   | id   | grade_id | name | sex  |
-- +----+--------+------+----------+------+------+
-- |  3 | 三年级 |    3 |        3 | 网2  |    1 |
-- +----+--------+------+----------+------+------+
-- 1 row in set (0.07 sec)

4.用户操作

描述:主要对mysql8.X数据库用户的查询/创建/删除以及权限分配查询;

基础语句:

#(1)系统用户表查看
mysql> SELECT user,host FROM mysql.user;     #查询用户 数据库.表


#(2)创建用户语句
#注意:host的格式` % | 192.168.1.% | 192.168.1.1/255.255.255.0`
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.%' IDENDIFIED BY '123456';


#(3)给建立用户分配权限并可以设置密码
GRANT all ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION; 
#注意点: ALL PRIVILEGES 是所有的用户操作权限,您可以指定权限以,分隔; GRANT OPTION 则是管理员权限;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'192.168.1.1/255.255.255.0' IDENTIFIED BY 'test' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;


#(4)查看用户权限
#注意点:host部分不同则有可能密码权限是不同的;
SHOW GRANTS FOR root@'%';


#(5)设置与更改用户密码
#注意点:当前客户端支持的加密方式，MySQL8.0默认的密码加密方式是caching_sha2_password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
FLUSH PRIVILEGES;
#5.7x 版本: SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
#5.7x 版本: 如果是当前登陆用户则可以使用它 SET PASSWORD = PASSWORD("newpassword"); 


#(6)撤销用户权限
REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'%';


#(7)删除指定用户
#注意: drop 有时候干不掉特殊字符
DROP USER 'test'@'127.0.0.1';
DELETE FROM mysql.user WHERE user='' or user='::1'; #删除空表用户 IPv6 (当主机名为大写时候采用这种方式)

实际执行结果:

#创建用户
mysql> CREATE USER 'admin'@'%' IDENTIFIED BY 'TestWeiyiGeek';
Query OK, 0 rows affected (0.06 sec)

#查看用户
mysql> SELECT user,host FROM mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| admin            | %         |
| root             | %         |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.06 sec)

#权限赋予(也能修改密码)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.05 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

#修改密码
mysql> ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'TEST';
Query OK, 0 rows affected (0.05 sec)

#权限查询
mysql> SHOW GRANTS FOR admin@'%';
+----------+
| Grants for admin@%  
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`%` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `admin`@`%` WITH GRANT OPTION |
2 rows in set (0.09 sec)

#移除用户权限
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'%';
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW GRANTS FOR admin@'%';
+-----------------------------------+
| Grants for admin@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `admin`@`%` |
+-----------------------------------+
1 row in set (0.08 sec)

#删除用户
mysql> DROP USER 'admin'@'%';
Query OK, 0 rows affected (0.05 sec)

注意事项:

给用户'pig'@'%'授权的时候是这样的(或类似的)，GRANT SELECT ON test.user TO 'pig'@'%', 则在使用REVOKE SELECT ON *.* FROM 'pig'@'%,命令并不能撤销该用户对test数据库中user表的SELECT 操作；
如果授权使用的是 GRANT SELECT ON *.* TO 'pig'@'%'则REVOKE SELECT ON test.user FROM 'pig'@'%'命令也不能撤销该用户对test数据库中user表的Select 权限,必须确定权限、数据库表、用户、主机是一致的才行;

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2020-01-22，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

数据库

sql

登录后参与评论

0 条评论

热度