[基本功]MySQL基本操作集合

MySQL

启动与停止

• 计算机右击，管理，服务和应用程序，服务，mysql，右击可改手动改停止

• 以管理员身份打开cmd

• net stop mysql：停止
• net start mysql：启动

登录与退出

mysql自带客户端

• 只适用root用户

cmd管理员

• mysql -h localhost -P3306 -u root -p

  • -h主机
  • -P端口号
  • -u用户
  • -p输入密码

• mysql -u root -p

  • 连接本机

退出

• exit或ctrl+c退出

语法规范

• 不区分大小写，但建议关键字大写，表名、列名小写
• 每条命令最好分号结尾
• 字符最好单引号引起来
• 索引从1开始

单行注释

• #注释文字
• – 注释文字

多行注释

• /* 注释文字*/

常见命令

查看当前数据库版本

• mysql内

  • select version()

• mysql外

  • mysql --version
  • mysql --V

库操作

• show databases

  • 展示数据库

• use test

  • 打开test数据库

• select database()

  • 查看在哪个库

show tables

• 查看有哪些表

  • show tables from 库名

desc 表名

• 查看表结构

show index from 表名

• 查看表中所有索引

select * from 表名

• 查看数据

流程控制结构

顺序结构

分支结构

• if函数

  • 实现简单的双分支
  • 可以作为表达式放在任何位置
  • select if(表达式1，表达式2，表达式3)
  • 如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值

• case结构

  • 类似于switch，一般用于实现等值判断

    • case 表达式|表达式|字段
      when 要判断的值 then 返回的值1
      when 要判断的值 then 返回的值2
      …
      else 要返回的值n
      end；
    • case 表达式|表达式|字段
      when 要判断的值 then 返回的语句1；
      when 要判断的值 then 返回的语句2；
      …
      else 要返回的语句n；
      end case；

  • 类似于多重IF,一般用于实现区间判断

    • case
      when 要判断的条件1 then 返回的值1
      when 要判断的条件2 then 返回的值2
      …
      else 要返回的值n
      end；
    • case
      when 要判断的条件1 then 返回的语句1；
      when 要判断的条件2 then 返回的语句2；
      …
      else 要返回的语句n；
      end case；

  • 特点

    • 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方
    • 可以作为独立语句，只能放在begin end中

• if结构

  • 实现多重分支

  • 只能放在 begin end中

  • 语法

    • if 条件1 then 语句1；
      elseif 条件2 then 语句2；
      …
      [else 语句n；]
      end if；

循环结构

• 只能放在begin end中

• while

  • 语法

    • 【标签：】while 循环条件 do
      循环体；
      end while【标签】；
    • 用到循环控制时应加上标签

• loop

  • 语法

    • 【标签：】 loop
      循环体；
      end loop 【标签】；

  • 可以用来模拟简单的死循环

• repeat

  • 语法

    • 【标签：】 repeat
      循环体；
      until 结束循环的条件
      end repeat 【标签】；

• 循环控制

  • iterate类似continue，结束本次循环，继续下一次
  • leave类似break，跳出当前所在循环

索引

优点

• 提高查询效率，加快检索速度
• 保证每行数据唯一性
• 加速表与表间的链接
• 减少查询中分组与排序时间

缺点

• 创建与维护索引需要消耗一定的系统性能，且随数据量增加而增加
• 索引需要占据额外的物理空间
• 在索引列插入、更新、删除时，对应索引也要动态维护，降低维护速度

存储类型

• B+树

  • 默认

• Hash

索引类型

• 普通索引

  • index/key

• 唯一性索引

  • unique index

• 全文索引

  • fulltext index

    • 只能创建在char、varchar、text类型字段上

• 单列索引

• 多列索引

• 空间索引

  • spatial

    • 目前只有MYISAM存储引擎支持空间索引，而且索引的字段不能为空

语法

• 查看索引

  • show index from 表名

• 删除索引

  • drop index 索引名 on 表名
  • alter table 表名 drop index 索引名

