MySQL基础
一、认识MYSQL
数据库相关概念
-
DB（database）
数据库，长期存储在计算机中，有组织 统一管理 可以共享的 的相关数据集合 -
DBA（Database Administrator）
数据库管理员，是从事管理和维护数据库管理系统(DBMS)的相关工作人员的统称 -
DBMS（Database Management System）
数据库管理系统，管理和维护数据库的系统软件。常用的DBMS有：Oracle、DB2、SqlServer、MySql等 -
DBS（Data Base System）
数据库系统，是实现有组织的、动态地存储大量关联数据、方便多用户访问的计算机软件、硬件和数据资源组成的系统，
简化为：DBS=计算机系统（硬件、软件平台、人）+DBMS+DB -
SQL（Structure Query Language）
结构化查询语言，专门用来与数据库通信的语言。
SQL语言的分类
- DQL（Data Query Language）：
数据查询语言，用于检索数据库中的数据，主要是SELECT语句； - DML（Data Manipulation Language)：
数据操纵语言，用于改变数据库中的数据，主要包括INSERT、UPDATE和DELETE语句； - DDL（Data Definition Language)：
数据定义语言，用于库和表的创建、修改、删除。主要包括CREATE、DROP、ALTER语句； - DCL（Data Control Language)：
数据控制语言，用于定义用户的访问权限和安全级别。主要包括GRANT和REVOKE语句； - TCL（Transaction Control Language)：
事务控制语言，用于维护数据的一致性，包括COMMIT、ROLLBACK和SAVEPOINT语句。
二、基本语法
DQL语言
基础查询
- 语法：SELECT 查询列表 FROM 表名;
- 特点：
- 查询列表可以是：表中的字段、常量值、表达式、函数
- 查询结果是一个虚拟表
条件查询
- 语法：SELECT 查询列表 FROM 表名 SELECT 筛选条件;
- 筛选条件的分类：
-
条件运算符：> 、<、 =、 !=、<> (不等于)、>= 、<= 、<=>(安全等于)
-
逻辑运算符
&& 和 and:两个条件都为true，结果为true，反之为false |和 or:只要有一个条件为true，结果为true，反之为false !或 not:如果连接的条件本身为false，结果为true，反之为false -
模糊查询
like:一般搭配通配符使用，可以判断字符型或数值型 通配符：%任意多个字符，_任意单个字符 between and（左小，右大，包括区间值） in(exp1，exp2，…)（相当于=多个or） is null /is not null：只用于判断null值 (=或<>不可判断null）
-
排序查询
- 语法：select 查询列表 from 表 【where 筛选条件】 order by;
- 特点：
- asc代表的是升序，desc代表降序，不写默认为升序
- order by子句中可以支持单个字段、多个字段、表达式、函数、别名
- order by子句一般是放在查询语句的最后面,limit子句除外
常见函数
- 功能：类似于java中的方法
- 调用：select 函数名(实参列表);
单行函数
- 单行函数分类：字符函数、数学函数、日期函数、其他函数、流程控制函数
一、字符函数
#1.length 获取参数值的字节值
#2.concat 字符串拼接
#3.upper:变大写、lower：变小写
#4.substr、substring（索引从1开始）
#5.instr:获取子串第一次出现的索引,找不到返回0
#6.trim:去前后空格
#7.lpad:用指定的字符实现左填充指定长度
#8.rpad:用指定的字符实现右填充指定长度
#9.replace:替换
二、数学函数
#1.round:四舍五入
SELECT ROUND(1.45);
SELECT ROUND(1.567,2);
#2.ceil:向上取整,返回>=该参数的最小整数
SELECT CEIL(1.005);
SELECT CEIL(-1.002);
#3.floor:向下取整,返回<=该参数的最大整数
SELECT FLOOR(-9.99);
#4.truncate:截断
SELECT TRUNCATE(1.65,1);
#5.mod:取余
SELECT MOD(10,3);
#6.rand:获取随机数，返回0-1之间的小数
SELECT RAND();
三、日期函数
#1.now():返回当前系统时间+日期
SELECT NOW();
#2.year():返回年
SELECT YEAR(NOW());
SELECT YEAR(hiredate) 年 FROM employees;
#3.month():返回月
#MONTHNAME:以英文形式返回月
SELECT MONTH(NOW());
SELECT MONTHNAME(NOW());
#4.day():返回日
#DATEDIFF:返回两个日期相差的天数
SELECT DAY(NOW());
SELECT DATEDIFF('2023/05/30','2023/06/1');
#5.str_to_date():将字符通过指定格式转换成日期
SELECT STR_TO_DATE('2023-5-31','%Y-%c-%d') AS out_put;
#6.date_format():将日期转换成字符
SELECT DATE_FORMAT('2023/5/31','%Y年%m月%d日') AS out_put;
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日') AS out_put;
#7.curdate():返回当前日期
SELECT CURDATE();
#8.curtime:返回当前时间
SELECT CURTIME();
四、其他函数
#version() 当前数据库服务器的版本
SELECT VERSION();
#database() 当前打开的数据库
SELECT DATABASE();
#user() 当前用户
SELECT USER();
#password('字符')：返回该字符的密码形式
SELECT PASSWORD('deping');
#md5('字符'):返回该字符的md5加密形式
SELECT MD5('deping');
五、流程控制函数
#1.
if(条件表达式，表达式1，表达式2);
#条件表达式成立，显示表达式1，否则显示表达式2（此语句会输出一个新列）
#2.case函数的使用一:switch case 的效果
case 要判断的变量或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
#3.case函数的使用二:类似于多重if
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end
分组函数（聚集函数）
- 语法：select 聚集函数(字段) from 表名;
- 功能：用作统计使用，又称为聚合函数或统计函数或组函数
- 分类：
SUM求和、AVG平均值、MAX最大值、MIN最小值、COUNT计算个数 - 特点:
- sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型 - 除了count(* )外分组函数都忽略null（count(列名)也忽略null值）
- 都可以搭配distinct使用，实现去重的统计
select 聚集函数(distinct 字段) from 表; - count函数
count(字段)：统计该字段非空值的个数
count(*):统计结果集的行数
count(1): 在表中(非实际)增加一列行值全为1的列，统计1的个数（1可改为任意常量）效率上：
MyISAM存储引擎：count(* )最高
InnoDB存储引擎：count(*)和count(1)效率>count(列名) （count(列名)还需判断是否为null值） - 和分组函数一同查询的字段，要求是group by后出现的字段
- sum和avg一般用于处理数值型
分组查询
-
语法:
select 分组函数,分组后的字段(要出现在group by的后面) from 表 【where 分组前的筛选】 group by 分组的字段 【having 分组后的筛选】 【order by 排序列表】 -
特点:
- 分组查询中的筛选条件分为两类
使用关键字 筛选的表 位置 分组前筛选 where 原始表 group by的前面 分组后筛选 having 分组后的结果 group by的后面 # 分组函数做条件肯定是放在having子句中 # 能用分组前筛选的，就优先考虑使用分组前筛选-
group by子句支持单个字段分组，多个字段分组(多个字段之间用逗号隔开没有顺序要求),表达式或函数(使用较少)
-
也可以添加排序(排序放在整个分组查询的最后
连接查询
含义
- 当查询中涉及到了多个表的列，需要使用多表连接
- 若直接使用：select 列1，列2，…
from 表1，表2, …;
则会出现笛卡尔乘积现象 --> 当查询多个表时，没有添加有效的连接条件，导致出现表一有m行， 表二有n行，结果为m*n行的结果 - 如何解决：添加有效的连接条件（添加条件的多表连接也是使用第一张表的某一行去匹配第二张表的所有行）
连接条件分类
- 按年代分类：
-- sql92标准:仅仅支持内连接
-- sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接
- 按功能分类：
-- 内连接：
select 查询列表
from 表1 别名
inner join 表2 别名 on 连接条件（inner可省略）
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
--- 等值连接
--- 非等值连接
--- 自连接
-- 外连接：
select 查询列表
from 表1 别名
left|right|full outer join 表2 别名 on 连接条件（outer可省略）
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
--- 左外连接
--- 右外连接
--- 全外连接
-- 交叉连接
select 查询列表
from 表1 别名
cross join 表2 别名;
sql92和sql99对比
- 功能：sql99支持的较多
- 可读性：sql99实现连接条件和筛选条件的分离，可读性较高
子查询
- 含义:嵌套在其他语句内部的select语句,称为子查询或内查询
- 外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
- 外面如果为select语句，则此语句称为外查询或主查询
- 分类
#一、where或having后面
/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）
3、行子查询（多列多行）
特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>
列子查询，一般搭配着多行操作符使用
in、any/some、all
④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
*/
#1.标量子查询★
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
SELECT job_id
FROM employees
WHERE employee_id = 141
) AND salary>(
SELECT salary
FROM employees
WHERE employee_id = 143
);
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id = 50
);
#2.列子查询（多行子查询）★
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';
#3、行子查询（结果集一行多列或多行多列）
SELECT * FROM employees
WHERE (employee_id,salary)=(
SELECT MIN(employee_id),MAX(salary)
FROM employees
);
#二、select后面
/*
仅仅支持标量子查询
*/
SELECT d.*,(
SELECT COUNT(*)
FROM employees e
WHERE e.department_id = d.`department_id`
) AS 个数
FROM departments d;
#三、from后面
/*
将子查询结果充当一张表，要求必须起别名
*/
SELECT ag_dep.*,g.`grade_level`
FROM (
SELECT AVG(salary) ag,department_id
FROM employees
GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;
#四、exists后面（相关子查询）
/*
语法： exists(完整的查询语句)
特点：先执行外查询，再判断选择的列的行是否存在于子查询的结果中，若是，则成功得到外层查询
结果： 1或0
*/
#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
SELECT department_id
FROM employees
);
#exists
SELECT department_name
FROM departments d
WHERE EXISTS(
SELECT *
FROM employees e
WHERE d.`department_id`=e.`department_id`
);
分页查询
- 语法
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
#注意
# offset要显示条目的起始索引（起始索引从0开始）
# size 要显示的条目个数
- 特点
① limit语句放在查询语句的最后
② 公式
select 查询列表 from 表
limit (page-1)*size,size;
联合查询
- 含义：union (联合、合并)：将多条查询语句的结果合并成一个结果。
- 语法
查询语句1
union 【all】
查询语句2
union 【all】
...
- 特点
- 要求多条查询语句的查询列数是一致的！
- 要求多条查询语句的查询的每一列的类型和顺序最好一致
- union关键字默认去重，如果使用union all 可以包含重复项
SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90;
#等价
SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;
DML语言
插入
- 方式一：经典的插入
- 语法：insert into 表名(字段名,…) values(值,…),(值，…),…;
- 方式二
- 语法：insert into 表名 set 列名=值,列名=值,…
- 两种方式的区别：
- 方式一支持一次插入多行，方式二不支持；
- 方式一支持子查询，方式二不支持
- insert into 表名（列名，…）查询语句;
#过程：将会把查询出的结果对应的插入到所选列中 INSERT INTO A(id,name) SELECT B.id,B.name FROM B;
修改
- 修改单表的记录★
- 语法：update 表名 set 列=新值,列=新值,… where 筛选条件;
- 修改多表的记录（先将多张表连接为新表，再去更新）
- sql92语法：
update 表1 别名,表2 别名 set 列=值,… where 连接条件 and 筛选条件; - sql99语法：
update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列=值,… where 筛选条件;
- sql92语法：
删除
-
方式一：delete
1、单表的删除【★】 delete from 表名 where 筛选条件2、多表的删除 sql92语法： delete 表1的别名,表2的别名 from 表1 别名,表2 别名 where 连接条件 and 筛选条件; sql99语法： delete 表1的别名,表2的别名 from 表1 别名 inner|left|right join 表2 别名 on 连接条件 where 筛选条件; -
方式二：truncate
- 语法：truncate table 表名;
-
两种方式的区别【面试题】★
1. delete 可以加where 条件，truncate不能加 2. truncate删除，效率高一点 3. 假如要删除的表中有自增长列， 如果用delete删除后，再插入数据，自增长列的值从断点开始， 而truncate删除后，再插入数据，自增长列的值从1开始。 4. truncate删除没有返回值，delete删除有返回值 5. truncate删除不能回滚，delete删除可以回滚.
DDL语言
库的管理
- 创建库
create database 【if not exists】 库名【 character set 字符集名】; - 修改库
alter database 库名 character set 字符集名; - 删除库
drop database 【if exists】 库名;
表的管理
-
表的创建 ★
语法： create table 【if not exists】 表名( 列名 列的类型【(长度) 约束】, 列名 列的类型【(长度) 约束】, 列名 列的类型【(长度) 约束】, ... #（此段最后的语句不可加逗号） 列名 列的类型【(长度) 约束】 ) -
表的修改
1.添加列 alter table 表名 add column 列名 类型 【first|after 字段名】; 2.修改列的类型或约束 alter table 表名 modify column 列名 新类型 【新约束】; 3.修改列名 alter table 表名 change column 旧列名 新列名 类型; 4 .删除列 alter table 表名 drop column 列名; 5.修改表名 alter table 表名 rename 【to】 新表名; -
表的删除
drop table【if exists】 表名; -
复制表
1、复制表的结构 create table 表名 like 旧表; 2、复制表的结构+数据 create table 表名 select 查询列表 from 旧表【where 筛选】;
数据类型
一、数值型
-
整型
-
类型
tinyint、smallint、mediumint、int/integer、bigint -
特点：
①都可以设置无符号或有符号，默认有符号，通过unsigned设置无符号
②如果插入的数值超出了整型范围，会报out of range警告，并且插入的值自动改为临界值
③数据类型后的括号及长度可以省略，会有一个默认值，括号中的长度指的是数据在表中显示的宽度， 而数据类型所代表的数值范围由类型决定，无法指定长度
④搭配zerofill时，若数据的长度不够指定的宽度则左边用0填充，并且默认变为无符号整型，若不搭配 zerofill，数据右对齐
-
-
浮点型
-
定点数：dec(M,D)；decimal(M,D)
-
浮点数：float(M,D) 4；double(M,D) 8
-
特点：
①M代表整数部位+小数部位的个数，D代表小数部位的个数
②如果插入的值超出括号中指定的宽度，则报out of range警告，并且插入的值自动改为临界值
③括号及M和D都可以省略，对于定点数，M默认为10，D默认为0，对于浮点数，则会根据输入的数值 来决定精度
-
④如果精度要求较高，则优先考虑使用定点数
二、字符型
-
较短的文本：
char、varchar -
其他：
binary和varbinary用于保存较短的二进制enum用于保存枚举set用于保存集合
-
较长的文本：
text、blob(较大的二进制) -
特点：
char(M)：固定长度的字符，最大长度不能超过M，其中M可以省略，默认为1varchar(M)：可变长度的字符，最大长度不能超过M，其中M不可以省略
三、日期型
- 分类：
date只保存日期；time只保存时间；year只保存年；datetime保存日期+时间；（8字节）timestamp保存日期+时间；（4字节，比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间）
- 特点：
字节 范围 时区等的影响 datetime 8 1000——9999 不受 timestamp 4 1970-2038 受
常见的约束
一、常见的约束
-
六大约束
-
NOT NULL：非空，保证该列的值不能为空 -
UNIQUE：唯一，该列的值具有唯一性，可以为空 -
DEFAULT值：默认，该列的值不用手动插入，有默认值 -
CHECK：检查(mysql不支持)，用于检查值是否满足所设定的条件 -
PRIMARY KEY：主键，该列的值具有唯一性并且非空（primary key = unique + not null） -
FOREIGN KEY：外键，该列的值引用了主表的列
-
-
添加约束的时机：1.创建表时；2.修改表时
-
约束的添加分类：
- 列级约束：直接在字段名和类型后面追加 约束类型即可。只支持：默认、非空、主键、唯一
- 表级约束：在各个字段的最下面 【constraint 约束名】 约束类型(字段名)
-
语法
一、创建表时添加约束 CREATE TABLE 表名{ 列级约束： 字段名 字段类型 列级约束, 字段名 字段类型 not null, #非空 字段名 字段类型 primary key, #主键 字段名 字段类型 unique, #唯一 字段名 字段类型 default 值, #默认 constraint 约束名 foreign key(字段名) references 主表（被引用列） 表级约束： constraint 约束名 foreign key(字段名) 【references 主表（被引用列）】 CONSTRAINT pk PRIMARY KEY(id),#主键 CONSTRAINT uq UNIQUE(seat),#唯一键 CONSTRAINT ck CHECK(gender ='男' OR gender = '女'),#检查 CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键 };二、修改表时添加或删除约束 1、非空 #添加非空 alter table 表名 modify column 字段名 字段类型 not null; #删除非空 alter table 表名 modify column 字段名 字段类型 ; 2、默认 #添加默认 alter table 表名 modify column 字段名 字段类型 default 值; #删除默认 alter table 表名 modify column 字段名 字段类型 ; 3、主键 #添加主键 alter table 表名 add【 constraint 约束名】 primary key(字段名); #删除主键 alter table 表名 drop primary key; 4、唯一 #添加唯一 alter table 表名 add【 constraint 约束名】 unique(字段名); #删除唯一 alter table 表名 drop index 索引名; 5、外键 #添加外键 alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表（被引用列）; #删除外键 alter table 表名 drop foreign key 约束名; -
主键和唯一的区别
保证唯一性 是否允许为空 一个表中可以有多少个 是否允许组合
主键 √ × 至多有1个 √，但不推荐
唯一 √ √ 可以有多个 √，但不推荐
-
外键：
1、用于限制两个表的关系，要求在从表设置外键关系，从表的字段值引用了主表的某字段值
2、外键列和主表的被引用列要求类型一致，意义一样，名称无要求
3、主表的被引用列要求是一个key（一般是主键或唯一），否则报错
4、外键所在的列插入数据时，先插入主表；删除数据时，先删除从表
二、自增长列（标识列）
- 特点：
- 不用手动插入值，可以自动提供序列值，默认从1开始，步长为1
auto_increment_increment
如果要更改起始值：手动插入值
如果要更改步长：更改系统变量
set auto_increment_increment=值; - 一个表至多有一个自增长列
- 自增长列只能支持数值型
- 自增长列必须为一个key
- 不用手动插入值，可以自动提供序列值，默认从1开始，步长为1
- 语法
一、创建表时设置自增长列 create table 表( 字段名 字段类型 约束 auto_increment );二、修改表时设置自增长列 alter table 表 modify column 字段名 字段类型 约束 auto_increment;三、删除自增长列 alter table 表 modify column 字段名 字段类型 约束;
TCL语言
事务
- 事务
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。 - 事务的特性(ACID)
原子性(Atomicity)：事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。回滚可以用回滚日志来实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可一致性 (Consistency)：数据库在事务执行前后都保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的。隔离性 (Isolation)：一个事务的执行不受其他事务的干扰（一个事务所做的修改在最终提交以前，对其它事务是不可见的）持久性 (Durability)：一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
事务的使用步骤
- 隐式（自动）事务
没有明显的开启和结束，本身就是一条事务，可以自动提交，比如insert、update、 delete语句等 - 显式事务
事务具有明显的开启和结束的标记；前提：必须先设置自动提交功能为禁用步骤1：开启事务 set autocommit=0;#设置事务的自动提交功能为禁用 start transaction;#可选的、可以省略 步骤2：编写事务中的sql语句(select insert update delete) 设置回滚点：savepoint 回滚点名;#可写在多条sql语句之间 语句1; 语句2; ... 步骤3：结束事务(三者需选一) commit; #提交事务 rollback; #回滚事务 rollback to 回滚点名; #回滚到savepoint处
并发事务
-
事务的并发问题是如何发生的？
多个事务同时操作 同一个数据库的相同数据时 -
对于同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制, 就会导致各种并发问题：
脏读：一个事务读取了其他事务还没有提交的数据，读到的是其他事务“更新”的数据；不可重复读：一个事务多次读取，结果不一样；幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是 其他事务“插入”的数据。
-
隔离级别
脏读 不可重复读 幻读 read uncommitted:读未提交 × × × read committed：读已提交 √ × × repeatable read：可重复读 √ √ × serializable：串行化 √ √ √1. 查看隔离级别 select @@tx_isolation; 2. 设置隔离级别 set session|global transaction isolation level 隔离级别; #session只针对当前连接，global支持整个数据库的所有连接- Oracle 支持2 种事务隔离级别：READ COMMITED, SERIALIZABLE。Oracle 默认的事务隔离级别为:
READ COMMITED - Mysql 支持4 种事务隔离级别. Mysql 默认的事务隔离级别为:
REPEATABLE READ
- Oracle 支持2 种事务隔离级别：READ COMMITED, SERIALIZABLE。Oracle 默认的事务隔离级别为:
DCL : 管理、授权用户
管理用户
- 查询用户：
(1) 切换到mysql数据库
use mysql;
(2) 查询user表
select * from user;
注：user表中localhost表示仅可在本机使用此用户登录数据库
user表中通配符 % 代表可以在任意主机使用此用户登录数据库
-
添加用户：
语法：create user ‘用户名’@’主机名’ identified by ‘此用户的密码’; -
删除用户：
语法：drop user ‘用户名’@’主机名’; -
修改用户的密码:
- 语法一：update user set password = password(‘新密码’) where user = ‘用户名’;
- 语法二：set password for ‘用户名’@’主机名’ = password(‘新密码’);
忘记了root密码的解决方式：
- 右键以管理员身份运行cmd，输入 net stop mysql;(停止mysql服务)
- 输入 mysql --skip -grant -tables
- 打开一个新的cmd窗口，只输入 mysql 即可登录成功
- 输入 use mysql;
- 输入 update user set password = password(‘新密码’) where user = ‘root’;
- 关闭两个cmd窗口，打开任务管理器，手动结束mysqld.exe进程
- 管理员身份打开cmd，输入 net start mysql;(启动mysql服务)
- 可以使用新密码登录
权限管理
- 查询权限：
语法：show grants for ‘用户名’@’主机名’; - 授予权限：
语法：grant 权限列表 on 数据库名.表名 to ‘用户名’@’主机名’; - 撤销权限：
语法：revoke 权限列表 on 数据库名.表名 from ‘用户名’@’主机名’;
其他
视图
-
含义
mysql5.1版本出现的新特性，是一个虚拟表，不保存数据，只保存sql逻辑，它的数据来自于表，通过执行时动态生成 -
优点：
- 简化sql语句
- 重用sql语句
- 保护基表数据，提高安全性
-
语法：
- 视图的创建：create view 视图名 as 查询语句;
- 视图的修改：
- 方式一：
create or replace view 视图名 as 查询语句; - 方式二：
alter view 视图名 as 查询语句;
- 方式一：
- 视图的删除：drop view 视图1，视图2,…;
- 用户可以一次删除一个或者多个视图，前提是必须有该视图的drop权限。
- 视图的查看：desc 视图名；
- 视图的更新：
视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新：- 包含分组函数、group by、distinct、having、union(all)关键字
- 常量视图，即select后是常量值
- select中包含子查询
- 进行了连接表的操作join
- from一个不能更新的视图
- where子句的子查询引用了from子句中的表
变量
-
系统变量：
- 全局变量
服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效，服务器重启，全局变量赋默认值 - 会话变量
服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）
语法： 1.查看所有的系统变量 show global|session variables; 2.查看满足条件的部分系统变量 show global|session variables like 变量包含的字符; 3.查看指定的系统变量的值 select @@global|session.系统变量名; 4.为系统变量赋值 4.1方式一： set global|session 系统变量名=值; #SET GLOBAL autocommit=0; 4.2方式二： set @@global|session.系统变量名=值; # SET @@global.autocommit=0; - 全局变量
-
自定义变量：
- 用户变量
- 作用域：针对于当前连接（会话）生效
- 位置：begin end里面，也可以放在外面
使用： 1.声明并赋值： set @变量名=值; 或 set @变量名:=值; 或 select @变量名:=值; 2.更新值 方式一： set @变量名=值; 或 set @变量名:=值; 或 select @变量名:=值; 方式二： select 字段 into @变量名 from 表; 3.使用 select @变量名; - 局部变量
- 作用域：仅仅在定义它的begin end中有效
- 位置：只能放在begin end中，而且只能放在第一句
使用： 1.声明 declare 变量名 类型 (default 值); 2.赋值或更新 方式一：set 变量名=值; 或 set 变量名:=值; 或 select @变量名:=值; 方式二：select xx into 变量名 from 表; 3.使用 select 变量名; - 用户变量和局部变量的对比
- 作用域 定义位置 语法 用户变量 当前会话 会话的任何地方 加@符号，不用指定类型 局部变量 定义它的BEGIN END中 BEGIN END的第一句话 一般不用加@,需要指定类型
- 用户变量
存储过程和函数
- 都类似于java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名字
- 好处
- 提高代码的重用性
- 简化操作
- 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
存储过程
-
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
-
创建语法
CREATE PROCEDURE 存储过程名(参数列表) BEGIN 存储过程体（一组合法的SQL语句） END /* 注意： 1、参数列表包含三部分： 参数模式 参数名 参数类型 举例：in argname varchar(20) 参数模式： in：该参数可以作为输入，也就是该参数需要调用方传入值 out：该参数可以作为输出，也就是该参数可以作为返回值 inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值 2、如果存储过程体仅仅只有一句话，begin end可以省略 存储过程体中的每条sql语句的结尾要求必须加分号。 存储过程的结尾可以使用 delimiter 重新设置 语法：delimiter 结束标记 案例：delimiter $ 关于delimiter： 默认情况下，delimiter是分号，在命令行客户端中，如果有一行命令以分号结束， 那么回车后，mysql将会执行该命令。 如输入语句 mysql> select * from test_table; 然后回车，那么MySQL将立即执行该语句。 但有时候，不希望MySQL这么做，有时输入较多的语句，且语句中包含有分号，而把这些语句当作整体执行。 这种情况下，就需要事先 把delimiter换成其它符号，这样只有当设置的符号出现之后， mysql解释器才会执行这段语句 ，而不会一遇到分号就执行。 */ -
调用语法
CALL 存储过程名(实参列表); /* 举例： 调用in模式的参数：call sp1（‘值’）; 调用out模式的参数：set @name; call sp1(@name);select @name; 调用inout模式的参数：set @name=值; call sp1(@name); select @name; */ -
查看语法
show create procedure 存储过程名; SHOW CREATE PROCEDURE myp2; -
删除语法
只能一次删除一个，无法一次删除多个drop procedure 存储过程名;
函数
-
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
-
区别：
- 存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新；
- 函数：有且仅有1 个返回，适合做处理数据后返回一个结果。
-
创建语法
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型 BEGIN 函数体 END /* 注意： 1.参数列表 包含两部分： 参数名 参数类型 2.函数体：肯定会有return语句，如果没有会报错 如果return语句没有放在函数体的最后也不报错，但不建议return 值; 3.函数体中仅有一句话，则可以省略begin end 4.使用 delimiter语句设置结束标记 */ -
调用语法
SELECT 函数名(参数列表); #1.无参有返回 SELECT myf1(); #2.有参有返回 SELECT myf2('deping') ; -
查看函数
show create function 函数名; -
删除函数
drop function 函数名;
流程控制结构
- 顺序结构：程序从上往下依次执行
- 分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行
- if函数
- 语法：if(条件,值1，值2)；
- case结构
- 语法：
情况1：类似于switch，一般用于实现等值判断。 语法： case 变量或表达式 when 值1 then 语句1; when 值2 then 语句2; ... else 语句n; end 情况2：类似于多重if语句，一般用于实现区间判断。 语法： case when 条件1 then 语句1; when 条件2 then 语句2; ... else 语句n; end
- 语法：
- if结构
- 语法：
if 条件1 then 语句1; elseif 条件2 then 语句2; .... else 语句n; end if;
- 语法：
- if函数
- 循环结构：程序满足一定条件下，重复执行一组语句
- 语法：
1、while语法： 【名称:】while 循环条件 do 循环体 end while 【名称】; 2、loop语法： 【名称：】loop 循环体 end loop 【名称】; 3、repeat语法： 【名称:】repeat 循环体 until 结束条件 end repeat 【名称】; - 对比：
1. 这三种循环都可以省略名称，但如果循环中添加了循环控制语句（leave或iterate）则必须添加名称 2. loop 一般用于实现简单的死循环 while 先判断后执行 repeat 先执行后判断，无条件至少执行一次
- 语法：
循环控制语句
- leave：类似于break，用于跳出所在的循环
- iterate：类似于continue，用于结束本次循环，继续下一次
范式
按照经典教材——王珊的《数据库系统概论》中的定义，范式是 “符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度”。你可以把它粗略地理解为一张数据表的表结构所符合的某种设计标准的级别。
数据库范式分为1NF，2NF，3NF，BCNF，4NF，5NF。一般在我们设计关系型数据库的时候，最多考虑到BCNF就够。符合高一级范式的设计，必定符合低一级范式，如符合2NF的关系模式，必定符合1NF。
- 第一范式（1NF）：属于第一范式关系的所有属性都不可再分，即数据项不可分。
- 第二范式（2NF）：在1NF的基础之上，消除了非主属性对于码的部分函数依赖。若某关系R属于第一范式，且每一个非主属性完全函数依赖于任何一个候选码，则关系R属于第二范式。
- 第三范式（3NF）：在2NF的基础之上，消除了非主属性对于码的传递函数依赖，非主属性既不传递依赖于码，也不部分依赖于码。
- 巴斯-科德范式（BCNF）：在3NF基础上，任何非主属性不能对主键子集依赖（在3NF基础上消除对主码子集的依赖）
巴斯-科德范式（BCNF）是第三范式（3NF）的一个子集，即满足巴斯-科德范式（BCNF）必须满
足第三范式（3NF）。通常情况下，巴斯-科德范式被认为没有新的设计规范加入，只是对第二
范式与第三范式中设计规范要求更强，因而被认为是修正第三范式，也就是说，它事实上是对第
三范式的修正，使数据库冗余度更小。这也是BCNF不被称为第四范式的原因。某些书上，根据范
式要求的递增性将其称之为第四范式是不规范，也是更让人不容易理解的地方。而真正的第四范
式，则是在设计规范中添加了对多值及依赖的要求。 - 第四范式 （4NF）： 限制关系模式的属性之间不允许有非平凡且非函数依赖的多值依赖。
- 第五范式 （5NF）：关系模式R依赖均由R候选码所隐含
数据库的备份和还原
- 命令行：
- 备份：
- 语法：mysqldump -u用户名 -p密码 需要备份的数据库名称 > 保存路径
- 还原：
- 登录数据库
- 创建数据库
- 使用数据库
- 执行语法：source 文件路径
- 备份：
- 图形化工具：自行点击
扫一扫
606
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
