Links

1. 查询

检索所有行和列

select * from table
​
select * from employee

查询部分列

select col1,col2,col3 from table
​
select fname,mint from employee

使用友好列标题查询(更换查询出来后的表头名称)

select col1 as 列1,col2 as 列2 from table
​
select fname as 名字,mint as 代号 from employee

检索前n行数据

-- SQL Server
select top n * from table
​
select top 3 * from employee
-- MySQL
select * from table limit 0,n
​
select * from employee limit 0,3

检索前%n行数据

-- SQL Server
select top n percent * from table
​
select top 3 percent * from employee

指定检索条件

select * from table where col1=content
​
SELECT * FROM employee WHERE sex='M'
-- 多条件
select * FROM employee WHERE sex= 'M' and salary> 10000
​
select * FROM employee WHERE sex="F" or salary <20000

区间查询

select * from employee where salary BETWEEN 20000 and 30000
​
select * from employee where salary not BETWEEN 20000 and 30000
​
-- 特定的取值区间
SELECT * FROM employee WHERE fname in ('james','sam','Frandin')

筛选1列或多列不重复的数据

select distinct col1,col2,... table
​
SELECT DISTINCT sex FROM employee
​
select distinct age from employee

模糊查询

SELECT * FROM employee WHERE fname LIKE 'J%'
​
SELECT * FROM employee WHERE fname LIKE '%a%'
​
SELECT * FROM employee WHERE fname LIKE '__i%'
-- 正则表达式查询匹配
select * from tablename where colname regexp '正则表达式'

查询null数据

SELECT * FROM employee WHERE salary IS NULL
​
SELECT * FROM employee WHERE salary is NOT NULL

对查询的数据进行排序

SELECT * from employee ORDER BY salary ASC
​
SELECT * from employee ORDER BY salary desc
​
SELECT * from employee ORDER BY salary DESC,bdate ASC

算数表达式

SELECT fname,salary,salary/30 AS daySalary FROM employee

函数

-- count函数(统计插叙数据的数量)
select count(*)as 数量 FROM employee WHERE salary>40000
​
-- max,min,avg函数
SELECT max(salary) as max,min(salary)as min,avg(salary) as average FROM employee
​
-- sum求和函数
select sum(hours) FROM works_on WHERE pno=1

数据分组(group by)

group by 对原始数据进行分组 having 对分组后的数据进行筛选(先有group by 后having)
SELECT sex,count(sex) FROM employee GROUP BY sex HAVING count(sex) >4
​
SELECT salary,COUNT(ssn) FROM employee GROUP BY salary

多表查询

链接查询分类

  • 内连接
    ```sql
    -- 查询每个员工的工作时长
    SELECT employee.fname,employee.address,sum(works_on.hours) FROM works_on JOIN employee ON works_on.essn=employee.ssn GROUP BY fname;
SELECT dname,dnumber,COUNT(dno) FROM department JOIN employee on dno=dnumber GROUP BY dname;
SELECT fname,dname FROM employee,department WHERE dno=dnumber
* 外连接
* 左外连接
```sql
SELECT fname,address,dependent_name FROM employee LEFT JOIN dependent ON ssn=essn;
  • 右外连接
    SELECT fname,address,dependent_name FROM employee right JOIN dependent ON ssn=essn;
  • 全外连接
    SELECT fname,address,dependent_name FROM employee full JOIN dependent ON ssn=essn;
    • 交叉连接
      SELECT * from employee CROSS JOIN department

嵌套查询

In

SELECT * FROM employee WHERE ssn in (SELECT essn FROM dependent);
​
SELECT * FROM employee WHERE ssn not in (SELECT DISTINCT essn FROM dependent);

all

SELECT * FROM employee WHERE sex='M' and salary> all(SELECT salary FROM employee where sex='F')
-- 等价于
SELECT * FROM employee WHERE sex='M' and salary> (SELECT max( salary )FROM employee where sex='F')

exists

SELECT * FROM employee WHERE not EXISTS (SELECT * FROM dependent WHERE ssn=essn )

集合查询

并运算

前提条件:select的列数目相等且类型兼容
  • union 剔除重复记录
  • unionall 保留重复记录
    SELECT fname FROM employee UNION SELECT dependent_name from dependent;

交运算

-- sql server
select fname from user where ses='M' intersect
select fname from user where age>20;

差运算

select fname from user where ses='M' except
select fname from user where age>20;
Last modified 3yr ago