mysql数据库

目录

数据库概述

mysql数据库

结构化查询语言（SQL）

SQL-DDL

SQL-DML

SQL-DQL

多表设计_关联查询

---

数据库概述

1.为什么学习数据库：

1>实现数据持久化到本地；

2>使用完整的管理系统统一管理，可以实现结构化查询，方便管理。

2.数据库的定义

数据库（DataBase）为了方便数据的存储和管理，它将数据按照特定的

规则存储在磁盘上,就是一个存储数据的仓库。通过数据库管理系统，可

以有效的组织和管理存储在数据库中的数据。

3.数据库的相关概念

DB：数据库（DataBase）

存储数据的容器，它保存了一系列有组织的数据。

DBMS：数据库管理系统（DataBase Management System）

又称为数据库软件或数据库产品，用于创建或管理DB。

SQL：结构化查询语言（Structure Query Language）

用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据 库软件通用的语言。

4.根据不同的存储类型可以分为：

1>关系型数据库

关系型数据库管理系统称为RDBMS，R指Relation

Oracle:功能强大,收费.

MySQL 快捷、可靠 开源、免费

SQL Server(微软): 只能安装在Windows操作系统

DB2 (IBM):适合处理海量数据,收费.

2>非关系型数据库

MongdoDB

Redis

mysql数据库

1>MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目

前属于 Oracle旗下产品。MySQL 流行的关系型数据库管理系统。

● MySql是一种关系数据库管理系统。

● MySql软件是一种开放源码软件,你可以修改源码来开发自己的 Mysql 系统。

● MySql数据库服务器具有快速、可靠和易于使用的特点。

● MySql使用标准的sql语言,并且支持多种操作系统,支持多种语言.

● mysql商业版与社区版

● MySQL商业版是由MySQL AB公司负责开发与维护,需要付费才能使用

● MySQL社区版是由分散在世界各地的MySQL开发者、爱好者一起开发与维

护，可以免费使用

2>MySQL的安装(参考安装资料)

命令行方式连接mysql

登录：mysql [-hlocalhost -P3306]（本机可省略）-uroot -p（可以直 接写密码，不能有空格） -h：主机名 -P：端口号 -u：用户名 -p：密码 退出：exit

3>查看mysql数据库的版本

select version();（mysql命令）

4>MySQL的常用命令

查看当前所有的数据库：show databases;

选择指定的库：use 库名

查看当前的所有表：show tables;

查看其他库的所有表：show tables from 库名;

5>安装可视化客户端工具

SQLyog / Navicat

6>MySQL语法规范

不区分大小写，建议关键字大写，表名、列名小写

每句话用;

7>注释

单行注释：#注释文字

单行注释：-- 注释文字（要有空格）

多行注释：/* 注释文字 */

结构化查询语言（SQL）

结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统.

SQL优点：

不是某个特定数据库供应商专有的语是言，几乎所有DBMS都支持SQL简单易学，实际上强有力的语言，灵活使用可以进行非常复杂和高级的数据库操作

SQL-DDL

数据(结构)定义语言DDL(Data Definition Language)，是用于创建和修改数据库表结构的语言。

常用的语句：create ,alter,drop,rename

创建,删除数据库

创建数据库并设置编码格式

CREATE DATABASE [if not exists] 数据库名 [ CHARSET utf8]

删除数据库

DROP DATABASE 数据库名 / [IF EXISTS数据库名];

修改字符集

ALTER DATABASE 数据库名 CHARSET gbk;

数据库存储数据的特点

将数据放到表中，表再放到库中。

一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有

唯一性。

表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中“类”的

设计。

表由列组成，我们也称列为字段。所有表都是由一个或多个列组成的，每一列

类似java中的”属性” >

表中的数据是按行存储的，每一行类似于Java中的“对象”。

数据库表的基本概念

1、数据表

表（table）是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素。表的最简单形式是由行和列组成，分别都包含着数据。 每个表都有一个表头和表体，表头定义表名和列名 .表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。

2、记录

记录也被称为一行数据，是表里的一行。在关系型数据库的表里，一行数据是指一条完整的记录。

3、字段

字段是表里的一列，用于保存每条记录的特定信息。如客户订单表的字段包括“订单ID”、“姓名”、“客户ID”、“职务”、“上级”、“地区”、“运货商”、“国家”等。 数据表的一列包含了特定字段的全部信息。

