MySQL命令-白红宇

数据库的操作可以分为两大类，表数据变更(DML)和表结构变更(DDL)。表数据变更就是增、删、改、查。表结构变更就是创建数据表，修改表结构等。接下来主要从这两方面来总结MySQL常用的操作语法。MySQL里对语法的关键词不敏感，因此本文中存在大小写混用的情况。

数据定义语言 DDL

定义数据库

创建数据库：create database <数据库名称>

删除数据库：drop database <数据库名称>

列出所有管理的数据库：show databases

切换到指定的数据库：use <数据库名称>

列出某数据库中的所有表：show tables

显示数据库被创建时的语言：show create database <数据库名称>

定义数据表

创建数据表

创建数据表的基本语法结构为， create table table_name (column_name column_type)

CREATE TABLE IF NOT EXISTS `runoob_tbl`(   `runoob_id` INT UNSIGNED AUTO_INCREMENT,   `runoob_title` VARCHAR(100) NOT NULL DEFAULT '',   `runoob_author` VARCHAR(40) NOT NULL DEFAULT '',   `submission_date` DATE,   PRIMARY KEY ( `runoob_id` )，   KEY ID_TITLE (`runoob_title`, `runoob_author`)    )ENGINE=InnoDB CHARSET=utf8;复制代码

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错；

AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1；

PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔；

ENGINE 设置存储引擎；

CHARSET 设置编码, 常用的编码格式有utf8、utf8mb4、gbk；

UNIQUE 用限定建立索引的索引为唯一索引；

删除数据表

drop table <数据表名称>

查询表相关信息

查询建表语言：SHOW CREATE TABLE TABLE_NAME

查询索引信息：show index from <table name>

查询表字段：show columns from <table name>或DESCRIBE tableName

修改表结构

创建一个表t1，然后在其基础上进行表结构的变更，表t1如下：`CREATE TABLE t1 (a INTEGER, b CHAR(10));`# 添加字段c,d和e(ADD)ALTER TABLE t1 ADD c bigint(20) COMMENT 'xxx',ADD d bigint(20) NOT NULL COMMENT 'xxx',ADD e bigint(20) NOT NULL COMMENT 'xxx';# 将表名重命名为t2(RENAME) ALTER TABLE t1 RENAME t2# 删除字段b(DROP)ALTER TABLE t2 DROP COLUMN b;# 修改字段b类型为VARCHAR(MODIFY)ALTER TABLE t2 MODIFY COLUMN b VARCHAR(255) NOT NULL DEFAULT '';# 修改字段名a为ach(CHANGE)ALTER TABLE t2 CHANGE a ach BIGINT(20) NOT NULL;# 添加一个名为key_a的索引ALTER TABLE t2 ADD INDEX key_a(a);# 删除名为key_a的索引ALTER TABLE t2 DROP INDEX key_a复制代码

数据操纵语言 DML

查询数据

SELECT column_name1, column_name2FROM table_name[WHERE Clause] [ORDER BY column_name1 DESC][LIMIT N][ OFFSET M]复制代码

根据过滤条件查询表中的一列或者多列或者全部列的数据, SELECT column_name1,column_name2 FROM table_name WHERE column_name1=xxx；其中过滤条件操作符有：=，<>,!=,<,<=,>,>=,BETWEEN AND,IS NULL；

可以使用星号（*）来代替字段，SELECT语句会返回表的所有字段数据；

LIMIT来限制查询的数量。可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。SELECT * FROM table_name WHERE column_name1 = xxx LIMIT 0 5,表示从第一条开始查询，查5条记录；

DISTINCT为查询出的某一列信息去重：SELECT DISTINCT column_name1 FROM table_name，对column_name1字段去重；

ORDER BY子将取出的数据按照一列或者多列排序：SELECT column_name1,column_name2 FROM table_name ORDER BY column_name1 DESC, cust_name，ASC表示从小到大拍，DESC表示从大到小排，不指定则按默认从小到大排;

IN操作符用来指定多个范围值：SELECT column_name1, column_name2 FROM table_name WHERE column_name1 IN (10, 20,30)。另外，NOT操作符可以和IN操作符配合使用，用于表示检索出不符合条件的所有数据；

LIKE操作符用来进行模糊查询，与通配符配合使用，%表示任何字符出现任何次数；_表示一个字符：SELECT column_name1, column_name2 FROM table_name WHERE column_name1 LIKE '%hello%'；

插入数据

插入语句的语法如下：

INSERT INTO t1 ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN )

如果要插入多组数据，用逗号分隔就行

INSERT INTO t1 ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ), (value11, value22, ...,valueNN)

插入的数据主键或者UNIQUE键冲突时，会执行后面的UPDATE命令，否者执行INSERT命令 INSERT INTO t1 (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

UPDATE修改数据

UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause]

DELETE删除数据

DELETE FROM table_name [WHERE Clause] [LIMIT 20]

如果没有where子句，会删除整个表里的数据，是非常危险的操作；

where子句用来限定删除指定的表；

如果确实要删除全表的数据，可以使用TRUNCATE代替，TRUNCATE也更加高效；

其他

事务控制

开启事务

开启事务有两种方式

begin 常用的开启事务的方式，实际上BEGIN后事务没有立即开启，而是等到第一条语句执行后才开启；

start transaction与begin相同；

start transaction with consistent snapshot则是运行该语句后就立刻开启了事务，不用等到第一条语句的执行；

提交事务

commit

回滚事务

rollback

例如一个转账过程分为三步，从账户a扣款，然后增加账户b的余额，最后插入一条流水。三个过程只有有一个失败即为失败，就应该回滚，不提交就不会生效。

begin;update table1 set a = a - 1;update table2 set a = a + 1;insert into table3 (a, b, c, d) value (1, 2, 3, 4);commit;复制代码