SQL(DQL与DML)

1、多表联查

1.1左外连接
查询出左边表的所有集，而右边的匹配到的则有值，没匹配到的则为null。

select 列名 from 主表 left join 从表 on 筛选条件;

1.2右外连接
解释：于和左外连接相反，查出右边数据的所有集，左边的表匹配到的显示，没匹配到的就显示null。

select 列名 从表 right join 主表 on 筛选条件;

全外语法:

select 列名 from 表1 full join 表2 on 筛选条件;

1.字符函数
length：获取字节个数
concat：拼接字符串

SELECT CONCAT(‘hello’,‘world’);

LOWER：转小写
UPPER：转大写

SELECT LOWER(‘HELLOWORLD’);
SELECT UPPER(‘helloworld’);

substr：截取
索引从1开始，到5结束所有的字符。
SELECT SUBSTR(‘helloworld’,1,5);#结果：hello

2.数字函数
round：四舍五入

select round(1.55);#结果：2

ceil：向上取整

select ceil(1.02);#结果：2

floor：向下取整

select floor(1.9);#结果：1

truncate：截断

SELECT TRUNCATE(2.333333,2);#结果：2.33

mod：取余

SELECT MOD(1600,300);#结果：100

3.日期函数
3.1. now 返回当前系统日期+时间
SELECT NOW();

3.2. curdate 返回当前系统日期，不包含时间
SELECT CURDATE();

3.3. curtime 返回当前时间，不包含日期
SELECT CURTIME();
3.4 str_to_date 将字符通过指定的格式转换成日期

3.5 date_format 将日期转换成字符

4.流程控制函数
if函数：
SELECT
last_name,
commission_pct,
IF
( commission_pct IS NULL, '1111, ‘2222’ ) 备注
FROM
t_mysql_employees;
case函数：

SELECT
salary 原始工资,
department_id,
CASE
department_id
WHEN 30 THEN
salary * 1.1
WHEN 40 THEN
salary * 1.2
WHEN 50 THEN
salary * 1.3 ELSE salary
END AS 新工资
FROM
t_mysql_employees;

子查询
子查询其实就是在sql语句中嵌套sql语句。
步骤：
①逻辑分析充分需求，分段书写SQL子句
②最后拼接sql子句完成需求

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用> < >= <= = <>
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果。

查询工资比Abel高的员工名字和工资
SELECT
last_name,
salary
FROM
t_mysql_employees
WHERE
salary > ( SELECT salary FROM t_mysql_employees WHERE last_name = ‘Abel’ );

查询工种与141号员工相同、工资比143号员工高的姓名，工种，工资

SELECT
last_name,
job_id,
salary
FROM
t_mysql_employees
WHERE
job_id = ( SELECT job_id FROM t_mysql_employees WHERE employee_id = 141 )
AND salary > ( SELECT salary FROM t_mysql_employees WHERE employee_id = 143 );

分页查询
语法：
原有查询+limit startIndex,offset

注意事项：
1、原始查询的记录数<offset，那么查询的结果必然是原始查询的记录数
2、startIndex=0，那么可以省略，limit offset

startIndex要显示条目的起始索引（起始索引从0开始）
offset要显示的条目个数

DML（数据管理语言）
1.简介：
DML（Data Manipulation Language ）数据操作语言，可以再下列条件下执行：
1.插入数据
•1.1使用 INSERT 语句向表中插入数据。
这种语法只能一次向表中插入一条数据

1.添加数据
关键字：INSERT
语法：INSERT INTO 表名 (列名,列名…) VALUES (值,值…);

1.1添加一条数据
INSERT INTO t_mysql_departments ( department_id, department_name, manager_id, location_id )
VALUES( 70, ‘Public Relations’, 100, 1700 );

2.修改数据
关键字：UPDATE
语法：UPDATE 表名 列名=新值 WHERE 筛选条件;
• 如果省略 WHERE 子句，则表中的所有数据都将被更新

修改张无忌的女朋友的手机号为119
UPDATE t_mysql_boys bo
INNER JOIN t_mysql_beauty b ON bo.id=b.boyfriend_id
SET b.phone=‘119’,bo.userCP=1000
WHERE bo.boyName=‘张无忌’;

3.删除数据
关键字：DELETE
语法：DELETE FROM 表名 WHERE 筛选条件;
• 如果省略 WHERE 子句，则表中的全部数据将被删除

删除张无忌的女朋友的信息
DELETE b
FROM
t_mysql_beauty b
INNER JOIN t_mysql_boys bo ON b.boyfriend_id = bo.id
WHERE
bo.boyName = ‘张无忌’;

delete和 truncate的区别
1.delete可以加where条件，用来删除指定数据， truncate则不能，只能删除全部。
2.truncate删除效率高一点点。
3.假如要删除的表中有自增长列，
如果用delete删除后，再插入数据，自增长列的值从断点开始，
而truncate删除后，再插入数据，自增长列的值从1开始。
4.truncate删除不能回滚，delete删除可以回滚。
5.truncate删除没有返回值，delete删除有返回值