MySQL的初学者之路

第一章 数据库概述

1.1、数据库的好处

将数据持久化到本地
提供结构化查询功能

1.2、数据库的常见概念

DB：数据库，存储数据的仓库
DBMS：数据库管理系统，又称为数据库软件或者数据库产品，用于创建和管理数据库，常见的有MySQL、Oracle、SQL Server
DBS：数据库系统，数据库系统是一个通称，包括数据库、数据库管理系统、数据库管理人员等，是最大的范畴
SQL：结构化查询语言，用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据库软件通用的语言

1.3、数据库的存储特点

数据存放到表中，然后表再放到库中
一个库中可以有多张表，每张表具有唯一的表名用来标识自己
表中有一个或多个列，列又称为“字段”，相当于Java中“属性”
表中的每一行数据，相当于Java中“对象”

1.4、数据库的常见分类

关系型数据库：MySQL、Oracle、DB2、SQL Server
非关系型数据库：
键值存储数据库：Redis、Memcached、MemcacheDB
列存储数据库：HBase、Cassandra
面向文档的数据库：MongDB、CouchDB
图形数据库：Neo4J

1.5、SQL语言的分类

DQL：数据查询语言：select、from、where
DML：数据操作语言：insert、update、delete
DDL：数据定义语言：create、alter、drop、truncate
DCL：数据控制语言：grant、revoke
TCL：事务控制语言：commit、rollback

第二章 DQL语言

2.1、基础查询

一、语法

SELECT 查询列表 FROM 表名;

二、特点
1.查询列表可以是字段、常量、函数、表达式
2.查询结果是一个虚拟表
三、示例
1、查询单个字段

SELECT 字段名 FROM 表名;

2、查询多个字段

SELECT 字段名,字段名 FROM 表名;

3、查询所有字段

SELECT * FROM 表名;

4、查询常量

SELECT 常量值;

注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要
5、查询函数

SELECT 函数名(实参列表);

6、查询表达式

SELECT 100/25;

7、起别名

1、 SELECT 字段名 AS "别名" FROM 表名;

注意：别名可以使用单引号、双引号引起来，当只有一个单词时，可以省略引号，当有多个单词且有空格或特殊符号时，不能省略，AS可以省略。

8、去重复

SELECT DISTINCT 字段名 FROM 表名;

9、做加法

SELECT 数值+数值; 直接运算
SELECT 字符+数值; 首先先将字符转换为整数，如果转换成功，则继续运算，如果转换失败，则默认为0，然后继续运算
SELECT NULL+数值; NULL和任何数值参与运算结果都是NULL

10、【补充】ifnull函数
功能：判断某字段或表达式是否为null，如果为null，返回指定的值，否则返回原本的值

SELECT IFNULL(字段名, 指定值) FROM 表名;

11、【补充】isnull函数
功能：判断某字段或表达式是否为null，如果是null，则返回1，否则返回0

SELECT ISNULL(字段名) FROM 表名;

3.2、条件查询

一、语法

SELECT 查询列表 FROM 表名 WHERE 筛选条件;

二、分类
条件运算符：>、>=、<、<=、=、<=>、!=、<>
逻辑运算符：and、or、not
模糊运算符：
like：%任意多个字符、_任意单个字符，如果有特殊字符，需要使用escape转义
between and
not between and
in
is null
is not null
三、演示
1、查询工资>12000的员工信息

SELECT 
  * 
FROM
  employees 
WHERE salary > 12000 ;

2、查询工资>=14000的员工信息

SELECT 
  * 
FROM
  employees 
WHERE salary >= 14000 ;

3、查询工资<12000的员工信息

SELECT 
  * 
FROM
  employees 
WHERE salary < 12000 ;

4、查询工资<=14000的员工信息

SELECT 
  * 
FROM
  employees 
WHERE salary <= 14000 ;

5、查询员工编号=100的员工信息

SELECT 
  * 
FROM
  employees 
WHERE employee_id = 100 ;

6、查询员工编号<=>100的员工信息

SELECT 
  * 
FROM
  employees 
WHERE employee_id <=> 100 ;

注意：=只能判断普通类型的数值，而<=>不仅可以判断普通类型的数值还可以判断NULL

7、查询员工编号!=100的员工信息

SELECT 
  * 
FROM
  employees 
WHERE employee_id != 100 ;

8、查询员工编号<>100的员工信息

SELECT 
  * 
FROM
  employees 
WHERE employee_id <> 100 ;

注意：!=和<>都是判断不等于的意思，但是MySQL推荐使用<>

9、查询工资>12000&&工资<18000的员工信息

SELECT 
  * 
FROM
  employees 
WHERE salary > 12000 AND salary < 18000 ;

10、查询工资<=12000||工资>=18000的员工信息

SELECT 
  * 
FROM
  employees 
WHERE salary <= 12000 OR salary >= 18000 ;

11、查询工资<=12000||工资>=18000的员工信息

SELECT 
  * 
FROM
  employees 
WHERE NOT (salary > 12000 AND salary < 18000) ;

