02、MySQL（DDL和DQL）

6、SQL语言

6.1、概念

　　SQL (Structured Query Language)结构化查询语言，用于存取数据、更新、查询和管理关系数据库系统的程序设计语言。

　　经验:通常执行对数据库的“增删改查”，简称c(Create) R (Read)u (Update)D (Delete)。

6.2、MySQL应用

　　对于数据库的操作，需要在进入MySQL环境下进行指令输入，并在一句指令的末尾使用;结束

6.3、基本命令　　

6.3.1、查看MySQL中所有数据库

SHOW DATABASES; #显示当前MySQL中包含的所有数据库

6.3.2、创建自定义数据库

CREATE DATABASE mydb1 ;#创建mydb数据库
CREATE DATABASE mydb2 CHARACTER SET gbk;#创建数据库并设置编码格式为gbk
CREATE DATABASE IF NOT EXISTS mydb4;#如果mydb4数据库不存在，则创建;如果存在，则不创建。

6.3.3、查看数据库创建信息

SHOW CREATE DATABASE mydb2;#查看创建数据库时的基本信息

6.3.4、修改数据库

ALTER DATABASE mydb2 CHARACTER SET UTF8;#查看创建数据库时的基本信息

6.3.5、删除数据库

DROP DATABASE mydb1;#删除数据库mydb1

6.3.6、查看当前所使用的数据库

SELECT DATABASE();#查看当前使用的数据库

6.3.7、使用数据库

USE mydb2;#使用mydb1数据库

7、数据查询

7.1、导入sql文件

7.2、数据库表的基本结构

　　关系结构数据库是以表格(Table)进行数据存储,表格由“行”和“列”组成

　　经验:

　　　　执行查询语句返回的结果集是一张虚拟表。

7.3、基本查询

　　语法:

　　　　SELECT 列名 FROM 表名

7.3.1、查询部分列

#查询员工表中所有员工的编号、名字、邮箱
SELECT employee_id,first_name , email
FROM t_employees;

7.3.2、查询所有列

#查询员工表中所有员工的所有信息(所有列)
# SELECT 所有列的列名 FROM t_employees;
SELECT * FROM t_employees;

注意:生产环境下，优先使用列名查询。* 的方式需转换成全列名，效率低，可读性差。

7.3.3、对列中的数据进行运算

# 查询员工表中所有员工的编号、名字、年薪
SELECT employee_id , first_name , salary*12
FROM t_employees;

注意: % 是占位符,而非模运算符。

7.3.4、列的别名

列 AS "列名"

#查询员工表中所有员工的编号、名字、年薪(列名均为中文)
SELECT employee_id AS "编号" , first_name AS "名字" , salary*12 AS "年薪" 
FROM t_employees;

7.3.5、查询结果去重

DISTINCT 列名

#查询员工表中所有经理的ID。
SELECT DISTINCT manager_id 
FROM t_employees;

7.4、排序查询

语法 :

　　SELECT 列名 FROM 表名 ORDER BY 排序列[排序规则]

7.4.1、依据单列排序

#查询员工的编号，名字,薪资。按照工资高低进行降序排序)。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC;

7.4.2、依据多列排序

#查询员工的编号，名字，薪资。按照工资高低进行升序排序（薪资相同时，按照编号进行升序排序)。
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC , employee_id ASC;

7.5、条件查询

语法 :

　　SELECT列名 FROM表名 WHERE 条件

7.5.1、等值判断(=)

#查询薪资是11808的员工信息(编号、名字、薪资)
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000;

注意: 与 java不同(==) , mysql中等值判断使用 =

7.5.2、逻辑判断(and、or、not)

#查询薪资是11088并且提成是0.38的员工信息(编号、名字、薪资)
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary = 11000 AND commission_pct= 0.30;

7.5.3、不等值判断(>、<、>=、<=、!=、>)

#查询员工的薪资在6888~18888之间的员工信息(编号，名字，薪资)
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary >= 6000 AND salary <=10000;

