数据库SQL和练习（中级）

迷南。 2024-03-27 15:42 73阅读 0赞

1.  ### 26位字母 ###

> 26个英文字母大写是：A、B、C、D、E、F、G、H、I、J、K、L、M、N、O、P、Q、R、S、T、U、V、W、X、Y、Z。  
> 26个英文字母小写是：a、b、c、d、e、f、g、h、i、j、k、l、m、n、o、p、q、r、s、t、u、v、w、x、y、z。

1.  ### 数据库介绍 ###

> 作用: 学习如何对数据进行增删改查

1.  ### SQL执行顺序（有时间进行更新） ###

1.  ### SQL ###

> Structured Query Language 结构化查询语言, 此语言是用于程序员和数据库软件进行交流的语言

insert into user values("刘德华",18);

1.  ### DBMS ###

*  DataBaseManagementSystem数据库管理系统(俗称数据库软件)

*  常见的DBMS包括:

*  MySQL/MariaDB: 开源数据库, 是Oracle公司的产品, 08年被Sun公司收购,09年Sun公司被Oracle收购, 市占率排名第一

*  Oracle:是Oracle公司的产品, 闭源数据库, 性能最高价格最贵, 市占率排名第二

*  SQLServer: 微软公司产品, 闭源数据库

*  DB2: IBM公司产品, 闭源数据库

*  SQLite: 轻量级数据库,安装包几十k

1.  ### 如何连接数据库软件 ###

*  连接数据库软件的几种方式:

*  通过命令行连接本机上的数据库软件

*  通过三方的可视化软件连接数据库软件

*  通过Java代码连接数据库软件

1.  ### 通过命令行连接数据库 ###

*  从开始菜单中找到MariaDB或MySQL文件夹然后找到里面的MySQL Client 打开后输入密码 然后回车 显示 Welcome....说明连接成功
 *  或者cmd打开终端通过命令进入数据库，进入数据库后输入密码，进入数据库命令如下：
 *      mysql -u root -p
 *  如果是Linux或mac OS 打开终端输入: mysql -u root -p 回车后再输入密码 回车

*  退出连接指令: exit 回车

1.  ### 数据库中如何保存数据 ###

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

![b6007fc0c287cf767a6aa56a72a7ad5d.png][]

1.  ### 数据库相关的SQL语句 ###

1.  查询所有数据库

*  格式:

show databases;

![5429f0672bd57f16eeeb592157f851bf.png][]

1.  创建数据库

*  默认字符集格式: create database 数据库名;

*  指定字符集格式: create database 数据库名 charset=utf8/gbk;

*  举例:

create database db1;
    create database db2 charset=utf8;
    create database db3 charset=gbk;
    show databases;

1.  查询数据库信息

*  格式: show create database 数据库名;

*  举例:

show create database db1;
    show create database db2;
    show create database db3;

1.  删除数据库

*  格式: drop database 数据库名;

*  举例:

drop database db3;
    drop database db2;
    show databases;

1.  使用数据库

*  对表和数据操作之前必须使用了某一个数据库否则会报错

*  格式: use 数据库名;

*  举例:

use db1;

1.  ### 数据库相关练习题: ###

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

create database mydb1 charset=utf8;
    create database mydb2 charset=gbk;

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

show databases;

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

show create database mydb1;
    show create database mydb2;

1.  先使用mydb2 再使用 mydb1

use mydb2;
    use mydb1;

1.  删除两个数据库

drop database mydb2;
    drop database mydb1;

1.  ### 表相关的SQL语句 ###

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

![831a005b243296d0e1e290f956d8076b.png][]

use db1;

1.  创建表

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

*  举例:

create table person(name  varchar(50), age int);
    create table student(name varchar(30),chineses int,english int )charset=utf8;
    create table car(name varchar(20),type varchar(20))charset=gbk;

1.  查询所有表

*  格式:

show tables;

1.  查看表信息

*  格式: show create table 表名;

*  举例:

show create table person;
    show create table student;
    show create table car;

1.  查看表字段

*  格式:desc 表名;

