6.数据库（讲义）

-da数据库概述

• 为什么要使用数据库？

  • 那我们在没有学习数据库的时候，数据存放在json或者磁盘文件中不也挺好的嘛，为啥还要学习数据库？

    • 文件中存储数据，无法基于文件直接对数据进行操作或者运算，必须借助python将数据读取到计算机内存中然后基于Python程序操作数据，麻烦而且性能不高。

    • 使用文件存储数据，无法实现数据的共享。

• 什么是数据库呢？先介绍几个概念：

  • 数据：Data

    • 描述事物的符号记录称为数据，描述事物的符号既可以是数字，也可以是文字、图片，图像、声音、语言等，数据由多种表现形式，它们都可以经过数字化后存入计算机。

    • 例如：描述一个人的基本信息就是一组数据，在数据库中叫一行记录。

    • name age sex
      
      Ailsa 23 女
      

  • 数据库：DataBase(DB)

    • 即存放数据的仓库，我们可以把它想象成文件夹，里面存放了很多张表，每个表就是一个文件，不过数据库中的数据是按照一定的规则进行组织、描述和储存，且具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享

  • 数据库管理软件

    • 在了解了Data与DB的概念后，如何科学地组织和存储数据，如何高效获取和维护数据成了关键，这就用到了一个系统软件—数据库管理系统

    • 如MySQL、Oracle、SQLite、Access、MS SQL Server

    • 但是我们日常工作中经常会把数据库管理软件称为数据库，注意理解对象的意思：例如

      • 小王啊，你们公司用的什么数据库啊，这个指的是数据库管理软件
      • 小王啊，这个学生管理系统的数据库是哪个？这个指的就是数据库文件夹，一般情况下一个项目的所有涉及到的数据会存放在一个数据库中，方便统一管理。

  • 总结：

    • 数据库服务器：运行数据库管理软件
    • 数据库管理软件：管理数据库
    • 数据库：即文件夹，用来组织文件/表
    • 表：即文件，用来存放多行内容/多条记录

  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-s6x4mxXb-1640509917610)(imgs/40.png)]

Mysql介绍

• 数据库管理软件按照分类分为 关系型数据库和非关系型数据库
• 关系型数据库
  • 在数据库中各个表之间存在关联关系，需要设计表结构，一张表中每个字段之间也存在关系，通过SQL语句对数据库进行增删改查的操作。
  • 例如：MySQL、oracle 、sql server、sqllite，access，db2
  • 注意：sql语句通用
• 非关系型数据库
  • 非关系型数据库是key-value存储的，没有表结构，存取速度快
  • 例如：redis、mongodb
• Mysql：
  • MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。MySQL 最流行的关系型数据库管理系统，在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
  • MySQL被广泛的应用在Internet上的大中小型网站中。由于体积小、速度快、总体拥有成本低，开放源代码
  • 特点:开源,免费,应用广泛

Mysql下载和安装

下载

我们下载安装的是mysql管理软件，它相当于是一个服务端，每个建立好的连接的用户都可以访问，但是这里为了让自己自主学习，所以我们会把这个软件下载安装到每个人的自己电脑上，相当于在自己电脑上安装了mysql的服务端，这个软件他也自带一个客户端，我们可以通过cmd终端的形式进行访问，当然也可以通过可视化工具navicate来访问，接下来我们会一一讲解。

• mysql管理下载

  • 第一步：打开网址，https://www.mysql.com，点击downloads之后跳转到https://www.mysql.com/downloads

  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bkyvK06q-1640509917613)(imgs/41.png)]

  • 第二步：选择Community选项

  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MeeA603D-1640509917613)(imgs/42.png)]

  • 第三步：选择对应的操作系统版本

  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4H5un31W-1640509917614)(imgs/43.png)]

  • 第四步：选择具体版本的mysql，建议选择5.6或者5.7版本

  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Dz1QVTKy-1640509917615)(imgs/44.png)]

  • 第五步：直接下载

  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vjhL4uuS-1640509917617)(imgs/45.png)]

解压

• 下载的zip文件解压，将解压之后的文件夹放到任意目录下，这个目录就是mysql的安装目录。
• 注意:安装目录尽量在某盘的根目录下,如果不在根目录,则安装路径中不能有中文,不能有特殊转义符的出现。

配置环境变量

• 在系统变量PATH后面添加: 你的mysql的bin文件夹的路径（如C:\Program Files\mysql-5.6.41-winx64\bin）

执行初始化

在任意路径下，以管理员的身份打开终端执行：

