Database—DML



待整理

5.DML常用语句：

1.insert：插入记录

2.update：修改记录

3.delete：删除记录

4.select：查询记录

6.insert：

1.语法：

1.insert into 表名 values(值1,值2··· ···);

2.insert into 表名(列名1,列名2···) values(值1，值2···);

3.insert into 表名 查询语句

2.例句：

1.insert into books(bookname,bookprice) select name,price from libary//将libary中的name，price添加到books中

7.select：

1.语法：

1.select 列名|表达式|*

2.from 表名

3.where 条件语句//分组前过滤

4.group by 列名|表达式      //分组

5.having 条件语句              //分组后过滤

6.order by 排序 [asc,desc]//排序

7.limit 起点,记录数//截取

2.条件子句中可以使用的谓词

1.>,<,!=,=,<=,>=

2.in

3.not

4.between and：连续的区间

5.is null,is not null：不能使用=null来判断是否为空

6.like 模糊查询

1._ ：任意一个字符

2.% ：任意多个字符

7.and or：逻辑运行符

3.聚合函数(集合函数)：

1.count(列名l*)：记数

2.sum() ：求和

3.avg() ：平均数

4.max() ：最大数

5.min() ：最小值

4.where与having的区别：where是在分组之前进行过滤，having是在分组之后进行过滤，因此在having子句中可以使用分组之后的结果

5.例句：

1.select distinct bookname,'hello' 哈喽 from books//查询不重复的书名并在其后加一列名为哈喽，内容为hello的列

2.select distinct bookname as "书  名",bookid//将列名改为“书名”显示，as可以省略，书名中间无空格时" "可以省略

3.select * from `user` where name like '%阳%';//name中含有“阳”的元素，‘--阳’代表name中为XX阳的元素

4.select SUM(salary),count(*),avg(salary),count(DISTINCT deptnu) 'sort of deptnu' from `user`

5.select u.deptnu,d.dname,SUM(salary),count(*) from `user` u,dept d where u.deptnu=d.deptnu group by u.deptnu//分组必须和聚合搭配使用；多条语句分组，将多个列元素都相同的分为一组，列名之间用逗号隔开；使用分组时只能select聚合函数，select列则报错(mysql中不报错，但查得数据是错误的)

6.select deptnu,SUM(salary),count(*) from `user` group by deptnu having COUNT(*)>2

7.select * from `user` order by salary，deptnu//先按照salary，再按照deptnu从小到大排序

8.select * from `user` order by salary desc//按照salary从大到小排序

9.select * from `user` order by salary desc,deptnu limit 0,3//截取结果，从0开始，截取三个

8.update：更新语句

1.语法：update 表名 set 列名1=值,列名2=值 where 条件

2.例句：

1.update `user` set salary=10000,deptnu=4 where name='陈衍舟'

2.update `user` set salary=salary+100//所有人的salary加100

3.update `user` set name=lower(name)//所有人的名字改为小写

9.delete的语法：

delete from 表名where 条件

高级查询：

1.表连接：

1.连接方式：

1.select *|表达式|列 from table1 t1，table2 t2

 where 连接条件

2.标准的sql-99语句

2.笛卡尔积：两个集合的乘积，即表一的每一条记录(如10条)与表二的每一条记录(如5条)都关联，结果为50条

3.表的别名：上述的t1就是table1的别名，进行连接条件判断时，可以通过别名调用列名

4.多表连接：三个表连接一般至少要两个条件

5.非等值连接：where 列名 between  and

6.标准的sql-99

1.语法：

select * from table1

连接方式

table2

on 连接条件

2.分类：

1.内连接：完全依赖于连接条件；inner join

2.外连接：保证其中一边表的数据一定会被查出来，另一边没有则用null填充

1.左外连接：左边的表一定被查出来；left (outer) join

2.右外连接：右边···；right [outer] join

3.全外连接：两边···；full [outern] join ；mysql不支持

3.自连接：

7.例句：

1.select * from `user`,salaryclass where salary BETWEEN low_salary and high_salary//查询每条记录salary对应的等级

2.select * from dept d left outer join `user` u on u.deptnu=d.deptnu//左外连接

2.子查询(嵌套)：

1.在一个查询语句中包含另一个查询语句

2.注释：-- 注释语句

3.例句：

1.select * from `user` where deptnu=(select deptnu from dept where dname='开发部')//查找开发部在user中对应的记录

2.select * from `user` where salary>(select AVG(salary) from `user`)//查找所有大于平均工资的记录

3.select * from `user` where salary in (select max(salary) from `user` where deptnu=3)//比所有deptnu=3的salary都大的salary

4.select * from `user` where deptnu=1 or deptnu=2

5.select * from `user` where deptnu in|any|all|not in(select deptnu from dept where dname in('开发部','销售部'))

6.select * from `dept` where deptnu not in(select DISTINCT deptnu from `user`)//查找没有人的部门

7.select count(*),u.deptnu,dname from `user` u,dept d where u.deptnu=d.deptnu group by u.deptnu having count(*)>(select count(*) from `user` where u.deptnu=1)//查找人数比deptnu=1多的记录

8.select * from  (select deptnu,count(*),avg(salary) sal from `user` group by deptnu) a where a.sal>10000//查出分组后平均salary大于10000的记录

4.位置：

1.where：

2.having：

3.from：

4.select：不常用

5.常用谓词：因为> <,>=,=,<=只能比较一个唯一值，而不对多个查询记录进行比较，因此推出了一些用于集合操作的谓词

1.all：

2.any：

3.in：

4.not in：

6.exists(存在)|not exist：

1.语法：select * from 表名 where exists(查询语句)

2.例：

1.select * from tb_class where classid in(select classid from tb_student)//先查里面

2.select * from tb_class c where exists(select classid from tb_student s where c.classid=s.classid)//先查外面，再查括号里的内容看结果返回的是true或false，查询出有学生的班级，外面少用exist更合适，否则用in性能更好

3.函数(自查API)：

1.字符函数：

2.数值函数：

3.时间函数：

1.根据出生日期计算年龄：

1.select year(SUBTIME(NOW(),SBIRTH)) age from student

2.select DATEDIFF(NOW(),SBIRTH)) age from student

4.加密：

5.系统函数：

6.case when：实现java的分支

1.(可以完成行变列的查询)

2.例句：select  ACCOUNTID,name,case when remain<1000 then '丝丝' when remain>10000 then'富豪' else '中产阶级' end from ACCOUNT