数据库操作

AMBLE RUM

已于 2024-04-13 22:06:07 修改

阅读量875

点赞数 18

文章标签：数据库 mysql

于 2024-04-13 22:03:56 首次发布

本文链接：https://blog.csdn.net/qq_55380181/article/details/137726641

版权

DDL操作

操作数据库，表结构，表中字段

创建数据库

指定字符集

create database if not exists itcast default charset utf8;

删除数据库

drop database if exists test;

查询当前数据库

select database()

查询指定表的建表语句

show create table '表名'

创建表操作

create table tb_user
(
    id int comment '编号',
    name varchar(50) comment '姓名',
    age int comment '年龄',
    gender  varchar(1) comment '性别'
) comment '用户表';

数据类型

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 '身份证号',
    entrydate date comment '入职时间'
) comment '员工表'

修改表操作

1.添加字段

alter table emp add nickname varchar(20) comment '昵称';

2.修改字段

修改数据类型

alter table 表名 modify 字段名 新数据类型(长度)

修改字段名和字段类型

alter table emp change nickname username varchar(30) comment '用户名';

删除操作

删除字段

alter table emp drop username;

删除表

drop table if exists tb_user;

删除指定表，并重新创建该表

truncate table employ;

重新创建后只有表结构，没有数据

修改表名

alter table emp rename to employ;

DML操作

对表中数据进行增删改

添加数据

字符串，日期加双引号

insert into employee(id, workno, name, gender, age, idcard, entrydate)
values (1,'1','xiaoming','男',18,'100861111111111111','2000-01-01')

更新和删除

#修改id为1的数据，将name修改为itheima
update employee
set name='itheima'
where id = 1

#删除gender为女的员工
delete from employee where gender='女';

聚合函数

将一列数据作为一个整体，进行纵向计算

select count(字段列表) from employee;

null值不参与所有聚合函数运算

#统计该企业员工的平均年龄
select avg(age) from employee;

#求取最大年龄
select max(age) from employee;

DQL操作

分组查询

where与having的区别

1.执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组，而having是分组之后对结果进行过滤

2.判断条件不同：wher不能对聚合函数进行判断，而having可以

#查询年龄小于20岁的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workplace,count(*) from employee where age < 20 group by workplace having count(*)>=3

排序查询

select * from employee order by age asc;#升序，asc可以省略
select * from employee order by age desc;

#多字段排序
select *
from employee
order by age asc, entrydate desc

分页查询

# 查询第2页员工数据，每页显示5条数据------(页码-1)*上一页展示记录数
select * from employee limit 5,5;

DQL执行顺序

# 查询年龄大于20的员工的姓名、年龄，并根据年龄进行升序排序
select name, age
from employee e
where e.age > 20
order by age asc;

DCL操作

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

查询用户

use mysql;
select * from user;

创建用户

# 创建用户itcast,只能够在当前主机localhost访问，密码123456
create user 'itcast'@'localhost' identified by '123456';

# 创建用户 liang 可以在任意主机访问该数据库，密码123456
create user 'liang'@'%' identified by '123456'

修改用户密码

alter user 'liang'@'%' identified by '1234';

删除用户

# 删除itcast用户
drop user 'itcast'@'localhost';

主机名可以使用%通配

权限控制

查询权限

show grants for 'liang'@'%';

授予权限

获取itcast的所有权限

# 授予权限
grant all on itcast.* to 'liang'@'%';

撤销权限

撤销itcast的所有权限

# 撤销权限
revoke all on itcast.* from 'liang'@'%';

函数

字符串函数

# concat
select concat('hello','MySQL');

# lower
select lower('Hello')

# upper
select upper('Hello');

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

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

# trim
select trim(' hello MySQL ');

# substring
select substring('hello MySQL',1,5);

# 企业员工的工号，统一为5位数，前面补0
update employee
set workno= lpad(workno, 5, '0');

数值函数

# 数值函数
# ceil
select ceil(1.5);

# floor
select floor(1.1);

# mod
select mod(3, 4);

# rand
select rand();

#round
select round(2.345,2)

#生成六位验证码,保留0位小数
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-01', '2021-11-01');

# 查询所有员工的入职天数，并根据入职天数倒序排序
select name, datediff(current_date(), entrydate) as 'entrydays'
from employee
order by entrydays desc;

流程控制函数

# 流程控制函数
# if
select if(true, 'ok', 'Error');


# ifnull
select ifnull('oK', 'default');
select ifnull('', 'default');
select ifnull(null, 'default');

# case when then else end
# 需求，查询emp表的员工姓名和工作地址
select name,
       (case workplace
            when '北京' then '一线城市'
            when '上海' then '一线城市'
            when '深圳' then '一线城市'
            when '南京' then '一线城市'
            when '杭州' then '一线城市'
            else '二线城市' end) as '工作地址'
