2020年MySQL课堂笔记

MySOL

SQL语法

本节用到的架构:

在这里插入图片描述

特点:

  1. SQL语句可以单行或多行书写,以;结尾
  2. 可以使用空格和缩进增强语句的可读性
  3. MySQL的SQL语句不区分大小,关键字建议大写

3种注释

单行:

-- 注释内容
#注释内容 (MySQL特有)

多行:

/*注释内容*/

SQL按功能分类

  1. DDL:操作数据库、表
  2. DML:增删改表中的数据
  3. DQL:查询表中的数据
  4. DCL:管理用户,授予权限

DDL

作用:操作数据库、表

操作数据库:CRUD
  1. C(create):创建数据库
create database 数据库名称
改进后:
create database if not exists 数据库名称;(防止重复)
create database 数据库名称 character set 字符集名称(更改默认字符集:utf-8)
综合:
create database if not exists 数据库名称 character set 字符集名称

实例代码

CREATE DATABASE IF NOT EXISTS Student;-- 创建Student数据库
  1. R(Retrieve):查询数据库
查询所有数据库名称 
show databases;
查询某个数据库
show create database 数据库名称;

实例代码

SHOW CREATE DATABASE Student; -- 查询Student数据库
  1. U(update):修改数据库
修改数据库的字符集
alter database 数据库名称 character set 字符集名称;

实例代码

ALTER DATABASE Student CHARACTER SET utf-8; -- 将字符集改为utf-8
  1. D(delete):删除数据库
drop database 数据库名称;
改进后:
drop database if exists 数据库名称;(判断数据库存在,存在再删除)

实例代码

drop database if exists Student; -- 删除Student数据库
  1. 使用:数据库
查询当前正在使用的数据库名称
select database();
使用数据库
use 数据库名称;

实例代码

USE  Student; -- 使用Student数据库
操作表:CRUD

数据库的数据类型

数据类型含义例子
int整数类型age int;
double小数类型score double(5,2); 一共5位,小数点后保留2位
date日期,只包含年月日,yyyy-MM-ddbirthday date
datetime日期,包含年月日时分秒,yyyy-MM-dd HH:mm:sstime datetime
timestamp时间错类型,如果是null,默认复制系统当前时间time timestamp;
varchar字符串name varchar(10);
  1. C(create):创建表
create table 表名(
  列名1 数据类型1,
  列名2 数据类型2,
  ...
  列名n 数据类型n);  //注意:最后一行没有逗号
复制表
create table 表名 like 被复制的表名称

实例代码

CREATE TABLE class_01(  -- 创建班级表
NAME VARCHAR(10),       -- 姓名
age INT,                -- 年龄
class VARCHAR(20),      -- 班级
English DOUBLE(3,1),    -- 英语
Chinese DOUBLE(3,1),    -- 语文
Math DOUBLE(3,1),       -- 数学
recordtime TIMESTAMP)         -- 录档日期
  1. R(Retrieve):查询表
查询某个数据库中所有的表名称
show tables;
查询表结构
desc 表名;

实例代码

DESC class_01; -- 查询表结构
  1. U(update):修改表
修改表名
alter table 表名 rename to 新表名;
修改表的字符集
alter table 表名 character set 新字符集;
添加一列
alter table 表名 add 列名 数据类型;
修改列名称、数据类型
alter table 表名 change 列名 新列名 (新)数据类型;
alter table 表名 modify 列名 新数据类型;
删除列
alter table 表名 drop 列名;

实例代码

ALTER TABLE class_01 RENAME TO class_1; -- 将表名class_01改为class_1
ALTER TABLE class_1 ADD temp INT; -- 添加一列temp列
ALTER TABLE class_1 CHANGE temp newtemp VARCHAR(6); -- 将int类型temp列改为varchar(6)类型 newtemp列
ALTER TABLE class_1 MODIFY  newtemp INT; -- 将varchar(6)类型newtemp列改为int类型
ALTER TABLE class_1 DROP newtemp; -- 删除newtemp列
  1. D(delete):删除表
