第5章、SQL编程与开发实战

第5章、SQL编程与开发实战

1.SQL编程入门也精彩
2.MySQL数据类型与性能
3.MySQL约束
4.SQL编程高级
5.关于表的元数据库应用案例
6.SQL语句分类

1.SQL编程入门也精彩

​ 导入测试库：

​ 文档地址： https://dev.mysql.com/doc/employee/en/sakila-structure.html

​ 下载地址： https://github.com/datacharmer/test_db

​ 导入测试库：

​ cd

​ mysql -uroot -p -S < employees.sql

​ 库操作

​ 增：create database test character set utf8;

​ 删：drop database test;

​ 改：alter database test character set gbk;

​ 查

​ ：show databases; 查看当前有哪些数据库

​ ：show create database test; 查看创建test库的sql语句

​ 表操作

​ 增：create table t1 (id int)engine=innodb charset=utf8;

​ 删：drop table t2;

​ 改

​ ：alter table t1 rename to t2; 修改表名称

​ ：alter table t2 add name varchar(10); 添加新字段

​ ：alter table t2 drop name; 删除字段

​ ：alter table t2 change name title varchar(10); 修改字段名称

​ ：alter table t2 modify title varchar(30); 修改字段类型约束

​ ：alter table t2 convert to character set gbk; 修改字符集

​ ：alter table t2 engine=myisam; 修改存储引擎

​ 查

​ ：show tables; 查看当前库下有哪些表

​ ：show create table t1; 查看创建表的sql语句

​ ：desc t1; 查看表结构

​ ：show table status like ‘t1’ G; 查看表状态

​ 数据操作

​ 增

​ ：insert into t1(id, name, gender) values(1, ‘张三’, ‘m’);

​ ：insert into t1(id, name, gender) values(null, ‘王五’, ‘m’),(null,‘李四’,‘m’);

​ 删：delete from t1 where id=1;

​ 改：update t1 set name=‘ergou’ where id=1;

​ 查

​ ：select * from t1;

​ ：select name from t1;

​ 案例：

​

​ a.use 库; use选择数据库时，是如何影响MySQL性能的？

​ 问题描述：执行use information_schema有明显卡顿的情况出现

​ 原因：在切换数据库时（use mysql），会从元数据库中读取表和字段的信息，为了方便我们使用tab补全命令

​ 解决：

​ 方法一：在mysql -u -p 连接mysql时，带上-A参数

​ 方法二：在配置文件中[mysql] --no-auto-rehash,重启数据库

​ b.delete、truncate、drop的区别

​ 1.delete支持回滚，逻辑删除，不释放表空间

​ 2.truncate不支持回滚，物理删除，释放表空间

​ c.create table t2 like t1; # 快速创建一张与原表结构一致的表

2.MySQL数据类型与性能

​ a. 整型

整型应用细节：

​ 。生产中应该选择哪种类型

​ 在满足业务所需数值范围的情况下，选择占用字节更少的类型。可以节省内存空间

​ 。int(11) 、tinyint(4)与 zerofill(0填充)的问题

​ 只是显示属性，没有实际意义，只表示宽度，不会影响字段的值

​ alter table t1 modify a tinyint zerofill not null;

​ a tinyint(3) unsigned zerofill NOT NULL

​ 负数加zerofill是不被允许的

​ 。int类型转换为bigint注意事项

​ 如果该列是主键，则无法使用onlineddl

​ 如果不是主键，支持onlineddl

​ onlineddl：执行ddl语句，可以支持dml语句，不会阻塞dml语句

​ 。ipv4地址选择什么类型存放

​ 建议使用int类型存储。

​ inet_aton(‘ip’) 将ip地址转为整型select inet_aton(‘255.255.255.255’);

​ inet_ntoa(‘int’) 将整型转为ip地址 select inet_ntoa(‘4294967295’);

​

​ b.浮点型

浮点型应用细节：

​ 。生产中浮点数如何选择？

​ 凡是涉及到资金的，建议选择decimal。

​ 。测试环境中插入随机字符？

​ floor() :向下取整 select floor(‘1.23’);

	ceil()：select ceil('1.23');

	round()：select round(0.54); select round(0.5354, 2); 

​ rand()：取0-1随机值 select rand();

