MySQL

发表于 更新于 分类于 Disqus： 本文字数： 1.2k 阅读时长 ≈ 5 分钟

管理MySQL服务

启动服务

1
net start MySQL80

停止服务

1
net stop MySQL80

配置环境变量

C:\Program Files\MySQL\MySQL Server 8.0\bin

登录MYSQL

1
mysql -u root -p

数据库操作

查询

1
2
show databases;         查询所有数据库
select database();      查询当前所处数据库

创建

1
create database 数据库名;

删除

1
drop database 数据库名;

使用

1
use 数据库名;

表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 增
insert into user(id, name, gender, age) values (1, '张三', '男', 20);
insert into user values (2, '李四', '男', 18);
insert into user values (3, '王麻子', '男', 30),(4, '赵六', '男', 40);

-- 删
delete from user where id = 1;
delete from user;

-- 改
update user set name = '匿名' where id = 1;

update user set name = '迪迦', gender = '女' where id = 2;

update user set gender = '女';

表查询

基础查询

1
2
3
4
5
6
7
8
9
select name,gender from emp;

select * from emp;

select entrydate from emp;
select entrydate as '入职时间' from emp;
select entrydate '入职时间' from emp;

select distinct workaddress from emp;

条件查询

1
2
3
4
5
6
7
8
9
10
select * from emp where age < 20;
select * from emp where age >= 18 and age <= 30;
select * from emp where age between 16 and 20;
select * from emp where gender != '男';
select * from emp where idcard is null;
select * from emp where age = 20 and gender = '女';
select * from emp where age = 16 or age = 18 or age = 20;
select * from emp where age in (16,18,20);
select * from emp where name like '__';
select * from emp where idcard like '%X';

聚合查询

1
2
3
4
5
6
7
8
9
10
11

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 = '西安';

分组查询

1
2
3
4
5
select gender, count(*) '数量' from emp group by gender;

select gender, avg(age) '年龄' from emp group by gender;

select workaddress, count(*) from emp where age < 50 group by workaddress having count(*) > 3;

排序查询

1
2
3
4

select * from emp order by age ;
select * from emp order by age desc ;
select * from emp order by age asc , entrydate desc ;

分页查询

1
2
3
4
5

select * from emp limit 0, 10;
select * from emp limit 10;

select * from emp limit 10, 10;

练习

1
2
3
4
5
6
7
8
9
10
11
-- 1．查询年龄为20,21,22,23岁的女性员工信息。
select * from emp where age in (20,21,22,23) and gender = '女';
-- 2．查询性别为男,并且年龄在20-40岁(含)以内的姓名为三个字的员工。
select * from emp where gender = '男' and age between 20 and 40 and name like '___';
-- 3．统计员工表中，年龄小于60岁的,男性员工和女性员工的人数。
select gender, count(*) from emp where age < 60 group by gender;
-- 4，查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select name, age from emp where age <= 35 order by age, entrydate desc ;
-- 5．查询性别为男，且年龄在20-40 岁(含)以内的前5个员工信息，对查询的结果按年龄升序排齐，年龄相同按入职时间升序槽序。
select * from emp where gender = '男' and age between 20 and 40 order by age, entrydate desc limit 5;

执行顺序

函数

字符串函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- 拼接字符
select concat('Hello', 'MySQL');
-- 大小写
select upper('Hello');
select lower('Hello');
-- 左右增加字符
select rpad('01', 5, '-');
select lpad('01', 5, '-');
-- 去前后空格
select trim(' Hello MySQL');
-- 截取
select substring('Hello MySQL', 1, 5);

-- practice工号前补零
update emp set workno = lpad(workno, 5, '0');

数值函数

1
2
3
4
5
6
7
8
9
10
11
12
-- 上下取整
select ceil(1.1);
select floor(1.9);
-- 求模运算
select mod(7, 5);
-- 0~1随机数
select rand();
-- 保留两位小数
select round(2.345, 2);

-- practice生成一个随机六位数
select lpad(round(rand()*1000000, 0), 6, '0');

日期函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 日期
select curdate();
-- 时间
select curtime();
-- 日期和时间
select now();
-- 当前年
select year(now());
-- 当前月
select month(now());
-- 当前天
select day(now());
-- 增加时间
select date_add(now(), interval 2 year );
-- 时间做差
select datediff('2023-3-30', '2002-9-5');

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

流程函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- if判断
select if(true, 'y', 'n');
select if(false, 'y', 'n');
-- ifnull是否为空
select ifnull('y', 'n');
select ifnull('', 'n');
select ifnull(null, 'n');

-- demo判断员工工作地址是否为一线城市，不是则为二线城市
select
    name,
    case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end as '工作地址'
from emp;
-- demo2查询score表各科成绩
select
    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;

约束

基本约束

建表

1
2
3
4
5
6
7
create table user2(
    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 '用户表2';

插入数据

1
2
3
4
5
6
7
8
9
10
11
insert into user2(name, age, status, gender) values ('Tom1', 19, 1, '男'), ('Tom2', 25, 0, '男'), ('Tom3', 19, 1, '男');

insert into user2(name, age, status, gender) values (null, 19, 1, '男');
insert into user2(name, age, status, gender) values ('Tom3', 19, 1, '男');

insert into user2(name, age, status, gender) values ('Tom4', 80, 1, '男');

insert into user2(name, age, status, gender) values ('Tom5', -1, 1, '男');
insert into user2(name, age, status, gender) values ('Tom6', 121, 1, '男');

insert into user2(name, age, gender) values ('Tom7', 120, '男');

效果展示

外键

1
2
alter table emp add constraint emp_emp_id_fk foreign key (dept_id) references dept(id);
alter table emp drop foreign key emp_emp_id_fk;

效果

Buy me a coffee
Uu 微信 微信
Uu 支付宝 支付宝
欢迎关注我的其它发布渠道