一.单表查询
1.基础查询
1.查询所有列
select * from TableName;
2.查询特定列
select Coumn1,Coumn2 from TableName;
3. 列别名
select Coulumn1 as name1,Coulumn2 as name2 from Tablename;
4. 去重查询
select distinct Coulumn1 from Tablename;
5. 限制返回行数
select * from Tablename limit 10;
6. 分页查询
select * from Tablename limit 10 offset 20;
7. 排序查询
select * from TableName order by Column1 desc,Column2 asc;
2. 数据过滤****
1. 基础过滤
select *from Tablename where Column1 > value1; -- <,>,>=,<=,！=，=--
2. 多条件过滤
select * from Tablename where Column1 > value1 and Column2 > value2;
select * from Tablename where Column1 > value1 or Column2 > value2;
3. 范围查询
select * from Tablename where Column1 between valuel and value2
4. in操作符
select * from Tablename where Column1 in(value1,value2,value3);
5. 模糊查询
select * from Tablename where Column1 like ‘%value’;
select * from Tablename where Column1 like ‘value%’;
select * from Tablename where Column1 like ‘%value%’;
6. null值判断
select * from Tablename where Column1 is null;
7. 排除特定值
select * from Tablename where Column1 != value
3. 聚合查询****
1. 计算总数
select count(*) as cnt
from Tablename
where Column1 = value;
2.分组求和
select coumn1 as col1, sum(column2) as col2
from Tablename
group by column1;
3.分组平均值
select coumn1 as col1, avg(column2) as col2
from Tablename
group by column1;
4.分组最大值
select coumn1 as col1, max(column2) as col2
from Tablename
group by column1;
5.分组最小值
select coumn1 as col1, min(column2) as col2
from Tablename
group by column1;
6.分组筛选(having)
select coumn1 as col1, sum(column2) as col2
from Tablename
group by column1;
`where coumn3 = value
Having sum(column2) >value;
7.多列分组
select coumn1 as col1, coumn2 as col2,sum(column3) as col3
from Tablename
group by column1,column2;
4. 高级窗口函数****
1. ROW_NUMBER生成唯一序号
select column1,column2,
ROW_NUMBER() over(order by column2) as row
from Tablename;
2. RANK与DENSE_RANK排名
select column1,colum2,
RANK() over(order by column2 desc) as rank,
DENSE_RANK()over(order by column2 desc) as dense_rank
from Tablename;
3. 累计百分比计算
select column1,column2,
sum(column2) over(order by column1)/sum(colum2) over()
as cumulative_percent
from Tablename;
4. 移动平均（最近三个窗口）
select column1,column2,
avg(column2) over (order by column1 rows between 2
Preceding and CIRRENT row) as moving_avg
from Tablename;
5. 分组内前N名
select * from(
select column1,column2,column3,
ROW_NUMBER() over (partition by column1 order by
Column2 desc) as rn
from Tablename
)where rn <= 3;
二.多表查询****
1.表连接操作
1.内连接
select t1.column1,t2.column2
from Table1 t1
join Table2 t2
on t1.column3 = t2.column3;
2.左连接
select t1.column1,t2.column2
from Table1 t1
left join Table2 t2
on t1.column3 = t2.column3;
3.右连接
select t1.column1,t2.column2
from Table1 t1
right join Table2 t2
on t1.column3 = t2.column3;
4.全外连接
select t1.column1,t2.column2
from Table1 t1
full outer join Table2 t2
on t1.column3 = t2.column3;
5.自连接
select t1.column as column1,t2.column as column2
from Table1 t1
join Table1 t2
on t1.column1 = t2.column2;
6.交叉连接
`select * from Colors cross join Sizes；
2.子查询****
1.标量子查询
select column1,(select count(*) from TableB where column2
=a.column2) as cnt
`from TableA a;
2.in子查询
select column1
from TableA
`where column2 in(select column2 from Categories where Name=’Electronics’);
3.exists子查询
select column1
from TableA a
where exists (select 1 from TableB where column2 = a.column2);
4.子查询作为派生表
select avg(sum) as avg
from (select sum(column2) as sum from TAHLEA group by column1) as t;
5.多条件子查询
select column1,column2
from TableA
where column2 > (select avg(column2) from TableA);
3.联合查询****
1.去重联合
select column1
from TableA union
select column1 from TableB;
2.不去重联合
select column1
from TableA union all
select column1 from TableB;
三．常用函数****
1 .字符串处理
1.字符串长度
select length(column1) from TableName;
2.字符串截取
select substring(,start,length)from Tablename;
3.字符串替换
select replace(column1,’old_string’,’new_string’)from Tablename
4.字符串拼接
select concat (column1, column2) from TableName;
5.字符串转大写
select upper(column_name) from TableName;
6.字符串转小写
select lower(column_name) from TableName;
2.时间日期函数****
1.当前时间
select curtime();
2.当前日期
select curdate();
3.当前日期和时间
select now();
4.日期向后加天数
select date_add(now(), interval 10 day);
5.日期减天数
select date_sub(now(), interval 10 day));
6.获取两个日期插值
select datediff(date1, date2);
7.获取日期年
select year(date) from TableName;
8.获取月
select month(date) FROM TableName;
9.获取日
select day (date) from TableName;
10.获取小时
select hour(time) from TableName;
11.获取分钟
select minute(time) from TableName;
12.获取秒
select second(time) from TableName;
13.获取第几周
select week(time) from TableName;
14.日期转字符串
select date_fromat (date, '%Y-%m-%d') from TableName;
15.字符串转日期
select cast(column AS date) from TableName;
四.常用操作****
1 .数据操作
1.插入单条数据
insert into TableName (Column1, Column2) values (value1,value2);
2.插入多条数据
insert into TableName (Column1, Column2)
valuse (value1, value2), (value3, value4);
更新数据3.
update TableName set Column1 = value1 where Column2=value2;
删除数据
delete from Orders where OrderDate < '2020-01-01';
5.全表删除
delete from TempData;
6.清空表数据
truncate table logs;
2.表操作
1.创建新表
create table TableName(
column1 int primary key,
column2 varchar (50)
column3 date
);
2 添加新列
alter table TableName add COLUMN column1 int;
修改列类型3
alter table TableName modify COLUMN column1 varchar(20);
4.删除列
alter table TableName drop COLUMN column1;
5.重命名表
alter table TableName rename to NewTableName;
6.删除表
drop table TableName;
3.约束与索引****
1.添加主键约束
alter table TableName add primary key (column1);
2.唯一约束
alter table TableName add unique (column1);
3.外键约束
alter table TableName
add constraint FK_column1
foreign key (column1) references TableB(column2);
4.创建索引
create index idx_column1 on TableName (column1);
5.删除索引
drop index idx_column1 ON TableName;
6.非空约束
alter table TableName modify COLUMN column1 varchar(100) not null;
4 .视图
创建视图1.
create view ViewName as
select column1
from TableName
where condition;
2.更新视图数据
uodate ViewName set column1 = 'value'
where condition;
3.删除视图
drop view if ezists ViewName;
5.事务控制****
1.开启事务
start transaction;
2.提交事务
commit;
3.回滚事务
rollback;
4.保存点
savepoint savepoint1;
5.回滚到保存点
rollback to savepoint1;
6，权限管理****
1.授予查询权限
grant select on TableName to user1;
2.授予所有权限
grant all PRIVILEGES on DatabaseName.* to’admin'@'localhost';
3.撤销权限
revoke delete on TableName from user2;
7.其他操作
1.查询所有数据库
show databases
2.查询所有表
show tables
3.查询表结构
describe TableName
4.查询建表语句
show create table TableName
5.查询表的所有列
select COLUMN_NAME.
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'database_name' and TABLE_NAME='table_name';
6.查询表的所有索引
show index from TableName;
7.查询表大小
select table_name as 'Table',
round((DATA_LENGTH + index_LENGTH) / 1024 / 1024, 2) as 'Size(MB)’
from information_schema.TABLES where table_schema = 'database_name'
8.设置时区--设置亚洲/上海时区
set time_zone = 'Asia/Shanghai';
9.创建数据库
create database database_name;
10.删除数据库
drop database database_name;
