数据库学习

一，sql介绍

Sql

结构化查询语言（Structured Query Language）简称sql，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存储数据以及查询、更新和管理关系数据库系统。同时也是数据库脚本文件的拓展名。

Sql是专门为关系型数据库而设计出来的。

结构化查询语言是高级的非过程化的编程语言，允许用户在高层数据结构上工作，它不要求用户指定对数据的存放方法，也不需要用户了解具体的存储放方式，所以具有完全不同底层结构的不同数据库系统，可以使用相同的结构化查询语言座位数据输入与管理的接口。结构化查询语言语句可以嵌套，这使他具有强大的灵活性和功能。

结构

1. 数据查询语言(DQL：Date Query Language)：其语句，也被称为数据检索语句，用以从表中获取数据，确定数据怎样在程序中输出，保留字 select，是dql也是所有sql用的最多的动词，其他的dql常用的保留字有where、group by、order by和having等等。这些保留字常与select语句一起使用。
2. 数据操作语言（DML：date manipulate language）：其语句包括动词insert、update、delete。他们分别用于添加、修改、删除。
3. 事务控制语言（TCL）：它的语句能确保被DML语句影响的表所有行及时得到更新。包括commit 提交 save point 保存点、roll back 回滚。
4. 数据控制语言（DCL）：它的语句通过grant或者revoke实现权限控制，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用grant或者revoke控制对表单个列的访问。
5. 数据定义语言（DDL）：其语句也包含动词create、alter、drop。在数据库中创建新表或者修改、删除表，以及为表加入索引等。

二，数据库概念

数据库概念

数据库（database）是按照数据结构来组织、存储和管理建立在计算机存储设备上的仓库。

数据库：存储数据的仓库。

数据库的分类

网络数据库

网络数据库是指把数据库技术引入到计算机网络系统中，借助于网络技术将存储于数据库中的大量信息及时发布出去，而计算机网络借助于成熟的数据库技术对于网络中的各种数据进行有效管理，并实现用户与网络中的数据库及逆行实时动态数据交互。

层级数据库

层次结构模型实质上是一种有根节点的定向有序树，在数学中树被定义为一个无回的连通图。

关系型数据库

关系型数据库：是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

数据库的另外一种区分方式：基于存储介质区分

1. 关系型数据库：存储在磁盘中
2. 非关系型数据库：存储在内存中

关系型数据库基本概念

关系型数据库：是建立在关系模型基础上的数据库，借助于代数集合等数学概念和方法来处理数据库中的数据。现实世界中的各种实体之间的各种联系均用关系模型来标识。关系模型是由埃德加·科德与1970年首先提出来的，并配合科德十二定律，现如今虽然对此模型有一些批评意见，但它还是数据存储的传统标准。关系模型由关系数据结构、关系操作集合、关系完整性约束3部分组成。

关系数据结构：指的是数据以什么方式来存储，是一种二维表的形式存储，有行有列。

本质：二维表

姓名	年龄	身高	体重
张三	30	175	75
李四	40

关系操作集合：如何来关联和管理对应的存储数据，sql指令。

获取张三的年纪，已知张三姓名。

Select 年龄 from 二维表 where 姓名=张三

关系完整性约束：数据内部有对应的关联关系以及数据与数据之间也有对应的关联关系。

表内约束：对应的具体列只能放对应的数据，不能乱放。

表间约束：自然界各实体都有一个对应的关联关系（外键）

典型的关系型数据库

 Oracle、mysql、microsoft sql server、microsoft access、sqlLite、DB2

小型关系型数据库：sqlLite、microsoft access

中型关系型数据库：mysql、microsoft sql server

大型关系型数据库：Oracle、DB2

Mysql当前也是属于oracle的

Mysql服务端架构

Mysql服务端架构由以下几层构成：

1. 数据库管理系统（最外层）：DBMS，专门管理服务器端所有内容
2. 数据库（第二层）DB专门用于存储数据的仓库，可以有多个。
3. 二位数据表（第三层）table ：专门用于存储具体实体数据
4. 字段（第四层）：field具体存储某种类型的数据（实际存储单元）

Row：行

Column：列 field

Mysql基本介绍

Mysql是一个关系型数据库，由瑞典mysql ab公司开发，目前属于oracle旗下产品。

Mysql是最流行的关系型数据库管理系统之一，在web应用方面，mysql是最好的RDBMS应用软件。

