sql

MySql

数据库基本概念

数据库(DataBase) : 简称DB ， 用于存储和管理数据的仓库

数据库的特点

• 1.持久化存储数据,其实数据库就是一个文件系统
• 2.方便存储和管理数据
• 3.使用了统一的方式来操作数据库

常见数据库软件网上可查

MySql

打开和关闭mysql

// windows下  mac需要找到mysql
net start mysql
net stop mysql

登录和登出

// 登录
mysql -u root -p 密码
mysql -h ip -u root -p 密码
mysql --host ip --user 用户名 --password 密码
//登出
quit 
exit

mysql 的目录结构

安装目录

• my.ini: mysql配置文件

数据目录

• 数据库 -> 文件夹
• 表 -> 文件
• 数据 -> 文件内的数据

SQL

结构化查询语言： 定义了操作所有关系型数据库的规则

• 每一种数据库操作的方式存在不一样的地方成为方言

SQL 通用语法

• SQL语法可以以单行或者多行书写，以分号结尾。
• 空格、缩进来强化可读性
• SQL不区分大小写，关键字建议大写
• 3种注释
  • 单行注释 – 注释内容 或者 # 注释内容
  • 多行注释 /* 注释内容 */

sql分类

• DDL : 数据定义语言，操作数据库、表
• DML : 数据操作语言，增删改表中的数据
• DQL : 数据查询语言，查询表中的数据
• DCL : 数据控制语言，授权

操作数据库、表

操作数据库 : CRUD

• C(Create) : 创建
  • create database 数据库名; // 创建数据库
  • create database 数据库名 character set 字符集名称; // 创建指定字符集的数据库
  • create database if not exists 数据库名; // 创建数据库并判断是否存在数据库
• R(Retrieve) : 查询
  • show databases; // 查看所有数据库的名称
  • show create database 数据库名称; // 查询某个数据库的字符集
• U(Updata) : 修改
  • 修改数据库的字符集: alter database 数据库名称 character set 字符集名称(utf8);
• D(Delete) : 删除
  • 删除数据库: drop database 数据库名称;
  • 先判断是否存在再删除: drop database if exists 数据库名称;
• 使用数据库
  • select database(); // 查询当前是使用 的数据库
  • use 数据库名称; // 使用数据库

操作表

数据库的常用数据类型

• int : 整数类型 -> age int
• double: 小数类型 -> score double(5,2)
• date: 日期类型，只包含年月日，yyyy-MM-dd
• datetime: 日期，包含年月日时分秒, yyyy-MM-dd HH:mm:ss
• timestamp : 时间戳类型, 包含年月日时分秒, yyyy-MM-dd HH:mm:ss
  • 不给这个字段赋值或赋值为null，则默认使用当前的系统时间默认赋值
• varchar: 字符串类型 -> name varchar(20); // 姓名最大20个字符

• C(Create) : 创建
  • create table 表名(
  • 列名1 数据类型1;
  • 列名2 数据类型2;
  • …
  • 列名n 数据类型n
  • );
  • 最后一列不需要加逗号

    create table student(
      id int,
      name varchar(32),
      age int,
      score double(4,1),
      birthday date,
      insert_time timestamp
    );

• 复制表: create table 表名 like 被复制的表名;

• R(Retrieve) : 查询
  • 查询表结构: desc 表名
  • 查询某个数据库中所有的表名称 : show tables;

• U(Updata) : 修改
  • 修改表名 : alter table 表名 rename to 新的表名;
  • 修改表的字符集 : alter table 表名 character set 字符集名称;
  • 添加一列 : alter table 表名 add 列明 数据类型;
  • 修改列的名称和类型 :
    • alter table 表名 change 列名 新列名 新数据类型;
    • alter table 表名 modify 列名 新数据类型;
  • 删除列 : alter table 表名 drop 列名;

• D(Delete) : 删除
  • drop table if exists 表名称;
  • drop table 表名称;

客户端图形化工具 : SQLYog / navicat for mysql

DML 增删改表中的数据

1.添加数据