• 创建索引

  • create table时创建索引

    • [fulltext|unique] index 索引名(索引列)

  • create index创建索引

    • create [fulltext|unique] index 索引名 on 表名(索引列)

  • alter table创建索引

    • alter table 表名 add [unique|fulltext] index 索引名(索引列)

      • 索引名可省略

变量

系统变量

• 全局变量

  • 服务器每次启动将为所有全局变量赋初始值，针对所有会话（连接）有效，但不能跨重启

  • 查看所有变量

    • show global variables

  • 查看满足条件的部分系统变量

    • show global variables like ‘%char%’

  • 查看指定的某个系统变量的值

    • select @@global.系统变量名

  • 为某个系统变量赋值

    • set global 系统变量名 =值
    • set @@global.系统变量名=值

• 会话变量

  • 仅针对当前会话（连接）有效

  • 查看所有变量

    • show [session] variables

  • 查看满足条件的部分系统变量

    • show [session] variables like ‘%char%’

  • 查看指定的某个系统变量的值

    • select @@[session.]系统变量名

  • 为某个系统变量赋值

    • set [session]系统变量名 =值
    • set @@[session].系统变量名=值

自定义变量

• 使用步骤

  • 声明

  • 赋值

  • 使用

    • 查看
    • 比较
    • 运算

• 用户变量

  • 针对当前会话（连接）有效

  • 步骤

    • 声明并初始化

      • set @用户变量名=值
      • set @用户变量名=值
      • set @用户变量名：=值

    • 赋值

      • set @用户变量名=值
      • set @用户变量名：=值
      • select @用户变量名：=值
      • select 字段 into @变量名 from 表

    • 使用（查看值）

      • select @用户变量名

• 局部变量

  • 仅在定义它的begin end中有效

  • 必须在begin and中的第一句话

  • 声明

    • declare 变量名 类型
    • declare 变量名 类型 default 值

  • 赋值

    • set 局部变量名=值
    • set 局部变量名：=值
    • select @局部变量名：=值
    • select 字段 into 局部变量名 from 表

  • 使用

    • select 局部变量名

存储过程和函数

存储过程

• 一组预先编译好的sql语句集合，理解成批处理语句

• 适合批量插入、批量更新

• 创建语法⭐

  • create procedure 存储过程名(参数列表)
    begin
    存储过程体
    end

    • 参数列表

      • 参数模式

        • IN

          • 该参数可作为输入，即需要调用方传入值

            • 可省略

        • OUT

          • 该参数可作为输出，即该参数可以作为返回值

        • INOUT

          • 既可以作为输入，又可以作为输出，即既需要传入值，又可以返回值

      • 参数名

      • 参数类型

      • 如果存储过程体只有一句话，begin end可省略

      • 存储过程体中的每条sql语句的结尾要求必须加分号！！！

      • 存储过程的结尾可以使用delimiter重新设置

        • 语法

          • delimiter 结束标记

      • IN stuname varchar(20)

• 调用语法

  • call 存储过程名（实参列表）

    • 调用in模式参数

      • call sp1（‘值’）

    • 调用out模式参数

      • set @name
      • call sp1（@name）

    • 调用inout模式参数

      • set @name=值
      • sp1（@name）
      • select @name

• 存储过程的删除

  • drop procedure 存储过程名
  • 一次只能删除一个

• 存储过程的查看

  • show create procedure 存储过程名

• 举例

  • 空参列表

    • delimiter $
      create procedure mypl()
      begin
      insert into admin…
      end $
    • 调用 call mypl()$

  • 带in模式参数的存储过程

    • create procedure myp2(in beautyname varchar(20))
      begin
      select bo.*
      from boys bo
      right join beauty b on bo.id = b.boyfriend_id
      where b.name=beautyname;
      end $
    • call myp2(‘刘亦菲’)$
    • create procedure myp3 (in username varchar(20),in password varchar(20))
      begin
      declare result varchar(20) default ‘’;

    select count(*) into result
    from admin
    where admin.username = username
    and admin.password=password;

    select result;
    end $

  • 带out模式参数的存储过程

    • create procedure myp5(in beautyname varchar(20),out boyname varchar(20))
      begin
      select bo.boyname into boyname
      from boys bo
      inner join beauty b on bo.id = b.boyfriend_id
      where b.name=beautyname;
      end $
    • set @bname$
    • call myp5(‘小昭’,@bname)$
    • select @bname$

  • 带inout模式的存储过程

    • create procedure myp8(inout a int,inout b int)
      begin
      set a=a2
      set b=b2
      end $
    • set @m=10$
    • set @n=20$
    • call myp8(@m,@n)$
    • select @m,@n$

