mysql基础部分

weixin_53184069

已于 2022-03-19 15:41:42 修改

阅读量1.4k

点赞数 23

分类专栏： java SQL MySQL 文章标签： mysql 数据库 database

于 2022-03-18 17:29:57 首次发布

本文链接：https://blog.csdn.net/weixin_53184069/article/details/123580419

版权

java 同时被 3 个专栏收录

1 篇文章 0 订阅

订阅专栏

SQL

1 篇文章 0 订阅

订阅专栏

MySQL

1 篇文章 0 订阅

订阅专栏

MySQL概述

数据库的好处

实现数据持久化，便于查询

MySQL服务的启动和停止

net stop MySQL

net start MySQL

MySQL服务的登录和退出

mysql -h localhost -P3306 -u root -p 1234

exit

MySQL注释

单行注释：#注释文字

单行注释：-- 注释文字

多行注释：/* 注释文字 */

SQL

DDL

show databases; : 展示数据库都有哪些子数据库

use + 数据库名字; : 进入该数据库

show tables; ：查询当前数据库都有哪些表

show create table 表名; : 查询指定表的建表语句

show tables from + 数据库名字; : 查询某数据库的表

desc 表名; : 查询表结构

select database(); ：查询当前在哪个数据库

create database [if not exists] 数据库名字 [default charset 字符集] [collate 排序规则];

drop database[if exists] 数据库名字 : 删除数据库

create table + 要新建的表的名字 + (id int, name varchar(20)); : 在当前数据库新建一个表

desc + 表名字; : 查看表的结构

select version(); : 查看mysql

DDL-表操作-修改

alter table 表名 add 字段名类型(长度) [comment 注释] [约束]; : 添加字段

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

alter table 表名 change 旧字段名新字段名类型(长度) [comment 注释][约束]; : 修改字段名和字段类型

alter table 表名 drop 字段名; : 删除字段

alter table 表名 rename to 新表名 ; : 修改表名

drop table[if exists] 表名 : 删除表

truncate table 表名; : 删除指定表，并重新创建该表（就是删除表中数据）

DML

DML-数据操作（增删改）

insert into 表名(字段一，字段二)values(值1，值2); : 给指定字段添加数据

insert into 表名 values(值1,值2); : 给全部字段添加数据

insert into 表名(字段1，字段2,...) values(值1，值2),(值1，值2)； : 批量添加数据

insert into 表名 values(值1,值2),(值1,值2); : 批量添加数据

update 表名 set 字段1=值1,字段2=值2,...[where 条件]; : DML-修改语句

delete from 表名 [where 条件]; : DML-删除数据

DQL

DQL-数据操作（查询）

基本查询

select 字段1,字段2... from 表名; : 查询多个字段

select * from 表名; : 查询所有字段

select 字段1[AS 别名1],字段2[AS 别名2] ... from 表名; : 设置别名

select distinct 字段列表 from 表名 ; : 去除重复记录

条件查询

select 字段列表 from 表名 where 条件列表; : 条件查询

# 条件查询
select * from emp where age = 88;

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 != 88;
select * from emp where age <> 88;

select * from emp where age >=15 && age <= 20;
select * from emp where age >= 15 and age <=20;
select * from emp where age between 15 and 20;

select * from emp where gender = '女' and age < 25;

select * from emp where age = 18 or age = 20 or age = 40;
select * from emp where age in (18,20,40);

select * from emp where name like '__';查询名字为两个字的
select * from emp where name like '___';查询名字为三个字的

select * from emp where idcard like '%x';

聚合函数

就几个关键字，，，记住就行了

count 、avg、max、 min、sum

select 聚合函数(字段) from 表名; ; 利用聚合函数求值

# 聚合函数
select COUNT(*) from emp;# 16
select count(idcard) from emp;# 15

select AVG(age) from emp;# 求平均年龄

select MAX(age) from emp;# 求最大年龄

select MIN(age) from emp;# 求最小年龄

select * from emp where workaddress = '西安';# 求所有西安的员工
select sum(age) from emp where workaddress = '西安';# 求在西安工作的员工的年龄和

分组查询

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤的条件]; : 分组查询

where 和 having 区别

执行时机不同，where是分组之前过滤，不满足where条件不参与分组，having是分组之后过滤
判断条件不同，where当中不能对聚合函数进行判断，但是having可以

# 分组查询
select gender,count(*) from emp group by gender;# 根据性别分组，统计男性员工和女性员工的数量

select gender,AVG(age) from emp group by gender;# 根据性别分组，统计男性员工和女性员工的平均年龄