• insert into 表名(列名1,列名2…,列名n) values (值1,值2,….,值n)
  • 注意
    • 列名和值需要一一对应
    • 如果表名后，不定义列名，则默认给所有列添加值
    • 除了数字类型，其他类型需要使用引号引起来，单双引号都可以

2.删除数据

• delete from 表名 [where 条件]
  • 如果不加条件，则把表中的数据全部删除(不推荐使用，一条一条删除，效率不高)
• TRUNCATE TABLE 表名 (推荐使用，效率更高)– 删除表，然后再创建一个一模一样的空表

3.修改数据

• update 表名 set 列名1 = 值1, 列名2 = 值2, …. , 列名n = 值n [where 条件];
  • 如果不加任何条件，则会修改表中所有的数据

DQL 查询表中的记录

查询表所有: select * from 表名;

语法
select 字段列表
from 表名列表
where 条件列表
group by 分组字段
having 分组后的条件
order by 排序
limit 分页限定

基础查询

• 多个字段的查询
  • 如果查询所有字段可以使用 * 来替代字段的列表
• 去除重复
  • DISTINCT关键字
• 计算列
  • 一般可以使用四则运算计算一些列的值
  • 如果有null参与计算，结果都为null,可以使用IFNULL关键字解决为null的值
  • IFNULL(表达式1，表达式2)
• 起别名
  • AS关键字

// AS 可以用空格代替
SELECT DISTINCT NAME , math , math + IFNULL(english , 0) AS 总分 FROM student;

条件查询

• where字句后面跟条件
• 运算符

  • , < , = , >=,<= , <>(表示不等于)

  • BETWEEN …AND
  • IN
  • LIKE ：模糊查询
    • _ : 单个任意字符
    • % : 多个任意字符
  • IS NULL
  • and 或者 &&
  • or 或者 ||
  • not 或者 |

-- 查询年龄大于等于20岁，小于等于30岁的数据
SELECT * FROM stu WHERE  age >=20 AND age <=30; -- and 可以换成 &&
SELECT * FROM stu WHERE BEWTEEN 20 AND 30; -- 表示年龄在20-30之间并且包含 20 和 30 
-- 查询年龄不等于20岁的
SELECT * FROM stu WHERE  age <> 20;  -- 或者 != 符号也可以
-- 查询年龄为22、19、25的数据
SELECT * FROM stu WHERE age = 22 OR age = 19 OR age = 25;
SELECT * FROM stu WHERE age IN (22,19,25); -- 上面语句的简化写法
-- 查询英语成绩为null的
SELECT * FROM stu WHERE english = NULL; -- 不对的，null值不能使用=或者!=判断
SELECT * FROM stu WHERE english IS NULL;
-- 查询英语成语不为null的数据
SELECT * FROM stu WHERE english IS NOT NULL; 
-- 同学姓马的有哪些 like
SELECT * FROM stu WHERE name LIKE '马%';
-- 查询第二个字是化的人
SELECT * FROM stu WHERE name LIKE '_马%';
-- 查询姓名是三个字的人
SELECT * FROM stu WHERE name LIKE '___';
-- 查询姓名中包含马的人
SELECT * FROM stu WHERE name LIKE '%马%';

高级查询

• 排序查询

  • order by 字句
    • order by 排序字段1 排序方式1,….
  • 排序方式
    • ASC : 升序，默认的
    • DESC : 降序
  • 如果有多个排序条件，则当前面的条件值一样时，才会判断后面条件

• 聚合函数: 将一列数据作为一个整体，进行纵向计算

  • count : 计算个数
    • 一般选择非空的列，主键
  • max : 计算最大值
  • min : 计算最小值
  • sum : 计算和
  • avg : 计算平均值
  • 注意: 聚合函数的计算会排除null值
    • 解决方案:
      • 选择不包含null的列计算
      • IFNULL函数
      • SELECT COUNT (*) FROM stu;(不推荐)

• 分组查询

  • 语法: group by 分组字段;
  • 注意:
    • 分组之后查询的字段: 分组字段、聚合函数
    • WHERE和HAVING的区别
      • where在分组之前进行限定，如果不满足条件则不参与分组，having在分组之后进行限定，如果不满足条件则不会被查询出来
      • whrer后不可以跟聚合函数判断，having可以