设 计 表

对于具体的某一个表，在创建之前，需要确定表的下列特征：

表名(表信息) ，表中的字段 ，字段的数据类型和长度 ，哪些约束

设计表(数据类型)

char（n） 长度为n的定长字符串

varchar(n） 最大长度为n的可变长字符串

date 日期，包含年月日

datetime 年月日 时分秒

设计表(数据类型)

整数

TINYINT(1字节),SMALLINT(2字节),MEDIUMINT(3字节),INT(4字节),BIGINT(8字节)

浮点

float(4字节)，double(8字节)

数据类型(M,D)

M：精度，数据的总长度；

D：标度，小数点后的长度

Float(6,2)、Double(6,2)

设计表(数据类型)

BLOB是一个二进制大对象，可以容纳可变数量的数据,用于存储图片视频信息 有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。 它们只是可容纳值的最大长度不同。

TEXT列字符字符串

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

它们（TEXT和BLOB同）的长度：

Tiny：最大长度255个字符(2^8-1)

BLOB或TEXT：最大长度65535(2^16-1)

Medium：最大长度16777215(2^24-1)

LongText 最大长度4294967295(2^32-1)

主键：

在一张表中代表唯一的一条记录,不能为空,不能重复

约束:

PRIMARY KEY 设置主键约束，不能为空，不能重复，一个表中只有一个唯一约束

NOT NULL 不能为空约束

UNIQUE 唯一性约束

检查约束 设置条件 CHECK(height>100.0)

外键约束

主键自动增长 ：AUTO_INCREMENT

默认值 ：DEFAULT default_value

字段注释: comment '注释

创建表语法:

CREATE TABLE 表名(列名 数据类型 [约束] [默认值] [ 注释],......)

CREATE TABLE t_user(

id INT [PRIMARY KEY NOT NULL AUTO_INCREMENT],

number INT(5) NOT NULL,

stuname VARCHAR(10) NOT NULL,

age INT(3) CHECK(age>18),

birthday DATE,

weight DOUBLE,

opertime datetime,

[CONSTRAINT 约束名 约束规则]

)

删除表，修改表名

删除表

DROP TABLE [if exists ]表名

DROP TABLE IF EXISTS t_student;

修改表名

RENAME TABLE 旧表名 TO 新表名

RENAME TABLE t_student TO student;

复制表结构

CREATE TABLE 新表名 LIKE 被复制表名;

CREATE TABLE stu LIKE student;

修改表结构

添加主键约束

ALTER TABLE 表名 ADD PRIMARY KEY(列名)

ALTER TABLE stu ADD PRIMARY KEY(num)

ALTER TABLE 表名 DROP PRIMARY KEY

ALTER TABLE stu DROP PRIMARY KEY

设置自动增长

ALTER TABLE 表名 MODIFY 列名 类型 AUTO_INCREMENT;

ALTER TABLE stu MODIFY num int AUTO_INCREMENT;

删除自动增长

ALTER TABLE 表名 MODIFY 列名 类型 ;

ALTER TABLE stu MODIFY num int ;

设置不能为空

ALTER TABLE 表名 MODIFY 列名 类型 NOT NULL ;

ALTER TABLE stu MODIFY name varchar(4) NOT NULL ;

ALTER TABLE 表名 MODIFY 列名 类型 NULL ;

ALTER TABLE stu MODIFY name varchar(4) NULL;

添加唯一约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名)

ALTER TABLE stu ADD CONSTRAINT uni_name UNIQUE(name)

ALTER TABLE 表名 DROP INDEX约束名;

ALTER TABLE stu DROP INDEX uni_name

添加检查约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(条件)

ALTER TABLE stu ADD CONSTRAINT check_height CHECK(height>100)

ALTER TABLE 表名 DROP CHECK 约束名;

ALTER TABLE stu DROP CHECK check_height;

修改表结构

添加列

ALTER TABLE 表名 ADD 列名 数据类型

ALTER TABLE stu ADD weight float(5,2);

ALTER TABLE 表名 ADD 列名 数据类型 FIRST

ALTER TABLE stu ADD weight float(5,2) FIRST;

ALTER TABLE 表名 ADD 列名 数据类型 AFTER 列名

ALTER TABLE stu ADD weight float(5,2) AFTER height;

删除列

