数据库MySQL

从开始菜单中找到MySQL/MariaDB–>MySQL Client 打开客户端 , 输入自己的密码显示Welcome … 说明建立好了连接

退出指令 : exit

建立连接指令 : mysql -uroot -p 回车后输入密码 , 再回车

数据库相关

查询所有数据库 show databases;

创建数据库

  • 默认字符集格式 : create database 数据库名;
  • 指定字符集格式 : create database 数据库名 charset=utf8/gbk;
  • 查询数据库信息 : show create database 数据库名;
  • 删除数据库 : drop database 数据库名;
  • 使用数据库 : use 数据库名;
  • 查询所有数据库检查是否创建成功 : show databases;
  • 检查数据库的字符集是否正确 show create database 数据库名;

表相关

执行表相关的SQL之前必须使用了某个数据库.use

插入数据库表source C:\mall_pms.sql;

1
2
create table user(id int primary key auto_increment,username varchar(50),password varchar(50),nickname varchar(50))charset=utf8;
insert into user values(null,'admin','123456','管理员');
  • 创建表 :create table 名字 (字段1名 类型,字段2名 类型,…) charset = utf8;
  • 查询所有表 : show tables;
  • 查询表信息 : show create table 表名;
  • 查询表字段 : desc 表名;
  • 删除表 : drop table 表名;
  • 修改表名 : rename table 原名 to 新名;

添加表字段

  • 最后面添加格式 :alter table 表名 add 字段名 类型;
  • 最前面添加格式 : :alter table 表名 add 字段名 类型 first;
  • 某字段后面添加格式 : alter table 表名 add 字段名 类型 after 字段名;
  • 删除表字段 : alter table 表名 drop 字段名;
  • 修改表字段 : alter table 表名 change 字段名原名 字段名新名 新类型;

数据相关

操作数据必须保证已经使用了某个数据库并且已经准备好了保存数据的表

1
2
3
create database 数据库名 charset = utf8;
use 数据库名;
create table 表名(字段名 varchar(50),字段名 int);

往表中插入数据

  • 全表插入格式 : insert into 表名 values(值1,值2);
    举例 : insert into person values("tom",18);

  • 指定字段插入格式 : insert into 表名(字段1名,字段2名) values (值1,值2);
    举例 : insert into person (name) values ("jerry");

批量插入格式

  • 全表插入格式 : insert into 表名 values (值1,值2),(值1,值2),(值1,值2);
    举例 : insert into person values("lucy",21),("lily",22);

  • 指定字段插入格式 : insert into 表名 (字段1名,字段2名) values (值1,值2),(值1,值2);
    举例 : insert into person (name) values ("danny"),("lilei"),("田浩");

中文问题 : set 字段名 gbk;

查询数据 : select 字段信息 from 表名 where 条件;

修改数据 : update 表名 set 字段名=值,字段名=值 where 条件;

1
2
3
update person set age = 88 where name = "刘备";
update person set name = "黎明" where age = 5;
update person set name = "张飞",age = 18 where name = "关羽";

删除数据 : delete from 表名 where 条件;

主键约束

主键 : 表示数据唯一性的字段称为主键

约束 : 创建表时,给表字段添加的限制条件

主键约束 : 限制主键的值唯一且非空

举例:

1
2
3
4
5
6
create database day2db charset = utf8;
use day2db;
create table t1 (id int primary key,name varchar(50));
insert into t1 values (1,"aaa");
insert into t1 values (1,"bbb"); //报错,主键值不能重复
insert into t1 values (null,"ccc"); //报错,主键值不能为空

主键约束+自增

如何使用 :

1
2
3
4
5
6
7
create table t2 (id int primary key auto_increment,name varchar(50));
insert into t2 values (null,"aaa");
insert into t2 values (null,"bbb");
insert into t2 values (10,"ccc");
insert into t2 values (null,"ddd");
delete from t2 where id >= 10;
insert into t2 values (null,"eee");

数据类型

整数 : int(m) 和 bigint(m) , bigint相当于Java中的long , m代表显示长度 , m=5 , 存18会得到00018 , 需要结合zerofill关键字使用
浮点数 : double(m,d) , m代表总长度 , d代表小数长度 , m=5 , d=3 , 23.123

字符串 : char(m) : 固定长度 , m=5 存 “abc” 占5个字符长度 , 执行效率略高 , 当存储内容长度固定时使用 , 比如 : 性别 , m最大值255
varchar(m) : 可变长度 , m=5 存 “abc” 占3个字符长度 , 更节省存储空间 , m最大值65535 , 长度在255以内的建议使用
text(m) : 可变长度 , m最大值65535 , 建议长度大于255时使用

日期 :
date : 保存年月日

time : 保存时分秒

