SQL 基础

空空空肘子
133 阅读6分钟

Based on MySQL

1. MYSQL概述

1.1 数据库相关概念

数据库相关概念

1.2 数据模型

关系型数据库 RDBMS： 建立在关系模型基础上，由多张相互连接的二维表组成的数据库

数据模型

2. SQL

2.1 SQL通用语法

SQL通用语法

2.2 SQL分类

SQL分类

2.3 DDL 数据定义语言

Data Definition Language

2.3.1 DDL操作数据库

DDL操作数据库

create database test1;
show databases;
select database();
create database if not exists test1;
drop database if exists test1;
use test1;

2.3.2 DDL表操作

  1. 创建表

DDL创建表

create table test(
    id int,
    name varchar(50),  -- text
    age int,
    gender varcahr(50) -- 最后一行不加逗号
);
  1. 操作表

  • 查询 查询

    show tables;
desc test;
show creat table test; -- 展示sql创建

  • 添加 添加

    alter table test add nickname varchar(20);

  • 修改 修改 修改2

    alter table test modify age float;
alter table test change nickname username varchar(30);
alter table test rename to temp;

  • 删除 删除 删除2

    alter table temp drop username;
drop table if exists temp; -- 数据全部删除
truncate table temp; -- 数据全部删除
  1. 数据类型

  • 数据类型 数据类型

  • 字符串类型 字符串类型

  • 日期类型 日期类型

2.4 DML 数据操作语言

Data Manipulation Language

  1. 添加数据 INSERT

insert

insert into employee(id, name, gender) values (1, 'Joey', 'Male'); -- 可以选部分字段插入
insert into employee(id, name) values (2, 'Paul')
insert into employee(3, 'Mike', 'Male'); -- 顺序需要一一对应
  1. 修改数据 UPDATE

update

update employee set name = 'joey' where id = 1;
update employee set name = 'paul', gender = 'Male' where id = 2;
update employee set gender = 'unknown'; -- 不带where修改整个表
  1. 删除数据 DELETE

delete

delete from employee where gender = 'Male';
delete from employee; -- 没有where全部删除

2.5 DQL 数据查询语言

Data Query Language -- SELECT

image.png

2.5.1 基本查询

基本查询

select name, workno, age from employee;
select * from employee;
select name as n from employee;
select distinct age from employee;

2.5.2 条件查询 where

where

select * from emp where age = 28;
select * from emp where age < 28;
select * from emp where age >= 20;
select * from emp where idcard is null;
select * from emp where idcard is not null;
select * from emp where age != 28;
select * from emp where age >= 15 and age <= 20;
select * from emp where age between 15 and 20;
select * from emp where gender = 'male' and age < 20;
select * from emp where age = 15 or age = 20 or age = 30;
select * from emp where age in (15,20,30);
select * from emp where name like '__' -- 两个字符
select * from emp where idcard like '%X'; -- X前任意字符

2.5.3 聚合函数 count/max/min/avg/sum

聚合函数

select count(*) from emp;
select count(idcard) from emp;
select avg(age) from emp;
select max(age) from emp;
select min(age) from emp;
select sum(age) from emp where workaddress = 'NY';

2.5.4 分组查询 group by

group by

select gender,count(*) from emp group by gender;
select gender, avg(age) from emp group by gender;
select workadress, count(*) as address_count from emp where age < 45 group by workaddress having address_count > 3;

2.5.5 排序查询 order by

order by

select * from emp order by age asc;
select * from emp order by entrydate desc;
select * from emp order by age asc, entrydate desc; -- 多字段排序，先排age，再排entrydate

2.5.6 分页查询 limit

limit

select * from emp limit 0,10; -- 查询第一页，每页展示10条记录
select * from emp limit 10;

2.5.7 一些例子

select * from emp where gender = 'F' and age in (20,21,22,23);
select * from emp where gender = 'M' and age between 20 and 40 and name like '___'
select gender,count(*) from emp where age < 60 group by gender;
select name, age from emp where age <= 35 order by age, entrydate desc;
select * from emp where gender = 'M' and age between 20 and 40 order by age, entrydate desc limit 5;