​ 公式：floor(i+rand()(j-i)) 随机取i-j整数 select floor(1+rand()(10-1));随机取1-10

​ insert into t2 values(repeat(‘a’, floor(1+rand()*(30-1))));

​ 。float长度细节

​ float没有定义长度，默认占4字节

​ float§ , 0<=p<=24,占4字节

​ 25<=p<=53,占8字节

​ 。float列不指定精度时，不能做等值查询

​ 等值查询结果为空。

​ 。decimal最大长度

​ decimal(M,D) M最大值65，D最大值30。

​ c.字符串类型

字符串类型应用细节：

​ 。varchar是变长类型，需要额外的1-2个字节

​ varchar(30) <255 额外占用1字节，>255额外占2字节

​ 。char(30)、varchar(30)，在utf8字符集下，占字节数计算

​ char(30) 占用字节数=30*3

​ varchar(30) 占用字节数=30*3+1

​ 。varchar实际长度超过255字节时，会被作为text处理

​ 。varchar长度越长排序时使用更多的内存空间，所以varchar类型的字段长度越短越好

​ 。字段超长可能会造成页溢出（off page）

​ page 16k，字段超过page大小的一半，就会发生off page

​ 。大文本大字段尽量不要去使用，业务上必须有的话，可以存放到独立子表中

​

​ 。对text列进行排序，只会用到max_sort_length字节去作排序

​ 。enum() 枚举类型，细节

​ enum(v_list), v_list最多可以是65535个，如果不超过255占1字节，超过255占2字节。

​ d.日期类型

日期类型应用细节：

​ 。timestamp，当行被更改时，会自动更新为当前时间

​ 5.6.5之后datetime也可以设置自动更新为当前时间

​ alter table t3 modify d datetime default current_timestamp on update current_timestamp;

​

​ 。5.6.4之后底层都是以整型存储，效率高，推荐使用datetime

​ 。日期函数

​ now() 当前时间select now();

​ cast(now() as date) 时间转换 select cast(now() as date);

3.MySQL约束

约束：

​ 约束对象：字段

​ 如何约束：与字段类型配合一起约束字段，如不符合字段类型和约束的写操作，则会被拒绝。

​ 。unsigned/signed

​ unsigned 无符号（<=0）必须与数值类型配合使用

​ signed 有符号（负数 0 正数）默认，必须与数值类型配合使用

​ 。not null ：非空约束（不能为空）

​ 为什么生产中建议使用not null

​ 允许为null的情况下，会有哪些不利的影响？

​ ①.空值的字段添加索引，空值会存放在b+tree最左边，造成索引性能下降

​ ②.造成磁盘和内存空间的浪费。给空值字段添加索引，会多占用一个字节

​ ③.造成count(*) 和 count(name)统计结果不准确

​ 在innodb存储引擎下select count(*)为什么慢

​ mvcc特性，innodb存储引擎只能全表扫描，累积计数

​ 。default ： 给字段指定默认值

​ 。unique ：唯一性约束（字段值不能重复，但是可以为空）

​ 在生产中，给已有数据添加unique，前提必须是已有数据不能有重复值

​ 。auto_increment ： 自增约束，必须是与整型配合使用

​ auto_increment 5.7自增值回溯的问题

​ 原因：5.7版本表的auto_increment是基于内存去存储的，不会持久化到磁盘上，我们在重启数据库之后，内存中的数据被释放，会重新计算作为下一个自增值。

​ 8.0版本解决了自增值回溯的问题。

​ 。primary key ：既不能为空也不能重复

4.SQL编程高级

​

	查询Syntax:
	SELECT
    [* | DISTINCT | col_name | col_name as new_name ]
    FROM table
    WHERE condition
    GROUP BY {col_name} HAVING where_condition
    ORDER BY {col_name } [ASC | DESC]
    LIMIT {[offset,] row_count | row_count OFFSET offset}

查询列

​ 。select * from …

​ select * from emp; 会查询所有的记录行；生产中杜绝使用（select * from emp;会做全表扫描）

​ select * from emp where empno=‘7900’; 会取出所有的字段；生产中杜绝使用

​ 原因1：占用更多的内存空间（缓冲池innodb buffer pool）

​ 原因2：查询条件是普通索引，就会进行回表查询（随机io，查询性能降低）