AB公司被sun公司收购，又被Oracle收购。

Mysql是开源免费的数据库产品。

启动和停止mysql服务

Mysql是一种c/s结构，客户端和服务器。

服务对应的软件：mysql.exe

登录和登出

通过客户端与服务器进行逆行连接认证，就可以操作。

通常：服务端与客户端不在同一台电脑上。

登录

1. 找到mysql.exe（通过cmd控制台:如果在安装的时候指定了mysql.exe所在的路径为观景变量，就可以直接访问，如果没有那么就必须进入到mysql.exe所在路径）
2.  输入对应的服务器地址 -h ip地址
3. 输入服务器中mysql的监听端口 ： -P port 3306
4. 输入用户名 -u 密码 -p

连接认证的基础语法:mysql -h 主机地址 -P 端口 -u -p

通常端口都可以默认

登出

断开与服务器的连接，直接关闭不推荐，通常mysql提供的服务器连接数量有限。

Exit

设置客户端所有字符集

如果通过cmd下的控制台访问，跟我在navicat中访问的结果不一致，原因是两个连接相当于两个客户端，这边修改了字符集编码格式，在另外一个客户端无法识别，原因是两个客户端的编码不同。

Set names utf8;  一次性打通三层关系的字符集变得一致。

查看变量名包含char的变量

show VARIABLES like '%char%';

character_set_client：客户端传入给服务器端

character_set_connection： 客户端与服务器端之间的连接

character_set_server：  服务端返回数据给客户端

三，基本操作

1.数据库操作

数据库是数据存储的最外层。

创建数据库

create database 数据库名字(库选项)

选择数据库

为什么要选择数据库？因为数据是存储到数据表，表存储在数据库下，如果要操作数据，那么必须要进入到对应的数据库。

use 数据库名字;

修改数据库

修改数据库的字符集（库选项）：字符集和校对集

alter database 数据库名字 charset 字符集;

另外是否可以修改数据库名字？不可以。

删除数据库

drop database 数据库名字;

2.数据表操作

创建数据表

普通创建表 基本语法：

create table 表名(字段1 字段类型 字段属性，字段2 字段类型 字段属性，….)表选项

create table classes(

name VARCHAR(10)

)charset utf8;

表必须存到对应的数据库上，没有办法单独存在

1. 在数据表名字前面加上数据库名字，用.连接，数据库.数据表
2. 在创建数据表之前，先进入到该数据库

表选项：

  Engine：存储引擎，mysql提供的具体存储数据方式，默认innodb（5.5之前默认myisam）

  Charset：字符集，只针对当前自己表有效（级别比库选项要高）

  Collate：校对集

复制已有表结构

从已经存在的表复制一分，只复制结构，如果表中有数据，数据不复制

create table 新表名 like 表名;

 //只要使用数据库.表名就可以在任意数据库下访问该表

显示数据表

Show tables;

匹配显示表

Show tables like ‘匹配格式’;

显示表结构

本质：显示表中所包含的字段信息（名字、类型、属性）语法：

describe 表名;

DESC 表名;

Show columns from 表名;

三，列属性

列属性又称之为字段属性，在mysql中一共有6个属性，null，默认值，列描述，主键，唯一键和自动增长。

1.Null

Null属性：代表字段为空，如果对应的值为yes，表示该字段可以为null

注意：设计表的时候，尽量不要让字段为空。

Mysql的记录长度为65535个字节，如果一个表中有字段允许为null，那么系统就会设计保留一个字节来存储null，最终有效存储长度为65534个字节。

2.默认值

Default：当字段被设计之后，如果允许默认条件下，用户不进行数据的插入，那么就可以使用事先准备好的数据来填充，通常填充的为null。

Default关键字的另外一层使用，显示告知字段使用默认值，在进行数据插入的时候，对字段直接使用default。

create table my_default(

name VARCHAR(10) not null,

age int DEFAULT 18

)

desc my_default;

--测试不给当前字段值

insert into my_default (name) VALUES ('jack');

select * from my_default;

insert into my_default VALUES ('rose',DEFAULT);

3.列描述

列描述：comment 是专门用于给开发人员维护的一个注释说明

基本语法： comment 字段描述;

查看comment：必须通过查看创建语句

Show create table 表名;

create table my_comment(

username VARCHAR(10) not null COMMENT '当前是用户名，不能为空',

password VARCHAR(10) not null comment '当前是密码，不能为空'

)

