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)