MySQL学习

lwwwa.

已于 2023-11-16 11:25:19 修改

阅读量88

点赞数

文章标签： mysql 学习数据库

于 2023-09-14 11:39:55 首次发布

本文链接：https://blog.csdn.net/m0_73767545/article/details/132870719

版权

SQL分类

相关操作

数据类型

DDL修改和删除操作

DML(数据库表中增删改的操作)

DML添加数据

DML修改数据

DML删除数据

DML小结

DQL（数据查询）

基本查询

# drop table employee;

create table emp(
    id int comment '编号',
    workno varchar(10) comment '工号',
    name varchar(10) comment '姓名',
    gender char(1) comment '性别',
    age tinyint unsigned comment '年龄',
    idcard char(18) comment '身份证号',
    workaddress varchar(50) comment '工作地址',
    entrydate date comment '入职时间'
) comment '员工表';

insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
values (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01'),
       (2, '2', '张无忌', '女', 18 ,'123456789012345678', '上海', '2001-01-01'),
       (3, '3', '韦一笑', '女', 26, '123456789012345678', '天津', '2002-01-01'),
       (4, '4', '赵敏', '女', 27, '123456789012345678', '武汉', '2003-01-01'),
       (5, '5', '小昭', '女', 26, '123456789012345678', '河南', '2004-01-01'),
       (6, '6', '杨逍', '女', 15, '123456789012345678', '北京', '2005-01-01'),
       (7, '7', '范瑶', '女', 19, '123456789012345678', '北京', '2006-01-01'),
       (8, '8', '黛绮丝', '女', 17, '123456789012345678', '北京', '2007-01-01'),
       (9, '9', '范凉凉', '女', 22, '123456789012345678', '湖南', '2008-01-01'),
       (10, '10', '陈友谅', '女', 19, '123456789012345678', '湖北', '2009-01-01'),
       (11, '11', '张士诚', '女', 27, '123456789012345678', '青海', '2010-01-01'),
       (12, '12', '常遇春', '女', 20, '123456789012345678', '北京', '2011-01-01'),
       (13, '13', '张三丰', '女', 20, '123456789012345678', '北京', '2012-01-01'),
       (14, '14', '灭绝', '女', 21, '123456789012345678', '青岛', '2013-01-01'),
       (15, '15', '胡青牛', '女', 28, '123456789012345678', '北京', '2014-01-01'),
       (16, '16', '周芷若', '女', 27, null, '山东', '2015-01-01');


-- 查询基本操作
-- 1.查询指定字段 name, workno, age 返回
select name, workno, age from emp;

-- 2.查询所有字段 返回
select id, workno, name, gender, age, idcard, workaddress, entrydate from emp;
select * from emp;

-- 3.查询所有员工的工作地址， 起别名
select workaddress '工作地址' from emp;

-- 4.查询公司员工的上班地址（不重复）
select distinct workaddress '工作地址' from emp;

条件查询

聚合函数

null值不参与聚合函数运算

分组查询

排序查询

分页查询

综合案例：

DQL语句执行顺序

DCL

函数

用select关键字加函数名调用

约束

案例：

外键约束（保证表与表之间的关联性）

注：含有外键的表叫子表，外键所关联的表叫父表

-- 约束外键
create table dept(
    id int auto_increment comment 'ID' primary key,
    name varchar(50) not null comment '部门名称'
) comment '部门表';
insert into dept (id, name) values (1, '研发部'), (2, '市场部'), (3, '财务部'), (4, '销售部'), (5, '总经办');


create table emp(
    id int auto_increment comment 'ID' primary key ,
    name varchar(50) not null comment '姓名',
    age int comment '年龄',
    job varchar(20) comment '职位',
    salary int comment '薪资',
    entrydate date comment '入职时间',
    managerid int comment '直属领导ID',
    dept_id int comment '部门ID'
) comment '员工表';


insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
            (1, '金庸', 66, '总裁', 20000, '2000-01-01', null, 5), (2, '张无忌', 20, '项目经理', 12500, '2005-12-05', 1, 1),
            (3, '杨逍', 33, '开发', 8400, '2000-11-03', 2, 1), (4, '韦一笑', 48, '开发', 11000, '2002-02-05', 2, 1),
            (5, '常遇春', 43, '开发', 10500, '2004-09-07', 3, 1), (6, '小昭', 19, '程序员鼓励师', 6600, '2004-10-12', 2, 1);


-- 添加外键
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;

多表查询

-- 多对多
create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';
insert into student values (null, '黛绮丝', '2000100101'), (null, '谢逊', '2000100102'), (null, '殷天正', '2000100103'), (null, '韦一笑', '2000100104');

create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';
insert into course values (null, 'Java'), (null, 'PHP'), (null, 'MySQL'), (null, 'Hadoop');

create table student_course(
    id int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid int not null comment '课程ID',
    constraint fk_courerid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
) comment '学生课程中间表';

insert into student_course values (null, 1, 1), (null, 1, 2), (null, 1, 3), (null, 2, 2), (null, 2, 3), (null, 3, 4);

-- 一对一
create table tb_user(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '1: 男 , 2: 女',
    phone char(11) comment '手机号'
) comment '用户基本信息表';

create table tb_user_edu(
    id int auto_increment primary key comment '主键ID',
    degree varchar(20) comment '学历',
    major varchar(50) comment '专业',
    primaryschool varchar(50) comment '小学',
    middleschool varchar(50) comment '中学',
    university varchar(50) comment '大学',
    userid int unique comment '用户ID',
    constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';


insert into tb_user(id, name, age, gender, phone) values
        (null, '黄渤', 45, '1', '18800001111'),
        (null, '冰冰', 35, '2', '18800002222'),
        (null, '码云', 55, '1', '18800008888'),
        (null, '李彦宏', 50, '1', '18800009999');

insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) values
        (null, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1),
        (null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2),
        (null, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3),
        (null, '本科', '应用数学', '阳泉第一小学', '阳泉区第一中学', '清华大学', 4);

多表查询

内连接

对表起别名，用空格隔开

外连接

自连接

联合查询

子查询

标量子查询

列子查询（一列多行）

行子查询（一行多列）

表子查询

案例：

注：连接查询跟多表查询不一样，连接查询不需要外键，多表查询需要外键

-- 多表查询案例
create table salgrade(
    grade int,
    losal int,
    hisal int
) comment '薪资等级表' ;

insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);

