1.数据库概述
1.1 为什么要使用数据库
- 持久化(persistence)：把数据保存到可掉电式存储设备中以供之后使用。大多数情况下，特别是企业级应用，数据持久化意味着将内存中的数据保存到硬盘上加以”固化”，而持久化的实现过程大多通过各种关系数据库来完成。
- 持久化的主要作用是将内存中的数据存储在关系型数据库中，当然也可以存储在磁盘文件、XML数据文件中。
1.2 数据库的相关概念
| DB：数据库（Database） |
|---|
| 即存储数据的“仓库”，其本质是一个文件系统。它保存了一系列有组织的数据。 |
| DBMS：数据库管理系统（Database Management System） |
| 是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制。用户通过数据库管理系统访问数据库中表内的数据。 |
| SQL：结构化查询语言（Structured Query Language） |
| 专门用来与数据库通信的语言。 |
| 数据库管理系统、数据库和表的关系如图所示： |
 |
2.SQL概述
2.1SQL语言在功能上主要分为如下3大类：
-
DDL（Data Definition Languages、数据定义语言），这些语句定义了不同的数据库、表、视图、索引等数据库对象，还可以用来创建、删除、修改数据库和数据表的结构。
- 主要的语句关键字包括
CREATE、DROP、ALTER等。
- 主要的语句关键字包括
-
DML（Data Manipulation Language、数据操作语言），用于添加、删除、更新和查询数据库记录，并检查数据完整性。
- 主要的语句关键字包括
INSERT、DELETE、UPDATE、SELECT等。 - SELECT是SQL语言的基础，最为重要。
- 主要的语句关键字包括
-
DCL（Data Control Language、数据控制语言），用于定义数据库、表、字段、用户的访问权限和安全级别。
- 主要的语句关键字包括
GRANT、REVOKE、COMMIT、ROLLBACK、SAVEPOINT等。
- 主要的语句关键字包括
因为查询语句使用的非常的频繁，所以很多人把查询语句单拎出来一类：DQL（数据查询语言）。
还有单独将
COMMIT、ROLLBACK取出来称为TCL （Transaction Control Language，事务控制语言）。
2.2 基本规则
-
SQL 可以写在一行或者多行。为了提高可读性，各子句分行写，必要时使用缩进
-
每条命令以 ; 或 \g 或 \G 结束
-
关键字不能被缩写也不能分行
-
关于标点符号
- 必须保证所有的()、单引号、双引号是成对结束的
- 必须使用英文状态下的半角输入方式
- 字符串型和日期时间类型的数据可以使用单引号（’ '）表示
- 列的别名，尽量使用双引号（" "），而且不建议省略as
2.3 SQL大小写规范 （建议遵守）
-
MySQL 在 Windows 环境下是大小写不敏感的
-
MySQL 在 Linux 环境下是大小写敏感的
- 数据库名、表名、表的别名、变量名是严格区分大小写的
- 关键字、函数名、列名(或字段名)、列的别名(字段的别名) 是忽略大小写的。
-
推荐采用统一的书写规范：
- 数据库名、表名、表别名、字段名、字段别名等都小写
- SQL 关键字、函数名、绑定变量等都大写
2.4 注 释
可以使用如下格式的注释结构
单行注释：#注释文字(MySQL特有的方式)
单行注释：-- 注释文字(--后面必须包含一个空格。)
多行注释：/* 注释文字 */
2.5数据导入指令
在命令行客户端登录mysql，使用source指令导入
mysql> source d:\mysqldb.sql
mysql -uroot -p
password:***
CREATE DATABASE my_db;
USE my_db;
SOURCE d:my_db.sql;
mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int(6) | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int(6) | YES | MUL | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
3.基本的SELECT语句
3.0 SELECT…
SELECT 1; #没有任何子句
SELECT 9/2; #没有任何子句
3.1 SELECT … FROM
- 语法：
SELECT 标识选择哪些列
FROM 标识从哪个表中选择
- 选择全部列：
SELECT *
FROM departments;
3.2 列的别名
-
重命名一个列
-
便于计算
-
紧跟列名，也可以在列名和别名之间加入关键字AS，别名使用双引号，以便在别名中包含空格或特殊的字符并区分大小写。
-
AS 可以省略
-
建议别名简短，见名知意
-
举例
SELECT last_name AS name, commission_pct comm FROM employees;
3.3 去除重复行
在SELECT语句中使用关键字DISTINCT去除重复行
SELECT DISTINCT department_id
FROM employees;
3.4 空值参与运算
- 所有运算符或列值遇到null值，运算的结果都为null
SELECT employee_id,salary,commission_pct,
12 * salary * (1 + commission_pct) "annual_sal"
FROM employees;
这里你一定要注意，在 MySQL 里面， 空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。
3.5查询常数
我们想对 employees 数据表中的员工姓名进行查询，同时增加一列字段corporation，这个字段固定值为“wedu”，可以这样写：
SELECT 'wedu' as corporation, last_name FROM employees;
3.6 显示表结构
使用DESCRIBE 或 DESC 命令，表示表结构。
DESCRIBE employees;
或
DESC employees;
mysql> desc employees;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| employee_id | int(6) | NO | PRI | 0 | |
| first_name | varchar(20) | YES | | NULL | |
| last_name | varchar(25) | NO | | NULL | |
| email | varchar(25) | NO | UNI | NULL | |
| phone_number | varchar(20) | YES | | NULL | |
| hire_date | date | NO | | NULL | |
| job_id | varchar(10) | NO | MUL | NULL | |
| salary | double(8,2) | YES | | NULL | |
| commission_pct | double(2,2) | YES | | NULL | |
| manager_id | int(6) | YES | MUL | NULL | |
| department_id | int(4) | YES | MUL | NULL | |
+----------------+-------------+------+-----+---------+-------+
11 rows in set (0.00 sec)
其中，各个字段的含义分别解释如下：
- Field：表示字段名称。
- Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的。
- Null：表示该列是否可以存储NULL值。
- Key：表示该列是否已编制索引。PRI表示该列是表主键的一部分；UNI表示该列是UNIQUE索引的一部分；MUL表示在列中某个给定值允许出现多次。
- Default：表示该列是否有默认值，如果有，那么值是多少。
- Extra：表示可以获取的与给定列有关的附加信息，例如AUTO_INCREMENT等。
3.7过滤数据
- 语法：
SELECT 字段1,字段2
FROM 表名
WHERE 过滤条件
- 使用WHERE 子句，将不满足条件的行过滤掉
- WHERE子句紧随 FROM子句
3.8SELECT执行顺序
#方式1：
SELECT ...,....,...
FROM ...,...,....
WHERE 多表的连接条件
AND 不包含组函数的过滤条件
GROUP BY ...,...
HAVING 包含组函数的过滤条件
ORDER BY ... ASC/DESC
LIMIT ...,...
#其中：
#（1）from：从哪些表中筛选
#（2）where：从表中筛选的条件
#（3）group by：分组依据
#（4）having：在统计结果中再次筛选
#（5）order by：排序
#（6）limit：分页
4.运算符
4.1算术运算符
1．加法与减法运算符
mysql> SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5 FROM dual;
+-----+---------+---------+----------+--------------+------------+------------+
| 100 | 100 + 0 | 100 - 0 | 100 + 50 | 100 + 50 -30 | 100 + 35.5 | 100 - 35.5 |
+-----+---------+---------+----------+--------------+------------+------------+
| 100 | 100 | 100 | 150 | 120 | 135.5 | 64.5 |
+-----+---------+---------+----------+--------------+------------+------------+
1 row in set (0.00 sec)
由运算结果可以得出如下结论：
- 一个整数类型的值对整数进行加法和减法操作，结果还是一个整数；
- 一个整数类型的值对浮点数进行加法和减法操作，结果是一个浮点数；
- 加法和减法的优先级相同，进行先加后减操作与进行先减后加操作的结果是一样的；
- 在Java中，+的左右两边如果有字符串，那么表示字符串的拼接。但是在MySQL中+只表示数值相加。如果遇到非数值类型，先尝试转成数值，如果转失败，就按0计算。（补充：MySQL中字符串拼接要使用字符串函数CONCAT()实现）
2．乘法与除法运算符
#计算出员工的年基本工资
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees;
由运算结果可以得出如下结论：
- 一个数乘以整数1和除以整数1后仍得原数；
- 一个数乘以浮点数1和除以浮点数1后变成浮点数，数值与原数相等；
- 一个数除以整数后，不管是否能除尽，结果都为一个浮点数；
- 一个数除以另一个数，除不尽时，结果为一个浮点数，并保留到小数点后4位；
- 乘法和除法的优先级相同，进行先乘后除操作与先除后乘操作，得出的结果相同。
- 在数学运算中，0不能用作除数，在MySQL中，一个数除以0为NULL。
4.2 比较运算符
比较运算符经常被用来作为SELECT查询语句的条件来使用，返回符合条件的结果记录。
1．等号运算符
-
等号运算符（=）判断等号两边的值、字符串或表达式是否相等，如果相等则返回1，不相等则返回0。
-
在使用等号运算符时，遵循如下规则：
- 如果等号两边的值、字符串或表达式都为字符串，则MySQL会按照字符串进行比较，其比较的是每个字符串中字符的ANSI编码是否相等。
- 如果等号两边的值都是整数，则MySQL会按照整数来比较两个值的大小。
- 如果等号两边的值一个是整数，另一个是字符串，则MySQL会将字符串转化为数字进行比较。
- 如果等号两边的值、字符串或表达式中有一个为NULL，则比较结果为NULL。
-
对比：SQL中赋值符号使用 :=
2．安全等于运算符
安全等于运算符（<=>）与等于运算符（=）的作用是相似的，唯一的区别是‘<=>’可以用来对NULL进行判断。在两个操作数均为NULL时，其返回值为1，而不为NULL；当一个操作数为NULL时，其返回值为0，而不为NULL。
3．不等于运算符
不等于运算符（<>和!=）用于判断两边的数字、字符串或者表达式的值是否不相等，如果不相等则返回1，相等则返回0。不等于运算符不能判断NULL值。如果两边的值有任意一个为NULL，或两边都为NULL，则结果为NULL。
SQL语句示例如下：
mysql> SELECT 1 <> 1, 1 != 2, 'a' != 'b', (3+4) <> (2+6), 'a' != NULL, NULL <> NULL;
+--------+--------+------------+----------------+-------------+--------------+
| 1 <> 1 | 1 != 2 | 'a' != 'b' | (3+4) <> (2+6) | 'a' != NULL | NULL <> NULL |
+--------+--------+------------+----------------+-------------+--------------+
| 0 | 1 | 1 | 1 | NULL | NULL |
+--------+--------+------------+----------------+-------------+--------------+
1 row in set (0.00 sec)
此外，还有非符号类型的运算符：
4.3 逻辑运算符
MySQL中支持4种逻辑运算符如下：
4.4 位运算符
位运算符是在二进制数上进行计算的运算符。位运算符会先将操作数变成二进制数，然后进行位运算，最后将计算结果从二进制变回十进制数。
MySQL支持的位运算符如下：
4.5 运算符的优先级
5.排序与分页
5.1 排序规则
- 使用 ORDER BY 子句排序
- ASC（ascend）: 升序
- DESC（descend）:降序
- ORDER BY 子句在SELECT语句的结尾。
5.2 单列排序
SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;
5.3 多列排序
SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;
- 可以使用不在SELECT列表中的列排序。
- 在对多列进行排序的时候，首先排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有值都是唯一的，将不再对第二列进行排序。
5.4 分页实现规则
-
分页原理
所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
-
MySQL中使用 LIMIT 实现分页
-
格式：
LIMIT [位置偏移量,] 行数第一个“位置偏移量”参数指示MySQL从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是0，第二条记录的位置偏移量是1，以此类推）；第二个参数“行数”指示返回的记录条数。
-
举例
--前10条记录：
SELECT * FROM 表名 LIMIT 0,10;
或者
SELECT * FROM 表名 LIMIT 10;
--第11至20条记录：
SELECT * FROM 表名 LIMIT 10,10;
--第21至30条记录：
SELECT * FROM 表名 LIMIT 20,10;
MySQL 8.0中可以使用“LIMIT 3 OFFSET 4”，意思是获取从第5条记录开始后面的3条记录，和“LIMIT 4,3;”返回的结果相同。
- 注意：LIMIT 子句必须放在整个SELECT语句的最后！
- 使用 LIMIT 的好处
约束返回结果的数量可以减少数据表的网络传输量，也可以提升查询效率。如果我们知道返回结果只有 1 条，就可以使用LIMIT 1，告诉 SELECT 语句只需要返回一条记录即可。这样的好处就是 SELECT 不需要扫描完整的表，只需要检索到一条符合条件的记录即可返回。
6.增加和删除
1 DDL:操作表
操作表也就是对表进行增（Create）删（Retrieve）改（Update）查（Delete）。
1.1 查询表
- 查询当前数据库下所有表名称
SHOW TABLES;
- 查询表结构
DESC 表名称;
1.2 创建表
- 创建表
CREATE TABLE 表名 (
字段名1 数据类型1,
字段名2 数据类型2,
…
字段名n 数据类型n
);
注意：最后一行末尾，不能加逗号
知道了创建表的语句，那么我们创建创建如下结构的表
1.3 数据类型
MySQL 支持多种类型，可以分为三类：
-
数值
tinyint : 小整数型，占一个字节 int ： 大整数类型，占四个字节 eg ： age int double ： 浮点类型 使用格式： 字段名 double(总长度,小数点后保留的位数) eg ： score double(5,2) -
日期
date ： 日期值。只包含年月日 eg ：birthday date ： datetime ： 混合日期和时间值。包含年月日时分秒 -
字符串
char ： 定长字符串。 优点：存储性能高 缺点：浪费空间 eg ： name char(10) 如果存储的数据字符个数不足10个，也会占10个的空间 varchar ： 变长字符串。 优点：节约空间 缺点：存储性能底 eg ： name varchar(10) 如果存储的数据字符个数不足10个，那就数据字符个数是几就占几个的空间
1.4 删除表
- 删除表
DROP TABLE 表名;
- 删除表时判断表是否存在
DROP TABLE IF EXISTS 表名;
1.5 修改表
- 修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
-- 将表名student修改为stu
alter table student rename to stu;
- 添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
-- 给stu表添加一列address，该字段类型是varchar(50)
alter table stu add address varchar(50);
- 修改数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
-- 将stu表中的address字段的类型改为 char(50)
alter table stu modify address char(50);
- 修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
-- 将stu表中的address字段名改为 addr，类型改为varchar(50)
alter table stu change address addr varchar(50);
- 删除列
ALTER TABLE 表名 DROP 列名;
-- 将stu表中的addr字段 删除
alter table stu drop addr;
2. DML（Manipulation）
DML主要是对数据进行增（insert）删（delete）改（update）操作。
2.1 添加数据
- 给指定列添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);
- 给全部列添加数据
INSERT INTO 表名 VALUES(值1,值2,…);
- 批量添加数据
INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
2.2 修改数据
- 修改表数据
UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;
注意：
- 修改语句中如果不加条件，则将所有数据都修改！
- 像上面的语句中的中括号，表示在写sql语句中可以省略这部分
-
练习
-
将张三的性别改为女
update stu set sex = '女' where name = '张三';
-
2.3 删除数据
- 删除数据
DELETE FROM 表名 [WHERE 条件] ;
- 练习
-- 删除张三记录
delete from stu where name = '张三';
-- 删除stu表中所有的数据
delete from stu;
7.聚合函数
7.1 聚合函数介绍
- 什么是聚合函数
聚合函数作用于一组数据，并对一组数据返回一个值。
- 聚合函数类型
- AVG()
- SUM()
- MAX()
- MIN()
- **COUNT() **
1.1 AVG和SUM函数
可以对数值型数据使用AVG 和 SUM 函数。
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
1.2 MIN和MAX函数
可以对任意数据类型的数据使用 MIN 和 MAX 函数。
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;
1.3 COUNT函数
- COUNT(*)返回表中记录总数，适用于任意数据类型。
SELECT COUNT(*)
FROM employees
WHERE department_id = 50;
7.2 GROUP BY
可以使用GROUP BY子句将表中的数据分成若干组
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
明确：WHERE一定放在FROM后面
在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
7.3 HAVING
过滤分组：HAVING子句
- 行已经被分组。
- 使用了聚合函数。
- 满足HAVING 子句中条件的分组将被显示。
- HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
- ** 非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数。**如下：
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 8000
GROUP BY department_id;
8.子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL 4.1开始引入。
8.1 子查询的基本使用
-
子查询的基本语法结构：
-
子查询（内查询）在主查询之前一次执行完成。
-
子查询的结果被主查询（外查询）使用 。
-
注意事项
- 子查询要包含在括号内
- 将子查询放在比较条件的右侧
- 单行操作符对应单行子查询，多行操作符对应多行子查询
8.2 子查询的分类
分类方式：
我们按内查询的结果返回一条还是多条记录，将子查询分为单行子查询、多行子查询。
8.2.1 单行子查询
题目：返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees);
HAVING 中的子查询
- 首先执行子查询。
- 向主查询中的HAVING 子句返回结果。
题目：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
8.2.2多行子查询
- 也称为集合比较子查询
- 内查询返回多行
- 使用多行比较操作符
| 操作符 | 含义 |
|---|---|
| IN | 等于列表中的任意一个 |
| ANY | 需要和单行比较操作符一起使用，和子查询返回的某一个值比较 |
| ALL | 需要和单行比较操作符一起使用，和子查询返回的所有值比较 |
| SOME | 实际上是ANY的别名，作用相同，一般常使用ANY |
题目：查询平均工资最低的部门id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL (
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id
)
8.2.3相关子查询
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。
题目：若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同id的员工的employee_id,last_name和其job_id
SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);
36万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
