MySQL数据库知识的总结

一、数据库

1.1 数据库简介

• 数据库：DataBase 简称：DB。按照数据结构来组织，存储和管理数据的仓库
• 特征：数据的结构化、数据间的共享、减少数据的冗余度，数据的独立性
• 关系型数据库：使用关系模型把数据组织到数据表中。
• 常见的数据库产品：Oracle（oracle公司）、DB2（IBM公司）、SQL Server(微软公司)、MySQL(Oracle公司)。
• 关系型数据库中的数据表是由行和列组成的二维表。

1.2 MySQL数据库软件

使用cmd的一些操作MySQL数据库的命令

使用：
		登陆数据库：
			cmd-->mysql -uroot -p你自己的密码
			
		 服务：系统后台进程
			启动服务： net start mysql
			停止服务： net stop mysql
            删除服务    sc delete mysql 
      		服务面板： services.msc 

二、SQL：结构化查询语言

概念：通过sql语言可以操作所有的关系型数据库。每种数据库之间会存在差异，称为“方言”。
SQL可以分为：

• DDL(Data Definition Language)数据定义语言：用来定义数据库对象：数据库，表，列等。（create, drop,alter等）
• DML(Data Manipulation Language)数据操作语言：用来对数据库中表的数据进行增删改。（insert, delete, update 等）
• DQL(Data Query Language)数据查询语言：用来查询数据库中表的记录(数据)。（select, where 等）
• DCL(Data Control Language)数据控制语言：用来定义数据库的访问权限和安全级别，及创建用户。（GRANT， REVOKE 等）

2.1 SQL的一些通用语法：

1. SQL 语句可以单行或多行书写，以分号结尾。
2. 可使用空格和缩进来增强语句的可读性。
3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
4. 3 种注释
		* 单行注释: -- 注释内容 或 # 注释内容(mysql 特有) 
		* 多行注释: /* 注释 */

2.2 DDL：操作数据库和表（create, drop,alter）

（1）操作数据库(CRUD)：

	1. C(Create):创建
		 创建数据库：
			 create database 数据库名称;
		 创建数据库，判断不存在，再创建：
			 create database if not exists 数据库名称;
		 创建数据库，并指定字符集
			 create database 数据库名称 character set 字符集名;

		 例子： 创建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):删除
		 删除数据库
			 drop database 数据库名称;
		 判断数据库存在，存在再删除
			 drop database if exists 数据库名称;
	5. 使用数据库
		 查询当前正在使用的数据库名称
			 select database();
		 使用数据库
			 use 数据库名称;

操作数据库的实例

-- 1.创建数据库
CREATE DATABASE mydemo;
CREATE DATABASE if not exists mydemo2；
create database mydemo3 character set utf-8;
-- 2.创建数据库并指定字符集
CREATE DATABASE mydemo3 CHARACTER SET UTF8;
-- 3.查看所有数据库
SHOW DATABASES;
-- 4.查看当前使用的数据库
SELECT DATABASE();
-- 5.修改数据库的字符集
ALTER DATABASE mydemo CHARACTER SET UTF8;
-- 6.删除指定的数据库
DROP DATABASE mydemo1;

（2）操作表(CRUD)：

1. C(Create):创建
		1. 语法：
			create table 表名(
				列名1 数据类型1,
				列名2 数据类型2,
				....
				列名n 数据类型n
			);
	复制表：create table 表名 like 被复制的表名;	
2. R(Retrieve)：查询
		 查询某个数据库中所有的表名称
			 show tables;
		 查询表结构
			 desc 表名;
3. U(Update):修改
		1. 修改表名
			alter table 表名 rename to 新的表名;
		2. 修改表的字符集
			alter table 表名 character set 字符集名称;
		3. 添加一列
			alter table 表名 add 列名 数据类型;
		4. 修改列名称 类型
			alter table 表名 change 列名 新列别 新数据类型;
			alter table 表名 modify 列名 新数据类型;
		5. 删除列
			alter table 表名 drop 列名;
