MySQL

0 数据库

• DB：database，数据库，用于存储数据；DBA：数据库管理员

• DBMS：database manager system，数据库管理系统，如MySQL、PostgreSQL，用于管理DB

• SQL：Structured Query Language，结构化查询语言，用于与DBMS通信

• 关系型数据库：数据存储为表格式，表彼此关联协作，易提取数据

• 数据库三范式-设计依据

1NF	表必有主键，列遵从原子性即列不可再分
2NF	先满足1NF，然后其他列必完全依赖主键而不能部分依赖 如存在多个列做一个联合主键的表，列依赖主键中的某个列，此时就应该拆分为多个表
3NF	先满足2NF，然后依赖是直接依赖而不能存在传递依赖 如有一个外键存在了，该表不能再有外键对应那张表中的其他列属性（从空间角度避免产生冗余信息和内存浪费） 反（第三）范式设计：特殊情况需要提高查询效率（读多写少），如上述的冗余列字段被频繁查询，则就违反3NF而添加该列字段，查询时通过该列直接查询（单表查询），而不需要通过外键连表查询

• 数据库设计步骤：

  • 需求分析

  • 概念结构设计

  • 逻辑结构设计

  • 物理结构设计

  • 数据库实施

  • 数据库运行与维护

• 完整性约束：

  • 实体完整性：主键不为空

  • 参照完整性：外键为空或另一个表的主键

  • 用户定义完整性：SQL约束列表

---

1 MySQL

1.1 MySQL

1.1.1 基本概念

• 基本关系（表）：

  • 实际表，对实际存储数据的逻辑表示

  • 查询表：查询结果

  • 视图表：由基本表或视图表导出的虚表

• 关系操作：查query，插insert，删delete，改update

• 数据类型：

  • 时间类型：

    • date：年月日的日期，time：时间，year：年，dateTime：日期+时间，取值1000-9999，timestamp：日期时间的时间戳，取值1970-2038

    • 日期格式：%y-年，%m-月，%d-日，%h，%i，%s

  • 字符串类型：

    • varchar：变长字符串，根据实际数据长度动态分配空间，速度慢，省空间；char：定长，速度快但可能造成空间浪费，适用于固定数据，如性别；指定n表示字符数而非字节数

    • clob：字符大对象，最多4GB，用于存储超过255个字符的，如文章

    • blob：二进制对象，常用于图片、声音、视屏等流媒体数据；tinyBlob,mediumBlob,longBlob

    • text：长文本；tinyText,mediumText,longText

    • blob和text类型的值在性能上存在问题，尤其在删除操作中，数据表中遗留大量“空洞”碎片，以后填补这些“空洞”的记录的插入操作性能受到影响；可定期适用optimize table功能对表的碎片进行处理，提高性能（测试删除表数据，发现文件大小没变，直到对碎片进行处理后）

  • 数字类型：

    • 严格数值类型：tinyInt，smallInt，mediumInt，int（integer），bigInt；小括号内可指定显示宽度，指定后不足位数的在前面填满；默认int(11)；zerofill表示以0填充；可选属性unsigned无符号，它设置字段值为非复数，即下限为0，上限为原来取值的2倍；整数类型还有可有属性auto_increment自增，设置唯一标识符或顺序值

    • float单精度，double或real双精度，（dec）decimal或numberic定点数：近似数值类型；定点数在数据库中以字符串形式存储，比浮点数更精确，适用于货币数据；后可接（M,D）即（精度，标度）进行限制，表示以一共显示M位数，其中小数位数D位；浮点数插入数据时超过限制的四舍五入且不报错，定点数则在默认模式下报警告，传统模式下报错而无法插入；无限制则按实际精度值显示；定点数默认（10,0）

    • bit：存放位数据；（M）可指定存放多位二进制数据，M取1-64；如bit(1)限制插入1位二进制，而如果插入十进制的2，即二进制的10，则插入失败

1.1.2 数据查询DQL

• data query language；此类也可归于DML下

• 查：select 查询列表 from 表名 / 视图名列表 / (selcte子句) [as..] [条件查询]

  • 查询列表：单列，多列，常量，* 或all

  • distinct：针对结果，把该列的值去重；位于所有字段的最前；多个字段时表示联合字段后去重

  • 常量：包括单引号包裹的字符串、数学计算表达式如age*10

• 别名：as + 别名或省略as；别名只用于显示，不会修改原表列名；别名中有空格则一定用单引号括起来（字符串统一用单引号，双引号在oracle中不合法）；针对表名、列名或中间查询结果；推荐使用，可读性更强

• SQL执行顺序：区别于写SQL的顺序：7-8-1-3-2-4-5-6-9-10

  1. from：来自哪些表，结果为笛卡尔积

  2. on：表上的字段，主表保留

  3. join：关联哪些表

  4. where：条件

  5. group by：分组

  6. having：分组后过滤

  7. select：结果

  8. distinct：去重

  9. order by：排序

  10. limit：返回数据量限制；缺省的参数1为起始偏移量（如第5条开始的后面3条数据） ，参数2为条数；对应第pageNo页显示limit (pageNo - 1) * pageSize,pageSize

• 条件查询：

  • [ where 条件表达式 ]：根据where条件从from指定集中选出元组并按照select的要求形成结果

  • [ group by 列名 [having 条件表达式]]：分组查询，即先分组，再处理每一组数据；having限制（聚集函数作用于每一组）满足条件的组才输出，对分类后（聚合后，区别于where是针对聚合前）的结果再进行过滤，条件优先使用where，即先过滤，再处理

  • [ order by 列名 [asc / desc]]；按不同多个字段排序，字段值一样时再根据第二个要求的字段排序；只有一个排序字段，值重复时无序排序

  • 条件运算符：=，> ，>=，<=，<，!=或<>，between.and（包含临界值），in，is null（没有任何值，不能用=），&&，||，！，and（优先级高），or以及not配合，like（字符模糊匹配，%任意长度字符，_任意单个字符）

    • \可转义或自定义转义符，如like '%A%%' escape 'A'即将A做转义字符，从而查询出%

    • 找出字符串中包含_的：where userName like '%\ _%'

  • limit offset,rowCount：限制显示，偏移量offset默认从0开始，可不写

  • between.and当操作数类型相同时，等同于>=min and <=max，当类型不同时，遵循类型转换原则转换后再比较

  • 实例：

    1. 按照员工工作，求每种工作的平均工资：select job,avg(salary) from table group by job；按照job分组，每一组job中的每一条记录的salary求和，再除以该组的大小得到平均值；该语句的select出现其他如select name则查询无意义，在oracle中直接报错；select后一般为参与group by的字段及函数

    2. 联合分组：查询每个部门，不同工作的最高工资：select deptNo,job,max(salary) from table group by deptNo,job，这不必再用嵌套查询

    3. 查询每个部门的最高工资，显示大于3000的：select deptNo,max(salary) from table group by deptNo having max(salary) > 3000；这种方式效率不高，改为将数据先过滤再处理，即select deptNo,max(salary) from table where salary > 3000 group by deptNo；注意不是select deptNo,max(salary) from table where max(salary) > 3000 group by deptNo ；而如果要求查询平均工资，则where不能使用，不得不使用having

    4. 查询每种工作的平均工资，显示平均工资大于3000的，且除总统的工作外，降序排序：思路：先过滤即where job <> '总统'，再分组即group by job，分组后过滤即having avg(salary) > 3000，得到结果即select job,avg(salary) avgSalary，最后排序即order by avgSalary

    5. 有些数据设计如大类为10，小类分1001，1002等，设计需求为查询某个子类如1003，而如果没有1003就查它的大类

       • where userName = '1003' or (substr(userName, 1 ,2) = '10'); // 不截取的在前，or存在短路特性

