MySQL常用SQL语句整理

这篇文章简单整理一下MySQL数据库中常用SQL语句相关内容。

一.数据定义语言（DDL）

create table tb
(   
    a int       not null,
    b char(10)  default 'abc',
    c date
);
--创建数据表tb，包含列abc，其中列a不能为空值，列b默认为'abc'

create table nt as
(
    select  a  from t
);
--复制表t查询内容为新表nt，标准SQL写法：select * into nt from t;

alter table t 
add a int not null;
--修改表t，增加列a

alter table t
add
(
    a int not null,
    b char(10)  default 'abc',
    c varchar(10)
);
--修改表，增加列abc

alter table t
drop a,drop b;
--修改表t，删除列ab

alter table t
drop column a;
--修改表t，删除列a

alter table t
modify column a int not null;
--修改表t，修改列a属性

drop table t;
--删除表t

rename table t to t1,tt to tt2;
--重命名表t为t1，表tt为tt2

二.数据查询语言（DQL）

书写顺序：select from，where，group by，having，order by

执行顺序：from，where，group by，having，select，order by

select a from table_a;
--从表table_a中查询列a

select table_a.a from table_a;
--从表table_a中查询列a

select a,b,c from table_a;
--从表table_a中查询列a,b,c

select * form table_a;
--从表table_a中查询所有列

select t.a as b from table_a as t;
--从表table_a中查询列a，并且在查询结果中给列a起个别名叫b，以及给表table_a起个别名叫t

select t.a,10 as b,'列' as c,1+2 as d,t.e+t.f as h from table_a as t;
--从表table_a中查询列a，并且在查询结果中插入列名为b的列，列b中每个数据都是数值10，如果不as b，则列名也是10，同样插入列c，并且列c中的每个数据都是文本“列”，同样插入列d，列d每个数据都是表达式1+2的结果，同样插入列h，列h中每个数据都是表table_a中列e和列f中数据+运算之后的结果

select t.a,concat('内容', 1+2,t.b, t.c) as d from t;
--查询表t中的列a，在查询结果中插入列d，列d的内容是'内容'和表达式1+2的结果以及列b和列c的数据拼接得到的结果，如果遇到null，则拼接结果是null

select t.a concat_ws('，',t.b, t.c, t.d) as e from t;
--从表t查询列a，并且插入列e，列e的数据是列bcd的数据中间加上拼接符“，”拼接得到的，如果遇到null，则忽略null

select distinct t.a from t;
--从表t中查询列a，并且数据去重

select * from t order by t.a, t.b desc t.c;
--查询表t中所有列，查询结果优先按列a升序排列，然后按列b降序排列，然后按列c升序排列，即默认升序，也可以添加关键字asc升序排列，添加关键字desc为降序排列

select t.* from t order by 2, 3;
--查询表t中所有列，根据第2和第3列排序

select t.a from t order by t.b;
--查询表t中的列a，根据列b进行排序

select * from t where t.a = 10;
--查询表t中的所有列，展示列a内容是10的行

select a from t where b=10;
--查询表t的列a，展示列b内容是10的行

select * from t where t.a+t.b=10;
--查询表t中所有列，展示表达式t.a+t.b是10的行

select * from t where t.a between 5 and 10;
--查询表t中所有列，展示列a内容在5和10之间的行，包含5和10

select * from t where a is null;
--查询表t中所有列，展示列a内容是null的行

select * from t where a is not null;
--查询表t中所有列，展示列a内容不是null的行

select * from t where t.a like 'a_b';
--模糊匹配，_必须表示一个字符

select * from t where t.a like 'a%b';
--模糊匹配，%可以表示任意个字符，可以是零个

select * from t where t.a like 'a\%%b';
--\表示转义字符，\%表示%就是字符，而不代表模糊匹配
--模糊匹配_和%尽量放在字符的中间或者后面，放在前面影响查询效率

select * from t where t.a=1 and t.b=2 or t.c=3;
--and并且，or或者

select * from t where t.a in ('1','2','3');
--in表示属于

select * from t where not t.a=1;
--not表示否定，可以理解为是补集，只影响后面跟着的最近的表达式

