MySQL基础

最新推荐文章于 2024-08-23 18:32:39 发布

HuiEr_h

最新推荐文章于 2024-08-23 18:32:39 发布

阅读量241

点赞数 1

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/HuiEr_h/article/details/127974816

版权

文章目录

MySQL数据库
基础

MySQL数据库

基础

第一章基本概念

数据库

创建数据库

create database mysql1;
create database mysql2;

在这里插入图片描述

查看所有数据库

show databases

使用数据库

use mysql2;

表

表（TABLE）是数据库中用来存储数据的对象，是有结构的数据的集合，是整个数据库系统的基础。

第二章 SQL

SQL是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

一、SQL语句分类

DCL(Data Control Language)：数据控制语言，用来定义访问权限和安全级别。
DDL(Data Definition Language)：数据定义语言，用来定义数据库对象：库、表、列等。功能：创建、删除、修改库和表结构。
DML(Data Manipulation Language)：数据操作语言，用来定义数据库记录：增、删、改表记录。
DQL(Data Query Language)：数据查询语言，用来查询记录。也是本章学习的重点。

二、DCL（数据控制语言）

mysql中的权限无非是针对不同的用户而言，不同的用户的权限提现在以下几点：可否链接mysql服务、可否访问数据库、可否访问某张数据库表、可否对表进行一些操作等。

1、创建用户

创建一个用户，该用户只能在指定ip地址上登录mysql：

create user '用户名'@'IP地址' identified by ‘密码’;
create user 'testUser'@'localhost' identified by 'testUser';

创建一个用户，该用户可以在任意ip地址上登录mysql:

create user '用户名'@'%' identified by '密码';
create user 'testUser2'@'%' identified by 'testUser2';

修改密码：

-- 5.7版本需要使用password对密码进行加密
set password for '用户名'@'IP' = password('newpwd');
-- 8.0直接赋值新的密码即可
set password for '用户名'@'IP' = 'newpwd';
set password for 'testUser'@'localhost' = 'root';

如果直接修改表，也是可以创建用户修改密码的，【mysql数据库下的user表】但是通过修改数据库创建用户，修改密码，都需要刷新权限：

flush privileges;

-- 创建数据库的两种方式
create database mysql1;
create database mysql2;

-- 显示所有的数据库
show databases;

-- 指定使用的数据库
use mysql2;

-- 创建用户
create user 'testUser'@'localhost' identified by 'testUser';
create user 'testUser2'@'%' identified by 'testUser2';

-- 修改用户密码
-- 5.7版本需要使用password对密码进行加密
set password for 'testUser'@'localhost' = password('root');
-- 8.0直接赋值新的密码即可
set password for 'testUser'@'localhost' = 'newpwd';

2、用户授权

create：可以创建数据库
select：可以查询数据
delete：可以删除数据
update：可以更新数据
insert：可以插入数据

grant 权限 on 数据库.表 to '用户名'@'IP';

-- 用户授权 所有
grant all on mysql1.* to 'testUser'@'localhost';
-- 个别
grant select,create on mysql1.* to 'testUser'@'localhost';

3、撤销授权

revoke 权限 on 数据库.表 from '用户名'@'IP';

-- 撤销授权
revoke all on `mysql1`.* from 'testUser'@'localhost';
revoke select on `mysql1`.* from 'testUser'@'localhost';

4、查看权限

show grants for '用户名'@'IP';

-- 查看权限
show grants for 'testUser'@'localhost';

5、删除用户

drop user '用户名'@'IP';

-- 删除用户
drop user 'testUser'@'localhost';
drop user 'testUser2'@'%';

三、DDL（数据定义语言）

DDL主要是用在定义或改变表（TABLE）的结构，主要的命令有CREATE、ALTER、DROP等：

1、创建表的基本语法

create table 表名 (

字段名1（列名） 类型(宽度) 约束条件,

字段名2 类型(宽度) 约束条件,

字段名3 类型(宽度) 约束条件,

.......

);

create table tb_user(
id int primary key AUTO_INCREMENT,
account varchar(64) unique not null,
`password` varchar(64) not null
);

