MySQL数据库知识整理

@[TOC]MySQL数据目录

1.什么是数据库?
1.1 概念
数据库(Database):按照数据结构来组织存储和管理数据的仓库;每个数据库都有一个或多个不同的API用于创建,访问,管理,搜索和复制所保存的数据;也可以把数据存进文件里,但是文件中访问提取速度比较慢,所以使用关系型数据库管理系统来储存和管理大量数据.关系型数据库就是建立在关系模型基础上的数据库,是借助高等数学中的几何代数等来处理数据库中的数据

1.2 关系数据库的特点
1.数据是以表格的形式出现

2.每一行为各种记录名称

3.每一列为记录名称所对应的数据域(表头)

4.所有的行和列组成一张数据表

5.若干的数据表组成数据库

1.3 数据库的术语
● 数据库:一些关联表的集合

● 冗余:储存两倍数据,冗余降低了性能,但是提高了数据的安全性

● 主键:主键在每个表中是唯一的,可以用来查询数据

● 外键:用于关联表格

● 复合键:一般用于复合索引,将多个列作为一个索引建

● 索引:使用索引可以快速查询数据,索引是对一列或多列的值进行排序的一种结构,类似于目录

2.数据库基本命令
在cmd窗口中的命令,也可在MySQL服务端

2.1 创建数据库
登陆MySQL服务以后,使用create命令创建数据库,语法如下

create database 数据库名;
2.2 删除数据库
删除数据库过程中,必须要谨慎,因为数据库数据一旦删除不能恢复,删除需谨慎(删库要跑路~哈哈哈)

drop命令删除数据库

drop database <数据库名>;
例如删除的库名为cgb2109的数据库:

mysql> drop database cgb2109;
2.3 选择数据库
连接数据库后,可能会有多个可操作的数据库,所以需要选择数据库

在mysql>提示窗口中,可简单的选择数据库,以cgb2109数据库为例,命令如下:

mysql> use cgb2019;
2.4 创建数据表
创建数据表需要:表名,表字段名,定义每个字段

以下为创建MySQL的基础语法:

create table table_name (column_name column_type);
以下例子是在cgb2109数据库中创建数据表dept:

create table dept(
    deptno int unsigned auto_increment,
    dname varchar(20) not null,
    loc varchar(20) not null,
    primary key('deptno')
)engine=innoDB default charset=utf-8;

2.5 删除数据表
删除需谨慎(删库要跑路~哈哈哈),基础语法如下:

drop table table_name;

2.6 插入数据(完成dept和emp数据表,用于后续练习~)
在数据表中插入数据使用INSERT INTO语句,基本语法如下:

insert into table_name values(null,values1,values2);

注意:如果插入的数据是字符型,必须使用单引号或者双引号,如:“value”

小练习1:完成以下表数据的添加(以下帖子查询会用哦~)

小练习2:试着完成以下员工表emp(以下帖子查询会用哦~)

可以用数据库软件直接插入数据

3.数据库基础语句
3.1 查询数据
数据库中查询数据使用SELECT语句,基本语法如下:

select column_name,column_name from table_name where 条件;

● 查询语句中可以使用一个表或多个表,表之间用逗号(,)隔开，并用WHERE语句设定查询条件

● SELECT命令可以读取一条或多条记录

● 可用(*)来代替其他字段,*表示返回表中所有字段

● 可以使用WHERE语句来包含任何条件

● 可以使用LIMIT属性来设定返回的记录数

● 可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0

select dname from dept where loc="二区";

3.2 WHERE 子句
WHERE 子句用于提取那些满足指定条件的记录,基本语法如下:

select column_name,column_name
from table_name
where column_name oprator value;

实例:查询dept数据表中部门(loc)为"二区"的部门名称(dname)

WHERE中的运算符:

运算符	描述
=	等于
<>	不等于
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	模糊查询
IN	指定针对某个列的多个可能值

3.2 AND & OR 运算符
AND & OR 运算符用于基于一个以上的条件对记录进行过滤

AND: 想让两个条件全都成立

OR:想让两个条件成立一个

