MySQL基础

写在前面:在使用sql软件写代码的时候,
==一定要手动保存!==一定要保存!一定要保存!
(不说了,都是泪)

一.数据库概述

数据库：存储、维护和管理数据的集合。

数据库（DataBase，DB）：指长期保存在计算机的存储设备上，按照一定规则组织起来，可以被各种用户或应用共享的数据集合。
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。

二.Sql的分类

DD（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等
DML(Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；
DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

注意:sql语句以;结尾

2.1 DDL:操作数据库、表、列等

使用关键字 CREATE ALTER DROP

1.创建

Create database mydb1;

2.查询

-- 查看当前数据库服务器中的所有数据库
	Show databases;
-- 查看前面创建的mydb2数据库的定义信息
	Show  create  database mydb2;
-- 删除前面创建的mydb3数据库
	Drop database mydb3;

3.修改

--  查看服务器中的数据库，并把mydb2的字符集修改为utf8;
	 alter database mydb2 character set utf8;

4.删除

--  查看服务器中的数据库，并把mydb2的字符集修改为utf8;
	 Drop database mydb3;

2.1.2 操作数据表

• 语法：
  create table 表名(
  字段1 字段类型,
  字段2 字段类型,
  …
  字段n 字段类型
  );

* 常用数据类型：
	int：整型
	double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
	char：固定长度字符串类型；
	varchar：可变长度字符串类型；
	text：字符串类型；
	blob：字节类型；
	date：日期类型，格式为：yyyy-MM-dd；
	time：时间类型，格式为：hh:mm:ss
	timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss  会自动赋值
	datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

当前数据库中的所有表:SHOW TABLES;
查看表的字段信息:DESC employee;
删除image列,一次只能删一列:ALTER TABLE employee DROP image;
表名改为user:RENAME TABLE employee TO user;
查看表格的创建细节:SHOW CREATE TABLE user;
修改表的字符集为gbk:ALTER TABLE user CHARACTER SET gbk;
列名name修改为username: ALTER TABLE user CHANGE name username varchar(100);

删除表
DROP TABLE user ;

2.2DML操作(重要)

查询表中的所有数据 SELECT * FROM 表名;
使用关键字 INSERT UPDATE DELETE
DML是对表中的数据进行增删改的操作,不要与DDL混淆了

2.2.1 插入操作：INSERT:

语法： INSERT INTO 表名（列名1，列名2 ...）
   	   VALUES(列值1，列值2...);

注意：
a)列名与列值的类型、个数、顺序要一一对应。
b)可以把列名当做java中的形参，把列值当做实参。
c)如果插入空值，请使用null
d)插入的日期和字符一样，都使用引号括起来

   CREATE TABLE emplement(
   		id INT,
   	NAME VARCHAR(100),
   	gender VARCHAR(10),
   	birthday DATE,
   	salary FLOAT(10,2),
   	entry_date DATE,
   	RESUME TEXT
   );

INSERT INTO emplement(id,NAME,gender,birthday,salary,entry_date,RESUME)
VALUES(1,'zhangsan','female','1990-5-10',10000,'2015-5-5-','good girl');

INSERT INTO emplement(id,NAME,gender,birthday,salary,entry_date,RESUME)
VALUES(2,'lisi','male','1995-5-10',10000,'2015-5-5','good boy');

INSERT INTO emplement(id,NAME,gender,birthday,salary,entry_date,RESUME)
VALUES(3,'你好','male','1995-5-10',10000,'2015-5-5','good boy');

SELECT * FROM emplement;

2.2.2 修改操作 UPDATE

语法：UPDATE 表名 SET 列名1=列值1，列名2=列值2 ...
WHERE 列名=值

2.2.3 删除操作 DELETE

语法 ： DELETE 表名 【WHERE 列名=值】

-- 删除表中名称为’zs’的记录。
DELETE FROM emp WHERE name=‘zs’;
-- 删除表中所有记录。
DELETE FROM emp;
-- 使用truncate删除表中记录。
TRUNCATE TABLE emp;

delete删除表中的数据,表结构还在;删除后的数据可以找回
truncate删除是把表直接drop掉,然后再创建一个同样的新表。
删除的数据不能找回。执行速度比delete快。

2.3DQL操作