4. D(Delete):删除
		 drop table 表名;
		 如果存在删除
		 drop table  if exists 表名 ;

其中，介绍一下数据库中的数据类型

1.int：整数类型		例如：id int,
2.double：浮点型		 例如：double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
										
3.char：固定长度字符串类型；		例如：name char(10)		"张三"
4. varchar：可变长度字符串类型；		例如：name varchar(20)	"张三"
5.blob：字节类型；存字节类型的数据  比如电影字节  图片字节 但是一般不会把字节数据存到数据库当中
6.date：日期类型，格式为：yyyy-MM-dd；
7.time：时间类型，格式为：hh:mm:ss
8.datetime：日期时间类型   yyyy-MM-dd hh:mm:ss
9.timestamp：时间戳类型	 yyyy-MM-dd hh:mm:ss    如果该类型的字段不给赋值，则默认当前时间

操作表的实例

--1.创建表
create table student(
      id int,
      name varchar(10),
      sex char(1),
      birthday timestamp
);
-- 2.查看数据库中所有表
show TABLES;
-- 3.查看表结构
desc student;
-- 4.表的删除
drop table syudent;
drop table  if exists syudent;
-- 5.修改表
-- 给表中添加一个字段 add(列名 数据类型)
alter table student add(age int(11));
-- 删除表中的字段 drop 字段名
alter table student drop age;
-- 修改字段名称 change 旧字段名 新字段名 数据类型（长度）
alter table student change name username varchar(20);
-- 修改字段的数据类型 modify 旧字段名 新的数据类型(长度)
alter table student modify sex varchar(2);

2.3 DML：增删改表中的数据（insert, delete, update）

1. 添加数据：
	 语法：
		 insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
	 注意：
		1. 列名和值要一一对应。
		2. 如果表名后，不定义列名，则默认给所有列添加值
			insert into 表名 values(值1,值2,...值n);
		3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来
2. 删除数据：
	 语法：
		 delete from 表名 [where 条件]
	 注意：
		1. 如果不加条件，则删除表中所有记录。
		2. 如果要删除所有记录
			1. delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作
			2. TRUNCATE TABLE 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。
3. 修改数据：
	 语法：
		 update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

	 注意：
		1. 如果不加任何条件，则会将表中所有记录全部修改。

实例

-- 1.往表中插入数据 brithday字段的数据类型为timestamp，所以不给定值则会有默认当前时间
insert into student(id,username,sex) values (1,'zhangsan','m');
insert into student(id,username,sex,birthday) values (2,'lisi','m',null);
insert into student(id,username,sex,birthday) values (3,'wangwu','m','1990-01-01 15:30:30');
-- 简写形式 表中所有的字段都要插入
insert into student values (2,'zhaoliu','m','1995-03-01 15:30:30');
-- 2.删除表中所有的数据
delete from student;   -- 没有条件的删除
-- 3.根据条件删除某些数据 where 条件 =、!=、<>(不等于)、<、<=、>、>=；&& and  || or
delete from student where id=1;
delete from student where sex='m' && username='lisi'; --并且
delete from student where sex='m' and username='wangwu'; -- 并且
delete from student where id=1 || id>2; --或者
delete from student where id=1 or id>2; -- 或者
--4.修改表中的数据 update 表名 set 字段名=新值,字段名2=值2....
-- 不带有条件的修改，那就把表中的数据全部改了。
update student set username='wanglaohu',sex='w';
-- 带有条件的修改 where 条件  =、!=、<>(不等于)、<、<=、>、>=；&& and  || or
update student set username='zhoubapi',sex='m' where id=1;
update student set username='zhoubapi',sex='m' where id=2 and birthday='2020-03-29 14:43:02';

2.4 DQL：查询表中的数据（select, where）

