MySQL基础 -- 笔记

数据库的基本概念：

1、英文: DateBase (DB) 2、数据库：用于存储和管理数据的仓库 3、数据库的特点：1、持久化存储数据的。 2、方便存储和管理数据 3、使用了统一的方式操作数据库 -- SQL

什么是SQL？

Structured Query Language：结构化查询语言，其实就是定义了操作有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方，称为“方言”。

SQL通用语法

1、SQL 语句可以单行或多行书写，以分号结尾 2、可用空格和缩进来增强语句的可读性。 3、MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

4、注释：

• 单行注释：--注释内容 或 # 注释内容（mysql特有）

• 多行注释： /* 注释 */

SQL分四类

1、DDL（操作数据库，表）CREATE TABLE、VIEW、INDEX、SYN、CLUSTER 2、DQL（查询表中的数据）SELECT、FROM、WHERE 3、DML（增删改表中的数据）INSERT、UPDATE、DELETE 4、DCL（授权）GRANT，REVOKE

DDL：操作数据库、表

1、操作数据库：CRUD

1、C（Create）：创建

• 创建名为db4的数据库，判断是否存在，字符集为gbk

  create database if not exists db4 character set gbk;

2、R（Retrieve）：查询

• 查询所有数据库的名称

  show databases；

• 查看某个数据库的字符集：查询某个数据库的创建语句

  show create database 数据库名称;

3、U（Update）：修改

• 修改指定数据库的字符集

  alter database 数据库名称 character set 字符集名称;

4、D（Delete）：删除

• 删除数据库

  alter database 数据库名称 character set 字符集名称;

• 判断数据库是否存在，存在再删除

  drop database if not exists 数据库名称;

5、使用数据库

• 查询当前正在使用的数据库名称

  select database();

2、操作表：CRUD

1、C（Create）：创建

• 语法：

create table 表名(
                    列名1  数据类型1，
                    列名2  数据类型2,
                    ....
                    列名3  数据类型3,
            );
注意：最后一列不需要加逗号。

• 常用数据库类型：

1、int ：整数类型
			    *  age int,
2、double：小数类型
			    *  score double(5,2)
3、date：日期，只包含年月日，yyyy-MM-dd4、datetime：日期，包含年月日时分秒 yyyy-MM-dd HH:MM:SS
5、timestamp：时间错类型 包含年月日时分秒 yyyy-MM-dd HH:MM:SS
注：如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间来自动赋值
6、varcher：字符串
				*   name varchar(20) ： 姓名最大20个字符

• 创建表

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

• 复制表

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

2、R（Retrieve）：查询

• 查询某个数据库中所有表的名称

  show tables;

• 查询表结构

  desc 表名;

3、U（Update）：修改

• 修改表名

  alter table 表名 rename to 新的表名;

• 修改表的字符集

  alter table 表名 character set 新的字符集;

• 添加一列

  alter table 表名 add 列明 数据类型;

• 修改列名称，类型

  alter table 表名 change 列名 新列名 新数据类型;
  alter table 表名 modify 列名 新数据类型;

• 删除列

   alter table 表名 drop 列名;

4、D（Delete）：删除

• 删除表

  drop table 表名;
  drop table if exists 表名;

DML：增删改表中数据

1、添加数据：

• 语法

  insert into 表名(列名1,列名2,...,列名n) values(值1,值2,...,值n);

• 注意

  • 列名和值要一一对应。

  • 如果表名后不定义列名，则默认给所有列添加值

    insert into 表名 values(值1,...,值n);

  • 除了数字类型，其他类型需要使用引导(单双都可以)引起来

2、删除数据：

• 语法：

  delete from 表名 [where 条件]

• 注意：

  • 如果不加条件，则删除表中所有记录

  • 如果要删除所有记录

    1、 delete from 表名;  -- 不推荐使用。有多少记录就会执行多少次删除操作
    2、 truncate table 表名; -- 推荐使用，效率更高，先删除表，然后再创建一个一模一样的表

3、修改数据

• 语法：

  update 表名 set 列名1 = 值1, 列名2 = 值2,...[Where 条件];