*  举例:

desc person;
    desc student;

1.  删除表

*  格式: drop table 表名;

*  举例:

drop table student;
    show tables;

1.  修改表名

*  格式: rename table 原名 to 新名;

*  举例:

rename table person to p;
    rename table car to c;

1.  ### 表相关练习题: ###

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

create database mydb3 charset=gbk;
    use mydb3;

1.  创建t\_hero英雄表, 有名字和年龄字段 默认字符集

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

1.  修改表名为hero

rename table t_hero to hero;

1.  查看表的字符集

show create table hero;

1.  查询表字段

desc hero;

1.  删除表

drop table hero;

1.  删除数据库

drop database mydb3;

1.  ### 表相关SQL(续) ###

1.  添加表字段

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

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

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

*  举例:

create table hero(name varchar(20));
    alter table hero add age int;
    alter table hero add id int first;
    alter table hero add gender varchar(2) after name;

1.  删除表字段

*  格式: alter table 表名 drop 字段名;

*  举例:

alter table hero drop age;

1.  修改表字段

*  格式: alter table 表名 change 原名 新名 新类型;

*  举例:

alter table hero change gender age int;

1.  ### 表相关练习题: ###

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

create database mydb4 charset=utf8;
    use mydb4;

1.  创建teacher表 有名字字段

create table teacher(name varchar(30));

1.  添加表字段: 最后添加age 最前面添加id , age前面添加salary工资

alter table teacher add age int;
    alter table teacher add id int first;
    alter table teacher add salary int after name;

1.  删除age字段

alter table teacher drop age;

1.  修改表名为t

rename table teacher to t;

1.  删除表

drop table t;

1.  删除数据库

drop database mydb4;

1.  ### **数据相关SQL** ###

*  执行数据相关的SQL必须使用了某个数据库并且在这个数据库里面创建了某张表,才能往这个表里面添加数据

create database newdb1 charset=utf8;
    use newdb1;
    create table person(name varchar(10),age int)charset=utf8;

1.  插入数据(增)

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

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

*  批量插入:在values后面写多组值通过逗号分隔

*  举例:

insert into person values("张三",30);
    insert into person(name) values('李四');
    insert into person values("刘备",50),("关羽",40),("张飞",30);
    insert into person(name) values('诸葛亮'),('司马懿');

1.  查询数据

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

*  举例:

select name from person;
    select name,age from person;
    select * from person;
    select name from person where age>30;
    select * from person where name="刘备";

1.  修改数据

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

*  举例:

update person set age=88 where name="诸葛亮";
    update person set name="孙尚香" where name="刘备";
    update person set name="关二爷",age=100 where name="关羽";

1.  删除数据

*  格式: delete from 表名 where 条件;

*  举例:

delete from person where name="张三";
    delete from person where age is null;
    delete from person where age>50;
    delete from person;

1.  ### **综合练习题 :**  ###

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

create database day1db charset=utf8;
    use day1db;

1.  创建t\_hero表, 有name字段 字符集utf8

create table t_hero(name varchar(20))charset=utf8;

1.  修改表名为hero

rename table t_hero to hero;

1.  最后面添加价格字段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;

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

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

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

select name from hero where money=6888;

1.  修改刘备年龄为52岁

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

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

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

1.  删除价格为5000的信息

delete from hero where money=5000;

1.  删除表, 删除数据库

drop table hero;
    drop database day1db;

1.  ### **数据类型** ###

*  整数: int(m)和bigint(m) , m代表显示长度 m=5 存18 查询到的是00018 , 是用来补零的 不是代表存储数据的长度, 而且需要和zerofill关键字结合使用

create database newdb2 charset=utf8;
    use newdb2;
    create table t1(age int(5) zerofill);
    insert into t1 values(18);
    select * from t1;

*  浮点数:double(m,d) m代表总长度,d代表小数长度 58.123 m=5 d=3

create table t2(price double(5,3));
    insert into t2 values(56.231);
    insert into t2 values(23.456789);  //不报错四舍五入
    insert into t2 values(123.456789); //报错

*  字符串

