一下mysql的语句正确的是_MySQL中常用语句1

最新推荐文章于 2022-05-06 09:53:46 发布

weixin_39741459

最新推荐文章于 2022-05-06 09:53:46 发布

阅读量372

点赞数

文章标签：一下mysql的语句正确的是

本文链接：https://blog.csdn.net/weixin_39741459/article/details/113551194

版权

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

在某个范围内

搜索某种模式

文本值：

这是正确的：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 * FROMtest_auto_increment;+----+----------+

| id | username |

+----+----------+

| 1 | A |

| 2 | B |

| 3 | C |

| 4 | E |

| 5 | F |

| 15 | G |

+----+----------+

6 rows in set (0.00sec)

mysql> INSERT INTO test_auto_increment VALUES(20,'Twenty');

Query OK,1 row affected (0.01sec)

mysql> SELECT * FROMtest_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.01sec)

Rows matched:1 Changed: 1 Warnings: 0mysql> SELECT * FROMtest_auto_increment;+----+----------+

| id | username |

+----+----------+

| 1 | A |

| 2 | B |

| 3 | C |

| 4 | E |

| 5 | Five |

| 15 | G |

| 20 | Twenty |

+----+----------+

7 rows in set (0.00sec)mysql> UPDATE test_auto_increment SET id=100 WHERE id=15;

Query OK,1 row affected (0.01sec)

Rows matched:1 Changed: 1 Warnings: 0mysql> SELECT * FROMtest_auto_increment;+-----+----------+

| id | username |

+-----+----------+

| 1 | A |

| 2 | B |

| 3 | C |

| 4 | E |

| 5 | Five |

| 20 | Twenty |

| 100 | G |

+-----+----------+

7 rows in set (0.00sec)

mysql> UPDATE test_auto_increment SET username='Onehundred' WHERE id=100;

Query OK,1 row affected (0.01sec)

Rows matched:1 Changed: 1 Warnings: 0mysql> SELECT * FROMtest_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 * FROMtest_auto_increment;+-----+------------+

| id | username |

+-----+------------+

| 1 | A |

| 2 | B |

| 3 | C |

| 4 | E |

| 5 | Five |

| 20 | Twenty |

| 100 | Onehundred |

+-----+------------+

7 rows in set (0.00sec)

mysql> DELETE FROM test_auto_increment WHERE id=20;

Query OK,1 row affected (0.01sec)

mysql> SELECT * FROMtest_auto_increment;+-----+------------+

| id | username |

+-----+------------+

| 1 | A |

| 2 | B |

| 3 | C |

| 4 | E |

| 5 | Five |

| 100 | Onehundred |

+-----+------------+

6 rows in set (0.00 sec)

删除所有行

可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：

DELETE FROMtable_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 EXISTSuser1(

`id`INT UNSIGNED AUTO_INCREMENT KEY)ENGINE=INNODB DEFAULT CHARSET=UTF8;--添加用户名字段 username VARCHAR(20)

ALTER TABLEuser1ADD username VARCHAR(20);--添加密码字段 password CAHR(32) NOT NULL

ALTER TABLEuser1ADD password CHAR(32) NOT NULL;--添加邮箱字段 email VARCHAR(50) NOT NULL UNIQUE 加到username之后

ALTER TABLEuser1ADD email VARCHAR(50) NOT NULL UNIQUE KEYAFTER username;--添加测试字段 test VARCHAR(10) NOT NULL DEFAULT 0 加到最前面

ALTER TABLEuser1ADD test VARCHAR(10) NOT NULL DEFAULT 0FIRST;--删除字段

ALTER TABLEuser1DROPtest;--添加age, addr字段, 删除email字段

ALTER TABLEuser1ADD age INT UNSIGNED NOT NULL DEFAULT 18,ADD addr VARCHAR(100) NOT NULL DEFAULT '北京',DROP IF EXISTSemail;-----------------------------------------------------------------------------

mysql> DESCuser1;+-------+------------------+------+-----+---------+----------------+

+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+-------+------------------+------+-----+---------+----------------+

1 row in set (0.01sec)

mysql> ALTER TABLEuser1-> ADD username VARCHAR(20);