• 注意：

  • 如果不加任何条件，则会将表中所有记录全部修改

DQL：查询表中的记录

1、语法

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

2、基础查询

1、多个字段的查询

select 字段1, 字段2... from 表名;
注意：如果查询所有字段，则可以使用 * 来替代字段列表;

2、去除重复

• distinct

select DISTINCT address FROM student;

3、计算列

• 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）

  ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null；
  	表达式1：哪个字段需要判断是否为null
  	表达式2：如果该字段为null后的替换值。
  SELECT '字段名1'，'字段名2','字段名1'+'字段名2' FROM '表名';
  	# 如果有null参与运算，计算结果都为null
  SELECT '字段名1'，'字段名2','字段名1' + IFNULL('字段名2',0) FROM '表名';
  	# 判断字段的值是否位null，是则替换为0 IFNULL()

4、起别名

as : as可以省略
SELECT 字段名1，字段名2,字段名1 + IFNULL(字段名2,0) AS 别名 FROM 表名;

3、条件查询

1、where子句后跟条件

2、运算符

1、 >  <  <=  >=  =  <> -- (不等号的意思,和!=一样)
select * from 表名 where 属性 >= ..;
2、between...and -- (在..之间)
select * from 表名 where 属性 between .. and .. ; -- 和(6)意思一样
3、in (集合) -- (在..范围之内)
select * from 表名 where 属性 in(值1,值2,值3); -- 和(7)意思一样
4、like   (模糊查询)
5、is null  (空)
6、and 或 && (和)
select * from 表名 where 属性 条件1 and(&&) 条件2;
7、or 或 ||	(或者)
select * from 表名 where 属性 条件1 or(||) 条件2 or(||) 条件3;
8、not 或 !	(不是) 

查询为空的值

select * from 表名 where 属性 is null;  -- null不能使用等号"=" "!=";

查询不为空的值

select * from 表名 where 属性 is not null;  -- null不能使用等号"=" "!="; 

like（模糊查询）

like：模糊查询
	占位符：
		_:单个任意字符
select * from 表名 where name like "_马"; -- 查询表中姓名（name）第二个字是马的人
		%:多个任意字符
select * from 表名 where name like "%马"; -- 查询表中姓名（name）最后一个字是马的人
select * from 表名 where name like "马%"; -- 查询表中姓名（name）第一个字是马的人
select * from 表名 where name like "%马%"; -- 查询表中姓名（name）有马字的人

4、排序查询

1、语法

order by 子句
	order by 排序字段1 排序方式1,排序字段2 排序方式2...

2、排序方式：

• ASC： 升序（一般默认）

• DESC：降序

3、注意

• 如果有多个排序条件，则当前边的条件值一样的，才会去判断第二条件

4、例子

SELECT * FROM student ORDER BY math; # 按照math的大小排序（默认升序）
SELECT * FROM student ORDER BY math ASC;  # 按照math的大小排序（升序）
SELECT * FROM student ORDER BY math DESC; # 按照math的大小排序（降序）
SELECT * FROM student ORDER BY math DESC,english ASC;	# 按照math的大小排序,先判断math（降序），当math值一样的时候，再判断english（升序）

5、聚合函数

1、含义：

将一列数据作为一个整体，进行纵向的计算。--注意是纵向的计算。

2、聚合函数count

1、一般选择非空的列：主键

2、count(*) -- 计算所i有列只要有一列不为空就

select count(name：需要计算那一列的名称) from student; -- 查询某一列有多少个值（单列的个数）
SELECT COUNT(`name`) AS '学生人数' FROM student;  

3、聚合函数max

select MAX(math) FROM student;  -- 查询math最大值

4、聚合函数min

select MIN(math) FROM student;	-- 查询math最小值

5、聚合函数sum

select SUM(math) FROM student;	-- 查询math总和

6、聚合函数avg

select AVG(math) FROM student;	-- 查询math平均值

7、注意

聚合函数的计算，排除null值。

解决方案：1、选择不包含非空的列进行计算

2、IFNULL函数

SELECT COUNT(IFNULL(english,0)) as 英语考试人数 FROM student;