12、查询员工名中第一个字符为B、第四个字符为d的员工信息

SELECT 
  *
FROM
  employees 
WHERE last_name LIKE 'B__d%' ;

13、查询员工编号在100到120之间的员工信息

SELECT 
  * 
FROM
  employees 
WHERE employee_id BETWEEN 100 AND 120 ;

14、查询员工编号不在100到120之间的员工信息

SELECT 
  * 
FROM
  employees 
WHERE employee_id NOT BETWEEN 100 AND 120 ;

15、查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号

SELECT 
  last_name,
  job_id 
FROM
  employees 
WHERE job_id IN ('IT_PROT', 'AD_VP', 'AD_PRES') ;

注意：in列表的值类型必须一致或兼容，in列表中不支持通配符%和_
16、查询没有奖金的员工名和奖金率

SELECT 
  last_name,
  commission_pct 
FROM
  employees 
WHERE commission_pct IS NULL ;

17、查询有奖金的员工名和奖金率

SELECT 
  last_name,
  commission_pct 
FROM
  employees 
WHERE commission_pct IS NOT NULL ;

注意：=、!=不能用来判断NULL、而<=>、is null 、 is not null可以用来判断NULL，但注意<=>也可以判断普通类型的数值

3.3、排序查询

一、语法

SELECT 
  查询列表 
FROM
  表 
【WHERE 筛选条件】
ORDER BY 排序列表 【asc | desc】 ;

二、注意

排序列表可以是单个字段、多个字段、别名、函数、表达式
asc代表升序，desc代表降序，如果不写，默认是asc
order by的位置一般放在查询语句的最后（除limit语句之外）
三、示例

1、按单个字段排序：查询员工信息，要求按工资降序

SELECT 
  * 
FROM
  employees 
ORDER BY salary DESC ;

2、按多个字段查询：查询员工信息，要求先按工资降序，再按员工编号升序

SELECT 
  * 
FROM
  employees 
ORDER BY salary DESC, employee_id ASC ;

3、按别名排序查询：查询员工信息，要求按员工年薪升序

SELECT 
  *,
  salary * 12 * (1+ IFNULL(commission_pct, 0)) 年薪 
FROM
  employees 
ORDER BY 年薪 ASC ;

4、按函数排序查询：查询员工信息，要求按员工名字的长度降序

SELECT 
  LENGTH(last_name),
  last_name 
FROM
  employees 
ORDER BY LENGTH(last_name) DESC ;

5、按表达式排序：查询员工信息，要求按员工年薪升序

SELECT 
  *,
  salary * 12 * (1+ IFNULL(commission_pct, 0)) 
FROM
  employees 
ORDER BY salary * 12 * (1+ IFNULL(commission_pct, 0)) ASC ;
1

3.4、单行函数

一、语法

SELECT 函数名(实参列表) 【FROM 表】;

二、分类

字符函数

concat：连接字符
substr：截取子串
replace：替换字符
upper：变大写
lower：变小写
lpad：左填充
rpad：右填充
length：获取字节长度
trim：去除前后空格
instr：获取子串第一次出现的索引

注意MySQL中的索引是从1开始的
数学函数

round：四舍五入
ceil：向上取整
floor：向下取整
mod：取模运算（a-a/b*b）
truncate：保留小数的位数，不进行四舍五入
rand：获取随机数，返回0-1之间的小数

日期函数

now：返回当前日期+时间
curdate：返回当前日期
curtime：返回当前时间
year：返回年
month：返回月
day：返回日
hour：小时
minute：分钟
second：秒
monthname：以英文形式返回月
datediff：返回两个日期相差的天数
date_format：将日期转换成字符
str_to_date：将字符转换成日期
格式符：
%Y：四位的年份
%y：二位的年份
%m：二位的月份（01，02，...，12）
%c：一位的月份（1，2，...，12）
%d：日（01，02，...，31）
%H：小时（24小时制）
%h：小时（12小时制）
%i：分钟（00，01，02，...，59）
%s：秒（00，01，02，...，59）    

控制函数

if：判断函数
case：分支函数

1、IF(条件表达式，表达式1，表达式2)：如果条件表达式成立，返回表达式1，否则返回表达式2

2、case的格式一：

 CASE 变量或字段或表达式 
      WHEN 常量1 THEN 值1 
      WHEN 常量2 THEN 值2
      ...
      ELSE 值n 
    END ;

3、case的格式二：

CASE
WHEN 条件1 THEN 值1
WHEN 条件2 THEN 值2
…
ELSE 值n
END

其它函数

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

三、演示

1、concat

SELECT CONCAT('Hello',' ','World') AS out_put;

2、substr

#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7)  AS out_put;

#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) AS out_put;

3、replace

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

4、upper

SELECT UPPER('john') AS out_put;

5、lower

SELECT LOWER('john') AS out_put;

6、lpad

SELECT LPAD('殷素素',10,'*') AS out_put;

7、rpad

SELECT RPAD('殷素素',10,'*') AS out_put;

8、length

SELECT LENGTH('john') AS out_put;

9、trim

