mysql

MySQL

1、数据库相关概念

1.1 狭义数据

描述事物的一种符号

1.2 广义数据

可以有多种形式：数字、文字、图像等

数据特点：数据与其语义密不可分

1.3 数据库

存放数据的仓库

1.4 数据库与数据表关系

数据表包含行和列

1.5 主键

主键是能确定一条记录的唯一标识

1.6 外键

外键用于与另一张表的关联

1.7 数据模型

数据模型组成：数据结构+数据操作+完整性约束

数据结构

数据在数据库中的存储形式

数据操作

数据更新(插入、删除、修改)和查询

数据完整性约束

是一种规则，用来限定数据库的状态，以保证数据正确、有效、相容。

实体完整型：规则一：表中不能存在完全相同的记录

​ 规则二：每条记录都要具有一个不重复的主键

​ 规则三：主键不能为空

参照完整性：当一个表引用另一张表中的某一些数据时，相关字段的值要保持一致

域完整性：向表中添加数据时，要限制向表中输入值的范围，保证输入有效

1.8 概念模型

实体

可以是具体的人、事 用矩形表示

属性

实体所具有的的某一个特性 用椭圆表示

联系

1、实体内部联系：各属性之间的联系

2、实体之间联系：不同实体集之间的联系

联系用菱形表示，框内写明联系名[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

联系种类

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传

联系属性：

实体集

同一类型实体的集合称为实体集。

1.9 E-R 方法（E-R模型）

将实体、属性、联系的表示方法整合起来，用来描述现实世界。

一对一

一对多

多对多

多个实体之间的联系

三个实体之间一对多联系

三个实体之间一对多联系

综合案例

用 E-R 图表示某个工厂物资管理的概念模型

步骤一：确定实体及他们的属性

步骤二：确定实体之间的联系

步骤三：画 E-R 图

1.10 常用数据模型

层次模型：数据之间存在着箱像树一样的层级关系

网状模型：数据之间存在着像网一样的关系

关系模型：在关系模型中，称表格为关系。

关系模型（一个表格对应一个关系）

2、关系运算

常用的关系操作（选择、投影、连接、除、并、交、差、笛卡尔积）

2.1 并

用来合并两个或多个相类似的集合，删除重复项

2.2 差

用来查找存在于某一集合，但不存在于另一集合的元素 R - S

2.3 交

用来获取两个或多个不同集合中的共同元素

2.4 笛卡尔积

是一种可以将两个表格中的所有行排列组合的方法

2.5 选择

选择又称限制，记为 σ ,它是调取表中某行(多行)的运算

2.6 投影

是调取表中某一列的运算，运算符 π

2.7 除

除就是从 ‘ 被除表格 ’ 中调取 ’ 除表格 ‘中包含的所有行，然后在从中去掉这些行。

2.8 连接

内连接

外连接

左外连接

是把左边的表的元素全部选出来

右外连接

是把右边的表的元素全部选出来

全外连接

是把左右两个表的数据全部取出来，不管是否匹配

3、基础查询

3.1 什么是SQL?

是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统

3.2 SQL特点

1. SQL不是某个特定数据库专有的语言
2. 语言简洁，易学易用
   3.

3.3 SQL语言分类

DML:数据操作语言(insert、delete、update、select)

DCL:数据控制语言(grant授权、revoke撤回权限)

DDL:数据定义语言(create、drop、alter)

3.4 增加

INSERT INTO 表名称 VALUES (值1, 值2,....)

3.5 删除

DELETE FROM 表名称 WHERE 列名称 = 值

3.6 查找

SELECT 列名称 FROM 表名称

3.7 修改

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

3.8 查看列

select 完整语法：

select 列名

from 表名 或 试图

where 查询条件

group by 分组表达式

having 分组条件

order by 列名 [ASC|DESC]

limit

查看特定列

select 姓名 from student；

查看多个列

select 姓名，性别 from student；

使用计算列

select sum(字段名) from 表名；

select count(字段名) from 表名；

select avg(字段名) from 表名；

select max(字段名) from 表名；

select min(字段名) from 表名；

使用 distinct 关键字

select distinct 字段名 from 表名；

使用 where 子句

select * from 表名 where 条件表达式；

select 姓名，性别，班级 from student where year（出生日期）> 1990;

使用模糊查询

select 字段名 from 表名 where like 字段名 ‘刘%’;

使用 order by 排序

order by 列名 asc

order by 列名 desc

对两个字段进行排序：

order by A,B; 这个时候都是默认按升序排序

order by A desc, B 这时候A降序，B升序

order by A,B desc 这时候A升序，B降序

order by A desc ，B desc 如果都用降序，必须是两个desc

对空值进行排序

升序排序，默认情况下，Null 值排后面

降序排序，默认情况下，Null 值排在前面

使用 Top 关键字

1、返回确定数目的记录个数

select top n 列名 from 表名

top n  返回最前面的 n 行    top 5 就是返回结果集中前5条记录

2、返回结果集中指定百分比的记录数

select top n percent 列名 from 表名；

top n percent   用百分比表示返回的行数  top 5 percent  返回结果前5%的记录数

案例： 查询年龄大于23岁的前20名学生信息
select top 20 * from student where year（出生日期）> 23;

3、top 结合 order by

查询年龄较大的前20名学生的学生信息
select top 20 * from student order by age DESC;

4、MySQL函数

数学函数 abs()、max()、min()…
字符函数 left()、right()、substring()…
日期与时间函数 day()、month()、year()…
系统函数 user_name()、db_name()…

4.1 数学函数

绝对值函数
案例： 计算 -10 绝对值

select abs(-10);

最大值函数

select max(price)  from  product;

最小值函数

select min(price)  from  product;

求和函数

select sum(price)  from  product;

平均值函数

select avg(price)  from  product;

向上取整函数
函数公式：ceiling（数值表达式）
案例：计算大于或等于1.6的最小整数并返回

select ceiling（1.6）;

向下取整函数
函数公式：floor（数值表达式）
案例：计算小于或等于7.2的最大整数并返回

select floor（7.2）;

四舍五入函数
函数公式：round（参数一，参数二）
根据参数二指定精度将参数一进行四舍五入，参数二可以是0

select round（3.14159）;       =》 3
select round（3.64159）;       =》 4
select round（3.64159,2）;       =》 3.64

幂运算函数
函数公式：power（x，y）

select power（2,3）;         =》8

求圆周率函数
函数公式：pi（）

select  pi();         =》3.14159......

平方根函数
函数公式：sqrt（数值）

select sqrt（4）;         =》2

指数函数
函数公式：exp（数值）= e∧（数值）

select exp（10）;        

4.2 字符函数

len() 函数
函数公式：len（字符串）
字符串可以是汉字、数字、字母。空格也作为一个字符

select len（“中华人民共和国”）;         =》7
select len（“ab cd”）;         =》5

left() 函数
函数公式：left（字符串,长度n）
字符串可以是汉字、数字、字母。空格也作为一个字符

select left（“abcd”,2）;         =》ab

right() 函数
函数公式：left（字符串,长度n）
字符串可以是汉字、数字、字母。空格也作为一个字符

select right（“abcd”,2）;         =》cd

substring() 函数
函数公式：substring（字符串,开始，长度）
字符串可以是汉字、数字、字母。空格也作为一个字符

select substring（“abcd”,2,2）;         =》bc

upper() 函数
函数公式：upper（字符串）
字符串可以是汉字、数字、字母。空格也作为一个字符

select upper（“abcd”）;         =》ABCD

lower() 函数
函数公式：upper（字符串）
字符串可以是汉字、数字、字母。空格也作为一个字符

select lower（“ABCD”）;         =》abcd

space() 函数
函数公式：space（数值）
产生数值的空格

select ‘a’+ space（2）+'b';         =》  a  b

replicate() 函数
函数公式：replicate（字符，数值）
需要重复的字符，重复次数

select replicate（“abcd”,2） ;         =》 abcdabcd

stuff() 函数 也叫 字符替换函数
函数公式：replicate（字符1，起始位置，长度，字符2）
需要重复的字符，重复次数

select stuff（“abcdef”,2，2，“qp”） ;         =》 aqpdef

trim() 函数
函数公式：trim（字符串）
去除字符串左右两侧的空格

select trim（“   abcd  ”） ;         =>abcd

ltrim() 函数
函数公式：trim（字符串）
去除字符串左侧的空格

select trim（“   abcd  ”） ;         =>abcd  

rtrim() 函数
函数公式：trim（字符串）
去除字符串右侧的空格

select trim（“   abcd  ”） ;         =>   abcd

reverse() 函数
函数公式：reverse（字符串）
字符串反转

select reverse（“abcd”） ;         =>dcba

charindex() 函数
函数公式：charindex（字符1，字符2）
返回字符1在字符2中的位置

select charindex（“a”，“abcd”） ;         =>1

str() 函数
函数公式：str（数值）
将数值转化为字符型

select str（123） ;         =>123

4.3 日期函数

getdate() 函数 返回当前系统日期、时间

select getdate();

datepart(指定部分，给定日期) 函数
返回当前系统指定日期

select datepart(year，“2018-10-15”);    ===>2018

day() 函数 返回月中第几天

select day('2018-10-15');         ==>15
案例：返回当前日期是月中第几天
select day(getdate());

month() 函数 返回指定日期月份

select month('2018-10-15');         ==>10

year() 函数 返回指定日期年份

select year('2018-10-15');         ==>2018

dateadd() 函数 向指定日期添加或减去指定日期时间间隔
dateadd（datepart，number，date）

向指定日期加上两天是哪一天
select DATE_ADD('2018-10-15',INTERVAL 2 DAY);  ==>2018-10-17
向指定日期减去两天是哪一天
select DATE_ADD('2018-10-15',INTERVAL -2 DAY);  ==>2018-10-17

datediff() 函数 返回两个日期之间的差值
datediff（datepart，number，date）

SELECT DATEDIFF('2008-12-30','2008-12-29')   ===> 1

datename() 函数 从日期中提取部分指定数据
datename（指定部分，给定日期）

SELECT datename(weekday,'2008-10-15')   ===> 1

5、多表查询

5.1连接查询

将两个表或多个表之间的关系，建立连接表（中间表），从连接表中查询数据。

5.2 内连接

内连接查询：只有两个表相匹配的行才会在结果集中出现

根据所使用的比较方式的不同，内连接又分为等值连接、自然连接、非等值连接。

5.2.1 等值连接

在连接条件中使用等于号（=），运算符比较连接列的列值。

案例：现有两张表，需要将两张表进行内连接？

select * from A inner join B on A.id = B.id;

5.2.2 非等值连接

在连接条件使用除等于运算符以外的其他比较运算符比较被连接的列的列值。
这些运算符包括：>、>=、<、<=、!>、!<、<>.

select * from A inner join B on A.id > B.id

5.2.3 自然连接(常用)

如果是按照两个表中的相同属性等值连接，且目标列中去掉了重复的属性列，但保留了所有不重复的属性列，则称之为自然连接。

select student.* , sc.* from student natural join sc;
无需指明连接条件，系统会自动匹配列值相等的字段。

n个表连接查询，至少需要n-1个查询条件；

5.3 外连接

外连接根据连接方式不同，可以分为左外连接、右外连接、全外连接。

5.3.1 左外连接

左外连接：是把左边的表的元组全部筛选出来。

左外连接的两种情况：
1.左外连接生成表A的所有记录，如果没有匹配的，右边将是 null；
2.为了生成只在表A里而不在表B里的记录，我们用同样的左外连接，然后用where语句排除我们不想要的记录。

select * from A left join B on A.id = B.id

select 员工id，姓名，销售日期，客户名称，金额 from 员工表 left join 销售表 on 员工表.员工id = 销售表.销售员 left join 商品一览表 on 商品一览表.货号 = 销售表.货号；

5.3.2 右外连接

右外连接:就是将右边表的数据全部取出，不管左表是否有匹配的值。

右外连接的两种情况：
1.右外连接生成表B的所有记录，如果没有匹配的，左边将是 null；
2.为了生成只在表B里而不在表A里的记录，我们用同样的右外连接，然后用where语句排除我们不想要的记录。

select * from A right join B on A.id = B.id

5.3.3 全外连接

全外连接：把左右两个表的数据都取出来，不管是否匹配

全外连接的两种情况：
1.全外连接生成表A和表B里的所有记录，包括两边都匹配的记录，如果有一边么有匹配的，缺失的这一边为null。
2.为了生成对于表A和表B唯一的记录集，我们用同样的全外连接，然后用where语句排除两边都不想要的记录。

select * from A full join B on A.id = B.id;

5.4 聚合函数

聚合函数对一组数据执行计算查询结果集中生成汇总值

5.4.1 sum函数

计算一个字段中各个记录的总和
语法：

select sum(某个字段) from 表名 where 条件表达式；

5.4.2 avg函数

计算字段中记录的平均值
语法：

select avg(某个字段) from 表名；
select avg(distinct 某个字段) from 表名；

5.4.3 max与min函数

计算指定列的最大值或最小值
语法：

select max(某个字段) from 表名 ；
select min(某个字段) from 表名 ；

5.4.4 cunt函数

统计列中值得个数
语法：

select count(*) from 表名；
select count(某个字段) from 表名；
select count(distinct 某个字段) from 表名；

5.5 Group by 子句

对数据进行分组；
group by 经常和聚合函数一同使用

使用 having 子句 对分组之后结果进行过滤。

5.6 having 子句

having 用于对 where 和 group by 查询出来的分组进行过滤，查出满足条件的分组结果，他是一个过滤声明。
where用于在初始化表中进行筛选查询，having用于group by 结果分组中查询

案例：根据学号统计至少选择了2门课程的学生的选修课程门数及平均成绩
select 学号，count(课程号) as 选修门数，avg(成绩) as 平均成绩 
from sc 
group by 学号
having count(课程号) >= 2

where 子句与 having 子句区别

where子句	having子句
不能使用聚合函数	可以使用聚合函数
在分组之前进行过滤	分组之后进行过滤
限制的是行	限制的是组
mysql中sql的执行顺序：

6、嵌套查询

1.什么是查询块？
在SQL语言中，一个select - from - where 语句成为一个查询块。
2.什么是嵌套查询？
将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询。

子查询的select 语句不能使用 order by 子句，因为 order by 子句只能对最终查询结果排序。

嵌套查询分类：

比较子查询（any ，all）
使用 in 子查询
简单子查询

6.1 简单嵌套查询

select * from student where 学号 = （ select 学号 from grade where 课程成绩 > 96 ）;

6.2 使用 in 子查询

select * from student where 学号 in （select 学号 from grade）；

6.3 使用 not in 子查询

select * from student where 学号 not in （select 学号 from grade where 学号 is not null）；

子查询结果存在 null 值时，避免使用 not in
not in的效率不高，尽量少用，因为它对子查询中的表执行了一个全表扫描

6.4 使用比较子查询

6.4.1 使用 any 子查询

就是将查询表达式与子查询的任一结果进行比较，若有一次为真，比较结果就为真。

>any:表示至少大于其中一个值，即大于最小值

<any:表示至少小于其中一个值，即小于最大值

案例：

select * from student where 年龄 < any (select avg(年龄) from student group by 性别)；

SQL中的定量谓词（any / all）不支持反操作，也就是说，不能在any 或 all 前加 not 关键字，但可以用 “ < >” 号表示否定

6.4.1 使用 all 子查询

就是将查询表达式与子查询的结果运算比较，若每次的比较结果都为真时，结果才为真。

>all:表示大于子查询结果的所有值，即大于最大值

<all :表示大于子查询结果的所有值，即大于最大值

案例：

select * from course where 课程代号 <> all (select 课程代号 from grade where 课程成绩 > 90);

7、数据库对象操作

7.1 插入数据

insert  into  表名（列名1，列名2，....） values (列值1，列值2....)

7.2 修改数据

update  表名 set 列名1 = 表达式1，列名2 = 表达式2；

7.3 删除数据

delete  from 表名  where 条件表达式

7.4 创建语句

1.创建数据库 create database 数据库名

2.创建表 create table 表名
3.创建视图 create view 视图名

MySQL中列的数据类型

修改表

alter table 表名
[add …]
[drop …]
[modify …]

alter table 表名 add 字段 列类型;

add 添加主键

alter table 表名 add primary key（列名）;

add 添加外键

alter table 表名 add foreign key（列名） references 表名（列名）；

删除列

alter table 表名 drop 列名；

删除主键

• 如果有 auto_increment，先删除，再删除主键
• 如果没有，直接删除
  

alter table products change pid int;
alter table products drop primary key;

modify子句：修改列名称或类型

案例：将年龄（age）的数据类型改为半字长整数（smallint）

alter table student modify age (smallint);

更改默认值：

alter table 表名 alter 列名 set default 默认值；

change与modify区别：

重命名只能用 change

alter table 表名 change 旧字段名 新字段名 字段属性；

删除表(drop)

表的结构、属性以及索引也会被删除

drop table 表名；
drop table 表1，表2，......；

mysql中各种删除语句比较：
delete

删除数据表中的行

删除某一行
delete from 表名称 where 列名称 = 值；
删除所有行
delete * from 表名称；

drop

删除表、数据库、字段

删除数据库
drop database 数据库名称；
删除表
drop table 表名；
删除表中字段
alter table 表名 drop 字段名；

truncate

清空数据表中的数据
仅删除数据表中数据，不删除表的结构

truncate table 表名称；

总结

• 当你必须要该表时，用drop
• 当你仍要保留该表，但需要删除所有记录时，用turncate
• 当你要删除部分记录时，用delete

truncate与drop,delete的对比

• truncate与drop是DDL语句，执行后无法回滚；delete是DML语句，可回滚。
• truncate只能作用于表；delete，drop可作用于表、视图等。
• truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；drop会删除表的结构及其所依赖的约束、索引等。
• truncate会重置表的自增值；delete不会。
• truncate后会使表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。

MySQL中常见的完整性约束

• primary key 主键约束：可以唯一标识一个元组
• foreign key 外键约束：是与之联系的某表的主键
• unique 唯一性约束：标识该属性的值是唯一的
• not null 非空值约束：标识该属性不能为空
• auto_increment 创建标识列：该标识列的值自动增加
• default 默认值约束：为该属性设置默认值

主键约束

方法一：
create table 表名 （列名1 列类型 [列的完整性约束]，
列名1 列类型 [列的完整性约束].....
primary key (某列名)）;
方法二：
create table 表名（列名1 列类型 primary key，
列名1 列类型 [列的完整性约束]......）;

主键作用：

• 唯一标识每一行
• 作为一个可以被外键所有效引用的对象

主键与外键区别：

	主键	外键
定义	唯一标识一条记录，不能有重复的，不允许为空	表外键是另一表的主键，外键可以有重复，可以是空值
作用	用来保证数据的完整性	用来和其他表建立联系
个数	只能有一个	一个表可以有多个外键

非空值约束

creat table SC (
Sno char(9) not null,
Cno char(4) not null,
Grade smallint not null,
primary key (Sno,Cno),
);

唯一性约束

creat table SC (
Sno char(9) not null,
Cno char(4) not null,
Grade smallint unique,
primary key (Sno,Cno),
);