数据库基本操作

如何连接数据库执行SQL语句

执行SQL语句需要先和数据库软件建立链接之后

从开始菜单中找到MariaDB或MySQL,然后打开找到里面的MySQL Client 打开 , 然后输入密码后回车

退出指令: exit
登录指令: mysql -uroot -p

导入*.sql批处理文件

从老师工程中得到emp.zip 从idea中复制粘贴到某个磁盘的根目录 ,然后右键解压到当前文件夹, 在根目录下出现emp.sql文件
在客户端中执行 source f:/emp.sql; source +路径
执行以下SQL语句检查是否成功

show tables; //检查是否出现了 emp和dept两个表
select * from emp; //检查是否出现了数据, 如果出现乱码执行set names utf8; 如果格式错乱正常

数据库和表的概念

在MySQL数据库软件中保存数据,需要先建库,然后在库里面建表,然后把数据保存到表中

常用SQL语句:

以;号结尾
关键字不区分大小写
可以有空格或换行但一定要以;结尾
库SQL语句
创建数据库

格式: create database 数据库名 charset=utf8/gbk;
举例:
- create database db1;
- create database db2 charset=utf8;
- create database db3 charset=gbk;
  删除数据库
格式: drop database 数据库名;
举例:
- drop database db3;
- drop database db2;
- show databases;
  查看数据库信息
格式: show create database 数据库名;
举例:
- show create database db1;
- show create database db2;
  查询所有数据库
格式: show databases;

使用数据库
执行表相关和数据相关的SQL语句之前必须先使用了某个数据库
格式: use 数据库名;
举例:
- use db1;
  表SQL语句
执行表相关的SQL语句必须已经使用了某个数据库 use db1;

表格整体

创建表
格式: create table 表名(字段1名类型,字段2名类型,…….);（基本类型只有varchar（），int）
举例:
- create table person(name varchar(50),age int);
- create table student(name varchar(50),chinese int,math int,english int)charset=utf8;
- create table t2(id int primary key auto_increment,name varchar(50))charset=utf8;
  删除表
格式: drop table 表名;

修改表名
格式: rename table 原名 to 新名;
举例:
- rename table student to stu;
  查询所有表
格式: show tables;

查询表信息
格式: show create table 表名;
举例:
- show create table emp;
  查询表字段
格式: desc 表名;

表字段
都要锁定表格操作alter table 表名 (alter改变)

添加表字段

最后面添加格式: alter table 表名 add 字段名类型;
最前面添加格式: alter table 表名 add 字段名类型 first;
在xxx字段后面添加: alter table 表名 add 字段名类型 after xxx;

举例:
- alter table emp add gender varchar(5);
- alter table emp add id int first;
- alter table emp add dept varchar(20) after name;
  删除表字段
格式: alter table 表名 drop 字段名;
举例:
alter table emp drop dept;

修改表字段
格式: alter table 表名 change 原名新名新类型;
举例:
alter table emp change job dept varchar(5);

查询表字段
格式: desc 表名;

表内容

添加数据
全表插入格式: insert into 表名 values(值1,值2);
指定字段插入格式: insert into 表名 (字段1名,字段2名) values(值1,值2);
举例:
- insert into person values(‘tom’,18); （全插单个）
- insert into person (name) values(‘jerry’); （局部插一个）
- insert into person values(‘aaa’,10),(‘bbb’,20),(‘ccc’,30); （全插多个）
- insert into person (name) values(‘xxx’),(“yyy”),(“zzz”); （局部插多个）
  删除数据
格式: delete from 表名 where 条件;
举例:
- delete from person where name=’张飞’;
- delete from person where age<30;
- delete from person; （删除表中所有内容）
  修改数据
格式: update 表名 set 字段名=值 where 条件;
举例:
update person set age=88 where name=’刘备’;
update person set name=’张飞’,age=18 where name=’关羽’;
update person set name=’黎明’ where age=5;

查询数据
格式: select 字段信息 from 表名 where 条件;
举例:
- select name from person; （查单个）
- select name,age from person; （查多个）
- select * from person; （查所有）
- select * from person where age=50; （按条件查所有）
- select age from person where name=”悟空”; （按条件查部分）
  主键约束
主键: 表示数据唯一性的字段称为主键
约束: 创建表时给表字段添加的限制条件
主键约束: 限制主键的值唯一且非空
举例:
- create table t1 (id int primary key,name varchar(50)) charset=utf8;
- insert into t1 values(1,”aaa”); (必须要设置主键)
- insert into t1 values(1,”bbb”); //报错主键值重复
- insert into t1 values(null,”ccc”); //报错主键没有设置自增
  主键约束+自增