Query OK,0 rows affected (0.04sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser1;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

2 rows in set (0.00sec)

mysql> ALTER TABLEuser1-> ADD password CHAR(32) NOT NULL;

Query OK,0 rows affected (0.01sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser1;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

3 rows in set (0.00sec)

mysql> ALTER TABLEuser1-> ADD email VARCHAR(50) NOT NULL UNIQUE KEYAFTER username;

Query OK,0 rows affected (0.04sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser1;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

4 rows in set (0.01sec)

mysql> ALTER TABLEuser1-> ADD test VARCHAR(10) NOT NULL DEFAULT 0FIRST;

Query OK,0 rows affected (0.02sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser1;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| test | varchar(10) | NO | | 0 | |

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

5 rows in set (0.01sec)

mysql> ALTER TABLEuser1-> DROPtest;

Query OK,0 rows affected (0.02sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser1;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

4 rows in set (0.01sec)

mysql> ALTER TABLEuser1-> ADD age INT UNSIGNED NOT NULL DEFAULT 18,-> ADD addr VARCHAR(100) NOT NULL DEFAULT '北京',-> DROP IF EXISTSemail;

Query OK,0 rows affected (0.02sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESC user1; , ',

+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| age | int(10) unsigned | NO | | 18 | |

| addr | varchar(100) | NO | | 北京 | |

+----------+------------------+------+-----+---------+----------------+

5 rows in set (0.01 sec)

对字段默认值得操作:

--测试添加和删除默认值操作

CREATE TABLE IF NOT EXISTSuser2(

idINT UNSIGNED AUTO_INCREMENT KEY,

usernameVARCHAR(20) NOT NULL,

ageTINYINT UNSIGNED NOT NULL DEFAULT 18,

emailVARCHAR(50) NOT NULL)ENGINE=INNODB DEFAULT CHARSET=UTF8;--给email字段添加默认值 Tencent@qq.com, 并且删除age字段的默认值

ALTER TABLEuser2ALTER email SET DEFAULT 'Tencent@qq.com',ALTER age DROP DEFAULT;-------------------------------------------------------------------------------------

mysql> CREATE TABLE IF NOT EXISTSuser2(-> 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.02sec)

mysql> DESCuser2;+----------+---------------------+------+-----+---------+----------------+

+----------+---------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| age | tinyint(3) unsigned | NO | | 18 | |

+----------+---------------------+------+-----+---------+----------------+

4 rows in set (0.01sec)

mysql> ALTER TABLEuser2-> ALTER email SET DEFAULT 'Tencent@qq.com',-> ALTER age DROP DEFAULT;

Query OK,0 rows affected (0.00sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser2;+----------+---------------------+------+-----+----------------+----------------+

+----------+---------------------+------+-----+----------------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| age | tinyint(3) unsigned | NO | | NULL | |

+----------+---------------------+------+-----+----------------+----------------+

4 rows in set (0.01sec)

mysql> SELECT * FROMuser2;

Emptyset (0.00 sec)

修改字段类型, 名称, 属性:

--测试修改字段类型和字段属性, 字段名称

CREATE TABLEuser3(

idINT UNSIGNED AUTO_INCREMENT KEY,

usernameVARCHAR(5)NOT NULL UNIQUE,

passwordCHAR(32) NOT NULL,

emailVARCHAR(10) NOT NULL)ENGINE=INNODB DEFAULT CHARSET=UTF8;--将用户名字段类型改为20

ALTER TABLEuser3

MODIFY usernameVARCHAR(20) NOT NULL UNIQUE;--将email字段改为VARCHAR(50) NOT NULL FIRST

ALTER TABLEuser3

MODIFY emailVARCHAR(50) NOT NULLFIRST;--将username名称改为user, password改为pwd

ALTER TABLEuser3

CHANGE usernameuser VARCHAR(20) NOT NULL,

CHANGE password pwdCHAR(40) NOT NULL UNIQUE KEY;------------------------------------------------------------------------------

mysql> CREATE TABLEuser3(-> 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.01sec)

mysql> DESCuser3;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

4 rows in set (0.01sec)

mysql> ALTER TABLEuser3-> MODIFY email VARCHAR(50) NOT NULLFIRST;

Query OK,0 rows affected (0.03sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser3;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

4 rows in set (0.01sec)

mysql> ALTER TABLEuser3-> CHANGE username user VARCHAR(20) NOT NULL,-> CHANGE password pwd CHAR(40) NOT NULL UNIQUE KEY;

Query OK,0 rows affected (0.03sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser3;+-------+------------------+------+-----+---------+----------------+

+-------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

| pwd | char(40) | NO | UNI | NULL | |

+-------+------------------+------+-----+---------+----------------+

4 rows in set (0.01 sec)

主键(PRIMARY KEY)操作:

--测试添加和删除主键

CREATE TABLEuser4(

idINTUNSIGNED,

usernameVARCHAR(20) NOT NULL)ENGINE=INNODB DEFAULT CHARSET=UTF8;--给id字段添加primary key

ALTER TABLEuser4ADD PRIMARY KEY(id);--删除主键

ALTER TABLEuser4DROP 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 TABLEuser5(

idINT UNSIGNED AUTO_INCREMENT KEY,

usernameVARCHAR(20) NOT NULL)ENGINE=INNODB DEFAULT CHARSET=UTF8;--此时还没有去除AUTO_INCREMENT属性

ALTER TABLEuser5DROP 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 TABLEuser5

MODIFY idINT UNSIGNED NOT NULL;--去除QUTO_INCREMENT属性后:

ALTER TABLEuser5DROP PRIMARY KEY;----------------------------------------------------------------------

mysql> CREATE TABLEuser5(-> id INT UNSIGNED AUTO_INCREMENT KEY,-> username VARCHAR(20) NOT NULL

-> )ENGINE=INNODB DEFAULT CHARSET=UTF8;

Query OK,0 rows affected (0.01sec)

mysql> DESCuser5;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

2 rows in set (0.01sec)

mysql> ALTER TABLEuser5-> DROP PRIMARY KEY;

ERROR1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a keymysql> ALTER TABLEuser5-> MODIFY id INT UNSIGNED NOT NULL;

Query OK,0 rows affected (0.03sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser5;+----------+------------------+------+-----+---------+-------+

+----------+------------------+------+-----+---------+-------+

| id | int(10) unsigned | NO | PRI | NULL | |

+----------+------------------+------+-----+---------+-------+

2 rows in set (0.01sec)

mysql> ALTER TABLEuser5-> DROP PRIMARY KEY;

Query OK,0 rows affected (0.04sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser5;+----------+------------------+------+-----+---------+-------+

+----------+------------------+------+-----+---------+-------+

| id | int(10) unsigned | NO | | NULL | |

+----------+------------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

唯一索引(UNIQUE KEY)操作:

--测试添加和删除唯一(UNIQUE KEY)

CREATE TABLEuser6(

idINT UNSIGNED AUTO_INCREMENT KEY,

usernameVARCHAR(100) NOT NULL UNIQUE,

passwordCHAR(32) NOT NULL,

emailVARCHAR(50) NOT NULL UNIQUE)ENGINE=INNODB DEFAULT CHARSET=UTF8;--MySQL中默认是唯一索引的名称和字段名称相同--删除唯一索引

ALTER TABLEuser6DROP INDEXusername;--添加唯一索引

ALTER TABLEuser6ADD UNIQUE KEY(username);--如果要指定唯一索引的名称(即不要"默认是唯一索引的名称和字段名称相同")

ALTER TABLEuser6ADD UNIQUE INDEXuni_email(email);--指定唯一索引的名称后, 如果要删除该索引, 就要使用指定后的索引名称

ALTER TABLEuser6DROP INDEXuni_email;------------------------------------------------------------------------------------

mysql> CREATE TABLEuser6(-> 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.02sec)

mysql> DESCuser6;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

4 rows in set (0.01sec)

mysql> ALTER TABLEuser6-> DROP INDEXusername;

Query OK,0 rows affected (0.01sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser6;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

4 rows in set (0.00sec)

mysql> ALTER TABLEuser6-> ADD UNIQUE KEY(username);

Query OK,0 rows affected (0.01sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser6;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

4 rows in set (0.01sec)

mysql> SHOW CREATE TABLEuser6;+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| user6 | CREATE TABLE`user6` (

`id`int(10) unsigned NOT NULLAUTO_INCREMENT,

`username`varchar(100) NOT NULL,

`password`char(32) NOT NULL,

`email`varchar(50) NOT NULL,PRIMARY KEY(`id`),UNIQUE KEY`email` (`email`),--------------------

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

1 row in set (0.00sec)

mysql> ALTER TABLEuser6-> DROP INDEXemail;

Query OK,0 rows affected (0.01sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser6;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

4 rows in set (0.01sec)

mysql> ALTER TABLEuser6-> ADD UNIQUE INDEXuni_email(email);

Query OK,0 rows affected (0.02sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser6;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

4 rows in set (0.01sec)

mysql>mysql>mysql> SHOW CREATE TABLEuser6;+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

| user6 | CREATE TABLE`user6` (

`id`int(10) unsigned NOT NULLAUTO_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`)------------

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

1 row in set (0.00sec)

mysql> ALTER TABLEuser6-> DROP UNIQUE INDEXuni_email;

ERROR1064 (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 2mysql> ALTER TABLEuser6-> DROP INDEXuni_email;

Query OK,0 rows affected (0.01sec)

Records:0 Duplicates: 0 Warnings: 0mysql> DESCuser6;+----------+------------------+------+-----+---------+----------------+

+----------+------------------+------+-----+---------+----------------+

| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

+----------+------------------+------+-----+---------+----------------+

4 rows in set (0.01 sec)

重命名(RENAME):

--修改数据表名称

ALTER TABLEtab_name

RENAME[TO|AS]new_tab_name;--或者:

RENAME TABLE tab_name TOnew_tab_name;--将user6改为user666

ALTER TABLEuser6

RENAMETOuser666;--------------------------------------------------------------------------------------------

mysql> ALTER TABLEuser6-> RENAME TOuser666;

Query OK,0 rows affected (0.01sec)

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.00sec)

mysql> RENAME TABLE user666 TOuser66;

Query OK,0 rows affected (0.01sec)

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)

weixin_39741459

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
一下mysql的语句正确的是_MySQL中常用语句1

SELECT USER()------得到登陆的用户SELECT VERSION()------得到MySQL的版本信息SELECT NOW()------得到当前的日期时间SELECT DATABASE()------得到当前打开的数据库1. 创建数据库CREATE {DATABASE|SCHEMA} db_name;------检测数据库名称是否存在，不存在则创建CREATE DATABASE...
复制链接

扫一扫