MySQL，SQL语句大全（包含例子）

SQL语法

DDL（数据库）

创建数据库：

create database 名称;

eg:创建一个db01数据库

create database db01;

查询所有数据库：

注意查询所有数据库的时候database后面有s

show databases;

查询当前数据库：

查询当前正在使用哪个数据库

select database();

创建数据库：

create database后面可以添加可选项（如果不存在再创建，如果存在不会创建也不会报错）

create database[if not exists]数据库名;

eg:如果没有db01数据库则创建

create database[if not exists] db01;

使用数据库：

use 数据库名;

删除数据库：

drop database后面可以添加可选项（如果存在就删除，如果不存在不会删除也不会报错）

drop database[if exists]数据库名;

eg:如果db01数据库存在则删除db01数据库

drop database[if exists]db01;

[database可以替换成schema]

DDL(表)

创建表：

[comment 表注释] :给这张表添加注释信息这张表是添加什么数据的（备注）

[comment 字段1注释]：描述这个字段是用来干什么的

create table 表名{

字段1 字段类型[约束] [comment 字段1注释],

......

字段n 字段类型[约束] [comment 字段n注释]

}[comment 表注释];

约束：

非空约束 限制该字段不能为null notnull

唯一约束 保证字段的所有数据都是唯一、不重复的 unique

主键约束 主键是一行数据的唯一标识，要求非空且唯一 primary key(auto_increment自增)

默认约束 保存数据时，如果未指定该字段值，则采取默认值 default

外键约束 让两张表的数据建立连接，保证数据的一致性和完整性 foreign key

eg:创建一个含有id（主键），username（非空，唯一），name（非空），age，gender（默认男）的用户表

create table tb_User(
    id int primary key auto_increment comment 'ID,唯一标识，让主键自动向上增长',
    username varchar(20) not null unique comment '用户名,最多只能存储20位，非空且唯一',
    name varchar(10) not null comment '姓名,最多只能存储10位，非空',
    age int comment '年龄',
    gender char(1) default '男' comment '性别,最多只能存储1位,默认值男'
)comment '用户表';

表操作：

查询：

查询当前数据库所有表：show tables;(注意查询所有表时tables后面有s)

查询表结构：desc 表名;

eg:查询ks_emp表结构

desc ks_emp

查询建表语句：show create table 表名;

eg:查询sd_emp建表语句

show create table sd_emp

修改：

添加字段：alter table表名 add 字段名 类型（长度）[comment 注解] [约束];

eg:修改：为表ks_emp 添加字段 qq varchar(11)

alter table ks_emp add qq varchar(11) comment 'QQ';

修改字段类型：alter table表名 modify 字段名 新数据类型（长度）;

eg:修改：修改ks_emp 字段类型 qq varchar(13)

alter table ks_emp modify qq varchar(13) comment 'QQ';

修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型（长度）[comment 注解] [约束];

eg:修改：修改ks_emp 字段名 qq 为 qq_num varchar(13)

alter table ks_emp change qq qq_num varchar(13) comment 'QQ';

删除字段：alter table表名 drop column字段名;

eg:修改：删除ks_emp 的 qq_num 字段

alter table ks_emp drop column qq_num;

修改表名：rename table表名 to新表名

eg:修改：将ks_emp 表明修改为emp

rename table ks_emp to emp;

删除：

删除表:drop table[if exist] 表名;

eg:删除ks_emp 表

drop table[if exist] ks_emp;

DML：

insert：

指定字段添加数据：insert into 表名 （字段名1，字段名2）values（值1，值2）;

eg:为ks_emp 表的username，name，gender字段插入值

insert into ks_emp (username,name,gender,create_time,up_data) values ('Tom','汤姆',1,now(),now());

全部字段添加数据：insert into 表名 values（值1，值2，......）;

eg:为ks_emp 表的所有字段插入值

insert into ks_emp values(null,'jerry','1234','杰瑞',2,'I love you',1,'2001-11-03',now(),now());

批量添加数据（指定字段）：insert into表名（字段名1，字段名2）values（值1，值2）,（值1，值2）;

eg:批量为 ks_emp 表的username，name，gender 字段插入数据