使用关键字 ==SELETE ==

语法： SELECT 列名 FROM表名 
   	【WHERE --> BROUP BY -->HAVING--> ORDER BY】

2.3.1基础查询

1.1查询所有列 : SELECT * FROM stu;
1.2查询指定列 : SELECT sid, sname, age FROM stu;

2.3.2 条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
 =、!=、<>、<、<=、>、>=；
 BETWEEN…AND；
 IN(set)；
 IS NULL；
 AND；
 OR；
 NOT；

练习:
-- 查询性别为女，并且年龄50的记录
　　SELECT * FROM stu 
   WHERE gender='female' AND ge<50;

-- 查询学号为S_1001，或者姓名为liSi的记录
   SELECT * FROM stu 
   WHERE sid ='S_1001' OR sname='liSi';

-- 查询学号为S_1001，S_1002，S_1003的记录
   SELECT * FROM stu 
   WHERE sid IN ('S_1001','S_1002','S_1003');

2.3.3 模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE。

通配符:
	_ 任意一个字母    
    %：任意0~n个字母

练习:
-- 查询姓名由5个字母构成，并且第5个字母为“i”的学生记录
   	SELECT * FROM stu   WHERE sname LIKE '____i';
   	
-- 查询姓名以“z”开头的学生记录
   	SELECT * FROM stu   WHERE sname LIKE 'z%';(其中“%”匹配0~n个任何字母。)

-- 查询姓名中第2个字母为“i”的学生记录
   	SELECT * FROM stu    WHERE sname LIKE '_i%';

-- 查询姓名中包含“a”字母的学生记录
   	SELECT * FROM stu     WHERE sname LIKE '%a%';

2.3.4 字段控制查询

使用关键字 DISTINCT AS