7.5.4、区间判断(BETWEEN AND)

#查询员工的薪资在6880~10880之间的员工信息(编号，名字，薪资)
SELECT employee_id , first_name , salary
FROM t_employees
WHERE salary BETWEEN 6000 AND 10000;#闭区间，包含区间边界的两个值

注: 在区间判断语法中，小值在前，大值在后，反之，得不到正确结果

7.5.5、NULL值判断(IS NULL、IS NOT NULL)

IS NULL
　　列名 IS NULL
IS NOT NULL
　　列名 IS NOT NULL

#查询没有提成的员工信息(编号，名字，薪资，提成)
SELECT employee_id , first_name , salary , commission_pct
FROM t_employees
WHERE commission_pct IS NULL;

7.5.6、枚举查询(IN(值1，值2，值3))

#查询部门编号为78、80、98的员工信息(编号，名字，薪资，部门编号)
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id IN( 70,80,90);

注: in 的查询效率较低，可通过多条件拼接。

7.5.7、模糊查询

　　LIKE _ (单个任意字符)
　　　　列名 LIKE '张_'
　　LIKE %(任意长度的任意字符)
　　　　列名 LIKE '张%'

注意 : 模糊查询只能和LIKE 关键字结合使用

#查询名字以"L"开头的员工信息(编号，名字，薪资，部门编号)
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L%';

#查询名字以"L"开头并且长度为4的员工信息(编号，名字，薪资，部门编号)
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE first_name LIKE 'L____';

7.5.8、分支结构查询

CASE
    WHEN 条件1 THEN 结果1
    WHEN 条件2 THEN 结果2
    WHEN 条件3 THEN 结果3
    ELSE 结果
END

注意: 通过使用 CASE END 进行条件判断，每条数据对应生成一个值。

经验: 类似Java中的 switch

#查询员工信息(编号，名字，薪资，薪资级别<对应条件表达式生成>)
SELECT employee_id , first_name , salary , department_id ,
    CASE
        WHEN salary>=10008 THEN 'A'
        WHEN salary>=8000 AND salary<10000 THEN 'B'
        WHEN salary>=6000 AND salary<8088 THEN 'C'
        WHEN salary>=4000 AND salary<6080 THEN 'D'
    ELSE 'E'
    END AS "LEVEL"
FROM t_employees;

7.6、时间查询

语法:

　　SELECT 时间函数([参数列表])

经验:

　　执行时间函数查询，会自动生成—张虚表（一行一列)

7.6.1、获得当前系统时间

#查询当前时间
SELECT SYSDATE();
#查询当前时间
SELECT NOW();
#获取当前日期
SELECT CURDATE();
#获取当前时间
SELECT CURTIME();

7.7、字符串查询

语法:

　　SELECT 字符串函数([参数列表])

7.7.1、字符串应用

# 拼接内容
# MySQL
SELECT CONCAT('My' ,'S' , 'QL');

# 字符串替换
# 结果为这是MySql数据库
SELECT INSERT('这是一个数据库',3,2,'MySql');

# 指定内容转换为小写
# mysql
SELECT LOWER('MYSQL');

# 指定内容转换为大写
# MYSQL
SELECT UPPER('mysql');

# 指定内容截取
# MySQL
SELECT SUBSTRING( 'JavaMySQLOracle' ,5,5);

7.8、聚合函数

语法:

　　SELECT 聚合函数(列名) FROM 表名;

经验:

　　对多条数据的单列进行统计，返回统计后的一行结果。

7.8.1、单列总和

#统计所有员工每月的工资总和
SELECT SUM(salary)
FROM t_employees;

7.8.2、单列平均值

#统计所有员工每月的平均工资
SELECT AVG(salary)
FROM t_employees;

7.8.3、单列最大值

#统计所有员工中月薪最高的工资
SELECT MAX( salary)
FROM t_employees;

7.8.4、单列最小值

#统计所有员工中月薪最低的工资
SELECT MIN( salary)
FROM t_employees;

7.8.5、总行数

