Mysql

创建一个xxx的数据库

create database xxx;

删除xxx数据库

drop database xxx;

显示xxx数据库

show create database xxx;

查看所有数据库

show databases;

使用xxx数据库

use xxx；

删除表 

drop table 表名；

创建表

create table 表名（
列名1 类型,
列名2 类型,
列名3 类型
）

添加数据 insert into 表名 values(数据1,数据2…) 

insert into students values(190701,'刘盼盼','女','2020-3-8 10:50:21','宜春',190.6);
insert into students values(190702,'王小红','男','2010-3-8 10:21:21','菏泽',140.6);
insert into students values(190703,'刘春香','女','2006-3-8 9:50:21','廊坊',null);

 

主键

-- 创建课程表 带有主键 auto_increment 自动下一个 primary key主键
create table xxx表名(
cid int auto_increment  primary key,
cname varchar(12),
ccredit smallint,
)

 

添加外键

TypeID int,//表 id 
外键id                           主表   主表字段
foreign key(TypeID) references Category(TypeID),

 一次添加多条数据

insert into course values
(数据1)，
(数据2)，
(数据3)
insert into course VALUES
(3,'Java基础',6),
(4,'SQLserver数据库',4),
(5,'swing程序开发',5)

 

去掉重复值 （distinct） 

select distinct 列名 from 表名
select distinct ssex from students;

 

修改 限制 

update 表名 set 列名=新值  where 条件
update students set  saddress='黄浦' where sno=190703;

 

where限制查询

select 列名 from 表名 where 条件 
select saddress from students where sno=190701;

limit用来查询指定范围的记录 

select * from 表名 limit 开始值,长度;
案例1 查询前两条
select * from students limit 0,2;
select * from students limit 0,2;
select * from students limit 2;
select * from students limit 2,2;

为空is null 和不为空 is not null 

select * from 表名 where 列名 is null;--为空的
select * from 表名 where 列名 is not  null; ---不为空的

select * from course where ccredit is null;
select * from course where ccredit is not null;

逻辑运算<、>、=、!=查询学分大于4的

select * from course where ccredit>=4;

查询学分不等于5

select * from course where ccredit!=5;

between and查询一个范围 包含开始和结束(闭区间)

不包含开始和结束(开区间)

select * from 表名 where 列名 between 值 and 值 ;
select * from course where ccredit between 3 and 5;

in 表示数学的一个集合
查询学分在4 5 6 之间的 包含括号里的 

select * from 表名 where 列名 in(值1，值2.....)
select * from course where ccredit in(3,5,6);

查询学分不在4 5 6 之间的 不包含括号里的 

select * from 表名 where 列名 not in(值1，值2.....)
select * from course where ccredit not in(3,5,6);

 

like用于字符串匹配 %所有 _一个字符

select * from students where saddress like '宜%';
select * from students where saddress like '_春';
找出王**   like '王__' 有2个_ _
select * from students where sname like '王__';
找出不是2020年开头的 not like 
select * from students where sbirthday not like '2020%';

聚合函数 

聚合函数 用于数据的汇总 比如 求和 最大 最小 平均 统计

sum求和
select sum(列名) from 表名
select sum(sheight) from students;

max最大值
select max(列名) from 表名
select max(sheight) from students;

min最小值
select min(列名) from 表名
select min(sheight) from students;

平均值
select avg(列名) from 表名
select avg(sheight) from students;

统计人数
select count(*) from 表名

--统计身高大于大于150的人数
select count(*) from students where sheight>=150;

连接查询 union 同显示 不需要加分号 union合并查询的结果
查出上海和北京的
select * from students where saddress like'黄%'
UNION
select * from students where saddress like'宜%'
 

 

修改 update 

无条件

update 表名 set 列名=新值

有条件

update 表名 set 列名=新值 where 条件

示例

把计算机系学生的Department改为信息学院

 update test1 set Department='信息学院' where Department='计算机系';

将没有值的课程学分修改为4分 

update xxx set ccredit=4 where ccredit is null;

排序

排序使用order by  asc升序  desc降序
select  * from 表名 order by 排序列名