• 分页查询

  • 语法: limit 开始索引,每页查询的条数;
  • 公式: 开始的索引 = (当前的页码 - 1) * 每页显示的条数
  • LIMIT分页操作是一个mysql方言

-- 升序方式排列 默认升序
SELECT * FROM stu ORDER BY math;
-- 降序
SELECT * FROM stu ORDER BY math DESC;
-- 按照数学成绩排序，如果一样，则按照英语成绩排序
SELECT * FROM stu ORDER BY math ASC , english ASC;
-- 查看学生人数,如果是null给其设置为0
SELECT COUNT(IFNULL(name,0)) from stu;
-- 数学最大成绩 MIN 则是最小值
SELECT MAX(math) from stu; 
-- 求和 ， 会排除null值进行计算 AVG则为平均分
SELECT SUM(math) from stu; 
-- 按照性别分组，分别查询男，女同学的平均分和人数
SELECT sex , AVG(math),COUNT(id) from stu GROUP BY sex; 
-- 按照性别分组，分别查询男，女同学的平均分和人数，分数低于70分的人不参与分组
SELECT sex , AVG(math),COUNT(id) from stu WHERE math > 70 GROUP BY sex; 
-- 按照性别分组，分别查询男，女同学的平均分和人数，分数低于70分的人不参与分组,分完组之后人数要大于两个人(对分完组的数据在进行限定)
SELECT sex , AVG(math),COUNT(id) from stu WHERE math > 70 GROUP BY sex HAVING COUNT(id) > 2; 

-- 每页显示三条数据
SELECT * FROM stu LIMIT 0,3; -- 第一页
SELECT * FROM stu LIMIT 3,3; -- 第二页
-- 公式: 开始的索引 = (当前的页码 - 1) * 每页显示的条数

约束

对表中的数据进行限定，保证数据的正确性、有效性和完整性

• 分类
  • 非空约束 : not null（值不能为null）
  • 唯一约束 : unique (唯一的值)
  • 主键约束 : primary key
    • 含义 : 非空且唯一
    • 一张表只能有一个字段为主键
    • 主键是表中的唯一标识
    • 自动增长: 如果某一列是数值类型的，使用auto_increment 可以来完成值得自动增长，一般配合int类型主键一起使用
    • 自动增长只跟上一条数据的值相关，是上条数据+1
  • 外键约束 : foreign key -> 让表和表之间产生关系，保证数据的正确性
    • 在创建表时，可以添加外键
    • 语法: constraint 外键名称 foreign key 外键列名称 references 主表名称(主表列名称)
    • 级联更新 : ON UPDATE CASCADE
    • 级联删除 : ON DELETE CASCADE

-- 创建表时添加约束
create table stu(
    id int primary key auto_increment, -- 主键约束 并实现自动增长
    name varchar(20) NOT NULL,-- 非空约束
    phone_number varchar(20) UNIQUE, -- 唯一约束
    dep_id INT,-- 外键对应主表的主键
    CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) -- 添加外键关系
);
-- 创建完后修改非空约束
ALTER TABLE stu MODIFY name varchar(20) not null;
-- 删除唯一约束
ALTER TABLE stu DROP INDEX phone_number ; 
-- 创建表后添加唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE; 
-- 删除主键
ALTER TABLE stu DROP PRIMARY KEY; 
-- 创建完表后添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
-- 自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCRENMENT;
-- 删除自动增长
ALTER TABLE stu MODIFY id INT;
-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;
-- 创建完表后添加外键  并设置级联更新和级联删除
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE;

多表之间的关系

• 一对一
  • 如: 人和身份证
  • 一个人只能对应一个身份证，一个身份证只能对应一个人
• 一对多(多对一)
  • 如: 部门和员工
  • 分析: 一个部门有多个员工，一个员工只能对应一个部门
• 多对多 :
  • 如 : 学生和课程
  • 分析 : 一个学生可以对应多门课程，一个课程也可以对应多个学生

• 实现关系
  • 一对多（多对一）:
    • 实现方式 : 在多的一方建立外键，指向一的一方的主键
  • 多对多 :
    • 实现方式 : 需要借助第三张表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键
  • 一对一 :
    • 实现方式 : 可以在任意一方添加唯一外键来指向另一方的主键，