函数

• 有且仅有一个返回

• 适合做数据处理后返回一个结果

• 创建语法

  • create function 函数名(参数列表) returns 返回类型
    begin
    函数体
    end

    • 参数列表

      • 参数名
      • 参数类型

    • 函数体

      • 肯定有return，没有则报错
      • 如果return未放最后也不报错，但不建议
      • 函数体仅一句话 可省略begin end

    • 使用delimiter设置结束标记

• 调用语法

  • select 函数名（参数列表）

• 案例

  • 无参有返回

    • create fuction myf1() returns int
      begin
      declare c int default 0;
      select count(*) into c
      from employees;
      return c;
      end $
    • select myf1() $

  • 有参有返回

    • create function myf2(empname varchar()20) returns double
      begin
      set @sal=0;
      select salary into @sal
      from employees
      where last_name=empname;
      return @sal
      end $
    • select myf2(‘kochar’) $

• 查看函数

  • show create function myf3

• 删除函数

  • drop function myf3

视图

创建视图

• create view 视图名
  as
  查询语句
• 实现sql语句重用

修改视图

• create or replace view 视图名
  as
  查询语句

  • 存在则修改；不存在则创建

• alter view 视图名
  as
  查询语句

删除视图

• drop view 视图名，视图名

查看视图

• desc myv3

• show create view myv3

  • 末尾加\G格式化成键值对形式

更新视图

• 视图一般用于查询而非更新！！！

• 插入数据

  • insert into myv1 values(a,b,c)

    • 注：原始表也会更新，无字段则添加null

• 修改数据

  • update myv1 set last_name = ‘张无忌’ where last_name=‘张飞’

    • 原始表也会更新

• 删除数据

  • delete from myv1 where last_name=‘张无忌’

• 具备以下特点视图不允许更新

  • 分组函数，distinct，group by,having,union,union all
  • 常量视图
  • select中包含子查询
  • join
  • from一个不能更新的视图
  • where子句的子查询引用了from子句中的表

Transaction Control Language

事务控制语言

• 事务

  • 一个或一组sql语句组成一个执行单元，要么全部执行，要么全不执行，如转账

show engines

• 查看存储引擎

savepoint 节点名

• 搭配rollback to 节点名

  • 回滚

事务的ACID属性

• 原子性Atomicity

  • 事务是一个不可分割的工作单位

• 一致性Consistency

  • 事务必须使数据库从一个一致性状态变换到另一个一致性状态

• 隔离性Isolation

  • 一个事务的执行不能被其他事务干扰

• 持久性Durability

  • 事务一旦提交，其对数据库中数据的改变就是永久性的，接下来的其他操作和故障不应该对他有影响

事务的创建

• 隐式事务

  • 事务没有明显的开启和结束的标记

    • 如insert、update、delete
    • 不包括drop create等

• 显式事务

  • 事物具有明显的开启和结束的标记

    • show variables like autocommit

      • 展示变量

    • 开启事务

      • set autocommit=0

        • 先设置自动提交功能为禁用

      • start transaction

        • 可选

    • 编写事务中的sql语句

      • select、insert、delete、update
      • delete可以回滚；truncate不可以回滚

    • 结束事务

      • commit

        • 提交事务

      • rollback

        • 回滚事务

事务的四种隔离级别

• cmd中查看隔离级别

  • select @@tx_isolation

• 设置隔离级别

  • set session transaction isolation level 隔离级别
  • session表示当前的SQL连接
  • 可更改session为global表示全局的隔离级别

