Mysql的函数及约束

MySQL

数据查询语言，查询数据库(表的记录)

函数

字符串函数

 -- concat(s1,s2,...) : 字符串的拼接
 select concat('zzy ','eat shit');
 -- lower(str):把字符串全搞成小写
 select lower('Fuck');
 -- upper(str):把字符串全搞成大写
 select upper('fuck');
 -- lpad(str,n,pad):用字符串pad对str左边填充至n字符串长度
 select  lpad('hello',9,'-');
 -- rpad(str,n,pad)：用字符串pad对str右边填充至n字符串长度
 select  rpad('fuck',5,'*');
 -- trim(str):去掉字符串头尾空格
 select trim('   fuck you ass   ');
 -- substring(str,start,len):返回从start到len长度的字符串
 select  substring('fuck you bitch',3,7);
 ​
 ​

数值函数

 -- ceil(x):向上取整
 select ceil(1.6);
 -- floor(x):向下取整
 select floor(3.9);
 -- mod(x,y):返回x/y的模,余数
 select mod(3,6);
 -- rand():返回0~1随机数
 select rand();
 -- round(x,y):求参数x的四舍五入值，保留y位小数
 select round(2.4567,1);
 -- 生成6位数的随机验证码
 select lpad(round(rand()*1000000,0),6,'0');

日期函数

 -- curdate():返回当前日期
 select curdate();
 -- curtime()：返回当前时间
 select curtime();
 -- now():返回当前日期和时间
 select now();
 -- year(data):获取data年份
 select  year(now());
 -- month(data):获取data月
 select  month(now());
 -- day(data):获取data日
 select  day(now());
 -- date_add(data,INTERVAL expr type/年月日):当前时间往后推expr type天的日期
 select date_add(now(),INTERVAL 70 day );
 -- datediff(data1,data2):返回起始时间2到结束时间1天数
 select datediff('2024-9-21','2004-4-08');
 -- 查询all员工入职天数，并根据天数倒叙排序
 ​

流程函数

 -- if(value,t,f):如果value为true返回t，不然返回f
 select if(true,'yes','no');
 -- ifnull(value1,value2):如果value不为空返回value1，不然value2
 select ifnull(,'ok');
 select  ifnull('ok','yes');
 -- case when[val1] then[res1]..else[defalt] end:如果value返回true，返回res1，不然返回default
 -- 查询emp表员工姓名工作地址（北上广 --->一线城市，其他--->二线城市）
 select name,
        (case workaddress when '北京'then'一线城市'when'上海'then'一线城市'else '二线城市'end)as "工作地址"
 from emp;
 -- case[expr] when[val1] then[res1]..else[default] end:如果expr值为val1，返回res1，不然返回default
 -- 统计成员成绩，》=85优秀，》60及格，否则不及格
 ​
 select name,
        (case when math>=85 then'优秀'when math>=60 then'及格'else'不及格'end)as '数学',
        (case when chinese>=85 then'优秀'when chinese>=60 then'及格'else'不及格'end)as '语文',
        (case when english>=85 then'优秀'when english>=60 then'及格'else'不及格'end)as '英语'
 from score

约束

概念：作用于表中字段上的规则，限制存储在表中的数据。

目的：保证数据库数据的正确、有效和完整。

 -- ID唯一标识（主键，并且自动增长）：primary key ,auto_increment
 -- name 不为空，并且唯一：not null,unique
 -- age >0,<=120:check
 -- status 如果没有指定该值默认为1:default
 -- gender：
 create table user(
     id int primary key  auto_increment comment '主键',
     name varchar(10) not null unique comment '姓名',
     age int check ( age>0 and age<=120 ) comment '年纪',
     status char(1) default '1' comment '状态',
     gender char(1) comment '性别'
 )comment '用户表';
 insert into user(name, age,status, gender) VALUES ('Tom1',19,'8','男'),('Tom3',10,'0','男');
 ​
 ​
 ​
 -- 两张表产生联系（添加外键）
 alter table emp1 add constraint 外键名 foreign key(字段) references 主表（主字段）;
 -- 删除外键
 alter table 表名 drop foreign key 外键名;