2、常用数据类型

整形

MySQL数据类型	含义（有符号）
tinyint	1字节，范围（-128~127）
smallint	2字节，范围（-32768~32767）
mediumint	3字节，范围（-8388608~8388607）
int	4字节，范围（-2147483648~2147483647）
bigint	8字节，范围（±9.22*10的18次方）

在整形中我们默认使用的都是【有符号】的，当然了，我们也可以加上unsigned关键字，定义成无符号的类型，那么对应的取值范围就会发生改变：

比如：tinyint unsigned的取值范围为0~255，

宽度n的作用需要配合zerofill进行使用：如：int(4) UNSIGNED zerofill 查询结果: 0001 0002 1000

浮点型

MySQL数据类型	含义
float(m, d)	4字节，单精度浮点型，m总长度，d小数位
double(m, d)	8字节，双精度浮点型，m总长度，d小数位
decimal(m, d)	decimal是存储为字符串的浮点数，对应我们java的Bigdecimal

针对数据类型为float(5, 3)的列，做了以下试验：

插入123.45678，最后查询得到的结果为99.999；
插入12.34567，最后查询结果为12.346；

所以，在使用浮点型的时候，还是要注意陷阱的，要以插入数据库中的实际结果为准。

字符串

MySQL数据类型	含义
char(n)	固定长度，最多255个字符
varchar(n)	可变长度，最大容量65535个字节
tinytext	可变长度，最大容量255个字节
text	可变长度，最大容量65535个字节
mediumtext	可变长度，最大容量2的24次方-1个字节 16MB
longtext	可变长度，最大容量2的32次方-1个字节 4GB

（1）char和varchar的区别

char类型是**【定长】**的类型，即当定义的是char(10)，输入的是"abc"这三个字符时，它们占的空间一样是10个字符，包括7个空字节。当输入的字符长度超过指定的数时，char会截取超出的字符。而且，当存储char值时，MySQL是自动删除输入字符串末尾的空格。
char是适合存储很短的、一般固定长度的字符串。例如，char非常适合存储密码的MD5值，因为这是一个定长的值。对于非常短的列，char比varchar在存储空间上也更有效率。
varchar(n)类型用于存储**【可变长】**的，长度最大为n个字符的可变长度字符数据。比如varchar(10), 然后输入abc三个字符，那么实际存储大小为3个字节。除此之外，varchar还需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于等于255字节（是定义的最长长度，不是实际长度），则使用1个字节表示长度，否则使用2个字节来表示。n表示的是最大的
char类型每次修改的数据长度相同，效率更高，varchar类型每次修改的数据长度不同，效率更低。

（2）varchar和text

text不能设置默认值，varchar可以
text类型，包括（MEDIUMTEXT，LONGTEXT）也受单表 65535 最大行宽度限制，所以他支持溢出存储，只会存放前 768 字节在数据页中，而剩余的数据则会存储在溢出段中。虽然 text 字段会把超过 768 字节的大部分数据溢出存放到硬盘其他空间，看上去是会更加增加磁盘压力。但从处理形态上来讲 varchar 大于 768 字节后，实质上存储和 text 差别不是太大了。因为超长的 varchar 也是会用到溢出存储，读取该行也是要去读硬盘然后加载到内存，基本认为是一样的。
根据存储的实现：可以考虑用 varchar 替代 text，因为 varchar 存储更弹性，存储数据少的话性能更高。
如果存储的数据大于64K，就必须使用到 mediumtext，longtext，因为 varchar 已经存不下了。
如果 varchar(255+) 之后，和 text 在存储机制是一样的，性能也相差无几。

日期和时间

MySQL数据类型	含义
date	3字节，日期，格式：YYYY-MM-DD
time	3字节，时间，格式：HH:MM:SS
datetime	8字节，日期时间，格式：YYYY-MM-DD HH:MM:SS
timestamp	4字节，自动存储记录修改的时间
year	1字节，年份

3、建表约束

（1）MySQL约束类型