• 问题

  • 脏读：读取未提交的更新数据
  • 不可重复读：多次读取数据不一致
  • 幻读：增加行，读取数据不一致

• READ UNCOMMITTED

  • 读未提交数据

    • 可能出现不可重复读和脏读、幻读

• READ COMMITED

  • 读已提交数据

    • 可能出现不可重复读和幻读

• REPEATABLE READ(默认)

  • 可重复读

    • 可能出现幻读

• SERIALIZABLE

  • 串行化

    • 所有并发问题可避免，但性能十分低下

Data Define Language

库的管理

• 库的创建

  • create database 库名

    • 已经存在则报错

  • create database if not exists 库名

• 库的修改

  • 一般不可修改库名

  • 更改库的字符集

    • alter database books character set gbk

• 库的删除

  • drop database if exists books

• 备份数据库

  • mysqldump -R -U -P 文件路径.sql

    • 登陆前

表的管理

• 表的创建

  • create table 表名(
    列名 列的类型 【(长度) 约束】,
    列名 列的类型 【(长度) 约束】
    )

• 表的修改

  • 添加新列

    • ALTER TABLE author ADD COLUMN salary DOUBLE

      • 默认添加至最后一列
      • 最后可添加 first/after 字段名

  • 修改列名

    • ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME

  • 修改列的类型或约束

    • ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP

  • 删除列

    • ALTER TABLE author DROP COLUMN salary

  • 修改表名

    • ALTER TABLE author RENAME TO book_author

• 表的删除

  • DROP TABLE 【if exists】author

    • 字段什么的都删掉了

• 表的复制

  • 仅复制表的结构

    • CREATE TABLE copy LIKE author

  • 复制表的结构+数据

    • CREATE TABLE copy2 SELECT * FROM author

  • 只复制部分数据

    • CREATE TABLE copy3
      SELECT id,au_name
      FROM author
      WHERE nation=‘中国’

  • 只复制部分字段

    • CREATE TABLE copy4
      SELECT id,au_name
      FROM author
      WHERE 1=2

• 只有表的创建或删除可以添加 if exists容错

数据类型

• 数值型

  • 整型

    • Tinyint

      • 1个字节(8位)
      • 有符号：-128-127
      • 无符号：0-255

    • Smallint

      • 2个字节

    • Mediumint

      • 3个字节

    • Int\integer

      • 4个字节

    • Bigint

      • 8个字节

    • 特点

      • 设置无符号和有符号

        • CREATE TABLE tab_int(
          t1 INT,
          t2 INT UNSIGNED
          )

          • t1有符号，默认
          • t2无符号

      • 如果插入数值超过整型范围，报out of range异常，默认插入临界值

      • 如果不设置长度，会有默认的长度

      • 对于整型，括号内长度是指显示的长度，如不够则填充

        • CREATE TABLE tab_int(
          t1 INT(7) ZEROFILL
          )

          • 用0填充空位，默认无符号

  • 小数

    • 定点数

      • DEC(M,D)或DECIMAL(M,D)

        • M+2字节
        • 最大取值范围与double相同，给定decimal的有效取值范围由M和D决定

      • 精确度要求较高时使用

    • 浮点数

      • float(M,D)

        • 4字节

      • double(M,D)

        • 8字节

    • 特点

      • M:整数部位+小数部位

      • D:小数部位

      • 如果超过范围则插入临界值

      • M和D都可以省略

        • 如果是decimial,则M默认为10，D默认为0
        • 如果是float,double,则会根据插入的数值的精度来决定精度

      • 定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用

• 字符型

  • 较短的文本

    • char（M）

      • 固定长度的字符
      • 相对更耗费空间
      • 效率高
      • M可以省略，默认为1，表示最大长度

    • varchar(M)

      • 可变长度的字符
      • 不那么耗费空间
      • 效率低
      • M不可省略

    • 其他类型

      • binary和varbinary与char和varchar类似，二进制字符串

      • Enum

        • 枚举类型，插入值必须为列表中的一个
        • 不区分大小写
        • CREATE TABLE tab_char(
          c1 ENUM(‘a’,‘b’,‘c’)
          )

      • Set

        • 与Enum类似，但一次可以选取多个成员

          • CREATE TABLE tab_set(
            s1 SET(‘a’,‘b’,‘c’,‘d’)
            )
          • INSERT INTO tab_set VALUES(‘a,b,c’)

    • M为最多字符数(汉字也是一个字符)

  • 较长的文本

    • text\blob(二进制)

