初识MySQL

mysql
字段约束条件

mysql

数据存取演变史

1.文本文件
	基于我们之前所学，数据要想永久保存，都是保存于文件中，毫无疑问，一个文件仅仅只能存在于某一台机器上。
	如果我们暂且忽略直接基于文件来存取数据的效率问题，并且假设程序所有的组件都运行在一台机器上，那么用文件存取数据，并没有问题。
	但是事实上它会存在许多问题：
		·文件路劲不一致 C:\a.txt D:\aaa\b.txt E:\ccc.txt
		·数据格式不一致 jason|123 tony$123 kevin@123
2.软件开发目录规范
	规定了数据文件的大致位置：db文件夹
	针对数据格式还是没有完全统一，比如统一json文件但是内部键值对不同
3.数据库服务
	统一了存取位置，也统一了数据格式(完全统一)

数据库软件应用史

1.单机游戏
	不同计算机上的相同程序，数据无法共享
		数据库服务全部在本地完成
2.网络游戏
	不同计算机的相同程序，数据可以共享
		数据库服务单独在网络上架设
"""数据库服务集群：提升数据的安全性"""

数据库的本质

'数据库' 这三个字在不同的角度下描述的意思是不一样的

1.站在底层原理的角度
	数据库指的是专用于操作数据的进程
		即：运行在内存中的代码
2.站在现实应用的角度
	数据库指的是拥有操作界面的应用程序
		即：用于操作数据进程的快捷界面
我们不做特殊说明的情况下提出的数据库其实是在指数据库软件
	我们也称数据库软件本质是一款cs架构的应用程序
		即：所有的程序员理论上都可以编写>>>:市面上已经有许多数据库软件了

数据库的分类

1.关系型数据库
	·数据的组织方式有明确的表结构
		关系型数据库存取数据的方式可以看作是表格
	·表与表之间可以建立代码层面的关系
		用户表--（有关系）--房屋表
		只要获取到用户表的一条数据，就可以获取到与之相关的其他数据
    '''
    MySQL、PostgreSQL、MariaDB、Oracle、sqlite、db2、sql、server

    MySQL:开源，使用最为广泛、入门必学
    PostgreSQL:开源,支持二次开发
    MariaDB:开源，与MySQL是同一个人写的，用法极其相似
    Oracle:收费，安全性非常高，主要用于银行各大重要机关
    sqlite:小型数据库，主要用于本地色测试
    '''
2.非关系型数据库
	·数据的组织方式没有明确的表结构，是以K:V键值对的形式组织的
		{'name':'lisa'}
		{'habby':'sing、dance'}
	·数据之间无法直接建立代码层面的关系
    '''
    redis、mongoDB、memcache

    redis:目前最火，使用频率最高，是一个缓存型数据库
    mongoDB:稳定型数据库，最像关系型的非关系型，主要用于爬虫、大数据
    memcache:已经被redis淘汰
    '''

MySQL数据库简介

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司
MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS
(Relational Database Management System，关系数据库管理系统) 应用软件之一

1.MySQL版本问题
	5.6X:前几年使用频率最高版本
	5.7X:最近尝试迁移的版本问题
	8.0X:最新版，功能很强大，但是线上环境几乎不用（本地自己用非常好用）
    '''虽然版本上有区别，但是操作几乎没有区别，主要体现在底层运作'''
2.下载与安装
	1.访问官网
 	2.点击DOWNLOADS
 	3.点击GPL
 	4.点击community server
 	5.点击archives
	6.点击download
3.解压安装
	上述方式下载的压缩包里含有服务端和客户端 支持本地操作
4.主要文件介绍
	bin文件夹
		mysqld.exe服务端		mysql.exe客户端
 	data文件夹
		存取数据
 	my-default.ini
		默认配置文件

基本使用

1.首先切换到mysql的所在盘
	C:\Users\***>D:
2.查找mysql文件位置
	D:\>cd mysql-5.6.44-winx64\bin
3.输入mysql启动文件
	D:\mysql-5.6.44-winx64\bin>mysqld
4.再开启新的cmd窗口
	作为客户端使用，直接敲mysql回车是游客模式
5.用户密码登录
	mysql -u用户名 -p密码(mysql默认密码为空)
6.使用游客登陆和使用管理员密码登录是有区别的
	管理员用户密码登录：
		mysql> show databases;
		+--------------------+
		| Database           |
		+--------------------+
		| information_schema |
		| mysql              |
		| performance_schema |
		| test               |
		+--------------------+
		4 rows in set (0.00 sec)
	游客登录：
		mysql> show databases;
		+--------------------+
		| Database           |
		+--------------------+
		| information_schema |
		| test               |
		+--------------------+
		2 rows in set (0.00 sec)
7.退出
	exit、quit

系统服务制作

1.如何解决每次都需要切换路径查找文件的缺陷
	添加环境变量(D:\mysql-5.6.44-winx64\bin)
2.将mysql服务端制作成系统服务(随着计算机的开启而启动 关闭而结束)
	·以管理员身份打开cmd窗口
	·执行系统服务命令
		mysqld --install
		Service successfully installed.
	·启动服务端
		1.右键直接点击启动
		2.命令启动
			net start mysql
'''
1.查看系统服务的命令
	services.msc
2.关闭mysql服务端
	net stop mysql
3.移除系统服务
	1.先确保服务已经关闭
	2.执行移除命令
		mysqld --remove
'''

密码相关操作

1.修改密码
	mysqladmin命令
		通用方式:  # 直接在cmd中写
			mysqladmin -u用户名 -p原密码 password 新密码
			'Warning: Using a password on the command line interface can be insecure.'
      提示这个不是报错，而是提醒你=我们此时这么更改密码是不安全的，密码暴露在了外面
				第一次修改：mysqladmin -uroot -p password 123，首次修改因为mysql默认密码为空所以-p后面什么都没有
				第二次修改：mysqladmin -uroot -p123 password 321
		偏门方式(有些版本无法使用):  需要先登录，更改的是当前登录的这个账号的密码
			set password=PASSWORD('新密码');引号必须要加
2.忘记密码
	直接重装\拷贝对应文件(user.frm、user.MYD、user.MYI)
	了解知识：
		先关闭服务端 然后以不需要校验用户身份的方式启动 再修改 最后再安装正常方式启动
			1.net stop mysql
			2.mysqld --skip-grant-tables(跳过授权表，不校验用户的密码，可以直接进去)
			3.mysql -uroot -p
			4.update mysql.user set password=password(123) where Host='localhost' and User='root';
			5.net stop mysql
			6.net start mysql

SQL和NoSQL

数据库的服务端支持各种语言充当客户端
MySQL服务端仅仅是用来管理数据的，没有其他操作
    eg:以MySQL服务端为例
          MySQL客户端、python代码编写的客户端、java代码编写的客户端
        为了能够兼容所有类型的客户端 有两种策略
            1.服务端兼容
                不合理 消耗数据库服务端资源!!!
            2.制定统一标准
                 SQL语句、NoSQL语句
SQL与NoSQL
    SQL语句的意思是操作关系型数据库的语法
    NoSQL语句的意思操作非关系型数据库的语法
ps:SQL有时候也用来表示关系型数据库 NoSQL也用来表示非关系型数据库

基本SQL语句

1.SQL语句结束符是分号
	;
2.取消SQL语句的执行
	\c
3.将查询结果进行按列打印，可以使每个字段打印到单独的行
	\G
4.基本常用语句
    show databases;  查看所有的数据库
    show tables;  查看所有的表
    select * from mysql.user;  查看user表里面所有的记录

\G使用前，显示效果很差

\G使用后即将查到的结构旋转90度变成纵向

1.针对库的基本SQL语句

1.增
	create database 库名;
2.查
	show databases;
	show create database 库名;
3.改
	alter database 库名 charset='gbk';
4.删
	drop database 库名;
 

-----------------------------------客户端sql语句执行-------------------------------
mysql> create database school;
Query OK, 1 row affected (0.00 sec)

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> show create database school;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| school   | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter darabase school charset='gbk';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'darabase school charset='gbk'' at line 1
    
'根据错误提示检查发现单词写错了，要学会看错误修改错误'

mysql> alter database school charset='gbk';
Query OK, 1 row affected (0.00 sec)

mysql> drop database school;
Query OK, 0 rows affected (0.03 sec)

2.针对表的基本SQL语句

"""
查看当前所在的库名
	select database();
		如果没有切换指定的库 那么默认是NULL
	use 库名;
"""
1.增
	create table 表名(字段名 字段类型,字段名 字段类型,字段名 字段类型);
2.查
	show tables;
	show create table 表名;
 	describe 表名;  
 	desc 表名;
3.改
	alter table 旧表名 rename 新表名;  # 改表名
4.删
	drop table 表名;
    
-----------------------------------客户端sql语句执行-------------------------------   
'创建数据库'
mysql> create database school;
Query OK, 1 row affected (0.00 sec)
'使用数据库'
mysql> use school;
Database changed
'在当前数据库中创建表'
mysql> create table student(
    -> id int,
    -> name varchar(20),
    -> age int,
    -> habby varchar(50));
Query OK, 0 rows affected (0.05 sec)
'查看表里的信息'
mysql> select * from student;
Empty set (0.02 sec)
'展示当前数据库中的表'
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)
'展示表的数据，会告诉这个表是怎么写的，带有编码和引擎'
mysql> show create table student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                     |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `habby` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
'对表的介绍'
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| habby | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.04 sec)
'修改表的名字'
mysql> alter table student rename students;
Query OK, 0 rows affected (0.03 sec)
'删除表'
mysql> drop table students;
Query OK, 0 rows affected (0.01 sec)

3.针对记录的基本SQL语句

"""
既然想操作记录 那么肯定的先有库和表
	辛辛苦苦一个月 换来的就是一条SQL语句 修改你的账户余额
"""
1.增
	insert into 表名 values(数据,数据);
	insert into 表名 values(数据,数据),(数据,数据),(数据,数据);
2.查
	select * from 表名;  # *表示查看所有字段
 	select 字段1,字段2 from 表名;
	ps:如果表中字段较多出现了错乱 可以结尾写\G
3.改
	update 表名 set 字段名=新数据 where 筛选条件;
4.删
	delete from 表名;  # 删除表中所有的数据
  	delete from 表名 where 筛选条件  # 按照条件删除数据
    
-----------------------------------客户端sql语句执行-------------------------------   
'添加数据'
mysql> insert into student values(1,'aa',18,'eat');
Query OK, 1 row affected (0.00 sec)
'查看数据'
mysql> select * from student;
+------+------+------+-------+
| id   | name | age  | habby |
+------+------+------+-------+
|    1 | aa   |   18 | eat   |
+------+------+------+-------+
1 row in set (0.00 sec)
'指定要查询的数据'
mysql> select id, name from student;
+------+------+
| id   | name |
+------+------+
|    1 | aa   |
|    2 | bb   |
|    3 | dd   |
+------+------+
3 rows in set (0.00 sec)
'添加多条数据'
mysql> insert into student values(2,'bb',19,'sing'),(3,'cc',20,'dance');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from student;
+------+------+------+-------+
| id   | name | age  | habby |
+------+------+------+-------+
|    1 | aa   |   18 | eat   |
|    2 | bb   |   19 | sing  |
|    3 | cc   |   20 | dance |
+------+------+------+-------+
3 rows in set (0.00 sec)
'修改表中的数据'
mysql> update student set name='dd' where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+------+------+------+-------+
| id   | name | age  | habby |
+------+------+------+-------+
|    1 | aa   |   18 | eat   |
|    2 | bb   |   19 | sing  |
|    3 | dd   |   20 | dance |
+------+------+------+-------+
3 rows in set (0.00 sec)
'删除数据'
mysql> delete from student where id=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+------+------+-------+
| id   | name | age  | habby |
+------+------+------+-------+
|    1 | aa   |   18 | eat   |
|    2 | bb   |   19 | sing  |
+------+------+------+-------+
2 rows in set (0.00 sec)

