mysql基础（DQL语言、DML语言、DDL语言）详解

DQL语言：数据库查询语言

分组查询：Group by
和分组函数搭配使用：
案例：通过部门编号筛选每个部门的平均工资

Select department_id,avg(salary) where 表名 group by department_id

分组后筛选：
案例：查询每个部门人数>2的部门

Select count(*),department_id from employees group by department_id 
<font color=red>Having count(*)>2

排序查询：order by
Select * from 表名 order by 字段名 desc;
DESC：从大到小
ASC：默认从小到大

连接查询：内连接（等值连接、非等值连接、自连接）、外连接（左外连接、右外连接、全外连接（mysql不支持，orcle支持）、交叉连接）

sql92支持：内连接
sql99支持：内连接、外连接、交叉连接

内连接：等值连接、非等值连接、自连接
等值连接（sql99语法）：内连接的结果等于两表的交集
select 【查询列表】
from 表A 别名1 (inner) join
表B 别名2
on 1.key=2.key（取交集部分）

外连接：左外连接、右外连接、全外连接（mysql不支持，orcle支持）、交叉连接
左外连接：查询的结果为主表（左表）中所有的行，如果从表（右表）和它匹配则显示匹配行，不匹配则显示null值。
语法：
select 【查询列表】
from 表A 别名1 left join
表B 别名2
on 1.key=2.key

案例：查询哪个学生的成绩为空
涉及到两张表，学生表和成绩表，其中学生表的stu_id和成绩表的stu_id作为连接条件，一般“哪个”后面的字段所在的表为主表，该案例为学生表，所以学生表为主表，左查询实放在左边，右查询时放在右边

select s.*,r.score
from student s LEFT JOIN result r
on s.stu_id = r.stu_id
where r.id = null

右外连接：查询主表（右表）中所有的值，从表（左表）中有的显示表值，没有的显示null值
语法：
select 【查询列表】
from 表A 别名1 right join
表B 别名2
on 1.key=2.key

若要去掉交集部分，添加筛选即可

全外连接：取得两个表所有的值+交集部分

若要去掉交集部分，

交叉连接：查询结果为两张表的笛卡尔乘积
语法：
select 【查询列表】
from 表1 cross join 表2

子查询：出现在其他语句中的select语句，称为子查询

分类：

• 按出现的位置分类：

select后面：仅仅支持标量子查询
from后面：支持表子查询
where和having后面：支持标量子查询（单行）或列子查询（多行），行子查询较少
exists后面：支持表子查询

• 按结果集的行列数分类：

标量子查询：结果集只有一行一列
列子查询：结果级只有一列多行
行子查询：结果集只有一行多列
表子查询：结果集是多行多列

①where后面的标量子查询：一般搭配 > < = <>使用
案例：查询谁的工资比Able高？

select last_name,salary
from employees
where salary>(select salary from employees where last_name='Able')

案例：查询哪个部门的最低工资大于50号部门的最低工资？（分组查询+子查询）
第一步：查出50号部门的最低工资

SELECT MIN(salary) FROM employees WHERE department_id=50;

第二部查询每个部门的最低工资（按部门分组查询最低工资）

SELECT MIN(salary),department_id FROM employees GROUP BY department_id;

第三步：查询是那个部门大于50的最低工资

SELECT MIN(salary),department_id FROM employees GROUP BY department_id HAVING MIN(salary)>
(SELECT MIN(salary) FROM employees
WHERE department_id=50)

②where后面的列子查询：把子查询的结果充当筛选条件
一般搭配in、any、all使用

③select后面的子查询：把子查询的结果充当查询列表
案例：查询每个部门的员工个数

SELECT department_id,(
SELECT COUNT(*) FROM employees 
WHERE employees.`department_id`=departments.`department_id`) 
FROM departments

④from后面的子查询：把子查询的结果集充当一张表（必须起别名）
案例：查询每个部门平均工资的工资等级
第一步：查询每个部门的部门编号和平均工资

SELECT AVG(salary),department_id FROM employees GROUP BY department_id

第二步：把第一步的结果集当成一张表连接工资登记表进行非等值连接

SELECT av.*,g.grade_level
FROM (SELECT AVG(salary) avs,department_id FROM employees GROUP BY department_id) av
INNER JOIN job_grades g ON
av.avs BETWEEN g.lowest_sal AND g.highest_sal

⑤exists后面的子查询（相关子查询）：一般子查询都是一个连接查询
案例：查询没有女朋友的男生的信息
方式一：使用外连接查询

SELECT bo.*FROM boys bo LEFT JOIN beauty b ON bo.`id`=b.`boyfriend_id` WHERE b.`boyfriend_id` IS NULL

方式二：使用子查询where+in（select）

 SELECT bo.*,FROM boys bo WHERE bo.`id` NOT IN (SELECT beauty.`boyfriend_id` FROM beauty);

