SQL语句的学习笔记（详细）

SQL语句主要分为 DQL语言(Data Query Language), DML语言(Data Manipulation Language), DDL语言(Data Define Language), TCL语言(Transaction Control Language)4种语言。

DQL语言（CRUD中的R,即Retrieve）

基础查询

语法：select 查询列表 from 表名；
其中，查询列表可以是：①表中字段；②常量值；③表达式；④函数。此“字段”相当于表中的一个或多个列，对应java中的“属性”，表中的每一行数据，相当于java中的“对象”，此部分参考ORM。

1.查询表中的单个字段：
SELECT first_name FROM employees;

查询表中的多个字段（如果需要查询的字段数量多，可以点击表中想要查询的对应字段，就会在sql文件中添加对应字段,可以按F12对文件中SQL语句进行格式化）：
SELECT first_name,last_name,address FROM employees;

2.查询表中的所有字段：
SELECT *FROM employees;

//Tips：
如果在初始状态下去采用以上SQL语句，
那么有可能会出现数据库不存在等类似错误信息，
此时就需要输入 “USE  对应的库名;”  进入数据库，
就按上方的例子来举例，employees表对应的库名是myemployees;
那么要从employees表中查询到字段信息，
就需要先USE myemployees进入myemployees表，
否则就得在每个表前加上“myemployees.”来表示是myemployees的*表

3.查询函数
SELECT VERSION(); //查询数据库当前版本

4.常量值和表达式（这两个方面基本不会用到）
SELECT 100；
SELECT ‘rose’;
SELECT 100%50;

5.起别名(可提高可读性)
①SELECT last_name AS 姓，first_name AS 名 FROM employees;
②SELECT last_name 姓，first_name 名FROM employees;

//使用情况：
如果要查询的字段有重名的情况，使用别名区分开来；
如果别名中有特殊符号，比如`空格，#`等，
那么可以在别名的整体范围上加个双引号（MySQL推荐）或者单引号。

6.去重
如果要查询员工表中所涉及的所有部门编号，但是数量繁多，不易看出包括的部门有哪一些，那么可以采用DISTINCT关键字
SELECT DISTINCT department_id FROM employees;

7.将几个字段连接成一个字段
这里将会使用到CONCAT连接的关键字，具体用法如下：
SELECT
CONCAT(last_name,first_name) AS 姓名
FROM
employees;
那么执行完上面这条SQL语句后就会显示一列字段是last_name和first_name拼凑而成的字段，就可以完整的显示姓名。

//注意：
这里不能用“ + ”号对数据库的数个字段进行连接，MySQL中“ + ”仅仅只有一个功能，那就是运算符，就像上面说的查询列表可以是表达式，就像这样，SELECT 100+90; 就可以得出100+90 190的字段。

扩充: IFNULL(expr1,expr2)函数中，如果选中的列名(即expr1)为空，那么 将会返回expr2的值。
举例：
SELECTIFNULL(commission_pct,0.00) FROM employees;
意思为：
从employees表中选取commission_pct字段，如果该字段为NULL,那么则返回0.00值。

条件查询