*  char(m): 固定长度 m=5 存"abc" 占5, 在长度固定时执行效率略高, 最大值255

*  varchar(m): 可变长度, m=5 存"abc" 占3, 更节省空间, 最大值65535 ,建议保存255以内的数据

*  text(m): 可变长度, 最大值65535, 建议保存长度较大的数据时使用

*  时间和日期相关

*  date: 只能保存年月日

*  time: 只能保存时分秒

*  datetime: 保存年月日时分秒, 默认值为null, 最大值9999-12-31

*  timestamp:时间戳(指距离1970年1月1日的毫秒数),保存年月日时分秒, 默认值为当前系统时间(极个别的同学会出现默认值为null), 最大值2038-1-19

举例:

create table t3(t1 date,t2 time,t3 datetime,t4 timestamp);
    insert into t3 values("2022-10-11",null,null,null);
    select * from t3;
    insert into t3 values(null,"16:20:30","2011-10-22 11:22:33",null);
    select * from t3;

1.  ### **主键约束 primary key** ###

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

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

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

*  举例:

create table t4(id int primary key,name varchar(20));
    insert into t4 values(1,'aaa');
    insert into t4 values(2,'bbb');
    insert into t4 values(1,'ccc');     //报错 提示以下信息表示数据重复

![eeac91e85562fac6d13b3a138ac5fb6e.png][]

insert into t4 values(null,'ddd');  //报错 提示不能为null

![7cebedef7362dfaab034349f4e61c672.png][]

1.  ### **自增 auto\_increment**  ###

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

create table t5(id int primary key auto_increment,name varchar(20));
    insert into t5 values(null,'aaa');
    insert into t5 values(null,'bbb');
    insert into t5 values(10,'ccc');
    insert into t5 values(null,'ddd');
    delete from t5 where id>=10;
    insert into t5 values(null,'eee');

1.  ### **综合练习题 :**  ###

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

create database newdb1 charset=utf8;
    use newdb1;

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

create table emp(id int primary key auto_increment,name varchar(20),sal int,deptId int)charset=utf8;

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

create table dept(id int primary key auto_increment,name varchar(20),loc varchar(20))charset=utf8;

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

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

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

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

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

select name,sal from emp where sal<6000;

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

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

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

alter table emp add comm int;

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

update emp set comm=500 where deptId=1;

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

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

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

alter table emp change comm gender varchar(5);

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

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

1.  删除没有性别的员工

delete from emp where gender is null;

1.  删除性别字段

alter table emp drop gender;

1.  删除表 和 删除数据库

drop table emp;
    drop table dept;
    drop database newdb1;

1.  ### **导入\*.sql 批处理文件** ###

*  此文件是通过三方工具从MySQL数据库里面导出的数据文件

*  通过在命令行中执行以下指令将此文件的数据导入到自己数据库软件中

格式：source 路径;

source d:/emp.sql;

*  检测是否成功: 执行以下SQL语句 检查是否出现员工的信息

select * from emp;

*  如果查询员工表得到乱码执行以下指令后再重新测试

set names utf8; 
    set names gbk;

1.  ### **比较运算符 > < >= <= = !=和<>** ###

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

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

1.  查询程序员的名字

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

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

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

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

select name,job from emp where job!='人事';
    select name,job from emp where job<>'人事';

1.  ### **and,or,not** ###

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

*  or:当多个条件只需要满足某一个时使用

*  not 取反

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

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

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

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

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

select name from emp where manager is not null;

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

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

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

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

1.  ### **between x and y 两者之间 包含x和y** ###

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

select * from emp where sal>=2000 and sal<=3000;
    select * from emp where sal between 2000 and 3000;

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

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

1.  ### **in(x,y,z)**  ###

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

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

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

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

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

1.  ### **去重distinct** ###

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

select distinct job from emp;

1.  查询员工表中有哪几种不同的部门id

select distinct dept_id from emp;

1.  ### **综合练习题** ###

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

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

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

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

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

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

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

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

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

select distinct job from emp where dept_id=1;

1.  ### **模糊查询like** ###

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

