数据库基本操作
启动与停止
1.第一种方式:
1>以管理员身份运行cmd
2>在命令行窗口中输入:
启动:net start mysql80
停止:net stop mysql80
2.第二种方式:
1>Win+R快捷方式打开如下:
输入:services.msc
2>找到MySQL80
3>双击:
4>这里我选择的是开机自启动
客户端连接
1.第一种方式:通过MySQL提供的客户端命令行工具
2.第二种方式:通过命令行工具执行命令
mysql [-h 127.0.0.1] [-P 3306] -u 用户 -p
注意:
1.[]中可省略
2.使用这种方式时,需要配置PATH环境变量
SQL
1.DDL(数据定义语言)
数据库操作
查询所有数据库:
show databases;
查询当前数据库:
select datebase();
创建数据库:
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
删除数据库:
drop database [if exists] 数据库名;
使用数据库:
use 数据库名;
表操作
查询:
查询当前数据库所有表:
show tables;
查询表结构:
desc 表名;
查询指定表的建表语句:
show create table 表名;
创建:
create table 表名(
字段1 字段1类型[comment '注释'],
字段2 字段2类型[comment '注释'],
字段3 字段3类型[comment '注释'],
.......
字段n 字段n类型[comment '注释']
) [comment '注释'];
修改:
添加字段:
alter table 表名 add 字段名 类型(长度) [comment '注释'] [约束];
修改数据类型:
alter table 表名 modify 字段名 新数据类型(长度);
修改表名:
alter table 表名 rename to 新表名;
删除:
删除表:
drop table [if exists] 表名;
删除指定表并重新创建该表:
truncate table 表名;
2.DML(数据操作语言)
添加数据(insert)
给指定字段添加数据:
insert into 表名 (字段1,字段2......) values(值1, 值2......);
给全部字段添加数据:
insert into 表名 values(值1, 值2......);
批量添加数据:
insert into 表名 (字段1,字段2......) values(值1, 值2......),(值1, 值2......),(值1, 值2......);
insert into 表名 values(值1, 值2......),(值1, 值2......),(值1, 值2......);
注意:
1.插入数据时要按注意顺序
2.字符串和日期型数据应该包含在引号中
3.插入的数据大小要合法
修改数据(update)
update 表名 set 字段1=值1,字段2=值2......[where 条件];
注意:
如果没有条件,则会修改整张表
删除数据(delete)
delete from 表名 [where 条件]
注意:
1.如果没有条件,则会删除整张表的数据
2.delete不能删除某一字段的值
3.DQL(数据查询语言)
编写顺序:
select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数;
基本查询
查询多个字段:
select 字段1,字段2,字段3...from 表名;
select * from 表名;
设置别名:
select 字段1[as 别名1],字段2 [as 别名2]......from 表名;
去除重复记录:
select distinct 字段列表 from 表名;
条件查询
语法:
select 字段列表 from 表名 where 条件列表;
条件:
比较运算符 | 功能 |
---|---|
> | |
>= | |
< | |
<= | |
= | |
<> 或 != | 不等于 |
between...and... | 在某个范围之内 |
in(...) | 在in之后的括号中,多选一 |
like 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
is null | |
and 或 && | 并且 |
or 或 || | 或 |
not 或 ! | 非 |
eg:
#二、条件查询
#select 字段列表 from 表名 where 条件列表;
#1.查询年龄等于25的员工
select * from emp where age = 25;
#2.查询年龄小于20的员工
select * from emp where age < 20;
#3.查询没有身份证信息的员工
select * from emp where idcard is null;
#4.查询有身份证信息的员工
select * from emp where idcard is not null;
#5.查询年龄不等于18的员工
select * from emp where age != 18;
select * from emp where age <> 18;
#6.查询年龄在20岁到25岁(包含25)之间的员工信息
select * from emp where age > 20 && age <= 25;
select * from emp where age > 20 AND age <= 25;
#两端都包含
select * from emp where age between 15 and 25;
#7.查询性别为女且年龄小于25的员工信息
select * from emp where gender = '男' && age < 25;
#8.查询年龄等于15或者20或者25的员工
select * from emp where age = 15 || age = 20 || age = 25;
select * from emp where age = 15 or age = 20 or age = 25;
select * from emp where age in(15, 20, 25);
#9.查询姓名为两个字的员工 模糊匹配
select * from emp where name like '___';
#10.查询身份证号最后一位为X的员工
select * from emp where idcard like '%X';
聚合函数
select 聚合函数(字段列表) from 表名;
注意:
对一列进行计算 所有null值不参与聚合函数的计算
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
eg:
#三、聚合函数
#对一列进行计算 所有null值不参与聚合函数的计算
#select 聚合函数(字段列表) from 表名;
#1.统计数量
select count(*) from emp;
#2.统计企业员工的平均年龄
select avg(age) from emp;
#3.最大年龄
select max(age) from emp;
#4.最小年龄
select min(age) from emp;
#5.统计北京地区员工年龄之和
select sum(age) from emp where workaddress = '北京';
分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
where | 分组之前执行,不满足where条件的不参与分组,where不能对聚合函数进行判断 |
having | 分组之后对结果进行过滤,having可以对聚合函数进行判断 |
eg:
#1.根据性别分组, 统计男性与女性的数量
select gender, count(*) from emp group by gender;
#2.根据性别分组,统计男性和女性的平均年龄
select gender, avg(age) from emp group by gender;
#3.查询年龄小于25的员工, 并根据工作地址分组,获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from emp where age <= 25 group by workaddress having count(*) > 1;
排序查询
select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;
asc | 升序(默认) |
desc | 降序 |
eg:
#1.根据年龄对公司员工进行升序排序
select * from emp order by age asc;
#2.根据年龄对公司员工进行降序排序
select * from emp order by age desc
#3.根据年龄升序 根据id降序
select * from emp order by age asc, id desc;
分页查询
select 字段列表 from 表名 limit 起始索引, 查询记录数;
eg:
#1.查询第1页员工数据,每页展示2条记录
select * from emp limit 0, 2;
#2.查询第2页员工数据,每页展示10条数据 ------>(页码 - 1)*页展示记录数
select * from emp limit 2, 2;
4.DCL(数据控制语言)
管理用户
注意:
主机名可以使用%通配
查询用户:
use mysql;
select * from user;
创建用户:
create user '用户名'@‘主机名’ identified by '密码';
修改用户密码:
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
删除用户:
drop user '用户名'@'主机名';
权限控制
权限 | 说明 |
---|---|
all, all privileges | 所有权限 |
select | 查询数据 |
insert | 插入数据 |
update | 修改数据 |
delete | 删除数据 |
alter | 修改表 |
drop | 删除数据库/表/视图 |
create | 创建数据库/表 |
查询权限:
show grants for '用户名'@'主机名';
授予权限:
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
注意:
授权时数据库名和表名可以用*进行通配,代表所有
撤销权限:
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
函数
查看函数返回结果
select + 函数;
字符串函数
函数 | 功能 |
---|---|
concat(s1, s2, s3...sn) | 拼接 |
lower(s) | 转小写 |
upper(s) | 转大写 |
lapd(s, n, pad) | 左填充,用pad对s进行左填充以达到n个字符 |
rpad(s, n, pad) | 右填充,用pad对s进行右填充以达到n个字符 |
trim(s) | 去掉s头部和尾部的空格 |
substring(s, st, len) | 截取从st开始的len个字符 st从1开始 |
eg:
#concat
select concat('Karry',' Wang');
#lower
select lower('HELLO');
#upper
select upper('hello');
#lpad
select lpad('01', 9, '-');
#rpad
select rpad('01', 9, '-');
#trim
select trim(' 010 101 ');
#substring
select substring('Hello World', 1, 5);
#1
update emp set working=lpad(working, 5, '0');
数值函数
函数 | 功能 |
---|---|
ceil(x) | 向上取整 |
floor(x) | 向下取整 |
mod(x, y) | 返回x % y |
rand() | 返回0~1内的随机数 |
round(x, y) | 求x四舍五入的值,保留y位小数 |
eg:
#生成一个六位验证码
select rpad(round(rand()*1000000, 0), 6, round(rand()*10, 0));
日期函数
函数 | 功能 |
---|---|
curdate() | 返回当前日期 |
curtime() | 返回当前时间 |
now() | 返回当前日期和时间 |
year(date) | 获取date的年份 |
month(date) | 获取date的月份 |
day(date) | 获取date的日期 |
date_add(date, interval expr type) | 返回一个日期加上一个时间间隔expr后的时间值 |
datediff(date1, date2) | 返回起始时间date1和结束时间date2之间的天数(date1 - date2) |
eg:
select date_add(now(), interval 70 day);
select date_add(now(), interval 70 month);
select datediff(now(), '2022-9-21');
#根据入职时长倒序排序
select * from emp;
select name, datediff(now(), entrydate) from emp order by datediff(now(), entrydate) desc;
流程函数
函数 | 功能 |
---|---|
if(value, t, f) | 如果val为true,返回t,否则返回f |
ifnull(value1, value2) | 如果val1不为空,返回val1,否则返回val2 |
case when [val1] then [res1]... else [default] end | 如果val1为true,返回res1,否则返回default默认值 |
case [expr] when [val1] then [res1]...else [default] end | 如果expr=val1,返回res1,否则返回default默认值 |
eg:
select if(true, 'ok', 'no');
select ifnull('1', '2');
select ifnull(null, 'default');
select case when true then 'ok' else 'no' end;
select case '2' when '2' then 'yes' else 'no' end;
eg:
select name,
workaddress,
case workaddress when '南京' then '一线城市'
when '重庆' then '一线城市'
else '二线城市' end
as citydiffer
from emp;
eg:
select
id,
name,
(case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) as '数学',
(case when English >= 85 then '优秀' when English >= 60 then '及格' else '不及格' end) as '英语',
(case when Chinese >= 85 then '优秀' when Chinese >= 60 then '及格' else '不及格' end) as '语文'
from score;
约束
概述
概念:
约束是作用于表中字段上的规则,用于限制存储在表中的数据
约束可以在创建表/修改表的时候添加
目的:
保证数据库中数据的正确性、有效性和完整性
分类:
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为null | not null |
唯一约束 | 保证该字段的所有数据都是唯一的、不重复的 | unique |
主键约束 | 主键是一行数据的唯一标识,要求非空且唯一 | primary key (自增:auto_increment) |
默认约束 | 保存数据时,如果未指定该字段的值,则采用默认值 | default |
检查约束 | 保证字段值满足某一条件 | check |
外键约束 | 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 | foreign key |
约束演示
create table user(
id int primary key auto_increment comment '主键',
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 '用户表';
外键约束
概念
子表(从表):具有外键的表
父表(主表):外键所关联的表
语法
添加外键
sql语句添加:
alter table (从表) add constraint 外键名称 foreign key (外键字段名) references 主表(主键);
创建表时添加:
create table emp1(
id int primary key auto_increment comment '主键',
name varchar(10) not null unique comment '姓名',
age int check ( age > 0 && age <= 120 ) comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导id',
dept_id int comment '部门id',
constraint dept_id_fk foreign key (dept_id) references dept(id)
)comment '员工表';
eg:
#添加外键
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id);
删除外键
alter table 从表 drop foreign key 外键名称;
eg:
#删除外键
alter table emp drop foreign key fk_emp_dept_id;
指定外键删除/更新行为
行为 | 说明 |
---|---|
not action / restrict | 默认行为,当父表删除或更新记录时,如果当前记录对应的父表与子表有关联,则不予删除或更新 |
cascade | 当父表删除或更新记录时,如果有外键关联父表与子表,则也删除或者更新外键在子表中的记录 |
set null | 当父表删除或更新记录时,如果有外键关联父表与子表,则设置子表中该外键为null |
set default | 当父表删除或更新记录时,如果有外键关联父表与子表,则设置子表中该外键为默认值(innodb不支持) |
alter table (从表) add constraint 外键名称 foreign key (外键字段名) references 主表(主键) on update 行为 on delete 行为;
eg:
#指定外键更新或删除行为
alter table emp add constraint fk_emp_dept_id foreign key (dept_id) references dept(id) on update cascade on DELETE cascade;
example:
#主表
create table dept(
id int auto_increment primary key comment 'id',
name varchar(50) not null comment '部门名称'
)comment '部门表';
#从表
create table emp(
id int primary key auto_increment comment '主键',
name varchar(10) not null unique comment '姓名',
age int check ( age > 0 && age <= 120 ) comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导id',
dept_id int comment '部门id'
)comment '员工表';
#插入数据
insert into dept(name) values ('研发部'), ('市场部'), ('销售部'), ('外交部');
insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id) values
(1, '成朗', 24, '外交官', 5000, '1-1-1', 1, 4),
(2, '庄文杰', 18, '学生', 3000, '1-1-1', 2, 2);
#外键约束
#添加外键
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;
多表查询
多表关系
一对多(多对一)
在多的一方建立外键,指向一的一方的主键
多对多(中间表)
建立第三张中间表,中间表至少包含两个外键,分别关联多方主键
一对一(单表拆分)
在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(unique)
多表查询概述
笛卡尔积
两个集合所有组合的情况
#多表查询
select * from emp, dept;
消除无效笛卡尔积
#多表查询
select * from emp, dept where emp.dept_id = dept.id;
多表查询分类
连接查询
外连接
左外连接:
查询左表所有数据,以及两张表交集部分数据
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
右外连接:
查询右表所有数据,以及两张表交集部分数据
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
eg:
#左外连接
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 表1, 表2 where 条件...;
显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
eg:
#显式内连接
select emp.name, dept.name from emp, dept where emp.dept_id = dept.id;
#隐式内连接
select emp.name, dept.name from emp inner join dept where emp.dept_id = dept.id;
自连接
当前表与自身的连接查询,自连接必须使用表别名
select 字段列表 from 表1 别名1 join 表1 别名2 on 条件...;
eg:
#自连接
select e1.name, e2.name from emp e1 join emp e2 on e1.managerid = e2.managerid;
select e1.*, e2.name from emp e1 left outer join emp e2 on e1.managerid = e2.managerid;
联合查询
union查询
把多次查询的结果合并起来,形成一个新的查询结果集
union:
对查询结果进行去重操作
union all:
将查询结果直接输出
注意:
联合查询的多张表字段列表个数及类型必须保持一致,查询结果的字段名称为第一次查询字段名
select 字段列表 from 表1 ... union [all] select 字段列表 from 表2 ...;
eg:
#联合查询
select emp.name, emp.age, emp.salary from emp where salary <= 5000 union
select emp.name, emp.age, emp.salary from emp where age >= 10;
子查询(嵌套查询)
概念
SQL语句中嵌套select语句,成为嵌套查询(子查询)
语法
insert/update/delete/select * from 表1 where column1 = (select column1 from 表2);
分类
按子查询结果:
标量子查询,列子查询, 行子查询,表子查询
按子查询位置
where之后、from之后、select之后
标量子查询(单个值)
子查询返回结果为单个值(数字,字符串,日期......)
常用操作符:= 等于
<> 不等于
> 大于
>= 大于等于
< 小于
<= 小于等于
eg:
select * from emp where dept_id = (select id from dept where name = '外交部');
select * from emp where entrydate >= (select entrydate from emp where name = '成朗');
列子查询(一列)
子查询返回结果为一列(可以是多行)
常见操作符:
in 在指定的集合范围之内,多选一
not in 不在指定集合范围之内
any 子查询返回列表中,有任何一个满足即可
some 与any相同
all 子查询返回列表的所有值都必须满足
eg:
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 > some(select salary from emp where dept_id = (select id from dept where name = '销售部'));
行子查询(一行)
子查询返回结果为一行
常见操作符:
= 等于
<> 不等于
in 在集合范围之内
not in 不在集合范围之内
eg:
select * from emp where (salary, managerid) = (select salary, emp.managerid from emp where name = '成朗');
表子查询(多行多列)
子查询返回结果为多行多列
常见操作符:
in 在集合范围之内
eg:
select * from emp where (salary, dept_id) in (select salary, dept_id from emp where name = '成朗' or name = '庄文杰');
select e.*, dept.name from (select * from emp where entrydate >= '1-1-1') e, dept where e.dept_id = dept.id;
select e.*, d.* from (select * from emp where entrydate >= '1-1-1') e left join dept d on e.dept_id = d.id;
如有错误,欢迎指正!!!