内容回顾

  1. 多积累使用工具的经验
  2. 尽量多练习
    • 1.多练几种类型
    • 2.不要照着写好的sql敲,要自己组织语言

内容回顾

存储引擎

  • innodb : 外键 行级锁(并发修改) 事务(客户管理系统)
  • myisam : 表级锁 不支持外键\事务\行级锁
  • memory : 只能在内存中存储数据 重启server数据丢失

mysql中的基础数据类型

  • 数字
    • int id/age/部门编号
    • float(8,2) salary
  • 字符串
    • char 定长 越是长度固定char越节省空间 读写速度快
      * 名字 部门名
    • varchar 变长 越是长度不固定varchar越节省空间 读写速度慢
      * 部门描述
  • 时间
    • year
    • date 入职日期 离职 开学 毕业
    • time
    • datetime 出生日期 交易记录 打卡时间
    • timestamp
  • enum 和 set
    • enum 单选
      • enum('male','female')
    • set 多选(去重)

完整性约束

  • id int unsigned
  • id int default 0
  • id int not null
  • id int unique
  • auto_increment 相当于非空+自增且只能用于整数类型
    • id int unique auto_increment
    • id int primary key auto_increment
  • 非空 + 唯一
    • id int unique not null 如果没有主键,第一个设置非空唯一的就是主键
  • 联合唯一
    • id int,
    • name char(12),
    • unique(id,name)
  • primary key 主键 一张表只能有一个主键
    • id int primary key
  • foreign key 外键
    • id int,
    • name char(12),
    • tid int,
    • foreign key(tid) references 外表(字段名) on update cascade on delete cascade

表的操作

  • 创建表
    • create table 表名(
    • 字段名 类型(长度约束) 其他约束,
    • 字段名 类型(长度约束) 其他约束,
    • 字段名 类型(长度约束) 其他约束);
  • 删除表
    • drop table 表名
  • 修改表
    • alter table 表名 rename 新表名;
    • add 新字段 类型(长度约束) 其他约束 first;
    • drop 字段 ;
    • modify 原字段名 新类型(新长度) 新约束 after 某字段;
    • change 原字段名 新字段名 新类型(新长度) 新约束;
  • 查看表结构
    • desc 表名 == describe 表名
    • show create table 表名; 查看详细表结构,存储引擎 编码 更复杂的约束条件

默写

  • 员工id 姓名 性别 年龄 入职日期 部门 部门描述 部门编号 薪资
 create table staff(
     id int primary key auto_increment,
     name char(12) not null
     sex enum('male','female') default 'male',
     age int,
     hire_date date,
     post_name char(12),
     post_comment varchar(255),
     post_id int,
     salary float(8,2) unique
);
  • 拆分表
create table post(
    post_id int primary key auto_increment,
    post_name char(12),
    post_comment varchar(255)
)
create table staff(
    id int primary key auto_increment,
    name char(12) not null
    sex enum('male','female') default 'male',
    age int,
    hire_date date,
    salary float(8,2) unique
    post_id int,
    foreign key(post_id) references post(post_id)
);

今日内容

数据操作

  • 增
  • 删
  • 改
  • 查
    • 单表查询
    • 多表查询

增删改查

增加数据

id name age sex
insert into 表名 value (1,'alex',83,'不详');
insert into 表名 values (1,'alex',83,'不详'),
                       * (2,'太白',40,'male');
insert into 表名 (id,name) values(3,'宝元');
了解
   * insert into 表名 select * from 另一张表;
   * insert into 表名 select 字段1,字段2 from 另一张表;
   * insert into 表名(字段1,字段2) select 字段1,字段2 from 另一张表;

删除数据

delete from 表名 where id=3

更新数据

update 表名 set 字段名=新的值 where age>20

SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

单表查询

简单查询

  • select 字段名 from 表名
  • select 字段名,字段名... from 表名
  • select * from 表

去重 distinct

  • select distince 某个字段 from 表 * 对某个字段去重
  • 对筛选的结果进行四则运算
  • select 字段*12 from 表
  • 在显示查询结果的时候临时重命名
  • select 字段名 新名字 from 表;
  • select 字段名 as 新名字 from 表;

concat函数

  • concat('你想拼接的内容',字段名,'你想拼的其他内容','字段名')
  • concat_ws('连接符号',字段1,字段2,....)

case语句

  SELECT
      (case
      when emp_name = 'jingliyang' then
          emp_name
      when emp_name = 'alex' then
          concat(emp_name,'_bigsb')
      else
          concat(emp_name,'_sb')
      end) as new_name
  FROM employee;

where条件

  • select 字段 from 表 where 条件
  • 比较运算符 > < >= <= <> !=
  • 范围(范围更大) between a and b 查询a,b之间的所有内容
  • 范围(范围更精准) in (a,b,c) 查询值为a或者b或者c的所有内容

like

  • select * from 表 where emp_name like '金%'
  • %是一个通配符,表示任意长度的任意內容
  • select * from 表 where emp_name like '金三_'
  • _也是一个通配符,表示一个长度的任意内容

逻辑运算符 and or not

身份运算符 is null/ is not null

  • emp_name is null

正则匹配

  • 所有人的身份证号,匹配所有身份证号是15位的居民 \d{15}$

  • select 字段 from 表 where age regex '\d{15}$';

  1. 查看岗位是teacher的员工姓名、年龄
  2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
  3. 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资
select emp_name,age,salary  from employee where salary between 9000 and 10000
  1. 查看岗位描述不为NULL的员工信息
    • where post_comment is not null
  2. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
  3. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
where salary !=10000 and salary !=9000 and salary !=30000
where salary not in (10000,9000,30000)
  1. 查看岗位是teacher且名字是jin开头的员工姓名、年薪 python select emp_name,salary*12 as annul_year from employee where post='teacher' and emp_name like 'jin%'

group by

  • 分组
  • 根据分组的字段自动的做去重
  • 其他重复的项目都不会在结果中显示
  • 但是可以使用count来计算每个组中的项，也可以使用group_concat来查看组内的名字

having 总是和group by 连用,where中不能出现聚合函数,所以和聚合函数有关的条件筛选也只能用having

  • 对分组进行条件过滤

  • 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数

    • 查各个岗位的员工个数
      select post,count(id) from employee group by post having count(id) <2
      总是对分组之后的结果进行一个条件筛选的时候用having

  1. 查询各岗位平均薪资大于10000的岗位名、平均工资
    select post,avg(salary) from employee group by post having avg(salary) > 10000

  2. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

select post, avg(salary) from employee group by post
having avg(salary) > 10000 and avg(salary) < 20000;

order by

  • 默认从小到大排序 升序
  • 从大到小排序 desc 降序

先按年龄升序排,当年龄相同的时候,工资低的排前面

查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列

select post,avg(salary) as avg_salary from employee group by post having avg(salary)>10000 order by avg_salary

limit 取前n个或者web开发中做分页功能

  • 显示前n条 limit n
  • 从第m+1条开始,显示n条 limit m,n
  • 从第m+1条开始,显示n条 limit n offset m
select distinct 字段 from 表 where 条件 group by 分组 having 过滤条件 order by 排序 limit n;
sql的解析顺序
扫码关注我们
微信号：SRE实战
拒绝背锅 运筹帷幄