--- theme: smartblue highlight: androidstudio --- **这是我参与8月更文挑战的第1天，活动详情查看：[8月更文挑战](https://juejin.cn/post/6987962113788493831 "https://juejin.cn/post/6987962113788493831")** # 第一章 MySQL概述 ## 为什么前端要学数据库最近有个很火的词「**新生代农民工**」.
新生代农民工中有这么一个特殊的群体「**前端工程师**」,在没有正名之前, 它也叫做「**前端切图仔**」.
他们地位低下, 但是受教育程度高, 职业期望高, 工作耐受力高, 物质精神享受极低, 码农的边缘散工.
直到有一天, 大地春暖花开. 前端革命性的提高了地位...
但是, 这个时候又面临了另一个新的问题「内卷」.
庞大的前端群体不甘心继续受「压迫」, 他们开始了卷.
今天, 给大家带来一个内卷神器 ---「**数据库**」.
前期的前端工程师会做业务就可以了, 职业深挖之后却是前后端都要会的, 这也是必学数据库的原因.
「**你不学, 别人就学了**」.
「**已所不欲勿施于人**」.
后端都在学`vue`了, 你还在指着自家的一亩三分地❓ ## 什么是数据库数据库就是拿来存储数据的容器.
市场上的数据库有很多, 包括关系型数据库Oracle、mySQL、SQL server, 以及文档型数据库mongoDB等.
我们不讲数据库的具体原理, 这里只会去讲用法. ## 本系列文档你会收获什么? 首先, 只要你认真跟着敲下来, MySQL的大部分用法你都可以学会.
同时你会学到:
- sql中的数据类型 - 如何用sql语句去操作数据 - 视图是怎么使用的 - 如何进行sql编程 - 存储过程的使用 - 数据表创建的范式 - 建表的一些索引和约束 - 如何使用事务 - mySQL中的锁 - orm的使用 - 在node中使用mysql - ... 废话不多说, 开始吧! # 第二章 MySQL数据类型数据库作为存储数据的重要手段, 对数据表的列的数据类型进行定义至关重要.
MySQL提供了很多数据类型来对不同的数据进行区分. 在实际操作中, 定义合适的数据类型也能够提高数据库性能.
MySQL的数据类型主要分为一下几种: - 数值类型 - 日期和时间类型 - 字符串类型 ## 一、数值类型 **整型** | 类型 | 字节大小 | 有符号范围 | 无符号范围 | 表示| | :-- | :-- | :-- | :-- | :-- | | tinyint | 1 byte | (-128，127) | (0，255) | 小整数值 | | smallint | 2 bytes | (-32 768，32 767) | (0，65 535) | 大整数值 | | mediumint | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 | | int | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 | | bigint | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 | **那要怎么表示布尔值呢?** 通常我们使用`tinyint(1)`表示布尔值, 0为false, 1为true. **限制不使用负数** 在字段后面跟随`unsigned`. ```sql -- 创建一张表 Id unsigned create table test4(id int(10) unsigned,num int(10)); -- 插入数据 -- 1.插入10,10 insert into test4 values(10,10); Query OK, 1 row affected (0.00 sec) -- 2.插入-10,10 insert into test4 values(-10,10); ERROR 1264 (22003): Out of range value for column 'id' at row 1 -- 3.插入10,-10 insert into test4 values(10,-10); Query OK, 1 row affected (0.00 sec) ``` 我们通过例子可以看到, 声明了`unsigned`, 就不可以插入负数了, 整型和浮点型均如是. **当数据超出最大长度范围了怎么办?** 首先我们在`tinyint(100)`中的100是指显示宽度范围, 并不是只能存储到100.
举个例子🌰: ```sql --创建一张表 create table test(id int, num tinyint(100)); ``` 可以看到我们设置`num`字段显示宽度是100. ```sql --插入一条数据 insert into test values(1,111); --插入成功 Query OK, 1 row affected (0.00 sec) ``` 可以看到, 我们的数据插入成功了同样的, tinyint的无符号最大范围是255, 接下来我们插入一个256的数据. ```sql --插入一条数据280 insert into test2 values(2,280); --报错 ERROR 1264 (22003): Out of range value for column 'age' at row 1 ``` 可以看到我们的数据库报错了.数据表中也没有插入该条数据.
所以我们在设计表的时候数值类型的选择要选择恰当合适, 不大不小. **那么当我们数据库的自增id超出最大长度了怎么办?** 首先, 如果我们设置了主键约束, 那么就会和上面一样报错, 主键冲突.
如果没有设置, 数据库会自动生成一个row_id, 新数据将会覆盖老数据.
所以设置主键的时候, 尽量使用bigint类型.
[详细](https://juejin.cn/post/6913765444457332743) **浮点型** | 类型 | 字节大小 | 有符号范围 | 无符号范围 | 表示| | :-- | :-- | :-- | :-- | :-- | | float |4 bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 | | double | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 | | decimal | 对decimal(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 | `float`、`double`和`decimal`后面均可以带长度. - float(M,D) - double(M,D) - decimal(M,D) 这里的`M,D`中, M表示显示宽度, D表示小数位数. **float和double我们怎么选择呢?** `float`相对于`double`, 计算速度更快, 所占空间仅为`double`的一半.但是`float`的精度小于`double`. *那我们如何选择呢?* `float`能表示的小数位数少, `double`能表示比`float`更多的位数且更加精确.
实际应用中, 我们使用`decimal`的情况会更多. `decimal`可以决定我们的小数位数, 他的存储空间依赖M、D的值, 使用更少的空间, 同时更加灵活. **decimal是如何计算存储空间的** 官方表格对照: | Leftover Digits | Number of Bytes | | :-- | :-- | |0 |0| |1–2 | 1 | |3–4 | 2 | |5–6 | 3 | |7–9 | 4 | 这个怎么用呢? 例如我们定义: - decimal(15,6) 整数部分15-6 = 9, 小数部分 6, 对照表格, 存储空间就是 4 + 3 = 7; - decimal(20,9) 整数部分20-9 = 11, 小数部分 9, 对照表格, 存储空间就是 4 + 1 + 3 = 8; 这里的1 就是11 - 9=2, 2对应字节数为1. 每超过9, 就减去9再开始计算. 举个例子🌰: 我们可以这样去使用这三个类型: ```sql -- 创建test表 create table test(id float(6,2),id2 double(10,4),id3 decimal(4,2)); -- 得到 mysql> desc test; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | float(6,2) | YES | | NULL | | | id2 | double(10,4) | YES | | NULL | | | id3 | decimal(4,2) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) -- 插入数据 insert into test values(10.22,200.44444,88.88); -- 得到结果 mysql> select * from test; +-------+----------+-------+ | id | id2 | id3 | +-------+----------+-------+ | 10.22 | 200.4444 | 88.88 | +-------+----------+-------+ 1 row in set (0.01 sec) -- 可以看到, 我们插入的id2为200.44444, 超出限制, 只显示200.4444 ``` 合理搭配使用整型和浮点数, 能更好的存储数据. - 可以在字段之后跟随 unsigned, 表示不能使用负数 - 可以使用tinyint(1)表示布尔值0 1 - 一般使用descmal定义浮点数 ## 二、日期和时间类型 | 类型 | 字节大小 | 有符号范围 | 无符号范围 | 表示| | :-- | :-- | :-- | :-- | :-- | |date| 3| 1000-01-01/9999-12-31| YYYY-MM-DD| 日期值| |time| 3| '-838:59:59'/'838:59:59'| HH:MM:SS| 时间值或持续时间| |year| 1| 1901/2155| YYYY| 年份值| |datetime| 8| 1000-01-01 00:00:00/9999-12-31 23:59:59 |YYYY-MM-DD HH:MM:SS| 混合日期和时间值| |timestamp| 4|1970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日凌晨 03:14:07| YYYYMMDD HHMMSS| 混合日期和时间值，时间戳| **我们该如何选择日期类型** 通常, 我们会考虑一下几种方案来存储时间: - int - timestamp - datetime **选择int类型** 只能存储时间戳, 即毫秒数. 选择`int`虽然很方便, 但是我们需要导出数据, 做一些数据分析的时候, 就会很难看出具体时间. **选择timestamp类型** 这个类型本质上存储的也是毫秒数, 而且他会做一些时间格式化, 看起来直观. 但是他却有范围, 结束时间2038年 **选择datetime类型** 存储的时间格式, 占8个字节, 范围很大, 比较适用. 举个例子🌰: ```sql --创建一个表 create table test(id int(10),t_int int(10),t_time timestamp(6),t_date datetime(2)); --desc test; +--------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | t_int | int | YES | | NULL | | | t_time | timestamp(6) | YES | | NULL | | | t_date | datetime(2) | YES | | NULL | | +--------+--------------+------+-----+---------+-------+ 4 rows in set (0.02 sec) --往表中插入时间数据 insert into test5 values(1,unix_timestamp(now()),now(),now()); --结果 +------+------------+----------------------------+------------------------+ | id | t_int | t_time | t_date | +------+------------+----------------------------+------------------------+ | 1 | 1627461516 | 2021-07-28 16:38:36.000000 | 2021-07-28 16:38:36.00 | +------+------------+----------------------------+------------------------+ ``` 我们可以看到三种时间的存储方式, `timestamp`和`datetime`观感其实差别不大. ## 三、字符串类型 | 类型 | 字节大小 | 表示 | | :-- | :-- | :-- | |char| 0-255 bytes| 定长字符串| |varchar| 0-65535 bytes| 变长字符串| |tinyblob| 0-255 bytes| 不超过 255 个字符的二进制字符串| |tinytext| 0-255 bytes| 短文本字符串| |blob| 0-65 535 bytes| 二进制形式的长文本数据| |text| 0-65 535 bytes| 长文本数据| |mediumblob| 0-16 777 215 bytes| 二进制形式的中等长度文本数据| |mediumtext| 0-16 777 215 bytes| 中等长度文本数据| |longblob| 0-4 294 967 295 bytes| 二进制形式的极大文本数据| |longtext| 0-4 294 967 295 bytes| 极大文本数据| **char和varchar的区别** 1. `char(n)`和`varchar(n)`中的n都代表字符个数. 超过之后会被截断. 2. `char(n)`不论实际存储多大, 都会占用n个字符的空间. 3. `varchar(n)`只会占用实际字符应该占用的字节空间加1, 1是用来保存字符长度的. 4. `char(n)`会被截断尾部空格, `varchar(n)`则不会. 所以一般`char(n)`用来存储已知的比较段的字段. 例如密码什么的. **blob和text** 1. `blob`是用来存储二进制字符串, 有不同长度的三种类型: blob、mediumblob、longblob. 2. `text`用来存储字符串文本, 有不同长度的三种类型: text、mediumtext、longtext. 举个例子🌰: ```sql --创建一张表 create table test6(id int(10), name varchar(10),pwd char(10),color blob(6),txt text(100)); --插入数据 insert into test6 values(1,"梦佳","zs123","ffffff","我爱我的祖国!"); insert into test6 values(2,"孟洋","没有23",0xffffff,"我爱我的祖国!"); --结果 +------+--------+---------+----------------+---------------------+ | id | name | pwd | color | txt | +------+--------+---------+----------------+---------------------+ | 1 | 梦佳 | zs123 | 0x666666666666 | 我爱我的祖国! | | 2 | 孟洋 | 没有23 | 0xFFFFFF | 我爱我的祖国! | +------+--------+---------+----------------+---------------------+ ``` > 我们可以看到, 插入的color字段, 不管是字符串还是二进制, 都会存储为二进制的数据. ## 总结我们在拿到需求的时候, 确定技术方案之后, 就需要设计我们的数据库, 在定义每一张表的时候, 数据类型的定义就显得很重要, 我们需要仔细并且熟练的使用SQL的数据类型, 不只是应用到表的定义中, 在SQL编程中也是必不可少的. 我们这一章讲了sql的数据类型, 包括: - 数值类型: 整型和浮点型 - 日期和时间类型 - 字符串类型这些类型已经可以覆盖我们大部分的场景了. 我们需要注意一些数据类型的范围和存储空间, 灵活的使用类型定义能更好的完成需求任务. [SQL数据类型表格引用](https://www.runoob.com/sql/sql-datatypes.html) # 第三章 MySQL中的建表约束 ## 一、主键约束「主键约束」能够唯一确定一张表中的一条记录. 通过给某个字段添加约束, 就可以使得该字段不重复且不为空.
使用关键字`primary key`这只主键约束. ```sql -- 创建一张宠物表 create table pet( id int primary key, name varchar(20), ); -- 查询表pet desc pet; -- 得到 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ ``` 接下来, 我们往`pet`表中插入一条数据: ```sql -- 插入一条数据 insert into pet values(1,'狗狗'); -- 查询 select * from pet; -- 得到 +----+--------+ | id | name | +----+--------+ | 1 | 狗狗 | +----+--------+ ``` **这个时候我们继续插入id=1的数据还可以吗?** 当然是不可以的. ```sql insert into pet values(1,'狗狗'); -- 报错 ERROR 1062 (23000): Duplicate entry '1' for key 'pet.PRIMARY' ``` 同样的, `id`也不能传空、`null`.
这样子, 我们才可以通过这个唯一id来确定一条数据. 我们要唯一确定一条数据, 还可以通过「联合主键」的形式来创建表. ```sql -- 创建联合主键的表 create table pet1( id int, name varchar(10), pwd int(10), primary key(id,name) ); -- desc desc pet1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(10) | NO | PRI | NULL | | | pwd | int | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ ``` 我们来看下, 插入两条数据. ```sql insert into pet1 values(1, "狗狗",123); insert into pet1 values(2,"猫猫",345); -- 可以得到 +----+--------+------+ | id | name | pwd | +----+--------+------+ | 1 | 狗狗 | 123 | | 2 | 猫猫 | 345 | +----+--------+------+ ``` 如果插入数据id和name一致, 则不能插入. ## 二、自增约束一般情况下, 自增约束都是和主键约束配合使用.
使用关键字「auto_increment」来表示自增约束.
我们来创建一张表. ```sql create table pet2( id int primary key auto_increment, name varchar(20) ); -- 得到 +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ ``` 接着, 我们来插入两条数据看下: ```sql insert into pet2 values(1,"狗狗"); insert into pet2 (name) values("猫猫"); -- 得到 +----+--------+ | id | name | +----+--------+ | 1 | 狗狗 | | 2 | 猫猫 | +----+--------+ ``` 显而易见, 我们插入的数据「猫猫」是没有`id`的, 但是mysql让我们的`id`自动增长了. >注意:
> 如果我们创建表的时候, 忘记添加主键约束了, 我们还可以继续添加、修改、删除主键约束. ```sql -- 添加 alter table pet3 add primary key(id); --删除 alter table pet3 drop primary key; -- 修改 alter table pet3 modify id int primary key; ``` ## 三、唯一约束约束修饰的字段的值不可以重复.
使用关键字`「unique」` 来添加唯一约束. ```sql create table pet5( id int, name varchar(20), unique(name) ); -- 得到 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ ``` > 注意:
> 我们也可以直接在那么后面跟随关键字「unique」来添加唯一约束. > 同主键约束, 唯一约束也可以联合添加. 两个键在一起不重复. **还有那些方式操作唯一约束呢?** 创建万表之后, 唯一约束也可以添加、删除. ```sql -- 如何删除唯一约束 alter table pet7 drop index name; -- modify添加 alter table pet7 modify name varchar(20) unique; ``` 具体的用法可以自己下去实验. 这里就不演示了. ## 四、非空约束修饰的字段不能为空. 使用关键字`not null`来创建非空约束. ```sql create table pet8( id int, name varchar(20) not null ); --desc +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ -- 插入数据 insert into pet8 values(1,"狗狗"); insert into pet8 values(2,""); -- 得到 +------+--------+ | id | name | +------+--------+ | 1 | 狗狗 | | 2 | | +------+--------+ insert into pet8 values(2,null); --得到 ERROR 1048 (23000): Column 'name' cannot be null ``` 显而易见, 使用`not null`约束之后, 该字段就不会为`null`了. > 注意:
> 空不是null. ## 五、默认约束当插入某一个字段值的时候, 没有传值, 就会使用默认值. 使用关键字`default`来创建默认约束. ```sql create table pet9( id int, name varchar(20), age int(10) default 10 ); -- 得到 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int | YES | | 10 | | +-------+-------------+------+-----+---------+-------+ -- 插入数据 insert into pet9 (id,name) values(1,'大象'); -- 得到 +------+--------+------+ | id | name | age | +------+--------+------+ | 1 | 大象 | 10 | +------+--------+------+ ``` 显而易见, 没有传入的值如果设置了默认约束, 则会自动填上默认值. ## 六、外键约束外间约束需要使用到两个表, 一个主表, 一个副表. 首先, 我们来创建两个表, 一个宠物表pet10, 一个动物分类表pet_type. ```sql -- 创建pet_type create table pet_type( id int primary key, name varchar(20) ); -- 创建pet10 create table pet10( id int primary key, name varchar(20), pet_type_id int, foreign key(pet_type_id) references pet_type(id) ); ``` 接下来, 我们向pet_type表中插入数据. ```sql insert into pet_type values(1,"狗科"); insert into pet_type values(2,"猫科"); -- 得到 +----+--------+ | id | name | +----+--------+ | 1 | 狗科 | | 2 | 猫科 | +----+--------+ ``` 然后, 我们继续往pet10表中插入数据如下. ```sql insert into pet10 values(1,"狗狗",1); insert into pet10 values(2,"猫猫",2); -- 得到 +----+--------+-------------+ | id | name | pet_type_id | +----+--------+-------------+ | 1 | 狗狗 | 1 | | 2 | 猫猫 | 2 | +----+--------+-------------+ ``` 两张表我们都创建好了, 显然, `pet10`表中的`pet_type_id`字段与`pet_type`表中`id`关联. 接下来, 我们来向副表`pet10`插入主表`pet_type`中没有的数据. ```sql insert into pet10 values(3,"鼠鼠",3); -- 报错 ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`pet10`, CONSTRAINT `pet10_ibfk_1` FOREIGN KEY (`pet_type_id`) REFERENCES `pet_type` (`id`)) ``` 然后, 我们删除主表中的`id=2`的字段. ```sql delete from pet_type where id=2; -- 报错 ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`pet10`, CONSTRAINT `pet10_ibfk_1` FOREIGN KEY (`pet_type_id`) REFERENCES `pet_type` (`id`)) ``` 显而易见, 是不可以被删除的. > 注意:
> 主表中没有的数据, 在副表中是不可以被使用的.
> 主表中的数据被副表中的数据引用, 在主表中是不可以被删除的. ## 七、建表范式简单学习了创建表的约束条件.
我们就可以考虑设计业务表了.
我们在设计一张表的时候具有三大范式. **第一范式** 数据表中的所有字段都是不可分割的原子值;
就是说每个字段尽可能的小, 直到不可分割.
比如「中国浙江省杭州市」可以拆为「中国」、「浙江省」、「杭州市」.
```sql create table student( id int primary key, name varchar(10), country varchar(20), privence varchar(20), city varchar(20), ); ``` 设计的越详细也不是越好, 根据实际需求设计. **第二范式** 需要在满足第一范式的条件下.
第二范式要求: 除主键外的每一列都必须完全依赖与主键.
如果出现不完全依赖, 只可能发生在联合主键的情况下.
```sql create table order_list( product_id int, customer_id int, product_name varchar(20), customer_name varchar(20), primary key(product_id,customer_id) ); ``` 上面的表的设计就有点臃肿了. 这个时候我们就需要拆表了 ```sql -- 订单表 create table order_list( order_id int primary key, product_id int, customer_id int ); -- 产品表 create table product( id int primary key, name archer(20) ); -- 用户表 create table customer( id int primary key, name archer(20) ); ``` 这样子, 就满足第二范式了, 拆表能够更好的设计数据库. **第三范式** 必须先满足第二范式.
除去主键列的其他列之间不能有传递的依赖关系. ```sql create table order_list( order_id int primary key, product_id int, customer_id int, customer_phone int ); ``` 显而易见, 这样子设计, `customer_phone`可以根据`customer_id`查找到. 我们可以将`customer_phone`放到表`customer`中. ```sql create table customer( id int primary key, name archer(20), phone int(10) ); ``` 以上, 我们就简单介绍完MySQL中表的设计了, 如何设计表, 需要自己实践和需求具体的要求去思考了. # 第四章 SQL语言 ## 一、SQL介绍 Structured Query Languag.
SQL是一种结构化查询语言, 适用于管理关系型数据库管理系统.
它可以让我们便捷的操作数据库, 以及对数据进行「增删改查」等操作.
大部分的数据库都支持SQL语言, 同时他们又各自有自己的扩展, 但是万变不离其宗.
接下来的demo都是基于MySQL.
## 二、SQL语言分类 1. DQL（Data Query Language）数据查询语言, 例如select、from 2. DMLDML (Data Manipulate Language) 数据操作语言, 例如insert、update、delete 3. DDL（Data Define Languge）数据定义语言, 例如create、frop, 用来创建数据库中的各种对象-----表、视图等 4. DCL (Data Control Language) 数据控制语言, 例如grant、commit 这四类语言共同组成了SQL语言. ## 三、管理数据库和表首先, 我们需要先启动MySQL数据库服务器 ```sql mysql -u root -p123456 -- 123456是你数据库的密码 ``` 接下来我们先学习几个数据库命令: --- ### 1、数据库管理我们需要知道如何去查看数据库, 并知道如何去使用数据库, 以及查看数据库中的表. **如何查看数据库** ```sql -- 展示所有的数据库. Show databases; -- 得到 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ ``` **使用数据库** ```sql use 库; ``` **查看当前库的所有表** ```sql show tables; show tables from 库名; ``` ### 2、库的创建和删除 **管理数据库:** ```sql -- 1.创建库test create database test; -- 2.删除库tset drop database test; ``` **管理数据表:** 我们先来看下如何创建一张表 ```sql -- 1.创建一张user表, 具有name和age create table user( id int, name varchar(20), age int ); --2.查看user表的描述 desc user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ ``` > 创建表的时候, 不能创建同名的表, 使用 if not exists 可以创建同名表不报错, 但是表没有创建成功. ```sql create table if not exists user( id int, name varchar(20), age int(200) ); -- 表还是之前创建的. desc user; ``` 接下来, 我们来学习如何对user表进行修改. --- **(1)、如何修改表名 ✅** ```sql -- 1.如何修改表名 alter table user rename students; -- 结果 mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | students | +----------------+ -- 下面的语句也是可以的: alter table user rename to students; ``` **(2)、如何添加字段 ✅** ```sql -- 2.添加字段 alter table students add phone int(20); -- 可以在后面跟随first表示在第一列插入, 在后面跟随after可以表示插入到某列之后 alter table students add pwd int(20) first; alter table students add city varchar(10) after age; -- 可以得到 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | pwd | int | YES | | NULL | | | id | int | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int | YES | | NULL | | | city | varchar(10) | YES | | NULL | | | phone | int | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ -- 同时我们还可以声明这个字段不为空, 在语句后跟随not null. -- column可以省略, 表示修改的是列. alter table students add column phone int(20); -- column省略会有警告, 不过没事. -- add支持批量添加 alter table students add (phone int(20), pwd int(20)); ``` **(3)、删除字段 ✅** ```sql -- 3.删除字段 alter table students drop pwd; alter table students drop column phone; ``` **(4)、修改字段类型 ✅** ```sql -- 4.修改字段类型 alter table students modify column city varchar(20); alter table students modify column age varchar(10); alter table students modify id int primary key; --可以看到我们的字段city和age的数据类型被改变了, 同时设置id为主键 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | age | varchar(10) | YES | | NULL | | | city | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ ``` **(5)、修改列名 ✅** ```sql -- 5.修改列名 alter table students change column age sex varchar(10); +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | | sex | varchar(10) | YES | | NULL | | | city | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ ``` ### 3、对数据进行增删改 **如何对数据进行增删改** **** 这里用到的是SQL重的DML语言. **(1)、插入数据 ✅** 通过insert into的方法插入. ```sql -- 1.插入数据 insert into students values(1,"超哥","男","杭州"); -- 可以看到我们已经向students表中插入了一条数据 +----+--------+------+--------+ | id | name | sex | city | +----+--------+------+--------+ | 1 | 超哥 | 男 | 杭州 | +----+--------+------+--------+ -- 可以批量插入 insert into students values(2,"孟洋","男","null"),(3,"梦佳","女","山沟沟"); +----+--------+------+-----------+ | id | name | sex | city | +----+--------+------+-----------+ | 1 | 超哥 | 男 | 杭州 | | 2 | 孟洋 | 男 | null | | 3 | 梦佳 | 女 | 山沟沟 | -- 也可以只插入一部分字段, 其他字段就会取默认值了 insert into students (id,name) values(4,"爱德"); +----+--------+------+-----------+ | id | name | sex | city | +----+--------+------+-----------+ | 1 | 超哥 | 男 | 杭州 | | 2 | 孟洋 | 男 | null | | 3 | 梦佳 | 女 | 山沟沟 | | 4 | 爱德 | NULL | NULL | +----+--------+------+-----------+ ``` 插入数据的过程中需要注意: - 字段类型与值一致且一一对应. - 默认值null, 可以不插入. - 字段个数与插入值个数一致. - 省略字段, values后的值与表中字段对应, 顺序不能错. **(2)、修改数据 ✅** 使用update方法修改表数据. ```sql -- 2.修改数据 update students set city='杭州' where id=3; +----+--------+------+--------+ | id | name | sex | city | +----+--------+------+--------+ | 1 | 超哥 | 男 | 杭州 | | 2 | 孟洋 | 男 | null | | 3 | 梦佳 | 女 | 杭州 | | 4 | 爱德 | NULL | NULL | +----+--------+------+--------+ -- 可以支持多个字段修改和多个条件满足. -- 修改数据还可以有限制, order by 和limit. update students set city='山沟沟' where id=3 order by id asc limit 1; --order by id asc 是id正序排列 ; limit 1 只修改一行. --desc 逆序. ``` **(3)、删除数据 ✅** 使用delete和truncate删除数据的操作. ```sql -- 3.删除数据 delete from students where id=4; -- 结果 +----+--------+------+-----------+ | id | name | sex | city | +----+--------+------+-----------+ | 1 | 超哥 | 男 | 杭州 | | 2 | 孟洋 | 男 | null | | 3 | 梦佳 | 女 | 山沟沟 | +----+--------+------+-----------+ -- 删除也可以指定排序和限制删除行数 order by 和limit delete from students where id=4 order by id desc limit 1; -- 有相同id的时候, 可选择用不同的删除条件 -- 还有一种删除方法 truncate truncate table students; -- 删除掉表中的所有记录 delete from students; -- delete也可以删除表中所有记录. ``` 在使用delete和truncate的时候需要注意: - 合理的使用delete操作, 以及他的删除条件 - truncate删除不能带有where条件 - truncate比delete快 - delete也可以删除表中所有记录 ### 4、多种查询方式查询数据我们用到的是DQL语言, 查询是数据库操作中比较重要的一环, 一个好的查询方法能提高我们得到数据的效率.
查询的方式也有很多种: - 普通查询 - 条件查询 - 模糊查询 - 排序查询 - 分组查询 - 多表链接查询 - 子查询 - 分页查询 - 联合查询 - 函数查询 - ... 接下来, 我们就来详细看下每种查询是如何操作的吧! --- **(1)、普通查询 ✅** ```sql -- 1.普通查询 select id, name from students; -- 结果 +----+--------+ | id | name | +----+--------+ | 1 | 超哥 | | 2 | 孟洋 | | 3 | 梦佳 | -- 查询全部记录 select * from students; -- 注意: mysql不支持select into这种方式,可以使用下面的方式 create temporary table students2(select * from students); ``` > 在实际操作中, 很少使用 select * from table; **(2)、条件查询 ✅** 使用where关键字以及一些条件运算符和逻辑运算符
条件运算符「 > < = != <> >= <= 」
逻辑运算符「 && || ! 」和「 and or not 」, 符号和关键字一样
```sql -- 2. 条件查询 select * from students where id=2; -- 得到 +----+--------+------+------+ | id | name | sex | city | +----+--------+------+------+ | 2 | 孟洋 | 男 | null | +----+--------+------+------+ select * from students where id=2 or name="梦佳"; -- 得到 +----+--------+------+-----------+ | id | name | sex | city | +----+--------+------+-----------+ | 2 | 孟洋 | 男 | null | | 3 | 梦佳 | 女 | 山沟沟 | +----+--------+------+-----------+ ``` **(3)、模糊查询 ✅** 模糊查询的关键字有「 like、between and、in、is null、is not null 」以及一些SQL函数等. ```sql -- 3.模糊查询 -- 模糊查询的关键字有like、between and、in、is null、is not null以及一些SQL函数等 -- (1)、使用like -- like 一般配合通配符使用. -- %表示任意个字符, _表示单个字符 -- '%a' 以a结尾的数据 -- 'a%' 以a开头的数据 -- '%a%' 含有a的数据 -- '_a_' 三位且中间字母是a的 -- '_a' 两位且结尾字母是a的 -- '_a' 两位且结尾字母是a的 -- 'a_' 两位且开头字母是a的 select * from students where name like '%佳%'; -- 得到 +----+--------+------+-----------+ | id | name | sex | city | +----+--------+------+-----------+ | 3 | 梦佳 | 女 | 山沟沟 | +----+--------+------+-----------+ select * from students where name like '_洋'; -- 得到 +----+--------+------+------+ | id | name | sex | city | +----+--------+------+------+ | 2 | 孟洋 | 男 | null | +----+--------+------+------+ -- (2)、使用 between and select * from students where id between 2 and 5; -- 得到 +----+--------+------+-----------+ | id | name | sex | city | +----+--------+------+-----------+ | 2 | 孟洋 | 男 | null | | 3 | 梦佳 | 女 | 山沟沟 | +----+--------+------+-----------+ -- (3)、使用 in select * from students where id in (1, 2, 5, 8, 9); -- 得到 +----+--------+------+--------+ | id | name | sex | city | +----+--------+------+--------+ | 1 | 超哥 | 男 | 杭州 | | 2 | 孟洋 | 男 | null | +----+--------+------+--------+ -- (4)、使用rlike关键字来使用正则表达式 select * from students where name rlike '^梦'; -- 得到以梦开头的 +----+--------+------+-----------+ | id | name | sex | city | +----+--------+------+-----------+ | 3 | 梦佳 | 女 | 山沟沟 | +----+--------+------+-----------+ -- (5)、使用一些函数instr(str,substr)、locate(substr, str)、position(substr in str)等; select * from students where instr(name, "佳")>0; select * from students where locate("佳", name)>0; select * from students where position("佳" in name)>0; -- 得到的结果都是一样的 +----+--------+------+-----------+ | id | name | sex | city | +----+--------+------+-----------+ | 3 | 梦佳 | 女 | 山沟沟 | +----+--------+------+-----------+ -- 使用模糊查询还可以使用is null 和is not null来判断空还是非空 ``` **(4)、排序查询 ✅** 使用「 **order by** 」进行数据排序. ```sql -- 4、排序查询 -- 我们前面也接触到了排序order by -- 为了方便, 我们在之前的数据表students中在插入一个字段age alter table students add age int(20); update students set age=20 where id=1; update students set age=10 where id=2; update students set age=18 where id=3; -- 然后我们来查询数据 select * from students order by age asc; -- 得到 +----+--------+------+-----------+------+ | id | name | sex | city | age | +----+--------+------+-----------+------+ | 2 | 孟洋 | 男 | null | 10 | | 3 | 梦佳 | 女 | 山沟沟 | 18 | | 1 | 超哥 | 男 | 杭州 | 20 | +----+--------+------+-----------+------+ select * from students where age > 0 order by age desc, id asc; --得到 +----+--------+------+-----------+------+ | id | name | sex | city | age | +----+--------+------+-----------+------+ | 1 | 超哥 | 男 | 杭州 | 20 | | 3 | 梦佳 | 女 | 山沟沟 | 18 | | 2 | 孟洋 | 男 | null | 10 | +----+--------+------+-----------+------+ select * from students where age > 0 order by age desc, id asc limit 1; --得到 +----+--------+------+--------+------+ | id | name | sex | city | age | +----+--------+------+--------+------+ | 1 | 超哥 | 男 | 杭州 | 20 | +----+--------+------+--------+------+ -- 我们可以通过排序得到很多我们需要的数据. -- 同时我们可以使用limit关键字来限制查询的长度. ``` **(5)、分组查询 ✅** 使用「 **group by** 」对数据进行分组. ```sql -- 5、分组查询 -- (1)、单独使用group by select sex from students group by sex; -- 得到 +------+ | sex | +------+ | 男 | | 女 | +------+ -- 当我们要分组查询多条字段的时候, 例如下面: select name,sex from students group by sex; -- 这个时候数据库会报错 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column. -- 当我们给sex分组的时候, 只有「男」和「女」, 所以数据库很难知道name是那一条 -- 据说是mysql5.7以后自动开启了 only_full_group_by服务，只要关了就行. -- 这里就不讲关的方法了.网上有很多. -- 一般来说我们按照什么分组就查询那一列就可以了 -- 那我们分完组之后如何对数据再进行查询呢? select sex, group_concat(name) from students group by sex; -- 得到 +------+--------------------+ | sex | group_concat(name) | +------+--------------------+ | 女 | 梦佳 | | 男 | 超哥,孟洋 | +------+--------------------+ -- 我们可以看到, 「女」只有一个「梦佳」,「男」有两个. 查询正确. -- (2)、group by 配合使用聚合函数 -- 这里简单介绍下count(), 具体会在函数章节详细介绍. select sex, group_concat(name),count(name) from students group by sex; -- 得到 +------+--------------------+----------+ | sex | group_concat(name) | count(*) | +------+--------------------+----------+ | 女 | 梦佳 | 1 | | 男 | 超哥,孟洋 | 2 | +------+--------------------+----------+ -- 可以看到使用函数count(name)统计出了男女各自的人数. -- (3)、group by 配合having -- 上面的例子我们再插入一条数据 insert into students values(4,"爱德","男","郑州",18); -- having 的作用和where类似, 但是having只能用于group by -- 我们下面来查询年龄大于10的人姓名并统计个数 select age,group_concat(name) as n_name,group_concat(age) as n_age,count(age) as age_num from students group by age having age > 10; -- 得到 +------+---------------+-------+---------+ | age | n_name | n_age | age_num | +------+---------------+-------+---------+ | 18 | 梦佳,爱德 | 18,18 | 2 | | 20 | 超哥 | 20 | 1 | +------+---------------+-------+---------+ -- 按照年纪分组, 18岁的2个, 20岁的1个 -- 可以对分组之后统计字段进行条件筛选, 例如得到每个年龄段个数大于1的 select age,group_concat(name) as n_name,group_concat(age) as n_age,count(age) as age_num from students group by age having age_num > 1; -- 得到 +------+---------------+-------+---------+ | age | n_name | n_age | age_num | +------+---------------+-------+---------+ | 18 | 梦佳,爱德 | 18,18 | 2 | +------+---------------+-------+---------+ ``` **where和having 有什么区别呢?** > where后面不可以使用分组函数, having后面可以. > where是对原表条件筛选, having是对分组之后的记录进行条件筛选. **(6)、多表链接查询 ✅** 学习多表查询之前, 我们先来继续创建两个表grades和jobs. 还记得我们之前创建表和插入数据的方法吗? ```sql -- 创建grades表 create table grades(id int, grade varchar(10)); -- 往grades中插入几条数据 insert into grades values(1,"一年级"),(2,"二年级"),(3,"三年级"); -- 得到 +------+-----------+ | id | grade | +------+-----------+ | 1 | 一年级 | | 2 | 二年级 | | 3 | 三年级 | +------+-----------+ -- 创建jobs表 create table jobs(id int, job varchar(10)); -- 往grades中插入几条数据 insert into jobs values(1,"班长"),(2,"学习委员"),(3,"体育委员"),(4,"数学课代表"); -- 得到 +------+-----------------+ | id | job | +------+-----------------+ | 1 | 班长 | | 2 | 学习委员 | | 3 | 体育委员 | | 4 | 数学课代表 | +------+-----------------+ -- 接下来我们继续讲如何进行多表查询. ``` **交叉连接(笛卡尔积)** 什么是笛卡尔乘积呢? 我们在高中数学学过, 其实就是两个集合的排列组合. > 假设集合A={ a, b }，集合B={ 0, 1 }，则两个集合的笛卡尔积为{(a, 0), (a, 1), (b, 0), (b, 1)}。在sql中, 笛卡尔积常用来做多表链接. ```sql -- 交叉连接 select * from students, grades; select * from students cross join grades; -- 这两个查询语句得到的结果是一样的. +----+--------+------+-----------+------+------+-----------+ | id | name | sex | city | age | id | grade | +----+--------+------+-----------+------+------+-----------+ | 1 | 超哥 | 男 | 杭州 | 20 | 3 | 三年级 | | 1 | 超哥 | 男 | 杭州 | 20 | 2 | 二年级 | | 1 | 超哥 | 男 | 杭州 | 20 | 1 | 一年级 | | 2 | 孟洋 | 男 | null | 10 | 3 | 三年级 | | 2 | 孟洋 | 男 | null | 10 | 2 | 二年级 | | 2 | 孟洋 | 男 | null | 10 | 1 | 一年级 | | 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 | 三年级 | | 3 | 梦佳 | 女 | 山沟沟 | 18 | 2 | 二年级 | | 3 | 梦佳 | 女 | 山沟沟 | 18 | 1 | 一年级 | | 4 | 爱德 | 男 | 郑州 | 18 | 3 | 三年级 | | 4 | 爱德 | 男 | 郑州 | 18 | 2 | 二年级 | | 4 | 爱德 | 男 | 郑州 | 18 | 1 | 一年级 | +----+--------+------+-----------+------+------+-----------+ select * from grades cross join students; -- 这个得到结果与上面一样, 只是排列顺序不一致. ``` 这个就是我们的交叉连接查询了, 是不是很简单. 当然, 查询还可以带入条件, 分组等. 与之前讲的一致. **内连接** 这是最常见也是最常用的表连接, 但是只会查询到匹配的行.
我们先来看下显示内链接, 使用`join on`关键字.
这里, 我们需要在之前的students表中添加一个字段grade_id来表示年级id. ```sql -- 添加一列grade_id alter table students add grade_id int; -- 插入数据 -- grade_id的值和grade表的id一致 update students set grade_id=2 where id=1; update students set grade_id=1 where id=2; update students set grade_id=3 where id=3; update students set grade_id=2 where id=4; -- 得到 +----+--------+------+-----------+------+----------+ | id | name | sex | city | age | grade_id | +----+--------+------+-----------+------+----------+ | 1 | 超哥 | 男 | 杭州 | 20 | 2 | | 2 | 孟洋 | 男 | null | 10 | 1 | | 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 | | 4 | 爱德 | 男 | 郑州 | 18 | 2 | +----+--------+------+-----------+------+----------+ ``` 我们通过关联students的grade_id与grades表的id字段, 将每个人的年级对应起来.
一般也会约定主键和外键来关联. 后面会讲到. ```sql -- 查询每个人对应的年级 select * from students join grades on students.grade_id = grades.id; -- 得到 +----+--------+------+-----------+------+----------+------+-----------+ | id | name | sex | city | age | grade_id | id | grade | +----+--------+------+-----------+------+----------+------+-----------+ | 1 | 超哥 | 男 | 杭州 | 20 | 2 | 2 | 二年级 | | 2 | 孟洋 | 男 | null | 10 | 1 | 1 | 一年级 | | 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 | 3 | 三年级 | | 4 | 爱德 | 男 | 郑州 | 18 | 2 | 2 | 二年级 | +----+--------+------+-----------+------+----------+------+-----------+ ``` 在内链接中, 我们还可以使用隐式连接. 使用where字段来表示. ```sql -- 使用where隐式连接 select * from students, grades where students.grade_id = grades.id; -- 得到的结果与上面一致 +----+--------+------+-----------+------+----------+------+-----------+ | id | name | sex | city | age | grade_id | id | grade | +----+--------+------+-----------+------+----------+------+-----------+ | 1 | 超哥 | 男 | 杭州 | 20 | 2 | 2 | 二年级 | | 2 | 孟洋 | 男 | null | 10 | 1 | 1 | 一年级 | | 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 | 3 | 三年级 | | 4 | 爱德 | 男 | 郑州 | 18 | 2 | 2 | 二年级 | +----+--------+------+-----------+------+----------+------+-----------+ ``` **外连接** 外连接其实可以看作是内连接的扩展, 包括左、右连接, 全外连接.
在mysql中全外连接的方法不是full join. - 左外连接即是优先显示左表的全部记录. 而第二个表只包含与第一个表相匹配的行, 空为null. 我们先在students表中插入一条数据. ```sql -- 先插入数据 insert into students values(5, "旭神", "女", "阿拉罕",88,4); -- 使用左外连接查询 select * from students left join grades on students.grade_id = grades.id; -- 得到 +----+--------+------+-----------+------+----------+------+-----------+ | id | name | sex | city | age | grade_id | id | grade | +----+--------+------+-----------+------+----------+------+-----------+ | 1 | 超哥 | 男 | 杭州 | 20 | 2 | 2 | 二年级 | | 2 | 孟洋 | 男 | null | 10 | 1 | 1 | 一年级 | | 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 | 3 | 三年级 | | 4 | 爱德 | 男 | 郑州 | 18 | 2 | 2 | 二年级 | | 5 | 旭神 | 女 | 阿拉罕 | 88 | 4 | NULL | NULL | +----+--------+------+-----------+------+----------+------+-----------+ ``` 我们可以看到, 在grade表中没有id为4的字段, 所以全部为null;
如果使用内连接查询, students表中id=5的一行数据就没有了. - 右外连接即是优先显示右表的全部记录. 而第二个表只包含与第一个表相匹配的行, 空为null. ```sql -- 使用右外连接查询 select * from students right join grades on students.grade_id = grades.id; -- 得到 +------+--------+------+-----------+------+----------+------+-----------+ | id | name | sex | city | age | grade_id | id | grade | +------+--------+------+-----------+------+----------+------+-----------+ | 2 | 孟洋 | 男 | null | 10 | 1 | 1 | 一年级 | | 4 | 爱德 | 男 | 郑州 | 18 | 2 | 2 | 二年级 | | 1 | 超哥 | 男 | 杭州 | 20 | 2 | 2 | 二年级 | | 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 | 3 | 三年级 | +------+--------+------+-----------+------+----------+------+-----------+ ``` 可以清晰的看到, students表中id=5的数据记录没有查询出来. - 全外连接可以完整显示两个表的全部记录.
在mysql中只能使用union来连接左外连接和右外连接来实现全外连接. ```sql -- 实现全外连接 select * from students left join grades on students.grade_id = grades.id union select * from students right join grades on students.grade_id = grades.id; -- 得到 +------+--------+------+-----------+------+----------+------+-----------+ | id | name | sex | city | age | grade_id | id | grade | +------+--------+------+-----------+------+----------+------+-----------+ | 1 | 超哥 | 男 | 杭州 | 20 | 2 | 2 | 二年级 | | 2 | 孟洋 | 男 | null | 10 | 1 | 1 | 一年级 | | 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 | 3 | 三年级 | | 4 | 爱德 | 男 | 郑州 | 18 | 2 | 2 | 二年级 | | 5 | 旭神 | 女 | 阿拉罕 | 88 | 4 | NULL | NULL | +------+--------+------+-----------+------+----------+------+-----------+ ``` > 注意 union all 关键字是显示全部的记录, union 会删除掉相同的记录. **(7)、子查询 ✅** 子查询其实就是使用一个查询语句的结果作为另一个查询语句的条件, 再进行查询. ```sql -- 子临时表中只有一个字段的时候 select * from students where grade_id = (select id from grades where id=1); -- 得到 +----+--------+------+------+------+----------+ | id | name | sex | city | age | grade_id | +----+--------+------+------+------+----------+ | 2 | 孟洋 | 男 | null | 10 | 1 | +----+--------+------+------+------+----------+ -- 子临时表中多个字段的时候 select * from students where grade_id in (select id from grades where id>1); -- 得到 +----+--------+------+-----------+------+----------+ | id | name | sex | city | age | grade_id | +----+--------+------+-----------+------+----------+ | 1 | 超哥 | 男 | 杭州 | 20 | 2 | | 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 | | 4 | 爱德 | 男 | 郑州 | 18 | 2 | +----+--------+------+-----------+------+----------+ ``` 我们可以看到, 其实子查询需要用到两个select, 所以效率会低.一般我们多用连接查询.
子查询的时候我们可以使用例如in、not in、any、all、exists、not exists等关键字来进行条件判断. > 子查询的select语句一定要加小括号.
> 使用exits, 内层查询不返回查询记录, 而是true和false; true可以继续查询. not exists同理. **(8)、分页查询 ✅** 分页这个概念, 做过网页的同学们肯定不陌生, 那我们数量过于巨大的时候, 我们就要考虑分页展示数据.
在数据库中的使用很简单, 就是使用limit字段来进行分页.
在limit后面跟两个参数, 第一个是开始的行数, 第二个是每页展示的条数. ```sql -- 第1页, 每页两条; select * from students limit 0,2; -- 得到 +----+--------+------+--------+------+----------+ | id | name | sex | city | age | grade_id | +----+--------+------+--------+------+----------+ | 1 | 超哥 | 男 | 杭州 | 20 | 2 | | 2 | 孟洋 | 男 | null | 10 | 1 | +----+--------+------+--------+------+----------+ -- 第2页, 每页两条; select * from students limit 2,2; -- 得到 +----+--------+------+-----------+------+----------+ | id | name | sex | city | age | grade_id | +----+--------+------+-----------+------+----------+ | 3 | 梦佳 | 女 | 山沟沟 | 18 | 3 | | 4 | 爱德 | 男 | 郑州 | 18 | 2 | +----+--------+------+-----------+------+----------+ ``` 我们可以找到一个规律, 当我们知道第a页, 每页b条的时候, 我们就可以返回对应的数据了. ```sql select * from students limit (a-1)*b, b; ``` 很简单的我们就实现了分页的功能.
同时, 我们在查询的时候还可以用where、order by等关键字进行组合查询. > 注意:
> 一般数据量比较大的时候, 我们需要利用主键约束或者唯一索引进行分页.
> 同时比如我们做排行的时候, 需要对数据按照一定的规则排名. **(9)、联合查询 ✅** 联合查询就是将两个表中的相同数据叠加返回. 我们来看下面的例子: ```sql select * from students union select * from grades; -- 报错 ERROR 1222 (21000): The used SELECT statements have a different number of columns ``` 我们使用联合查询需要有两个及以上的字段相同.
首先我们再创建一张pet表; ```sql -- 创建pet create table pet (id int, name varchar(10),age int(10),owner varchar(20)); insert into pet values(1,"狗狗", 2, "超哥"); insert into pet values(2,"猫猫",3, "梦佳"); -- 得到 +------+--------+------+--------+ | id | name | age | owner | +------+--------+------+--------+ | 1 | 狗狗 | 2 | 超哥 | | 2 | 猫猫 | 3 | 梦佳 | +------+--------+------+--------+ ``` 接下来我们联合查询students表和pet表. ```sql -- 查询人和宠物的年龄 select name,age from students union select name,age from pet; -- 得到 +--------+------+ | name | age | +--------+------+ | 超哥 | 20 | | 孟洋 | 10 | | 梦佳 | 18 | | 爱德 | 18 | | 旭神 | 88 | | 狗狗 | 2 | | 猫猫 | 3 | +--------+------+ -- 以下查询会成功吗? select name,age from students union select name from pet; -- 报错 select name from students union select name from pet; -- 成功 select name from students union select age from pet; -- 成功 +--------+ | name | +--------+ | 超哥 | | 孟洋 | | 梦佳 | | 爱德 | | 旭神 | | 2 | | 3 | +--------+ ``` > 注意:
> 所有单个查询结果应该具有相等的列数.
> 列名以第一个查询的为主.
> 联合查询使用union, 默认是union distinct, 不重复.
> 使用union all可以查询全部记录, 可以重复.
**(10)、函数查询 ✅**7 使用聚合函数进行数据查询. ```sql -- 使用count() select count(*) from students; -- 得到 +----------+ | count(*) | +----------+ | 5 | +----------+ -- 使用sum() select sum(age) as age_sum from students; -- 得到 +---------+ | age_sum | +---------+ | 154 | +---------+ ``` 这里就不列举具体的函数了, 我们可以使用SQL的函数计算和查询我们需要的数据. 到这里, 我们的查询方法也基本讲完了, 很多种查询方法都可以相互组合使用.
具体需求具体分析, 要很熟练的使用SQL查询只有通过大量的练习和实战. --- 文章太长, 我们下一篇见. [「新生代农民工」之前端工程师必会的数据库-MySQL(下)](https://juejin.cn/post/6997271543931732004/) **文章粗浅, 望诸位不吝您的评论和点赞~**
**注: 本文系作者呕心沥血之作, 转载须声明**