insert into ks_emp (username,name,gender,create_time,up_data) values('zhiruo','周芷若',2,now(),now()),('wuji','张无忌',1,now(),now());

批量添加元素（全部字段）：insert into表名values（值1，值2）,（值1，值2）;

update：

修改数据：update 表名 set 字段名1 = 值1，字段名2 = 值2，.........[where 条件];

eg:将ks_emp 表的Id为2员工 姓名name字段更新为'张三'

update ks_emp set name = '张三' where Id = 2;

delete：

删除数据：delete from 表明[where 条件];

eg:删除ks_emp 表中 ID为2的员工

delete from ks_emp where Id = 2;

eg:删除tb_emp 表中的所有员工

delete from ks_emp;

DQL:

基本查询：（select）

查询多个字段：select 字段1，字段2，字段3 from 表名;

eg:查询指定字段name，entrydate并返回

select name,entrydate from tb_emp;

查询所有字段（通配符）：select * from 表名;

eg:查询返回所有字段

select * from tb_emp;

设置别名：select 字段1 [as 别名 1],字段 2 [as 别名2] from 表名;

eg:查询所有员工的name，entrydate，并起别名（姓名、入职日期）

select name as 姓名, entrydate as 入职日期 from tb_emp;

去除重复记录：select distinct 字段列表 from 表名;

eg:查询已有的员工关联了哪几种职位（不要重复）

select distinct job from tb_emp;

条件查询：（where）

条件查询：select 字段列表 from 表名 where 条件列表;

比较运算符（java没有的）：

between...and... 在某个范围范围之内（含最小、最大值）

in(...) 在in之后的列表中的值，多选一

like 占位符 模糊匹配（_匹配单个字符，%匹配任意个字符）

is null 是null

逻辑运算符：

and 或 && 并且（多个条件同时成立）

or 或 || 或者（多个条件任意一个成立）

not 或 ！ 非，不是

eg:查询 姓名 为 杨逍 的员工

select name from tb_emp where name = '杨逍';

eg:查询 id小于等于5 的员工信息

select * from tb_emp where id <= 5;

eg:查询 没有分配职务 的员工信息

select * from tb_emp where job is null;

eg:查询 有职位 的员工信息

select * from tb_emp where job is not null;

eg:查询 密码不等于'123456'的员工信息

select * from tb_emp where password != '123456';

或者select * from tb_emp where password <> '123456';

eg:查询 入职日期 在 '2000-01-01'(包含)到'2010-01-01'之间的员工信息

select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01';

eg:查询 入职时间 在 '2000-01-01'(包含)到'2010-01-01'之间 且 性别为女的员工信息

select * from tb_emp where entrydate between '2000-01-01' and '2010-01-01' && gender = 2;

eg:查询 职位是 2，3，4的员工信息

select * from tb_emp where job = 2 || job = 3 || job = 4;

或者select * from tb_emp where job in (2,3,4);

eg:查询 姓名 为两个字的员工信息

select * from tb_emp where name like '__';

eg:查询 姓'张' 的员工信息

select * from tb_emp where name like '张%';

分组查询：（group by）

聚合函数：

select 聚合函数（字段列表） from 表名;

聚合函数：不对null值进行运算,count推荐使用count(*)

count 统计数量

max 最大值

min 最小值

avg 平均值

sum 求和

eg:统计该企业员工数量

count(字段)

select count(id) from tb_emp;

count(常量)

select count(0) from tb_emp;

count(*)

select count(*) from tb_emp;

eg:统计该企业最早入职的员工

select min(entrydate) from tb_emp;

eg:统计该企业最迟入职的员工

select max(entrydate) from tb_emp;

eg:统计该企业员工 ID 的平均值

select avg(id) from tb_emp;

eg:统计该企业员工的 ID 之和

select sum(id) from tb_emp;

分组查询：

select 字段列表 from 表名[where 条件] group by 分组字段名 [having 分组后过滤条件];

eg:根据性别分组，统计男性和女性员工的数量

select gender,count(*) from tb_emp group by gender;