AND实例:查询dept数据表,得到部门名称(dname)为:"java开发部"并且部门(loc)为:"一区"的所有数据

select * from dept where dname=“java开发部” and loc=“一区”;

OR实例:查询dept数据表,得到部门名称(dname)为:"java开发部"或者部门(loc)为:"一区"的所有数据

select * from dept where dname=“java开发部” or loc=“一区”;

结合AND & OR实例:

查询dept数据表,得到部门名称(dname)为:"java开发部"并且部门(loc)为:"一区"或者部门为"二区"的

select * from dept
where dname=“java开发部” and loc=“一区”
or loc=“二区”;

3.3 ORDER BY 关键字
ORDER BY 关键字用于对结果按照一个或者多个列进行排序

ORDER BY 默认排序方式为升序,降序可加DESC关键字

基本语法如下:

select column_name,column_name
from table_name
order by column_name,column_name asc|desc;
实例:查询员工表(emp)的员工姓名和薪资,并按照薪资(sal)从低到高排序

select ename,sal from emp order by sal;

ORDER BY 多列

按照多列数据排序,实例如下

把员工表(emp)按照薪资(sal)和奖金(comm)从高到低排序

select * from emp order by sal and comm desc;

3.4 INSERT INTO 语句
INSERT INTO 语句用于向表中插入新数据,有两种编写方式

1.无需指定插入数据的列名,只需要提供被插入的值即可:

insert into table_name values(value1,value2,value3,…);
2.需要指定列名及被插入的值

insert into table_name (column1,column2,…)
values (value1,value2,…);
实例:在部门表中插入数据(5,php开发部,北京)

insert into dept values(5,“php开发部”,“北京”);

此表deptno为主键并设置了自增,所以添加数据时,第一个数据可以设置为null值

3.5 UPDATE 语句
UPDATE语句用于更新表中的数据,基础语法如下:

updata table_name
set column1=value1,column=value2,…
where some_column=some_value_some;
注意:此代码中的WHERE语句是规定了哪条记录需要更新,如果省略了,所有数据都会被更新!

实例:修改部门表(dept)中的部门名称(dname)为"php开发部"的数据,修改为deptno=4,loc=“三区”

UPDATE dept SET deptno=4,loc=“三区” WHERE dname=“php开发部”;

注意:我们在更新数据是要格外注意,不能省略WHERE,如果省略了,所有deptno都变成4,所有loc都变成"三区"所以要慎重(可以试验一下~)

3.6 DELETE 语句
DELETE语句用于删除表中的记录

1.用于删除表中的行,基础语法如下:

delete from table_name where some_column=some_value;
注意:此处的WHERE如果省略了会删除所有数据!!!

实例:把部门表(dept)中dename="php开发部"所在行删除

delete from dept where dname=“php开发部”;
2.删除所有数据:可以在不删除表结构的情况下删除所有数据,基础语法如下:

delete * from table_name;
注意:删除是一定格外注意!因为删除不能恢复!!!(不信的小伙伴可以试一下~)

4.SQL高级
4.1 SELECT TOP,LIMT,ROWNUM子句
SELECT TOP 子句用来规定要返回的记录的数目,对于千万级数据表来说非常有用.不是所有数据库都支持SELECT TOP语句,Mysql支持LIMIT语句来选取指定条数的数据,Oracle可以通过ROWNUM来选取

1.SQL Server/MS Access 语法

SELECT TOP number|percent column_name(s)
FROM table_name;
2.MySQL 语法

select column_name(s) from table_name limit number;
3.Oracle 语法

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
这里我们只演示MySQL选取数据条数的实例:

查询部门表(dept)中前两条数据

select * from dept limit 2;

小贴士:LIMIT语句可叫为分页语句,一般都放在查询语句的末尾!

4.2 LIKE 操作符
LIKE操作符用于在WHERE语句中搜索列中的指定模式(也称为模糊查询)

LIKE ‘A%’:表示查询以’A’开头的数据

LIKE ‘%A%’:表示查询字段包含’A’的数据

