MySQL数据库

于 2021-12-10 16:07:21 发布
阅读量1.5k 收藏 4
点赞数
文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43915382/article/details/121858780
版权

MySQL数据库

以前，在开发程序时，我们会把很多的数据和信息存储到某个文件夹中的文件中，例如：user.txt 、 db.xlsx等。
现在，有那么一个叫：数据库管理系统（DBMS，Database Management System）的软件，可以帮助我们实现对文件夹中的文件进行操作，而我们只要学习DBMS能识别的指令，就能控制它去帮助我们实现的文件和文件夹的处理。例如:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3WCHLdFX-1639123505641)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124201228804.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MQDqXLTq-1639123505643)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124201257622.png)]

数据库管理系统（DBMS)专注于帮助开发者解决数据存储的问题，这样开发者就可以把主要精力放在实现业务功能上了。

业内有很多的的数据库管理系统产品，例如∶

  • MySQL，原来是sun公司，后来被甲骨文收购。现在互联网企业几乎都在使用。【免费＋收费】
  • Oracle，甲骨文。收费，一般国企、事业单位居多。【收费】
  • Microsoft SQL Server，微软。【收费】
  • DB2，IBM。【免费＋收费】
  • SQLite，D.Richard Hipp个人开发。【免费】.
  • Access，微软。【收费】
  • PostgreSQL，加州大学伯克利分校。【免费】
  • 等众多…

由于各大公司都是使用MySQL，所以我们课程主要给大家讲解MySQL数据库。

在项目开发中想要基于MySQL来进行数据存储，大致应该怎么做呢？

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mU0h1qsk-1639123505644)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124200914326.png)]

本系列的MySQL模块会分为5部分来讲解：

  • MySQL入门，安装和快速应用Python实现数据库的操作。
  • 必备SQL和授权，学习 更多必备的指令让数据库实现更多业务场景。
  • SQL强化和实践，强化练习必备"指令”(项目开发写的最多)。
  • 索引和函数以及存储过程，掌握常见性能提升手段以及那些应用不是很频繁却又需了解的知识点。
  • Python操作MySQL和应用，侧重点在于Python开发，让大家了解Python开发中必备的实战应用，例如︰锁、事务、数据库连接池等。

一、MySQL入门

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2skcTQU4-1639123505645)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124200914326.png)]课程目标：学习安装和快速应用Python实现数据库的操作。

课程概要：

  • 安装 & 配置 & 启动
    • win
    • mac
  • 数据库 管理（类比文件夹）
  • 表 管理 （类比文件夹下的Excel文件）
  • 数据行 管理（类比Excel文件中的数据行）
  • Python操作MySQL及相关安全的问题

1. 安装 & 配置 & 启动

1.1 win系统

第1步：下载安装
第2步：解压至任意文件夹

D:\Program Files\mysql-5.7.31-winx64

第3步：创建配置文件

在MySQL的安装目录下创建my.ini的文件，作为MySQL的配置文件。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9J7CGTvE-1639123505646)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124211202288.png)]

其实，MySQL的配置文件可以放在很多的目录。

强烈建议把配置文件放在MySQL安装目录下，这样以后电脑上想要安装多个版本的MySQL时，配置文件可以相互独立不影响。

注意:如果你电脑的上述其他目录存在MySQL配置文件，建议删除，否则可能会影响MySQL的启动。

第4步：初始化

>>> "D:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --initialize-insecure

初始化命令在执行时，会自动读取配置文件并执行初始化，此过程主要会做两件事：

  • 自动创建data目录，以后我们的数据都会存放在这个目录。
  • 同时创建建必备一些的数据，例如默认账户root(无密码)，用于登录MySQL并通过指令操作MySQL。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZFJRBrI7-1639123505647)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124213230171.png)]

第5步：启动

启动MySQL常见的有两种方式：

  • 临时启动（不建议）

    >>> "D:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe"

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ObghK4aX-1639123505648)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124214106688.png)]

    注意：此时程序会挂起，内部就是可以接收客户端发来的MySQL指令，关闭窗口或Ctrl+c就可以停止运行。

    这种启动方式每次开机或想要开启都需要手动执行一遍命令比较麻烦。

  • 制作windows服务，基于windows服务管理。

    一定要以管理员身份打开cmd

    >>> "D:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --install mysql57

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0Obn5NT4-1639123505649)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124220045855.png)]

    创建好服务之后，可以通过命令 启动和关闭服务，例如：

    >>> net start mysql57
>>> net stop mysql57

    也可以在windows的服务管理中点击按钮启动和关闭服务。例如：

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RxPAFufR-1639123505650)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124220252318.png)]

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-eWhhdvbX-1639123505651)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124220320959.png)]

    以后不再想要使用windows服务了，也可以将制作的这个MySQL服务删除。

    >>> "D:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --remove mysql57
第6步 测试连接MySQL

安装并启动MySQL之后，就可以连接MySQL来测试是否已正确安装并启动成功。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ChUQPdUt-1639123505652)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124220819606.png)]

以后在开发时，肯定是要用Python代码来连接MySQL并且进行数据操作（后面学）。
在安装MySQL时，其实也自动安装了一个工具（客户端)，让我们快速实现连接MySQL并发送指令。

mysqld.exe是MySQL的一个服务。
mysql.exe相当于提供了一个客户端，连接MySQL的一个工具。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cVSRzKhJ-1639123505653)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124222656502.png)]

其中：

  • -h 127.0.0.1 表示你要连接的MySQL的IP地址：因为数据库在本机所以就连接127.0.0.1

  • -p 3306 表示你要连接的MySQL的端口：这个在my.ini配置文件中配置过

  • -u root：默认用户名是root，没有密码

  • -p：表示密码，不用在后面写，点回车后会提示输入密码，默认无密码

之后就连接了MySQL，就可以发送指令进行操作了。

  • show databases;：查看MySQL默认都有哪些数据库
  • exit;：发送指令推出数据库

注意：添加bin目录到环境变量，每次运行命令时，就不用再重新输入绝对路径了。以上述例子为例，直接输入mysql.exe -h 127.0.0.1 -P 3306 -u root -p就可以了。

1.2 MAC系统

1.3 关于配置文件

上述的过程中，我们在配置文件中只添加了很少的配置。

其实，配置项有很多，而哪些配置项都有默认值，如果我们不配置，MySQL则自动使用默认值。

1.4 关于密码

1.4.1 设置和修改root密码

在windows系统中模块默认root账户是没有密码的，如果想要为账户设定密码，可以在利用root账户登录成功之后，执行：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uKPhJ6q2-1639123505654)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211125195922251.png)]

1.4.2 忘记root密码

如果忘记了MySQL账户的密码。

  • 修改配置文件my.ini，在[mysqld]节点下添加skip-grant-tables=1

    [mysqld]
...
skip-grant-tables=1
...

  • 重启MySQL，再次登录时，不需要密码直接可以进去了

    net stop mysql57
net start mysql57

    重启后，无需密码就可以进入。

    >>> mysql -u root -p

  • 进入数据库后执行修改密码命令

    use mysql;
update user set authentication_string = password('新密码'),password_last_changed=now() where user='root';

  • 退出并再次修改配置文件，删除[mysqld]节点下添加skip-grant-tables=1

    [mysqld]
...
# skip-grant-tables=1
...

  • 再次重启，以后就可以使用新密码登录了。

2. 数据库管理

安装上数据库之后，就需要开始学习指令了，通过指令让MySQL去做出一些文件操作。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YTTr28F4-1639123505655)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124201228804.png)]

如果将数据库管理系统与之前的文件管理作类比的话：

数据库管理系统文件管理
数据库文件夹
数据表文件夹下的excel文件

2.1 内置客户端操作

当连接上MySQL之后，执行如下指令（一般称为SQL语句），就可以对MySQL的数据进行操作。

  • 查看当前所有的数据库：show databases;

  • 创建数据库：create database 数据库名 DEFAUIT CHARSET utf8 COLLATE utf8_general_ci;

    create database day25db DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

  • 删除数据库：drop database 数据库名;

  • 进入数据（进入文件）: use 数据库名;

示例：

# 1.登录MySQL
C:\Users\lirenjie>mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

# 2.查看当前数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| rjdb               |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

# 3.删除数据库
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)

# 4.查看当前数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| rjdb               |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

# 5.创建数据库
mysql> create database db1 default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

# 6.查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mysql              |
| performance_schema |
| rjdb               |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

# 7.进入数据库
mysql> use mysql;
Database changed

# 8.进入mysql数据库（文件夹），查看此数据库下的数据表。
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
31 rows in set (0.00 sec)

# 9.退出数据库
mysql> exit;
Bye

2.2 Python代码操作

无论通过何种方式去连接MySQL，本质上发送的指令都是相同的，只是连接的方式和操作形式不同而已。

当连接上MySQL之后，执行如下指令，就可以对MySQL的数据进行操作。（同上述过程）

  • 查看当前所有的数据库show databases;
  • 创建数据库：create database数据库名 default charset utf8 collate utf8_general_ci;
  • 删除数据库：drop database 数据库名;
  • 进入数据（进入文件）： use数据库;

想要使用Python操作MySQL需要安装第三方模块：

pip install pymysql

安装完成后，就可以编写代码：

import pymysql

# 连接MySQL
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', charset='utf8')
cursor = conn.cursor()

# 1.查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果（在查看数据库、数据表都用cursor.fetchall()来得到结果）
result = cursor.fetchall()
print(result)  # (('information_schema',), ('db1',), ('mysql',), ('performance_schema',), ('rjdb5',), ('rjdb6',), ('sys',))

# 2.创建数据库（新增、删除、修改都要使用conn.commit()）
# 发送指令
cursor.execute("create database rjdb default charset utf8 collate utf8_general_ci")
conn.commit()

# 3.查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result)  # (('information_schema',), ('db1',), ('mysql',), ('performance_schema',), ('rjdb',), ('rjdb5',), ('rjdb6',), ('sys',))

# 4.删除数据库
# 发送指令
cursor.execute("drop database rjdb6")
conn.commit()

# 5.查看数据库
# 发送指令
cursor.execute("show databases")
# 获取指令的结果
result = cursor.fetchall()
print(result)  # (('information_schema',), ('db1',), ('mysql',), ('performance_schema',), ('rjdb',), ('rjdb5',), ('sys',))

# 6.进入数据库，查看数据表
cursor.execute("use mysql")
cursor.execute("show tables")
result = cursor.fetchall()
print(result)  # (('columns_priv',), ('db',), ('engine_cost',), ('event',),...)
 
# 关闭连接
cursor.close()
conn.close()

总结：

  • 在查看数据库、查看数据表的时候都用cursor.fetchall()来得到结果
  • 在创建、删除、修改数据库的时候最后都执行conn.commit()

快速记忆：执行sql语句、查看数据库数据表的时候都是光标执行；创建、删除、修改数据库的时候光标不执行。

3. 数据表管理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Us9HJ2QB-1639123505655)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124201228804.png)]

接下来，我们先学习 数据表 （文件夹下的文件）相关操作的指令。

其实，在数据库中创建数据表 和 创建 Excel非常类似，需要指定：表名、列名、列类型（整型、字符串或其他）。

3.1 内置客户端操作

数据表常见操作的指令：

  • 进入数据库use 数据库;，查看当前所有表：show tables;查看某个表：desc 表名

  • 创建表结构

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Fz7tdbHL-1639123505656)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211125223008948.png)]

    create table 表名(
    列名	类型，
    列名  类型，
    列名  类型
)default charset=utf8;

    create table tb1(
    id int,
    name varchar(16)
)default charset=utf8;

    create table tb2(
    id int,
    name varchar(16) not null,  -- 不允许为空
    email varchar(32) null,  -- 允许为空（默认）
    age int
)default charset=utf8;

    create table tb3(
    id int,
    name varchar(16) not null,  -- 不允许为空
    email varchar(32) null,  -- 允许为空
    age int default 3  -- 插入数据时，如果不给age列设置值，默认值：3
)default charset=utf8;

    create table tb4(
    id int primary key,  -- 主键（不允许为空、不能重复）
    name varchar(16) not null,  -- 不允许为空
    email varchar(32) null,  -- 允许为空（默认）
    age int default 3  -- 插入数据时，如果不给age列设置值，默认值：3
)default charset=utf8;

    主键一般用于表示当前这条数据的ID编号（类似于人的身份证)，需要我们自己来维护一个不重复的值，比较繁琐。所以，在数据库中一般会将主键和自增结合。

    create table tb5(
    id int not null auto_increment primary key,  -- 不允许为空、自增、主键
    name varchar(16) not null,  -- 不允许为空
    email varchar(32) null,  -- 允许为空（默认）
    age int default 3  -- 插入数据时，如果不给age列设置值，默认值：3
)default charset=utf8;

    注意：一个表中只能有一个自增列【自增列一般都是主键】。

  • 删除表drop table 表名;

  • 清空表delete from 表名;或truncate table 表名;（速度快、无法回滚撤销等）

    • delete from 表名;：执行速度较慢，但是可以恢复；
    • truncate table 表名;：执行速度快，但是不可恢复；

  • 修改表

    • 添加列

      alter table 表名 add 列名 类型；
alter table 表名 add 列名 类型 DEFAULT 默认值；
alter table 表名 add 列名 类型 not null default 默认值；
alter table 表名 add 列名 类型 not null primary key auto_increment;

    • 删除列

      alter table 表名 drop column 列名;

    • 修改列 类型

      alter table 表名 modify column 列名 类型;

    • 修改列 类型 + 名称

      alter table 表名 change 原列名 新列名 新类型;

      alter table tb change id id int not null;