6、分组查询

1、语法

group by 分组字段;
# 注意： 1、分组之后查询的字段：分组字段、聚合字段  （其他字段没有意义）
	   # 2、where 和 having的区别
       		1、where在分组之前进行限定，如果不满足条件，则不参与分组
       			having在分组之后进行限定，如果不满足结果，则不会被查询出来
       		2、where后不可以跟聚合函数，having可以进行聚合函数的判断。

SELECT sex as '性别' , AVG(math) as '平均分' FROM student GROUP BY sex;
-- 按照性别分组，分别查询男、女同学的平均分
SELECT sex as '性别' , AVG(math) as '平均分' FROM student WHERE math > 70 GROUP BY sex; 
-- 按照性别分组，分别查询男、女同学的平均分  条件math>70 
SELECT sex as '性别' , AVG(math) as '平均分' FROM student WHERE math > 70 GROUP BY sex HAVING count(id)>2; 
-- 按照性别分组，分别查询男、女同学的平均分  条件math>70 count(id)>2 

7、分页查询

1、语法

limit : 开始的索引，每页查询的条数;

2、公式

开始的索引 = （当前页码 - 1） * 每页显示的条数

-- 每页显示三条记录
select * from student limit 0,3; -- 第一页
select * from student limit 3,3; -- 第二页
select * from student limit 6,3; -- 第三页

limit 是一个MySQL“方言”

约束

1、概念：

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

2、分类：

1、主键约束：primary key

1、注意

1、含义：非空且唯一

2、一张表只能有一个字段为主键

3、主键就是表中记录的唯一标识

2、在创建表时，添加主键约束

create table stu(
	id int primary key, -- 给id添加主键约束
    name varchar(20)
);

3、删除主键

alter table stu drop primary key; -- 注意： drop primary key

4、创建完主键后，添加主键

alter table stu modify id int primary key; -- 修改添加id主键

5、自动增长

1、概念

如果某一列时数值类型，使用 auto_increment 可以来完成值的自动增长

2、在创建表时，添加主键约束，并且完成主键自增长

create table stu(
	id int primary key auto_increment, -- 给id添加主键约束,并设置为自增长
    name varchar(20)
);

3、删除自动增长

alter table stu modify id int;

4、添加自动增长

alter table stu modify id int auto_increment;

2、非空约束：not null，值不能为null

1、创建表的时候添加约束

create table stu(
	id int,
    name varchar(20) not null; -- name为非空
);

2、创建完表后，添加非空约束

alter table stu modify name varchar(20) not null; -- 修改name为非空

3、删除非空约束

alter table stu modify name varchar(20); -- 修改name为非空

3、唯一约束：unique，值不能重复

1、创建表时，添加唯一约束

create table stu(
	id int unique, -- 添加了唯一约束
	name varchar(20)
);
-- 注意 ： mysql中，唯一约束限定的列的值可以有多个null;

2、删除唯一约束

alter table stu drop index id; -- 修改id的唯一约束，，，注意：删除唯一约束要用drop index

3、在创建表后，添加唯一约束

alter table stu modify id unique; -- 修改id的唯一约束

4、外键约束：foreign key

1、含义

让表与表产生关系，从而保证数据的正确性。

2、在创建表时，可以添加外键

#语法：
	create table 表名(
    	...
        外键列名称
        constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
    );

3、删除外键

alter table 表名 drop foreign key 外键名称;

4、创建表之后，添加外键

alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称); 

5、级联操作

1、添加级联的操作

语法：
	alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称) on update cascade on delete cascade; 
	-- 级联更新和级联删除能写一块

2、分类

级联更新：on update cascade

级联删除：on delete cascade

注意：级联操作要谨慎使用 ....

数据库的设计

1、多表之间的关系

1、分类

1、一对一

如 ：人和身份证

分析：一个人只有一个身份证，一个身份证只能对应一个人

2、一对多（多对一）

如：部门和员工

分析：一个部门有多个员工，一个员工对应一个部门

3、多对多

如：学生和课程

分析：一个学生可以选择很多门课程，一门课程也可以被很多学生选择