约束名称	描述
NOT NULL	非空约束
UNIQUE	唯一约束，取值不允许重复,
PRIMARY KEY	主键约束（主关键字），自带非空、唯一、索引
DEFAULT	默认值（缺省值）
FOREIGN KEY	外键约束（外关键字）

（2）NOT NULL

非空约束指的是，如果我们要在这张表中添加数据，设定了非空约束的列必须赋值，不能为空：

create table tb_user(
id int primary key AUTO_INCREMENT,
account varchar(64) unique not null, -- 不能为空
`password` varchar(64) not null
);

（3）UNIQUE

唯一约束指的是，如果我们要在这张表中添加数据，设定了唯一约束的列中的值不能重复，不能为空：

create table tb_user(
id int primary key AUTO_INCREMENT,
account varchar(64) unique not null, -- 唯一值
`password` varchar(64) not null
);

（4）DEFAULT约束

默认约束指的是，如果我们要在这张表中添加数据，如果为给设定了默认约束的列赋值，该列会自动填充默认值：

create table tb_user(
id int primary key AUTO_INCREMENT,
account varchar(64) unique not null,
`password` varchar(64) not null default '123456' -- 默认值
);

（5）PRIMARY KEY约束

主键（PRIMARY KEY）的完整称呼是“主键约束”，是 MySQL中使用最为频繁的约束。一般情况下，为了便于 DBMS 更快的查找到表中的记录，都会在表中设置一个主键。

主键分为单字段主键和多字段联合主键

使用主键应注意以下几点：

每个表只能定义一个主键。
主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不可能存在有相同主键值的两行数据。这是唯一性原则。
一个字段名只能在联合主键字段表中出现一次。
【联合主键】不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。

单个字段作为主键

方法一

create table tb_user(
id int primary key AUTO_INCREMENT, -- 主键
account varchar(64) unique not null,
`password` varchar(64) not null default '123456'
);

方法二

create table tb_user(
id int,
account varchar(64) unique not null,
`password` varchar(64) not null default '123456',
primary key(id)
);

联合主键

create table tb_user(
id int,
number varchar(64),
account varchar(64) unique not null,
`password` varchar(64) not null default '123456',
primary key(id,number)
);

（6）AUTO_INCREMENT约束

需要配合主键使用，有个这个约束，我们在向表中插入数据时，不需要额外关心主键的数据，他会自动帮我们维护一个递增的主键：

create table tb_user(
id int primary key AUTO_INCREMENT, -- 主键自增
account varchar(64) unique not null,
`password` varchar(64) not null default '123456'
);

（7）FOREIGN KEY约束

外键维护的表与表之间的关系，他规定了当前列的数据必须来源于一张其他表的某一列中的主键：

外键效果

删除主表时需要先删除外键的影响
外键的值必须来源于引用的表的主键

foreign key (外键字段) references 主键表(主键表中的主键字段)

create table tb_author(
id int primary key auto_increment,
`name` varchar(64) not null unique
);

create table tb_book(
id int primary key auto_increment,
`name` varchar(64) not null unique,
`author_id` int not null,
foreign key (author_id) references tb_author(id)  -- 外键约束
);

4、对表的修改操作

查看当前数据库的所有表
```
show tables;
```
查看表的结构
```
desc 表名;
desc tb_author;
```

修改表的操作，所有前缀都是

alter table 表明...

添加列

alter table tb_user add(
hoby varchar(64),
address varchar(64)
);

修改列

仅修改字段类型

alter table tb_user modify address varchar(100);

既修改字段名称和字段类型

alter table tb_user change address addr varchar(60);

删除列
```
alter table tb_user drop addr;
```
修改表名
```
alter table tb_user rename `user`;
```

删除表

drop table if exists 表名;
drop table if exists `user`;

四、DML

该语言用来对表记录进行操作（增、删、改），不包含查询。

create table tb_user(
id int primary key auto_increment,
`name` varchar(64)
);

1、插入数据

-- 指定字段插入数据
insert into tb_user(name) values('灰二');