select * from my_comment;

show create table my_comment;

4.主键

顾名思义：主要的键。Primary key 在一张表中，有且仅有一个字段，里面的值具有唯一性与其他字段没有任何冲突，同时不允许为空，保证数据的唯一性。

创建主键三种方式：

1.随表创建

直接在需要当作主键的字段之后，增加 primary key，增加primary key属性确定主键，在字段后增加主键属性。

create table my_pri1(

username VARCHAR(20) PRIMARY key

)

1. 1. 2.在所有字段之后增加primary key选项，

primary key（字段信息）

create table my_pri2(

username VARCHAR(20),

PRIMARY key(username)

)

1. 3.表后增加

基本语法；alter table 表名 add primary key(字段)

create table my_pri3(

username VARCHAR(10)

)

alter TABLE my_pri3 add PRIMARY key(username);

查看主键

1. 通过查看表结构desc 表名，pri主键，主键描述非空且唯一。
2. 查看表的创建语句 show create table 表名;

删除主键

  基本语法：

alter table drop PRIMARY key;

  保留了非空条件。

复合主键

  将多个字段看成一个字段，作为主键，同时满足主键约束。

  案例：有一张学生选修课表，一个学生可以选择多个课程，一个课程可以由多个学生来选择，但是一个学生在一个选修课程中只有一个成绩。

  主键约束：主键一旦增加，那么对对应的字段有要求

1. 当前字段对应的数据不能为空
2. 当前字段对应的数据不能有重复

create table my_score(

student_no char(10),

course_no char(10),

score int not null,

PRIMARY key(student_no,course_no)

)

select * from my_score;

insert into my_score VALUES ('001','course001',100);

insert into my_score VALUES ('002','course001',90);

insert into my_score VALUES ('001','course002',95);

insert into my_score VALUES ('002','course001',98);  --报错 主键冲突

主键分类

  主键分类采用的是主键对应的字段的业务意义

   业务主键：主键所在的字段，具有业务意义

   逻辑主键：主键所在的字段，没有业务意义

5.唯一键

唯一键：unique key ，用来保证对应的字段中的数据唯一性的。

主键也可以用来保证数据的唯一性，但是一张表中只能有一个主键，多个字段都有需求，就要用到唯一键。

  唯一键在一张表中可以存在多个

  唯一键允许字段为null，null可以有多个，null不参与比较。

创建唯一键与创建主键非常类似：

1. 直接在表字段之后使用unique key(字段)
2. 在创建完表之后也可以增加唯一键

删除唯一键，有多个唯一键，根据唯一键名字删除

删除基本语法

 alter table 表名 drop index 唯一键名字;

Index关键字:索引，唯一键是索引的一种。

6.自动增长

 自动增长：auto_increment,当给定某个字段该属性之后，该列的数据在没有提供确定数据的时候，系统会根据之前已经存在的数据进行自动增加后，填充数据。

通常应用于逻辑主键。

原理：

1. 在系统中有维护一组数据，用来保存当前使用了自动增长属性的字段，记录当前对应数据值，再给定一个指定的步长。
2. 当用户进行数据插入的时候，如果没有给定值，系统在原始值上加上步长变成新的数据进行插入
3. 自动增长的触发：给定的属性字段没有提供值
4. 自动增长只适用于数值。

基本语法：在字段之后增加一个属性：auto_increment

create table my_auto(

id int PRIMARY key auto_increment,

name VARCHAR(10) not null comment '用户名',

pass VARCHAR(10) not null comment '密码'

)

insert into my_auto VALUES (null,'jack','123');

insert into my_auto VALUES (null,'rose','123');

insert into my_auto VALUES (null,'liuneng','123');

select * from my_auto;

delete from my_auto where id=2;

四，列类型

1.整型

Tinyint

迷你整型，系统采用一个字节来保存的整型，最大的表示范围是0-255

Smallint

小整型，系统采用2个字节来保存的整型，能表示0-65535之间

Mediumint

中整型，系统采用3个字节来保存的整型。

Int

标准整型，采用4个字节来保存的数据

Bigint

大整型，采用8个字节来报从的数据。

2.无符号设定

无符号：表示存储的数据在当前字段中，没有负数，只有正数。

基本语法：在类型之后加上一个unsigned

3.显示长度

显示长度：指数据（整型）在数据显示的时候到底可以显示多少位

Tinyint(3)：表示最长可以显示3位，unsigned说明只是正数，0-255永远不会超过3位。