• 日期型

  • date

    • 1000-01-01
    • 只有日期

  • datetime

    • 8字节

      • 1000-01-01 00：00：00
      • 9999-12-31 23：59：59
      • 只能反映插入时时区

  • timestamp

    • 4字节

      • 19700101 080001

      • 2038年某时刻

      • 更能反映当前真实时间

        • 和实际时区有关
        • 受Mysql版本影响

  • time

    • -838：59：59
    • 只有时间

  • year

    • 只有年

  • 查看当前时区

    • SHOW VARIABLES LIKE ‘time_zone’

  • 修改当前时区

    • SET time_zone=’+9:00’

      • 东九区

• 选择原则

  • 所选择类型越简单越好，能保存数值的类型越小越好

常见约束

• 添加约束时机

  • 创建表时

  • 修改表时

    • 添加列级约束

      • ALTER TABLE t1 MODIFY COLUMN 字段名 字段类型 新约束

    • 添加表级约束

      • ALTER TABLE 表名 ADD [constraint 约束名] 约束类型(字段名) [外键的引用]

    • 删除约束

      • 删除主键

        • ALTER TABLE 表名 DROP PRIMARY KEY

      • 删除唯一

        • ALTER TABLE 表名 DROP INDEX 约束名

• 约束的添加分类

  • 列级约束

    • 默认，非空，主键，唯一
    • 不可以起约束名
    • 可以在一个字段上追加多个，中间空格隔开，没有顺序要求

  • 表级约束

    • 放在create table括号最后
    • 外键⭐，主键，唯一
    • [constraint 约束名] 约束类型(字段名)
    • 可以起约束名，但对主键无效

• 通用写法

  • CREATE TABLE IF NOT EXISTS stuinfo(
    id INT PRIMARY KEY,
    stuname VARCHAR(20) NOT NULL,
    sex CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    constraint fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
    )

• 约束种类

  • not null

    • 非空约束

      • 保证该字段的值不能为空

        • 如姓名、学号等

  • default

    • 默认约束

      • 保证该字段的值有默认值

        • 如性别

  • primary key

    • 主键约束

      • 保证该字段的值具有唯一性，且非空

        • 如学号，员工编号

      • 0或1个

  • unique

    • 唯一性约束

      • 唯一，但可以为空，但null只能插一个

        • 如座位号

      • 可以有多个

  • check

    • 检查约束(mysql不支持)

      • 如年龄，性别

  • foreign key references

    • 外键约束

      • 限制量表关系，保证该字段值必须来自主表关联列的值

      • 在从表添加外键约束，用于引用主表中某列的值

        • 如学生表的专业编号

      • 主表关联列必须key，即unique或primary key

      • 插入数据时，先插主表再插从表

      • 阐述数据时，先删除从表，再删除主表

      • 有约束时删除主表数据

        • 级联删除

          • 更改从表

            • alter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id) ON DELETE CASCADE

              • 从表中对应位置删除记录

        • 级联置空

          • alter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id) ON SET NULL

            • 从表中对应位置置空

• 修改约束

  • 非空

    • alter table 表名 modify column 字段名 字段类型 [not null]

  • 默认

    • alter table 表名 modify column 字段名 字段类型 [default 值]

  • 主键

    • alter table 表名 add primary key(字段名)
    • alter table 表名 drop primary key(字段名)

  • 唯一

    • alter table 表名 add [constraint 约束名] unique (字段名)
    • alter table 表名 drop index 索引名

  • 外键

    • alter table 表名 add [constraint 约束名] foreign key (字段名) references 主表(被引用列)
    • alter table 表名 drop foreign key 约束名

• 备注

  • 主键，唯一可以是组合的列