-- 所有字段插入数据，主键自增后可以使用null占位
insert into tb_user values(null,'日向');

-- 插入多条数据
insert into tb_user VALUES(null,'及川彻'),(null,'鸣人');
select * from tb_user;

说明:

在数据库中所有的字符串类型，必须使用单引号。
(列名1，列名2，列名3)可省略，表示按照表中的顺序插入。但不建议采取这种写法，因为降低了程序的可读性。

2、修改记录

修改某列的全部 : update 表名 set 列名1 = 列值1(,列名2 = 列值2)

要特别注意 : 如果修改表数据没有添加where条件，则表示修改所有数据，会产生极大的问题，所以我们对于修改操作一定要再三检查

update tb_user set `name` = '宁次' where id = 1;

where是一个很关键的关键字，我们可以使用where关键字实现丰富的筛选，他很像我们的if语句，可以使用各种条件运算：

可使用的逻辑运算符如下：=、!=、<>、<、>、>=、<=、between...and、in(…)、is null、not、or、and，其中in(…)的用法表示集合。

where 列名>1
where 列名in (1,3,5)
where 列名 between 1 and 4
where 列名>1 and 列名=‘xxx’
where 列名 is null : 判断空
where 列名is not null : 判断非空

字符串也使用=比较而不是==或者equals

3、删除数据

delete from 表名 (where 条件);

不加where条件时会删除表中所有的记录，所以为了防止这种失误操作，很多数据库往往都会有备份。我们删除时也要在三检查

delete from tb_user where id = 1;

truncate是DDL语言，操作立即生效，原数据不会放到rollback segment中，不能回滚。

当表被truncate后，表和索引的所占空间会恢复到初始大小，delete操作不会减少表和索引的所占空间。
truncate和delete只删除数据，drop则删除整个表（结构和数据）。
truncate速度快，效率高，可以理解为先把表删除了，再重新建立。
truncate和delete均不会使表结构及其列、约束、索引等发生改变。

第三章 DQL数据查询

DQL是业务开发中较为重要的部分

一、构建初始项目

drop TABLE if EXISTS student;
CREATE TABLE student (
    id INT(10) PRIMARY key,
    name VARCHAR (10),
    age INT (10) NOT NULL,
    gander varchar(2)
);

drop TABLE if EXISTS course;
CREATE TABLE course (
  id INT (10)  PRIMARY key,
  name VARCHAR (10) ,
  t_id INT (10) 
) ;

drop TABLE if EXISTS teacher;
CREATE TABLE teacher(
  id INT (10)  PRIMARY key,
  name VARCHAR (10) 
);

drop TABLE if EXISTS scores;
CREATE TABLE scores(
  s_id INT ,
  score INT (10),
  c_id INT (10) ,
	PRIMARY key(s_id,c_id)
) ;

insert into  student (id,name,age,gander)VALUES(1,'白杰',19,'男'),(2,'连宇栋',19,'男'),(3,'邸志伟',24,'男'),(4,'李兴',11,'男'),(5,'张琪',18,'男'),(6,'武三水',18,'女'),(7,'张志伟',16,'男'),(8,'康永亮',23,'男'),(9,'杨涛瑞',22,'女'),(10,'王杰',21,'男');

insert into  course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null);


insert into  teacher (id,name)VALUES(1,'张楠'),(2,'李子豪'),(3,'薇薇姐'),(4,'猴哥'),(5,'八戒');


insert into  scores (s_id,score,c_id)VALUES(1,80,1);
insert into  scores (s_id,score,c_id)VALUES(1,56,2);
insert into  scores (s_id,score,c_id)VALUES(1,95,3);
insert into  scores (s_id,score,c_id)VALUES(1,30,4);
insert into  scores (s_id,score,c_id)VALUES(1,76,5);

insert into  scores (s_id,score,c_id)VALUES(2,35,1);
insert into  scores (s_id,score,c_id)VALUES(2,86,2);
insert into  scores (s_id,score,c_id)VALUES(2,45,3);
insert into  scores (s_id,score,c_id)VALUES(2,94,4);
insert into  scores (s_id,score,c_id)VALUES(2,79,5);