drop table 表名;
改进:
drop table if exists 表名;

实例代码

drop table if exits class_1;  -- 删除class_1表

DML

作用:增删改表中的数据

  1. 添加数据:
insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

实例代码

INSERT INTO class_1 VALUES("Tom",18,"一班",80,91.5,78,NULL); -- 添加一条学生记录

注意:

  • 列名与值要一一对应

  • 如果表名后不定义列名,则默认所有列添加值

    insert into 表名 values (值1,值2,...值n);
    
  • 除了数字类型,其他类型的值需要引号(单双都可以)引起来

  1. 删除数据:
如果没有条件,删除表中所有记录
delete from 表名[where 条件];
删除表,再创建一个一模一样的空表
truncate table 表名;
  1. 修改数据
update 表名 set 列名1 = 值1,列名2 = 值2,...[where 条件]

实例代码

UPDATE class_1 SET English = 78 WHERE age = 18; -- 将age = 18的学生English改为78

DQL

作用:查询表中的记录

查询表中所有记录
select * from 表名

语法

select 字段列表
from 表名列表
where 条件列表
group by 分组字段
having 分组之后的条件
order by 排序
limit 分页限定
基础查询
去除重复的结果集
select distinct 列名 from 表名;
计算几列之和
select 列名1,列名2,列名1+列名2 from 表名;
改进:如果有null参与运算,计算结果都为null
select 列名1,列名2 列名1+ifnull(列名2,0)from 表名;
改进:起别名as(as 可替换成 空格)
select 列名1,列名2 列名1+ifnull(列名2,0) as 别名 from 表名;
select 列名1 新列名1,列名2 新列名2 from 表名;

实例代码

SELECT English,Math,Chinese,(Math+Chinese+English) 总分 FROM class_1; -- 计算总分
条件查询

语法:where 字句后跟条件

条件运算符

  • 比较运算符
    • 等于=
    • 大于>
    • 大于或等于>=
    • 小于<
    • 小于或等于<=
    • 不等于!=或<>
  • 逻辑运算符
    • and或&&
    • or或||
    • not或!
  • 模糊查询 like
    • 占位符:_(下划线)表示单个任意字符
    • 占位符:% 表示任意多个字符
  • 范围查询
    • in表示在一个非连续的范围内 eg:in(1,4,8)
    • between…and…表示在一个连续的范围内 eg:between 1 and 8
  • 空判断
    • 判空 is null 或 is not null

实例代码

-- 查询一班name第二个字是a的记录
SELECT * FROM class_1 WHERE class="一班" AND NAME LIKE  "_a%"; 
-- 查询English范围在80到100的记录
SELECT * FROM class_1 WHERE English BETWEEN 80 AND 100;
排序查询

语法:order by 字句

order by 排序字段1 排序方式1,排序字段2 排序方式2

排序方式:

asc:升序(默认)

desc:降序

注意:如果有多个排序条件,则当前一个的条件值一样时,才会判断第二条件

聚合函数:将一系列数据作为整体,进行纵向的计算

  • count:计算个数
  • max:计算最大值
  • min:计算最小值
  • sum:计算和
  • avg:计算平均值
计算有多少行
select count(列名) from 表名

注意:聚合函数的计算,会排除null值(null值不参与计算)

实例代码

-- 将学生根据name升序
SELECT * FROM class_1 ORDER BY NAME ASC;
-- 计算学生人数
SELECT COUNT(NAME) FROM class_1;
-- 计算Math最大值
SELECT MAX(Math) FROM class_1;
-- 计算Math最小值
SELECT MIN(Math) FROM class_1;
分组查询

语法:group by 分组字段

注意:分组之后查询的字段是分组字段,聚合函数