Tinyint(4):  表示最长可以显示4位 -127---128

显示长度只是代表了数据是否可以达到指定长度，但是不会自动满足到指定长度，如果想要显示数据的时候，保持最高位显示长度，那么还需要给字段增加一个zerofill属性才行。

Zerofill：从左侧开始填充0，从左侧不会改变数值大小，所以负数的时候就不能使用zerofill，一旦使用了zerofill就相当于确定该字段位unsigned。

如果不足3位，那么填充到3位，如果本身已经够了或者超出，那么就不再填充。

显示长度可以自己设定，超出长度（但是没有超出范围）不会影响，只会对不够长度的进行补充。

create table my_int(

int_1 TINYINT,

int_2 smallint,

int_3 MEDIUMINT,

int_4 int,

int_5 BIGINT

)

-- 插入数据

insert into my_int VALUES (10,1000,100000,1000000,1000000000);

select * from my_int;

-- 插入超出范围的值

insert into my_int VALUES (255,255,255,255,255);

-- 错误原因：并不是说tinyint没有那么大的空间，而是因为mysql默认的为整型增加了负数

-- 实际的表示范围是-128---127

insert into my_int VALUES (-128,255,255,255,255);



-- 修改表结构增加一个字段

alter table my_int add int_6 TINYINT UNSIGNED FIRST;

insert into my_int VALUES (255,-128,255,255,255,255);

-- 实际应用中，应该根据对应的数值范围去选定对应的整型类型，通常使用的比较多的是

tinyint和int



alter table my_int add int_7 TINYINT ZEROFILL FIRST;

alter table my_int add int_8 TINYINT(2) ZEROFILL FIRST;

-- 超出显示长度

insert into my_int VALUES (100,1,1,1,1,1,1,1);

insert into my_int VALUES (1,1,1,1,1,1,1,1);

4.小数类型

 专门用来存储小数

 在mysql中，将小数类型又分为两类，浮点型和定点型。

5.浮点型

  浮点型又称为精度类型，是一种有可能丢失精度的数据类型，数据有可能不那么准确，尤其是在超出范围的时候，不是大小范围，而是精度范围。

浮点型之所以能够存储较大的数值，利用存储数据的位来存储指数。

整型：所有位都为1

1

1

1

1

1

1

1

1

浮点型：有的部分用于存储数据，有的部分用来存储指数

1

1

1

1

1

1

1

1

前三位转换成十进制用作10的指数，10^7数值

Float

又称为单精度类型，系统提供4个字节来存储数据，但是能表示的数据范围要比整型大很多，大概是10^38此方，只能保证大概7个左右的精度，如果数据在7位以内，那么基本准确，但是超过了7位，那么有可能不准确。

基本语法：

   Float: 表示不指定小数位的浮点数

   Folat(M,D); 表示一共存储M个有效数字，其中小数部分占D位

   Float(10,2) 表示整数部分8位，小数部分2位

浮点数的应用：通常是用来保存一些数量特别大，大到可以不用那么精确。

create table my_float(

f1 float,

f2 float(10,2)

)

insert into my_float VALUES (123.123,12345678.90);

select * from my_float;

-- 精度大概在7位左右

-- 如果数据丢失精度，那么四舍五入进行舍弃。

insert into my_float VALUES (123.1234567,123456789.00);



-- 数据长度刚好满足条件，但是会超出精度

insert into my_float VALUES (123.1234567,99999999.99);

/*说明：用户不能插入数据直接超过指定的整数部分长度，但是如果是系统自动进位导致，

系统是可以承担的

浮点数可以采用科学计数法
*/

insert into my_float VALUES (123.123,10e5);

Double

双进度类型：系统采用8个字节来存储数据，表示的范围更大，大概是10^308次方，但是精度也只有15位左右。

作业：自行测试double精度范围等。

6.定点型

Decimal：定点数，系统自动根据存储的数据来分配存储空间，每大概9个数就会分配4个字节来存储，同时小数部分和整数部分是分开的。

基本语法：

Decimal(M,D)M表示总长度，最大值不能超过65，D代表小数部分，最长不能超过30

定点数的应用：如果涉及到钱的时候，使用定点数。

create table my_decimal(

f1 float(10,2),

d1 decimal(10,2)

)

insert into my_decimal VALUES (12345678.90,12345678.90);

select * from my_decimal;

--插入最大值