mysqld --initialize-insecure

初始化时将root用户的登录密码设置为空。

注意，如果报错：提示缺少MSVCP120.dll文件的话，后面有解决办法。

初始化成功如下图：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tTRu6F8v-1640509917618)(imgs/56.png)]

初始化成功的另一个标志是，在MySQL的安装目录中，会多个data目录，这个data目录是是MySQL在初始化过程中创建的数据目录。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wicrz8zz-1640509917619)(imgs/57.png)]

安装Mysql服务

• 1.以管理员身份打开cmd窗口，记住,一定要是管理员身份
• 2.输入mysqld install回车运行

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xIaOeYLB-1640509917621)(imgs/58.png)]

启动Mysql服务

• 现在系统的服务中就可以找到MySQL了。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Nj7asoWS-1640509917622)(imgs/59.png)]

• 但此时MySQL服务还没有启动，你可以在服务中点击启动，也可以在终端中使用net命令来启动/关闭MySQL服务。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UaBXJHAw-1640509917623)(imgs/60.png)]

• ok，现在MySQL服务正常启动了，并且，由于系统服务中的MySQL服务设置的是自动，下次系统启动时，MySQL服务也默认启动了。
• 服务启动成功之后，就可以登录了
  • 输入mysql -u root -p（第一次登录没有密码，直接按回车过）
  • 但登录成功之后需要设置密码:
    • 语法: set password = password(‘密码’) 为了方便记忆，密码尽量简单一点，但是实际工作中为了保证数据安全，密码尽量设置的复杂一些。

可能发生问题处理

• 缺少MSVCP120.dll文件

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UIRGJGo5-1640509917623)(imgs/61.png)]

• 报错原因是，系统缺少Visual C ++可再发行组件包。

• 解决：

  • 打开microsoft官网：https://www.microsoft.com/en-us/download/details.aspx?id=40784，点击下载:
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-00Porsji-1640509917625)(imgs/62.png)]
  • 根据系统位数选择下载：
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cFNJPP2D-1640509917626)(imgs/63.png)]
  • 以管理员的身份运行，然后默认安装即可。
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yUxkSD6Z-1640509917626)(imgs/64.png)]
  • 重新以管理员身份打开终端，重新执行初始化步骤吧！

  mysqld --initialize-insecure
  

Navicate下载安装

• Navicate是一种操作数据库的可视化工具。
• 下载地址：https://www.navicat.com.cn/products
• 使用Navicate链接数据库：连接成功之后，你就可以通过该软件查看所有的数据库文件了
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ScF0DVAf-1640509917627)(imgs/47.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sgqPrNF5-1640509917628)(imgs/48.png)]

Sql概述

• 先来看一个例子：小王第一次使用数据库，然后跟数据库来了个隔空对话

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ebG34XXT-1640509917629)(imgs/49.png)]

  • 其实，我们想一想，mysql是一个软件，它有它自己一套的管理规则，我们想要跟它打交道，就必须遵守它的规则，如果我想获取数据，它自己有一套规则，这个规则就是SQL。

• 什么是sql？

  • SQL : 结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程言，是一种数据库查询和程序设计语言，SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。
  • 其实简单的说，就是你发送给他能识别的暗号，他懂了就会给你返回数据。
  • 注意：
    • 对于关系型数据库而言，SQL语句是通用的，学会了一种，其他只是一些细微的差别，毕竟人家数据库也是一个种族，语言是通用的，只不过有些存在方言的差别。

• sql的分类

  • SQL语言分为3种类型：

    1、DDL语句 数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER

    2、DML语句 数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT

    3、DCL语句 数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

库表操作DDL

库的增删改查

这里的库指的数据库，也就是我们所谓的那个文件夹，一般情况下，我们在开发项目前，会先设计数据库中相关表结构，一个项目中所有表都会放在同一个文件夹下，对于库的操作属于SQL分类中的DDL，也就是数据库定义语言。

• 创建数据库

•  create database db1;
  

• 创建数据库并制定编码

• create database 库名 charset 字符编码
  create database db1 charset utf8;
  

• 使用数据库

• use 库名
  

• 查看所有数据库和单独常看当前数据库

• # 查看当前库
  select database();
  # 查看所有数据库
  show datasets;
  

• 修改数据库

  • 数据库的名称一旦创建好之后就无法修改
  • 修改数据库一般只修改编码

• alter database 库名 charset 字符编码
  alter database db1 charset utf8;
  

• 删除数据库