范式 ：用来判断数据库设计的是否合理的一些规范

• 概念: 设计数据库时需要遵循的一些规范，呈递次规范，越高级别的范式数据库冗余越小
• 第一范式 : 每一列都是不可分割的原子列
• 第二范式 : 1的基础上，消除非主属性对对主码的部分函数依赖
  • 函数依赖: A–>B , 如果通过A属性(属性组)的值，可以确定唯一B的属性值，则称B依赖于A
    • 例如: 学号 –> 学生姓名 (学号和课程名称可以确定出来一个分数，称之为属性组)
  • 完全函数依赖 : A–>B, 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值
  • 部分函数依赖 : A–>B ，如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值就可以确定
    • 例如 : 学号和课程名称 –> 学生姓名
  • 传递函数依赖 : A –> B , B –> C , 如果同过A属性的值，可以确定B属性的值，而B属性的值可以确定C属性的值，那么称C传递依赖于A
    • 例如 : 学号 –> 系名 , 系名 –> 系主任
  • 码 : 如果一个属性(属性组)，被其他所有属性完全依赖，则称这个属性(属性组)为该表的码
    • 例如: 该表中的码为属性组 : (学号，课程名称)
    • 主属性: 码属性组中的所有属性
    • 非主属性: 除码属性组的属性称为非主属性
• 第三范式 : 在2的基础上，消除传递依赖

数据库的备份和还原

• 命令行方式
  • 语法:
    • 备份 : mysqldump -u用户名 -p密码 要备份的数据库名称 > 保存的路径
    • 还原:
      • 1.登录数据库
      • 2.创建数据库
      • 3.使用数据库
      • 4.执行文件 : source 执行文件
• 图形化工具方式
  • 备份/导出
  • 执行SQL脚本文件

多表查询

语法: SELECT 列名列表 FROM 表列表 … ;

• 笛卡尔积 : 多个集合组合的所有可能
• 要完成多表查询需要消除笛卡尔积中无用的数据

多表查询的分类

• 1.内连接查询:
  • 隐式内连接:
  • 显式内连接:
    • 语法: SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
  • 使用内连接的前提需要知道:
    • 从哪些表中查询数据
    • 查询条件是什么
    • 查询哪些字段

-- 隐式内连接
-- 查询所有员工的信息和对应的部门信息
SELECT * FROM emp , dept WHERE emp.dept_id = dept.id;
-- 查询员工表名称、性别、部门名称
SELECT emp.name , emp.gender, dept.name  FROM emp , dept WHERE emp.dept_id = dept.id;
-- 显示内连接
SELECT * FROM emp INNER JOIN  dept ON emp.dept_id = dept.id;
SELECT * FROM emp JOIN  dept ON emp.dept_id = dept.id; -- inner可不写

• 2.外连接查询:
  • 左外连接
    • 语法: select 字段列表 from 表1 left [outer] join 表2 on 条件;
    • 查询的是左表所有数据以及交集部分
  • 右外连接
    • 语法: select 字段列表 from 表1 right [outer] join 表2 on 条件;
    • 查询的是右表所有数据以及交集部分(满足where)

-- 查询所有员工信息，部门有则查，无则不显示
SELECT t1.*,t2.name FROM emp t1 LEFT JOIN dept t2 ON t1.dept_id = t2.id;

• 3.子查询: 查询中嵌套查询，称嵌套的这个查询为子查询
  • 子查询的不同情况
    • 1.子查询的结果是单行单列的
      • 子查询可以作为条件，使用运算符去判断
    • 2.子查询的结果是多行单列的
      • 可以使用运算符IN判断
    • 3.子查询的结果是多行多列的
      • 子查询可以作为一个虚拟表参与查询

-- 查询工资最高的员工  1.查询最高工资 2.查询员工信息，并且工资等于9000的
SELECT * FROM emp WHERE emp.'salary' = (SELECT MAX(salary) FROM emp); 
-- 查询员工工资小于平均工资的
SELECT * FROM emp WHERE emp.'salary'< (SELECT AVG(salary) FROM emp); 
-- 查询财务部和市场部所有的员工信息
SELETE * FROM dept WHERE name = "财务部" or name = "市场部";
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2 ; 
-- 子查询
SELECT * FROM emp WHERE dept_id IN (SELETE * FROM dept WHERE name = "财务部" or name = "市场部") ; 

