佛山网站建设模板,最新山西太原阳性,贵州建设厅网站办事大厅,视频网站设计模板数据库简介
关系型数据库#xff1a;Mysql 、Oracle 、SqlServer.... DB2 达梦
非关系型数据库#xff1a;Redis 、MongoDB...
MySQL是一个关系型数据库管理系统#xff0c;由瑞典MySQL AB 公司开发#xff0c;属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管…数据库简介
关系型数据库Mysql 、Oracle 、SqlServer.... DB2 达梦
非关系型数据库Redis 、MongoDB...
MySQL是一个关系型数据库管理系统由瑞典MySQL AB 公司开发属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一在 WEB 应用方面MySQL是最好的 RDBMS (Relational Database Management System关系数据库管理系统) 应用软件之一。
MySQL是一种关系型数据库管理系统关系数据库将数据保存在不同的表中而不是将所有数据放在一个大仓库内这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策分为社区版和商业版由于其体积小、速度快、总体拥有成本低尤其是开放源码这一特点一般中小型网站的开发都选择 MySQL 作为网站数据库。
SQL(Structured Query Language)
结构化查询语言
SQL包括了所有对数据库的操作主要是由4个部分组成
1.数据定义又称为“DDL语言”定义数据库的逻辑结构包括定义数据库、基本表、视图和索引4部分。
2.数据操纵又称为“DML语言”包括插入、删除和更新三种操作。
3.数据查询又称为“DQL语言”包括数据查询操作。
4.数据控制又称为“DCL语言”对用户访问数据的控制有基本表和视图的授权及回收。
5.事务控制又称为“TCL语言”包括事务的提交与回滚。
登录数据库
mysql -u root -p root
Mysql简单命令
查询所有数据库
show databases;
选择使用数据库
use 数据库名称;
创建数据库
create database 数据库名称;
删除数据库
drop database 数据库名称;
查看数据库编码
show create database 数据库名称;
查看当前数据库下面所有的表格
show tables;
创建表格
create table 表名称 (字段 字段类型(长度));
create table 表名称 (字段 字段类型(长度),字段 字段类型(长度),字段 字段类型(长度)........);
create table user (id int(3),name varchar(10));
create table person (id int(4),name varchar(10),phone varchar(11),address varchar(30));
查看表结构
desc user;
desc 表名称;
删除表格
drop table user;
drop table 表名称;
给表格中添加数据 insert into person values (1,admin,13988888888,taiyuan);//全字段 insert into person(id,name) values (1“张三”)
若表名后面什么也不写values后面则写全字段按顺序来字段类型不能乱
若指定添加则在表名称后面写清楚要添加的字段同时values后面要和字段相对应 insert into 表格名称 values (字段1,字段2....);
查询表格中所有数据 select * from person;
修改表结构选
语法修改表字段名 ALTER TABLE 表名称 CHANGE 原列名 想改成的列名 列类型;
ALTER TABLE test CHANGE id idd INT(10);
修改表名字
语法1 ALTER TABLE 原表名称 rename 想改成的表名称;
alter table test rename testt;
语法2
RENAME TABLE 原表名称 TO 想改成的表名称;
RENAME TABLE test TO testt;
修改字段类型一般不修改字段类型
如varchar 修改成 int
Update 表名称 set 要修改的字段 null //一定要赋值为null 不能是空字符串
alter table 表名称modify column 修改的字段名称 要修改成的字段类型(长度) ;
update user set namenull
alter table user modify column name int(20) ;
增加字段
alter table 表名 add字段 类型(长度);
alter table user add phone int(11);
删除字段
alter table 表名 drop 字段名
alter table user drop phone;
MySQL是一种关系型数据库管理系统它支持多种数据类型包括整数、浮点数、定点数、字符串、二进制和日期/时间类型。在本文中我们将列出MySQL中所有的字段类型并解释每种类型的含义、所占用的字节、存储范围、最大存储值以及使用场景。
整数类型包含(TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT)适用于存储整数如用户ID、年龄等。
浮点数类型(FLOAT、DOUBLE)适用于存储小数如商品价格、订单金额等。
定点数类型(DECIMAL)适用于存储精度要求高的数值如货币金额等。
字符串类型(CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT)适用于存储文本数据如用户名、地址等。
二进制类型(BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB)适用于存储二进制数据如图片、音频等。
日期和时间类型(DATE、TIME、DATETIME、TIMESTAMP)适用于存储日期和时间相关的数据如订单创建时间、用户注册时间等。 CHAR与VARCHAR的区别
1.CHAR是定长的范围是0~255char会开辟固定的长度值超出范围将被截短值小于范围的用空格填充。
2.VARCHAR是可变长度范围0~65535varchar是数据占多长就多长不会来填补但是会多一个字节来存储其字符的长度
3.它们之间的不同处在于MySQL数据库处理指示器的方式CHAR把这个大小视为值的大小在长度不足的时用空格补足。而VARCHAR类型把它是为最大值并且只存储字符串实际需要的长度增加一个额外字节来存储字符串本身的长度来存储值所以短于指示器长度的VARCHAR类型不会被空格填补但长于指示器的值仍然会被截短。
4.VARCHAR类型可以根据实际内容动态改变存储值的长度因此在不能确定字段需要多少字符时使用VARCHAR类型可以有效地节约磁盘空间、提高存储效率。
5.CHAR的存储方式是对英文字符ASCII占用1个字节对一个汉字占用两个字节
6.VARCHAR的存储方式是对每个英文字符占用2个字节汉字也占用2个字节两者的存储数据都非unicode的字符数据。
SQL中的增删改查
增加
insert into person values (1,admin,13988888888,taiyuan);
insert into person (id) values (1);
insert into 表格名称 values (字段1,字段2....);
删除
delete from person where id 1; 指定条件删除
delete from person; 清空表格
修改
update person set name admin where id 3;
update person set name admin001;
查询
select * from person;
select * from person where id 4;
数据库中的约束
在创建表格的过程中可以给某些字段追加约束条件
非空约束 NOT NULL
NK
create table t_user (
id int(3) not null,
username varchar(10),
password varchar(15)
);
唯一约束 UNIQUE
UK
create table t_user (
id int(3) not null,
username varchar(10) unique,
password varchar(15)
);
create table t_user (
id int(3) not null,
username varchar(10),
password varchar(15),
constraint UK_username unique(username)
);
主键约束 PRIMARY KEY
主键PRIMARY KEY也称“主键约束”。
MySQL主键约束是一个列或者多个列的组合其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键通过它可以强制表的实体完整性。主键主要是用于其他表的外键关联以及本记录的修改与删除。
主键PRIMARY KEY是 MySQL 中使用最为频繁的约束。一般情况下为了便于 DBMS 更快的查找到表中的记录都会在表中设置一个主键。
PK 效果上 非空约束唯一约束
create table t_user (
id int(3),
username varchar(10),
password varchar(15),
constraint pk_id primary key(id)
);
联合主键
MYSQL是一个关系型数据库管理系统其中联合主键是一种非常重要的数据库设计概念。在MYSQL中联合主键是指使用多个列值来唯一标识一个记录的主键。一个联合主键可能包含两个或更多个字段而这些字段一起组成了一个唯一标识符。这样可以保证在数据表中每个记录都有唯一的标识。
外键约束 FOREIGN KEY选修
一对一 一对多 多对多
FK 用到两张表格
t_user 一
t_book 多
关系维护在多方 uid Username password 1 Lufei lufei 2 Qiaoba qiaoba bid Bookname author u_id外键 1 笑傲江湖 金庸 1 2 神雕侠侣 金庸 2 3 鹿鼎记 金庸 2 4 连城诀 金庸 2
创建过程中分两步进行
1. 指定哪一个字段是外键 foreign key(u_id)
2. 外键需要引用哪一个表格的字段 references t_user(id)
id int(3),
password varchar(15),
);
create table t_book (
bid int(3),
bookname varchar(6),
author varchar(3),
u_id int(3),
constraint pk_bid primary key(bid),
constraint fk_u_id foreign key(u_id) references t_user(id)
);
一级菜单 二级菜单 自关联
设计一张菜单表 id name url oneid 1 用户管理 XXX 2 用户添加 XXX 1 3 用户删除 XXX 1 4 用户查询 XXX 1 5 用户修改 XXX 1 6 商品管理 XXX 7 商品上架 XXX 6 8 商品下架 XXX 6
create table t_menu (
id int(3),
name varchar(10),
url varchar(20),
oneid int(3),
constraint pk_id primary key(id),
constraint fk_oneid foreign key(oneid) references t_menu(id)
级联删除谨慎使用
bid int(3),
u_id int(3),
constraint fk_u_id foreign key(u_id) references t_user(id) on delete cascade
create table t_book (
bid int(3),
bookname varchar(6),
ahthor varchar(3),
u_id int(3),
constraint pk_bid primary key(bid),
constraint fk_u_id foreign key(u_id) references t_user(id) on delete set null
);
检查约束 CHECK
在一个范围中间
create table test32(
id number primary key,
age number check(age0 and age
);
数据库三范式
第一范式(1NF)
· 第一范式主要是保证数据表中的每一个字段的值必须具有原子性也就是数据表中的每个字段的值是不可再拆分的最小数据单元
第二范式(2NF
· 第二范式要求在满足第一范式的基础上还要满足数据表里的每一条数据记录都是可唯一标识的而且所有的非主键字段都必须完全依赖主键不能只依赖主键的一部分。
第三范式(3NF
· 第三范式建立在已经满足第二范式的基础上
· 数据表中的每一个非主键字段都和主键字段直接相关
查询机制
emp员工表 字段 描述 类型 empno 员工编号 mediumint ename 员工姓名 varchar job 职位 varchar mgr 领导编号 mediumint hiredate 入职日期 date sal 工资 decimal comm 奖金 decimal deptno 部门编号 mediumint
dept部门表 字段 描述 类型 deptno 部门编号 mediumint dname 部门名称 varchar loc 部门位置 varchar
salgrade工资等级 字段 描述 类型 grade 等级 mediumint losal 最低工资 decimal hisal 最高工资 decimal
简单查询
查询所有员工信息
SELECT * from emp;
查询所有并起别名
SELECT empno 员工编号,ename 员工姓名,job 职位,mgr 领导编号 from emp;
SELECT e.empno,e.job,e.sal from emp e;
查询公司中的职位
SELECT DISTINCT job from emp;
DISTINCT 去重关键字
限定查询 SELECT * FROM 表名称 [限定条件] Where
查询工资高于1500的所有员工信息
SELECT * from emp WHERE sal 1500;
查询工资高于1500低于3000的所有员工信息
SELECT * from emp WHERE sal 1500 and sal
//between...and...取的区间值
SELECT * from emp WHERE sal BETWEEN 1500 and 3000;
查询没有奖金的所有员工信息 or或者
SELECT * from emp where comm 0 or comm is NULL;
查询有奖金的员工信息
SELECT * from emp where comm 0;
查询员工姓名中带有S的员工信息
SELECT * from emp where ename like %S%;
模糊匹配
%:占位符 代表0个或者多个字符
_占位符 代表一个字符
查询入职日期在1981年的员工
SELECT * from emp where hiredate BETWEEN 1981-01-01 and 1981-12-31;
SELECT * from emp where hiredate LIKE %1981%;
查询员工编号在7499 7521 7566 in包含
SELECT * from emp where empno in (7499,7521,7566);
排序查询 SELECT * FROM 表名称 [限定条件][排序条件] Order by 字段 升序/降序 升序 asc 降序 desc
根据工资由高到底进行排序查询
SELECT * from emp order by sal desc;
如果工资一样那么按照入职日期升序
多表查询
查询所有员工的详细信息包含部门信息
select * from emp,dept; 有问题的查询
带来的问题是简单对两张表做乘积处理形成大量冗余数据这种现象叫做笛卡尔积效应
需要追加两张表的关联条件从显示上消除笛卡尔积效应
select * from emp,dept where emp.deptno dept.deptno;
查询员工编号员工姓名员工职位员工工资部门名称部门位置
逐步分析
先确定需要的表
emp e,dept d
确定需要的字段
e.empno,e.ename,e.job,e.sal,d.dname,d.loc
确定关联条件
e.deptno d.deptno
select e.empno,e.ename,e.job,e.sal,d.dname,d.loc from emp e,dept d where e.deptno d.deptno;
查询所有的员工编号员工姓名员工职位领导姓名领导职位
先确定需要的表
emp e1,emp e2
确定需要的字段
e1.empno 员工编号,e1.ename 员工姓名,e1.job 员工职位,e2.ename 领导姓名,e2.job 领导职位
确定关联条件
e1.mgr e2.empno
select e1.empno 员工编号,e1.ename 员工姓名,e1.job 员工职位,e2.ename 领导姓名,e2.job 领导职位 from emp e1,emp e2 where e1.mgr e2.empno;
发现少一条记录如果要解决这个问题需要借助连接查询
连接查询 SELECT * FROM 表名称 [连接查询][限定条件][排序条件] 左外连接 left (outer) join...on 条件 右外连接 right (outer) join...on 条件
查询所有的员工编号员工姓名员工职位领导姓名领导职位
select e1.empno 员工编号,e1.ename 员工姓名,e1.job 员工职位,e2.ename 领导姓名,e2.job 领导职位 from emp e1 left join emp e2 on e1.mgr e2.empno;
分组查询
分组前提需要分组的列出现了重复数据 SELECT * FROM 表名称 [连接查询][限定条件][分组查询][排序条件] Group by 字段
语法限制
1. 一旦出现分组那么select后边只允许出现统计函数以及分组字段
2. 统计函数可以单独使用
3. 如果出现统计函数的嵌套那么select后边只允许出现统计函数
求每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
select emp.deptno,dept.dname,avg(sal) from emp,dept where emp.deptno dept.deptno group by emp.deptno,dept.dname;
通过右连接将没有展示的数据进行展示
select dept.deptno,dept.dname,avg(sal) from emp right join dept on emp.deptno dept.deptno group by emp.deptno,dept.deptno,dept.dname;
展示平均工资高于2000的部门信息
错误写法
select dept.deptno,dept.dname,avg(sal) from emp right join dept on emp.deptno dept.deptno group by emp.deptno,dept.deptno,dept.dname where avg(sal) 2000;
解决办法
如果在分组之后需要再次筛选不可以使用where,需要使用Having
select dept.deptno,dept.dname,avg(sal) from emp right join dept on emp.deptno dept.deptno group by emp.deptno,dept.deptno,dept.dname having avg(sal) 2000;
HAVING 关键字和 WHERE 关键字都可以用来过滤数据且 HAVING 支持 WHERE 关键字中所有的操作符和语法。
但是 WHERE 和 HAVING 关键字也存在以下几点差异
1.一般情况下WHERE 用于过滤数据行而 HAVING 用于过滤分组。
2.WHERE 查询条件中不可以使用聚合函数而 HAVING 查询条件中可以使用聚合函数。
3.WHERE 在数据分组前进行过滤而 HAVING 在数据分组后进行过滤 。
4.WHERE 针对数据库文件进行过滤而 HAVING 针对查询结果进行过滤。也就是说WHERE 根据数据表中的字段直接进行过滤而 HAVING 是根据前面已经查询出的字段进行过滤。
5.WHERE 查询条件中不可以使用字段别名而 HAVING 查询条件中可以使用字段别名。
单行函数查询
在MySQL中单行函数是一种对单个输入值执行操作的函数。它接受一个输入参数并返回一个值。单行函数可以用于处理数据并在查询中进行数据转换、计算、日期和时间处理等操作。
以下是MySQL中常见的单行函数示例
字符串函数
CONCAT连接两个或多个字符串。
SUBSTRING返回字符串的子串。
LENGTH返回字符串的长度。
LOWER将字符串转换为小写。
UPPER将字符串转换为大写。
数值函数
ABS返回数值的绝对值。
CEIL返回大于或等于给定数值的最小整数。
FLOOR返回小于或等于给定数值的最大整数。
RAND返回一个随机数。
日期和时间函数
CURDATE返回当前日期。
CURTIME返回当前时间。
NOW返回当前日期和时间。
DATE_FORMAT按照指定格式格式化日期或时间。
条件函数
IF根据条件返回不同的值。
CASE根据条件执行不同的操作。
其他函数
MD5计算字符串的MD5哈希值。
SHA1计算字符串的SHA1哈希值。
REVERSE反转字符串的顺序。
TRIM删除字符串开头和结尾的空格或指定字符。
子查询
Where子查询 单行单列 多行单列
From子查询 多行多列
查询比SMITH工资高的人
select * from emp where sal (select sal from emp where ename SMITH);
查询比平均工资高的人
select * from emp where sal (select avg(sal) from emp);
查询和经理工资相同的员工信息
select * from emp where sal in (select sal from emp where job MANAGER);
查询部门编号部门位置部门人数部门平均工资
第一步查询每个部门的编号名称位置
Dept d
第二步统计部门人数平均工资
emp e
e.deptno deptno, count(e.empno) num,avg(e.sal) sal
Group by e.deptno
select e.deptno deptno, count(e.empno) num,avg(e.sal) sal from emp e Group by e.deptno;
将查到的数据和dept组合
select temp.deptno,temp.num,temp.sal,d.dname,d.loc from (select e.deptno deptno, count(e.empno) num,avg(e.sal) sal from emp e Group by e.deptno) temp,dept d where temp.deptno d.deptno;
通过右连接处理边界数据
select d.deptno,IFNULL(temp.num,0) 部门人数 ,IFNULL(temp.sal,0) 平均工资 ,d.dname,d.loc from (select e.deptno deptno, count(e.empno) num,avg(e.sal) sal from emp e Group by e.deptno) temp right join dept d on temp.deptno d.deptno;
IFNULL() 函数用于判断第一个表达式是否为 NULL如果为 NULL 则返回第二个参数的值如果不为 NULL 则返回第一个参数的值。
分页查询
LIMIT 关键字 参数 参数1为索引值从哪开始第二个参数为每页显示几个
SELECT * FROM product LIMIT 30;// 从0开始显示30条
SELECT * FROM product LIMIT 10,10;//从10开始 显示10条
由此我们可以分析出分页查询的SQL语句格式
select * from my_book limit (page-1)*pageSize,pageSize;
其中page是页码pageSize是每页显示的条数
当数据库中数据量较大时使用limit函数会存在性能问题我们可以用主键来代替limit函数实现数据分页查询
select * from my_book where id (page-1)*pageSize limit pageSize;
select * from my_book where id 10 limit 5;
事务处理
概念在一个业务逻辑中包含多个步骤操作需要被事务进行管理要么同时成功要么同时失败
A -500
//异常
B500
默认提交事务
关闭默认提交事务set autocommit 0;
手动提交commit
手动回滚rollback
开启默认提交事务set autocommit 1;
事务的四大特性掌握
1、原子性事务包含的所有数据库操作要么全部成功要不全部失败回滚
2、一致性一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说假设用户A和用户B两者的钱加起来一共是5000那么不管A和B之间如何转账转几次账事务结束后两个用户的钱相加起来应该还得是5000这就是事务的一致性。
3、隔离性一个事务未提交的业务结果是否对于其它事务可见。级别一般有read_uncommitread_commitread_repeatable串行化访问。
4、持久性一个事务一旦被提交了那么对数据库中数据的改变就是永久性的即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。持久化操作
事务的隔离级别了解
Read uncommitted 读未提交
产生问题脏读、不可重复读、幻读
提供了事务建最小限度的隔离。顾名思义就是一个事务可以读取另一个未提交事务的数据。
示例小明去商店买衣服付款的时候小明正常付款钱已经打到商店老板账户但是小明发起的事务还没有提交。就在这时商店老板查看自己账户发现钱已到账于是小明正常离开。小明在走出商店后马上回滚差点提交的事务撤销了本次交易曹邹。
结果小明未付钱买到了衣服商店老板实际未收到小明的付款。
分析商店老板查看自己的资金账户这个时候看到的是小明还没有提交事务的付款。这就是脏读。
注意处于该隔离级别的事务A与B如果事务A使用事务B不提交的变化作为计算的基础然后哪些未提交的变化被事务A撤销这就导致了大量的数据错误变化。
Read committed 读已提交Oracle数据库默认的隔离级别
产生问题不可重复读、幻读
处于Read committed 读已提交级别的事务可以看到其他事务对数据的修改。也就是说在事务处理期间如果其他事务修改了相应的表那么同一个事务的同一sql在其他事务执行前后返回的是不同的结果。一个事务要等另一个事务提交后才能读取数据。
示例小明卡里有1000元准备与几个朋友聚餐消费消费1000元当他买单时事务开启收费系统检测到他卡里有1000元。就在检测完毕的时候小明女朋友发现小明有私房钱全部转走并提交。当收费系统准备扣款时再检查小明卡里的金额发现已经没钱了付款不成功。小明此时就会很纳闷明明有钱的呀钱呢
分析该示例中同一个事务范围内两个相同的查询却返回了不同数据这就是不可重复读。该隔离级别可以解决脏读问题。
Repeatable read 可重复读Mysql数据库默认的隔离级别
产生问题幻读
在开始读取数据事务开启时不再允许修改操作
示例还是小明有1000元准备跟朋友聚餐消费这个场景当他买单事务开启时收费系统检测到他卡里有1000元这个时候他的女朋友不能转出金额。接下来收费系统就可以扣款成功了小明醉醺醺的回家准备跪脱衣板。
分析重复读可以解决不可重复读的问题这句话有些别扭大家可以仔细品一下。
写到这里大家可能会产生疑问什么情况下产生幻读呢
示例来了
小明在公司上班女朋友告诉他拿着他的卡去逛街消费。花了一千元然后小明去查看他银行卡的消费记录事务开启看到确实是花了一千元。就在这个时候小明女朋友又花三千元买了一些化妆品和衣服即新增了一些消费记录。当小明打印自己银行卡消费记录单的时候女朋友事务提交发现花了四千元似乎出现了幻觉小明很心疼。这就是幻读
Serializable 串行化
解决所有问题
隔离级别越高性能越低
查询数据库的默认隔离级别
select tx_isolation;
改变数据库的隔离级别
修改全局
set global transaction isolation level 隔离级别关键字;
修改回话
//设置read uncommitted级别
set session transaction isolation level read uncommitted;
//设置read committed级别
set session transaction isolation level read committed;
//设置repeatable read级别
set session transaction isolation level repeatable read;
//设置serializable级别
set session transaction isolation level serializable;
开启事务
Start transaction
Update person set sal sal - 500 where id 1;
Update person set sal sal 500 where id 2;
