首页 > 资源分享 > 分享自己整理的MySQL基础笔记(附实例代码)

分享自己整理的MySQL基础笔记(附实例代码)

头像
西安第一废物
编辑于 2019-12-14 20:29:51 APP内打开
赞 30 | 收藏 398 | 回复13 | 浏览4267

多线程的笔记:https://www.nowcoder.com/discuss/355081
牛客有的地方显示有问题 我的源地址:https://blog.csdn.net/qq_41112238/article/details/103400224

数据库的好处

  • 实现数据持久化存储
  • 使用完整的管理系统统一管理,易于查询

数据的相关概念

  1. DB
    数据库(database) 存储数据的仓库,保存了一系列有组织的数据
  2. DBMS
    数据库管理系统(Database Management System)用于管理DB中的数据,数据库是通过DBMS创建和操作的容器。
    常见的数据库管理系统:MySQL、Oracle、 DB2、 SQL server
  3. SQL
    结构化查询语言(Structure Query Language):专门用来与数据库通信的语言
    特点:①不是某个特定数据库供应商专有的语言,几乎所有DBMS都支持SQL。②简单易学。③灵活使用可以进行非常复杂和高级的数据库操作。

    数据库存储数据特点

  4. 将数据放到表中,表再放到库中
  5. 一个数据库可以有多个表,每个表都有一个唯一的表名用来标识自己。
  6. 表具有一些特性,这些特性定义了数据在表中如何存储,类似Java中的类。
  7. 表由列组成,也称字段。每个表都由一或多个列组成,类似Java中的属性。
  8. 表中的数据是按行存储的,每一行类似于Java中的对象。

MySQL

优点

  • 成本低,开源,一般可***。

  • 性能高,执行很快。

  • 简单,易于安装和使用。

  • DBMS的分类*

  • 基于共享文件系统的DBMS(access)

  • 基于C/S的DBMS(MySQL,Oracle,SQL server)(一般安装的是服务端)。

  • 配置*
    mysql安装目录中的my.ini中是mysql的配置文件

  • [mysql]配置的是客户端

  • [mysqld]配置的是服务器端

  • port设置端口,默认为3306

  • datadir是数据目录

  • basedir是安装目录

  • character-set-server设置字符集

  • default-storage-engine设置默认数据库引擎
    改完配置后需要重启服务

MySQL服务的启动和停止

  • 第一种方式,右键计算机->管理->服务和应用程序->服务->找到MySQL
  • 第二种方式,cmd打开命令行窗口
    启动:`net start mysql`
    停止:`net stop mysql`

MySQL的登录/退出

  • 方式一:通过MySQL自带客户端 只限于root用户
    (现在系统变量Path配置MySQL)
    • 方式二:命令行窗口
    • 登陆*mysql [-h localhost] [-P 3306] -u root -p([...]代表可选,连接本机可省略)
    • 退出*exit

MySQL常用命令

需要登陆后使用:

 - show databases; 查看所有数据库
 - use 数据库名;使用某个数据库
 - show tables; 显示当前选中数据库中的所有表
 - show tables form 数据库名; 显示某数据库中的表(不改变所选中数据库)
 - select database() ;查看所选中的数据库
 - desc 表名; 查看某表的结构
 - select * from 表名; 查看某表的全部记录
 - select version();查看数据库版本(登陆前:mysql --version/mysql -V)
 - create table 表名( 列名 列类型...);创建表

MySQL的语法规范

  • 不区分大小写,建议关键字大写,表名和列名小写。
  • 每条命令用\g或;结尾,建议用;结尾。
  • 根据命令需要,可以进行缩进或换行。
  • 注释:
     单行注释:#注释文字  或        --(空格)注释文字
     多行注释:/* 注释文字 */

DQL Data Query Language

  • 进阶1 :基础查询
    语法:select 查询列表 from 表名;
    查询列表可以是:表中的字段、常量、表达式、函数
    查询的结果是一个虚拟的表格

    1. 查询表中的单个字段
    例:select last_name from employees;
    2. 查询表中的多个字段
    例:select last_name,salary from employees;
    3. 查询表中的所有字段
    例: select * from 表名;
    4. 查询常量值
    例:select 100;/ select ‘john’;
    5. 查询表达式
    例:select 100*98;
    6. 查询函数
    例:select version();
    7. 起别名
    好处:便于理解、如果要查询的字段有重名情况,使用别名区分
    用AS:select 10098 *as** 结果;
    省略AS:select last_name 姓;
    别名中有关键字,加引号,例:select salary as 'out put' from employees;
    8. 去重
    例:查询员工表中所有部门编号select DISTINCT department_id from employees;
    9. +号的作用
    仅有一个功能,运算符。

  例:select 100+90;//190  两个操作数都为数值型,加法运算。
  例:select '100'+90;//190 其中一个为字符型,试图将字符型转换成数值型,如果转换成功,继续做加法运算。如果失败,将字符型数值转换成0。 
  例:select 'a'+90;//90
  例:select null+10;//null 其中一方为null,结果为null。
  例:查询员工名和姓,并显示为 姓名。
  select last_name+first_name as 姓名 from employees;//错误方式,输出0

