[使用速查]mysql常用SQL语句汇总

基础使用

库操作

建库

CREATE DATABASE 数据库名;

删库

DROP DATABASE 数据库名;

选择数据库

show databases;  //展示数据库列表
use 数据库名;  //选择数据库

表操作

建表

1.不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
2.AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
3.PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
4.ENGINE 设置存储引擎，CHARSET 设置编码

CREATE TABLE IF NOT EXISTS `test_tab`(
   `test_id` INT UNSIGNED AUTO_INCREMENT,
   `test_title` VARCHAR(100) NOT NULL,
   `test_author` VARCHAR(40) NOT NULL,
   `create_date` DATE,
   PRIMARY KEY ( `rtest_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

更新表

ALTER TABLE testalter_tbl DROP i;  //删除字段
ALTER TABLE testalter_tbl ADD i INT;  //新增字段
ALTER TABLE testalter_tbl MODIFY c CHAR(10);  //修改子段类型
ALTER TABLE testalter_tbl CHANGE i j BIGINT;  //修改字段类型与字段名
ALTER TABLE testalter_tbl RENAME TO alter_tbl;  //修改表名
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;  //增加字段默认值
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;  //删除字段默认值

删表

DROP TABLE test_tab;

查询表结构

desc tb1; --查看tb1的表结构
show COLUMNS from tb1; --查看tb1的表结构
show create table tb1; --查看tb1的建表语句

查询table列表

show tables; 

数据操作

插入数据

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

删除数据

DELETE FROM table_name [WHERE Clause]

更新数据

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

查询数据

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N] [OFFSET M]

example:
where cause1 = 1 and cause2 in (1,2) or cause3 like "%a%"

进阶操作

数据操作

联表查询

select  column1, column2 from table1
UNION  
select  column1, column2 from table2; 

UNION 内部的 SELECT 语句必须拥有相同数量的列。
列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

排序

SELECT * from runoob_tbl ORDER BY submission_date ASC;  //升序排列
SELECT * from runoob_tbl ORDER BY submission_date DESC;  //降序排列

分组

//在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
SELECT name, COUNT(*) FROM employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小丽 |        1 |
| 小明 |        3 |
| 小王 |        2 |
+--------+----------+

//WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）
SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽 |            2 |
| 小明 |            7 |
| 小王 |            7 |
| NULL   |           16 |
+--------+--------------+

//我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法
SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数') | singin_count |
+--------------------------+--------------+
| 小丽                   |            2 |
| 小明                   |            7 |
| 小王                   |            7 |
| 总数                   |           16 |
+--------------------------+--------------+

联表

联表-内联

select * from T1 inner join T2 on T1.userid=T2.userid; //内联

联表-左联

select * from T1 left outer join T2 on T1.userid=T2.userid; //内联

联表-右联

select * from T1 right outer join T2 on T1.userid=T2.userid; //内联

联表-全联

select * from T1 full outer join T2 on T1.userid=T2.userid; //内联

添加索引

//ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

查看索引

show index from tblname;