alter table tb change id id int not null default 5;
alter table tb change id id int not null primary key auto_increment;
alter table tb change id id int;  -- 允许为空，删除默认值，删除自增。

    • 修改列 默认值

      alter table 表名 alter 列名 set default 1000;

    • 删除列 默认值

      alter table 表名 alter 列名 drop default;

    • 添加主键

      alter table 表名 add primary key(列名);

    • 删除主键

      alter table 表名 drop primary key;

  • 常见列类型

    create table 表(
	id int,
	name varchar(16)
)default charset=utf8;

    • int[(m)][unsigned][zerofill]

      int              表示有符号，取值范围:-2147483648 ～ 2147483647
int unsigned     表示无符号，取值范围:0 ～ 4294967295
int(5)zerofill   仅用于显示，当不满足5位时，按照左边补0，例如:00002;满足时，正常显示。

      mysql> create table L1(id int, uid int unsigned, zid int(5)zerofill)defult charset=utf8;
Query OK, 0 rows affected (0.58 sec)

mysql> insert into L1(id, uid, zid) values(1,2,3);
Query OK, 1 row affected (0.13 sec)

mysql> insert into L1(id, uid, zid) values(2147483641, 4294967294, 300000);
Query OK, 1 row affected (0.12 sec)

mysql> select * from L1;
+------------+------------+--------+
| id         | uid        | zid    |
+------------+------------+--------+
|          1 |          2 |  00003 |
| 2147483641 | 4294967294 | 300000 |
+------------+------------+--------+
2 rows in set (0.00 sec)

mysql> insert into L1(id,uid,zid) values(214748364100, 4294967294, 300000);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql>

      上述代码：第一行定义了 zid 列不满5位左边补零；第19行插入新的数据时超过了int最大的长度，因此报错了。

    • tinyint[(m)][unsigned][zerofill]：短整型 用法同int

      有符号，取值范围：-128 ~ 127.
无符号，取值范围：0  ~ 255.

    • bigint[(m)][unsigned][zerofill]：长整型 用法同int

      有符号，取值范围：-9223372036854775808 ～ 9223372036854775807
无符号，取值范围：0 ~ 18446744073709551615

    • decimal[(m[,d])] [unsigned] [zerofill]

      准确的小数值，m是数字总个数（负号不算），d是小数点后个数,m-d就是整数部分位数。m最大值65，d最大值30。

例如：
create table L2(
	id int not null primary key auto_increment,
	salary decimal(8,2)
)default charset=utf8;

      mysql> create table L2(
    -> id int not null primary key auto_increment,
    -> salary decimal(8,2)
    -> )default charset=utf8;
Query OK, 0 rows affected (0.70 sec)

mysql> insert into L2(salary) values(1.28);
Query OK, 1 row affected (0.13 sec)

mysql> insert into L2(salary) values(5.289);
Query OK, 1 row affected, 1 warning (0.13 sec)

mysql> insert into L2(salary) values(5.282);
Query OK, 1 row affected, 1 warning (0.16 sec)

mysql> insert into L2(salary) values(512132.28);
Query OK, 1 row affected (0.04 sec)

mysql> insert into L2(salary) values(512132.283);
Query OK, 1 row affected, 1 warning (0.13 sec)

mysql> select * from L2;
+----+-----------+
| id | salary    |
+----+-----------+
|  1 |      1.28 |
|  2 |      5.29 |
|  3 |      5.28 |
|  4 | 512132.28 |
|  5 | 512132.28 |
+----+-----------+
5 rows in set (0.00 sec)

mysql> insert into L2(salary) values(5121321.283);  -- 整数位数超了
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql>

    • float[(m,d)] [unsigned] [zerofill]

      单精度浮点数 32位，非准确小数值，m是数字总个数，d是小数点后个数。

    • double[(m,d)] [unsigned] [zerofill]

      双精度浮点数 64位，非准确小数值，m是数字总个数，d是小数点后个数。

    • char(m)

      定长字符串，m代表字符串的长度，最多可容纳255个字符。

定长的体现︰即使内容长度小于m，也会占用m长度。例如: char(5)，数据是: yes，底层也会占用5个字符;如果超出m长度限制（默认MySQL是严格模式，所以会报错)。
	如果在配置文件中加入如下配置，
		sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
	保存并重启，此时MySQz则是非严格模式，此时超过长度则自动截断（不报错)。。

注意∶默认底层存储是固定的长度〈不够则用空格补齐)，但是查询数据时，会自动将空白去除。如果想要保留空白，在sql-mode中加入PAD_CHAR_TO_FULL_LENGTH即可。
查看模式sql-mode，执行命令: show variables like 'sql_mode ' ;

一般适用于:固定长度的内容。

create table L3(
	id int not null primary key auto_increment,
	name varchar(5),
	depart char(3)
)default charset=utf8;

    • varchar(m)

      变长字符串,m代表字符串的长度，最多可容纳65535个字节。

变长的体现︰内容小于m时，会按照真实数据长度存储;如果超出m长度限制（(默认MysQL是严格模式，所以会报错)。
	如果在配置文件中加入如下配置，
		sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
	保存并重启，此时MySQL则是非严格模式，此时超过长度则自动截断（不报错)。

例如∶
create table L3(
	id int not null primary key auto_increment,
	name varchar(5),
	depart char(3)
) default charset=utf8;

      mysql> create table L3(id int not null primary key auto_increment ,namevarchar(5),depart char(3))default charset=utf8;
Query oK, 0 rows affected (0.03 sec)

-- 插入多行
mysql> insert into L3(name,depart) values("wu","WU"),("wupei","ALS");
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0  warnings: 0

mysql> select * from L3;
+----+-------+--------+
| id | name  | depart |
+----+-------+--------+
|  1 | wu    | WU     |
|  2 | wupei | ALS    |
+----+-------+--------+
2 rows in set (0.00 sec)

-- 非严格模式下，不会报错。
mysql> insert into L3(name,depart) values("wupeiqi","ALS");
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> insert into L3(name,depart) values("wupei","ALSB");
ERROR 1406 (22001): Data too long for column 'depart' at row 1
mysql>

-- 如果sql-mode 中加入了 PAD_CHAR_TO_FULL_LENGTH ，则查询时char时空白会保留。
mysql> select name,length (name),depart,length(depart) from L3;
+-------+--------------+--------+----------------+
| name  | length(name) | depart | length(depart) |
+-------+--------------+--------+----------------+
| wu    |            2 | WU     |              3 |
| wupei |            5 | ALS    |              3 |
+-------+--------------+--------+----------------+
4 rows in set (0.00 sec)
mysql>

    • text

      text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 - 1)个字符。

一般情况下，长文本会用text类型。例如:文章、新闻等。

      create table L4(
	id int not null primary key auto_increment,
	title varchar(128),
	content text
)default charset=utf8 ;

    • mediumtext

      A TEXT column with a maximum length of 16,777,215(2**24 - 1) characters.

    • longtext

      A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32)

    • datetime

      YYYY-MM-DD HH:MM:SS (1000-01-01 00:00:00/9999-12-31 23:59:59)

    • timestamp

      YYYY-MM-DD HH:MM:SS (1970-01-01 00:00:00/2037年)

      对于TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC(世界标准时间)进行存储，查询时将其又转化为客户端当前时区进行返回。

对于DATETIME，不做任何改变，原样输入和输出。

      mysql> create table L5(
    -> id int not null primary key auto_increment,
    -> dt datetime,
    -> tt timestamp
    -> )default charset=utf8;
Query OK, 0 rows affected (0.26 sec)

mysql> insert into L5(dt,tt) values("2025-11-11 11:11:44", "2025-11-11 11:11:44");
Query OK, 1 row affected (0.13 sec)

mysql> select * from L5;
+----+---------------------+---------------------+
| id | dt                  | tt                  |
+----+---------------------+---------------------+
|  1 | 2025-11-11 11:11:44 | 2025-11-11 11:11:44 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set, 1 warning (0.00 sec)
-- "CST"指的是MySQL所在主机的系统时间,是中国标准时间的缩写，China Standard Time UT+8:00

mysql> set time_zone='+0:00';
Query OK,0 rows affected (0.00 sec)

mysql> show variables like '%time_zone%';
+------------------+--------+
| variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +00:00 |
+------------------+--------+
2 rows in set (0.01 sec)

mysql> select * from L5;
+----+---------------------+---------------------+
| id | dt                  | tt                  |
+----+---------------------+---------------------+
| 1  | 2025-11-11 11:11:44 | 2025-11-11 03:11:44 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

    • data：年月日

      YYYY-MM-DD (1000-01-01/9999-12-31)

    • time：时分秒

      HH:MM:SS ('-838:59:59'/'838:59:59')

3.2 MySQL代码操作

基于Python去连接MySQL之后，想要进行数据表的管理的话，发送的指令其实都是相同的。

4. 数据行

当前数据库和数据表创建完成之后，就需要对数据表中的内容进行：增、删、改、查了。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9Y0Sjz8l-1639123505657)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211126203050556.png)]

4.1 内置客户端操作（非常重要）

数据行操作的相关SQL语句（指令）如下：

  • 新增数据

    insert into 表名(列名，列名，列名) values(对应列的值，对应列的值，对应列的值);

    insert into tb1(name,password) values('lrj','123123');
insert into tb1(name,password) values('lrj','123123'),('zjx','123');


-- 如果表中只有2列
insert into tb1 values('lrj','123123'),('zjx','123');

  • 删除数据

    delete from 表名;
delete from 表名 where 条件;

    delete from tb1;
delete from tb1 where name="lrj";
delete from tb1 where name="lrj" and password="123";
delete from tb1 where id>9;

  • 修改数据

    update 表名 set 列名=值;
update 表名 set 列名=值 where 条件;

    update tb1 set name="lrj";  -- name那一列都改为了"lrj"
update tb1 set name="lrj" where id=1;

update tb1 set age=age+1;  -- 不能是字符串型
update tb1 set age=age+1 where id=2;

update L3 set name=concat(name,"db");
update L3 set name=concat(name,"123") where id=2;  -- concat()可以拼接字符串

  • 查询数据

    select * from 表名;
select 列名,列名,列名 from 表名;
select 列名,列名 as 别名,列名 from 表名;
select * from 表名 where 条件;

    select * from tb1;
select id,name,age from tb1;
select id,name as N,age, from tb1;
select id,name as N,age,111 from tb1;  -- 111不存在的话就新建列名111然后每一个值都是111

select * from tb1 where id = 1;
select * from tb1 where id > 1;
select * from tb1 where id < 1;
select * from tb1 where name="lrj" and password="123";

4.2 Python代码操作

# 增删查改python代码实现
import pymysql

# 连接MySQL
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', charset='utf8', db='rjdb')  # db直接进入指定数据库
cursors = conn.cursor()

# 增加数据
sql = "insert into L1(id,uid,zid) values(2,971986,411)"
cursors.execute(sql)
conn.commit()

# 删除数据
sql = "delete from L1 where id=2"
cursors.execute(sql)
conn.commit()

# 修改数据
sql = "update L1 set uid=666 where id=1"
cursors.execute(sql)
conn.commit()

# 查找数据
sql = "select * from L1 where uid>10"
cursors.execute(sql)
data = cursors.fetchone()  # cursors.fetchall()
print(data)


# 关闭连接
cursors.close()
conn.close()

在真正做项目开发时，流程如下：

  1. 根据项目功能来设计相应的 数据库 & 表结构 （不会经常变动，在项目设计之初就确定好了）
  2. 操作表结构中的数据，来达到实现业务逻辑的目的。

例如：实现一个用户管理系统。

先使用MySQL自带的客户端创建相关 数据库和表结构（相当于创建好excel结构）。

create database userdb default charset utf8 collate utf8_general_ci;

create table users(
	id int not null primary key auto_increment,
	name varchar(32),
	password varchar(64)
)default charset=utf8;

再在程序中执行编写相应的功能实现 注册、登录 等功能。

# 用户管理系统，实现用户注册和登录功能
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='userdb')
cursor = conn.cursor()


def register():
    
    # 已经提前创建好了数据库userdb中的数据表users，而且规定好了列名及其类型。
    # 连接数据库userdb
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='userdb')
    cursor = conn.cursor()
    
    un_corr = False
    pw_corr = False
    print("欢迎注册！")
    
    while not un_corr:
        user_name = input("请输入需要注册的用户名：")

        # 查看用户名是否已经存在
        sql = "select name from users where name='{}'".format(user_name)
        cursor.execute(sql)
        result = cursor.fetchone()

        # if user_name in name_data:
        if result:
            print("用户名已存在！请重新输入。")
        else:
            un_corr = True
    
    while not pw_corr:
        password = input("请输入密码：")
        password2 = input("再次输入密码：")
        if password == password2:
            pw_corr = True
        else:
            print("两次密码不一致，请重新输入。")
    
    
    # 将新注册的用户名密码数据插入数据库中
    sql = "insert into users(name, password) values('{}', '{}')".format(user_name, password)  # 插入新的列
    cursor.execute(sql)
    conn.commit()
    print("注册成功！")

    # 关闭数据库
    conn.close()
    cursor.close()


def login():
    # 连接数据库userdb
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='userdb')
    cursor = conn.cursor()

    user_name = input("请输入用户名：")
    user_pw = input("请输入密码：")

    sql = "select name, password from users where name='{}' and password='{}'".format(user_name, user_pw)
    cursor.execute(sql)
    data = cursor.fetchone()

    if data:
        print("登录成功！")
    else:
        sql = "select name from users where name='{}'".format(user_name)
        cursor.execute(sql)
        name_data = cursor.fetchone()
        if not name_data:
            print("用户名不存在！")
        else:
            print("密码错误！")

    # 关闭数据库
    conn.close()
    cursor.close()


def run():
    choice = eval(input("1、注册 2、登录："))
    if choice == 1:
        register()
    elif choice == 2:
        login()
    else:
        print("输入错误")


if __name__ == "__main__":
    run()

5. 关于SQL注入

假如，你开发了一个用户认证的系统，应该用户登录成功后才能正确的返回相应的用户结果。

import pymysql