如要拼接,使用concat()

select CONCAT( last_name,first_name) as 姓名 from employees;//正确方式

如concat中有某列存在null值,结果为null。
可使用IFNULL(列名,为null时默认值),不为null时返回原本值。
ISNULL 判断结果,为true返回1,false返回0。

  • 进阶2 :条件查询
    语法: select 查询列表 from 表名 where 筛选条件
    分类:
    1.按条件表达式筛选
    条件运算符:大于> 小于< 等于= 不等于<> !=大于等于>= 小于等于<=
    2.按逻辑运算符筛选
    逻辑运算符:
    与:&& and 如果两个条件都为true则为true,否则为false
    或:|| or 如果有一个条件为true则为true,否则为false
    非:!not 取反
    3.模糊查询
    like
    一般和通配符搭配使用 %:0或多个字符 _任意单个字符
    可判断字符型或数值型
    例:查询员工名包含a的信息
select * from enployees where last_name **like** '%a';

例:查询员工名第三个字符为a第五个为b的信息

 select * from enployees where last_name **like** '__a_b%';   

例:查询员工名第二个字符为_的信息

 select * from enployees where last_name like  '_\_%';
  select * from enployees where last_name like '\_$_%' escape '\$' ;

between and
可提高语句的简洁度,包含边界值,不能颠倒。
例:查询工资在8000到9000之间的员工信息

 select * from employees where salary BETWEEN 8000 AND 9000;

in
用于判断某字段的值是否属于in列表中的某一项
提高语句简洁度,in列表的值类型必须一致或兼容,不能包含通配符
例:查询员工编号1,2,3中的一个的员工信息

 select * from employees where employee_id in (1,2,3);

is null
例:查询没有奖金的员工名

 select last_name from employees where commission_pct IS NULL;

为空用IS NULL 不为空用IS NOT NULL不能用=或!=
<=>
安全等于,除普通数值,还可用于判断null值。可读性差。

  • 进阶3 :排序查询
    语法:
    select 查询列表 from 表 where 筛选条件 order by 排序列表 [asc/desc]
    默认为asc升序。
    一般放在查询语句的最后面,limit语句除外。
    别名排序:
    例:按员工年薪排序
 SELECT last_name,salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 FROM employees ORDER BY 年薪;

函数排序:
例:按员工姓名长度排序

 SELECT LENGTH(last_name) 姓名长度,last_name FROM employees ORDER BY 姓名长度;

多字段排序:
例:先按工资再按编号排序

 SELECT * FROM employees ORDER BY salary,employee_id DESC;
  • 进阶4 :常见函数
    好处:提高重用性、隐藏实现细节
    调用:select 函数名(实参列表) from 表名
    分类:
  • 单行函数*,如concat、length、ifnull等
  • 1.字符函数*
    length(str) 获取str的字节个数 一个汉字占3字节(utf8)
    concat(str1,str2...strn) 拼接字符串
    upper(str)lower(str) 转换大小写
    substrsubstring返回子字符串,SQL中索引从1开始
    substr(str,n) 截取从n开始的所有字符
    substr(str,n,m) 截取从n开始的m个字符
    instr(str,substr) ,返回substr在str中第一次出现的索引,没有返回0。
    trim(str),去掉字符串中空格。
    trim(x from str),去掉str前后的x字符。
    lpad(str,n,c) 左填充,用指定字符c填充str左侧至n长度。
  • rpad(str,n,c)* 右填充,用指定字符c填充str右侧至n长度。
    replace(str,a,b) 替换
  • 2.数学函数*
    round(x) 四舍五入
    ceil(x) 向上取整,返回大于等于参数的最小整数
    floor(x) 向下取整,返回小于等于参数的最大整数
    truncate(n,m) 截断n,保留m位
    mod(a,b) 取余
    rand() 获取0-1之间的随机数
  • 3.日期函数*
    now() 返回当前系统日期+时间
    curdate() 返回当前日期
    curtime() 返回当前时间
    year(),month(),day(),hour(),minute(),second() 获取年月日时分秒
    str_to_date() 将日期格式的字符转换成指定格式的字符串
    date_format() 将日期转换成字符
    datediff()返回两日期相差的天数
  • 4.其他函数*
    version() 版本号
    databases() 查看所有数据库
    user() 当前用户
    password(str) 返回加密形式(MySQL8已弃用)
    md5(str) 返回字符串的md5模式
  • 5.流程控制函数*
    if (exp1,exp2,exp3) 若exp1为true,返回exp2,否则返回exp3
    case
    用法一:
    case 要判断的字段或表达式
    when 常量1 then 要显示的值 或 语句1;
    ...
    when 常量n then 要显示的值 或 语句n;
    else 要显示的值或语句m
    end
    用法二:
    case
    when 条件1 then 要显示的值或语句1;
    ...
    when 条件n then 要显示的值 或 语句n;
    else 要显示的值或语句m
    end
  • 分组函数,做统计使用,又称统计函数或聚合函数
    sum()** 求和
    avg() 求平均值
    min() 求最小值
    max() 求最大值
    count() 求非空个数
    count(
    )/count(常量值) 统计总行数
    MYISAM存储引擎下,count(*)效率高,INNODB下差不多
    sum和avg对数值型处理,min和max可对字符型和日期型排序
    所有分组函数都忽略null值,可和distinct搭配使用
    和分组函数一同查询的字段要求是group by后的字段
  • 进阶5 :分组查询
    语法:
            select 分组函数,列
            from 表名 【where 筛选条件】
            group by 分组列表 【order by 字句】
        注意:查询列表必须使分组函数和group by后出现的字段