• 函数：

函数类型	函数及说明
数值函数	1、count()：统计个数；count(*)统计总行数，count(column)统计该列的值不为null的记录行数 2、sum()：求和 3、avg()：求平均 4、max()，min()：求最大最小 前五个为分组函数或多行处理函数或聚合函数，需要先分组，处理多行数据为一个数据，未分组的默认以整张表为一组，自动忽略null；分组函数不能直接使用在条件查询where子句中，只能用于select后或having后面，因为分组函数类似分组查询，而SQL执行顺序先where后group by，后select 5、abs()：求绝对值 6、cell()，floor()：求向上向下取整 7、mod()：求模 8、rand()：求0-1随机数；配合其他函数，可以取得任意范围内的随机数，如0-100的随机整数：ceil(100乘以rand()) 9、round(n,count)：四舍五入保留指定位数，count值为0-保留整数，1-一位小数，2-两位小数，-1-十位数，-2百位数 10、truncate(n,count)：截取为指定位数
字符串函数	1、concat(str1…strn)：字符串的连接；也可以用+，但当两边都是数字时，+表示计算；当有一边是字符串而另一边是数字时，尝试转为数字，转换失败则是字符串拼接，转换成功则是计算；当表达式中存在null时结果为null 2、left/right(str, count)：返回字符串左/右边的指定个字符；count为null时不返回任何字符串 3、insert(str, fromIndex, count, newstr)：将str的从指定位置处后面指定个字符替换为newstr 4、lpad/rpad(str, count, fillstr)：从左/右填充指定个字符 5、ltrim/rtrim/trim(str1,str2)：去除str2中的str1；去除空格 6、repeat(str, count)：重复指定次 7、replace(str, oldChar, newChar)：替换所有oldChar 8、strcmp(str1, str1)：比较 9、substr(str, fromIndex, count)：截取，起始下标从1开始 10、lower / upper(str)：转换大小写（单行处理函数，即一行一行的处理） 11、length(str)：长度；注意UTF8下中文占3字节 12、instr(str1,str2)：返回str2在str1中首次出现的位置 实例：将字符串首字母大写：select concat(upper(substr(name,1,1)),substr(name,2,length(name) -1))
日期函数	1、curdate() / curtime() / now()：当前日期、时间、日期时间 2、year / month / week(date)：年、月、年的第几周；moth可换为monthname 3、hour / minute(time)：时、分 4、date_format(date, fmt)：示例：将包含时分秒的时间截取为年月日：DATE_FORMAT(create_time, '%Y-%m-%d')，或者left(create_time, 10) 5、date_add(date,intervalExpressionType)：返回与指定date相差指定表达式结果的date，负数则表示在date之前的date 6、datediff(date1, date2)：两个日期的天数差；如查询今天的数据：select * from tableName where datediff(‘2020-12-30’,now())=0，昨天的则=-1 7、字符串转date：str_to_date(str, format表达式即日期格式)：如str_to_date('2020-01-20','%y-%m-%d') ；当日期字符串样式为严格的默认短日期格式%y-%m-%d'时（长日期后面是%h:%i:%s），可省略日期格式参数而自动转换；java中日期格式为yyyy-MM-dd HH:mm:ss 8、extract(util from date)：返回时间的单独部分，如年、月、日；util可取YEAR,DAY,MONTH等枚举
流程函数	1、if(value,t,f)；如果value真，返回t，否则返回f 2、ifnull(value1,value2)：如果value1不为null则返回，否则返回value2；注意很多运算要对null的判断，因为null参与的运算结果一定为null；因此此函数常用于替换null值 3、case [expression] when [value1/expression] then [result1]…else [default] end：如果[表达式结果等于]value1真，返回result1，否则default；可以多个when…then
其他函数	datebase()：数据库名 version()：版本 user()：用户名 inet_ation(ip)：ip地址的数字表示inet_ntop(num)：数字表示的IP地址 password(str)：加密 MD5()：MD5加密

• 联合查询join：

  • 举例表A和表B：

id(A)	name(A)	id(B)	age(B)	a_id
1	A1	1	B1	1
2	A2	2	B2	2
		3	B3	2
		4	B4

查询类型	说明	语句	查询结果
内连接：条件为=时即取交集（共有的），条件为！=时即取差集（两者合并后减去共有的）	1、等值/非等值连接：选出满足条件的元组数据并拼接成在一起；非等值有between等 2、自连接：常见于树形结构数据，如员工名和领导名查询，如select a.name as '员工名',b.name as '领导名' from user a join user b on a.uersId = b.parentId 3、多张表连接时罗列多个join，且任意连接类型	1、SQL92语法：from 表1,表2 where 表1.字段=表2.字段；语法结构不清晰，表连接条件和筛选糅杂在一起 2、SQL99语法：from表1 inner join 表2 on 连接条件；推荐使用，因为结构清晰，表连接条件独立，如果需要进一步筛选，再加where	1、无条件内连接，则结果为笛卡尔积的6条数据，即1A11B11，1A12B22，1A13B32，1A14B4，2A21B11，2A22B22，2A23B32，2A24B4 2、id等值内连接，则结果为1A11B11，2A22B22，2A23B32；尽管有了条件限制，但过程中匹配的次数依然是笛卡尔积；为了避免比较次数，尽量使用join的方式，且起别名和明确查询列
外连接：主表所有数据 + 连接表不满足的数据且变为null	1、左连接：选出左表全部数据和右边表中不匹配的数据 （内连接则没有这些数据，只有满足查询的数据） 2、右连接 3、笛卡尔积和主表的确定，形成数据的一对一，一对多关系	1、from A left join B on A.key = B.key 2、from A right join B on A.key = B.key 3、from A left join B on A.key = B.key where B.key is null：A表在B表中没有关系的数据 4、from A right join B on A.key = B.key where A.key is null：B表在A表中没有关系的数据	1、1A11B11，2A22B22，2A23B32 2、1A11B11，2A22B22，2A23B32，--4B4- 3、null 4、--4B4-“
嵌套/子查询	1、在select、from、where或having中嵌套查询块 2、常配合in，not in，=，!=，>，<，any，some，all，exists，not exists等 3、在from子句中的查询结果可看做一个临时表，表即可起别名；不建议使用位于select后的子查询，它必须要求返回结果唯一	嵌套条件： 1、=,>,<：比较运算要求内存查询返回的一定是单个值 2、in：结果集；当子查询记录数唯一时可用=代替in	1、查询比最低工资高的员工：select name,salary from user where salaru > (select min(salary) from user);
查询结果联合	1、对查询的结果的并union，交intersect，差except 2、此种方式效率最高，减少了匹配次数，如三张各有10条记录的表，笛卡尔积为1000，但拼接则为表1表2的100 + 表1表3的100 3、要求联合的结果集的列数必须相同；oracle还要求列的数据类型相同	union all将结果全部直接合并；union会进行一次distinct	查询姓名为A或者B的： 1、select * from user where name = ’A' or name ='B' 2、优化版：where name in('A',' B') 3、再优化：where name =‘A‘ union (select * from user where name='B')

---

1.1.3 数据操作DML

