MySQL从入门到精通

第1章 数据库概述

数据库优点

1. 持久化数据
2. 方便检索
3. 存储大量数据
4. 共享、安全
5. 通过组合分析，获取新的数据

数据库的常见概念

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

数据库的存储特点

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

数据库的常见分类

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

第2章 MySQL

MySQL的启动与退出

1. 图形化界面：【此电脑】-右键-【管理】–【服务和应用程序】–【服务】–【MySQL】-右键-【启动】/【停止】

2. DOS命令行：以管理员身份运行
   输入

net start MySQL
net stop MySQL

MySQL的登录与退出

DOS命令行：
mysql -h主机名 -P端口号 -u用户名 -p密码
本机可省略 -h
端口为3306可省略 -P

mysql -uroot -p****

exit

SQLyog可视化数据库管理

第3章 SQL语句

SQL概述

SQL(Structured Query Language)，结构化查询语言，对关系型数据库的操作语言，可以应用到所有关系型数据库中

MySQL中的LIMIT语句是它独有的方言

语法要求

• SQL语句可以单行或多行书写，以分号结尾
• 可以使用空格和缩进
• 关键字不区分大小写，建议使用大写

分类

• DDL（Data Definition Language）：数据定义语言，用来以定义数据库对象：库、表、列等；
  create/drop/alter

• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）
  Insert/updata/delete

• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别

• **DQL（Date Query Language）：**数据查询语言，用来查询记录（数据）
  select

DQL

基础查询

语法

SELECT 查询列表 FROM 表名;

特点

• 查询列表可以是字段、常量、函数、表达式，多个部分以逗号隔开
• 查询结果是一个虚拟表
• 先执行from，再执行select

示例

1、查询单个字段

SELECT `字段名` FROM `表名`;

最好加入着重号`，防止与某些关键字重复

2、查询多个字段

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

选中所有字段名，按F12即可对齐格式

3、查询所有字段

SELECT * FROM 表名;

4、查询常量

SELECT 常量值;

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

5、查询函数

SELECT 函数名(实参列表);

• contect();将两个表组合

6、查询表达式

SELECT 100%25;

7、起别名

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

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

8、去重复

SELECT DISTINCT 字段名 FROM 表名;

9、做加法（mySQL + 只作为加法运算）

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

10、查看表的结构

DESC 表名;

11、ifnull函数

功能：判断某字段或表达式是否为null，如果为null，返回指定的值，否则返回原本的值

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

12、isnull函数

功能：判断某字段或表达式是否为null，如果是null，则返回1，否则返回0

SELECT ISNULL(字段名) FROM 表名;

条件查询

一、语法

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

先执行FROM
再WHRER
再SELECT

二、分类

1. 条件运算符：>、>=、<、<=、判断普通类型的数值=、<=>安全等于，还可以判断null、不等于：<>、不建议使用!=

#1、查询工资>=12000的员工信息

SELECT 
  * 
FROM
  employees 
WHERE salary >= 12000 ;

2. 逻辑运算符：and、or、not ， 不建议使用&& || !

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

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

3. 模糊运算符：

   • like：和通配符搭配使用（类似正则表达式）：%任意多个字符、_任意单个字符，如果有特殊字符，需要使用转义字符\

   • between XX and YY/ not between XX and YY ：判断某个字段的值是否介于XX和YY之间
     XX要小于等于YY

   • in(字段1，字段2…)，查询某字段的值是否属于指定的列表内
     not in()
     注意：in列表的值类型必须一致或兼容，in列表中不支持通配符%和_

   • 字段 is null/is not null：查询某字段的值是否为空

#1.查询姓名中包含第二个字符为_的员工信息
SELECT *
FROM employees
WHERE last_name LIKE '_$_%' ESCAPE '$';
#代表$成为一个转义字符

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

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

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

排序查询

一、语法

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

查出数据后再排序

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

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

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

单行函数

一、语法

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：返回当前时间

SELECT YEAR(NOW());

• year：返回年
• month：返回月
• day：返回日
• hour：小时
• minute：分钟
• second：秒
• monthname：以英文形式返回月

• datediff：返回两个日期相差的天数

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

• date_format：将日期转换成字符

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

• str_to_date：将字符转换成日期

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

格式符：

%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：分支函数

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

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

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

/*
案例：查询员工的工资，要求
	部门号=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 ;

case的格式二：
    CASE
      WHEN 条件1 THEN 值1 
      WHEN 条件2 THEN 值2
      ...
      ELSE 值n 
    END

/*
案例：查询员工的工资情况
    如果工资>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 ;

其它函数

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

分组函数（聚合函数）

一、语法

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

二、分类

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

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

2、以上分组函数都忽略null值

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

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

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

SELECT SUM(salary) FROM employees;

分组查询

一、语法

SELECT 
  查询列表 
FROM
  表 
【where 筛选条件】 
GROUP BY 分组的字段（也叫聚合字段） 
【having 分组后的筛选】
【order BY 排序的字段】;
#【】表示选择填入

二、特点

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

2、筛选分为两类：分组前筛选和分组后筛选
            	针对的表				 语句位置			   连接的关键字
	分组前筛选	 分组前的原始表			group by前			where
	分组后筛选	 分组后的各个分组		group by后			having
	
3、分组可以按单个字段也可以按多个字段

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

#查询每个工种有奖金的员工的最高工资>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 ;

连接查询

一、含义

连接查询又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

二、注意

笛卡尔乘积现象：表1 有m行，表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

三、分类

按年代分类

• sql92标准：支持内连接
• sql99标准：支持内连接、部分外连接（左外、右外）、交叉连接

按功能分类

• 内连接
• 等值连接
• 非等值连接
• 自连接
• 外连接
• 左外连接
• 右外连接
• 全外连接
• 交叉连接

四、sql92标准演示

1、sql92标准：等值连接

#查询员工名和对应的部门名

SELECT 
  last_name,
  department_name 
FROM
  employees,
  departments 
WHERE employees.`department_id` = departments.`department_id` ;

2、sql92标准：非等值连接

#查询员工的工资和工资级别

SELECT 
  salary,
  grade_level 
FROM
  employees e,
  job_grades g 
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal` ;

