SQL

背景

字体颜色


show variables like 'character%' # 查看编码相关

    +--------------------------+--------------------------------+
    | Variable_name            | Value                          |
    +--------------------------+--------------------------------+
    | character_set_client     | latin1                         |
    | character_set_connection | latin1                         |
    | character_set_database   | utf8mb4                        |
    | character_set_filesystem | binary                         |
    | character_set_results    | latin1                         |
    | character_set_server     | utf8mb4                        |
    | character_set_system     | utf8mb3                        |
    | character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
    +--------------------------+--------------------------------+

  • 简单说明：

    • character_set_server： 这个变量表示 MySQL 服务器使用的默认字符集。它定义了在没有明确指定字符集的情况下，服务器应该使用哪种字符集来处理数据。
    • collation_server： 这个变量表示 MySQL 服务器使用的默认排序规则（collation）。排序规则影响了字符串的比较和排序方式。它与字符集有关，因为不同的字符集可能使用不同的排序规则。

  • 可能遇到的问题：

    • 中文乱码，因此要把character设置成 utf-8，使用docker安装的时候需要注意这点

基本概念

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）

  1. MySQL Command-Line Client
  2. MySQL Workbench
  3. HeidiSQL
  4. DBeaver
  5. Navicat
  6. Sequel Pro (macOS)
  7. phpMyAdmin (Web-based)
  8. Toad for MySQL
  9. MySQL Shell
  10. 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六种类型的触发器。