MySQL基础（DQL语言）

一、MySQL

优点：成本低，性能高，简单

服务的启动与停止：启动：net start 服务名 停止：net stop 服务名

登录与退出：

命令提示符登录：mysql 【-h (主机名) -P(端口)】 -u (用户名) -p(密码)

退出：exit / ctrl+ c

常见命令：

查看当前所有数据库	show databases;
打开指定库	use 库名;
查看当前库的所有表	show tables;
查看其他库的所有表	show tables from 库名;
创建表	create table 表名(列名 列类型,列名 列类型,...)
查看表结构	desc 表名;

语法规范：

1.不区分大小写，但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进或换行
4.注释
    单行注释：#注释文字
    单行注释：-- 注释文字
    多行注释：/* 注释文字 */

数据库存储数据特点：

1.将数据放到表中，表在放到库中
2.一个数据库中可以有多个表，每个表具有唯一的表名
3.表具有一些特性，这些特性定义了数据在表中如何存储，类似 java 中“类”的设计
4.表有列组成，也称为字段，所有表都是由一个或多个列组成，每列类似 java 中 的“属性”
5.表中的数据是按行存储的，每一列类似 java 中的”对象“

二、DQL语言

1、基础查询

1.1 查询单个字段

SELECT last_name FROM employees;

1.2 查询多个字段

SELECT last_name,salary,email FROM employees;

1.3 查询表中的所有字段

#方式一：
SELECT 
  `employee_id`,
  `first_name`,
  `last_name`,
  `email`,
  `phone_number`,
  `job_id`,
  `salary`,
  `commission_pct`,
  `manager_id`,
  `department_id`,
  `hiredate` 
FROM
  employees;
#方式二：
SELECT * FROM employees;

1.4 查询常量值

SELECT 100;
SELECT 'john';

1.5 查询表达式

SELECT 100*98;

1.6 查询函数

SELECT VERSION();

1.7 起别名，AS关键字

#方式一：
SELECT 100*98 AS 结果;
SELECT last_name AS 姓,first_name AS 名 FROM employees;

#方式二：
SELECT last_name 姓,first_name 名 
FROM employees;

1.8 去重，DISTINCT关键字

SELECT DISTINCT department_id FROM employees;

1.9 显示表结构，DESC关键字

DESC departments;

2、条件查询

2.1 条件运算符

        <，>，=，<>(同!=)，<=，>=

2.2 逻辑运算符

        关键字：and，or，not

2.3 模糊查询

        关键字：like，between...and...，in，is null ，is not null

like
'%a%'：包含a的字符，%为通配符
通配符：
% 任意多个字符，包含0个字符
_ 任意当个字符
例：...WHERE last_name LIKE  '%a%';

可通过\进行转义，或者使用ESCAPE关键字，标记指定字符为转义字符
例：'$_' ESCAPE '$'

between and(包含临界值)
例：...WHERE employee_id BETWEEN 100 AND 120;

in(判断某字段是否属于 in 列表中的一项，in 列表值必须一致或兼容，且不支持通配符)
例：...WHERE job_id IN('IT_PROT','AD_VP','AD_PRES');

is null(=或<>不能用于判断 null 值，is null 仅可以判断 null 值)
例：...WHERE commission_pct IS NULL;

安全等于<=>(判断是否等于)
例：...WHERE commission_pct <=> NULL;

3、排序查询

/*语法：
	select	 查询列表
	from	 表名
	[where  筛选条件]
	order by 排序列表 [asc|desc]

asc 升序，desc 降序 */

SELECT * FROM employees ORDER BY salary DESC;

4、常见函数

4.1 单行函数

字符函数	说明
length(str)	获取参数值的字节个数
concat(str1,str2,...)	拼接字符串
upper(str)	转大写
lower(str)	转小写
substr(str,pos)	返回 pos 索引（索引从1开始）后所有字符
substr(str,pos,len)	返回 pos 索引（索引从1开始）开始的 len 个字符
instr(str,substr)	返回 substr 第一次在 str 出现的索引，若没有则返回0
trim(str)	清除 str 前后的空格
lpad(str,len,padstr)	用指定 padstr 在str 左边填充到 len 长度
rpad(str,len,padstr)	用指定 padstr 在str 右边填充到 len 长度
replace(str,from_str,to_str)	替换

数学函数	说明
round(X)、round(X,D)	四舍五入 四舍五入，保留D位小数
ceil(X)	向上取整，返回>=该参数的最小整数
floor(X)	向下取整
truncate(X,D)	截断，保留X小数点后D位
mod(n,m)	取余，运算方式：n-n/m*m
rand()、rand(X)	获取随机数，返回0~1之间小数，不可重复的 指定随机数生产的种子X，产生的随机数是可重复的

日期函数	说明
now()	返回当前系统日期+时间
curdate()	返回当前系统日期，不包含时间
curtime()	返回当前时间，不包含日期
year(date)、month(date)、day(date)、hour(date)、minute(date)、second(date)	获取指定的年、月、日、时、分、秒
monthname(date)	返回英文的月份名
datediff(date1,date2)	返回两个日期相差的天数
str_to_date(str,format)	将日期格式字符转换为指定格式的日期
date_format(date,format)	将日期转换为字符

流程控制函数

if(条件表达,表达式1,表达式2)
例：if(10<5,'大','小')

case()：
使用一，类似 switch case
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值 n 或语句 n
end

使用二，类似于多重 if
case
when 条件1 then 要显示的值1或语句1
when 条件1 then 要显示的值1或语句1
...
else 要显示的值 n 或语句 n
end

