发布日期: 2021-01-26

数据库和表

数据库操作

列出所有数据库

我们可以使用如下SQL语句 SHOW DATABASES; 来查看系统中目前有哪些数据库。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

创建、删除数据库

我们可以执行如下SQL语句来创建名为jn的数据库

CREATE DATABASE jn;

指定utf8的编码，需要这样写

CREATE DATABASE jn CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_520_ci;

删除一个数据库，使用命令 DROP DATABASE

mysql> DROP DATABASE jn;
Query OK, 0 rows affected (0.03 sec)

注意，SQL 语句的关键字是大小写不敏感的，但是上面的数据库名字是大小写敏感的，你不能这样写 DROP DATABASE JN;

数据库导出、导入

导出:

执行下面的命令可以把jn数据库里面所有的数据导出为sql数据文件

mysqldump –u username –p --databases dbname > data.sql

导入:

执行下面的命令可以把导出为sql数据文件的数据导入到数据库byhy中

mysql –u username –p  < data.sql

表的操作

要对表进行操作，首先要指明你操作的是哪个数据库。应该使用 use ，如下所示

mysql> use jn;
Database changed

use byhy; 表示我们要操作的数据库名称是 jn。

列出当前数据库中所有表

我们可以使用如下SQL语句 show tables; 来查看当前数据库里有哪些表。

mysql> show tables;
Empty set (0.00 sec)

创建表

要创建表，最主要的是要指明表中的各个字段

可以执行如下SQL语句来创建名为user的表

CREATE TABLE user (
  id int NOT NULL AUTO_INCREMENT,
  username varchar(150)  NOT NULL,
  `password` varchar(128)  NOT NULL,
  realname varchar(30)  NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE 就是创建表的 SQL 语句后面跟表的名字，这里就是 user 作为表名。

接下来括号里面的是表中各列的数据类型、特性的描述。

前面 4 行指定了该表中有 4 列，每列的名字分别为 id、username、password、realname。

其中 id 是整数类型，其他的都是字符串类型。

由于 password正好是MySQL的一个关键字，我们的字段名如果和关键字重名，应该用反引号把字段包起来，表示这不是关键字。

其中：

NOT NULL 表示这列的数据不能为空。就是每条记录，这一列里面一定要有数据内容。

AUTO_INCREMENT 表示该列中的字段内容是自动递增的，也就是从1开始，添加一条记录id字段的值就自动加1

通常每张表，就应该有个 AUTO_INCREMENT 的 id字段，为每条记录产生一个编号。

PRIMARY KEY (id) 这行指定了id这列作为主键。

可以通过命令行 SHOW CREATE TABLE user\G 来查看创建表的语句，从而查看表的结构

mysql> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(150) NOT NULL,
  `password` varchar(128) NOT NULL,
  `realname` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)****

删除表

使用命令 DROP TABLE

mysql> DROP TABLE user;
Query OK, 0 rows affected (0.01 sec)

注意，SQL 语句的关键字是大小写不敏感的，但是上面的表名字是大小写敏感的，你不能这样写 DROP DATABASE USER;

更改表名字

要更改表的名字，可以使用命令 RENAME TABLE ，可以一次修改多个表的名字。

mysql> RENAME TABLE user TO user, medicine TO jn;
Query OK, 0 rows affected (0.01 sec)

修改表结构

修改表结构包括很多种处理：添加、删除、重命名列，添加、删除、修改索引、主键等等。

具体可以参考 MySQL官方网站

这里列出几个，比较常见的操作。

添加列

如果要在user表中添加两列 description 和 qq

mysql> ALTER TABLE user ADD `description` varchar(850) NOT NULL, ADD `qq` varchar(20) NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除列

如果要在user表中删除两列 description 和 qq

mysql> ALTER TABLE user DROP `description` , DROP `qq`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改列

如果要在user表中修改列 realname 名字为 realname1

mysql> ALTER TABLE user CHANGE  `realname` `realname1`  varchar(30) NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果要在user表中修改列 realname 名字为 realname1， 并且修改类型 为 char(30)

mysql> ALTER TABLE user CHANGE  `realname` `realname1`  char(30) NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

插入表记录

前面我们使用如下SQL语句创建了名为user的表