# 输入用户名和密码
user = input("请输入用户名：")
pwd = input("请输入密码：")

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd= 'root123', charset="utf8", db='userdb')
cursor = conn.cursor()

# 基于字符串格式化来拼接SQL语句
# sql = "select * from users where name='alex' and password='123'"
sql = "select * from users where name='{}' and password='{}'".format(user, pwd)
cursor.execute(sql)

result = cursor.fetchone()
print(result)  # None,不是None

cursor.close()
conn.close()

如果用户在输入user时，输入：'or 1=1 -- ，这样即使用户输入的密码不存在，也会通过验证。

在python中如何避免SQL注入？

切记：SQL语句不要使用python的字符串格式化，而是使用pymysql中的execute方法。

import pymysql

# 输入用户名和密码
user = input("请输入用户名：")
pwd = input("请输入密码：")

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd= 'root123', charset="utf8", db='userdb')
cursor = conn.cursor()

# 用execute方法列表
cursor.execute("select * from user where name=%s and password=%s", [user, pwd])
# 或者
# cursor.execute("select * from user where name=%(n1)s and password=%(n2)s", {"n1": user, "n2": pwd})

总结

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BYTroup2-1639123505658)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211124200914326.png)]

除了【第5步用户授权】以外，现在使用的是默认root账户（拥有最大权限)，上述所有的过程已讲解完毕。

本节内容大家需要掌握:

  • 安装和启动MySQL.

  • SQL语句：

    • 数据库操作

    • 表操作

    • 数据行操作

  • 基于Python操作MySQL

  • 注意SQL注入的问题

作业

  1. 根据要求创建表结构并编写相应的SQL语句(基于MySQL自带客户端操作)

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cc3EIlxn-1639123505659)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211127153120051.png)]

    • 根据上述表的要求创建相应的数据和表结构（注意编码)。
    • 任意插入5条数据。
    • 将id>3的所有人的性别改为男。
    • 查询余额amount>1000的所有用户。
    • 让每个人的余额在自己原的基础上+1000。
    • 删除性别为男的所有数据。

二、必备SQL和表关系及授权

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-q7MSN0pi-1639123505660)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211129215308373.png)]

目标：掌握开发中最常见的SQL语句和表关系及授权相关知识点。

概要：

  • 必备SQL（8个必备）
  • 表关系
  • 授权

1. 必备SQL语句

上一节了解了最基础SQL语句：增删改查，其实还有很多必备SQL语句。

这一部分SQL语句都是围绕着 对表中的数据进行操作 的。

现在创建如下两张表：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bAJ7a53E-1639123505661)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211129215720001.png)]

create database day26 default charset utf8 collate utf8_general_ci;

create table depart(
    id int not null auto_increment primary key,
    title varchar(16) not null
)default charset=utf8;

create table info(
    id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int
)default charset=utf8;

1.1 条件（where）

根据条件搜索结果。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TlLrS5m2-1639123505662)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211129215720001.png)]

select * from info where age > 30;
select * from info where id > 1;
select * from info where id < 1;
select * from info where id >= 1;
select * from info where id != 1;
select * from info where id between 2 and 4;
select * from info where id >= 2 and id <=4;  -- 与前一条等效

select * from info where name ='lrj' and age = 19;
select * from info where name = 'alex' or age = 49;
select * from info where (name = '嘉豪' or email="pyyu@live.com" ) and age=49;

select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart);
-- select * from info where id in (1,2,3);

-- exists select * from depart where id=5:去查id=5存在不，如果存在，如果不存在
select * from info where exists (select * from depart where id=5);
select * from info where not exists (select * from depart where id=5);

-- 临时表 里面 再查age>10
select * from (select * from info where id>5) as T where T.age > 10;

1.2 通配符（like % _）

一般用于模糊搜索。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PuimXOyU-1639123505663)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211129215720001.png)]

-- %表示零个或多个任意字符
select * from info where name like "%豪%";
select * from info where name like "%rj";
select * from info where email like "%@live.com" ;
select * from info where name like "zhao%xin";
select * from info where name like "k%y";
select * from info where email like "wupeiqi%";

-- _表示一个任意字符
select * from info where email like "_@live.com";
select * from info where eail like "_irenjie@live.com";
select * from info where email like "__renjie@live.com";
select * from info where email like "__renjie_live.co_";

注意：数据量少，可以用；数据量大，不推荐。

1.3 映射（as）

想要获取的列。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VVWVOKDl-1639123505663)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211129215720001.png)]

select * from info;

select id, name            from info;
select id, name as NM      from info;
select id, name as NM, 123 from info;

-- 注意:少些select *，根据自己需求来获得列就行。

select
	id,
	name,
	666 as num,
	(select max(id) from depart) as mid, -- max/min/sum
	(select min(id) from depart) as nid, -- max/min/sum
	age
from info;

select
	id,
	name,
	(select title from depart where depart.id=info.depart_id) as xl
from info;

-- 注意:效率很低

select
	id,
	name,
	(select title from depart where depart.id=info.depart_id) as x1,
	(select title from depart where depart.id=info.id) as x2
from info;
+----+------------+------+------+
| id | name       | x1   | x2   |
+----+------------+------+------+
|  1 | lirenjie   | 开发 | 开发 |
|  2 | zhaojunxin | 开发 | 运营 |
|  3 | alex       | 运营 | 销售 |
|  4 | tony       | 开发 | NULL |
|  5 | kelly      | 销售 | NULL |
|  6 | james      | 开发 | NULL |
|  7 | 嘉豪       | 开发 | NULL |
+----+------------+------+------+
7 rows in set (0.16 sec)

case when then else end

select
	id,
	name,
	case depart_id when 1 then "第1部门" end v1,
	case depart_id when 1 then "第1部门" else "其他" end v2,
	case depart_id when 1 then "第1部门" when 2 then "第2部门" else "其他"
end v3,
	case when age<18 then "少年" end v4,
	case when age<18 then "少年" else "油腻男" end v5,
	case when age<18 then "少年" when age<30 then "青年" else "油腻男" end v6
from info;
+----+------------+---------+---------+---------+------+--------+--------+
| id | name       | v1      | v2      | v3      | v4   | v5     | v6     |
+----+------------+---------+---------+---------+------+--------+--------+
|  1 | lirenjie   | 第1部门 | 第1部门 | 第1部门 | NULL | 油腻男 | 青年   |
|  2 | zhaojunxin | 第1部门 | 第1部门 | 第1部门 | NULL | 油腻男 | 青年   |
|  3 | alex       | NULL    | 其他    | 第2部门 | 少年 | 少年   | 少年   |
|  4 | tony       | 第1部门 | 第1部门 | 第1部门 | NULL | 油腻男 | 青年   |
|  5 | kelly      | NULL    | 其他    | 其他    | NULL | 油腻男 | 油腻男 |
|  6 | james      | 第1部门 | 第1部门 | 第1部门 | NULL | 油腻男 | 油腻男 |
|  7 | 嘉豪       | 第1部门 | 第1部门 | 第1部门 | NULL | 油腻男 | 青年   |
+----+------------+---------+---------+---------+------+--------+--------+
7 rows in set (0.12 sec)

1.4 排序（order by…asc）

查询表的时候将查询到的数据进行排序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mXJ9BinD-1639123505664)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211129215720001.png)]

order by 列名 asc/desc

select * from info order by age desc;
select * from info order by age asc;

select * from info order by id desc;
select * from info order by id asc;

select * from info order by age asc, id desc;  -- 优先按照age从小到大，如果相同则按照id从大到小。

mysql> select * from info order by age asc, id desc;
+----+------------+---------------------+------+-----------+
| id | name       | email               | age  | depart_id |
+----+------------+---------------------+------+-----------+
|  3 | alex       | alex@live.com       |    9 |         2 |
|  7 | 嘉豪       | jiahao@live.com     |   19 |         1 |
|  2 | zhaojunxin | zhaojunxin@live.com |   19 |         1 |
|  1 | lirenjie   | lirenjie@live.com   |   19 |         1 |
|  4 | tony       | tony@live.com       |   29 |         1 |
|  6 | james      | james@live.com      |   49 |         1 |
|  5 | kelly      | kelly@live.com      |   99 |         3 |
+----+------------+---------------------+------+-----------+
7 rows in set (0.00 sec)


select * from info where id>10 order by age asc, id dese;
select * from info where id>6 or name like "%y" order by age asc, id desc;

1.5 取部分（limit,offset）

一般要用于获取部分数据。

limit offset

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cOOBiLtt-1639123505665)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211129215720001.png)]

select * from info limit 5;  -- 获取前5条数据
select * from info order by id desc limit 3;  -- 先排序，再获得前3条数据
select * from info where id > 3 order by id desc limit 3;

mysql> select * from info where id > 3 order by id desc limit 3;
+----+-------+-----------------+------+-----------+
| id | name  | email           | age  | depart_id |
+----+-------+-----------------+------+-----------+
|  7 | 嘉豪  | jiahao@live.com |   19 |         1 |
|  6 | james | james@live.com  |   49 |         1 |
|  5 | kelly | kelly@live.com  |   99 |         3 |
+----+-------+-----------------+------+-----------+
3 rows in set (0.00 sec)

select * from info limit 3 offset 2;  - 从id=3开始获取3条数据

mysql> select * from info limit 3 offset 2;  - 从id=3开始获取3条数据
+----+-------+----------------+------+-----------+
| id | name  | email          | age  | depart_id |
+----+-------+----------------+------+-----------+
|  3 | alex  | alex@live.com  |    9 |         2 |
|  4 | tony  | tony@live.com  |   29 |         1 |
|  5 | kelly | kelly@live.com |   99 |         3 |
+----+-------+----------------+------+-----------+
3 rows in set (0.00 sec)

数据库表中:1000条数据。

  • 第一页：select * from info limit 10 offset 0;
  • 第二页：select * from info limit 10 offset 10;
  • 第三页：select * from info limit 10 offset 20;
  • 第四页：select * from info limit 10 offset 30;
  • …

1.6 分组（group by,having）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-T1zOLapo-1639123505666)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211129215720001.png)]

select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;

select age,count(1) from info group by age;

mysql> select age,count(1) from info group by age;
+------+----------+
| age  | count(1) |
+------+----------+
|    9 |        1 |
|   19 |        3 |
|   29 |        1 |
|   49 |        1 |
|   99 |        1 |
+------+----------+
5 rows in set (0.00 sec)


select depart_id,count(id) from info group by depart_id;

mysql> select depart_id,count(id) from info group by depart_id;
+-----------+-----------+
| depart_id | count(id) |
+-----------+-----------+
|         1 |         5 |
|         2 |         1 |
|         3 |         1 |
+-----------+-----------+
3 rows in set (0.00 sec)


select depart_id,count(id) from info group by depart_id having count(id)>2;  -- 2次筛选用having

mysql> select depart_id,count(id) from info group by depart_id having count(id)>2;
+-----------+-----------+
| depart_id | count(id) |
+-----------+-----------+
|         1 |         5 |
+-----------+-----------+
1 row in set (0.12 sec)

select count(id) from info;
select max(id) from info;

mysql> select max(id) from info;
+---------+
| max(id) |
+---------+
|       7 |
+---------+
1 row in set (0.00 sec)

select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;

select age,name from info group by age;  -- 不建议
select * from info where id in (select max(id) from info group by age);

mysql> select * from info where id in (select max(id) from info group by age);
+----+-------+-----------------+------+-----------+
| id | name  | email           | age  | depart_id |
+----+-------+-----------------+------+-----------+
|  3 | alex  | alex@live.com   |    9 |         2 |
|  4 | tony  | tony@live.com   |   29 |         1 |
|  5 | kelly | kelly@live.com  |   99 |         3 |
|  6 | james | james@live.com  |   49 |         1 |
|  7 | 嘉豪  | jiahao@live.com |   19 |         1 |
+----+-------+-----------------+------+-----------+
5 rows in set (0.10 sec)

select age, count(id) from info group by age having count(id) > 2;
select age, count(id) from info where id > 4 group by age having count(id) > 2;  -- 聚合条件放在having后面

到目前为止SQL执行顺序：
	where
	group by
	having
	order by
	limit

mysql> select age, count(id) from info where id > 2 group by age having count(id) > 0 order by age desc limit 2;
+------+-----------+
| age  | count(id) |
+------+-----------+
|   99 |         1 |
|   49 |         1 |
+------+-----------+
2 rows in set (0.00 sec)

-- 要查询的表info
-- 条件 id > 2
-- 根据 age 分组
-- 对分组后的数据再根据聚合条件过滤 count(id)>0
-- 根据 age 从大到小排序
-- 获取 前2条数据

1.7 左右连表(left/right/inner join, on)

多个表可以连接起来进行查询。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dZLrtqAo-1639123505667)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211129215720001.png)]

展示用户信息&部门名称：

主表 left outer join 从表 on 主表.x = 从表.x  -- 两张表

-- 多张表
主表 left outer join 从表 on 主表.x = 从表.x left outer join 从表 on ...

select * from info left outer join depart on info.depart_id = depart.id;

select info.id, info.name, info.email, depart.title from info left outer join depart on info.depart_id = depart.id;

从表 right outer join 主表 on 主表.x = 从表.x

select info.id, info.name, info.email, depart.title from depart right outer join info on info.depart_id = depart.id;

为了更加直接的查看效果，我们分别再depart表和info表中额外插入一条数据。

insert into depart(title) values("运维");

这样一来，主从表就有区别：

  • info 主表，就以info数据为主，depart为辅，info中没有的depart中特有的部分就会删掉。

    select info.id, info.name, info.email, depart.title from info left outer join depart on info.depart_id = depart.id;