• data manipulation language；针对数据，增insert，删delete，改update 数据并检查和保证数据完整性；区别于DDL，DML仅操作表内部的数据

操作类型	语法
增	inser into 表名 (列名列表) values(值列表)；不指明列名列表时，注意values列表顺序；可空字段、非空但有默认值字段和自增字段可不出现在列名列表中而自动设值 ；default指定默认值；逗号隔开值列表，实现批量插入，提高效率
删	1、delete from 表名 [where ...]：每次删一行数据；删除操作被作为事务保存在日志，日后可回滚；没有条件则删除表中所有数据 2、truncate table 表名：一次性永久删除表中所有行而不记录单个行删除操作；不会激活触发器；速度快；不回滚；适用于大表 3、drop：删除数据和表；隐式提交，不回滚；同truncate属于DDL语言；速度最快
改	update 表名 set 列名=值(列表) where ...；可以同时更新多个表中数据

1.1.4 数据定义DDL

• data definition language；针对表结构，用于定义数据段、库、表、列、索引等数据库对象，常用关键字建create，删drop，改alter；操作时添加if exists tableName以保证健壮性

• 将MySQL安装目录bin指定到环境变量path（最前面）中，或者在bin目录下进入doc；操作MySQL：不区分大小写

• mysql [ -h localhost -P 3306] -u用户名 -p密码：客户端连接服务；本机连接可省参数

  • 连接成功后显示信息：

    • 命令结束符：；或者\g

    • your MySQL connection id is X：客户端连接id，X表示目前为止连接次数，每个新连接自动加1

    • MySQL服务器的版本：版本号+beta/standard即测试版或标准版

    • help或\h命令显示帮助内容；\c命令清除命令行buffer

• create database dbName;：创建数据库

  • 安装MySQL是系统默认创建的数据库：

    • information_schema：存储系统中的数据库对象信息/元信息，即数据的数据，如用户表信息，列信息，权限信息，字符集信息，分区信息，索引名等；这是虚拟数据库，其中的表全是视图

    • SCHEMATA：所有数据库信息，show databases的结果来源

    • TABLES：所有表的信息，包括表类型，表引擎，创建时间等

    • STATISTICS：所有表索引信息

    • cluster：存储系统的集群信息

    • mysql：存储系统的用户权限信息

    • test：测试数据库

• use dbName：使用指定数据库

• drop database dbName：删除数据库

• show tables：显示该数据库下所有数据表；可直接show tables from databaseName

• create table tableName(列名 数据类型 列级完整性约束条件,...)：创建表

• desc tableName：查看表定义信息；show create table tableName \G：查看创建表的详细信息，包括表定义信息，engine存储引擎，charset字符集等；\G使得记录能按字段竖向排列

• drop table tableName：删除表；可选属性[restrict/cascade]，默认前者，有限制即若有依赖对象则表不能删除；后者无限制，即依赖对象全被删除

• alter table tableName ...：修改表，省略号可以为：

  • modify [column] columnName columnType：修改字段类型，如果是修改自增长，则后面加auto_increment

  • add [column] columnName columnType：增加字段，默认最后

  • drop [column] columnName：删除字段

  • change [column] oldColumnName newColumnName [columnType]：修改字段名[并修改字段类型]

  • rename [to] new tableName：修改表名

  • 可选项：后面加first或after columnName：改变字段排列的顺序

• set names gbk：乱码问题

• show columns from tableName：查看表中字段信息

• exit或ctrl+c：退出；\c：终止SQL输入

• 注释：#或--；多行注释用/* */

• 模式 = 表 + 视图 + 索引

• 完整性约束：

  • 针对列，保证数据的有效性和完整性

  • primary key主键：即unique又not null；写在列后为列级约束；在所有列后再指定则为表级约束

    • auto_increment：主键自增策略

    • 该列自动创建索引

  • unique唯一

    • 联合唯一（表级约束）：unique(name,email)，区别于各自唯一

    • 该列自动创建索引

  • not null非空

  • foreign key（a_id） references A(id)外键：

    • 约束该列取值的合法性，如B（子表）中a_id列作为外键，引用的值为A（父表）表中id存在的值

    • 由于依赖关系，删除表数据的顺序与创建表数据的顺序相反，java开发中注意增删的业务逻辑

    • 被引用的父表的列可不为主键，但必须具有unique性；可为null

• 视图、存储过程、函数不被删除但失效

