MySQL

最新推荐文章于 2024-01-24 20:10:41 发布

LuckyGuyy

最新推荐文章于 2024-01-24 20:10:41 发布

阅读量692

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_36821220/article/details/103060081

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1 介绍数据库概念

数据库概念：是一种持久化存储的数据管理仓库,数据实际上存储在文件中
采用的数据库软件：
- Oracel：大型企业：银行
- Mysql:中小型企业
- SqlServcer：
- DB2

2 安装数据库

mysql服务启动
- 手动
- net start mysql
- net stop mysql
mysql登录
- mysql -uroot -proot
- mysql -hip地址 -uroot -proot
- 开启root账号远程访问：
  - mysql -uroot -proot账号密码
  - GRANT ALL PRIVILEGES ON . TO ‘root’@’%’ IDENTIFIED BY ‘root账号密码’；
  - flush privileges;

3 SQL

基础概念：

可以操作所有的关系型数据库的语言
90%通用，10%特殊：方言

语法：

分号结尾
注释：-- # /* */
字母不区分大小写

分类：

DDL：操作数据库和表

操作数据库

C：创建
- create database if not exists 数据库名称
R：查询
- show databases;
- show create database 数据库名称
U:修改
- alter database 数据库名称 charecter set utf8
D：删除
- drop database if exists 数据库名称
使用：
- use 数据库名称

操作表

R：查询
- show tables；
- desc 表名称；-- 查看表结构
- show create table 表名称;-- 查看建表语句

C:创建表【笔试题的考点】

 1. 语法：
 			create table 表名(
 				列名1 数据类型1,
 				列名2 数据类型2,
 				....
 				列名n 数据类型n
 			);
 			* 注意：最后一列，不需要加逗号（,）
 			* 数据库类型：
 				1. int：整数类型===Integer
 				   bigint === Long
 				   smallint=== Short
 					* age int,
 				2. double:小数类型
 					float
 					* score double(5,2)
 				3. date:日期，只包含年月日，yyyy-MM-dd
 				4. datetime:日期，包含年月日时分秒	 yyyy-MM-dd HH:mm:ss
 				5. timestamp:时间戳类型	包含年月日时分秒	 yyyy-MM-dd HH:mm:ss	
 					* 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
 				6. varchar（25）：字符串===String
 					更节省空间：varchar（25)：hello :只会占用5个字符空间
 				   char(25):hello，任然会占用25个字符空间
 					* name varchar(20):姓名最大20个字符
 					* zhangsan 8个字符  张三 2个字符
 					
 复制表：
 	create table 表名 like 被复制的表名;

D：删除表
- drop table if exists 表名
U:修改表
- 添加一列：alter table 表名 add 列名数据类型；

DML：增删改表中的数据

增加

 insert into 表名(列1,列2) values(数据1,数据2)
 insert into 表名 values(数据1,数据2...)
 注意：
 1.列和值要一一对应
 2.字符类型需要引号

删除

 delete from 表名 where name ='张三'；
 删除所有：
 delete from 表名；1000万条数据，性能低
 truncate table 表名；删除表，创建一个一模一样的表，性能高

修改

 update 表名 set age=18,score=100 where name='张三';
 注意：不加where条件表示修改所有数据[慎重]

DQL：查询表中的数据

基础查询：select * from 表名;

查询部分字段：select name,age from student;
去重： select distinct name,age from student;
计算：加减乘除:如果数据为null，结果永远为null
- ifnull(math,0)+ifnull(english,0)
起别名：as 可以省略:一般针对名字很长的字段
- select name as n,age from student;

条件查询：where

< > = !=
并且：
- age >18 and age <20
- between 18 and 20
- &&
或者：
- or
- in（18，20，21，25，29）【常用】
- ||
null值判断：
- is null
- is not null

模糊查询：

like ‘’
- _:单个任意字符
- %：多个任意字符【常用】
- like ‘马%’
- like ‘%马%’

排序查询：order by

select * from student order by english desc, math desc
先安照英语成绩排名，如果英语成绩一样，再按数据成绩排名
asc :升序，默认的排序规则，可以省略
desc：降序

聚合函数：对某一列进行纵向计算

计算个数：count(列名)，count(*)
- 如果数据为空，那么不会进行统计
  - count(*)
  - count(不可能为null的列（主键）)
  - ifnull()
最大值：max(列名)
最小值：min(列名)
平均值：avg（列名）
求和:sum(列名)
select sum(math) from

分组查询:group by 分组字段

注意：
- 使用分组之后，查询的字段一般只包含：分组字段，聚合函数
  - 如果不是分组字段，那么就会取每个分组的第一条数据
- 可以使用多个聚合函数
- where和having的区别
  - where：分组前进行筛选，后面的条件不是聚合函数
  - having：分组后进行筛选，后面的条件一般都是聚合函数

分页查询:

目的：提高用户体验，提高传输效率（响应效率）

	-- limit 起始索引,每页显示条数;
	
	SELECT * FROM student3 LIMIT 0,3; -- 第1页：0，1，2
	
	SELECT * FROM student3 LIMIT 3,3; -- 第2页：3，4，5
	
	SELECT * FROM student3 LIMIT 6,3; -- 第3页：6，7，8
	
	-- 第10页：（10-1）*3=27
	SELECT * FROM student3 LIMIT 27,3;
	-- 公式：（当前页码-1）*每页显示条数

