JAVA-WEB（三）：数据库

一、数据库

1. DMBS

DataBaseManagementSystem，数据库管理系统，俗称数据库软件

常见的DBMS：

• MySQL：Oracle公司产品，08年被Sun公司收购了，09年Sun公司被Oracle收购了，MySQL开源产品，市占率排名第一

• Oracle：Oracle公司产品，闭源产品，性能最强价格最贵，市占率排名第二

• SQLServer：微软公司产品，闭源产品，市占率第三

• DB2：IBM公司产品，闭源产品

• SQLite：轻量级数据库

2. SQL

Structured Query Language，结构化查询语言，通过此语言让程序员和数据库软件进行交流

刘德华 30 5000

insert into emp values("刘德华",30,5000);

3. 数据库和表的概念

在MySQL数据库软件中保存数据需要先建库再建表

二、SQL

1. 数据库相关的SQL

1. 查询所有数据库

格式: show databases;

2. 创建数据库

格式: create database 数据库名 charset=utf8/gbk;

举例:

create database db1;

create database db2 charset=utf8;

create database db3 charset=gbk;

show databases;

3. 查询数据库信息

格式: show create database 数据库名;

举例:

show create database db1;

show create database db2;

show create database db3;

4. 删除数据库

格式: drop database 数据库名;

举例:

drop database db3;

drop database db2;

show databases;

5. 使用数据库

使用完数据库之后再执行表相关或数据相关的SQL 否则会报错: No database selected

格式: use 数据库名;

举例:

use db1;

数据库相关练习题

1. 创建 mydb1和mydb2 数据库 字符集分别为utf8和gbk

create database mydb1 charset=utf8;

create database mydb2 charset=gbk;

2. 查询所有数据库检查是否创建成功

show databases;

3. 检查两个数据库的字符集是否正确

show create database mydb1;

show create database mydb2;

4. 先使用mydb2 再使用 mydb1

use mydb2;

use mydb1;

5. 删除两个数据库 drop database mydb1; drop database mydb2;

2. 表相关的SQL

执行表相关的SQL语句必须使用了某个数据库否则会报错

1. 创建表

格式: create table 表名(字段1名 类型,字段2名 类型) charset=utf8/gbk;

举例:

create table person(name varchar(50),age int);

create table car(name varchar(50),type varchar(5),price int);

create table student(name varchar(50),age int,chinese int,math int,english int)charset=gbk;

2. 查询所有表

格式: show tables;

3. 查询表信息

格式: show create table 表名;

举例:

        show create table person;

        show create table student;

4. 查询表字段信息

格式: desc 表名;

5. 删除表

格式: drop table 表名;

举例:

drop table car;

show tables;

6. 修改表名

格式: rename table 原名 to 新名;

7. 添加表字段

最后面添加格式: alter table 表名 add 字段名 类型;

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

在xxx字段的后面添加: alter table 表名 add 字段名 类型 after xxx;

举例:

create database db4;

use db4;

create table teacher(name varchar(20));

alter table teacher add age int;

alter table teacher add id int first;

alter table teacher add salary int after name;

8. 删除表字段

格式:

        alter table 表名 drop 字段名;

   alter table teacher drop salary;

9. 修改表字段

格式:

        alter table 表名 change 原名 新名 新类型;

 alter table teacher change age salary int;

表相关练习题(一):

1. 创建数据库mydb4 字符集utf8并使用

create database mydb4 charset=utf8;

use mydb4;

2. 创建员工emp表 有名字字段

create table emp(name varchar(50));

3. 添加表字段: 最后添加salary 最前面添加id , salary前面添加age年龄

alter table emp add salary int;

alter table emp add id int first;

alter table emp add age int after name;

4. 删除age字段

alter table emp drop age;

5. 修改表名为e

rename table emp to e;

6. 删除表

drop table e;

7. 删除数据库

drop database mydb4;

表相关练习题(二):

1. 创建数据库mydb3 字符集gbk 并使用

create database mydb3 charset=gbk;

use mydb3;

2. 创建t_hero英雄表, 有名字和年龄字段 默认字符集

create table t_hero(name varchar(20),age int);

3. 修改表名为hero

rename table t_hero to hero;

4. 查看表的字符集

show create table hero;

5. 查询表字段

desc hero;

6. 删除表 drop table hero;

7. 删除数据库 drop database mydb3;

3. 数据相关SQL

执行数据相关的SQL语句必须使用了某个数据库并且已经创建好了保存数据的表