insert into my_decimal VALUES (99999999.99,99999999.99);

-- 尝试定点数四舍五入

insert into my_decimal VALUES (99999999.99,99999999.999);

7.时间日期类型

Date

日期类型：系统使用3个字节来存储数据，对应的格式位 yyyy-MM-dd 能表示的范围是从1000-01-01-------9999-12-31 初始值位0000-00-00

Time

时间类型：系统采用3个字节来存储，能够表示某个指定的时间也能表示时间段。对应的格式是 HH:mm:ss 在mysql中time类型能够表示的时间范围要大的多，能表示从-838：59：59----838：59：59

Datetime

日期时间类型：就是将前面的date和time的结合，表示的时间，使用8个字节来存储数据

格式位yyyy-MM-dd HH:mm:ss，能表示区间位1000-01-01 00：00：00---9999-12-31 23：59：59，可以为0值，0000-00-00 00：00：00

Timestamp

时间戳类型：mysql中的时间戳只是表示从格林威治时间开始，但是其格式依然是 yyyy-MM-dd HH:mm:ss

Year

年类型：占用一个字节来保存，能表示1900-2155年，但是year有两种数据插入方式0-99或者是4位数的年分。

create table my_date(

d1 date,

d2 time,

d3 datetime,

d4 TIMESTAMP,

d5 year

)

select * from my_date;

desc my_date;

/*时间戳类型不能为空，有默认值为当前时间对应的时间戳

当数据被更新时，这个字段自动更新为当前最新时间
*/

insert into my_date VALUES ('1900-01-01','12:12:12','1900-01-01 12:12:12',null,69);

insert into my_date VALUES ('1900-01-01','12:12:12','1900-01-01 12:12:12',null,70);

insert into my_date VALUES ('1900-01-01','12:12:12','1900-01-01 12:12:12',null,2022);

year的特殊性

可以采用2位数的数据插入也可以采用4位数的年分插入，

year进行2位数插入的时候，有一个区间划分，临界点是69和70

当输入69一下的数字，那么系统年份为20+

当输入70以上的数字，那么系统年分为19+

time类型特殊性

time本身是用来表示时间点，但是也可以表示时间段

insert into my_date VALUES ('1900-01-01','512:12:12','1900-01-01 12:12:12',null,69);

在进行时间类型录入的时候还可以使用一个简单的日期代替，在时间格式之前加一个空格，

然后指定一个数字（可以是负数），系统会自动将该数字转换成天数 *24小时再加上后面的时间

insert into my_date VALUES ('1900-01-01','5 12:12:12','1900-01-01 12:12:12',null,69);

8.字符串

Char

定长字符类型：指定长度之后，系统一定会分配指定的空间用于存储。

基本语法：char(L) L代表字符数，（中文与英文一样）L的长度范围是0-255

Varchar

变长字符类型：指定长度后，系统会根据实际存储的数据来计算长度，分配合适的长度，数据没有超出长度。

基本语法：varchar(L) L代表长度，L理论长度值为0-65535

因为varchar要记录数据长度，系统才会根据长度自动分配空间，所以每个varchar数据产生后系统都会再数据后面增加1-2个字节的额外开销，用来保存数据所占空间长度，如果数据本身小于127个字符额外开销1个字节，如果大于127，额外开销2个字节。

Char和varchar数据存储对比：utf8，一个字符都会只用3个字节。

存储数据	Char(2)	Varchar(2)	Char占字节	Varchar占字节
A	A	A	2*3=6	1*3+1=4
AB	AB	AB	2*3=6	2*3+1=7

Char和varchar的区别：

  Char一定会使用指定的空间，varchar是根据数据来定空间

  Char的查询效率比varchar高，varchar是需要通过后面的记录数来计算的。

  如果确定数据一定是占用指定长度，要使用char，如果不确定使用varchar

  如果数据长度超过255个字符了，不论是否固定长度，都会使用text，不会再使用char或者是varchar了。

Text

文本类型：mysql中提供了两种文本类型

Text：存储普通的字符文本。

Blob：存储二进制文本（图片、文件等等）一般我们不会使用blob来存文件本身，通常是存放url

Text：系统中提供了4种text

Tinytext：系统使用一个字节来保存，实际能够存储的数据为2^8+1

Text: 系统使用2个字节来保存，实际能够存储的数据为2^16+2

mediumText: 系统使用3个字节来保存，实际能够存储的数据为2^24+3