from employee;

约束

create table user
(
    id     int primary key auto_increment comment 'id主键',
    name   varchar(10) not null unique comment '注释',
    age    int check ( age > 0 && age <= 120) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表'

外键约束

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

语法

create table 表名(
		字段名 数据类型
        ...
        [constraint] [外键名称] foreign key (外键字段名) references 主表(主表列名)
)

外键约束删除行为

alter table 表名 add constraint 外键名称 foreign key(外键字段) references 主表名(主表字段名) on update cascade on delete cascade;

多表关系

数据库表准备

create database if not exists itcast default charset utf8;
use itcast;
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_studentid foreign key (studentid) references student (id),
    constraint fk_courseid foreign key (courseid) references course (id)
)comment '学生课程中间表';

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

多对多关系

一对一关系

表的拆分

在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的

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);

多表查询

从多张表中进行查询

# 笛卡尔积
select * from student,tb_user

消除无效的笛卡尔积

select *
from tb_user_edu,
     tb_user
where tb_user_edu.userid=tb_user.id

多表查询分类

内连接

查询A、B交集部分数据

#隐式内连接
select tb_user.name, tb_user_edu.degree
from tb_user,
     tb_user_edu
where tb_user.id=tb_user_edu.userid;

# 查询一个员工的姓名，及关联的部门的名称(显示内连接)
select tb.name,tbe.degree
from tb_user tb
         inner join tb_user_edu tbe on tb.id = tbe.userid

外连接

左外连接

查询左表所有数据，以及两张表交集部分数据

左表公共字段为空，仍然可以查出

# 查询tb_user表的所有数据左外连接
select tb.*,tbe.userid from tb_user tb left outer join tb_user_edu tbe on tb.id = tbe.userid;
#outer可以省略

右外连接

查询右表所有数据，以及两张表交集部分数据

# 查询tb_user_edu表的所有数据，和对应的员工信息(右外连接)
select tue.*,tu.*
from tb_user_edu tue
         right outer join tb_user tu on tu.id = tue.userid;

自连接

当前表与自身的连接查询，自连接必须使用表别名

#查询所有员工及其领导的名字
select a.name,b.name from emp a,emp b where a.managerid=b.id;

#查询所有员工及其领导的名字，如果员工没有领导，也要查询
select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid=b.id;

联合查询-union,union all

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

union可以将查询结果去重

#查询薪资低于5000的员工，和年龄大于50的员工
select * from emp where salary < 5000
union
select * from emp where age > 50;

注意：

多张表的列数必须保持一致，字段类型也要保持一致

子查询

嵌套查询，嵌套select语句

标量子查询

#查询销售部所有员工信息
#查询销售部门id
#根据销售部门id，查询所有员工信息
select * from emp where id=(
	select id from dept where name='销售部'
);

#查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (
	select entrydate from emp where name='张三'
);

列子查询

#查询销售部和市场部的所有员工信息

#根据部门id查询员工信息

select * from emp where dept_id in (
	select dept_id from dept where name='销售部' or name='市场部'
)

#查询比财务部所有人工资都高的员工信息

select * from emp where salary > all(
    select salary from emp where dept_id =(
   		 select id from dept where name='财务部'
    )
);

#查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > any(
     select salary from emp where dept_id =(
   		 select id from dept where name='研发部'
    )
);

表子查询

子查询返回的结果是多行多列

常用操作符IN

#查询与'张三'，'李四'的职位和薪资相同的员工信息
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

事务

事物简介

一组操作的集合，不可分割的单位，把所有的操作作为一个整体一起向系统提交或撤销操作请求

，即这些操作要么全部同时成功，要么同时失败

mysql默认自动提交事务，每当执行一条DML语句，MySQL会立即隐式的提交事务

创建新的表

#数据准备
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);

# 转账操作
# 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 = '李四';

事务操作

当前事务为自动提交

select @@autocommit;
#设置为手动提交
set @@autocommit = 0;

手动提交事务

# 转账操作
# 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;

第二种

开启事务

# 第二种
# 开启事务
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;

事物的四大特性

并发事务问题

事务隔离级别

x代表解决

从上往下，隔离级别递增，性能递减，数据安全性递增

查看系统隔离级别：select @@global.tx_isolation;
查看会话隔离级别(5.0以上版本)：select @@tx_isolation;
查看会话隔离级别(8.0以上版本)：select @@transaction_isolation;

# 查看事务隔离级别
select @@tx_isolation;

# 设置事务隔离级别
set session transaction isolation level read uncommitted;
set session transaction isolation level repeatable read;

同时打开两个数据库命令行窗口，模拟两个事务并行的操作

设置事务隔离级别为read uncommitted

两边同时开启事务

左边数据读取到了右边未提交的事务，这个过程为脏读

其余可模仿该操作设置不同隔离级别来区分