CREATE TABLE user (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(150)  NOT NULL,
  `password` varchar(128)  NOT NULL,
  `realname` varchar(30)  NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

这还是一张空表，要添加一条记录，使用 INSERT INTO 语句，

INSERT INTO user (username,`password`,realname) VALUES ('jn1','password1','江南1');

添加多条记录

如果要添加多条记录，非常简单，如下所示

INSERT INTO user (username,`password`,realname) VALUES 
    ('jn1','password1','江南1'),
    ('jn2','password1','江南2‘),
    ('jn3','password1','江南3'),
    ('jn4','password1','江南4');

查询、修改、删除表记录

查询表记录

select * from user;

*代表查询所有的列

这个user表，我们只想查看 id和username 这两列的信息，就可以这样写

select id,username  from user;

查询过滤

我们有时候只需要查询其中符合某些条件的部分信息。这就叫过滤查询。

根据进过滤条件查询，我们使用 where从句

过滤条件

比如，上面的user表，我们只需要查询用户名为 jn3 的那个用户的信息，就可以这样

select *  from user where username='jn3';

其中 username='jn3' 里面的等号就是一个过滤条件的 操作符 ，表示过滤条件是查找记录的username字段值等于jn3。

如果我们只需要查询 id为 2 的那个用户的信息，就可以这样

select *  from user where id=2;

除了等于条件（使用 =），还有

不等于（使用 <> 或者 !=）

select *  from user where id!=1;

大于（使用 > ）

select *  from user where id>3;

小于（使用 < ）

select *  from user where id<3;

大于等于（使用 >= ）

select *  from user where id>=3;

小于等于（使用 <= ）

select *  from user where id<=3;

包含字符（使用 like 和 % ）

/* 以 jn 开头*/
select *  from user where username like "jn%";
/* 以 33 结尾*/
select *  from user where username like "%33";
/* 以 jn 开头 并且 以 33 结尾*/
select *  from user where username like "byhy%33";
/* 包含 jn */
select *  from user where username like "%jn%";

在集合里面（使用 in）

/* id 在指定集合里面 */
select * from user where id in (1,2,3);

/* usernmae 在指定集合里面 */
select * from user where username  in ("jn1","jn2");

并且关系

select *  from customer1 where id > 10 and level<15;

或者关系

如果你要查询的记录过滤条件是多个， 只要满足其中一个条件即可 ，就使用 or 关键字连接多个过滤条件

比如，我们要查询 id > 10 或 等级 level值小于 15的用户。就可以这样

select *  from customer1 where id > 10 or level<15;

修改表记录

我们经常需要更新表记录信息，比如一个用户更新了他的头像，消费后余额更新等等。

MySQL更新记录使用的是 update 语句。

假如已有customer1 表，如果我们要更新其中用户名为 cus16 这条记录，把coin值设置为 100，就可以这样写

update customer1 set coin=100 where username='cus16';

注意，这里也用到了 where 从句，限制了更新的只是 username为cus16的这些记录。

如果没有where 从句，像这样

update customer1 set coin=100;

就会更新该表中所有的记录，把所有的记录的coin 全部设置为 100

删除表记录

我们有时也需要删除表记录。

MySQL删除记录使用的是 delete from 语句。

前面我们曾经创建过customer1 表，如果我们要删除其中cus16这条记录，可以这样写

delete from customer1  where username='cus16';

注意，这里也用到了where 从句，限制了删除的只是 username为cus16的这些记录。

否则，如果没有where 从句，像这样

delete from customer1;

就会删除该表中所有的记录。

读取数据库表内容

我们来看下面这样的一个读取数据库表的例子。

import MySQLdb

# 创建一个 Connection 对象，代表了一个数据库连接
connection = MySQLdb.connect(
                host="192.168.0.100",# 数据库IP地址  
                user="username",     #  mysql用户名
                passwd="xxxxx",      # mysql用户登录密码
                db="dbname" ,        # 数据库名
                # 如果数据库里面的文本是utf8编码的，
                #charset指定是utf8
                charset = "utf8")   

# 返回一个 Cursor对象
c = connection.cursor()

# 执行一个获取 users 表中所有记录的 sql 语句
c.execute("""SELECT * FROM users """)


# rowcount属性记录了最近一次 execute 方法获取的数据行数
numrows = c.rowcount

for x in range(numrows):
    # fetchone 方法返回的是一个元组，
    # 代表获取的一行记录，元组里面每个元素代表一个字段
    row = c.fetchone()
    print(row)

我们可以发现，对数据库的操作是 通过SQL语句 进行的。

我们的代码需要先创建一个 Connection 对象，然后再通过Connection 对象创建一个Cursor 对象。

最后使用Cursor对象的execute方法，传入要数据库服务执行的SQL语句。

调用execute执行完SQL语句后，cursor 对象的 fetchone 方法是获取一行记录。

fetchone 方法返回的是一个元组，代表获取的一行记录，元组里面每个元素代表一个字段。

上面的代码通过一个for循环，可以依次获取到数据库的记录行。

我们还可以用 fetchmany 方法来获取多行记录，该方法的参数就是要获取记录的条数，比如

# 执行一个获取 users 表中所有记录的 sql 语句
c.execute("""SELECT * FROM users """)

# fetchmany方法返回的是一个元组，
# 里面每个元素也是元组，代表一行记录
rows = c.fetchmany(2)
print(rows)

我们还可以用 fetchall 方法来获取所有记录，比如

# 执行一个获取 users 表中所有记录的 sql 语句
c.execute("""SELECT * FROM users """)

# fetchall方法返回的是一个元组，
# 里面每个元素也是元组，代表一行记录
rows = c.fetchall()
print(rows)

事务

为什么需要事务机制

我们设计了一个图书馆系统，可以管理图书借阅信息。

已经有用户表user、图书表book 和借阅登记表borrow。

如下所示：

CREATE TABLE user (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username varchar(150)  NOT NULL,
  `password` varchar(128)  NOT NULL,
  realname varchar(30)  NOT NULL
) ;


CREATE TABLE book (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name varchar(150)  NOT NULL,
  `desc` varchar(128)  NOT NULL,
  status int  NOT NULL
) ;


CREATE TABLE borrow (
  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id      int  NOT NULL,
  book_id  int  NOT NULL,
  
  FOREIGN KEY (user_id)
   REFERENCES user(id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT,

  
  FOREIGN KEY (book_id)
   REFERENCES book(id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
) ;

其中

book 表中的status 字段记录了某本图书的状态值为 0 表示没有借出， 1 表示借出了。

借阅登记表 borrow 里面的user_id就是借阅者id，是一个外键，引用了 user表，而book_id 也是一个外键，引用了 book表。

当一个用户（假设id为 666）借阅某本图书（假设id为 888）时，系统要对 两张表 进行操作，

修改book 表中 id为 888 的记录中的 status值为1 表示借出
在 borrow 表中添加一个记录，记录是谁借出了哪本书。

似乎，这个问题不大，学习了前面的课程，大家可以很轻易的写出如下两段SQL

update book set status=1 where id=888;

insert into borrow  (user_id,book_id) VALUES (666,888);

朋友，想想，这里面有什么问题吗？

我们分析这个业务需求，这两个操作必须一起完成。

如果出现第一个SQL操作（修改图书状态）完成了，第二个操作由于某种原因却失败了。这就会导致数据状态不一致的问题。

这种事情在安全性健壮性要求特别高的金融系统中是万万不允许出现的。

解决这个问题的办法，就是使用数据库服务的 事务(transaction) 机制

事务和 autocommit

对于InnoDB格式的表，所有的客户端请求都是在事务中处理的。每个事务必须提交才能生效。

MySQL服务对每个客户端连接有个设置开关： autocommit 。

如果值为 1 ，处于自动提交模式

执行 每条 SQL语句，都是自动放入一个事务中，执行完该语句后， 自动提交 事务
如果值为 0 ，处于手动提交模式

手动提交模式打开后，自动创建一个事务；执行一条SQL语句后， 不会自动提交 事务，接着等待执行后续的SQL语句。

所有已经执行的SQL语句的结果，都是临时保存在缓冲区 innodb_log_buffer 中，外部看不到这些改动

如果接收执行了一条 COMMIT 语句，就把前面所有的更新从缓冲区写入磁盘生效，并且自动开始下一个事务。这时，外部客户端可以看见更新生效了。

如果接收执行了一条 ROLLBACK 语句，就把前面所有缓冲的更新删除，并且自动开始下一个事务。这时，外部客户端发现数据库状态几乎和操作前一样，没有更新。（除了一些 AUTO_INCREMENT字段的当前自增值可能发生了改变）

当 MySQL 客户端连接到服务端，缺省 autocommit 值就是1，表示自动提交。这就是为什么我们在 MySQL命令行客户端 执行每条SQL语句都不需要执行 COMMIT，就可以生效的原因。

如果，我们想取消自动提交模式，执行如下命令即可

SET autocommit = 0;

而前面我们学习过的python mysql 客户端库 mysqlclient 创建连接后，该库底层就自动执行了上面的命令，取消了自动提交模式。这就是为什么，我们执行完每个改动数据库的操作都要commit 一下的原因。

更多关于 autocommit的信息，点击这里参考官方文档

如果，一个客户端连接当前是 自动提交模式（autocommit为1），根据前面的描述，我们知道每条SQL语句执行都是自动放入一个事务中，自动提交了。

如果，我们想把 多条SQL语句 放入 同一个事务 中，可以这样：

-- 1. 创建一个 多条SQL语句 事务
START TRANSACTION;

-- 2. 更新book 表
update book set status=1 where id=888;

-- 3. 更新 borrow表
insert into borrow  (user_id,book_id) VALUES (666,888);

-- 4. 提交事务 
COMMIT;
-- 或者回滚事务
ROLLBACK;

如果在执行完第3步，没有错误，更新borrow表成功了，整个事务里面的操作都没有问题，我们可以选择执行语句 COMMIT 来提交事务，这样第2步，和第3步的更新操作才会真正成功。

如果在执行完第3步，更新borrow表失败了，我们的程序可以选择执行 ROLLBACK 来回滚事务，这样第2步的更新操作也会撤销。

如果，一个客户端连接当前是 手动提交模式（autocommit为0），

多条SQL语句自动放入事务中，如下所示：

-- 开始自动处于一个事务中，无需执行  START TRANSACTION;

-- 1. 更新book 表
update book set status=1 where id=888;

-- 2. 更新 borrow表
insert into borrow  (user_id,book_id) VALUES (666,888);

-- 3. 提交事务 ，并且自动启动下一个事务
COMMIT;
-- 或者回滚事务 ，并且自动启动下一个事务
ROLLBACK;

可以发现自动提交模式和手动提交模式的区别在于手动提交模式，开始不需要我们执行 START TRANSACTION; 显式的创建一个事务

通常我们开发程序操作数据库，建议使用手动提交模式，这样如果一批操作中途出现问题时，可以很方便的回复数据库到最初的数据状态。

四种类型的 SQL 语句

SQL 包括四种类型的语句：

DDL（Data Definition Language）数据定义语言，它处理数据库和表的结构定义，比如 CREATE，DROP，ALTER等。

DML（Data Manipulation Language）数据操作语言，它处理具体的数据内容，包括最常见的SQL语句，如SELECT，INSERT，UPDATE，DELETE等，它用于存储，修改，检索，删除和更新数据库中的数据。

DCL（Data Control Language）数据控制语言，包括GRANT等命令，主要涉及数据库系统的权限等控制。

TCL（Transaction Control Language）是事务控制语言的简称，包括BEGIN Transaction、COMMIT Transaction、ROLLBACK Transaction等命令，主要涉及数据库系统的权限，权限和其他控制。

姜楠

http://www.jiangnan.press/2021/0126undefined.html

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源姜楠 !

mysql

Pytest

2021-05-06 姜楠

pytest

Mysql 安装

2021-01-25 姜楠

mysql

Mysql应用

数据库和表

数据库操作

列出所有数据库

创建、删除数据库

数据库导出、导入

表的操作

列出当前数据库中所有表

创建表

删除表

更改表名字

修改表结构

添加列

删除列

修改列

插入表记录

添加多条记录

查询、修改、删除表记录

查询表记录

查询过滤

过滤条件

修改表记录

删除表记录

读取数据库表内容

事务

为什么需要事务机制

事务 和 autocommit

四种类型的 SQL 语句

你的赏识是我前进的动力

事务和 autocommit