Python学习之复习MySQL-Day1（DDL）

---

文章声明⭐⭐⭐

1. 该文章为我（有编程语言基础，非编程小白）的 MySQL复习笔记
2. 知识来源为 B站UP主（黑马程序员）的MySQL课程视频，归纳为自己的语言与理解记录于此并加以实践
3. 此前我已经学习过了MySQL，现在是在复习阶段，所以不是面向小白的教学文章
4. 不出意外的话，我大抵会 持续更新
5. 想要了解前端开发（技术栈大致有：Vue2/3、微信小程序、uniapp、HarmonyOS、NodeJS、Typescript）与Python的小伙伴，可以关注我！谢谢大家！

---

让我们开始今天的学习吧！

SQL的分类

• DDL(Data Definition Language)：数据定义语言，主要用来定义数据库对象（数据库、表、字段）
• DML(Data Manipulation Language)：数据操作语言，凡是对表当中的数据进行增删改查的都是操作语言
• DQL(Data Query Language)：数据查询语言，凡是带有select关键字的都是查询语句
• DCL(Data Control Language)：数据控制语言，可对数据访问权进行控制的指令

DDL

操作数据库

查询所有数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

查询当前数据库

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

创建数据库

mysql> create database RichieSQL;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| richiesql          |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql> create database richiesql; # 已存在改名字的数据库，所以报错
ERROR 1007 (HY000): Can't create database 'richiesql'; database exists

mysql> create database if not exists richiesql; # if not exists意为如果不存在便创建，如果已存在也不会报错
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> drop database richiesql; # 删除掉准备重新创建
Query OK, 0 rows affected (0.03 sec)

mysql> create database if not exists richiesql default charset utf8mb4; # default charset 意为指定字符集
Query OK, 1 row affected (0.04 sec)

使用数据库

mysql> use richiesql;
Database changed

mysql> select database();
+------------+
| database() |
+------------+
| richiesql  |
+------------+
1 row in set (0.00 sec)

删除数据库

mysql> drop database richiesql;
Query OK, 0 rows affected (0.05 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

mysql> drop database richiesql; # 删除不存在的数据库会报错
ERROR 1008 (HY000): Can't drop database 'richiesql'; database doesn't exist

mysql> drop database if exists richiesql; # if exists 意为如果存在便删除，如果不存在也不会报错
Query OK, 0 rows affected, 1 warning (0.03 sec)

---

操作表

查询当前数据库中的所有表

mysql> create database richie; # 创建数据库
Query OK, 1 row affected (0.04 sec)

mysql> select database(); # 查询当前数据库
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use richie; # 使用数据库
Database changed
mysql> show tables; # 查询当前数据库中的所有表，刚创建的新表，所以为空
Empty set (0.00 sec)

创建表

结构如下：

id	name	age	gender
1	张三	20	男
2	李四	21	女
3	王五	22	男

创建结构（不含内容）：

mysql> create table user( # 此处user为表名
    -> id int comment '编号', # 此处id为字段名，int为字段类型，comment可以为这个字段打备注
    -> name varchar(50) comment '姓名', # 此处的50表示字符串最大50个字符
    -> age int comment '年龄',
    -> gender varchar(1) comment '性别'
    -> ) comment '用户表'; # 这里的comment为整个表打备注
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_richie |
+------------------+
| user             |
+------------------+
1 row in set (0.00 sec)

查询表结构

mysql> desc user;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(1)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

查询当时创建表时的sql语句

mysql> show create table user;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` (
  `id` int DEFAULT NULL COMMENT '编号',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表' |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

向表中添加字段

mysql> alter table user add nickname varchar(20) comment '昵称';
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(50) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| gender   | varchar(1)  | YES  |     | NULL    |       |
| nickname | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

修改字段数据类型

mysql> alter table user modify name varchar(20);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| gender   | varchar(1)  | YES  |     | NULL    |       |
| nickname | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

同时修改字段名和字段的数据类型

mysql> alter table user change name username varchar(10);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(10) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| gender   | varchar(1)  | YES  |     | NULL    |       |
| nickname | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

删除字段

mysql> alter table user drop nickname;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(10) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| gender   | varchar(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

修改表名

mysql> alter table user rename to emp;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_richie |
+------------------+
| emp              |
+------------------+
1 row in set (0.00 sec)

删除表

mysql> drop table if exists user; # 删除了整张表，数据和表结构都删除了
Query OK, 0 rows affected, 1 warning (0.03 sec)

删除表并重新创建该表

mysql> truncate table emp; # 删除了表，又重新创建了该表，数据删除了但表结构还在
Query OK, 0 rows affected (0.08 sec)

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(10) | YES  |     | NULL    |       |
| age      | int         | YES  |     | NULL    |       |
| gender   | varchar(1)  | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)