ALTER TABLE 表名 DROP 列名

ALTER TABLE stu DROP weight

修改列名

ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型

ALTER TABLE stu CHANGE weight t_weight float(5,2)

修改列的数据类型

ALTER TABLE 表名 MODIFY 列名 新数据类型

ALTER TABLE stu MODIFY t_weight double(5,2)

SQL-DML

数据操纵语言DML（Data Manipulation Language）

常用语句： insert,delete,update

插入数据

方式1: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n);

INSERT INTO stu(name)VALUES('李四');

方式2: INSERT INTO 表名 set 列名1=值1,..列名n=值n;

INSERT INTO stu set name='张三';

方式3: INSERT INTO 表名(列1,列2……,列n) VALUES(值1,值2…..,值n),(值1,值2…..,值n);

INSERT INTO stu(name)VALUES('时光'),('金克斯 ');

方式4:INSERT INTO 表名(列1,列2……,列n) 查询语句(查询的列数与插入列数匹配)

CREATE TABLE stu LIKE t_student

INSERT INTO stu(name) SELECT name from t_student

修改数据

UPDATE 表名 SET 列名 = ‘新值’ WHERE 条件

UPDATE stu SET name = '四哥' WHERE id =1;

删除数据

DELETE FROM 表名 WHERE 条件

TRUNCATE TABLE 表名;清空整张表

DELETE FROM stu where id =1;

SQL-DQL

DQL（Data Query Language）数据查询语言查询是使用频率最高的一个操作，可以从一个表中查询数据，也可以从多个表中查询数据。

基础查询

语法：

select 查询列表 from 表名;

特点：

查询列表可以是：表中的字段、常量、表达式、函数

查询的结果是一个虚拟的表格

查询结果处理：

查询常量值 SELECT 100;

SELECT 100,NAME FROM stu

查询表达式：select 100*98;

SELECT 100*98,NAME FROM stu

查询函数：select 函数; / 例如version()

SELECT VERSION()

特定列查询:select column1,column2 from table

SELECT LENGTH(name) FROM stu

SELECT CHAR_LENGTH(name) FROM stu

全部列查询: select * from table

排除重复行: select distinct column1,column2 from table

算数运算符:+ - * /

查询结果处理：

函数:类似于java中的方法，将一组逻辑语句事先在数据库中定义好,可以直接调用

好处：

隐藏了实现细节

提高代码的重用性

调用：select 函数名(实参列表) [from 表];

分类：

单行函数：如concat、length、ifnull等

分组函数：做统计使用，又称为统计函数、聚合函数、组函数

单行函数

字符函数

length()：获取参数值的字节个数

char_length()获取参数值的字符个数

concat(str1,str2,.....)：拼接字符串

upper()/lower()：将字符串变成大写/小写

substring(str,pos,length)：截取字符串 位置从1开始

instr(str,指定字符)：返回子串第一次出现的索引，如果找不到返回0

select intr(name,'i') from t_student

trim(str)：去掉字符串前后的空格或子串,trim(指定子串 from 字符串)

SELECT num,TRIM('b' FROM NAME) FROM t_student

lpad(str,length,填充字符)：用指定的字符实现左填充将str填充为指定长度

select lpad(name,5,'c') from t_student

rpad(str,length,填充字符)：用指定的字符实现右填充将str填充为指定长度

select rpad(name,5,'c') from t_student

replace(str,old,new)：替换，替换所有的子串

单行函数

逻辑处理

case when 条件 then 结果1 else 结果2 end; 可以有多个when

select

num,name,

case when weight>=150 then '偏胖' else '正常' end

from t_student

select

num,name,

case when weight>=150 then '偏胖'

when weight>=80 and weight<150 then '正常'

else '偏瘦' end

from t_student

ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值

select ifnull(height,'暂未登记') from t_student

if函数：if else的效果 if(条件,结果1,结果2)

select if(height>175,'高个','低个') from t_student

数学函数

round(数值)：四舍五入

select round(列名)

ceil(数值)：向上取整，返回>=该参数的最小整数

floor(数值)：向下取整，返回<=该参数的最大整数

truncate(数值,保留小数的位数)：截断，小数点后截断到几位

mod(被除数,除数)：取余，被除数为正，则为正；被除数为负，则为负

rand()：获取随机数，返回0-1之间的小数

日期函数

select num,name,now() from t_student