查询英语成绩前3的同学

SELECT * FROM student3 ORDER BY english DESC LIMIT 0,3;
SELECT * FROM student3 ORDER BY english DESC LIMIT 3;

2 约束

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

主键约束:primary key

一般叫主键索引，主键

1. 注意：
		1. 含义：非空且唯一
		2. 一般情况下一张表只能有一个字段为主键
		   而且一般主键的列名通常为id
		3. 主键就是表中记录的唯一标识

	2. 在创建表时，添加主键约束【常用】
		create table stu(
			id int primary key,-- 给id添加主键约束
			name varchar(20)
		);

	3. 删除主键
		-- 错误 alter table stu modify id int ;
		ALTER TABLE stu DROP PRIMARY KEY;

	4. 创建完表后，添加主键
		ALTER TABLE stu MODIFY id INT PRIMARY KEY;

非空约束：not null：某一列的值，不能为空

1. 创建表时添加约束【常用】
	CREATE TABLE stu(
		id INT,
		NAME VARCHAR(20) NOT NULL -- name为非空
	);
2. 创建表完后，添加非空约束
	ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

3. 删除name的非空约束
	ALTER TABLE stu MODIFY NAME VARCHAR(20);

唯一约束：unique：某一列的值不能重复

唯一约束：一般叫唯一索引

注意：列可以有null值，但是只能有一天null的记录

2. 在创建表时，添加唯一约束【常用】
	CREATE TABLE stu(
		id INT,
		phone_number VARCHAR(20) not null UNIQUE -- 手机号
	);
3. 删除唯一约束[注意]
	ALTER TABLE stu DROP INDEX phone_number;【唯一索引】
4. 在表创建完后，添加唯一约束
	ALTER TABLE stu MODIFY phone_number VARCHAR(20)  UNIQUE;

自动增长：一般结合主键使用

在创建表时，添加主键约束，并且完成主键自增长【常用】
create table stu(
	id int primary key auto_increment,-- 给id添加主键约束
	name varchar(20)
);

外键约束
关于外键不建议使用

注意：

外键不是必须建立的约束，当表与表的关系比较多时部建议建立外键（10表）
外键缺点：影响性能
有些公司的项目没有设置外键，一般都是为了确保性能不受影响
建议：进入公司后，先观察之前的模块有没有建立外键的习惯，没有就不使用，有就使用。

1. 在创建表时，可以添加外键[常用]
外键一般关联的都是主表（部门表）的主键（id）
* 语法：
	create table 表名(
		....
		外键列 --部门id
		constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
	);

2. 删除外键
	ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

3. 创建表之后，添加外键
	ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

级联操作

1. 添加级联操作
	语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
			FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;
2. 分类：
	1. 级联更新：ON UPDATE CASCADE 
	2. 级联删除：ON DELETE CASCADE

3 索引【拓展】[面试考点]

sql-索引的作用(超详细)

索引添加一般是针对数据量比较大的表，（上万条数据）
目的：提高查询效率，对增加了索引的列进行条件查询效率更高。
目：100页
先查索引文件：
- name 设置了索引
- 查询张三：100条
主键索引
- 包含主键约束的概念同时可以作为索引，如果对主键索引的字段进行条件查询时，效率更高。
- select * from student where id=1;
- select * from student where name=‘张三’;
- 第一个sql的查询效率高于第二个sql
```
-- 添加
    ALTER TABLE student ADD PRIMARY KEY (id);【常用】
    ALTER TABLE stu MODIFY id INT PRIMARY KEY;
    -- 删除
    ALTER TABLE student drop PRIMARY KEY;
```
唯一索引
- 包含唯一约束的概念同时可以作为索引，如果对唯一索引的字段进行条件查询时，效率更高。
- select * from student where name=‘张三’;
```
-- 添加
ALTER TABLE student ADD UNIQUE (name);【常用】
ALTER TABLE stu MODIFY name INT UNIQUE;
-- 删除
ALTER TABLE student drop INDEX name;
```

普通索引

仅仅只是提高查询效率

-- 添加
ALTER TABLE student ADD INDEX (name);【常用】
-- 删除
ALTER TABLE student drop INDEX name;

组合索引
- 多个字段组合为一个索引，当以组合索引的全部字段为条件或者以最左边的字段的为条件进行查询时效率更高
- select * from student where name =‘张三’ and age =18;
- select * from student where name =‘张三’ ;
- select * from student where age =18 ;
```
-- 添加
ALTER TABLE student ADD INDEX inx_name_age(name,age);【常用】
-- 删除
ALTER TABLE student drop INDEX inx_name_age;
```
查询效率高到低：主键索引>唯一索引>普通索引>组合索引

4 多表关系

数据库设计：设计表【开发人员需要做的一件事情】
- 一对一（了解）：
  - 如：人，身份证
  - 建表原则：在任意一方建立外键指向另一方的主键