mysql> select info.id, info.name, info.email, depart.title from info left outer join depart on info.depart_id = depart.id;
+----+------------+---------------------+-------+
| id | name       | email               | title |
+----+------------+---------------------+-------+
|  1 | lirenjie   | lirenjie@live.com   | 开发  |
|  2 | zhaojunxin | zhaojunxin@live.com | 开发  |
|  4 | tony       | tony@live.com       | 开发  |
|  6 | james      | james@live.com      | 开发  |
|  7 | 嘉豪       | jiahao@live.com     | 开发  |
|  3 | alex       | alex@live.com       | 运营  |
|  5 | kelly      | kelly@live.com      | 销售  |
+----+------------+---------------------+-------+
7 rows in set (0.00 sec)

  • depart主表，就以depart数据为主，info为辅。

    select info.id, info.name, info.email, depart.title from info right outer join depart on info.depart_id = depart.id;

mysql> select info.id, info.name, info.email, depart.title from info right outer join depart on info.depart_id = depart.id;
+------+------------+---------------------+-------+
| id   | name       | email               | title |
+------+------------+---------------------+-------+
|    1 | lirenjie   | lirenjie@live.com   | 开发  |
|    2 | zhaojunxin | zhaojunxin@live.com | 开发  |
|    3 | alex       | alex@live.com       | 运营  |
|    4 | tony       | tony@live.com       | 开发  |
|    5 | kelly      | kelly@live.com      | 销售  |
|    6 | james      | james@live.com      | 开发  |
|    7 | 嘉豪       | jiahao@live.com     | 开发  |
| NULL | NULL       | NULL                | 运维  |
+------+------------+---------------------+-------+
8 rows in set (0.00 sec)

select * from info left outer join depart on ...
select * from depart left outer join info on ...

简写：select * from depart left join info on ...

-- 内连接：  表 inner join 表  on  条件     互相匹配，没有关联上的都删掉了
select * from info inner join depart on info.id = depart.id;

到目前为止SQL执行顺序：
	from
	join
	on
	where
	group by
	having
	select
	oder by
	limit

1.8 联合（上下连表union）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uYW7xTYN-1639123505668)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211129215720001.png)]

select id,title from depart
union
select id,name from info;


select id,title from depart
union
select email,name from info;
-- 列数需相同

select id from depart
union
select id from info;

-- 自动去重

select id from depart
union all
select id from info;

-- 保留所有

小结

到目前为止，已经掌握了如下相关指令（SQL语句）

  • 数据库
  • 数据表
  • 数据行
    • 增加
    • 删除
    • 修改
    • 查询（各种变着花样的查询）

2. 表关系

在开发项目时，需要根据业务需求去创建很多的表结构，以此来实现业务逻辑。

一般表结构有三种：

  • 单表，单独一张表就可以将信息保存。

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ch6qZwna-1639123505669)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211130151026229.png)]

  • 一对多，需要两张表来存储信息，且两张表存在一对多或多对一关系。

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TmrpGVEV-1639123505669)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211130151137546.png)]

  • 多对多，需要三张表来存储信息，两张单表+关系表，创造出两个单表之间的多对多关系。

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E37X3X79-1639123505670)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211130151332704.png)]

在上述的表：一对多的info.depart_id字段、多对多的boy_girl.boy_id、boy_girl.girl_id直接用整型存储就可以，因为他们只要存储关联表的主键ID即可。

在开发中往往还会为他们添加一个外键约束，保证某一个列的值必须是其他表中特定列已存在的值，例如：info.depart_id的值必须是depart.id中已存在的值。

后续外键添加方法

alter table 表名 add constraint 约束名 foreign key(当前表中约束的字段) references 主表表名(要约束的字段名);

一对多示例：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AsSviULA-1639123505671)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211130151137546.png)]

create table depart(
    id int not null auto_increment primary key,
    title varchar (16) not null
)default charset=utf8;


create table info(
    id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int not null,
    constraint fk_info_depart foreign key (depart_id) references depart(id)  -- 外键
) default charset=utf8;

如果表结构已经创建好了，额外想要增加外键：

alter table info add constraint fk_info_depart foreign key info(depart_id) references depart(id);

删除外键：

alter table info drop foreign key fk_info_depart;

多对多示例：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jl4LTrx1-1639123505672)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211130151332704.png)]

create table boy(
    id int not null auto_increment primary key,
    name varchar(16) not null
)default charset=utf8;

create table girl(
	id int not null auto_increment primary key,
    name varchar(16) not null
)default charset=utf8;


create table boy_girl(
    id int not null auto_increment primary key,
    boy_id int not null,
    girl_id int not null,
    constraint fk_boy_girl_boy foreign key boy_girl(boy_id) references boy(id),
    constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id)
)default charset=utf8;

如果表结构已经创建好了，额外想要增加外键：

alter table boy_girl add constraint fk_boy_girl_boy foreign key boy_girl (boy_id) references boy(id);
alter table boy_girl add constraint fk_boy_girl_girl foreign key boy_girl(girl_id) references girl(id);

删除外键：

alter table info drop foreign key fk_boy_girl_boy;
alter table info drop foreign key fk_boy_girl_girl;

在以后项目开发时，设计表结构及其关系的是一个非常重要的技能。

一般项目开始开发的步骤：

  1. 需求调研
  2. 设计数据库表结构（根据需求）
  3. 项目开发（写代码）

大量的工作在前面两个步骤，前期设计完成之后，后续的功能开发就比较简单了。

3. 授权

之前我们无论是基于Python代码 or 自带客户端去连接MySQL时，均使用的是root账户，拥有对MySQL数据库操作的所有权限。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DLKVk4Ty-1639123505673)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211130175801368.png)]

如果有多个程序的数据库都放在同一个MySQL中，如果程序都用root账户就存在风险了。

这种情况怎么办呢？

在MySQL中支持创建账户，并给账户分配权限，例如︰只拥有数据库A操作的权限、只拥有数据库B中某些表的权限、只拥有数据库B中某些表的读权限等。

3.1 用户管理

在MySQL的默认数据库 mysql 中的 user 表中存储着所有的账户信息（含账户、权限等）。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| day26              |
| luffy              |
| mysql              |
| performance_schema |
| practicedb         |
| rjdb               |
| sys                |
| userdb             |
+--------------------+
9 rows in set (0.01 sec)

mysql> select user,authentication_string,host from mysql.user;
+---------------+-------------------------------------------+-----------+
| user          | authentication_string                     | host      |
+---------------+-------------------------------------------+-----------+
| root          |                                           | localhost |
| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |
+---------------+-------------------------------------------+-----------+
3 rows in set (0.02 sec)

  • 创建和删除用户

    create user '用户名'@'连接者的IP地址'; identified by '密码';
drop user '用户名'@'连接者的IP地址';

    create user lirenjie@127.0.0.1 identified by 'root123';
drop user lirenjie@127.0.0.1;

create user lirenjie2@'127.0.0.%' identified by 'root123';  -- %表示任意
drop user lirenjie2@'127.0.0.%';

create user lirenjie3@'%' identified by 'root123';
drop user lirenjie3@'%';

  • 修改用户

    rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

    rename user 'lirenjie'@'127.0.0.1' to 'lirenjie1'@'127.0.0.1';

rename user 'lirenjie'@'127.0.0.1' to 'lirenjie'@'localhost';

  • 修改密码

    set password for '用户名'@'IP地址' = Password('新密码');

    set password for 'lirenjie1'@'127.0.0.1' = Password('123123');

3.2 授权管理

创建好用户之后，就可以为用户进行授权了。

  • 授权

    grant 权限 on 数据库.表 to '用户'@'IP地址'

    grant all privileges on *.* to 'lirenjie1'@'localhost';
grant all privileges on day26.* to 'lirenjie1'@'localhost';
grant all privileges on day26.* to 'lirenjie1'@'localhost';

grant select on day26.info To 'lirenjie'@'localhost';
grant select,insert on day26.* to 'lirenjie1'@'localhost';

注意：flush privileges;  -- 将数据读取到内存中，从而立即生效。

    • 对于权限

      all privileges       除grant外的所有权限
select               仅查权限
select,insert        查和插入权限
...
usage                       无访问权限
alter                       使用alter table
alter routine               使用alter procedure和drop procedure
create                      使用create table
create routine              使用create procedure
create temporary tables     使用create temporary tables
create user                 使用create user、drop user、rename user和revoke all privileges
create view                 使用create view
delete                      使用delete
drop                        使用drop table
execute                     使用call和存储过程
file                        使用select into outfile和 load data infile
grant option                使用grant和revoke
index                       使用index
insert                      使用insert
lock tables                 使用lock table
process                     使用show full processlist
select                      使用select
show databases              使用show databases
show view                   使用show view
update                      使用update
reload                      使用flush
shutdown                    使用mysqladmin shutdown(关闭MySQL)
super                       使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆
replication client          服务器位置的访问
replication slave           由复制从属使用

    • 对于数据库和表

      数据库名.*             数据库中的所有
数据库名.表名           指定数据库中的某张表
数据库名.存储过程名      指定数据库中的存储过程
*.*                   所有数据库

  • 查看授权

    show grants for '用户'@'IP地址';

    show grants for 'lirenjie1'@'localhost';

  • 取消授权

    revoke 权限 on 数据库.表 from '用户'@'地址';

    revoke ALL privileges on day26.* from 'lirenjie1'@'localhost';

    一般情况下，在很多的正规公司，数据库都是由DBA来统一进行管理，DBA为每个项目的数据库创建用户，并赋予相关的权限。

总结

本节主要讲解的三大部分的知识点：

  • 常见SQL语句，项目开发中使用最频繁的知识点。
  • 表关系，项目开发前，项目表结构设计时必备知识点。
    • 单表
    • 一对多
    • 多对多
  • 授权，在MySQL中创建用户并赋予相关权限。

三、SQL强化和实践

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-epSMppOX-1639123505673)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211201151511442.png)]

目标：练习常见的SQL语句和表结构设计。

概要：

  • SQL强化
  • 表结构设计（博客系统）

1. SQL强化

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c2PYKy8V-1639123505674)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211201153956287.png)]

  1. 根据上图创建 数据库 & 表结构 并录入数据（可以自行创造数据)

    create database day27 default charset utf8 collate utf8_general_ci;
use day27;

drop database day27;
drop database day27 IF EXISTS day27;

    利用导入数据库命令：

    • 导入

      mysql -u root -p day27 < D:\py\database\day27.sql

    • 导出

      # 结构+数据
mysqldump -u root -p day27 > D:\py\database\day272.sql

# 结构
mysqldump -u root -p -d day27 > D:\py\database\day273.sql

mysql> select * from student;
+-----+--------------+--------+----------+
| sid | sname        | gender | class_id |
+-----+--------------+--------+----------+
|   1 | lirenjie     | 男     |        1 |
|   2 | zhaojunxin   | 女     |        1 |
|   3 | wangjiaxin   | 男     |        1 |
|   4 | jiaomingjian | 男     |        1 |
|   5 | zhangjiahao  | 男     |        2 |
|   6 | xiangzenan   | 女     |        2 |
|   7 | ruiwen       | 女     |        2 |
|   8 | jinx         | 女     |        3 |
|   9 | jaze         | 男     |        3 |
|  10 | vector       | 男     |        3 |
|  11 | caitlyn      | 女     |        3 |
+-----+--------------+--------+----------+
11 rows in set (0.00 sec)

mysql> select * from teacher;
+-----+--------+
| tid | tname  |
+-----+--------+
|   1 | 王洪波 |
|   2 | 史艳国 |
|   3 | 高殿荣 |
|   4 | 孔玉萍 |
|   5 | 荆存成 |
|   6 | 嵩天   |
|   7 | 武佩奇 |
+-----+--------+
7 rows in set (0.00 sec)

mysql> select * from course;
+-----+----------+-----------+
| cid | cname    | tearch_id |
+-----+----------+-----------+
|   1 | 机械设计 |         1 |
|   2 | 嵌入式   |         2 |
|   3 | 液压     |         3 |
|   4 | 物理     |         4 |
|   5 | 数学     |         5 |
|   6 | Python   |         6 |
|   7 | Python   |         7 |
|   8 | 物理     |         1 |
+-----+----------+-----------+
8 rows in set (0.00 sec)

mysql> select * from score limit 10;
+-----+------------+-----------+--------+
| sid | student_id | course_id | number |
+-----+------------+-----------+--------+
|   1 |          1 |         1 |     86 |
|   2 |          1 |         2 |     96 |
|   3 |          1 |         3 |     91 |
|   4 |          1 |         4 |     88 |
|   5 |          1 |         5 |     85 |
|   6 |          1 |         6 |     99 |
|   7 |          2 |         1 |     76 |
|   8 |          2 |         4 |     91 |
|   9 |          2 |         5 |    100 |
|  10 |          3 |         1 |     81 |
+-----+------------+-----------+--------+
10 rows in set (0.00 sec)

  1. 根据上图创建数据库&表结构并录入数据（可以自行创造数据）。

  2. 创建用户lrj并赋予此数据库的所有权限。

    create user 'lrj'@'localhost' identified by 'lrj9971599863210';
grant all privileges on day27.* to 'lrj'@'localhost';
flush priviledges;

  3. 查询姓“李"的老师的个数。

    select count(tid) from teacher where tname like '李%'

  4. 查询姓“张"的学生名单。

    select count(tid) from teacher where tname like '张%'

  5. 查询男生、女生的人数。

    select count(sid) from student group by gender

  6. 查询同名同姓学生名单，并统计同名人数。

    select count(sname) from student group by sname having count(sname)>0;

  7. 查询“三年二班”的所有学生。

    select * from student left outer join class on student.class_id=class.cid where class.caption="三年二班";

  8. 查询每个班级的班级名称、班级人数。

    select class.caption,count(class.cid) from class left join student on class.cid=student.class_id group by class.caption;

  9. 查询成绩小于60分的同学的学号、姓名、成绩、课程名称。

    select
	student.sid,
	student.sname,
	score.number,
	course.cname 
from
	class 
	left join student on class.cid=student.class_id 
	left join score on student.sid = score.student_id 
	left join course on score.course_id = course.cid 
	left join teacher on course.tearch_id = teacher.tid 
