MySQL数据库

数据库简介

数据库分类

关系型数据库

关系型:指的是表关系
MySQL
Oracle
db2

非关系型数据库

redis

MySQL数据库-数据库操作系统

数据库操作系统(DBMS)的组成

• 数据库DB
• 表 TABLE(列 Column)
• 数据 data

SQL语法

• SQL:Structure Query Language 结构化查询语言
• SQL定义了所有的关系型数据库的规则

SQL基本语法

• SQL语句可以多行书写,以回车结尾
• 可以使用空格和缩进来增强语句的可读性
• MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
• 注释: 单行:空格-- 多行:/* */

SQL的分类(CRUD)

• DDL:数据库定义语言-Data Definition Language
• DML:数据库操作语言-Data Manipulation Language
• DQL:数据库查询语言-Data Query Language
• DCL:数据库控制语言-Data Control Language

常用数据类型

• INT():括号里可以加数字代表长度
• DOUBLE(a,b):括号里可以加数字代表长度,a代表整数部分,b代表小数部分
• VARCHAR():括号里必须加数字代表长度,VARCHAR长度随实际输入改变长度
• CHAR():括号里必须加数字代表长度,CHAR会自动补全(空格)
• DATE: 日期
• DATETIME:日期(精确到秒)
• TIMESTAMP:时间戳,如果赋Null值或者不赋值,默认为当前本地系统时间

DDL

DATA

-创建:
  CREATE DATA  数据库名
-查询:
  SHOW DATA S 查看数据库列表
  SHOW CREATE DATA  数据库名:查看数据库
  SELECT DATA () 查看当前数据库
-删除:
  DROP DATA  数据库名
-使用:
  USE 数据库名

TABLE

-创建:
  CREATE TABLE 表名(列名 数据类型 约束, 列名2 数据类型2 约束2)
-查询:
  SHOW TABLES:查看所有表格(当前数据库)
  SHOW CREATE TABLE 表名:查看任意的表
  DISC 表名: 查看表的结构
-修改:
  ALTER TABLE 表名 ADD 列名 数据类型 约束 :添加一列
  ALTER TABLE 表名 MODIFY 列名 数据类型 约束:修改一列的数据类型
  ALTER TABLE 表名 CHANGE 列名 新列名 数据类型 约束:修改一列的列名
  ALTER TABLE 表名 DROP 列名:删除一列
  ALTER TABLE 表名 CHARACTER SET 字符集:修改表的字符集
  REMANE TABL 表名 TO 新表名:修改表名称
-删除:
  DROP TABLE 表名

DML(增删改)

• 注意:DML是针对表格中的数据的

插入数据(增)

-INSERT INTO 表名(列1,列2...) VALUES(值1,值2...)
    插入新的一行并给列1,列2...赋值
-INSERT INTO 表名 VALUES(值1,值2...)
    插入新的一行,并给所有列赋值

删除

-DELETE FROM 表名 WHERE 条件
  删除符合条件的行
-DELETE FROM 表名
  删除表中的所有数据
-TRUNCATE TABLE 表名
  删除表,并重新创建一个一样的表
!!!注意:TRUNCATE方式删除表中的数据不能被事务恢复而DELETE可以

更新(改)

-UPDATE 表名 SET 列名1=值1,列名2,值2 WHERE 条件
  更新表中符合条件行中的某列的值

约束

约束的分类

单表约束

• 添加

-主键约束:PRIMARY KEY AUTO_INCREMENT
   主键约束表示非空且唯一
   AUTO_INCREMENT:自增长,如果主键赋NULL,会根据上行主键值自动加1
- 非空约束:NOT NULL
- 唯一约束:UNIQUE

• 删除

主键约束: ALTER TABLE 表名 DROP PRIMARY KEY
其他约束: ALTER TABLE 表名 MODIFY 列名 数据类型 [约束]

• 注意

- 这里的添加是创建时添加,如果创建后添加使用ALTER TABLE 表名 MODIFY 列名 数据类型 约束 的方法
- 一般都在创建时添加约束

多表约束

• 添加(一般在创建表时候添加)

-外键约束:[CONSTRAINT 外键名] FOREIGN KEY (外键列) REFERENCES 主表(主键)
  如果省略CONSTRAINT 外键名省略,则会自动生成一个外键名

• 删除

ALTER TABLE 表名 DROP FOREIGN KEY 外键名

• 注意

- 外键名不是外键列名!!!而是用来DROP外键而起的一个名称

注意事项

• 主键和外键可以在创建表格的最后再创建

CREATE TABLE 表格名{
    列1 数据类型1,
    列2 数据类型2 NOT NULL,
    列3 数据类型3,
    PRIMARY KEY(列1),
    [CONSTRAINT 外键名] FOREIGN KEY (列3) REFERENCES 主表(主键)
}

• []符号:以上和以后的[]符号都代表可省略

DQL(查询)

• 注意:DQL是本章最重点,涉及到单表查询,多表查询等等

单表查询

基本查询语法!!!

-基本查询:
SELECT 列1,列2... FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 条件 ORDER BY 列名 ASC/DESC LIMIT index,rows