insert into  scores (s_id,score,c_id)VALUES(3,65,2);
insert into  scores (s_id,score,c_id)VALUES(3,85,3);
insert into  scores (s_id,score,c_id)VALUES(3,37,4);
insert into  scores (s_id,score,c_id)VALUES(3,79,5);

insert into  scores (s_id,score,c_id)VALUES(4,66,1);
insert into  scores (s_id,score,c_id)VALUES(4,39,2);
insert into  scores (s_id,score,c_id)VALUES(4,85,3);

insert into  scores (s_id,score,c_id)VALUES(5,66,2);
insert into  scores (s_id,score,c_id)VALUES(5,89,3);
insert into  scores (s_id,score,c_id)VALUES(5,74,4);


insert into  scores (s_id,score,c_id)VALUES(6,80,1);
insert into  scores (s_id,score,c_id)VALUES(6,56,2);
insert into  scores (s_id,score,c_id)VALUES(6,95,3);
insert into  scores (s_id,score,c_id)VALUES(6,30,4);
insert into  scores (s_id,score,c_id)VALUES(6,76,5);

insert into  scores (s_id,score,c_id)VALUES(7,35,1);
insert into  scores (s_id,score,c_id)VALUES(7,86,2);
insert into  scores (s_id,score,c_id)VALUES(7,45,3);
insert into  scores (s_id,score,c_id)VALUES(7,94,4);
insert into  scores (s_id,score,c_id)VALUES(7,79,5);

insert into  scores (s_id,score,c_id)VALUES(8,65,2);
insert into  scores (s_id,score,c_id)VALUES(8,85,3);
insert into  scores (s_id,score,c_id)VALUES(8,37,4);
insert into  scores (s_id,score,c_id)VALUES(8,79,5);

insert into  scores (s_id,score,c_id)VALUES(9,66,1);
insert into  scores (s_id,score,c_id)VALUES(9,39,2);
insert into  scores (s_id,score,c_id)VALUES(9,85,3);
insert into  scores (s_id,score,c_id)VALUES(9,79,5);

insert into  scores (s_id,score,c_id)VALUES(10,66,2);
insert into  scores (s_id,score,c_id)VALUES(10,89,3);
insert into  scores (s_id,score,c_id)VALUES(10,74,4);
insert into  scores (s_id,score,c_id)VALUES(10,79,5);

二、单表查询

1、基本查询

（1）基本语法

查询所有列，其中*表示查询所有列 : select * from 表名

select * from student;

查询指定列 : select 列1,列2,列n from 表名;

select `id`,`name`,`age`,`gander` from `student`;
select `id`,`name`,`age` from `student`;

完全重复的记录只显示一次，在查询的列之前添加distinct

（2）列运算

数量类型的列可以做加、减、乘、除等运算:

-- 查询给所有员工工资加1000的结果
select id,name,sal+1000 from employee；
select `id`,`name`,`age`*10 from student;

说明:

1、null加任何值都等于null，，需要用到ifnull()函数。SELECT IFNULL(sal,0) from 表名; 如果薪资列为空，则输出0；

2、将字符串做加减乘除运算，会把字符串当作0。

（3）别名

给列名起【别名】，因为在查询的结果中列名可能重复，可能名字不够简洁，或者列的名字不满足我们的要求:

select 列名1 (as) 别名1,列名2 (as) 别名2 from 表名；

select `id` `编号`,`name` `名字`,ifnull(`age`,0) as `age` from `student` as s;

只需要在列名后加 as 新列名，或是直接加上新列名即可。

（4）条件控制

条件查询：在后面添加where指定条件

-- 条件控制  select * from 表名 where 列名＝指定值;
select * from student where id = 3;
select * from student where id in (1,3,7);
select * from student where id >5 ;
select * from student where id between 3 and 7 ;
select * from student where id between 6 and 7 or age > 20;

模糊查询：当你想查询所有姓张的记录。用到关键字like

select * from student where name like '张_'; 
select * from student where name like '张%';

