mySQL

SQL

• structure query language：结构化查询语言，是关系型数据库应用语言
• 不大小写敏感
• 编写顺序&执行顺序

编写顺序	执行顺序
SELECT （DISTINCT）	from
FROM	join
JOIN	on
ON	where
WHERE	group by
GROUP BY	having（优先使用where）
having	select
order by	order by
limit	limit

SQL分类

• 主要分为三类

DDL:（data definition language）数据定义（对表结构进行增删改查）create、drop、alter
# DQL:（data query language）数据查询select

DML:（data manipulation language）数据操作（对数据进行增删改）

DCL:（data control language）数据控制语言（授权grant、撤销权限revoke）

# TCL:事务控制语言（事务提交commit、回滚rollback）

DDL：

• 创建数据库：create database dname

• 删除数据库：drop dname

• 建表：create table （表名 ）（字段 数据类型，字段 数据类型）

• 删除：drop table （表名）
  truncate table删除数据，表结构还在

• 改表：
  删除列：alter table （表名）drop （字段名）
  增加列：alter table （表名） add （字段名 类型）
  重命名：alter table change （字段名 新名）

DML：

• 增：insert into (表名）（字段名）values（值）；

• 删：delete from（表名） where ；

• 改：update （表名）set （字段名=值） where；

• 查：（可以分出来为DQL）

  • 简单查询
    • select
  • 条件查询
    • select ...from... where
  • 排序查询
    • order by（asc升、desc降）

• 分组查询

  • group by
  • 注意：在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数。其它的一律不能跟。Having：对分组后的数据进一步过滤（where是在分组前进行筛选，尽量先使用where）
  • 连接查询
    • 内连接（没有null）：等值连接、非等值连接、自连接
    • 外连接（会有null）：左外连接、右外连接
    • 多张表的连接：from…join…on…join…on…
  • 子查询
    • where中嵌套select
    • 注意：select ename,sal from emp where sal > min(sal); (ERRORwhere子句中不能直接使用分组函数)，改为select ename,sal from emp where sal > (select min(sal) from emp);

DCL:

• DBA用来管理系统对象使用，一般开发人员很少使用

• 创建用户、删除用户、授权

mysql

• 默认port：3306

• 常用命令：exit、\C（取消当前命令执行）、source（导入演示数据）

• 全面支持事务，满足事务ACID属性，并支持外键约束

• 实际应用方面，LAMP（linux+apache+mysql+python/perl/php）

• 性能好

• 连接：

  mysql -u username -p
  

1语法

• SQL

• 帮助

  • 查找官方文档

  • mysql安装后自带帮助文档

    • ? contents
      如：
      ? show
      ? create table
      

  • 常用网络资源：

    • mysql官网
    • bugs.mysql.com

• 查元数据

  • use dname
  • show dname/table
  • desc dname/table

• 数据导入与导出

  • # 数据导入
    	注意：在windows的dos命令窗口中：
    mysqldump dname>D:\filename.sql -uroot -p123456
    	导出指定的表：
    mysqldump dname tname>D:\filename.sql -uroot -p123456
    # 数据导出
    	注意：需要先登录到mysql数据库服务器上。然后创建数据库：create database bjpowernode；使用数据库：use bjpowernode；然后初始化数据库：source D:\bjpowernode.sql
    

2数据类型

• 数值类型

• 日期时间类型

  • datetime
    date
    time
    ~
    

• 字符串类型

  • char
    varchar
    ~
    

3运算符

• 算数运算符
• 比较运算符（select时）
• 逻辑操作符（not、and、or、xor）
• 位运算符

4常用函数

1. lower、upper、substr（被截取的字符串，起始下标，截取的长度）、concat（字符串的拼接）、length、trim、str_to_date （将字符串转换成日期）、date_format （格式化日期）、format （设置千分位）、round（四舍五入）、rand（生成随机数）、union（连接表）
2. ifnull（将null转换为一个具体的值）
3. case…when…then…when…then…else…end（case when then when then 注意：不修改数据库，生成一个新的列）
4. distinct（原表数据不会被修改，只是查询结果去重）
5. 分组函数：count、sum、avg、max、min

5约束

• 约束类型：

①非空约束：not null（约束的字段不能为NULL）

②唯一性约束：unique （约束的字段不能重复，但是可以为NULL）

③主键约束：primary key（主键值不能是NULL，同时也不能重复！）

分为单一主键和复合主键或者自然主键和业务主键。主键不建议使用varchar类型。

④外键约束：foreign key（必须是外表的主键，但外键值可以为null）

当某字段值没有约束时可能会出现数据无效。用reference添加

• 添加约束的方式：

①列级约束

create table t_vip(id int primary key, name varchar(255));

②表级约束

create table t_vip(id int,name varchar(255),primary key(id)；

• 主外键约束删除数据问题--级联操作

通常情况下，需要先删除子表数据，再删除父表数据。
on delete cascade：当主表数据删除时，对应的子表数据同时删除；
on delete set null：当主表数据删除时，对应的子表数据设置为null；

6transaction

• 一个事务是一个完整的业务逻辑，是最小的工作单元，不可再分，要么同时成功，要么同时失败。默认情况是一条语句提交一次这种自动提交实际上是不符合我们的开发习惯，因为一个业务通常是需要多条DML语句共同执行才能完成的，为了保证数据的安全，必须要求同时成功之后再提交，所以不能执行一条就提交一条。只有DML语句有事务的说法，其他语句和事务无关（insert、delete、update）

• 事务的四个特性：

  ​ A：原子性：事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。

  ​ C：一致性：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

  ​ I：隔离性：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

  ​ D：持久性：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

• 事务的隔离性：

  读未提交：read uncommitted（最低的隔离级别）《没有提交就读到了》

  读已提交：read committed《提交之后才能读到》

  可重复读：repeatable read《提交之后也读不到，永远读取的都是刚开启事务时的数据》

  序列化/串行化：serializable（最高的隔离级别）

查看隔离等级：select @@transaction_isolation
修改隔离等级：set global transaction isolation level read uncommitted;

start transaction;
#..
commit\rollback;

7storage engine

在建表的最后添加：engine=InnoDB default charset=gbk;

• 常用存储引擎：

①InnoDB（默认）：非常安全，支持事务机制，但是效率不是很高且不能压缩
②MyISAM：使用三个文件表示每个表，能够压缩。但是不支持事务机制。
③MEMORY：数据存储在内存中，查询效率最高，不需要和硬盘交互，但是不安全。

8index

• 索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。（主键和unique约束会自动创建索引对象）索引底层是自平衡二叉树。

创建索引：create index 索引名 on 表名（字段名）
删除索引：drop index 索引名 on 表名
查看一个SQL语句是否使用了索引进行检索：explain select * from emp where ename = 'KING'; type=all说明没有使用索引

• 索引失效的情况

①模糊查询select * from emp where ename like '%T';

②使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引。所以不推荐使用or。

③使用复合索引的时候，没有使用左侧的列查找，索引失效

④在where当中索引列参加了运算，索引失效

⑤在where当中索引列使用了函数

9views：

• 虚拟表
  • 简化用户操作
  • 多角度看待数据
  • 对数据提供安全保障
• 视图常用操作：

Create view 视图名 as （select…DQL语句）
Drop view 视图名；
（视图的特点：通过对视图的操作，会影响到原表数据。）

数据库设计三范式：

• 数据库设计的一般步骤：需求分析、概念结构设计、逻辑结构设计、物理结构设计、数据库的实施、运行与维护。
• 三范式

① 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。

​ 一对一： 在实际的开发中，可能存在一张表字段太多，太庞大。这个时候要拆分表。口诀：一对一，外键唯一！！！！！！！！！！

②第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。

​ 多对多：口诀：多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！

③第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

​ 一对多：口诀：两张表，多的表加外键！！！！！！！！！！！！

声明：三范式是面试官经常问的，所以一定要熟记在心！

• 实际设计

数据库设计三范式是理论上的。实践和理论有的时候有偏差。最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。

注意：

①在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。为了避免这个现象，需要使用ifnull函数。

②ifnull函数用法：ifnull(数据, 被当做哪个值)。如果“数据”为NULL的时候，把这个数据结构当做哪个值。补助为NULL的时候，将补助当做0。

③通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的连接次数。