*  \_: 代表1个未知字符

*  举例:

*  x开头 x%

*  x结尾 %x

*  包含x %x%

*  第二个字符是x \_x%

*  x开头y结尾 x%y

*  第二个是x倒数第三个是y \_x%y\_\_

1.  查询名字姓孙的员工姓名

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

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

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

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

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

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

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

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

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

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

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

1.  ### concat()函数 ###

1.  concat()函数

> 功能：将多个字符串连接成一个字符串。  
> 语法：concat(str1, str2,...)  
> 说明：返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

举例：

select concat (id, name, score) as 别名 from 表名；//参考例子
    SELECT * FROM by_app_categories WHERE c_name LIKE CONCAT('%', (SELECT `name` FROM b_catelist WHERE type="wangluo"), '%')//实际例子

1.  concat\_ws()函数

> 功能：和concat()一样，但是可以指定分隔符（concat\_ws就是concat with separator）  
> 语法：concat\_ws(separator, str1, str2, ...)  
> 说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

举例：

select concat ('#',id, name, score) as 别名 from 表名；//参考例子

1.  group\_concat()函数

> 功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。  
> 语法：group\_concat( \[distinct\] 要连接的字段 \[order by 排序字段 asc/desc \] \[separator\] )  
> 说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator分隔符是一个字符串值，缺省为一个逗号。

举例：

select name,group_concat(id order by id desc separator '#') as 别名 from 表名 group by name；//参考例子

1.  concat\_ws()和group\_concat()联合使用

题目：查询以name分组的所有组的id和score

举例：

select name,group_concat(concat_ws('-',id,score) order by id) as 别名 from 表名 group by name；//参考例子

1.  ### **排序 order by** ###

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

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

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;

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

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

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

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

1.  ### **综合练习题:** ###

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

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

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

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

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

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

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

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

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

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

1.  ### **分页查询limit** ###

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

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

*  举例:

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

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

*  第5页的10条数据 limit 40 ,10

*  第10页的10条数 limit 90,10

*  第8页的6条数据 limit 42,6

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

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

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

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

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

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

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

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

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

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

1.  ### **别名** ###

select name as "名字" from emp;
    select name "名字" from emp;
    select name 名字 from emp;

1.  ### timestampdiff(年/月/日/时/分/秒其中一个,开始时间,结束时间) ###

MICROSECOND  微秒
    SECOND  秒
    MINUTE  分钟
    HOUR  小时
    DAY  天
    WEEK  周
    MONTH  月份
    QUARTER 
    YEAR  年份

作用：查询两个时间之间的差值

> 参数1 时间类型（想要按照什么进行计算差值，比如：年、月、日、时、分、秒、毫秒）  
> 参数2 起始时间  
> 参数3 结束时间

