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;

SQL

2. 插入

Last modified 3yr ago