语法：
			SELECT 
				selection_list /*要查询的列名称*/
			FROM 
				table_list /*要查询的表名称*/
			WHERE 
				condition /*行条件*/
			GROUP BY 
				grouping_columns /*对结果分组*/
			HAVING 
				condition /*分组后的行条件*/
			ORDER BY 
				sorting_columns /*对结果分组*/
			LIMIT 
				offset_start, row_count /*结果限定*/

		
		 条件查询：where 子句
			=、!=、<>(不等于)、<、<=、>、>=；
			BETWEEN…AND；  在什么范围之间
			IN(set)；
			IS NULL；为空
			IS NOT　NULL　不为空
			AND； 并且
			OR；   或者
			NOT；非

1.模糊查询：like
	通配符：（1） _  :匹配单个任意字符
                  （2）%   匹配多个任意字符
2.字段控制：
		修改字段的名称：AS（可以省略）
		字段运算：null参与的运算，结果都为null  
		去除重复记录：DISTINCT     
		排序：order by  排序规则
				（1）ASC  升序排序 默认值 
				（2）DESC  降序排列
3.聚合函数：用来做纵向运算的函数
		COUNT()：统计指定列不为NULL的记录行数；
		MAX()：计算指定列的最大值
		MIN()：计算指定列的最小值
		SUM()：计算指定列的数值和
		AVG()：计算指定列的平均值
4.分组查询：group by     一般配合聚合函数使用 查出的数据才有意义
		查询的字段：（1）分组字段本身 （2）聚合函数
5.分页查询：limit （MySQL的分页查询的关键字）起始索引=(页码-1)*每页的条数
 oracle：rownum 分页方言
 sqlserver：top 分页方言

在分组查询中where和having的区别：
where：在分组之前对条件进行限定。不满足条件，就不会参与分组
having：在分组之后，对结果集的筛选

实例

-- 创建表：
CREATE TABLE emp(
	empno		INT,
	ename		VARCHAR(50),
	job		VARCHAR(50),
	mgr		INT,
	hiredate	DATE,
	sal		DECIMAL(7,2),
	comm		decimal(7,2),
	deptno		INT
) ;
-- 表中插入数据：
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
-- 1.查询表中的所有字段：SELECT * FROM 表;
SELECT * FROM emp;
-- 2.查询表中的部分字段：SELECT 字段,字段,字段... FROM 表;
SELECT empno,ename,job FROM emp;
-- 3. 过滤重复字段行
SELECT DISTINCT empno,ename,job FROM emp;
-- 4.查询字段起别名
SELECT empno,ename AS name FROM emp;
-- 5.查询指定字段
SELECT empno,ename,job FROM emp WHERE empno=7369;
-- 6.模糊查询 ename 的第二个字符是m
SELECT * FROM emp WHERE  ename like '_m%';
-- ename 的任意三个字符
SELECT * FROM emp WHERE ename like='___';
-- ename 包含m的
SELECT * FROM empwhere WHERE  ename like  '%m%';
-- 7.字段运算   null参与的运算，结果都为null
SELECT ename,sal+comm AS 总收入 FROM emp ;
-- ifnull(字段名称,如果是null的替换值)
SELECT ename,(sal+ifnull(comm,0)) AS 总收入FROM emp ;
-- 8.去重
SELECT distinct sal FROM emp ;
-- 9.排序 升序排序
SELECT * FROM emp order by sal ASC ;
-- 降序排序
SELECT * FROM emp order by sal DESC ;

分组和分页查询语句