按学分排序 升序 

select * from course order by ccredit; 

按学分排序 降序

select * from course order by ccredit desc; 

同时排序2个

select * from  表名 order by 排序列名1 （asc/desc）,排序列名2 （asc/desc）
-- 按学分升序 在按名称降序
select * from course order by ccredit asc,cname desc;

having 聚合函数过滤 后面必须是聚合函数

having字句：
where不能和聚合函数一起使用。
where只能过滤分组之前的数据
having：过滤分组之后的数据。
语法： select 分组字段，聚合函数 from 表 group
by 分组字段 having 聚合函数的过滤

如:
–查询每门功课的平均分，要求显示平均分在60分以上的(包括60分).
–(解题思路：根据科目名称进行分组，通过where条件筛选出平均分大于等于60分的科目)

select ExamSubject,avg(ExamResult)as 平均分 from StudentExam group by ExamSubject 
 having avg(ExamResult)>=60

 

– group by 用来分组 必须它与聚合函数 （max min avg sum count ）结合起来
– 按出生地统计人数

select * from students group by saddress;
select saddress,count(*) from students group by saddress;
select saddress,count(*) from students where saddress is not null group by saddress;

– 按出生地分组并且人数在2人以上 having是对分组结果的过滤 必须是聚合函数 

select saddress,count(*) from students group by saddress having  count(*)>=2;

 

外连接

—外连接

use StudentDb
select * from ClassInfo
select * from StudentInfo
insert into StudentInfo values(11,'013','王大财',65,'男','46711477148','2009-8-7','北京',3)

–外连接—左连接（找出所有学生信息，包括没有班级）左边表为基准 left join

select a.*,b.classnumber from StudentInfo as a left 
join ClassInfo as b on a.SClassID=b.ClassID

外连接—右连接（找出所有学生信息，包括没有班级）右边表为基准 right join

select a.*,b.classnumber from StudentInfo as a right join ClassInfo as b on a.SClassID=b.ClassID

全连接 两表为基准 full join

select a.*,b.classnumber from StudentInfo as a full join ClassInfo as b on a.SClassID=b.ClassID

三表联差查

select 查询字段1，字段2 from 表1 inner join 表2 on 表1字段=表2 inner join 表3 on 表2字段=表3字段

查询学生的学号 姓名 系 课程 以及成绩

select a.No,a.Name,a.Department,b.Courses,b.Grade from test1 as a inner join test2 as b on a.No=b.No;

连接查询找出大于等于75分的学生的No、Name、Grade、Courses并且降序

select a.NO,a.Name,b.Grade,b.Courses from test1 as a inner join test2 as b on a.No=b.No where b.Grade>=75 order by Grade desc;

内连接

select a.No,a.Name,a.Department,b.Courses,b.Grade from test1 as a inner join test2 as b on a.No=b.No;

左连接 左边为基准

select a.No,a.Name,a.Department,b.Courses,b.Grade from test1 as a left join test2 as b on a.No=b.No;

全连接

两表为基准 mysql没有全连接 可以用union实现全连接

select a.No,a.Name,a.Department,b.Courses,b.Grade from test1 as a left join test2 as b on a.No=b.No;
union
select a.No,a.Name,a.Department,b.Courses,b.Grade from test1 as a right join test2 as b on a.No=b.No;

子查询

– 找出和张可同一个部门的人 先查出张可的部门编号 然后在去查询
– 利用子查询

select * from emp where deptno=14; 
select * from emp where deptno=(select deptno from emp where cname='张可');

– 找出工资大于部门平均工资的人员

select avg(sal) from emp group by deptno;
select a.* from emp  as a,(select deptno, avg(sal) as pj from emp group by deptno) as b where a.deptno=b.deptno and a.sal>=b.pj

表视图

创建视图表  视图名 v_开头
create view 视图名 [(视图字段列表)] as select 语句
创建视图列：
create view v_avgsalary 
as 
select * from dt_pay where Salary>(select avg(Salary) from dt_pay);