-- 查询员工的入职日期是2011年11月11号之后的员工信息和部门信息
-- 子查询
SELECT * FROM dept t1 , (SELECT * FROM emp WHERE emp.'join_date' > '2011-11-11') t2 WHERE t1.id = t2.dept_id ; 

事务

事务基本介绍

• 概念

  • 如果一个包含多个步骤的业务操作，被事务管理，要么同时成功，要么同时失败。

• 操作

  • 1.开启事务 start transaction
  • 2.回滚 rollbackß
  • 3.提交 commit

• mysql数据库中事务默认自动提交

  • 一条DML(增删改)会自动提交一条事务
  • 事务提交的两种方式:
    • 自动提交
      • mysql就是自动提交事务的，一条DML语句会自动提交一条事务
    • 手动提交
      • Oracle 数据库默认是手动提交事务的
      • 需要开启事务，在提交
      • 可以修改自动提交的方式(见下方代码)

-- 开启事务
start transaction;
-- 多个操作的代码.......

-- 如果发现出现问题，回滚事务
rollback;

-- 发现结果没有问题，提交事务
commit;

-- 查看提交方式
SELECT @@autocommit; -- 1代表自动提交，0代表手动提交
-- 修改提交方式
SET  @@autocommit = 0;

事务的四大特征(面试)

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

事务的隔离级别(了解)

• 概念 : 多个事务之间是隔离的，相互独立。但是如果多个事务操作同一批数据则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
• 存在的问题 :
  • 1、脏读: 一个事务读取到另一个事务中没有提交的数据
  • 2、不可重复读(虚度): 在同一个事务中，两次读取到的数据不一样
  • 3、幻读: 一个事务操作(DML操作)数据表中所有的记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
• 隔离级别
  • 1、read uncommitted : 读未提交
    • 产生的问题 : 脏读，不可重复读，幻读
  • 2、read committed ：读已提交 (Oracle 默认)
    • 产生的问题 : 不可重复读，幻读
  • 3、repeatable read : 可重复读 (MySql 默认)
    • 产生的问题 : 幻读
  • 4、serializable : 串行化 , 会把表锁住，等其他事物提交才会解锁
    • 不会产生所有的问题
• 隔离级别从小到大安全性越来越高，但是效率越来越低
  ```sql
• • 数据库查询隔离级别
    select @@tx_isolation
• • 设置数据库隔离界别
    set global transaction isolation level 级别字符串;

DCL ： 管理用户，授权

• 1、管理用户
  • 添加用户
  • 删除用户
  • 修改用户密码
    • 如果忘记了mysql的root账户密码
      • 1、cmd –> net stop mysql 停止mysql服务
        • 需要管理员身份
      • 2、使用无验证方式启动mysql服务 : mysqld –skip-grant-tables
      • 3、打开新的cmd执行修改密码的操作(见下方代码)
      • 4、结束掉mysqlId的进程
      • 5 、启动mysql -> net start mysql ,使用新的密码登录即可
  • 查询用户
    • % 是通配符，意思是可以在任何主机上登录数据库

-- 查询用户
-- 切换到mysql数据库
use mysql;
-- 查询user表
select * from user;

-- 创建用户 主机名为%通配符意思是在任意主机可以登录
create user '用户名'@'主机名' identified by '密码';

-- 删除用户
drop user '用户名'@'主机名';

-- 修改用户密码 password 函数给密码加密
update user set password = password('新密码') where user='用户名';
set password for '用户名'@'主机名' = password('新密码');

• 2、权限管理 :
  • 1、查询权限
    • usage : 可登录
  • 2、授予权限
  • 3、撤销权限

-- 查询权限
show grants for '用户名'@'主机名';

-- 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
grant select delete update on db1.account to 'lisi'@'%';
-- 授予所有权限在任意数据库任意表中,使用通配符完成
grant all on *.* to '张三'@'localhost';

-- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'