LIKE ‘%A’:表示查询以’A’结尾的数据

基础语法如下:

select column_name(s)
from table_name
where column_name like pattern;
实例:查询部门表(emp)找出员工名字(ename)包含a的所有数据

select * from emp where ename like ‘%a%’;

4.3 IN操作符
IN操作符允许在WHERE子句中规定多个值,基础语句如下:

select column_name(s)
from tale_name
where column_name in (value1,value2,…);
实例:查询员工表(emp)中职位(job)为’经理’和’总监’的数据

select * from emp where job in(‘经理’,‘总监’);

4.4 BETWEEN 操作符
BETWEEN操作符选取介于两个值之间的数据范围内的值(可以是数值,文本或者日期)基础语法如下:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
实例:查询员工表(emp)中薪资(sal)在5000-10000之间的人的数据

select * from emp where sal between 5000 and 10000;

带有IN的BETWEEN操作实例:

查询员工表(emp)中薪资(sal)在3000-9000之间的数据,但是不包括总监(job)

select * from emp
where (sal between 3000 and 9000)
and ename not in(‘总监’);

带有文本值的BETWEEN操作实例:

查询员工表(emp)中员工名字(ename)介于’A’和’P’之间字母的所有员工数据

SELECT * FROM emp
WHERE ename BETWEEN ‘A’ AND ‘P’;

带有文本值的NOT BETWEEN 操作实例:

查询员工表(emp)中员工名字(ename)不介于’A’和’P’之间字母的所有员工数据

SELECT * FROM emp
WHERE ename NOT BETWEEN ‘A’ AND ‘P’;

带有日期值的BETWEEN操作实例:

查询员工表(emp)中员工入职日期(hireddate)介于2016和2019之间的所有员工数据

SELECT * FROM emp WHERE hireddate BETWEEN ‘2016-1-1’ AND ‘2019-12-30’;

注意:在不同上午数据库中,BETWEEN操作符会产生不同的结果!

在某些数据库中

BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
BETWEEN 选取介于两个值之间且包括两个测试值的字段。
BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。

因此，请检查您的数据库是如何处理 BETWEEN 操作符！

4.5 SQL别名
通过使用SQL,可以为表明称或列名称指定别名,创建别名是为了可读性更强,方便查询

起别名的时候可以用AS接别名,也可以用"空格"来接别名

列的SQL别名基础语法如下:

SELECT column_name AS alias_name
FROM table_name;
表的SQL别名基础语法如下:

SELECT column_name(s)
FROM table_name AS alias_name;
列名实例:把员工表中(emp)的员工名字(ename)和员工职位(job)设置别名

select ename as a,job as j from emp;

表名实例:设置员工表(emp)的别名

SELECT w.ename, w.job FROM emp AS w;
在以下情况下,使用别名很有用:

1.在查询中涉及超过一个表

2.查询中使用了函数

3.列名称很长或者可读性很差

4.需要把两个列或者多个列结合在一起

4.6 内连接(join)
jion用于把来自两个表或多个表的行结合起来,用于多表联查

包括:左连接(LEFT JOIN),右连接(RIGHT JOIN),内连接(INNER JOIN),外连接(OUTER JOIN)

以下是相关的7种模式

SOL JOIN

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段

最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）

SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行

实例:部门表(dept)中的主键deptno与员工表(emp)中的deptno关联,查询员工姓名(ename)和所在的部门的名称(dname)

select emp.ename,dept.dname
from emp inner join dept
on dept.deptno=emp.deptno;

不同的SQL JOIN

● INNER JOIN: 如果表中只有一个匹配,则返回行

● LEFT JOIN: 即使右表没有匹配,也从左表中返回所有行

● RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行

● FULL JOIN: 只要其中一个表中存在匹配，则返回行

4.7 SQL约束
SQL约束用于规定表中的数据规则

如果存在违反数据约束行为,行为会被约束终止

约束可以在创建表的时候规定(CREATE TABLE语句),或者在表创建之后规定(ALTER语句)

在SQL中有以下约束:

1.NOT NULL: 指示某列不能存储NULL值