外键约束（modify table）

 alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名（主表字段名） on update 行为;

多表查询

多表关系：根据业务需求，业务之间相互关联，表结构也相互关联，即表与表间也存在各种联系。

1. 一对多（多对一）：

   • 一个部门多个员工，一个员工在一个部分

   • 在多的一方建立外键，指向的一方的主键

2. 一对一：

   • 多用于单表拆分，将一张表基础字段放在一张表中，以提升操作效率

   • 在任意一方加入外键，关联另一方主键，并设置外键唯一（unique）

3. 多对多：

   • 一个学生可以选修多门课程，一门课程供多个学生选择

   • 建立第三张中间表，中间表至少含2个外键，分别关联两方主键。

   •  alter table student_course add
          constraint  fk_couseid foreign key (courseid)
              references course(id);
      alter table student_course add
          constraint fk_studentid foreign key (studenid)
      references  student(id);

多表查询

 select * from A,B;
 -- 出现笛卡尔积的情况

笛卡尔积：集合A和集合B所有的组合情况

分类：

• 连接查询

  • 内连接：查询AB交集部分数据

     -- 隐式内连接
     -- 查询每一个员工姓名及关联部门名称
     -- 表结构：emp.dept
     -- 连接条件：emp.dept_id = dept.id
     select emp.name ,dept.name from emp,dept where emp.dept_id = dept.id;
     ​
     select e.name ,d.name from emp e,dept d where e.dept_id = d.id;-- (别名版)

    -- 显式内连接（inner join... on..）
    -- 查询每一个员工姓名及关联部门名称
    -- 表结构：emp.dept
    -- 连接条件：emp.dept_id = dept.id
    select e.name ,d.name from emp e (inner) join dept d on e.dept_id = d.id;
    -- 没有领导也需要查询
    select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid = b.id;

  • 外连接：

    • 左/右外连接：查询左/右表all数据，及两张表交集部分数据

      -- 左/右外连接
      select 字段列表 from left/right[outer] join 表2 on 条件..;
      -- 查询emp表all date，和对应部门信息
      -- 表结构：emp.dept
      -- 连接条件：emp.dept_id = dept.id
      select e.*,d.name from emp e left (outer) join dept d on e.dept_id =d.id;
      -- 查询dept表all date，和对应员工信息
      select d.*.e.* from emp e right join dept d on e.dept_id =d.id;

  • 自连接：当前表与自身的连接查询，自连接必须使用表别名

  • select 字段列表 from 表A 别名A join 表A 别名B on 条件..;
    -- 查询员工及其所属领导的名字
    -- 表结构：emp
    -- 本业务思路：把emp看成两张表一张看成领导表，一张看成员工表
    select a.name,b.name from emp a,emp b where a.managerid = b.id;

• 联合查询 -union/union all(去重)

  把多次查询结果合并成新的表

  select * from emp where salary <5000
  union
  select * from emp where age>50;

• 子查询（嵌套查询）

  SQL语句嵌套（select 语句）,子查询外部语句可以是insert,update,delete,select

  select * from t1 where column1 = (select column1 from t2);

  • 标量子查询

    子查询返回单个值

    -- 查询“销售部”所有员工信息
    -- 1、查询该部门id，2、利用id查询员工
    select id from dept where name ='销售部';
    select * from emp where dept_id = 4;
    ------------
    select * from emp where dept_id = (select id from dept where name ='销售部');

  • 列子查询

    子查询返回的结果是一列（可多行）

-- 查询“销售部”，“市场部”all 员工信息
select id from dept where name = '销售部'or name = '市场部';
select * from emp where dept_id in(2,4);
---------------
select * from emp where dept_id in(select id from dept where name = '销售部'or name = '市场部');