mysql基本sql语句（整理中）

关系型数据库的SQL语句都可以分为4大类：

1. DDL（数据定义语言）
DDL 主要是指如下的四种SQL 语句，以 CREATE、DROP、ALRET开头和 TRUNCATE TABLE 语句。这里主要说一下 TRUNCATE TABLE ，截断表的数据，也就是删除表中的数据，删除这些数据的时候，系统不做日志，因此无法恢复，删除的速度比较快；而DELETE 语句也是删除表中的记录，但它要写日志，删除的数据可以恢复，数据量大的时候删除比较慢。

2. DML和DQL（数据操纵语言）
它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。

3. TCL（事务处理语言）
事物处理语言是指提交、回滚和保留点3句SQL，既是commit、rollback和savepoint。事务是指一系列的连续的不可分割的数据库操作，这些操作要么同时成功，要么同时失败。oracle 的默认事务模型是显式事务模型，即执行完DML后必须手动提交或回滚。

4. DCL（数据控制语言）
是指授予权限和回收权限语句，既是grant、revoke、deny 等语句。

DML和DQL

新增：

INSERT INTO t1 (field1,field2) VALUE (v001,v002);            // 明确只插入一条Value
INSERT INTO t1 (field1,field2) VALUES (v101,v102),(v201,v202),(v301,v302),(v401,v402);  //批量插入
INSERT INTO t2 (field1,field2) SELECT col1,col2 FROM t1 WHERE //不要加values或value，和插入sql混了
#不要写成:INSERT INTO Table2 (field1,field2,...) VALUES (select value1,value2,... from Table1)

ps:

1 如果value(s)左边的没有指定字段列表，则右边需按表默认字段顺序输入全部字段值

删除：

DELETE FROM table_name //清空表
DELETE * FROM table_name //清空表
DELETE FROM table_name WHERE condition//按条件删除行

修改：

UPDATE table_name SET column_name=new_value [, column_name=new_value] WHERE column_name=some_value

查询：

SELECT column_name, column_name FROM table_name;//指定列，没有排除列的写法
SELECT * FROM table_name;//全部列

聚合函数：

//数值类型
avg()
min()
max()
sum()
round(column_name,decimals)

//通用
count()计数

//字符类型
mid(str_column,start,end)截取字符//start从1开始，end可选默认到最后
length(str_column) 长度

//举例
SELECT `category_id`, `name`,LENGTH(`name`) from `votes` GROUP BY `created_by` HAVING mid(`name`,2,8) LIKE '%风格%' ;

条件：

//and和or
SELECT column_name(s)
FROM table_name
WHERE condition
AND|OR condition

//between
SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

//in
SELECT column_name(s)
FROM table_name
WHERE column_name
IN (value1,value2,..)

//like
SELECT column_name(s)
FROM table_name
WHERE column_name
LIKE pattern

//group by （按某字段分组，结果集中该字段唯一）
SELECT column_name1,SUM(column_name2)
FROM table_name
GROUP BY column_name1

//having （必须与group by配合使用）
SELECT column_name1,SUM(column_name2)
FROM table_name
GROUP BY column_name1
HAVING SUM(column_name2) condition value

//order by （结果排序）
SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]

//limit 和 offset
SELECT column_name(s)
FROM table_name
ORDER BY column_name [ASC|DESC]  limit 5 offset 5；
//必须按 order by limit offset的顺序书写，否则报错,offset从0开始

DDL

新建：create

//新建数据库（完整）
CREATE DATABASE IF NOT EXISTS dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