2.UNIQUE: 保证每列的行必须有唯一的值

3.PRIMARY KEY: NOT NULL和UNIQUE的结合,保证每列有唯一标识,有助于快速查找特定数据

4.FOREIGN KEY:保证一个表中的数据匹配另一个表中的值的参照完整性

5.CHECK: 保证列中的值符合指定的条件

6.DEFAULT:规定没有给赋值时的默认值

1.NOT NULL 约束(非空约束)

NOT NULL 约束用来强制列不接受NULL值,也就意味着,若不向字段添加值,就无法插入新数据或更新数据

实例:是创建表中强制"ID"列和"pname"列不接受NULL值

create table Person(
ID int not null
pname varchar(20) not null
age int
tel vachar(30)
);
实例:在已经创建的表中添加NOT NULL约束,在上表中age添加NOT NULL 约束

alter table Person
modify age into not null;
实例:删除age字段的NOT NULL约束

alter table Preson
modify age int null;
2.UNIQUE 约束(唯一约束)

UNIQUE 约束唯一标识数据库表中的每条记录

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证

PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束

注意:每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束

实例:约束Person中的"ID"字段唯一

CREATE TABLE Persons
(
ID int NOT NULL,
pname varchar(20) NOT NULL,
Address varchar(255),
UNIQUE (ID)
);
3.PRIMARY KEY 约束(主键约束)

PRIMARY KEY 约束唯一标识数据库表中的每条记录

主键必须包含唯一的值

主键列不能包含 NULL 值

每个表都应该有一个主键，并且每个表只能有一个主键

实例:给Person表的"ID"字段设置为此表的主键

CREATE TABLE Persons
(
ID int NOT NULL,
pname varchar(20) NOT NULL,
Address varchar(255),
PRIMARY KEY (ID)
);
4.FOREIGN KEY(外键约束)

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)

可以预防破坏两个表之间的连接,也能防止非法数据插入外键列,因为必须指向另一个表中的值

实例:比如在新建一个Student表,在其创建时在Person "ID"字段添加FOREIGN KEY约束

CREATE TABLE Student
(
S_Id int NOT NULL,
StudentNo int NOT NULL,
ID int,
PRIMARY KEY (S_Id),
FOREIGN KEY (ID) REFERENCES Persons(ID)
);
5.CHECK 约束(范围约束)

CHECK 约束用于限制列中的值的范围

如果对单个列定义 CHECK 约束，那么该列只允许特定的值

如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制

实例:在Person表创建时对"ID"字段添加CHECK约束,规定"ID"字段必须是大于0的整数

CREATE TABLE Persons
(
ID int NOT NULL,
pname varchar(20) NOT NULL,
Address varchar(255),
CHECK (ID>0)
)
6.DEFAULT 约束(默认约束)

DEFAULT 约束用于向列中插入默认值

如果没有规定其他的值，那么会将默认值添加到所有的新记

实例:在 “Persons” 表创建时在 “City” 列上创建 DEFAULT 约束

CREATE TABLE Persons
(
P_Id int NOT NULL,
pname varchar(20) NOT NULL,
Address varchar(255),
City varchar(255) DEFAULT ‘Sandnes’
);
4.8 CREATE INDEX 语句 (创建索引)
可以在表中创建索引，以便更加快速高效地查询数据。

用户无法看到索引，它们只能被用来加速搜索/查询。

注意：更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。一般在数据多的列中创建索引比较常见(并不是创建索引越多,越高效)

创建索引的基础语法如下:

create index index_name
on table_name (column_name);
删除索引基础语法如下:

alter table table_name drop index index_name;
实例:在emp的ename上创建索引,索引名为a

create index a on emp(ename);
4.9 SQL日期(Date)
MySQL中最重要的内建日期函数:

函数 描述
NOW() 返回当前的日期和时间
CURDATE() 返回当前日期
CURTIME() 返回当前的时间
DATE()
提取日期或日期/时间表达式的日起部分

EXTRACT() 返回日期/时间的单独部分
DATE_ADD()