• drop database 库名
  drop database db1;
  

表的增删改查

创建表

• create table 表名(
    字段名1 类型(宽度) 约束条件,
    字段名2 类型(宽度) 约束条件,
    字段名3 类型(宽度) 约束条件,
  );
  注意:
  	1.字段名不能重复
  	2.宽度和约束条件可选
  	3.字段名和类型是必须的
  

查看表结构

• desc 表名;   
  show create table 表名; 
  

数据类型

**字符串：**顾名思义，就是存储的一连串的字符，例如文字

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jAvoIxxB-1640509917630)(imgs/50.png)]

​

• 我们主要用到的是char和varchar这两种字符串类型，二者的区别是面试经常考的内容

  • 区别1：定长和变长

    • char 固定长度，例如你定义了char(8),则这一列中存储的内容长度都为8，不足8则会用空格补充（但是我们在查询的时候是不会带空格的，mysql会对此进行处理）

    • varchar 变长存储，则根据实际的字符长度存储，例如varchar(8),则不足8按照实际存储

    • 注意：对于上述两个类型，如果存储的数据超过设定好的长度会如何？

      • 如果超过8则会根据你设置的sql_mode而定，默认是【NO_ENGINE*SUBSTITUTION】-非严格模式，这种情况下，超过8会被截断；如果你的sql_*mode是 【strict_trans_tables】则超过8会报错

      • 查看sql_mode模式

      • # 查看模式:默认是非严格模式
        show variables like '%mode%'
        

      • 修改成严格模式:改完之后需要退出重登

      • set global sql_mode="strict_trans_tables";
        

  • 区别2：存储方式

    • char直接存储字符内容
    • varchar 开头由1-2个字节存储该字符的总长度，后面接着存储字符内容
    • 两种存储方式的优劣
      • char的存取速度很快，但是由于是定长，当大部分内容没有达到规定长度时，会浪费不少空间资源；varchar则不会，它根据实际长度存储，但是由于存储的特殊形式造成存取速度不及char。
      • char存储的内容都为一个定值，则可以提高存取效果，因为varchar还要留出一部分存储字符串的长度，则存取速度不如char。
      • 很早之前，大家都觉得varchar好，节省资源，但是到现在，磁盘资源已经不成问题，因此我更倾向于选择char,也就是所谓的以空间换时间了。

数值型：常用的有：int ，double， float

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g7CMXeGi-1640509917631)(imgs/51.png)]

• 整数型：int 基本int能够处理日常工作中大部分整数存储问题
• 小数型：double float
• 强调一下float(5,2)其中5代表总长度，2代表小数长度,这个意思是整数是3位,小数是2位
• 日常工作中float足以解决小数问题了。

日期类型

• now()函数：返回当前系统时间

• date：年月日

• time：时分秒

• datetime：年月日时分秒

select now();

ENUM和SET类型：

• 这里的类似于下拉字段，在进行数据插入的时候，必须选择事先设置的内容
• 对于set而言，可以多选，但是enum只能单选

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nUMfRpve-1640509917632)(imgs/53.png)]

create table t1(id int,name char(6),gender enum('female','male'),hobby set('抽烟','喝酒','烫头'));
insert into t1 values(1,'张三','female','喝酒,烫头');

约束条件

• 为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。

• 约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种：

  • NOT NULL ：非空约束，指定某列不能为空；
  • DEFAULT:默认值
  • UNIQUE : 唯一约束，指定某列或者几列组合不能重复
  • PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录
  • FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性

• not null:不为空，当你设置一个字段时，不允许它为空，可以使用此约束条件

• create table t1(id int,name varchar(20) not null);
  

• default 默认值

  • 例如：对于性别一列，如果大部分都是男性，可以设置成默认值，不填则取默认值，填写了则覆盖默认值

• create table t2 (id int,name char(8),sex char(8) DEFAULT 'man');
  

• unique 唯一:当有一列字段你不想让它有重复值时，可以设置为唯一

create table t3(name varchar(10),perId int unique,age int)

• 联合唯一：只有当你设置的这些字段同时重复时才会报错

create table t4(name varchar(10),perId int ,age int,unique(name,perID))

• primary key

  • 主键为了保证表中的每一条数据的该字段都是表格中的唯一值。换言之，它是用来独一无二地确认一个表格中的每一行数据。

  • 主键可以包含一个字段或多个字段。当主键包含多个字段时，称为组合键 (Composite Key),也可以叫联合主键。

  • 单字段主键：

  • create table t5(id int PRIMARY KEY,name varchar(10),age int)
    

  • 联合主键:

  •  create table t6(id int ,name varchar(10),age int,PRIMARY KEY(id,name))
    

