MySQL学习记录—常用语句集

深渊向深渊呼唤
MySQL学习记录—常用语句集

文章目录

修改表名 修改表选项
修改字段名 修改字段类型 添加字段 删除字段
SELECT SELECT 子查询语句 LIKE 模糊查询 IN 和 NOT IN 范围查询

修改表名

语法

alter table 旧表名 rename to 新表名;

或

rename table 旧表名 to 新表名;

示例

mysql> show tables;
+---------------------+
| Tables_in_studentms |
+---------------------+
| student             |
| teacher             |
+---------------------+
2 rows in set

<-- 将表名teacher 修改成 assistant -->
mysql> alter table teacher rename to assistant;
Query OK, 0 rows affected

mysql> show tables;
+---------------------+
| Tables_in_studentms |
+---------------------+
| assistant           |
| student             |
+---------------------+
2 rows in set

<-- 将表名assistant 修改成 teacher -->
mysql> rename table assistant to teacher;
Query OK, 0 rows affected

mysql> show tables;
+---------------------+
| Tables_in_studentms |
+---------------------+
| student             |
| teacher             |
+---------------------+
2 rows in set

修改表选项

语法

alter table 表名 character set=utf8;

示例

<-- 修改字符集为: utf8 -->
mysql> alter table teacher character set=utf8;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

<-- 修改存储引擎为: InnoDB -->
mysql> alter table teacher engine=InnoDB;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

MySQL学习记录—常用语句集
修改字段名

语法

alter table 表名 change 旧字段名 新字段名 字段类型;

示例

mysql> select *from teacher;
+----------------+------+------+
| assistant_name | age  | sex  |
+----------------+------+------+
| NULL           | NULL | NULL |
+----------------+------+------+
1 row in set

<-- 将字段名assistant_name 修改成 teacher_name -->
mysql> alter table teacher change assistant_name teacher_name varchar(20);
Query OK, 1 row affected
Records: 1  Duplicates: 0  Warnings: 0

mysql> select *from teacher;
+--------------+------+------+
| teacher_name | age  | sex  |
+--------------+------+------+
| NULL         | NULL | NULL |
+--------------+------+------+
1 row in set

修改字段类型

语法

alter table 表名 modify 字段名 字段类型;

示例

mysql> describe teacher;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| teacher_name | varchar(20) | YES  |     | NULL    |       |
| age          | int(2)      | YES  |     | NULL    |       |
| sex          | char(2)     | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
3 rows in set

<-- 将teacher_name字段的类型 修改成 int类型 -->
mysql> alter table teacher modify teacher_name int(10);
Query OK, 1 row affected
Records: 1  Duplicates: 0  Warnings: 0

mysql> describe teacher;
+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| teacher_name | int(10) | YES  |     | NULL    |       |
| age          | int(2)  | YES  |     | NULL    |       |
| sex          | char(2) | YES  |     | NULL    |       |
+--------------+---------+------+-----+---------+-------+
3 rows in set

添加字段

语法

alter table 表名 add 字段名 字段类型;

示例

mysql> select *from teacher;
+--------------+------+------+
| teacher_name | age  | sex  |
+--------------+------+------+
| NULL         | NULL | NULL |
+--------------+------+------+
1 row in set

<-- 向teacher表中添加addr字段,类型为varchar类型 -->
mysql> alter table teacher add addr varchar(50);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> select *from teacher;
+--------------+------+------+------+
| teacher_name | age  | sex  | addr |
+--------------+------+------+------+
| NULL         | NULL | NULL | NULL |
+--------------+------+------+------+
1 row in set

删除字段

语法

alter table 表名 drop 字段名;

示例

mysql> select *from teacher;
+--------------+------+------+------+
| teacher_name | age  | sex  | addr |
+--------------+------+------+------+
| NULL         | NULL | NULL | NULL |
+--------------+------+------+------+
1 row in set

<-- 删除teacher表中的addr字段 -->
mysql> alter table teacher drop addr;
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