now()：返回当前系统日期+时间

select num,name,curdate() from t_student

curdate()：返回当前系统日期，不包含时间

select num,name,curtime() from t_student

curtime()：返回当前时间，不包含日期

select num,name,year(reg_time) from t_student

select num,name,month(reg_time) from t_student

select num,name,day(reg_time) from t_student

select num,name,hour(reg_time) from t_student

select num,name,minute(reg_time) from t_student

select num,name,second(reg_time) from t_student

可以获取指定的部分，年、月、日、小时、分钟、秒

YEAR(日期),MONTH(日期),DAY(日期) ,HOUR(日期) ,MINUTE(日期) SECOND(日期)

select str_to_date('2021-7-6',)

str_to_date：将日期格式的字符转换成指定格式的日期

SELECT STR_TO_DATE('1998-3-2', '%Y-%m-%d');

date_format：将日期转换成字符串

SELECT DATE_FORMAT(NOW(), '%y年%m月%d日') AS output ;

datediff(big,small)：返回两个日期相差的天数

select datediff(date_format(now(),'%Y-%m-%d'),birthday) from t_student

日期格式

%Y 年，4 位

%m 月，数值(00-12)

%d 月的天，数值(00-31)

%H 小时 (00-23)

%i 分钟，数值(00-59)

%s 秒(00-59)

%f 微秒

%T 时间, 24-小时 (hh:mm:ss)

%j 年的天 (001-366)

%w 周的天 （0=星期日, 6=星期六）

分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计数

（非空）

1.sum，avg一般用于处理数值型max，min，count可以处理任何类型

select sum(weight) from t_student

select sum(weight),sex from t_student group by sex

select avg(weight) from t_student

select max(weight) from t_student

select min(weight) from t_student

select count(*/主键/指定列) from t_student

2.以上分组函数都忽略null值

3.count函数的一般使用count（*）用作统计行数

4.和分组函数一同查询的字段要求是group by后的字段

条件查询

使用WHERE 子句，将不满足条件的行过滤掉，WHERE 子句紧随 FROM 子句。

语法:select <结果> from <表名> where <条件>

比较

select * from t_student where sex='男'

select * from t_student where sex!='男'

select * from t_student where sex<>'男'

select * from t_student where height>=180

=, != 或<>, >, <, >=, <=

逻辑运算

select * from t_student where sex='男' and height>=180 and weight>=100

select * from t_student where sex='男' and height>=180 or weight>=100

select * from t_student where height not in(180,175)

and 与 or 或 not 非

模糊查询

以J开头 向后匹配任意个字符

select * from t_student where name like 'J%'

以J开头 向后只匹配一个字符

select * from t_student where name like 'J_'

LIKE ：是否匹配于一个模式 一般和通配符搭配使用，可以判断字符型数值或数值型.

通配符： % 任意多个字符，包含0个字符 _ 任意单个字符

select * from t_student where height>= 175 and height <=180

select * from t_student where height between 175 and 180

between and 两者之间,包含临界值；

select * from t_student where height in(180,175)

select * from t_student where height not in(180,175)

in 判断某字段的值是否属于in列表中的某一项

select * from t_student where height is null

select * from t_student where height is not null

IS NULL（为空的）或 IS NOT NULL（不为空的）

1、UNION 的语法如下：

[SQL 语句 1]

UNION

[SQL 语句 2]

2、UNION ALL 的语法如下：

[SQL 语句 1]

UNION ALL

[SQL 语句 2]

当使用union 时，mysql 会把结果集中重复的记录删掉，而使用union all ， mysql 会把所有的记录返回，且效率高于union 。

将多条查询结果合并，会去掉重复的行

select num,name,sex from t_student where sex='男'

union

select num,name,sex from t_student where height>170

将多条查询结果合并，不会去掉重复的行

select num,name,sex from t_student where sex='男'

union all

select num,name,sex from t_student where height>170

排序

查询结果排序,使用 ORDER BY 子句排序 order by 排序列 ASC/DESC

asc代表的是升序，desc代表的是降序，如果不写，默认是升序

order by子句中可以支持单个字段、多个字段、表达式、函数、别名

数量限制

limit子句：对查询的显示结果限制数目 (sql语句最末尾位置)

SELECT * FROM table LIMIT offset rows;

SELECT * from table LIMIT 0,5;

