MySQL中常用语句1
SELECT USER()------得到登陆的用户
SELECT VERSION()------得到MySQL的版本信息
SELECT NOW()------得到当前的日期时间
SELECT DATABASE()------得到当前打开的数据库
1. 创建数据库
CREATE {DATABASE|SCHEMA} db_name;------检测数据库名称是否存在，不存在则创建
CREATE DATABASE [IF NOT EXISTS] db_name;------在创建数据库的同时指定编码方式
CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset;
注意
数据库名称最好有意义
名称不要包含特殊字符或者是MySQL关键字
查看当前服务器下全部数据库------SHOW DATABASES|SCHEMAS;
查看指定数据库的详细信息------SHOW CREATE DATABASE db_name;
修改指定数据库的编码方式------ALTER DATABASE db_name [DEFAULT] CHARACTER SET [=] charset;
打开指定数据库------USE db_name;
得到当前打开的数据库------SELECT DATABASE()|SCHEMA();
删除指定的数据库------DROP DATABASE db_name;
如果数据库存在则删除------DROP DATABASE [IF EXISTS] db_name;
2. 创建表
CREATE TABLE [IF NOT EXISTS] tbl_name(
字段名称 字段类型 [完整性约束条件],
字段名称 字段类型 [完整性约束条件],
...
)ENGINE=存储引擎 CHARSET=编码方式;
UNSIGNED------无符号，没有负数，从0开始
ZEROFILL------零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
NOT NULL------非空约束，也就是插入值的时候这个字段必须要给值,值不能为空
DEFAULT------默认值，如果插入记录的时候没有给字段赋值，则使用默认值
PRIMARY KEY------主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，自动禁止为空
AUTO_INCREMENT------自动增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
UNIQUE KEY------唯一性，一个表中可以有多个字段是唯一索引，同样的值不能重复，但是NULL值除外
FOREIGN KEY------外键约束
查看当前数据库下已有数据表------SHOW TABLES;
------SHOW [FULL] TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
查看指定数据表的详细信息------SHOW CREATE TABLE tbl_name;
查看表结构------DESC tbl_name;
------DESCRIBE tbl_name;
------SHOW COLUMNS FROM tbl_name;
删除指定的数据表------DROP TABLE [IF EXISTS] tbl_name;
3. 表结构相关操作
① SELECT 相关操作:
SELECT 列名称 FROM 表名称 SELECT * FROM 表名称 星号（*）是选取所有列的快捷方式。 关键词 DISTINCT 用于返回唯一不同的值。 SELECT DISTINCT 列名称 FROM 表名称
SELECT select_expr,... FROM tbl_name
[WHERE 条件]
[GROUP BY {col_name|position} HAVING 二次筛选]
[ORDER BY {col_name|position|expr} [ASC|DESC]]
[LIMIT 限制结果集的显示条数] ;
查询表中所有记录:
SELECT * FROM tbl_name;
*所有字段
指定字段的信息:
SELECT 字段名称,... FROM tbl_name
库名.表名:
SELECT 字段名称,... FROM db_name.tbl_name;
给字段起别名:
SELECT 字段名称 [AS] 别名名称,... FROM db_name.tbl_name;
给数据表起别名:
SELECT 字段名称 ,... FROM tbl_name [AS] 别名;
表名.字段名的:
SELECT tbl_name.col_name,... FROM tbl_name;
② WHERE 子句用于规定选择的标准:
SELECT 列名 FROM 表名 WHERE 列 运算符 值
下面的运算符可在 WHERE 子句中使用：
|
操作符 |
描述 |
| = | 等于 |
| <> / != | 不等于 |
|
> |
大于 |
| < | 小于 |
| >= | 大于等于 |
| <= | 小于等于 |
| BETWEEN | 在某个范围内 |
| LIKE | 搜索某种模式 |
文本值： 这是正确的： SELECT * FROM Persons WHERE FirstName='Bush' 这是错误的： SELECT * FROM Persons WHERE FirstName=Bush
数值： 这是正确的： SELECT * FROM Persons WHERE Year>1965 这是错误的： SELECT * FROM Persons WHERE Year>'1965'
AND 和 OR 运算符
AND 和 OR 可在 WHERE 子语句中把两个或多个条件结合起来。
如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。
AND 运算符实例 使用 AND 来显示所有姓为 "firstname" 并且名为 "lastname" 的人： SELECT * FROM Persons WHERE FirstName='firstname' AND LastName='lastname'
使用 OR 来显示所有姓为 "firstname" 或者名为 "lastname" 的人：
SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'
可以把 AND 和 OR 结合起来（使用圆括号来组成复杂的表达式）: SELECT * FROM Persons WHERE (FirstName='Thomas' OR FirstName='William') AND LastName='Carter'
ORDER BY 语句
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照升序对记录进行排序。
如果希望按照降序对记录进行排序，可以使用 DESC 关键字。
SELECT column1, column2 FROM Orders ORDER BY column2 --按照column2升序排序
SELECT Column1, Column2 FROM Orders ORDER BY Column2 DESC --按照Column2降序排序
INSERT INTO 语句 INSERT INTO 语句用于向表格中插入新的行
语法 INSERT INTO 表名称 VALUES (值1, 值2,....) 我们也可以指定所要插入数据的列： INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
INSERT table_name SET 字段名称=值, 字段名称=值, ...., 字段名称=值; --(example: INSERT user SET username='streamice', age=18, email='12345@163.com'; )
INSERT [INTO] table_name1(字段名称1, 字段名称2, ...) SELECT 字段名称1, 字段名称2, ... FROM table_name2 [WHERE条件]
--解释: 从table_name2中选出一些字段名称插入到table_name1中相应的字段中(example: INSERT user(username) SELECT username_temp FROM temp;
--user和temp为表, username和username_temp为相应表中的其中一个字段)<两个字段的名字不一定要相同 >
一次插入多条记录:
INSERT [INTO] table_name(列1, 列2, ...) VALUES(值1, 值2, ...), (值1, 值2, ...), ....., (值1, 值2, ...);
mysql> SELECT * FROM test_auto_increment; +----+----------+ | id | username | +----+----------+ | 1 | A | | 2 | B | | 3 | C | | 4 | E | | 5 | F | | 15 | G | +----+----------+ 6 rows in set (0.00 sec) mysql> INSERT INTO test_auto_increment VALUES(20,'Twenty'); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM test_auto_increment; +----+----------+ | id | username | +----+----------+ | 1 | A | | 2 | B | | 3 | C | | 4 | E | | 5 | F | | 15 | G | | 20 | Twenty | +----+----------+ 7 rows in set (0.00 sec)
Update 语句
Update 语句用于修改表中的数据。
UPDATE 表名称 SET 列名称(1) = 新值 WHERE 列名称(2) = 旧值
UPDATE table_name SET 字段名1=值1, 字段名2=值2, ... WHERE 字段名=值; --若没有WHERE条件, 则整个表中的记录都会被更新
注: 列名称(1)是要UPDATE的值, 列名称(2)是原来的字段(column列)------------UPDATE 表名称 SET [要更新的部分] WHERE [旧部分]
[]mysql> UPDATE test_auto_increment SET username='Five' WHERE username='F'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM test_auto_increment; +----+----------+ | id | username | +----+----------+ | 1 | A | | 2 | B | | 3 | C | | 4 | E | | 5 | Five | | 15 | G | | 20 | Twenty | +----+----------+ 7 rows in set (0.00 sec) mysql> UPDATE test_auto_increment SET id=100 WHERE id=15; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM test_auto_increment; +-----+----------+ | id | username | +-----+----------+ | 1 | A | | 2 | B | | 3 | C | | 4 | E | | 5 | Five | | 20 | Twenty | | 100 | G | +-----+----------+ 7 rows in set (0.00 sec) mysql> UPDATE test_auto_increment SET username='Onehundred' WHERE id=100; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM test_auto_increment; +-----+------------+ | id | username | +-----+------------+ | 1 | A | | 2 | B | | 3 | C | | 4 | E | | 5 | Five | | 20 | Twenty | | 100 | Onehundred | +-----+------------+ 7 rows in set (0.00 sec)
DELETE 语句
DELETE 语句用于删除表中的行。
DELETE FROM 表名称 WHERE 列名称 = 值 --(如果不添加WHERE条件, 表中所有记录都会被删除)
mysql> SELECT * FROM test_auto_increment; +-----+------------+ | id | username | +-----+------------+ | 1 | A | | 2 | B | | 3 | C | | 4 | E | | 5 | Five | | 20 | Twenty | | 100 | Onehundred | +-----+------------+ 7 rows in set (0.00 sec) mysql> DELETE FROM test_auto_increment WHERE id=20; Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM test_auto_increment; +-----+------------+ | id | username | +-----+------------+ | 1 | A | | 2 | B | | 3 | C | | 4 | E | | 5 | Five | | 100 | Onehundred | +-----+------------+ 6 rows in set (0.00 sec)
删除所有行
可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：
DELETE FROM table_name 或者： DELETE * FROM table_name
删除记录:
DELETE FROM tbl_name [WHERE 条件]
如果不添加条件，表中所有记录都会被删除
DELETE 清空数据表的时候不会重置AUTO_INCREMENT的值，可以通过ALTER 语句将其重置为1 ALTER TABLE table_name AUTO_INCREMENT=1
彻底清空数据表:
TRUNCATE [TABLE] tbl_name;
清除表中所有记录
会重置AUTO_INCREMENT的值
添加字段------ALTER TABLE tbl_name
------ADD 字段名称 字段属性 [完整性约束条件] [FIRST|AFTER 字段名称]
删除字段------ALTER TABLE tbl_name
------DROP 字段名称
添加默认值------ALTER TABLE tbl_name
------ALTER 字段名称 SET DEFAULT 默认值;
删除默认值------ALTER TABLE tbl_name
------ALTER 字段名称 DROP DEFAULT
修改字段类型、字段属性------ALTER TABLE tbl_name
------MODIFY 字段名称 字段类型 [字段属性] [FIRST | AFTER 字段名称]
修改字段名称、字段类型、字段属性------ALTER TABLE tbl_name
------CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST | AFTER 字段名称]
添加主键------ALTER TABLE tbl_name
------ADD PRIMARY KEY(字段名称)
删除主键------ALTER TABLE tbl_name
------DROP PRIMARY KEY;
添加唯一------ALTER TABLE tbl_name
------ADD UNIQUE KEY|INDEX [index_name] (字段名称)
删除唯一------ALTER TABLE tbl_name
------DROP index_name;
修改数据表名称------ALTER TABLE tbl_name
------RENAME [TO|AS] new_tbl_name
RENAME TABLE tbl_name TO new_tbl_name;------修改AUTO_INCREMENT的值
------ALTER TABLE tbl_name AUTO_INCREMENT=值
-----------------------------------------------------------------------------------
--测试添加和删除字段 CREATE TABLE IF NOT EXISTS user1( `id` INT UNSIGNED AUTO_INCREMENT KEY )ENGINE=INNODB DEFAULT CHARSET=UTF8; --添加用户名字段 username VARCHAR(20) ALTER TABLE user1 ADD username VARCHAR(20); --添加密码字段 password CAHR(32) NOT NULL ALTER TABLE user1 ADD password CHAR(32) NOT NULL; --添加邮箱字段 email VARCHAR(50) NOT NULL UNIQUE 加到username之后 ALTER TABLE user1 ADD email VARCHAR(50) NOT NULL UNIQUE KEY AFTER username; --添加测试字段 test VARCHAR(10) NOT NULL DEFAULT 0 加到最前面 ALTER TABLE user1 ADD test VARCHAR(10) NOT NULL DEFAULT 0 FIRST; --删除字段 ALTER TABLE user1 DROP test; --添加age, addr字段, 删除email字段 ALTER TABLE user1 ADD age INT UNSIGNED NOT NULL DEFAULT 18, ADD addr VARCHAR(100) NOT NULL DEFAULT '北京', DROP IF EXISTS email; ----------------------------------------------------------------------------- mysql> DESC user1; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | +-------+------------------+------+-----+---------+----------------+ 1 row in set (0.01 sec) mysql> ALTER TABLE user1 -> ADD username VARCHAR(20); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user1; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | YES | | NULL | | +----------+------------------+------+-----+---------+----------------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE user1 -> ADD password CHAR(32) NOT NULL; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user1; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | YES | | NULL | | | password | char(32) | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> ALTER TABLE user1 -> ADD email VARCHAR(50) NOT NULL UNIQUE KEY AFTER username; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user1; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | YES | | NULL | | | email | varchar(50) | NO | UNI | NULL | | | password | char(32) | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> ALTER TABLE user1 -> ADD test VARCHAR(10) NOT NULL DEFAULT 0 FIRST; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user1; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | test | varchar(10) | NO | | 0 | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | YES | | NULL | | | email | varchar(50) | NO | UNI | NULL | | | password | char(32) | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec) mysql> ALTER TABLE user1 -> DROP test; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user1; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | YES | | NULL | | | email | varchar(50) | NO | UNI | NULL | | | password | char(32) | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> ALTER TABLE user1 -> ADD age INT UNSIGNED NOT NULL DEFAULT 18, -> ADD addr VARCHAR(100) NOT NULL DEFAULT '北京', -> DROP IF EXISTS email; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user1; , ', +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | YES | | NULL | | | password | char(32) | NO | | NULL | | | age | int(10) unsigned | NO | | 18 | | | addr | varchar(100) | NO | | 北京 | | +----------+------------------+------+-----+---------+----------------+ 5 rows in set (0.01 sec)
对字段默认值得操作:
--测试添加和删除默认值操作 CREATE TABLE IF NOT EXISTS user2( id INT UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(20) NOT NULL , age TINYINT UNSIGNED NOT NULL DEFAULT 18, email VARCHAR(50) NOT NULL )ENGINE=INNODB DEFAULT CHARSET=UTF8; --给email字段添加默认值 Tencent@qq.com, 并且删除age字段的默认值 ALTER TABLE user2 ALTER email SET DEFAULT 'Tencent@qq.com', ALTER age DROP DEFAULT; ------------------------------------------------------------------------------------- mysql> CREATE TABLE IF NOT EXISTS user2( -> id INT UNSIGNED AUTO_INCREMENT KEY, -> username VARCHAR(20) NOT NULL , -> age TINYINT UNSIGNED NOT NULL DEFAULT 18, -> email VARCHAR(50) NOT NULL -> )ENGINE=INNODB DEFAULT CHARSET=UTF8; Query OK, 0 rows affected (0.02 sec) mysql> DESC user2; +----------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | 18 | | | email | varchar(50) | NO | | NULL | | +----------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> ALTER TABLE user2 -> ALTER email SET DEFAULT 'Tencent@qq.com', -> ALTER age DROP DEFAULT; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user2; +----------+---------------------+------+-----+----------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+----------------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | NO | | NULL | | | email | varchar(50) | NO | | Tencent@qq.com | | +----------+---------------------+------+-----+----------------+----------------+ 4 rows in set (0.01 sec) mysql> SELECT * FROM user2; Empty set (0.00 sec)
修改字段类型, 名称, 属性:
--测试修改字段类型和字段属性, 字段名称 CREATE TABLE user3( id INT UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(5)NOT NULL UNIQUE, password CHAR(32) NOT NULL , email VARCHAR(10) NOT NULL )ENGINE=INNODB DEFAULT CHARSET=UTF8; --将用户名字段类型改为20 ALTER TABLE user3 MODIFY username VARCHAR(20) NOT NULL UNIQUE; --将email字段改为VARCHAR(50) NOT NULL FIRST ALTER TABLE user3 MODIFY email VARCHAR(50) NOT NULL FIRST; --将username名称改为user, password改为pwd ALTER TABLE user3 CHANGE username user VARCHAR(20) NOT NULL, CHANGE password pwd CHAR(40) NOT NULL UNIQUE KEY; ------------------------------------------------------------------------------ mysql> CREATE TABLE user3( -> id INT UNSIGNED AUTO_INCREMENT KEY, -> username VARCHAR(5)NOT NULL UNIQUE, -> password CHAR(32) NOT NULL , -> email VARCHAR(10) NOT NULL -> )ENGINE=INNODB DEFAULT CHARSET=UTF8; Query OK, 0 rows affected (0.01 sec) mysql> DESC user3; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(5) | NO | UNI | NULL | | | password | char(32) | NO | | NULL | | | email | varchar(10) | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> ALTER TABLE user3 -> MODIFY email VARCHAR(50) NOT NULL FIRST; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user3; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | email | varchar(50) | NO | | NULL | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | UNI | NULL | | | password | char(32) | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> ALTER TABLE user3 -> CHANGE username user VARCHAR(20) NOT NULL, -> CHANGE password pwd CHAR(40) NOT NULL UNIQUE KEY; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user3; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | email | varchar(50) | NO | | NULL | | | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | user | varchar(20) | NO | UNI | NULL | | | pwd | char(40) | NO | UNI | NULL | | +-------+------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)
主键(PRIMARY KEY)操作:
--测试添加和删除主键 CREATE TABLE user4( id INT UNSIGNED, username VARCHAR(20) NOT NULL )ENGINE=INNODB DEFAULT CHARSET=UTF8; -- 给id字段添加primary key ALTER TABLE user4 ADD PRIMARY KEY (id); --删除主键 ALTER TABLE user4 DROP PRIMARY KEY; --在去除有AUTO_INCREMENT 属性的 PRIMARY KEY 字段时, 要先去掉该字段的AUTO_INCREMENT属性 --不然会报错ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key. CREATE TABLE user5( id INT UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(20) NOT NULL )ENGINE=INNODB DEFAULT CHARSET=UTF8; --此时还没有去除AUTO_INCREMENT属性 ALTER TABLE user5 DROP PRIMARY KEY; --ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key --将id的AUTO_INCREMENT属性去掉 ALTER TABLE user5 MODIFY id INT UNSIGNED NOT NULL; --去除QUTO_INCREMENT属性后: ALTER TABLE user5 DROP PRIMARY KEY; ---------------------------------------------------------------------- mysql> CREATE TABLE user5( -> id INT UNSIGNED AUTO_INCREMENT KEY, -> username VARCHAR(20) NOT NULL -> )ENGINE=INNODB DEFAULT CHARSET=UTF8; Query OK, 0 rows affected (0.01 sec) mysql> DESC user5; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ 2 rows in set (0.01 sec) mysql> ALTER TABLE user5 -> 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 user5 -> MODIFY id INT UNSIGNED NOT NULL; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user5; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | PRI | NULL | | | username | varchar(20) | NO | | NULL | | +----------+------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> ALTER TABLE user5 -> DROP PRIMARY KEY; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user5; +----------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | | NULL | | | username | varchar(20) | NO | | NULL | | +----------+------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)
唯一索引(UNIQUE KEY)操作:
--测试添加和删除唯一(UNIQUE KEY) CREATE TABLE user6( id INT UNSIGNED AUTO_INCREMENT KEY, username VARCHAR(100) NOT NULL UNIQUE, password CHAR(32) NOT NULL, email VARCHAR(50) NOT NULL UNIQUE )ENGINE=INNODB DEFAULT CHARSET=UTF8; --MySQL中默认是唯一索引的名称和字段名称相同 --删除唯一索引 ALTER TABLE user6 DROP INDEX username; --添加唯一索引 ALTER TABLE user6 ADD UNIQUE KEY(username); --如果要指定唯一索引的名称(即不要"默认是唯一索引的名称和字段名称相同") ALTER TABLE user6 ADD UNIQUE INDEX uni_email(email); --指定唯一索引的名称后, 如果要删除该索引, 就要使用指定后的索引名称 ALTER TABLE user6 DROP INDEX uni_email; ------------------------------------------------------------------------------------ mysql> CREATE TABLE user6( -> id INT UNSIGNED AUTO_INCREMENT KEY, -> username VARCHAR(100) NOT NULL UNIQUE, -> password CHAR(32) NOT NULL, -> email VARCHAR(50) NOT NULL UNIQUE -> )ENGINE=INNODB DEFAULT CHARSET=UTF8; Query OK, 0 rows affected (0.02 sec) mysql> DESC user6; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(100) | NO | UNI | NULL | | | password | char(32) | NO | | NULL | | | email | varchar(50) | NO | UNI | NULL | | +----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> ALTER TABLE user6 -> DROP INDEX username; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user6; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(100) | NO | | NULL | | | password | char(32) | NO | | NULL | | | email | varchar(50) | NO | UNI | NULL | | +----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) mysql> ALTER TABLE user6 -> ADD UNIQUE KEY(username); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user6; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(100) | NO | UNI | NULL | | | password | char(32) | NO | | NULL | | | email | varchar(50) | NO | UNI | NULL | | +----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> SHOW CREATE TABLE user6; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user6 | CREATE TABLE `user6` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(100) NOT NULL, `password` char(32) NOT NULL, `email` varchar(50) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`),--------------------<------------------MySQL中默认unique key 的值和字段名相同 UNIQUE KEY `username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE user6 -> DROP INDEX email; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user6; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(100) | NO | UNI | NULL | | | password | char(32) | NO | | NULL | | | email | varchar(50) | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> ALTER TABLE user6 -> ADD UNIQUE INDEX uni_email(email); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user6; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(100) | NO | UNI | NULL | | | password | char(32) | NO | | NULL | | | email | varchar(50) | NO | UNI | NULL | | +----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) mysql> mysql> mysql> SHOW CREATE TABLE user6; +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user6 | CREATE TABLE `user6` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(100) NOT NULL, `password` char(32) NOT NULL, `email` varchar(50) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), UNIQUE KEY `uni_email` (`email`)------------<----------自定义unique key 的名字 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE user6 -> DROP UNIQUE INDEX uni_email; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UNIQUE INDEX uni_email' at line 2 mysql> ALTER TABLE user6 -> DROP INDEX uni_email; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC user6; +----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | username | varchar(100) | NO | UNI | NULL | | | password | char(32) | NO | | NULL | | | email | varchar(50) | NO | | NULL | | +----------+------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)
重命名(RENAME):
--修改数据表名称 ALTER TABLE tab_name RENAME [TO|AS] new_tab_name; --或者: RENAME TABLE tab_name TO new_tab_name; --将user6改为user666 ALTER TABLE user6 RENAME TO user666; -------------------------------------------------------------------------------------------- mysql> ALTER TABLE user6 -> RENAME TO user666; Query OK, 0 rows affected (0.01 sec) mysql> SHOW TABLES; +---------------------+ | Tables_in_test | +---------------------+ | admin_role | | imooc_user | | product | | test_auto_increment | | test_date | | test_date_and_time | | test_primary_key | | test_primary_key2 | | test_primary_key3 | | test_primary_key4 | | test_set | | test_time | | test_timestamp | | user1 | | user2 | | user3 | | user4 | | user5 | | user666 | | wh_logrecord | +---------------------+ 20 rows in set (0.00 sec) mysql> RENAME TABLE user666 TO user66; Query OK, 0 rows affected (0.01 sec) mysql> SHOW TABLES; +---------------------+ | Tables_in_test | +---------------------+ | admin_role | | imooc_user | | product | | test_auto_increment | | test_date | | test_date_and_time | | test_primary_key | | test_primary_key2 | | test_primary_key3 | | test_primary_key4 | | test_set | | test_time | | test_timestamp | | user1 | | user2 | | user3 | | user4 | | user5 | | user66 | | wh_logrecord | +---------------------+ 20 rows in set (0.00 sec)