字符编码与配置文件

1.\s  查看数据库基本信息

2.my-default.ini  它是windows下MySQL默认的配置文件
	拷贝上述文件并且重命名为my.ini，必须是以my开头的mysql才能识别

3.由于5.6版本编码不统一 会造成乱码 我们需要统一修改>>>:utf8
	将my.ini里的内容修改为:
    '''
    [mysqld]
        character-set-server=utf8
        collation-server=utf8_general_ci
    [client]
        default-character-set=utf8
    [mysql]
        default-character-set=utf8 
    '''
	注意如果配置文件涉及到了mysqld相关的配置修改 那么需要重启服务端才可以生效
    
4.在配置文件中的mysql下提前写好用户名和密码，之后直接用mysql登录就好，不需要再输用户名和密码

数据库存储引擎

1.针对相同的数据采用的不同的存取策略

2.show engines;

3.需要掌握的存储引擎
    MyISAM
        MySQL5.5及之前版本默认的存储引擎
        存取数据的速度 快 但是功能较少 安全性较低
    InnoDB
        MySQL5.5之后版本默认的存储引擎
        存取数据的速度没有MyISAM快 但是支持事务、行锁、外键等诸多功能 
        安全性较高
    Memory
        基于内存的存储引擎 存取数据极快 但是断电立刻丢失
    BlackHole
        黑洞 任何写进去的数据都会立刻丢失 类似于垃圾站
4.不同存储引擎之间底层文件的区别
    create table t1(id int) engine=innodb;
    create table t2(id int) engine=MyISAM;
    create table t3(id int) engine=Memory;
    create table t4(id int) engine=BlackHole;
    创建表其实就是在mysql文件夹下面的data文件夹里的某个数据库文件夹里创建了文件
    不同的存储引擎产生的文件也是不一样的
    InnoDB
    	.frm	表结构
        .ibd	表数据、表索引(加快数据查询)
    MyISAM
    	.frm	表结构
        .MYD	表数据
        .MYI	表索引(加快数据查询)
    Memory
    	.frm	表结构
    BlackHole
    	.frm	表结构