​ 。select col_name from …

​ select ename, job from emp where empno=‘7900’;

​ 索引覆盖（using index）

​ 。select col_name as new_col_name from …

​ select sum(sal) as t_sal from emp; 取别名

​ 。select distinct col_name from …

​ select distinct job from emp; 去重

where子句

​ 。作用：用于条件过滤，有条件的从表中获取数据

​ 。结构：where col_name 操作符 值

​ where name = ‘ergou’;

​ 。where子句中的操作符

​ 范围操作符：< , <= , > , >= , between…and…，in(set)，not in(set)

​ select * from emp where empno > 7900;

​ select * from emp where empno >= 7900;

​ select * from emp where empno between 7900 and 9999; 查询结果包含两边的值

​ select * from emp where empno in(7900,7902);

​ select * from emp where empno not in(7900,7902); 在生产中杜绝使用；

​ 等值查询：=

​ select * from emp where ename = ‘smith’;

​ 注意事项：

​ 字符串类型、日期类型的sql语句where条件中，一定要加引号，否则将会发生隐式转换，将进行全表扫描，效率极低。

​ explain select * from emp where ename = ‘007’; 使用到索引，索引应用级别为type:ref

​ explain select * from emp where ename = 007;没有使用到索引，将会进行全表扫描，sql效率非常低

​ 不等值查询：!=

​ 生产中不建议使用；如果是普通索引作为查询条件，将进行全表扫描；

​ select * from emp where empno != 7900;

​ 模糊查询：like，not like

​ select * from emp where ename like ‘s%’;

​ explain select * from emp where ename like ‘%n’; 生产中杜绝使用左边模糊查询

​

​ select * from emp where mgr is null;

​ 逻辑操作符：and or not

​ select ename from emp where empno > 7900 and job= ‘clerk’;

​ select ename from emp where empno=7934 or empno=9999;

​ 。where子句查询案例

​ 查询工资高于2000的员工

​ select * from emp where sal > 2000;

​ 查询工资在2000到3500的员工情况

​ select * from emp where sal between 2000 and 3500;

​ 查询员工姓名首字母为S的员工姓名和工资

​ select ename ,sal from emp where ename like ‘s%’;

​

​ 查询empno为 7839,7902,7934的员工情况

​ select * from emp where empno in(7839,7902,7934);

​

​ 查询没有上级的员工情况

​ select * from emp where mgr is null;

​ 查询工资高于500或是岗位为MANAGER，同时还要满足他们的姓名首写字母为J的员工信息

​ select * from emp where (sal > 500 or job = ‘MANAGER’) and ename like ‘j%’;

​

group by … having子句

​ 。作用：对查询结果进行分组统计,having 用于限制分组结果的显示

​ 。结构：group by col_name1, col_name2 having …

​ 。注意事项：select @@sql_mode; ONLY_FULL_GROUP_BY

查询列必须是group by后面的字段或者是聚合函数，出现其他列将报错

​

​ 。group by 子句查询案例

​ 查询每个部门的平均工资和最高工资

​ select deptno,avg(sal), max(sal) from emp group by deptno;

​ 查询每个部门的每种岗位的平均工资和最低工资

​ select deptno, job, avg(sal),min(sal) from emp group by deptno,job;

​ 查询部门平均工资低于2000的部门号和平均工资

​ select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

​

​

order by子句

​ 。作用：用于排序

​ 。结构：order by col_name [asc | desc];

​ select sal from emp order by sal ;

​ 。注意事项：排序字段是索引，如果指定排序为降序，无法使用索引的排序，需要重新排序

​

​ 。order by子句案例

​ 查询工资的从低到高的顺序显示员工的信息

​ select * from emp order by sal asc;

​ 查询按照员工的年工资进行降序排序显示员工的姓名和年工资

​ select ename , sal*12 as y_sal from emp order by y_sal desc;

​

limit子句

​ 。作用：用于限制取出的记录数，也可以用于分页

​ select * from emp limit 2;

​ 分页：limit (pagenow-1)*pagesize,pagesize

​ 。分页查询案例：

​ 按员工号升序查询员工姓名、工资， 每页显示3条记录。请分别显示 第一页，第二页，第三页

​ select ename,sal from emp order by empno asc limit 0,3;

