03-MySQL表操作

MySQL表操作

1.介绍

　　表就相当于文件，表中的一条记录就相当与文件的一行内容，不同的是，表中的一条记录有对应的标题，成为表的字段。

2.创建表

2.1语法

create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件],
);

注意：
    1. 必须进入数据库中才能创建表
    2. 标点符号均为英文标点符号
    3. 在同一张表中，字段名是不能相同的
    4. 宽度和约束条件为可选项
    5. 字段名和类型是必须的

2.2范例

1-使用默认格式创建表

mysql> use python25;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table test1(id int,
    -> name char(11),
    -> sex enum('male', 'female'),
    -> hobby set('swiming', 'running', 'basketball'));
Query OK, 0 rows affected (0.02 sec)

mysql> desc test1;
+-------+---------------------------------------+------+-----+---------+-------+
| Field | Type                                  | Null | Key | Default | Extra |
+-------+---------------------------------------+------+-----+---------+-------+
| id    | int(11)                               | YES  |     | NULL    |       |
| name  | char(11)                              | YES  |     | NULL    |       |
| sex   | enum('male','female')                 | YES  |     | NULL    |       |
| hobby | set('swiming','running','basketball') | YES  |     | NULL    |       |
+-------+---------------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

2-自定义格式创建表

# 指定字符编码
mysql> create table test3 (
    -> id int,
    -> name char(10),
    -> age int,
    -> sex enum('male', 'female'),
    -> phone char(11),
    -> job char(10)
    -> ) charset=utf8;
Query OK, 0 rows affected (0.03 sec)

