MySQL笔记01

一、数据库常识

数据库中最基本的单元：表
表以表格形式表示数据
每一张表都有行和列
行(row):被称为数据或记录
列(column):被称为字段

SQL语句分类

1.DQL 数据查询语言：凡是带有select关键字的都是查询语句

2.DML数据操作语言：对表中的数据进行操作
insert，delete，update
insert：增
delete：删
update：改
3.DDL数据定义语言：对表的结构进行操作
常见命令：create，drop，alter
crate：新建
drop：删除
alter：修改
4.TCL事务控制语言
事务提交：commit
事务回滚：rollback
5.DCL数据控制语言
授权：grant
撤销权限：revoke

二、语句

查询语句

1. 查一个表的所有信息
   select * from 表名
   例如：create table abc
   select * from abc

2. 查一个表的结构
   desc 表名
   例如：create table abc
   desc from abc

3. 查一个表的多个多个字段信息
   例如：create table abc
   SELECT a,b FROM abc

4. 给字段起别名
   例如：create table abc
   SELECT a,b as bbb FROM abc
   如果别名内有空格（加引号，例如：‘b b’ )
   例如：create table abc
   SELECT a,b as ‘b b’FROM abc

5. 字段加减乘除，数值
   SELECT TenantId*10 as T10 FROM abpauditlogs
   

SELECT TenantId*10 as ‘我的’ FROM abpauditlogs

6. 条件查询
   select 字段名 from 表明 where…

语法格式

select 字段1，字段2…字段n from 表名 where 条件
select name,age from abc where age >= 20 年龄大于等于20
select name,age from abc where age <> 20 年龄不等于20
select name,age from abc where age >= 10 and age <= 40 【10，40】
select name,age from abc where age between 10 and 40 【10，40】

null && not null

注意：数据库中的null不能使用等号，要用is null 或 is not null
select name,age from abc where name is null
select name,age from abc where name is not null
## and && or
select name,age from abc where name <> ‘张三’ and age >= 20
name不是张三和年龄大于等于20
select name,age from abc where name = ‘张三’ or name = “李四”
name是张三或者是李四

and 和 or 同时出现 有优先级问题吗
and 优先级比 or 高
例如，现在 我想找出 身高大于175cm，并且体重为70kg或80kg的学生
select height,weight from student where height > 175 and weight = 70 or weight = 80
这个语句是否正确呢？？？
首先从语法上没有错，但是，它所表达的意思却不是题目的意思
它所表达的意思是：找出身高大于175并且体重等于70kg的学生
和
体重等于80kg所有的学生
q:那我们怎么实现正确的呢?
a:加个括号就行了
select height,weight from student where height > 175 and (weight = 70 or weight = 80)

## in
in相当于多个or（not in 表示不在这个范围中）
例如：查询名字（name）是张三和李四和王五的学生
select name,weight,height from student where name in (‘张三’,‘李四’,‘王五’)
注意：in不是一个区间，in后面跟的是具体的值

## like
like被称为模糊查询，支持%或下划线匹配
%匹配任意个字符
下划线，一个下划线只匹配一个字符
找出学生名字中含有o的
select name from student where name like ‘%o%’
找出学生名字是以J开头的
select name from student where name like ‘J%’
找出学生名字是以s结尾的
select name from student where name like ‘%s’
找出学生名字是第二个字母是A的
select name from student where name like ‘_A%’
找出学生名字是第三个字母是B的
select name from student where name like ‘__B%’

现在，假设有一张students表 ，其中，有name字段
name 字段

