金鹏建设集团网站,深圳网站建设deyond,diywap手机网站系统,自己怎么用h5做网站root数据库技术
一、数据库理论
1 什么是数据库技术 数据库技术主要研究如何组织、存储数据#xff0c;并如何高效地提取和处理数据。 2 什么是SQL SQL#xff08;Structured Query Language#xff09;结构化查询语言 SQL是操作数据库的命令集#xff0c;也是功能齐全的…root数据库技术
一、数据库理论
1 什么是数据库技术 数据库技术主要研究如何组织、存储数据并如何高效地提取和处理数据。 2 什么是SQL SQLStructured Query Language结构化查询语言 SQL是操作数据库的命令集也是功能齐全的数据库语言。 SQL只需要掌握“怎么用”或“做什么”不需要了解如何做或“怎么做”。 SQL在不同的数据库软件中有一小部分是不相同的(编程部分)但大多数通用的。 SQL分类 DDL 数据描述语言负责创建和管理资源、DML 数据库操纵语言负责管理数据、DTL 数据库事务语言(管理事务、DQL 数据查询语言、DCL 数据库控制语言管理数据库运行环境的。 DDL数据定义语言用来定义数据库对象 – 库表列等 创建数据库create database 查看数据库show databases 修改数据库的编码格式alter database 数据库名 charactset 编码格式 删除数据库drop database 数据库名 切换数据库use 数据库名 DML数据库操纵语言用来定义数据库记录数据的增删改 增删改insert into 表名 values DCL数据控制语言用来定义访问权限和安全级别 1、授权grantgrant create session to scott 2、回滚rollback 3、提交commit 4、新建用户create user DQL数据查询语言用来查询记录数据查询 数据查询语言DQL基本结构是由SELECT子句FROM子句WHERE子句组成的查询块 SELECT 字段名表 FROM 表或视图名 WHERE 查询条件 3 DBDBMSDBS
3.1 Data数据 数据是客观事物的符号表示数据也是数据库中存储的最基本的单位。 数据分类文字、图形、图像、音频、视频。 3.2 数据库的定义 数据库 长期存储在计算内、有组织、可共享的数据集合。数据库中的数据是按一定的数据模型组织、描述和存储。具有较少的冗余、较高的数据独立性和易扩展性以及数据的完整性并为多个用户共享。 3.3 DBMS DBMS: 应用于应用软件与数据存储之间的一层数据管理软件是基础软件如,Access也可以是个大型复杂的软件系统Oracle, MySQLSQLServer。 DBMS的用途 科学地组织、存储数据并高效的提取和维护数据。 DBMS的主要功能数据库定义、数据库操作、数据库的保护安全性和维护。 DBMS的优点 较少的数据冗余程序与数据相互对立保证数据的安全、可靠、正确数据可以并发使用并能同时保证一致性数据易维护、易扩展 3.4 DBS数据库系统 DBS由数据库、数据库管理系统、应用系统、系统管理员和用户组成的经历三个阶段网状模型、层次模型、关系模型重点。 数据和信息的关系
信息是数据经过加工后提取出有用的知识通过某种数据形式表示的。信息的层次从低到高从客观到信息化 存在三个世界现实世界、信息世界、数据世界。
4 范式 范式 关系模型满足的条件, 主要用于设计关系的一种规范。 4.1 第一范式 1NF: 关系中的每一个属性都不可分隔。即没有重复的列。 4.2 第二范式 2NF: 关系中非主键属性完整依赖于主键。即存在主键字段或属性 4.3 第三范式 3NF: 非主键属性之间不存在依赖关系。即存在外键字段时只需要另外关系的主键。 如在员工表只需要部门表的部门编号主键即可不需要将部门的名称存放在员工表中。部门编号在员工表中是一个外键。 二、MySQL数据库 MySQL数据库是一个关系型数据库管理系统 目前是属于Oracle公司的。也是一个轻量级数据库。目前主要版本集中5.5/5.6/5.7和8.0 最新的版本8。 MySQL数据库可以安装在Window、Mac、Linux等操作系统上。 MySQL5.6官方文档https://dev.mysql.com/doc/refman/5.6/en/ 1 安装注意事项
安装路径 尽量安装到D盘默认安装到C盘版本选择 Server Only 只有服务没有其它的工具。配置中Root用户的口令 root
2 MySQL数据库操作
进入mysql的交互环境
按Win 键 mysql 选择 MySQL 5.6 Command Line
进入命令行窗口时输入root用户的口令 正常情况下进入MySQL交互环境。
操作以下的sql语句
use mysql;
select user,host from user;【重要的说明】mysql数据库管理系统中包含了很多的数据库和用户每一个数据库下存在多个关系表、视图、函数或存储过程等资源。
三、mysql数据库的操作
1 MySQL数据库命令
1.1 查看所有数据库
show databases;1.2 打开数据库
use 数据库名;1.3查看当前所在的库
select database();1.4 查看当前库下的表
show tables;1.5 查看表结构
desc 表名;1.6 查看表内容
select 属性1,属性2 ... from 表名或关系表 --查看表所列出的属性的列
select * from 表名 --查看表的所有列1.7 查看当前的时间
select now(); --显示时间和日期
select current_date; --只显示日期
select current_time; --只显示时间
select current_timestemp; --显示时间和日期1.8 查看当前字符集
show variables like character%; --正常情况下字符集是 utf82 管理数据库
2.1 创建数据库
create database [if not exists] 数据库名 [charset utf8]; --语法 [如果不存在] [指定字符集]--创建studb数据库如果存在的话则不创建指定字符集为 utf8create database if not exists studb charset utf8;2.2 删除数据库
drop database [if exists] 数据库; --如果数据库存在则删除
--删除bookdb数据库
drop database bookdb;2.3 查看创建数据库的语句
show create database 数据库;3 管理表 表的创建、修改、删除以及重命名。 3.1 创建表
create table [if not exists] 表名
(字段名 数据类型[(数据长度)] [约束] [comment 注释信息], ...)
engineInnoDB charsetutf8; --引擎 字符集3.2 数据类型 数值类型整数类型 intinteger、小数类型 fioat、精确小数类型 decimal(长度,小数位)。 字符类型固定长度的字符类型 char长度、可变长的字符类型 varchar长度。 日期类型日期 date、时间 time、日期时间 timestamp。 文本类型普通文本 text, 、大文本 clob 。 二进制类型blob如音频或视频的媒体文件、电子合同等。 3.3 字段约束 主键约束primary key 主键约束包含唯一约束和非空约束。 外键约束foreign key 外键引用另一个表的主键字段。 唯一约束unique 字段的值在所有记录中是唯一的。 非空约束not null字段值是不能为null。 默认值default 字段值如果没有提供时使用默认值。 自增mysql特定的功能序列auto_increment 只配合primary key主键使用而且数据类型是integer。 3.4 表的引擎
show engines; --查看mysql数据库存在哪些引擎3.5 实战
任务1: 创建班级表 班级表的名称 tb_class 班级表的字段 编号 cls_id、名称 name、最大人数 max_persons 编号是自增的、主键 名称是唯一的不能重复。 最大人数的默认是100人 create table tb_class (cls_id integer primary key auto_increment,name varchar(20) unique,max_person int default 100
);任务2: 创建学生表 表名 tb_stu 字段名 编号 stu_id, 姓名 name, 性别 sex 出生日期 birthday 编号是主键且自增、性别的默认值为男, 出生日期不能为空 create table tb_stu (stu_id integer primary key auto_increment,name varchar(20),sex char(1) default 男,birthday date not null
); 任务3: 创建老师表 表名 tb_teacher 字段名 编号 tid, 姓名 name, 工龄 work_years 编号主键且自增姓名不能为空工龄默认为3年 create table tb_teacher(tid integer primary key auto_increment,name varchar(20) not null,work_years int default 3
);任务4: 创建课程表 表名 tb_course 字段 编号 cid, 名称 name, 带课老师编号 tid 编号主键且自增名称唯一老师编号外键引用老师表 tb_teacher的主键字段 -- 字段级外键约束
create table tb_course
(cid integer primary key auto_increment,name varchar(20) unique,tid integer references tb_teacher(tid)
);
-- 表级外键约束
create table tb_course
(cid integer primary key auto_increment,name varchar(20) unique,tid integer,constraint tid_fk foreign key(tid) references tb_teacher(tid)
);注意表级的方式添加外键约束
constraint [约束名] 约束类型(字段) references 引用表名(引用字段)
任务5: 创建成绩表 表名 tb_score 字段 学生编号 sid, 课程编号 cid, 成绩 score 学生编号是外键引用学生表的编号, 外键约束名定为 tb_score_sid_fk 课程编号是外键引用课程表的编号,外键约束名定为 tb_score_cid_fk 成绩的默认为 0.0 create table tb_score
(sid integer,cid integer,score float default 0.0,constraint tb_score_sid_fk foreign key (sid) references tb_stu(stu_id),constraint tb_score_cid_fk foreign key (cid) references tb_course(cid)
);3.6 修改表日期3.22
1 修改表名
alter table 表名 rename [to] 新表名; --语法
--如, 修改tb_stu表名为tb_student;
alter table tb_stu rename tb_student;2 修改字段名
--语法
alter table 表名
change 原字段名 新字段名 数据类型[(长度)] [约束];
--如修改tb_student表中的name字段名为sname
alter table tb_student
change name sname varchar(20);3 修改字段 一 般用于修改字段的类型和长度、以及非空约束和默认值。 --语法
alter table 表名
modify 字段名 数据类型[(长度)] [约束] [comment 注释];--如1修改tb_teacher表的name字段的数据长度为30并且默认值为老师。
alter table tb_teacher
modify name varchar(30) default 老师;
--如2: 修改tb_class表的name字段长度为40且非空同时修改max_person字段的默认为120
alter table tb_class
modify name varchar(40) not null,
modify max_person int default 120;4 删除字段 不能直接删除主键、外键和唯一约束的字段。 --语法
alter table 表名
drop [column] 字段名;--如1 删除tb_score表的score字段。
alter table tb_score
drop score;
--如2 尝试删除tb_score表的cid字段外键约束的字段应该先删除外键约束再删除字段。
alter table tb_score
drop foreign key tb_score_cid_fk;5 增加字段
--语法
alter table 表名
add [column] 新字段名 数据类型[(数据长度)] [约束]; --如向tb_score表添加新的字段cid和score。
alter table tb_score
add column cid integer,
add column score float;6 增加外键约束
--语法
alter table 表名
add constraint 约束名 foreign key (字段名) references 引用的表名(引用字段);--如 向tb_score表的cid字段添加外键约束tb_score_cid_fk并引用tb_course表的cid主键字段。
alter table tb_score
add constraint tb_score_cid_fk foreign key (cid) references tb_course(cid);7 查指定表的所有约束 information_schema.table_constraints字典表 select table_name,constraint_name,constraint_type --表名、约束名、约束类型
from information_schema.table_constraints
where table_nametb_score;3.7 删除表
--语法
drop table [if exists] 表名;--如删除tb_course表
drop table tb_course;4 操作表的数据 DML 数据库操纵语言insret、update、delete 等语句 4.1 插入数据
--语法1单条数据插入
insert into 表名[(字段名1, 字段名2,...字段名n)]
values (字段1值, 字段2值,...字段n值);--语法2多条数据插入
insert into 表名[(字段名1, 字段名2,...字段名n)]
values
(字段1值, 字段2值,...字段n值),
(字段1值, 字段2值,...字段n值),
(字段1值, 字段2值,...字段n值),
...
(字段1值, 字段2值,...字段n值)
;任务1班级表插入一条数据 班级名Testing-2021,最大人数50 insert into tb_class(max_person, name)
values (50, Testing-2021);任务1 班级表批量插入 insert into tb_class(name, max_person) values
(Testing-2022-01, 50),
(Testing-2022-02, 45),
(Testing-2022-03, 80);4.2 更新数据 更新数据是指修改表中某一条记录或元组中对应属性的值支持同时修改多个属性值。当然可以通过选择操作条件选择选择符合条件的元组或记录。 --语法
update 表名 set
字段1值 [, 字段2值...]
[where 条件表达式];条件表达式 等值条件 如 namedisen关系条件 大于, 小于, 大于等于, 小于等于, ! 不等于非空条件 not null 非空, is null 为空包含条件 in (条件值条件2,…) 条件值之间是或的关系逻辑关系 and 且的条件 or 或的条件区间条件 between 开始值 and 结束值 是闭合区间 [开始值, 结束值]模糊条件 like 匹配字符 匹配字符的通配符 % 任意个数任意字符 _ 任意一个字符 任务1: 将低于50人的班级更改为100人的容量
update tb_class
set max_person100
where max_person 50;任务2: 将名字中包含江的老师等级更改为特级
update tb_teacher
set level特级
where name like %江%;4.4 删除数据不加条件是清空表内容
--语法
delete from 表名
[where 条件表达式];【注意】如果不加where条件时表示清空表数据。
【扩展】事务操作 begin 开启事务 commit 提交事务 rollback 回滚事务取消任务开始到当前的所有操作。 任务1开启事务并清空成绩表查看数据之后回滚事务
begin;
delete from tb_score;
select * from tb_score;
rollback;任务2删除等级为null的老师记录
delete from tb_teacher
where level is null;5 查询数据 查询数据是mysql最主要的功能之一查询语句涉及的面比较广。涉及选择、投影和连接等操作。 5.1 select 语句 select 语句主要是选择哪些字段在select语句中可以使用一些表达式显示运算之后的结果。 语法
select [all|distinct] 字段名或表达式 [as 别名] [,字段名或表达式...]
from 表名 [as 别名]
[where 条件表达式]
[group by 字段或表达式]
[having 分组条件表达式]
[order by 排序字段 [ASC|DESC]]
[limit 分页表达式]
[for update]select 中的表达式可以进行算术计算也可以调用mysql函数。
【说明】having子句用于聚合统计之后的数据作为条件如显示总成绩高于290的课程或学生。
5.2 条件语句 where子句设定选择记录的条件。 如查看学号大于等于2且成绩等于100分的课程号和成绩信息, 可以去重
select distinct cid, score
from tb_score
where sid2 and score100;5.3 排序语句 order by 子句可以针对某一列的值进行排序(行记录的先后顺序) 如按成绩高低进行排序显示所有的学生不同课程的成绩信息
select sid,cid, score
from tb_score
order by score desc;【说明】DESC/desc 表示从高到低的降序ASC/asc 表示从低到高的升序, 默认asc
5.4 分页语句 limit子句格式 limit 开始记录序号,每一页显示的记录数 记忆的格式 limit offset, page_size 第一条记录的序号 0 如每页显示3条记录 第一页的limit的写法 limit 0,3; 第二页的limit写法 limit 3, 3 第三页的limit写法 limit 6, 3 总结查看第n页的limit写法 limit (n-1)*page_size, page_size 如2按每页3条记录查看第2页的成绩表数据
select * from tb_score limit 3,3;5.5 分组统计语句 group by 子句 针对某一个字段的值进行分组分组之后的数据可以进行聚合统计。 聚合统计需要mysql的函数 sum()求和avg()求平均max() 最大值min() 最小值count() 统计行数 任务1统计每一位学生的总成绩
select sid, sum(score) as total
from tb_score
group by sid;【注意】在select子句中没有 使用聚合函数的列必须要放在group by 子句中。
任务2 统计每门课程的考试人数和平均成绩
【扩展】四舍五入的函数: round(数小数位) 默认小数位为0
selectcid,count(1) as persons,round(avg(score), 1) as avg_score
from tb_score
group by cid
order by avg_score desc;【扩展】
mysqldump -u用户名 -p密码 数据库名导出路径和文件名
source命令在mysql交互环境中执行sql文件中的有效的语句。
如
将taiyang数据库导出成sql文件的sql语句mysqldump -uroot -proot taiyangd:\taiyang.sql
将创建表的所有的sql语句复制到d:\init.sql文件中, 导入文件需要在mysql命令行交互环境执行命令 source d:/init.sql
5.6 多表连接语句 简单地理解多个表之间相互联系关联通过连接的sql语句将多表之间的数据显示在一起。 【注意】多表连接时必须存在连接条件否则会出现笛卡尔乘积(n*m)。 1 等值连接
语法
select [ [表名.]字段或表达式 [as 别名], ...]
from 表名1 [as 别名1], 表名2 [as 别名2] [, 表名3 ...]
where 表名1.字段名表名2.字段名
[and 表名2或1.字段名表名3.字段名]
[and 表名1或2或3.字段名表名4.字段名....]【总结】如果存在n张关系表进行连接必须存在n-1的等值连接条件。
任务1: 查看所有学生的所有课程的成绩显示学生名、课程名和成绩并按每位学生的成绩进行排序
select s.sname as 姓名, c.name as 课程, sc.score as 成绩
from tb_score sc,tb_student s,tb_course c
where sc.sids.stu_id
and sc.cidc.cid
order by sid DESC, score DESC;2 join连接 join连接表等值连接更方便使用而且支持外连接。即等值连接相当于join的内连接。 语法
select 字段或表达式 [,...]
from 表名1
[natural|left|right] join 表名2 on (连接条件1[,连接条件2])
[join 表名3 on (连接条件2) ]
[join ...]
[where 条件表达式]【说明】
natural自然连接从两个表中找到相同的字段名作为默认的连接条件left 左外连接, 查询的结果除了内连接两个集合交集之外还包含左边表的其它数据。right 右外连接查询的结果除了内连接之外还包含右边表的其它数据。left和right都可能存在 null空数据。
3 union联合 将多个select语句的查询合并到一起但是select选择的字段尽量保持一致。默认显示是第一个select语句的字段名。 一般在分表的数据查看情况下使用分表指按某一种分类将数据分开存储避免一张表存放过多的数据。分表或分库是数据库的优化手段解决查询效率问题。 语法
select ... from ... [where... order ..]
union [all | distinct ]
select ... from ... [where ... order ..]如 将成绩表的每位学生的平均成绩和总成绩的统计数据合并
select sid,sum(score) total from tb_score group by sid
union
select sid,round(avg(score)) avg from tb_score group by sid;5.7 子查询语句 子查询select语句中包含select语句。 子查询应用的场景 复杂的sql查询语句需要从不同的select语句查询结果中组合的。 子查询可以使用的位置 where条件中join连接, update 更新insert插入数据等。 1 where中应用
语法
select ... from ...
where 字段或表达式 [|in||||| !](select 字段或表达式 from ...);任务1: 查询高于最低平均成绩的学生及其成绩显示学生姓名、课程名和成绩
select min(avg) as min
from
(select sid,avg(score) as avg from tb_score group by sid) A;【注意】如果from 后跟上子查询时必须给定别名表示从某一个表中查询数据。join的子查询也需要一个表的别名。
2 join中应用
语法
[left | right] join (select ...) 别名 on (连接条件)使用join方式完成where应用中的任务2
select c.*, if(A.cnt is null,0,A.cnt) as cnt
from tb_class c
natural left join
(select cls_id,count(1) as cnt from tb_studentgroup by cls_id) A
where cnt 2 or cnt is null;【扩展】mysql存在一个分支函数 if(条件, 为true值为false的值)
3 update中应用 update更新语句在set子句对子查询的结果赋值给某一个字段。 语法
update 表名,(select ... ) 别名 set 字段1别名.字段名 [,字段2别名.字段名 ...]【注意】更新字段值时一般是单行结果的子询中某一列的值。
任务1将班级最大人数低于平均数时更改为平均数。
update tb_class,(select round(avg(max_person)) avg from tb_class) a
set max_persona.avg
where max_person a.avg;【注意】子查询可以理解为一个虚拟的表不能直接在set中使用子查询作为某一个字段的值。
4 insert中应用 插入数据时可以使用子查询将查询到的数据插入到某一个表中。 同插入数据使用子查询的方式create table也可以使用子查询。 语法
insert into 表[(字段1, ....)]
select ...【注意】向表的哪些字段插入数据时在select子句中必须有对应的字段值。
【扩展】create table使用子查询的语法
create table 表名 [as]
select ...【问题】创建表的字段只有select中选择的字段而且不存在约束。
任务1根据班级将成绩表分成多分即创建不同班级的成绩表
-- 新的成绩表与原成绩表的结构保持不变
create table tb_cls1_score as
select * from tb_score where score0;任务2继任务1将不同班级学生的成绩分别插入指定的表
-- 将班级1的学生考试成绩插入到 tb_cls1_score
insert into tb_cls1_score
select sc.*
from tb_score sc join tb_student s on (sc.sids.stu_id)
where s.cls_id1; 6 mysql内置函数
6.1 字符串函数 针对字符串的运算提供的内置函数 1 ascii函数 将一个左边字符转化为ascii值asscii中的字符只占一个字节。 select ascii(a); -- 查看字符 a的ascii的十进制的数值
select char(97); -- 将数值转化为ascii中的字符,如97转化为a字符
select oct(n); --将n的数值转化为8进制的字符串
select hex(n) --将n的数值转化为16进制的字符串
select bin(11); -- 查看11的二进制的字符串2 字符长度
select char_length(狄哥); -- 显示字符个数 【重点】
select length(狄哥); --显示字节个数
-- 显示字符所占的字节长度一个英文在utf8字符集中占1个字节一个中文在utf8中占3个字节。
-- 常用的字符集gbk/gb2312 (英文占1字节中文占2字节), unicode每个字符占2字节
-- 设计表的字段时使用varchar(length) length表示是字符的个数【面试】3 字符串拼接
-- 字符串拼接 【重点】
select concat(hi, ,, good);
select name, concat(work_years,年) as work from tb_teacher;4 查找字符串位置
instr(str, substr); --从str中查找substr第一次出现的位置
-- 显示的位置是从1开始的。
select instr(hi,disen, disen) as position;5 大小写转换
-- low() 转小写
-- upper() 转大写6 删除空白
-- trim() 删除字符串两边的空白
-- ltrim() 删除字符串左边的空白
-- rtrim() 删除字符串右边的空白
select trim( disen ) a, ltrim( **disen** ) as b,rtrim( disen );7 替换
-- replace(str, oldstr, newstr) 从str中将oldstr替换成newstr
select replace(hi,disen, disen, jack) as rt;8 字符串截取
-- substr(str, pos, len) 从pos开始截取子len长的子字符串len默认为最后的位置
select substr(hi,disen,4);
select substr(hi,disen,very good!, 4, 5);6.2 数值函数
1 绝对值函数
abs() -- 绝对值
select abs(-90); 2 小数取整函数
ceil() -- 上行取整
floor() -- 下行取整select ceil(15.5), floor(15.5);3 四舍五入函数
round(n, 小数位数) --四舍五入
select round(13.4456, 3);4 次幂函数
pow(m, n)/power() -- m的n次幂
select pow(3, 2);5 开方函数
sqrt(n) --开平方根的函数
select sqrt(9); 6 取余函数
mod(number, m) --计算 number对m的取余
-- 12 % 8 4
select mod(12, 8); 7 取整
select 10 div 3;8 截断函数
truncate(x, d) -- 保存x中d位小数如果d是负数时表示截断整数部分内容。
select truncate(12.56, 1); 结果是12.5
select truncate(1987, -3); 结果是10006.3 日期函数
1 日期相关的函数
now() -- 当前日期时间
current_date() -- 当前的日期
year(date) -- 从时间数据中提取年
month(date) -- 提取月份
day(date) -- 提取日
date(timestamp) -- 从日期时间中提取日期
week(date) -- 显示date是一年中第几周 【重点】 从0开始
weekofyear(date) -- 显示date是一年中的第几周从1开始
yearweek(date) -- 返回 date中年和一年中的第几周
dayofyear(date) -- 一年中的第几天 1-366
dayofweek(date) -- 一周内的星期几的索引 星期日是1
dayname(date) -- 显示星期几
dayofmonth(date) -- 一月中的第几天索引从1开始范围0-31select dayofmonth(current_date);2 时间相关函数
current_time() -- 当前时间
hour(time) -- 取小时
minute(time) -- 取分钟
second(time) -- 取秒
time(timestamp) -- 从日期时间信息中提取时间3 日期时间转换相关的函数
timestamp(e1) -- 将e1日期转化日期时间
timestamp(e1, e2) -- 将e2的时间加到e1得到新的日期时间
time_format(time, format_str) -- 按format_str格式转化time字符串
date_format(date, format_str) -- 按format_str格式转化date字符串
str_to_date(str, format) -- 将format格式的str字符串转化为日期时间。select timestamp(2022-03-12);
select timestamp(2022-03-24 09:10:10, 13:15:25);
select char_length(time_format(now(), %H/%i/%s));
select time_format(12:15:18, %i:%s);
select date_format(now(), %m/%d/%y);-- 日期或时间计算时不能按默认的YYYY-dd-mm格式转化的认为不合法的日期或时间
select datediff(now(), 2012年10月12日);select str_to_date(2012/10/12,%Y/%m/%d);
select str_to_date(10月12日 12年,%m月%d日 %y年);
select datediff(now(), str_to_date(2012年10月12日,%Y年%m月%d日));
4 时间间隔相关的函数
datediff(d1, d2) -- 计算d1-d2的相隔的天数
timediff(e1, e2) -- 返回 e1-e2相差的时间结果是时间格式。
adddate(date, INTERVAL n UNIT), adddate(date, days)
-- UNIT时间单位 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
-- n表示可以是数值也可以是字符串如果字符串存在day最后出现秒unit则为day_second
addtime(time, add_time)
subdate(date, INTERVAL n UNIT)
subtime(time, sub_time)select datediff(now(), 2022-01-12);
select timediff(now(), 2022-01-12 12:10:10);select adddate(now(), interval 25 day);
select adddate(now(), interval 25 10 day_hour);
select subdate(now(), interval 40 day);重要的日期格式的字符串
%Y-%m-%d %H:%i:%s6.4 分支函数
1 if函数
if(条件表达式, 结果为true的值, 结果为false的值), 用于select子句where子句。任务1
存在以下A表
year month amount
1991 1 1.1
1991 2 1.2
1992 1 2.1
1992 2 2.2写出sql语句输出以下结果
year m1 m2
1991 1.1 1.2
1992 2.1 2.2sql语句
create table a(year int, month int, amount float);
insert into a values
(1991, 1, 1.1),
(1991, 2, 1.2),
(1992, 1, 2.1),
(1992, 2, 2.2);selectyear,max(round(if(month1, amount, 0),1)) m1,max(round(if(month2, amount, 0),1)) m2
from a
group by year;2 case语句
语法
select ...,
case [字段名或表达式] when 条件或值 then 条件为true结果when 条件2或值2 then 条件2为true结果2...else 以上条件都不为true时的结果n end 别名
from ...如1
select case 11 when 1 then 错误 when 2 then 正确 else 系统出问题了 end result;【注意】如果when中是单个值时表示值和 case后表达式是等值判断
如2
select case when 12 then 1大于2when 12 then 1等于2else 1小于2 end result;【注意】when中关系表达式计算关系表达式的结果case后不需要字段或表达式。
6.5 加密函数 加密函数即将明文转化为密文更好地保护数据。 【扩展】加密算法分类 对称加密AES, DES和非对称加密(RSA 公钥与私钥如ssl证书采用RSA算法) md5(str) -- 通过md5算法将str明文转化为32长度的密文且不可逆的。
select md5(123);
select md5(disen);sha1(str) -- 通过sha1算法将str明文转化为密文密文的长度一般为40位 不可逆的。
select sha1(disen);password(str) -- 将str转化为密文 一般用于口令加密。-- 对称加密与解密
aes_encrypt(str, key) -- str明文
aes_decrypt(en_str, key) -- en_str 加密后的密文
hex(str) --将str内容转化16进制内容
unhex(16进制的str) -- 取消16进制转换的内容select hex(disen);
select unhex(646973656E);
select hex(aes_encrypt(disen, abc123));
select aes_decrypt(unhex(EB0BD931B06891CBDEEB3A90A1BDDCA0), abc123);7、视图 视图是一张虚拟的表是一种高级的子查询且存在名称。即视图是一个select查询语句。 视图的数据取决于是select语句一般存储复杂的且频繁使用查询的select语句 7.1、视图创建的语法
create or replace view 视图名 as select 语句;如1 创建视图存储查询每门课程的平均分数和课程名的数据的sql
create or replace view avg_sc as
select cid,ceil(avg(score)) avg_score from sc group by cid;视图创建成功之后 可以通过 show tables查看。
从视图中查询数据时类似于从表中查询数据
select * from avg_sc;【注意】视图一般用于查询不能像表一样插入、修改和删除数据。但是表的数据发生变化之后会影响视图查询的结果。因此可以把视图查看是一个子查询。
7.2、删除视图
drop view 视图名; eg:drop view avg_sc; --删除视图 avg_sc查看stu数据库下所有的视图
select table_name
from information_schema.views
where table_schemastu;8、索引 索引是数据库的一种数据结构b树、hash为表的数据建议一个快速查询的方式。 可以创建表的一个或多个字段的索引一般索引字段是在查询条件中使用。 思考: 表中的每一个字段都可以创建索引 可以一张表中的索引是否越多越好不是的因为索引过多的时候影响DML语句效率。 索引分类 普通索引、主键索引、唯一索引、联合索引、全文索引、分区索引 - 主键索引 一般是创建主键约束时自动创建的索引
- 唯一索引字段存在唯一约束时也会自动创建唯一索引也可以手动创建唯一索引
- 联合索引也称之为组合索引或集簇索引由多个字段联合创建的索引。索引创建的语法
create [online | offline] [unique|fulltext| spatial ]
index 索引名 [ using { btree | hash }]
on 表名(字段名[(长度)] [asc|desc] [, ...] )
【说明】online 表示启用索引 offline关闭索引
如1: 为学生表的name字段创建一个唯一索引
create unique index name_unique using hash on student(name desc);索引删除的语法:
drop index 索引名 on 表名;如2: 删除name_unique索引
drop index name_unique on student;如3: 创建成绩表sc的sid和cid两个字段的组合索引
create index sid_cid_index using btree on sc(sid asc, cid desc); 检查sql语句是否使用索引
explain select * from sc where sid01 and cid01;【扩展】查看表中的所有索引
select i.index_id,i.name, i.type,t.name
from information_schema.innodb_sys_indexes i
join information_schema.innodb_sys_tables t on (i.table_idt.table_id)
where t.name like stu/%;9、事务 事务是一系列操作的组合事务具有ACID的特性。 ACID
Atomicity (原子性) 事务内发生的所有操作要么全成功要么全失败。Consistency(一致性): 事务开始和结束时数据保持一致。Isolation(隔离性): 事务之间互不影响Durability(持久性): 数据一旦保存将永久性存在不受系统影响。事务的隔离级别
read uncommitted 读未提交, 可以读取另一个事务中未提交的数据 【脏读】【不可重复读】【幻读】
read committed 读已提交 可以读取另一个事务中已提交的数据【不可重复读】【幻读】
repeatable read 可重复读事务开启之后数据不会发生变化。【幻读】
serializable 串行化一个事务结束之后另一个事务才能开始。存在锁的概念。查看当前事务的隔离级别
show vairalbes like %isolation%;设置当前的会话的事务隔离级别
set session transaction isolation level 隔离级别名称;【扩展】事务的还原点
savepoint 名称--取消还原点之后的所有操作
rollback to 还原点【扩展】自动提交事务 当开启事务之后执行了DDL语句时事务会自动提交。 begin;
delete from login where namedisen;
create table abc(name varcher(20));
--以下提交或回滚事务没有任何意义因为上一行发生了DDL语句自动提交了事务
commit;10、用户和权限
10.1查看当前系统用户
select user,host,password from mysql.user;【说明】mysql.user表存储系统的所有用户信息。user字段表示用户名host表示当前用户登录的主句ip地址。如localhost表示只能在本地进行登录进入到mysql环境中。如果用户需要在10.36.174.22主机上登录mysql系统需要更改host字段值。理解为host即是可登录mysql系统的ip白名单。
10.2创建用户
语法
create user 用户名主机地址 identified [with mysql_native_password] by 口令;【注意】with mysql_native_password 是mysql8.0向下加密兼容
如1创建任何主机可连接的sdd用户口令sdd 任意主机% use mysql;
create user sdd% identified by sdd;10.3修改用户口令
语法
set password [for user]password(口令)|old_password(口令)|口令user部分包含用户名和主机的
如将sdd用户的口令修改为sdd888
set password for sddpassword(sdd888);语法2修改用户口令过期和口令
--口令失效
alter user 用户名主机 password expire;
--修改口令
alter user 用户名主机 identified by 口令;10.4删除用户
语法
drop user 用户名主机 或 用户名;如删除disen用户
drop user disen;10.5用户授权
语法
grant 权限 on [table|function|procedure] *|*.*|db.*|db.table
to 用户; --数据库.表|函数|存储过程
--刷新权限
flush privileges;如1授予disen用户操作所有数据库的权限
grant all on * to disen;如2授予disen用户管理stu数据库的所有权限
grant all privileges on stu.* to disen;如3创建jack用户操并授予查询stu数据库所有表权限
create user jack% identified by jack;
grant select on stu.* to jack;如4授予jack用户向stu.student表中插入数据的权限
grant insert on stu.student to jack;如5授予jack用户刷新权限
grant reload on *.* to jack;10.6撤销权限
语法
revoke [all privileges] [权限名]
on [table|function|procedure] *|db.*|db.table from 用户如1撤销jack用户向stu.student插入权限
revoke insert on stu.student from jack;如2撤销jack查询stu库的所有权限
revoke select on stu.* from jack;11、mysql编程
11.1 函数编程
语法
create function 函数名([参数 数据类型[长度],...])
[deterministic] [comment 说明]
returns [数据类型(长度)]
return 返回的函数结果数据;如定义aes加密函数实现aes加密
create function aes(str1 varchar(20)) returns varchar(50)
return hex(aes_encrypt(str1,123%^*));
--使用aes函数
select ase(disen);如实现aes加密和解密算法
--删除函数
drop function aes;create function aes(str1 varchar(50),flag int) returns varchar (50)
return if(flag0,hex(aes_encrypt(str1,123%^*)),if(flag1,
aes_decrypt(unhex(str1),123%^*),无效的内容));将以下的aes函数中的判断尝试修改为case表达式
drop function aes;
create function aes(str1 varchar(50),flag int) returns varchar (50)
return case flag when 0 then hex(aes_encrypt(str1,123%^*))when 1 then aes_decrypt(unhex(str1),123%^*),else 无效的内容 end;11.2 存储过程
语法
delimiter
create procedure 存储过程名([in|out|inout] 参数名 数据类型(长度),...)
beginselect into 语句;
end
delimiter ;因为在存储过程的body中可能有sql语句需要执行默认sql语句结束标识符是;一个完整的sql语句也是使用sql结束为了避免创建完整的存储过程之中出现;可以使用delimiter结束符命令声明临时的sql结束符。
如定义村相互过程查询某一个学科的平均分
delimiter
create procedure course_avg(cname varchar(20),out avg decimal)
beginselect avg(score) into avgfrom sc natural join coursewhere namecname;
end
delimiter ;执行存储过程
--调用存储过程参数写法常量变量 变量以开头变量名
call 存储过程名(参数....)--查询存储过程执行的结果
select 变量名;call course_avg(语文,avg);
select avg如2创建存储过程查询不同学科的最高分、最低分、平均分和总分,并直接显示
delimiter
create procedure course_avg(cname varchar(20))
beginselect cname,avg(score) avg,max(score) max,min(score) min,sum(score) sumfrom sc natural join coursewhere namecname;
end
delimiter ;删除存储过程
drop procedure 存储过程名;11.3 触发器 触发器监听对数据的操作事件如当删除数据时如果存在删除事件监听的触发器则会执行触发器。在触发器可以对操作前或后的数据进行处理备份、验证。 创建触发器语法
create trigger 触发器名 before|after insert|update|delete
on 表名 for each row
trigger_body【重要说明】在触发器的body中存在两个特殊的对象OLD、NEW 分别代表是原行数据和新行数据。如针对before delete事件触发器来说OLD表示即将删除的数据(元组)。如针对before insert事件触发器来说NEW代表是即将插入的数据元组。
如1备份一个空的成绩表当成绩表的数据被删除时将原数据插入到备份的成绩表
--创建一个空的成绩表
create table sc_copy as
select * from sc where 1!1;--创建触发器
create trigger watch_sc_delete before delete on sc for each row
insert into sc_copy values(OLD.sid,old.cid,OLD.score);--尝试删除一条记录
begin;
delete from sc where sid07 and cid03;
select * from sc_copy;
rollback;如2创建一个用户登录表表中存在账号和口令的字段并插入2条记录同时创建登录表的更新后的触发器在触发器将更改前后的口令保存到一个用户更新日志表中字段更新时间账户原口令新口令
--创建登录表 login
create table login(id int primary key auto_increment,name varchar(20) unique,pwd varchar(20) notnull
);--创建变更登录用户口令日志表
create table update_pwd_log(id int primary key auto_increment,update_time timeusername varchar(20),old_pwd varchar(50),new_pwd varchar(50)
);--插入两位登录的用户
insert into login(name,pwd) values
(disen,123),
(jack,888);--创建触发器
create trigger watch_update_pwd after update on login for each row
insert into update_pwd_log(update_time, username, old_pwd, new_pwd)
values (current_timestamp(), OLD.name, OLD.pwd, NEW.pwd);-- 变更disen用户的口令为666
update login set pwd666 where namedisen;
