004 数据库学习笔记--CRUD + 聚合函数 + 分组查询 + 连接查询

对表的的插入操作：

1. 单条数据插入
   • insert into 表名 (列1，列2，列N) values (value1,vaule2,valueN)
   • insert into 表名 (列1，列2，列N)  select value1,vaule2,valueN
2. 多条插入
   • insert into 表名 (列1，列2，列N) values (value1,vaule2,valueN)，(value1,vaule2,valueN)，(value1,vaule2,valueN)
   • insert into 表名 (列1，列2，列N) select value1,vaule2,valueN 　　union 　　select value1,vaule2,valueN 　　union　　 select value1,vaule2,valueN
   • 注意：union 支持去重操作，union all 不支持去重操作； union all 效率比union高；
3. 克隆表数据--将一张表的数据复制到领一张表
   • 目标表已在数据库中存在：insert into 目标表(列) select 列名 from 原始数据源表
   • 目标表在数据库中不存在，执行脚本时创建目标表：select 源表列名 into 目标表 from 数据源表

更新：update tablename set colname='',colname='' where ......

删除：

• 删除数据——delete from tablename where 。。。。。(删除数据要当心，where 当先。。。。。)
  • 注：当使用delete语句时，会造成标识列的值不连续(删除数据后，标识列的值还是接着删除前的值进行自增，而不会从初始值开始)
  • 删除每一条数据都会在日志中进行记录，效率相对truncate较低
  • delete/update/insert在事务中，可以回滚，可恢复
• truncate table tablename——清空表数据，恢复到初始化状态(不可恢复，慎用)
  • truncate不会记录日志，且不会激活触发器
  • drop/truncate是即时操作，不可回滚rollback

查询：

1. 查询单表
   • 查所有：select * from tablename——不建议使用，耗资源内存
   • 查部分：select colname1,colname2,colnamen from tablename——建议使用方式
   • 别名查：select age as 年龄，name as 姓名 from tablename——别名使用(方式二：select age  年龄，name  姓名 from tablename；方式三：select age = 年龄，name = 姓名 from tablename)
   • 排序查：asc|desc 自增主键列默认排序asc；
     • select age as 年龄，name as 姓名 from tablename order by age asc|desc(多字段使用逗号隔开)
     • order by 始终在查询语句的最后——位于where、groupby之后
   • 模糊查询：select age as 年龄，name as 姓名 from tablename where  name like。。。。。。
     • %：匹配0个或多个——
       • name like '%刚%'——包含刚
       • name like '%刚' ——以刚结尾
       • namek like '刚%'——以刚开头
     • _ 匹配单个字符——限制表达式的字符长度
       • name like '_dmin'　（_下划线表示字符长度）　
     • [] 范围匹配：括号中所有字符中的一个
       • name like 'ad[mnd]in' ：例如 name=admin
     • [^]不在括号中所有字符之内的单个字符
       • 与上面相反
   • 范围查询：
     • where 子句查询条件，给定范围
       • select  from tablename where 。。。。。。
     • 前多少条或百分比
       • 前10条：select top 10 * from tablename
       • 前10%：select top 10 percent * from tablename
     • 通过比较运算符 >、<、>=、<=、<>　　（多条件用and 或 or）
     • in 或 not in
     • 子查询：select * from tablename where id in (select id from tablename2 where id >100)
     • between and：select * from tablename where age between 20 and 30
       • 等价与 age>=20 and age <=30 且效率较运算符较高，建议使用
聚合函数：对一组值执行计算并返回单一的结果； 5种；常与group by 使用
• count：统计记录个数——select count(1) from tablename; count(1),伪造列，建议使用；统计表的记录数，不建议使用 count(*)
• sum：求和——select sum(price) from tablename
• avg：求平均值——select avg(price) from tablename
• max：求最大值——select max(price) from tablename
• min：求最小值——select min(price) from tablename
2. 分组查询
   • select col,col2 from tablename group by  col1,col2：根据一列或多列对结果集进行分组
   • 注：在使用分组查询时，select 查询的列必须在group by 出现（聚合函数列可不包含）
   • 语法示例：select patientname,deviceno from tablename
     • 　　where age > 30
     • 　　group by patientname,deviceno
     • 　　having deviceno > 10 ——对分组后的数据添加筛选条件
     •        order by deviceno
3. 连接查询：根据两个或多个表之间的关系，查询其中的数据——其目的就是实现多表查询
   • 内连接：
     • inner join on 使用比较运算符 >、<、>=、<=、<>进行表之间的比较，查询与条件匹配的数据集合
     • 查询匹配结果，结果不匹配，则无结果
     • select userid,username,age,detpname,deptinfo from userinfo  u  inner join deptinfo d on   u.deptinfo = d.deptinfo  　　where  age > 10
     • 隐式连接：select userid,username,age,detpname,deptinfo from userinfo  u,deptinfo d　　 where u.deptinfo = d.deptinfo  and  age > 10
   • 外连接：
     • 左外连接
       • left join on
       • 返回左表的所有行，右表中没有匹配上，对应的列就显示null(返回所有行，右表--与左表相同，没有匹配上显示null)
       • select  *  from UserInfo  u 　　left  outer join DeptInfo d   on u.deptinfo = d.deptinfo  (outer可省略)
     • 右外连接
       • right join on 
       • 返回右表的所有行，左表中没有匹配上，对应的列就显示null(返回所有行，左表--与右表相同，没有匹配上显示null)
       • select  *  from UserInfo  u 　　right outer join DeptInfo d   on u.deptinfo = d.deptinfo  (outer可省略)
   全连接：(参考：https://www.freesion.com/article/907283441/)
   • full （outer） join 全外连接，返回左表和右表所有行
   • 两个表中匹配的所有行记录
   • 左表中那些在右表中找不到匹配的行的记录，这些记录的右边全为null
   • 右表中那些在左表中找不到四配的行的记录，这些记录的左边全为null
   • select * from emp full join dept on emp.deptno=dept.deptno
   • select * from emp E full join dept D on E.deptno=D.deptno full join salgrade S on E.sal>S.lostl and E.sal<S.hisal
   • 交叉连接：（参考：https://blog.csdn.net/tswc_byy/article/details/81948973）
     • cross join 也称为 笛卡尔积
     • select * from emp,dept where emp.deptno=dept.deptno　　等价于　　select * from emp cross join dept where emp.deptno=dept.deptno

     • select * from emp,dept,salgrade
       where emp.deptno=dept.deptno and
       (emp.sal>salgrade.lostl and emp.sal<salgrade.hisal)
       等价于
       select * from emp "E"
       cross join dept "D"
       cross join salgrade "S"
       where "E".deptno="D".deptno and
       ("E".sal>"S".lostl and "E".sal<"S".hisal)

     • 建议使用内连接
4.