sql学习记录（一）

好像好久没更新了，哎…….学业压人啊（绝对不是因为hades太好玩了）

本系列主要用于记录我在sql方面的学习，主要是想到哪，写到哪

sql学习网站推荐

在开始之前，我要先介绍一下我学习sql使用到的一些网站。

sqlzoo：强烈推荐的一个可以一边练习一边学习的sql学习网站，建议对基础语法有一定了解后来使用

w3school：十分著名的w3school，用于了解sql基础语法

易百教程：跟w3school差不多，但个人觉得讲的更为详细一点

基础查询子句

select…from..

查询语法的基础，select后加要查询的东西，from后加表名。（注意sql语法是不区分大小写的，写sql脚本的时候语句后加分号，一般查询不用加）

SELECT gdp/population FROM world x; # 将world表定义别名为x，从x中选出population
SELECT * FROM world x; #星号代表全部属性都取出

注意一个select返回的是数据对应的行

而且select后面是可以接数学表达式或者一些sql函数的

where

where子句同样是查询的重中之重，用于定义查询数据的条件，一般在where中的判断条件包括三种

SELECT population FROM world
  WHERE name <> 'France'; #用运算符，<>意思是不等于，也可用！=
SELECT name, population FROM world
  WHERE name IN ('Brazil', 'Russia', 'India', 'China');# 用in来定义name所能取的有限值
SELECT name, area FROM world
  WHERE area BETWEEN 250000 AND 300000;
  #between...and...确定范围，但between在不同数据库对于是否能取到边界值不同，所以我一般直接用大于和小于

通配符

一般用于where子句中字符串的匹配，类似正则表达式，匹配时使用like

• %表示匹配任意长度的任意字符
• _表示匹配一个任意字符
• [charlist]匹配字符列中的任何单一字符
• [^charlist]或[!charlist]匹配不在字符列中的任何单一字符

select name from world where name like '%United%';#找包含United的
SELECT * FROM Persons WHERE FirstName LIKE '_eorge';
SELECT * FROM Persons WHERE City LIKE '[ALN]%';
SELECT * FROM Persons WHERE City LIKE '[!ALN]%'#找不以A，L，N开头的

and,or,not

这三个主要是用于where子句中的条件判断，即与或非

select name, population, area 
from world
where (area>3000000 or population>250000000) 
and not (area>3000000 and population>250000000)
#选area>3000000或population>250000000但是不要两个都满足
#注意括号的优先级是最高的
SELECT name
   FROM world
WHERE name LIKE 'B%'
  AND name NOT LIKE '%a%' #可以not like和not in

order by排序

用于对结果进行排序，默认是ASC升序，降序用desc

select winner, yr,subject 
from nobel
where winner like 'Sir%'
order by yr desc,winner asc
#下面要注意的是排序子句中要注意先后顺序，数据先按照前面的排列再在不违反前面的基础上执行后面的
SELECT winner, subject
  FROM nobel
 WHERE yr=1984
 ORDER BY subject IN ('Physics','Chemistry'),subject,winner

distinct

用于排除重复值

select distinct continent
from world

sql函数

round取整

select name,round(population/1000000,2),round(gdp/1000000000,2) from world
where continent='South America';#保留两位小数
select name,round(gdp/population,-3) from world where gdp>=1000000000000#保留到千分位，即百位十位个位都用0替代

length取长度

SELECT name,capital 
  FROM world
 WHERE LENGTH(name)=LENGTH(capital)

left

取出字符串前n位

SELECT name, capital
FROM world
where LEFT(name,1)=left(capital,1) and name!=capital#取首字母相同的

concat

用于多个字符串连接，若其中有一个是null，则结果为null

select name,concat(round(100*population/(select population from world where name='Germany')),'%')
from world 
where continent='Europe' and population>0

附：concat_ws(separator, str1, str2, …)，功能同上，只是可以指定分隔符

avg,sum,count

如字面意思，而且都支持distinct选项，但注意avg和sum会忽略null值，而count(*)或count(1)不会（count(某一属性)会）。

