MySQL数据库知识

数据库服务器:

数据库是按照一定的形式组织存储数据,便利数据的操作----增删改查

1.数据库发展历史

网状数据库  ->  层次型数据库  ->  关系型数据库  ->  非关系型数据库(NoSQL)  

2.关系型数据库逻辑结构

Server  ->  Database  ->  Table  ->  Row  ->  Column

(服务器->数据库->数据表->行->列)

一.服务器端

负责存储维护数据,管理数据

从硬件看:就是一台计算机

从软件看:提供各种服务

通过IP地址/域名找到这台计算机,使用端口号找到对应的服务

启动文件位置:c:/xampp/mysql/bin/mysqld.exe

二.客户端

连接服务器,操作服务端的数据

客户端文件:c:/xampp/mysql/bin/mysql.exe

两种形式:DOS窗口/可视化工具

使用客户端连接服务器端:

mysql.exe         -h127.0.0.1 -p3306 -uroot -p    //简化:mysql -uroot(结尾不能加分号)

-h        host        127.0.0.1/localhost        自己的电脑的IP

-p        port        端口

-u        user        用户名,mysql默认有一个管理员账户root

-p        possward        密码,xampp下MySQL的密码默认为空

查看现在mysql的管理员:

三.SQL语句

结构化查询语言,专门用来操作数据库的语言,而且是一个标准语言,可以操作任何数据库

1.运行方式

(1).交互模式(适用于临时性的查看数据)

在客户端输入一行,点击回车,服务器端就会执行一行

(2).脚本模式(适用于批量的操作数据)

客户端把执行的命令是写在一个脚本文件中,然后一次性的提交给服务器执行

mysql -uroot<拖拽脚本文件      回车没有任何显示代表成功

2.SQL命令语法规范

一条SQL命令可以跨越多行,以英文的分号作为结束

SQL命令不区分大小写,建议关键字大写,非关键字小写

假设某一条命令出现语法错误,则此命令往后都不会执行

注释分为:单行注释(#..)和多行注释(/*...*/),注释的代码不会被服务器执行

四.SQL分类

DML:数据库的操纵语言,就是对数据库的操作(新增,修改,查询,删除)

DDL:数据库的定义语言,比如说创建表

DCL:数据库的控制语言,比如说权限管理:grant(授权)/revoke(收权)

DQL:数据库的查询语言,比如说进行复杂的查询语句

五.数据库的常见操作

1.新建库

//丢弃数据库,如果存在的话

drop database if exists 数据库名

//确定此刻电脑没有这个数据库

create database 数据库名字

//进入数据库

use 数据库名称

//设置新创建的库的编码格式(防止出现乱码)

create database 数据库名字 (default character set utf8)

注意:以下命令只会执行一段时间,只修改一次编码格式,临时防止中文乱码

set names gbk;

mysql中文乱码产生的原因:

默认使用Latin-1编码格式导致的

解决中文乱码:

1)脚本文件另存为的编码为utf-8

2)客户端连接服务器的编码为utf-8

3)服务器端创建数据库存储字符的字符为utf-8

//查看创建的数据库编码格式

show create database 数据库名

//查看当前数据库编码格式

show database 数据库名

2.查询此电脑中的所有数据库

show databases

3.查看MySQL版本号

select version()

4.删除数据库(慎重!!!!)

drop databases 数据库名字

5.退出数据库

exit/quit

六.数据表的操作

1.操作数据表的前提,指定表所处的数据库

use 数据库名

补充:查看当前运行的是哪个数据库

select database()

2.查询表

show tables

3.创建表

create table 表名(字段名称 字段类型(字段长度),字段2,字段3...)

字段长度:int类型的限制不住,无需设置长度,其他的字符串必须在规定长度内否则报错

4.修改表

alter table 表名 add column 列名 类型(值)

alter table 表名 drop column 列名

5.删除表

drop table 表名

6.描述表(查看表结构,有多少列)

desc 表名

七.记录的常见操作

1.查询表里的所有记录

select * from 表名

2.新建记录

insert into 表名 values (值1,值2...)

注意:

1)values后面括号里要写几个值需要参考表里字段的个数(个数要匹配)

2)值的意义:要和字段的顺序位置匹配,每个值交给每个对应的字段来保存

3)值的写法细节:如果值中有varchar数据类型,注意要写引号

3.修改记录某一条id对应的记录,修改多个列用逗号隔开

update 表名 set 字段=新值(where id =1 )

4.删除某一条id对应的记录

delete from 表名(where id = 10)

5.排序

select * from 表名 order by 表里的某一列 desc;

6)记录总数

select count(*) from 表名

如何存储中文字符:

GB2312:对常用的6000多汉字进行了编码,兼容ASCII

GBK:对2万多汉字进行了编码,兼容GB2312

Unicode:对世界上主流国家常用的语言进行了编码,具体有三种存储方案,分别是utf-8,utf-16,urf-32

小练习:

补充:列类型(注:标红的是常用的)

在创建数据表的时候,指定的列存储的数据类型

