SQL基础知识
2019-10-01 本文已影响0人
周一ing
SQL在线测验工具：http://sqlfiddle.com/#!9/a6c585/1
表的修改插入删除
1、修改
修改表名
alter table data rename to data1
修改字段值
update tabele1 set salary=salary*1.1 where id in (select id from emp)
update tabele1 set salary=replace(salary,100,105) where id=1
2、插入删除
INSERT INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');
DELETE FROM Websites
WHERE name='百度' AND country='CN';
#不删除表，删除所有行
DELETE * FROM table_name;
条件查询相关语法
WHERE Grade BETWEEN 70 AND 80
#比较操作符
OR/AND
mysql><：小于 ; >：大于； 不等于 <>或!=,但sqlserver中不识别!=,所以建议用<>
LIKE'[BCDF]_a%' ：下划线表示任意一个字符，%表示一个或多个字符。以B,C,D,F 开始且第三个字符为"a"
LIKE'[^ABCDEF]%a'：^表示不以某个字符开始
IN
NOT IN： 不在
EXIST
NOT EXIST
IS NOT NULL
IS NULL
CASE WHEN
IF
#限定记录数
sql server> 最高 TOP 1
mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
#排序
ORDER BY ASC（从小到大）；DESC（从大到小）
#分组+条件
GROUP BY
HAVING
#连接
SELECT student.Sname AS 有考试成绩的学生的姓名,course.Cname AS 修课名称,SC.Grade AS 考试成绩
INTO [new_sc]
inner join SC ON student.Sno=SC.Sno
UNION
1、exist和in的差别
-
in()只执行一次,它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录.
exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false. -
如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等.
如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.
select * from A
where id in(select id from B)
2、空值处理
IS NOT NULL
IS NULL
IFNULL(expr1 , expr2)
在 expr1 的值不为 NULL的情况下都返回 expr1，否则返回 expr2
COALESCE (expression,value1,value2……,valuen)
如果expression不为空值则返回expression；否则判断value1是否是空值，如果value1不为空值则返
回value1；否则判断value2是否是空值，如果value2不为空值则返回value3；……以此类推，
3、IF和case when
if条件表达式
IF( 条件 , 条件为真值 , 为假值 )
case when的用法
- 通过两个字段的情况得到一个新的字段
#根据奖金类型确定员工奖金如果奖金类型为1，则bonus字段值为工资的90%，为2则为工资的80%，其余是70%
SELECT
salary.id,bonus_type,salary,
case
when bonus_type=1 then 0.9*salary
when bonus_type=2 then 0.8*salary
else 0.7*salary
end 'bonus'
FROM bonus,salary
WHERE bonus.id = salary.id
- 打折：根据销量和类型更新价格字段
UPDATE books
SET price=price*
CASE
WHEN sales>10000 THEN 0.95
WHEN type = 'popular' THEN 0.75
ELSE 0.9
END
日期函数
#mysql用法
#当前日期
now() #2019-08-29T02:20:29Z;
current_date() #2019-08-29;
current_time() #02:23:15
#日期加减
DATE_ADD(OrderDate,INTERVAL 45 DAY) #在OrderDate日期上添加 45 天，也可以是HOUR,WEEK等
DATE_SUB(OrderDate,INTERVAL 5 DAY) #在OrderDate日期上减 5 天，也可以是HOUR,WEEK等
DATEDIFF('2008-11-30','2008-11-29') #返回两个日期之间的天数，这里返回1
#日期提取，sql server是datepart函数
DATE(date) #提取日期部分，如2008-11-11
EXTRACT(YEAR FROM OrderDate) #提取OrderDate的年份，也可以是是HOUR,WEEK等
EXTRACT(YEAR_MONTH FROM '2019-08-29') #返回201908,不支持MONTH_DAY,也不支持三个
#日期格式转换
DATE_FORMAT(NOW(),'%Y-%m-%d') #将当前日期以‘2019-08-29’形式展示，小写y的话年份会变成19，大写M是August，大写D是29th
字符串函数
连接
MySQL: CONCAT()
Oracle: CONCAT(), ||
SQL Server: +
#按id分组后，将每个id对应所有的rev用,连接
SELECT id, group_concat(rev)
FROM `docs` group by id
#按id分组后，rev排序后用_连接
group_concat(rev order by rev desc separator '_' )
截取：substr (str, pos, len)
#比如按某个字段的后两个字母排序
order by substr(name,length(name)-1,2)
