MySQL

创建一个choose的数据库

create database choose;

删除choose数据

drop database choose;

查看所有数据库

show databases;

显示某个数据库

show create database ucdos;

打开指定的数据库

use choose;
use ucdos;

创建表

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

添加外键

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

主键

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

添加学生数据  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);

删除表

drop table 表名

一次添加多条数据

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 条件

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

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

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

update course 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;

条件排序

– 将北京人的身高降序

select * from students where saddress='宜春' order by sheight desc ;
select * from students where saddress like'北京%' order by sheight 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;

右连接 右边为基准
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;

全连接

两表为基准 Sql没有全连接 可以用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

(select deptno, avg(sal) as pj from emp group by deptno)//求出每个部门的平均工资 把平均工资去当一个表 取别名
avg(sal)as pj 取别名 平均工资

一表为不存在时 条件用where  表1的相同字段=表2的相同字段 and 表1的每个人的工资>表2每次算出的平均工资

表视图

创建视图表  视图名 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,dt_emp as b where 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;

创建一个视图

关于每个部门的人数
create view v_count
AS
select DepartnentName,count(*) as 人数 from dt_emp group by DepartnentName;

删除视图

使用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('人事部长','后勤部部长');

表变化视图表也会变化

删除某个人

delete  from emp where empno=‘3065’

字段自动增加时 添加数据 自动增加列不需要添加数据 自动增加列为 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("Sql");//Sql.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.Sql";
//读取properties文件 需要把配置文件放src下面
 ResourceBundle rs = ResourceBundle.getBundle(flename);//Sql.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下的Sql.init
File f=new File("config/Sql.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

多表查询 分组在排序

select b.*,count(*)as lablecount from article as a INNER JOIN articlelable as b on a.articlelableid=b.articlelableid GROUP BY a.articlelableid ORDER BY lablecount desc

得到所有数据中的不同年份

SELECT date_format(b.createtime, '%Y') as year from article b GROUP BY year ORDER BY year DESC;

就会得到不同的年份

查询某个年份下的所有内容

也可以用like

SELECT * from article b where date_format(b.createtime, '%Y') = '2021'; 

终端输入Sql -u root -p无效

在你的Mac终端,输入： cd ~   然后回车
然后输入：touch .bash_profile   然后回车
再输入：open -e .bash_profile   然后回车
如果你没有配置其他的path，会出现一个空白页面；如果配置了其他的比如jdk的或者其他的，不需要理会，直接加上这句 export PATH=$PATH:/usr/local/Sql/bin  保存后关闭
回到终端面板，输入：source ~/.bash_profile  即执行这个修改后的文件

终端提示ERROR 1396 (HY000): Operation ALTER USER failed for ‘root’@‘localhost’

把localhost改成 %

一定条件多个或者

select * from article as a INNER JOIN articletype as b on a.articletypeid=b.articletypeid
where state=0 and (articletitle like '%切换%'  or articlecontext like '%切换%') order by a.createtime desc

只截取某个字段的一部分修改

UPDATE 表 SET 字段A= INSERT(字段A, 起始位置, 几个, 替换内容)

查找重复数据

select employee_name,count(*) from employee group by employee_name having count(employee_name)>1;

统计重复数据

SELECT COUNT(*) as repetitions, last_name, first_name FROM person_tbl GROUP BY last_name, first_name HAVING repetitions > 1;