• 快速生成大量测试数据：(原文档地址：https://www.fujieace.com/mysql/test-data.html)

  # 建表
  CREATE TABLE user(
  id serial,
  name  varchar(20) ,
  createtime  datetime  ,
  age  int(11))
  ENGINE=MYISAM
  DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
  AUTO_INCREMENT=1
  ROW_FORMAT=COMPACT;

  # 建存储过程
  delimiter $$
  SET AUTOCOMMIT = 0$$
   
  create  procedure test()
  begin
  declare v_cnt decimal (10)  default 0 ;
  dd:loop
          insert  into user values
          (null,'用户1','2010-01-01 00:00:00',20),
          (null,'用户2','2010-01-01 00:00:00',20),
          (null,'用户3','2010-01-01 00:00:00',20),
          (null,'用户4','2010-01-01 00:00:00',20),
          (null,'用户5','2011-01-01 00:00:00',20),
          (null,'用户6','2011-01-01 00:00:00',20),
          (null,'用户7','2011-01-01 00:00:00',20),
          (null,'用户8','2012-01-01 00:00:00',20),
          (null,'用户9','2012-01-01 00:00:00',20),
          (null,'用户0','2012-01-01 00:00:00',20)
                  ;
          commit;
          set v_cnt = v_cnt+10 ;
              if  v_cnt = 10000000 then leave dd;
              end if;
          end loop dd ;
  end;$$
   
  delimiter ;

  # 执行存储过程生成数据
  call test;

1.1.5 数据控制DCL

• data control language；用于控制不同数据段直接的许可、安全权限和访问级别，常用关键字赋权grank，回收权限revoke

• 创建用户并赋予对某个数据库的select，insert的权限：grant select,insert on dbName.* to userName identified by 123

• 数据备份

  • 导出：mysqldump databaseName>D:\ ...

  • 导入：source D:\ ...sql

• 帮助的使用：

  • 遇到问题如查询操作语法，当前版本某字段类型的取值范围，支持的函数及举例等，可以使用帮助文档

  • ?contents：起始命令，罗利所有可供查询的分类，然后可继续针对每个分类，使用?类名，如?date types，进一步查看，层层递进

  • 快速查看语法：如？show可快速查阅show的功能

---

1.1.6 索引

• 帮助高效获取数据的排好序的数据结构；数据库不仅维护数据，还维护一个满足特定查找算法的数据结构即索引，它以某种方式指向数据，以此为基础实现高级查找算法（简单理解为给某一列建立了索引且以二叉树做数据结构，那么该列的数据由原来的类似数组的结构依次遍历和对比，变为一个二叉树的数据结构，在树节点上对比后过滤掉一部分数据以减少遍历和对比）

• 每列都能被索引，对相关列使用索引是提高select操作性能的最佳途径；每种存储引擎（而不是服务器层）对每个表至少支持16个索引，总索引长度至少256字节

• （MySQL支持的4种） 索引数据结构：

  • full-text全文：针对记录值按升序或降序存储的记录创建的顺序索引文件；数据的逻辑删除即保证数据的有序性，方便查找；用于全文搜索，仅限于char,varchar,text列且是整个列而不支持前缀和部分索引

  • B+树：B树变种，平衡多叉树(balenced)，MySQL使用的；支持全关键字、等值、关键字范围、关键字前缀查询

    • 例：where条件为根据=id，=name，=age的条件组合查询，explain输出可见type为const即常量，key为索引名即优化器选择该组合索引进行扫描；当where条件为范围查询，type为range表示优化器选择范围查询，选择另一个索引来加速访问查询条件同时存在等值和范围查询，但等值条件也为索引列，结果为只查询索引字段，此时type为range，同时优化器选择使用索引来加速，extra为using index

    • 最佳左前缀法则：索引了多列，则查询从索引的最左前列开始，且不跳过索引的中间列，否则索引失效；如idx_user_name_age_sex，条件查询必须至少包含name列；如果有sex列，则age列也不能少（但这只是sex列失效，name索引依然有效）

    • 覆盖索引扫描：只访问索引列就能获取想要的结果的查询，即查询列与索引列一致，而不是select * ；extra显示using index

    • 因为B-Tree的结构关系，like中以%为前缀时索引失效，为后缀时不会失效；此时使用全文索引来解决全文检索问题

  • hash散列：查找快，使用场景简单，适用于key-value查询，支持等值查询（=或！=）而不能范围查询 ；仅Memory支持

  • R-tree：MyISAM和InnoDB默认；除了模糊查询中以通配符开头的情形都可用此类索引

• 索引类型：

  • 单值：一个索引只包含一列，一个表可以多个单值索引，但建议不超过5个

  • 唯一：索引列的值必须唯一，可为null；主键是一种唯一索引

     create [ unique,cluster ] index inx_user_name on user(name); # 在user表的name列上建立名为inx_user_name的索引
     alter user add [ unique ]index inx_user_name on (name);
     drop index [ indexName ] on user;
     show index from user\G #\G以key-value形式展示而非表格

  • 复合：用多列建立的一个索引

     create index inx_user_name_age on user(name, age);

  • 索引的特殊应用：

    • 合成索引：根据大文本字段如text或blob类型数据的内容，建立一个散列值并存储为单独的一列，通过检索散列值查询这条记录（只能使用精确匹配而不能>=等范围搜索或模糊查询），散列值可用MD5()函数，SHA1()、CRC32()或自定义程序逻辑来计算；这还可以避免不必要的查询，如select *就可能毫无目的的在网络上传输大量值

      create table T(id varchar(100), context blob, hash_value varchar(40));
      insert into T values(1, repeat('内容',2), md5(context));
      insert into T values(3, repeat('另一些内容',3), md5(context));
      # 查询
      select * from T where hash_value = md5(repeat('内容',2));

    • 前缀索引：MySQL不支持函数索引，但支持前缀索引； 只为该字段的前N列创建索引，而不必对整个列索引，用来对大文本字段的模糊查询提高性能，如create index index_blob on T(context(100));查询时where context like('内容%')；%不能放在最前面；如此对context的前100个字符进行模糊查询；以context为条件的查询时索引inex_blob将被使用：explain select * from T where context='';前缀索引在order by或group by时无法使用

    • 分表：将text或blob数据移动到第二长表中，原数据列换成固定长度数据行格式，也能减少主表中的碎片产生，减少主表select *的数据传输

• 缺点：在维护索引尤其是对记录CURD时增加开销，增加空间即索引文件来存储索引信息在磁盘，降低写操作性能，修改表时更新或重构费时

• 适合建立索引的：

  • 出现在where子句或连接子句而不是select后的列；条件查询用不到的字段不建立索引

  • 列基数越大的，如性别列基数太小就不适合

  • 表中记录太少、数据重复且分布均匀的如值为true和false的，不建立索引

  • 主键，外键；但自增主键不适合用二叉树结构（单边增长）

  • 频繁作为查询条件的；频繁更新的字段和增删改的表不可建立索引；如购物平台上预显示的几个查询条件字段，都被建立了索引

  • 统计、分组、排序的字段使用索引列

  • 使用InnoDB的表，记录默认按一定的顺序保存；若明确主键，则按主键顺序保存；否则若有唯一索引，则按该列顺序保存；否则自动生成内部列并按该列保存；以主键或内部列访问速度最快；表中有多列唯一时，选最常访问的作为主键，且主键数据类型尽量短

• 索引失效：虽然存在索引，但不被优化器选择和使用，转向全表扫描；应防止索引失效

  • 做！=，<，>，in null，is not null，or，like等操作时，范围之后的索引失效；范围之后如a = 3 and b > 4 and c =5中使用了a,b索引，但c失效；or前面有索引，后面没有索引，后面的查询一定会走全表扫描，因此前面的索引扫描就多余了

  • 不在索引列上做任何计算、函数、类型转换、null值判断等操作

  • 存储引擎不能使用索引中范围条件右边的列

  • 字符串类型不加单引号时索引失效，底层会发生数据类型的隐式类型转换，一定要为该字符串用引号包裹

1.1.7 视图

• 将查询结果以另一种视角来看待；区别于查询得到中间表，针对视图的DML语言会同时影响到原表的数据；作用类似java开发中抽象出一个公用的工具类；可看做存储于内存的虚拟表

• create view viewName as (DQL)

1.1.8 存储过程与函数

• 事先经过编译，并存储在数据库中的一段SQL语句的集合；调用存储过程和函数可简化开发，减少数据传输

• 存储过程可以没有返回值，而函数必有；存储过程的参数可以为in入参，out出参和inout；函数的参数类型只能in

• 同视图一样，各种操作需要对应的权限，如创建的权限

1.1.9 触发器

1.2 安装与配置

• 书籍参考《深入浅出MySQL》

1.2.1 Windows环境

• mysql安装：https://baijiahao.baidu.com/s?id=1629661608981614271&wfr=spider&for=pc

• 配置：my.ini，实质是可视化安装过程中的各种选项配置

  • port：3306

  • dateDir：默认为安装目录下的/Data下

  • characte-set-server：utf8

  • max-connecton：最大连接数

• 启动（检查是否已安装MySQL服务） ：

  • 任务管理器-服务，或cmd命令service.msc直接打开服务，后启动

  • 命令启动：以管理员身份运行，net start/stop MySQL（或具体名称）

1.2.2 Linux环境

• Windows环境安装过程中的勾选包含了数据库字符集设置、加入环境变量、超级管理员用户和密码设置；这些在Linux环境都需要手动命令去设置

• 检查环境是否安装过mysql：rpm -qa|grep -i mysql；检测命令在没有检测到东西时，没有任何反应，否则会显示信息，如版本号

• cat /proc/version查询系统版本

• 下载： 下载Linux版本的mysql（rpm版本一般为一个server和一个client的rpm文件，gz版本是一个压缩包），用FTP等工具将文件传送到Linux服务器上， 放于opt目录下（虚拟机-设置-选项-共享文件夹，开启后会共享虚拟机所在的主机的共享了的文件，在mnt/hgfs下即可看到文件；复制改文件，没有权限，则用sudo su切换为root超级管理员；切换普通用户 su userName）；方式2是直接在Linux服务器上使用命令下载：右键得到下载地址，，使用wget url命令安装

• 安装（gz文件的方式）：解压后移动到/usr/local/mysql下，并设置mysql目录权限为mysql用户和mysql组(id mysql查看mysql用户和组)：chown -R mysql.mysql /usr/local/mysql；-R表示对目录的递归操作；查看该用户和组是否存在，或者mysqladmin --version查看版本，检查是否安装成功

• （初始化数据库？？？：scripts/mysql_install_db --user=mysql，可是没有script目录和data目录呀？移除mariadb-libs库文件：yum remove mariadb-libs）

• 启动：ps -ef|grep mysql 查看服务是否启动；复制support.files下的mysql.server到/etc/init.d下，使其受service管理；启动service mysql start

1.2.3 连接

• 使用Navcat等可视化工具连接MySQL服务；使用DOC操作连接本机MySQL服务

• 设置允许远程连接：使用其他IP连接MySQL服务时，可能报错：Host XXX is not allowed to connect to this MySQL server。这表示MySQL配置了不支持远程连接；**登录所在的MySQL服务：mysql -u root -p，后执行use mysql；select host from user where user='root';可看到主机配置信息为localhost，代表只有localhost可以访问；将其值改为%通配符，即允许所有IP可访问（慎用）：update user set host = '%' where user = ' root';用flush privileges刷新生效

1.3 SQL优化

1.3.1 SQL执行信息

• show [session,global] status：此命令获取服务器状态信息，或在操作系统上用mysqladmin extended_status；可选属性默认session表示session级即当前连接的统计结果，global级即自上次数据库启动至今的统计结果

  • show status like 'Com_%'：Com_xxx表示每个xxx语句执行的次数，如select,insert,update等；通过这些参数，可以大致了解到当前数据是以插入删除为主还是以查询为主；对于事务型应用，Com_commit和Com_rollbak可以了解提交和回滚的频率，如果回滚太多，说明应用设计存在问题

  • Connections：试图连接服务器次数；Uptime：服务器工作时长；Slow_queries：慢查询次数

  • Handler_read_key：索引使用次数；太低则说明性能改善不高

  • Handler_read_rnd_next：读取下一行的请求数；大量全表扫描时此值很高，说明索引不正确或查询没用到索引，应补救

• 定位执行效率较低的SQL：查看慢查询日志查看执行效率低的SQL，或者用show processlist命令查看正在执行的线程信息，分析线程状态和锁表情况后进行优化

• 用Explain分析低效SQL的执行计划：

  • SQL语句前使用此关键字或desc，可以模拟执行查询，但输出信息为执行查询时的处理方式等信息，包括select语句执行过程中的表如何连接，连接顺序等，从这些信息可以分析查询性能并针对性优化

    • id：表的读取顺序；值相同，则表示table由上到下顺序执行，如from A,B时两表的id相同；值不同，越大的优先级越高，如嵌套查询中嵌套的内存查询的表的id大于外层查询的表

    • select_type：操作类型

      • simple：单一表查询或无子查询

      • primary：外层查询

      • subquery：子查询中的第一个select

      • derived：中间表的查询

      • union：联合查询中的第二个或以后的查询语句

      • union result：union合并后的查询

    • table：输出结果集的表

    • type：查询类型即在表中找出目标记录所需的方式；以下类型性能依次降低

      • null：无需访问表或索引，能直接得到结果，如select 1

      • const/system：单表查询中以主键或其他唯一索引为条件；表中仅有一条数据时为system

      • eq_ref：唯一索引查询，结果只有一条数据，如连表查询中连接条件用主键或其他唯一索引

      • ref：非唯一索引的扫描，或唯一索引的前缀索引扫描，如=，!=

      • range：索引范围扫描，如<，>，>=，between等

      • index：遍历整个索引树的全扫描，如select id

      • all：如select *即为all的全表扫描

    • possible_keys：查询时可能用到的索引

    • key：实际使用了的索引，null表示未使用索引

    • key_len：索引字段的最大可能长度

    • rows：扫描的行数

    • extra：执行情况的说明，包含在其他列中不便显示但有用的额外信息

  • 新版本的expain extended，explain partitions

• 通过show profile分析SQL

• 两个简单实用的优化方法：

  • 定期分析和检查表：analyze [local或no_write_to_binlog] table tableName列表

  • 定义优化表：optimize [local或no_write_to_binlog] table tableName列表；常针对频繁删除或修改数据的表，使碎片合并以消除空间浪费

  • 分析，检查，优化，修改等都对表锁定，因此注意在数据库不繁忙时才操作

1.3.2 查询优化

• 优化insert：

  • 尽量多commit()即分段批量提交

  • 批量SQL操作，如一个insert对应多个value值列表，删除id in(1,2,3)而不是三条删除语句

  • 数据来自文件，使用数据导入而不用很多的insert语句

• 优化order by：

  • MySQL的两种排序方式：一是通过有序索引顺序扫描直接返回有序数据，explain分析显示using index，不需要额外的排序；二是using Filesort，即通过返回数据再进行排序，它有相应的排序算法，如将数据在sort_buffer_size系统变量设置的内存排序区中排序，若内存不足，就在磁盘上对数据分块，每个块排序后又合并结果

  • 减少额外排序，即通过索引直接返回有序数据，即where和order by使用相同的索引，且order by的顺序和索引顺序一致，且都是升序或都是降序

• 优化group by：默认情况下会对group by的字段排序；如果想避免排序，可指定order by null禁止排序，进而避免Filesort

• 优化嵌套查询：尝试用连接查询join替换子查询，在连接查询中存在索引时效率甚至更好；因为子查询会在内存中创建临时表来存储中间数据，连接查询减去了这个步骤

• 优化分页查询：limit 100,20会对前120条记录做排序，即全表扫描，但是根据要求仅仅返回101到120的20条记录，前100被舍弃；如一般查询为：select id，name from user limit 100,20；改写：先按照索引来分页后再返回结果，即不要全表扫描：select a.id,a.name from user a inner join (select id from user limit 100,20) b on a.id = b.id；当只需要一条数据时，使用limit 1

• 存储过程减少数据库访问次数；特殊时候truncate替换delete

• in中的常量存储在一个数组且排好了序，in中包含的数值不应过多；in适合外表大而内表小的情况（in先执行子查询，即以内表为驱动）；exists反之（以外表为驱动表，先访问）；推荐not exists而不是not in，not in可能存在逻辑问题

• 对连续的数字，尽量用between

• union all 代替union

• 主表应小；inner join会自动以数据少的表为驱动表，推荐

• 分段查询：查询范围过大时，分段查询后，结果合并

• 数据导入：

  • 针对MyISAM表：使用load data infile filePath从文件导入数据到非空的表中时(filePath是文件绝对路径)，先关闭索引关联：alter table tableName disable keys；导入后再开启索引：alter table tableName enable keys；有明显效率提升

  • 针对InnoDB表：

    • 因为表记录按主键升序保存，所以导入的数据按主键排好序再导入

    • 导入前执行set unique_checks=0关闭唯一性校验；导入后=1设置回来

    • 建议导入前执行set autocommit=0关闭自动提交；导入后=1设置回来

1.4 数据库对象优化

• 数据类型优化：

  • 使用procedure analyse()对表进行分析，获取对该表的列的数据类型的优化建议；select * from tableName procedure analyse();获取的信息包括某列的数据的Min_length,Max_length,Avg_value_or_avg_length；如果确定要修改，则使用alter修改数据库对象

• 拆分表以提高访问效率：

  • 垂直拆分：将表中的主码和经常使用的列划分为一个表，主码和其他不常用的列划分为另一个表；缺点是产生了冗余列，查询时需要连表查询

  • 水平拆分：根据一列或多列数据的值，把数据行划分为多个独立的表中，如一个表存储最近三个月数据，一个表存储三个月以前的数据；查询时需要union

• 反规范化：不一定严格按照数据库设计规范来，根据实际应用，可能需要反规范化设计；如需要连表才能查询name，那么在另一个表中增加name字段，这样查询时只需要查询一个表；如此，查询速度加快，但修改速度降低甚至数据完整性出现问题，字段产生冗余；因此反规范化需要权衡利弊；增加的冗余列可以是其他表的某些列，或是其他表的数据经过计算后的结果；数据完整性可在同一事务中保证，但还是容易遗漏和难维护

• 增加中间表：在数据量很大的表中，需要使用到某些字段，则新建一个中间表，此表字段保存使用到的列，甚至新增辅助列；对源表的列的操作转移到中间表上

2 事务

2.1 基本特性

• Transaction：数据库针对DML操作的最小执行单元，一个完整的业务逻辑，包含多个DML语句，这一组操作封装为事务；用以保证数据安全性

• 事务属性：隔离，传播，只读（用于只有查询的业务，提高效率），超时（事务等待（如访问数据被其他事务加锁处理） 时间，超时后不执行或报异常），异常

• 4个ACID特性：

  特性：	内容：
  A-原子性	事务不可分割，要么都执行，要么都不执行；回滚后数据状态与事务执行之前一致
  C-一致性	事务从一种一致的状态到另一种一致的状态
  I-隔离性：解决事务并发产生的问题	并发访问 / 多线程存在的问题： 1、脏读：事务2修改但未提交数据，之后事务2回滚了，事务1读到脏数据 2、不可重复读：事务1读取数据后，事务2修改数据并提交，事务1再次读取而数据不一致 3、虚幻读：事务1读取数据后，事务2修改数据并提交，事务1虽然可重复读取了，但和第一次读取时一致，而不是最新数据，即读取了虚假数据 4个隔离级别： 1、读未提交read_uncommitted：事务1可以操作还未提交的事务2；隔离性最低，不能避免脏读等所有问题，并发性高 2、读已提交read_committed：事务1不可以操作未提交的事务2；最常用；防止脏读 3、读可重复repeatable_read（MySQL默认）：防止脏读、不可重复读 4、串行化serializable：严格的事务隔离，事务串行执行即不能并发；级别最高，避免所有问题但效率低
  D-持久性	提交后永久存储；数据库操作完成后都应该commit

  • 查看隔离级别：select @@tx_isolation

2.2 传播行为

• Propagation：解决事务嵌套执行问题，内部嵌套的小事务影响外部大事务，如丧失其原子性

  传播行为	内容
  PROPAGATION_REQUIRED（默认）	若外部存在事务，则加入到该事务中执行，自身事务属性失效；若没有事务，则新建事务
  PROPAGATION_SUPPORTS	若外部存在事务，则加入到该事务中执行；否则以非事务方式执行
  PROPAGATION_MANDATORY	外部必须存在事务，融合，否则抛出异常
  PROPAGATION_REQUIRES_NEW	总是开启新事务并执行，若外部存在事务，则挂起该事务，新建事务执行完后唤醒该事务
  PROPAGATION_NOT_SUPPORTED	不能在事务中执行，若外部存在事务则将事务挂起
  PROPAGATION_NEVER	不能在事务中执行，存外部在事务则抛出异常
  PROPAGATION_NESTED	若存在事务，则嵌套事务执行；若当前没有事务，则新建事务

  • 默认值为0-5；java类TransactionDefinition新添了自定义的初始值为6；PROPAGATION_NESTED—事务存在，则嵌套事务执行

2.3 事务控制

• 事务执行过程中，每一条DML操作被记录到事务活动日志文件

• 提交commit：将数据持久化到数据库，并清空日志文件；提交成功标志着所有DML执行成功

  • mysql默认支持自动提交

  • 执行start transaction;即关闭自动提交机制，在需要提交时提交，在提交前回滚

• 回滚rollback：将DML操作全部撤销，并清空日志文件；回滚成功标志着所有DML执行失败

  • 回滚到上一次的提交点

3 大数据

• 数据库分库分表策略：

  1. 业务分库：按照业务将数据存储在不同数据库，如用户数据数据库，商品数据数据库，订单数据数据库；然后数据库集群；缺点：无法做连接查询（join），无法通过同一事务做统一，设备增加

  2. 主从复制与读写分离：主从数据库集群，一主，多从，主库负责写和少量的读，从库只负责读；主库数据复制到从库；服务将操作分发到对应的节点上；（区别主备数据库，备只备用，主库有问题的时候，备库才工作）

  3. 数据库分表：当一个表的数据（如某个应用的用户表）特别大时，则分表（表还可能位于不用数据库）；垂直拆分：按列拆分，如某一列存储的数据量特别大时可拆出来，或查询时不是很重要和频繁查询的字段拆出来；水平拆分：按照行拆分，如某表保存前多少行数据（优点为平滑扩充表，缺点是如果主键采用自增策略，则分段大小的选取很难最合理化，后续数据因为增删查改后分布可能不均匀，造成存储空间浪费；其他策略：hash算法策略，与自增优缺点相反；雪花算法/分布式主键ID生成器：保证不同表中主键不同，数据按主键有序）

4 SQL语句实例

• 创建数据库：CREATE DATABASE database-name

• 删除数据库：drop database dbname

• 创建表：create table tbName(col1 type1not null primary key, col2...)

• 根据已有的表创建新表：

  • 方式1：create table tab_new like tab_old

  • 方式2：create table tab_new as select col1,col2… from tab_old definition only

• 删除新表：drop table tabname

• 增加列：alter table tabname add column col colType

• 添加主键： alter table tabname add primary key(col)

• 删除主键： alter table tabname drop primary key(col)

• 创建索引：create [unique] index idxname on tabname(col….)

• 删除索引：drop index idxname；索引不可更改，想更改必须删除重新建

• 创建视图：create view viewname as select statement

• 删除视图：drop view viewname

• 选择查询数据：select * from table1 where 范围

• 插入数据：insert into table1(field1,field2) values(value1,value2)

• 删除数据：delete from table1 where 范围

• 更新数据：update table1 set field1=value1 where 范围

• 模糊查找：select * from table1 where field1 like ’%value1%’

• 排序：select * from table1 order by field1,field2 [desc]

• 总数：select count as totalcount from table1

• 求和：select sum(field1) as sumvalue from table1

• 平均：select avg(field1) as avgvalue from table1

• 最大：select max(field1) as maxvalue from table1

• 最小：select min(field1) as minvalue from table1

---

• 高级查询运算：

  • UNION：运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行来自 TABLE1或TABLE2

  • EXCEPT：运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行

  • INTERSECT：运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行

  • 使用运算词的几个查询结果行必须是一致的

  • 使用外连接：

    • left （outer） join：左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行： select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

    • right （outer） join：右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行

    • full/cross （outer） join： 全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录

  • 分组Group by：一张表，一旦分组 完成后，查询后只能得到组相关的信息；组相关的信息（统计信息）：count,sum,max,min,avg

---

• 提升：

  • 复制表：

    • 只复制结构：select top 0 * into newTable from oldTbale

    • 复制数据：insert into newTable(a, b, c) select d,e,f from oldTable

    • 跨数据库之间表的拷贝：具体数据使用绝对路径)：insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件；例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..

  • 子查询：select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)

  • 两张关联表，删除主表中已经在副表中没有的信息：delete from table1 where not exists (select * from table2 where table1.field1=table2.field1)

  • 四表联查问题：select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....

  • 一条sql语句搞定数据库分页：select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段

  • 随机取出10条数据：select top 10 * from tablename order by newid()；随机选择记录：select newid()

  • 删除重复记录：

    • delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

    • select distinct * into temp from tablename delete from tablename insert into tablename select * from temp；这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作

  • 列出表里的所有的列名：select name from syscolumns where id=object_id('TableName')