# t1表ENGINE=InnoDB
mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# t2表ENGINE=MyISAM
mysql> show create table t2;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| t2    | CREATE TABLE `t2` (
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# t3表ENGINE=MEMORY
mysql> show create table t3;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| t3    | CREATE TABLE `t3` (
  `id` int(11) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# t4表ENGINE=BLACKHOLE
mysql> show create table t4;
+-------+-----------------------------------------------------------------------------------------+
| Table | Create Table                                                                            |
+-------+-----------------------------------------------------------------------------------------+
| t4    | CREATE TABLE `t4` (
  `id` int(11) DEFAULT NULL
) ENGINE=BLACKHOLE DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 分别向四个表里添加数据
mysql> insert into t1 values(1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values(1);
Query OK, 1 row affected (0.00 sec)
# 挨个查看表的内容
mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
# 表t3存储引擎为Memory，它的特点就是基于内存的存储数据，重启后数据就会消失
mysql> select * from t3;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
mysql> use db1;
Database changed
mysql> select * from t3;
Empty set (0.00 sec)
# 表t4存储引擎为黑洞，存储进去就删掉，所以没有数据
mysql> select * from t4;
Empty set (0.00 sec)

创建表的完整语法

create table 表名(
    字段名1 字段类型(数字) 约束条件1 约束条件2,
    字段名2 字段类型(数字) 约束条件,
    字段名3 字段类型(数字) 约束条件,
    字段名4 字段类型(数字) 约束条件
)ENGINE=存储引擎 DEFAULT CHARSET=utf8;

1.字段名和字段类型是必须要写的(至少写一个)
2.数字跟约束条件是可选的(可有可无)
3.约束条件可以写多个，空格隔开
4.最后一个字段的结尾不能加逗号
ps：编写SQL语句报错之后不要慌 仔细查看提示 会很快解决

字段类型之整型

1.验证整形是否自带负号
	-------------tinyint-------------
	create table t6(id tinyint);
 	insert into t6 values(-129),(256);
 	
 	mysql> desc t6;
    +-------+------------+------+-----+---------+-------+
    | Field | Type       | Null | Key | Default | Extra |
    +-------+------------+------+-----+---------+-------+
    | id    | tinyint(4) | YES  |     | NULL    |       |
    +-------+------------+------+-----+---------+-------+
    1 row in set (0.01 sec)

    mysql> select * from t6;
    +------+
    | id   |
    +------+
    | -128 |
    |  127 |
    +------+
    2 rows in set (0.00 sec)
    
    mysql> create table t7(id smallint);
	Query OK, 0 rows affected (0.02 sec)
	mysql> insert into t7 values(-32769),(32769);
    Query OK, 2 rows affected, 2 warnings (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 2
	-------------smallint----------
    mysql> desc t7;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | smallint(6) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    1 row in set (0.01 sec)

    mysql> select * from t7;
    +--------+
    | id     |
    +--------+
    | -32768 |
    |  32767 |
    +--------+
    2 rows in set (0.00 sec)
    验证结果：
    	发现自动填写为两个边界值 数据失真 没有实际意义
    	上述所有的整型类型默认都会带有负号

2.自定义移除负号
	'''使用约束条件：unsigned，作用是去掉负号'''
	create table t7(id tinyint unsigned)
	
    """
    插入的数据值超出了数据类型的范围 不应该让其插入并自动修改 没有意义
    数据库应该直接报错(这个特性其实是有的 只是被我们改了>>>:配置文件)

    方式1:命令临时修改
        set session sql_mode='strict_trans_tables'  当前客户端操作界面有效
        set global sql_mode='STRICT_TRANS_TABLES'  服务端不重启永久有效
    方式2:配置文件永久修改
        [mysqld]
            sql_mode='STRICT_TRANS_TABLES'
    """

字符类型之浮点型

float、double、decimal
'''三者都可以存储浮点型数据，但是各自的精确度不一致'''
使用方式：
	float(255,30)
	double(255,30)
	decimal(65,30)
	括号里第一个数字是总共有多少位，第二个数字表示的是小数占多少位
验证精确度问题
	create table t8(id float(255,30));
	create table t9(id double(255,30));
	create table t10(id decimal(65,30));
	insert into t8 values(1.111111111111111111);
	insert into t9 values(1.111111111111111111);
	insert into t10 values(1.111111111111111111);

	mysql> use db1;
	Database changed
	mysql> create table t8(id float(255,30));
	Query OK, 0 rows affected (0.03 sec)

	mysql> create table t9(id double(255,30));
	Query OK, 0 rows affected (0.01 sec)

	mysql> create table t10(id decimal(65,30));
	Query OK, 0 rows affected (0.02 sec)

	mysql> insert into t8 values(1.111111111111111111);
	Query OK, 1 row affected (0.00 sec)

	mysql> insert into t9 values(1.111111111111111111);
	Query OK, 1 row affected (0.00 sec)

	mysql> insert into t10 values(1.111111111111111111);
	Query OK, 1 row affected (0.00 sec)

	mysql> select * from t8;
	+----------------------------------+
	| id                               |
	+----------------------------------+
	| 1.111111164093017600000000000000 |
	+----------------------------------+
	1 row in set (0.00 sec)

	mysql> select * from t9;
	+----------------------------------+
	| id                               |
	+----------------------------------+
	| 1.111111111111111200000000000000 |
	+----------------------------------+
	1 row in set (0.00 sec)

	mysql> select * from t10;
	+----------------------------------+
	| id                               |
	+----------------------------------+
	| 1.111111111111111111000000000000 |
	+----------------------------------+
	1 row in set (0.00 sec)

    得出结论：
        float < double < decimal
        一般情况下float就够了，如果想要精确度完美一点，可以使用字符串来代替

字段类型之字符类型

char
	称之为定长
	char(4) 最大只能存储四个字符，如果超过范围则直接报错
	如果不超出范围，则用空格填充至四个字符
	例如：如果要存储 'a',那么它就会被存储为'a空格空格空格'，
varchar
	称之为变长
	varchar(4) 最大只能存储四个字符，如果超过范围则直接报错
	如果不超出范围，则有几位就存几位
1.验证俩者区别
	create table t11(id int, name char(4));
 	create table t12(id int, name varchar(4));	
 	
	mysql> insert into t11 values(1,'zhang');
	ERROR 1406 (22001): Data too long for column 'name' at row 1
	mysql> insert into t11 values(1,'tony');
	Query OK, 1 row affected (0.00 sec)

	mysql> insert into t12 values(1,'tony');
	Query OK, 1 row affected (0.00 sec)

	mysql> insert into t12 values(1,'zhang');
	ERROR 1406 (22001): Data too long for column 'name' at row 1
	mysql> desc t12;
	结果验证:超出范围俩者都会报错
		注意sql_mode='STRICT_TRANS_TABLES'
2.验证定长与变长
	用到一个小知识：char_length(),返回的是字符串的长度
	mysql> select char_length(name) from t11;
	+-------------------+
	| char_length(name) |
	+-------------------+
	|                 4 |
	|                 1 |
	+-------------------+
	2 rows in set (0.00 sec)

	mysql> select * from t11;
	+------+------+
	| id   | name |
	+------+------+
	|    1 | tony |
	|    1 | a    |
	+------+------+
	2 rows in set (0.00 sec)
	使用char(4)的时候，存进去的时候确实是'a空格空格空格',但是在取出来的时候会自动删掉这三个空格
	然后在读取的时候又会自动将填充的空格移除 如果想取消该机制 需要sql_mode
	set global sql_mode='strict_trans_tables,pad_char_to_full_length';
	上述目录是替换 不是新增 所以之前的配置也要写上
	设置以后:
	mysql> use db1;
        Database changed
        mysql> select char_length(name) from t11;
        +-------------------+
        | char_length(name) |
        +-------------------+
        |                 4 |
        |                 4 |
        +-------------------+
        2 rows in set (0.00 sec)
3.char与varchar哪个会更好 
	char
		整存整取 速度快
		浪费存储空间
  	varchar
		节省存储空间
		存取数据的速度慢于char
 	"""
 	char(4)
 		在硬盘上存储：a空格空格空格son空格jacktom空格lili
 		取得时候就是4个4个拿
 	varchar(4)
 		在硬盘上存储：asonjacktomlili
 		拿的时候就不对了
 		
 		1bytes+a1bytes+son1bytes+jack1bytes+tom1bytes+lili
 		存取数据都需要操作报头(耗时)
 	
 	存储人的姓名>>>:varchar
 	"""
	两者使用频率都很高 现在默认很多时候是varchar

字段后面数字的含义

数字大部分情况下都是用来限制字段的存储长度 但是整型除外!!!
	不是用来限制存储的长度 而是展示的长度

create table t13(id int(3)); 
'''如果想要看到展示范围的话，那么就使用约束条件：zerofill，它的作用是位数不够的话就使用0填充，但是如果超了的话那就超了，全部展示出来'''
create table t14(id int(3) zerofill);

mysql> insert into t14 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t14;
+--------+
| id     |
+--------+
| 123456 |
|    001 |
+--------+
2 rows in set (0.00 sec)
总结
	以后涉及到整型字段的定义 类型后面不需要加括号写数字 除非有业务需求必须固定位数
    eg:
    	00000000123
    	00123123031

字段类型之枚举和集合

1.枚举
	多选一
    	eg:性别(男 女 其他)

mysql> create table t15(
    -> id int,
    -> name varchar(32),
    -> gender enum('male','female')
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc t15;
+--------+-----------------------+------+-----+---------+-------+
| Field  | Type                  | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| id     | int(11)               | YES  |     | NULL    |       |
| name   | varchar(32)           | YES  |     | NULL    |       |
| gender | enum('male','female') | YES  |     | NULL    |       |
+--------+-----------------------+------+-----+---------+-------+
3 rows in set (0.05 sec)
mysql> insert into t15 values(1,'zuzu','女');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

mysql> insert into t15 values(1,'zuzu','female');
Query OK, 1 row affected (0.04 sec)
总结：提前规定好到底将来可以插入哪些数据
	
2.集合
	多选多(包含多选一)
    	eg:爱好(唱 跳 rap)
create table t16(
	id int,
	name varchar(32),
	hobbies set('read','run','music','rap')
);

mysql> insert into t16 values(1,'titi','read,run');
Query OK, 1 row affected (0.04 sec)

mysql> insert into t16 values(1,'xixi','read');
Query OK, 1 row affected (0.04 sec)
mysql> select * from t16;
+------+------+----------+
| id   | name | hobbies  |
+------+------+----------+
|    1 | zuzu | read,run |
|    1 | titi | read,run |
|    1 | xixi | read     |
+------+------+----------+
3 rows in set (0.00 sec)
总结：提前规定好到底将来可以插入哪些数据，可以选择多个，但是只能是提前录入好的

字段类型之日期类型

字段类型	表示
date	年月日
datetime	年月日时分秒
time	时分秒
year	年份

mysql> create table t17(
    -> id int,
    -> name varchar(32),
    ->  birth date,
    ->  reg_time datetime,
    ->  study_time time,
    ->  join_time year
    -> );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t17 values(1,'jason','2022-8-15','1999-10-02 08:13:11','11:11:11','1995');
Query OK, 1 row affected (0.03 sec)

mysql> select * from t17;
+------+-------+------------+---------------------+------------+-----------+
| id   | name  | birth      | reg_time            | study_time | join_time |
+------+-------+------------+---------------------+------------+-----------+
|    1 | jason | 2022-08-15 | 1999-10-02 08:13:11 | 11:11:11   |      1995 |
+------+-------+------------+---------------------+------------+-----------+
1 row in set (0.00 sec)

字段约束条件

"""
insert into 表名 vlaues()  # 默认按照创建表的字段顺序添加
insert into 表名(字段) vlaues()  # 可以自定义字段顺序
"""
1.unsigned	无负号
	id int unsigned
	'作用于整形，去掉负数'
2.zerofill	零填充
	id int zerofill
	'作用于整形，不超出范围0填充'
3.not null	非空
	name varchar(32) not null
	'飞空不一定是有值，比如说空字符串'
4.default 	默认值
	name varchar(32) default 'jason'
	'发给一些字段提前设置一些默认值，如果后期我们添加数据的时候没有给值，那就使用默认值'
    mysql> create table t18(id int,name varchar(23) not null default 'tony');
    Query OK, 0 rows affected (0.08 sec)

    mysql> desc t18;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  |     | NULL    |       |
    | name  | varchar(23) | NO   |     | tony    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.06 sec)
    mysql> insert into t18(id) values (1);
    Query OK, 1 row affected (0.01 sec)

    mysql> desc t18;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | id    | int(11)     | YES  |     | NULL    |       |
    | name  | varchar(23) | NO   |     | tony    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.06 sec)
5.unique	唯一值
	id int unique   单列唯一

 	host varchar(32)
 	port int		
	unique(host,port)  联合唯一
	例子：
	mysql> create table t20(id int, host varchar(32),port int,unique(host,port));
	Query OK, 0 rows affected (0.04 sec)

	mysql> desc t20;
	+-------+-------------+------+-----+---------+-------+
	| Field | Type        | Null | Key | Default | Extra |
	+-------+-------------+------+-----+---------+-------+
	| id    | int(11)     | YES  |     | NULL    |       |
	| host  | varchar(32) | YES  | MUL | NULL    |       |
	| port  | int(11)     | YES  |     | NULL    |       |
	+-------+-------------+------+-----+---------+-------+
	3 rows in set (0.03 sec)

	mysql> insert into t20 values(1, '127.0.0.1', 8080);
	Query OK, 1 row affected (0.02 sec)

	mysql> insert into t20 values(2, '127.0.0.1', 8080);
	ERROR 1062 (23000): Duplicate entry '127.0.0.1-8080' for key 'host'
	mysql> insert into t20 values(2, '127.0.0.1', 8081);
	Query OK, 1 row affected (0.02 sec)

	mysql> insert into t20 values(2, '127.0.0.2', 8080);
	Query OK, 1 row affected (0.02 sec)

	mysql> desc t20;
	+-------+-------------+------+-----+---------+-------+
	| Field | Type        | Null | Key | Default | Extra |
	+-------+-------------+------+-----+---------+-------+
	| id    | int(11)     | YES  |     | NULL    |       |
	| host  | varchar(32) | YES  | MUL | NULL    |       |
	| port  | int(11)     | YES  |     | NULL    |       |
	+-------+-------------+------+-----+---------+-------+
	3 rows in set (0.03 sec)

	mysql> select * from t20;
	+------+-----------+------+
	| id   | host      | port |
	+------+-----------+------+
	|    1 | 127.0.0.1 | 8080 |
	|    2 | 127.0.0.1 | 8081 |
	|    2 | 127.0.0.2 | 8080 |
	+------+-----------+------+
	3 rows in set (0.00 sec)

字段约束条件

无符号、零填充

unsigned
 id int unsigned  # 无符号
zerofill
 id int(5) zerofill  # 零填充

非空

create table t1(
  id int,
  name varchar(16)
);
insert into t1(id) values(1);
insert into t1(name) values('jason');
insert into t1(name,id) values('kevin',2);
ps:所有字段类型不加约束条件的情况下默认都可以为空
  
非空关键字 not null 
create table t2(
   id int,
  name varchar(16) not null  
);
insert into t2(id) values(1);
insert into t2(name) values('jason');
insert into t2 values(1,'');
insert into t2 values(2,null);

默认值

默认值关键字 defaut
create table t3(
   id int defaut 666,
   name varchar(16) defaut '匿名'
 );
 insert into t3(id) values(1);
 insert into t3(name) values('jason');
 insert into t3 values(2,'kevin');

唯一值

'''单列唯一'''
create table t4(
 id int unique,
 name varchar(32) unique
);
insert into t4 values(1,'jason'),(2,'jason');

"""联合唯一"""
create table t5(
 id int,
 ip varchar(32),
 port int,
 unique(ip, port)
);
insert into t5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080);
insert into t5 values(4,'127.0.0.1',8080);  # 报错

主键

1.主键介绍
	我们在建立数据库的时候，需要为每张表指定一个主键，所谓主键就是能够唯一标识表中某一行的属性或属性组，一个表只能有一个主键，因为主键可以唯一标识某一行记录，所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。主键除了上述作用外，常常与外键构成参照完整性约束，防止出现数据不一致。所以数据库在设计时，主键起到了很重要的作用。
2.主键的关键字:primary key
3.主键特点
	i.单从约束角度上而言主键等价于非空且唯一  not null unique,验证：
        mysql> create database db;
        Query OK, 1 row affected (0.00 sec)

        mysql> use db;
        Database changed
        mysql> create table t1(
            ->            id int primary key,
            ->            name varchar(32)
            ->         );
        Query OK, 0 rows affected (0.03 sec)

        mysql> select * from t1;
        Empty set (0.00 sec)

        mysql> desc t1;
        +-------+-------------+------+-----+---------+-------+
        | Field | Type        | Null | Key | Default | Extra |
        +-------+-------------+------+-----+---------+-------+
        | id    | int(11)     | NO   | PRI | NULL    |       |
        | name  | varchar(32) | YES  |     | NULL    |       |
        +-------+-------------+------+-----+---------+-------+
        2 rows in set (0.03 sec)

        mysql> insert into t1(name) values('tony');
        # 'ERROR 1364 (HY000): Field 'id' doesn't have a default value'
        mysql> insert into t1(id) values(1);
        Query OK, 1 row affected (0.02 sec)

        mysql> insert into t1(id) values(1);
        ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
            
	ii.InnoDB存储引擎规定一张表必须有且只有一个主键
    	·如果创建的表中没有主键也没有非空且唯一的字段，那么InnoDB存储引擎会自动采用一个隐藏的字段作为主键(主键可以加快数据查询:新华字典的目录)
        ·如果创建的表中没有主键但是有非空且唯一的字段，那么InnoDB存储引擎会自动将该字段设施为主键
        	mysql> create table t2(
                -> nid int not null unique,
                -> sid int not null unique,
                -> uid int not null unique,
                -> name varchar(32));
            Query OK, 0 rows affected (0.05 sec)

            mysql> desc t2;
            +-------+-------------+------+-----+---------+-------+
            | Field | Type        | Null | Key | Default | Extra |
            +-------+-------------+------+-----+---------+-------+
            | nid   | int(11)     | NO   | PRI | NULL    |       |
            | sid   | int(11)     | NO   | UNI | NULL    |       |
            | uid   | int(11)     | NO   | UNI | NULL    |       |
            | name  | varchar(32) | YES  |     | NULL    |       |
            +-------+-------------+------+-----+---------+-------+
            4 rows in set (0.03 sec)
	iii.创建表的时候都应该有一个'id'字段，并且该字段应该作为主键
    	比如说：uid、sid、pid
补充说明:争对主键有且只有一个
	单列主键
		id int primary key
	联合主键：
		primary key(sid, nid)
		实操：
		mysql> alter table t2 add primary key(nid,sid);
		Query OK, 0 rows affected (0.06 sec)
		Records: 0  Duplicates: 0  Warnings: 0

		mysql> desc t2;
		+-------+-------------+------+-----+---------+-------+
		| Field | Type        | Null | Key | Default | Extra |
		+-------+-------------+------+-----+---------+-------+
		| nid   | int(11)     | NO   | PRI | NULL    |       |
		| sid   | int(11)     | NO   | PRI | NULL    |       |
		| uid   | int(11)     | NO   | UNI | NULL    |       |
		| name  | varchar(32) | YES  |     | NULL    |       |
		+-------+-------------+------+-----+---------+-------+
		4 rows in set (0.03 sec)

没有办法更新主键，主键需先删除才能增加
alter table [表名] drop primary key;

主键的自增

当对应的字段不给值，或者说给默认值或者给null的时候，会自动的被系统触发，系统会从当前字段中已有的最大值再进行+1操作，得到一个新的再不同的字段

自增长字段必须是数字，而且是整型


mysql> select * from t4;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
+----+------+
3 rows in set (0.01 sec)

mysql> insert into t4(id,name) values(4,'aa'),(5,'bb'),(6,'cc');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t4;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | aa   |
|  5 | bb   |
|  6 | cc   |
+----+------+
6 rows in set (0.00 sec)

mysql> insert into t4(name) values('aa'),('bb'),('cc');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t4;
+----+------+
| id | name |
+----+------+
|  1 | aa   |
|  2 | bb   |
|  3 | cc   |
|  4 | aa   |
|  5 | bb   |
|  6 | cc   |
|  7 | aa   |
|  8 | bb   |
|  9 | cc   |
+----+------+
9 rows in set (0.00 sec)
自增长如果对应的字段输入了值，那么自增长失效；但是下一次还是能够正确的自增长（从最大值+1）

1.auto_increment
	该约束条件不能单独使用，必须跟在键后面(配合主键主键使用)
    mysql> create table t3(
        -> id int auto_increment);
    ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
	'''这里只能有一个自动增加的列，并且必须是一个键'''
    
    正确的写法：
    mysql> create table t4(
        -> id int primary key auto_increment,
        -> name varchar(32));
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> desc t4;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(32) | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    2 rows in set (0.01 sec)
    mysql> insert into t4(name) values('zz'),('aa'),('bb');
    Query OK, 3 rows affected (0.02 sec)
    Records: 3  Duplicates: 0  Warnings: 0
	'''主键字段不需要添加自动生成，只需要去添加数据就好'''
    mysql> select * from t4;
    +----+------+
    | id | name |
    +----+------+
    |  1 | zz   |
    |  2 | aa   |
    |  3 | bb   |
    +----+------+
    3 rows in set (0.00 sec)
    
    这里需要注意的主键只会向后增加，不管中途是怎么操作了数据，它只会向后增加，例如：如果删除了id=3的数据，接下来在向里面添加数据它也是从4开始，那么这样也好，如果中途删了数据，我们就会从id号上查看到，总而言之就是自增不会因为删除的操作而进行回退，只会向后增加
    mysql> delete from t4 where id=3;
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t4(name) values('zz'),('aa'),('bb');
    Query OK, 3 rows affected (0.02 sec)
    Records: 3  Duplicates: 0  Warnings: 0

    mysql> select * from t4;
    +----+------+
    | id | name |
    +----+------+
    |  1 | zz   |
    |  2 | aa   |
    |  4 | zz   |
    |  5 | aa   |
    |  6 | bb   |
    +----+------+
    5 rows in set (0.00 sec)
    针对自增以上的这个特性，如果我们非要重置，那么可以使用格式化表
    	truncate 表名; 它的作用就是删除表数据并重置主键值

外键

1.外键前戏

1.外键前戏
	需要创建一张员工表
		id	name gender	dep_name dep_desc
	上述表的缺陷
    	1.表结构不清晰 到底是员工表还是部门表(不严重 无所谓)
   		2.字段数据反复存取 浪费存储空间(不严重 无所谓)
        3.表的扩展性极差 牵一发动全身(很严重 效率极低)
	优化操作>>>:拆表
		id	name	  gender
		id	dep_name  dep_desc
       拆表之后解决了上述的三个问题，但是出现了一个致命的缺陷，不知道员工对应的部门，部门里也不知道存在哪些员工
   	解决措施:在员工表里增加'dep_id'
    	id		name	gender 	  dep_id
       添加一个部门编号字段填写部门数据的主键值
   	外键字段
    	专门用于记录表与表之间数据的关系

2.外键字段的创建

2.外键字段的创建
	外键字段是用来记录表与表之间数据的关系 而数据的关系有四种
		一对多关系、多对多关系、一对一关系、没有关系
	表数据关系的判定  >>>: '换位思考'
      针对员工表和部门表判断数据关系
		1.先站在员工表的角度
			问:一条员工数据能否对应多条部门数据
			翻:一名员工能否属于多个部门
			答:不可以
		2.再站在部门表的角度
			问:一条部门数据能否对应多条员工数据
			翻:一个部门能否拥有多个员工
			答:可以
		完成换位思考之后得出的答案 一个可以一个不可以
		那么表关系就是"一对多"
			部门是一 员工是多
		"""针对'一对多'的关系 外键字段建在多的一方 """
		ps:没有多对一 统一称为'一对多'

3.表关系之一对多

"""涉及到外键字段，先写普通字段，然后再写外键字段"""
create table emp(
	id int primary key auto_increment,
    name varchar(32),
    gender enum('male','female') default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id)
);

create table dep(
	id int primary key auto_increment,
    dep_name varchar(32),
    dep_desc varchar(32)
);
mysql> insert into dep(dep_name,dep_desc) values('讲师部','教书育人'),('安保部','维护治安');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from dep;
+----+-----------+--------------+
| id | dep_name  | dep_desc     |
+----+-----------+--------------+
|  1 | 讲师部    | 教书育人     |
|  2 | 安保部    | 维护治安     |
+----+-----------+--------------+
2 rows in set (0.00 sec)

mysql> insert into emp(name,dep_id) values('jason',1),('kevin',2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      1 |
|  2 | kevin | male   |      2 |
+----+-------+--------+--------+
2 rows in set (0.00 sec)
"""
1.创建表的时候需要先创建被关联的表(没有外键) 然后再是关联表(有外键)
2.插入表数据的时候 针对外键字段只能填写被关联表字段已经出现过的数据值
3.被关联字段无法修改和删除
	有点不太好 操作限制性太强
"""
级联更新、级联删除
	被关联数据一旦变动 关联的数据同步变动
	on update cascade  # 级联更新 
	on delete cascade  # 级联删除
级联更新、级联删除
	被关联数据一旦变动 关联的数据同步变动
create table emp1(
	id int primary key auto_increment,
 	name varchar(32),
  	gender enum('male','female','others') default 'male',
   dep_id int,
   foreign key(dep_id) references dep1(id) 
   on update cascade
   on delete cascade
);
create table dep1(
	id int primary key auto_increment,
   	dep_name varchar(32),
    dep_desc varchar(32)
);
mysql> insert into dep1(dep_name,dep_desc) values('讲师部','教书育人'),('安保部','维护治安');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
            
mysql> insert into emp1(name,dep_id) values('jason',1),('kevin',2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
            
mysql> update dep1 set id=200 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp1;
+----+-------+--------+--------+
| id | name  | gender | dep_id |
+----+-------+--------+--------+
|  1 | jason | male   |      1 |
|  2 | kevin | male   |    200 |
+----+-------+--------+--------+
2 rows in set (0.00 sec)
"""
扩展:
    在实际工作中 很多时候可能并不会使用外键
        因为外键增加了表之间的耦合度 不便于单独操作 资源消耗增加
    我们为了能够描述出表数据的关系 又不想使用外键
        自己通过写SQL 建立代码层面的关系
	如果表少的话使用外键会方便一点，但是如果表多的话就不建议使用外键了，那样的话就会乱
"""

4.表关系之多对多

以书籍表与作者表为例
	1.先站在书籍表的角度
		问:一条书籍数据能否对应多条作者数据
		答:可以
	2.再站在作者表的角度
		问:一条作者数据能否对应多条书籍数据
		答:可以
	总结:两边都可以 那么表数据关系就是'多对多'
	针对多对多表关系 外键字段不能建在任意一方!!!
mysql> create table book(
    -> id int primary key auto_increment,
    ->     title varchar(32),
    ->     author_id int,
    ->     foreign key(author_id) references author(id)
    ->     on update cascade
    ->     on delete cascade
    -> );
ERROR 1215 (HY000): Cannot add foreign key constraint
    
mysql> create table author(
    -> id int primary key auto_increment,
    ->     name varchar(32),
    ->     foreign key(book_id) references book(id)
    ->     on update cascade
    ->     on delete cascade
    -> );
ERROR 1072 (42000): Key column 'book_id' doesn't exist in table
'''需要单独开设第三张表，存储数据关系'''
create table book(
	id int primary key auto_increment,
	title varchar(32)
);
create table author(
    id int primary key auto_increment,
    name varchar(32)
);
create table book2author(
    id int primary key auto_increment,
    book_id int,
    foreign key(book_id) references book(id) 
    on update cascade 
    on delete cascade,
    author_id int,
    foreign key(author_id) references author(id) 
    on update cascade 
    on delete cascade
);

5.表关系之一对一

以用户表和用户详情表
	1.先站在用户表的角度
		问：一个用户可以对应多个详细信息
		答：不可以
	2.再站在用户详情表的角度
		问：一份详细信息可以对应多个用户
		答：不可以
	总结：两边都不可以，那么先考虑是不是没有关系
    	如果有关系那么肯定就是'一对一'
        针对'一对一'的表关系 外键字段建在任何一张表都可以 但是建议你建在查询频率较高的表中便于后续查询
        create table user(
            id int primary key auto_increment,
            name varchar(32)
            detail_id int unique, # 重点，给detail_id加约束条件unique
            foreign key(detail_id) references userDetail(id) 
            on update cascade
            on delete cascade
        );
        create table userDetail(
            id int primary key auto_increment,
            phone bigint
        );

SQL语句查询关键字

select
 指定需要查询的字段信息
 select *    查询所有字段
 select name   查询name字段
 select char_length(name)  支持对字段做处理
 
 from 
  指定需要查询的表信息
  from mysql.user
  from t1
 
SQL语句中关键字的执行顺序和编写顺序并不是一致的 可能会错乱
eg：
    select id,name from userinfo;
    我们先写的select在写的from 但是执行的时候是先执行的from在执行select
    
对应关键字的编写顺序和执行顺序我们没必要过多的在意  熟练之后会非常自然的编写
我们只需要把注意力放在每个关键字的功能上即可

前期数据准备

create table emp(
  id int primary key auto_increment,
  name varchar(20) not null,
  gender enum('male','female') not null default 'male', #大部分是男的
  age int(3) unsigned not null default 28,
  hire_date date not null,
  post varchar(50),
  post_comment varchar(100),
  salary double(15,2),
  office int, #一个部门一个屋子
  depart_id int
);

#插入记录
#三个部门：教学，销售，运营
insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);

编写SQL语句的小技巧

针对select后面的字段名可以先用*占位往后写 最后再回来修改

在实际应用中select后面很少直接写*  因为*表示所有 当表中字段和数据都特别多的情况下非常浪费数据库资源
"""
SQL语句的编写类似于代码的编写 不是一蹴而就的 也需要反反复复的修修补补
"""

查询关键字之where筛选

where其实就是对数据进行筛选

# 1.查询id大于等于3小于等于6的数据
写法一：
mysql> select * from emp where id >=3 and id <=6;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name  | sex    | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
|  3 | kevin | male   |  81 | 2013-03-05 | teacher | NULL         | 8300.00 |    401 |         1 |
|  4 | tony  | male   |  73 | 2014-07-01 | teacher | NULL         | 3500.00 |    401 |         1 |
|  5 | owen  | male   |  28 | 2012-11-01 | teacher | NULL         | 2100.00 |    401 |         1 |
|  6 | jack  | female |  18 | 2011-02-11 | teacher | NULL         | 9000.00 |    401 |         1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
4 rows in set (0.00 sec)
写法二：
mysql> select * from emp where id between 3 and 6;

# 2.查询薪资是20000或者18000或者17000的数据
写法一：
mysql> select * from emp where salary =20000 or salary =18000 or salary =17000;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         | 20000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation | NULL         | 18000.00 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation | NULL         | 17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
3 rows in set (0.00 sec)
写法二：
'''MySQL也支持成员运算'''
select * from emp where salary in (20000,18000,17000);

补充：模糊查询

模糊查询:关键字 like
		%:匹配任意个数的任意字符
		
		_:匹配单个个数的任意字符

# 3.查询员工姓名中包含o字母的员工姓名和薪资（模糊查询）
mysql> select name,salary from emp where name like '%o%';
+-------+------------+
| name  | salary     |
+-------+------------+
| jason |    7300.33 |
| tom   | 1000000.31 |
| tony  |    3500.00 |
| owen  |    2100.00 |
+-------+------------+
4 rows in set (0.00 sec)

# 4.查询员工姓名为四个字符组成的员工姓名和薪资
写法一：
mysql> select name,salary from emp where name like '____';
+------+----------+
| name | salary   |
+------+----------+
| tony |  3500.00 |
| owen |  2100.00 |
| jack |  9000.00 |
| sank | 10000.00 |
+------+----------+
4 rows in set (0.00 sec)
写法二：
'''根据统计长度来查找'''
mysql> select name,salary from emp where char_length(name)=4;

# 5.查询id小于3或者大于6的数据
写法一：
select * from emp where id < 3 or id > 6;
写法二：取反
select * from emp where id not between 3 and 6;

查询关键字之group by 分组

按照某个指定的条件将单个单个的数据分为一个个的整体

分组之后我们研究的对象应该是以组为单位不应该再直接获取单个数据项如果获取了应该直接报错 select后面可以直接填写的字段名只能是分组的依据(其他字段需要借助于一些方法才可以获取)

set global sql_mode='strict_trans_tables,only_full_group_by';

mysql> use db;
Database changed
mysql> select * from emp group by post;
# ERROR 1055 (42000): 'db.emp.id' isn't in GROUP BY

此时就不可以拿到所有的数据了，是按部门分组的，将来是按照部门管理数据的

我们写SQL是否需要使用分组 可以在题目中得到答案
	每个、平均、最大、最小
	
配合分组常见使用的有聚合函数
	max		最大值
	min 		最小值
	sum 		总和
	count 		计数
	avg		平均

实例：

# 1.每个部门的最高工资
mysql> select post,max(salary) from emp group by post;
+-----------------------------+-------------+
| post                        | max(salary) |
+-----------------------------+-------------+
| operation                   |    20000.00 |
| sale                        |     4000.33 |
| teacher                     |  1000000.31 |
| 浦东第一帅形象代言          |     7300.33 |
+-----------------------------+-------------+
4 rows in set (0.00 sec)

# 2.每个部门的平均工资
mysql> select post,avg(salary) from emp group by post;
+-----------------------------+---------------+
| post                        | avg(salary)   |
+-----------------------------+---------------+
| operation                   |  16800.026000 |
| sale                        |   2600.294000 |
| teacher                     | 151842.901429 |
| 浦东第一帅形象代言          |   7300.330000 |
+-----------------------------+---------------+
4 rows in set (0.00 sec)

# 3.求每个部门的工资总和
mysql> select post,sum(salary) from emp group by post;
+-----------------------------+-------------+
| post                        | sum(salary) |
+-----------------------------+-------------+
| operation                   |    84000.13 |
| sale                        |    13001.47 |
| teacher                     |  1062900.31 |
| 浦东第一帅形象代言          |     7300.33 |
+-----------------------------+-------------+
4 rows in set (0.00 sec)

# 4.求每个部门的人数
mysql> select post,count(id) from emp group by post;
+-----------------------------+-----------+
| post                        | count(id) |
+-----------------------------+-----------+
| operation                   |         5 |
| sale                        |         5 |
| teacher                     |         7 |
| 浦东第一帅形象代言          |         1 |
+-----------------------------+-----------+
4 rows in set (0.00 sec)

# 5.查询分组之后的部门名称和每个部门下所有的员工姓名
mysql> select post,group_concat(name) from emp group by post;
+-----------------------------+------------------------------------------------+
| post                        | group_concat(name)                             |
+-----------------------------+------------------------------------------------+
| operation                   | 程咬铁,程咬铜,程咬银,程咬金,僧龙               |
| sale                        | 拉拉,乐乐,西西,呵呵,哈哈                       |
| teacher                     | sank,jenny,jack,owen,tony,kevin,tom            |
| 浦东第一帅形象代言          | jason                                          |
+-----------------------------+------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select post,group_concat(name,':',salary) from emp group by post;
+-----------------------------+--------------------------------------------------------------------------------------------------+
| post                        | group_concat(name,':',salary)                                                                    |
+-----------------------------+--------------------------------------------------------------------------------------------------+
| operation                   | 程咬铁:17000.00,程咬铜:18000.00,程咬银:19000.00,程咬金:20000.00,僧龙:10000.13                    |
| sale                        | 拉拉:4000.33,乐乐:3000.29,西西:1000.37,呵呵:2000.35,哈哈:3000.13                                 |
| teacher                     | sank:10000.00,jenny:30000.00,jack:9000.00,owen:2100.00,tony:3500.00,kevin:8300.00,tom:1000000.31 |
| 浦东第一帅形象代言          | jason:7300.33                                                                                    |
+-----------------------------+--------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

补充：分组起别名

分组起别名:
	在查看结果的时候可以给字段起别名
	select post as '部门',max(salary) as '最高薪资' from emp group by post;
省略as：
	select post '部门',max(salary) as '最高薪资' from emp group by post;
	as可以省略但是为了语义更加明确建议不要省略

查询关键字之having过滤

where与having的功能其实是一样的 都是用来筛选数据

只不过where用于分组之前的筛选 而having用于分组之后的筛选

为了人为的区分 所以叫where是筛选 having是过滤

实例：

# 统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000
select * from emp where age > 30; 获取到的是所有年龄大于30的员工数据

mysql> select post,group_concat(name) from emp where age>30 group by post;

mysql> select post,avg(salary) from emp where age>30 group by post;

mysql> select post,avg(salary) from emp where age>30 group by post having avg(salary)>10000;

关键字的使用顺序：where--group by--having

查询关键字之distinct去重

去重的前提是存在一摸一样的数据，如果针对于主键肯定无法去重
关键字：distinct

实例：

1、针对于主键没有效果：
mysql> select distinct id,age from emp;
+----+-----+
| id | age |
+----+-----+
|  1 |  18 |
|  2 |  78 |
|  3 |  81 |
|  4 |  73 |
|  5 |  28 |
|  6 |  18 |
|  7 |  18 |
|  8 |  48 |
|  9 |  48 |
| 10 |  38 |
| 11 |  18 |
| 12 |  18 |
| 13 |  28 |
| 14 |  28 |
| 15 |  18 |
| 16 |  18 |
| 17 |  18 |
| 18 |  18 |
+----+-----+
18 rows in set (0.00 sec)
   
mysql> select distinct id,distinct age from emp;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct age from emp' at line 1

2、查看有几个年龄段的员工：
mysql> select distinct age from emp;
+-----+
| age |
+-----+
|  18 |
|  78 |
|  81 |
|  73 |
|  28 |
|  48 |
|  38 |
+-----+
7 rows in set (0.00 sec)

查询关键字之order by排序

"""
关键字：  
	关键字需要用在指定字段之后
	asc升序(默认)、desc降序 
"""

select * from emp order by salary asc; #默认升序排
select * from emp order by salary desc; #降序排

select * from emp order by age desc; #降序排

#先按照age降序排，在年轻相同的情况下再按照薪资升序排,可以自己定义是升序还是降序
select * from emp order by age desc,salary asc; 

# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
mysql> select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary);
+-----------------------------+---------------+
| post                        | avg(salary)   |
+-----------------------------+---------------+
| sale                        |   2600.294000 |
| 浦东第一帅形象代言          |   7300.330000 |
| operation                   |  16800.026000 |
| teacher                     | 151842.901429 |
+-----------------------------+---------------+
4 rows in set (0.00 sec)

查询关键字之limit分页

在查看的数据太多情况下，使用分页来限制每次查看数据的数量。

关键字： limit

用法：limit 数字
1、limit后只跟一个数字：从头开始展示多少行
	select * from emp limit 5;    # 从头开始展示5行
	
2、limit后跟两个数字：第一个数字为起始位，第二个数字为从起始位开始展示多少行
	select * from emp limit 5,5;   # 从第5行开始展示5行

实例：

# 求薪资最高的员工所有数据
分析：可以使用order by使薪资降序排序
	 在使用limit 1 展示一行
	 
mysql> select * from emp order by salary desc limit 1;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  2 | tom  | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)