1.数值型----值可以不加引号

tinyint 微整型,占1个字节,范围:-128~127

smallint 小整型,占2个字节,范围:-32768~32767

int 整型,占4个字节,范围:-2147483648~2147483647

bigint 大整型,占8个字节

float 单精度浮点型,占4个字节,存储的数字越大精度越低

double 双精度浮点型,占8个字节,存储的数字越大精度越低

decimal(M,D) 定点小数,小数点不会发生变化,M代表总的有效位数,D代表小数点后的有效位数

boolean 布尔型,只有两个值,分别是true和false,用于存储只有两个值的数据,例如:性别...

使用的时候自动转为tinyint;把true转为1,false转为0,也可以直接插入1或者0

true和false是关键字,使用的时候不能加引号

2.日期时间型----值必须加引号

date  日期型 '2022-01-04'

time  时间型 '15:21:30'

datetime  日期时间型 '2022-01-04 15:21:30'

3.字符串型----值必须加引号

varchar(M) 变长字符串,有几个剩余空间都用1个空格:\0来填充;不会产生空间的浪费,数据的操作速度相对慢,常用于存储变化长度的数据,例如:文章的标题,内容等;M的最大值是65535

char(M) 定长字符串,有几个剩余空间就用几个空格:\0来填充;使用不当,可能会产生空间浪费,数据的操作速度相对快,常用于存储固定长度的数据,例如:手机号码,身份证号码等;M的最大值是255

text(M) 大型变长字符串,M的最大值是2G

	char(5)	varchar(5)
a	a\0\0\0\0	a\0
ab	ab\0\0\0	ab\0
一二三	一二三\0\0	一二三\0

数据库中如何存储图片呢?        ----直接写一个列名 列类型(长度大一点)

八.列约束

概述:MySQL可以对插入的值进行验证,只有符合条件才允许插入;通过不同的方式,给不同的字段添加不同的约束,实现不同的表的效果

位置:列类型的后面

常见的字段约束:非空约束/唯一约束/主键约束等

通常在设计表时,就已经设计好了表里的一些字段约束

自增列:auto_increment

注意:会自动增长,在插入值的时候只需要赋值为null,就会获取最大值然后加1插入

必须配合着整数形式的主键列使用  允许手动赋值!!!!                

id int primary key auto_increment

1.主键约束(primary key)

如果给一个列添加了主键约束,那么这个列就是主键(会按照从小到大进行排列),主键的特点是唯一且不能为空(null),通常情况下,每张表只能有一个主键约束

说明:一般是给编号列,这样会加快数据的查询速度

null:表示空,是一个暂时无法确定的值,例如:暂时无法确定一个员工的手机号码,无法确定一个学生的成绩... null是关键字,使用的时候不能加引号

2.非空约束(not null)

哪个字段添加了非空约束,那么那个字段的值不允许插入null,使用not null来实现

create table 表名(password varchar(10) not null)

3.唯一约束(unique)

注意:

1.声明了唯一约束的列不允许插入重复的值,但是允许为null,甚至多null也可以;

2.可能会影响排序

3.一个表中可以有多个唯一约束

4.默认约束(default)

哪个字段添加了默认约束,那个字段的值就有了默认值,使用default来实现

实现方式1:插入值的时候,没有出现的列自动应用默认值

实现方式2:在要使用默认值的地方直接写default关键字,会自动调用默认值

5.检查约束(check) 

注:mysql不支持,认为会严重影响数据的插入速度        解决方法:交给后端那些语言

哪个字段添加了默认约束,那个字段的值就要通过检查才能保存成功(了解即可)

6.外键约束-foreign key(外键列) references 主键所在的表(主键列)

注意1:为了节省内存,要插入的值必须得在另一个表的主键列中出现过

注意2:外键列要和对应的主键列类型要保持一直

注意3:子表里的主键的值,必须取自于主表

注意4:主表里的记录想要删除时,必须保证子表里没有引用才行 

九.基础函数

SQL中可以通过不同条件来查询数据;满足条件的数据查出来,不满足的就查不出来

1.LOWER & UPPER(转小写&转大写)

2.LENGTH & SUBSTR & CONCAT(数据长度&截取&拼接)

 

3.REPLACE & IFNULL & ROUND(替换&如果为空进行数字替换&四舍五入取整)

 

4.日期函数 & 转义字符

 

十.条件查询

1.简单查询

1.1查询特定的列

select 列名1,列名2... from 数据表

1.2查询所有的列

select * from 数据表

1.3给列起别名

select 列名1 as 别名,列名2 as 别名 from 数据表

注:as可以省略不写

1.4查询时执行计算

select 列名(+ - * /) 别名 from 数据表

1.5显示不同的记录

去除重复的:

select distinct 列名 from 数据表

注意:不加排序规则,默认是按升序排列

1.6查询的结果排序

降序  select * from 数据表 order by 列名 desc

升序  select * from 数据表 order by 列名 asc

如果数据相同,按照备选条件排序  select * from 数据表 order by 列名1 asc,列名2 asc

1.7条件查询