#统计员工总数
SELECT COUNT(*)FROM t_employees;
#统计有提成的员工人数
SELECT COUNT(commission_pct)FROM t_employees;

注意: 聚合函数自动忽略null值，不进行统计。

7.9、分组查询

语法:

　　SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组依据 (列);

7.9.1、查询各部门的总人数

#思路:
#1 .按照部门编号进行分组(分组依据是department_id)
#2.再针对各部门的人数进行统计(count)
SELECT department_id , COUNT(employee_id)
FROM t_employees
GROUP BY department_id;

7.9.2、查询各部门的平均工资

#思路:
#1.按照部门编号进行分组(分组依据department_id)。
#2.针对每个部门进行平均工资统计(avg).
SELECT department_id , AVG( salary)
FROM t_employees
GROUP BY department_id

7.9.3、查询各个部门、各个岗位的人数

#思路:
#1.按照部门编号进行分组(分组依据department_id)。
#2.按照岗位名称进行分组(分组依据job_id)。
#3.针对每个部门中的各个岗位进行人数统计(count) .
SELECT department_id , job_id , COUNT ( employee_id)
FROM t_employees
GROUP BY department_id , job_id;

7.9.4、常见问题

#查询各个部门id、总人数、first_name
#error
SELECT department_id , COUNT(*), first_name
FROM t_employees
GROUP BY department_id;

注: 分组查询中，select显示的列只能是分组依据列，或者聚合函数列，不能出现其他列。

7.10、分组过滤查询

语法:

　　SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组列 HAVING 过滤规则

7.10.1、统计部门的最高工资

#统计60、70、90号部门的最高工资
#思路:
#1)．确定分组依据(department_id)
#2)．对分组后的数据,过滤出部门编号是60、70、90信息
#3). max()函数处理
SELECT department_id ,MAX(salary)
FROM t_employees
GROUP BY department_id
HAVING department_id IN(60,70,90)

# group确定分组依据department_id
# having过滤出60 70 90部门
# select查看部门编号和max函数。

7.11、限定查询

　　SELECT 列名 FROM 表名 LIMIT 起始行,查询行数

7.11.1、查询前5行记录

#查询表中前五名员工的所有信息
SELECT * FROM t_employees LIMIT 0,5;

注意: 起始行是从0开始，代表了第一行。第二个参数代表的是从指定行开始查询几行

7.11.2、查询范围记录

#查询表中从第四条开始,查询10行
SELECT * FROM t_employees LIMIT 3,10;

7.11.3、LIMIT典型应用

分页查询:一页显示10条，—共查询三页

#思路:第一页是从8开始,显示10条
SELECT * FROM LIMIT 0,10;
#第二页是从第10条开始,显示1日条
SELECT * FROM LIMIT 10,10;
#第三页是从2日条开始,显示10条
SELECT * FROM LIMIT 20,10;

经验: 在分页应用场景中，起始行是变化的，但是一页显示的条数是不变的

7.12、查询总结

7.12.1、SQL语句编写顺序