查询关键字之regexp正则表达式

正则表达式：使用一些符号的组合产生一些特殊的含义，然后去字符串中筛选出符合条件的数据

关键字：regexp

实例：

# 取指定员工信息
mysql> select * from emp where name regexp '^j.*(n|y)$';
+----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
| id | name  | sex  | age | hire_date  | post                        | post_comment | salary   | office | depart_id |
+----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
|  1 | jason | male |  18 | 2017-03-01 | 浦东第一帅形象代言          | NULL         |  7300.33 |    401 |         1 |
|  7 | jenny | male |  18 | 1900-03-01 | teacher                     | NULL         | 30000.00 |    401 |         1 |
+----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
2 rows in set (0.00 sec)
解释：取员工姓名为：开头为j结尾为n或者y中间是任意字符的所有信息

多表查询的思路

表数据准备
create table dep(
  id int primary key auto_increment,
  name varchar(20) 
);

create table emp(
  id int primary key auto_increment,
  name varchar(20),
  sex enum('male','female') not null default 'male',
  age int,
  dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);


select * from emp,dep;  会将两张表中所有的数据对应一遍
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义 应该将有关系的数据对应到一起才合理
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定
select * from emp,dep where emp.dep_id=dep.id;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据

多表查询的两种方法

1.连表操作
  先将所有涉及到结果的表全部拼接到一起形成一张大表  然后在大表中进行查询数据