where与having区别:

  • where在分组之前限定,如果不满足条件,不参与分组

  • having在分组之后限定,如果不满足条件,不会被查询出来

  • where后不可以加聚合函数

  • having可以进行聚合函数的判断

实例代码

-- 按照年龄分组查询人数
SELECT age,COUNT(NAME) FROM class_1 GROUP BY age;
分页查询

语法:limit 开始索引,每页查询的条数

公式:开始索引 = (当前页码 - 1)*每页显示的页数

注意:limit是MySQL特有

实例代码

-- 按照3个记录为一页
SELECT * FROM class_1 LIMIT 0,3;
SELECT * FROM class_1 LIMIT 3,3;

约束

作用:对表中的数据进行限定,保证数据的正确性、有效性和完整性。

分类:

  1. 主键约束:primary key
  2. 非空约束:not null
  3. 唯一约束:unique
  4. 外键约束:foreign key
非空约束

语法:not null

创建表时添加约束
create table 表名(...,列名 数据类型 not null);
删除非空约束
alter table 表名 modify 列名 数据类型;
创建表后,添加非空约束
alter table 表名 modify 列名 数据类型;
唯一约束

语法:unique

创建表时添加约束
create table 表名(...,列名 数据类型 unique);
删除唯一约束
alter table 表名 drop index 列名;
创建表后,添加唯一约束
alter table 表名 modify 列名 数据类型 unique;

注意:唯一约束可以有null值,但只能有一条null值记录

主键约束

语法:primary key

注意:

  1. 含义:非空且唯一
  2. 一张表只能有一个字段为主键
  3. 主键就是表中记录的唯一标识
创建表时,添加主键
create table 表名 (...,列名 数据类型 primary key);
删除主键
alter table 表名 drop primary key;
创建表后,添加主键
alter table 表名 modify 列名 数据类型 primary key;

自动增长

概念:如果某一列是数值类型的,使用auto-increment可以来完成值自动增长

创建表时,添加主键并实现自动增长
create table 表名(列名 数值类型 primary key auto-increment);
删除自动增长
alter table 表名 modify 列名 数据类型;
添加自动增长
alter table 表名 modify 列名 数值类型 auto-increment;
外键约束

语法:foreign key

作用:让表与表之间产生关系,保证数据的正确性。

创建表时,添加外键
create table 从表名(...,constraint 外键名称 foreign key(外键列名称) references 主表名称(主表列名称));
删除外键
alter table 从表名 drop foreign key 外键名称;
创建表后,添加外键
alter table 从表名 add constraint 外键名称 foreign key(外键字段名称)references
 主表名称(主表列名称);

注意:外键名称可以随便起,只是标记不会显式出来,只要不重复就行。

级联操作

分类:

  1. 级联更新: on update cascade
  2. 级联删除:on delete cascade
添加级联操作
alter table 从表名 add constraint 外键名称 foreign key(外键字段名称) references
主表名称(主表列名称)on update cascade on delete cascade;

DCL

作用:管理用户,授予权限

管理用户

添加用户
create user '用户名'@'主机名' identified by '密码';
删除用户
drop user '用户名'@'主机名';
修改用户密码
update user set password = password('新密码')where user='用户名';
或者
set password for '用户名'@'主机名'=password("新密码");
查询用户
1. 切换到mysql数据库
use mysql;
2.查询user表
select * from user;

mysql中忘记了root用户的密码怎么办?

  1. 管理员身份打开cmd – > net stop mysql 停止mysql服务
  2. 使用无验证方式启动mysql:mysqld --skip-grant-tables
  3. 打开新的cmd窗口,直接输入mysql命令,enter,就可以登入成功
  4. use mysql;
  5. update user set password = password(‘新密码’) where user = ‘root’;
  6. 关闭两个cmd窗口
  7. 打开任务管理器,手动结束mysql.exe进程
  8. 启动mysql服务,使用新密码登入

通配符: 主机名写% : 表示可以在任意主机使用用户登录数据库

