• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

MySQL数据库,这一篇就够啦!!!(持续更新)

开发技术 开发技术 7天前 11次浏览

第一章 概念

一、数据库的好处

1、可以持久化数据到本地

2、结构化查询

二、数据库的常见概念 ★

1、DB:数据库,存储数据的容器

2、DBMS:数据库管理系统,又称为数据库软件或数据库产品,用于创建或管理DB

3、SQL:结构化查询语言,用于和数据库通信的语言,不是某个数据库软件特有的,而是几乎所有的主流数据库软件通用的语言

三、数据库存储数据的特点

1、数据存放到表中,然后表再放到库中

2、一个库中可以有多张表,每张表具有唯一的表名用来标识自己

3、表中有一个或多个列,列又称为“字段”,相当于java中“属性”

4、表中的每一行数据,相当于java中“对象”

四、常见的数据库管理系统

mysql、oracle、db2、sqlserver

第二章 简单实用

0-1、MySQL服务器的启动和停止

方式一:系统偏好设置—>(右下角)mysql—>start mysql server
方式二:Mac终端启动(待补充)

0-2、MySQL的登录与退出

0、首先要保证服务器处于启动状态
1、登录

  • 连接本机:mysql -u账号 -p密码
  • 连接其他主机
  1. mysql -h要连接的主机名 -P 3306 -u用户名 -p enter后输入密码
  2. mysql -h要连接的主机名 -P 3306 -u用户名 -p密码 (p和密码之间一定是没有空格的
    2、退出:exit

一、常用命令(在登录服务器的前提下)

1、 查看mysql中所有数据库:show databases;

2、 使用/打开一个数据库:use 库名;(此时我会身处在这个数据库中)

3、查看当前库的所有表:show tables;

4、查看其他库的所有表:show tables from 库名;(此时我仍身处use的那个库)

5、创建表

CREATE TABLE students(
num int,
age int
);
1234

create table 表名(

列名 列类型,

列名 列类型,

。。。(此处没逗号)

);

6、查看表结构:desc 表名; (要记住自己位于哪个库,只能看这个库的表)

7、查看服务器版本

在已登录mysql情况下在terminal使用select version();

select version();
1

在没登录mysql情况下在terminal使用mysql –version或mysql -V

mysql --version
musql -V
12

二、命令规范

1、 每条命令最好是,结尾分号(terminal中)

2、不区分大小写,建议关键字大写,表名,列表名小写

3、可以缩进、可以换行,->换行的标志

4、注释

#这是单行注释
--这是单行注释
/*
这是多行注释
*/
12345

单行注释:#注释文字
单行注释:– 注释文字
多行注释:/* zhushi */

第三章 DQL

一、基础查询

(一)语法

SELECT 查询字段
FROM 表名;

(二)特点

  1. 查询列表可以是:字段、常量、表达式、函数(都可以是单个或者多个)
  2. 查询结果是一个虚拟表

(三)实例

  1. 查询单个字段

    SELECT email
    FROM employees;
    
    • 英文分号结尾
  2. 查询多个字段(英文逗号隔开)

    SELECT email,job_id,last_name
    FROM employees;
    
    • 英文逗号隔开字段
  3. 查询所有字段

    SELECT *
    FROM employees;
    
    • 操作技巧:可以通过双击Navicat右侧的字段来实现查询(如下)

    •   SELECT `employees`.`email`,`employees`.`first_name`
        FROM `employees`;
      
  4. 查询常量值(数值型、字符型、日期型)(暂时不知道有何用???)

  5. 查询表达式

    SELECT 100%3;
    FROM employees;
    
  6. 查询函数

    SELECT 函数名(实参列表)
    
    SELECT version();
    FROM employees;
    
  7. 起别名:在字段后面+AS或空格+别名

  8. 去重:DISTINCT关键字

    SELECT DISTINCT `employees`.`department_id`
    FROM `employees`;
    
  9. +:mysql中“+”只有一个作用就是做加法运算

    • select 数值+数值:直接运算
    • select 字符+数值:先试图将字符转换为数值,成功则继续运算,失败就把字符转换为0再运算
    • select null+值:结果都是null
  10. 补充

    1. CONCAT函数:拼接字符

      CONCAT(str1,str2,...)
      
    2. IFNULL函数:判断字段或表达式(expr1)是否是null,是null返回指定值(expr2),不是null返回原本的值

      IFNULL(expr1,expr2)
      
    3. ISNULL函数:判断字段或表达式是否为null,是null返回1,不是null返回0

二、条件查询

(一)、语法

select 查询列表
from 表名
where 筛选条件

(二)、筛选条件的分类

1、简单条件运算符
 < 
 >
 = 
 <>   !=  
 >=
 <=  
 <=>安全等于
2、逻辑运算符
&& and
|| or
!  not
3、模糊查询
  • like:查询类似的值,一般搭配通配符使用,可以判断字符型或数值型

    通配符:

    • %任意多个字符

    • _任意单个字符

    • 和关键字重复时,可以用来转义,也可以自定义转义

      where lastname LIKE ‘_$_’ESCAPE '$';
      

    eg:查询姓名中第二个字母是o的员工姓名

    SELECT last_name
    FROM employees
    WHERE last_name LIKE '_o%';
    
  • between and:用于显示在某个范围内的值,包含边界

    eg:查询工资在2500到3500之间的员工姓名

    SELECT last_name
    FROM employees
    WHERE salary IN(2500,3500);
    

    sql

  • in:查询某个字段是某个/几个确定值的

    • 好处:可以代替OR,简化代码
    • eg:查询部门号为100,101,102的员工姓名和部门号
    SELECT manager_id,last_name
    FROM employees
    WHERE manager_id IN(100,101,102);
    
  • is null /is not null:用于判断null值

    eg:查询没有分配部门的员工姓名

    SELECT last_name
    FROM employees
    WHERE manager_id IS NULL;
    

(三)、总结

1、is null和<=>安全等与的区别(待补充)

三、排序查询

(一)、语法

select 查询列表
from 表名
where 筛选条件
order by 排序列表【asc或desc】

eg:按照入职日期升序查询100号部门的员工的姓名和入职日期

SELECT last_name,hiredate
FROM employees
WHERE manager_id=100
ORDER BY hiredate
  • asc(ascend):升序(可省略,默认升序)
  • desc(descend):降序

(二)、特点

  • 可以查询多个字段,每个字段后面加asc或desc(查询结果是在第一个字段顺序的基础上为第二个字段排序
  • 可以按别名查询
  • order by 放在select语句的结尾

四、常见函数

(一)、概述

  • 功能:类似于java中的方法
  • 好处:提高重用性和隐藏实现细节
  • 调用:select 函数名(实参列表);

(二)、单行函数

(本来觉得要记得详细一些,后来发现,详细的用法在开发中都会提示出来,所以只需要记住这个有函数,用的时候可以记起来就ok啦)

1、字符函数
函数 作用 注意
CONCAT(str1,str2,…) 连接
SUBSTR(str FROM pos FOR len) 截取子串 下标从1开始
UPPER(str)、LOWER(str) 变大写、小写
LPAD(str,len,padstr)、RPAD(str,len,padstr) 左填充、右填充
REPLACE(str,from_str,to_str) 替换
LENGTH(str) 获取字节数
TRIM([remstr FROM] str) 去前后空格
INSTR(str,substr) 获取子串第一次出现的索引
2、数学函数
函数 作用 注意
CEIL(X)、FLOOR(X) 向上、下取整
MOD(N,M) 取模
ROUND(X)、ROUND(X,D) 四舍五入
RAND() 0~1直接的随机数
TRUNCATE(X,D) 截断
3、日期函数
函数 作用 注意
now() 返回日期+时间
YEAR(date)、MONTH(date)、DAY(date) 返回年、月、日 date可以用now()获取
date_format、str_to_date 日期转字符、字符转日期
datediff 日期天数差
curdate 返回当前日期
curtime 返回当前时间
hour、minute、second 返回时、分、秒
monthname 返回英文月份
4、其他函数
函数 作用 注意
version 服务器版本
database 当前打开的数据库
user 当前用户
password 返回字符的密码形式
md5 返回字符的md5加密形式
5、流程控制函数

①、if(条件表达式,表达式1,表达式2):如果条件表达式成立,返回表达式1,否则返回表达式2

IF(expr1,expr2,expr3)

②、case情况1

case 变量或表达式或字段
when 常量1 then 值1/语句 (值不加分号,语句加分号)
when 常量2 then 值2/语句
...
else 值n
end

③case情况2

case 
when 条件1 then 值1/语句
when 条件2 then 值2/语句
...
else 值n
end

(三)、分组函数

0、说明

​ 分组函数作用于一组数据,然后返回一个值

1、分类
函数 作用 注意
min、max 最小、大值
sum、avg 和、平均值
count 计算个数
2、特点
  • 语法
select max(字段)
from 表名
  • 支持的类型
    sum和avg一般用于处理数值型
    max、min、count可以处理任何数据类型

  • 以上分组函数都忽略null

  • 都可以搭配distinct使用,实现去重的统计

    select sum(distinct dep_id)
    from employees;
    
  • count函数

    • count(字段):统计该字段非空值的个数
    • count(*):统计结果集的行数 (涉及到个数,用count(*))
    • count(1):统计结果集的行数(相当于给原表加了一列1,当然1可以换做任何常量值)

    效率上:

    以前:MyISAM存储引擎,count(*)最高
    现在:InnoDB存储引擎,count(*)和count(1)效率>count(字段)

  • 和分组函数一同查询的字段,要求是group by后出现的字段(因为查询结果要求是一个规则的表格,如果查询普通字段,表格不是矩形,没意义,如下查询是没意义的)

    select avg(salary),job_id
    from employees;
    

五、分组查询

一、语法

select 分组函数,列(分组的条件,对应“按照每个”等字眼)(要求出现在group by后面) 
from 表
【where 筛选条件】
group by 分组的字段(多个字段没有顺序)
【having 分组后的筛选】(在where筛选条件得到的结果上再次筛选)
【order by 排序列表】 (order by在最后)
  • eg:查询每个工种的最高工资

    select max(salary),job_id
    from employees
    group by job_id
    
  • 如eg:查询部门员工数大于2的部门

    select count(*),department_id
    from employees
    group by department_id
    #到这里我查询到的结果是每个部门员工个数
    having count(*)>2;
    
  • eg:

    #eg:查询每个工种有奖金的员工的最高工资   大于12000的员工编号和最高工资
    
    #第一步:查询每个工种有奖金的员工的最高工资
    
    #1-1 先把框架写出来
    SELECT MAX(salary),job_id
    FROM employees
    GROUP BY job_id
    
    #1-2 在根据条件写where和having,因为“有奖金”在employee表中就有,所以用紧跟from的where
    SELECT MAX(salary),job_id
    FROM employees
    WHERE `employees`.`commission_pct` is not null
    GROUP BY job_id
    
    #第二步:大于12000的员工编号和最高工资
    SELECT MAX(salary),job_id
    FROM employees
    WHERE `employees`.`commission_pct` is not null
    GROUP BY job_id
    HAVING MAX(salary)>12000
    

二、特点

关键字 筛选的表 位置
分组前筛选 where 原始表 group by前
分组后筛选 having 分组后的中间表 group by后
  • 补充
    • 分组函数做条件,一定是放在having中
    • 能用分组前,用分组前,提高性能
    • 可以按多个字段分组

六、连接查询

(一)、含义·

  1. 当查询中涉及到了多个表的字段,需要使用多表连接
    select 字段1,字段2
    from 表1,表2,…;

  2. 笛卡尔乘积:当查询多个表时,没有添加有效的连接条件,导致多个表所有行实现完全连接

  3. 如何解决:添加有效的连接条件

(二)、分类

  1. 按年代分类
  • sql92

  • sql99(推荐使用)

  1. 按照功能分类
  • 内连接
    • 等值连接
    • 非等值
    • 自连接
  • 外连接
    • 左外
    • 右外
    • 全外
  • 交叉连接

(三)、SQL92语法

1. 等值连接
  • 语法:

    select 查询列表
    from 表1 别名,表2 别名
    where 表1.key=表2.key
    【and 筛选条件】(多表连接and后面可以是表2.key2=表3.key2)
    【group by 分组字段】
    【having 分组后的筛选】
    【order by 排序字段】
    
  • 特点:

    • 一般为表起别名
      • 有别名后,查询字段不能再用原来的表名去限定
    • 多表的顺序可以调换
    • n表连接至少需要n-1个连接条件
    • 等值连接的结果是多表的交集部分
2. 非等值连接
  • 语法:

    select 查询列表
    from 表1 别名,表2 别名
    where 非等值的连接条件
    【and 筛选条件】
    【group by 分组字段】
    【having 分组后的筛选】
    【order by 排序字段】
    
3. 自连接
  • 语法:

    select 查询列表
    from 表 别名1,表 别名2
    where 等值的连接条件
    【and 筛选条件】
    【group by 分组字段】
    【having 分组后的筛选】
    【order by 排序字段】
    

(四)、SQL99语法

1. 内连接(重要)
  • 语法:

    select 查询列表
    from 表1 别名
    【inner】 join 表2 别名 on 连接条件
    【【inner】 join 表3 别名 on 连接条件】
    where 筛选条件
    group by 分组列表
    having 分组后的筛选
    order by 排序列表
    limit 子句;
    
  • 特点:

    • 表的顺序可以调换(多表连接时,需要有关联列可以保证两者可以连接)
    • 内连接的结果=多表的交集
    • n表连接至少需要n-1个连接条件
  • 分类:

    • 等值连接
    • 非等值连接
    • 自连接
2. 外连接
  • 应用场景:没有关联列

  • 语法:

    select 查询列表
    from 表1 别名
    left|right|full【outer】 join 表2 别名 on 连接条件
    where 筛选条件
    group by 分组列表
    having 分组后的筛选
    order by 排序列表
    limit 子句;
    
  • 特点:

    • 查询的结果=主表中所有的行,如果从表和它匹配的将显示匹配行,如果从表没有匹配的则显示null
    • left join 左边的就是主表,right join 右边的就是主表, full join 两边都是主表
    • 一般用于查询除了交集部分的剩余的不匹配的行
  • 分类

    • 左外连接
    • 右外连接
    • 全外连接(不支持mysql)
3. 交叉连接
  • 语法:

    • select 查询列表
    • from 表1 别名
    • cross join 表2 别名;
  • 特点:

    • 类似于笛卡尔乘积

七、子查询

(一)、含义

  • 嵌套在其他语句内部的select语句称为子查询或内查询
  • 外面的语句可以是insert、update、delete、select等,一般select作为外面语句较多
  • 外面如果为select语句,则此语句称为外查询或主查询

(二)、分类

1. 按出现位置
  • select后面:
    • 仅仅支持标量子查询
  • from后面:
    • 表子查询
  • where或having后面:(重要)
    • 标量子查询 (重要)
    • 列子查询 (重要)
    • 行子查询(用的较少)
  • exists后面:
    • 标量子查询
    • 列子查询
    • 行子查询
    • 表子查询
2. 按结果集的行列
  • 标量子查询(单行子查询):结果集为一行一列
  • 列子查询(多行子查询):结果集为多行一列
  • 行子查询:结果集为多行多列
  • 表子查询:结果集为多行多列

(三)、特点

  • 子查询放在小括号

  • 子查询一般放在条件的右侧

  • 标量子查询,搭配单行操作符使用:(因为单行子查询只有一个结果)

    >, <, >=, <=, =, <>

  • 列子查询,搭配多行操作符使用:(类似in(1,2,3))

    操作符 含义 备注
    in、not in 等于列表中的任意一个 相当于in(1,2,3)、
    in相当于=any、
    not in相当于<>all
    </>any、some 小于/大于任意一个 可以用min、max代替
    </>all 小于/大于所有的 可以用min、max代替
  • 子查询先于主查询执行

(四)、示例

1. 标量子查询
  • where案例:查询最低工资的员工姓名和工资

    #①最低工资
    select min(salary) from employees
    
    #②查询员工的姓名和工资,要求工资=①
    select last_name,salary
    from employees
    where salary=(
    	select min(salary) from employees   #这里没有分号
    );
    
    • 两个条件的话就用where和and连接起来两个条件
  • having案例:查询最低工资大于50号部门最低工资的部门部门id和其最低工资

    #①先查询50号部门最低工资
    SELECT MIN(salary)
    FROM employees
    WHERE department_id=50;
    
    #②再查询各个部门的最低工资
    SELECT MIN(salary),department_id
    FROM employees
    GROUP BY department_id;
    
    #在②的基础上,选择满足min(salary)>①的结果的部门id和最低工资
    SELECT MIN(salary),department_id
    FROM employees
    GROUP BY department_id
    HAVING MIN(salary)>(
    	SELECT MIN(salary)
    	FROM employees
    	WHERE department_id=50
    );
    
    • 非法的子查询有:
      • 错误一:单行子查询使用单行比较符
      • 错误二:写入的子查询不返回任何行,即使空值
  • selsect案例:查询每个部门的员工个数

    select d.*,(
    	select count(*)
    	from employees e
    	where e.department_id=d.department_id
    ) 员工个数
    from departments d;
    
    • select内只支持标量子查询
2. 列子查询
  • where案例:查询所有是领导的员工姓名

    #①查询所有员工的 manager_id
    select manager_id
    from employees
    
    ②查询姓名,employee_id属于①列表的一个
    select last_name
    from employees
    where employee_id in(
    	select manager_id
    	from employees
    );
    
  1. from后面的
    • ​ -将查询的结果当做一张表,要求必须有别名
  • 案例:查询每个部门的平均工资和工资等级
#查询每个部门的平均工资和工资等级

#①.先查询每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id

#②.连接①的结果集和job_grades表,筛选条件是工资位于最高工资和最低工资之间
SELECT ag_dep.*,g.grade_level 
FROM (
	SELECT AVG(salary) AS ag,department_id
	FROM employees
	GROUP BY department_id
) AS avg_dep
JOIN job_grades g
ON avg_dep.ag BETWEEN lowest_sal AND highest_sal
  1. exists后面(相关子查询)
  • exists(完整的查询语句),结果是布尔值
  • 先去执行外查询,在执行exists里面的查询

八、分页查询

(一)、应用场景

  1. 当要查询的条目数太多,一页显示不全。也是分页向服务器提交请求的

(二)、语法

select 查询列表
from 表
limit 【offset,】size;

(三)、注意:

  1. offset代表的是起始的条目索引,默认从0开始。如果是从0开始可以省略
  2. size代表的是显示的条目个数
  3. limit语句放在最后

(四)、公式:

# 假如要显示的页数为page,每一页条目数为size
select 查询列表
from 表
limit (page-1)*size,size;

九、联合查询

(一)、含义

union:合并、联合,将多次查询结果合并成一个结果

(二)、语法

查询语句1
union 【all】
查询语句2
union 【all】
...

(三)、意义

1、将一条比较复杂的查询语句拆分成多条语句
2、适用于查询多个表的时候,查询的列基本是一致

(四)、注意

1、要求多条查询语句的查询列数必须一致
2、要求多条查询语句的查询的各列类型、顺序最好一致
3、union 去重,union all包含重复项

第四章 DML

一、插入

(一)、方式一

语法:
insert into 表名(列名1,列名2...) values(值1,值2...);
特点:
  1. 要求值的类型和字段的类型要一致或兼容
  2. 字段的个数和顺序不一定与原始表中的字段个数和顺序一致,但必须保证值和字段一一对应
  3. 假如表中有可以为null的字段(nullable),注意可以通过以下两种方式插入null值
    • 字段和值都省略
      • 字段写上,值使用null
  4. 字段和值的个数必须一致
  5. 字段名可以省略,默认所有列,字段顺序和表中顺序一致

(二)、方式二

语法:
insert into 表名 set 字段=值,字段=值,...;

(三)、两种方式区别:

  1. 方式一支持一次插入多行,语法如下:
insert into 表名【(字段名,..)】 
values(值,..),(值,...),...;
  1. 方式一支持子查询,语法如下:
insert into 表名
查询语句;

eg:
insert into beauty(id,name,phone)
select 23,'刘亦菲',110;(会省略values)

二、修改

(一)、修改单表的记录 ★

语法:
update 表名 
set 字段=值,字段=值 
【where 筛选条件】;

(二)、修改多表的记录【补充】

语法:
update 表1 别名 
left|right|inner join 表2 别名 
on 连接条件 
set 字段=值,字段=值 
【where 筛选条件】;

三、删除

(一)、方式一:使用delete

  1. 删除单表的记录★
  • 语法:

    delete from 表名 【where 筛选条件】【limit 条目数】
    
  1. 级联删除[补充]
  • 语法:

    delete 别名1,别名2 
    from 表1 别名1 
    inner|left|right join 表2 别名2
    on 连接条件
     【where 筛选条件】
    

(二)、方式二:使用truncate

  • 语法:

    truncate table 表名
    #删除整个表
    

(三)、两种方式的区别【面试题】★

  1. truncate删除后,如果再插入,标识列从1开始
    delete删除后,如果再插入,标识列从断点开始
  2. truncate不可以添加筛选条件
    delete可以添加筛选条件
  3. truncate效率较高
  4. truncate没有返回值
    delete可以返回受影响的行数
  5. truncate不可以回滚
    delete可以回滚

第五章 DDL

1、库的管理

一、创建库

create database 【if not exists】 库名【 character set 字符集名】;

二、修改库

alter database 库名 character set 字符集名;
  • 不是修改库名,而是修改字符集

三、删除库

drop database 【if exists】 库名;

2、表的管理

一、创建表 ★

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 筛选】;
  • 用*就是复制所以字段+数据;
  • 字段1,字段2…就是复制部分字段+数据;
  • 把where条件恒不成立(1=2或0或 false)就可以复制字段而不复制数据
  • 夸库执行的时候,加上前缀★

3、数据类型

一、数值型

1、整型
类型 占用字节
tinyint 1byte=8bit
smallint 2
mediumint 3
int/integer 4
bigint 8
  • 特点:

    ①、都可以设置无符号和有符号,默认有符号,通过int unsigned设置无符号
    ②、如果超出了范围,会报out of range异常,会插入临界值
    ③、长度可以不指定,默认会有一个长度
    ④、长度不代表范围(因为范围已经通过不同的int决定了),代表显示的最大宽度,如果不够则左边用0填充,但需要搭配int zerofill,并且默认变为无符号整型

2、浮点型
  • 定点数:decimal/dec(M,D)

  • 浮点数:
    float(M,D) 4
    double(M,D) 8

  • 特点:
    ①、M代表整数部位+小数部位的个数,D代表小数部位
    ②、如果超出范围,则报out or range异常,并且插入临界值
    ③、M和D都可以省略,浮点数插入不受限制,只要在范围内就原样输入,但对于定点数,M默认为10,D默认为0
    ④、如果精度要求较高,则优先考虑使用定点数

3、使用原则
  • 简单
  • 根据范围选择类型越小越好

二、字符型

char、varchar、binary、varbinary、blob(较长的二进制)enum(枚举)、set(集合)、text(文本)

字符 特点 空间耗费 效率 应用场景
char 固定长度,M省略时默认为1 耗费多 固定长度,如性别
varchar 可变长度,M不能省略 耗费少 长度变化多,如姓名

三、日期型

  • year年
  • date日期
  • time时间
  • datetime 日期+时间:8字节
  • timestamp 日期+时间:4 字节,比较容易受时区、语法模式、mysql版本的影响,更能反映当前时区的真实时间

4、约束

一、常见的约束

  1. NOT NULL:非空,该字段的值必填
  2. UNIQUE:唯一,该字段的值不可重复
  3. DEFAULT:默认,该字段的值不用手动插入,有默认值
  4. CHECK:检查,mysql不支持(语法不报错,但是没效果),比如限制性别为男女
  5. PRIMARY KEY:主键,该字段的值不可重复并且非空 unique+not null
  6. FOREIGN KEY:外键,从表该字段的值引用了并且必须来自于主表的字段
  • 主键和唯一键的异同【面试题】★
    1、区别:
    ①、一个表至多有一个主键,但可以有多个唯一
    ②、主键不允许为空,唯一可以为空(但是也只能有一个null)
    2、相同点
    都具有唯一性
    都支持组合键,但不推荐
  • 外键:
    1. 在从表设置外间关系
    2. 用于限制两个表的关系,从表的字段值引用了主表的某字段值
    3. 外键列和主表的被引用列要求类型一致,意义一样,名称无要求
    4. 主表的被引用列要求是一个key(一般就是主键)
    5. 插入数据,先插入主表;删除数据,先删除从表
    6. 可以通过以下两种方式来删除主表的记录

二、创建表时添加约束

create table 表名(
	字段名 字段类型 not null,#非空
	字段名 字段类型 primary key,#主键
	字段名 字段类型 unique,#唯一
	字段名 字段类型 default 值,#默认
	constraint 约束名 foreign key(字段名) references 主表(被引用列)
)
#设置通用的写法,即,外键约束用标记约束,其他的都用列级约束
#约束名建议写成:自己起的名字_本表名_引用表名

注意:

约束分类 支持类型 可否起约束名
列级约束 都支持(但是外键在myqsl无效) 不可
标记约束 不支持非空、默认 可以(对主键无效)

说明:列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求

三、修改表时添加或删除约束

1、非空

添加非空

alter table 表名 modify column 字段名 字段类型 not null;

删除非空

alter table 表名 modify column 字段名 字段类型【null】 ;
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 约束名;
6、总结

添加列级约束

alter table 表名 modify column 字段名 字段类型 新约束

添加表级约束

alter table 表名 add 【constraint 约束名】 约束类型(字段名)【外键的引用】

四、自增长列/标识列

1、特点:

​ 1、不用手动插入值,可以自动提供序列值,默认从1开始,步长为1

  • 如果要更改起始值:手动插入值

  • 如果要更改步长:更改系统变量:set auto_increment_increment=值;

    2、一个表至多有一个自增长列
    ​ 3、自增长列只能支持数值型
    ​ 4、自增长列必须为一个key

2、语法
#一、创建表时设置自增长列
create table 表(
	字段名 字段类型 约束 auto_increment
)

#二、修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment

#三、删除自增长列
alter table 表 modify column 字段名 字段类型 约束

第六章 TCL

事务

一、含义

事务:一条或多条sql语句组成一个执行单位,一组sql语句要么都执行要么都不执行

二、特点(ACID)【面试题】★

  • A(atomicity)原子性:一个事务是不可再分割的整体,要么都执行要么都不执行
  • C(consistency)一致性:一个事务可以使数据从一个一致状态切换到另外一个一致的状态
  • I(isolation)隔离性:一个事务不受其他事务的干扰,多个事务互相隔离的
  • D(durability)持久性:一个事务一旦提交了,则永久的持久化到本地

三、事务的使用步骤 ★

1、了解:
  • 隐式(自动)事务:没有明显的开启和结束,本身就是一条事务可以自动提交,比如insert、update、delete
  • 显式事务:具有明显的开启和结束
2、使用显式事务:

①、开启事务
set autocommit=0;(禁用自动提交功能)
start transaction;#可以省略

②、编写一组逻辑sql语句
注意:事务中的sql语句指的是insert、update、delete

设置回滚点:
savepoint 回滚点名(自己起名);

③、结束事务
提交:commit;
回滚:rollback;
回滚到指定的地方:rollback to 回滚点名;

四、并发事务

1、事务的并发问题是如何发生的?

多个事务 同时 操作 同一个数据库的相同数据时

2、并发问题都有哪些?
  • 脏读:一个事务读取了其他事务还没有提交的数据,读到的是其他事务“更新”的数据
  • 不可重复读:一个事务多次读取,结果不一样
  • 幻读:一个事务读取了其他事务还没有提交的数据,只是读到的是 其他事务“插入”/“删除”的数据
3、如何解决并发问题

通过设置隔离级别来解决并发问题

4、隔离级别
避免脏读 避免不可重复度 避免幻读 评价
read uncommitted × × ×
read committed × ×
repeatable read ×
serializable 性能很低
5、关于隔离级别的命令
#查看当前隔离级别
select @@tx_isolation;

#设置当前mysql连接的级别
set transaction ioslation level read committed;

#设置数据库系统的全局隔离级别
set global transation ioslation level read committed;

#一般需要重启生效

第七章 视图

一、含义

  • mysql5.1版本出现的新特性,本身是一个虚拟表,它的数据来自于表,通过执行时动态生成
  • 好处:
    1、简化sql语句
    2、提高了sql的重用性(因为是包装成视图来用)
    3、保护基表的数据,提高了安全性

二、创建

create view 视图名
as
查询语句;

#或者
create or replace view 视图名
as
查询语句;

三、修改

#方式一:
create or replace view 视图名
as
查询语句;

#方式二:
alter view 视图名
as
查询语句;

四、删除

drop view 视图1,视图2,...;

五、查看

desc 视图名;
#或者
show create view 视图名;

六、视图的更新

  1. 插入:insert
  2. 修改:update
  3. 删除:delete
  4. 注意:视图一般用于查询的,而不是更新的,所以具备以下特点的视图都不允许更新
    ①、包含分组函数、group by、distinct、having、union、
    ②、包含join
    ③、常量视图
    ④、where后的子查询用到了from中的表
    ⑤、用到了(即from了)不可更新的视图

七、视图和表的对比

关键字 物理内存 使用场景
table 保存实际的数据 增删改查
视图 view 占用小,只保存sql逻辑,不保存查询结果 一般用于查询

第八章 变量

一、系统变量

1、说明:

  • 系统变量由系统提供的,不用自定义

2、语法:

①、查看系统变量

#查看所有的系统变量
show 【global|session 】variables

#查看满足条件的系统变量
show 【global|session 】variables like ''; 

#如果没有显式声明global还是session,则默认是session

②、查看指定的系统变量的值

select @@【global.|session.】变量名; 
#如果没有显式声明global还是session,则默认是session

③为系统变量赋值
方式一:

set 【global|session 】 变量名=值; 
#如果没有显式声明global还是session,则默认是session

方式二:

set @@global.变量名=值;
set @@变量名=值;

# @@和.是一起出现的

3、注意:

  • 全局变量
    服务器层面上的,必须拥有super权限才能为系统变量赋值,作用域为整个服务器,也就是针对于所有连接(会话)有效,但是不能跨重启(跨重启需要改配置文件)

  • 会话变量
    服务器为每一个连接的客户端都提供了系统变量,作用域为当前的连接(会话)

二、自定义变量

1、用户变量
  • 作用域:针对于当前连接(会话)生效

  • 位置:begin end里面,也可以放在外面—>任何地方

  • 使用:(使用变量的时候也要加上@)

    • 声明并赋值:(三种)

      set @变量名=值;
      set @变量名:=值;
      select @变量名:=值;
      
    • 更新值
      方式一:(三种,和声明赋值一样)

      set @变量名=值;
      set @变量名:=值;
      select @变量名:=值;
      

      方式二:

      select xx into @变量名 from 表;
      
    • 使用/查看

      select @变量名;
      
2、局部变量
  • 作用域:仅仅在定义它的begin end中有效

  • 位置:只能放在begin end中,而且只能放在第一句

  • 使用:

    • 声明

      declare 变量名 类型 【default 值】;
      
    • 赋值或更新
      方式一:

      set 变量名=值;
      set 变量名:=值;
      select @变量名:=值;
      

      方式二:

      select xx into 变量名 from 表;
      
    • 使用

      select 变量名;		
      

      第九章 存储过程和函数

1、说明:

  • 相同点:都类似于java中的方法,将一组完成特定功能的逻辑语句包装起来,对外暴露名字

  • 区别:

    返回值 适用场景
    存储过程 0个、多个 批量插入、批量更新
    函数 1个 数据处理后返回一个结果

2、存储过程

零、含义

  • 一组预先编译好的SQL语句的集合,理解成批处理语句,后续使用就

零、好处

  • 提高重用性
  • sql语句简单
  • 减少了和数据库服务器连接的次数,提高了效率

一、创建 ★

create procedure 存储过程名(参数模式 参数参数类型)
begin
	存储过程体
end
  • 注意:
    1、参数模式:in(表示这个参数需要传入值)、out(这个参数可以作为返回值)、inout,其中in可以省略
    2、存储过程体的每一条sql语句都需要用分号结尾

    3、存储过程体内只有一条SQL语句时,可以省略begin…end

    4、存储过程体的结尾可以加上上文中使用delimiter设置的结束标志

二、调用

call 存储过程名(实参列表)
  • 举例:
  1. 创建并调用空参列表

    #创建
    delimiter $
    create procedure myp1()
    begin
    	insert into admin(username,password)
    	values('zhao','1111'),('qian','2222'),('sun','3333');
    end $
    
    #调用
    call myp1() $
    
  2. 创建并调用in模式的参数:call sp1(‘值’);

  3. 创建并调用out模式的参数:set @name; call sp1(@name);select @name;

  4. 创建并调用inout模式的参数:set @name=值; call sp1(@name); select @name;

三、查看

show create procedure 存储过程名;

四、删除

drop procedure 存储过程名;

3、函数

一、创建
create function 函数名(参数名 参数类型) returns  返回类型
begin
	函数体
end
  • 注意:
    • 函数体中肯定需要有return语句
    • 函数只有一句时,begin…end可以省略
    • delimiter $,然后用$结尾;
    • 自己生命变量接受查询值,然后返回
二、调用
select 函数名(实参列表);
三、查看
show create function 函数名;
四、删除
drop function 函数名;

第十章 流程控制结构

一、分支结构

1、if函数

  • 功能:实现简单双分支

  • 语法:

    if(表达式1,表达式2,表达式3) -- 如果1成立返回2,否则返回3
    
  • 位置:

    • 可以作为表达式放在任何位置
    • else语句可以省略,如果省略,且when的条件都不满足,返回null

2、case结构

  • 功能:实现多分支

  • 语法1:(等值判断)

    case 表达式或字段
    when 值1 then 语句1;
    when 值2 then 语句2;
    ...
    else 语句n;
    end case;
    
  • 语法2:(区间判断)

    case 
    when 条件1 then 语句1;
    when 条件2 then 语句2;
    ..
    else 语句n;
    end case;
    
  • 位置:

    • 可以放在任何位置,
    • 如果放在begin end 外面,作为表达式结合着其他语句使用
    • 如果放在begin end 里面,一般作为独立的语句使用
    • else语句可以省略,如果省略,且when的条件都不满足,返回null

3、if结构

  • 功能:实现多分支

  • 语法:

    if 条件1 then 语句1;
    elseif 条件2 then 语句2;
    ...
    else 语句n;
    end if;
    
  • 位置:
    只能放在begin end中

二、循环结构

  • 位置:

    都只能放在begin end中

  • 对比:

①、这三种循环都可以省略名称,如果循环中添加了循环控制语句(leave或iterate)则必须添加名称
②、loop 一般用于实现简单的死循环;while 先判断后执行;repeat 先执行后判断,无条件至少执行一次

1、while

语法:
【名称:】while 循环条件 do
		循环体
end while 【名称】;

2、loop(可以用来模拟简单的死循环)

#语法:
【名称:】loop
		循环体
end loop 【名称】;

3、repeat

#语法:
【名称:】repeat
		循环体
until 结束条件 
end repeat 【名称】;

4、循环控制语句

leave:类似于break,用于跳出所在的循环
iterate:类似于continue,用于结束本次循环,继续下一次


喜欢 (0)