-- 1.聚合函数 最大值
SELECT MAX(sal) FROM emp;
-- 查询最高工资的人是谁  使用子查询
SELECT deptno,ename,sal FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);
-- 配合分组一起使用  GROUP BY 分组的字段 查询部门的平均工资
SELECT deptno AS 部门编号,AVG(sal) FROM emp GROUP BY deptno;
-- 查询每个部门最高的工资
SELECT deptno AS 部门编号,MAX(sal) AS 最高工资 FROM emp GROUP BY deptno;
-- 查询 各个部门  员工工资大于1500 的平均工资  并且平均工资 大于2000的部门
SELECT deptno AS 部门编号,AVG(sal) AS pj FROM emp WHERE sal>1500 GROUP BY deptno HAVING pj >2000;
(1)WHERE 是用来过滤记录的,HAVING是用来过滤分组的
(2)先过滤WHERE 后过滤HAVING.  
(3)WHERE是在查询表，分组之前对条件进行限定
(4)HAVING是在数据查询后并且分完组后对分组的结果进行过滤的
(5)HAVING必须跟在GROUP BY后
-- 2.分页查询 limit 起始索引,每页的条数  起始索引=(页码-1)*每页的条数
-- 第一页的数据 每页 展示4条
SELECT * FROM emp LIMIT 0,4；
-- 第二页的数据 每页4条
SELECT * FROM emp LIMIT 4,4；

三、约束

MySQL中常用的约束：

• 主键约束：primary key
• 自增长约束 auto_incrment 加在整数型的字段配和主键约束来使用
• 唯一约束：unique
• 非空约束：not null
• 外键约束：foreign key

3.1主键约束

PRIMARY KEY：被修饰过的字段非空且唯一，一张表只能有一个主键。但是这个主键可以包含多个字段

-- 创建表，添加主键约束
CREATE TABLE test1(
     tid INT PRIMARY KEY, -- tid 添加了主键约束
     tname VARCHAR(15)
)
CREATE TABLE test2(
     tid INT, 
     tname VARCHAR(15),
     PRIMARY KEY(tid) -- 把tid设置为主键
)
CREATE TABLE test3(
     tid INT,
     tname VARCHAR(15)
)
-- 创建表完成之后，添加主键
ALTER TABLE test3 ADD PRIMARY KEY(tname);
-- 联合主键，可以将多个字段设置为主键
ALTER TABLE test3 ADD PRIMARY KEY(tname,tid);

3.2唯一约束

UNIQUE：不允许值重复，但是null值不起作用

CREATE TABLE test5(
     tid INT PRIMARY KEY, -- tid 添加了主键约束
     tname VARCHAR(15) UNIQUE -- 添加了唯一约束，tname的值不能够重复
)
INSERT INTO test6 VALUES(2,'bbb');-- 插入数据成功
INSERT INTO test6 VALUES(3,NULL);-- 插入数据成功
INSERT INTO test6 VALUES(4,NULL);-- 插入数据成功

3.3非空约束

NOT NULL：被修饰的字段不能为空

    CREATE TABLE test4(
     tid INT PRIMARY KEY, -- tid 添加了主键约束
     tname VARCHAR(15) NOT NULL  -- 非空约束
   )
   INSERT INTO test5 VALUES(1,'aaa');-- 插入数据成功
   INSERT INTO test5 VALUES(2,NULL);--插入数据失败

3.4自增长约束

AUTO_INCREMENT：被修饰的字段类型可以自动增长，配合主键进行使用

CREATE TABLE test8(
     tid INT AUTO_INCREMENT PRIMARY KEY, -- 主键自增长
     tname VARCHAR(15) UNIQUE NOT NULL
    )
 -- 主键自增长，你不给值，或者给成null 这个主键会自己自增默认从1开始
 INSERT INTO test8 VALUES(NULL,'aaa');
 INSERT INTO test8(tname) VALUES('ddd');
 -- 根据主键进行删除一条记录后，然后添加，自增长的值是按着上一条数据的值进行增加

3.5外键约束

FOREIGN KEY：让表与表之间产生关系，从而保证数据的正确性
比如：在用户和订单的关系中，一个用户可以有多个订单信息，一个订单信息只能属于一个用户，那么这种情况下，用户可以看成主表（一表），订单可以认为是从表（多表），两个表之间存在一定的关系，一般会在多表上增加一个外键，去关联主表的主键。
添加了外键的约束之后，两个表之间存在一定的关联：

• 一表一方，不能删除多表还在引用的数据
• 多表一方，不能添加主表没有描述的数据