自增规则: 从历史最大值基础上+1, 删除之前字段或者人为跨字段,都会刷新历史最大值
举例:
create table t2(id int primary key auto_increment,name varchar(50))charset=utf8;
insert into t2 values(null,”aaa”); (主键值可为null,会自动按历史最大值添加)
insert into t2 values(null,”bbb”);
insert into t2 values(10,”ccc”); (主键值可为固定值,就是你输入的值,但是不能重复)
insert into t2 values(null,”ddd”);//11,ddd (主键值可为null,会自动按历史最大值添加)
delete from t2 where id>=10;
insert into t2 values(null,”eee”);//12,eee (主键值可为null,会自动按历史最大值添加)
SQL语句分类

DDL: 数据定义语言,包括数据库相关和表相关的SQL语句
DML: 数据操作语言, 包括增删改查
DQL: 数据查询语言, 只包含select查询相关的SQL语句
TCL: 事务控制语言
DCL: 数据控制语言
数据类型
整数:
格式: int(m)和bigint(m) m代表显示长度(超出可正常显示) zerofill表示不足用0补满onefill不行哟
举例:

create table t3(age int(5) zerofill); //m=5
insert into t3 values(18); //查询得到00018
create table t3(age int(5) ); //m=5
insert into t3 values(18); //查询得到18
insert into t3 values(111118); //查询得到111118

浮点数:
格式: double(m,d) m代表总长度,d代表小数长度 , 存23.212 m=5 d=3
举例:
create table t5(price double(5,3));
insert into t5 values(23.32123); //显示为23.321, 超出部分四舍五入
insert into t5 values(233.32123); //报错整数部分应该是5-3位,超出了就会报错.

字符串:
char(m) 固定长度, m=10 存abc 占10, 执行效率略高, 当保存数据的长度相对固定时使用, 最大值255
varchar(m) 可变长度,m=10 存abc 占3,更节省空间, 最大值65535 但推荐保存短的数据(255以内)
text(m) 可变长度, 最大值65535,建议保存长度大于255的

日期:
格式:
date, 只能保存年月日
time, 只能保存时分秒
datetime, 保存年月日时分秒, 默认值为null , 最大值 9999-12-31
timestamp(时间戳),保存年月日时分秒,默认值为当前系统时间,最大值 2038-1-19 (后期代码控制值)

举例:

create table t6 (t1 date, t2 time, t3 datetime, t4 timestamp);
insert into t6 values (“2022-5-15”, null, null, null);
insert into t6 values ( null, “14:20:25”, “2011-10-22 10:20:30”, null);

精细化查询
select 所需内容 from 表名 + 内容

去重distinct
举例:
查询员工表中所有不同的工作 : select distinct job from emp;
查询员工表中出现了哪几个不同的部门id? : select distinct dept_id from emp;

is null和is not null
举例:
查询有领导的员工姓名和领导id : select name,manager from emp where manager is not null;
查询没有领导的员工姓名 : select name from emp where manager is null;

and 和 or
查询1号部门工资高于2000的员工信息 : select from emp where dept_id=1 *and sal>2000;
查询3号部门或工资等于5000的员工信息 : select from emp where dept_id=3 *or sal=5000;
查询出孙悟空和猪八戒的员工信息 : select from emp where name=”孙悟空” *or name=”猪八戒”;

比较运算符
格式:
< >= <= = !=和<>(小于且大于)

举例:

查询工资大于等于3000的员工信息 : select from emp where sal*>=3000;
查询工作不是程序员的员工信息(两种写法):
- select from emp where job*!=“程序员”;
- select from emp where job*<>“程序员”;
  between x and y
  格式:
between x and y 两者之间(包括xy的值)

举例:

查询工资在2000到3000之间的员工信息:
- select * from emp where sal=>2000 and sal<=3000;
- select * from emp where sal between 2000 and 3000;
- select * from emp where sal not between 2000 and 3000;
  in关键字
  格式 : in(内容1,内容2,…)
  举例 :
查询工资等于5000,1500,3000的员工信息
- select * from emp where sal=5000 or sal=1500 or sal=3000;
- select from emp where sal* in(5000,1500,3000);
查询工资不等于5000,1500,3000的员工信息
- select from emp where sal *not in(5000,1500,3000);
  模糊查询
  格式 :
like “不确定内容”
%: 代表0或多个未知字符
_: 代表1个未知字符

举例 :