2.子查询
  将一张表的查询结果括号括起来当做另外一条SQL语句的条件
        eg:类似以日常生活中解决问题的方式
		第一步干什么
		第二步基于第一步的结果在做操作 ...

连表操作：
inner join 内连接
 select * from emp inner join dep on emd.dep_id =dep.id;
 只连接两张表中公有的数据部分

left join   左连接
 select * from emp left join dep on emd.dep.id =dep.id;
 以左表为基准 展示左表所有的数据 如果没有对应项则用NULL填充
 
right join 右连接
 select * from emp right join dep on emp.dep_id = dep.id;
 以右表为基准 展示右表所有的数据 如果没有对应则用NULL填充
 
union  全连接
 select * from emp left join dep on emd.dep_id =dep.id;
 union
 select * from emp right join dep on emd.dep_id =dep.id;
 以左右表为基准 展示所有的数据  各自没有的全部NULL填充
 
"""
学会了连表操作之后也就可以连N多张表
思路:将拼接之后的表起别名当成一张表再去与其他表拼接 再起别名当一张表 再去与其他表拼接 以此往复即可
"""

子查询：
将一条SQL语句用括号括起来当成另外一条SQL语句的查询条件
	题目:求姓名是jason的员工部门名称
	子查询类似于我们日常生活中解决问题的方式>>>:分步操作
   		步骤1:先根据jason获取部门编号
           select dep_id from emp where name='jason';
      	步骤2:再根据部门编号获取部门名称
          	select name from dep where id=200;
       总结
        	select name from dep where id=(select dep_id from emp where name='jason');
'''
很多时候多表查询需要结合实际情况判断用哪种  更多时候甚至是相互配合使用
'''

小知识点补充说明

1.concat与concat_ws

concat用于分组之前的字段拼接操作
    	select concat(name,'$',sex) from emp;
 	concat_ws拼接多个字段并且中间的连接符一致
    	select concat_ws('|',name,sex,age,dep_id) from emp;

2.exists

定义：exists关键字表示存在,在使用在使用exists关键字时，内层查询语句不返回查询的记录，而是返回一个真假值，True或False。 

当返回为True时，外层查询语句将进行查询、
返回值为False时，外层查询语句不进行查询。

用法：sql语句 exists(判断语句) 
判断语句为True则执行sql语句 如果返回为False则不执行.

select * from emp where exists (select id from emp where id > 3);
解释：括号内判断语句为True有结果则会执行exists前的sql语句

select * from emp where exists (select id from emp where id > 20);
解释：括号内判断语句为False没有结果则不会执行exists前的sql语句

3.表相关SQL补充

	alter table 表名 rename 新表名;  # 修改表名
 	alter table 表名 add 字段名 字段类型(数字) 约束条件;  # 添加字段
 	alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段;
  	alter table 表名 add 字段名 字段类型(数字) 约束条件 first;# 修改字段
  	alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;
 	alter table 表名 modify 字段名 新字段类型(数字) 约束条件;
 	alter table 表名 drop 字段名;  # 删除字段

可视化软件Navicat

第三方开发的用来充当数据库客户端的简单快捷的操作界面
	无论第三方软件有多么的花里胡哨 底层的本质还是SQL
能够操作数据库的第三方可视化软件有很多 其中针对MySQL最出名的就是Navicat

1.浏览器搜索Navicat直接下载
	版本很多、能够充当的数据库客户端也很多
2.破解方式
	先试用在破解、直接下载破解版(老版本)、修改试用日期
3.常用操作
	有些功能可能需要自己修改SQL预览
    	创建库、表、记录、外键
    	逆向数据库到模型、模型创建
    	新建查询可以编写SQL语句并自带提示功能
    	SQL语句注释语法
        --、#、\**\
    	运行、转储SQL文件

多表查询练习题

"""
编写复杂的SQL不要想着一口气写完
	一定要先明确思路 然后一步步写一步步查一步步补
"""
1、查询所有的课程的名称以及对应的任课老师姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
7、查询没有报李平老师课的学生姓名
8、查询没有同时选修物理课程和体育课程的学生姓名
9、查询挂科超过两门(包括两门)的学生姓名和班级
 