where 当什么时候

可以结合: >   <  >=  <=  =  !=        and/&&并且        or/||或者   is null/not is null          in()/not in()使用

1.8模糊条件查询

包含的两个字符:        % 匹配任意个字符        _匹配任意一个字符

注:以上两个字符必须结合关键字like使用

'%a%'前后包含a的记录

'_a%'第二个字符是a的记录

1.9分页查询

查询的结果中有太多数据,一次显示不完可以做成分页

条件:当前的页码以及每页显示的数据量

like:查询的结果中有太多数据,一次显示不完可以做成分页
条件:当前的页码以及每页显示的数据量
算法:每页开始查询的值=(当前的页码-1)*每页显示的数据量
语法:select * from 数据表 limit 开始查询的值,每页显示的数据量
注意:limit后的两个值必须写数字,不能加引号

2.复杂查询

2.1聚合查询

聚合函数:是一个功能体,需要提供若干个数据返回结果

求总数量:count()        求和:sum()        平均:avg()        最大:max()        最小:min()        获取日期中的年份部分:year()

2.2分组查询group by 

分组查询:建立在聚合查询基础之上的,通常只查聚合函数和分组条件

分组后的过滤:having                group by ... having

2.3子查询

多个查询命令的组合,把一个的结果作为另一个的条件使用

2.4多表查询

select 一张表.列名,另一张表.列名 from 数据表1,数据表2 where 一张表.外键对应的列=另一种表.主键的列;

把两个表的数据都拼接整合在一起:

笛卡尔积:下面这种查询表的方式:

select * from 表1,表2(表和表之间用逗号分隔)

注意1:一般笛卡尔积没有实际的业务意义,但是多表查询都是先生成笛卡尔积,再进行数据的筛选过滤

注意2:有的公司禁止3张表以上的联查,原因:查询过程中,现在内存中构建一个大大的结果集,然后再进行数据的过滤;在这个构建过程中,所使用的内存资源,过滤的判断都会既浪费时间又浪费资源

2.4.1内连接(inner join...on)

和之前的查询结果一致

select 一张表,另一张表 from 数据表 inner join 另一张数据表 on 一张表外键对应的列=一张表主键对应的列;

2.4.2左外连接(left outer join...on)

显示左侧表中所有的记录,先写的哪个表哪个表就是左表

select 一张表,另一张表 from 数据表 left outer join 右表 on 一张表外键对应的列=一张表主键对应的列;
注:左外连接中outer关键字可以省略

2.4.3右外连接(right outer join...on)

显示右侧表中所有的记录,后写的哪个表哪个表就是右表

select 一张表,另一张表 from 数据表 right outer join 右表 on 一张表外键对应的列=一张表主键对应的列;

注:右外连接中outer关键字可以省略

2.4.4全连接 full join on(但是,mysql不支持)

同时显示左侧和右侧所有的记录,就是把左外和右外结果进行联合
    union 联合后,合并相同的记录
    union all 联合后,不合并相同的记录
格式:左外连接 关键字:union/unionall 右外连接

十一.事务

1.概述:英文叫transaction,主要作用是用来保证多条SQL语句成功运行,要么全成功要么全失败

2.四大特征:

原子性:同一个事务里的多条SQL语句是一个原子,密不可分的,要么全成功要么全失败

一致性:保证多台服务器里的数据是一致的

隔离性:数据库为了提高操作的效率允许高并发的访问,隔离性保证了数据的安全性(采用锁机制)

持久性:是指我们对数据库的操作(增删改)是持久性的

3.隔离级别

read uncommitted: 读未提交,安全性最差,但是效率高.

read committed: 读已提交,安全性有所提升,但是效率降低一些.也是Oracle数据库的默认隔离级别

repeatable read : 可重复读,安全性有所提升,但是效率又会低一些.也是MySQL数据库的默认隔离级别

Serializable: 串行化,安全性最高,但是性能最低

4.事务管理的方式

方式1:使用MySQL数据库为我们提供的自动事务管理,默认会为每条SQL语句提供事务

方式2:手动管理事务,必须有两个过程:开启事务  ... 结束事务(commit提交 / rollback回滚,相当于这个事务为执行)

十二.表关联association

概念:表关联代表了表之间的关系，如：部门和员工，商品和商品分类，老师和学生，教室和学生。同时，也要知道，表并不都有关系，它们形成自己的小圈子。如商品和商品详情一圈，部门和员工一圈，出圈就可能没关系了，如商品和员工无关，商品和学生无关。

表的关系分为四种:

一对一 one to one 比如:员工和员工编号

一对多 one to many 比如:部门和员工

对多一 many to one 一对多反过来的,比如员工和部门

多对多 many to many 比如老师和学生

表的设计特点:

1)表都以s结束,标识复数

eg:create table courses

2)字段多以表的首字母作为开头,在多表联查中,方便标识出是哪个表的字段(做到"见名知意")

eg:cno varchar(5) not null

插入测试数据:(直接把表名-字段名-对应的值的内容整合在一起)

eg:INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825);