　　SELECT 列名 FROM 表名 WHERE 条件 GROUP BY 分组 HAVING 过滤条件 ORDER BY 排序列 (ASC(DESC) LIMIT 起始行，总条数

7.12.2、SQL语句执行顺序

1、FROM：指定数据来源表

2、WHERE：对查询数据做第一次过滤

3、GROUP BY：分组

4、HAVING：对分组后的数据第二次过滤

5、SELECT：查询各字段的值

6、ORDER BY：排序

7、LIMIT：限定查询结果

7.13、子查询(作为条件判断)

　　SELECT 列名 FROM 表名 WHERE 条件(子查询结果)

7.13.1、查询工资大于Bruce的员工信息

#1.先查询到Bruce的工资(一行一列)
SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce';#工资是6000
#2.查询工资大于 Bruce的员工信息
SELECT * FROM t_employees WHERE SALARY > 6000;
#3.将1、2两条语句整合
SELECT * FROM t_employees WHERE SALARY >(SELECT SALARY FROM t_employees WHERE FIRST_NAME = 'Bruce') ;

注意: 将子查询"一行一列"的结果作为外部查询的条件，做第二次查询

子查询得到一行一列的结果才能作为外部查询的等值判断条件或不等值条件判断

7.14、子查询(作为枚举查询条件)

　　SELECT 列名FROM 表名 Where 列名 IN(子查询结果);

7.14.1、查询与名为'King'同一部门的员工信息

#思路:
#1．先查询 King 所在的部门编号(多行单列)
#部门编号:80、90
SELECT department_id
FROM t_employees
WHERE last_name = 'King';

#2．再查询80、98号部门的员工信息
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id IN (80,90);

#3 .SQL:合并
#N行一列
SELECT employee_id , first_name , salary , department_id
FROM t_employees
WHERE department_id IN (SELECT department_id FROM t_employees WHERE last_name = 'King');

将子查询多行一列的结果作为外部查询的枚举查询条件，做第二次查询

7.14.2、工资高于60部门所有人的信息

#1.查询6日部门所有人的工资(多行多列)
SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID=60;
#2.查询高于60部门所有人的工资的员工信息(高于所有)
SELECT * FROM t_employees WHERE SALARY > ALL(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60);
#3.查询高于6日部门的工资的员工信息(高于部分)
SELECT * FROM t_employees WHERE SALARY > ANY(SELECT SALARY FROM t_employees WHERE DEPARTMENT_ID = 60);

注意: 当子查询结果集形式为多行单列时可以使用ANY或ALL关键字

7.15、子查询(作为一张表)

　　SELECT 列名 FROM (子查询的结果集) WHERE 条件;

7.15.1、查询员工表中工资排名前5名的员工信息

#思路:
#1．先对所有员工的薪资进行排序(排序后的临时表)
SELECT employee_id , first_name , salary
FROM t_employees
ORDER BY salary DESC

#2．再查询临时表中前5行员工信息
SELECT employee_id , first_name , salary
FROM(临时表)
LIMIT 8,5;

#SQL:合并
SELECT employee_id ,first_name ,salary
FROM (SELECT employee_id , first_name , salary FROM t_employees ORDER BY salary DESC) AS temp
LIMIT 0,5;

将子查询“多行多列”的结果作为外部查询的一张表，做第二次查询。

注意: 子查询作为临时表,为其赋予一个临时表名

7.16、合并查询（了解)

　　SELECT * FROM 表名1 UNION SELECT * FROM 表名2

　　SELECT * FROM 表名1 UNION ALL SELECT * FROM 表名2

7.16.1、合并两张表的结果（去除重复记录)

#合并两张表的结果,去除重复记录
SELECT * FROM t1 UNION SELECT * FROM t2;

注意: 合并结果的两张表，列数必须相同，列的数据类型可以不同

7.16.2、合并两张表的结果（保留重复记录)

#合并两张表的结果,不去除重复记录(显示所有)
SELECT * FROM t1 UNION ALL SELECT *FROM t2;

经验: 使用 UNION 合并结果集，会去除掉两张表中重复的数据

7.17、表连接查询

　　SELECT 列名 FROM 表1 连接方式表2 ON 连接条件

7.17.1、内连接查询(INNER JOIN ON)

#1.查询所有有部门的员工信息(不包括没有部门的员工)SQL标准
SELECT * FROM t_employees
INNER JOIN t_jobs ON t_employees.JOB_ID = t_jobs.JOB_ID

#2.查询所有有部门的员工信息(不包括没有部门的员工) MYSQL
SELECT * FROM t_employees,t_jobs
WHERE t_employees.JOB_ID = t_jobs.JOB_ID

经验: 在Mysql中，第二种方式也可以作为内连接查询，但是不符合SQL标准·而第一种属于SQL标准，与其他关系型数据库通用

7.17.2、三表连接查询

