操作MySQL2¶

一. 修改表的结构¶

1. 修改字段的数据类型¶

alter table 表名 modify 字段名修改后的数据类型修饰符;

例如：

Ⅰ. 将student表中“学号”字段的数据类型修改为char(20)、not null、primary key

mysql> alter table student modify `学号` char(20) not null;

Ⅱ. 修改字段的名称和数据类型

mysql> alter table 表名 change 旧字段名新字段名数据类型修饰符

例如：

将student表中“年龄”字段修改为“出生年月”数据类型为datetime且不允许为空

mysql> alter table student change `年龄` `出生日期` datetime not null;

Ⅲ. 格式字段

格式：

alter table 表名 add 字段名数据类型 [修饰符]; #添加在最后一列

alter table 表名 add 字段名数据类型 [修饰符] first; #添加在第一列

alter table 表名 add 字段名数据类型 [修饰符] after 字段名 #添加到指定列后

案例：

mysql> alter table student add 爱好 varchar(30);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student add `编号` smallint unsigned not null first;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student add `班级` char(20) not null after `ssex`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table student \G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `编号` smallint unsigned NOT NULL,
  `sno` char(20) NOT NULL COMMENT '学生学号',
  `sname` varchar(20) NOT NULL COMMENT '学生姓名',
  `sage` tinyint unsigned NOT NULL COMMENT '学生年龄',
  `ssex` enum('0','1') NOT NULL DEFAULT '1' COMMENT '学生性别(1男0女)',
  `班级` char(20) NOT NULL,
  `sbrithday` date DEFAULT NULL COMMENT '学生生日',
  `class` varchar(60) NOT NULL COMMENT '学生班级',
  `爱好` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Ⅳ. 删除字段

格式：

alter table 表名 drop 字段名；

案例：

mysql> alter table student drop `爱好`;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student drop `编号`;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table student drop `班级`;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table student \G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `sno` char(20) NOT NULL COMMENT '学生学号',
  `sname` varchar(20) NOT NULL COMMENT '学生姓名',
  `sage` tinyint unsigned NOT NULL COMMENT '学生年龄',
  `ssex` enum('0','1') NOT NULL DEFAULT '1' COMMENT '学生性别(1男0女)',
  `sbrithday` date DEFAULT NULL COMMENT '学生生日',
  `class` varchar(60) NOT NULL COMMENT '学生班级',
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Ⅴ. 修改表名

格式：

mysql> alter table 旧表名 rename 新表名

mysql> alter table student rename stu;
Query OK, 0 rows affected (0.01 sec)

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

二. 删除表¶

格式：

mysql> drop table if exists 表名;

例如：

mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| stu               |
| student           |
+-------------------+
2 rows in set (0.00 sec)

mysql> drop table if exists stu;
Query OK, 0 rows affected (0.01 sec)

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

三. SQL语句的分类¶

DDL(数据定义语句)：create、alter、drop

DML(数据操纵语句)：增删改 insert、delete、update

DQL(数据查询语句)：查 select

DCL(数据控制语句)：grant、revoke

四. DML语句(操作表中的数据)¶

1. 增加insert¶

Ⅰ. 格式：

insert into 表名 [(字段名1,字段名2,字段名3)] values(值1,值2,值3...)

Ⅱ. 说明：

如果字段不写，则是表中的所有字段。
如果写字段名，就必须写上表中出了有自增、默认、值可为空的字段名外的所有字段。
值必须与器一一对应（顺序及数据类型）

Ⅲ. 案例：

a. 向stu表中插入数据

mysql> insert into student values('1035400','jerry','20','0','2003-5-15','AIO169');
Query OK, 1 row affected (0.00 sec)

mysql> desc student;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| sno       | char(20)         | NO   | PRI | NULL    |       |
| sname     | varchar(20)      | NO   |     | NULL    |       |
| sage      | tinyint unsigned | NO   |     | NULL    |       |
| ssex      | enum('0','1')    | NO   |     | 1       |       |
| sbirthday | date             | YES  |     | NULL    |       |
| class     | varchar(60)      | NO   |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> select * from student;
+---------+-------+------+------+------------+--------+
| sno     | sname | sage | ssex | sbirthday  | class  |
+---------+-------+------+------+------------+--------+
| 1035400 | jerry |   20 | 0    | 2003-05-15 | AIO169 |
+---------+-------+------+------+------------+--------+
1 row in set (0.00 sec)

b. 向student表中一次性插入多行数据

mysql>  insert into student values
    -> ('1035401','Alice','19','0','2004-5-16','AIO169'),
    -> ('1035402','Bob','18','0','2005-5-16','AIO169'),
    -> ('1035403','Mary','21','1','2002-5-16','AIO169');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+---------+-------+------+------+------------+--------+
| sno     | sname | sage | ssex | sbirthday  | class  |
+---------+-------+------+------+------------+--------+
| 1035400 | jerry |   20 | 0    | 2003-05-15 | AIO169 |
| 1035401 | Alice |   19 | 0    | 2004-05-16 | AIO169 |
| 1035402 | Bob   |   18 | 0    | 2005-05-16 | AIO169 |
| 1035403 | Mary  |   21 | 1    | 2002-05-16 | AIO169 |
+---------+-------+------+------+------------+--------+
4 rows in set (0.00 sec)

2. 修改表中的数据¶

格式：

update 表名 set 字段名1=修改后的值[字段名2=值,字段名3=值...] [where 条件]

*注：

如果不加where条件，则会将表中该列值全部修改
一条update语句可以同时修改符合条件的多列

案例：

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

mysql> create table tl(name char(10),age tinyint unsigned not null);
Query OK, 0 rows affected (0.02 sec)

mysql> desc tl;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name  | char(10)         | YES  |     | NULL    |       |
| age   | tinyint unsigned | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into tl values('aa',20),('bb',18),('cc',19),('dd',20);
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from tl;
+------+-----+
| name | age |
+------+-----+
| aa   |  20 |
| bb   |  18 |
| cc   |  19 |
| dd   |  20 |
+------+-----+
4 rows in set (0.00 sec)

mysql> select * from tl;
+------+-----+
| name | age |
+------+-----+
| aa   |  20 |
| bb   |  18 |
| cc   |  19 |
| dd   |  20 |
+------+-----+
4 rows in set (0.00 sec)

mysql> update tl set age=23;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from tl;
+------+-----+
| name | age |
+------+-----+
| aa   |  23 |
| bb   |  23 |
| cc   |  23 |
| dd   |  23 |
+------+-----+
4 rows in set (0.00 sec)

mysql> update tl set age=19 where name='bb';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from tl;
+------+-----+
| name | age |
+------+-----+
| aa   |  23 |
| bb   |  19 |
| cc   |  23 |
| dd   |  23 |
+------+-----+
4 rows in set (0.00 sec)

3. 删除(delete)表中符合条件的数据¶

格式：

delete from 表名 [where 条件]

*注：

如果不加where条件，则会将表中该列值全部删除

案例：

将tl表中age大于19的数据全部删除

mysql> delete from tl where age>19;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from tl;
+------+-----+
| name | age |
+------+-----+
| bb   |  19 |
+------+-----+
1 row in set (0.00 sec)

mysql> insert into tl values('aa',23),('bb',16);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select *from tl;
+------+-----+
| name | age |
+------+-----+
| bb   |  19 |
| aa   |  23 |
| bb   |  16 |
+------+-----+
3 rows in set (0.00 sec)

mysql> delete from tl;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from tl;
Empty set (0.00 sec)