方式三：使用exists

 SELECT bo.* FROM boys bo WHERE NOT EXISTS(SELECT b.`boyfriend_id` FROM beauty b WHERE b.`boyfriend_id`=bo.`id`)

DML语言：数据的增删改

添加语句： insert into(字段1,字段2,字段3,…字段n) 表名 values（值1,值2,值3,…值n）,（值1,值2,值3,…值n）

修改语句： update 表名 set 字段1=值1,字段2=值2 where【 筛选条件】

删除语句：
方式一：delete from 表名 where 【筛选条件】
方式二：truncate table 表名（将表的数据全部删除）

【面试题】使用删除语句中，delete和truncate的区别：

• ①delete可以加筛选条件where，truncate不能加
• ②truncate对比delete进行整表删除时效率较高
• ③delete删除数据后，再进行添加操作该表的自增id从断点处开始；而truncate删除表后，在进行添加操作后该表的自增加id从1开始。
• ④在处理事务时，delete删除后可以回滚，truncate不能回滚。

DDL语言：数据定义语言（主要涉及库和表的创建、修改、删除）

创建：create
语法：create table 表名（字段名 数据类型 【约束】，字段名2 数据类型2 【约束】）
修改：alter
语法：alter table 表名 add|drop|modify|change column 字段名 【字段类型 约束】
删除：drop
语法：drop table 表名

表的复制：
①只复制表的结构：create table 复制的表名 like 被复制的表名;
②复制表的结构和数据：create table 复制的表名 select * from 被复制的表名

表的创建时涉及到的6大约束：

NOT NULL：该字段不能为空
DEFAULT：设定该字段的默认值
CHECK（字段名 = ？）：检查该字段是否由？组成
PRIMARY KEY：主键
FOREIGN KEY：外键
UNIQUE：唯一建
#其中主键、外键、唯一键都会自动生成索引

#语法：
#CREATE TABLE 表名(
#	字段名 字段类型 【约束】,
#	字段名2 字段类型2 【约束2】,
#	....
#	字段n 字段类型n 【约束n】
#);

CREATE TABLE stuInfo(
	id INT,
	stuName VARCHAR(15) NOT NULL,#设置非空
	age INT DEFAULT 18,#设置默认
	seat INT,
	majorId INT,
	
	#以下为表级约束
	PRIMARY KEY(id),#设置主键
	UNIQUE (seat),#设置唯一键
	FOREIGN KEY (majorId) REFERENCES major(id)#设置外键

)
CREATE TABLE major(
	id INT PRIMARY KEY,
	majorNAme VARCHAR(12)
)

【面试题】主键和唯一键的区别：

• 两者都能保证唯一性
• 两者都可以进行字段组合（但不推荐）
• 主键不能为null，唯一键可以为null，但只能有一个值为null
• 主键一个表中只能有一个，唯一键可以有多个

【面试题】外键的特点：

• 要求外键在从表中设置
• 外键的类型和关联列的类型要求一致或兼容
• 外键关联的列必须为主键或唯一键

【面试题】自增长列的特点：

• 必须和主键和唯一键搭配使用
• 一张表至多一个自增长列
• 自增长列的类型只能是数值型
• 可以通过set auto_increment_increment=?设置步长

MySQL 函数分类：

字符函数：

去重：distinct
Select distinct 字段名 from 表名

+:
只做加法运算，不做连接

Concat函数:连接符
Select concat(str1,str2) from 表名

Length函数：获取参数值的字段个数
Select length(email) from 表名

Upper/lower函数:改变大小写

Trim函数：去掉字段前后的多余字符

ifnull函数：判断字段是否为null，如果为null，返回指定值，不为null，返回原始值

isnull函数：判断字段是否为null，是返回1否返回0

数学函数：

Round函数：四舍五入

Ceil函数：向上取整

Floor函数：向下取整

Truncate函数：保留几位小数点
Select truncate(1.69999,1)———>1.6

Mod函数：取模

日期函数：

Now函数：返回当前日期和时间
Curdate：返回当前日期
Curtime：返回当前时间
Year(),month(),day(),hour()

Str_to_date（str,str）：将日期格式的字符串转成指定日期
Select str_to_date(‘9-13-1999’,’%m-%d-%Y’）

Date_format（date,str）：将日期转成字符
Select date_format(now(),’%y年%m月%d日’)

DateDiff(date1,date2)：比较两个日期的大小，第一个大，返回正数；第二个大返回负数；相等返回0

条件判断函数：

If(,)：类似java三元运算符

Case salary 工资
When 常量1 then 表达式1
When 常量2 then 表达式2
Else 表达式3
end

分组函数：

Sum
Avg
Max
Min
Conut