mysql> select *from teacher;
+--------------+------+------+
| teacher_name | age  | sex  |
+--------------+------+------+
| NULL         | NULL | NULL |
+--------------+------+------+
1 row in set

MySQL学习记录—常用语句集
SELECT SELECT 子查询语句

语法

select *
from assistant
where age > (
	select age
	from assistant
	where assistant_name = '五婶'
);

示例

需求: 查看assistant表中所有年龄比五婶大的助教(五婶年龄未知情况下)。

mysql> select *from assistant;
+----------------+------+------+
| assistant_name | age  | sex  |
+----------------+------+------+
| NULL           | NULL | NULL |
| 三娘           |   25 | 女   |
| 四叔           |   24 | 男   |
| 五婶           |   20 | 女   |
+----------------+------+------+
4 rows in set

mysql> select *from assistant where age > (select age from assistant where assistant_name='五婶');
+----------------+-----+-----+
| assistant_name | age | sex |
+----------------+-----+-----+
| 三娘           |  25 | 女  |
| 四叔           |  24 | 男  |
+----------------+-----+-----+
2 rows in set
MySQL学习记录—常用语句集
LIKE 模糊查询

语法

SELECT *
FROM table_name
WHERE column_name LIKE pattern;

示例

需求: 查看assistant表中所有名字中含有三字的助教信息。

mysql> select *from assistant;
+----------------+-----+-----+
| assistant_name | age | sex |
+----------------+-----+-----+
| 三叔公         |  35 | 男  |
| 三娘           |  25 | 女  |
| 四叔           |  24 | 男  |
| 五婶           |  20 | 女  |
+----------------+-----+-----+
4 rows in set

<-- 名字中含有三字 -->
mysql> select *from assistant where assistant_name like '%三%';
+----------------+-----+-----+
| assistant_name | age | sex |
+----------------+-----+-----+
| 三叔公         |  35 | 男  |
| 三娘           |  25 | 女  |
+----------------+-----+-----+
2 rows in set

<-- 名字中以三字开头 -->
mysql> select *from assistant where assistant_name like '三%';
+----------------+-----+-----+
| assistant_name | age | sex |
+----------------+-----+-----+
| 三叔公         |  35 | 男  |
| 三娘           |  25 | 女  |
+----------------+-----+-----+
2 rows in set

<-- 名字中以三字结尾 -->
mysql> select *from assistant where assistant_name like '%三';
Empty set
MySQL学习记录—常用语句集
IN 和 NOT IN 范围查询

作用

   - IN子查询 用于查询符合条件的某个范围的数据。

   - NOT IN子查询 用于查询不符合条件的某个范围的数据。

   - IN和 NOT IN子查询语句的作用相反。

语法

SELECT *
FROM table_name
WHERE column_name IN (value1, value2, value3);


SELECT *
FROM table_name
WHERE column_name NOT IN (value1, value2, value3);

示例

需求: 分别查看assistant表中所有名字为三娘和四叔的助教信息 以及 所有名字不为三娘和四叔的助教信息。

mysql> select *from assistant;
+----------------+-----+-----+
| assistant_name | age | sex |
+----------------+-----+-----+
| 三叔公         |  35 | 男  |
| 三娘           |  25 | 女  |
| 四叔           |  24 | 男  |
| 五婶           |  20 | 女  |
+----------------+-----+-----+
4 rows in set

<-- 所有名字为三娘和四叔的助教信息 -->
mysql> select *from assistant where assistant_name in ('三娘','四叔');
+----------------+-----+-----+
| assistant_name | age | sex |
+----------------+-----+-----+
| 三娘           |  25 | 女  |
| 四叔           |  24 | 男  |
+----------------+-----+-----+
2 rows in set

<-- 所有名字不为三娘和四叔的助教信息 -->
mysql> select *from assistant where assistant_name not in ('三娘','四叔');
+----------------+-----+-----+
| assistant_name | age | sex |
+----------------+-----+-----+
| 三叔公         |  35 | 男  |
| 五婶           |  20 | 女  |
+----------------+-----+-----+
2 rows in set
栏目