#删除指定字符的左右空格
SELECT LENGTH(TRIM('    张翠山    ')) AS out_put;
#删除指定字符的指定字符
SELECT TRIM('aaa' FROM 'aaaaaaaaa张翠山aaaaaaaaa')  AS out_put;

10、instr

SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;

注意：返回子串第一次出现的索引，如果找不到返回0

11、round

#默认四舍五入
SELECT ROUND(-1.55) AS out_put;

#指定小数位数
SELECT ROUND(1.567,2) AS out_put;

注意：四舍五入和符号无关

12、ceil

SELECT CEIL(-1.02) AS out_put;

注意：向上取整，返回>=该参数的最小整数

13、floor

SELECT FLOOR(-9.99) AS out_put;

注意：向下取整，返回<=该参数的最大整数

14、mod

SELECT MOD(10,3) AS out_put;

15、truncate

SELECT TRUNCATE(1.69999,1) AS out_put;

16、rand

SELECT RAND() AS out_put;

17、now

SELECT NOW() AS out_put;

18、curdate

SELECT CURDATE() AS out_put;

19、curtime

SELECT CURTIME() AS out_put;

20、year

SELECT YEAR(NOW()) 年;

21、month

SELECT MONTH(NOW()) 月;

22、day

SELECT DAY(NOW()) 日;

23、hour

SELECT HOUR(NOW()) 时;

24、minute

SELECT MINUTE(NOW()) 分;

25、second

SELECT SECOND(NOW()) 秒;

26、monthname

SELECT MONTHNAME(NOW()) 月名;

27、datediff

SELECT DATEDIFF('1995-2-7','1995-2-6') AS out_put;

28、date_format

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS out_put;

29、str_to_date

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;

30、if

SELECT IF(10 < 5, '大', '小') AS out_put;

31、case的格式一

/*
案例：查询员工的工资，要求
	部门号=30，显示的工资为1.1倍
	部门号=40，显示的工资为1.2倍
	部门号=50，显示的工资为1.3倍
	其它部门，显示的工资为原工资
*/

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
  employees ;

32、case的格式二

/*
案例：查询员工的工资情况
    如果工资>20000，显示A级别
    如果工资>15000，显示B级别
    如果工资>10000，显示C级别
    否则，显示D级别
*/

SELECT 
  salary,
  CASE
    WHEN salary > 20000 THEN 'A' 
    WHEN salary > 15000 THEN 'B' 
    WHEN salary > 10000 THEN 'C' 
    ELSE 'D' 
  END AS 工资级别 
FROM
  employees ;

33、version

SELECT VERSION();

34、database

SELECT DATABASE();

35、user

SELECT USER();

36、password(‘字符’)

SELECT PASSWORD('123456');

37、md5(‘字符’)

SELECT MD5('123456');

3.5、分组函数

一、语法

SELECT 函数名(实参列表) 【FROM 表】;

二、分类

sum：求和

avg：平均值

max：最大值

min：最小值

count：计算个数

sum、avg一般用于处理数值型，max、min、count可以处理任何类型

以上分组函数都忽略null值

可以和distinct搭配实现去重的运算：select sum(distinct 字段) from 表;

一般使用count(*)用作统计行数

和分组函数一同查询的字段要求是group by后的字段

三、演示

1、sum

SELECT SUM(salary) FROM employees;

2、avg

SELECT AVG(salary) FROM employees;

3、max

SELECT MAX(salary) FROM employees;

4、min

SELECT MIN(salary) FROM employees;

5、count

SELECT COUNT(salary) FROM employees;

3.6、分组查询

一、语法

SELECT 
  查询列表 
FROM
  表 
【where 筛选条件】 
GROUP BY 分组的字段 
【having 分组后的筛选】
【order BY 排序的字段】 ;

二、特点

1、和分组函数一同查询的字段必须是group by后出现的字段

2、筛选分为两类：分组前筛选和分组后筛选
针对的表 语句位置 连接的关键字
分组前筛选 分组前的原始表 group by前 where
分组后筛选 分组后的结果集 group by后 having

3、分组可以按单个字段也可以按多个字段

4、分组可以搭配着排序使用
三、演示

1、查询每个工种的员工平均工资

SELECT 
  AVG(salary),
  job_id 
FROM
  employees 
GROUP BY job_id ;

2、查询每个位置的员工部门个数

SELECT 
  COUNT(*),
  location_id 
FROM
  departments 
GROUP BY location_id ;

3、查询有奖金的每个领导手下员工的平均工资

SELECT 
  AVG(salary),
  manager_id 
FROM
  employees 
WHERE commission_pct IS NOT NULL 
GROUP BY manager_id ;

4、查询哪个部门的员工个数>5

SELECT 
  COUNT(*),
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING COUNT(*) > 5 ;

5、查询每个工种有奖金的员工的最高工资>6000的最高工资和公众编号，按最高工资升序

SELECT 
  MAX(salary) m,
  job_id
FROM
  employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING m > 6000 
ORDER BY m ;

6、查询每个工种每个部门的最低工资并按最低工资降序

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