Mysql 表结构的修改 记录的增删改查

1.表结构的修改

1.1表的创建

create table 表名(
字段名1 数据类型 字段属性,
字段名2 数据类型 字段属性,
字段名3 数据类型 字段属性,
字段名4 数据类型 字段属性,
字段名5 数据类型 字段属性
);

MariaDB [books]> create table student(
    -> id int primary key auto_increment,
    -> name varchar(30),
    -> age tinyint,
    -> class varchar(40),
    -> gender enum("男","女")
    -> );
Query OK, 0 rows affected (0.77 sec)

查看表结构
MariaDB [books]> desc student;
+--------+-----------------+------+-----+---------+----------------+
| Field  | Type            | Null | Key | Default | Extra          |
+--------+-----------------+------+-----+---------+----------------+
| id     | int(11)         | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30)     | YES  |     | NULL    |                |
| age    | tinyint(4)      | YES  |     | NULL    |                |
| class  | varchar(40)     | YES  |     | NULL    |                |
| gender | enum('男','女') | YES  |     | NULL    |                |
+--------+-----------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

1.2添加新字段

alter table student add 新字段名 数据类型 字段属性;

MariaDB [books]> alter table student add height decimal(5,2);
Query OK, 0 rows affected (0.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看表结构
MariaDB [books]> desc student;
+--------+-----------------+------+-----+---------+----------------+
| Field  | Type            | Null | Key | Default | Extra          |
+--------+-----------------+------+-----+---------+----------------+
| id     | int(11)         | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30)     | YES  |     | NULL    |                |
| age    | tinyint(4)      | YES  |     | NULL    |                |
| class  | varchar(40)     | YES  |     | NULL    |                |
| gender | enum('男','女') | YES  |     | NULL    |                |
| height | decimal(5,2)    | YES  |     | NULL    |                |
+--------+-----------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

1.3删除字段

alter table 表名 drop 字段名;

MariaDB [books]> alter table student drop height;
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [books]> desc student;
+--------+-----------------+------+-----+---------+----------------+
| Field  | Type            | Null | Key | Default | Extra          |
+--------+-----------------+------+-----+---------+----------------+
| id     | int(11)         | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30)     | YES  |     | NULL    |                |
| age    | tinyint(4)      | YES  |     | NULL    |                |
| class  | varchar(40)     | YES  |     | NULL    |                |
| gender | enum('男','女') | YES  |     | NULL    |                |
+--------+-----------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

1.4修改字段

alter table student change 已有的字段名 新字段名 数据类型 字段属性;