where course.number<60;

  10. 查询选修了“生物课”的所有学生ID、学生姓名、成绩。

    select student.sid, student.sname, score.number from student left join score on student.sid=score.student_id left join course on score.course_id=course.cid where course.cname="数学" and score.number>90;

  11. 查询选修了“生物课”且分数高于90的的所有学生ID、学生姓名、成绩。

    select student.sid, student.sname, score.number from student left join score on student.sid=score.student_id left join course on score.course_id=course.cid where course.cname="数学" and score.number>90;

  12. 查询所有同学的学号、姓名、选课数、总成绩。

    select student.sid,student.sname,count(score.student_id) as num_subject,sum(score.number) as total_points from score left join student on score.student_id=student.sid group by score.student_id;

  13. 查询各科被选修的学生数。

    select count(score.course_id) as num_students from course left join score on course.cid=score.course_id group by score.course_id;

  14. 查询各科成绩的总分、最高分、最低分，显示:课程ID、课程名称、总分、最高分、最低分。

    select course.cid as course_id, course.cname as course_name, sum(score.number) as course_total_points, max(score.number) as max_points, min(score.number) as min_points from course left outer join score on course.cid=score.course_id group by course.cid;

  15. 查询各科成绩的平均分，显示:课程ID、课程名称、平均分。

    select course.cid as course_id, course.cname as course_name, avg(score.number) as course_average_points from course left outer join score on course.cid=score.course_id group by course.cid;

  16. 查询各科成绩的平均分，显示:课程ID、课程名称、平均分（按平均分从大到小排）。

    select course.cid as course_id, course.cname as course_name, avg(score.number) as course_average_points from course left outer join score on course.cid=score.course_id group by course.cid order by course_average_points desc;

  17. 查询各科成绩的平均分和及格率，显示:课程ID、课程名称、平均分、及格率。

    select 
   course.cid as course_id,
   course.cname as course_name,
   avg(score.number) as course_average_points, 
   sum(case when score.number > 60 then 1 else 0 end)/count(1) *100 as 'pass_rate(%)'
from 
   score
   left join course on score.course_id=course.cid
group by
   course_id;

  18. 查询平均成绩大于60的所有学生的学号、平均成绩。

    select student.sid as id, avg(score.number) as average_points from student left join score on student.sid=score.student_id group by student.sid having average_points>60;

  19. 查询平均成绩大于85的所有学生的学号、平均成绩、姓名。

    select student.sid as id, avg(score.number) as average_points, student.sname as student_name from student left join score on student.sid=score.student_id group by student.sid having average_points>85;

  20. 查询“三年二班”每个学生的学号、姓名、总成绩、平均成绩。

    select 
   student.sid as id, 
   student.sname as student_name, 
   sum(score.number) as total_points, 
   avg(score.number) as average_points 
from 
   class 
   left join student on class.cid=student.class_id 
   left join score on student.sid=score.student_id 
where 
   class.caption="三年二班" 
group by 
   student.sid;

  21. 查询各个班级的班级名称、总成绩、平均成绩、及格率（按平均成绩从大到小排序）。

    select 
   class.caption, 
   sum(score.number) as total_points, 
   avg(score.number) as average_points, 
   sum(case when score.number > 60 then 1 else 0 end)/count(1) *100 as 'pass_rate(%)'
from 
   class 
   left join student on class.cid=student.class_id 
   left join score on student.sid=score.student_id 
group by 
   class.cid 
order by 
   average_points desc;

  22. 查询学过“高殿荣”老师课的同学的学号、姓名。

    select distinct 
   student.sid as id, 
   student.sname as name 
from 
   teacher 
   left join course on teacher.tid=course.tearch_id 
   left join score on course.cid=score.course_id 
   left join student on score.student_id=student.sid 
where 
   tname='高殿荣';

  23. **重要：**查询没学过“高殿荣”老师课的同学的学号、姓名。

    select sid from student where sid not in(
   select distinct 
   	student.sid as id 
   from 
   	teacher 
   	left join course on teacher.tid=course.tearch_id 
   	left join score on course.cid=score.course_id 
   	left join student on score.student_id=student.sid 
   where 
   	tname='高殿荣'
);

  24. **重要：**查询选修“史艳国”老师所授课程的学生中，成绩最高的学生姓名及其成绩（不考虑并列）。

    select 
   student.sname as name, 
   score.number 
from 
   teacher 
   left join course on teacher.tid=course.tearch_id 
   left join score on course.cid=score.course_id 
   left join student on score.student_id=student.sid 
where 
   teacher.tname='史艳国' 
order by 
   score.number desc 
   limit 1;

  25. **重要：**查询选修“史艳国”老师所授课程的学生中，成绩最高的学生姓名及其成绩（考虑并列）。

    select 
   student.sname as name, 
   score.number 
from 
   teacher 
   left join course on teacher.tid=course.tearch_id 
   left join score on course.cid=score.course_id 
   left join student on score.student_id=student.sid 
where 
   teacher.tname = '史艳国' 
   and score.number = (
   	select
   		max(number)
   	from
   		score
   		left join course on score.course_id = course.cid 
   		left join teacher on course.tearch_id = teacher.tid
   	where
   	teacher.tname = '史艳国'
   );

  26. **重要：**查询只选修了一门课程的全部学生的学号、姓名。

    select student.sid from score left outer join student on student.sid=score.student_id group by score.student_id having count(1)=1;

    select 
	student.sid,
	student.sname
from 
	score 
	left outer join student on student.sid=score.student_id 
group by 
	score.student_id 
having 
	count(1)=1;

  27. 查询至少选修两门课程的学生学号、学生姓名、选修课程数量。

    select student.sid as id, student.sname as student_name, count(1) as course_num from score left outer join student on student.sid=score.student_id group by score.student_id having count(1)>1;

  28. 查询两门及以上优秀的同学的学号、学生姓名、选修课程数量。

    select 
   student.sid as id, 
   student.sname as student_name, 
   count(1), 
   sum(case when score.number > 90 then 1 else 0 end) as A 
from 
   score 
   left outer join student on student.sid=score.student_id 
group by 
   score.student_id 
having A>1;

-- 方法2
select 
   student.sid as id, 
   student.sname as student_name, 
   count(1) as A
from 
   score 
   left outer join student on student.sid=score.student_id 
where
   number > 90 
group by 
   score.student_id 
having A>1;

  29. 查询选修了所有课程的学生的学号、姓名。

    select student.sid as id, student.sname as student_name from student inner join score on student.sid=score.student_id group by score.student_id having count(1) = (select count(1) from course);

  30. 查询未选修所有课程的学生的学号、姓名。

    select student.sid as id, student.sname as student_name from student inner join score on student.sid=score.student_id group by score.student_id having count(1) != 6;

-- 2
select sid, sname from student where sid not in(select student.sid as id from student inner join score on student.sid=score.student_id group by score.student_id having count(1) = 6);

  31. 查询所有学生都选修了的课程的课程号、课程名。

    select course.cid as course_id, course.cname as course_name from course inner join score on course.cid=score.course_id group by score.course_id having count(1)=(select count(1) from student);

  32. 查询选修“机械设计”和“嵌入式”课程的所有学生学号、姓名。

    select student.sid as id, student.sname as student_name from score inner join student on score.student_id=student.sid inner join course on score.course_id=course.cid where course.cname="机械设计" or course.cname="嵌入式" group by student_id having count(1) = 2;

  33. 查询至少有一门课与学号为“1"的学生所选的课程相同的其他学生学号和姓名。

    select distinct student.sid as id, student.sname as student_name from student left outer join score on student.sid=score.student_id where (score.course_id in (select course_id from score where student_id=1)) and ((score.student_id != 1));

-- 法2
select student.sid as id, student.sname as student_name from student left outer join score on student.sid=score.student_id where (score.course_id in (select course_id from score where student_id=1)) and ((score.student_id != 1)) group by student_id having count(1)>=1;

  34. 查询与学号为“2”的同学选修的课程完全相同的其他学生学号和姓名。

    select student.sid, student.sname, count(score.course_id) as course_num, sum(case when score.course_id in (select course_id as student2_course from score where student_id=2) then 1 else 0 end) as identic_num from student inner join score on student.sid=score.student_id 
group by score.student_id having identic_num = (select count(1) from score where student_id=2) and student.sid!=2 and course_num=identic_num;

  35. **重要：**查询"嵌入式"课程比“物理"课程成绩高的所有学生的学号;

    select student_id, max(case cname when "嵌入式" then number else -1 end) as "嵌入式", max(case cname when "物理" then number else -1 end) as "物理" from score left join course on score.course_id=course.cid where cname in ("嵌入式","物理") group by student_id having 嵌入式>物理;

  36. **重要：**查询每门课程成绩最好的前3名(不考虑成绩并列情况)。

    select course.cid, course.cname, (select student.sname from score left join student on student.sid=score.student_id where course_id=course.cid order by number desc limit 1 offset 0) as "第1名", (select student.sname from score left join student on student.sid=score.student_id where course_id=course.cid order by number desc limit 1 offset 1) as "第2名", (select student.sname from score left join student on student.sid=score.student_id where course_id=course.cid order by number desc limit 1 offset 2) as "第3名" from course;


select student.sname, score.number, course.cname from score left join student on student.sid=score.student_id left join course on score.course_id=course.cid where course_id=course.cid order by number desc;

  37. **重要：**查询每门课程成绩最好的前3名(考虑成绩并列情况)。

    select 
	* 
from 
	score 
	
	left join (
        select 
        	course.cid, 
        	course.cname, 
        	(select number from score where course_id=course.cid group by number order by number desc limit 1) as "最高分", 
        	(select number from score where course_id=course.cid group by number order by number desc limit 1 offset 1) as "第二高分", 
        	(select number from score where course_id=course.cid group by number order by number desc limit 1 offset 2) as "第三高分" 
        from 
        	course) as C on score.course_id = C.cid 