create database db5 charset=utf8;

use db5;

create table person(name varchar(50),age int);

1. 插入数据

全表插入格式: insert into 表名 values(值1,值2);

指定字段插入格式: insert into 表名(字段1名,字段2名) values (值1,值2);

举例:

insert into person values("tom",30);

insert into person(name) values('jerry');

批量插入:

insert into person values("liubei",20),("guanyu",18),("zhangfei",15);

insert into person(name) values('libai'),('liucangsong');

插入中文:

insert into person values("刘德华",17);

如果执行上面的SQL语句报以下错误, 执行 set names gbk; 之后再次执行

2. 查询数据

格式:select 字段信息 from 表名 where 条件;

举例:

select name from person;

select name,age from person;

select * from person;

select * from person where age>20;

select age from person where name='tom';

select name from person where age=15;

3. 修改数据

格式: update 表名 set 字段名=值,字段名=值 where 条件;

举例:

update person set age=50 where name='libai';

update person set name='刘备' where age=20;

4. 删除数据

格式: delete from 表名 where 条件;

举例:

delete from person where name='刘德华';

delete from person where age<20;

delete from person where age is null;

综合练习题(一):

1. 创建数据库day1db 字符集utf8并使用

create database day1db charset=utf8;

use day1db;

2. 创建t_hero表, 有name字段 字符集utf8

create table t_hero(name varchar(50));

3. 修改表名为hero

rename table t_hero to hero;

4. 最后面添加价格字段money, 最前面添加id字段, name后面添加age字段

alter table hero add money int;

alter table hero add id int first;

alter table hero add age int after name;

5. 表中添加以下数据: 1,李白,50,6888 2,赵云,30,13888 3,刘备,25,6888

insert into hero values(1,'李白',50,6888),(2,'赵云',30,13888),(3,'刘备',25,6888);

6. 查询价格为6888的英雄名

select name from hero where money=6888;

7. 修改刘备年龄为52岁

update hero set age=52 where name='刘备';

8. 修改年龄小于等于50岁的价格为5000

update hero set money=5000 where age<=50;

9. 删除价格为5000的信息

delete from hero where money=5000;

10. 删除表, 删除数据库

drop table hero;

drop database day1db;

综合练习题(二):

1. 创建数据库newdb1, 字符集utf8并使用

create database newdb1 charset=utf8;

use newdb1;

2. 在数据库中创建员工表emp 字段:id,name,sal(工资),deptId(部门id) 字符集utf8

create table emp(id int,name varchar(50),sal int,deptId int);

3. 创建部门表dept 字段:id,name,loc(部门地址) 字符集utf8

create table dept(id int,name varchar(50),loc varchar(50));

4. 部门表插入以下数据: 1 神仙部 天庭 2 妖怪部 盘丝洞

insert into dept values(1,'神仙部','天庭'),(2,'妖怪部','盘丝洞');

5. 员工表插入一下数据: 1 悟空 5000 1 , 2 八戒 2000 1 ,3 蜘蛛精 8000 2 , 4 白骨精 9000 2

insert into emp values(1,'悟空',5000,1),(2,'八戒',2000,1),(3,'蜘蛛精',8000,2),(4,'白骨精',9000,2);

6. 查询工资6000以下的员工姓名和工资

select name,sal from emp where sal<6000;

7. 修改神仙部的名字为取经部

update dept set name='取经部' where name='神仙部';

8. 给员工表添加奖金comm字段

alter table emp add comm int;

9. 修改员工表中部门id为1的 奖金为500

update emp set comm=500 where deptId=1;

10. 把取经部的地址改成五台山

update dept set loc="五台山" where name='取经部';

11. 修改奖金字段为性别gender字段 类型为varchar(5)

alter table emp change comm gender varchar(5);

12. 修改孙悟空和猪八戒性别为男

update emp set gender="男" where deptId=1;

13. 删除没有性别的员工

delete from emp where gender is null;

14. 删除性别字段

alter table emp drop gender;

15. 删除表 和 删除数据库

drop table emp;

drop table dept;

drop database newdb1;

三、数据库知识与用法

1. 主键约束 primary key

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

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

主键约束: 唯一且非空

举例:

create database day2db charset=utf8;

use day2db;

create table t1(id int primary key,name varchar(20));

insert into t1 values(1,'aaa');

insert into t1 values(2,'bbb');

insert into t1 values(2,'ccc'); 报错:Duplicate entry '2' for key 'PRIMARY'