MariaDB [books]> alter table student change gender genders enum("男","女","变性人");
Query OK, 0 rows affected (0.34 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [books]> desc student;
+---------+--------------------------+------+-----+---------+----------------+
| Field   | Type                     | Null | Key | Default | Extra          |
+---------+--------------------------+------+-----+---------+----------------+
| id      | int(11)                  | NO   | PRI | NULL    | auto_increment |
| name    | varchar(30)              | YES  |     | NULL    |                |
| age     | tinyint(4)               | YES  |     | NULL    |                |
| class   | varchar(40)              | YES  |     | NULL    |                |
| genders | enum('男','女','变性人') | YES  |     | NULL    |                |
+---------+--------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

1.5 主键的修改

主键的删除 之前确保主键所在的字段 没有其自增属性 auto_increment
先删除 自增属性 auto_increment 在删除主键
alter table student change id id int;
alter table student drop primary key;

MariaDB [books]> alter table student change id id int;
Query OK, 0 rows affected (0.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [books]> desc student;
+---------+--------------------------+------+-----+---------+-------+
| Field   | Type                     | Null | Key | Default | Extra |
+---------+--------------------------+------+-----+---------+-------+
| id      | int(11)                  | NO   | PRI | NULL    |       |
| name    | varchar(30)              | YES  |     | NULL    |       |
| age     | tinyint(4)               | YES  |     | NULL    |       |
| class   | varchar(40)              | YES  |     | NULL    |       |
| genders | enum('男','女','变性人') | YES  |     | NULL    |       |
+---------+--------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)


MariaDB [books]> alter table student drop primary key;
Query OK, 0 rows affected (0.57 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [books]> desc student;
+---------+--------------------------+------+-----+---------+-------+
| Field   | Type                     | Null | Key | Default | Extra |
+---------+--------------------------+------+-----+---------+-------+
| id      | int(11)                  | NO   |     | NULL    |       |
| name    | varchar(30)              | YES  |     | NULL    |       |
| age     | tinyint(4)               | YES  |     | NULL    |       |
| class   | varchar(40)              | YES  |     | NULL    |       |
| genders | enum('男','女','变性人') | YES  |     | NULL    |       |
+---------+--------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)


主键的添加 之前确保主键所在的字段 没有重复的记录 (因为主键具有唯一性)
alter table student add primary key(id);

MariaDB [books]> alter table student add primary key(id);
Query OK, 0 rows affected (0.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [books]> desc student;
+---------+--------------------------+------+-----+---------+-------+
| Field   | Type                     | Null | Key | Default | Extra |
+---------+--------------------------+------+-----+---------+-------+
| id      | int(11)                  | NO   | PRI | NULL    |       |
| name    | varchar(30)              | YES  |     | NULL    |       |
| age     | tinyint(4)               | YES  |     | NULL    |       |
| class   | varchar(40)              | YES  |     | NULL    |       |
| genders | enum('男','女','变性人') | YES  |     | NULL    |       |
+---------+--------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

2.表中记录的增删改查

2.1 表记录的添加

2.1.1 全字段添加

insert into 表名 values(字段值1,字段值2,字段值3,字段值4…); 按照表结构顺序依次全部添加

MariaDB [books]> insert into student values(
    -> 1,"张三",18,"1901A",1
    -> );
Query OK, 1 row affected (0.06 sec)

MariaDB [books]> select * from student;
+----+------+------+-------+---------+
| id | name | age  | class | genders |
+----+------+------+-------+---------+
|  1 | 张三 |   18 | 1901A | 男      |
+----+------+------+-------+---------+
1 row in set (0.00 sec)

2.1.2 部分字段添加

insert into 表名 (字段名1,字段名2,字段名3,字段名4…) values(字段值1,字段值2,字段值3,字段值4…); values 前的字段名和后面的字段值一一对应

MariaDB [books]> insert into student
    -> (id,name,genders)values
    -> (2,"小红",2);
Query OK, 1 row affected (0.06 sec)

MariaDB [books]> select * from student;
+----+------+------+-------+---------+
| id | name | age  | class | genders |
+----+------+------+-------+---------+
|  1 | 张三 |   18 | 1901A | 男      |
|  2 | 小红 | NULL | NULL  | 女      |
+----+------+------+-------+---------+
2 rows in set (0.00 sec)

2.2 查询记录

2.2.1 查询某字段的记录

select 字段名1,字段名2… from 表名;

MariaDB [books]> select name,id,class from student;
+------+----+-------+
| name | id | class |
+------+----+-------+
| 张三 |  1 | 1901A |
| 小红 |  2 | NULL  |
+------+----+-------+
2 rows in set (0.00 sec)

2.2.2 查询某个特定字段值的记录

select * from student where id=1;

MariaDB [books]> select * from student where id=1;
+----+------+------+-------+---------+
| id | name | age  | class | genders |
+----+------+------+-------+---------+
|  1 | 张三 |   18 | 1901A | 男      |
+----+------+------+-------+---------+
1 row in set (0.00 sec)

2.3 修改记录

update 表名 set 字段名=字段值 where id=1;

MariaDB [books]> update student set class="1901B" where id=2;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [books]> select * from student;
+----+------+------+-------+---------+
| id | name | age  | class | genders |
+----+------+------+-------+---------+
|  1 | 张三 |   18 | 1901A | 男      |
|  2 | 小红 | NULL | 1901B | 女      |
+----+------+------+-------+---------+
2 rows in set (0.00 sec)

2.4 删除记录

delete from 表名 where id =1;

MariaDB [books]> delete from student  where id =1;
Query OK, 1 row affected (0.07 sec)

MariaDB [books]> select * from student;
+----+------+------+-------+---------+
| id | name | age  | class | genders |
+----+------+------+-------+---------+
|  2 | 小红 | NULL | 1901B | 女      |
+----+------+------+-------+---------+
1 row in set (0.00 sec)
;