（_代表匹配任意一个字符，％代表匹配0～n个任意字符）

2、排序（所谓升序和降序都是从上往下排列）

升序 : ascend

select * form 表名 order by 列名 asc;    -- asc为默认值可以不写

降序:descend

select * from 表名 order by 列名 desc; -- desc为降序

使用多列作为排序条件：当第一列排序条件相同时，根据第二列排序条件排序(当第二列依旧相同时可视情况根据第三例条件排序)：
```
select * from 表名 order by 列名1 asc, 列名2 desc;
```
当列名1的值相同时按照列名2的值降序排。

3、聚合函数

1.count：查询满足条件的记录行数，后边可以跟where条件：

如果使用的列值为空，不会进行统计，

我们如果统计真实的表记录条数，最好不要用可以为空的列：

count(*) count(id) count(1)
```
select count(列名) from 表名;
select max(age) from student where id > 5;
```
2.max : 查询满足条件的记录中的最大值，后边可以跟where条件：
```
select max(列名) from 表名;
```
3.min：查询满足条件的记录中的最大值，后边可以跟where条件：
```
select min(列名) from 表名;
```
4.sum：查询满足条件的记录中的值的和，后边可以跟where条件：
```
select sum(列名) from 表名;
```
5.avg：查询满足条件的记录中的值的平均数，后边可以跟where条件：
```
select avg(列名) from 表名;
```

4、分组查询

基本语法：select 分组列名，聚合函数1,聚合函数2 from 表名 group by 该分组列名;

分组要使用关键词group by，后边可以是一列，也可以是多个列，分组后查询的列只能是分组的列，或是使用了聚合函数的其他列，其他列不能单独使用。

有时我们需要对数据进行帅选，作为分组条件的列和聚合函数：

分组查询前，还可以通过关键字【where】先把满足条件的人分出来，再分组，语法为:

select 分组列，聚合函数 from 表名 where 条件 group by 分组列;

分组查询后，也可以通过关键字【having】把组信息中满足条件的组再细分出来，语法为：

select 分组列，聚合函数 from 表名 where 条件 group by 分组列 having 聚合函数或列名(条件);

select gander,avg(age) avg_age,sum(age) sum_age from student GROUP BY gander HAVING  gander = '男'

5、LIMIT子句

LIMIT用来限定查询结果的起始行，以及总行数，通常用来做分页查询，他是MySQL中独有的语法。

select * from 表名 limit 4,3;

如果一个参数：说明从开始查找三条记录

SELECT id,name,age,gander FROM student limit 3;

如果两个参数：说明从第三行起（不算），向后查三条记录

SELECT id,name,age,gander FROM student limit 3,3;

第四章多表查询

一、笛卡儿积

二、内连接

在这里插入图片描述

三、外连接

在这里插入图片描述

四、全连接

MySQL中并不支持全连接，但是有些数据库是支持的，比如oracle，使用【full outer join】关键字，SQL如下：

SELECT * from teacher t full outer join course c on c.t_id = t.id
SELECT * from teacher t full join course c on c.t_id = t.id

MySQL 不支持此种方式，可以用其他方式替代解决，如下。

SELECT * from teacher t right outer join course c on c.t_id = t.id
union
SELECT * from teacher t left outer join course c on c.t_id = t.id

在这里插入图片描述

小知识：阿里规约有这么一条【强制】超过三个表禁止join。需要join的字段，数据类型必须绝对一致；多表关联查询时，保证被关联的字段需要有索引。说明：即使双表join也要注意表索引、SQL性能。

五、子查询

按照结果集的行列数不同，子查询可分为以下几类：

标量子查询：结果集只有一行一列（又称为单行子查询）
列子查询：结果集只有一列多行
行子查询：结果集只有一行多列
表子查询：结果集一般为多行多列

1、where/having型子查询

2、from型子查询

3、select型子查询

4、exists型子查询

select * from teacher t where exists (
	select * from course c where c.t_id = t.id
);

第五章 MySQL常用函数介绍

