数据库相关SQL_根据主键求和的sql-优快云博客

DataBaseManagementSystem,数据库管理系统,俗称数据库软件
常见的DBMS
- MySQL: Oracle公司产品, 08年被Sun公司收购了,09年Sun公司被Oracle收购了,MySQL开源产品,市占率排名第一
- Oracle: Oracle公司产品, 闭源产品,性能最强价格最贵, 市占率排名第二
- SQLServer: 微软公司产品,闭源产品,市占率第三
- DB2: IBM公司产品,闭源产品
- SQLite:轻量级数据库

SQL

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

刘德华 30 5000

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

如何连接数据库软件

检查数据库服务是否开启
windows:开始菜单中找到MySQL或MariaDB里面的 MySQL Client 点击进入

Linux或Mac OS, 打开终端输入 mysql -uroot -p 回车输入密码再回车

断开连接:
- 关了窗口
- 输入exit 回车

数据库和表的概念

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

数据库相关的SQL

查询所有数据库

格式: show databases;

创建数据库

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

create database db1;

create database db2 charset=utf8;

create database db3 charset=gbk;

show databases;

查询数据库信息

格式: show create database 数据库名;
举例:

show create database db1;

show create database db2;

show create database db3;

删除数据库

格式: drop database 数据库名;

举例:

drop database db3;

drop database db2;

show databases;

使用数据库

使用完数据库之后再执行表相关或数据相关的SQL 否则会报错: No database selected
格式: use 数据库名;
举例:

use db1;

表相关SQL

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

创建表

格式: 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;

查询所有表

格式: show tables;

查询表信息

格式: show create table 表名;
举例:

show create table person;

show create table student;

查询表字段信息

格式: desc 表名;

删除表

格式: drop table 表名;
举例:

drop table car;

show tables;

修改表名

格式: rename table 原名 to 新名;

添加表字段

最后面添加格式: 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;

删除表字段

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

alter table teacher drop salary;

修改表字段

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

alter table teacher change age salary int;

数据相关SQL

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

create database db5 charset=utf8;

use db5;

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

插入数据

全表插入格式: 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; 之后再次执行

查询数据

格式: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;

修改数据

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

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

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

删除数据

格式: delete from 表名 where 条件;
举例:

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

delete from person where age<20;

delete from person where age is null;

主键约束 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

主键约束+自增 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');

**导入*.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;

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

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<>"人事";

与或非 and or not

and: 查询多个条件同时满足时使用
or : 查询多个条件满足一个条件时使用
not: 取反

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

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

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

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

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

select name from emp where manager is not null;

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

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

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

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

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

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

select * from emp where sal between 2000 and 3000;

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

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

in(x,y,z)

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

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

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

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

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

去重 distinct

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

select distinct job from emp where dept_id=1;

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

select distinct dept_id from emp;

模糊查询 like

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

查询姓孙的员工姓名

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

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

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

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

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

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

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

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

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

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

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

排序 order by

格式: 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;

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

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

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

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

分页查询 limit