3、sql92标准：自连接

#查询员工名和它对应上级的名称

SELECT 
  e.employee_id,
  e.last_name,
  m.employee_id,
  m.last_name 
FROM
  employees e,
  employees m 
WHERE e.`manager_id` = m.`employee_id` ;

五、sql99标准
一、语法

SELECT 
  查询列表 
FROM 表1 别名1 
【连接类型】 JOIN 表2 别名2 ON 连接条件 
【where 分组前筛选条件】
【group BY 分组列表】
【having 分组后筛选条件】
【order BY 排序列表】 ;

二、连接类型

• 内连接：inner
• 外连接
  • 左外连接：left 【outer】（左边的是主表）
  • 右外连接：right 【outer】（右边的是主表）
  • 全外连接：full 【outer】（两边都是主表，但是MySQL不支持全外连接、Oracle支持）
• 交叉连接：cross（交叉连接其实是用sql99语法实现笛卡尔乘积）

三、演示

1、内连接：等值连接

#查询员工名和对应的部门名

SELECT 
  last_name,
  department_name 
FROM
  departments d 
INNER JOIN employees e ON e.`department_id` = d.`department_id` ;

2、内连接：非等值连接

#查询员工的工资和工资级别

SELECT 
  salary,
  grade_level 
FROM
  employees e 