2.5.8 执行顺序

执行顺序

2.6 DCL 数据控制语言

Data Control Language，用来管理数据库用户、控制数据库的访问权限

  1. 用户管理

用户管理

create user 'user1'@'localhost' identified by '123456';
create user 'user2'@'%' identified by '123456'; -- 任意主机都能访问
alter user 'user1'@'localhost' identified with mysql_native_password by '1234';
drop user 'user1'@'localhost';

2.权限控制

权限

权限控制

show grants for user1'@'localhost';
grant all on db.* to user1'@'localhost';
revoke all on db.* from user1'@'localhost';

3. 函数

一段可以直接被另一段程序调用的程序或代码

3.1 字符串函数

字符串函数

select concat('hello','world'); -- helloworld
select lower('Hello'); -- hello
select upper('Hello');  -- HELLO
select lpad('01', 5, '-') -- ---01
select rpad('01', 5, '-') -- 01---
select trim('  hello sql  ') -- hello sql
select substring('hello sql', 1, 5) -- hello

例：员工公号变5位数，补0

update emp set workno = lpad(workno, 5, '0');

3.2 数值函数

数值函数

select ceil(1.1) -- 2
select floor(2.9) -- 2
select mod(3,4) -- 3
select rand(); -- [0,1]
select round(2.345,2) -- 2.35
select round(2.344,2) -- 2.344

例：生成六位随机验证码

select lpad(round(rand()*1000000,0),6,'0'); -- 要补0补全为六位

3.3 日期函数

日期函数

select curdate();
select curtime();
select now();
select year(now());
select month(now());
select day(now());
select date_add(now(), interval 70 day);
select datediff('2021-12-01', '2021-11-01');

例：查询职工入职天数，并倒序排序

select name, datediff(curdate(), entrydate) as days from emp order by days desc;

3.4 流程函数

流程函数

select if(ture, 'ok','error'); -- ok
select ifnull('OK', 'Default'); -- OK
select ifnull('', 'Default'); -- ''
select ifnull(null, 'Default'); -- Default

例：工作地址上海/北京展示一线，其他二线

select name, (case workaddress when '北京' then '一线' when ‘上海’ then '一线' else '二线' end ) as 'address' from temp;

例：成绩统计

select 
    id, 
    name, 
    (case when math >= 85 then 'A' when math >= 60 then 'C' else 'D' end )  as 'Math',
    (case when egnlish >= 85 then 'A' when english >= 60 then 'C' else 'D' end )  as 'English',
from score;

4. 约束

4.1 约束概述

  1. 概念：约束是作用于表中字段上的规则，用于限制存存储在表中的数据
  2. 目的： 保证数据库中的数据的正确，有效性和完整性
  3. 分类： 约束 分类

4.2 约束演示

例：

约束演示

create table user(
    id int primary key auto_increment,
    name varchar(10) not null unique,
    age int check (age > 0 && age <= 120),
    status char(1) default '1',
    gender char(1)
)

4.3 外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

外建约束

外键语法

alter table emp add constraint fk_emp_dept_id foreign key (dept_id) reference 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) reference dept(id) on update cascade on delete cascade;

5. 多表查询

5.1 多表关系

  1. 一对多（多对一）

一对多

  1. 多对多

多对多

create table student_course(
    id int auto_increment primary key,
    studentid int not null,
    courseid int not null,
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
)
  1. 一对一

一对一

5.2 多表查询概述

  1. 从多张表中查询数据
  2. 笛卡尔积

笛卡尔积

select * from emp, dept where emp.dept_id = dept.id;
  1. 分类

查询分类

5.3 内连接

内连接

select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
select e.name, d.name from emp e, dept d where e.dept_id = d.id;

select e.name, d. name from emp e join dept d on e.dept_id = d.id;

5.4 外连接

外连接

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

5.5 自连接

自连接 要给表取别名，看作两张表。

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

select a.name, b. name from emp a left join emp b on a.managerid = b.id;

5.6 联合查询union

union

select * from emp where salary  < 5000
union 
select * from emp where age > 50;

5.7 子查询

  1. 概述 子查询

  2. 标量子查询

标量子查询