-- 1、查询员工的姓名，年龄，职位，部门信息（隐式内连接）
select emp.name, emp.age, emp.job, dept.name from emp, dept where emp.dept_id = dept.id ;

-- 2、查询年龄小于30岁的员工的姓名，年龄，职位，部门信息（显式内连接）
select emp.name, emp.age, emp.job, dept.name from emp inner join dept on emp.dept_id = dept.id where emp.age < 30 ;

-- 3、查询拥有员工的部门ID，部门名称(内连接)
select distinct d.id, d.name from emp e, dept d where e.dept_id = d.id;

-- 4、查询所有年龄大于40的员工，及其归属部门，如果员工没有分配部门，也要展示（外连接）
-- 表：emp, dept
-- 连接条件：emp.dept_id = dept.id
select e.*, d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;

-- 5、查询所有员工的工资等级
-- 表：emp, salgrade
-- 连接条件：emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal 也可以用between and 表示

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

-- 6、查询 “研发部” 所有员工信息及工资等级
-- 表：emp, salgrade, dept
-- 连接条件：emp.salary between salgrade.losal and salgrade.hisal ，emp.dept_id = dept.id
-- 查询条件：dept.name = '研发部'

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、查询 “研发部” 员工的平均工资
-- 表：emp, dept
-- 连接条件：emp.dept_id = dept.id

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

-- 子查询
-- 8、查询工资比 “灭绝” 高的员工信息(标量子查询)
-- a、查询 “灭绝” 的工资
select salary from emp where emp.name = '灭绝' ;

-- b、查询比她工资高的员工信息
select * from emp where salary > ( select salary from emp where emp.name = '灭绝' )

-- 9、查询比平均薪资高的员工信息
-- a、查询员工的平均薪资
select avg(salary) from emp ;

-- b、查询比平均薪资高的员工信息
select * from emp where salary > ( select avg(salary) from emp );

-- 10、查询低于本部门平均工资的员工信息（自连接）
-- a、查询指定部门的平均工资
select avg(e1.salary) from emp e1 where e1.dept_id = 1 ;
select avg(e1.salary) from emp e1 where e1.dept_id = 2 ;

-- b、查询低于本部门平均工资的员工信息
select * from emp e2 where e2.salary < ( select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id ) ;

select *, (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id) '平均'
from emp e2
where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id);

-- 11、查询所有的部门信息，并统计部门的员工人数
select d.id, d.name, ( select count(*) from emp e where e.dept_id = d.id ) '人数' from dept d;

-- 12、查询所有学生的选课情况，展示出学生名称，学号，课程名称
-- 表：student, course, student_course
-- 连接条件：student.id = student_course.studentid, course.id = student_course.courseid

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

事务

0表示手动提交，1表示自动提交

-- 事务操作
-- 数据准备
create table account (
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    money int comment '余额'
) comment '账户表' ;
insert into account(id, name, money) values (null, '张三', 2000), (null, '李四', 2000) ;


-- 恢复数据
update account set money = 2000 where name = '张三' or name = '李四' ;

-- 设置事务
select @@autocommit ;
set @@autocommit = 0 ; -- 设置为手动提交
-- 注意：方式二需要将@@autocommit设置为1



-- 转账操作（张三给李四转账1000）
-- 1、查询张三账户余额
select * from account where name = '张三' ;
-- 2、将张三账户余额-1000
update account set money = money - 1000 where name = '张三' ;

程序执行报错...

-- 3、将李四账户余额+1000
update account set money = money + 1000 where name = '李四' ;


-- 提交事务
commit ;

-- 回滚事务
rollback ;


-- 方式二
-- 转账操作（张三给李四转账1000）
-- 开启事务
start transaction ;
-- 1、查询张三账户余额
select * from account where name = '张三' ;
-- 2、将张三账户余额-1000
update account set money = money - 1000 where name = '张三' ;

程序执行报错...

-- 3、将李四账户余额+1000
update account set money = money + 1000 where name = '李四' ;

-- 提交事务
commit ;
-- 回滚事务
rollback ;

事务的四大特性

脏读：A事务读取到B事务尚未提交的数据

不可重复读：同样的数据在一个事务中查询的结果不一致

幻读：A，B事务在同一数据库，B事务在表中id=3处插入一个字段后提交事务，在A事务中查询不到刚插入的数据，于是在A事务id=3处插入数据，结果报错显示id=3处有数据，导致id=3处有数据但是A事务查询不到

事务隔离级别（×表示解决，√表示存在相关问题）

从上到下，事务的隔离级别越来越高。事物的隔离级别越高，数据越安全，但是性能越低

serializable表示串行化

MySQL进阶

存储引擎

# 查询表的建表语句
# 默认引擎：ENGINE=InnoDB
show create table account ;

# 查询当前数据库所支持的存储引擎
show engines ;

# 创建表 my_myisam， 并指定MyISAM存储引擎
create table my_myisam(
    id int,
    name varchar(10)
) engine = MyISAM ;

# 创建表my_memory， 并指定Memory引擎
create table my_memory(
    id int,
    name varchar(10)
) engine = Memory ;

InnoDB