---

5 实例：学生-课程数据库

• 建表：

  • 学生表：Student(Sno,Sname,Ssex,Sage,Sdept系)

    • CREATE TABLE Student (Sno char(9) PRIMARY KEY,Sname varchar(20) union,Ssex char(2),Sage SMALLINT,Sdept char(20))

  • 课程表：Course(Cno,Cname,Cpno,Ccredit学分)

    • Create table Course(Cno char(4) PRIMARY KEY,Cname char(40) not null,Cpno char(4),Ccredit smallint,FOREIGN KEY(Cno) references Course(Cno))

  • 成绩/学生选课表：SC(Sno,Cno,Grade)

    • Create table SC(Sno char(9),Cno char(4),Grade smallint,primary key(Sno,Cno),foreign key(Cno) references Course(Cno))

• 增删查改：

  1. 出生年份：当前年份-Sage

  2. 查询既不是CS系，MA系，IS系的学生：select Sname form student where Sdept not in(“CS”,”MA”,”IS”)

  3. 查询选了3门以上课程的学生学号：select Sno from SC group by Sno having count()>3

  4. 查询平均成绩>=90分的学生学号和平均成绩：select Sno,avg(Grade) from SC group by Sno having avg(Grade>=90); // 只涉及SC表

  5. 查询选修课程2且该课程成绩大于90分的学生学号和姓名：select Student.Sno,Sname from Student,SC where Student.Sno=SC.Sno and SC.Cno=’2’ and SC.Grade>90; // 涉及Student表和SC表，等值连接

  6. 查询选修课程语文的学生学号和姓名：select Sno,Sname from Student where Sno in(select Sno from SC where Cno in(select Cno from Course where Cname=’语文’)); // 课程名属于Course，学号和姓名属于Student，两表没有直接联系，需通过SC表连接；先查课程为语文的课程号Cno,再查选了Cno的学生号Sno，最后取出Sno和Sname; 它的等值连接查询(推荐)：select Student.Sno,Sname from Student,SC,Course where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Course.Cname=’语文’

  7. 查询选了课程1号的学生姓名：select Sname from Student where exists (select * from SC where Sno=Student.Sno and Cno=’1’)

  8. 查询选了所有课程的学生姓名：select Sname from Student where *not exists*(select * from Course where not exists(select * from SC where Sno=Student.Sno and Cno=Course.Cno))

  9. 查询所有学生的学号，姓名，选课数，总成绩：select Student.Sno,Student.Sname,count(Cno),sum(Grade) from Student join SC on Student.Sno=SC.Sno group by Student.Sno,Sname

  10. 查询所有课程成绩都<60分的学生学号和姓名：select Sno,Sname from Student where Sno not in(select Student.Sno from Student,SC where Student.Sno=SC.Sno and SC.Grade>60)

  11. 查询‘001’课程分数比‘002’课程高的学生学号：select a.Sno from (select sno, score from sc where cno=’001’) a,(select sno,score from sc where cno=’002’) b where a.score>b.score and a.sno=b.sno;

  12. 查询姓李老师的人数：select count(distinct(tname)) from teacher where tname like ‘李%’

  13. 用一个SQL语句查询N种数据：select sum(case when table.salary>9999 and table.age>35 then 1 else 0 end) as “salary>9999 and age>35”,sum(case when table.salary……),… from table

  14. 连接字符串(与null连接的都为null)：select concat(“str1”,”str2”,”str3”),concat(“str”,null)

  15. 查询工资，大于2000的显示”high”，否则显示”low”：select if(salary>2000,’high’,’low’) from …；select case when salary>2000 then ‘high’ else ‘low’ end from…

  16. 建索引：create index citys on city(cityName(10))

  17. 修改名为‘张’的薪水为5000，性别为‘男’：update tableName set salary=5000,sex=’男’ where name=’张’

  18. 统计学生总分：select name,(chinese+math+english) as total from student

  19. 查询工资多于张员工的员工信息：select * from employee where salary>(select salary form employee where name=’张’)

  20. 查询员工姓名及他的上司姓名：select 员工.name ,上司.name from employee 员工 left jion employee 上司 on 员工.上司编号id=上司.id

  21. 查询雇佣时间小于其上司的员工姓名和上司姓名：select 员工.name,上司.name from employee 员工,employee上司 where 员工.上司id=上司.id and 员工.hireDate<上司.hireDate

  22. 返回从事‘a’工作的员工姓名和所在部门：select 员工.name,dname from employee 员工,dept where 员工.job=’a’ and 员工.deptno=dept.deptno

  23. 返回所有部门及部门最低工资：select deptno,min(salary) from employee group by deptno

  24. 查询销售部的所有员工姓名：select ename from employee 员工,dept 部门 where 部门.dname=’销售部’ and 员工.deptno=(select deptno from dept where dname=’销售部’)

  25. 查询薪资水平多于平均薪资的员工：select * from employee where salary>(select avg(salary) from employee);//注意聚合函数(group function)不能放在where里即不能where salary>avg(salary)

  26. 查询与张从事相同工作的员工：selct * from employee where job=(select job from employee where ename=’张’) and ename!=’张’;//同一部门:where edeptno=(select edeptno from employee where ename=’张’) and ename!=’张’

  27. 返回工资高于a部门所有员工的员工：select * from employee where salary> all(select salary from employee where edeptno=’a’)