• auto_increment 自增字段:

  • 对于主键id而言，往往我们可以设置为自增字段，不用手动填写

  • create table t7(id int PRIMARY KEY auto_increment ,name varchar(10),age int);
    

• foreign key

  • 思考：

    • 假设我们要描述所有公司的员工，需要描述的属性有这些 ： 姓名,年龄，性别，部门，部门描述

    • 公司有3个部门，但是有1个亿的员工，那意味着部门和部门描述这两个字段需要重复存储，部门名字和部门描述内容越长，越浪费内存，如何处理呢？

      • 解决方法： 我们完全可以定义一个部门表然后让员工信息表关联该表，如何关联，即foreign key

      • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OsD7OoB0-1640509917632)(imgs/54.png)]

        • foreign key(当前表中建立关系的外键字段) references 被关联表名(id)

      • #先创建被关联表
        create table dep (
        	id int primary key auto_increment,
        	dep_name char(10) not null,
        	dep_desc varchar(50) not null
        )
        
        #创建关联表
        create table emp (
        	id int PRIMARY KEY auto_increment,
        	name varchar(10),
        	age int,
        	gender enum('male','female'),
        	dep_id int,
        	FOREIGN KEY(dep_id) references dep(id)
        )
        #作用:
        #1.减少数据的冗余度
        #2.防止插入无效的脏数据
        

  • 级联删除，级联更新

    • 两张表建立关联之后，如果部门表某个部门的砍掉了，那对应的人员表中的那些部门的人员相应的该怎么处理呢？可以保存，也可以随之一起删除.

      • 如果要保证两表一致，则需要在设置外键时添加on delete cascade

      • 如果部门id更新了，要一起更新的话，则添加on update cascade

      • create table emp (
        	id int PRIMARY KEY auto_increment,
        	name varchar(10),
        	age int,
        	gender enum('male','female'),
        	dep_id int,
        	FOREIGN KEY(dep_id) references dep(id) on delete cascade on update cascade
        )
        

修改表

• 修改表名

• 修改表字段的数据类型

• 修改表字段名

• 修改字段名以及数据类型和约束

• 新增字段

• 删除字段

删除表

记录操作DML

插入数据

• 插入完整的数据

• insert into tableName values (字段1,字段2)
  

• 插入指定字段的数据

• insert into tableName (name,age) values ('lisi',23)
  

• 插入多条数据

• insert into tableName values (字段1,字段2),(字段1,字段2)
  

更新数据

• 单独更新一个字段

• update emp set age = 33 where name = 'zhangsan'
  

• 更新多个字段

• update emp set age = 33,sex='female' where name = 'zhangsan'
  

删除数据

• 删除指定数据

• delete from emp where name = 'lisi'
  

• 删除表中所有数据

• delete from emp
  

查询数据

单表查询

• 准备数据