SELECT TIMESTAMPDIFF(DAY, #{startTime}, #{endTime}) FROM USER;

1.  ### **综合练习题** ###

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;

1.  ### 聚合函数 ###

*  将查询到的多条数据进行统计查询

*  统计方式包括:

*  求平均值

*  最大值

*  最小值

*  求和

*  计数

*  平均值:avg(字段名)

查询1号部门的平均工资

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

*  最大值:max(字段名)

查询1号部门的最高工资

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

*  最小值:min(字段名)

查询1号部门的最低工资

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

*  求和: sum(字段名)

查询1号部门的工资总和

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

*  计数: count(\*)

查询程序员的数量

select count(*) from emp where job='程序员';

1.  ### **聚合函数练习题** ###

1.  查询销售的平均工资

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

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

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

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

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

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

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

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

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

1.  ### **分组查询group by** ###

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1.  通过别名将重复出现的内容复用

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

1.  ### **having** ###

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

*  having后面是专门用来写聚合函数条件, having要和分组查询结合使用不要单独使用,having写在group by的后面

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

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

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

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

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

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

1.  查询每个部门的平均工资, 只查询工资在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.  ### **子查询** ###

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

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

1.  查询拿最高工资的员工信息

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

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

select * from emp 
    where sal>(select max(sal) from emp where job='程序员')

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

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

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

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

1.  ### **数值计算+ - \* / %**  ###

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

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

1.  给每个3号部门的员工涨薪5块钱

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

1.  ### **关联关系** ###

*  指创建表时,表和表之间存在的业务关系.

*  包含以下几种关系:

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

![54e8fcef8ebff83e0fade13b3746de81.png][]

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

![89c1ebc433610727da8127935937146b.png][]

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

![a275b9b3bbda10ecb91c26161e3643e0.png][]

*  如果两张表之间存在业务关系,如何建立关系?

*  一对一: 在两张表中的任意一张表里面添加建立关系的字段,指向另外一张表的主键

*  一对多: 一对多的两张表, 在"多"的表里面添加建立关系的字段,指向另外一张表的主键

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

1.  ### **关联查询** ###

*  同时查询存在管理关系的多张表的数据时使用的查询方式

*  包含三种查询方式:

*  等值连接

*  内连接

*  外连接

1.  ### **关联查询之等值连接** ###

*  格式: select \* from A,B where A.x=B.x

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

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

1.  查询工资高于2000的员工姓名,工资和对应的部门名和地址

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

1.  ### **关联查询之内连接** ###

*  等值连接和内连接查询到的数据是一样的都是两个表的交集数据,只是书写格式不一样

*  格式: select \* from A join B on A.x=B.x

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

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

1.  查询工资高于2000的员工姓名,工资和对应的部门名和地址

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

1.  ### **关联查询之外连接** ###

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

*  格式: select \* from A left/right join B on A.x=B.x

1.  查询所有员工姓名和对应的部门信息

insert into emp(name,sal) values('灭霸',5);
     
    select e.name,d.*
    from emp e left join dept d on e.dept_id=d.id;

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

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

1.  ### **关联查询总结:** ###

*  如果查询的数据是来自多张表,则需要使用关联查询

*  如果查询的是两个表的交集数据则使用等值连接或内连接(推荐)

*  如果查询的是一张表的全部和另外一张表的交集则使用外连接

1.  ### **JDBC**  ###

*  作用: 通过Java代码执行SQL语句

*  JavaDataBaseConnectivity: Java数据库链接,

*  JDBC是Sun公司提供的一套用于Java语言和数据库软件进行连接的API(Application Programma Interface应用程序编程接口)

*  Sun公司为了避免Java程序员每一种数据库软件都学习一套新的方法, 通过JDBC接口将方法名定义好, 各个数据库厂商根据JDBC里面的方法名写各自的实现类(jar包) , 这样Java程序员只需要掌握JDBC接口中方法的调用,将来就算是换了数据库软件, 代码是不需要改变的,这样的话就降低了Java程序员的学习成本

1.  ### **如何通过JDBC和数据库软件进行连接并执行SQL语句** ###

1.  创建maven工程

1.  在工程的pom.xml文件中添加以下内容 然后刷新maven

<dependencies>
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
    </dependencies>

检查是否出现了和mysql相关的jar包文件

![371c46c934bfa642e8049dd84621a7bc.png][]

1.  如果没有出现 ,检查pom.xml里面是否书写正确, 如果确定书写没问题,检查maven配置是否正确, 如果正确 则删除.m2文件夹下的repository文件夹然后回到工程中 再次刷新maven , 如果还未解决联系项目经理现场或远程解决

1.  在main文件夹中添加cn.tedu.Demo01.java文件

1.  添加main方法, 并在里面添加如下代码:

//1.获取数据库链接对象   异常抛出
    Connection conn = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false",
            "root","root");
    System.out.println("链接对象:"+conn);
    //2.创建执行SQL语句的对象
    Statement s = conn.createStatement();
    //3.执行SQL语句
    s.execute("create table jdbct1(id int)");
    //4.关闭资源
    conn.close();
    System.out.println("执行完成!");

1.  ### **SQL语句分类** ###

*  DDL:数据定义语言,包含数据库相关和表相关的SQL语句

*  DML:数据操作语言,包括增删改查

*  DQL:数据查询语言,只包含查询