1.去除重复记录(两行或两行以上记录中系列的上的数据都相同）:
需要使用distinct
   		SELECT DISTINCT sal FROM emp;
2.查看雇员的月薪与佣金之和
   		SELECT *,sal+IFNULL(comm,0) FROM emp;
	(两个类型不同的数据求和时,要把NULL转换成数值0)
3.给列名添加别名
    SELECT *, sal+IFNULL(comm,0) AS total FROM emp;

2.3.5排序

使用关键字 ASC DESC

-- 查询所有学生记录，按年龄升序排序
SELECT * FROM stu ORDER BY sage ASC;

-- 查询所有学生记录，按年龄降序排序
SELECT * FROM stu ORDER BY age DESC;

2.3.6 聚合函数

使用关键字 SUM AVG MAX MIN COUNT

1.-- 查询emp表中记录数：
	SELECT COUNT(*) AS cnt FROM emp;
  -- 统计月薪与佣金之和大于2500元的人数：
	SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;
2.-- 查询所有雇员月薪和，以及所有雇员佣金和：
	SELECT SUM(sal), SUM(comm) FROM emp;
  -- 统计所有员工平均工资：
	SELECT AVG(sal) FROM emp;
3.-- 查询最高工资和最低工资：
	SELECT MAX(sal), MIN(sal) FROM emp;

2.3.7 分组查询

使用关键字 GROUP BY HAVING子句

1.-- 查询每个部门的部门编号以及每个部门的人数：
	SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
2.-- 查询每个部门的部门编号以及每个部门工资大于1500的人数：
	SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 
	GROUP BY deptno;
3.-- 查询工资总和大于9000的部门编号以及工资和
	SELECT deptno,SUM(sal) FROM emp GROUP BY deptno
	HAVING SUM(sal)>9000;

注：having与where的区别:
1.having是在分组后对数据进行过滤.
where是在分组前对数据进行过滤
2.having后面可以使用分组函数(统计函数)
where后面不可以使用分组函数。
WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

2.3.8分页

使用关键字 LIMIT

1.-- 查询5行记录，起始行从0开始
   		SELECT * FROM emp LIMIT 0, 5;
注意，起始行从0开始，即第一行开始！

查询语句书写顺序：
select – from- where- group by- having- order by-limit
查询语句执行顺序：
from - where -group by - having - select - order by-limit

三.数据的完整性

保证用户输入的数据保存到数据库中是正确的,确保数据的完整性,在创建表时给表中添加约束.
完整性的分类：实体完整性 域完整性 引用完整性

3.1实体完整性

约束类型 主键约束(primary key) 唯一约束(unique)
自动增长列(auto_increment)

3.1.1主键约束(primary key)

注：每个表中要有一个主键。 特点：数据唯一，且不能为null

-- 主键约束创建方式
-- 方式一
   	CREATE TABLE student(
   		id INT PRIMARY KEY,
   		NAME VARCHAR(50)
   	);
-- 方式二
   	CREATE TABLE student(
   		id INT,
   		sname VARCHAR(5),
   		PRIMARY KEY(id)
   );
-- 方式三
   	CREATE TABLE student(
   		id INT,
   		sname VARCHAR(50),
   		class INT
   );
   	ALTER TABLE student ADD PRIMARY KEY(id);

出现黄色的小钥匙标志,代表主键创建成功

3.1.2唯一约束(unique)

保证数据不能重复

   	CREATE TABLE stu(
   		id INT PRIMARY KEY ,
   		sname VARCHAR(50),
   		class INT UNIQUE,
   		address VARCHAR(50)
   	);

3.1.3自动增长列(auto_increment)

实现数据的自动增长

-- 给主键添加自动增长的数值，列只能是整数类型
   	CREATE TABLE student(
   		Id INT PRIMARY KEY AUTO_INCREMENT,
   		sName VARCHAR(50),
   		class INT UNIQUE,
   		address VARCHAR(50)
   	);

3.2域完整性

域完整性的作用：限制此单元格的数据正确
约束类型 数据类型 非空约束 默认值约束 check约束

3.2.1数据类型

数值类型、日期类型、字符串类型 类型要匹配

3.2.2非空约束(not null)

   	CREATE TABLE student(
   	Id int pirmary key,
   	Name varchar(50) not null,
   	Sex varchar(10)
   );
   INSERT INTO student values(1,’tom’,null);

3.2.3 默认值约束 (default)

   	CREATE TABLE student(
   		Id int pirmary key,
   		Name varchar(50) not null,
   		Sex varchar(10) default ‘男’
   );

3.3引用完整性(主外键约束)

外键约束: FOREIGN KEY (必须有主键)

   	CREATE TABLE stu(
   		id INT PRIMARY KEY,
   		sname VARCHAR(50) NOT NULL,
   		sex VARCHAR(10) DEFAULT '男',
   		sclass INT UNIQUE
   	);
   	CREATE TABLE score(
   		id INT ,			-- 外键列的数据类型一定要与主键的类型一致
   		score INT,
   		sclass INT,
   		CONSTRAINT fk_score_id FOREIGN KEY(id) REFERENCES student(id)
   );

   	-- 第二种创建方式
   	ALTER TABLE score1 ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid)
   	 REFERENCES stu(id);

3.4表与表之间的关系

为什么要拆表?
避免表中存在大量冗余数据,但并不是拆的越碎越好,会增加成本,应根据需要进行拆分.

四.多表查询

多表查询有如下几种：


• 合并结果集；UNION 、 UNION ALL
• 连接查询

  • 内连接 [INNER] JOIN ON

  • 外连接 OUTER JOIN ON

    • 左外连接 LEFT [OUTER] JOIN
    • 右外连接 RIGHT [OUTER] JOIN
    • 全外连接（MySQL不支持）FULL JOIN

  • 自然连接 NATURAL JOIN

  • 子查询

4.1合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起！

被合并的两个结果：列数、列类型必须相同。
列数不同的时候可以只筛选其中相同的部分进行合并

4.2连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

-- 使用主外键关系做为条件来去除无用信息
   	SELECT * FROM student s,score1 sc WHERE s.Id=sc.id;

--上面查询结果会把两张表的所有列都查询出来，
-- 也许你不需要那么多列，这时就可以指定要查询的列了。
   	SELECT s.Id,s.sName,s.class,sc.score FROM 
   	student s,score1 sc WHERE s.Id=sc.id;

4.2.1内连接(INNER JOIN)

SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;
要是还有其他条件,继续加where限定即可

4.2.2左连接(LEFT OUTER JOIN)

左连接是先查询出左表（即以左表为主),然后查询右表,右表中满足条件的显示出来，不满足条件的显示NULL。

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