语法：
SELECT 查询列表 FROM 表名 WHERE 筛选条件;
筛选条件有以下几种情况：
①条件表达式
简单条件运算符： < > = !=(不等也可以写为<>) >= <=
#-- 案例1：查询工资大于1W2的员工信息
SELECT * FROM employees WHERE salary>12000;
#-- 案例2： 查询部门编号不等于90号的员工名和部门编号
SELECT last_name,department_id FROM employees WHERE department_id<>90
②逻辑表达式
逻辑运算符：&& || !
分别对应标准写法为: and or not
#-- 案例1：查询工资在1w到2w之间的员工名，工资以及奖金
SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary<=20000;
#-- 案例2： 查询部门编号不是在90到110之间，或者工资高于15000的员工信息
①SELECT * FROM employees WHERE department_id<=90 OR department_id>=110 OR salary>=15000;
②SELECT * FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>=15000;
③模糊查询
a. LIKE（一般和通配符搭配使用）[通配符：%表示任意多个字符(包含0个字符)；_任意单个字符]
#-- 案例1： 查询员工名中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
#-- 案例2： 查询员工名中第三个字符为a，第五个字符为b的员工名和工资
SELECT last_name,salary FROM employees WHERE last_name LIKE'__a_b%';
#-- 案例3： 查询员工名中第二个字符为_的员工名
SELECT last_name FROM employees WHERE last_name LIKE '_\ _%';
亦可以
SELECT last_name FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';
b. BETWEEN AND(可以包含临界值，但是两个临界值不能调换顺序）
#-- 案例：查询员工编号在100到200之间的员工信息
①SELECT * FROM employees WHERE employee_id>=100 AND employee_id<=200;
②SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 200;
c. IN(判断某字段的值是否属于in列表中的某一项，in列表中的值类型必须一致或者兼容)
#-- 案例： 查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个的员工名和工种编号
①SELECT last_name,job_id FROM employees WHERE job_id=‘IT_PROG’ OR ob_id=‘AD_VP’ OR ob_id=‘AD_PRES’;
②SELECT last_name,job_id FROM employees WHEREjob_id IN('IT_PROG','AD_VP','AD_PRES');
d. IS NULL(= 或 <>不能用于判断NULL值，IS NULL 或 IS NOT NULL可以判断null值)
#-- 案例1：查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;
#-- 案例2：查询有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

练习题:
1.查看departments表的结构是如何的；
命令: DESC departments;
2.经典面试题：
SELECT * FROM employees 和
SELECT * FROM employees WHERE commission_pct LIKE ‘%%’ AND last_name LIKE ‘%%’;结果是否一样？并说明原因
答：不一样。如果判断的字段有NULL值，那么查询出来的结果是不一样的，反之则一样。

排序查询

语法：SELECT 查询列表 FROM 表 [WHERE 筛选条件] ORDER BY 排序列表 [ASC|DESC](ASC代表的是升序[默认]，DESC代表的是降序;ORDER BY 子句中支持单个字段，多个字段，表达式，函数，别名，一般位于语句最后面，除了LIMIT子句)
#-- 案例1 ：查询员工信息，要求工资从高到低排序
SELECT * FROM employees ORDER BY salary DESC;
#-- 案例2 ：查询员工信息，要求工资从低到高排序
SELECT * FROM employees ORDER BY salary ASC;
#-- 案例3：查询部门编号大于等于90的员工信息，按入职时间的先后进行排序【添加筛选条件】
SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ASC;
#-- 案例4：按年薪的高低显示员工的信息和年薪【按表达式排序】
SELECT ,salary12*(1+IFNULL(commission_pct,0)) FROM employees ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;
#-- 案例5：按年薪的高低显示员工的信息和年薪【按别名排序】
SELECT *,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪FROM employees ORDER BY 年薪 DESC;
#-- 案例6：按姓名的长度显示员工的姓名和工资【按函数排序】
SELECT last_name,salary FROM employees ORDER BY LENGTH(last_name) ASC;
#案例7：查询员工信息，要求先按工资排序，再按员工编号排序【按多个字段排序】
SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;

常见函数（类似java中的方法）

语法：SELECT 函数名(实参列表) [FROM 表];

单行函数（CONCAT、LENGTH、IFNULL…）

• 字符函数
  1.LENGTH 获取参数值的字节个数
  举例：SELECT LENGTH(‘细节决定chengbai’); 查询得到的结果是20（因为MySQL默认的编码方式是UTF-8，所以每个中文汉字是3个字节，加上每个字母单个字节，总长度为20）；
  2.UPPER、LOWER 使大写/使小写
  举例： SELECT UPPER('john'); 返回结果：JOHN
  SELECT LOWER('joHN'); 返回结果：john
  亦可以这样使用：
  SELECT CONCAT(UPPER(last_name),LOWER(first_name))FROM employees;
  3.SUBSTR/SUBSTRING
  a.SUBSTR('对应的字符'，num1）
  截取从指定索引处后面的所有字符（注意，索引第一个从1开始，不是从0）
  b.SUBSTR(对应的字符'，num1,num2）
截取从指定索引处指定字符长度的字符
  案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
  SELECTCONCAT(UPPER(SUBSTR('last_name',1,1)),'_','LOWER(SUBSTR(last_name',2))) AS out_put FROM employees;
  4.INSTR 返回子串第一次出现的索引，如果找不到就返回0
  SELECT INSTR('细节决定成败','成败') AS out_put; 返回的结果是5
  5.TRIM 默认去掉实参列表中前和后的空格，也可以在实参列表之前加’ ? ’ FROM ,'?'表示要去掉的字符。
  SELECT TRIM('a' FROM 'aaaa细节决定成败aaa'); 返回的结果为"细节决定成败"
  6.LPAD、RPAD 用指定的字符实现左/右填充指定长度
  LPAD/RPAD('传入的参数',填充后的总长度,'填充的字符')

这里填充后的总长度不是按LENGTH方法来算的
而是按个数来算的长度，并非字节数
可以总结来说
除了LENGTH()方法需要计算到字节数
其他的都只需要计算个数

7.REPLACE 替换
REPLACE(‘传入的参数’,‘要替换的字符串’,‘替换上去的字符串’)

• 数学函数
  1.ROUND 四舍五入
  SELECT ROUND(1.65); 返回 2
  SELECT ROUND(-1.65); 返回 -2
  SELECT ROUND(3.14168,2); 返回3.14 (小数点后四舍五入后保留2位）
  2.CEIL 向上取整，返回>=该参数的最小整数
  SELECT CEIL(1.5); 返回2
  SELECT CEIL(-1.5); 返回-1
  3.FLOOR 向下取整，返回<=该参数的最大整数
  SELECT FLOOR(-9.99); 返回-10
  4.TRUNCATE 小数点后保留一位，不四舍五入
  SELECT TRUNCATE(1.6999,1); 返回1.6;
  5.MOD 取余
  MOD(a,b); a对b取余
• 日期函数
  1.NOW 返回当前系统日期+时间
  2.CURDATE 返回当前系统日期，不包含时间
  3.CURTIME 返回当前系统时间
  4.可以获取指定的年、月、日、小时、分钟、秒
  SELECT MONTH(NOW()) 月; 显示中文
  SELECT MONTHNAME(NOW()) 月； 显示英文
  5.STR_TO_DATE 将字符通过指定的格式转换成日期
  如：SELECT STR_TO_DATE('6-18-2020','%m-%d-%Y');
  返回2020-06-18
  6.DATE-FORMAT 将日期转换为字符
  SELECT DATE-FORMAT(‘2020/6/18’,’%Y年%m月%d日’)
  返回2020年6月18日
  可以参考如下表格:

序号	格式符	功能
1	%Y	四位的年份
2	%y	两位的年份
3	%m	月份(01,02,…,11,12)
4	%c	月份(1,2,…,11,12)
5	%d	日(01,02,…)
6	%H	小时(24小时制)
7	%h	小时(12小时制)
8	%i	分钟(00,01,…,59)
9	%s	秒(00,01,…,59)

• 其他函数
  SELECT VERSION();
  SELECT DATABASE();
  SELECT USER();
• 流程控制函数
  1.IF函数： if else的效果
  SELECT IF(10<5,‘大’,‘小’); 返回小
  这里相当于10<5?大:小 如果前面表达式为真，则为大，反之则小。
  2.CASE函数的使用一： switch case的效果
  语法：
  CASE 要判断的字段或表达式
  WHEN 常量1 THEN 要显示的值1或语句1
  WHEN 常量2 THEN 要显示的值2或语句2
  ...
ELSE 要显示的值n或语句n
  END
  #-- 案例1： 查询员工的工资，要求
  部门号=30，显示的工资为1.1倍
  部门号=40，显示的工资为1.2倍
  部门号=50，显示的工资为1.3倍
  其他部门，显示的工资为原工资
  SQL语句：
  SELECT salary 原始工资，department_id 部门编号 ,
  CASE department_id WHEN 30 WHEN salary*1.1 WHEN 40 WHEN salary*1.2 WHEN 50 WHEN salary*1.3 ELSE salary END AS 新工资
  FROM employees;
  3.CASE函数的使用二：类似于 多重if;
  CASE
WHEN 条件1 THEN 要显示的值1或语句1
  WHEN 条件2 THEN 要显示的值2或语句2
  ...
ELSE 要显示的值n或语句n
  END
  #-- 案例2：查询员工的工资的情况
  如果工资>20000,显示A级别
  如果工资>15000,显示B级别
  如果工资>10000,显示C级别
  否则显示D级别
  SQL语句:
  SELECT salary,
  CASE WHEN salary>20000 THEN 'A' WHEN salary>15000 THEN 'B' WHEN salary>10000 THEN 'C' ELSE 'D' END AS '工资级别'
  FROM employees;

分组函数（统计函数、聚合函数、组函数）

• SUM 求和(处理数值类型，并且忽略NULL值)
  SELECT SUM(salary) FROM employees;(以下几种类似)
• AVG 平均值(处理数值类型，并且忽略NULL值)
• MAX 最大值(处理任何类型，也忽略NULL值)
• MIN 最小值(处理任何类型，也忽略NULL值)
• COUNT 计算非空的值的个数(处理任何类型，可以和DISTINCT搭配使用)
  SELECT COUNT(*) FROM employees;
  选取表中所有的行数，每一列中但凡有一个不为NULL值都会计入其中。 也有以下的情况，在括号中加入数值或者字符都可以计算表中总的行数，如下：
  SELECT COUNT(1) FROM employees;
  SELECT COUNT('STR') FROM employees;
  效率上的对比：
  在MYISAM存储引擎下，COUNT(*)效率要高些
  在INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些

//可以通过这条命令来知道自己来到世上多少天了哦
SELECT DATEDIFF(NOW(),'****-**-**');
//'****-**-**'填写自己的年月日
//DATEDIFF方法是来计算第一个日期参数和第二个日期参数相差多少天

分组查询

• GROUP BY
  语法：
  //分组函数和列之间可以调换位置
  SELECT 分组函数，列(要求出现在GROUP BY的后面)
  FROM 表
  [WHERE 筛选条件]
  GROUP BY 分组的列表
  [ORDER BY 子句]
  注：
  查询列表必须特殊，要求是分组函数和GROUP BY后出现的字段。
  – 案例1：查询每个工种的最高工资
  SELECT MAX(salary),job_id
  FROM employees
  GROUP BY job_id;
  – 案例2：查询每个位置上的部门个数
  SELECT COUNT(*),location_id
  FROM departments;
  GROUP BY location_id;
  #添加筛选条件
  –案例3：查询邮箱中包含a字符的，每个部门的平均工资
  SELECT department_id,AVG(salary)
  FROM employees
  WHERE email LIKE '%a%'
  GROUP BY department_id;
  –案例4：查询每个领导手下有奖金的员工的最高工资
  SELECT MAX(salary),manager_id
  FROM employees
  WHERE commission_pct IS NOT NULL
  GROUP BY manager_id;
  #添加分组后的筛选条件
  –案例5：查询哪个部门的员工个数>2
  ①查询每个部门的员工个数
  SELECT COUNT(*),department_id
  FROM employees
  GROUP BY department_id;
  ②根据①的结果进行筛选，查询哪个部门的员工个数>2
  把上面的SQL语句挪下来后，在语句最后面添加HAVING子句，完成查询
  SELECT COUNT(*),department_id
  FROM employees
  GROUP BY department_id
HAVING COUNT(*)>2
  –案例6,：查询每个工种有奖金的员工的最高新工资>12000的工种编号和最高工资(作为巩固)
  SELECT MAX(salary*12*(1+commission_pct)),job_id
  FROM employees
  WHERE commission_pct IS NOT NULL
  GROUP BY job_id
  HAVING MAX(salary*12*(1+commission_pct))>12000
  –案例7：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
  ①查询每个领导手下的员工固定最低工资
  SELECT MIN(salary),manger_id
  FROM employees
  GROUP BY manager_id
  ②添加筛选条件：编号>102
  SELECT MIN(salary),manager_id
  FROM employees
  WHERE manager_id>102
  GROUP BY manager_id
  ③添加筛选条件：最低工资>5000
  SELECT MIN(salary),manager_id
  FROM employees
  WHERE manager_id>102
  GROUP BY manager_id
  HAVING MIN(salary)>5000;

连接查询

定义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。
笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n行
（发生原因：没有有效的连接条件；如何避免：添加有效的连接条件）
SELECT employee_name,repair
FROM t_employee,dormitory
WHERE t_employee.dormitory_id = dormitory.id

分类：
1.按年代：
a.sql92标准（仅支持内连接）
b.sql99标准（推荐）（支持内+外连接【左外和右外】+交叉连接）
2.按功能：
a.内连接：等值连接、非等值连接、自连接
b.外连接：左外连接、右外连接、全外连接
c.交叉连接

举例：
等值连接：上面笛卡尔积现象下的举例
注： 为表起别名：
①提高语句的简洁度(在使用连接的过程中，往往会涉及到多个表，如果表名长度长，在写SQL语句的过程中，就会出现比较冗余的现象，所以可以通过起别名来提高语句的简洁度)
②区分多个重名的字段
e.g.:查询员工名，工种号，工种名
SELECT employees.last_name,employees.job_id,jobs.job_title
FROM employees , jobs
WHERE employees.`job_id` = jobs.`job_id`
通过起别名,[AS 可以省略！]
SELECT e.last_name,e.job_id,j.job_title
FROM employees AS e , jobs AS j
WHERE e.`job_id` = j.`job_id`
！！！但是，起别名之后，就没办法用之前的表名了！！！用employees和jobs就会报错！！！
另外，在此之上，还可以加筛选的条件
SELECT e.last_name,e.job_id,j.job_title
FROM employees e , jobs j
WHERE e.`job_id` = j.`job_id`

后续会继续更新

子查询

分页查询

union联合查询

DML语言(CRUD中的CUD(Create,Update,Delete))

插入语句

修改语句

删除语句

DDL语言

库和表的管理

常见数据类型

常见约束

TCL语言

事务和事务处理