数据库
背景
字体颜色
基本概念
DB:数据库:保存的是数据,保存一组有有组织的数据的容器
DBMS:数据库管理系统:我们用来操作数据的系统(数据库软件)
分类:
(1):基于共享文件系统的
(2):基于客服机的,需要安装客户端和服务端
SQL(Structure Query Language):结构化查询语言(专门用来与数据库进行通讯的语言)
SQL的优点:
1、SQL语言是几乎所有的DBMS都支持的一门语言,
2、比较简单易学(相对于Java),
3、相关的职位:DBA(Database Administrator):数据库管理员,
DB存储数据的原理:
1、先将数据存在表里面,再将数据存在库里面
2、一个数据库可以有多个表,但是每个表的名字都不能重复,要保持唯一性
3、表的里面定义了数据在表中是如何存储的,这个就类似于java中的类
4、每个表都是由列组成的,我们通常将列称之为字段。所有的表都是有一个或者多个列组成的,每一个列类似于java中的“属性”
5、表里面的数据是按照行来存储的,每一行类似于java中的“对象”
基本规则
- SQL中显示的表格必须是规则的
- 对于SQL92语法,能看懂就可以
- 掌握SQL99语法,并且能够熟练使用
- 通过使用 `` 防止与关键字冲突
MySQL服务的启动
可以设置为自动自动启动和手动启动
手动启动
1、进入服务,点击开始或者停止
2、命令行进行操作
2.1 net stop mysql #关闭MySQL服务 2.2 net start mysql #开启MySQL服务
MySQL服务的登录和退出
1、前提:服务是启动的
2、命令行进入MySQL: mysql -h localhost -P 3306 -u root -p(主机 ->端口号->登录账号->密码)
3、如果是localhost的话,可以直接写: mysql -uroot -proot
4、使用MySQL提供的控制台只能是登陆root用户
相关简单命令
show databases; #展示数据库 schema:架构、方案
information_schema
mysql
performance_schema
#上面这三个是系统自带的数据库、不能动
use XXX;#进入某个数据库
desc(describe)#打开某个表
select version(); #查看MySQL的版本
show tables; #查看的当前所有的表
show tables from db;#查看某个数据库的所有的表
create table tableName(
列名 类型,
....
);
desc 表名;//查看表的结构
select version();//查看版本(已经登录之后)
set names utf8;#将字符集设置为utf8
一般的查询代码
select 查询列表
from 表1 别名 [连接类型]
join 表2 别名 #jion指出加入的表
on 连接条件 #on中指出连接表的条件
[where 筛选条件]
[group by]
[having]
[order by]
[limit a,b] # 如果欧就是在最后
连接说明
内连接 inner :显示两个表中有联系的所有数据 #inner join 求得的结果是一个交集
外连接
左外 left [outer] :用左边的表作为参照,左边的是主表 #left join
右外 right [outer]:用右边的表作为参照,右边的是主表 #right join
全外 full [outer]:MySQL不支持 # 全外连接 = 内连接 + 表一独有 + 表二独有 最后得到一个大表 求得的结果是并集
交叉连接 cross #cross join 对应的就是笛卡尔乘积
5、MySQL不区分大小写(但是建议关键字大写)
7、MySQL是属于cs架构的,我们一般而言就只是安装服务端
MySQLUI(我使用的是sqlyog 一般还可以使用navicate)
- MySQL Command-Line Client
- MySQL Workbench
- HeidiSQL
- DBeaver
- Navicat
- Sequel Pro (macOS)
- phpMyAdmin (Web-based)
- Toad for MySQL
- MySQL Shell
- TablePlus
单行函数(每一个字段中的每一个元素都会返回一个值)
SQL函数
字符函数
concat();#用来连接多个字符串,在连接之前会进行类型转换
length();#用来进行计算字符串的字符的长度,记住,是字符长度,不是字节长度
upper();#和C语言那些一样的
lower();#
substr();#用来返回一个子字符串
substr(str,3);#用来返回str的从3好位置开始的到最后的字符串
substr(str,x,y);#用来返回从x号位置开始的总共的字符的数量是y的字符串
replace(str,from,to);#从 str 中 将 from 改为to
instr(str,substr);#返回substr在str中的首个位置,没有的话就返回0
trim(str);#一般的情况下就是将str两边的空字符串去掉
trim(a from str);#将str中的a去掉
lpad(str,finalLength,x);#将x填充到str的左边,最终的长度是finalLength
rpad();
数学函数
round(num);#表示的进行四舍五入
round(num,accuracy);#表示的是四舍五入之后的保留的位数是accuracy
ceil();#表示的向上取整
floor();#向下取整
truncate(num,x);#截断的意思保留x个小数位,其他的注解舍弃
mod();#表示的取余数
日期函数
now();#返回当前的日期和时间
curdate();#返回当前的额日期
curtime();#返回当前的时间
year(param);#将参数param中的year提取出来
date();#将参数的date提取出来
month();#
str_to_date();#将字符串转化成日期
date_format();#将日期转化成字符串
from_unixtime(238403289,"%Y-%m-%d %H:%m:%S"); #时间戳到字符串
其他函数
select version();#打印MySQL版本号
select database();#打印当前的是谁数据库
select user();#展示当前的用户
in(,,);#这个函数用来表示是否在里面
流程控制函数
if();#使用和三元运算符一样
if(x,y,z);#x为真返回y,x为假返回z
还有的一个就是case
case的使用
case的使用情况一(类似switch)
注意:这点不像vb.net,在SQL中只需要end就表示结束
case的使用情况二(有一点像if嵌套)此时的case后面不带参数
1、DQL(data query language)
SELECT DISTINCT department_id FROM employees; distinct实现了对XXX去重
如果in()里面写的是直接的字符串的话,那就只能是字符串
使用like的时候将会将数据转化成 字符型
通配租 % :人员字符 _一个任意字符
排序使用的是order by 列 asc | desc 支持多个条件(字段)进行排序,一般的情况下我们都是按照单个字段进行排序
asc表示的是升序,是默认的值
desc表示的是降序,不是默认值
order一般都是放在代码的最后面(其实就是除了limit子句),排序是最后进行执行的
字符串连接:concat(str1,str2);
使用length函数的时候,会将里面的数据转化成字符的形式在进行计算长度
所有的数据库中的字符的计数开始都是1,和VB.NET是一样的
substr(str,x,y):表示的是截取str从x位置开始,截取的长度是y
函数的使用(任何一个函数在使用的时候都必须加上select)
在字符的操作之前都会将参数转化成字符类型,实际上SQL中所有的函数都是这样的
首先函数都是像C语言那样进行使用的,而不像java那样直接.就可以了
分组函数(用于统计,又称统计函数,最终得到的还是一个值),一个字段的数据只有一个返回值
sum();#可以用来求一个字段的和 avg();#可以用来计算总的平均值 max();#可以用来求最大值 min(); count();#对于NULL数据,就不会进行相应的计数 #总结1 :sum avg适合数值类型的max min count适合任何类型的数据 #总结2 :对null的处理 sum mix max avg count 直接忽略null #总结3:可以和distinct一起使用
count函数来统计表中的行的数量
select count(*) from 表 #统计表中的行的数量,只有某一行的值全是null的手才会不计入 select count(1) from #有些时候我们会使用来计数, #效率:使用*的是偶效率不会低,所以我们 都是使用count(*)
和统计函数统计使用的字段是group by后的字段
函数转化说明
如果是参数的话,那个所有的数据都会在参与运算之前进行类型转化
对于转化成数字类型的,转换失败的话就是0
分组查询(select相连接的那一部分是最后运行的)
使用having关键字
SELECT COUNT(*) AS 组内数量,LENGTH(last_name) + LENGTH(first_name) AS 姓名长度 FROM employees GROUP BY LENGTH(last_name) + LENGTH(first_name) HAVING COUNT(*) > 5; # 注意这个是在count(*) 是在分组之后再进行分别计数的
上面这个脚本下面还可以追加排序的代码
从两个表中获取数据
SELECT * FROM table1 INNER|LEFT|RIGHT|FULL JOIN table2 ON table1.column_name = table2.column_name;
自连接(各种连接的模型和我想的是一样的)
自连接是指在同一张表中进行连接操作,通常需要使用别名来区分被连接的同一张表的不同实例。自连接通常用于在表中查找相关联的数据。
别名
在 SQL 中,使用别名需要使用
AS
关键字来为表、列或计算结果指定临时的名称。AS
关键字是可选的,你也可以省略它,直接使用别名。having 和 group by 建议不要在这点取别名
关于SQL标准,应该使用的是sq99标准
多个表进行连接的时候,通过where条件可以实现将每个表按照一定的要求重新排序
2、DML(data manipulation language)
插入的方式
方式一(支持子查询:[就是在即查询输出了结果,又将结果插入了表里面],可以多行插入)
inertt into 表名 #不用谢table这个关键字 values() #子查询的形式 INSERT INTO users #只要是涉及到的类似嵌套的语句都尽量用一个小的括号包起来 (SELECT id,`name` FROM beauty);
1、将所有的表的名字都一一列出来,然后在 value(...)里面进行对号入座,将所有的信息都按照顺序填写进去
2、直接在insert into之后写出对象的键
3、可以省略列名,但是列的顺序和表中的kn是一致的
插入的规则
1、插入的顺序可以和显示出来的顺序不一样,但是输入的值必须和相应的键对应
2、总之就是键值对必须匹配
方式二(不支持子查询,不能多行插入)
insert into表名 set 列名=,列名=,...
修改的方法
方式一:修改单个表的记录
update 表名 ① #这点不需要写table这个关键字 set 列=新值,... ③ where 筛选条件#和查询的那个一模一样的 ②
方式二:修改多个表的记录(其实就是这两个表有关联,放在一起的话,我们就可以根据一个表的联系来更改另外一个表的数据)
#语法一般使用sql99
删除的使用
删除的话都是是删除一整行
1、单表的删除
delete from 表名 where 筛选条件 #没有直接指定是哪个表,之间选择满足条件的进行删除 #delete的删除是一条一条进行删除的
2、多表的删除
truncate table 表名 #没有筛选条件 truncate 表名#这个进行的是全部的删除
3、DDL(data definition language)
数据库的创建等,是在结构上的增删改查
必须使用的关键字 create alter drop才能够改变相关的属性
相关的使用语言ddl即为data define language
删除一类的仅仅是delete配合from一起使用
数据库的删除和创建
DROP DATABASE books; #这里必须指明是哪个数据库 #数据库的删除 CREATE DATABASE books; #这里必须指明是哪个数据库 DESC XXX;#DESC这个关键字的操作对象是table USE XXX;这个关键字的使用对象时数据库
define of column
约束的分类 六大 not null:表示的是非空约束,用来保证这个字段的数据必须填写 default:用来保证这个字段有一个默认的值 primary key(默认非空):主键约束,用来保证这个字段的数据具有唯一性,比如学号,员工编号等 unique:唯一约束,也是用来保证这个字段的数据具有唯一性,但是最多有一个为空的, check:检查约束,在MySQL中不支持 foreign key:外键,用来限制两个表之间的关系的,表示该字段的值必须来自主表的关联列的值 一般是在创建表的时候就添加约束,但是一定是在数据添加之前进行这个约束的添加 列级约束 约束的时指定的列 每个约束在语法上都是支持 但是外键约束没有效果 表级约束 在所有的列都写完了之后在写 不支持非空和默认
同样地unique也是可以类似primary key一样组合来设置的
foreign key 引用的是主键或者是唯一键 #一定要记牢 ALTER TABLE info3 MODIFY COLUMN id CHAR(20) PRIMARY KEY;#修改限制和修改类型一样 列级约束 ALTER TABLE info3 ADD COLUMN PRIMARY KEY(id); # 修改表级约束的写法 ALTER TABLE info3 DROP PRIMARY KEY; #删除主键
又有一个别名自增长列 标识列的数据我们可以不用手动不插入 不需要我们手动插入值,系统会给我们提供值 一个表中只能有一个标识列,同时标识列只能和key一起使用 #可以通过set auto_increment_increment = x;来设置增长跨度
4、TCL(transaction control language)事务(交易)控制语言
事物其实就是将多条代码放在一起执行
事物的特性
1、一个事务要么被执行,要么不被执行
2、事务的状态变化具有一致性
3、执行不会其他事务的干扰
4、一个事务一旦提交,数据永久改变
我们学习的显示事务,隐式事务已经被封装好了
1、首选要设置自动提交为禁用
set autocommit = 0; start transiction;#可以不写,也可以写 #事务仅仅针对select insert update delete show variable like ''; #展示出相应的变量 commit; # 提交事务 或者下面 rollback;#以实现回滚事务,可以通过jdbc来进行模拟 savepoint point;#这个关键字是用来设置保存点
2、MySQL中的相关隔离级别
select @@tx_isolation; #查看隔离级别 set transaction isolation level XXX; #设置当前MySQL连接隔离级别设置成XXX set global transaction isolation level XXX;# 设置数据库系统的全局的隔离级别 #事务的隔离界别 #read uncommitted #read committed #repeatable read 默认 #serializable 效率很低下
3、关于回滚
delete 可以回滚 truncate不可以回滚
视图的使用
视图是虚拟的表,保存的是逻辑,而不是真实的数据
#语法: ## 创建视图 create view XXX as select YYY; ##视图的修改 create or replace view XXX as select YYY; #或者 alter view XXX as select YYY; ## 删除视图 drop view v1,v2,v3...; #删除view #查看视图结构 desc XXX; #视图的更新 ##插入##此时其实是将原始表进行修改#最好的就是不对视图进行修改 insert into ##删除 delete from ##修改 update XXX
显示键的信息
SHOW INDEX FROM XXX;#显示table XXX 的相关信息 union all #就这个混在一起 union #进行排序,插入不重复的
cascase的使用(级联删除和级联置空)
#在使用外键的时候在末尾追加 on delete cascase 那么就是连坐 #添加的如果是on delete set null相关的引用的那点就设置为null
变量
##默认查看的都是session变量 #查看每一类变量 show global / session variables like 'XXX'; #查看指定的某个系统变量的值 select @@global / session 系统变量 = 值; #设置变量 set @@global / session 变量 = 值; select @@global.XXX = 值; #查看指定的全局变量的值 select @@global.XXX; #全局的话就需要添加global
#赋值的操作符有两个= := #赋值或者更新数据使用的语法是一样的 SET @a = 10 ,@b = 100; #这样赋值也是支持的 set / select @变量 = 值; select 字段 into 变量名 from 表;
变量的begin和end(局部变量)
@1声明
declare 变量名 类型; declare 变量名 类型 default 值;
@2赋值
set / select 局部变量=值 select XXX into 变量名 from 表;
和用户变量相比的话,局部变量就是多了声明的过程,并且数据的类型不可以改变而且必须放在begin和end中
对比用户变量和局部变量
用户变量是在当前这个会话中使用,但是局部变量使用在begin end中,局部变量都需要声明,并且局部变量有着类型的限定
函数和存储过程(和C语言vb.net那些比较像)
存储过程
1、参数 参数名 参数类型 参数模式 参数模式 in:传入参数 out:返回值 inout:两者均可(但是效率较低下) 2、如果存储体里面只有一句话的话,我们可以不添加begin end 3、使用delimiter来 定义结束标志,默认的情况下我们使用 ';'来表示结束 4、调用call 函数名字();
delimiter $$ create procedure XXX() begin ......; ......; end$$
存储过程的删除
#和表的那些的删除都是一样的 #语法 drop procedure XXX;#将某个存储过程删除
查看存储过程的结构
show create procedure XXX;#就是在创建的前面加上一个show #修改没必要
函数(有且仅有一个返回值),当函数体只有一句话的时候,begin end可以省略
返回的时候,返回值必须已经声明过,函数的名字不可以是内部的函数名字
只要是函数,调用的时候使用的关键字都是 select
因为函数有且仅有一个返回值,因而不需要管是不是传入的参数还是传出去的参数,return就是传出来的参数
SELECT DATEDIFF('1999-1-1','1999-2-1');#这个函数返回的是前面的减去后面的天数 #创建的语法 create function XXX() returns 返回类型 begin ......; ......; return YYY; end
if函数
if结构
#只能运用在begin end中 #语法 if XXX then 语句1; elseif XXX then 语句2; ... [else 语句n]; end if
循环(只能放在begin end中)
#while loop repeat #while #这下面的标签的使用是为了能够进行leave或者iterate [标签:] while XXX do ... end while[]; #loop 这个是没有条件的死循环 #repeat [标签:] repeat #至少执行一次 ... until 结束循环的条件 #util后面没有; end repeat[]; ##控制iterate类似于continue leave类似于break ##leave 标签 iterate标签
触发器
MySQL 触发器(Trigger)是一种在指定事件发生时自动执行的数据库对象。触发器与表关联,当触发事件发生时,触发器会自动触发相应的动作。MySQL 支持在表上定义BEFORE INSERT
, AFTER INSERT
, BEFORE UPDATE
, AFTER UPDATE
, BEFORE DELETE
和AFTER DELETE
六种类型的触发器。