INNER JOIN job_grades g ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal` ;

3、内连接：自连接

#查询员工名和它对应上级的名称

SELECT 
  e.last_name,
  m.last_name 
FROM
  employees e 
INNER JOIN employees m ON e.`manager_id` = m.`employee_id` ;

4、外连接：左外连接

#查询哪个部门没有员工

SELECT 
  d.`department_name`
FROM
  departments d 
LEFT OUTER JOIN employees e ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;

5、外连接：右外连接

#查询哪个部门没有员工

SELECT 
  d.`department_name`
FROM
  employees e 
RIGHT OUTER JOIN departments d ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;

6、交叉连接

#使用交叉连接进行笛卡尔乘积查询

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

子查询

一、含义

嵌套在其它语句内部的select语句称为子查询或内查询，外面的语句可以是insert、delete、update、select等，一般select作为外面语句较多，外面如果为select语句，则此语句称为外查询或主查询

二、分类

• 按出现的位置划分
  • select后面：标量子查询
  • from后面：表子查询
  • where或having后面
    - 标量子查询
    - 列子查询
    - 行子查询
  • exists后面
  • 标量子查询
  • 列子查询
  • 行子查询
  • 表子查
• 按结果集行列数划分
• 标量子查询（单行子查询）：结果集为一行一列
• 列子查询（多行子查询）：结果集为多行一列
• 行子查询：结果集为多行多列
• 表子查询：结果集为多行多列

三、特点

• 子查询放在小括号内
• 子查询一般放在条件的右侧
• 子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
• 标量子查询，一般搭配着单行操作符使用：>、>=、<、<=、!=、<>、=、<=>
• 列子查询，一般搭配着多行操作符使用：in、not in、any、some、all、exits

四、演示

1、select后面

#查询每个部门的员工个数

SELECT 
  d.*, (
  SELECT 
    COUNT(*) 
  FROM
    employees e 
  WHERE e.department_id = d.`department_id`
) 个数 
FROM
  departments d ;

2、from后面

#查询每个部门平均工资的工资等级

SELECT 
  ag_dep.*,
  g.`grade_level` 
FROM (
  SELECT 
    AVG(salary) ag,
    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 ;

3、where或having后面

标量子查询：查询最低工资的员工姓名和工资

SELECT 
  last_name,
  salary 
FROM
  employees 
WHERE salary = (
	SELECT MIN(salary) FROM employees
) ;

列子查询：

#查询所有是领导的员工姓名

SELECT 
  last_name 
FROM
  employees 
WHERE employee_id IN (
	SELECT DISTINCT manager_id FROM employees
) ;

#返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id以及salary

SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary < ANY (
  SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG' ;

#返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id以及salary

SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary < ALL (
  SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG'
) AND job_id <> 'IT_PROG' ;

行子查询：查询员工编号最小并且工资最高的员工信息

SELECT 
  * 
FROM
  employees 
WHERE (employee_id, salary) = (
	SELECT MIN(employee_id), MAX(salary) FROM employees
) ;

4、exists后面

#查询有员工的部门名

SELECT 
  department_name 
FROM
  departments d 
WHERE EXISTS (
	SELECT * FROM employees e
	WHERE e.`department_id` = d.`department_id`
) ;

分页查询

一、语法

SELECT 
  查询列表 
FROM
  表1 别名1
【连接类型】 JOIN 表2 别名2 ON 连接条件 
【WHERE 分组前的筛选】
【GROUP BY 分组字段】 
【HAVING 分组后的筛选 】
【ORDER BY 排序字段 ASC|DESC】
LIMIT 【offset, 】size ;

二、特点

• limit语句放在查询语句的最后
• offset代表起始索引，起始索引从0开始，size代表条目个数
• 分页语句：select 查询列表 from 表 limit (page-1)*size,size;

三、演示

#查询前五条员工信息

SELECT * FROM  employees LIMIT 0,5;

联合查询

一、语法

查询语句1
union 【all】
查询语句2
union 【all】
...

二、特点

• 要查询的结果来自于多个表且多个表没有直接的连接关系，但查询的信息一致时，可以使用联合查询
• 要求多条查询语句的查询列数是一致的
• 要求多条查询语句的查询的每一列的类型和顺序最好一致
• union关键字默认去重，如果使用union all可以包含重复项
  三、演示

#查询中国用户中男性的信息以及外国用户中年男性的用户信息

SELECT id,cname FROM t_ca WHERE csex='男'
UNION ALL
SELECT t_id,tname FROM t_ua WHERE tGender='male';

DML

插入语句

一、语法

#方式一：
INSERT INTO 表名(字段名,...) VALUES(值,...);

#方式二：
INSERT INTO 表名 SET 字段名=值,字段名=值,...;

二、特点

• 要求值的类型和字段的类型要一致或兼容
• 字段的个数和顺序不一定与原始表中的字段个数和顺序一致，但必须保证值和字段一一对应
• 假如表中有可以为null的字段，注意可以通过以下两种方式插入null值：①字段和值都省略、②字段写上，值使用null
• 字段和值的个数必须一致
• 字段名可以省略，默认所有列
• 方式一支持一次插入多行，语法如下：INSERT INTO 表名【(字段名,…)】 VALUES(值,…),(值,…),…;
• 方式一支持子查询，语法如下：INSERT INTO 表名 查询语句;

三、演示

1、方式一：插入数据

INSERT INTO beauty(id,name,sex,borndate,phone,photo,boyfriend_id) 
			VALUES(15,'唐艺昕','女','1997-12-05','15633029014',NULL,2);

2、方式二：插入数据

INSERT INTO beauty SET 
    id = 19,name = '张倩倩',
    sex = '女',
    borndate = '1997-12-05',
    phone = '15633029014',
    photo = NULL,
    boyfriend_id = 3 ;