longText: 系统使用4个字节来保存，实际能够存储的数据为2^32+4

注意：

1. 在选择对应的存储类型的时候，不用刻意去选择text的类型，系统自动会根据存储的数据长度来选择合适的文本类型。
2. 在选择字符存储的时候，如果超过255个字符，那么一定选择text，效率高。

9.Enum（枚举）

枚举类型：在数据插入之前，先设定几个项，这几个项就是可能最终出现的数据结果。

如果确定某个字段的数据只有那么几个值，例如男、女、保密，系统就刻意在设定字段的时候规定当前字段只能存放固定的几个之。

语法： enum(数据值1，数据值2…….)

系统使用了1-2个字节来存储枚举数据，通过计算enum列举的具体值来选择实际的存储空间，如果数据列表在255以内，那么一个字节就够，如果超过255但是小于65535那么系统采用2个字节保存。

五，表关系

1.一对一

一张表中的一条记录与另外一张表中最多有一条明确的关系，通常此设计方案保证两张表使用相同的主键即可。

学生表

SID	姓名	年龄	性别	籍贯	住址	婚否

表的使用过程种，常用的信息是会经常查询的，而不常用的信息偶尔才会用到

解决方案：将表拆分，常见的放一张表，不常见的放一张表。

常用表

SID	姓名	年龄	性别

不常用表

SID	籍贯	住址	婚否

2.一对多

通常也叫做多对一的关系，看你从什么角度去看，通常一对多的关系设计方案种，在多的关系表种，去维护一的字段，这个字段就是一关系的主键。

母亲表

母亲ID	姓名	年龄	身高

孩子表

孩子ID	姓名	年龄	身高	母亲ID

是否可以在母亲表中增加孩子的ID呢？

理论上可以，但是不方便

3.多对多

多对多：一张表种的一条记录可以在另外一张表种匹配多条记录，反过来也一样。

多对多的关系如果按照多对一的关系维护，就会出现一个字段种有多个其他表的主键，在访问的时候就会带来不便。

既然通过两张表自己增加字段解决不了问题，那么就通过第三张表来解决。

师生关系：

1. 一个老师教过多个班级的学生
2. 一个学生会听过多个老师的课程

首先要有2个实体，学生表和老师表

老师表

TID	Name	Age	Gender
T1	A	30	男
T2	B	20	女

学生表

SID	NAME	AGE	GENDER
S1	AA	15	男
S2	BB	16	女
S3	CC	15	女
S4	DD	15	女

从中间设计一张表，维护两张表对应的关系，每一种关系都包含。

中间表

Id	TID	SID
1	T1	S1
2	T2	S2
3	T2	S1

多对多的解决方案：增加中间表，让中间表与对应的其他表形成2个多对一的关系，多对一的解决方案是在多表中增加一表对应的主键字段

 六，三大范式

1. 字段是原子性的，不可再分
2. 有主键，非主键字段依赖主键，确保一个表只能说明一个事务
3. 非主键字段不能互相依赖，每列都与主键有直接关系，不存在传递的依赖

第一范式：不符合的例子

   表：字段1，字段2（字段2.1，字段2.2），字段3.。。。。

第二范式：不符合的例子

   表：学号、课程号、姓名、学分

这个表说明了两个事物，学生信息、课程信息，由于非主键字段必须依赖主键，这里学分依赖课程号，姓名依赖学号，所以不符合二范式。

解决：课程号变内容，或者增加课程表。或者增加逻辑主键。

第三范式：不符合第三范式的例子

表：学号、姓名、年龄、所在学院、学院地点、学院联系电话

存在依赖传递 学号==》所在学院=》学院地点、学院电话

解决方案：新增学院表

七，运算符

1.算术运算符

+，-，*，/，%

基本算术运算，通常不在条件中使用，而是用于结果（select字段）

除法的运算用浮点表示，除数为0，系统返回null

拿null进行任何算术运算，结果都为null

create table ysf(

i1 int,

i2 int,

i3 int,

i4 int

)

insert into ysf VALUES (10,100,0,null);

select i1+i2,i1-i2,i1*i2,i1/i3,i2/i1,i1+i4 from ysf;

2.比较运算符

>,<,>=,<=,<>

通常用来在条件中限定结果

查询不同班级20岁以上的学生

Select class_id,count(*) from my_student where age>=20 group by class_id;

<=>比较

特殊应用：在字段结果中进行比较 select没有规定必须要有数据源