zhangsan
lisi
wangwu
john_son
找出名字中有"_"的
select * from students where name like ‘%_%’
说明：_要经过转义
## 排序
给学生身高进行排序
select * from students order by height
默认是升序（小到大）
怎么降序
select * from students order by height desc
升序还有一种写法
select * from students order by height asc
如果升高一样的话，还要判断名字(也是升序排列）
select * from students order by height asc,name asc

了解：根据字段的位置也可以排序
select name,height from students order by 2
2表示第二列，第二列是height
按照查询结果的第二列height ，但是不建议这样写，因为不健壮

找出身高在160到180的学生信息，要求按照身高降序排列
select height from students where height >= 160 and height <= 180 order by height desc
select height from students where height between 160 and 180 order by height desc
以上语句执行顺序
from
where
select
order by

函数

数据处理函数–单行处理函数
特点：一个输入对应一个输出
多行处理函数
特点：多个输入对应一个输出

单行处理函数

lower 转成小写
select lower(name) from students
upper 转成大写
select upper(name) from students
substr 取子串
select substr(name,1,1) from students
注意：起始下标从1开始 没有0
小题1：找出学生名字第一个字母是A的学生信息
第一种方式：模糊查询
select name from students where name like ‘J%’
第二种方式：substr函数
select name from students where substr(name,1,1) = ‘A’
concat 字符串拼接
select concat(name,number) from students
lenth 取长度
select lenth(name)from sudents
trim 去除空格
select name from students where name = trim(’ king ')
round 四舍五入
select round(money,0) from emp 保留整数
select round(money,1) from emp 保留一位小数
select round(money,2) from emp 保留两位小数
select round(money,-1) from emp 保留到十位
select round(money,-2) from emp 保留到百位
rand 生成随机数
select rand() from emp
select round(rand()*100,0) from emp 生成100以内的随机整数
ifnull----空处理函数
null只要参与运算，最终结果一定是null，为了避免这个现象，需要使用ifnull函数
ifnull函数用法：ifnull(数据,被当做哪个值)
select name,(height+ifnull(weight,0)) from students

case…when…then…when…then…else…end
当岗位是A的时候，工资上调20%，B上调30%，其他上调10%
select name,job,sal as oldsal,(case job when ‘A’ sal1.2 when ‘B’ sal1.3 else sal*1.1) as newsal from emp

## 分组函数（多行处理函数）

一共5个
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
注意：分组函数自动忽略null

分组函数中count()和count(具体字段)有什么区别？？？
count() 行记录
count(num) 不为null的元素的个数

分组函数不能够直接使用在where子句中

所有的分组函数可以组合起来一起用
select sum(sal),min(sal),count(name) from emp

分组查询(非常重要）

什么是分组查询？
在实际的应用中，可能有这样的需求，需要先进行分组，然后对每一组的数据进行操作
这个时候我们就要用分组查询，那么怎么进行分组查询呢？？？
select
…
from
…
group by
计算每个部门的工资和
计算每个工作岗位的平均薪资
找出每个工作岗位的最高薪资

将之前的关键字全部组合在一起
    select
           ...
           from
                ....
                  group by
                          ....
                          order by
                               ....
       以上的关键字的顺序不能颠倒，需要记忆
       执行顺序
       1.from
       2.where
       3.group by
       4.select
       5.order by

      分组函数在使用的时候必须进行分组，才能够使用
      
     找出每个工作岗位的工资和
     实现思路：按照工作岗位分组，然后对工资求和。
       select
            job,sum(sal)
        from
             emp
         group by
              job
        以上语句执行顺序？？？
        先从表中查询数据
        根据job字段进行分组
        然后对每一组的数据进行sum(sal)

      select ename,job,sum(sal) from emp group by job
      以上语句在MySQL中可以执行，但是毫无意义
      在Oracle中执行报错
        
        重要结论
        在一条select 语句当中，如果有group by 语句的话
        select 后面只能跟：参加分组的字段，以及分组函数
        其它的一律不能跟

      现在我想找出每个部门的最高薪资
      实现思路是什么/？？/
            select x,max(sal) from emp group by x
            
       找出每个部门，不同工作岗位的最高薪资
       技巧：两个字段联合成一个字段看（两个字段联合分组）
       select
                x,job,max(sal)
        from 
               emp
         group by
                x,job

       问题：找出每个部门最高薪资，要求显示最高薪资大于3000的
       第一步：找出每个部门最高薪资
                   按照部门编号分组，求每一组最大值
                   select deptno,max(sal) from emp group by deptno
       第二步：要求显示最高薪资大于3000
       select deptno,max(sal) from emp group by deptno having max(sal)>3000
        
        效率比较低，考虑，现将大于3000的都找出来。然后再分组
        select xxx from emp where sal>3000 group by deptno

      优化策略：where和having优先选择where

         where没办法的？？？
         找出每个部门平均薪资，要求显示平均薪资高于2500的
         第一步：找出每个部门平均薪资
         select deptno,avg(sal) from emp group by deptno
         第二步：要求显示平均薪资高于2500的
         select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500     

三、总结（单表查询结束）

select
…
from
…
where
…
group by
…
having
…
order by
…
执行顺序
1.from
2.where
3.group by
4.having
5.select
6.order by

问题：现在想找出每个岗位的平均薪资，要求显示平均薪资大于1500的，除manager岗位之外，要求按照平均薪资降序排列

select
job,avg(sal) as avgsal
from
emp
where
job <> ‘manager’
group by
job
having
avg(sal) > 1500
order by
avgsal desc