其他函数 

version()：版本
database()：当前打开的数据库
user()：当前用户
password('字符')：返回该字符的密码形式
MD5('字符')：返回该字符MD5加密形式

4.2 分组函数

sum(expr)：求和        avg(expr)：平均值        max(expr)：最大值        min(expr)：最小值
count(expr)：计算个数

特点：
1.sum、avg 处理数值型
max、min、count 可处理任何类型
2.分组函数都忽略null值
3.可以和distinct搭配使用
例：sum(distinct ex)
4.count(*) 统计所有非空个数
count(1) 
count(字段)
MYISAM存储引擎下，count(*)效率高
INNODB下，count(*)>count(1)>count(字段)
5.和分组函数一同查询的字段要求是 group by 后的字段

5、分组查询

语法：
分组前筛选：
	select	 分组函数，列(要求出现在 group by 后面)
	from	 表名
	[where  筛选条件]
	group by 分组列表
	[order by 排序列表];
注意：
查询列表必须特殊，要求是分组函数和 group by 后出现的字段

分组后的筛选：
	select	 分组函数，列
	from	 表名
	group by 分组列表
	having 筛选条件
	[order by 排序列表];

	数据源	位置	关键字
分组前筛选	原始表	group by前	where
分组后筛选	分组后结果集	group by后	having

6、连接查询

使用sql99语法

6.1 内连接

#等值连接
#例：
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.'department_id'= d.'department_id';

#非等值连接
#例：
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e,'salary' BETWEEN g.'lowest_sal' AND g.'highest_sal';

#自连接
#例：
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.'manager_id'=m.'employee_id';

6.2 外连接

#左外连接
#得到表A中所有部分和表A与表B的交集部分
SELECT <select_list>
FROM A
LEFT JOIN B
ON A.key=B.key;
#得到表A中去掉表A与表B的交集部分的剩余部分
SELECT <select_list>
FROM A
LEFT JOIN B
ON A.key=B.key;
WHERE B.key IS NULL;

#右外连接
#得到表B中所有部分和表B与表A的交集部分
SELECT <select_list>
FROM A
RIGHT JOIN B
ON A.key=B.key;
#得到表B中去掉表A与表B的交集部分的剩余部分
SELECT <select_list>
FROM A
RIGHT JOIN B
ON A.key=B.key;
WHERE A.key IS NULL;

#全外连接（MySQL不支持）
#得到表A、表B、表A与表B的交集
SELECT <select_list>
FROM A
FULL JOIN B
ON A.key=B.key;
#得到表A和表B去除掉表A与表B的交集的部分
SELECT <select_list>
FROM A
FULL JOIN B
ON A.key=B.key;
WHERE A.key IS NULL OR B.key IS NULL;

6.3 交叉连接

#例：
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

 交叉连接结果是笛卡尔乘积，m*n 行数据

7、子查询

含义：出现在其他语句中的 select 语句，称为子查询或内查询，外面的语句可以是 insert、update、delete、select 等，一般 select 用得多
外部的查询语句，称为主查询或外查询

分类
按子查询出现的位置：
    select 后面：
        仅支持标量子查询
    from 后面：
        支持表子查询
    where 或 having 后面：
        支持标量子查询(单行)、列子查询(多行)、行子查询
    exists 后面(相关子查询)：
        支持表子查询

按结果集的行列数不同：
    标量子查询(结果集只有一行一列)
    列子查询(结果只有一列多行)
    行子查询(结果集有一行多列或多行多列)
    表子查询(结果集一般为多行多列)

特点：放在小括号内，一般放在条件右侧

标量子查询一般搭配单行操作符使用
单行操作符：<,>,>=,<=,=,<>
列子查询一般搭配多行操作符使用
多行操作符：in，any/some，all
子查询的执行优先于主查询的执行

#select 后面(标量子查询)
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.'department_id'= d.'department_id'
)
FROM departments d;

#from 后面(表子查询)将子查询结果充当一张表，要求必须取别名
SELECT ag_dep.*,g.'grade_level'
FROM (
	SELECT AVG(salary),department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

#where 或 having 后面
#标量子查询(单行子查询)
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id = 143
);
#列子查询(多行子查询)
SELECT last_name
FROM employees
WHERE department_id IN(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);
#行子查询
SELECT *
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employ_id), MAX(salary)
	FROM employees
);

#exists 后面，结果：1或0
SELECT department_name
FROM ddepartments
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.'department_id'= e.'department_id'
);

8、分页查询

应用场景：当显示的数据，一页显示不全，需要分页提交 sql 请求

语法：
SELECT 查询列表
FROM 表
[连接类型 JOIN 表2
ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后筛选
ORDER BY 排序字段]
LIMIT offset,size;

offset 可省略，省略则从0开始

offset：要显示条目的起始索引(从0开始)
size：要显示的条目个数

特点：
limit 语句放在查询语句最后
公式：
若要显示的页数 page，每页条目数 size
SELECT 查询列表
FROM 表
LIMIT (page-1)*size, size;

9、联合查询

将多条查询语句的结果合并成一个结果

应用：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

语法：
查询语句1
UNION
查询语句2
UNION
......

特点：
1、要求多条查询语句的列数一致
2、要求多条查询语句的每一列的类型和顺序最好一致
3、union 关键字默认去重，使用 union all 可以包含重复项

#例：
SELECT *
FROM employees
WHERE email LIKE '%a%'
UNION
SELECT *
FROM employees
WHERE department_id>90;