select workaddress,count(*) AS address_count from emp where age < 45 group by  workaddress having address_count >= 3;# 查询年龄小于45的员工，根据工作地址分组，获取员工数量大于等于3的工作地址

排序查询

select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2; : 排序查询

排序方式：

ASC:升序(默认)
DESC:降序
如果第一个字段相同，则按照第二个字段排序

# 排序查询
select * from emp order by age asc;# 根据年龄对员工升序排序
select * from emp order by age desc;# 根据年龄对员工升序降序

select * from emp order by entrydate desc;# 根据入职时间，对员工进行降序排序

select * from emp order by age asc , entrydate desc;# 根据年龄对员工进行升序排序，年龄相同，在按照入职时间降序排序

分页查询

select 字段列表 from 表名 limit 起始索引,查询记录数; : 分页查询

起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数

# 分页查询
select * from emp limit 0,10;# 查询第一页员工数据，每页显示十条
select * from emp limit 10;# 查询第一页员工数据，每页显示十条

select* from emp limit 10,10;# 查询第二页员工数据，每页显示十条

DQL-执行顺序

编写顺序

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
    分页参数

执行顺序

from
	表名列表
where
	条件列表
group by
	分组字段列表
having
	分组后条件列表
select
	字段列表
order by
	排序字段列表
limit
    分页参数

DCL

DCL-管理用户

use mysql; : 查询用户

select * from user; : 查询用户

create user '用户名'@'主机名' identified by '密码' ; : 创建用户

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码' ; : 修改用户密码

drop user '用户名'@'主机名'; : 删除用户

%可以表示任意主机

create user 'wjr'@'localhost' identified by '123456'; # 创建用户wjr，密码123456，主机号本机

create user 'thrive'@'%' identified by '123456'; # 创建用户thrive，密码123456，任意主机可以访问

alter user 'thrive'@'%' identified with mysql_native_password by '1234';# 修改thrive密码

drop user 'thrive'@'%';# 删除用户

DCL-权限控制

show grants for '用户名'@'主机'; : 查询权限

grant 权限列表 on 数据库名.表名 to '用户名@‘主机名'; : 授予权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'; : 撤销权限

多个权限之间用，隔开
也可直接*

show grants for 'wjr'@'localhost';

grant all on itcast.* to 'wjr'@'localhost';

revoke all on itcast.* from 'wjr'@'localhost';

函数

字符串函数

在这里插入图片描述

select concat('hello' , 'mysql');

select upper('hello');

select lpad('01',6,'-');

select substring('hello mysql',1,5);

# 员工工号统一为五位数，不足五位数的在前面补0
update emp set workno = lpad(workno , 5 , '0');

数值函数

在这里插入图片描述

# 通过数据库的函数，生成一个随机验证码
select lpad(round(rand()*1000000 , 0), 6, '0');

日期函数

在这里插入图片描述

select curdate();
select curtime();
select now();
select year(now());
select month(now());
select date_add(now(), interval  70 year );
select datediff('2022-03-01' , '2022-03-04');# 第一个时间减去第二个时间

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

流程函数

在这里插入图片描述

select if(true,'ok','error');
select  ifnull(null,'wjr');# wjr
select  ifnull('','wjr');#

#查询emp表的员工的姓名和地址（北京/上海一线，其他二线）
select
    name,
    (case workaddress when  '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;

约束

在这里插入图片描述

主键约束

auto_increment自增

约束练习

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

insert into user(
                 name,
                 age,
                 status,
                 gender
)values ('tom1',19,'1','男'),
        ('tom2',25,'0','男');

insert into user(name,age,status,gender)values (null,19,'1','男');
insert into user(name,age,status,gender)values ('tom3',19,'1','男');
insert into user(name,age,status,gender)values ('tom4',80,'1','男');
insert into user(name,age,status,gender)values ('tom5',120,'1','男');
insert into user(name,age,gender)values ('tom6',120,'男');

外键约束

添加外键

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

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

删除外键

alter table 表名 drop foreign key 外键名称;

-- ----------------------------------------------------------------------------------------------------------
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, '销售部');



