3.管理表
1.创建表
mysql> create table if not exists txt(id int,name varchar(10) not null,age int not null,primary key(id))engine=innodb default charset=utf8; Query OK, 0 rows affected (0.00 sec)
2.查看表结构和类型
mysql> desc txt; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(10) | NO | | NULL | | | age | int(11) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> show create table txt; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | txt | CREATE TABLE `txt` ( `id` int(11) NOT NULL, `name` varchar(10) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
3.插入数据
mysql> insert into txt (id,name,age) values(1,'zhangsan',12); Query OK, 1 row affected (0.00 sec) mysql> insert into txt values(2,'lisi',15),(3,'wangwu',18); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from txt; +----+----------+-----+ | id | name | age | +----+----------+-----+ | 1 | zhangsan | 12 | | 2 | lisi | 15 | | 3 | wangwu | 18 | +----+----------+-----+ 3 rows in set (0.00 sec)
4.对表的字符集进行修改
mysql> alter table txt convert to character set gb18030; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show create table txt; +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | txt | CREATE TABLE `txt` ( `id` int(11) NOT NULL, `name` varchar(10) NOT NULL, `age` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=gb18030 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
5.对表重命名
mysql> alter table a2 rename a3; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | a3 | +----------------+
6.删除表
mysql> drop table txt; Query OK, 0 rows affected (0.00 sec)
4.对表内数据进行修改
1.查看表内字符集
mysql> show full columns from txt; +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | id | int(11) | NULL | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(10) | utf8_general_ci | NO | | NULL | | select,insert,update,references | | | age | int(11) | NULL | NO | | NULL | | select,insert,update,references | | +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+
2.对表内字段进行修改字符集
格式：alter table 表名 modify 字段 属性 character set 字符集 collate 校验集;
mysql> alter table txt modify age varchar(50) character set utf8 collate utf8_general_ci; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show full columns from txt; +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | id | int(11) | NULL | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(50) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | age | varchar(50) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ 3 rows in set (0.00 sec)
3.对字段属性进行修改
mysql> alter table txt modify age int; Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> show full columns from txt; | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | id | int(11) | NULL | NO | PRI | NULL | | select,insert,update,references | | | name | varchar(50) | utf8_general_ci | YES | | NULL | | select,insert,update,references | | | age | int(11) | NULL | YES | | NULL | | select,insert,update,references | | 3 rows in set (0.00 sec)
4.创建字段的属性
1.字段属性
2.其他选项
3.使用命令
mysql> create table a1 ( -> id int primary key auto_increment, -> name varchar(10) not null unique, -> age int default 18, -> stu_id int ); Query OK, 0 rows affected (0.00 sec) mysql> create table a2 ( -> id int primary key auto_increment, -> name varchar(10), -> foreign key(id) references a1(id)); Query OK, 0 rows affected (0.00 sec)
5.修改字段内容
mysql> update txt set name='zhaoliu' where name='zhangsan'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from txt; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | zhaoliu | 12 | | 2 | lisi | 15 | | 3 | wangwu | 18 | +----+---------+------+ 3 rows in set (0.00 sec)
6.删除字段内容
mysql> delete from txt where name='zhaoliu'; Query OK, 1 row affected (0.00 sec) mysql> select * from txt; +----+--------+------+ | id | name | age | +----+--------+------+ | 2 | lisi | 15 | | 3 | wangwu | 18 | +----+--------+------+ 2 rows in set (0.00 sec)
7.查看字段类型
mysql> show create table a1; | a1 | CREATE TABLE `a1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL, `age` int(11) DEFAULT '18', `stu_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) mysql> desc a2; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec)
8.添加字段
mysql> alter table a1 add stu_number varchar(10) not null unique; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc a1; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(10) | NO | UNI | NULL | | | age | int(11) | YES | | 18 | | | stu_id | int(11) | YES | | NULL | | | stu_number | varchar(10) | NO | UNI | NULL | | +------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> alter table a1 add stu_number_1 varchar(10) not null unique first; #添加到首行 mysql> alter table a1 add stu_number_2 varchar(10) not null unique after id; #添加到id行之后 Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0
9.删除字段
mysql> alter table a1 drop stu_number_1;
10.修改字段排序顺序
mysql> alter table a1 modify stu_number varchar(10) first; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc a1; +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | stu_number | varchar(10) | YES | UNI | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | | stu_number_2 | varchar(10) | NO | UNI | NULL | | | name | varchar(10) | NO | UNI | NULL | | | age | int(11) | YES | | 18 | | | stu_id | int(11) | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec) mysql> alter table a1 modify age int after id ; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc a1; +--------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+----------------+ | stu_number | varchar(10) | YES | UNI | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | | age | int(11) | YES | | NULL | | | stu_number_2 | varchar(10) | NO | UNI | NULL | | | name | varchar(10) | NO | UNI | NULL | | | stu_id | int(11) | YES | | NULL | | +--------------+-------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)
11.删除外键约束
mysql> create table test4(id int primary key ,name varchar(10),constraint `test` foreign key(id) references test2(id)); Query OK, 0 rows affected (0.01 sec) mysql> alter table test4 drop foreign key test; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc test4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