2、实现关系

1、一对多（多对一）

如：部门和员工

实现方式：在多的一方建立外键，只想一的一方的主键

2、多对多

如：学生和课程

实现方式：多对多关系实现需要借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

3、一对一

如：学生和身份证

实现方式：一对一的关系实现，可以在任意一方添加唯一外键指向另一方的主键。

3、案例

2、数据库设计的范式

1、概念

设计数据库时，需要遵循的一些范式。

	设计关系数据库时，遵从不同的要求，设计出合理的关系型数据库，这些不同的规范要求被成为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。在第一范式的基础上进一步满足更多要求的称为第二范式（2NF），其余范式以次类推。
	# 一般说来，数据库只需满足第三范式（3NF）就行了。

2、分类：

1、第一范式（1NF）：每一列都是不可分割的原子数据项

2、第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

几个概念：

1、函数依赖： A-->B ，如果通过A属性（属性组）的值，可与确定唯一B属性的值。则称B依赖于A
	例如：学号--> 姓名 。   （学号，课程名称）--> 分数
2、完全函数依赖：A-->B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值
	例如：（学号，课程名称）--> 分数
3、部分函数依赖：A-->B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中的某一些值即可。
	例如：（学号，课程名称）--> 姓名
4、传递函数依赖：A-->B,B-->c,如果通过A属性（属性组）的值，可以确定唯一属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称C传递函数依赖于A
	例如：学号-->系名，系名-->系主任
5、码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性（属性值）为该表的码
	主属性：码属性组中的所有属性
	非主属性：除了码属性组的属性

3、第三范式（3NF）：在2NF的基础上，任何非主属性不依赖于其他非主属性（在2NF基础上消除传递依赖）

3、数据库的备份和还原

1、命令行：

• 语法：

  • 备份：

    mysqldump -u用户名 -p密码 > 保存的路径

  • 还原：

    1、登录数据库

    2、创建数据库

    3、使用数据库

    4、执行文件。source 文件路径

2、图形化工具：

多表查询

笛卡尔积：

有两个集合A，B，取这两个集合的所有组成情况。

要完成多表查询，需要消除无用的数据

多表查询的分类：

1、内连接查询：

1、隐式内连接：

使用where条件消除无用数据

-- 查询所有员工信息和对应部门的信息 emp(员工表)  dept(部门表)
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
	t1.name,
	t1.gender,
	t2.name
from
	emp t1,
	dept t2， -- 起别名
where
	t1.dept_id = t2.id;

2、显示内连接：

语法：
	select 字段列表 from 表名1 inner join 表名2 on 条件;
例如：
	select * from emp inner join dept on emp.dept_id = dept.id; -- inner可以省略

3、内连接查询：

1、从哪些表中查询数据

2、条件是什么

3、查询哪些字段

2、外连接查询：

1、左外连接

语法：
	select 字段列表 from 表1 left outer join 表2 on 条件; -- outer 可以省略
# 查询的是左表所有数据以及其交集部分

2、右外连接

语法：
	select 字段列表 from 表1 right outer join 表2 on 条件; -- outer 可以省略
# 查询的是右表所有数据以及其交集部分

3、子连接查询：

概念：

查询中嵌套查询，称嵌套查询为子查询。

基本查询
-- 例子：查询工资最高的员工的信息
select MAX(salary) from emp;	-- 查询最高工资
select * from emp where emp.salary = 9000;	-- 查询最高工资等于9000的员工
-- 一步到位 -- 子查询
select * from emp where emp.salary = (select MAX(salary) from emp);		-- 记得加括号

子查询不同情况

1、子查询的结果是单行单列的：

子查询可以作为条件，使用运算符去判断。# 运算符是： > >= < <= = 
-- 例如：查询员工工资小于平均工资的员工
select avg(salary) from emp;
select * from emp where emp.salary = 3;
-- 子查询
select * from emp where emp.salary < (select avg(salary) from emp);

2、子查询的结果是多行单列的：