//新建表格（完整）
DROP TABLE IF EXISTS `city_info`;CREATE TABLE `city_info` (
`ci_id`  int(10) NOT NULL COMMENT '城市ID值' ,//类型，是否空，默认值，注释，字符编码和排比规则5种信息，顺序随意
`ci_province`  int(10) NOT NULL COMMENT '省份外键' ,
`ci_city`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '城市名称' ,
PRIMARY KEY (`ci_id`),//主键索引
FOREIGN KEY (`ci_province`) REFERENCES `province_info` (`pr_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,//外键索引
[unique|textfull] INDEX `ci_province` (`ci_province`) USING BTREE //[唯一|全文]普通索引
)
ENGINE=InnoDB //表引擎
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci //表数据的字符编码和排序比较规则
COMMENT='城市表' //表注释
AUTO_INCREMENT=123; //自增起始值

//eg (外键失败，其余成功)

CREATE TABLE `member_update_store` (
`id` int(10) NOT NULL COMMENT '自增id' ,
`memberid` int(10) NOT NULL COMMENT '主表会员id' ,
`nickname` varchar(32) default '' NOT NULL COMMENT '昵称' ,
`thumb` varchar(500) default '' NOT NULL COMMENT '头像' ,
`address` varchar(500) default '' NOT NULL COMMENT '地址' ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
COMMENT='会员信息修改暂存表'
AUTO_INCREMENT=1;

//创建索引，可参考：https://blog.csdn.net/u014518337/article/details/88179607
CREATE [UNIQUE|fulltext] INDEX index_name ON table_name (column_name1[,column_name2...])//普通索引，唯一索引，组合索引，全文索引
CREATE [UNIQUE|fulltext] INDEX index_name ON table_name (column_name1 [(length)] [ASC|DESC])//单列索引可以设置索引顺序，字符索引可以设置索引长度

//创建视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

删除：drop

//删除数据库
DROP DATABASE [IF EXISTS] database_name

//删除表
DROP TABLE table_name

//删除索引（mysql的语法）
ALTER TABLE table_name DROP INDEX index_name

//删除视图
DROP VIEW <视图名1> [ , <视图名2> …]

//删除字段
ALTER TABLE table_name DROP COLUMN column_name

修改：alter

//修改数据库
ALTER DATABASE dbname DEFAULT CHARSET SET utf8 COLLATE utf8_general_ci;

//修改表名
ALTER TABLE table_name rename to new_name;
//修改表全局属性
ALTER TABLE testalter_tbl 
ENGINE=InnoDB //表引擎
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci //表数据的字符编码和排序比较规则
COMMENT='城市表' //表注释
AUTO_INCREMENT=123; //自增起始值

//添加字段
ALTER TABLE table_name 
ADD column_name datatype [default,null,comment,character] [first|after column_name2]
//eg
alter table member add audit_state int(10) default 1 null comment '审核状态'\G;

//1. 添加单个字段
alter table test add c4 varchar(2);

//2. 添加多个字段
alter table test add column sent_blacklist int(11) DEFAULT 0 COMMENT ' 我是注释1', add column sent_notexist int(11) DEFAULT 0 COMMENT '注释2', add column sent_error int(11) DEFAULT 0 COMMENT '注释3';

#对比可以看到，添加多个字段需要"add column 字段名"，而单个字段直接"add 字段名"。
#添加多个字段中间的,不能换行添加（会报错）；补充：新版mysql已经支持换行


//删除字段
ALTER TABLE table_name 
DROP COLUMN column_name

//修改字段 类型,默认值，是否null,注释，字符编码和排比规则(必须设置字段类型才能成功，即使不修改类型也需要加上datatype)
ALTER TABLE table_name
MODIFY column_name datatype [default,null,comment，character]
//eg
alter table member_update_store modify updated_at timestamp default ''\G;

//修改字段名称 
ALTER TABLE table_name CHANGE column_name new_name datatype; #datatype必须加上，否则失败

　　modify和change的区别：https://www.cnblogs.com/liuurick/p/11627109.html

　　modify和change都可以修改表的定义，但是change却需要写两次列名，不是很方便。但是change的优点是可以修改列名称，modify则不能

 

TCL：事务相关

 

DCL：授权相关