特点:
分组前筛选 数据源为原始表 用where
分组后筛选 数据源为分组后结果集 用having
分组函数做条件肯定放在having子句中
能用分组前筛选的优先考虑分组前筛选
group by支持单个字段,多个字段(用,隔开),表达式或函数分组,也可以添加排序(放在最后)。

  • 进阶6 :连接查询
    又称多表查询,当查询的字段来自多个表时,会用到连接查询
    笛卡尔乘积:查询多个表时没有添加有效的连接条件,导致多个表出现完全连接。如表1有a行,表2有b行,将产生ab行结果。
    避免:添加*
    连接条件**
  • 分类*
    按年代:
    sql92(仅支持内连)、sql99(支持内连接、左外连接、交叉连接)
    按功能:
    内连接(等值,非等值,自然) 外连接(左,右,全) 交叉连接
  • 1.sql92 等值连接*
    例:查询员工名和对应部门名
SELECT
    last_name,department_name
FROM
    employees,departments
WHERE
    employees.`department_id`=departments.`department_id`;
多表等值连接的结果为多表的交集部分,n表连接至少需要n-1个连接条件,多表的顺序没有要求,一般需要为表起别名,可以搭配排序分组筛选字句。

注意:为表起别名后,不能使用原表名。
2.sql92 非等值连接
where后跟非等值连接条件
3.sql92 自然连接
把一张表通过别名当多表使用
4.sql99语法
语法:

select 查询列表
from  表1 别名 
连接类型  join 表2 别名
on 连接条件
...

内连接inner 可以省略
1.等值连接
和sql92的等值连接效果一样
2.非等值连接
3.自然连接
例:查询员工及对应上级名

SELECT e1.`last_name`,e2.`last_name` FROM  employees e1
INNER JOIN employees e2 ON e1.`manager_id`=e2.`employee_id`

外连接
用于查询一个表中有,另一个表中没有的记录
特点:外连接查询的结果为主表中的所有记录,如果从表有和他匹配的则显示匹配的值,若没有则显示null。外连接查询结果=内连接结果+主表有而从表没有的记录。
左外连接中left左边的是主表,右外连接right右边的是主表。
左外和右外交换顺序,可实现同样的效果
左外连接:left 【outer】
例:查询哪个部门没有员工

SELECT d.*,e.`id` FROM departments d 
LEFT JOIN employees e ON d.`department_id`=e.`department_id`
WHERE e.`employee_id` IS NULL

右外连接:right【outer】
**
交叉连接:cross【outer】
两个表进行笛卡尔乘积
全外连接:full【outer】
等于内连接的结果+表1中有表2中没有的+表2中有但表1中没有的
5.sql92和sql99比较
功能:sql99支持较多
可读性:sql99实现连接条件和筛选条件的分离

图1 左外连接/内连接/右外连接

**

图2 左外连接/右外连接/全外连接

在这里插入图片描述

  • 进阶7 :子查询
  • 含义*:出现在其他语句中的select语句,称为子查询或内查询
    外部的查询语句,称为主查询或外查询
  • 分类*
  • 按子查询出现的位置*
  • 1.select后面* 仅支持标量子查询
    案例:查询每个部门的员工个数
SELECT d.*,(SELECT COUNT(*) FROM employees WHERE employees.`department_id`=d.`department_id`)
FROM departments d

2.from后面 支持表子查询
案例:查询每个部门的平均工资的工资等级

SELECT t1.*,t2.`grade_level`
FROM(
    SELECT department_id,AVG(salary) avg_salary FROM employees
    GROUP BY department_id
) t1 INNER JOIN job_grades t2
ON t1.avg_salary BETWEEN t2.`lowest_sal` AND t2.`highest_sal`

将子查询结果充当一张表,要求必须起别名
3.where或having后面
特点:子查询都放在小括号内、子查询放在条件右侧、标量子查询搭配单行操作符(>, <, >=, <=, =, <>)、列子查询搭配多行操作符(in/not in,any/some,all)、子查询的执行优先于主查询执行,主查询的条件用到了子查询的结果
①支持标量子查询(单行)
案例:谁的工资比abel高?

