SQL语句大全[300个关键字]

一、查询操作

数据操作

序列	关键字	注解	实例	备注
1.	select	检索数据	select * from test_name;
2.	insert	添加数据行	insert into test_name(id,name,age,sex) values(1,‘aaa’,12,‘女’); insert into new_table_name select * from table_name;	值方式，查询方式
3.	replace into	替换数据		需求表中有PrimaryKey或者unique索引
4.	insert ignore	已经存在相同的记录，则忽略当前新数据
5.	delete	删除数据行	delete from table_name where age=12;
6.	truncate	删除所有数据行	truncate table_name;
7.	update	更新数据行	update table_name set age=13 where id=1;
8.	distinct	返回唯一不同值	select distinct sex,age from table_name;
9.	where	条件语句	select * from table_name where age=12;
10.	and	与	select * from table_name where age=11 and sex=‘男’;
11.	or	或	select * from table_name where age=11 or sex=‘男’;
12.	order by	排序	select * from table_name order by sex;
13.	group by	组合	select * from table_name group by sex;
14.	having	聚合条件类似where	select sex,avg(age) from table_name group by sex having avg(age)>10;
15.	top	选择开始数据行	select top 10 sex,age from table_name;	sqlserver支持语句
16.	limit	选择数据行	select * from table_name limit 2;	mysql支持语句 (limit 偏移行数,行数)(limit 行数）
17.	like % _	模糊条件、通配符	select * from table_name where name like ‘%a%’;	%一个或多个字符，_一个字符 [!xx]不包含
18.	ln	包含	select * from table_name where name in (‘aaa’);
19.	between	两者之间	select * from table_name where age between 10 and 12;
20.	left join	左连接	select * from table1 left join table2 on table1.id=table2.id	参考下面join
21.	full outer join	全集	select * from table_name left join new_table_name on table_name.id=new_table_name.id
22.	union/union all	表的串接	select * from table_name union select * from new_table_name;	union 不重复 union all 全部
23.	as	别名	select * from table_name as a;
24.	exists	正在否	select * from table_name where exists (select age from new_table_name where table_name.id=id and age>12);
25.	mid	字符提取	select mid(name,1,1) from table_name;	mysql支持
26.	substring	提取字符	select substring(name,1,1) from table_name;
27.	regexp	正则表达式	select * from table_name where name regexp ‘^a’;
28.	over	开窗函数	select *,row_number() over(order by sex) as a from table_name;	msyql8.0以上及sqlserver支持
29.	convert/cast	格式转换	select convert(date,‘20201112’,120)
30.	concat	字符拼接	select concat(‘str1’,‘str2’) as newstr
31.	group_concat	字段拼接	select c1,group_concat(c2) as c from tablename group by c1	字段相同进，另一个字段按‘，’拼接

支持函数

函数	注解	函数	注解	函数	注解
取合函数
avg()	平均值	count()	计数	first()	第一个
last()	最后一个	max()	最大值	min()	最小值
sum()	求和
Scalar函数
ucase()	转大写	lcase()	转小写	len()	长度
round()	四舍五入	ceil()	向上取整	floor()	向下取整
format()	格式化
mysql时间函数
now()	当前日期时间	curdate()	当前日期	curtime()	当前时间
date()	当前日期	extract()		date_add()	后几天
date_sub()	前几天	datediff()	日期间天数	date_format()	自定义格式
sql server 时间函数
getdate()	当前日期时间	datepart()	日期、时间	dateadd()	几天前或后
datediff()	日期间天数
窗口函数
partition by	分区	order by	分组	frame	当前子集
row_number	分配序列号	rank()	排名(1,2,2,4)	dense_rank()	等级序列(1,2,2,3…)
percent_rank()	百分数等级(0,0.1,…1)	cume_dist	累积分布(0.1,0.2…1)
first_value()	第一行值	last_value()	最后一行	lag()	前N行值
nth_value()	框架n行返回参数值	ntile()	窗口指定排名

join 函数（分三部分：左中右）

函数	注释	备注	扩展	备注
left join	左表全部，右表与左表相同部分	左中	where b.id is null	左
right join	左表与右表相同部分，右表全部	右中	where a.id is null	右
inner join	左表与右表相同部分，右表与左表相同部分	中
full outer join	左表全部，右表全部	左中右	where a.id is null or b.id is null	左 右

滑动窗口：

函数	注解	函数	注解	函数	注解
expr preceding	前几行	expr following	后几行
unbounded preceding	分区第一行	unbounded following	分区最后一行
current row	边界是当前行

示例，前一行与后一行记录的平均数 rows between frame_start and frame_end

函数	注解	示例
rows	行对象	select * ,avg(age) over(partition by sex order by id rows between 1 preceding and 1 following ) avg_age from table_name;
range	值对象	select * ,avg(age) over(partition by sex order by create_date range between interval 1 day preceding and interval 1 day following) avg_age from table_name;

sqlserver 与mysql 区别函数

mysql 函数	sqlserver 函数	功能	注释
mid()	substring()	字符串截取
find()	charindex()	查找字符
search()	patindex()	模糊查找
show databases	select Name from master.dbo.sysdatabases	查询数据库
show tables from databaseName;	SELECT Name FROM PLC_DB.dbo.SysObjects Where XType=‘u’ ORDER BY Name --'U’表示用户表，'S’表示系统表,'P’表示过程函数	用户表
select column_name from information_schema.COLUMNS where table_name = ‘TableName’;	SELECT Name FROM SysColumns WHERE id=Object_Id(‘TableName’)	获取表字段
	parsename(‘192.168.1.2’,’.’,1)	字段拆分，示例返回2，返回序列为倒序(n,n-1,…,3,2,1)
SUBSTRING_INDEX（str，delim，count）		按指定字符拆分，count表示第几个指定字符为拆分

更多支持函数参考：函数大全 如：数学函数，统计函数 等

表、字段属属性函数

序列	关键字	注解	实例	备注
1.	primary key	主键
2.	unique	约束唯一性
3.	foreign key	外键	foreign key(new_col) references table_name(id)
4.	check	检查约束	CHECK(salary>0 AND salary<100)
5.	default	默认值	column varchar(20) default ‘xiamen’
6.	not null	非空
7.	auto increment	自增主键

系统函数

序列	关键字	注解	实例	备注
1.	show databases;	查看数据库表
2.	show tables;	查看当前数据库所有表
3.	show create table table_name;	查看构造表函数
4.	show procedure status where db=‘sql’;	查看数据库函数
5.	show create procedure sql.delete_name;	查看存储过程函数
6.	app_name()	当前应用程序名称
7.	coalesce()

表操作

序列	关键字	注解	实例	备注
1.	create table	创建表	create table test_name (id int,name varchar(20),age smallint ,sex char(2));
2.	create table	复制表	create table new_table_name select * from table_name;	mysql支持语句
3.	select into	复制表	select * into new_table_name from table_name;	sqlserver支持语句
4.	drop table	删除表	drop table test_name;
5.	select into	备份表	select * into new_table_name from table_name;
6.	create temporary table	临时表
6.	alter table	修改表结构	alter table test_name add height FLOAT(2); alter table test_name drop age;
7.	create index	创建索引	create [qnique] index id on table_name(id);	可选参数qnique 唯一索引
8.	drop index	删除索引	drop index id on table_name;
9.	create view	创建视图	create view table_name_view as select sex,avg(age) from table_name group by sex;
10.	drop view	删除视图	drop view table_name_view;
11.	create procedure	创建存储过程
12.	drop procedure	删除存储过程
13.	create trigger	创建解发器
14.	drop trigger	删除解发器
15.	create schema	数据库添加新模式
16.	drop schema	删除模式
17.	create domain	创建数据值域
18.	drop domain	删除域值
19.	frace	强索引	create index idx_col on tablename(colname); select * from tablename force index(idx_col) where colname=“aaa”	构建普通索引 使用强索引

二、视图

函数	代码	示例
create view	创建视图	create view table_name_view as select sex,avg(age) from table_name group by sex;
drop view	删除视图	drop view table_name_view;

三、存储过程函数

序列	函数	代码	示例
1.	create procedure	创建存储过程	create procedure p_name() begin select * from table_name; end ;
2.	alter procedure	修改存储过程 特征	alter procedure p_name comment ‘test’;
3.	drop procedure	删除存储过程
	函数
4.	if…then… else… end if	条件
5.	case …when…then…when…then…end case	条件
6.	while… do…end while	条件循环
7.	repeat…until …end repeat	条件循环
8.	loop…end loop	循环
9.	iterate	引用复合语句

存储过程示例

delimiter $$  //定义结束字符

//声明存储过程
create [definer={user | current_user}] --定义函数人员，默认登入用户
	procedure sp_name ( [proc_parameter[,...]]) --定义名称
		[characteristic ...] routine_body
proc_parameter:
	[ in|out|inout] param_name type   --程序参数，输出，输出

characteristic:
	comment 'string'
	| language sql
	| [not] deterministic
	| { contains sql | no sql | reads sql data | modifies sql data }
	| sql security { definer | invoker }

routine_body:
	valid sql routine statement

//存储过程开始与结束
[begin_lable:] BEGIN  --语句的开始，可以给语句赋予标签，增加可读性
	[statement_list]
	...
END [end_lable]

delimiter; --将语句的结束符恢复

输出函数实例：删除给定人员所有信息

create procedure delete_name(in p_name varchar(20))
begin
	delete from table_name where name=p_name;
end

call delete_name('aaa'); --调用函数

输出函数实例：查看平均年龄

create procedure out_avg_age(out p_avg_age integer)
begin
	declare test_val_1 int; --声明变量方式一 局部变量
	set test_val_1=100; 
	set @test_val_2=200; --声明变量方式二 （类似全局变量）

	set p_avg_age=(select avg(age) from table_name);
	set p_avg_age=p_avg_age+test_val_1+@test_val_2;
end;

call out_avg_age(@test);
select @test;

alter procedure 存储过程名 [ 特征 … ]

序列	代码	解释
1.	CONTAINS SQL	表示子程序包含 SQL 语句，但不包含读或写数据的语句。
2.	NO SQL	表示子程序中不包含 SQL 语句。
3.	READS SQL DATA	表示子程序中包含读数据的语句。
4.	MODIFIES SQL DATA	表示子程序中包含写数据的语句。
5.	SQL SECURITY { DEFINER	INVOKER }
6.	DEFINER	表示只有定义者自己才能够执行。
7.	INVOKER	表示调用者可以执行。
8.	COMMENT ‘string’	表示注释信息。

触发器

单执行：

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;

多执行：

	CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
	ON 表名 FOR EACH ROW
	BEGIN
		        执行语句列表
	END;

示例：

-- DELIMITER ||
drop TRIGGER if exists triggername;
create TRIGGER triggernameAFTER insert on tablename
for each ROW
BEGIN
	UPDATE triggertable set col='val'
END; -- ||
-- DELIMITER ;

游标

构造游标 declare cursor_name cresor for select_statement;
打开游标 open cursor_name;
使用游标 fetch cursor_name into var_name[,var_name]…
关闭游标 close cursor_name;

示例

drop procedure if exists p_cursor; --删除函数
create procedure p_cursor(in num int(11),out result varchar(100)) --创建函数
begin
declare p_name varchar(10);
declare p_age int(11);
declare done int default 0;
declare count int(11) default 0;
declare cur_demo cursor for select name,age from table_name; --定义游标
-- declare continue handler for sqlstate '02000' set done=1; --信息状态
open cur_demo; --打开游标
	repeat --循环
	fetch cur_demo into p_name,p_age; -- 提取游标值
		if num=count then
			select concat_ws(',',result,p_name,p_age) into result;
			set done=true;
		else 
			set count=count+1;
		end if;
	until done -- 循环条件
	end repeat; --关闭循环
	close cur_demo; -- 关闭游标
end;

call p_cursor(2,@test);  --调用函数
select @test;

四、事件

五、报表

六、备份

七、系统

1、系统时间
查看系统时间格式：show VARIABLES like ‘%time_zone%’; 默认为UTC