• #创建员工表，字段为：
  #id自增，员工名不能为空，性别只可以为male或者female且不能为空，默认值为male
  #age不能为空，默认值28，入职日期只显示年月日，职位名称，工资保留两位小数，办公室门牌号，部门id
  
  
  create table emp (
  	id int PRIMARY KEY auto_increment,
  	name varchar(10) not null,
  	sex enum('female','male') default 'male',
  	age int default 28,
  	hireDate date,
  	post char(10),
  	salary float(9,2),
  	office_num int,
  	dep_id int
  
  )
  
  insert into emp (name,sex,age,hireDate,post,salary,office_num,dep_id) values
  ('huahua','male',18,'20170301','teacher',7300.33,401,1), #以下是教学部
  ('weiwei','male',78,'20150302','teacher',1000000.31,401,1),
  ('lala','male',81,'20130305','teacher',8300,401,1),
  ('zhangsan','male',73,'20140701','teacher',3500,401,1),
  ('liulaogen','male',28,'20121101','teacher',2100,401,1),
  ('aal','female',18,'20110211','teacher',9000,401,1),
  ('zhugelang','male',18,'19000301','teacher',30000,401,1),
  ('成龙','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),
  

• 查询所有字段信息

• select * from emp
  

• 查询指定字段信息

• select name,salary from emp
  

• 通过四则运算查询

• #查看所有员工的年薪
  select name,salary from emp
  

  
- 为字段重命名

- ```
  select name,salary*12 as year_salary from emp

• 条件查询where语句

  • 单条件查询

  • #查看岗位是sale的员工名称和岗位名称
    select name,post from emp where post='sale'
    

    
  - 多条件查询1：结合and
  
  - ```
    #查询岗位是教师且工资大于10000的员工名字和薪资
    select name,salary from emp where post='teacher' and salary > 10000
  

  • 多条件查询2：结合or

  • #查询员工薪资为3500，4000和9000的员工名称和年龄
    select name,age from emp where salary=3500 or salary=4000 or salary=9000
    

    
  - 多条件查询3：结合between...and
  
  - ```
    #查询员工薪资在1000到5000的员工名字和年龄和岗位名称
    select name,age,post from emp where salary BETWEEN 1000 and 5000
  
  

  • 多条件查询4:结合in

  • #查询员工薪资为3500，4000和9000的员工名称和年龄
    select name,age from emp where salary in (3500,4000,900)
    
    

  • 多条件查询5:结合not in

  • #查询员工薪资不为3500，4000和9000的员工名称和年龄
    select name,age from emp where salary not in (3500,4000,900)
    

    
  - 模糊查询like
  
    - 通配符%:表示多个字符
  
      - ```
        #查看姓zhang的员工的年纪,名字和薪资
        
      select name,age,salary from emp where name like 'zhang%'
  

  • 通配符_:表示一个字符

    • #查看姓zhang的员工的年纪,名字和薪资
      select name,age,salary from emp where name like 'zhang___'
      
      

• 分组查询：group by

  • 简单的分组查询

    • 注意：使用group by的查询字段必须是分组字段，否则会出错，想要获取其他字段信息,可以借助于group_concat或聚合函数

  • #根据岗位名称进行分组
    select post from emp group by post
    

    
  - 使用group_concat(字段名)，可以实现在使用group by的查询字段为其他字段名
    
    - ```
      select post from emp group by post
  

  • 分组聚合

  • #对岗位分组，查看分组后的岗位名称和岗位人数
    select post,count(id) from emp GROUP BY post;
    

  or
  select post,count(1) from emp GROUP BY post;

  
  - having子句
  
  - where 与 having的区别：	
  
    - where 是针对分组之前的字段内容进行过滤,而having是针对分组后的
  
  - 注意：having后面的条件字段只可以是分组后结果中存在的字段名，否则会报错！
  
  - ```
    #查看不同岗位下薪资超过6000的员工数量
    select post,count(1) from emp where salary > 6000 GROUP BY post 
    
    #根据年龄分组查看年龄大于30的员工数量和姓名
    select age,GROUP_CONCAT(name) from emp GROUP BY age having age > 30
    
    #基于薪资分组,将大于4000员工的平均薪资求出
    select avg(salary) from emp GROUP BY salary having salary >= 4000
    ```
  
  

• 排序：order by

  • 升序： order by 字段 asc(默认升序,可以不写)

  • 降序： order by 字段 desc

  • 单列排序：

  • #按照薪资的高低进行员工的排序
    select name,post,salary from emp ORDER BY salary desc
    
    

  • 多列排序：越前面的列优先级越高

  • #根据年龄和薪资对员工排序
    select name,age,salary,post from emp ORDER BY age,salary
    
    

• limit

  • 显示前三条数据

  • select * from emp LIMIT 3
    
    

    
  - 从0开始,先查出第一条,然后包含这条再往后查5条
  
  - ```
    select * from emp LIMIT 0,5
    
  

  • 从第3开始,即先查出第4条,然后包含这条再往后查7条

  • select * from emp LIMIT 3,7
    
    #查询出薪资最高的前三名员工的信息
    select * from emp ORDER BY salary desc LIMIT 3
    
    

• 使用正则：regexp

• #找出员工名字是以z开头的
  
  select name from emp where name REGEXP "^zha"
  #找出员工名字是以n结尾的
  select name from emp where name REGEXP "n$"
  

多表查询

根据指定条件将两张表中的数据进行合并，然后在合并后的结果表中进行数据的查询

• 准备数据

• #创建部门表：主键id，name
  #创建员工表：主键id，name，sex非空-枚举类型，年龄，部门id
  create table emp (
  	id int primary key,
      name varchar(10),
      sex enum("male","female"),
      age int,
      dep_id int,
      #foreign key(dep_id) references dep(id)
  )
  
  create table dep (
  	id int primary key,
      name varchar(10)
  )
  
  # 插入数据
  (200,'技术'),
  (201,'人力资源'),
  (202,'销售'),
  (203,'运营');
  
  ('ailsa','male',18,200),
  ('lala','female',48,201),
  ('huahua','male',38,201),
  ('zhangsan','female',28,202),
  ('zhaosi','male',18,200),
  ('shenteng','female',18,204)
  ;
  

• 内连接:

  • 两张表公共的部分,必须同时有,没有就不显示

• select * from emp as e inner join dep as d on e.dep_id = d.id
  

• 外连接：

  • 两张表进行指定条件连接，能连接的就连接，连接不了的显示null
  • 把左右两边的数据都显示完整,匹配不上的则显示为null,在mysql中没有真正的全外连接,可以使用union来实现

  select * from emp as e right join dep as d on e.dep_id = d.id
  union
  select * from emp as e left join dep as d on e.dep_id = d.id
  

• 外连接之左连接

  • 以左表为主表,根据左表数据匹配右表,左表数据是全的,而右表若匹配不上则为null

• select * from emp as e left join dep as d on e.dep_id = d.id
  
  

• 外连接之右连接

  • 以右表为主表,根据右表数据匹配左表,右表数据是全的,而左表若匹配不上则为null

• select * from emp as e right join dep as d on e.dep_id = d.id
  
  

  
- 符合条件的多表联查

- ```
  #示例1：以内连接的方式查询emp和dep表，并且emp表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
  select e.name,d.name from emp as e inner join dep as d on e.dep_id = d.id where age > 25
  
  
  
  #示例2：以内连接的方式查询emp和dep表，并且emp表中的age字段值必须大于25，并且以age字段的升序方式显示
  
  select e.name,d.name from emp as e inner join dep as d on e.dep_id = d.id where age > 25 ORDER BY age 
  
  

• 子查询:子查询是将一个查询语句嵌套在另一个查询语句中

  • 带in关键字的子查询

    • 查询平均年龄在25岁以上的部门名部门id

  • select id,name from dep where id in (select dep_id from emp GROUP BY dep_id having avg(age) > 25
    )
    

    
- 查看技术部员工姓名和员工id
    
  - ```
    select name,id from emp where dep_id in (select id from dep where name = '技术')
  

• 带比较运算符的子查询(比较运算符: =、!=、>、>=、<、<=、<>)

  • 查询大于所有人平均年龄的员工与年龄

  select name,age from emp where age > (select avg(age) from emp)

综合练习

• 准备数据

• CREATE TABLE class (
    cid int(11) NOT NULL AUTO_INCREMENT,
    caption varchar(32) NOT NULL,
    PRIMARY KEY (cid)
  ) ENGINE=InnoDB CHARSET=utf8;
  
  INSERT INTO class VALUES
  (1, '三年二班'), 
  (2, '三年三班'), 
  (3, '一年二班'), 
  (4, '二年九班');
  
  CREATE TABLE teacher(
    tid int(11) NOT NULL AUTO_INCREMENT,
    tname varchar(32) NOT NULL,
    PRIMARY KEY (tid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  INSERT INTO teacher VALUES
  (1, '张磊老师'), 
  (2, '李平老师'), 
  (3, '刘海燕老师'), 
  (4, '朱云海老师'), 
  (5, '李杰老师');
  
  CREATE TABLE course(
    cid int(11) NOT NULL AUTO_INCREMENT,
    cname varchar(32) NOT NULL,
    teacher_id int(11) NOT NULL,
    PRIMARY KEY (cid),
    FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  INSERT INTO course VALUES
  (1, '生物', 1), 
  (2, '物理', 2), 
  (3, '体育', 3), 
  (4, '美术', 2);
  
  CREATE TABLE student(
    sid int(11) NOT NULL AUTO_INCREMENT,
    gender char(1) NOT NULL,
    class_id int(11) NOT NULL,
    sname varchar(32) NOT NULL,
    PRIMARY KEY (sid),
    FOREIGN KEY (class_id) REFERENCES class (cid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  INSERT INTO student VALUES
  (1, '男', 1, '理解'), 
  (2, '女', 1, '钢蛋'), 
  (3, '男', 1, '张三'), 
  (4, '男', 1, '张一'), 
  (5, '女', 1, '张二'), 
  (6, '男', 1, '张四'), 
  (7, '女', 2, '铁锤'), 
  (8, '男', 2, '李三'), 
  (9, '男', 2, '李一'), 
  (10, '女', 2, '李二'), 
  (11, '男', 2, '李四'), 
  (12, '女', 3, '如花'), 
  (13, '男', 3, '刘三'), 
  (14, '男', 3, '刘一'), 
  (15, '女', 3, '刘二'), 
  (16, '男', 3, '刘四');
  
  CREATE TABLE score (
    sid int(11) NOT NULL AUTO_INCREMENT,
    student_id int(11) NOT NULL,
    course_id int(11) NOT NULL,
    num int(11) NOT NULL,
    PRIMARY KEY (sid),
    KEY fk_score_course (course_id),
    CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),
    CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  INSERT INTO score VALUES
  (1, 1, 1, 10),
  (2, 1, 2, 9),
  (5, 1, 4, 66),
  (6, 2, 1, 8),
  (8, 2, 3, 68),
  (9, 2, 4, 99),
  (10, 3, 1, 77),
  (11, 3, 2, 66),
  (12, 3, 3, 87),
  (13, 3, 4, 99),
  (14, 4, 1, 79),
  (15, 4, 2, 11),
  (16, 4, 3, 67),
  (17, 4, 4, 100),
  (18, 5, 1, 79),
  (19, 5, 2, 11),
  (20, 5, 3, 67),
  (21, 5, 4, 100),
  (22, 6, 1, 9),
  (23, 6, 2, 100),
  (24, 6, 3, 67),
  (25, 6, 4, 100),
  (26, 7, 1, 9),
  (27, 7, 2, 100),
  (28, 7, 3, 67),
  (29, 7, 4, 88),
  (30, 8, 1, 9),
  (31, 8, 2, 100),
  (32, 8, 3, 67),
  (33, 8, 4, 88),
  (34, 9, 1, 91),
  (35, 9, 2, 88),
  (36, 9, 3, 67),
  (37, 9, 4, 22),
  (38, 10, 1, 90),
  (39, 10, 2, 77),
  (40, 10, 3, 43),
  (41, 10, 4, 87),
  (42, 11, 1, 90),
  (43, 11, 2, 77),
  (44, 11, 3, 43),
  (45, 11, 4, 87),
  (46, 12, 1, 90),
  (47, 12, 2, 77),
  (48, 12, 3, 43),
  (49, 12, 4, 87),
  (52, 13, 3, 87);
  

• 题目

• 1、查询所有的课程的名称以及对应的任课老师姓名
  
  
  2、查询学生表中男女生各有多少人
  
  3、查询物理成绩等于100的学生的姓名
  
  4、查询平均成绩大于八十分的同学的姓名和平均成绩
  
  
  5、查询所有学生的学号，姓名，选课数，总成绩
  
  
  6、 查询姓李老师的个数
  
  7、 查询没有报李平老师课的学生姓名
  
  8、 查询物理课程比生物课程高的学生的学号
  返回每个学生的物理分数表和每个学生生物分数表，将其连接成一张表，然后进行条件查询
  
  
  9、 查询没有同时选修物理课程和体育课程的学生姓名
  
  10、查询挂科超过两门(包括两门)的学生姓名和挂科数量
  
  
  
  11、查询选修了所有课程的学生姓名
  
  
  12、查询李平老师教的课程的所有学生（id）的成绩记录
  
   
  13、查询全部学生都选修了的课程号和课程名
  
  
  14、查询每门课程被选修的次数(每门课有多少学生选择)
  
  15、查询只选修了一门课程的学生姓名和学号
  
  
  16、查询所有学生考出的成绩并按从高到低排序（成绩去重:在需要去重字段前面加distinct）
  
  17、查询平均成绩大于85的学生姓名和平均成绩
  
  
  18、查询生物成绩不及格的学生姓名和对应生物分数
  
  
  19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
  
  

• 答案

• 1、查询所有的课程的名称以及对应的任课老师姓名
  select cname,tname from course as c inner join teacher as t on c.teacher_id = t.tid
  
  2、查询学生表中男女生各有多少人
  select gender,count(1) from student GROUP BY gender
  
  3、查询物理成绩等于100的学生的姓名
  select sname from student as t inner join score as s on t.sid = s.student_id
  inner join course as c on c.cid = s.course_id
  where cname = '物理' and num = 100
  
  4、查询平均成绩大于八十分的同学的姓名和平均成绩
  select sname,avg(num) as 平均成绩  from student as t inner join score as s on t.sid = s.student_id
  GROUP BY t.sid having avg(num) > 80
  
  5、查询所有学生的学号，姓名，选课数，总成绩(存在有学生没有选择任何一门课)
  select t.sid,t.sname,count(s.course_id) as 选课数,sum(num) as 总成绩 from student as t left join score as s on t.sid = s.student_id
  GROUP BY t.sid 
  6、 查询姓李老师的个数
  select count(1) from teacher where tname like '李%'
  7、 查询没有报李平老师课的学生姓名
  select sname from student where sid not in (
  select t1.sid from student as t1 inner join score as s on t1.sid = s.student_id
  inner join course as c on s.course_id = c.cid
  inner join teacher t2 on c.teacher_id = t2.tid
  where tname = '李平老师'
  )
  
  8.查询物理课程比生物课程高的学生的学号
  select sw.sid from
  (select t.sid,num from student as t inner join score as s on t.sid = s.student_id
  inner join course as c on c.cid = s.course_id where cname = '生物') as sw
  inner join
  (select t.sid,num from student as t inner join score as s on t.sid = s.student_id
  inner join course as c on c.cid = s.course_id where cname = '物理') as wl
  on  sw.sid = wl.sid
  where wl.num > sw.num
  
  9、 查询没有同时选修物理课程和体育课程的学生姓名
  select t.sname from student as t inner join score as s on t.sid = s.student_id
  inner join course as c on c.cid = s.course_id
  where cname = '物理' or cname = '体育'
  GROUP BY t.sid
  having count(1) = 1
  
  10、查询挂科超过两门(包括两门)的学生姓名和挂科数量
  select t.sname,count(1) from student as t inner join score as s on t.sid = s.student_id
  where num < 60
  GROUP BY t.sid
  having count(1) >= 2
  
  11、查询选修了所有课程的学生姓名
  select t.sname from student as t inner join score as s on t.sid = s.student_id
  GROUP BY t.sid 
  having count(1) = (select count(1) from course)
  12、查询李平老师教的课程的所有学生（id）的成绩记录
  select s.student_id,s.num from score as s inner join course as c on s.course_id = c.cid 
  inner join teacher as t on t.tid = c.teacher_id
  where tname = '李平老师'
   
  13、查询全部学生都选修了的课程号和课程名
  select c.cid,c.cname from score as s inner join course as c on c.cid = s.course_id
  GROUP BY course_id
  having count(1) = (select count(1) from student)
  
  14、查询每门课程被选修的次数(每门课有多少学生选择)
  select course_id,count(1) from score
  GROUP BY course_id
  
  15、查询只选修了一门课程的学生姓名和学号
  select t.sname,t.sid from student as t inner join score as s on t.sid = s.student_id
  GROUP BY t.sid having count(1) = 1
  
  16、查询所有学生考出的成绩并按从高到低排序（成绩去重:在需要去重字段前面加distinct）
  select distinct num  from score ORDER BY num desc
  
  17、查询平均成绩大于85的学生姓名和平均成绩
  
  select t.sname,avg(num) as 平均成绩 from student as t inner join score as s on t.sid = s.student_id
  GROUP BY t.sid having avg(num) > 85
  
  18、查询生物成绩不及格的学生姓名和对应生物分数
  select t.sname,num from student as t inner join score as s on t.sid = s.student_id
  inner join course as c on c.cid = s.course_id
  where cname = '生物' and num < 60
  
  19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
  select t.sname from student as t inner join score as s on t.sid = s.student_id
  inner join course as c on c.cid = s.course_id
  inner join teacher as tt on tt.tid = c.teacher_id
  where tname = '李平老师'
  GROUP BY t.sid 
  ORDER BY avg(num) desc
  LIMIT 1
  
  

视图

• 什么是视图？

  • mysql中的视图与数据表有很多相似的地方，视图是由若干个字段以及若干条记录构成，视图也可以作为select语句的数据源。
  • 视图中保存的仅仅是一条select语句，或者说保存的是视图的定义，并没有保存真正的数据。数据库表称为基本表或者基表，视图称为虚表（因为没有保存真正的数据）。

• 创建视图

• 	create view empView as select * from emp;
    
    	create view empView3 (name,age)  as select name,age from emp;	
    
  

• 查询视图

• 	select * from viewName
  

  
- 修改视图

- ```
  alter view viewName as select xxx

• 删除视图

• drop view viewName
  

• 视图的作用

  • 安全性：

    • 视图的安全性可以防止未授权用户查看特定的行或列，使有权限用户只能看到表中特定行的方法
    • 就是给具有不同用户提供不同的视图

  • 简单性：

    • 可以简化用户的操作，那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

  • 逻辑数据独立性：

    • 视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，程序一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来

      • 如果应用建立在数据库表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而使应用程序可以不动。