where 
	score.number >= C.第三高分;

  38. **重要：**创建一个表sc，然后将score表中所有数据插入到sc表中。

    create table `sc` (
    `sid` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `student_id` int NOT NULL,
    `course_id` int NOT NULL,
    `num` int NOT NULL,
    CONSTRAINT `fk_sc_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`cid`),
    CONSTRAINT `fk_sc_student` FOREIGN KEY (`student_id`) REFERENCES `student` (`sid`)
) DEFAULT CHARSET=utf8;

    insert into sc select * from score;

  39. **重要：**向score表中插入一些记录，这些记录要求符合以下条件：

    • 学生ID为︰没上过课程ID为“2”课程的学生的学号；
    • 课程ID为∶2
    • 成绩为︰80
    -- 没上过
select
	sid
from
	student
where
	sid not in ( select student_id from score where course_id = 2);


-- 构造数据
select
	sid,
	2,
	80
from
	student
where
	sid not in ( select student_id from score where course_id = 2);

    insert into sc ( student_id, course_id, number ) select
sid,
2,
80
from
	student
where
	sid not in ( select student_id from score where course_id = 2);

  40. **重要：**向score表中插入一些记录，这些记录要求符合以下条件:

    • 学生ID为:没上过课程ID为"2"课程的学生的学号
    • 课程ID为: 2
    • 成绩为：课程ID为3的最高分
    insert into sc ( student_id, course_id, number ) select
sid,
2,
(select max(number) from score where course_id=3) as number
from
	student
where
	sid not in ( select student_id from score where course_id = 2);

2. 设计表结构

根据如下的业务需求设计相应的表结构，内部需涵盖如下功能。

  • 注册
  • 登录
  • 发布博客
  • 查看博客列表，显示博客标题、创建时间、阅读数量、评论数量、赞数量等。
  • 博客详细，显示博文详细、评论等。
    • 发表评论
    • 赞or踩
    • 阅读数量＋1

参考如下图片请根据如下功能来设计相应的表结构。

注意∶只需要设计表结构，不需要用python代码实现具体的功能(再学一点知识点后再更好的去实现)。

2.1 注册和登录

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e4EaVot9-1639123505675)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205163518272.png)]

2.2 文章列表

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yFWplIfe-1639123505676)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205163546657.png)]

2.3 文章详细

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rJ23NiJV-1639123505677)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205163622086.png)]

2.4 评论 & 阅读 & 赞 & 踩

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7dY1tRLg-1639123505678)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205163657802.png)]

注意：假设都是以及评论（不能回复评论）。

博客系统-表结构设计

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bDzOBntv-1639123505678)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205163738338.png)]

四、索引和函数及存储过程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n0DsKCT4-1639123505679)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205180937477.png)]

课程目标：了解MySQL中索引、函数、存储过程、函数、触发器、视图等知识点。

课程概要：

  • 索引（重要）
  • 函数
  • 存储过程
  • 视图
  • 触发器

1. 索引

在数据库中索引最核心的作用是︰加速查找。例如︰在含有300w条数据的表中查询，无索引需要700秒，而利用索引可能仅需1秒。

数据量巨大，希望快速查找，就会用索引。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0QGgGDiL-1639123505680)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205181618136.png)]

在开发过程中会为哪些经常会被搜索的列创建索引，以提高程序的响应速度。

例如：查询手机号、邮箱、用户名等。

1.1 索引原理

为什么加上索引之后速度能有这么大的提升呢?因为索引的底层是基于B+Tree的数据结构存储的。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5wx84HQ5-1639123505681)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205192607850.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ft0ZGAgV-1639123505681)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205192703894.png)][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XpOahhVi-1639123505682)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205192716676.png)]

很明显，如果有了索引结构的查询效率比表中逐行查询的速度要快很多且数据量越大越明显。

B+Tree结构连接

数据库的索引是基于上述B+Tree的数据结构实现，但在创建数据库表时，如果指定不同的引擎，底层使用的B+Tree结构的原理有些不同。

聚簇和非聚簇索引的区别：

  • myisam引擎，非聚簇索引（数据和索引结构分开存储，索引保存的是数据文件的指针，主键索引和辅助索引是独立的，叶子节点都是数据文件的地址指针）。
  • innodb引擎，聚簇索引（数据和主键索引结构存储在一起，主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值）。
1.1.1 非聚簇索引（mysiam引擎）
create table 表名(
	id int not null auto_increment primary key,
    name varchar(32) not null,
    age int
)engine=myisam default charset=utf8;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-TAp8OHIR-1639123505683)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205193237066.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-78whunvO-1639123505684)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205193348712.png)]

1.1.2 聚簇索引（innodb引擎）
create table 表名(
	id int not null auto_increment primary key,
    name varchar(32) not null,
	age int
)engine=innodb default charset=utf8;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zfNsRk7f-1639123505684)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205193702701.png)]

我想给name这一列也生成索引，辅助索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fkSudX0h-1639123505685)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205193648864.png)]

在MySQL文件存储中的体现：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9XPfvsOd-1639123505686)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205193847839.png)]

上述 聚簇索引 和 非聚簇索引 底层均利用了B+Tree结构结构，只不过内部数据存储有些不同罢了。在企业开发中一般都会使用innodb引擎(内部支持事务、行级锁、外键等特点)，在MySQL5.5版本之后默认引擎也是innodb。

  • innodb引擎，一般创建的索引：聚簇索引。

1.2 常见索引

在innodb引擎下，索引底层都是基于B+Tree数据结构存储（聚簇索引）。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LJBWpUx9-1639123505687)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205195248107.png)]

在开发过程中常见的索引类型有：

  • 主键索引：加速查找、不能为空、不能重复。 +联合主键索引
  • 唯一索引：加速查找、不能重复。 + 联合唯一索引
  • 普通索引：加速查找。 +联合索引

1.2.1 主键和联合主键索引

create table 表名(
	id int not null auto_ increment primary key,  --主键
	name varchar(32) not null
);

create table表名(
	id int not null auto_increment,
	name varchar(32) not null,
	primary key(id)
);

create table 表名(
	id int not null auto_increment ，
	name varchar(32) not null,
	primary key(列1,列2)  --如果有多列，称为联合主键(不常用且myisam引擎支持)
);

alter table 表名 add primary key(列名) ;

alter table 表名 drop primary key;

注意：删除索引时可能会报错，自增列必须定义为键。一个列自增，主键。删除主键时，自增就会报错。

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

alter table 表 change id id int not null;

create table t7(
	id int not null,
	name varchar(32) not null,
	primary key(id)
);

alter table t6 drop primary key;
1.2.2 唯一和联合唯一索引
create table t10(
	id int not null auto_increment primary key,
	name varchar(32),
	email varchar(64) not null,
	unique ix_name(name)
);

create table t11(
	id int not null auto_increment,
	name varchar(32) not null,
    gender varchar(16) not null,
	unique ix_id_name(id, name)    -- 如果有多列，称为联合唯一索引。
);

create unique index 索引名 on 表名(列名);

drop index 索引名 on 表名;
1.2.3 索引和联合索引
create table 表名(
	id int not null auto_ increment pr imary key,
	name varchar(32) not null,
	email varchar(64) not null,
	index ix_email (email)
);

create table 表名(
	id int not null auto_increment primary key,
	name varchar(32) not null,
	email varchar(64) not nul1,
	index ix_email(name, email)    --如果有多列，称为联合索引。
);

create index 索引名 on 表名(列名);

drop index 索引名 on 表名;

在项目开发的设计表结构的环节，大家需要根据业务需求的特点来决定是否创建相应的索引。

案例：博客系统

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CwfHy7aK-1639123505688)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205203336920.png)]

  • 每张表id列都创建自增+主键。
  • 用户表
    • 用户名+密码创建联合索引。
    • 手机号，创建唯一索引。
    • 邮箱，创建唯一索引。
  • 推荐表
    • user_ id 和 article_ id创建联合唯一索引。

1.3 命中索引

在表中创建索引后，查询时一定要命中索引。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FPZDFmfg-1639123505689)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205204701550.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YgFhYz2J-1639123505690)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205204709496.png)]

在数据库的表中创建索引之后优缺点如下：

  • 优点:查找速度快、约束(唯一、主键、联合唯一)
  • 缺点:插入、删除、更新速度比较慢，因为每次操作都需要调整整个B+Tree的数据结构关系。

所以，在表中不要无节制的去创建索引啊。。。

在开发中，我们会对表中经常被搜索的列创建索引，从而提高程序的响应速度。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PbPGA4kJ-1639123505691)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205204850041.png)]

一般情况下，我们针对只要通过索引列去搜搜都可以命中索引(通过索引结构加速查找)。

select * from big where id = 5;
select * from big where id > 5;
select * from big where email = "wupeiqi@live.com" ;
select * from big where name = " 武沛齐" ;
select * from big where name = "kelly" and password="ffsijfs";
...

但是，还是会有一些特殊的情况，让我们无法命中索引（即使创建了索引），这也是需要大家在开发中要注意的。

  • 类型不一致

    select * from big where name = 123;    -- 未命中
select * from big where email = 123;    -- 未命中

-- 特殊的主键:
select * from big where id = "123";    -- 命中

  • 使用不等于

    select * from big where name != "武沛齐";    -- 未命中
select * from big where email != "wupeiqi@live.com";    -- 未命中

-- 特殊的主键:
select * from big where id != 123;    -- 命中

  • or，当or条件中有未建立索引的列才失效。

    select * from big where id = 123 or password="xx";    -- 未命中
select * from big where name =‘wupeiqi" or password="xx";    -- 未命中

-- 特别的:
select * from big where id = 10 or password="xx" and name="xx";  -- 命中

  • 排序，当根据索引排序时候，选择的映射如果不是索引，则不走索引。

    select * from big order by name asc;    -- 未命中
select * from big order by name desc;   -- 未命中

特别的主键:
	select * from big order by id desc;    -- 命中

  • like，模糊匹配对。

    select * from big where name like "8u-12-19999";  -- 未命中
select * from big where name like "_u-12-19999";  -- 未命中
select * from big where name like "wu-8-10";  -- 未命中

特别的:
select * from big where name like "wu-1111-8"; -- 命中
select * from big where name like "wuw-8";  -- 命中

  • 使用函数

    select * from big where reverse(name) = "wupeiqi";  -- 未命中

-- 特别的:
select * from big where name = reverse( " wupeiqi");  -- 命中

  • 最左前缀，如果是联合索引，要遵循最左前缀原则。

    如果联合索引为: (name, password)
	name and password  -- 命中
	name               -- 命中
	password           -- 未命中
    name or password   -- 未命中

常见的无法命中索引的情况就是上述的示例。

1.4 执行计划

MySQL中提供了执行计划，让你能够预判SQL的执行(只能给到一定的参考，不一定完全能预判准确)。

explain select * from big;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MOfno2VE-1639123505692)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211205210118457.png)]

其中比较重要的是 type，它是SQL性能比较重要的标志，性能从低到高依次: all < index < range < index_ merge < ref_ or_ null < ref < eq_ref < system/ const

  • ALL，全表扫描，数据表从头到尾找一遍。(一般未命中索引，都是会执行权标扫描）

    select * from big;

特别的:如果有limit，则找到之后就不在继续向下扫描.
select * from big limit 1;

  • INDEX，全索引扫描，对索引从头到尾找一遍。

    explain select id from big;
explain select name from big;

  • RANGE，对索引列进行范围查找。

    explain select * from big where id > 10;
explain select * from big where id in ( 11,22,33 ) ;
explain select * from big where id between 10 and 20;
explain select * from big where name > "wupeiqi";

  • INDEX_MERGE，合并索引，使用多个单列索引搜索。

    explain select * from big where id = 10 or name="武沛齐";

  • REF，根据索引直接去查找（非键）。

    select * from big where name ='武沛齐';

  • EQ_REF，连表操作时常见。

    explain select big.name, users.id from big left join users on big.age = users.id;

  • CONST，常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数，const表很快。

    explain select * from big where id=11;  -- 主键
explain select * from big where email="w-11-0@qq.com";  -- 唯一索引

  • SYSTEM，系统，表仅有一行(=系统表)。这是const联接类型的一个特例。

    explain select * from (select * from big where id=1 limit 1) as A;

其他列：

id，查询顺序标识

z，查询类型
	SIMPLE			简单查询
	PRIMARY			最外层查询
	sUBQUERY		映射为子查询
	DERIVED			子查询
	UNION			联合
	UNION RESULT	使用联合的结果
	...
	
table，正在访问的表名

partitions，涉及的分区(MySQL支持将数据划分到不同的idb文件中，详单与数据的拆分)。一个特别大的文件拆分成多个小文件（分区)。

possible_keys，查询涉及到的字段上若存在索引，则该索引将被列出，即:可能使用的索引。

key，显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。例如︰有索引但未命中，则possible_keys显示、key则显示NULL。

key_len，表示索引字段的最大可能长度。(类型字节长度＋变长2 ＋可空1)，例:key_len=195，类型varchar(64),195=64*3+2+1

ref，连表时显示的关联信息。例如∶A和B连表，显示连表的字段信息。

rows，估计读取的数据行数（只是预估值)
	explain select * from big where password ="025dfdeb-d803-425d-9834-445758885d1c";
	explain select * from big where password ="025dfdeb-d803-425d-9834-445758885d1c" limit 1;

filtered，返回结果的行占需要读到的行的百分比。
	explain select * from big where id=1; -- 100，只读了一个1行，返回结果也是1行。
	explain select * from big where password="27d8ba90-edd0-4a2f-9aaf-99c9d607c3b3";-- 10，读取了10行，返回了1行。
	注意:密码27d8ba90-edd0-4a2f-9aaf-99c9d607c3b3在第10行

extra，该列包含MySQL解决查询的详细信息。
	“Using index"
	此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了
	“Using where"
	这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示"Using where"。有时“Using where"的出现就是一个暗示︰查询可受益于不同的索引。

小结

上述索引相关的内容讲的比较多，大家在开发过程中重点应该掌握的是∶

  • 根据情况创建合适的索引（加速查找）。
  • 有索引，则查询时要命中索引。

2. 函数

MySQL中提供了很多函数，为我们的SQL操作提供便利，例如∶

count(),max(),min(),avg(),reverse(),concat(),NOW(),DATE_FORMAT( , '%Y-%m-%d %H:%i:%s')

更多函数

当然，MySQL中也支持自定义函数。

  • 创建函数

    delimiter $$
create function f1(
	i1 int,
	i2 int)
returns int
BEGIN
	declare num int;
	declare maxId int;
	select max(id) from big into maxId;
	
	set num = i1 + i2 + maxId;
	return(num);
END $$
delimiter ;

  • 执行函数

    select f1(11, 22);

select f1(11, id), name from d1;

  • 删除函数

    drop function f1;

3. 存储过程

存储过程，是一个存储在MySQL中的SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-B9Xe4Bnf-1639123505693)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211206105328889.png)]

3.1 参数类型

存储过程的参数可以有如下三种：

  • in，仅用于传入参数用
  • out，仅用于返回值用
  • inout，既可以传入又可以当作返回值
delimiter $$
create procedure p2(
    in il int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
	DECLARE temp1 int;
	DECLARE temp2 int default 0;
	
	set templ = 1;

	set r1 = i1 +i2 + temp1 + temp2;

	set i3 = i3 + 100;

end $$
delimiter ;


set @t1 = 4;
set @t2 = 0;
CALL p2 (1, 2, @t1, @t2);
SELECT @t1,@t2;

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect (host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程T
cursor.callproc('p2', args=(1, 22, 3, 4))

# 获取执行完存储的参数
cursor.execute("select @_p2_0, @_p2_1, @_p2_2, @_p2_3")
result = cursor.fetchall()

cursor.close()
conn.close()

print(result)

3.2 返回值 & 结果集

delimiter $$
create procedure p3(
	in n1 int,
    inout n2 int,
    out n3 int
)
begin
	set n2 =n1 +100 ;
	set n3 = n2 +n1 + 100;
	select * from d1;
end $$
delimiter;

set @t1 = 4;
set @t2 = 0;
CALL p3(1,@t1, t2);
SELECT @t1,@t2;

#!/usr/bin/env python# -*- coding:utf-8 -*-import pymysqlconn = pymysql.connect (host='127.0.0.1', port=3306, user='root', passwd= 'root123', db='userdb')cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 执行存储过程cursor.callproc('p3', args=(22,3,4))table = cursor.fetchall()  # 得到执行存储过中的结果集# 获取执行完存储的参数cursor.execute("select @_p3_0, @_p3_1, @_p3_2")rets = cursor.fetchall()cursor.close()conn.close()print(table)print(rets)

3.3 事务 & 异常

事务，成功都成功，失败都失败。

delimiter $$
create PROCEDURE p4(
    OUT p_return_code tinyint
)
BEGIN
	DECLARE exit handler for sqlexception
	BEGIN
		--ERROR
		set p_return_code = 1;
		rollback;
    END;
    
    DECLARE exit handler for sqlwarning
    BEGIN
    	-- WARNING
    	set p_return_code = 2;
    	rollback;
    END;
    
    START TRANSACTION;    -- 开启事务
    	delete from d1;
    	insert into tb(name) values('seven');
    COMMIT;  -- 提交事务
    
    -- SUCCESS
    set p_return_code = 0;
    
END $$
delimiter ;

set @ret =100;
CALL p4(@ret);
SELECT @ret;

#!/usr/ bin/env python# -*- coding:utf-8 -*-import pymysqlconn = pymysql.connect(host='127.0.0.1',port=3306，user='root', passwd='root123',db='userdb')cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)# 执行存储过程cursor.callproc('p4', args=(100))# 获取执行完存储的参数cursor.execute("select @_p4_0")rets = cursor.fetchall()cursor.close()conn.close()print(table)print(rets)

3.4 游标

delimiter $$
create procedure p5()
begin
	declare sid int;
	declare sname varchar(50);
	declare done int default false;
	
	
	declare my_cursor CURSOR FOR select id ,name from d1;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
	
	open my_cursor;
    	xxoo: LOOP
    		fetch my_cursor into sid, sname;
    		IF done then
    			leave xxoo;
    		END IF;
    		insert into t1(name) values(sname);
    	end loop xxoo;
    close my_cursor;
end $$
delimiter ;

call p5();

4. 视图

视图其实是一个虚拟表（非真实存在)，其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。

SELECT
	*
FROM
	(SELECT nid, name FROM tb1 WHERE nid > 2) AS A
WHERE
	A.name > 'alex';

  • 创建视图

    create view v1 as select id, name from d1 where id > 1;

  • 使用视图

    select * from v1;-- select * from (select id,name from d1 where id > 1) as v1;

  • 删除视图

    drop view v1;

  • 修改视图

    alter view v1 as SQL语句

注意∶基于视图只能查询，针对视图不能执行增加、修改、删除。如果源表发生变化，视图表也会发生变化。

5. 触发器

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8RXXfl0E-1639123505694)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211206114014517.png)]

对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器。

#插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
	...
END

#插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
	...
END

#删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
	...
END

#删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
	...
END

#更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
	...
END

#更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
	...
END

DROP TRIGGER tri_after_insert_tb1;

示例：

  • 在t1表中插入数据之前，先在t2表中插入一行数据。

    delimiter $$
CREATE TRIGGER tri_before_insert_t1 BEFORE INSERT ON t1 FOR EACH ROW

BEGIN
	
IF NEW.name = 'alex' THEN
	INSERT INTO t2 (name) VALUES(NEW.id);  -- NEW代表新插入的那一行
END IF;

END $$
delimiter ;

  • 在t1表中删除数据之后，再在t1表中插入一行数据。

    delimiter $$
CREATE TRIGGER tri_after_delete_t1 AFTER DELETE ON t1 FOR EACH ROW

BEGIN

IF OLD.name = 'alex' THEN
	INSERT INTO t2 (name) VALUES(OLD.id);
END IF;

END $$
delimiter ;

特别的：NEW表示新数据，OLD表示原来的数据。

总结

对于Python开发人员，其实在开发过程中触发器、视图、存储过程用的很少（以前搞C#经常写存储过程），最常用的其实就是正确的使用索引以及常见的函数。

  • 索引，加速查找 & 约束。
    • innodb和myisam的区别，聚簇索引和非聚簇索引。
    • 常见的索引:主键、唯一、普通。
    • 命中索引
    • 执行计划
  • 函数，提供了一些常见操作 & 配合SQL语句，执行后返回结果。
  • 存储过程，一个SQL语句的集合，可以出发复杂的情况，最终可以返回结果＋数据集。
  • 视图，一个虚拟的表。
  • 触发器，在表中数据行执行前后自定义一些操作。

五、Python操作MySQL和实战

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lLGaCMSu-1639123505694)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211206160042972.png)]

课程目标：掌握事务和锁以及Python操作MySQL的各种开发必备知识。

课程概要：

  • 事务
  • 锁
  • 数据库连接池
  • SQL工具类
  • 其他

1. 事务

innodb引擎中支持事务，myisam不支持。

CREATE TABLE `users` (
	`id` int(11) NOT NULL AUTO_ INCREMENT PRIMARY KEY,
	`name` varchar(32) DEFAULT NULL,
	`amount` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KBdOdPzs-1639123505695)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211206181226134.png)]

例如：李杰 给 武沛齐转账100，那就会涉及2个步骤。

  • 李杰账户减100
  • 武沛齐账户加100

这两个步骤必须同时完成才算完成，并且如果第一个完成、第二步失败，还是回滚到初始状态。

事务，就是来解决这种情况的。大白话：要成功都成功；要失败都失败。

事务的具有四大特性(ACID) :

  • 原子性（Atomicity）：

    原子性是指事务包含的所有操作不可分割，要么全部成功，要么全部失败回滚。

  • 一致性（Consistency）：

    执行的前后数据的完整性保持一致。

  • 隔离性（Isolation）：

    一个事务执行的过程中,不应该受到其他事务的干扰。

  • 持久性（Durability）：

    事务一旦结束,数据就持久到数据库

1.1 MySQL客户端

mysql> select * from users;
+----+------------+----------+
| id | name       | amount   |
+----+------------+----------+
|  1 | lirenjie   | 123456   |
|  2 | zhaojunxin | 123      |
+----+------------+----------+
2 rows in set (0.03 sec)

mysql> begin;  -- 开启事务 也可以写成 start transaction;
Query OK, 0 rows affected (0.00 sec)


mysql> update users set amout=amout-2 where id=1;  -- 执行操作
Query 0K，1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> update users set amount=amount+2 where id=2;  -- 执行操作
Query OK，1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0


mysql> commit;  --提交事务
Query 0K, 0 rows affected (0.00 sec)


mysql> select * from users;
+----+------------+----------+
| id | name       | amount   |
+----+------------+----------+
|  1 | lirenjie   | 123454   |
|  2 | zhaojunxin | 125      |
+----+------------+----------+
2 rows in set (0.03 sec)

1.2 Python代码

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd=' root123', charset="utf8", db='userdb')
cursor = conn.cursor()

# 开启事务
conn.begin()

try:
    cursor.execute("update users set amount=1 where id=1")
    int('asdf')
    cursor.execute("update tran set amout=2 where id=2")
except Exception as e:
    # 回滚
    print("回滚")
    conn.rollback()
else:
    # 提交
    print("提交")
    conn.commit()
    
cursor.close()
conn.close()

2. 锁

在用MySQL时，不知你是否会疑问:同时有很多做更新、插入、删除动作，MySQL如何保证数据不出错呢？

MySQL中自带了锁的功能，可以帮助我们实现开发过程中遇到的同时处理数据的情况。对于数据库中的锁，从锁的范围来讲有：

  • 表级锁，即A操作表时，其他人对整个表都不能操作，等待A操作完之后，才能继续。
  • 行级锁，即A操作表时，其他人对指定的行数据不能操作，其他行可以操作，等待A操作完之后，才能继续。

所以，一般情况下我们会选择使用innodb引擎，并且在搜索时也会使用索引(命中索引)。

接下来的操作就基于innodb引擎来操作:

CREATE TABLE `L1` (
	`id` int(11) NOT NULL AUTO_ INCREMENT,
	`name` varchar(255) DEFAULT NULL,
    `count` int(11) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7bDsZljN-1639123505696)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211206181741849.png)]

在innodb引擎中，update、 insert、 delete的行 为内部都会先申请锁（排它锁），申请到之后才执行相关操作,最后再释放锁。

所以，当多个人同时像数据库执行: insert、update、delete等操作时，内部加锁后会排队逐一执行。

而select则默认不会申请锁。

select * from xxx;

如果，你想要让select去申请锁，则需要配合 事务 + 特殊语法来实现。

  • for update，排他锁，加锁之后，其他人不可以读写。实质上：排他锁是限制不能再加锁，update、insert、delete都是自动加锁的，所以就不可以进行操作。而select默认不申请锁，所以可以查找。

    begin;
	select * from L1 where name="武佩奇" for update;  -- name列不是索引（表锁）
commit;

    begin;  -- 或者 start transaction;
	select * from L1 where id=1 for update;	-- id列是索引（行锁）
commit;

  • lock in share mode，共享锁，加锁之后，其他可读但不可写。多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

    begin;
	select * from L1 where name="武佩奇" lock in share mode;  -- name列不是索引（表锁）
commit;

    begin;  -- 或者 start transaction;	select * from L1 where id=1 lock in share mode;  -- id列是索引（行锁）commit;

2.1 排它锁

排它锁（for update），加锁之后，其他事务不可以读写。

应用场景：总共100件商品，每次购买一件需要让商品个数减1。

A: 访问页面查看商品剩余100
B: 访问页面查看商品剩余100

此时A、B同时下单，那么他们同时执行SQL:
	update goods set count=count-1 where id=3 
由于Innodb引擎内部会加锁，所以他们两个即使同一时刻执行，内部也会排序逐步执行。


但是，当商品剩余1个时，就需要注意了。
A:访问页面查看商品剩余1
B:访问页面查看商品剩余1
此时A、B同时下单，那么他们同时执行SQL:
	update goods set count=count-1 where id=3
这样剩余数量就会出现-1，很显然这是不正确的，所以应该怎么办呢?

这种情况下，可以利用排它锁，在更新之前先查询剩余数量，只有数量>0才可以购买，所以，下单时应该执行: 
	start tr ansaction;
    select count from goods where id=1 for update ;
    -- 获取个数进行判断
    if个数>0:
    	update goods set count=count-1 where id=3;
    else:
    	-- 售罄
    commit;

基于Python代码示例：

import pymysql
import threading


def task():
	conn = pymysql.connect (host='127.0.0.1', port=3306, user=' root', passwd='root123', charset="utf8", db='userdb')
	# cursor = conn.cursor(pymysql.cursors.DictCursor),这样可以把fetchone返回的结果变成字典样式。如果不添加参数，则返回元组样式。
	cursor = conn.cursor()
	
    # 开启事务
	conn. begin()

    cursor.execute("select id,age from tran where id=2 for update")
	# fetchall		({"id":1, "age":10},{"id":2, "age":10},)  ((1,10),(2,10))
	# fetchone		{"id":1, "age":10}  (1,10)
    result = cursor.fetchone()
    current_age = result['age']
    
    if current_age > 0:
    	cursor.execute("update tran set age=age-1 where id=2")
    else:
    	print("已售罄")
        
    conn.commit()
    
    cursor.close()
    conn.close()
    
    
def run():
    for i in range(5):
    	t = threading.Thread(target=task)
    	t.start()

2.2 共享锁

共享锁（lock in share mode），可以读，但不允许写。
加锁之后，后续其他事物可以进行读，但不允许写(update、 delete、 insert) ，因为写的默认也会加锁。

Locking Read Examples

Suppose that you want to insert a new row into a table child, and make sure that the child row has a parent row in table parent. Your application code can ensure referential integrity throughout this sequence of operations.
First, use a consistent read to query the table PARENT and verify that the parent row exists. Can you safely insert the child row to table CHILD? No, because some other session could delete the parent row in the moment between your SELECT and your INSERT , without you being aware of it.
To avoid this potential issue, perform the SELECT using LOCK IN SHARE MODE:

SELECT * FROM parent WHERE NAME = ' Jones' LOCK IN SHARE MODE ;

After the LOCK IN SHARE MODE query returns the parent ' Jones' , you can safely add the child record to the CHILD table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in the PARENT table waits until you are finished, that is, until the data in all tables is in a consistent state.

3. 数据库连接池

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AISrTtaM-1639123505697)(C:\Users\lirenjie\AppData\Roaming\Typora\typora-user-images\image-20211206191458891.png)]

在操作数据库时需要使用数据库连接池。

pip install dbutils

import threading
import pymysql
from dbutils.pooled_db import PooledDB

MYSQL_DB_POOL = PooledDB(
    creator=pymysql,	# 使用链接数据库的模块
    maxconnections=50,	# 连接池允许的最大连接数，0和None表示不限制连接数	
    mincached=2,		# 初始化时，链接池中至少创建的空闲的链接，0表示不创建
	maxcached=3,		# 连接池中最多闲置的链接，0和None不限制
    blocking=True,		# 连接池中如果没有可用连接后，是否阻塞等待。True,等待; ralse,等待然后报错
    setsession=[],		# 开始会话前执行的命令列表。如：["set datestyle to ..." , "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。
    # 如∶0 = None = never, 1 = default = whenever it is requested,
    # 2 = when a cursor is created,4 = when a query is executed,7 = always
    host= '127.0.0.1',
    port= 3306,
    user= 'root',
    password= 'root123',
    database= 'userdb',
    charset= 'utf8'
)

def task( ):
	# 去连接池获取一个连接
	conn = MYsQL_DB_POOL.connection()
	cursor = conn.cursor(pymysql.cursors.DictCursor)
    
	cursor.execute('select sleep(2)')
	result =cursor.fetchall()
	print(result)
    
	cursor.close()	
    # 将连接交换给连接池
    conn.close()

def run( ) :
	for i in range(10) :
		t = threading.Thread(target=task)
        t.start()
        
        
if __name__ == '__main__':
	run()

4. SQL工具类

基于数据库连接池开发一个公共的SQL操作类，方便以后操作数据库。

4.1 单例和方法

# db.py
import pymysql
from dbutils.pooled_db import PooledDB


class DBHelper:
    
    def __init__(self):
		#TODO此处配置,可以去配置文件中读取。
        self.pool = PooledDB(
            creator = pymysql,    # 使用链接数据库的模块
            maxconnections = 5,	# 连接池允许的最大连接数，0和None表示不限制连接数
            mincached = 2,		# 初始化时，链接池中至少创建的空闲的链接，0表示不创建
            maxcached = 3,		# 链接池中最多闲置的链接，0和None不限制
            blocking = True,		# 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
            setsession = [],		#开始会话前执行的命令列表。如: ["set datestyle to ...", "set time zone ..."]
            ping = 0,
            # ping MySQL服务端，检查是否服务可用。#如: 0 = None = never，1 = default = whenever it is requested,2 = when a cursor is created,4 = when a query is executed, 7 = always
            host = '127.0.0.1',
            port = 3306,
            user = 'root ',
            password = 'root123', 
            database='userdb', 
            charset='utf8'
        )
    def get_conn_cursor(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn,cursor
    
    def close_conn_cursor(self, *args):
        for item in args:
       		item.close()

    def exec(self, sql, **kwargs):
        '''
        执行一些其他操作
        '''
    	conn,cursor = self.get_conn_cursor()
		cursor.execute(sql, kwargs)
        conn.commite()
        
    def fetch_one(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()
        
        cursor.execute(sql,kwargs)
        result = cursor.fetchone()
        
        self.close_conn_cursor(conn, cursor)
        return result
    
    def fetch_all(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()
        
        cursor.execute(sql, kwargs)
        result = cursor.fetchall()
        
        self.close_conn_cursor(conn,cursor)
        return result

db = DBHelper()

之后通过from db import db就可以直接使用fetch_one等函数，无需每次都连接数据库。

# 用单例模式和方法创建的db.py操作数据库
from db import db

v1 = db.fetch_one(sql="select * from users")
print(v1)

v2 = db.fetch_one("select * from users where id=%(nid)s", nid=3)
print(v2)

4.2 上下文管理

如果你想要让他也支持with上下文管理。

with 获取连接：
	执行SQL（执行完毕后，自动将连接交还给连接池）

import pymysql
from dbutils.pooled_db import PooledDB


POOL= PooledDB(
    creator = pymysql,    # 使用链接数据库的模块
    maxconnections = 5,	# 连接池允许的最大连接数，0和None表示不限制连接数
    mincached = 2,		# 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached = 3,		# 链接池中最多闲置的链接，0和None不限制
    blocking = True,		# 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession = [],		#开始会话前执行的命令列表。如: ["set datestyle to ...", "set time zone ..."]
    ping = 0,
    # ping MySQL服务端，检查是否服务可用。#如: 0 = None = never，1 = default = whenever it is requested,2 = when a cursor is created,4 = when a query is executed, 7 = always
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '', 
    database='userdb', 
    charset='utf8'
)


class Connect(object):
    def __init__(self):
        self.conn = conn = POOL.connection()
        self.cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    
    def __enter__(self):    # __enter__ 和 __exit__ 分别是with语句执行前后执行。
        return self    		# return的结果赋值给了 with...as... as后的object


    def __exit__(self, exc_type, exc_val, exc_tb):	# 在with后的内容执行完毕后执行
        self.cursor.close()
        self.conn.close()
    

    def exec(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        self.conn.commit()


    def fetch_one(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchone()
        return result

    def fetch_all(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchall()
        return result

from db_context import Connect

with Connect() as obj:
    # print(obj.conn)
    # print(obj.cursor)
    ret = obj.fetch_one("select * from users")
    print(ret)


    v2 = obj.fetch_one("select * from users where id=%(nid)s", nid=3)
    print(v2)

5. 其他

navicat，是一个桌面应用，让我们可以更加方便的管理MySQL数据库。

  • mac系统：https://www.macdo.cn/17030.html
  • win系统：
    • 链接：https://lpan.baidu.com/s/13cjbrBquz9vjVqKgWoCQ1w 密码: qstp
    • 链接：https://lpan.baidu.com/s/1JULlIlwQA5s0qN98KP8UXHA 密码: p18f

总结

本节内容比较重要，也是开发中经常会使用到的技能。

  • 事务，解决批量操作同时成功或失败的问题。
  • 锁，解决并发处理的问题。
  • 数据库连接池，解决多个人请求连接数据库的问题。
  • SQL工具类，解决连接数据库代码重复的问题。
  • navicat工具

项目

根据如下的业务需求设计相应的表结构，内部需涵盖如下功能。

  • 注册
  • 登录
  • 发布博客
  • 查看博客列表，显示博客标题、创建时间、阅读数量、评论数量、赞数量等。(支持分页查看)
  • 博客详细，显示博文详细、评论等。
    • 发表评论
    • 赞or踩
    • 阅读数量＋1

可参考如下图片来设计相应的表结构。

zone …"]
ping = 0,
# ping MySQL服务端，检查是否服务可用。#如: 0 = None = never，1 = default = whenever it is requested,2 = when a cursor is created,4 = when a query is executed, 7 = always
host = ‘127.0.0.1’,
port = 3306,
user = 'root ',
password = ‘root123’,
database=‘userdb’,
charset=‘utf8’
)
def get_conn_cursor(self):
conn = self.pool.connection()
cursor = conn.cursor(pymysql.cursors.DictCursor)
return conn,cursor

def close_conn_cursor(self, *args):
    for item in args:
   		item.close()

def exec(self, sql, **kwargs):
    '''
    执行一些其他操作
    '''
	conn,cursor = self.get_conn_cursor()
	cursor.execute(sql, kwargs)
    conn.commite()
    
def fetch_one(self, sql, **kwargs):
    conn, cursor = self.get_conn_cursor()
    
    cursor.execute(sql,kwargs)
    result = cursor.fetchone()
    
    self.close_conn_cursor(conn, cursor)
    return result

def fetch_all(self, sql, **kwargs):
    conn, cursor = self.get_conn_cursor()
    
    cursor.execute(sql, kwargs)
    result = cursor.fetchall()
    
    self.close_conn_cursor(conn,cursor)
    return result

db = DBHelper()


之后通过`from db import db`就可以直接使用fetch_one等函数，无需每次都连接数据库。

```python
# 用单例模式和方法创建的db.py操作数据库
from db import db

v1 = db.fetch_one(sql="select * from users")
print(v1)

v2 = db.fetch_one("select * from users where id=%(nid)s", nid=3)
print(v2)

4.2 上下文管理

如果你想要让他也支持with上下文管理。

with 获取连接：
	执行SQL（执行完毕后，自动将连接交还给连接池）

import pymysql
from dbutils.pooled_db import PooledDB


POOL= PooledDB(
    creator = pymysql,    # 使用链接数据库的模块
    maxconnections = 5,	# 连接池允许的最大连接数，0和None表示不限制连接数
    mincached = 2,		# 初始化时，链接池中至少创建的空闲的链接，0表示不创建
    maxcached = 3,		# 链接池中最多闲置的链接，0和None不限制
    blocking = True,		# 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错
    setsession = [],		#开始会话前执行的命令列表。如: ["set datestyle to ...", "set time zone ..."]
    ping = 0,
    # ping MySQL服务端，检查是否服务可用。#如: 0 = None = never，1 = default = whenever it is requested,2 = when a cursor is created,4 = when a query is executed, 7 = always
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '', 
    database='userdb', 
    charset='utf8'
)


class Connect(object):
    def __init__(self):
        self.conn = conn = POOL.connection()
        self.cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    
    def __enter__(self):    # __enter__ 和 __exit__ 分别是with语句执行前后执行。
        return self    		# return的结果赋值给了 with...as... as后的object


    def __exit__(self, exc_type, exc_val, exc_tb):	# 在with后的内容执行完毕后执行
        self.cursor.close()
        self.conn.close()
    

    def exec(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        self.conn.commit()


    def fetch_one(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchone()
        return result

    def fetch_all(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchall()
        return result

from db_context import Connect

with Connect() as obj:
    # print(obj.conn)
    # print(obj.cursor)
    ret = obj.fetch_one("select * from users")
    print(ret)


    v2 = obj.fetch_one("select * from users where id=%(nid)s", nid=3)
    print(v2)

5. 其他

navicat，是一个桌面应用，让我们可以更加方便的管理MySQL数据库。

  • mac系统：https://www.macdo.cn/17030.html
  • win系统：
    • 链接：https://lpan.baidu.com/s/13cjbrBquz9vjVqKgWoCQ1w 密码: qstp
    • 链接：https://lpan.baidu.com/s/1JULlIlwQA5s0qN98KP8UXHA 密码: p18f

总结

本节内容比较重要，也是开发中经常会使用到的技能。

  • 事务，解决批量操作同时成功或失败的问题。
  • 锁，解决并发处理的问题。
  • 数据库连接池，解决多个人请求连接数据库的问题。
  • SQL工具类，解决连接数据库代码重复的问题。
  • navicat工具

项目

根据如下的业务需求设计相应的表结构，内部需涵盖如下功能。

  • 注册
  • 登录
  • 发布博客
  • 查看博客列表，显示博客标题、创建时间、阅读数量、评论数量、赞数量等。(支持分页查看)
  • 博客详细，显示博文详细、评论等。
    • 发表评论
    • 赞or踩
    • 阅读数量＋1

可参考如下图片来设计相应的表结构。

Grit_Lrj
关注
  • 0
    点赞
  • 踩
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
5d7y.net index.php,webshell/php/oi.php.txt at f875e7b78085777b8a10c3456478dbd192d851a1 · tennc/websh...
weixin_35797963的博客
03-11 13万+
eval("?>".gzuncompress(base64_decode("eJzsvXtbG0eyOPz/eZ7zHZqJYkuxEALbuYAhwYBjToxhAa83P+BVRpqRNEHSKDMjLsn6u79V1feeHkmQZE/2POvd2Jq+VFdXd1dXV1dVv/p2Opz+9399ttwfLMjif7DDyU0YJVHKNtrtb1jpDxaM4n4yietP82H...
QQFM 中转站(囧转站)OOXX V1.1 by wy811007 (附SkinH_Net的使用) 程序失效 更新1.3版 未发布...
12-28 394
QQFM 中转站(囧转站)OOXX V1.1 by wy811007(原创) 话说我圣诞时候在群里发布了 现在发到这里 别的不多说了 你们知道怎么用 要低调 输入网址 点击ooxx... PS 经测试 获得的地址 如果服务器上不删文件 还是可以下载的 程序隐藏方式 哈哈 自己琢磨下吧.. 吼吼 程序就在上面图片里 PW 自己猜 这个运用了SkinH_...
CSU1826-Languages-AC自动机
君安在AreYouWell
06-28 22万+
P: LanguagesDescriptionThe Enterprise has encountered a planet that at one point had been inhabited. The only remnant from the prior civilization is a set of texts that was found. Using a small set of
hxyx4xyzindex php vo,webshell/php/oi.php.txt at da33d92ba83c1f8d44e15957022ed4a03423a699 · tennc/web...
weixin_31163455的博客
03-26 24万+
eval("?>".gzuncompress(base64_decode("eJzsvXtbG0eyOPz/eZ7zHZqJYkuxEALbuYAhwYBjToxhAa83P+BVRpqRNEHSKDMjLsn6u79V1feeHkmQZE/2POvd2Jq+VFdXd1dXV1dVv/p2Opz+9399ttwfLMjif7DDyU0YJVHKNtrtb1jpDxaM4n4yietP82H...
各种技术网站的网址
wenyawen的专栏
09-09 5084
http://www.eclipse.com/http://www.apache.org
MySQL数据库运维.pdf
06-16
随着去 IOE 运动的如火如荼，MySQL 数据库已经深入 到传统行业，大有改变行业格局。而与此同时，MySQL 数据库规模成倍的增长， 如何快速定位问题，解决问题？如何规模化、自动化运维？如何进行优化，提高 MySQL ...
c# mysql数据库连接池实现
03-24
c#语言下，使用netcore2.1框架，调用netstandard数据库连接池组件
mysql数据库
07-27
mysql数据库开发工具，免安装解压即可用，不占内存。里面带文档和可视化开发界面。
麒麟V10安装MySQL数据库
03-17
在虚拟机上安装一个国产系统麒麟V10，在麒麟V10上安装MySQL数据库
pbootcms数据sqlite转mysql数据库
08-14
sqlite数据库转mysql数据库
mysql数据库实验报告 数据表的操作
07-27
MySQL数据库的创建、查看、删除、使用命令。 表结构创建和修改、表约束的创建和修改； 表数据的插入、删除和修改； 表联系的创建和修改。
Qt实现MySQL数据库读写
10-20
因为Qt没有MySQL库，所以利用QODBC来实现MySQL数据库读写，注意看工程下的《Qt导入MYSQL.docx》文档
国家开放大学 MySQL数据库应用 实验训练1 在MySQL中创建数据库和表
06-05
"国家开放大学 MySQL数据库应用实验训练1 在MySQL中创建数据库和表" 本实验训练旨在让学生掌握 MySQL 中的数据库和表的创建过程。 MySQL 是一种关系型数据库管理系统，广泛应用于各种领域。为提高学生的实践能力，...
贪心法
Me的专栏
05-07 22万+
贪心法 贪心算法并不是从整体最优上加以考虑， 而是从局部最优考虑， 每次总是做出当前看起来最好的选择，在某种意义上的局部最优选择； 最优子结构性质 ： 贪心选择性质： 1、  活动安排 ： 问题描述 ：设有n个活动集合E = {1 ,2,…,n} ，其中每个活动i都要求使用同一资源， 而在同一时间内只有一个活动能使用这一资源。每个活动i都有一个要求使用该资源的起始
python爬虫爬美女图片：“小甲鱼056节轮一只爬虫的自我修养4：OOXX” 最终更新2021.6.27日，更新后可用！！！
Alocus的博客
10-25 8313
需要改动有两个方面： 第一方面，网址编码有变化，如http://jandan.net/ooxx/MjAyMDEwMjUtODY=#comments，尾部的为base64编码，在网址构造时对应处使用base64编码.即可。 第二方面，图片地址发生了小改变，只需要将图片地址加入列表时，增加 http：即可。 代码如下 import urllib.request import os import base64 def url_open(url): opener = urllib.req...
》》css3--动画
weixin_33690367的博客
12-06 9万+
<!DOCTYPE html> <html> <head> <meta charset="utf-8" /> <title>功能特权-QQ登录</title> <link rel="Shortcut Icon" href="im...
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>Title</title> <sty
热门推荐
u014497548的博客
06-03 51万+
Title img,div,body{margin: 0;padding: 0;} .orgindiv {width: 200px;height: 200px;position: relative;float: left;margin-left: 100px;} .orgindiv img{width: 100%;height: 100%;}
mysql数据库调优
最新发布
05-28
MySQL数据库调优是针对MySQL数据库进行性能优化的过程，旨在通过优化数据库服务器、数据库结构、SQL查询语句等方面的设置和调整，以提高MySQL数据库的性能和可扩展性。具体来说，MySQL数据库调优包括以下几个方面： ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值