但是若avg，sum的对象没有记录，则会返回null值，此时建议使用ISNULL(sum(对象),0)将null替换为0

SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)#找到OrderPrice高于平均值的顾客
SELECT SUM(distinct salary) FROM employees WHERE department_id = 5;
SELECT COUNT(*) FROM employees;where department_id=1#代表获取该表满足条件的行数

事实上这几个函数在分组中很有作用，但由于篇幅原因，下次一定

max,min

如字面意思

SELECT MAX(column_name) FROM table_name #既支持数值也支持文本,自动忽略null值，不支持distinct
SELECT MIN(column_name) FROM table_name#同上

嵌套查询

select出的结果可以作为一个值用于另一个查询的where子句中来作为比较条件

#作为值
SELECT name FROM world WHERE continent = (SELECT continent FROM world WHERE name = 'Brazil')
#作为列表
SELECT name, continent FROM world WHERE continent IN (SELECT continent FROM world WHERE name='Brazil' OR name='Mexico')
#还可以放在select后运算
SELECT population/(SELECT population FROM world WHERE name='United Kingdom') FROM world WHERE name = 'China'

ALL运算符

将单个值和子查询中所有值进行比较

SELECT name FROM world WHERE population > ALL(SELECT population FROM world WHERE continent='Europe')

这也可以作为寻找某个范围内最大值的方法之一

SELECT continent, name, area FROM world x  WHERE area >= ALL(SELECT area FROM world y        
WHERE y.continent=x.continent AND area>0)#代表寻找每个洲面积最大的国家，这种别名的方法很常用
#下面这个是找全部国家人口都少于25000000的大洲
select name, continent, population from world x where 25000000>=ALL(select population from world y where y.continent=x.continent)
#下面这个是找比同一大洲其他国家人口多三倍以上的国家
select name,continent from world x where population>all(select 3*population from world y where y.continent=x.continent and y.name<>x.name)

any/some

all是子查询出的所有值，any则是其中任意一组值，也就是比较运算中，只要等式另一边比子查询任意一组值满足条件即可

SELECT first_name, last_name, salary FROM employees WHERE salary = ANY (SELECT AVG(salary) FROM employees) 
#找出所有工资等于平均工资的员工

NULL(这位更是重量级)

null在其他编程语言中往往是作为空值，但是在sql中，null的意思是未知值。在一些字段未知时，我们可以给其赋予null。

• null不能使用比较运算符，如>,<,=等，否则将返回null，因为是未知，所以不会有数据返回

• 当我们想要查询哪些行的某个属性是否为空值时，应使用is null或is not null

  SELECT * FROM SOME_TABLEWHERE SOME_COLUMN IS NULL

false与null

麻烦来了，看好false和null的区别：

select * from world where not(1=0);#因为1=0为false，则not(1=0)为true，返回所有行
select * from world where not(1=null);
#1=null可能为true或者false，因为null是未知，则1=null为null，not(1=null)也是null，则不返回任何值

null与not in

SELECT * FROM SOME_TABLE WHERE 1 IN (1, 2, 3, 4, NULL)#显然正确
SELECT * FROM SOME_TABLE WHERE 5 NOT IN (1, 2, 3, 4, NULL)
#牛逼的地方在于null是未知，所以null有可能是5，则这个where后返回null，最后不返回任何值

null与null

select 1 from dual where null=null
select 1 from dual where null<>null
#上面的都不会返回任何值，用未知的思想就是null可能等于null，所以都返回未知

因此若表中可能存在null时，比较运算符不能随便乱用，最好加入一些限制条件或者使用isnull函数

SELECT name FROM world WHERE population > ALL(SELECT population FROM world WHERE continent='Europe' and population>0)
#population>0是避免一些人口记录为null的国家
SELECT AVG(ISNULL(Weight, 50)) FROM Production.Product;  #遇到null就将其替换为50

注意isnull函数是微软数据库特有的，相同作用的函数在oracle中叫NVL，在mysql中为IFNULL或COALESCE

NULL这一部分参考了这篇文章：SQL查询中的NULL值

一些小tips

• 字符串中想打出单引号时需打两个单引号