向日期添加指定的时间间隔
DATE_SUB() 从日期减去指定的时间间隔
DATEDIFF() 返回两个日期之间的天数
DATE_FORMAT()

用不同的格式显示日期/时间
MySQL在数据库中的日期或者时间的存储格式

● DATE - 格式：YYYY-MM-DD

● DATETIME - 格式：YYYY-MM-DD HH:MM:SS

● TIMESTAMP - 格式：YYYY-MM-DD HH:MM:SS

● YEAR - 格式：YYYY 或 YY

5.SQL函数
5.1 AVG() 函数
AVG()函数是用来查平均数的

基础语法如下:

select avg(column_name) from table_name;
实例:查询员工表(emp)中的平均薪资

select avg(sal) from emp;

练习:查询员工表(emp)和部门表(dept)中部门(loc)为二区的 平均薪资(sal)

子查询方式

SELECT AVG(sal) FROM emp
WHERE emp.deptno=(SELECT dept.deptno FROM dept WHERE loc=“二区”)
SELECT loc,AVG(sal) FROM emp INNER JOIN dept
ON emp.deptno=dept.deptno GROUP BY loc
HAVING loc=“二区”;
5.2 COUNT() 函数
COUNT()函数返回匹配的指定条件的行数(计数)

SQL COUNT(column_name)语法

COUNT(column_name)返回指定列值的数目(NULL不计数) 基本语法如下:

select count(column_name) from table_name;
SQL COUNT(*)语法

COUNT(*)函数返回表中的记录数 基础语法如下:

select count(*) from table_name;
SQL COUNT(DISTINCT column_name)语法

COUNT(DISTINCT column_name)函数返回指定列的不同值数目:

select count(distinct column_name) from table_name;
实例:查询员工表(emp)薪资(sal)超过4000的人数

select count(ename) from emp where sal>4000;

5.3 MAX() 函数
MAX()函数返回指定列最大的值 基础语法如下:

select max(column_name) from table_name;
实例:查询员工表(emp)中薪资(sal)最高的员工名称(ename)

SELECT MAX(sal),ename FROM emp ;

5.4 MIN() 函数
MIN()函数返回指定列的最小值,基础语法如下:

select min(column_name) from table_name;
实例:查询员工表(emp)中薪资(sal)最低的员工名称(ename)

select ename,min(sal) from emp;

5.5 SUM() 函数
SUM()返回数值列的总数,基础语法如下:

select sum(column_name) from table_name;
实例:查询员工表(emp)与部门表(dept),查询部门(loc)为二区的总薪资(sal)

SELECT SUM(sal) FROM emp
WHERE deptno=(SELECT deptno FROM dept WHERE loc=“二区”)

5.6 UCASE() 函数
UCASE() 函数把字段的值转换为大写 基础语法如下:

SELECT UCASE(column_name) FROM table_name;
实例:把员工表(emp)中的员工名字(ename)转为大写

select ucase(ename) from emp ;

5.7 LCASE() 函数
LCASE() 函数把字段的值转换为小写,基础语法如下:

SELECT LCASE(column_name) FROM table_name;
实例:把员工表(emp)中的员工名字(ename)转为小写

select lcase(ename) from emp ;
5.8 MID() 函数
MID() 函数用于从文本字段中提取字符,基础语法如下:

SELECT MID(column_name,start[,length]) FROM table_name;
参数 描述
column_name 必需 要提取字符的字段
start 必需 规定开始的位置
length 可选 要返回的字符数,若省略,则显示剩余文本
实例:从员工表(emp)中的员工名字(ename)中提取前三个字符

select mid(ename,1,3) from emp;

5.9 LEN() 函数
LEN() 函数返回文本字段中值的长度,基础语句如下:

SELECT LENGTH(column_name) FROM table_name;
5.10 ROUND() 函数
ROUND() 函数用于把数值字段舍入为指定的小数位数 基础语句如下:

SELECT ROUND(column_name,decimals) FROM TABLE_NAME;
参数 描述
column_name 必需 要舍入的字段
decimals 可选 规定要返回的小数位数