#指定存储引擎
mysql> create table test4 (
    -> id int,
    -> name char(10),
    -> age int,
    -> sex enum('male', 'female'),
    -> phone char(11),
    -> job char(10)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

3-查看表结构、创建表的语句

# 查看表结构
mysql> show columns from test1;
+-------+---------------------------------------+------+-----+---------+-------+
| Field | Type                                  | Null | Key | Default | Extra |
+-------+---------------------------------------+------+-----+---------+-------+
| id    | int(11)                               | YES  |     | NULL    |       |
| name  | char(11)                              | YES  |     | NULL    |       |
| sex   | enum('male','female')                 | YES  |     | NULL    |       |
| hobby | set('swiming','running','basketball') | YES  |     | NULL    |       |
+-------+---------------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> desc test1;
+-------+---------------------------------------+------+-----+---------+-------+
| Field | Type                                  | Null | Key | Default | Extra |
+-------+---------------------------------------+------+-----+---------+-------+
| id    | int(11)                               | YES  |     | NULL    |       |
| name  | char(11)                              | YES  |     | NULL    |       |
| sex   | enum('male','female')                 | YES  |     | NULL    |       |
| hobby | set('swiming','running','basketball') | YES  |     | NULL    |       |
+-------+---------------------------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

# 查看创建表的语句
mysql> show create table test1 G;
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `id` int(11) DEFAULT NULL,
  `name` char(11) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  `hobby` set('swiming','running','basketball') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

2.删除表 

语法:

DROP TABLE 表名;

注意：删表要谨慎

mysql> show tables;
+--------------------+
| Tables_in_python25 |
+--------------------+
| test1              |
| test2              |
| test3              |
| test4              |
+--------------------+
4 rows in set (0.00 sec)

mysql> drop table test1;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table test2;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------------+
| Tables_in_python25 |
+--------------------+
| test3              |
| test4              |
+--------------------+
2 rows in set (0.00 sec)

3.修改表

3.1 修改表名

语法

ALTER TABLE 表名 RENAME 新表名;

范例

mysql> show tables;
+--------------------+
| Tables_in_python25 |
+--------------------+
| test3              |
| test4              |
+--------------------+
2 rows in set (0.00 sec)

mysql> alter table test3 rename test1;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+--------------------+
| Tables_in_python25 |
+--------------------+
| test1              |
| test4              |
+--------------------+
2 rows in set (0.00 sec)

3.2 增加字段

语法

ALTER TABLE 表名 ADD 字段名 数据类型[(宽度) 约束条件], ADD 字段名 数据类型[(宽度) 约束条件];

范例

mysql> desc test1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(10)              | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | char(11)              | YES  |     | NULL    |       |
| job   | char(10)              | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

# 增加hobby、home两个字段
mysql> alter table test1 add hobby set('football', 'running'), add home char(10);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test1;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id    | int(11)                   | YES  |     | NULL    |       |
| name  | char(10)                  | YES  |     | NULL    |       |
| age   | int(11)                   | YES  |     | NULL    |       |
| sex   | enum('male','female')     | YES  |     | NULL    |       |
| phone | char(11)                  | YES  |     | NULL    |       |
| job   | char(10)                  | YES  |     | NULL    |       |
| hobby | set('football','running') | YES  |     | NULL    |       |
| home  | char(10)                  | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

3.3 删除字段

语法

ALTER TABLE 表名 DROP 字段名;

范例

mysql> desc test1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(10)              | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | char(11)              | YES  |     | NULL    |       |
| job   | char(10)              | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

# 删除hobby、home字段
mysql> alter table test1 add hobby set('football', 'running'), add home char(10);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test1;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id    | int(11)                   | YES  |     | NULL    |       |
| name  | char(10)                  | YES  |     | NULL    |       |
| age   | int(11)                   | YES  |     | NULL    |       |
| sex   | enum('male','female')     | YES  |     | NULL    |       |
| phone | char(11)                  | YES  |     | NULL    |       |
| job   | char(10)                  | YES  |     | NULL    |       |
| hobby | set('football','running') | YES  |     | NULL    |       |
| home  | char(10)                  | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

3.4 修改字段

语法

ALTER TABLE 表名 MODIFY 字段名 数据类型[(宽度) 约束条件];

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型[(宽度) 约束条件];

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型[(宽度) 约束条件];

范例

mysql> desc test1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(10)              | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | char(11)              | YES  |     | NULL    |       |
| job   | char(10)              | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

# 修改job字段的数据类型
mysql> alter table test1 modify job varchar(11);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(10)              | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | char(11)              | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

# 修改job字段为hobby字段
mysql> alter table test1 change job hobby varchar(11);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(10)              | YES  |     | NULL    |       |
| age   | int(11)                   | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | char(11)              | YES  |     | NULL    |       |
| hobby | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

# 修改hobby字段为job字段，且数据类型改为char
mysql> alter table test1 change hobby job char(11);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(10)              | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | char(11)              | YES  |     | NULL    |       |
| job   | char(11)              | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

3.5 修改字段排列顺序、在增加的时候指定字段位置

语法

ALTER TABLE 表名 ADD 字段名 数据类型 [(宽度) 约束条件] FIRST;

ALTER TABLE 表名 ADD 字段名 数据类型 [(宽度) 约束条件] AFTER 字段名;

ALTER TABLE 表名 CHANGE 字段名 旧字段名 新字段名 新数据类型 [(宽度) 约束条件] FIRST;

ALTER TABLE 表名 MODIFY 字段名 数据类型 [(宽度) 约束条件] AFTER 字段名;

范例

mysql> desc test1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(10)              | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | char(11)              | YES  |     | NULL    |       |
| job   | char(11)              | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

# 在表的开头添加hobby字段
mysql> alter table test1 add hobby char(10) first;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test1;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| hobby | char(10)              | YES  |     | NULL    |       |
| id    | int(11)               | YES  |     | NULL    |       |
| name  | char(10)              | YES  |     | NULL    |       |
| age   | int(11)               | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | char(11)              | YES  |     | NULL    |       |
| job   | char(11)              | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

# 在job字段后面添加salary字段
mysql> alter table test1 add salary int after job;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test1;
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| hobby  | char(10)              | YES  |     | NULL    |       |
| id     | int(11)               | YES  |     | NULL    |       |
| name   | char(10)              | YES  |     | NULL    |       |
| age    | int(11)               | YES  |     | NULL    |       |
| sex    | enum('male','female') | YES  |     | NULL    |       |
| phone  | char(11)              | YES  |     | NULL    |       |
| job    | char(11)              | YES  |     | NULL    |       |
| salary | int(11)               | YES  |     | NULL    |       |
+--------+-----------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

# 修改hobby字段名为aihao
mysql> alter table test1 change hobby aihao char(10) first;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test1;
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| aihao  | char(10)              | YES  |     | NULL    |       |
| id     | int(11)               | YES  |     | NULL    |       |
| name   | char(10)              | YES  |     | NULL    |       |
| age    | int(11)               | YES  |     | NULL    |       |
| sex    | enum('male','female') | YES  |     | NULL    |       |
| phone  | char(11)              | YES  |     | NULL    |       |
| job    | char(11)              | YES  |     | NULL    |       |
| salary | int(11)               | YES  |     | NULL    |       |
+--------+-----------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

# 把aihao字段放到salary字段后面
mysql> alter table test1 modify aihao char(10) after salary;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test1;
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id     | int(11)               | YES  |     | NULL    |       |
| name   | char(10)              | YES  |     | NULL    |       |
| age    | int(11)               | YES  |     | NULL    |       |
| sex    | enum('male','female') | YES  |     | NULL    |       |
| phone  | char(11)              | YES  |     | NULL    |       |
| job    | char(11)              | YES  |     | NULL    |       |
| salary | int(11)               | YES  |     | NULL    |       |
| aihao  | char(10)              | YES  |     | NULL    |       |
+--------+-----------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

4.数据操作

创建表

# 创建语句
create table teacher(
id int primary key,
name char(10) not null,
age int,
hire_date datetime,
sex enum('male', 'female') not null default 'male',
phone char(11) unique,
salary float(8, 2),
hobby set('running', 'football', 'basketball'),
comment varchar(255)
);

# 查看表结构
mysql> desc teacher;
+-----------+----------------------------------------+------+-----+---------+-------+
| Field     | Type                                   | Null | Key | Default | Extra |
+-----------+----------------------------------------+------+-----+---------+-------+
| id        | int(11)                                | NO   | PRI | NULL    |       |
| name      | char(10)                               | NO   |     | NULL    |       |
| age       | int(11)                                | YES  |     | NULL    |       |
| hire_date | datetime                               | YES  |     | NULL    |       |
| sex       | enum('male','female')                  | NO   |     | male    |       |
| phone     | char(11)                               | YES  | UNI | NULL    |       |
| salary    | float(8,2)                             | YES  |     | NULL    |       |
| hobby     | set('running','football','basketball') | YES  |     | NULL    |       |
| comment   | varchar(255)                           | YES  |     | NULL    |       |
+-----------+----------------------------------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

4.1 插入数据

语法

插入单行数据，

INSERT INTO 表名 (字段1， 字段2， 字段3...) value (值1, 值2, 值3);

如果不添加字段名称，插入的数据需要与表里的字段一一对应

INSERT INTO 表名 value (值1, 值2, 值3);

插入多行数据

INSERT INTO 表名 values (值1, 值2, 值3),(值1, 值2, 值3),(值1, 值2, 值3);

范例

#  插入单条数据，注意float类型会进行四舍五入
mysql> insert into teacher (id, name, hobby) value (1, 'zhangsan', 'running');
Query OK, 1 row affected (0.42 sec)

mysql> select * from teacher;
+----+----------+------+-----------+------+-------+--------+---------+---------+
| id | name     | age  | hire_date | sex  | phone | salary | hobby   | comment |
+----+----------+------+-----------+------+-------+--------+---------+---------+
|  1 | zhangsan | NULL | NULL      | male | NULL  |   NULL | running | NULL    |
+----+----------+------+-----------+------+-------+--------+---------+---------+
1 row in set (0.00 sec)

mysql> insert into teacher value (2, 'lisi', 30, '2017-10-20', 'male', '13069948980', 2000, 'basketball', 'haha');
Query OK, 1 row affected (0.15 sec)

mysql> select * from teacher;
+----+----------+------+---------------------+------+-------------+---------+------------+---------+
| id | name     | age  | hire_date           | sex  | phone       | salary  | hobby      | comment |
+----+----------+------+---------------------+------+-------------+---------+------------+---------+
|  1 | zhangsan | NULL | NULL                | male | NULL        |    NULL | running    | NULL    |
|  2 | lisi     |   30 | 2017-10-20 00:00:00 | male | 13069948980 | 2000.00 | basketball | haha    |
+----+----------+------+---------------------+------+-------------+---------+------------+---------+
2 rows in set (0.03 sec)

# 插入多条数据
mysql> insert into teacher values (3, 'wangwu', 28, '2016-11-20', 'male', '1382333333', 20000, 'football', 'wowo'),
    ->  (4, 'xiaoli', 28, '2015-11-20', 'female', '13823333333', 20000, 'football', 'wowo');
Query OK, 2 rows affected (0.40 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from teacher;
+----+----------+------+---------------------+--------+-------------+----------+------------+---------+
| id | name     | age  | hire_date           | sex    | phone       | salary   | hobby      | comment |
+----+----------+------+---------------------+--------+-------------+----------+------------+---------+
|  1 | zhangsan | NULL | NULL                | male   | NULL        |     NULL | running    | NULL    |
|  2 | lisi     |   30 | 2017-10-20 00:00:00 | male   | 13069948980 |  2000.00 | basketball | haha    |
|  3 | wangwu   |   28 | 2016-11-20 00:00:00 | male   | 1382333333  | 20000.00 | football   | wowo    |
|  4 | xiaoli   |   28 | 2015-11-20 00:00:00 | female | 13823333333 | 20000.00 | football   | wowo    |
+----+----------+------+---------------------+--------+-------------+----------+------------+---------+
4 rows in set (0.00 sec)

4.2 删除数据

语法

DELETE FROM 表名 WHERE ....;

范例

mysql> select * from teacher;
+----+----------+------+---------------------+--------+-------------+----------+------------+---------+
| id | name     | age  | hire_date           | sex    | phone       | salary   | hobby      | comment |
+----+----------+------+---------------------+--------+-------------+----------+------------+---------+
|  1 | zhangsan | NULL | NULL                | male   | NULL        |     NULL | running    | NULL    |
|  2 | lisi     |   30 | 2017-10-20 00:00:00 | male   | 13069948980 |  2000.00 | basketball | haha    |
|  3 | wangwu   |   28 | 2016-11-20 00:00:00 | male   | 1382333333  | 20000.00 | football   | wowo    |
|  4 | xiaoli   |   28 | 2015-11-20 00:00:00 | female | 13823333333 | 20000.00 | football   | wowo    |
+----+----------+------+---------------------+--------+-------------+----------+------------+---------+
4 rows in set (0.00 sec)

mysql> delete from teacher where id=1;
Query OK, 1 row affected (0.48 sec)

mysql> select * from teacher;
+----+--------+------+---------------------+--------+-------------+----------+------------+---------+
| id | name   | age  | hire_date           | sex    | phone       | salary   | hobby      | comment |
+----+--------+------+---------------------+--------+-------------+----------+------------+---------+
|  2 | lisi   |   30 | 2017-10-20 00:00:00 | male   | 13069948980 |  2000.00 | basketball | haha    |
|  3 | wangwu |   28 | 2016-11-20 00:00:00 | male   | 1382333333  | 20000.00 | football   | wowo    |
|  4 | xiaoli |   28 | 2015-11-20 00:00:00 | female | 13823333333 | 20000.00 | football   | wowo    |
+----+--------+------+---------------------+--------+-------------+----------+------------+---------+
3 rows in set (0.00 sec)

4.3 更新数据

语法

UPDATE 表名 SET 字段1=值1, 字段2=值2 WHERE ...;

范例

mysql> select * from teacher;
+----+--------+------+---------------------+--------+-------------+----------+------------+---------+
| id | name   | age  | hire_date           | sex    | phone       | salary   | hobby      | comment |
+----+--------+------+---------------------+--------+-------------+----------+------------+---------+
|  2 | lisi   |   30 | 2017-10-20 00:00:00 | male   | 13069948980 |  2000.00 | basketball | haha    |
|  3 | wangwu |   28 | 2016-11-20 00:00:00 | male   | 1382333333  | 20000.00 | football   | wowo    |
|  4 | xiaoli |   28 | 2015-11-20 00:00:00 | female | 13823333333 | 20000.00 | football   | wowo    |
+----+--------+------+---------------------+--------+-------------+----------+------------+---------+
3 rows in set (0.00 sec)

mysql> update teacher set age=40 where id=2;
Query OK, 1 row affected (0.46 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from teacher;
+----+--------+------+---------------------+--------+-------------+----------+------------+---------+
| id | name   | age  | hire_date           | sex    | phone       | salary   | hobby      | comment |
+----+--------+------+---------------------+--------+-------------+----------+------------+---------+
|  2 | lisi   |   40 | 2017-10-20 00:00:00 | male   | 13069948980 |  2000.00 | basketball | haha    |
|  3 | wangwu |   28 | 2016-11-20 00:00:00 | male   | 1382333333  | 20000.00 | football   | wowo    |
|  4 | xiaoli |   28 | 2015-11-20 00:00:00 | female | 13823333333 | 20000.00 | football   | wowo    |
+----+--------+------+---------------------+--------+-------------+----------+------------+---------+
3 rows in set (0.00 sec)

4.4 查询数据

单表查询

多表查询