例：

-- 查询销售部的所有员工信息///1.查询销售部部门Id 2.根据部门ID查询员工信息
-- select id from dept where name = '销售部';
-- select * from emp where dept_id = 4;
select * from emp where dept_id = (select id from dept where name = '销售部')

-- 查询Joey入职之后的员工信息///1.查询joey入职信息 2.查询这个日期之后的入职信息
-- select entrydate from emp where name = 'joey';
-- select * from emp where entry date > '2001-06-08'
select * from emp where entry date > (select entrydate from emp where name = 'joey');
  1. 列子查询

列子查询

--- 查询销售部和市场部的所有员工信息/// 1. 查部门id 2.根据部门ID查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');

--- 查询比财务部所有人工资都高的员工 /// 1. 财务部人员工资 2. 比财务部都高的员工
select * from emp where salary > all(select salary from demp where dept_id = (select id from dept where name = '财务部'));

--- 查询比研发部其中任意一人工资高的员工信息 /// 1.研发部所有人工资 2. 比任意一个人高
select * from emp where salary > any(select salary from demp where dept_id = (select id from dept where name = '研发部'));
  1. 行子查询

行子查询

--- 查询与joey的薪资及直属领导相同的员工信息// 1.查joey工资以及manager 2. 查工资相同的人
--- select salary, managerid from emp where name = 'joey';
--- select * from emp where (salary, managerid) = (10000,1);
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = 'joey');
  1. 表子查询

表子查询

--- 查询与joey，mike 的职位和薪资相同的员工///1. 查人 2. 查相同
--- select job, salary from emp where name = 'joey' or name = 'mike'
select * from emp where (job, salary) in (select job, salary from emp where name = 'joey' or name = 'mike');

---查询入职日期是2006-1-1之后的员工信息，及其部门// 1.查员工信息 2. 查部门
--- 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;

5.8 多表查询案例

多表插叙案例

--- 1
select e.name, e.age, e.job, d.name from emp e, dept d where e.dept_id = d.id;

--- 2
select  e.name, e.age, e.job, d.name from emp e join dept d on e.dept_id = d.id where e.age < 30;

--- 3
select distinct d.id, d.name from emp e, dept d where e.dept.id = d.id;

--- 4 外连接
select e.*, d.name from emp e left join dept d on e.dept.id = d.id where e.age > 40;

--- 5 
select e.*, s.grade from emp e, salary s where e.salary >= s.losal and e.salary <= s.hisal;

--- 6
select e.*, s.grade from emp e, dept d, salgrade s where e.dept_id = d.id and (e.salary between s.losal and s.hisal) and d.name = '研发部';

--- 7 
select avg(e.salary) from emp e, dept d where e.dept_id = d.id and d.name = '研发部';

--- 8 
select * from emp where salary > (select salary from emp where name = '灭绝');

--- 9 
select * from emp where salary > (select avg(salary) from emp);

--- 10
select * from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);

--- 11
select e.id, d.name, (select count(*) from emp e where e.dept_id = d.id) from dept d;

--- 12 多对多

select s.name, s.no, c.name from student s, student_course sc, course c where s.id = sc.student.id and sc.courseid = c.id;

6. 事务

6.1 事务简介

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

事务

6.2 事务操作

事务操作1

事务操作2

--- 分步操作，抛出异常
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三'；
update account set money = money + 1000 where name = '李四';

-- 添加事务 1 
select @@autocommit;
set @@autocommit = 0; -- 设置为手动提交
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三'；
update account set money = money + 1000 where name = '李四';
commit; -- 没异常，提交事务
rollback; -- 有异常，回滚事务

--- 添加事务 2
start transaction;
select @@autocommit;
set @@autocommit = 1; -- 设置为自动提交
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三'；
update account set money = money + 1000 where name = '李四';
commit; -- 没异常，提交事务
rollback; -- 有异常，回滚事务

6.3 事务四大特征 ACID

acid

6.4 并发事务问题

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

6.5 事务隔离级别

解决并发事务问题

事务隔离级别

select @@transaction_isolation;
set session transaction isolation level read uncommitted;
avatar
文章
阅读
粉丝