表的操作

创建一张学生表

create table student(
    sid int,
    sname varchar(20)
);

字段

字段的添加(追加 | first | after）

alter table student add sex bit(1);   # 追加
alter table student add email varchar(20) first;  # 最前面
alter table student add birthday date after sid;  # 在什么后

查看表结构

desc student;

字段修改

alter table student change email semail varchar(20) after sid;
alter table student modify semail varchar(50);

销毁字段

alter table student drop semail;

表

修改表名：

 rename table student to stu;
 alter table stu rename to student;

修改表注释

alter table student comment '学生表';

清空表

truncate table student;

表的销毁

drop table student;

主键约束

在创建表的时候添加

    create table student(
      sid int primary key,
        sname varchar(20),
        brithday date
    );


        create table student(
      sid int,
        sname varchar(20),
        brithday date,
        primary key(sid)
    );

联合主键

    create table student(
      sid int,
        sname varchar(20),
        brithday date,
        primary key(sid,sname)
    );

表已经存在

alter table student add constraint PK_SID primary key(sid);
alter table student add constraint PK_SID primary key(sid,sname);   # 联合主键

销毁主键

 alter table student sid drop primary key;

字段约束(unique)，唯一约束字段值不能重复

    create table student(
        sid int primary key,
        sname varchar(20) unique,
        brithday date
    );

    create table student(
        sid int primary key,
        sname varchar(20),
        brithday date,
        unique(sname)
    );

    # 修改表结构添加唯一约束
    alter table student add CONSTRAINT UN_SNAME unique(sname);

    # 销毁：
    drop unique index UN_SNAME on student;

主键自增(auto_increment)

 create table student(
        sid int primary key auto_increment,
        sname varchar(20),
        brithday date,
        unique(sname)
    );

域完整性 – 保证列的数据正确性

  # 类型约束
  # 非空约束(not null)
  # 默认值(default)
        create table userinfo(
       uid int primary key auto_increment,
       username varchar(20) not null,
       sex bit(1) default 1
      );
# 引用完整性
 # 保证表中字段值的有效
 # 外键约束(foreign key)
  # 表中外键字段的取值需要依赖于另张表的主键的取值

CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  UNIQUE KEY `sname` (`sname`),
  KEY `FK_CID` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4


CREATE TABLE `classroom` (
  `cid` int(11) NOT NULL,
  `cname` varchar(25) NOT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

    # 添加外键
    alter table student add constraint FK_CID foreign key(cid) references classroom(cid);
    # 删除外键
    alter table student drop foreign key FK_CID;

增删改查

查看建表语句

show create table student;

添加（返回影响的记录数） 值是字符串要加引号

    # 代表多有字段都添加值（值的顺序和表的字段的顺序一样）
    insert into userinfo values (1,'ls',1);

    # 给部分字段添加值（值顺序和前面的字段顺序一致）
    insert into userinfo (username,sex) values ('ww',1);

    # 批处理(添加多条记录)
    insert into userinfo (username,sex) values ('oo',1),('pp',1),('tt',0);

    # 复制表
        # 复制表结构
        create table user select * from userinfo where 0;
        # 把表数据插过来
        insert into user select * from userinfo;
            #  <==>  等价上面两句
        create table user select * from userinfo;

修改

update userinfo set sex    = 0 where username = 'ww';
update userinfo set sex    = 1 where uid = 5;

删除(如果没有条件是删除整个表的数据)

    delete from userinfo where uid = 5;
    delete from user;  # 清空表数据
    truncate table userinfo;  # 清空表数据


    # delete 不会重置自增，逐行删除
    # truncate 清空表，重置自增




select * from user;

select * from student;

drop delete truncate区别：

    # drop 删除表及数据
    # delete 逐行删除 
    # truncate 清空表数据

查询语句DQL

复制表

insert into user select * from userinfo;

查询user表中的所有的记录所有字段

select * from user;

    # * ：通配符
    # 表达式/字段

算术运算符

select 1+1;       #2
select 1-1;            #0
select 1*1;            #1
select 5/2;            #2.5000
select 5%2;            #1
select 3/0;            #null
select 5 div 2;    #取整

比较运算符

#结果?  true--> 1    /    false ---> 0
select 1=1;   #1
select 1!=1;  #0
select 1<>1;  #0

逻辑运算符 and or !

# 结果?  true--> 1    /    false ---> 0
select 1>2 and 1<2;   # 0
select 1>2 or 1<2;    # 1
select !(1>2);        # 1

位运算符 & | ^

# &:按位与  有一侧为0结果为0
   select 3 & 2  # 2
    # |:按位或  有一侧为1结果为1
  select 3 | 2  # 3
    #    ^:按位异或  两侧数据一样取0,两侧数据不一样取1
  select 3 ^ 2  # 1

查看字符编码

show variables like '%char%';


# character_set_client    utf8mb4
# character_set_connection    utf8mb4
# character_set_database    utf8mb4
# character_set_filesystem    binary
# character_set_results    utf8mb4
# character_set_server    utf8mb4
# character_set_system    utf8
# character_sets_dir    /www/server/mysql/share/charsets/

DQL———————————————————–

# select 子句   #表达式/字段,结果集中展示的内容
# from 子句     #表/结果集/视图,数据的来源
# where 子句    #条件(单个条件,组合条件)
# order by 子句  #排序
# group by 子句  #分组
# having 子句    #分组之后条件
# limit 子句     #限制结果查询(mysql方言)  分页

查询所有字段的数据

select * from emp;

查询部分字段

# 查询员工的编号和名称
select empno,ename from emp;

单一条件查询 where

# 查询20号部门的员工的所有的信息
select * from emp where deptno = 20;

组合条件

# 查询20号部门工资大于2000的员工信息
select * from emp where deptno = 20 and sal > 2000;
# 查询员工编号为7788，7521，7369的员工信息
select * from emp where empno = 7788 or empno = 7521 or empno = 7369;

查询范围

# 查询员工工资在1000到2000之间的员工信息
select * from emp where sal >=1000 and sal <= 2000;
select * from emp where sal between 1000 and 2000;

集合查询

# 查询员工编号为7788，7521，7369的员工信息
select * from emp where empno in (7788,7521,7369);

别名(字段,表达式,结果集,表…) [as] 别名

# 查询员工工资提升5%之后的样子
select ename,sal*1.05 sal from emp;
select e.ename from emp e;

去重

# 查询所有的职位
select distinct(job) from emp;

空判断 is

# 查询没有奖金的员工信息
select * from emp where comm is null;
# 查询有奖金的员工信息
select * from emp where comm is not null;

排序 order by 字段 [asc|desc]

# 查询员工工资排序-升序
select * from emp order by sal;
select * from emp order by sal asc;
# 查询员工工资排序-降序
select * from emp order by sal desc;
# 查询员工工资排序-降序,并列时empno排序
select * from emp order by sal desc,empno desc;        #并列时第二排序

模糊查询 like %:0-多位 _:代表一位字符

# 查询名字中包含S的员工信息
select * from emp where ename like 's%';      # s开头
select * from emp where ename like '%s';        # s结尾
select * from emp where ename like '%s%';        # 包含s
# 如果查询第二位为L的员工信息
select * from emp where ename like '_l%';

限制结果查询 limit index,length

# 查询前5条记录
select * from emp limit 5;
select * from emp limit 1,5;

函数

数学函数

select abs(-10);
select ceil(10.5);        # 相上取整
select floor(10.5);        # 向下取整
select rand();    # 取随机数
select round(12.46);    # 四舍五入
select round(12.46,1);    # 四舍五入

字符函数

select length('abc');        # 获取字符串长度
select length(ename) from emp;

日期函数

select now();        # 获取当前2020-12-24 13:45:22
select current_date();     # 获取当前 2020-12-24
select CURRENT_TIME();    # 获取当前 13:47:05
select CURRENT_TIMESTAMP();        # 获取当前时间戳

select YEAR(NOW());        # 获取当前年份
select YEAR('2018-08-08');  # 获取年份
select MONTH(NOW());        # 获取当前月份
select DAY(NOW());        # 获取当前是几号

日期计算函数

# 两周前
select DATE_ADD(NOW(),interval - 2 WEEK);        # 不能使用组合
select DATE_ADD('2019-03-02',interval 5 day);

select last_day('2016-02-03');    # 获取给定日期月的最后一天

聚合函数

select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select sum(sal) from emp;

计算总记录数

select count(*) from emp;
select count(1) from emp;

求字段的个数(非空)

select count(comm) from emp;

分组函数

# 每个部分的平均工资
    # 将数据划分为更小的小组,然后进行计算
    # group by 字段:根据字段值的不同划分为多个小组,每个小组返回一条记录
    # 在select子句中能够出现聚合函数,只能出现分组字段
    select deptno,avg(sal) from emp group by deptno;
# 平均工资大于2000的部门编号及平均工资
select deptno,avg(sal) avg from emp group by deptno having avg >= 2000;

        # having: 和where作用一致,但是出现在group by之后,主要分组之后再次进行过滤
        # 1. where只能出现在group by之前
        # 2. where中不能出现聚合函数

加密函数

    select md5('root');

流程函数

    # 若果expr1是真，返回expr2，否则返回expr3
    # IF(expr1,expr2,expr3)
    select if(1>2,1,0)


    # 如果expr1不是NULL，返回expr1，否则返回expr2
    # IFNULL(expr1,expr2)
    select ifnull(comm,0) from emp;

    # 如果value是真，返回result1，否则返回default
    case when [value1] then[result1] else[defaut] end

    # 如果expr等于value1，返回result1，否则返回default
    case [expr] when [value1] then[resultq] else[default] end

    # >=90  A     <=90 and >=70 B       <70 C
        select sname,
        case
         when score >= 90 then 'A'
         when score < 90 and score >= 70 then 'B'
         else 'C'
        end level
        from student;

        select sname,
        case
         when score >= 90 then 'A'
         when score < 90 and score >= 70 then 'B'
         when score < 70 then 'C'
        end level
        from student;

多表查询

查询所有员工的姓名emp及所在的部门的名称dept（分布在多张表）

内连接

    # 1.与连接顺序没有关系（没有主从表（主次））
    # 2.多张表都能匹配的数据才能出现在结果集中
    select ename,job from emp,dept where emp.deptno = dept.deptno;  # 方言
    select * from emp a INNER JOIN dept b on a.deptno = b.deptno;        # 标准

    # 1.前提：关联字段名称必须是一样，而且是等值连接
    #   优点：自动去重字段
    select * from emp inner join dept using(deptno);

外连接

    # 1.与连接顺序有关（主从表之分，驱动表/附属表）
    # 2.以主表为基准，依次在主从表中寻找关联的记录，如果匹配则关联并展示在结果中，否则以null填充
    select * from emp a left JOIN dept b on a.deptno = b.deptno;
    select * from dept a left JOIN emp b on a.deptno = b.deptno;

自连接

    # 查询员工及其领导的姓名
    select a.ename 员工,b.ename 领导 from emp a, emp b where a.mgr = b.empno;

嵌套查询（子查询）

    # 查询编号为7788的员工所在的部门名称
    # 1.连接查询
    select dname from emp,dept where emp.deptno = dept.deptno and empno = 7788;
    # 2.其他方式（分步）-- 单行子查询：子查询返回的结果单行单列
    select dname from dept where deptno = (select deptno from emp where empno = 7788);

    # 多行子查询：子查询返回的结果是多行
    # 薪水>2000的员工所在的部门的名称
    select dname from dept where deptno in (select distinct deptno from emp where sal > 2000);
    #    in
    # any：    =any：相当于in        >any： 大于最小值         <any：小于最大值
    # all：    >all:    大于最大值    <all：小于最小值


    # 查询30号部门工资次高的的员工信息
    select * from emp where sal = (select max(sal) from emp where sal  <any(select distinct sal from emp where deptno = 20 ORDER BY sal desc));

案例：

    # 1.查询薪水超过所在部门平均工资的员工信息
    # 采用连接
    select a.* from emp a,(select deptno,avg(sal) pj from emp  GROUP BY 1) b where a.deptno = b.deptno and sal > pj;
    # 采用子查询
    select * from emp a where sal > (select AVG(sal) from emp b where b.deptno = a.deptno);
    1.主查询传递deptno给子查询
    2.子查询根据传递的deptno查询出所在部门的平均工资返回给主查询
    3.主查询根据子查询返回的部门平均工资来处理后续

    # 2.薪水>2000的员工所在的部门的名称
    select dname from dept where deptno in (select distinct deptno from emp where sal > 2000);

    # in和exists的区别
    # in先执行子查询，在执行主查询;exists先执行主查询;
    #    exists子查询不返回具体结果，返回true值出现在结果集，否则不出现。
    select dname from dept where exists(select * from emp where sal > 2000 and emp.deptno = dept.deptno);
    # exists考虑的是匹配的问题：
    # 先执行主查询,将主查询的记录依次交给子查询进行匹配,如果能够匹配则子查询返回true,主查询的结果显示在结果集；否则不显示。

联合查询

    # 查询20号部门或者工资>2000的员工的信息
    select * from emp where deptno = 20 or sal > 2000;
    # union: 并集，所有的内容都查询，重复的显示一次
    select * from emp where deptno = 20  union  select * from emp where sal > 2000;
    # union all: 并集，所有的内容都显示，包括重复的
    select * from emp where deptno = 20  union all  select * from emp where sal > 2000;
文档更新时间: 2021-02-25 10:51   作者：李延召