以x开头 : x%
以x结尾 : %x
包含x : %x%
第二个字符是x : _x%
以x开头以y结尾 : x%y
第二个是x倒数第三个是y : x%y_
查询名字姓孙的员工信息 : select from emp where name* like “孙%”;
查询名字以精结尾的员工姓名 : select name from emp where name like “%精”;
查询工作第二个字是售的员工姓名和工作 : select name,job from emp where job like “_售%”;
查询名字中包含僧并且工资大于2000的员工姓名和工资 ::select name,sal from emp where name like “%僧%” and sal>2000;

排序查询
格式:
order by 字段名 asc(升序默认)/desc(降序)

举例:

查询所有员工姓名和工资并按照工资升序排序 :
- select name,sal from emp order by sal;
- select name,sal from emp order by sal asc;
查询所有员工姓名和工资并按照工资降序排序 :
- select name,sal from emp order by sal desc;
查询所有员工姓名,工资和部门id并且按照部门id升序排序,如果部门id一致则按照工资降序排序 :
- select name,sal,dept_id from emp order by dept_id,sal desc;
  分页查询
  格式:
limit 跳过的条数,请求的条数(每页的条数), 其中跳过的条数=(请求的页数-1)*请求的条数(每页条数)

举例:

查询工资最低的3个员工信息( 查询按照工资升序排序的第一页的3条数据 )
- select from emp order by sal *limit 0,3;
按照入职日期(hiredate) 升序排序查询第3页的3条数据
- select from emp order by hiredate *limit 6,3;
查询工资最高的员工信息
- select from emp order by sal desc *limit 0,1;
查询按照工资降序第2页的5条数据
- select from emp order by sal desc *limit 5,5;
  分组查询
  格式:
  group by 分组的字段名
  举例:
查询每个部门的平均工资
- select dept_id,avg(sal) from emp group by dept_id;
查询每个部门的最高工资
- select dept_id,max(sal) from emp group by dept_id;
查询每种工作的最高工资
- select job,max(sal) from emp group by job;
查询每种工作的人数
- select job,count() from emp *group by job;
查询每个部门工资高于2000的人数
- select dept_id,count() from emp where sal>2000 *group by dept_id;
查询每个部门有领导的员工的人数
- select dept_id,count() from emp where manager is not null *group by dept_id;
  别名
select name as “姓名” from emp ;
select name “姓名” from emp ;
select name 姓名 from emp ;

聚合函数
通过聚合函数可以对查询的多条数据进行统计查询,统计查询的方式包括 :
求平均值, 求最大值,求最小值,求和,计数

平均值avg(字段名)
查询1号部门的平均工资 : select avg(sal) from emp where dept_id=1;
查询销售的平均工资 : select avg(sal) from emp where job=”销售”;

最大值max(字段名)
查询程序员的最高工资 : select max(sal) from emp where job=”程序员”;

最小值min(字段名)
查询3号部门的最低工资 : select min(sal) from emp where dept_id=3;

求和sum(字段名)
查询2号部门的工资总和 : select sum(sal) from emp where dept_id=2;

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

聚合函数的引用
where后面只能写普通字段的条件,不能包含聚合函数,但是having后面可以包含聚合函数的条件,需要和group by结合使用,写在group by的后面
举例:
查询每个部门的平均工资要求平均工资高于2000
- select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000;
查询每种工作的人数,只查询人数大于1 的
- select job,count() from emp group by job **having count()>1**;
- select job,count() c from emp group by job *having c>1;
询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400
- select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s>5400;
查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的
- select dept_id,avg(sal) a from emp where sal between 1000 and 3000 group by dept_id having a>=2000;
  数值计算 + - * / %
查询每个员工的姓名,工资和年终奖(年终奖=5个月的工资) : select name,sal,sal*5 年终奖 from emp;
给3号部门的员工每人涨薪5块钱 : update emp set sal=sal+5 where dept_id=3;
查询条件的书写顺序

select 查询的字段信息
from 表名
where 普通字段条件
group by 分组字段名
having 聚合函数条件
order by 排序字段名
limit 跳过条数,请求条数;
子查询(嵌套查询)
当查询的条件需要另一个查询结果作为依据的时候,可以将另一个查询语句放在小括号里面直接使用

举例:
查询工资大于2号部门平均工资的员工信息
- select avg(sal) from emp where dept_id=2;
- select * from emp where sal>(select avg(sal) from emp where dept_id=2);
查询工资高于程序员最高工资的员工信息
- select max(sal) from emp where job=”程序员”;
- select * from emp where sal>(select max(sal) from emp where job=”程序员”);
查询工资最高的员工信息
- select max(sal) from emp;
- select * from emp where sal=(select max(sal) from emp);
查询和孙悟空相同工作的员工信息
- select job from emp where name=”孙悟空”;
- select * from emp where job=(select job from emp where name=”孙悟空”) and name!=”孙悟空”;
查询拿最低工资员工的同事们的信息(同事指同一部门)
- select min(sal) from emp;
- select dept_id from emp where sal=(select min(sal) from emp);
- 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);
  精细化查询小试身手:

查询工资大于等于3000的员工姓名和工资
select name,sal from emp where sal>=3000;
2. 查询1号部门的员工姓名和工作
select name ,job from emp where deptid=1;
3. 查询不是程序员的员工姓名和工作(两种写法)
select name,job from emp where name !=”程序员”;
select name,job from emp where name<>”程序员”;
4. 查询奖金等于300的员工姓名,工资和工作
select name,sal,job from emp where comm = 300;
5. 查询1号部门工资大于2000的员工信息
select from emp where dept_id=1 and sal>2000;
6. 查询3号部门或工资等于5000的员工信息
select from emp where dept_id =3 or sal=5000;
7. 查询出CEO和项目经理的名字
select name from emp where job=”CEO” or job =”项目经理”;
8. 查询工资为3000,1500和5000的员工信息
select from emp where sal in(3000,1500,5000);
9. 查询工资不等于3000,1500和5000的员工信息
select from emp where sal not in(3000,1500,5000);
10. 查询工资在1000到2000之间的员工信息
select from emp where sal between 1000 and 2000;
11. 查询工资在1000到2000以外的员工信息
select from emp where sal<1000 or sal >2000;
12. 查询有领导的员工姓名和领导id
select name,manager from emp where manager is not null;
13. 查询没有领导的员工姓名和领导id
select name,manager from emp where manager is null;
14. 查询员工表中出现了哪几种不同的工作
select distinct job from emp ;
15. 查询员工表中出现了那几个部门的id
select distinct dept_id from emp;
16. 查询姓孙的员工姓名
select name from emp where name like “孙%”;
17. 查询名字最后一个字是精的员工信息
select from emp where name like “%精”;
18. 查询工作中包含销售的员工信息
select from emp where job like “%销售%”;
19. 查询工作中第二个字是售的员工信息
select *from emp where job like “售%”;
20. 查询名字中包含僧的员工并且工资高于2000的员工信息
select from emp where name like “%僧%” and sal >2000;
21. 查询1号和2号部门中工作以市开头的员工信息
select from emp where job like “市%”and dept_id in (1,2);
22. 查询所有员工的姓名和工资按照工资升序排序
select name,sal from emp order by sal asc;
23. 查询所有员工的姓名和工资按照工资降序排序
select name,sal from emp order by sal desc;
24. 查询所有员工姓名工资和部门id 按照部门id降序排序,如果部门id一致则按照工资升序排序
select name,sal,dept_id from emp order by dept_id desc order by sal;(错的)
select name,sal,dept_id from emp order by dept_id desc,sal asc;
25. 查询员工表中3号部门工资高于1500的员工信息
select from emp where dept_id =3 and sal >1500;
26. 查询2号部门员工或者没有领导的员工信息
select from emp where dept_id =2 or manager is null;
27. 查询有领导的员工姓名,工资按照工资降序排序
select name from emp where manager is not null order by sal desc;
28. 查询2号和3号部门的员工姓名和入职日期hiredate 按照入职日期降序排序
select name ,hiredate from emp where dept_id in (2,3) order by hiredate desc;
29. 查询名字中包含僧和包含精的员工姓名
select name from emp where name like “%僧%” or name like “%精%”;
30. 查询工资高于2000的工作有哪几种?
select distinct job from emp where sal >2000;
31. 查询工资最高的前三个员工
select from emp order by sal desc limit 0,3;
32. 查询员工表按照id排序, 第2页的5条数据
select from emp order by id limit 5,5;
33. 查询员工表按照id排序, 第3页的4条数据
select from emp order by id limit 8,4;
34. 查询3号部门工资最低的员工姓名和工资
select name,min(sal) from emp ;
35. 查询工作不是人事的员工中工资降序第二页的3条数据
select from emp where job !=”人事” order by sal desc limit 3,3;
36. 查询每个员工的姓名,工资和年终奖(年终奖=5个月的工资)
select name,sal, sal5 年终奖 from emp;
37. 给3号部门所有员工涨薪5块钱
update emp set sal=sal+5 where dept_id=3;
select from emp ;
38. 查询没有领导的员工和3号部门的员工,工资降序取前三条
select from emp where manager is null or dept_id =3 order by sal desc limit 0,3;
39. 查询2号部门的最高工资
select max(sal) from emp where dept_id =2;
40. 查询有领导的员工中工资在1000到2000之间的人数
select count() from emp where manager is not null and sal between 1000 and 2000;
41. 查询3号部门的工资总和
select sum(sal) from emp where dept_id =3;
42. 查询程序员和销售的总人数
select count() from emp where job=”程序员” or job = “销售”;
select count() from emp where job in (“程序员”,”销售”);
43. 查询1号部门有领导的员工的平均工资
select avg(sal) from emp where manager is not null and dept_id=1;
44. 查询1号部门的最低工资和最高工资
select min(sal) 最低工资,max(sal) 最高工资 from emp where dept_id=1;
45. 查询和销售相关的工作人数
select count() from emp where job like “%销售%”;
46. 查询工资不是1500和3000的员工人数
select count() from emp where sal not in(1500,3000);
47. 查询1号部门出现了哪几种工作
select distinct job from emp where dept_id = 1;
关联关系