SELECT e.`last_name` FROM employees e WHERE e.`salary`>(SELECT salary FROM employees WHERE last_name = 'Abel');

②列子查询(多行)
案例:查询其他部门比it_prog部门任意工资低的员工名,工作类别和工资

SELECT last_name,job_id,salary FROM employees
WHERE job_id <> 'IT_PROG' AND salary<(
SELECT MAX(salary) FROM employees 
WHERE job_id='IT_PROG'
)

③行子查询(少)
案例:查询员工编号最小并且工资最高的员工信息

SELECT * FROM employees
WHERE (employee_id,salary) = (SELECT MIN(employee_id),MAX(salary) FROM employees)

4.exists后面(相关子查询) 支持表子查询
结果为1或0,1表示存在结果,0表示不存在。
案例:查询没有女朋友的男人
SELECT * FROM boys bo
WHERE NOT EXISTS(SELECT * FROM beauty b WHERE b.boyfriend_id=bo.id)
按功能不同
标量子查询(结果集只有一行一列)
列子查询(结果集只有一列多行)
行子查询(结果集有一行多列)
表子查询(结果集,一般多行多列)

  • 进阶8 :分页查询
    应用场景:当要显示的数据一页显示不全,需要分页提交sql请求
    特点:limit语句放在查询语句的最后
  • 公式*:要显示的页数page,每页的条目数size
select 查询列表 from 表 limit (page-1)*size,size

语法

select 查询列表 from 表 .... limit offset,size

offset:要显示条目的索引 从0开始
size:要显示的条目个数
案例:查询前5条员工信息
SELECT * FROM employees LIMIT 0,5
案例:查询有奖金的员工信息,并显示工资较高的前10名

SELECT * FROM employees 
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC  LIMIT 0,10
  • 查询涉及的关键字 ----------执行顺序

  • select* 查询列表 ------------------⑦

  • from* 表 -----------------------------①

  • 连接类型* join 表2-----------------②

  • on* 连接条件-------------------------③

  • where* 筛选条件--------------------④

  • group by* 分组列表----------------⑤

  • having* 分组后筛选----------------⑥

  • order by* 排序列表-----------------⑧

  • limit* 偏移,条目数 ----------------⑨

  • 进阶9 : 联合查询
    将多条查询语句的结果合并成一个结果
    语法:

  • 查询语句1 union 查询语句2 ...*
    应用场景:当要查询的结果来自多表且多表间无直接连接关系
    特点:
    ①要求多条查询语句的查询列数一致
    ②要求多条查询语句查询的每一列的类型和顺序最好一致
    ③默认去重,使用union all可以包含重复项
    ④将一条比较复杂的查询语句拆分成多条

DML Data Manipulation Language

  • 插入语句

  • 语法*

  • 方式一*insert into 表名(字段...) values (值...)
    要求插入值得类型必须与列得类型一致或兼容
    不为null的列必须插入值,可为null的列可以插入null或同时省略字段和值
    省略插入列名默认所有列,而且列的顺序和表的顺序一致

  • 方式二*insert into 表名 set 插入列名 = 值...

  • 比较*:方式一支持插入多行、方式一支持子查询

  • 修改语句

  • 修改单表的记录*⭐:update 表名 set 列 = 新值,... where 筛选条件

  • 修改多表的记录*

update 表1 别名,表2 别名 set 列=值... where 连接条件 and 筛选条件
update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列=值.. where 筛选条件
  • 删除语句
  • 方式一*
  • 语法*
  • 单表的删除*
    delete from 表名 where 筛选条件
  • 多表的删除*
delete 要删除表的别名 from 表1 别名 inner|left|right join 表2 别名 on 连接条件 where 筛选条件

方式二
语法truncate table 表名,整个表全部删除
区别
①delete 可以加where条件
②truncate效率较高
③如果要删除的表中有自增长列,如果用delete删除后再插入数据,值从断点开始,而truncate删除后再插入数据,值从1开始。
④truncate删除没有返回值,delete有。
⑤truncate不能回滚,delete可以。

DDL Data Define Language

数据定义语言,涉及库和表的管理
创建:create 修改:alter 删除:drop

  • 库的管理
    创建:
create database [if not exists]  库名;

修改:

 alter database 库名 character set gbk|utf8;

删除:

 drop database if exists 库名;
  • 表的管理
  • 创建:*
 create table [if  not exists]  表名(列名 列类型[长度] [约束]...)

修改:
①修改列名

alter table 表名  change [column] 旧列名 新列名 类型;

②修改列的类型或约束

alter table 表名 modify column 列名 新类型

③添加列

alter table** 表名  **add cloumn** 列名 类型 **[first|after 字段名]**

④删除列

 alter table 表名 drop cloumn 列名

⑤修改表名

 alter table 表名 rename to  新名

删除:

drop table [if exists] 表名;
  • 表的复制
  • 仅复制表的结构*
 create table 目标表名 like 源表名

仅复制表的部分结构

