Mysql数据库基础知识
一、Mysql5.7.19解压版安装
Mysql基本介绍：
Mysql是一个关系型数据库管理系统，由瑞典MYSQL AB公司开发，属于Oracle旗下产品。
Mysql是一个单进程多线程的数据库，在innodb中大概3种线程为：1、主线程Master Thread；2、IO Thread线程，用于异步处理写请求；3、purge Thread线程，用于删除undo日志。
下载地址https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip
特别说明：
安装步骤：
1.下载得到zip文件
2.解压的路径不要带有中文和空格
3.解压到这里
4.添加环境变量，其目的是：可以在dos下的任意目录操作mysql相关指令
WIN + E -> 右键此电脑 -> 属性 -> 高级系统设置 -> 高级选项卡 -> 环境变量， 在path环境变量中增加mysql安装目录，到那个bin目录下面，如：D:\mysql-5.7.19-winx64\bin\
5.在D:\mysql-5.7.19-winx64\目录下创建my.ini文件，需要我们自己创建
[client]
port=3306
default-character-set=utf8
[mysqld]
#设置为自己的mysql安装目录
basedir=D:\mysql-5.7.19-winx64\
#设置为mysql的数据目录
datadir=D:\mysql-5.7.19-winx64\data\
port=3306
character_set_server=utf8
#跳过安全检查，注释以后需要输入正确的用户名和密码才能登录
#skip-grant-tables
6.使用管理员身份打开cmd，并切换到D:\mysql-5.7.19-winx64\bin目录下执行 mysqld -install (mysqld后面有个空格)
7.初始化数据库 mysqld --initialize-insecure --user=mysql ，如果执行成功，生成data目录
8.启动mysql服务net start mysql；停止mysql服务net stop mysql；
9.进入mysql终端mysql -u root -p [安装完成以后，root账户，密码为空]
10.修改root用户密码
use mysql;
update user set authentication_string=password('你要设置的密码') where user='root' and Host='localhost';
或者下面修改密码语句也可以：
set password for root@localhost=password('你的密码');
flush privileges; //刷新权限
二、数据库
1、连接到mysql服务的命令：mysql -h localhost -P 3306 -u root -p123123123
注意：-P端口这个P是大写；-p密码这里没有空格
2、mysql三层结构
3、sql语句分类
4、创建数据库数据表操作
4.1、创建数据库
create database 数据库名；
4.2、创建数据库，同时设置字符集
create database 数据库名 character set utf8;
4.3、显示数据库语句
show databases;
4.4、显示数据库创建语句，下图中的反引号 `` 是用来规避关键字的
show create databases db_name;
4.5、数据库删除语句
drop database if exists ab_name; (谨慎使用，生产环境一定不要轻易操作，仔细确认再的确要删除才执行)
4.6、备份数据库，在dos命令行操作
mysqldump -u 用户名 -p 密码 -B 数据库1 数据库2 数据库n > 文件位置:\文件名.sql
4.7、恢复数据库，需要登录进mysql再执行操作
source 文件夹:\beifen.sql
4.8、备份数据库的表，
mysqldump -u 用户名 -p 密码 数据库 表1 表2 表n > 文件位置:\文件名.sql
三、表
1、创建mysql数据表，语法如图所示：
四、Mysql常用数据类型(列类型/字段类型)
1、mysql列类型-即mysql数据类型（数据类型选择遵守保小不保大）
1.1、数值类型
1.2、文本类型(字符串类型)
1.3、二进制数据类型
1.4、时间日期类型
五、列类型
5.1、mysql列类型之bit
5.2、mysql列类型之数值型
5.2.1小数类型
5.3、mysql列类型之字符串类型
5.3.1char(这里是字符数，char(10)，即使只存5个汉字，但是也占用10个字符空间 )、varchar(这里面是字符数，varchar(10)可以存储10个中文汉字，utf8编码占用内存空间31-33个字节)（utf8编码varchar最大21844个字符，gbk编码varchar最大32766个字符）
字符串使用细节1：占用多大字节空间根据表的编码决定(utf8中文3个字节，英文一个字节；gbk中文2个字节)
字符串使用细节2：
字符串使用细节3：
为什么char比varchar快呢？
char类型在存放数据的时候，中间是没有间隔的，数据本身是有空格的，但是数据段之间是没有间隔的，这是因为我们在创建列的时候已经告诉MySQL列的长度了，MySQL在查询数据的时候，只需要按部就班寻找就行了，不需要在中途计算这个数据段的长度。
但是varchar类型的存放就不同了，它是动态分配存储空间的，在每个数据段开头，都要有一段空间来（1~2个字节）存放数据段的长度信息，在数据段的结尾也还有一段空间（1个字节）标记此字段的节数。MySQL在读取一个数据段的时候，首先要读开头，比如读到了3，说明数据段的长度是3，之后就不多不少，只读3个字节，也只分配3个字节的存储空间。所以MySQL在遍历数据的时候，磁针要比char类型的列，多读很多次磁盘来获取字段的真实长度，这就是为什么varchar比char查询效率低的原因了。
字符串使用细节4：
5.4、mysql列类型之日期类型
六、表操作
6.1、修改表基本介绍
添加字段：
查看表结构：
修改表结构(字段类型)：
注意：int(10)，这个10表示数据显示的长度为10； int(2)并不表示数据大小的最大值为99，其大小和int表示的最大值一样，只是表示查询时显示的结果长度为2位； 而char(10) varchar(10)， 这里的10表示不管什么字符集，最多只能存10个字符！！！
删除表结构(字段)：
修改表名：
修改表字符集：
查看建表语句：
修改字段名称：
七、CURD C[create] U[update] R[retrieve 检索、找、取] D[delete]
7.1、添加语句
注意细节：
7.2、更新语句
注意细节：
7.3、删除语句 （注意：删除的时候一定要带条件，不然会导致整个表的数据全部被删除 ！！！）
注意了：
7.4、select查询语句 (SQL中的重点、难点)
7.4.1 指定字段查询
7.4.2 指定字段去重查询
7.4.3 使用表达式对查询的列进行运算
7.4.4 在select语句中使用as语句
7.4.5 在where字句中经常使用的运算符
> < like and
查询姓氏姓金的
(between and ) 是一个闭区间
or
in
order by order by语句应该位于select语句的结尾
7.5、合计/统计函数
7.5.1、count函数
count(字段) 返回非空的字段满足条件的数量！！！
7.5.2、sum函数
统计一个班级语文成绩的平均分
注意：sum函数仅对数值起作用，对其他类型值求sum没有意义； 还有多列求和的时候“,”不能少！！！
7.5.3、avg函数
7.5.4、max/min函数
7.6、分组统计
按照部门分组来查询
显示每个部门的每种岗位的平均工资和最低工资
显示平均工资低于2000的部门编号和它的平均工资
下面这个使用1次avg函数的查询方式效率高一点
7.7、字符串函数
7.7.1 返回字串字符集
7.7.2 连接字串，把多个列拼接成一列(把字串粘在一起)
7.7.3 INSTR(string,substring) 返回substring在string中出现的位置,从1开始，没有则返回0
7.7.4 转成大写小写 UCASE/LCASE
7.7.5 LEFT(string,length) / RIGHT(string,length) 从string的左边 / 右边起，取length个字符
7.7.6 LENGTH(string) 返回string的长度(字节长度)
7.7.7 REPLACE(str,search_str,replace_str) 在str中用 replace_str 替换 search_str
7.7.8 STRCMP(string1,string2) 逐字符比较两字串的大小， 函数用于比较两个字符串，如果两个字符串都相同，则返回0，如果根据定义的顺序第一个参数小于第二个参数，则返回-1，而当第二个参数小于第一个参数时，则返回1。
7.7.9 substring(str,position,[length]) 从str的position位置开始 [从1开始计算] ，取出length个字符；如果第三个参数不给，就默认取出到末尾
7.7.10 ltrim(string) rtrim(string) trim(string) 去掉string的左边、右边、两端空格
以首字母小写的方式显示所有员工emp表的姓名
7.8、数学函数
7.8.1 abs()函数
7.8.2 bin()函数， 十进制转二进制
7.8.3 ceiling(num)函数，向上取整，得到比num大的最小整数
7.8.4 conv(num,from_base,to_base)函数， 进制转换函数
7.8.5 floor(num)函数，向下取整，得到比num小的最大整数
7.8.6 format(num,decimal_places)函数，对num 保留 decimal_places位小数(四舍五入)
7.8.7 hex(num)函数，转成十六进制
7.8.8 least(num1,num2, [...])函数，求最小值
7.8.9 mod(numerator,denominator)函数，求余
7.8.10 rand([seed] rand [seed])函数，返回随机数，其范围为0 <= v <= 1.0
注意：使用rand()，每次返回不同的随机数，范围0 <= v <= 1.0；使用rand(seed)返回随机数，范围0 <= v <= 1.0，如果seed不变，该随机数也不会变化
7.9、时间日期相关函数
7.9.1 current_date()函数，返回当前日期； 查询的时候current_date 可以带小括号也可以不带
7.9.2 current_time()函数，返回当前时间
7.9.3 current_timestamp()函数，返回当前时间戳
7.9.4 now()函数，返回当前日期时间
应用实例1：显示所有新闻信息，发布日期只显示日期，不显示时间
7.9.5 date_add(date,interval d_value d_type)函数，在date上加上时间或者日期
应用实例2：查询在10分钟内、一天内、一个月内、一年内添加的数据
7.9.6 date_sub(date,interval d_value d_type)函数, 在date上减去时间或者日期
7.9.7 datediff(date1,date2)函数，返回两个日期差(结果是天)
7.9.8 year|month|date(datetime) 年月日
7.9.9 unix_timestamp()函数， 返回自1970-01-01 00:00:00 到当前时间的秒数
7.9.9 FROM_UNIXTIME()函数， 把一个unix_timestamp秒数[时间戳] 转成指定格式的日期
7.10、加密和系统函数
7.10.1 user()函数，查询当前是哪个用户
7.10.2 database()函数，查询当前使用的数据库名
7.10.3 md5()函数，为字符串算出一个md5 32位的字符串，常用于用户密码加密
7.10.4 password()函数，加密函数，mysql数据库的用户密码就是用password函数加密的
7.11、流程控制函数
7.11.1 if(expr1,expr2,expr3)函数，如果 expr1为true则返回expr2，否则返回expr3
7.11.2 ifnull(expr1,expr2)函数，如果expr1不为空，则返回expr1，否则返回expr2
7.11.3 判断是否为null，要使用is null， 判断不为空，使用is not null
7.11.4 SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END ; 【多重分支】 如果expr1为 true 则返回expr2，如果expr3为 true 则返回expr4， 否则返回expr5
八、MYSQL查询加强
8.1 mysql多表查询
8.2 在mysql中，日期类型可以直接比较
8.3 like模糊查询， %：表示0到多个任意字符 _：表示单个任意字符
显示首字母为N的员工的工资和姓名
显示第三个字符为小写n的员工工资和姓名
显示没有上级的职员信息 就是判断mgr字段为null， 判断null 使用 is null
根据工资升序倒序查询
根据工资倒序排序，部门升序排序
8.4 limit分页查询
8.5 使用分组函数和分组子句 group by
应用实例一：显示每种岗位的雇员总数、平均工资
应用实例二：显示雇员总数，以及获得奖金的雇员数
count(字段)，如果字段的值为null，则count()不会统计
应用实例三：显示雇员总数，以及没有获得奖金的雇员数
应用实例四：显示管理者的总人数
应用实例五：显示最大薪资差
应用实例六：统计各个部门的平均工资，并且大于1000，按照平均工资从高到低排序，取出前两行记录
注意：：语句顺序！！！
8.6 多表联合查询
应用实例一：多表查询，显示雇员名字、雇员工资、及所在部门的名字
当多表查询不加条件的时候：
分析：从第一张表中取出一行和第二张表进行组合，返回结果，结果包含两张表的所有列，上面两表查询不加条件的时候，结果就有32行，这样的多表查询默认处理返回的结果，称为笛卡尔集
应用实例二：多表查询，显示雇员名字、雇员工资、部门编号、及所在部门的名字
注意：当我们需要指定某个表的列时，需要使用 表名.列名的方式
小技巧：多表查询的条件不能少于表的张数-1，如：3表查询，条件至少要两个，才不会出现笛卡尔集
应用实例三：多表查询，显示雇员名字、雇员工资、部门编号为10的部门、及所在部门的名字
8.7 多表联合查询-自连接
什么是自连接：自连接指在同一张表的链接查询（将同一张表看做两张表）
自连接的特点：1、把同一张表当做两张表来说使用 2、需要给表取别名 表名 表别名 3、列名不明确，可以指定列的别名 列名 as 列的别名
过程分析：同一张表某条记录的mgr = 同一张表某条记录的empno
第一步：
第二步：
第三步：
8.8 子查询
什么是子查询：嵌入在sql语句中的select语句，也叫嵌套查询
单行子查询：指只返回一行数据的子查询语句
多行子查询：指只返回多行数据的子查询语句，使用关键字 IN
应用实例一：显示与smith同一部门的员工
第一步：查询部门编号
第二步：把上面的select语句当做子查询来使用
应用实例二：查询和部门10的工作相同的雇员的名字、岗位、工资、部门号，但是不包含10号部门自己的雇员
第一步：查询10号部门的工作岗位有哪些
像以下情况需要过滤
第二步：把上面查询的结果当做子查询
8.9 子查询当做临时表
8.10 在多行子查询中使用all操作符和any操作符
应用实例一：显示工资比部门30的所有员工的工资高的员工姓名、工资和部门号
应用实例二：显示工资比部门30的其中一个员工的工资高的员工姓名、工资和部门号
8.11 多列子查询
多列子查询是指查询返回多个列数据的子查询语句
应用实例一：查询与smith的部门和岗位完全相同的所有雇员（不包含smith本人）
第一步：得到smith的部门和岗位
第二步：把上面的查询当做子查询来使用，并且使用多列子查询的语法进行匹配
应用实例二：
第一步：先得到每个部门的平均工资和部门号
第二步：把上面的结果当做子查询，和emp进行多表查询
应用实例三：查询每个部门工资最高的人
应用实例四：查询每个部门的信息(包括：部门号、编号、地址)和人员数量
第一步：部门号、编号、地址在dept表
第二步：各个部门人员数量 -> 构建一个临时表
第三步：组合查询
8.12 自我复制数据(蠕虫复制)
有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此方法为表创建海量数据
应用实例一：把emp表得记录复制到my_tab1中
第一步：从其他表复制
第一步：自我复制
多次点击，数据成倍增长
应用实例二：删除掉一张表中重复记录
第一步：先创建一张表 下面语句，把表emp的结构(列)，复制到my_tab2中
第二步：添加数据
查询，已经有重复数据
第三步：去除重复数据
1、创建一张临时表
2、把数据去重以后添加到临时表
3、清除掉my_tab2的数据记录
4、把my_tmp表的记录复制到my_tab2
5、drop掉临时表my_tmp
8.13 合并查询
有些在实际的应用中，为了合并多个select语句的结果，可以使用集合操作符union（联合）、union all（联合所有）
应用实例一：union all 该操作用于取得两个结果集的并集，当使用该操作时，不会取消重复行
应用实例二：union 该操作与union all类似，但是会自动去掉结果集中重复行
8.14 mysql表外连接
左外连接：如果左侧的表完全显示（即使左侧的表和右侧的表没有匹配的记录），我们就说是左外连接； 语法：select field1,field2,... from 表1 left join 表2 on 条件[表1就是左表，表2就是右表]
右外连接：如果右侧的表完全显示（即使右侧的表和左侧的表没有匹配的记录），我们就说是右外连； 语法：select field1,field2,... from 表1 right join 表2 on 条件[表1还是叫左表，表2还是叫右表]
应用实例一：列出部门名称和这些部门员工名字和工作，同时要求显示那些没有员工的部门
第一步：这里只有三个部门
但实际上是有四个部门的
SQL：
应用实例二：使用左外链接，显示所有人的成绩，如果没有成绩，也要显示该同学的姓名和id，成绩显示为空
第一步：传统的外键
第二步：改成左外连接
应用实例三：使用右外链接，查询所有成绩，如果没有名字匹配，显示为空
应用实例四：查询部门的名称和这些部门员工信息（名字和工作），同时列出那些没有员工的部门
总结：在开发中，绝大多数情况下还是使用前面学过的多表查询