Select ‘1’ <=>1,0.02<=>0;

Mysql中数据会先转换成相同类型，再进行比较。

在mysql中没有boolean，用0，1代替，0代表false，1代表true

select class_id,count(*) from my_student where age BETWEEN 20 and 30 GROUP BY class_id;

between and 是闭区间

条件1必须小于条件2，反过来不可以。

3.逻辑运算符

 And   Or  not

select class_id,count(*) from my_student where age>20 and age<30 GROUP BY class_id;


select class_id,count(*) from my_student where age<20 or age>30 GROUP BY class_id;


select * from my_student where not age =18;

4.in运算符

in：在sql中用来代替=，当结果不是一个值，而是一个结果集的时候。

基本语法：in (结果1，结果2……)

只要当前条件在结果集中出现，那么就成立

not int (字段) 不包含某个条件 

select * from my_student where class_id in (1,2);

5.like运算符

用来进行模糊查找

两个占位符

%    ---- 代表多个字符

_     ------代表一个字符

select * from my_student where stu_name LIKE 'q%';

select * from my_student where stu_name LIKE 'q__';

八，高级操作

基础sql操作（单表的增删改查）

查询语法：select (字段列表) from 表名;

插入语法：insert into 表名 (字段列表) values (值列表)

修改语法：update set 表名 字段1=新值，字段2=新值…..where 条件过滤

删除语法：delete from 表名  where 条件过滤

1.多数据插入

只写一次insert命令，但是可以直接插入多条记录

基本语法：insert into 表名 (字段列表) values (值列表1),(值列表2)……..

create table t1(

name VARCHAR(10)

)

insert into t1 VALUES ('a'),('b'),('c');

select * from t1;

2.主键冲突

主键冲突：在有的表中，使用的是业务主键，但是往往在进行数据插入的时候又不能确定表中是否已经存在对应的主键。

create table ss(

stu_id VARCHAR(10) PRIMARY key,

stu_name VARCHAR(10)

)

insert into ss VALUES ('001','jack'),('002','rose'),('003','zhaosi');

解决方案：

1. 主键冲突更新：类似于插入语法，如果插入的过程中主键冲突，那么采用更新方法

Insert into 表名 (字段列表) values on duplicate key update 字段=新值;

insert into ss VALUES ('004','liuneng') on DUPLICATE key

 update stu_id='006';

1. 主键冲突替换：当主键冲突之后，干掉原来的数据，重新插入

Replace into 表名 values ();

REPLACE into ss VALUES ('001','dajiao');

3.蠕虫复制

蠕虫复制：一分为二，成倍增加（指数级别），从已有的数据中获取数据并且将获取到的数据插入到新的数据表中。

基本语法：insert into 表名 (字段列表) select 字段列表 from 表2；

insert into t1 select name from t1;

可以在短期内快速增加表的数据量，测试表的压力，需要注意主键冲突。

4.更新高级操作

1. 在更新数据的时候，特别要注意一定要有限定条件，不做批量更新

Update 表名 set 字段=新值, 字段2=新值2…. Where 限定条件

1. 如果没有条件，那么就是全表更新，但是可以使用limit语句来限制更新的数量

update t1 set name = 'q' where name='a' limit 3;

5.删除高级操作

1. 在删除数据的时候（尽量不删），应该使用where条件进行限制
2. 删除数据的时候可以使用limit进行限制具体数量

Delete删除数据的时候，无法重置auto_increment

DELETE FROM 表名 (WHERE 条件) ;

6.高级查询

完整的查询指令

Select 字段名 from 数据源 where 条件 group by 分组 having 条件 order by 排序 limit 限制；

Select选项:系统该如何对待查询结果

All：默认的，表示保存所有记录

DISTINCT：去重，去掉重复记录，只保留一条，所有的字段都相同

数据源

 From是为前面的查询提供数据源，数据源只要求是一个符合二维表结果的数据库结构即可。

单表数据

 From 表名;

多表数据

从多张表中获取数据

基本语法：from 表1，表2；

得到的结果：两张表记录数相乘，字段数拼接（相加）

本质：从第一张表中取出一条数据，去拼凑第二张表的所有记录，保留了所有记录。

得到的这个结果在数学上有个专业的说法，叫笛卡尔积，这个结果除了给数据库造成压力，没有其他作用，尽量避免出现笛卡尔积。

动态数据