-- 1、查询所有的课程的名称以及对应的任课老师姓名
# 1.先确定需要用到几张表  课程表 分数表
# 2.预览表中的数据 做到心中有数
-- select * from course;
-- select * from teacher;
# 3.确定多表查询的思路 连表 子查询 混合操作
-- SELECT
-- 	teacher.tname,
-- 	course.cname
-- FROM
-- 	course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要用到几张表 学生表 分数表
# 2.预览表中的数据
-- select * from student;
-- select * from score;
# 3.根据已知条件80分 选择切入点 分数表
# 求每个学生的平均成绩 按照student_id分组 然后avg求num即可
-- select student_id,avg(num) as avg_num from score group by student_id having avg_num>80;
# 4.确定最终的结果需要几张表 需要两张表 采用连表更加合适
-- SELECT
-- 	student.sname,
-- 	t1.avg_num
-- FROM
-- 	student
-- INNER JOIN (
-- 	SELECT
-- 		student_id,
-- 		avg(num) AS avg_num
-- 	FROM
-- 		score
-- 	GROUP BY
-- 		student_id
-- 	HAVING
-- 		avg_num > 80
-- ) AS t1 ON student.sid = t1.student_id;
-- 7、查询没有报李平老师课的学生姓名
# 1.先确定需要用到几张表  老师表 课程表 分数表 学生表
# 2.预览每张表的数据
# 3.确定思路 思路1:正向筛选 思路2:筛选所有报了李平老师课程的学生id 然后取反即可
# 步骤1 先获取李平老师教授的课程id
-- select tid from teacher where tname = '李平老师';
-- select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
# 步骤2 根据课程id筛选出所有报了李平老师的学生id
-- select distinct student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'))
# 步骤3 根据学生id去学生表中取反获取学生姓名
-- SELECT
-- 	sname
-- FROM
-- 	student
-- WHERE
-- 	sid NOT IN (
-- 		SELECT DISTINCT
-- 			student_id
-- 		FROM
-- 			score
-- 		WHERE
-- 			course_id IN (
-- 				SELECT
-- 					cid
-- 				FROM
-- 					course
-- 				WHERE
-- 					teacher_id = (
-- 						SELECT
-- 							tid
-- 						FROM
-- 							teacher
-- 						WHERE
-- 							tname = '李平老师'
-- 					)
-- 			)
-- 	)
-- 8、查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
# 1.先确定需要的表  学生表 分数表 课程表
# 2.预览表数据
# 3.根据给出的条件确定起手的表
# 4.根据物理和体育筛选课程id
-- select cid from course where cname in ('物理','体育');
# 5.根据课程id筛选出所有跟物理 体育相关的学生id
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 6.统计每个学生报了的课程数 筛选出等于1的
-- select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
-- group by student_id
-- having count(course_id) = 1;
# 7.子查询获取学生姓名即可
-- SELECT
-- 	sname
-- FROM
-- 	student
-- WHERE
-- 	sid IN (
-- 		SELECT
-- 			student_id
-- 		FROM
-- 			score
-- 		WHERE
-- 			course_id IN (
-- 				SELECT
-- 					cid
-- 				FROM
-- 					course
-- 				WHERE
-- 					cname IN ('物理', '体育')
-- 			)
-- 		GROUP BY
-- 			student_id
-- 		HAVING
-- 			count(course_id) = 1
-- 	) 
-- 9、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先确定涉及到的表	分数表 学生表 班级表
# 2.预览表数据
-- select * from class
# 3.根据条件确定以分数表作为起手条件
# 步骤1 先筛选掉大于60的数据
-- select * from score where num < 60;
# 步骤2 统计每个学生挂科的次数
-- select student_id,count(course_id) from score where num < 60 group by student_id;
# 步骤3 筛选次数大于等于2的数据
-- select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 步骤4 连接班级表与学生表 然后基于学生id筛选即可
SELECT
	student.sname,
	class.caption
FROM
	student
INNER JOIN class ON student.class_id = class.cid
WHERE
	student.sid IN (
		SELECT
			student_id
		FROM
			score
		WHERE
			num < 60
		GROUP BY
			student_id
		HAVING
			count(course_id) >= 2
	);

python操作MySQL

pymysql模块
	pip3 install pymysql
 
import pymysql
 
# 1.连接MySQL服务端
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',
    db='db4_03',
    charset='utf8mb4'
)
# 2.产生游标对象
# cursor = conn.cursor()  # 括号内不填写额外参数 数据是元组 指定性不强  [(),()]
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # [{},{}]
# 3.编写SQL语句
# sql = 'select * from teacher;'
sql = 'select * from score;'
# 4.发送SQL语句
affect_rows = cursor.execute(sql)  # execute也有返回值 接收的是SQL语句影响的行数
print(affect_rows)
# 5.获取SQL语句执行之后的结果
res = cursor.fetchall()
print(res)

pymysql补充说明

1.获取数据
    fetchall()  获取所有的结果
    fetchone()	获取结果集的第一个数据
    fetchmany() 获取指定数量的结果集
    ps:注意三者都有类似于文件光标移动的特性

    cursor.scroll(1,'relative')  # 基于当前位置往后移动
    cursor.scroll(0,'absolute')  # 基于数据的开头往后移动
 
2.增删改查
	 autocommit=True  # 针对增 删 改 自动确认(直接配置)
  	 conn.commit()  # 针对 增 删 改 需要二次确认(代码确认)

SQL注入问题

前戏
	只需要用户名即可登录
 	不需要用户名和密码也能登录
问题
	SQL注入
	select * from userinfo where name='jason' -- haha' and pwd=''
	select * from userinfo where name='xyz' or 1=1  -- heihei' and pwd=''
本质
	利用一些特殊符号的组合产生了特殊的含义从而逃脱了正常的业务逻辑
措施
	针对用户输入的数据不要自己处理 交给专门的方法自动过滤
    sql = "select * from userinfo where name=%s and pwd=%s"
    cursor.execute(sql, (username, password))  # 自动识别%s 并自动过滤各种符合 最后合并数据
    execute方法，自动将 用户名和密码放在对应的%s内，并且放之前会自动对用户名和密码做特殊符号的校验，确保安全性
补充
	 cursor.executemany()

代码实例：

import pymysql

# 创建链接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123456',
    database='db1',
    charset='utf8'
)

# 生成一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 让数据自动组织成字典

登录功能
1.获取用户名和密码
2.基于用户名和密码直接精准查找
username = input('username>>>:').strip()
password = input('password>>>:').strip()

# 构造SQL语句
sql = "select * from info where name=%s and password=%s"  # 针对核心数据 不要自己拼接 交由execute方法帮你筛选再拼接
print(sql)
# 执行sql语句
cursor.execute(sql,(username, password))
# 获取所有返回结果
res = cursor.fetchall()
# if判断
if res:
    print(res)
    print('登录成功')
else:
    print('用户名或密码错误')

视图

1.什么是视图？

SQL语句的执行结果是一张虚拟表 我们可以基于该表做其他操作
如果这张虚拟表需要频繁使用 那么为了方便可以将虚拟表保存起来 保存起来之后就称之为"视图"

2.为什么要有视图？

如果要频繁使用一张虚拟表，可以不用重复查询

3.如何使用视图？

正常情况下两张表连接情况（拼接）查询

select * from userinfo inner join department on userinfo.dep_id = deparment.id

使用内连接：
    inner join：连接两个表中都存在的数据
    
userinfo(用户表)       deparment(部门表)
"""
1.在硬盘中，视图只有表结构文件，没有表数据文件（视图数据来源与dep与emp文件，来源于创建的表文件）
2.视图通常是用于查询，尽量不要修改视图中的数据

总结：视图能尽量少用就尽量少用
"""

4.反复拼接的繁琐（引用视图的作用）

它的繁琐之处就在于当我们频繁的去查询连表，反复拼接，会出现繁琐的现象，影响效率

5.解决方法

如果要频繁的使用一张虚拟表，可以不用重复查询，而使用视图来进行操作，将拼接的连表保存起来

视图的应用

1.创建与删除视图的格式

创建视图：
    create view 创建视图名字 as 执行连表的SQL语句;
删除视图：
    drop view 视图名字;
使用删除视图：
    deop view 视图名字;
"""
注意：
     当字段名相同时，会产生冲突，导致无法建立视图。
修改字段名：
     alter table <表名> change <字段名><字段新名称><字段的类型>;
"""

2.查询视图

select * from depart_user;

3.查询navicat视图层位置

4.开发过程中为什么不会使用视图？

视图是mysql的功能，如果你的项目里大量使用了视图，那么意味着，
你后期想要扩展某个功能的时候这个功能恰巧又需要对视图进行修改，意味着你需要在MySQL这边将视图修改一下，
然后再去应用层修改对应的SQL语句，这就涉及到跨部门沟通的问题，所有通常不会使用视图，而是通过重新修改sql语句来扩展功能。

关于视图做了解即可，尽量不要使用，因为在使用的时候它只是拿来看的，并不能修改里面的数据，
如果使用者使用了视图去修改数据那么就会报错，可能使使用者不清楚为什么报错造成了混淆，并且视图创建多了的话会占有硬盘空间，
但是在navicat中会有表与视图的区分

触发器

1.什么是触发器？

在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器

2.为何要有触发器？

触发器专门针对我们对某张表的增 insert、删 delete、 改 update的行为，这类行为一旦执行就会 触发触发器的执行，即自动运行另外sql语句

3.触发器的触发条件以及语法

针对表数据的增、改、删自动触发的功能（增前、增后、改前、改后、删前、删后）
语法结构：
    create trigger 触发器的名字 before/ after insert/ update/ delete on 表名 for each row 
    begin
       sql 语句
    end

4.触发器名字在命名的时候推荐使用下列的方式（见名知意）

tri_after_insert_t1			tri_before_delete_t1

5.注意事项（临时更改结束符）

在书写sql代码的时候结束符是；而整个触发器的结束符也是；
这就会出现语法冲突  需要我们临时修改结束符

临时修改结束符格式：
    delimiter 名字
    
delimiter $$
临时修改sql语句结束符，该语法只在当前窗口有效

6.触发器三种状态（六种演示）

增加:
create trigger tri_after_insert_t1 after insert on 表名 for each row
begin
	 sql语句
end	 # 增加之后触发

create trigger tri_before_insert_t1 before insert on 表名 for each row
begin
	 sql语句
end  # 增加之前触发


修改:
create trigger tri_after_update_t1 after update on 表名 for each row
begin
	 sql语句
end  # 修改之后触发

create trigger tri_before_insert_t1 before insert on 表名 for each row
begin
	sql语句
end # 修改之前触发


删除:
create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
	sql语句
end # 删除之前触发

create trigger tri_before_delete_t1 defore delete on 表名 for each row

begin
	sql语句
end # 删除之后触发

触发器实例

1.创建cmd命令表与报错日志、创建触发器、插入数据

# 模拟cmd命令表
create table cmd(
    id int primary key auto_increment,
    user char (32),
    priv char(10),
    cmd char(64),  # cmd命令字段
    sub_time datetime # 提交时间
    success enum ('yes', 'no')  #0代表执行失败
);

# 报错日志表
create table errlog(
   id int primary key auto_increment,
   err_cmd char(64),
   err_time datetime
);
----------------------------创建触发器------------------------------
1.创建之前修改结束符
delimiter $$  # 将mysql默认的结束符由；换成$$

# 创建触发器
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
	# 触发器启动 判断cmd中success字段内容记录是否为no，为no则执行以下代码
    if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$

2.创建之后修改结束符
delimiter ;  # 结束之后记得再改回来，不然后面结束符就都是$$了。
-----------------------------插入数据--------------------------------
#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
    USER,
    priv,
    cmd,
    sub_time,
    success
)
VALUES
    ('tony','0755','ls -l /etc',NOW(),'yes'),
    ('tony','0755','cat /etc/passwd',NOW(),'no'),
    ('tony','0755','useradd xxx',NOW(),'no'),
    ('tony','0755','ps aux',NOW(),'yes');

2.验证触发器（查询结果）

查询errlog表记录
select * from errlog;

解析:
	1.当cmd命令行后台被插入数据时，if判断出现为‘on’ 报错的数据时
	2.会自动触发 触发器会执行代码，将为‘on’错误代码 插入到《errlog报错日志》

3.查看触发器