select t.a , sum(t.a) from t where t.a in (1,2) group by t.a;
--根据表t中列a的内容，分组进行求和,展示列t.a是为了方便查看

select t.a sum(t.a) from t where t.a in (1,2) group by t.a having sum(t.a) =10;
--根据表t中列a的数据，分组进行求和，展示结果=10的组

select * from tt 
where tt.b=
(select t.a from t where t.a=10);
--标量子查询，子查询的结果仅为一个常量值

select* from t
where t.a=(
select tt.a from tt
where t.b=tt.b);
--关联子查询，子查询的结果为一列

select * from tt
where tt.a in(select t.a from t);
--普通子查询，子查询的结果为一列

select * from (select * from t);
--普通子查询，将子查询的结果看为主查询的表

select * from t inner join tt
on t.a=tt.a;
--内部联结

select * from t lift/right/full outer join  tt on t.a=tt.a;
--左/右/全外部联结，指定保留的记录

select t.a , count(t.a) from t inner join tt on t.a=tt.a group by t.a;
--联结聚合

select *from t
union
select* from tt
union
select * from ttt;
--组合查询

三.数据操作语言（DML）

1.insert

insert into t
value(1，'a'，2);
--向表t中插入一行记录

insert into t(t.a,t.b,t.c)
value(1,'a',2);
--向表t中插入一行记录，只设置列abc的值

insert into t
select * from t where t.a=10;
--向表中插入查询的结果

2.delete

delete from t;
--删除表t的所有数据

delete from t
where a=10;
--删除在表t中满足过滤条件的所有记录

truncate table t;
--删除表t的所有数据

3.update

update t
set a=1，b='b';
--修改表t中列ab的所有内容

update t
set a=1，b='b'
where c=10;
--修改满足过滤条件的列ab中的内容

四.事务控制语言（TCL）

commit：提交事务

rollback：回滚事务

五.数据控制语言（DCL）

create user 'testuser'@'localhost' IDENTIFIED by 'testpassword';
--创建用户

六.数据类型

date：日期型y-m-d

time：时间型h:i:s

datetime：日期时间型y-m-d h:i:s，日期时间类型可以写入文本或者数值，会自动去调整格式

char：定长字符串，char(10)指十个字符，不足会补空位

varchar：变长字符串，varchar(100)会动态分配

integer：整型，可以指定显示宽度，比如integer(10)

decimal：定点型，decimal(x，y)表示一共x位，小数点后y位，默认x为10，y为0，多出位数四舍五入

float/double/real：浮点型，也可以指定位数，和浮点型类似，多出位数五舍六入

七.函数

1.日期处理函数

参数为日期型

year/mouth/day(date)：获取具体日期段

hour/minute/second(time)：获取具体时间段

mouthname(date)：获取月份名称

now()：获取当前日期时间

curdate()：获取当前日期

curtime()：获取当前时间

date_add(date，interval x(数值) year/mouth/day)：将日期型数据增加

date_sub(date，interval x(数值) year/mouth/day)：将日期型数据减少

2.字符(文本)处理函数

参数为字符(文本)型

char_length(x)：x的字符长度

length(x)：x的字节数

rtrim(x)：去除x中右边空值

ltrim(x)：去除x中左边空值

upper(x)：将x的字符全部转换为大写字符

lower(x)：将x的字符全部转换为小写字符

3.数值处理函数

参数为数值型

abs(x)：x的绝对值

sqrt(x)：x的平方根

exp(x)：e的x次方

round(x，y)：保留x的y位小数四舍五入

pi()：即圆周率

4.聚合函数

select max(t.a) from t;
--获取表t中列a中数据的最大值，null忽略

select min(t.a) from t;
--获取表t中列a中数据的最小值，null忽略

select sum(t.a,t.b,t.c) from t;
--对表t的列abc求和

select avg(t.a,t.b,t.c) from t;
--对表t中列abc求平均值，null不计

select count(*) from t;
select count(1) from t;
--获取表t的行数

select count(t.a) from t;
--获取表t中列a的行数，对null不计数

select count(distinct t.a) from t;
--去重计数

convert(a using gbk)
函数，将列a的内容转换为gbk编码

ifnull(a，'内容')
函数，如果列a内容是null，则认为其内容是“内容”