标识列（自增长列）

• 创建表时设置标识列

  • 在约束后加上AUTO_INCREMENT

  • SET auto_increment_increment=3

    • 设置步长

  • 可以通过手动插入值改变起始值

  • 标识列必须与主键或UNIQUE等key搭配

  • 一个表最多一个标识列

  • 标识列类型只能是INT，FLOAT，DOUBLE等数值型

• 修改表时设置标识列

• 修改表时删除标识列

Data Manipulation Language

插入语句

• 语法

  • insert into 表名(列名，…）
    values (值1,…),(值1,…),(值1,…)

    • 支持插入多行
    • 支持子查询

  • insert into 表名
    set 列名=值,列名=值

    • 不支持插入多行
    • 不支持子查询

• 插入的是行

• 插入的值的类型要与列的类型一致或兼容

• 不可为null的列必须插入值

• 可以为null的列

  • 列名写着，值写null代替
  • 列名不写，values也不写

• 列的顺序可以调换

• 列数和值的个数必须一样

• 可以省略列名，默认所有列，而且列的顺序和表中列的顺序一致

修改语句

• 修改单表的记录

  • update 表名
    set 列=新值,列=新值,…
    where 筛选条件；

• 修改多表的记录

  • sql92

    • update 表1 别名，表2 别名
      set 列=值,…
      where 连接条件
      and 筛选条件

  • sql99

    • update 表1 别名
      inner\left\right join 表2 别名
      on 连接条件
      set 列=值,…
      where 筛选条件

删除语句

• 删除整行

• 语法

  • 方式一:delete

    • 单表的删除⭐

      • delete from 表名 where 筛选条件 limit[筛选条件]

    • 多表的删除，级联删除

      • sql92

        • delete 别名
          from 表1,标2 别名
          where 连接条件
          and 筛选条件

      • sql99

        • delete 表1的别名,表2的别名
          from 表1 别名
          inner\left\right join 表2 别名
          on 连接条件
          where 筛选条件

  • 方式二:truncate

    • truncate table 表名

      • 清空数据，不能加where条件

• 区别⭐

  • 如果要删除的表中有自增长列

    • 用delete删除，再插入数据，自增长列的值从断点开始
    • 用truncate删除，再插入数据，自增长列的值从1开始

  • truncate比delete效率更高

  • truncate不能添加where条件，delete可以

  • truncate删除没有返回值，delete删除返回受影响行数

  • truncate删除不能回滚，delete删除可以回滚

Data Query Language

基础查询

• select 查询列表

  • 表中字段

  • 常量值

    • 字符型和日期型常量值必须单引号引起来
    • 数值型不需要

  • 表达式

  • 函数

  • 查询结果是虚拟表格

• from 表名

• 起别名

  • select a as name1
  • select last_name 姓,first_name 名 from employees
  • 若有特殊符号，则加双引号（单引号）

• 去重

  • select distinct department_id from employees
  • 仅允许一次一个

• +加号只能作为运算符

  • 数字加数字为数字

  • 字符加字符

    • 字符型内为数字则变成数值
    • 字符内为字符则变为0

  • 只要有null则结果为null

• 拼接

  • select concat(last_name,first_name) as 姓名 from employees
  • 数值型会自动转化成字符型

• 判断字段或表达式是否为空

  • ifnull(commission_pct,0)
  • 如果null，返回指定值；否则返回原本值

• 判断是否为null

  • isnull(commision_pct)
  • 如果null，返回1；否则返回0

条件查询

• select 查询列表 from 表名 where 查询条件

  • 条件表达式

    • < <> = >= <=

  • 逻辑表达式

    • & || !
    • and or not

  • 模糊查询

    • like

      • where last_name like ‘%a%’

        • 通配符

          • %任意多个字符，包含0个

          • _任意一个字符

          • \转义

          • escape可自定义转义字符

            • where last_name like ‘_KaTeX parse error: Expected group after '_' at position 1: _̲%' escape '’

    • between and

      • 包含临界值
      • 必须前小后大
      • where employee_id between 100 and 120

    • in

      • where job_id in (IT_PORT,AD_VP)
      • in列表值类型必须一致或兼容
      • 不支持通配符

    • is null

      • where commission_pct is null
      • =或<>不能判断null值

    • is not null

    • 安全等于<=>

      • =
      • 判断null

排序查询

• order by 排序列表 【asc|desc】

  • ASC升序，可省略

  • DESC降序

  • 支持别名排序

  • 支持表达式排序

  • 按函数排序

    • length()

  • 支持多字段排序

  • 除limit子句，排序子句放在最后

分组查询

• select column,分组函数
  from table
  where condition
  group by group_by_expression
  order by column

  • 查询列表必须是分组函数和group by后出现的字段

• having 可在group by 后筛选

• where可在group by前筛选

• 分组函数做条件，肯定放在having中

连接查询

• sql92标准

  • 只能内连接

    • 等值连接

      • 为表起别名比较方便
      • 一般需要为表起别名，起完别名不能用原表名
      • 两个表的顺序可以调换
      • 多表等值连接的结果为多表的交集部分
      • n表连接，至少需要n-1个连接条件
      • 多表顺序没有要求
      • select e.last_name,e.job_id,j.job._title
        from employees e,jobs j
        where e.job_id=j.job_id

    • 非等值连接

    • 自连接

• sql99标准

  • 不支持全外连接

    • select 查询列表
      from 表1 别名 [连接类型]
      join 表2 别名
      on 连接条件
      where 筛选条件
      group by 分组
      having 筛选条件
      order by 排序列表

      • 连接类型

        • 内连接

          • 等值连接

            • inner

              • select last_name,department_name,job_title
                from employees e
                inner join departments d on e.department_id=d.department_id
                inner join jobs j on j.job_id=e.job_id
                order by department_name desc

            • inner可以省略

              • 连接条件放On后面，筛选条件放where后面

          • 非等值连接

          • 自连接

        • 外连接

          • 用于查询一个表中有，另一个表没有的记录

          • 查询结果为主表中所有记录

            • 从表中有和它匹配的，显示匹配的值
            • 从表中没有和它匹配的，显示null
            • 查询结果=内连接结果+主表有而从表没有的

          • 左外连接

            • left [outer]

              • select b.name
                from beauty
                left outer join boys bo
                on b.boyfriend_id=bo.id
                where bo.id is null

          • 右外连接

            • right [outer]

          • 全外连接

            • full [outer]

        • 交叉连接

          • cross

            • 笛卡尔乘积

子查询

• 出现在其他语句内部的select语句，称为子查询或内查询

• 内部嵌套其他select语句的查询，称为主查询或外查询

• 按功能分类

  • 标量子查询

    • 结果集只有一行一列

  • 列子查询

    • 结果集只有一列多行

  • 行子查询

    • 结果集有一行多列

  • 表子查询

    • 结果一般为多行多列

• 按子查询出现的位置分类

  • select后面

    • 只支持标量子查询

  • from后面

    • 支持表子查询

      • 将子查询结果充当一张表，要求必须起别名

  • where或having后面

    • 支持标量子查询(单行)⭐

      • 标量子查询一般搭配单行操作符

        • < >= <= = <>

    • 支持列子查询(多行)⭐

      • 一般搭配多行操作符使用

        • in、any/some、all

    • 支持行子查询（用得少）

    • 特点

      • 子查询放在小括号内
      • 子查询一般放在条件的右侧
      • 子查询的执行优先于主查询

  • exists后面（相关子查询）

    • 支持表子查询

      • exists(完整的查询语句)
      • 结果：0或1

分页查询

• 当要显示的数据一页显示不全，需要分页提交sql请求

• 特点

  • limit语句放在查询语句的最后

  • 要显示的页数page,每页的条目数size

    • offset=(page-1)*size

• select 查询列表
  from 表
  limit offset,size

  • offset:要显示条目的起始索引

    • 起始索引从0开始，默认从头开始，该情况可省略该参数

  • size:要显示的条目个数

union联合查询

• 将多条查询语句的结果合并成一个结果

• 查询语句1
  union
  查询语句2

• 要查询的结果来自多个表，且没有直接联系，但查询的信息一致时

• 在下面行添加

• 特点

  • 要求多条查询语句的查询列数一致
  • 要求多条查询语句的查询的每列类型和顺序最好一致
  • union默认去重
  • union all 可以包含重复项

常见函数

调用

• select 函数名(实参列表)

单行函数

• 字符函数

  • length()

    • 获取参数值的字节个数

  • concat()

    • 拼接字符串

  • upper()、lower()

  • substr(),substring()

    • select substr(‘abcdefgh’,5)

      • efgh

    • select substr(‘abcdefgh’,2,4)

      • 从第2个开始截取4个字符长度，bcde

  • instr()

    • select instr(‘杨不悔爱上了殷六侠’，‘殷六侠’) as out_put

      • 返回第一次出现的起始索引，7，找不到则返回0

  • trim()

    • select trim(’ 张翠山 ') as out_put

      • 去前后的空格

    • select trim(‘a’ from ‘aaaa张aaa翠aa山aaa’)

      • 去前后的a

  • lpad()

    • select lpad(‘殷素素’,10,’*’) as out_put

      • 左边添加字符*直至字符数达到10，多了会从右边截断

  • rpad()

  • replace()

    • select replace(‘张无忌爱上了周芷若’，‘周芷若’,‘赵敏’)

      • 替换每一个

• 数学函数

  • round()

    • 四舍五入

    • round(1.65)

    • round(1.567,2)

      • 小数点保留2位

  • ceil()

    • 向上取整

  • floor()

    • 向下取整

  • truncate()

    • 截断

    • truncate(1.65,1)

      • 小数点保留1位

  • mod()取余

    • mod（a,b）

      • a-a/b*b

  • rand()

    • 获取0-1间随机数，不取1

• 日期函数

  • now()

    • 返回当前系统日期+时间

  • curdate()

    • 返回当前系统日期，没有时间

  • curtime()

    • 返回当前时间，不包含日期

  • 获取指定的部分 年 月 日 小时 分钟 秒

    • year(now())
    • year(‘1998-1-1’)
    • month(now())
    • monthname(now())
    • day\hour\minute\second

  • str_to_date(‘9-13-1999’,’%m-%d-%Y’)

    • 字符转换成日期
    • %Y四位年份
    • %y 两位年份
    • %m 月(01,02,…12)
    • %c月(1,2,…12)
    • %d日(01,02,…)
    • %H小时(24小时)
    • %h小时（12小时）
    • %i分钟
    • %s秒

  • date_format()

    • 日期转换成给定格式

  • datediff()

    • select datediff(‘2017-10-1’,‘2017-9-29’)

• 其他函数

  • select version()
  • select database()
  • select user()
  • password(‘字符’):返回该字符的密码形式
  • MD5(‘字符’)也是一种加密形式

• 流程控制函数

  • if函数

    • if-else三元效果
    • select if(10>5,‘大’,‘小’)

  • case函数

    • case 要判断的字段或表达式
      when 常量1 then 要显示的值1或语句1;
      when 常量2 then 要显示的值2或语句2;
      ……
      else 要显示的值n或语句n;
      end

      • 值无分号，语句有分号
      • switch case效果

    • case
      when 条件1 then 要显示的值1或语句1;
      when 条件2 then 要显示的值2或语句2;
      ……
      else 要显示的值n或语句n;
      end

      • 值无分号，语句有分号
      • if else if else效果

分组函数（统计函数、聚合函数、组函数）

• 做统计处理

• 都忽略null值

• 可以和distinct搭配实现去重运算

  • select sum(distinct salary),sum(salary) from employees

• 和分组函数一同查询的字段要求是group by后的字段

• sum(salary)

  • 处理数值型

• avg()

  • 处理数值型

• max()

  • 支持各种类型

• min()

  • 支持各种类型

• count()

  • 计算非空值个数

  • select count(salary) from employees

  • select count(*) from employees

    • 效率高

  • select count(1) from employees