MySQL数据库中提供了很丰富的函数，比如我们常用的聚合函数，日期及字符串处理函数等。SELECT语句及其条件表达式都可以使用这些函数，函数可以帮助用户更加方便的处理表中的数据，使MySQL数据库的功能更加强大

一、聚合函数

聚合函数是平时比较常用的一类函数，这里列举如下：

COUNT(col) ：统计查询结果的行数
MIN(col)：查询指定列的最小值
MAX(col)：查询指定列的最大值
SUM(col)：求和，返回指定列的总和
AVG(col)：求平均值，返回指定列数据的平均值

二、数值型函数

数值型函数主要是对数值型数据进行处理，得到我们想要的结果，常用的几个列举如下：

ceiling(x)：返回大于x的最小整数值，向上取整
floor(x)：返回小于x的最大整数值，向下取整
round(x,y)：返回参数x的四舍五入的有y位小数的值四舍五入
truncate(x,y)：返回数字x截短为y位小数的结果
pi()：返回pi的值（圆周率）
rand()：返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值
abs(): 绝对值
mod(x,y): 返回x被y除后的余数

# ABS()函数求绝对值
SELECT ABS(5),ABS(-2.4),ABS(-24),ABS(0);

# 取整函数 CEIL(x) 和 CEILING(x) 的意义相同，返回不小于 x 的最小整数值
SELECT CEIL(-2.5),CEILING(2.5);

# 求余函数 MOD(x,y) 返回 x 被 y 除后的余数
SELECT MOD(63,8),MOD(120,10),MOD(15.5,3);

# RAND() 函数被调用时，可以产生一个在 0 和 1 之间的随机数
SELECT RAND(), RAND(), RAND();

三、字符串函数

字符串函数可以对字符串类型数据进行处理，在程序应用中用处还是比较大的，同样这里列举几个常用的如下：

length(s)：计算字符串长度函数，返回字符串的字节长度
concat(s1,s2…,sn)：合并字符串函数，返回结果为连接参数产生的字符串，参数可以是一个或多个
lower(str)：将字符串中的字母转换为小写
upper(str)：将字符串中的字母转换为大写
left(str,x)：返回字符串str中最左边的x个字符
right(str,x)：返回字符串str中最右边的x个字符
trim(str)：删除字符串左右两侧的空格
replace：字符串替换函数，返回替换后的新字符串 REPLACE(name,‘白’,‘黑’)
substring：截取字符串，返回从指定位置开始的指定长度的字符换
reverse(str)：返回颠倒字符串str的结果
insert(s1，x，len，s2) 返回字符串 s1，子字符串起始于 x 位置，并且用 len 个字符长的字符串代替 s2

四、日期和时间函数

获取时间和日期

【CURDATE 和 CURRENT_DATE】两个函数作用相同，返回当前系统的【日期值】
【CURTIME 和 CURRENT_TIME】两个函数作用相同，返回当前系统的【时间值】
【NOW】和【SYSDATE】两个函数作用相同，返回当前系统的【日期和时间值】

时间戳或日期转换函数：

【UNIX_TIMESTAMP】获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数
【FROM_UNIXTIME】将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数

根据日期获取年月日的数值

【MONTH】获取指定日期中的月份
【MONTHNAME】获取指定日期中的月份英文名称
【DAYNAME】获取指定曰期对应的星期几的英文名称
【DAYOFWEEK】获取指定日期对应的一周的索引位置值
【WEEK】获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53
【DAYOFYEAR】获取指定曰期是一年中的第几天，返回值范围是1~366
【DAYOFMONTH】获取指定日期是一个月中是第几天，返回值范围是1~31
【YEAR】获取年份，返回值范围是 1970〜2069

时间日期的计算

【DATE_ADD】和【ADDDATE】两个函数功能相同，都是向日期添加指定的时间间隔
【DATE_SUB】和【 SUBDATE】两个函数功能相同，都是向日期减去指定的时间间隔
【ADDTIME】时间加法运算，在原始时间上添加指定的时间
【SUBTIME】时间减法运算，在原始时间上减去指定的时间
【DATEDIFF】获取两个日期之间间隔，返回参数 1 减去参数 2 的值
【DATE_FORMAT】格式化指定的日期，根据参数返回指定格式的值

