mysql基础语句学习｜ 青训营笔记

死歪哥灰常差
194 阅读10分钟

这是我参与「第五届青训营 」笔记创作活动的第19天

基础

概述

数据库(DB)存储数据的仓库，数据时有组织的进行存储

数据库管理系统(DBMS)，操控和管理数据库的大型软件

SQL ->操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准

mysql的启动和关闭

1.win+r 然后输入services.mcs进入界面找到mysql80程序关闭

2.利用命令行

启动 net start mysql80

停止 net stop mysql80

注：mysql是开机自动启动的

mysql的客户端连接

方法一 用mysql提供的客户端

方法二 Windows的命令行 用系统自带的命令行工具执行指令

mysql([-h 1247.0.0.1][-p 3306]这一段可以不要，但是要配置环境变量) -u root -p

mysql的数据模型

关系型数据库(RDBMS)

mysql数据库就是一个关系型数据库，建立在关系模型上，由多张相互连接的二维表组成的数据库

优点：表存储，格式统一便于维护；使用SQL语言操作

SQL

分类

DDL（数据定义语言

定义数据库对象（数据库，表，字段

SHOW DATABASES; 查询所有数据库
SELECT DATABASES();查询当前数据库
CTEATE DATABASE[名字][字符集][排序规则];方括号中的可以省略，存在默认值
DROP DATABASE[]数据库名称
USE 数据库名字 切换到某个数据库中

SHOW TABLES;查询当前数据库中的所有表
DESC 表名; 查询表结构
SHOW CREATE TABEL表名

CREATE TABLE 表名（
字段1 字段1类型[注释];
字段2 字段2类型[注释];
字段3 字段3类型[注释];
）[表注释];

DDL-表修改-操作
ALTER TABLE表名 ADD 字段名 类型(长度)[comment 注释][约束];新增字段
ALTER TABLE表名 MODIFY 字段名 新数据类型（长度
修改字段数据类型
ALTER TABLE表明 CHANGE 旧字段名 新字段名 类型 [注释]
修改字段名称与数据类型
ALTER TABLE表名 DROP 字段
删除字段

ALTER TABLE 表名 RENAME TO 新表名
修改表名
DROP TABLE 表名
删除指定表
TRUNCATE TABLE表名；
删除指定表，并重新创建该表

DML（数据操作语言

对表中数据修改

DQL（数据查询语言

查询表中的记录

DCL（数据控制语言

创建数据库用户，控制数据库访问权限

图像化界面

Datagrip

函数

字符函数

-- 函数演示
-- concat 字符串拼接
select concat('hello' , 'mysql');

-- lower 全部字母小写
select lower('HellO');

-- upper 全部字母大写
select upper('hellO');

-- lpad 填充字符串到指定长度，l填充在前面，r填充在后面
select lpad('01',5,'-');

-- rpad
select lpad('01',5,'-');

-- trim去除空格--->仅仅是前面和后面的空格，中间不去
select trim(' hello  MySQL  ');

-- substring
select substring('Hello MySQL',1,5);

-- 把员工工号全部变为5位
update emp set workno = lpad(workno,5,0);

数值函数

-- ceil  向上取整
select ceil(1.1);
-- floor  向下取整

-- mod 求模运算
select mod(3,5); -- 3/5余几

-- rand随机数
select rand();

-- round    四舍五入，保留自定义位小数
select round(2.345,2);

-- 通过数据库的函数，生成一个六位的随机生成数
select lpad(round(rand()*1000000,0),6,'0');

日期函数

-- 日期函数
-- curdate()
select curdate();

-- curtime()
select curtime();

-- now()
select now();

-- year,month,day;
select year(now());
select month(now());
select day(now());

-- date_add()  在给定日期的上继续加上指定的天数和日期
select date_add(now(),interval 70 day );

-- datediff  日期天数差异，前面日期减去后面日期
select datediff('2021-12-1','2021-10-01');

-- 查询所有员工入职天数，并根据入职天数倒序排列
select name,datediff(curdate(),entrydate) df from emp order by df desc;

流程函数

-- 流程函数
-- if(value,t,f) -- value为true返回t否则返回f
select if(true ,'ok','Error');
-- ifnull(value1,value2) --value不为空，返回value1否则返回value2
select ifnull('ok','default');
select ifnull(null,'default');
-- case when [val1] then[res1]....else[default] end; value为true返回res1，...否则返回default
-- 需求：查询emp表的员工姓名和工作地址，如果地址为北京上海，---->一线城市，其他为二线城市
select
       name,(
case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;
-- case [expr] when[val1] then [res1] else [default] end; expr等于val1，返回res1，...否则返回default

create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
) comment '学员成绩表';
insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95 ), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);

-- 查询语句
select * from score;

select
    id,
    name,
    (case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) '数学',
    (case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end)'英语',
    (case when chinese >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end)'语文'
    from score;

约束

概念

就是作用于表中字段上的规则，用于限制存储在表中的数据

目的

保证数据库中的数据正确，有效性和完整性

外键约束

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);

-- 删除外键
alter table emp drop foreign key fk_emp_dept_id;

-- 外键约束
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade;

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null ;

多表查询

多表关系

根据业务需求，分析性并设计表结构，表结构基本为以下三种：

一对多

例如员工和部门，一个员工一个部门，一个部门多个员工。