查看触发器：
	show triggers\G;  # 格式化展示
删除触发器：
	drop trigger tri_after_insert_cmd;

事务

1.事务的四大特性（ACID）

A:原子性
	每个事务都是不可分割的最小单位(同一个事务内的多个操作要么同时成功要么同时失败)
C:一致性
	事务必须是使数据从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的
I:隔离性
	事务与事务之间彼此不干扰
D:持久性
	一个事务一旦提交，它对数据库中数据的改变就应该是永久性的

2.事务存在的必要性

比如银行ATM机，转账功能比喻:

A有1000元，B有1000元，C有1000元

A使用工行 通过交行ATM机给B转账90元
A使用工行 通过交行ATM机给C转账10元

A有900元，B有1090元，C有1010元

create table user(
id int primary key auto_increment,
name char(32),
balance int
);

insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);

# 修改数据之前先开启事务操作
start transaction;

# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元

'''
但是在事物里边这个数据还没有到硬盘中，在内存中，还没有保存。
现在这个状态还可以回退,我反悔了我不买了(双方同时失败)
回滚操作:rollback;
'''
# 回滚到上一个状态
rollback;

# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""

# 站在python代码的角度，应该实现的伪代码逻辑，
try:
    update user set balance=900 where name='jason'; #买支付100元
    update user set balance=1010 where name='kevin'; #中介拿走10元
    update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
    rollback;
else:
    commit;

3.开启事务-回滚-确认

开启事务
	start transaction;
回滚
	rollback;
确认
	commit;

4.总结事务

1.开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
2.开启事务检测操作是否完整，不完整主动滚回上一个状态，如果完整就应该执行commit操作

5.扩展事务

事务处理中有几个关键词汇会反复出现
  事务(transaction)
  回滚(rollback)
  提交(commit)
  保留点(savepoint)
		为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)，但是它违反了事务的原理，是一个冷门知识
    创建占位符可以使用savepoint
    	savepoint sp01;
    回退到占位符地址
    	rollback to sp01;
    # 保留点在执行rollback或者commit之后自动释放

6.隔离级别

在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
	set transaction isolation level 级别

1.read uncommitted(未提交读)
	事务中数据修改即使没有提交，对其他事务也都是可见的，在内存中，没有提交到硬盘，别人可能也是引用的我内存中的数据，读的是修改的但是没有提交的，该现象称之为“脏读”
2.read committed(提交读)
	大多数数据库系统默认的隔离级别
	一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
	该事务修改了，只要没有提交，别人是不可读的，其他人用的就是没有改之前原表里面的数据，两者之间不会发生冲突，该现象称之为“不可重复读”
3.repeatable read(可重复读)
	MySQL默认隔离级别
	能够解决"脏读"问题，但是无法解决"幻读"
	所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
	强制事务串行执行，很少使用该级别
	一个个排队执行，效率太低，不可能使用

7.事物日志

事务日志可以帮助提高事务的效率 
	存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
	事务日志采用的是追加方式因此写日志操作是磁盘上一小块区域内的顺序IO而不像随机IO需要次哦按的多个地方移动磁头所以采用事务日志的方式相对来说要快的多
	事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为"预写式日志"修改数据需要写两次磁盘

8.锁

读锁
	多个用户同一时间可以同时读取一个资源互不干扰
写锁
	一个写锁会阻塞其他的写锁和读锁
死锁
	1.多个事务试图以不同的顺序锁定资源时就可能会产生死锁
  2.多个事务同时锁定同一个资源时也会产生死锁

mvcc多版本控制

MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新  serializable:所有的行都加锁)

InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
	一个列保存了行的创建时间
  一个列保存了行的过期时间(或删除时间)  # 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较

例如
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
    username		create_version		delete_version
    jason						1					
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
    username		create_version		delete_version
    jason						1									2
    jason01					2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
		username		create_version		delete_version
    jason01					2									 3
"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
   1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
   2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""

转换表的引擎

主要有三种方式，并各有优缺点!
# 1.alter table
	alter table t1 engine=InnoDB;
  """
  	适用于任何存储引擎 但是需要执行很长时间 MySQL会按行将数据从原表赋值到一张新的表中，在复制期间可能会消耗系统所有的IO能力，同时原表会加读锁
  """
# 2.导入导出
	"""
	使用mysqldump工具将数据导出到文件，然后修改文件中相应的SQL语句
		1.引擎选项
		2.表名
	""" 	
# 3.insert ... select
	"""
	综合了第一种方案的高效和第二种方案的安全
		1.先创建一张新的表
		2.利用insert ... select语法导数据
	数据量不大这样做非常合适 数据量大可以考虑分批处理 针对每一段数据执行事务提交操作避免产生过多的undo
	"""
  ps:上述操作可以使用pt-online-schema-change(基于facebook的在线schema变更技术)工具，简单方便的执行上述过程

安全管理

1.创建用户
	create user 用户名 identified by '密码';
 	"""修改密码"""
  	set password for 用户名 = Password('新密码');
    set password = Password('新密码');  # 针对当前登录用户
2.重命名
	rename user 新用户名 to 旧用户名; 
3.删除用户
	drop user 用户名;
4.查看用户访问权限
	show grants for 用户名;
5.授予访问权限
	grant select on db1.* to 用户名;
  # 授予用户对db1数据库下所有表使用select权限
6.撤销权限
	revoke select on db1.* from 用户名;
"""
整个服务器
	grant all/revoke all
整个数据库
	on db.*
特定的表
	on db.t1
"""

全文检索

MySQL的全文检索功能MYISAM存储引擎支持而InnoDB存储引擎不支持
一般在创建表的时候启用全文检索功能
create table t1(
	id int primary key auto_increment,
  content text
	fulltext(content)
)engine=MyISAM;

# match括号内的值必须是fulltext括号中定义的(单个或者多个)
select content from t1 where match(content) against('jason')
'''上述语句可以用like实现但是查询出来的结果顺序不同 全文检索会以文本匹配的良好程度排序数据再返回效果更佳'''

# 查询扩展
select note_text from productnotes where Math(note_text) Against('jason' with query expansion);
"""
返回除jason外以及其他jason所在行相关文本内容行数据
eg:
	jason is handsome and cool,every one want to be cool,tony want to be more handsome;
	二三句虽然没有jason关键字 但是含有jason所在行的cool和handsome
"""

# 布尔文本搜索
即使没有定义fulltext也可以使用，但是这种方式非常缓慢性能低下
select note_text from productnotes where Match(note_text) Against('jason' in boolean mode);

# 注意事项
1.三个及三个以下字符的词视为短词，全文检索直接忽略且从索引中排除
2.MySQL自身自带一个非用词列表，表内词默认均被忽略(可以修改该列表)
3.出现频率高于50%的词自动作为非用词忽略，该规则不适用于布尔搜索
4.针对待搜索的文本内容不能少于三行，否则检索不返回任何结果
5.单引号默认忽略

插入数据

数据库经常被多个用户访问，insert操作可能会很耗时(特别是有很多索引需要更新的时候)而且还可能降低等待处理的select语句性能
如果数据检索是最重要的(一般都是)，则可以通过在insert与into之间添加关键字low_priority指示MySQL降低insert语句优先级
	insert low_priority  into 
  
insert还可以将一条select语句的结果插入表中即数据导入:insert select
eg:想从custnew表中合并数据到customers表中
  insert into customers(contact,email) select contact,email from custnew;

更新数据

如果使用update语句更新多列值，并且在更新这些列中的一列或者多列出现一个错误会导致整个update操作被取消，如果想发生错误也能继续执行没有错误的更新操作可以采用
	update ignore custmoers ...
  """
  update ignore  set name='jason1',id='a' where id=1;
  	name字段正常修改
  update set name='jason2',id='h' where id=1;
  	全部更新失败
  """

删除数据

delete语句从表中删除数据，甚至可以是所有数据但是不会删除表本身
并且如果想从表中删除所有的行不要使用delete可以使用truncate速度更快并且会重置主键值(实际是删除原来的表并重新创建一个表而不是逐行删除表中的数据)

主键

查看当前表主键自增到的值(表当前主键值减一)
	select last_insert_id();

外键

MySQL存储引擎可以混用，但是外键不能跨引擎即使用一个引擎的表不能引用具有使用不同引擎表的外键

重命名表

rename关键字可以修改一个或者多个表名
	rename table customer1 to customer2;
  rename table back_cust to b_cust,
  						 back_cust1 to b_cust1,
   						 back_cust2 to b_cust2;

存储过程

1.什么是存储过程

存储过程包含了一系列可执行的sql语句，存储过程存放MySQL中，通过调用它的名字可以执行其内部的一堆sql，类似于python中的自定义函数

2.存储过程格式

关键字: procedure
格式:
    create procedure 函数名(参数)
    begin
        功能体代码
    end

    调用其内部sql代码格式
    call 函数名()
查看存储过程具体信息
	show create procedure status;
查看所有存储过程
	show procedure status;
删除存储过程
	drop procedure pro2;

3.无参存储过程

delimiter $$  # 修改结束符
create procedure p1()
begin
	select * from user;
end $$
delimiter ;  # 修改回来结束符

# 通过p1()调用其内部sql代码
call p1();

4.有参存储过程

delimiter $$
create procedure p2(
    in m int,  # in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int  # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
    select * from user where id > m and id < n;
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;


# 针对res需要提前定义
set @res=10;  # 定义/赋值  # 指定res=10
select @res;  # 查看
call p2(1,3,@res);  # 调用
select @res;  # 调用

5.pyMySQL代码调用存储过程

import pymysql

# 创建链接
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='123',
    db='db6',
    charset='utf8',
    autocommit=True  # 涉及到增删改，二次确认
)

# 生成一个游标对象(操作数据库)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 调用存储过程固定语法callproc('p2',(1,3,10))
cursor.callproc('p2',(1,3,10))  # 内部原理 @_p1_0=1,@_p1_1=3,@_p1_2=10;
# 查看结果
print(cursor.fetchall())

内置函数

1.移除指定字符

Trim、LTrim、RTrim

例子：
    mysql> select trim(leading 'x' from 'xxxbarxxx');
    +------------------------------------+
    | trim(leading 'x' from 'xxxbarxxx') |
    +------------------------------------+
    | barxxx                             |
    +------------------------------------+
    1 row in set (0.00 sec)

2.大小写转换

Lower、Upper

例子：
    mysql> select lower('JJDWHI');
    +-----------------+
    | lower('JJDWHI') |
    +-----------------+
    | jjdwhi          |
    +-----------------+
    1 row in set (0.00 sec)
    
    mysql> set @str=binary 'JJDWhjjbjaHI';
    Query OK, 0 rows affected (0.00 sec)

    mysql> select lower(@str),lower(convert(@str using latin1));
    +--------------+-----------------------------------+
    | lower(@str)  | lower(convert(@str using latin1)) |
    +--------------+-----------------------------------+
    | JJDWhjjbjaHI | jjdwhjjbjahi                      |
    +--------------+-----------------------------------+
    1 row in set (0.00 sec)

3.获取左右起始指定个数字符

Left、Right

4.返回读音相似值（对英文效果）