insert into t1 values(null,'ccc'); 报错: Column 'id' cannot be null

2. 主键约束+自增 auto_increment

自增规则: 从历史最大值+1

    create table t2(id int primary key auto_increment,name varchar(20));

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');

3. 导入 *.sql

1. 从老师工程的数据库文件夹中找到emp.zip 复制到某个盘的根目录,并解压到当前文件夹,得到emp.sql文件

2. 在命令行中连接上MySQL之后 执行 source 路径; 把文件导入到自己的MySQL数据库中

source D:/emp.sql;

3. show tables; 检查是否出现了两个表 分别是emp和dept

4. select * from emp; 如果查询时出现乱码 执行 set names utf8;

​​​​​​​4. 比较运算符 > < >= <= = != 和 <>

1. 查询工资小于等于3000的员工姓名和工资

select name,sal from emp where sal<=3000;

2. 查询程序员的名字

select name from emp where job='程序员';

3. 查询2号部门的员工姓名,工资和工作

select name,sal,job from emp where dept_id=2;

4. 查询不是人事的员工姓名和工作(两种写法)

select name,job from emp where job!="人事";

select name,job from emp where job<>"人事";

5. 与或非 and or not

and: 查询多个条件同时满足时使用

or : 查询多个条件满足一个条件时使用

not: 取反

1. 查询1号部门工资高于2000的员工信息

select * from emp where dept_id=1 and sal>2000;

2. 查询三号部门或工资等于5000的员工信息

select * from emp where dept_id=3 or sal=5000;

3. 查询有上级领导的员工姓名

select name from emp where manager is not null;

4. 查询出CEO和项目经理的名字

select name from emp where job='ceo' or job='项目经理';

5. 查询有奖金的销售名字和奖金

select name,comm from emp where comm>0 and job='销售';

6.between x and y 两者之间 包含x和y

1. 查询工资在2000到300之间的员工信息

select * from emp where sal between 2000 and 3000;

2. 查询工资在2000到3000以外的员工信息

select * from emp where sal not between 2000 and 3000;

7. in(x,y,z)

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

1. 查询工资等于3000,1500和5000的员工信息

select * from emp where sal in(3000,1500,5000);

1. 查询工作是程序员和销售的员工信息

select * from emp where job in('程序员','销售');

8. 去重 distinct

1. 查询1号部门中出现了哪几种不同的工作

select distinct job from emp where dept_id=1;

2. 查询员工表中出现了哪几种不同的部门id

select distinct dept_id from emp;

综合练习题

1. 查询2号部门工资高于1000的员工信息

select * from emp where dept_id=2 and sal>1000;

2. 查询3号部门或工资等于5000的员工信息

select * from emp where dept_id=3 or sal=5000;

3. 查询工资在1000到2000之间的员工姓名和工资

select name,sal from emp where sal between 1000 and 2000;

4. 查询工资不等于3000和5000的员工信息

select * from emp where sal not in(3000,5000);

5. 查询3号部门有哪几种不同的工作

select distinct job from emp where dept_id=3;

6. 查询1号部门没有领导的员工姓名

select name from emp where dept_id=1 and manager is null;

7. 查询出所有销售,人事和程序员

select * from emp where job in('销售','人事','程序员');

8. 查询出刘关张三个人的工资

select sal from emp where name in('刘备','关羽','张飞');

9. 模糊查询 like

_: 代表1个未知字符

%: 代表0或多个未知字符​​​​​​​​​​​​​​

举例:

​​​​​​​        以x开头 x%

​​​​​​​​​​​​​​​​​​​​​        以x结尾 %x

        以x开头y结尾x%y

        包含x %x%

        第二个字符是x _x%

        第三个是x倒数第二个是y __x%y_

1. 查询姓孙的员工姓名

select name from emp where name like "孙%";

2. 查询名字中包含僧的员工信息

select * from emp where name like "%僧%";

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

select name from emp where name like "%精";

4. 查询工作中包含销售并且工资大于1500的员工信息

select * from emp where job like "%销售%" and sal>1500;

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

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

6. 查询1号和2号部门中工作以市开头的员工信息

select * from emp where dept_id in(1,2) and job like "市%";

10. 排序order by

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

1. 查询每个员工的姓名和工资,按照工资升序

select name,sal from emp order by sal;

select name,sal from emp order by sal asc;

2. 查询每个员工的姓名和工资,按照工资降序

select name,sal from emp order by sal desc;