– 视图是一个虚拟的表 来自于基本表table
– 创建视图表 工资大于平均工资的人员信息

select * from dt_pay where Salary>(select avg(Salary) from dt_pay); 

create view v_avgsalary 
as 
select * from dt_pay where Salary>(select avg(Salary) from dt_pay);
使用这个视图 把视图当表来看待
select * from v_avgsalary;
select count(*) from v_avgsalary;
select * from v_avgsalary order by Salary;

多表查询

得到工资大于平均工资的人员的信息 id 名字 部门 岗位

select a.EmploryID,a.EmploryName,b.DepartnentName,a.Job from v_avgsalary as a inner join dt_emp as b on a.EmploryID=b.EmploryID;

左连接

select a.EmploryID,a.EmploryName,b.DepartnentName,a.Job from v_avgsalary as a left join dt_emp as b on a.EmploryID=b.EmploryID;

右连接 

select a.EmploryID,a.EmploryName,b.DepartnentName,a.Job from v_avgsalary as a right join dt_emp as b on a.EmploryID=b.EmploryID;

创建一个视图 

使用drop 视图存放的不是数据 而是sql查询语句
drop view v_count;
drop view 视图名;

select * from v_avgsalary; 
select * from dt_pay;
select * from dt_emp; 

人事部长和后勤部长工资涨20%
update dt_pay set Salary=Salary*1.2 where Job='人事部长' or Job='后勤部长';
update dt_pay set Salary=Salary+Salary*0.2 where job in('人事部长','后勤部部长');

删除视图 

使用drop 视图存放的不是数据 而是sql查询语句
drop view v_count;
drop view 视图名;

select * from v_avgsalary; 
select * from dt_pay;
select * from dt_emp; 

人事部长和后勤部长工资涨20%
update dt_pay set Salary=Salary*1.2 where Job='人事部长' or Job='后勤部长';
update dt_pay set Salary=Salary+Salary*0.2 where job in('人事部长','后勤部部长');

字段自动增加时 添加数据 自动增加列不需要添加数据 自动增加列为 id auto_increment
创建商品表格

create table goods
(
id int auto_increment not null primary key,
gdid varchar(12) not null,
gdname varchar(20) not null,
gdprice FLOAT,
gdnumber int ,
gddate varchar(12),
gdaddr varchar(10)
)

查询整个表

select * from goods

添加记录

insert into goods (gdid,gdname,gdprice,gdnumber,gddate,gdaddr) values
(
'3097','矿泉水',4,20,'2020-12-14','浙江'
)

第一、读取properties

//读取properties文件 需要把配置文件放src下面
ResourceBundle rs = ResourceBundle.getBundle("mysql");//mysql.properties
String driv=rs.getString("driver");
String url=rs.getString("url");
String name=rs.getString("dbroot");
String pass=rs.getString("dbpass");
System.out.println(driv);
System.out.println(url);
System.out.println(name);
System.out.println(pass);

指定路径 包名.配置文件名

	String flename="test.it.mysql";
	//读取properties文件 需要把配置文件放src下面
	 ResourceBundle rs = ResourceBundle.getBundle(flename);//mysql.properties
	 String driv=rs.getString("driver");
	 String url=rs.getString("url");
	 String name=rs.getString("dbroot");
	 String pass=rs.getString("dbpass");
	 System.out.println(driv);
	 System.out.println(url);
	 System.out.println(name);
	 System.out.println(pass);

第二 读取properties

//读取配置文件
Properties p=new Properties();
//文件对象 config下的mysql.init
File f=new File("config/mysql.properties");
//文件输入流
FileInputStream finst=new FileInputStream(f);
//加载文件
p.load(finst);
//读取属性文件中指定键的值，并赋给变量
String driv=p.getProperty("driver");
String url=p.getProperty("url");
String name=p.getProperty("dbroot");
String pass=p.getProperty("dbpass");

in的升级版

select * from question where find_in_set(question_id,(select exam_question_ids from exam where exam_id=1));

随机查询10条

 SELECT * FROM question WHERE question_bookid=1 ORDER BY RAND() LIMIT 10