文章目录
MySql
-
启动与关闭:
- 服务面板启动和关闭
- 管理员模式启动命令行
net start mysqlxx
启动MySQL服务,mysqlxx 是服务名net stop mysqlxx
关闭MySQL服务
-
登录
- 通过MySQL提供的客户端进行登录,此方式仅限于root账户登录
- 命令行操作:
mysql -h localhost -P host -u 用户名 -p 密码
- 如果主机是本地,且端口号默认可以直接使用
mysql -u 用户名 -p 密码
进行登录
- 如果主机是本地,且端口号默认可以直接使用
约束
概念:对表中的数据进行限定,保证数据的正确性,有效性和完整性。
分类:
- 主键约束:
primary key
(非空且唯一,一张表中只能由一个字段为主键,主键就是表中记录的唯一标识)- 注:删除主键只能用 alter table 表名 drop primary key;创建表后添加主键:alter table 表名 列名 change 列名 类型 限制; 或 alter table 表名 modify 列名 类型 限制;
- 非空约束:
not null
在创建时放列名后,或用alter 对表中的列名进行修改,列明后跟上限制 或 不给予限制 - 唯一约束:
unique
( 值不可以重复,但可以存在多个NULL值) 注:唯一约束在用“alter table 表名 列名 change to 列名 (不跟限制)”这样是取消不了UNIQUE的限制 而应该使用“alter table 表名 drop index 列名(想要取消unique的列名)”。 - 外键约束:
foreign key
表中数据的关联,让表与表之间建立关系,保证数据正确性
建表时:
1.语法:create table 表名(
-> ……
->外键列
->constraint 外键名称 foreign key (外键字段名称 )references 主表名称(主表关联列名称);
->);
建表后的删除与新加的语法:
删除:alter table 表名 drop foreign key/外键名;
新加,alter table 表名 add constraint 外键名称 foreign key (外键字段名)references 主表名称(主表关联列名称);
2.级联更新和级联删除
级联更新:on update cascade
级联删除:on delete cascade
语法:直接跟在外键的创建后 - 自动增长,如果某一列是数值类型,使用
auto_increment
可以来完成自动增长,一般配合主键完成,删除和添加自动增长与NOT NULL 相似。
SQL
- 什么是sQL?
structured Query Language:结构化查询语言其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”。 - SQL通用语法
1.SQL语句可以单行或多行书写,以分号结尾。
2.可使用空格和缩进来增强语句的可读性。
3.MySQL数据库的SQL语句不区分大小写,关键字建议使用大写。
3.MySQL注释:
单行注释: --(空格)内容 或 #(空格可有可无)内容
多行注释: /* 注释内容 */ - SQL分类
1)DDL(Data Definition Language)数据定义语言用来定义数据库对象:数据库,表,列等。关键字:create,drop,alter等
2)DML(Data Manipulation Language)数据操作语言用来对数据库中表的数据进行增删改。关键字:insert,delete,update等
3)DQL(Data Query Language)数据查询语言用来查询数据库中表的记录(数据)。关键字:select,where等
4)DCL(Data control Language)数据控制语言(了解)用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT,REVOKE等
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EuPXNJxQ-1604210818423)(index_files/102173359.png)]
DDL
- 操作数据库:CRUD
-
C: create 创建数据库
* create 数据库名称;
create database if not exists 数据库名称;
判断是否存在然后创建
create database 数据库名称 character set 字符集;
-
R:retrieve 查询
查询所有数据库名称:
show databases;
查询某个数据库字符集:查询某个数据库的创建语句
show Create database 数据库名称;
-
U:Updata
修改数据库字符集
alter database 数据库名称 character set 字符集名称;
-
D:Delete
删除数据库
drop database 数据库名称;
判断数据库存在后再删除
drop database if exists
数据库名称; -
使用数据库
查询当前正在使用的数据库名称
select database();
使用数据库
use 数据库名称;
- 操作表
- C:create
- 语法
* create table 表名( 列名1 数据类型1, 列名2 数据类型2, …… 列名n 数据类型n (注:最后一行不加“,”) );
- 数据库类型
int
age int,double
score double(小数位数,小数点后位数),date
: 只包含年月日 “YYYY-MM-DD”datetime
:包含时间timestamp
: 格式与datetime相同,区别在于,如果不给这个赋值,或指定为null;则默认为当前系统时间来自动赋值。varchar
(字符最大长度)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KHcyf2cT-1604210818426)(index_files/104384765.png)]
-
创建表
create table student(
id int,
name verchar(20),
age int,
score double(4,1),
birthday date,
insert_time timesramp
);复制表
create table 表名 like 被复制的表名; -
R:retrieve 查询
查询某个数据库中所有表的名称
show tables;
查询表结构
desc 表名;
查询表的字符集:查询表的创建语句
show create table 表名 -
U:Updata
1.修改表名
alter table 表名 rename to 新的表名;
2.修改表的字符集
alter table 表名 character set 字符集名称;
3.添加列
alter table 表名 add 列名 数据类型
4.修改列名
alter table 表名 change 列名 新列名 新数据类型
alter table 表名 modify 列名 新数据类型
5.删除列
alter table 表名 drop 列名; -
D:Delete
drop table 表名;
drop table if exists 表名; -
使用数据库
DML : 增删改表中的数据
- 添加数据:
-
语法:
insert into 表名(列名1,列名2,……,列名n) values (值1,……,值n);
注意事项:1. 列名和数值一一对应。
2. 如果表名后,不定义列名,则默认为所有列添加数据
3. 除了数字类型,其他的类型均要用引号引起来,可单可双- 写列名参数和不写列名参数
- 写参数的方式可以一次插入多行
- 写参数的方式支持子查询
- 写列名参数和不写列名参数
- 删除数据:
- 语法
delete form 表名【where 条件】;
注意:如果不加条件,则会删除所有数据
如果要删除所有记录
1. delete form 表名; ---------》 不推荐,因为有多少记录就会执行多少次删除操作
2. truncate table 表名 删除所有记录,先删除表,然后创建一张一样的空表
- 修改数据:
- 修改单表的记录.
- 语法:
update 表名 set 列名1 = 值1,列名2=值2,……【where 条件】;
注意:若不加任何条件,则会更改表中所有的记录。
- 语法:
- 修改多表中的记录
- 修改单表的记录.
# 92语法
update 表一 别名,表二 别名
set 列=值,...
where 连接条件
and 筛选条件
# 99 语法
update 表一 别名
inner | left | right join 表二 别名
on 连接条件
set 列=值...
where 删选条件;
DQL : 查询表中的记录
* select * from 表名;
*
语法
select
字段列表
from
表名
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
基础查询
- 基础查询
- 多个字段查询
- select * from 表名;(*代表所有,全部,可以用想要查询的字段代替就可以只显示字段列了)
- select 字段1,字段2,字段2,…… from 表名;
- 去除重复的结果集
select distinct 想要信息 from 表名; - 计算列
- select 列名,列名,……,列名(运算符)列名 from 表名;
如果有null参与的计算都为null;
可以使用select 列名,列名,……,IFNULL(列名,替换值)(运算符)列名 from 表名;(注:可以在任何可能为null的值前面加上IFNULL,将NULL值替换)。 - 起别名(将运算后的值所属列的列名更改)
select 列名,列名,……,IFNULL(列名,替换值)(运算符)列名 AS 别名 from 表名;(AS可以用“空格”替换),所有的列名都可以通过这种方式进行修改
当别名或者列名与关键字下相同,或者包含特殊符号(例如 空格、# ……)则可以使用双引号或者单引号包裹(mysql不区分字符和字符串)
- select 列名,列名,……,列名(运算符)列名 from 表名;
- 多个字段查询
条件查询
- 条件查询
where
子句后跟条件- 运算符
-
.<,<=,>=,=,<> (sql中没有“==”号,<>是不等号,NULL值不能用运算符判断,只能用IS 或 IS NOT)
-
BETWEEN...AND
- 大于等于左边的值,小于等于右边的值
-
IN(集合)
IN(...);
-
LIKE
(模糊查询)-
占位符:
-
“_”:单个任意字符
-
“%”: 多个任意字符
-
-
转义字符
- \
- 自定义一个,使用escape关键字
...where name LIKE '_$_%' ESCAPE '$'
-
-
IS NULL
-
AND
或&&
-
OR
或||
-
NOT
或!
eg:select * from 表名 where 条件; (例:age>20)
排序查询
- 排序查询
-
语法:order by 子句
- select * from 表名 order by 排序字段1 排序方式, 排序字段2 排序方式2……
-
排序方式:
- ASC: 升序(默认为ASC)
- DESC: 降序
若有多个排序条件,当前一个值的判断一样时,才用到下一个条件
-
聚合函数(又叫分组函数,统计函数或组函数):将一列数据作为一个整体,进行纵向计算
count
: 计算个数
1.一般选择非空的列:键值
2.select count()from 表名。
3.select count(1);或 select count(字符);
4.效率
* MyISAN存储引擎,count()最高
* InnoDB存储引擎,count(*)和count(1)较高max
:min
:avg
:null值不参与运算sum
:null值不参与计算
语法:select 聚合函数 (列名) from 表名; 注意:聚合函数的计算会自动排除NULL值。当然可以使用 IFNULL(列名,替换值);
-
分组查询
语法:group by 分组字段;
注意:分组之后查询的字段:分组字段 eg: select 分组字段 ,聚合函数(列名) from 表名 group by 分组字段;
where 和having 的区别:
*** 分组前可以用where 限定参与分组成员,分组后可用having 限定可被查询的组,且where 后不能跟聚合函数的判断,而having 后可以跟*** 且可以给聚合函数后得到的组起别名
-
多表连接查询
- 多表查询
- sql92
- 内连接
- 等值连接
- 非等值连接
- 自连接
- 支持一部分外连接,但是也不稳定
- 内连接
- sql99
- 语法
select 查询列表 from 表一 别名 【连接类型】 join 表二 别名 on 连接条件 [where 筛选条件] [having 筛选条件] [order by 排序列表]
* 内连接 连接类型inner 1. 等值连接 2. 非等值连接 3. 自连接 * 外连接: 1. 左外连接: * 连接类型left[outer] * 左边的是主表 2. 右外连接:连接类型right[outer] 3. 全外连接***(MySQL不支持)***:连接类型full[outer] 4. 注意 * > 外连接的查询结果为主表的所有记录 如果从表中有和它匹配的,则显示匹配的值 如果从表中没有和它匹配的,则显示null 外部连接的查询结果=内连接的结果+主表中有而从表中没有的记录 * 交叉连接:连接类型 cross * 笛卡尔积
- sql92
分页查询
语法:limit 开始的索引,每页查询的条数
select * from 表名 limit 开始的索引,显示的条数; 注:开始的索引=(当前页码-1)*每页显示的条数。
分页操作是一个“方言”------与其他数据库产品的语句不同
子查询
- 概念:出现在其他语句内部的select语句,称为子查询或内查询,内部嵌套其他select语句的查询,称为外查询或主查询
select first_name from employees where department_id in( select department id from departments where location id=1700
- 分类
- 按子查询出现的位置
select
后面- 仅仅支持标量子查询
from
后面- 支持表子查询
where
或having
后面- 标量子查询
- 列子查询
- 行子查询
exists
后面(相关子查询)- 表子查询
- 只关心是否有元素
- 按结果集的行列数不同
- 标量子查询(结果集只有一行一列,也叫单行子查询)
- 列子查询(结果集只有一列多行,也叫多行子查询)
- 行子查询(结果集有一行多列)
- 表子查询(结果集一般为多行多列)
- 按子查询出现的位置
where或having后面
- 标量子查询(单行子查询)
- 列子查询(多行子查询)
- 行子查询(多列多行)
- 特点:
- 子查询放在小括号内
- 子查询一般放在条件的右侧
- 标量子查询,一般搭配着单行操作符使用
- 单行操作符:> < >= <= <>
- 非法使用标量子查询:即查询结果不止一行一列
- 列子查询,一般搭配着多行操作符使用
- 多行操作符:in/not in、any/some、all
- in/not in:等于列表中的任意一个
- any/some:和子查询返回的某一个值进行比较
- all:和子查询返回的所有值进行比较
- 多行操作符:in/not in、any/some、all
联合查询
- union 联合 合并:将多条查询语句的结果合并成一个结果
-
语法
- 查询语句一 union 查询语句二……
-
注意事项
- 要求多条查询语句的查询的列数一致
- 使用union会自动去重,可以使用 union all可以包含重复项
-
常见函数
-
字符函数
length()
:获取参数值的字节个数concat()
:拼接字符串UPPER()
,LOWER()
:转换大小写substr()
,substring()
:字符串的截取,注意MySQL中的索引都从1开始instr(str,substr)
:返回后一个字符串在前一个字符串中出现的起始索引trim()
:去掉指定的字符串,如果没加则默认去掉空格只能去掉前后的字符,- 例:
TRIM(a FROM 'aaaaa你好呀,一身都是月~aaaa')
- 例:
lpad()
,rpad
:用指定的字符填充字符至指定长度replace()
:替换
-
数学函数
round()
:四舍五入ceil()
:向上取整floor()
:向下取整truncate()
:截掉小数点后第几位后的所有mod()
:取余
-
日期函数
new()
:返回当前系统日期+时间curdate()
:返回当前的系统日期curtime()
:返回当前的时间year()
,month()
,monthname()
……,获取指定字段的值str_to_date()
:将日期格式的字符转换成指定格式的日期date_format
:将日期转换成字符串- 格式的书写:[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Cu0OBi7v-1604210818430)(C:\Users\Lenovo\Desktop\学习\笔记文档\数据库\数据库日期格式.png)]
-
其他函数
version()
database()
user()
-
流程控制函数
IF()
:if else 的效果
-
if(10<3,'big','small');
返回smallcase()
- 使用一:switch case 的效果
case 要判断的字段或表达式 when 常量1 then 要显示的值1 或语句1; when 常量2 then 要显示的值2 或语句2; ... else 要显示的值n 或语句 n; end;
- 使用二:多重if的效果
case 条件1 then 要显示的值1或语句1 ... else 要显示的值n或语句n end
- 使用一:switch case 的效果
数据库的设计
- 多表之间的关系
- 分类
- 一对一
- 一对多(多对一)
- 如:部门和员工的关系
- 多对多
- 如: 学生和课程
- 实现关系
- 一对多(多对一)
在多一方建立外键,指向少的一方的主键。 - 多对多
需要借助第三张中间表,中间表中包含两个字段,这两个字段分别作为中间表的外键分别指向两张表的主键,中间表中的两个字段序列不能重复,称为联合主键
- 一对多(多对一)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2teRUOGs-1604210818446)(index_files/205532187.png)]
3. 一对一
可以在任意一方指定外键并且需要让外键唯一
-
范式(数据库设计规范)
第一范式(1NF):每一列都是不可分割的原子数据项、
第二范式(2NF):在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
几个概念:
函数依赖:A–>B 通过a的属性值能唯一推出B的属性(属性组)的值。
eg:学号–>姓名, (学号,课程名称)—>成绩
完全函数依赖:如果A是一个属性组,则B 属性值的确定需要A 属性组中所有的属性值。
eg:(学号,课程名称)—>成绩
部分函数依赖:如果A是一个属性组,则B属性值需要依赖于A属性组中的部分属性值即可。
eg:(学号,课程名称)—>姓名
传递函数依赖:如果通过A属性值(属性组),可以唯一确定B的属性值,再通过B属性值(属性组的值)可以唯一的确定C属性的值。eg“A—>B,B---->C.
码:如果一张表中,一个属性或属性组,被其他所有的属性所完全依赖,则称这个属性(属性组)为该表的码。
* 主属性:码属性组中的所有属性
* 非码属性:除码属性组中的属性
第三范式(3NF):在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖) -
数据库的备份与还原
-
命令行
- 语法:
备份:mysqldump -u用户名 -p密码 数据库名称 > 保存路径
还原:创建好数据库后,source 路径 备份文件;
- 语法:
-
图形化界面
……
-
多表查询
可以给表给别名,
select
------字符段
from
------表名 别名,表名 别名
……
select * from 表一,表二;
- 笛卡尔积
- 两个集合的所有组成情况
- 要完成多表查询,需要消除无用的数据
多表查询分类
- 内连接查询
- 确定需要查询的表,条件,查询字段 !!!
1. 隐式内连接:使用where 条件
2. 显式内连接:select 字段列表 from 表名1 【inner】 join 表名2 on 条件; 注【】表示可以省略
eg:select * from 表一 【inner】 表二 on 条件;
- 外连接查询
1 左外连接
* 语法: select 字段列表 from 表一 lefr 【outer】 jion 表二 on 条件;
查询交集部分,查询左表所有记录,并求所有交集部分
2 右外连接
* 语法: select 字段列表 from 表一 right 【outer】 jion 表二 on 条件;
查询交集部分,查询右表所有记录,并求所有交集部分 - 子查询
- 查询中嵌套查询,则称为子查询
一个select 中嵌套另一个select 语句 - 基本语法:select * from 表 where 字段一 运算符 (slect 聚合函数(字段) from 表;
- 子查询的不同情况
1. 结果单行单列
子查询可以作为条件,使用运算符判断 例:工资少于平均工资
2. 结果多行单列
子查询可以用运算符 in 判断
3. 结果多行多列
子查询作为一张虚拟表
eg: select * from dept as 别名一,(select * from emp where emp.age>20) as 别名二 where 别名一.id=别名二.id;
事务
DCL
变量
- 系统变量
- 全局变量
- 会话变量
- 自定义变量
- 用户变量
- 局部变量
系统变量
由系统提供,不是用户自定义,属于服务器层面
使用
语法:
SHOW GLOBAL/[SESSION] VARIABLES
查询所有的系统变量- `SHOW GLOBAL/[SESSION] VARIABLES LIKE ‘%_%’ 查看满足条件的部分系统变量
SELECT @@GLOBAL/[SESSION] .系统变量名
查询指定的系统变量SET GLOBAL/[SESSION] 系统变量名=值
为某个系统变量名赋值SET @@GLOBAL/[SESSION]. 系统变量名=值
为某个系统变量名赋值
注意:如果是全局级别,则需要加global,如果是会话级别,则需要加session,如果不加则默认session
全局变量:
作用域:服务器每次启动都将为所有全局变量赋值,针对所有的会话(连接)有效,但是不能跨重启
会话变量:
作用域:仅仅针对当前会话(连接)有效
-
用户变量
-
声明并初始化
SET @用户变量名=值
SET @用户变量名:=值
SELECT @用户变量名:=值
-
赋值
- 通过
SET
或SELECT
- 通过
SELECT INTO
SELECT 字段 INTO 变量名 FROM 表名
- 通过
-
-
局部变量
作用域:仅仅在定义它的begin end 中有效 -
声明:
DECLARE 变量名 类型
DECLARE 变量名 类型 DEFAULT 值
-
赋值
- 通过set 和select
- 通过select into
存储过程和函数
含义:预先编译好的SQL语句的集合,理解成批处理语句
优点:
- 提高代码的重用性
- 简化操作
- 减少编译次数、和数据库服务器连接的次数,提高效率
创建:
CREATE PROCEDURE 存储过程名()
begin
存储过程体
end
注意:
- 参数列表包含三个部分
- 参数模式
- IN:该参数可以作为输入
- OUT:该参数可以作为输出,返回值
- INOUT:既可以作为输入,也可以作为输出
- 参数名
- 参数类型
- 参数模式
- 如果存储过程体仅仅只有一句话,begin end可以省略,存储过程体中的每条SQL语句的结尾要求必须加分号
- 存储过程的结尾可以使用
DELIMTER
重新设置- 语法:DELIMTER 结束标记
- 案例:DELIMTER S;
调用
删除
drop procedure 存储过程名;
drop procedure 存储过程1,存储过程2…;
查看
show create procedure 存储过程名
函数
有且仅有一个返回,适合做处理数据后返回一个结果
创建
create function 函数名(参数列表) returns 返回类型
begin
函数体
end
注意:
- 参数列表:包含两部分,参数名,参数类型
- 函数体:肯定会有return语句,如果没有会报错,如果return没有放在函数体的最后也会报错
- 如果函数体中只有一句话,begin 和 end 可以省略
- 使用 delimiter 语句设置结束标记
查看
show create function 方法名;
删除函数
drop function 方法名
文章待后续完善