create table employee(
                    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 employee (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 employee add constraint fk_employee_dept_id foreign key(dept_id) references dept(id);

# 删除外键
alter table employee drop foreign key fk_employee_dept_id;

fk_employee_dept_id   这个fk是foreign key的缩写，仅仅是一个习惯写法，employee是表名，后面是字段名，这个就是外键名称，是可以自定义的。。。

删除/更新行为
在这里插入图片描述

# 外键的删除和更新行为
alter table employee add constraint fk_employee_dept_id foreign key(dept_id) references dept(id) on update  cascade on delete cascade;

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

多表查询

多表关系

一对多

再多的一方建立外键

多对多
建立中间表，中间表至少包含两个外键，分别关联两方的主键

-- -------------------------------- 多表关系 演示 ---------------------------------------------
-- 多对多 ----------------
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_courseid 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);

一对一

可以做表拆分

这个就是在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的约束(unique)、

-- --------------------------------- 一对一 ---------------------------
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 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),
            (7, '灭绝', 60, '财务总监',8500, '2002-09-12', 1,3),
            (8, '周芷若', 19, '会计',48000, '2006-06-02', 7,3),
            (9, '丁敏君', 23, '出纳',5250, '2009-05-13', 7,3),
            (10, '赵敏', 20, '市场部总监',12500, '2004-10-12', 1,2),
            (11, '鹿杖客', 56, '职员',3750, '2006-10-03', 10,2),
            (12, '鹤笔翁', 19, '职员',3750, '2007-05-09', 10,2),
            (13, '方东白', 19, '职员',5500, '2009-02-12', 10,2),
            (14, '张三丰', 88, '销售总监',14000, '2004-10-12', 1,4),
            (15, '俞莲舟', 38, '销售',4600, '2004-10-12', 14,4),
            (16, '宋远桥', 40, '销售',4600, '2004-10-12', 14,4),
            (17, '陈友谅', 42, null,2000, '2011-10-12', 1,null);
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

-- 单表查询
select * from emp;

-- 多表查询  -- 笛卡尔积
select * from emp , dept where emp.dept_id = dept.id;

多表查询的分类

连接查询
- 内连接：相当于查询A，B交集部分数据
- 外连接：
  - 左外连接：查询左表的所有数据，以及两张表的交集部分
  - 右外连接：查询右表的所有数据，以及两张表的交集部分
- 自连接：当前表与自身的连接查询，自连接必须使用表的别名
子查询

内连接

隐式内连接

select 字段列表 from 表一，表二 where 条件... ;

显式内连接

select 字段列表 from 表一 [inner] join 表二 on 连接条件...;

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 inner join dept d on e.dept_id = d.id;

select distinct emp.name , dept.name from emp , dept where emp.dept_id = dept.id;# distinct是去重

外连接

左外连接

select 字段列表 from 表1 left [outer] join 表2 on 条件...; : 查询左表的所有数据，以及两张表的交集部分

select 字段列表 from 表1 right[outer] join 表2 on 条件...; : 查询右表的所有数据，以及两张表的交集部分

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

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

自连接

select 字段列表 from 表A 别名A join 表A 别名B on 条件...;

# 查询员工和所属领导的名字
select a.name as '员工', b.name '领导' from emp a , emp b where a.managerid = b.id;

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

联合查询-union,union all

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

select 字段列表 from 表A...
union[all]
select 字段列表 from 表b...;

# 薪资低于5000，年龄大于50
select * from emp where salary < 5000
union all-- 有all就不去重
select * from emp where age > 50;

对于联合查询的多张表的列数必须保持一致，字段类型也要保持一致
union all会将全部的数据直接合在一起，union会对合并后的数据去重

子查询

sql语句中嵌套select语句，称为嵌套查询，又称为子查询

select * from t1 where column1 = (select column1 from t2);

子查询外部的语句可以使insert/update/delete/select的任何一个

标量子查询

子查询返回的结果是单个值(数字、字符串、日期) ，最简单的形式。

常用操作符：= <> > >= < <=

# 查询销售部的所有员工信息、
select* from emp where dept_id = (select id from dept where dept.name = '销售部');

列子查询

子查询的结果是一列(可以是多行)
在这里插入图片描述

# 查询销售部和市场部的与昂工信息
select * from emp where dept_id in (select 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、not in

# 查询和张无忌薪资和直属领导相同的员工信息
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张无忌');

表子查询

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

常用操作符：IN

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

# 查询入职日期是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;

事务

手动事务操作

查看/设置事务提交方式

select @@autocommit;-- 查看事务自动提交方式  如果1则是自动提交  如果0则是手动提交
SET @@autocommit = 0;-- 设置事务提交方式

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

手动提交事务方式

select @@autocommit;
set @@autocommit = 0;
# 转账操作
-- 查询张三账户余额
select * from account where name = '张三';
-- 张三余额-1000
update account set money = money - 1000 where name = '张三';
-- 李四余额+1000
update account set money = money + 1000 where name = '李四';
-- 提交事务
commit;
-- 回滚事务
rollback ;

自动事务操作

开启事务
```
start transaction 或 begin
```
提交事务
```
commit;
```
回滚事务
```
rollback;
```

事务隔离级别

select @@transaction_isolation  -- 查看事务隔离级别
set [session | global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable} -- 设置事务的隔离级别