​ select ename,sal from emp order by empno asc limit 3,3;

​ select ename,sal from emp order by empno asc limit 6,3;

聚合函数

​ count()：统计满足条件的记录数

​ sum()：求和

​ avg()：求平均值

​ max()：取最大值

​ min()：取最小值

​ 。聚合函数查询案例

​ 查询所有员工中最高工资和最低工资是多少

​ select max(sal), min(sal) from emp;

​ 查询所有员工的平均工资和工资总和

​ select avg(sal), sum(sal) from emp;

​ 计算共有多少员工

​ select count(*) from emp;

​ 统计部门编号为10的平均工资

​ select avg(sal) from emp where deptno=10;

合并查询

​ union和union all

​ 区别：union将合并的结果去重；union all 不去重

​ select ename from emp where empno=7900 union select ename from emp where empno>=7900;

​ select ename from emp where empno=7900 union all select ename from emp where empno>=7900;900;

多表查询

​ 显示所有员工名字(emp.ename),员工工资(emp.sal)及所在部门的名字(dept.dname)

​ 笛卡尔积：emp_num*dept_num;联表查询时一定要带上关联条件

​ select ename, sal, dname from emp,dept where emp.deptno = dept.deptno;

​ select ename, sal, dname from emp inner join dept on emp.deptno = dept.deptno;

​ select ename, sal, dname from emp join dept on emp.deptno = dept.deptno;

​

​ 内连接查询

​ 语法：

​ select 字段 from 表1 , 表2 where 连接条件;

​ select 字段 from 表1 inner join 表2 on 连接条件;

​ select 字段 from 表1 join 表2 on 连接条件;

​ 外连接查询

​ 左外连查询：select 字段 from 表1 left join 表2 on 连接条件;

​ 右外连查询：select 字段 from 表1 right join 表2 on 连接条件;

​ 案例：显示所有员工名字,员工工资及所在部门的名字

​ select ename, sal, dname from emp left join dept on emp.deptno = dept.deptno;

​ select ename, sal, dname from emp right join dept on emp.deptno = dept.deptno;

子查询

​ 什么是子查询：当一个查询是另一个查询的子部分时，我们叫做子查询

​ 子查询的分类：

​ ①从对象间的关系分类

​ 相关子查询：select * from t1 where a1=(select b from t2 where b=a1);

​ 非相关子查询：select * from t1 where a1=(select b from t2 where b=2);

​ ②从查询结果上分类

​ 单列子查询

​ 如何显示与SMITH同一部门的所有员工?

​ select * from emp where deptno=(select deptno from emp where ename=‘smith’) and ename !=‘smith’;

​ 多列子查询

​ 如何查询与smith的部门和岗位完全相同的所有雇员

​ select * from emp where (deptno,job) = (select deptno,job from emp where ename=‘smith’);

​ 多行子查询

​ 如何查询和10号部门的工作相同的雇员的名字、岗位、工资、部门号

​ select ename,job,sal,deptno from emp where job in(select job from emp where deptno=10);

​ 子查询可以出现在哪些位置？

​ 。查询列位置

​ 必须是单列子查询并且要取别名，否则报错

​ 。from子句位置

​ 必须非相关子查询，否则报错

​ 。where子句位置

​ 范围操作符> < ，必须是单列子查询

​ in 可以是多列子查询

5.关于表的元数据库应用案例

​ a. 统计应用库哪些表没有使用innodb存储引擎

​ information_schema.tables

​ select table_schema,table_name,engine from tables where ENGINE != ‘innodb’;

​ b.如何查看表中是否有大对象

​ information_schema.tables

​ TABLE_ROWS 10w

​ AVG_ROW_LENGTH 10k

​ c.统计数据库大小

​ select table_schema,sum(DATA_LENGTH+INDEX_LENGTH) from tables where table_schema=‘employees’;

​ d.统计表的大小

​ select table_schema,table_name,DATA_LENGTH+INDEX_LENGTH from tables where table_schema=‘employees’;

6.SQL语句分类

​ DML：数据操作语句（insert ,update, delete）

​ DDL：数据定义语句（alter ,create ,drop）

​ DCL：数据控制语句（grant,revoke）

​ DQL：数据查询语句（select）

​