子查询可以作为条件，使用运算符in来判断
-- 例如：查询财务部和市场部所有员工信息
select id from dept where name = '财务部' or name = '市场部';
select * from emp where emp.dept_id = 3 or emp.dept_id = 2;
-- 子查询
select * from emp where emp.dept_id in(select id from dept where name = '财务部' or name = '市场部');

3、子查询的结果是多行多列的：

子查询可以作为一张虚拟表参与查询
 -- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
 -- 子查询
 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';

事务

1、事务的基本特征

1、概念：

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

2、操作：

1、开启事务：start transaction;

2、回滚事务：rollback;

3、提交事务：commit;

4、MySQL数据库中事务默认自动提交

事务提交的两种方式：
	自动提交：
		mysql就是自动提交的
		一条DML(增删改)语句会自动提交一次事务。
	手动提交：
		Oracle 数据库默认是手动提交事务
		需要先开启事务，在提交
修改事务的默认提交方式：
	查看事务的默认提交方式：select @@autocommit; -- 1 代表自动提交 0 代表手动提交
	修改默认提交方式： set @@autocommit = 0;

2、事务的四大特征

1、原子性

是不可分割的最小操作单位，要么同时成功，要么同时失败。

2、持久性

当事务提交或回滚后，数据库会持久化的保存数据。

3、隔离性

多个事务之间，相互独立

4、一致性

事务操作前后，事务总量不变

3、事务的隔离级别

概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题

存在问题:

1、脏读：一个事务，读取到另一个事务中没有提交的数据

2、不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样

3、幻读：一个事务操作（DML）数据表中所有的记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改

隔离级别：

1、read uncommitted ：读未提交
	产生的问题：脏读、不可重复读、幻读
2、read committed：读已提交 （Oracle）
	产生的问题：不可重复读，幻读
3、repeatable read：可重复读 （MySQL）
	产生的问题：幻读
4、serializable：串行化
	可以解决所有的问题
-- 注意： 隔离级别从小到大安全性越来越高，但是效率越来越低
数据库查询隔离级别
	select @@tx_isolation;
数据库设置隔离级别
	set global transaction isolation level 级别字符串;

DCL：

DBA：数据库管理员

DCL：管理用户，授权

1、管理用户

1、添加用户：

-- 创建用户
create user '用户名' @ '主机名' identified by '密码';
-- 例：
	create user 'zhangsan' @ 'localhost' identified by '123456';

2、删除用户：

-- 删除用户
drop user '用户名' @ '主机名'
-- 例：
drop user 'shangsan' @ 'localhost'

3、修改用户密码：

-- 修改密码
update user set password = password('新密码') where user = '用户名';
set password for '用户名' @ '主机名' = password('新密码');
-- 例
update user set password = password('abc') where user = 'lisi';
set password for 'lisi' @ 'localhost' = password('123456');

-- mysql 中忘记root用户的密码？
	1、cmd -->net stop mysql -- 停止mysql服务（需要管理员运行该cmd）
	2、使用无验证方式启动mysql数据库: mysqld --skip-grant-tables
	3、打开新的cmd窗口，直接输入mysql命令，回车。就可以登录成功
	4、use mysql;
	5、update user set password = password('你的新密码') where user = 'root';
	6、关闭两个窗口
	7、打开任务管理器
	8、启动mysql服务
	9、使用新密码登录

4、查询用户

-- 1、切换到mysql数据库
use mysql;
-- 2、查询user表
select * from user;  -- localhost 本机  ， %（通配符） 任意主机

2、权限管理

1、查询权限：

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

2、授予权限：

-- 授予权限：
grant 权限列表 on 数据库名.表名 to '用户名' @ '主机名';
-- 例
grant select,delete on db1.account to 'lisi' @ '%';
-- 授予所有权限
grant all on *.* to '用户名' @ '主机名';
-- 例
grant all on *.* to 'zhangsan' @ 'loaclhost';

3、撤销权限：

-- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名' @ '主机名';
-- 例：
revoke update on db1.account from 'lisi' @ '%';
-- 撤销所有权限
revoke all on *.* to '用户名' @ '主机名';
-- 例
revoke all on *.* to 'zhangsan' @ 'loaclhost';