6 实例：借书系统

• CARD 借书卡：CNO 卡号，NAME 姓名，CLASS 班级

• BOOKS图书： BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数

• BORROW借书记录： CNO 借书卡号，BNO 书号，RDATE 还书日期

---

1. 查询借书超过5本的读者的借书卡号和册数：select cno,count() from borrow group by cno having count()>5

2. 查询借了水浒的读者的姓名和班级：select * from card c where exists(select * from borrow bo,books b where bo.bno=b.bno and b.bname=’水浒’ and bo.cno=c.cno)

3. 查询过期未还图书的借书卡号，书号及归还日期：select * from borrow where rdate

4. 查询借了a但没有借b的读者的借书卡号并降序：select bo.cno from borrow bo,books b where bo.bno=b.bno and b.bname=’a’ and not exists(select * from borrow bo2,books b2 where bo2.bno=b2.bno and b2.bname=’b’ and bo2.cno=bo.cno) grop by bo.cno desc

5. 将001班同学的归还日期延长一周：update borrow set rdate=dateAdd(day,7,borrow.rdate) from card,borrow where card.cno=borrow.cno and card.class=’001’

6. 删除无人借阅的图书记录：delete from books where not exists(select * from borrow where bno=books.bno)

7. 建立视图显示001班借书信息：create view v as select * from borrow bo,card c,books b where bo.cno=c.cno and bo.bno=b.bno and c.class=’001’

