select col1,col2,col3 fromtableselect fname,mint from employee
使用友好列标题查询(更换查询出来后的表头名称)
select col1 as 列1,col2 as 列2 fromtableselect fname as 名字,mint as 代号 from employee
检索前n行数据
-- SQL Serverselecttop n *fromtableselecttop3*from employee-- MySQLselect*fromtablelimit0,nselect*from employee limit0,3
检索前%n行数据
-- SQL Serverselecttop n percent *fromtableselecttop3 percent *from employee
指定检索条件
select*fromtablewhere col1=contentSELECT*FROM employee WHERE sex='M'-- 多条件select*FROM employee WHERE sex='M'and salary>10000select*FROM employee WHERE sex="F"or salary <20000
区间查询
select*from employee where salary BETWEEN20000and30000select*from employee where salary notBETWEEN20000and30000-- 特定的取值区间SELECT*FROM employee WHERE fname in ('james','sam','Frandin')
筛选1列或多列不重复的数据
select distinct col1,col2,... tableSELECT DISTINCT sex FROM employeeselect 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 ISNULLSELECT*FROM employee WHERE salary is NOT NULL
对查询的数据进行排序
SELECT*from employee ORDER BY salary ASCSELECT*from employee ORDER BY salary descSELECT*from employee ORDER BY salary DESC,bdate ASC
算数表达式
SELECT fname,salary,salary/30AS daySalary FROM employee
函数
-- count函数(统计插叙数据的数量)selectcount(*)as 数量 FROM employee WHERE salary>40000-- max,min,avg函数SELECTmax(salary) as max,min(salary)as min,avg(salary) as average FROM employee-- sum求和函数selectsum(hours) FROM works_on WHERE pno=1
数据分组(group by)
group by 对原始数据进行分组 having 对分组后的数据进行筛选(先有group by 后having)
SELECT sex,count(sex) FROM employee GROUP BY sex HAVINGcount(sex) >4SELECT 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 JOINdependentON ssn=essn;
全外连接
SELECT fname,address,dependent_name FROM employee full JOINdependentON ssn=essn;
交叉连接
SELECT*from employee CROSS JOIN department
嵌套查询
In
SELECT*FROM employee WHERE ssn in (SELECT essn FROMdependent);SELECT*FROM employee WHERE ssn notin (SELECT DISTINCT essn FROMdependent);
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> (SELECTmax( salary )FROM employee where sex='F')