create table 目标表名 select 目标列名 源表名  where 0;

复制表的结构+数据

create table 目标表名 select * from 源表名

复制表部分数据

create table 目标表名 select * from 源表名 where 复制条件
  • 常见的数据类型
    一 . 数值型
    整形
    默认有符号 设置无符号用unsigned 零填充zerofill(默认无符号)
    如果插入值超过范围,报警告并插入临界值
    长度代表了显示的最大宽度,不够会0填充(需要搭配zerofill)
    小数:m表示整数加小数部位的总长度,d表示小数点后保留位数,超过范围显示临界值,可省略,省略时dec的m默认10,d默认0。
    浮点型
    float(m,d)
    double(m,d)
    定点型 精度较高,如要求插入数值精度较高如货币运算考虑浮点型
    dec(m,d)
    二 . 字符型
    较短的文本:用来保存MySQL中较短的字符串 M为最大字符数
    char(M) 固定长度字符 M为0-255整数 M可省略,默认1
    varchar(M) 可变长度字符 M为0-65535整数 M不可省略
    区别:char比较耗费空间,但效率较高。如存储固定属性时可用char。
    其他
    binary和var binary用于保存较短二进制
    enum 保存枚举,不区分大小写 插入失败为空
    set 保存集合,和enum类似,区别为可选多个
    较长的文本:
    text、
    blob(较长的二进制数据)
    三 . 日期型
    date只保存日期
    time只保存时间
    year只保存年
    datetime和timestamp保存日期+时间
    datetime和timestamp区别:
    1 timestamp支持范围较小
    2 timestamp 和实际时区有关,受MySQL版本和SQLMode影响大
    3 datetime 8字节 范围1000-9999
    4 timestamp 4字节 范围 1970-2038

  • 常见约束

  • 含义*:一种限制,用于限制表中的数据,为了保证表中的数据的准确和可靠性
    添加约束的时机:创建表时、修改表时

  • 添加分类*

  • 1 列级约束*
    直接在字段名和类型后面追加约束类型,只支持默认、非空、主键、唯一,不可起名

  • 2 表级约束*
    在各个字段最下面 [constraint 约束名] 约束类型(字段名),支持主键、唯一、外键,可起名

  • 分类*

  • - 1* NOT NULL 非空约束 保证该字段的值不为空

  • - 2* DEFAULT 默认约束 用于保证该字段有默认值

  • - 3* PRIMARY KEY 主键约束 用于保证该字段值具有唯一性,并且非空

  • - 4* UNIQUE 唯一约束 保证该字段值唯一,但可为空

  • 主键和唯一键的区别*
    ①都可以保证唯一性②主键不允许为空,唯一允许为空(只能插入一个null)③一个表中至多一个主键,唯一键可以有多个④都可以组合使用(不推荐)

  • - 5* CHECK 检查约束 MySQL中不支持

  • - 6* FOREIGN KEY 外键约束,限制两个表的关系,保证该字段必须来自于主表关联列的值,在从表添加外键约束,用于引用主表中某列的值

  • 外键的特点*
    ①要求在从表设置外键关系②从表的外键列类型和主表的关联列类型要求一致或兼容,名称无要求③主表的关联列必须是一个key(一般是主键)④要求插入数据时,先插入主表,再插入从表,删除数据时,先删除从表,再删除主表。

  • 修改表时添加约束*

添加非空约束:
alter table 表名 modify column 列名 类型 not null
添加默认约束:
alter table 表名 modify column 列名 类型 default 默认值
添加主键约束(主键支持列级约束和表级约束):
alter table 表名 modify column 列名 类型 primary key
alter table 表名 add primary key(列名)
添加唯一键约束(唯一键支持列级约束和表级约束):
alter table 表名 modify column 列名 类型 unique 
alter table 表名 add unique(列名)
添加外键约束:
alter table 表名 add foreign key(列名)  references 表名(列名)
添加级联删除:末尾添加 on delete cascade  从表中被删外键值所在行被删
添加级联置空:末尾添加  on delete set null 从表中被删外键值置null

修改表时删除约束

删除非空约束:
alter table 表名 modify column 列名 类型 null
删除默认约束:
alter table 表名 modify column 列名 类型 
删除主键约束:
alter table 表名 drop primary key
删除唯一键约束:
alter table 表名 drop index  键名
删除外键约束:
alter table 表名 drop foreign key 键名
  • 标识列
    又称自增长列,含义:可以不用手动插入值,系统提供默认的序列值
    特点:①必须和key搭配②一个表中至多有一个标识列③类型只能是数值型④可通过set auto_increment_increment设置步长
        - 1 创建表时设置标识列**
        在列名后增加**auto_increment**关键字
        - 2 修改表时设置标识列**
        alter table 表名 modify column 列名 类型 auto_crement;
        - 3 修改表时删除标识列**
        alter table 表名 modify column 列名 类型 ;

