sql常用语句

SQL语句

DDL:数据定义语言

1.给stu表添加列classname列

ALTER table stu add(classname varchar(100))

2.修改列的数据类型 修改stu表的gender列类型为CHAR(2)

Alter table stu modify gender char(2)

3.修改列名 修改stu表的gender列名为sex

ALTER table stu change gender sex CHAR(2)

4.删除列 删除stu表中的classname列

ALTER table stu DROP classname

5.修改表名称 修改stu表为student表

ALTER table stu rename to student

6.查看表结构

DESC student

7.删除表

DROP table student

DML:数据操作语言

1.插入语句

insert into 表名（列名 1，列名 1，列名 1，列名 1，....）values(..)

insert into stu(sid,sname) values('s_1001','zhang');

insert into 表名 values();

insert into stu values('s_1001','zhangsan');

2.修改数据

update 表名 set 列名1=值1，列名2=值2 where 条件

update stu set name='lisi',age='20' where sid='s_1001';

3.删除数据

delete from 表名 where条件

delete from stu where sid='s_1001';
delete from stu where sid='s_1001' or age < 30;
delete from stu;

truncate table 表名

truncate table stu;

delete 和truncate的区别：

delete和truncate都可以删除表的所有数据记录 但原理不同 truncate的效率比delete高 truncate删除的记录无法回滚 delete可回滚

DQL:数据查询语言

语法

selete 列名 -----》 要查询的列的名称

from 表名 -----》 要查询的表的名称

where 条件 -----》 行条件

group by 分组列-----》 对结果分组

having 分组条件-----》 分组后的行条件

order by 排序列-----》 对结果分组

limit 起始行，行数-----》 结果限定

1.基础查询

查询stu表所有列

select * from stu;

查询指定列（查询stu表的sid sname）

select sid sname from stu;

2.条件查询

查询性别为女 并且年龄小于50的记录

select * from stu 
where gender='woman' and age<50;

查询学号为s_1001 或者姓名为lisi的记录

select * from stu
where sid='s_1001' or sname='lisi';

查询学号为s_1001 s_1002 s_1003的记录

select * from stu
where sid in ('s_1001','s_1002','s_1003');

查询年龄为null的数据

select * from stu
where age is null;

查询年龄在20到40之间的学生记录

select * from stu
where age between 20 and 40;

查询性别非男的学生记录

select * from stu
where gender != 'man';

查询姓名不为null的学生记录

select * from stu
where name IS NOT NULL;

3.模糊查询

查询姓名有五个字母构成的学生记录

select * from stu
where name like '_ _ _ _ _';

查询姓名由五个字母组成 并且第五个字母为i的学生记录

select * from stu
where name like '_ _ _ _ i';

查询姓名以'z'开头的学生记录

select * from stu
where name like 'z%';

查询姓名中第二个字母为'i'的学生记录

select * from stu
where name like '_i%';

查询姓名中包含字母a的学生记录

select * from stu
where name like '%a%';

4.字段控制查询

去除重复记录（查询emp表所有雇员的工资 要求去重）

select distinct salary from emp;

查看雇员的月薪（sal）与佣金（comm）之和

select *,sal+comm from emp;

佣金可能是null 任何值与null相加还是null 要做comm的控制判断

select *,sal+IFNULL(comm,0) from emp

sal+IFNULL(comm,0)很不美观 可以起个别名

select *,sal+ifnull(comm,0) as total from emp;

5.排序

查询所有学生记录 按年龄升序排序

select * from stu
order by age asc;

查询所有学生记录，按年龄降序排序

select * from stu
order by age desc;

查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

select * from emp
order by sal desc ,empno asc;

6.聚合函数

COUNT()：统计指定列不为 NULL 的记录行数； MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算； MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算； SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为 0； AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为 0； —————————————————————————————

COUNT:当需要纵向统计时可以使用count（）；

查询 emp 表中记录数：

select count(*) as cnt from emp

查询 emp 表中有佣金的人数

select count(*) as cntNotNull from emp
where comm is not null;

select count(comm) as cnt from emp;

查询 emp 表中月薪大于 2500 的人数

select count(*) as cnt from emp
where sal > 2500;

统计月薪与佣金之和大于 2500 元的人数

select count(*) as cnt from emp
where sal+ifnull(comm,0) as totle > 2500;

SUM 和 AVG：当需要纵向求和时使用 sum()函数和AVG函数

查询所有雇员月薪和：

select sum(sal) from emp;

查询所有雇员月薪和，以及所有雇员佣金和

select sum(sal) as sumSal , sum(comm) as sumComm 
from emp;

查询所有雇员月薪+佣金和

select sum(sal+ifnull(comm,0)) from emp;

统计所有员工平均工资

select avg(sal) from emp;

MAX 和 MIN

查询最高工资和最低工资

select max(sal),min(sal) from emp;

7.分组查询

查询每个部门的部门编号和每个部门的工资和

select deptno, sum(sal)
from emp
group by deptno;

查询每个部门的部门编号以及每个部门的人数

select deptno,count(*)
from emp
group by deptno;

查询每个部门的部门编号以及每个部门工资大于 1500 的人数

select deptno ,count(*)
from emp
where sal > 1500;
group by deptno

HAVING 子句

查询工资总和大于 9000 的部门编号以及工资和

select deptno and sum(sal)
from emp
group by deptno
having sum(sal) > 9000;

WHERE 是对分组前记录的条件，如果某行记录没有满足 WHERE 子句的条件，那么这行记录不会参加分组；而 HAVING 是对分组后数据的约束。

8.LIMIT：用来限定查询结果的起始行，以及总行数。

stu表查询 5 行记录，起始行从 0 开始

select * from stu limit 0,5;

stu查询 10 行记录，起始行从 3 开始

select * from stu limit 3,10;

stu表查询年龄最大的三个学生

select * from stu 
order by age desc
limit 3;

stu表查询年龄最小的三个学生

select * from stu
order by age asc
limit 3

9.多表连接查询： 表连接分为内连接和外连接

内连接仅选出两张表中互相匹配的记录，外连接会选出其他不匹配的记录

内连接

例如： 以下是员工表 staff 和职位表 deptno:

查询员工的姓名和职位

select staff.name,deptname 
from staff,deptno
where staff.name = deptno.name;

结果如下

外连接

左连接：包含左边表中所有的记录，右边表中没有匹配的记录显示为 NULL。 右连接：包含右边表中所有的记录，左边表中没有匹配的记录显示为 NULL。

左外连接

select staff.name deptname
from staff left join deptno
where staff.name = deptno.name;

结果如下

右外连接

select staff.name deptname
from staff right join deptno
where staff.name = deptno.name;

结果如下