权限管理

查询权限
show grants for '用户名'@'主机名';
授予权限
grant 权限列表 on 数据库.表名 to '用户名'@'主机名';
撤销权限
revoke 权限列表 on 数据库.表名 from '用户名'@'主机名';

数据库的设计

  1. 一对一

实现方式:在任意一方添加外键指向另一方的主键,且外键唯一unique

  1. 一对多

实现方式:在的一方建立外键,指向的一方

  1. 多对多

实现方式:借助第三张中间表

中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键

数据库的三大范式

第一范式:每个列都不可以再拆分

第二范式:在第一范式基础上,非主键列完全依赖主键

第三范式:在第二范式基础上,非主键列直接依赖主键,而不依赖于其他键

**注意:**在设计数据库结构时,尽量遵循三大范式,除非有性能顾虑。

数据库的备份和还原

cmd命令行语法

备份:mysqldump -u用户名 -p密码 数据库名称 > 保存的路径

还原:

  1. 登录数据库
  2. 创建数据库
  3. 使用数据库
  4. 执行文件: source 文件路径

多表查询

语法

select 列名列表
from 表名列表
where 条件

分类:

  1. 内连接查询
  2. 外连接查询
  3. 子查询

笛卡尔积:有两个集合A,B,取这两个集合的所有组成情况。

要完成多表查询,需要消除无用的数据

内连接查询

  1. 隐式内连接:使用where条件消除无用数据

  2. 显式内连接:

select 字段列表 from 表名1 [inner] join 表名2 on 条件;

内连接查询:

  1. 从哪些表中查询数据
  2. 条件是什么
  3. 查询那些字段

外连接查询

  1. 左外连接
查询左边表中所有数据以及其交集部分
select 字段列表 from 表1 left[outer] join 表2 on 条件;
  1. 右外连接
查询右边表所有数据以及其交集部分
select 字段列表 from 表1 right[outer] join 表2 on 条件;

子查询

概念:查询中嵌套查询,称嵌套查询为子查询

子查询的3种不同情况:

  1. 子查询的结果单行单列

子查询作为条件,使用运算符去判断

  1. 子查询的结果是多行单列

子查询可以作为条件,使用运算符in判断

  1. 子查询的结果是多行多列

子查询作为一张虚拟表

事务

事务的基本介绍

概念:如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败。

操作的关键字:

  1. 开启事务:start transaction
  2. 回滚:rollback
  3. 提交:commit

MySQL数据库中,事务默认自动提交

一条DML(增删改)语句会自动提交一次事务

事务提交的两种方式:

  1. 自动提交
  2. 手动提交

查看事务的默认提交方式

select @@autocommit;

修改事务的提交方式

-- 参数  1:自动提交   0:手动提交
set @@autocommit = 参数;

事务的四大特征

  1. 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
  2. 持久性:当事务提交或回滚后,数据库会持久化的保存数据
  3. 隔离性:多个事务之间相互独立
  4. 一致性:事务操作前后,数据总量不变

事务的隔离级别

概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别既可以解决这些问题。

存在问题:

  1. 脏读:一个事务,读取到另一个事务没有提交的数据
  2. 不可重复读(虚读):同一事务,两次读到的数据不一样
  3. 幻读:一个事务操作数据表中的所有数据,另一事务添加了一条数据,则第一个事务查询不到添加的数据

隔离级别:

  1. read uncommitted : 读未提交

产生的问题:脏读、不可重复读、幻读

  1. read committed:读已提交(Oracle默认)

产生的问题:不可重复读、幻读

  1. read repeaded:可重复度(MySQL默认)

产生的问题:幻读

  1. serializable:串行化

可以解决所有问题

注意:隔离级别从小到大安全性越来越高,但是效率越来越低

查询数据库隔离级别
select @@ts_isolation;
设置数据库隔离级别
set global transaction isolation level 级别字符串;
  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值