Timestamp : (时间戳 , 距离1970年1月1日的毫秒数) : 保存年月日时分秒,默认值为当前系统时间

测试 :

1
2
3
create table t5(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t5 values("2022-08-28",null,null,null);
insert into t5 values(null,"16:46:50","2022-08-28 16:46:50",null);

去重distinct : select distinct 字段名 from 表名;

is null 和 is not null

is null 是空 select 字段名 from 表名 where 字段名 is null;

is not null 不是空 select 字段名 from 表名 where 字段名 is not null;

and 和 or

  • 多个条件同时满足时使用and
  • 多个条件满足一个就可以时 , 使用or
    select * from 表名 where 字段名 = 1 and 字段名 > 2000;
    select * from 表名 where 字段名 = 3 or 字段名 = 5000;

比较运算符   > < >= <= = !=和<>

between x and y 两者之间

1
select * from 表名 where 字段名x >= 2000 and 字段名y <= 3000;
1
select * from 表名 where 字段名 between 2000 and 3000;

不在两者之间

1
select * from 表名 where 字段名 not between 2000 and 3000;

in关键字

当查询某个字段的值为多个值的时候使用in

查询字段等于5000,1500,3000的员工信息

1
select * from 表名 where 字段名 in (5000,1500,3000);
1
select * from 表名 where 字段名 = 5000 or 字段名 = 1500 or 字段名 = 3000; //查询效率更高

模糊查询 like

  • % : 代表0或多个未知字符
  • _ : 代表1个未知字符
  • 举例 :
        以x开头                 x%
    以x结尾                 %x
    包含x                     %x%
    第二个字符是x     x%
    以x开头以y结尾   x%y
    第二个是x倒数第三个是y     x%y

查询姓孙员工信息

1
select * from 表名 where  字段名 like "孙%";

查询名字以精结尾的员工姓名

1
select 字段名 from 表名 where 字段名 like "%精";

查询工作第二个字是售的员工姓名和工作

1
select name,job from emp where job like "_售%";

排序order by

  • 格式: order by 字段名 asc(默认升序)/desc(降序);
    ​
  • description 描述

descend 降序

查询员工姓名和工资,按照工资升序

1
select name,sal from emp order by sal;

查询员工姓名和工资,按照工资降序

1
select name,sal from emp order by sal desc;

查询每个员工的姓名,工资和部门id按照部门id升序排序,如果部门id相同则按照工资降序排序

1
select name,sal,dept_id from emp order by dept_id,sal desc;

分页查询

  • 格式 : limit 起始下标,请求的条数(每页的条数)
    ​

  • 举例 : 起始下标 = (请求页数-1) * 每页条数

    查询第1页的5条数据(1-5条)        limit 0, 5  
    ​
    查询第2页的5条数据(6-10条)      limit 5, 5
    ​
    请求第1页的10条数据             limit 0,10
    ​
    请求第3页的10条数据             limit 20,10
    ​
    请求第8页的10条数据             limit 70,10
    ​
    请求第6页的8条数数据            limit 40,8

查询按照工资升序排序的第一页的3条数据

1
select * from emp order by sal limit 0,3;

按照入职日期(hiredate) 升序排序 查询第3页的3条数据

1
select * from emp order by hiredate limit 6,3;

查询工资最高的员工信息

1
2
3
select * from emp order by sal desc limit 0,1;
```SQL
select * from emp order by sal desc limit 1; //必须是第一页的第一条

查询按照工资降序第2页的5条数据

1
select * from emp order by sal desc limit 5,5;

别名

给查询字段起别名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select name from emp;

select name as` "姓名" `from emp;

select name` "姓名" `from emp;

select name` 姓名 `from emp;
````

**数值计算 + - \* / %**

查询每个员工的姓名 , 工资和年终奖(5个月的工资)

```SQL
select name,sal,sal*5 年终奖 from emp;

给3号部门的员工每人涨薪5块钱

1
update emp set sal = sal + 5 where dept_id = 3;

聚合函数

平均值avg(字段名)
查询1号部门的平均工资

1
select avg(sal) from emp where dept_id = 1;

最大值max(字段名)
查询程序员的最高工资

1
select max(sal) from emp where job = "程序员";

最小值min(字段名)
查询3号部门的最低工资

1
select min(sal) from emp where dept_id = 3;

求和sum(字段名)
查询2号部门的工资总和

sum(sal) from emp where dept_id 
1
2
3
4
5
6
```

**计数count(*)**
查询程序员的数量
```SQL
select count(*) from emp where job = "程序员";

分组查询group by

将某个字段相同值的数据划分为一组 , 然后以组为单位进行统计查询

查询每个部门的平均工资

1
select dept_id,avg(sal) from emp group by dept_id;

查询每种工作的最高工资

1
select job,max(sal) from emp group by job;

查询每个部门的最高工资

1
2
3
4
5
6
select dept_id,max(sal) from emp group by dept_id;
`

查询每种工作的人数
```SQL
select job,count(*) from emp group by job;

查询每个部门工资高于2000的人数

1
select dept_id,count(*) from emp where sal > 2000 group by dept_id;

查询每个部门有领导的员工的人数

1
select dept_id,count(*) from emp where manager is not null group by dept_id;

having关键字

where后面只能写普通字段的条件,不能写聚合函数

1
2
3
4
5
6
7
select dept_id,avg(sal) from emp where avg(sal) > 2000 group by dept_id; //报错
````
having后面可以包含聚合函数的条件 , 需要和group by结合使用 , 写在group by的后面

查询每个部门的平均工资 , 要求平均工资大于2000
```SQL
select dept_id,avg(sal) from emp group by dept_id having avg(sal) > 2000;

查询每种工作的人数 , 只查询人数大于1的

1
select job,count(*) from emp group by job having count(*) > 1;
1
select job,count(*) c from emp group by job having c > 1;

查询每个部门的工资总和 , 只查询有领导的员工 , 并且要求工资总和大于5400

1
select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s > 5400;

查询每个部门的平均工资 , 只查询工资在1000到3000之间的 , 并且过滤掉平均工资低于2000的

1
select dept_id,avg(sal) a from emp where sal between 1000 and 3000 group by dept_id having a >= 2000;

各个关键字的顺序

1
select * from` 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 desc limit 起始下标,请求条数;

子查询(嵌套查询)

use empdb;

查询工资大于2号部门平均工资的员工信息

1
select avg(sal) from emp where dept_id = 2;
1
select * from emp where sal > (select avg(sal) from emp where dept_id = 2);

查询工资高于程序员最高工资的员工信息

1
select max(sal) from emp where job = "程序员";
1
select * from emp where sal > (select max(sal) from emp where job = "程序员");

查询工资最高的员工信息

1
select * from emp where sal = (select max(sal) from emp);

查询和孙悟空相同工作的员工信息

1
select * from emp where job = (select job from emp where name = "孙悟空") and name != "孙悟空";

查询拿最低工资员工的同事们的信息(同事指同一部门)

1
select dept_id from emp where sal = (select min(sal) from emp);
1
select * from emp where dept_id = (select dept_id from emp where sal = (select min(sal) from emp)) and sal != (select min(sal) from emp);

关联查询

查询存在关联关系的表的查询方式称为关联查询

关联查询的方式包括 : 等值连接 , 内连接 , 外连接

等值连接

格式 : select * from A,B where 关联关系;

查询每个员工的姓名和对应的部门名称

1
select e.name,d.name from emp e,dept d where e.dept_id = d.id;

查询工资高于2000的员工的姓名和对应的部门名称

1
select e.name,d.name from emp e,dept d where e.dept_id = d.id and sal > 2000;

内连接

格式: select * from A join B on 关联关系 where 其他条件;

查询工资高于2000的员工的姓名和对应的部门名称

1
select e.name,d.name from emp e join dept d on e.dept_id = d.id where sal > 2000;

外连接

等值连接和内连接查询到的是两个表的交集数据

外连接查询到的是一张表的全部和另外一张表的交集

格式: select * from A left / right join B on 关联关系;

1
insert into emp(name,sal) values ("灭霸",5);

查询所有员工姓名和对应的部门名称

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

查询所有部门的名称 , 地址 , 和对应的员工姓名 , 工资

1
select d.name,loc,e.name,sal from emp e right join dept d on e.dept_id = d.id;
1
select d.name,loc,e.name,sal from dept d left join emp e on e.dept_id = d.id;

关联查询总结

如果需要同时查询多张表的数据使用关联查询

关联查询包括 : 等值连接,内连接和外连接

等值连接和内连接查询的是两个表的交集数据 , 推荐使用内连接

如果需要查询一张表的全部和另外一张表的交集时 , 使用外连接 , 只需要掌握左外即可,因为表的位置可以交换

清除空格符

清除 char(9) \t制表符

1
update `表名` set 字段名 = REPLACE(字段名,char(9),'')

清除 char(10) \r换行符

1
update `表名` set 字段名 = REPLACE(字段名,char(10),'')

清除 char(13) 回车符

1
update `表名` set 字段名 = REPLACE(字段名,char(13),'')

字符前的空格，用ltrim(字段名)

1
update 表名 set `字段名`=ltrim(字段名)

字符后的空格，用rtrim(字段名)

1
update 表名 set `字段名`=rtrim(字段名)

字符中的空格，用replace(字段名, ’ ', ’ ')

1
update 表名 set `字段名`=replace(字段名,' ','')