-DISTINCT关键字:
 可以使用DISTINCT关键字来去重
 SELECT DISTINCT 列 FROM 表名...

聚合函数

• COUNT():计数
• SUM():求和
• AVG():求平均
• MAX():求最大值
• MIN():求最小值
• 注意

- 聚合函数会自动跳过NULL值
- 聚合函数的结果一般是一行一列,单个值

查询的关键字

• 条件查询 WHERE/HAVING

WHERE:分组之前的条件,不接受聚合函数
HAVING:分组之后的条件,一般和聚合函数配合使用

• 分组查询GROUP BY

- 分组查询显示的结果是每组的第一条数据,所以SELECT后面的列一般不包含个人信息,因为如果这样分组没有意义
- 分组查询一般和聚合函数配合使用

• 排序查询ORDER BY

- 排序查询最后要跟上排序规则 ASC/DESC 
- 排序查询如果不跟上排序规则则默认为 ASC

• 限制查询LIMIT

-LIMIT是限制查询的条数,有两个参数 index和rows
index:开始的索引 
  index的公式:(页数-1)*rows;
rows:显示的行数

多表查询

多表关系

• 一对一

处理一对一关系时,在任意一个表添加一列,添加另一张表的主键为外键,并且这里的外键值只能唯一

• 一对多

处理一对多关系时,在从表(多的一方)添加一列,添加主表的主键为外键

• 多对多

处理多对多关系时,一般创建一张中间表(该表至少有两列),分别添加两个主表的主键为外键,并且为了保证两个外键的组合唯一,可以设置联合主键

列:
 CREATE TABLE tab_favorite{
     rid INT, -- 线路
     DATE DATETIME,
     uid INT, -- 用户
     PRIMARY KEY(rid,uid),
     FOREIGN KEY(rid) REFERENCES tab_route(rid),
     FOREIGN KEY(uid) REFERENCES tab_user(uid)
 }

内连接查询

• 注意:一般查询的关键字/表名都使用别名.关键字/别名来表示
• 注意:内连接和外链接的区别

左外链接会获取左表的所有数据及左表和右表的交集,如果左表某行连接条件为NULL,则该行右表的相应值也为NULL
右外链接会获取右表的所有数据及右表和左表的交集,如果右表某行连接条件为NULL,则该行左表的相应值也为NULL
内连接只会获取左表和右表的交集,如果某行连接条件为NULL,则该行不会被查询出来

• 隐式内连接

SELECT 列名1,列名2 FROM 表1,表2 WHERE 关联条件

-显式内连接

SELECT 别名1.列名1,别名1.列名2 ,别名2.列名1,别名2.列名2...
FROM 表1 别名
[INNER] JOIN 表2 别名 ON 关联条件
WHERE 条件

外连接查询

• 注意:一般查询的关键字/表名都使用别名.关键字/别名来表示
  -左内连接

SELECT 别名1.列名1,别名1.列名2 ,别名2.列名1,别名2.列名2...
FROM 表1 别名
LEFT [OUTER] JOIN 表2 别名 ON 关联条件
WHERE 条件

-右内连接

SELECT 别名1.列名1,别名1.列名2 ,别名2.列名1,别名2.列名2...
FROM 表1 别名
RIGHT [OUTER] JOIN 表2 别名 ON 关联条件
WHERE 条件

子查询

• 子作为条件

子查询作果为1个值: WHERE 列名=?
子查询结果为多行1列: WHERE 列名 IN{子查询}

• 子连接作为虚拟表(一般要设置别名)

自连接查询结果为多行多列

事务

事务的基本介绍

-概念

 包含多个步骤的业务操作

• 操作

-开启事务 START TRANSACTION
-回滚 ROLL BACK       
  如果操作的结果和预期不符,可以使用该语句回滚
-提交 COMMIT    
  如果操作的结果和预期相符,可以用该语句提交事务

• 提交方式

-自动提交:
   MySQL数据库默认是自动提交事务
   如果没有手动开启事务,一条增删改语句会默认开启并提交一次事务
-手动提交:
	Oracle 数据库默认是手动提交事务
	手动开启事务,手动提交事务
-修改事务的默认提交方式
	查看:SELECT @@autocommit
	修改:SET @@autocommit = 0;

事务的四大特性

-原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
-持久性:当事务提交或回滚后,数据库会持久化的保持数据
-隔离性:多个事务之间相互独立
-一致性:事务操作前后,数据的总量不变

事务的隔离级别

• 存在问题

-脏读:一个事务,读取到另一个事务中没有提交的数据
-虚读:指一个的事务读取到了另外一个事务中提交的insert的数据
-不可重复读:指一个的事务读取到了另外一个事务中提交的UPDATE的数据
-幻读:一个事务操作(DML)数据表中的所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改

• 隔离级别****面试题

!!注意:隔离级别从小到大安全性越来越高,但是效率越来越低
-read uncommitted:读未提交
 产生的问题:脏读,虚读,不可重复读,幻读
-read committed:读已提交 (Oracle默认)
 产生问题:不可重复读,幻读
-repeatable read:可重复读(MySQL默认)
 产生的问题:幻读
-serializable:串行化
 可以解决所有问题

(了解)设置隔离级别:
SET GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别