4.2.3右连接(RIGHT OUTER JOIN)

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

4.3自然连接(NATURE JOIN)

自然连接无需你去给出主外键等式，它会自动找到相同的键

SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL LEFT JOIN dept;
SELECT * FROM emp NATURAL RIGHT JOIN dept;

4.4子查询(嵌套)

• 子查询出现的位置：
  - where后，作为条为被查询的一条件的一部分；
  -from后，作表；
• 当子查询出现在where后作为条件时，还可以使用如下关键字：
  -any
  -all
• 子查询结果集的形式：
  -单行单列（用于条件）
  -单行多列（用于条件）
  -多行单列（用于条件）
  -多行多列（用于表）

练习
   -- 1.工资高于JONES的员工。
   SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='JONES')
   -- 2.查询工作和工资与MARTIN（马丁）完全相同的员工信息
   	SELECT * FROM emp WHERE (job,sal) IN
    (SELECT job,sal FROM emp WHERE ename='MARTIN')
   -- 3.工资高于30号部门所有人的员工信息
   SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

五.三大范式

概念:设计数据库时,需要遵循的一些规范,
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范越高的范式数据库冗余越小
分类:
第一范式(1NF):每一列都是不可分割的原子数据项
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于码(在1NF的基础上消除非主属性对主码的部分函数依赖关系)
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其他非主属性(在2NF基础上消除传递依赖)

5.1第一范式(1NF)

存在的问题:
1.存在非常严重的数据冗余问题:姓名,系名,系主任
2.数据添加存在问题,添加新开设的系和系主任时,数据不合法
3.数据删除存在问题,张无忌同学毕业了,删除数据,会将系的数据一起删除

为解决以上问题,引入第二范式

5.2第二范式(2NF)

第二范式(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属性的值,再通过B属性(属性组)的值唯一确定C属性的值,则称C传递依赖于A
   			例如:学号-->系名     系名-->系主任
   	5.在一张表中,如果一个属性(属性组),被其他所有的属性所完全依赖,则称这个属性(属性组)为该表的码
   			例如:该表的码为:(学号,课程名称)
   		*主属性:码属性组中的所有属性
   		*非主属性:除了码属性组的其余属性

第二范式中,消除了第一范式存在的数据冗余的问题,但还是存在2和3两个问题
存在的问题:
1.数据添加存在问题,添加新开设的系和系主任时,数据不合法
2.数据删除存在问题,张无忌同学毕业了,删除数据,会将系的数据一起删除

5.3第三范式(3NF)

第三范式(3NF):在2NF基础上,任何非主属性不依赖于其他非主属性
(在2NF基础上消除传递依赖)

第三范式很好的解决了以上存在的三个问题.以此来规范数据库的合理规划

六.事务

概念:如果一个包含多个事务的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败,以保证操作的安全性
操作:
1.开启事务:start transaction
2.回滚:rollback(如果事务出现异常,就会回滚到事务开启之前)
3.提交:commit(如果没有异常则提交数据)
4.mySQL数据库中事务默认自动提交
* 事务提交的两种方式:

*1.自动提交:
mysql默认自动提交
一条DML(增删改)语句会自动提交一次事务
2.手动提交:
Oracle数据库默认是手动提交事务
需要先开启事务,再提交

*修改事务的默认提交方式:
1.查看事务的默认提交方式:SELECT @@autocommit; --1代表自动提交 --0代表手动提交
2.修改默认提交方式:set @@autocommit = 0;

6.1事务的四大特征

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

6.2事务的隔离级别(了解)

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

*存在的问题:
   	1.脏读:一个事物,读取到另一个事务中没有提交的数据
   	2.不可重复读(虚读):在同一个事务中,两次读到的数据不一样
   	3.幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
*隔离级别:
	1.read uncommitted :读未提交
   			产生的问题:脏读,不可重复读,幻读
    2.read committed:读已提交(Oracle)
     		产生的问题:不可重复读,幻读
    3.repeatable read:可重复读
    		产生的问题:幻读
    4.serializeable:串行化
    		可以解决所有问题
    		
   	数据库查询隔离级别:select @@tx_isolation;
   	数据库设置隔离级别:set global transaction isolation level  级别字符串

注意:隔离级别从小到大安全性越来越高,但是效率越来越低