- 一对多
  - 如：员工和部门
  - 建表原则：在多的一方建立外键指向少的一方的主键【重点重点】
- 多对多
  - 如：学生，选课
  - 建表原则：需要建立中间表，至少有两个字段，作为外键指向两个主表的主键

5 三大范式

三大范式的通俗理解

范式：设计数据库的规范。

分类：

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

注意：
- 有些时候公司的表没有完全满足第二或者第三范式，一般是由特殊的业务要求。

6 数据库的备份还原

一般由运维工程师
一般在凌晨去备份前一天的数据
命令：
- 备份：mysqldump -uroot -proot 数据库名称 > d://b.sql;
- 还原：
  - 创建，使用数据库
  - source d://b.sql;
工具：开发

7 多表查询【实践】

内连接查询

SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;【常用】
    
SELECT * FROM emp [INNER]JOIN dept ON emp.`dept_id` = dept.`id`;

外连接查询
- 左外连接【左连接】:以左表为主【常用】
  - 查询的是左表的所有记录，右边有就出来，没有就不显示
    SELECT * FROM emp LEFT OUTER JOIN dept ON emp.dept_id= dept.id;
- 右外连接【右连接】：以右表为主
  - 查询的是右表的所有记录，左边有就出来，没有就不显示
    SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.dept_id= dept.id;
子查询
- 概念：一个查询语句中如果嵌套其他查询语句，那么被嵌套的语句叫子查询。
- 单行单列：><=
- 多行单列:in
  SELECT * FROM emp WHERE dept_id IN(SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');
- 多行多列:
  - 将子查询的结果作为临时表和另外一张表进行关联查询
```
-- 查询员工入职日期是2011-11-11日之后的员工信息(emp)和部门信息(dept)
-- 子查询[解决很多问题]
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
WHERE t1.id = t2.dept_id;

-- 普通内连接[解决一部分需求]
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'
```
- sql编写必考题（笔试题）
  - sql查询【必考】
  - 多表【必考】
- 多表查询练习
  - 分析：
    - 查询哪些表：from
    - 查询哪些字段：select
    - 关联条件：where
  - 编写sql

8 事务

mysql的事务四个特性以及事务的四个隔离级别

概念
- 如果一个业务包含多个操作，该业务被事务管理，多个操作要吗同时成功，要吗同时失败
- 转账：张三 ===> 李四
- 开启事务: start transaction
- 没有错误：提交事务：commit
- 有错误：回滚事务：rollback
- 注意：
  - 如果事务没有提交或者回滚，那么这个数据只会在当前窗口（连接）才能看到数据变化，如果想让其他窗口（连接）也看到数据变化：需要：提交，回滚
  - 如果事务没有提交或者回滚，数据是保存在缓存内，没有写到数据库文件中，如果此时将窗口（连接）关闭，修改的数据就会丢失。
操作
- 开启事务:start transaction
- 没有错误：提交事务：commit
- 有错误：回滚事务：rollback
mysql自动提交（默认）
- 修改提交方式：set @@autocommit=0

四大特性【面试考点】

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

隔离级别
- 读未提交
  - 脏读，不可重复读，幻读
- 读已提交（oracel）
  - 不可重复读，幻读
- 可重复读（mysql默认）
  - 幻读
- 串行化
  - 解决所有问题
  - 相当于：锁表
- 注意：安全性越来越高，但是性能越来越低
- 一般不会修改隔离级别

9 DCL：授权

运维工程师
用户管理
授权管理

练习

部分1

排序查询
- 查询student表，按照英语成绩从高到低排名，然后按照数学成绩从高到低排名
  - select * from student order by english desc,math desc;
分组查询
- 查询student表，按照性别分组，查询男生，女生的数学平均分，且平均分要求大于60分的数据。
  - select sex,avg(math) avg_math from student group by sex having avg_math >60
分页查询
- 分页查询student表，每页显示5条，查询第3页的数据
  - select * from student limit 10,5;
查询英语成绩前3的同学
- select * from student order by english desc limit 0,3

部分2

多表查询
- 内连接
  - 将员工表emp和部门表dept进行内连接
  - select * from emp,dept where emp.dept_id = dept.id
  - select * from emp join dept on emp.dept_id = dept.id
- 外连接
  - 左连接
    - 查询员工表的所有数据，部门数据有就显示，没有就不显示
    - select emp.*,dept.name from emp left join dept on emp.dept_id= dept.id
  - 右连接
    - 查询员工表的所有数据，部门数据有就显示，没有就不显示
    - select emp.*,dept.name from dept right join emp on emp.dept_id= dept.id
- 子查询
  - 单行单列
    - 查询最高工资的员工信息
    - select * from emp where salary =(select max(salary ) from emp)
  - 多行单列
    - 查询财务部和市场部的员工信息
      - select * from emp where dept_id in(select id from dept where name in (‘财务部’,‘市场部’))
  - 多行多列
    - 查询入职日期大于2019-10-11 的员工信息和部门信息(dept)
      - select emp.,dept. from dept,
        (select * from emp where join_date >‘2019-10-11’) t
        where dept.dept_id = t.id