#查询所有员工工号、名字、部门名称、部门所在国家ID
SELECT * FROM t_employees e
INNER JOIN t_departments d ON e.department_id = d.department_id
INNER JOIN t_locations t ON d.location_id = t.location_id

7.17.3左外连接(LEFT JOIN ON)

#查询所有员工信息，以及所对应的部门名称（没有部门的员工，也在查询结果中,部门名称以NULL填充)
SELECT e.employee_id , e.first_name , e.salary , d.department_name
FROM t_employees e
LEFT JOIN t_departments d ON e.department_id = d.department_id;

注意:左外连接，是以左表为主表，依次向右匹配，匹配到，返回结果

匹配不到,则返回NULL值填充

7.17.4、右外连接（RIGHT JOIN ON)

#查询所有部门信息，以及此部门中的所有员工信息(没有员工的部门，也在查询结果中，员工信息以NULL填充)
SELECT e.employee_id , e.first_name , e.salary , d.department_name
FROM t_employees e
RIGHT JOIN t_departments d ON e.department_id = d.department_id;

·注意:右外连接，是以右表为主表，依次向左匹配，匹配到，返回结果

匹配不到,则返回NULL值填充

posted @ 2021-07-04 21:28 菜鸟的道路阅读(199) 评论(0) 收藏举报

刷新页面返回顶部

Loading

菜鸟的道路

02、MySQL（DDL和DQL）

6、SQL语言

6.1、概念

6.2、MySQL应用

6.3、基本命令

6.3.1、查看MySQL中所有数据库

6.3.2、创建自定义数据库

6.3.3、查看数据库创建信息

6.3.4、修改数据库

6.3.5、删除数据库

6.3.6、查看当前所使用的数据库

6.3.7、使用数据库

7、数据查询

7.1、导入sql文件

7.2、数据库表的基本结构

7.3、基本查询

7.3.1、查询部分列

7.3.2、查询所有列

7.3.3、对列中的数据进行运算

7.3.4、列的别名

7.3.5、查询结果去重

7.4、排序查询

7.4.1、依据单列排序

7.4.2、依据多列排序

7.5、条件查询

7.5.1、等值判断(=)

7.5.2、逻辑判断(and、or、not)

7.5.3、不等值判断(>、<、>=、<=、!=、>)

7.5.4、区间判断(BETWEEN AND)

7.5.5、NULL值判断(IS NULL、IS NOT NULL)

7.5.6、枚举查询(IN(值1，值2，值3))

7.5.7、模糊查询

7.5.8、分支结构查询

7.6、时间查询

7.6.1、获得当前系统时间

7.7、字符串查询

7.7.1、字符串应用

7.8、聚合函数

7.8.1、单列总和

7.8.2、单列平均值

7.8.3、单列最大值

7.8.4、单列最小值

7.8.5、总行数

7.9、分组查询

7.9.1、查询各部门的总人数

7.9.2、查询各部门的平均工资

7.9.3、查询各个部门、各个岗位的人数

7.9.4、常见问题

7.10、分组过滤查询

7.10.1、统计部门的最高工资

7.11、限定查询

7.11.1、查询前5行记录

7.11.2、查询范围记录

7.11.3、LIMIT典型应用

7.12、查询总结

7.12.1、SQL语句编写顺序

7.12.2、SQL语句执行顺序

7.13、子查询(作为条件判断)

7.13.1、查询工资大于Bruce的员工信息

7.14、子查询(作为枚举查询条件)

7.14.1、查询与名为'King'同一部门的员工信息

7.14.2、工资高于60部门所有人的信息

7.15、子查询(作为一张表)

7.15.1、查询员工表中工资排名前5名的员工信息

7.16、合并查询（了解)

7.16.1、合并两张表的结果（去除重复记录)

7.16.2、合并两张表的结果（保留重复记录)

7.17、表连接查询

7.17.1、内连接查询(INNER JOIN ON)

7.17.2、三表连接查询

7.17.3左外连接(LEFT JOIN ON)

7.17.4、右外连接（RIGHT JOIN ON)

公告

6.3、基本命令　　