3. 查询工资高于2000的员工姓名和工资, 按照工资降序排序

select name,sal from emp where sal>2000 order by sal desc;

4. 查询姓名,工资和部门id 按照部门id升序排序,如果部门id一致则按照工资降序排序

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

综合练习题:

1. 查询有领导的员工信息,按照入职日期(hiredate) 升序排序

select * from emp where manager is not null order by hiredate;

2. 查询1号部门中名字中包含八的员工信息

select * from emp where dept_id=1 and name like "%八%";

3. 查询2号和3号部门中工资低于1500的员工信息

select * from emp where dept_id in(2,3) and sal<1500;

4. 查询人事和程序员中工资高于2500的员工姓名,工资和工作

select name,sal,job from emp where job in("人事","程序员") and sal>2500;

5. 查询不是CEO的员工中工资高于2000的员工姓名,工资和工作,并且按照工资降序排序

select name,sal,job from emp where job!="CEO" and sal>2000 order by sal desc;

11. 分页查询 limit

格式: limit 跳过的条数,请求的条数(每页的条数)

跳过的条数=(请求的页数-1)*每页的条数

举例:

        ​​​​​​​第1页的5条数据 limit 0,5

        第2页的5条数据 limit 5,5

        第3页的5条数据 limit 10,5

        第4页的10条数据 limit 30,10

        第8页的7条数据 limit 49,7

        第3页的9条数据 limit 18,9

1. 查询所有员工id,姓名和工资,按照工资升序排序请求第一页的5条数据

        select id,name,sal from emp order by sal limit 0,5;

2. 查询所有员工的姓名和工资,按照工资升序排序,请求第2页的5条数据

        select name,sal from emp order by sal limit 5,5;

3. 查询工资最高的员工信息

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

4. 按照入职日期排序 查询第2页的3条数据

        select * from emp order by hiredate limit 3,3

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

        select * from emp order by sal limit 4,2;

12. 别名

        select name as "名字" from emp;

        select name "名字" from emp;

        select name 名字 from emp;

综合练习题

1. 查询员工表中3号部门工资高于1500的员工信息

select * from emp where dept_id=3 and sal>1500;

1. 查询2号部门员工或者没有领导的员工信息

select * from emp where dept_id=2 or manager is null;

1. 查询有领导的员工姓名,工资按照工资降序排序

select name,sal from emp where manager is not null order by sal desc;

1. 查询2号和3号部门的员工姓名和入职日期hiredate按照入职日期降序排序

select name,hiredate from emp where dept_id in(2,3) order by hiredate desc;

1. 查询名字中包含僧和包含精的员工姓名

select name from emp where name like "%僧%" or name like "%精%";

1. 查询工资高于2000的工作有哪几种?

select distinct job from emp where sal>2000;

1. 查询工资升序第4页的2条数据

select * from emp order by sal limit 6,2;

13. 聚合函数

可以对查询到的多条数据进行统计查询

统计方式包括:

​​​​​​​        求平均值

        求和

        求最大值

        求最小值

        ​​​​​​​​​​​​​​计数

​​​​​​​1. 平均值avg(字段名)

        a. ​​​​​​​​​​​​​​​​​​​​​查询1号部门的平均工资

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

        b. 查询销售的平均工资

        select avg(sal) from emp where job='销售';

2. 最大值max(字段名)​​​​​​​

        查询最高工资

        select max(sal) from emp;

3. 最小值min(字段名)​​​​​​​

        查询最低工资

        select min(sal) from emp;

4. 求和sum(字段名) ​​​​​​​

        查询程序员的工资总和

        select sum(sal) from emp where job='程序员';

5. 计数count(*)

        查询销售的数量

        select count(*) from emp where job="销售";

聚合函数练习题

1. 查询销售的平均工资

select avg(sal) from emp where job="销售";

2. 查询程序员的最高工资

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

3. 查询名字包含精的员工人数

        select count(*) from emp where name like "%精%";

4. 查询和销售相关的工作一个月工资总和

        select sum(sal) from emp where job like "%销售%";

5. 查询2号部门的最高工资和最低工资起别名

select max(sal) 最高工资,min(sal) 最低工资 from emp where dept_id=2;

14. 分组查询 group by

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

1. 查询每个部门的人数

select count(*) from emp where dept_id=1;

select count(*) from emp where dept_id=2;

select count(*) from emp where dept_id=3;

select dept_id,count(*) from emp group by dept_id;

2. 查询每种工作的人数