eg:先查询入职时间在'2015-01-01'（包含）以前的员工，并对结果根据职位分组，获取员工数量大于等于2的职位

select job,count() from tb_emp where entrydate <= '2015-01-01' group by job having count() >= 2;

排序查询：（order by）

排序查询：select 字段列表 from 表名[where 条件列表] [group by分组字段]order by 字段1 排序方式1，字段2 排序方式2....;

ASC:升序（默认值）

DESC:降序

eg:根据入职时间，对员工进行升序排序 -asc

select * from tb_emp order by entrydate;

eg:根据入职时间，对员工进行降序排序

select * from tb_emp order by entrydate DESC;

eg:根据 入职时间 对公司的员工进行 升序排序，入职时间相同，再按照 更新时间 进行降序排序

select * from tb_emp order by entrydate ,update_time DESC;

分页查询：（limit）

分页查询:select 字段列表 from 表名 limit 起始索引，查询记录数;

eg:从 起始索引0 开始查询员工数据，每页展示5条数据

select * from tb_emp limit 0,5;

或select * from tb_emp limit 5;

eg:查询 第一页 员工数据，每页展示5条记录

select * from tb_emp limit 0,5;

eg:查询 第二页 员工数据，每页展示5条记录

select * from tb_emp limit 5,5;

起始索引 = （页码 - 1）* 每页展示记录数

外键：

物理外键语法：

创建表时指定：

create table 表名(

字段名 数据类型,

....

[constraint] [外键名称] foreign key(外键字段名) references 主表（字段名）

);

建完表后，添加外键：

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表（字段名）

多表查询：

select 字段名 from 表名,表名;

连接查询：

内连接：

相当于查询A、B交集部分数据

隐式内连接：

select 字段列表 from 表1,表2 where 条件......;

eg:查询员工的姓名，及所属的部门名称（隐式内连接实例）

select e.name, d.name from tb_emp e,tb_dept d where e.dept_id = d.id;

显式内连接：

select 字段列表 from 表1[inner] join 表2 on 连接条件......;

eg:查询员工的姓名，及所属的部门名称（显示内连接实例）

select e.name, d.name from tb_emp e inner join tb_dept d on e.dept_id = d.id;

外连接：

左外连接：

查询左表所有数据（包括两张表交集部分数据）

select 字段列表 from 表1 left[outer] join 表2 on 连接条件...;

eg:查询员工表 所有 员工的姓名，和相对应的部门名称（左外连接）

select e.name, d.name from tb_emp e left join tb_dept d on e.dept_id = d.id;

右外连接：

查询右表所有数据（包括两张表交集部分数据）

select 字段列表 from 表1 right[outer] join 表2 on 连接条件...;

eg:-- 查询部门表 所有 部门的名称，和对应的员工名称（右外连接）

select d.name,e.name from tb_emp e right join tb_dept d on e.dept_id = d.id;

子查询：

select * from t1 where column1 = (select column1 from t2.....);

标量子查询：

子查询返回的结果是单个值（数字、字符串、日期等）,最简单的形式

eg:查询"教研部"的所有员工信息

select * from tb_emp where dept_id = (select id from tb_dept where tb_dept.name = '教研部');

eg:查询在"方东白"入职之后的员工信息

select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');

列子查询：

子查询返回的结果是一列（可以是多行）

eg:查询"教研部"和"咨询部"的所有员工信息

select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' || name = '咨询部');

行子查询：

子查询返回的结果是一行（可以是多列）

eg:查询与"韦一笑"的入职日期 及 职位都相同的员工信息;

select * from tb_emp where (job,entrydate) = (select job,entrydate from tb_emp where name = '韦一笑');

表子查询：

子查询返回的结果是多行多列，常作为临时表

eg:查询入职日期是"2006-01-01" 之后的员工信息，及其部门名称

select e.*,d.name from (select * from tb_emp where entrydate > '2006-01-01') e,tb_dept d where e.dept_id =d.id;

事务

开启事务：start transaction; /begin;

提交事务：commit;

回滚事务：rollback;

索引

创建索引：create[unique] index 索引名 on 表名(字段名);

查看索引:show index from表名;

删除索引:drop index 索引名 on 表名;