指创建的表和表之间存在的业务关系

关系分类

一对一:
有AB两张表,A表中的一条数据对应B表中的一条数据, 同时B表中的一条数据也对应A表中的一条数据
一对多:
有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据对应A表中的一条数据
多对多:
有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据也对应A表中的多条数据
外键

一对一:
在任意表中添加一个建立关系的字段指向另外一张表的主键 .

一对多:
在多的表中添加建立关系的字段(外键) 指向另外一张表的主键 .

多对多:
需要创建一个单独的关系表,里面至少包含两个字段分别指向另外两个表的主键.

关联查询
同时查询多张表数据的查询方式称为关联查询
关联查询包括: 等值链接, 内连接和外连接

等值链接
格式:
select *from A,B where 关联关系

举例:

查询工资高于2000的员工姓名和对应的部门名
- select e.name,d.name,sal
- from emp e,dept d where e.dept_id=d.id and sal>2000;
  内连接
  格式:
select *from A join B on 关联关系

举例:

查询工资高于2000的员工姓名和对应的部门名
- select e.name,d.name,sal
- from emp e join dept d on e.dept_id=d.id where sal>2000;
  外连接
等值链接和内连接查询到的都是两张表的交集数据 ,但是外连接查询的是一张表的全部和另外一张表的交集数据

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

查询所有员工姓名和对应的部门名
- select e.name,d.name
- from emp e left join dept d on e.dept_id=d.id;
查询所有部门的名称,地点和对应的员工姓名和工资
- select d.name,loc,e.name,sal
- from emp e right join dept d on e.dept_id=d.id;
  总结:

如果需要同时查询多张表的数据使用关联查询
关联查询包括:等值链接,内连接和外连接
等值链接和内连接查询的是两个表的交集数据, 推荐使用内连接
如果需要查询一张表的全部和另外一张表的交集时使用外连接,只需要掌握左外即可,因为表的位置可以交换
多对多查询
查表只能同时查两个,多对多的时候会建立中间的关联表,通过其中一张与关联变建立连接,然后在于另外一张建立连接.
举例:

查询每个老师对应的学生
- select t.name,s.name
- from teacher t
- join t_s ts on t.id=ts.tid
- join student s on s.id=ts.sid;
查询苍老师的学生都有谁?
- select s.name
- from teacher t
- join t_s ts on t.id=ts.tid
- join student s on s.id=ts.sid
- where t.name=”苍老师”;
查询小明的老师是谁?
- select t.name
- from teacher t
- join t_s ts on t.id=ts.tid
- join student s on s.id=ts.sid
- where s.name=”小明”;

SQL语言

数据库基本操作

如何连接数据库执行SQL语句

导入*.sql批处理文件

数据库和表的概念

常用SQL语句:

库SQL语句

创建数据库

删除数据库

查看数据库信息

查询所有数据库

使用数据库

表SQL语句

表格整体

创建表

删除表

修改表名

查询所有表

查询表信息

查询表字段

表字段

添加表字段

删除表字段

修改表字段

查询表字段

表内容

添加数据

删除数据

修改数据

查询数据

主键约束

主键约束+自增

SQL语句分类

数据类型

整数:

浮点数:

字符串:

日期:

精细化查询

去重distinct

is null和is not null

and 和 or

比较运算符

between x and y

in关键字

模糊查询

排序查询

分页查询

分组查询

别名

聚合函数

平均值avg(字段名)

最大值max(字段名)

最小值min(字段名)

求和sum(字段名)

计数count(*)

聚合函数的引用

数值计算 + - * / %

查询条件的书写顺序

子查询(嵌套查询)

精细化查询小试身手:

关联关系

关系分类

一对一:

一对多:

多对多:

外键

一对一:

一对多:

多对多:

关联查询

等值链接

内连接

外连接

总结:

多对多查询