TCL Transaction Control Language

  • 事务
    一条或多条sql语句组成的一个执行单元,这个执行单元要么全部执行,要么全部不执行
  • 存储引擎
    概念:MySQL中数据用各种不同技术存储在文件(或内存)中
    通过show engines查看mysql支持的存储引擎
    在MySQL中用的最多的存储引擎有:innodb、myisam、memory等,innodb支持事务,其余不支持。
  • 事务的属性ACID
    原子性Atomicity
    指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生
    一致性Consistency
    事务必须使事务从一个一致性状态变换到另一个一致性状态
    隔离性Isolation
    指一个事务的执行不能被其他事务干扰,即一个事物内部的操作及使用的数据对并发的其他事务是隔离的,并发执行的各个事务之间不能互相干扰
    持久性Durability
    指一个事务一旦被提交,它对数据库中数据的改变是永久性的
  • 事务的使用
    隐式事务:事务没有明显的开启和结束的标记,如insert、update、delete语句
    显式事务:事务具有明显的开启和结束的标记,前提:必须先设置自动提交功能为关,set autocommit=0
步骤1:
    开启事务 set autocommit=0; start transaction;(可选)
步骤2:
    编写事务中的sql语句(select insert update delete等)
**savepoint** 节点名,设置保存点
**rollback to** 节点名,可回滚到保存点
步骤3:
    结束事务 commit;提交事务 rollback 回滚事务;

delete和truncate在事务使用时的区别
delete可成功回滚,truncate回滚后表仍删除。

  • 隔离级别
  • 脏读*:对于两个事务T1和T2,T1读取了已被T2 更新但还没有提交 的字段,之后若T2回滚,T1读取的内容是临时且无效的。
  • 不可重复读*:T1读取了一个字段,T2 更新该字段并提交 ,T1再次读取同一字段,值不同
  • 幻读*:T1读取了一个字段,T2在该表中插入了一些新行,之后T1再读取同一个表会多出几行
    Oracle支持两个隔离级别:读已提交、可串行化。
    MySQL支持四个隔离级别:读未提交(存在脏读、不可重复读、幻读)、读已提交(解决脏读)、可重复读(默认,解决脏读,不可重复读)、可串行化(解决所有并发问题,但效率较低)
设置当前隔离级别:**set transaction isolation level** 级别
设置全局隔离级别:**set global transaction isolation level** 级别

视图

含义:虚拟表,和普通的表一样使用,MySQL5.0.1出现的新特性,是通过表动态生成的数据,只保存sql逻辑,不保存查询结果
应用场景:多个地方用到同样的查询结果、该查询结果的sql语句较复杂
好处:重用sql语句,简化复杂sql操作,保护数据提高安全性
-创建视图
create view 视图名 as 查询语句
-修改视图

   **方式一:create or replace view** 视图名 **as** 查询语句
   **方式二:alter view** 视图名 **as** 查询语句

具备以下特点的视图不允许更改:
①包含:分组函数 distinct group by、having、union、union all、join
②常量视图
③select包含子查询、from后跟不能更新的视图、where字句的子查询引用了from字句中的表
-删除视图
drop view 视图名...
-查看视图
desc 视图名;
show create view 视图名;
- 视图和表的区别
视图创建用create view,基本不占用实际物理空间,只是保存sql逻辑,一般不能增删改
表创建用create table,占用实际物理空间,保存了具体数据

变量

  • 系统变量
    变量由系统提供,不用自定义
    查看所有系统变量:
    show global | [session] variables;
    查看满足条件的部分系统变量:
    show global | [session] variables like 条件;
    查看某个指定系统变量:
    select @@global | [session] .系统变量名
    为系统变量赋值:
    set @@global | [session] .系统变量名 = 值;
    不写默认为session
    ①全局变量
    服务器层面,必须拥有super权限才能为系统变量赋值
    作用域:服务器每次启动为所有全局变量赋初始值,针对所有会话有效,不能跨重启
    ②会话变量
    服务器为每一个连接的客户端都提供了系统变量
    作用域:仅针对当前会话(连接)有效
  • 自定义变量
    声明
    赋值
    使用(查看,比较,运算)
    用户变量
    作用域:针对当前会话(连接)有效
    应用在任何地方
      - 1 声明并初始化
      set @用户变量名 = 值
      set @用户变量名:=值
      select @用户变量名:=值
      - 2 赋值
      set @用户变量名 = 值
      set @用户变量名:=值
      select @用户变量名:=值
      select 字段 into @变量名 from 表
      - 3 查看
      select @用户变量名

局部变量
作用域:仅在定义它的begin end中有效
应用在begin end中的第一句话

     声明:
       declare 变量名 类型
       declare 变量名 类型 [default 值]
    赋值:    
    set 变量名 = 值;
    set 变量名:=值
    select @用户变量名:=值:
    select 字段 into 变量名 from 表;
       查看:
    select 变量名;

存储过程和函数

  • 存储过程
    含义:一组预先编译好的sql语句的集合
    好处:减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率
  • 创建*