select job,count(*) from emp group by job;

3. 查询每种工作的平均工资

select job,avg(sal) from emp group by job;

4. 查询每个部门的最高工资

select dept_id,max(sal) from emp group by dept_id;

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

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

6. 查询每种工作的最低工资

select job,min(sal) from emp group by job;

7. 查询1号部门和2号部门的人数

select dept_id,count(*) from emp where dept_id in(1,2) group by dept_id;

8. 查询平均工资最高的部门id和平均工资

select dept_id,avg(sal) from emp group by dept_id order by avg(sal) desc limit 0,1;

通过别名 复用

select dept_id,avg(sal) a from emp group by dept_id order by a desc limit 0,1;

having

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

having 后面专门用来写聚合函数条件, 而且having要和group by 结合使用 写在group by的后面

1. 查询每个部门的平均工资,只查询出平均工资大于2000

        select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000;

        select dept_id,avg(sal) a from emp group by dept_id having a>2000;

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

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

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

select dept_id,sum(sal) s from emp

where manager is not null group by dept_id having s>5400;

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

select dept_id,avg(sal) a from emp

where sal between 1000 and 3000 group by dept_id having a>=2000;

​​​​​​​

四、练习题

1. 查询工资大于等于3000的员工姓名和工资 

2. 查询1号部门的员工姓名和工作

3. 查询不是程序员的员工姓名和工作(两种写法)

4. 查询奖金等于300的员工姓名,工资和工作  

5. 查询1号部门工资大于2000的员工信息

6. 查询3号部门或工资等于5000的员工信息

7. 查询出CEO和项目经理的名字

8. 查询工资为3000,1500和5000的员工信息

9. 查询工资不等于3000,1500和5000的员工信息

10. 查询工资在1000到2000之间的员工信息

11. 查询工资在1000到2000以外的员工信息

12. 查询有领导的员工姓名和领导id

13. 查询没有领导的员工姓名和领导id

select name,manager from emp where manager is null;

14. 查询员工表中出现了哪几种不同的工作

15. 查询员工表中出现了那几个部门的id

16. 查询姓孙的员工姓名

17. 查询名字最后一个字是精的员工信息

18. 查询工作中包含销售的员工信息

19. 查询工作中第二个字是售的员工信息

20. 查询名字中包含僧的员工并且工资高于2000的员工信息

21. 查询1号和2号部门中工作以市开头的员工信息

22. 查询所有员工的姓名和工资 按照工资升序排序

23. 查询所有员工的姓名和工资 按照工资降序排序

24. 查询所有员工姓名 工资和部门id 按照部门id降序排序,如果部门id一致则按照工资升序排序

25. 查询员工表中3号部门工资高于1500的员工信息

26. 查询2号部门员工或者没有领导的员工信息

27. 查询有领导的员工姓名,工资按照工资降序排序

28. 查询2号和3号部门的员工姓名和入职日期hiredate 按照入职日期降序排序

29. 查询名字中包含僧和包含精的员工姓名

30. 查询工资高于2000的工作有哪几种?

31. 查询工资最高的前三个员工

32. 查询员工表按照id排序, 第2页的5条数据

33. 查询员工表按照id排序, 第3页的4条数据

34. 查询3号部门工资最低的员工姓名和工资

35. 查询工作不是人事的员工中工资降序第二页的3条数据    

36. 查询没有领导的员工和3号部门的员工,工资降序取前三条

37. 查询2号部门的最高工资 

40. 查询有领导的员工中工资在1000到2000之间的人数

41. 查询3号部门的工资总和  

42. 查询程序员和销售的总人数 

43. 查询1号部门有领导的员工的平均工资 

44. 查询1号部门的最低工资和最高工资

45. 查询和销售相关的工作人数

46. 查询工资不是1500和3000的员工人数

47. 查询1号部门出现了哪几种工作

48. 查询名字包含精的员工数量

49. 查询和销售相关的工作一个月工资总和

50. 查询2号部门的最高工资和最低工资起别名

51. 查询每个部门的平均工资

52. 查询每种工作的平均工资

53. 查询每个部门的最高工资

54. 查询每种工作的最低工资

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

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

57. 查询1号部门每种工作的最低工资

58. 查询平均工资最高的部门id和平均工资

59. 查询每个部门的平均工资,要求平均工资大于2000

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

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

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

---

总结

本篇的内容为数据库，包括（数据库、表、数据）相关的SQL语法、数据库知识与用法等知识。