SQL脚本集锦
sql复制表结构
create table newTableName like sourceTableName
sql复制表结构和数据
create table newTableName select * from sourceTableName
如果只需要复制部分字段和数据，把*改成需要复制的字段即可。比如我只需要复制id和username：create table newTableName select id,username from sourceTableName
sql删除表
drop table tableName
修改表名称
alter table oldTableName rename newTableName
删除表的某列
alter table tableName drop fieldName
添加表的列
alter table tableName add fieldName VARCHAR(36)
插入数据
INSERT INTO tableName (column1,column2,column3,...) VALUES (value1,value2,value3,...);
更新数据
UPDATE tableName SET column1=value1,column2=value2,... WHERE some_column=some_value;
删除数据
DELETE FROM tableName WHERE some_column =some_value;
查询去除重复的数据
select distinct fieldName from tableName
查询前10行的数据
select * from tableName LIMIT 10
查询第2行到第10行的数据
select * from tableName LIMIT 1,10
内连接查询：将多个表中符合连接条件的数据查询出来
select * from tableA [inner] join tableB on 连接条件
左外连接查询：以左表为基础，过滤另外一张表(左表所有信息都会显示，右表中匹配的信息会显示，不匹配的使用空来填充)
select * from tableA left join tableB on 连接条件
交叉连接查询
select * from tableA,tableB where 关联条件
结果集：将查询出来的一个结果集作为一个表继续进行查询
select * from (select * from tableA) 别名, tableB where 关联条件
合并查询：将多个查询结果合并为一个结果集
select fieldName from tableA WHERE 条件 union select fieldName from tableB WHERE 条件
函数
求和 ——> sum()
平均值 ——> avg()
统计行数 ——> count()
最大值 ——> max()
最小值 ——> min()
获取当前日期和时分秒 ——> now()
获取当前日期 ——> curdate()
获取当前时分秒 ——> curtime()
计算2个时间相差的天数 ——> datediff(d1,d2)
在d日期的基础上增加n天 ——> adddate(d,n)
在d日期的基础上减少n天 ——> subdate(d,n)
在d日期时间的基础上增加/减少n单位 ——> date_add(d,interval n type)
type表示单位类型
|
type |
说明 |
|
microsecond |
微秒 |
|
second |
秒 |
|
minute |
分钟 |
|
hour |
小时 |
|
day |
天 |
|
week |
星期 |
|
month |
月 |
|
quarter |
季度 |
|
year |
年 |
|
second_microsecond |
秒和微秒 |
|
minute_microsecond |
分钟和微秒 |
|
minute_second |
分钟和秒 |
|
hour_microsecond |
小时和微秒 |
|
hour_second |
小时和秒 |
|
hour_minute |
小时和分钟 |
|
day_microsecond |
日期和微秒 |
|
day_second |
日期和秒 |
|
day_minute |
日期和分钟 |
|
day_hour |
日期和小时 |
|
year_month |
年和月 |
举例1：select date_add(NOW(),interval - 2 DAY) 这是查询两天前的日期
举例2：select date_add(NOW(),interval + 2 MONTH) 这是查询两个月后的日期
获取日期或时间中某个单位的整数
年份 ——> year(d)
月份 ——> month(d)
日 ——> dayofmonth(d)
小时 ——> hour(d)
分钟 ——> minute(d)
秒 ——> second(d)
一周中的第几天（周一为0） ——> weekday(d)
一年中的第几周 ——> week(d)
一年中的第几天 ——> dayofyear(d)
举例：select year('2020-8-26 7:30:00') -- 输出2020
四舍五入后的整数 ——> round(x)
四舍五入后的小数，y表示小数位数 ——> round(x,y)
保留y位小数的值，只舍不入 ——> truncate(x,y)
返回大于等于x的最小整数(向上取整) ——> ceil(x)
与ceil相反，返回小于等于x的最大整数（向下取整） ——> floor(x)
返回0-1之间的随机数，包括0不包括1 ——> rand()
将多个字符串拼接 ——> concat(s1,s2,…)
将多个字符串使用指定字符进行拼接 ——> concat_ws('指定字符',s1,s2,…)
截取x字符串的前面n个字符 ——> left(x,n)
截取x字符串的后面n个字符 ——> right(x,n)
对x字符串进行截取，从start位置开始截取length个 ——> substring(x,start,length)
转化为大写 ——> upper(s)
转化为小写 ——> lower(s)
在s1的字符串中查找s2字符串并替换成s3字符串 ——> replace(s1,s2,s3)
获取字符的长度 ——> char_length(s)
获取字符的字节数(注意：一个汉字占3个字节) ——> length(s)
将字符串s转化为md5编码 ——> md5(s)
变量
局部变量：使用declare定义，只能在函数、存储过程、触发器中使用，局部变量不需要@前缀
一个变量语法：declare 变量名 数据类型 default 默认值;
多个变量语法：declare nnum1,nnum2 int default 0;
系统变量：由系统定义，以@@开头，可以直接使用。
常用的系统变量
@@version：获取MySQL版本
@@last_insert_id：上一次添加记录的自增列的值(注意：@@identity也可以达到同样的效果)
会话变量：在客户端的本次连接过程中可以使用，当客户端断开后，无法再使用，以@开头。
创建变量并赋值：set @变量名=值;
一次性创建多个变量并赋值：set @num1=2,@num2=3;
自定义函数
|
语法： create function 函数名(参数名 数据类型,参数名 数据类型…) returns 返回类型 begin -- 代码 return 返回值; end; 定义函数注意事项： 1) 必须有返回类型 2) 函数名建议使用fn_开头命名 3) 函数中不能产生查询结果集 调用函数：可以在增删改查语句中使用函数，参数可以是列名 删除函数： drop function 函数名 |
IF语句
|
if 条件表达式1 then 代码块1; elseif 条件语句2 then 代码块2; else 代码块3; end if; |
CASE语句
|
case when 表达式 then 值 when 表达式 then 值 … else 值 end; |
WHILE语句
|
while 条件表达式 do 循环体; end while;
leave语句：跳出循环，类似java中的break iterate语句：跳过本次循环，类似java中的continue 标记：在while循环开始和结束定义，标记名称自己设置，在leave和iterate中使用标记。 |
视图
|
解释：视图中保存的仅仅是一条select语句，视图中的数据都来自于数据库表，数据库表称为基本表或者基表，视图称为虚拟表，视图不能存储数据。
创建视图语法： create view 视图名 as select 语句
删除视图语法： drop view 视图名
修改视图： alter view 视图名 as select语句 |
索引
创建索引语法：create index 索引名 on 表名(列名)
删除索引语法：drop index 索引名 on 表名
聚集索引和非聚集索引(普通索引)的区别
|
聚集索引 |
非聚集索引 |
|
表中的主键会自动创建聚集索引，数据在索引页中的顺序和在数据表中的顺序一致，每个表最多只能有1个聚集索引。 |
数据在索引页中的顺序和在数据表中的顺序不一致，每个表可以有多个非聚集索引。 |
存储过程
无参数的存储过程语法
|
create procedure 存储过程名() begin 存储过程语句; end; call 存储过程名称(); -- 调用存储过程 |
带输入参数的存储过程
|
create procedure 存储过程名(in 参数名 数据类型,in 参数名 数据类型,…) begin 存储过程语句; end; call 存储过程名称(值,值,…); -- 调用存储过程 |
带输出参数的存储过程
|
create procedure 存储过程名(in 参数名 数据类型,out 参数名 数据类型,…) begin 存储过程语句; end; set @value=值; call 存储过程名称(值,@value,…); -- 调用存储过程 select @value; |
删除存储过程
|
drop procedure 存储过程名称 |
事务
解释：事务(TRANSACTION)是作为单个逻辑工作单元执行的一系列操作，多个操作作为一个整体向系统提交，要么都执行、要么都不执行。事务是一个不可分割的工作逻辑单元。
事务的四个属性
|
原子性（Atomicity） |
事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。 |
|
一致性（Consistency） |
当事务完成时，数据必须处于一致状态。 |
|
隔离性（Isolation） |
并发事务之间彼此隔离、独立，它不以任何方式依赖或影响其他事务。 |
|
持久性（Durability |
事务完成后，事务对数据库的所有更新将被永久保存到数据库。 |
管理事务的sql语句
开始事务：start transaction;
提交事务：commit;
回滚事务：rollback;
触发器
解释：在增删改操作过程中触发并执行相应行为，在触发器中可以进行数据验证，也可以进行其他操作。
作用：实现检查约束、实现关联操作
种类：insert、update、delete
表new和old
触发器都是操作的这两张临时表；
insert触发器添加的数据在new中；
delete触发器需要删除的数据在old中；
update触发器修改前的数据在old中，修改后的数据在new中；
一个表每种类型的触发器只能有一个；
使用时new.field或old.field即可；
语法
|
CREATE TRIGGER myTrigger BEFORE INSERT ON tableName FOR EACH ROW BEGIN 触发器代码 END; |
游标
解释：游标用于从select语句的结果集中每次提取一条记录。
声明游标：declare 游标名 cursor for select语句
打开游标：open 游标名
从游标中获取数据：fetch 游标名 into 变量名1，变量名2…
关闭游标：close 游标名