当使用了表达式计算后，不能直接使用别名进行判断了。

# CURDATE() 和 CURRENT_DATE() 函数的作用相同，将当前日期按照“YYYY-MM-DD”或“YYYYMMDD”格式的值返回
mysql> SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE()+0;


# MONTH(date) 函数返回指定 date 对应的月份
SELECT MONTH('2017-12-15');


# DATE_ADD(date,INTERVAL expr type) 和 ADDDATE(date,INTERVAL expr type) 两个函数的作用相同，都是用于执行日期的加运算。
SELECT DATE_ADD('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C1,DATE_ADD('2018-10-31 23:59:59',INTERVAL '1:1' HOUR) AS C2, ADDDATE('2018-10-31 23:59:59',INTERVAL 1 SECOND) AS C3;


# DATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数
SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1,DATEDIFF('2017-11-30','2017-12-15') AS col2;


# DATE_FORMAT(date，format) 函数是根据 format 指定的格式显示 date 值
SELECT DATE_FORMAT('2017-11-15 21:45:00','%W %M %D %Y') AS col1,DATE_FORMAT('2017-11-15 21:45:00','%h:i% %p %M %D %Y') AS col2;

五、加密函数

MD5() 计算字符串str的MD5校验和

SELECT MD5('abc');
结果：900150983cd24fb0d6963f7d28e17f72

六、流程控制函数

流程控制类函数可以进行条件操作，用来实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台，列举如下：

IF(test,t,f)：如果test是真，返回t；否则返回f
IFNULL(arg1,arg2)：如果arg1不是空，返回arg1，否则返回arg2
NULLIF(arg1,arg2)：如果【arg1=arg2】返回NULL，否则返回arg1

SELECT NULLIF('abc','abc');     #返回null
SELECT NULLIF('abc','abcd');    #返回abc

通过对某一列的值进行判断，

CASE [test] WHEN[val1] THEN [result]…ELSE [default] END：

如果test和valN相等，则返回resultN，否则返回default

CREATE TABLE `mystudent` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(20) DEFAULT NULL,
  `COURSE` varchar(20) DEFAULT NULL,
  `SCORE` float DEFAULT '0',
  PRIMARY KEY (`ID`)
)

insert into mystudent(USER_NAME, COURSE, SCORE)  values
("张三", "数学", 34),
("张三", "语文", 58),
("张三", "英语", 58),
("李四", "数学", 45),
("李四", "语文", 87),
("李四", "英语", 45),
("王五", "数学", 76),
("王五", "语文", 34),
("王五", "英语", 89);

例子一：输出学生各科的成绩，以及评级，60以下是D,60-70是C，71-80：是B ，80以上是A

SELECT
	*,
CASE
		WHEN score < 60 THEN 'D' 
		WHEN score >= 60 and score < 70 THEN 'C' 
		WHEN score >= 70 and score < 80 THEN 'B' 
		WHEN score >= 80 and score <= 100 THEN 'A' 
	END AS "评级"
FROM
	mystudent

例子二：行转列案例，要求根据上边的表结构，查询出如下结果：

select user_name,
   max(case course when '数学' then score else 0 end) as '数学',
   max(case course when '语文' then score else 0 end)	as '语文',
   max(case course when '英语' then score else 0 end)	as '英语'
from mystudent group by user_name

第六章数据库设计

一、三范式

设计数据库表的时候所依据的规范，共三个规范：

第一范式：要求有主键，并且要求每一个字段原子性不可再分

数据库表中不能出现重复记录，每个字段是原子性的不能再分——字段不可再分
第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖

第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖——分表
第三范式：所有非主键字段和主键字段之间不能产生传递依赖

建立在第二范式基础上的，非主键字段不能传递依赖于主键字段（不要产生传递依赖）——分表

二、常见表关系

1、一对一

2、一对多

3、多对多

日期格式

格式	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天（0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位