Hi, guys yeah, it's me again
1. DQL(Data Query Language数据查询语言)查询语句
1.1 排序
# 关键字: ORDER BY字段, 可以将查询出的结果进行排序(排序只是显示方式, 不会影响数据库中数据的顺序)
# 格式: SELECT 字段 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC | DESC];
# 注意: ASC: 升序(数据库默认排序方式), DESC降序
1.2 本章节的测试数据表信息下面[如有雷同, 那是不可能的]
# 创建vip_user表, 字段包括: id, username, gender, tell, address, birthday, consume
CREATE TABLE vip_user(
id INT, # 会员编号
username VARCHAR(32), # 姓名
gender VARCHAR(32), # 性别
tell VARCHAR(32), # 电话
address VARCHAR(32), # 地址
birthday DATE, # 生日
consume DOUBLE # 消费金额
);
# 查询vip_user的表结构
DESC vip_user;
# 批量插入数据
INSERT INTO vip_user
(id, username, gender, tell, address, birthday, consume)
VALUES
(1, '沈万三', '男', '176000001234','江苏苏州东蔡村100号', '2001-02-09', 1000),
(2, '范蠡', '男', '176066688899', '河南南阳淅川县大石桥乡110号','1998-09-05', 90000),
(3, '胡雪岩', '男', '178000001234', '杭州鼓楼','2009-10-19', 8500),
(4, '和珅', '男', '18888888888', '北京西城驴肉胡同888号','2006-01-01', 999999),
(5, '董小姐', '女', '189078781234', '天津xxx号','2010-06-06', 868);
# 查看数据是否插入成功了
SELECT * FROM vip_user;
2. 正式开始
2.1 单利排序
# 什么是单列排序:就是只按某一个字段进行排序
# 格式:
# 例子: 查询消费金额从多到少的用户信息依次进行排序
SELECT *
FROM vip_user
ORDER BY consume DESC;
# 什么是组合排序, 同时对多个字段进行排序, 如果第一个字段相同, 则按照第二个字段排序, 以此类推
# 格式: SELECT 字段 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC | DESC], 字段名 [ASC | DESC];
# 本人偷懒, 不过还是希望您亲自动手操作一下, 我在本次测试数据表中没有插入类似的数据, 就是等待您的亲自动手使用INSERT INTO插入一下
3. 聚合函数
上面的查询都是横向查询, 它们都是根据条件一行一行的进行判断, 而是用聚合函数是纵向查询, 它是最一列的值进行计算, 然后返回一个结果, 聚合函数会忽略控制NULL
| SQL中的聚合函数 | 作用 |
|---|---|
| max(列名) | 求这一列的最大值 |
| min(列名) | 求这一列的最小值 |
| avg(列名) | 求这一列的平均值 |
| count(列名) | 统计这一列有多少条记录 |
| sum(列名) | 对这一列求总和 |
3.1 常用聚合函数的具体使用方式
# 例子1 : 查询vip用户总人数
SELECT COUNT(id)AS 总人数
FROM vip_user;
# 特殊情况: 本次创建的数据表中并没有空值NULL, 如果存在NULL, 例子1统计的记录会不全, 需要使用IFNULL()函数进行操作
# 格式: IFNULL(列名, 默认值)
# 例子2 : 查询vip用户总人数
SELECT COUNT(IFNULL(id, 0))AS 总人数
FROM vip_user;
# 查询表中消费总金额
SELECT SUM(consume) AS 消费总金额
FROM vip_user;
# 查询表中消费的平均值
SELECT AVG(consume) AS 平均消费金额
FROM vip_user;
# 查询表中消费的最大值
SELECT MAX(consume) AS 消费的最大金额
FROM vip_user;
# 查询表中消费的最小值
SELECT MIN(consume) AS 消费的最小金额
FROM vip_user;
4. 分组
分组查询是指使用GROUP BY 语句对查询信息进行分组, 相同数据作为一组
格式: SELECT 字段1, 字段2...FROM 表名GROUP BY 分组字段 [HAVING 条件];
一般分组是和聚合函数一起使用的
4.1 实际操作
# 查询女性会员中和男性会员中消费的最小金额分别是谁
SELECT username AS 姓名, MIN(consume) AS 消费金额
FROM vip_user
GROUP BY gender;
4.2 having 和 where 的区别
| 子句 | 作用 |
|---|---|
| where子句 | 1) 对查询的结果进行分组前, 将不符合where条件的行去掉, 即在分组之前过滤数据, 即先过滤在分组 2) where后面不可以使用聚合函数 |
| having子句 | 1)having 子句的作用是筛选满足条件的组, 即在分组之后过滤数据, 即选分组在过滤, 2)having后面可以使用聚合函数 |
5. limit语句
5.1 limit语句概述
- LIMIT是限制的意思, 所以LIMIT的作用就是限制查询记录的条数
- 格式: LIMIT offset, length; (offset: 起始行数, 从0开始计算, 如果省略, 默认就是0, length: 返回的行数)
- SQL查询中的关键字顺序如下:
SELECT * | 字段列表 [AS 别名]
FROM 表名
[WHERE 子句]
[GROUP BY 子句]
[HAVING 子句]
[ORDER BY 子句]
[LIMIT 子句];
# 例子: 分页显示每页显示2条vip用户数据
SELECT *
FROM vip_user
LIMIT 0, 2;
6. 数据库备份和还原
- 备份的应用场景: 在服务器进行数据传输、数据存储和数据交换， 就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时， 如果没有采取数据备份和数据恢复手段与措施， 就会导致数据的丢失， 造成的损失是无法弥补与估量的。
- 备份格式： DOS下， 未登录的时候，这是一个可执行文件exe，在bin文件夹 【mysqldump -u username -p password database_name > 文件路径】
- 还原格式： mysql中的命令， 需要登录后才可以操作【第一步： USE database_name】【第二步：SOURCE 导入文件的路径】
- 图形化备份和导出操作， 网上有很多的大牛给出了不同的图形化界面备份、导出方式， 可以自行查找， 本人比较懒， 比较喜欢使用命令行的格式， 不喜勿喷
7. 数据库的约束【这可是比较重的哟， 如果你要问有多重呢， 有还海神三叉戟那么重】
7.1 约束的作用
- 对表中的数据进行限制， 保证数据的正确性、有效性、完整性。一个表如果添加了一个约束， 不争取的数据将无法插入到数据表中， 约束在创建表的时候添加比较合适，
- 例如： 身份证对于我们很重要， 当你在银行注册银行卡的时候为了确保你的银行卡是属于你， 需要你使用身份证扫面， 确定是你， 这就是将身份证信息与银行卡绑定， 但是加入不绑定得话， 那么到时候丢失了银行卡， 想找回都难， 这就是约束。
7.2 约束的种类
| 约束名 | 约束关键字 |
|---|---|
| 主键 | PRIMARY KEY |
| 唯一 | UNIQUE |
| 非空 | NOT NULL |
| 外键 | FOREIGN KEY |
| 检查约束 | CHECK 注意: MySQL不支持的哟 |
7.2.1 主键约束
- 哪个字段应该作为表的主键呢?
- 通常不用业务字段作为主键, 单独给每张表设计一个id的字段, 把id作为主键, 主键是给数据库和程序使用的, 不是给最终的客户使用的, 所以主键有没有含义没有关系, 只要不重复, 非空就行
- 比如: 身份证、学号不建议做成主键
- 主键关键字： primary key
- 主键的特点：
- 非空 ： not null
- 唯一
# 格式： 字段名 字段类型 PRIMARY KEY；
# 例子1： 创建一个新的user表， 字段为id、name、 password【指定id为主键】
CREATE TABLE USER(
id INT PRIMARY KEY NOT NULL,
NAME VARCHAR(32),
PASSWORD VARCHAR(32)
);
# 例子2： 将vip_user表中的id字段设置为主键
ALTER TABLE vip_user ADD PRIMARY KEY(id);
# 例子3: 删除例子1中的主键
ALTER TABLE USER DROP PRIMARY KEY;
- 主键自增： 因为开发中数据表的数据可能会很多，要是我们自己手动添加主键的值， 很费时费力， 而且可能会出错， 因为使用[AUTO_INCREMENT] 表示自动增长（字段类型必须是整数类型）
# id为主键自增
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
- 修改自增长的默认值起始值
- 默认的AUTO_INCREMENT的开始值是1， 如果希望修改起始值， 请使用下列SQL语法
# 创造表时指定起始值
CREATE TABLE 表名（
列名 INT PRIMARY KEY AUTO_INCREMENT
）AUTO_INCREMENT=起始值;
7.2.2 唯一约束
- 什么是唯一约束: 表中某一列不能出现重复的值
- 基本格式: 字段名 字段类型 UNIQUE;
# 例子: 创建学生表student, 包含字段(id, name), name这一列设置唯一约束, 不能出现同名的学生
CREATE TABLE student(
id INT,
NAME VARCHAR(20) UNIQUE
);
7.2.3 非空约束
- 什么事非空约束, 某一列不能为null
- 基本格式: 字段名 字段类型 NOT NULL;
# 例子: 创建学生表student2, 包含字段(id, name), name不能为NULL
CREATE TABLE student2(
id INT,
NAME VARCHAR(20) NOT NULL
);
7.2.4 默认值
- 什么事默认值
- 基本格式: 字段名 字段类型 DEFAULT 默认值
# 例子: 创建学生表student3, 包含字段(id, name, addr), addr默认值为'中国'
CREATE TABLE student3(
id INT,
NAME VARCHAR(20) NOT NULL,
addr VARCHAR(20) DEFAULT '中国'
);