From后面跟的不是一个实体表，而是一个查询出来的二维结果表（子查询）

基本语法：from （select 字段列表 from 表名）as 别名；

select int_1 from

(select int_7,int_1 from my_int ) as int_my

7.Where子句

用来从数据表中获取数据的时候，进行过滤，条件筛选。

数据获取原理：针对表去对应的磁盘处获取所有记录（一条条），where的左右就是在拿到一条结果后就开始判断，判断是否符合条件，如果符合条件就保存下来，如果不符合就直接舍弃掉（没有放入内存中）

Where是通过运算符进行结果比较来判断数据的。

8.Group by

表示分组的含义，根据指定的字段将数据进行分组，目的是为了统计。

分组统计

基本语法：group by 字段名

select * from my_student GROUP BY class_id;

Group by是为了分组统计，如果只是想查看数据，group by没有意义，group by将数据按照指定的字段分组之后，之后保留每组字段的第一条记录。

统计需要使用统计函数（聚合函数）

Count()：计数函数，统计每组中的数量，如果统计目标是字段，不统计为空的字段，如果为*那么表示统计记录数。

Group by 后面的字段在前面的select查询的字段列表中一定要存在

使用聚合函数，按照每班级统计每班人数，最大年龄，最矮身高，平均年龄。

select class_id,count(*),max(age),min(heigh),avg(age) from my_student GROUP BY class_id;

count()是MySQL中用来统计表中记录的一个函数，返回条件的行数

用法：
count(*)
返回表中的记录数(包括所有列)，相当于统计表的行数(不会忽略列值为NULL的记录)

count(1)
忽略所有列，1表示一个固定值，也可以用count(2)、count(3)代替(不会忽略列值为NULL的记录)

count(列名)
返回列名指定列的记录数，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内

avg() 求平均值

sum() 求和

max() 求最大值

min() 求最小值

group concat（字段名）：字符串拼接，不是为了统计，是为了将分组中指定的字段进行合并，

select class_id,GROUP_CONCAT(stu_name),count(*),max(age),min(heigh),avg(age) from my_student GROUP BY class_id;

多分组

将数据按照某个字段分组之后，对已经分组的数据进行 再次分组

基本语法：group by 字段1,字段2 先按照字段1进行分组，再根据字段2进行分组

select class_id,GROUP_CONCAT(stu_name),count(*),sex from my_student GROUP BY class_id,sex;

分组排序

Mysql中，分组默认有排序功能，按照分组字段进行排序，默认是升序。

基本语法：group by 字段 asc 字段 desc；

select class_id,GROUP_CONCAT(stu_name),count(*),sex from my_student GROUP BY class_id,sex desc;

9.回溯统计

当分组进行多分组之后，往上统计的过程，需要进行层层上报，将这种层层上报的过程称之为回溯统计。每次分组都会向上统计，过程会产生一次新的统计数据，而且当前的数据对应的分组字段为null。

基本语法： group by 字段 asc/desc with rollup

select class_id,count(*) from my_student GROUP BY class_id;

select class_id,count(*) from my_student GROUP BY class_id with ROLLUP;

10.having

having本质跟where一样，都是用来数据条件筛选的，where不能使用聚合函数，聚合函数是在使用分组之后，having在分组之后是应用字段别名 where是从表中取数据，别名是在数据进入内存之后才有的

having 在group by之后，可以针对分组进行统计，where不行

查询班级人数大于等于4

select stu_id,count(*) as number from my_student where number>=4 GROUP BY class_id;

错误分析：number是 存在于内存中，where无法查询

select stu_id,count(*) as number,class_id from my_student GROUP BY class_id HAVING number>=4;

11.order by

排序：根据校对集规则 对数据进行排序

基本语法： order by 字段 asc/desc

可以像group by一样进行多字段排序，先按照第一个字段排序，再按照第二个排序

根据班级升序，学生按照身高降序排序

select * from my_student ORDER BY class_id,heigh desc;

12.limit

限制子句，主要限制记录数量获取

记录数限制：纯粹的限制获取数量，从第一到指定的数量

基本语法：limit 数量

Limit通常在查询的时候如果限定为1条记录的时候比较多，有时候获取多余的记录数并不能解决业务问题。

分页：

 利用limit来限制获取指定区间的数据，分页技术的出现缓解了数据库、网络传输的压力

基本语法： limit 0,2 mysql中记录数量从0开始， offset偏移量，length具体获得多少条数