8. 分页查询：select * from table limit (start-1)*limit,limit

9. 去重：Mysql不能删除查询出来的记录的，而是通过一张临时表且保留一条记录：delete from table where id in (select id from (select id from table where expect in (select expect from table group by expect having count(expect)>1) and id not in(select min(id) from table group by expect having count(expect)>1)) as tmpresult)

---

7 SQL语句大全与练习

• https://www.cnblogs.com/DreamDrive/p/6193530.html

• https://mp.weixin.qq.com/s?__biz=MzA5MTMyNTI0Nw==&mid=2649789638&idx=1&sn=fd23d4b61f5bf3596ca381b96e24cccf&chksm=887a2296bf0dab805b45e60ba362e5d12f138524a37b565ad794225c4a528e22f99bb863d388&xtrack=1&scene=0&subscene=131&clicktime=1558317759&ascene=7&devicetype=android-24&version=2700033c&nettype=WIFI&abtest_cookie=BAABAAoACwASABMABgAjlx4AyJkeANyZHgD5mR4AAJoeAAOaHgAAAA%3D%3D&lang=zh_CN&pass_ticket=%2BauxZYx33uTomyxzK3Xjl5qW%2Fu8%2B%2FPH1U6hyAsQ1N4XqFVc2m8mh4uUqtYl5I94e&wx_header=1