*  TCL:事务控制语言, 包含和事务相关

*  DCL:数据控制语言, 包含和用户管理和权限分配相关的SQL语句

1.  ### **Statement执行SQL语句的对象** ###

*  execute("sql"); 此方法可以执行任意SQL语句,推荐执行数据库相关和表相关的SQL语句(DDL数据定义语言)

*  int row = executeUpdate("sql"); 此方法执行增删改相关SQL语句,返回值表示生效的行数

*  ResultSet rs = executeQuery("sql"); 此方法执行查询相关SQL语句,返回值里面装着查询回来的数据

1.  ### **综合练习**  ###

1.  创建jdbc02工程, 在pom.xml文件中添加 MySQL相关的依赖信息 然后刷新maven

1.  创建cn.tedu.Demo01类文件 在里面创建一个 hero表, 有id,name两个字段

1.  创建Demo02, 往hero表里面添加进去 刘备,关羽,张飞三条数据

1.  创建Demo03, 把刘备改成孙悟空

1.  创建Demo04, 查询hero表里面所有名字在控制台输出

1.  创建Demo05, 把孙悟空删除

**数据库连接池DBCP**

*  DataBaseConnectionPool: 数据库连接池

*  作用: 将连接重用,从而提高执行效率

![4f71d5b10443884e8b7070b61acc4311.png][]

*  如何使用连接池?

*  在pom.xml文件中再添加一个和连接池相关的依赖

*  相关代码:

//创建连接池对象
    DruidDataSource dds = new DruidDataSource();
    //设置连接数据库的信息
    dds.setUsername("root");
    dds.setPassword("root");
    dds.setUrl("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false");
    //设置初始连接数量 和 最大连接数量
    dds.setInitialSize(3);
    dds.setMaxActive(5);
    //从连接池中获取连接    异常抛出
    Connection conn = dds.getConnection();
    System.out.println("连接对象:"+conn);

1.  ### **注册登录功能** ###

*  创建用户表

use empdb;
    create table user(id int primary key auto_increment,username varchar(50),password varchar(50),nick varchar(50)); 
     
    select count(*) from user where username='tom' and password='asdf';

1.  ### **SQL注入** ###

*  往用户传值的地方传递进去了SQL语句导致原有SQL语句的逻辑发生改变,这个过程称为SQL注入, SQL注入漏洞是网站中非常低级的漏洞但是危害性很高.

*  注入内容: ' or '1'='1

select count(*) from user 
    where username='asdf' and password='' or '1'='1'

1.  ### **PreparedStatement执行SQL语句的对象** ###

*  此对象具有预编译功能的执行SQL语句的对象

*  相关代码:

![4645c5b3e844c4589bcd0ff24b3cd56f.png][]

[b6007fc0c287cf767a6aa56a72a7ad5d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/04ba588308744d84bc0c96a739f59d1f.png
[5429f0672bd57f16eeeb592157f851bf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/5cc3e075580c412dbea4c339e6d92cac.png
[831a005b243296d0e1e290f956d8076b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/50907d1a1d8f4a7c908bf5152d60e252.png
[eeac91e85562fac6d13b3a138ac5fb6e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/bd45bc77cf2a415bbe05472347448fb8.png
[7cebedef7362dfaab034349f4e61c672.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/189546f07fab4391a35015b0a5229e8f.png
[54e8fcef8ebff83e0fade13b3746de81.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/d22505d8be5f42d5ba9b5013bcfe9d43.png
[89c1ebc433610727da8127935937146b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/95189da2d7244f2eb52c2ca2c7993f2b.png
[a275b9b3bbda10ecb91c26161e3643e0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/f215a5e5ff644bbe89ea4db38858159c.png
[371c46c934bfa642e8049dd84621a7bc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/19e1498adc5a4b6098089485628790d6.png
[4f71d5b10443884e8b7070b61acc4311.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/fd31812178b34585ab504c278558cf99.png
[4645c5b3e844c4589bcd0ff24b3cd56f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/67844dcf81124b42b55190d384fe7a95.png