select * from t_student order by num asc

select * from t_student where num>1 order by height desc,weight asc

select * from t_student where sex='男' order by height desc limit 0,1

分组查询

语法：

select 分组函数，列（要求出现在group by的后面）

from 表

[where 筛选条件]

group by 分组的列表

[having 分组后的筛选]

[order by 子句]

注意：查询列表比较特殊，要求是分组函数和group by后出现的字段

分组查询中的筛选条件分为两类:

数据源 源位置 关键字

分组前筛选 原始表 group by子句的前面 where

分组后筛选 分组后的结果集 group by的后面 having

select sex,count(*) from t_student group by sex

select sex,avg(height) from t_student group by sex

select sex,count(*)c from t_student where height>160 group by sex having count(*)>2

select sex,count(*)c from t_student where height>160 group by sex having count(*)>2 order by count(*) limit 0,1

gourp by 和 having 一般一起出现，order by 和 limit 可以单独出现

子查询

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询.

分类：

按子查询出现的位置：

insert into后面：支持行子查询，表子查询

insert into stu(name,sex,birthday,height,mobile,reg_time) select name,sex,birthday,height,mobile,reg_time from t_student

delete update 后面where (注意：子查询不能是当前的表)

update t_student set name='aaa' where num =(select num from stu where height = 180)

delete t_student set name='aaa' where num =(select num from stu where height = 180)

select后面：仅仅支持标量子查询

select ts.num,(select t.name from t_student t where t.name = ts.name) from t_student ts

from后面：支持表子查询

where或having后面：支持标量子查询，列子查询，行子查询

select * from t_student where height = (select max(height) from t_student)

select * from t_student where height in (select height from t_student where height>=175)

select * from t_student where (height,weight)=(select max(height),max(weight) from t_student)

select * from (select sex,count(*)c from t_student group by sex)t where t.c>1

按功能、结果集的行列数不同：

标量子查询（结果集只有一行一列）

列子查询（结果集只有一列多行）

行子查询（结果集有一行多列）（较少）

表子查询（结果集一般为多行多列）

子查询

子查询在SELECT语句内部可以出现SELECT 语句。

语句结果可以作为外部语句中条件子句的一部分，也可以作为外部查询的临时

表。

例如:查询成绩最高的学生信息

多表设计_关联查询

为什么需要多表关联

数据冗余-----拆分

数据库设计范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的

规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计

要求的总结。

目前关系数据库有5种范式：第一范式（1NF）、第二范式（2NF）、第

三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范

式（5NF，又称完美范式）。

满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步

满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般

来说，数据库只需满足第三范式(3NF）就行了。

数据库设计范式

1．第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

2． 第二范式就是要有主键，要求其他字段都依赖于主键。

没有主键就没有唯一性，没有唯一性在集合中就定位不到这行记录，所以要主键。其他字段为什么要依赖于主键？因为不依赖于主键，就找不到他们。更重要的是，其他字段组成的这行记录和主键表示的是同一个东西，而主键是唯一的，它们只需要依赖于主键，也就成了唯一的。

3.第三范式就是要消除传递依赖，方便理解，可以看做是“消除冗余”。

外键：引用另外一个数据表的某条记录。

外键列类型与主键列类型保持一致

数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键（

foreign key）建立起来

的。

create table score(

cid int not null auto_increment primary key,

score int,

sid int,

CONSTRAINT 约束名 foreign key(sid) references student(sid)

);

添加外键约束

ALTER TABLE 表名 ADD [CONSTRAINT 约束名] FOREIGN KEY(外键列) REFERENCES 关联表(主键);

删除外检键

ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名

1、当主表中没有对应的记录时，不能将记录添加到从表

2、不能更改主表中的值而导致从表中的记录孤立

3、从表存在与主表对应的记录，不能从主表中删除该行

4、删除主表前，先删从表

关联查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

按功能分类：

内连接

等值连接

非等值连接

自连接

外连接

左外连接

右外连接

关联查询

内连接(inner join)

把满足了条件的两张表中的交集数据查询出来

语法：Select 结果 from 表1，表2 where 表1.column1 = 表2.column2

多表关联

左外连接(left join)

select 结果 from

表1 left join 表2 on

表1.column1 = 表2.column2

右外连接(right join)

select 结果 from

表1 right join 表2 on

表1.column1 = 表2.column2