---

8 存储引擎

8.1 存储引擎

• 插件式存储引擎是MySQL数据库重要特性之一；用户根据应用，选择如何存储和索引数据、是否使用事务等

• 存储引擎是表组织和存储数据的方式；可实施更复杂的检查和操作，更精细和强大的数据控制能力的约束；预编译

• 对比项	InnoDB：MySQL使用的存储引擎	MyISAM
  主键与外键	支持	不支持
  事务	支持	不支持
  行表锁	行锁，即操作时锁一行而不影响其他行；适合高并发	表锁，即操作一条记录时也会锁住整个表
  缓存	缓存索引和真实数据，因此对内存要求高	之缓存索引
  表空间	大	小
  关注点	事务	性能

• 其他：

  • MyISAM：

    • 每个存储引擎在磁盘上存储为3个与表名同名，但后缀名不同的文件，分别是：.frm-存储表定义，.MYD-存储数据，.MYI-存储索引；数据文件和索引文件可放在不同目录，平均分布IO以获得更快的速度；设置文件路径，需要在建表时通过语句data directory和index directory语句指定绝对且有权访问的路径

    • 表可能损坏且原因多样，以至于表不能访问或返回错误结果；MyISAM提供表修复工具，用check table语句检查健康，用repair table修复表

    • 自增列必须是索引或组合索引的任意列，此时按照索引列前面的列排序后自增

    • 支持的存储格式：

      • 静态表：默认；即表中字段非变长，记录固定长度，因此存储快，容易缓存和回复故障，但占用空间多；因为定长，不足部分补齐后存储，获取时又会把末尾的填补的空格去掉，于是存储尾部本来就有空格的值要小心

      • 动态表：变长，占用空间少，但频繁的修改和删除记录会导致碎片产生，需要定期执行optimize table语句或myisamchk-r命令改善性能，难恢复故障

      • 压缩表：由myisampack工具创建，为每个记录单独压缩存储，占用极小磁盘空间，访问开支小

    • 访问速度快；对事务完整性无要求，适用于以select，insert为主的应用表

  • InnoDB：

    • 提供具有提交，回滚和崩溃恢复能力的事务安全，效率较MyISAM差，占用更多磁盘以保存数据和索引

    • 对自增长列可手动插入想要的数据；但当插入值为0或null时，实际插入值还是自增长的值

    • 自增长值默认从1开始；可auto_increment=n强制设置起始值，但此值在数据库重新启动后失效

    • 可适用last_insert_id()查询当前线程最后一次记录插入时使用的值，若那次是批量插入，则返回批量数据的第一条记录的自增值

    • 自增列必须是索引或组合索引的第一列

    • 外键约束：

      • 创建外键时，要求父表必须有对应的索引，子表创建外键时自动创建对应索引

      • 创建索引时，可指定在修改和删除父表时，对子表的操作，包括：

        • restrict或no action：在子表有关联记录时，父表不能修改

        • cascade：父表在修改或删除时，同时修改或删除子表对应记录；慎防数据丢失

        • set null：父表修改或删除时，子表对应记录设置为null；慎防数据丢失

        • 例：foreign key (user_id) references user(id) on delete restrict on update cascade

      • 有了外键约束，则父表的对应索引和主键禁止被删除

      • 导入多个表时忽略表的导入顺序，加载数据，或修改表时，都可暂时关闭外键检查以提高处理速度：set foreign_key_cheacks = 0；然后set foreign_key_checks=1恢复

    • 存储格式：

      • 共享表空间：.frm文件中存储表定义，数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中

      • 多表空间：.frm文件文件存储表结构和定义，但数据和索引单独存储在.ibd中；如果是分区表，每个分区对应单独.ibd文件，文件名为表名+分区名

        • 此种方式需要设置参数innodb_file_per_table并重启服务，且仅对后来新建的表生效，原来的表仍按原来方式存储

        • 多表空间方式的表改回共享表空间方式后，共享表空间中会新建，但多表空间仍会保留方式方式

        • 数据文件无大小限制，无需设置初始大小，最大限制和扩展大小等参数

        • 容易单表备份和恢复，但不能直接复制.ibd文件，因为没有共享表空间的数据字典信息；恢复命令：alter table tableName discard tablespace;alter table tableName import tablespace;但只能恢复到原来所在数据库；若想恢复到其他数据库，则需要mysqldump和mysqlimport

    • 适用于对事物完整性要求高，在并发条件下要求数据一致性，会有大量修改和删除操作的应用

  • memory：

    • 使用存在于内存中的内容来创建表，默认使用hash索引，每个memory表只对应一个磁盘.frm文件，因此访问速度快，但服务关闭后表中数据丢失

• 创建新表时，不指定存储引擎，则系统使用默认存储引擎；修改使用的存储殷勤：在参数文件中设置default-tablt_type

• 查看使用的存储引擎：show variables like 'table_type'；查看支持的存储引擎：show engines \G或者show variables like 'have%'

•  create trigger 名
   befor/after 触发事件insert/update/delete on 表名
   referencing new/old row as 变量名
   for each row/statement  // 类型：行级触发和语句级触发
   [when 触发条件] 触发动作insert/update/delete
   ​
   ## 在建表时指定
   create table tableName (...) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
   ## 修改表使用的存储引擎
   alter table tableName engine = InnoDB;

8.2 锁

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度低

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高

• 页面锁：介于表级锁和行级锁之间

• 查看表锁争用情况：show status like 'table%'；包含table_waited和table_locks_immediate，值越高则说明存在较严重的表级锁争用情况

• MyISAM表锁：

  • 两种模式：

    • 表共享读锁：不会阻塞其他用户对同一表的读请求，但会阻塞写请求

    • 表独占写锁：会阻塞其他用户对同一表的读和写请求

  • 加锁：执行select时自动给涉及的所有表加读锁，执行update,delete,insert时自动加写锁；各操作之间串行，即只有持有锁的线程可操作表；显式加锁：lock tables tableName [read/write local]；解锁：unlock tables;

9 PostgreSQL

9.1 对比mysql

• 不仅支持普通数据类型，还支持array（甚至多维）和json、jsonb（比text存储高效）；json存储文本，jsonb存储二进制数据以避免重新解析数据结构

• 高效处理R-trees等树状结构数据，适合处理有地理数据的应用

9.2 一些操作经验

• 表名或字段名为大写字母时，SQL操作需要加双引号

10 数据库可视化工具

• 使用工具带有的查询创建工具，而不是手动编写；明确主表，将关联表拖进来，选中字段，创建关联，选择连接方向；美化SQL