create procedure 存储过程名(参数列表)
begin
    存储过程体(一组sql语句)
end

注意:参数列表包括参数模式 参数名 参数类型
1.参数模式:
IN 代表参数可以作为输入,该参数需要调用方传入值 IN可省略
OUT 该参数可以作为输出,可以作为返回值
INOUT 该参数既可以作为输入,又可以作为输出
2.如果存储过程里仅有一句话,可以省略begin end
3.存储过程体的每条语句用;结尾,存储过程的结尾可以使用delimiter重设
调用
call 存储过程名(实参列表)

无参存储过程案例:向admin表插入5条记录
DELIMITER $
CREATE PROCEDURE myp2()
BEGIN
    INSERT INTO admin(username,PASSWORD) VALUES('lily',2211),('jack',2211),('sad',2211),('tom',2211),('sts',2211);    
END $

CALL myp2()$
带in模式的存储过程案例:判断登陆是否成功
DELIMITER $
CREATE PROCEDURE myp4(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
    DECLARE result INT DEFAULT 0;
    SELECT COUNT(*) INTO result
    FROM admin
    WHERE admin.`username`=username AND admin.`password`=PASSWORD;
    SELECT IF(result>0,'success','defeat');
END $

CALL myp4('lyt','6666')$
带out模式的存储过程案例:根据女神名,返回对应男神名
DELIMITER $
CREATE PROCEDURE myp5(IN beautyname VARCHAR(20),OUT boyname VARCHAR(20))
BEGIN
    SELECT bo.boyName INTO boyname
    FROM boys bo
    INNER JOIN beauty b ON bo.id=b.boyfriend_id
    WHERE b.name=beautyname;

END $

CALL myp5('小昭',@bname)$
SELECT @bname $
带inout模式的存储过程案例:传入a和b,返回2倍的a和b
DELIMITER $
CREATE PROCEDURE myp6(INOUT a INT,INOUT b INT)
BEGIN
    SET a=2*a;
    SET b=2*b;
END $

SET @a=1$
SET @b=2$
CALL myp6(@a,@b)$
SELECT @a,@b $//a=2,b=4
练习题1 创建存储过程实现传入用户名和密码,插入到admin表
DELIMITER $
CREATE PROCEDURE test1(IN uname VARCHAR(20),IN upwd VARCHAR(20))
BEGIN
    INSERT INTO admin(username,PASSWORD) VALUES(uname,upwd);
END $
CALL test1('kobe','1234')$


练习题2 创建存储过程实现传入女神编号,返回女神名称和电话
DELIMITER $
CREATE PROCEDURE test2(IN gid VARCHAR(20),OUT gname VARCHAR(20),OUT gtel VARCHAR(20))
BEGIN
    SELECT NAME,phone INTO gname,gtel
    FROM beauty WHERE id=gid;
END $
CALL test2(2,@gname,@gtel)$
SELECT @gname,@gtel $

练习题3 创建存储过程实现传入两个女神生日,返回大小
DELIMITER $
CREATE PROCEDURE test3(IN date1 DATETIME,IN date2 DATETIME,OUT re INT)
BEGIN
    SELECT DATEDIFF(date1,date2) INTO re;
END $


练习题4 创建存储过程实现传入一个日期,格式化为xx年xx月xx日返回
DELIMITER $
CREATE PROCEDURE test4(IN date1 DATETIME,OUT date2 VARCHAR(20))
BEGIN
    SELECT DATE_FORMAT(date1,'%y年%m月%d日') INTO date2;
END $

CALL test4(NOW(),@date2)$


练习题5 创建存储过程实现传入女神名,返回 女神 AND 男神 格式的字符串
DELIMITER $
CREATE PROCEDURE test5(IN girlname VARCHAR(20),OUT str VARCHAR(20))
BEGIN
    SELECT CONCAT(girlname,'and',IFNULL(boyname,'null')) INTO str
    FROM boys bo RIGHT JOIN beauty b ON b.boyfriend_id=bo.id
    WHERE b.name=girlname;
END $

CALL test5('小昭',@str)$

练习题6 创建存储过程,根据传入的条目数和起始索引,查询beauty表的记录
DELIMITER $
CREATE PROCEDURE test6(IN size INT,IN startindex INT)
BEGIN
    SELECT * FROM beauty LIMIT startindex,size;
END $

删除
一次只能删除一条
DROP PROCEDURE 存储过程名;
查看存储过程的信息
show create procedure 存储过程名;

  • 函数
    含义和存储过程类似,区别为函数必须有且仅有一个返回值
    存储过程适合批量插入
    函数适合处理数据后返回一个结果
  • 创建*
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END

参数列表包括参数名和参数类型
函数体必须有return语句,当函数体中仅有一句话时可以省略begin,end
使用delimiter设置结束标记
调用
select 函数名(参数列表)

案例1 无参数 有返回 返回公司的员工个数
DELIMITER $
CREATE FUNCTION fun1() RETURNS INT
BEGIN
    DECLARE c INT DEFAULT 0;
    SELECT COUNT(*)  INTO c FROM employees;
    RETURN c;
END$
SELECT fun1()$

案例2 有参数 有返回 根据员工名,返回其工资
DELIMITER $
CREATE FUNCTION fun3(ename VARCHAR(20)) RETURNS DOUBLE
BEGIN
    DECLARE sal DOUBLE;
    SELECT salary INTO sal FROM employees WHERE last_name =ename;
    RETURN sal;
END$
SELECT fun3()$

创建函数 实现传入2个float 返回两者之和
CREATE FUNCTION ADD(a FLOAT,b FLOAT) RETURNS FLOAT
BEGIN
    DECLARE c FLOAT;
    SET c=a+b;
    RETURN c;
END $
SELECT ADD(1,2)$

查看
show create function 函数名
删除
drop function 函数名

流程控制结构

  • 顺序结构:程序从上往下依次执行
  • 分支结构:程序从两条或多条路径中选择一条执行
  • 1 if函数* 能实现简单的双分支
    语法:select if(表达式1,表达式2,表达式3)
    如果表达式1成立,则返回表达式2的值,否则返回表达式3的值
    应用:任何地方
  • 2 case结构*
    情况1:类似于switch 一般用于等值判断
    case 变量|表达式|字段
    when 要判断的值 then 返回的值1|语句1;
    when 要判断的值 then 返回的值2|语句2;
    ...
    else 返回的值n
    end case;

    情况2:类似多重if 一般用于实现区间判断
    case 
    when 要判断的条件1 then 返回的值1|语句1;
    when 要判断的条件2 then 返回的值2|语句2;
    ...
    else 返回的值n
    end case;
    特点:
    1 既能作为表达式嵌套在其他语句中使用,可在任何地方使用
    2 如果作为独立语句只能放在begin end中
    3 如果when中的值或条件成立,执行对应then后面的语句,并结束case,
    如果都不满足执行else中语句,else可以省略,如果else省略并且所有条件都不满足,返回null

    例:创建存储过程,根据传入的成绩显示对应等级
    DELIMITER $
    create procedure test(in score int)
    begin
        case
        when score>=90 and score<=100 then select 'A';
        when score>=80 then select 'B';
        when score>=60 then select 'C';
        else select 'D';
        end case;
    end $

3 if结构
实现多重分支
语法:

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
[else 语句n;]
end if;
只能用于begin end中


例:创建函数,根据传入的成绩显示对应等级
    DELIMITER $
    create function fun(score int) returns char
    begin
        if score>=90 and score<=100 then return 'A';
        elseif score>=80 then return 'B';
        elseif score>=60 then return 'C';
        else return 'D';
        end if;
    end $
  • 循环结构:程序在满足一定条件的基础上重复执行一段代码
  • 只能放在begin end中*
    分类:
    while、loop、repeat
    循环控制:
    iterate 类似于continue 结束本次循环,继续下一次
    leave 类似于break,结束当前所在循环
1. while
[标签:]while 循环条件 do
    循环体;
end while [标签];
2. loop
[标签:]loop
     循环体;
 end loop[标签];
3. repeat
[标签:]repeat
     循环体;
 until 结束循环的条件;
 end repeat[标签];

while案例:批量插入,根据设定的次数插入admin表多条记录
create procedure test(in time int)
begin
    declare i int default 1;
    while i<=time do
        insert into admin(username,password) values (concat('jon',i),'123');
        set i=i+1;
    end while;
end$

leave案例:批量插入,根据设定的次数插入admin表多条记录,次数大于20则停止
create procedure test(in time int)
begin 
    declare i int default 1;
    a:while i<=time do
        insert into admin(username,password) values (concat('jon',i),'123');
        if i>20 then leave a;
        end if;
        set i=i+1;
    end while a;
end$

iterate案例:批量插入,只插入偶数记录
create procedure test(in time int)
begin 
    declare i int default 0;
    a:while i<=time do
        set i=i+1;
        if mod (i,2)<>0 then iterate a;
        end if;
        insert into admin(username,password) values (concat('jon',i),'123');
    end while a;
end$

三种循环的区别
while 先判断后执行
repeat 先执行后判断
loop 没有条件的死循环

习题:已知表stringcontent,字段 id自增长 content varchar(20)向该表插入指定个数的随机字符串

CREATE TABLE stringcontent(
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(20)
);

DELIMITER $ 
CREATE PROCEDURE randstr(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
    DECLARE startindex INT DEFAULT 1;
    DECLARE len INT DEFAULT 1;
    WHILE i<=num DO
        SET len=FLOOR(RAND()*(20-startindex+1)+1);
        SET startindex=FLOOR(RAND()*26+1);
        INSERT INTO stringcontent(content) VALUES(SUBSTR(str,startindex,len));
        SET i=i+1;
    END WHILE;
END $

13条回帖

回帖
加载中...
话题 回帖

相关热帖

资源分享近期热帖

近期精华帖

热门推荐