格式: limit 跳过的条数,请求的条数(每页的条数)
跳过的条数=(请求的页数-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

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

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

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

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

查询工资最高的员工信息

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

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

select * from emp order by hiredate limit 3,3

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

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

别名

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;

最小值min(字段名)

查询最低工资

select min(sal) from emp;

求和sum(字段名)

查询程序员的工资总和

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

计数count(*)

查询销售的数量

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

分组查询 group by

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

查询每个部门的人数

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;

查询每种工作的人数

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

查询每种工作的平均工资

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

查询每个部门的最高工资

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

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

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

查询每种工作的最低工资

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

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

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

查询平均工资最高的部门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的后面

查询每个部门的平均工资,只查询出平均工资大于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;

查询每种工作的人数,只查询人数大于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;

子查询(嵌套查询)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

关联关系

创建表的时候,表和表之间存在的业务关系.
有哪几种关系:
- 一对一: 有AB两张表,A表中的一条数据对应B表中的一条数据, 同时B表中的一条数据也对应A表中的一条数据.

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

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

如果表之间存在关联关系,如何建立关系?
- 一对一: 在两张表的任意一张表中添加建立关系的字段指向另外一张表的主键
- 一对多: 在多的表中添加建立关系的字段指向另外一张表的主键
- 多对多: 新建一张关系表,表中至少有两个字段用于建立关系, 指向另外两张表的主键

关联查询

同时从多张表中查询数据的方式称为关联查询

包括三种查询方式:
- 等值连接
- 内连接
- 外连接

等值连接

格式: select 字段信息 from A,B where A.xxx=B.xxx(关联关系) and 其它条件

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

select e.name,sal,d.name

from emp e,dept d

where e.dept_id=d.id;

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

select e.name,sal,d.name,loc

from emp e,dept d

where e.dept_id=d.id and sal>2000;

查询程序员的部门地址

select distinct loc

from emp e,dept d

where e.dept_id=d.id and job="程序员";

内连接

等值连接和内连接的作用一样,查询的都是两个表的"交集数据"(存在关系的数据)
格式: select 字段信息 from A join B on A.x=B.x where 其它条件

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

select e.name,sal,d.name

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

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

select e.name,sal,d.name,loc

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

查询程序员的部门地址

select distinct loc

from emp e join dept d on e.dept_id=d.id where job="程序员";

外连接

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

格式: select 字段信息 from A left/right join B on A.x=B.x where 其它条件

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

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

select e.name,sal,d.name

from emp e left join dept d on e.dept_id=d.id;

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

select d.name,e.name

from emp e right join dept d on e.dept_id=d.id;

关联查询总结:

如果需要同时查询多张表的数据使用关联查询
如果查询到的是两张表的交集数据使用等值连接或内连接(推荐)
如果查询的是一张表的全部和另外一张表的交集则使用外连接

JDBC

Java DataBase Connectivity: Java数据库连接
JDBC是Sun公司提供的一套专门用于Java语言和数据库软件进行连接的API(Application Programma Interface) , Sun公司为了避免Java程序员每一种数据库软件都学习一套全选的方法, Sun公司通过JDBC接口将方法名固定好,各个数据库厂商根据此接口中的方法名写各自的实现类(驱动) , 这样的话对于Java程序员不管连接什么数据库软件方法名是一样的,即使某一天换了数据库软件代码是不需要改变的,这样大大提高了开发效率.
如何通过JDBC和数据库软件进行连接并执行SQL语句

创建Maven工程
在pom.xml里面添加以下依赖

   <!--dependency依赖 dependencies多个依赖-->
    <dependencies>
        <!-- 连接MySQL数据库的依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
    </dependencies>

创建Demo01.java ,添加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语句 execute执行
        s.execute("drop table jdbct1");
        //4. 关闭资源
        conn.close();
        System.out.println("执行完成!");

Statement执行SQL语句的对象

execute("sql"); 此方法可以执行任意SQL语句, 推荐执行数据库相关和表相关的SQL语句
int row = executeUpdate("sql"); 此方法执行增删改相关的SQL语句, 返回值表示生效的行数
ResultSet rs = executeQuery("sql"); 执行查询相关的SQL语句,查询到的结果装在ResultSet对象中.

DBCP

DataBaseConnectionPool数据库连接池

作用: 将连接重用,避免频繁的开关连接, 从而提高执行效率

如何使用DBCP?
- 添加 dbcp的依赖赋值到工程的pom.xml里面
- 通过以下代码创建连接池,并从连接池对象中获取连接

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

单词

show	显示	database	数据库
create	创建	charset	字符集
drop	除名	select	选择
use	用	varchar	可变长字符串
desc	降序排列/排序	rename	重新命名改名
alter	改变，改动	change	改变
insert	插入	into	在...里面
values	值	update	更新
delete	删除	group by	分组查询