数据表操作
数据表的整体约束总结
-- 创建表小结
CREATE TABLE [IF NOT EXISTS] tbl_name(
-- 字段名称 字段类型 [UNSIGNED|ZERFILL] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY | UNIQUE [KEY]] [AUTO_INCREMENT]
-- 自增长一定是和主键来一起使用
)ENGINE=INNODE CHARSET=UTF8 AUTO_INCREMENT=100;
测试主键 KEY
主键是唯一的，非空的值
mysql> CREATE TABLE user1(
->
-> id INT PRIMARY KEY,
-> username VARCHAR(20)
->
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> DESC user1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE user1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------+
| user1 | CREATE TABLE `user1` (
`id` int NOT NULL,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> INSERT user1 VALUES(1,'KING');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT user1 VALUES(12,'QUEEN');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM user1;
+----+----------+
| id | username |
+----+----------+
| 1 | KING |
| 12 | QUEEN |
+----+----------+
2 rows in set (0.00 sec)
mysql> INSERT user1 VALUES(12,'QUN');
ERROR 1062 (23000): Duplicate entry '12' for key 'user1.PRIMARY'
mysql> select * from user1 where id = 1;
+----+----------+
| id | username |
+----+----------+
| 1 | KING |
+----+----------+
1 row in set (0.00 sec)
测试复合主键
mysql> CREATE TABLE user2(
->
-> id INT,
-> username VARCHAR(20),
-> card CHAR(18),
-> PRIMARY KEY(id,card) ---- 复合主键 多字段主键
->
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> DESC user;
+----------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------------------------+------+-----+---------+-------+
| id | smallint | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
| age | tinyint | YES | | NULL | |
| sex | enum('鐢','濂','淇濆瘑') | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
| addr | varchar(200) | YES | | NULL | |
| birth | year | YES | | NULL | |
| salary | float | YES | | NULL | |
| tel | int | YES | | NULL | |
| married | tinyint(1) | YES | | NULL | |
+----------+-------------------------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
mysql> DESC user2;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| username | varchar(20) | YES | | NULL | |
| card | char(18) | NO | PRI | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> INSERT user2 VALUES(1,'KING','111');
Query OK, 1 row affected (0.01 sec)
-- 此时这里并不是叫重复的记录 因为 后面的card并不相同 也就是两个不可以同时相等
mysql> INSERT user2 VALUES(1,'queen','112');
Query OK, 1 row affected (0.01 sec)
mysql> CREATE TABLE user3(
->
-> id INT,
-> username VARCHAR(20),
->
-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 6
mysql> CREATE TABLE user3( id INT, username VARCHAR(20) );
Query OK, 0 rows affected (0.00 sec)
还是测试主键
mysql> CREATE TABLE user3( id INT, username VARCHAR(20) );
Query OK, 0 rows affected (0.00 sec)
mysql> DESC user3;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> DROP TABLE user3;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE user3(
->
-> id INT KEY,
-> username VARCHAR(20)
->
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> DESC user3;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE user4(
->
-> id INT,
-> username VARCHAR(20) KEY
->
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> DESC user4;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| username | varchar(20) | NO | PRI | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> CREATE TABLE user5(
->
-> id SMALLINT KEY AUTO_INCREMENT,
-> username VARCHAR(20)
->
-> );
Query OK, 0 rows affected (0.01 sec)
测试自增长 AUTO_INCREMENT
一个表中只能有一个自增长字段，且必须配合主键使用，只对整数列、整数串有意义，对字符串没有意义
mysql> DESC user5;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | smallint | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> INSERT user5 VALUES(1,'king');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT user5 VALUES(1,'queen');
ERROR 1062 (23000): Duplicate entry '1' for key 'user5.PRIMARY'
mysql> INSERT user5(username) VALUES('queen');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user5;
+----+----------+
| id | username |
+----+----------+
| 1 | king |
| 2 | queen |
+----+----------+
2 rows in set (0.00 sec)
mysql> INSERT user5(username) VALUES('wangzi');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user5;
+----+----------+
| id | username |
+----+----------+
| 1 | king |
| 2 | queen |
| 3 | wangzi |
+----+----------+
3 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE user5;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user5 | CREATE TABLE `user5` (
`id` smallint NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> INSERT user5 VALUES(DEFAULT,'king2'); -- 这里也可以写NULL，都可以自增长1。
Query OK, 1 row affected (0.00 sec)
mysql> select * from user5;
+----+----------+
| id | username |
+----+----------+
| 1 | king |
| 2 | queen |
| 3 | wangzi |
| 4 | king2 |
+----+----------+
4 rows in set (0.00 sec)
修改自增长的值
mysql> DESC user6;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | smallint | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE user6;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user6 | CREATE TABLE `user6` (
`id` smallint NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE user6 AUTO_INCREMENT = 500; -- 修改自增长的值
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user6;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user6 | CREATE TABLE `user6` (
`id` smallint NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
测试非空 NOT NULL
mysql> DESC user7;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | char(32) | NO | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> INSERT user7(username,password) VALUES('king','nihao woshiguowang');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user7;
+----+----------+--------------------+------+
| id | username | password | age |
+----+----------+--------------------+------+
| 1 | king | nihao woshiguowang | NULL |
+----+----------+--------------------+------+
1 row in set (0.00 sec)
mysql> INSERT user7(username,password,age) VALUES('king','nihao woshiguowang',32);
Query OK, 1 row affected (0.00 sec)
mysql> select * from user7;
+----+----------+--------------------+------+
| id | username | password | age |
+----+----------+--------------------+------+
| 1 | king | nihao woshiguowang | NULL |
| 2 | king | nihao woshiguowang | 32 |
+----+----------+--------------------+------+
2 rows in set (0.00 sec)
mysql> INSERT user7(username,password) VALUES('king',NULL); --若有NOT NULL限制，输入NULL或不输入会报错
ERROR 1048 (23000): Column 'password' cannot be null
唯一性约束 UNIQUE [KEY] (KEY可省略)
mysql> CREATE TABLE user8(
->
-> id INT UNSIGNED KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL,
-> password CHAR(32) NOT NULL,
-> age TINYINT UNSIGNED DEFAULT 18,
-> addr VARCHAR(50) NOT NULL DEFAULT 'BEIJING',
-> sex ENUM('NAN','NV','保密') NOT NULL DEFAULT 'NAN'
->
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> DESC user8;
+----------+---------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | char(32) | NO | | NULL | |
| age | tinyint unsigned | YES | | 18 | |
| addr | varchar(50) | NO | | BEIJING | |
| sex | enum('NAN','NV','保密') | NO | | NAN | |
+----------+---------------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> INSERT user8(username,password) VALUES('king','passwordL');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user8;
+----+----------+-----------+------+---------+-----+
| id | username | password | age | addr | sex |
+----+----------+-----------+------+---------+-----+
| 1 | king | passwordL | 18 | BEIJING | NAN |
+----+----------+-----------+------+---------+-----+
1 row in set (0.00 sec)
mysql> INSERT user8 VALUES('queen','passwordL','pass',23,'上海','NV');
ERROR 1366 (HY000): Incorrect integer value: 'queen' for column 'id' at row 1
mysql> INSERT user8 VALUES(2,'queen','passwordL','pass',23,'上海','NV');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> INSERT user8 VALUES(2,'queen','pass',23,'上海','NV');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user8;
+----+----------+-----------+------+---------+-----+
| id | username | password | age | addr | sex |
+----+----------+-----------+------+---------+-----+
| 1 | king | passwordL | 18 | BEIJING | NAN |
| 2 | queen | pass | 23 | 上海 | NV |
+----+----------+-----------+------+---------+-----+
2 rows in set (0.00 sec)
mysql> INSERT user8 VALUES(3,'queen','pass',DEFAULT,DEFAULT,'NV');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user8;
+----+----------+-----------+------+---------+-----+
| id | username | password | age | addr | sex |
+----+----------+-----------+------+---------+-----+
| 1 | king | passwordL | 18 | BEIJING | NAN |
| 2 | queen | pass | 23 | 上海 | NV |
| 3 | queen | pass | 18 | BEIJING | NV |
+----+----------+-----------+------+---------+-----+
3 rows in set (0.00 sec)
测试唯一性约束
-- 测试唯一性约束
-- primary key = unique + not null
-- NULL 不算重复
mysql> CREATE TABLE user9(
->
-> id INT UNSIGNED KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL UNIQUE,
-> card CHAR(18) UNIQUE
->
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> DESC user9;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| card | char(18) | YES | UNI | NULL | |
+----------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> INSERT user9(username) VALUES('A');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT user9(username) VALUES('A1');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM user9;
+----+----------+------+
| id | username | card |
+----+----------+------+
| 1 | A | NULL |
| 2 | A1 | NULL |
+----+----------+------+
2 rows in set (0.00 sec)
mysql> INSERT user9(username,card) VALUES('B1','111');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT user9(username,card) VALUES('B2',null);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT user9(username,card) VALUES('B3',null);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM user9;
+----+----------+------+
| id | username | card |
+----+----------+------+
| 1 | A | NULL |
| 2 | A1 | NULL |
| 3 | B1 | 111 |
| 4 | B2 | NULL |
| 5 | B3 | NULL |
+----+----------+------+
5 rows in set (0.00 sec)
修改表结构
修改表名
ALTER TABLE tbl_name RENAME[TO|AS]new_name这里的AS和TO都可以省略RENAME TABLE tbl_name TO new_name这里的TO不可以省略
mysql> CREATE TABLE user10(
->
-> id SMALLINT UNSIGNED KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL UNIQUE,
-> password CHAR(32) NOT NULL,
-> email VARCHAR(50) NOT NULL DEFAULT '234235345@qq.com',
-> age TINYINT UNSIGNED DEFAULT 18,
-> sex ENUM('男','女','保密') DEFAULT '保密',
-> addr VARCHAR(200) NOT NULL DEFAULT 'BEIJING',
-> salary FLOAT(6,3),
-> regTime INT UNSIGNED, -- 注册时间
-> face CHAR(100) NOT NULL DEFAULT 'default.JPG'
->
-> );
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 234235345@qq.com | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
+----------+----------------------------+------+-----+------------------+----------------+
10 rows in set (0.00 sec)
mysql> ALTER TABLE user10 RENAME user11;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM user11;
Empty set (0.00 sec)
mysql> DESC user11;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 234235345@qq.com | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
+----------+----------------------------+------+-----+------------------+----------------+
10 rows in set (0.00 sec)
mysql> ALTER TABLE user11 RENAME AS user10;
Query OK, 0 rows affected (0.00 sec)
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 234235345@qq.com | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
+----------+----------------------------+------+-----+------------------+----------------+
10 rows in set (0.01 sec)
mysql> SELECT * FROM user10;
Empty set (0.00 sec)
mysql> RENAME TABLE user10 TO user11;
Query OK, 0 rows affected (0.01 sec)
mysql> DESC user11;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 234235345@qq.com | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
+----------+----------------------------+------+-----+------------------+----------------+
10 rows in set (0.00 sec)
mysql> RENAME TABLE user11 TO user10;
Query OK, 0 rows affected (0.00 sec)
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 234235345@qq.com | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
+----------+----------------------------+------+-----+------------------+----------------+
10 rows in set (0.00 sec)
添加字段
ALTER TABLE tbl_name ADD 字段名称 字段类型 [完整性约束条件] [FIRST|ALTER 字段名称]
mysql> ALTER TABLE user10 ADD card CHAR(20);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 234235345@qq.com | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(20) | YES | | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
11 rows in set (0.00 sec)
mysql> ALTER TABLE user10 ADD test1 VARCHAR(20) NOT NULL UNIQUE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 234235345@qq.com | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(20) | YES | | NULL | |
| test1 | varchar(20) | NO | UNI | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
12 rows in set (0.00 sec)
mysql> ALTER TABLE user10 ADD test2 VARCHAR(20) NOT NULL FIRST;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| test2 | varchar(20) | NO | | NULL | |
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 234235345@qq.com | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(20) | YES | | NULL | |
| test1 | varchar(20) | NO | UNI | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
13 rows in set (0.00 sec)
mysql> ALTER TABLE user10 ADD test3 INT NOT NULL AFTER username;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| test2 | varchar(20) | NO | | NULL | |
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| test3 | int | NO | | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 234235345@qq.com | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(20) | YES | | NULL | |
| test1 | varchar(20) | NO | UNI | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
14 rows in set (0.00 sec)
mysql> ALTER TABLE user10
-> ADD test4 INT NOT NULL DEFAULT 123 AFTER password,
-> ADD test5 FLOAT(6,2) FIRST,
-> ADD test6 SET('A','B','C')
-> ;
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| test5 | float(6,2) | YES | | NULL | |
| test2 | varchar(20) | NO | | NULL | |
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| test3 | int | NO | | NULL | |
| password | char(32) | NO | | NULL | |
| test4 | int | NO | | 123 | |
| email | varchar(50) | NO | | 234235345@qq.com | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(20) | YES | | NULL | |
| test1 | varchar(20) | NO | UNI | NULL | |
| test6 | set('A','B','C') | YES | | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
17 rows in set (0.01 sec)
删除字段
ALTER TABLE tbl_name DROP 字段名称
mysql> ALTER TABLE user10 DROP test6;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| test5 | float(6,2) | YES | | NULL | |
| test2 | varchar(20) | NO | | NULL | |
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| test3 | int | NO | | NULL | |
| password | char(32) | NO | | NULL | |
| test4 | int | NO | | 123 | |
| email | varchar(50) | NO | | 234235345@qq.com | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(20) | YES | | NULL | |
| test1 | varchar(20) | NO | UNI | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
16 rows in set (0.00 sec)
mysql> ALTER TABLE user10
-> DROP test2,
-> DROP test5; -- 批量删除某些字段
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| test3 | int | NO | | NULL | |
| password | char(32) | NO | | NULL | |
| test4 | int | NO | | 123 | |
| email | varchar(50) | NO | | 234235345@qq.com | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(20) | YES | | NULL | |
| test1 | varchar(20) | NO | UNI | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
14 rows in set (0.00 sec)
mysql> ALTER TABLE user10
-> ADD test100 INT UNSIGNED NOT NULL DEFAULT 10 AFTER sex,
-> DROP test3; -- 我们也可以同时删除和添加表内的字段
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| test4 | int | NO | | 123 | |
| email | varchar(50) | NO | | 234235345@qq.com | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| test100 | int unsigned | NO | | 10 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(20) | YES | | NULL | |
| test1 | varchar(20) | NO | UNI | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
14 rows in set (0.00 sec)
修改字段
ALTER TABLE tbl_name MODIFY字段名称 字段类型 [完整性约束条件] [FIRST|LAST字段名称]改变字段的类型TYPE
mysql> ALTER TABLE user10 MODIFY email VARCHAR(200);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+-------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+-------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| test4 | int | NO | | 123 | |
| email | varchar(200) | YES | | NULL | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| test100 | int unsigned | NO | | 10 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(20) | YES | | NULL | |
| test1 | varchar(20) | NO | UNI | NULL | |
+----------+----------------------------+------+-----+-------------+----------------+
14 rows in set (0.00 sec)
mysql>
mysql> ALTER TABLE user10 MODIFY email VARCHAR(50) NOT NULL DEFAULT '893243534@QQ.COM';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| test4 | int | NO | | 123 | |
| email | varchar(50) | NO | | 893243534@QQ.COM | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| test100 | int unsigned | NO | | 10 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(20) | YES | | NULL | |
| test1 | varchar(20) | NO | UNI | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
14 rows in set (0.01 sec)
mysql> ALTER TABLE user10 MODIFY card CHAR(18) AFTER test1;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| test4 | int | NO | | 123 | |
| email | varchar(50) | NO | | 893243534@QQ.COM | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| test100 | int unsigned | NO | | 10 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| test1 | varchar(20) | NO | UNI | NULL | |
| card | char(18) | YES | | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
14 rows in set (0.00 sec)
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| test4 | int | NO | | 123 | |
| email | varchar(50) | NO | | 893243534@QQ.COM | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| test100 | int unsigned | NO | | 10 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| test1 | varchar(20) | NO | UNI | NULL | |
| card | char(18) | YES | | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
14 rows in set (0.01 sec)
mysql> ALTER TABLE user10 MODIFY test1 CHAR(32) NOT NULL DEFAULT '123' FIRST;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| test1 | char(32) | NO | UNI | 123 | |
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| test4 | int | NO | | 123 | |
| email | varchar(50) | NO | | 893243534@QQ.COM | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| test100 | int unsigned | NO | | 10 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(18) | YES | | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
14 rows in set (0.00 sec)
修改字段的名称
ALTER TABLE tbl_name CHANGE 旧字段名称 新字段名称 字段类型 [完整性约束条件] [FIRST|ALTER字段名称]
mysql> ALTER TABLE user10 CHANGE test1 test CHAR(33) NOT NULL DEFAULT '123';
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| test | char(33) | NO | UNI | 123 | |
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| test4 | int | NO | | 123 | |
| email | varchar(50) | NO | | 893243534@QQ.COM | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| test100 | int unsigned | NO | | 10 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(18) | YES | | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
14 rows in set (0.00 sec)
mysql> ALTER TABLE user10 CHANGE test test1 VARCHAR(200) NOT NULL AFTER username;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| test1 | varchar(200) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| test4 | int | NO | | 123 | |
| email | varchar(50) | NO | | 893243534@QQ.COM | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| test100 | int unsigned | NO | | 10 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(18) | YES | | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
14 rows in set (0.00 sec)
mysql> ALTER TABLE user10 CHANGE test test INT(12);
ERROR 1054 (42S22): Unknown column 'test' in 'user10'
mysql> ALTER TABLE user10 CHANGE test test INT;
ERROR 1054 (42S22): Unknown column 'test' in 'user10'
mysql> ALTER TABLE user10 CHANGE test1 test1 INT(12);
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| test1 | int | YES | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| test4 | int | NO | | 123 | |
| email | varchar(50) | NO | | 893243534@QQ.COM | |
| age | tinyint unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| test100 | int unsigned | NO | | 10 | |
| addr | varchar(200) | NO | | BEIJING | |
| salary | float(6,3) | YES | | NULL | |
| regTime | int unsigned | YES | | NULL | |
| face | char(100) | NO | | default.JPG | |
| card | char(18) | YES | | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
14 rows in set (0.00 sec)
添加和删除默认值
ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值添加默认值ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT删除默认值
mysql> CREATE TABLE user11(
->
-> id TINYINT UNSIGNED KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL UNIQUE,
-> age TINYINT UNSIGNED
->
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> DESC user11;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| age | tinyint unsigned | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE user11 ALTER age SET DEFAULT 12;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user11;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| age | tinyint unsigned | YES | | 12 | |
+----------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE user11 ADD email VARCHAR(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user11;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| age | tinyint unsigned | YES | | 12 | |
| email | varchar(50) | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE user11 ALTER email SET DEFAULT '834464564@QQ.COM';
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user11;
+----------+------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| age | tinyint unsigned | YES | | 12 | |
| email | varchar(50) | YES | | 834464564@QQ.COM | |
+----------+------------------+------+-----+------------------+----------------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE user11 ALTER age DROP DEFAULT;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user11;
+----------+------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+------------------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| email | varchar(50) | YES | | 834464564@QQ.COM | |
+----------+------------------+------+-----+------------------+----------------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE user11 ALTER email DROP DEFAULT;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user11;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| age | tinyint unsigned | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
添加主键
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]PRIMARY KEY [index_type](字段名称……）ALTER TABLE tbl_name ADD PRIMARY KEY(字段名称);简单写
mysql> CREATE TABLE user12(
->
-> id INT
->
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc user12;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | YES | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> ALTER TABLE user12 ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user12;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> CREATE TABLE user13(
->
-> id INT,
-> card CHAR(18),
-> username VARCHAR(20) NOT NULL
->
-> );
Query OK, 0 rows affected (0.00 sec)
添加复合主键
mysql> DESC user13;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| card | char(18) | YES | | NULL | |
| username | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE user12 ADD PRIMARY KEY(id,card);
ERROR 1068 (42000): Multiple primary key defined
mysql> ALTER TABLE user13 ADD PRIMARY KEY(id,card);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user13;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| card | char(18) | NO | PRI | NULL | |
| username | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
删除主键
ALTER TABLE tbl_name DROP PRIMARY KEY
mysql> DESC user12;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | NO | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> ALTER TABLE user13 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user13;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int | NO | | NULL | |
| card | char(18) | NO | | NULL | |
| username | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> desc user12;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | NO | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> ALTER TABLE test12 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);
ERROR 1072 (42000): Key column 'id' doesn't exist in table
mysql> ALTER TABLE user12 ADD CONSTRAINT symbol PRIMARY KEY index_type(id);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user12;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)
删除主键时的一个问题
因为此时id是自增长 如果他是自增长的话 就不能删除此主键 自增长前提这个是主键，所以我们必须先删除自增长 然后才能删除主键。
mysql> CREATE TABLE user14(
->
-> id INT UNSIGNED KEY AUTO_INCREMENT
->
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc user14;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
+-------+--------------+------+-----+---------+----------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE user14 DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> ALTER TABLE user14 MODIFY id INT NSIGNED;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NSIGNED' at line 1
mysql> ALTER TABLE user14 MODIFY id INT UNSIGNED;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user14;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int unsigned | NO | PRI | NULL | |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> ALTER TABLE user14 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user14;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int unsigned | NO | | NULL | |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)
添加和删除唯一
ALTER TABLE tbl_name ADD [constraint [symbol] PRIMARY KEY [index_type][索引名称](字段名称）ALTER TABLE tbl_name DROP index_name
mysql> CREATE TABLE user15(
->
-> id TINYINT UNSIGNED KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL,
-> card CHAR(18) NOT NULL,
-> test VARCHAR(20) NOT NULL,
-> test1 CHAR(32) NOT NULL
->
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc user15;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| card | char(18) | NO | | NULL | |
| test | varchar(20) | NO | | NULL | |
| test1 | char(32) | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE user15 ADD UNIQUE(username);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user15;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| card | char(18) | NO | | NULL | |
| test | varchar(20) | NO | | NULL | |
| test1 | char(32) | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE user15 ADD CONSTRAINT symbol UNIQUE KEY uni_card(card);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user15;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| card | char(18) | NO | UNI | NULL | |
| test | varchar(20) | NO | | NULL | |
| test1 | char(32) | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE user15;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user15 | CREATE TABLE `user15` (
`id` tinyint unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`card` char(18) NOT NULL,
`test` varchar(20) NOT NULL,
`test1` char(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `uni_card` (`card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE user15 ADD CONSTRAINT symbol UNIQUE KEY mulUni_card(test,test1);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user15;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user15 | CREATE TABLE `user15` (
`id` tinyint unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`card` char(18) NOT NULL,
`test` varchar(20) NOT NULL,
`test1` char(32) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `uni_card` (`card`),
UNIQUE KEY `mulUni_card` (`test`,`test1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc user15;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| card | char(18) | NO | UNI | NULL | |
| test | varchar(20) | NO | MUL | NULL | |
| test1 | char(32) | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE user15 DROP INDEX username;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user15;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| card | char(18) | NO | UNI | NULL | |
| test | varchar(20) | NO | MUL | NULL | |
| test1 | char(32) | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> ALTER TABLE user15 DROP KEY uni_card;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE user15 DROP KEY mulUni_card;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc user15;
+----------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| card | char(18) | NO | | NULL | |
| test | varchar(20) | NO | | NULL | |
| test1 | char(32) | NO | | NULL | |
+----------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
修改表的储存引擎
ALTER TABLE tbl_name ENGINE=存储引擎名称
mysql> show create table
-> user15;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user15 | CREATE TABLE `user15` (
`id` tinyint unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`card` char(18) NOT NULL,
`test` varchar(20) NOT NULL,
`test1` char(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE user15 user15 ENGINE = MyISAM;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user15 ENGINE = MyISAM' at line 1
mysql> ALTER TABLE user15 ENGINE = MyISAM;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table user15;
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user15 | CREATE TABLE `user15` (
`id` tinyint unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`card` char(18) NOT NULL,
`test` varchar(20) NOT NULL,
`test1` char(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
设置自增长的值
ALTER TABLE tbl_name AUTO_INCREMENT=值
mysql> ALTER TABLE user15 AUTO_INCREMENT = 100;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table user15;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user15 | CREATE TABLE `user15` (
`id` tinyint unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`card` char(18) NOT NULL,
`test` varchar(20) NOT NULL,
`test1` char(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_maizi |
+-----------------+
| cms_cate |
| cms_news |
| course |
| test1 |
| test10 |
| test11 |
| test12 |
| test2 |
| test3 |
| test4 |
| test5 |
| test6 |
| test7 |
| test8 |
| test9 |
| user |
| user1 |
| user10 |
| user11 |
| user12 |
| user13 |
| user14 |
| user15 |
| user2 |
| user3 |
| user4 |
| user5 |
| user6 |
| user7 |
| user8 |
| user9 |
+-----------------+
31 rows in set (0.00 sec)
删除数据表
DROP TABLE [IF EXISTS] tbl_name[, tbl_name…]
mysql> DROP TABLE user12;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_maizi |
+-----------------+
| cms_cate |
| cms_news |
| course |
| test1 |
| test10 |
| test11 |
| test12 |
| test2 |
| test3 |
| test4 |
| test5 |
| test6 |
| test7 |
| test8 |
| test9 |
| user |
| user1 |
| user10 |
| user11 |
| user13 |
| user14 |
| user15 |
| user2 |
| user3 |
| user4 |
| user5 |
| user6 |
| user7 |
| user8 |
| user9 |
+-----------------+
30 rows in set (0.00 sec)
mysql> DROP TABLE user11,user10,user9;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-----------------+
| Tables_in_maizi |
+-----------------+
| cms_cate |
| cms_news |
| course |
| test1 |
| test10 |
| test11 |
| test12 |
| test2 |
| test3 |
| test4 |
| test5 |
| test6 |
| test7 |
| test8 |
| test9 |
| user |
| user1 |
| user13 |
| user14 |
| user15 |
| user2 |
| user3 |
| user4 |
| user5 |
| user6 |
| user7 |
| user8 |
+-----------------+
27 rows in set (0.00 sec)
mysql>
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