这样的话，主表进行删除数据的话，就会存在一个问题：就是我们必须先对从表删除有关的数据，然后在返回主表进行删除。这样的话会很麻烦，所以我们可以使用级联删除。
语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称
FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE ;
分类：
1. 级联更新（ON UPDATE CASCADE ）：主表的数据发生改变，那么从表中关联的数据也会跟着改变
2. 级联删除（ON DELETE CASCADE ）：主表中的数据进行删除，那么从表中关联的数据也会跟着删除

添加外键约束：

1. 在创建表时，可以添加外键
		 语法：
			create table 表名(
				....
				外键列
				constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
			);
	2. 删除外键
		ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
	3. 创建表之后，添加外键
		ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

外键约束案例

	-- 创建用户表
	CREATE  TABLE USER(
		id INT PRIMARY KEY AUTO_INCREMENT,
		username VARCHAR(20)
	);
	-- 创建订单表
	CREATE  TABLE orders(
		id INT PRIMARY KEY AUTO_INCREMENT,
		totalprice DOUBLE,
		user_id INT   
	);
 	-- 表已经创建好的情况下，修改表增加外键约束 
 	-- 订单表中的user_id关联用户表中的id
 	ALTER TABLE  orders ADD FOREIGN KEY(user_id) REFERENCES USER(id);
 	-- 添加级联删除
 	ALTER TABLE  orders ADD FOREIGN KEY(user_id) REFERENCES USER(id) ON DELETE CASCADE;
 	-- 添加级联更新
 	ALTER TABLE  orders ADD FOREIGN KEY(user_id) REFERENCES USER(id) ON DELETE CASCADE ON UPDATE CASCADE; 

四、多表查询

内连接:
	格式1:显式的内连接
		select a.*,b.* from a [inner] join b on ab的连接条件
	格式2:隐式的内连接
		select a.*,b.* from a,b where ab的连接条件
外连接:
	左外连接:
		select a.*,b.* from a left [outer] join b on 连接条件;  outer 可以不写
		意思:
			先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),符合条件则展示出来,不符合以null值展示.
	右外连接:
		select a.*,b.* from b right [outer] join a on 连接条件;  outer 可以不写
		意思:
			先展示jion右边的表(a)表的所有数据,根据条件关联查询join左边的表(b),符合条件则展示出来,不符合以null值展示.
子查询:
	一个查询依赖另一个查询.

对上面的用户表和订单表进行多表查询

-- 1.查询用户的订单,没有订单的用户不显示
-- 显示内连接
select user.*,orders.* from user join orders on user.id=orders.user_id;
-- 隐示内连接
select user.*,orders.* from user ,orders where user.id=orders.user_id;
--2.查询所有用户的订单详情
--左外连接，左边全显示
SELECT user.*,orders.* FROM USER LEFT OUTER JOIN orders ON user.`id`=orders.`user_id`;
--右外连接，右边全显示
SELECT user.*,orders.* FROM orders RIGHT OUTER JOIN USER ON user.`id`=orders.`user_id`;
--3.查询所有订单的用户详情
--左外连接
SELECT user.*,orders.* FROM orders LEFT OUTER JOIN USER ON user.`id`=orders.`user_id`;
--右外连接
SELECT user.*,orders.* FROM USER RIGHT OUTER JOIN orders ON user.`id`=orders.`user_id`;

五、自连接查询

可以通过表的别名，给一张表起两个不同的名字，看做成两张表从而进行查询。
比如：上面的emp为例，员工的编号和员工对应上级的编号都存在一张表中。员工号7369 的 SMITH 他对应的老板编号是(MGR) 7902 而7902 又是员工FORD(7902) 那FORD 对应的老板编号又是 7566。
对上面的情况，我们就可以使用自连接查询

SELECT e1.`empno`,e1.mgr,e1.ename,e2.ename,e2.`empno` FROM emp e1,emp e2 WHERE e1.`mgr`=e2.`empno`;