Soundex
"""
eg:客户表中有一个顾客登记的用户名为J.Lee
		但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
		where Soundex(name)=Soundex('J.Lie')
"""
mysql> use db3;
Database changed
mysql> create table t1(
    -> id int,
    -> name varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1(id,name) values(1,'jason'),(1,'json');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | jason |
|    1 | json  |
+------+-------+
2 rows in set (0.00 sec)

mysql> select * from t1 where soundex(name) = soundex('jason');
+------+-------+
| id   | name  |
+------+-------+
|    1 | jason |
|    1 | json  |
+------+-------+
2 rows in set (0.00 sec)

5.日期格式：date_format

'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
CREATE TABLE blog (
    id INT PRIMARY KEY auto_increment,
    NAME CHAR (32),
    sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');

1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数 
	adddate		增加一个日期
	addtime		增加一个时间
	datedif		计算两个日期差值

流程控制

# if条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
    
    declare i int default 0;
    if i = 1 THEN
        SELECT 1;
    ELSEIF i = 2 THEN
        SELECT 2;
    ELSE
        SELECT 7;
    END IF;

END //
delimiter ;


# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN

    DECLARE num INT ;
    SET num = 0 ;
    WHILE num < 10 DO
        SELECT
            num ;
        SET num = num + 1 ;
    END WHILE ;

END //
delimiter ;

索引

1.什么是索引

简单的理解为可以帮助你加快数据查询速度的工具

也可以把索引比喻成书的目录，它能让你更快的找到自己想要的内容

索引就是一种数据结构，类似于书的目录，意味着以后再查找数据应该先找到目录再找数据，而不是用翻页的方式查询数据

索引在MySQL中也叫做'键'，是存储引擎用于快速找到记录的一种数据结构
	primary key、unique key、index key
注意：
	foreign key不是用来加速查询的，不在我们研究范围之内，上面三种key前俩种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会绑你加速查询

本质：
	都是通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查询方式来锁定数据

那么它其实是一把双刃剑，有利也有弊，索引的存在可以加快数据的查询 但是会减慢数据的增删，如果在设置表的时候遇到一个需要加索引的就加索引，那么就会导致类似于要挨个建立索引，建立好多个目录，查找起来更麻烦

2.跟索引相关的关键字

1.聚集索引
	聚集索引其实就是表的主键，innodb引擎规定一张表中不需要有主键
	MyISAM引擎在建表的时候对应的硬盘有三个文件
	innodb引擎在建表的时候对应的硬盘有俩个文件，frm文件只存放表结构，不能存放索引，也就意味着innoDB引擎的索引跟数据都放在idb表数据文件中
	特点：叶子结点放的一条条完整的记录
2.辅助索引
	查询数据的时候不可能都是用id作为筛选条件，也可能会用name,passsord等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果，就需要给其他字段建立索引，这些索引就叫做辅助索引
	特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如：按照name字段创建索引，那么叶子节点存放的是：{name对应的值，name所在的那条记录的主键值})
3.覆盖与非覆盖索引
	select name from user where name='jason';
	上述语句，只在辅助索引的叶子结点中就已经找到了所有我们想要的数据
	select age from user where name='jason';
	上述语句，虽然查询的时候命中了索引字段name，但是要查的是age字段,所以还需要利用主键才去查找

索引的底层原理（树）

innoDB存储引擎默认的索引结构为B+树，而B+树是由二叉树、平衡二叉树、B树再到B+树一路演变过来的

1.二叉树（每个节点只能分两个叉）

2.数据结构（B树，也叫B-树）

B树:
	除了叶子节点可以有多个分支 其他节点最多只能两个分支
	所有的节点都可以直接存放完整数据(每一个数据块是有固定大小的)
B+树:
	只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
B*树
	在树节点添加了通往其他节点的通道 减少查询次数

为什么我们要以id作为主键？
	因为id字段是整形，整形是数字，数字在存储的时候容量更小，那么就可以在一个字段里存放更多的id值，就可以减少树的层级
结论：
	一个磁盘量它的存储容量是有限的，我们尽可能让磁盘里存储更多的信息，这样的话可以降低树的层级，相应的就会加快对数据查找的时间

b*树(在树节点添加了通往其他节点的通道 减少查询次数)

指针的作用:
	添加指针是为了加快范围查询的速度

3.总结

索引的作用:
	索引就是为了提供数据的查询速度
	在计算机底层的表现形式就是一些数据结构(树)
	
数据结构:
	二叉树 : 每个节点只能分两个叉
	b树 : 枝节点和叶节点没有指针
	b+树 : 叶节点添加指针
	b*树 : 枝节点添加了指针(叶节点也有)

指针添加的作用:
	指针的添加主要是为了解决范围查询的问题
	精确查找取决于树的高度

索引的必要性:
	将某个字段添加成索引就相当于依据该字段建立了一颗b+树从而加快查询速度
	如果某个字段没有添加索引 那么依据该字段查询数据会非常的慢(一行行查找)

索引管理

索引建立在表的列上（字段）的，在where后面的列建立索引才会加快查询速度

pages<---索引（属性）<---查数据

查看索引:
	show index from t1;
	desc table;
创建唯一索引需要提前排查是否有重复数据：
	select count(字段) from t1;
	select count(distinct(字段)) from t1;
删除索引：
	alter table 表名 drop index 索引名;
查看表中数据行数：
	select 凑他（*） from city;
查看去重数据行数：
	select count(distinct name) from city;

索引分类

主键索引（指定）

主键索引除了有加速查询的效果之外还具有一定的约束条件

alter table t1 add primary key pri_id(id);  # 以id字段为索引

pri_id	: 索引名<见名之意>

唯一索引

唯一键索引除了有加速查询的效果之外还具有一定的约束条件，使用唯一索引时，指定字段是唯一索引时，该字段如果有重复，使用唯一索引会报错。

alter table t1 add unique key uni_pwd(pwd)

普通索引

普通索引只有加速查询的效果没有额外约束

alter table t1 add index idx_name(name)

联合索引（属于普通索引）

联合索引作用:
	相亲平台 搜索心仪对象的时候 《女，富婆，未婚，漂亮，1.67》

遵循:最左匹配原则
    
例:
	where a.女生 and b.身高 and c.体重 and d.身材好
	index(a.b.c)
特点: 前缀生效特性
	a,ab,ac,abc,abcd 可以走索引或部分走索引
	b bc bcd c d ba... 不走索引

创建联合索引，前缀生效特性
	alter table t1 add index idx_all(id,name,pwd)

前缀索引(属于普通索引)

前缀索引的作用:
	避免对大列建索引(数据很多情况),如果有就使用前缀索引
比如:
	博客内容 百度搜索内容等
    
根据字段前N个字符建立索引
	alter table t1 add index idx_name(name(10))

慢查询优化（explain详解）

1.explain命令使用方法

explain select 查询内容 from 表名 where 条件;

2.查询数据的方式

2.1全表扫描

```mysql

'在explain语句结果中type为ALL，mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描'

什么时候会出现全表扫描？
1. 业务确实要获取所有数据
2. 不走索引导致的全盘扫描
3. 没索引
4. 索引创建有问题
5. 语句有问题

在业务数据库中，特别是数据量比较大的表，是没有全表扫描这种需求。
1. 对用户查看时非常痛苦的
2. 对服务器来讲毁灭性的
```

2.2索引扫描(从上到下，性能从最差到最好，我们认为至少要达到range级别)

常见的索引扫描类型	介绍使用
index	Full Index Scan，index与ALL区别为index类型只遍历索引树。
range	索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。例如：alter table city add index idx_city(population);explain select * from city where population>30000000;
ref	使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。例如：alter table city drop key idx_code;explain select * from city where countrycode='chn';explain select * from city where countrycode in ('CHN','USA');explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
eq_ref	类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A
const、system	当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问，如将主键置于where列表中，MySQL就能将该查询转换为一个常量。例如：explain select * from city where id=1000;
null	MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

3.不走索引的情况(记忆4条以上）

1.没有查询条件，或者查询条件没有建立索引
	全表扫描：select * from table;
		select * from 表名 where 条件
2.查询结果集是原表中的大部分数据(25％以上)有可能不走索引
	explain select * from city where population>3000 order by population;
	如果业务允许，可以使用limit控制
	结合业务判断，有没有更好的方式，如果没有更好的改写方案就尽量不要在mysql存放这个数据了，放到redis里面。
3.索引本身失效，统计数据不真实
	索引有自我维护的能力
	对于表内容变化比较频繁的情况下，有可能会出现索引失效
	重建索引就可以解决
4.查询条件使用函数在索引列上或者对索引列进行运算，运算包括(+，-，*等)
	错误的例子:	select * from test where id-1=9;
	正确的例子:  select * from test where id=10;
5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误
	测试隐式转换导致失效(类型转错成int类型)，见图5-1
	纠正隐式转换导致的失败(传入正确的 字符串类型)，见图5-2

6.<> ，not in 不走索引
	单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit、or或in尽量改成union
7.like "%_" 百分号在最前面不走
	走range索引扫描
		EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%';
	不走索引
		EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110';
8.单独引用联合索引里非第一位置的索引列(最多匹配原则，第一个不满足，剩下的就不满足了)
	mysql> ALTER TABLE t2 ADD INDEX t1_idx(money,age,sex);
	Query OK, 0 rows affected (0.04 sec)
	Records: 0  Duplicates: 0  Warnings: 0

	mysql> desc t2;
	+-------+---------------+------+-----+---------+-------+
	| Field | Type          | Null | Key | Default | Extra |
	+-------+---------------+------+-----+---------+-------+
	| id    | int(11)       | YES  |     | NULL    |       |
	| NAME  | varchar(20)   | YES  |     | NULL    |       |
	| age   | int(11)       | YES  |     | NULL    |       |
	| sex   | enum('m','f') | YES  |     | NULL    |       |
	| money | int(11)       | YES  | MUL | NULL    |       |
	+-------+---------------+------+-----+---------+-------+
	5 rows in set (0.03 sec)
	mysql> SHOW INDEX FROM t2;
    #走索引的情况测试
	EXPLAIN SELECT NAME,age,sex,money FROM t2 WHERE money=30 AND age=30 AND sex='m';
    #部分走索引
	EXPLAIN SELECT NAME,age,sex,money FROM t2 WHERE money=30 AND age=30;
	EXPLAIN SELECT NAME,age,sex,money FROM t2 WHERE money=30 AND sex='m';
    #不走索引
	EXPLAIN SELECT NAME,age,sex,money FROM t2 WHERE age=20;
	EXPLAIN SELECT NAME,age,sex,money FROM t2 WHERE age=30 AND sex='m';
	EXPLAIN SELECT NAME,age,sex,money FROM t2 WHERE sex='m';

索引的创建会加快数据的查询速度但是一定程度会拖慢数据的插入和删除速度。

posted @ 2022-11-22 19:38 hugmi男孩阅读(54) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· Django7-8

· Django2

· MySQL查询关键字where、group by、having、distinct、order by、limit、redexp正则、多表查询之子查询、连表操作

· 数据库与MySQL

· SQL语句

阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 一文读懂知识蒸馏
· 终于写完轮子一部分：tcp代理了，记录一下

公告

昵称： hugmi男孩
园龄： 2年5个月
粉丝： 10
关注： 16

+加关注

2025年3月

日

一

二

三

四

五

六

zjl248

初识MySQL

mysql

数据存取演变史

数据库软件应用史

数据库的本质

数据库的分类

MySQL数据库简介

基本使用

系统服务制作

密码相关操作

SQL和NoSQL

基本SQL语句

1.针对库的基本SQL语句

2.针对表的基本SQL语句

3.针对记录的基本SQL语句

字符编码与配置文件

数据库存储引擎

创建表的完整语法

字段类型之整型

字符类型之浮点型

字段类型之字符类型

字段后面数字的含义

字段类型之枚举和集合

字段类型之日期类型

字段约束条件

字段约束条件

无符号、零填充

非空

默认值

唯一值

主键

主键的自增

外键

1.外键前戏

2.外键字段的创建

3.表关系之一对多

4.表关系之多对多

5.表关系之一对一

SQL语句查询关键字

前期数据准备

编写SQL语句的小技巧

查询关键字之where筛选

查询关键字之group by 分组

查询关键字之having过滤

查询关键字之distinct去重

查询关键字之order by排序

查询关键字之limit分页

查询关键字之regexp正则表达式

多表查询的思路

多表查询的两种方法

小知识点补充说明

可视化软件Navicat

多表查询练习题

python操作MySQL

pymysql补充说明

SQL注入问题

视图

视图的应用

触发器

触发器实例

事务

mvcc多版本控制

转换表的引擎

安全管理

全文检索

插入数据

更新数据

删除数据

主键

外键

重命名表

存储过程

内置函数

流程控制

索引

索引的底层原理（树）

索引管理

公告

搜索