实现：多的一方建立外键，指向一的一方的主键

多堆多

例如：学生与课程，学生学多门课，一门课也可以拱多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一

例子：用户与用户详情的拆分

实际上就是单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率

实现：任意一方加入外键，关联另一方的主键，并且设置外键为唯一的(UNIQUE)

多表查询概述

一个非常普通的查询方式，直接查两个表

select * from emp,dept;

得到的结果引入新概念：笛卡尔积

笛卡尔积

数学中，两个集合A集合和B集所有的组合情况，因此，多表查询时，需要消除无效的笛卡尔积

消除笛卡尔积
select * from emp,dept where emp.dept_id = dept.id; -- 加入限定条件即可

连接查询

分为：内连接，外连接，自连接；

内连接

两张表交集部分

隐式内连接

-- 查询每一个员工的姓名，及关联部门的名称
-- 表结构 emp,dept;
-- 连接条件：emp.dept_id = dept.id
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;

显式内连接

-- 显示内连接的实现   ----- inner join ..... on...
-- 表结构 emp,dept;
-- 连接条件：emp.dept_id = dept.id
select e.name,d.name from emp e inner join dept d on e.dept_id = d.id;

外连接

和内连接的区别就在于，内连接包括的数据只有交集，但是外连接包括了除交集意外的左/右集合

左外连接

包括表1（左表）所有的数据，且包含表1和表2的交集数据

-- 查询 emp 所有数据，且对应部门信息
select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id;

left outer join ... on ...

右外连接

包括表2（右表）所有的数据，且包含表1和表2的交集数据

select d.*,e.* from emp e right outer join dept d on e.dept_id = d.id;

自连接

案例：emp表查询员工及其领导

select a.name,b.name from emp a,emp b where a.managerid = b.id;

联合查询

-union, union all

就是把多次查询的结果合并起来，形成一个新的查询结果集

-- 薪资地狱5000 的员工，年龄大于50的员工,直接拼表
-- union 会去重 union all就是直接合并
select * from emp where salary < 5000
union
select * from emp where age > 50;

子查询

概念：SQL语句中嵌套SELECT语句，又称嵌套查询，又称子查询

标量子查询

-- 标量子查询
-- 返回的结果是单个值，常用操作符：= <> > < ....
-- 1.查询销售部所有的员工信息
select id from dept where name = '销售部';

select *from emp where dept_id = (select id from dept where name = '销售部');

-- 2.查询在房东白入职之后的员工信息
select entrydate from emp where name = '方东白';
select * from emp where entrydate > '2009-02-12';

select * from emp where entrydate > (select entrydate from emp where name = '方东白');

列子查询

-- 列子查询
-- 常用操作符 IN, NOT IN, ANY, SOME, ALL
-- 查询销售部和市场部的所有员工信息
select id from dept where name = '销售部' or name = '市场部';
select * from emp where dept_id in(select id from dept where name = '销售部' or name = '市场部');

-- 财讯比财务部所有人工资都高的员工信息
select id from dept where name = '财务部';
select salary from emp where dept_id =  (select id from dept where name = '财务部');
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));

update emp set salary = 48000 where name = '周芷若';

-- 比研发部任意一人工资高的员工信息
select * from emp where salary > some (select salary from emp where dept_id = (select id from dept where dept.name = '研发部'));

行子查询

-- 行子查询
-- 查询与"张无忌"的薪资及直属领导相同的员工信息
-- a.查询张无忌的薪资与直属领导
select salary,managerid from emp where name = "张无忌";
-- b.查询与"张无忌的薪资与直属领导"相同的员工信息
select *from emp where (salary,managerid) = (select salary,managerid from emp where name = "张无忌");

表子查询

-- 表子查询
-- 查询与"鹿杖客"，"宋远桥"的职位和薪资相同的员工信息
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
select * from emp where (job,salary) in (select job,salary from emp where name = '鹿杖客' or name = '宋远桥');

-- 查询入职日期是'2006-01-01'之后的员工信息，即部门信息
select * from emp where entrydate >'2006-01-01';
select e.*,d.* from (select * from emp where entrydate >'2006-01-01') e left join dept d on e.dept_id = d.id;

子查询返回的结果是一张表

事物

事物是一组集合，是一个不可分割的工作单位，事物会把所有的操作作为一个整体一起向系统提交或撤销操作请求，这么操作要么同时成功，要么同时失败

例如：转账，张三转1000元给李四，是同一个事物，要么同时成功，要么同时失败

事物操作

  • 查看和设置事物提交方式 
    • select @@autocommit; -- 查看事物的提交方式是否为手动
set @@autocommit=0;
  • 提交事物 
    • commit
  • 回滚事物
    • rollback
  • 开始事务 
    • start transaction;
begin;

四大特性（ACID）

  • 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
  • 一致性：事务完成时，必须使所有的数据都保持一致状态
  • 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境运行
  • 持久性：事务一旦提交或者回滚，对数据库中数据的改变就是永久的

并发事务所引发的问题

  • 脏读
    • 一个事务读取到另外一个事务还没有提交的数据
  • 不可重复读
    • 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
  • 幻读
    • 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现幻影

事务隔离级别

隔离级别与安全性成正比，与效率成反比

查看隔离级别 select @@transaction_isolation

设置事务隔离级别* set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read| serializable}

avatar
文章
阅读
粉丝