mysql中sql笔记与sql函数

一、sql笔记

1、EXISTS关键字

• 成立则返回true不成立则返回false。如果返回的是true的话，则该行结果保留
• 返回的是false的话，则删除该行，最后将得到的结果返回

与 IN 的区别

• IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况

select * from student s where EXISTS(select stuid from score ss where ss.stuid = s.stuid)
select * from student s where s.stuid in(select stuid from score ss where ss.stuid = s.stuid)

2、mysql如何更新一个表中的某个字段值等于另一个表的某个字段值

update a inner join b
	on a.bid=b.id 
	set a.x=b.x , a.y=b.y ;

3、复制所有的列或者只复制希望的列插入到新表中

SELECT column_name字段名
	INTO newtable表名 [IN externaldb]
	FROM table1表名 
	where 字段=值;

4、SQL语句 怎么把从一个表中查出来数据插入到另一个表中

INSERT INTO 表名（字段名）
	select 字段名 
	from 表

例子: 将查询出的s表中sno，j表中jno，p表中pno插入spj表中

insert into spj（sno，jno，pno）
	select sno，jno，pno
	from s，j，p

5、MySQL 索引

CREATE INDEX indexName索引名 ON table_name表 (column_name,[…])字段 * 字段顺序按最佳左前缀法则
创建、删除、展示索引

CREATE index stateIndex on aqgly_order_detail(state,id,real_total_fee)
show index from aqgly_order_detail
drop index stateIndex on aqgly_order_detail

6、IGNORE关键字(翻译:忽视)

• INSERT IGNORE INTO 与 INSERT INTO 的区别就是

INSERT IGNORE INTO 会忽略数据库中已经存在的数据， 如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。 这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

• 统计重复数据

SELECT COUNT(*) as repetitions, last_name, first_name
    FROM person_tbl
    GROUP BY last_name, first_name
    HAVING repetitions > 1;

• 过滤重复数据
  distinct 或 group by

7、into outfile导出数据
show variables like ‘%secure%’; 查询导出功能设置(自查)

select * from sys_role 
into OUTFILE 'c:/temp/2018-08-21Data.xls'

二、MySql中的函数

1、字符串函数

• CONCAT(s1,s2…sn) - - - 字符串 s1,s2 等多个字符串合并为一个字符串
• LEFT(s,n) - - - 返回字符串 s 的前 n 个字符

SELECT LEFT('runoob',2) -- ru

• RIGHT(s,n) - - - 返回字符串 s 的后 n 个字符
• SUBSTRING(s, start, length) - - - 从字符串 s 的 start 位置截取长度为 length 的子字符串

SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO

2、数字函数

• AVG(expression) - - - 返回一个表达式的平均值，expression 是一个字段
• MOD(x,y) - - - 返回 x 除以 y 以后的余数
• RAND() - - - 返回 0 到 1 的随机数
• ROUND(x，y) - - - 返回数值 x 保留到小数点后 y 位的值

SELECT ROUND(12.3456,2)  --12.35   四舍五入

• TRUNCATE(x,y) - - - 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）

SELECT TRUNCATE(1.23456,3) -- 1.234

3、日期函数

• ADDDATE(d,n) - - - 计算起始日期 d 加上 n 天的日期 默认是天day (年year 月month 时hour 分minute 秒second) INTERVAL(偏移量)

SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); -->2017-06-25
SELECT ADDDATE("2017-06-15", INTERVAL -1 year); -->2016-06-15
SELECT ADDDATE("2011-11-11 11:11:11", INTERVAL 20 MINUTE); -->2011-11-11 11:31:11

• DATEDIFF(d1,d2) - - - 计算日期 d1->d2 之间相隔的天数

SELECT DATEDIFF('2001-01-01','2001-02-02')  -> -32

• 返回当前时间

SELECT CURDATE();  -- yyyy-MM-dd
SELECT CURTIME();  -- HH-mm-ss
SELECT NOW(); -- yyyy-MM-dd HH-mm-ss

• DATE_FORMAT(d,f) - - - 按表达式 f的要求显示日期 d

SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') --> 2011-11-11 11:11:11 AM

• DAY(d)、 MONTH(d) 、year(d) - - - 提取日期中的 日 月 年 值

SELECT year("2017-06-15"); -- 2017
SELECT month("2017-06-15"); -- 6
SELECT day("2017-06-15"); -- 15

• LAST_DAY(d) - - - 返回给给定日期的那一月份的最后一天,必须带dd

SELECT LAST_DAY("2017-06-20"); --> 2017-06-30

• SEC_TO_TIME(s) - - - 将以秒为单位的时间 s 转换为时分秒的格式

SELECT SEC_TO_TIME(1206)  --> 00:20:06

• TIME_TO_SEC(t) - - - 将时间 t 转换为秒 只计算HH:mm-ss部分

SELECT TIME_TO_SEC('18:30:10')  --> 66610 

• DAYNAME(d) - - - 返回日期 d 是星期几，如 Monday,Tuesday
• DAYOFMONTH(d) - - - 计算日期 d 是本月的第几天
• DAYOFWEEK(d) - - - 日期 d 今天是星期几，1 星期日，2 星期一，以此类推
• DAYOFYEAR(d) - - - 计算日期 d 是本年的第几天
• MAKEDATE(year, day-of-year) - - - 计算在year年的第day-of-year天的日期

SELECT DAYNAME('2011-11-11 11:11:11') -->Friday
SELECT DAYOFMONTH('2011-11-11 11:11:11') -->11
SELECT DAYOFWEEK('2011-11-11 11:11:11')  --> 6
SELECT DAYOFYEAR('2011-11-11 11:11:11')  --> 315
SELECT MAKEDATE(2017, 50); --> 2017-02-19

4、其他函数

• case 字段 when … then …else…end

CASE expression
		WHEN condition1 THEN result1
		WHEN condition2 THEN result2
		...
		WHEN conditionN THEN resultN
		ELSE result
	END

CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。

SELECT CASE 
　　	WHEN 1 > 0
　　		THEN '1 > 0'
　　	WHEN 2 > 0
　　		THEN '2 > 0'
　　	ELSE '3 > 0'
　　	END    --> 1 > 0

• IF(expr,v1,v2) - - - 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。

SELECT IF(1 > 0,'正确','错误')    -->正确

• IFNULL(v1,v2) - - - 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。

SELECT IFNULL(null,'Hello Word')  -->Hello Word

• LAST_INSERT_ID() - - - 返回最近生成的 AUTO_INCREMENT 值

本文地址：https://blog.csdn.net/weixin_45503079/article/details/108973554