Day_30_MySQL基础

数据库：顾名思义，就是用来存储数据的仓库。

文件跨平台性能差的原因是不是操作系统之间存储方式不一样，那么数据库为什麽就可以解决？

遵循一定数据格式的数据集合。

特点：可以认为它是对文件系统的改进。它解决了不同操作系统之间，数据格式的兼容性问题。也就是说，只要是同一个数据库的数据文件，即使是从Windows迁移到了Linux上，也可以正常处理的。

         关系型数据库 对于java而言,一个类就可以对应一个表,一个类对象就可以对应表中一行数据,一个成员变量可以对应表中的一个列(字段),能够做到一一的映射,数据库中是以表作为基本单位的。除此之外，关系型数据库的关系主要体现在它自己的关系模型，反应了表与表之间的关系。

DCL数据控制语言(Data Control Language)

代表关键字:grant,revoke.

DDL数据定义语言(Data Definition Language)

代表关键字:create ,drop,

DML数据操作语言(Data Manipulation Language)

代表关键字:insert,delete,update

DQL数据查询语言(Data Query Language) 

代表关键字:select

TCL事务控制语言(Transactional Control Language)

代表关键字:commit ,rollback;

基础操作

-- create user 'username'@'host' IDENTIFIED 'password';

create user 'zhu'@'localhost' IDENTIFIED by 'zhu';

-- 开启权限 grant All ON ''.'' TO 'username'@'%';
-- Select  查询
-- Insert 插入数据
-- Update 更新数据
-- Delete 删除数据
-- Drop 删除表
-- Create 创建表

GRANT All ON text.student TO 'zhu'@'localhost'

-- 关闭权限 revoke All ON ''.'' from 'username'@'%';

revoke all on text.student from 'zhu'@'localhost'

-- drop user 用户名@'%';

drop user 'zhu'@'localhost'






create table teacher (
   		 id int,
		`name` varchar(20),
		salary decimal(18,2)
)ENGINE = innodb default charset = utf8;

-- insert into 表名 (列名1,列名2) values (值1,值2);
insert into teacher (name, id, salary)  values ('老刘',1,56.23);
insert into teacher (name, salary)  values ('老王',56.23);

-- update  表名 set 列名1=值 , 列名2=值  where 列名 = 值;
update teacher set salary=1000.45, name='dave' where id = 1;

-- delete from 表名 where 列名 = 值;
delete from teacher where name='老王';

-- select 列限定 from 表限定 where 行限定

-- 在当前数据库服务器上创建一个新库
create database day01;

-- 在当前数据库服务器上删除一个库
drop database day01;

-- 在数据库day01下创建表student_info
use day01;
create table student_info (
   		 id int,
		`name` varchar(20),
		salary decimal(18,2)
)ENGINE = innodb default charset = utf8;

-- 在数据库day01下删除表student_info
use day01;
drop table student_info;

-- 加上if not exists 可以防止语句出错,如果这个表存在就不会再创建,但是不会报错
create table if not exists employee (
	id int(32) primary key auto_increment,
	name varchar(32),
	empNo char(6),
	deptNo char(3),
	telephone char(11),
	homeplace varchar(128),
	joinDate datetime
)

增删改查

1. Insert

格式 :

insert into 表名 (列名1,列名2) values (值1,值2);

insert into student_info (student_name,student_age) values ('师浩',23);

1. Update

格式 :

update  表名 set 列名1=值 , 列名2=值  where 列名 = 值;

update student_info set student_age=66 where student_name='师浩';

1. Delete

格式 :

delete from 表名 where 列名 = 值;

delete from student_info where student_name='师浩';

        select

格式 :

select 列限定 from 表限定 where 行限定

select student_name from student_info where student_age=23;

select * from student_info;

        Alter

create table student (
   		 id int,
		`name` varchar(20),
		salary decimal(18,2)
-- 		decimal小数
)ENGINE = innodb default charset = utf8;

-- alter table 表名 rename 新表名;

alter table student rename student_01

-- alter table 表名 change 列名 新列名 数据类型; 

alter table student_01 change name name_01 int

-- alter table 表名add 列名类型;

alter table student_01 add student_age int

-- alter table 表名 drop 列名;

-- alter table 表名 modify 列名 新数据类型; 

-- 修改数据库字符集
-- alter database 数据库名 character set utf8 
-- alter database _111_ character set utf8 
-- 修改表字符集
-- Alter table 表名 character set utf8 collate utf8_general_ci

        约束

create table temp_01 (
id int,
temp_name varchar(20),
-- 创建时添加唯一性约束
unique(id)
);

create table temp_02 (
id int,
temp_name varchar(20)
);
-- 创建后添加唯一约束
alter table temp_02 add unique(id);

create table temp_03 (
-- 创建表时主键设置
id int primary key,
temp_name varchar(20)
);

create table temp_04 (
id int,
temp_name varchar(20),
-- 添加主键
primary key(id)
);

create table temp_05(
id int,
s_name varchar(30),
sex varchar(10)
);
-- 不为空，default默认值
alter table temp_05 modify id int not null ;
-- 删除以后，还能保存是因为保存的是空字符串
alter table temp_05 modify s_name varchar(30) default'abc' ;
alter table temp_05 modify sex varchar(10) not null default ' ' ;

create table temp_06 (
-- 自增创建，创建在主键上
id int auto_increment,
temp_name varchar(20),
-- 添加主键
primary key(id)
);

create table temp_07 (
id int,
temp_name varchar(20),
-- 添加主键
primary key(id)
);
-- 自增创建，创建在主键上
alter table temp_07 modify id int auto_increment;


create table student (
student_id int,
student_name varchar(20),
-- 添加主键
primary key(id)
-- 添加外键
foreign key(teacher_id) REFERENCES teacher(teacher_id)
);

create table teacher (
teacher_id int,
teacher_name varchar(20),
-- 添加主键
primary key(id)
);



-- alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);

        判断关系：

create table student_text(
id int,
student_name varchar(30), 
score decimal(18,2)
);
insert into student_text(id, student_name,score) values (1,'张三',99.2);
insert into student_text (id, student_name, score) values (2,'李四',97.9);
insert into student_text(id, student_name, score) values (3,'王五' ,98);
insert into student_text(id,student_name) values (4,'赵六');
insert into student_text (id, student_name,score) values (5,'小明',98); 


-- select 列限定 from 表限定 where 行限定 

-- or 或判断
select * from student_text where id = 3 or student_name='小明';

-- and 且判断and优先级高于or
select * from student_text where id = 3 and student_name='小明';

-- 查询成绩为空 用is null
select * from student_text where score is null;
-- 查询成绩不为空 用is not null
select * from student_text where score is not null;

-- between and 范围限定，大范围好多数
select * from student_text where score between 95 and 98;
-- in (97,98)数比较少，且范围好查,括号是几就查几
select * from student_text where score in(97,98);

-- like '值'  
-- % 任意个数，任意字符
-- _ 单个任意字符
-- \ 转义字符
select * from student_text where student_name='明';


-- select 列限定 from 表限定 order by 列名 asc/desc(升序降序) limit(限定条数);
-- 两个限定
select * from student_text order by score asc, id desc limit 10;

-- limit n , m ;  从哪条开始取，取几条
select * from student_text order by score asc limit 3 , 3;

        组运算：

select count(*) from student_text where score>90;

-- 主函数
-- 最大值
select max(score) from student 
-- 最小值
select min(score) from student 
-- 求和
select sum(score) from student 
-- 平均值
select avg(score) from student 

-- 查询分组 ，分组  as 取别名  group by 表示按什么分组
select teacher_id , count(*) as '个数' from student as s group by s.teacher_id

-- having 过滤 将分组后的组，按某种情况过滤
select teacher_id , count(*) as '个数' from student as s group by s.teacher_id having thacher_id>1



-- union 合并查询，去除重复项
-- union all 合并查询，不去除重复项
-- 列字段数量必须一致
select * from student where id=1
union all
select * from student where name= '张三' ;

        常用的函数：

select version() ;显示 当前MySQL软件的版本
select database();显示当前所处数据库是哪个
select char_1ength('中国');返回字符个数。
select length('中国');返回字符所占字节数, MySQL中，一个UTF8编码的汉字占3个字节
select concat( 'a', 'b','c', 'd' );返回'abcd'。字符串拼接函数
select concat. ws('=','a','b','c' );返回'a=b=c'。字符串拼接函数,第一个是拼接间隔符
select upper('abcd');返回ABCD.将参数中所有小写字母转换为大写
select lower('ABCD' );返回abcd.将参数中所有大写字母转换为小写
select substring('系统信息类',1,3 );返回系统信。 第2个参数代表从1开始的第几个字符，第3个参数代表截取字符个数
select trim(' abc ');返回abc。 用于删去参数左右的所有空格
select curdate() ;返回当前日期
select curtime () ;返回当前时间
select now() ;返回当前日期时间
select unix timestamp() ;返回当前日期时间对应的时间戳(单位秒)
select unix timestamp('2018-05-24 20:00:00' ) ;返回参数指定的日期时间对应的时间戳(单位秒)
select from unixtime (1527163397) ;返回参数指定时间戳(单位秒)对应的日期时间
select datediff('2018-05-23',now() ) ;返回两个参数对应日期相差的天数(用第一个参数减第二个参数)
select adddate( now()， -2 ) ;返回指定天数前/后的日期时间(第一个参数是日期时间，第二个参数是天数，向后加是正数，向前减是负数)
select year('2019-02-24' );返回2019获得年份
select month('2019-02-24' )返回2获得月份
select day(' 2019-02-24')返回24获取日
select if( <判断条件>， <条件 为真时的返回值>，<条 件为假时的返回值>) ;相当于Java中的三目运算符<判断条件〉? <条件为真的返回值> : <条件 为假的返回

        复杂查询（case  when  then）：

select *, (
case rank
when 'a' then '优'
when 'b' then '良'
when 'c' then '差'
end
) as rank_hz from (

-- 当位于FROM后面时，要注意
-- 我们可以把子查询当成一张表
-- 必须要有别名，因为子查询优先被执行,子查询的别名,可以让别的查询当做表或者列去操作
-- 
select 
*,
(case
when score<60 then 'c'
when score>80 then 'a'
when score>60 and score>80 then 'c'
end)
as rank
from
student) as s

         行转列两种方法：

1.

select name,
min(
case course
when 'java' then score
end
) as 'java',
min(
case course
when 'MySql' then score
end
) as 'MySql'
from hang_lie
group by name
-- 分组查询，需要配合组函数使用


2.

-- group自带函数分组，separator 规定分隔符
select name , group_concat(course,'=', score separator '|') as '各科成绩' from hang_lie group by name

        链接查询：

select *
from student s
inner join teacher t on t.teacher_id=s.teacher_id

select *
from student s
left join teacher t on t.teacher_id=s.teacher_id

select *
from student s
right join teacher t on t.teacher_id=s.teacher_id

        应用实例：

-- 学生表 Student

create table Student(Sid varchar(6), Sname varchar(10), Sage datetime, Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

-- 成绩表 SC

create table SC(Sid varchar(10), Cid varchar(10), score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);


-- 课程表 Course

create table Course(Cid varchar(10),Cname varchar(10),Tid varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');



-- 教师表 Teacher

create table Teacher(Tid varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');



-- 查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
SELECT a.Sid,a.score as '01成绩',b.score as '02成绩' FROM (SELECT * from sc WHERE Cid='01')as a,(SELECT * from sc WHERE Cid='02')as b WHERE 
a.Sid=b.Sid AND a.score>b.score;


-- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select s.Sid, Sname , avg(score) as avgScore 
from sc as s
inner join student as s1 
on s.Sid = s1.Sid
group by Sid having avgScore>60


-- 查询在 SC 表存在成绩的学生信息
select s1.* 
from sc as s
inner join student as s1
on s.Sid = s1.Sid and score is not null 
group by s.Sid


-- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select s.Sid , Sname , count(Cid) as '课程数' , sum(score) as '总成绩' 
from sc as s 
inner join student as s1
on s.Sid = s1.Sid 
group by s.Sid


-- 查有成绩的学生信息
select s1.*
from sc as s 
inner join student as s1
on s.Sid = s1.Sid and score is not null
group by s.Sid


-- 查询「李」姓老师的数量
select count(*) as '姓李的' from teacher where Tname like '李%'


-- 查询学过「张三」老师授课的同学的信息
SELECT s.* 
FROM sc 
INNER JOIN student s 
ON sc.Sid=s.Sid 
INNER JOIN course c 
ON c.Cid=sc.Cid 
INNER JOIN teacher t 
ON t.Tid=c.Tid AND Tname='张三';


-- 查询没有学全所有课程的同学的信息
SELECT s.* 
FROM student s 
inner JOIN  sc 
ON s.Sid=sc.Sid 
GROUP BY Sid HAVING count(Cid)<3


-- 查询和” 01 “号的同学学习的课程完全相同的其他同学的信息
select s.*
from student s
inner join sc s1
on s.Sid=s1.Sid 
group by s.Sid having count(Cid)=3

select s.* from student s inner join
(select sc.sid from sc as sc where sid in(
	select sc.sid from sc where sid <> 01 and cid in(
		select cid from sc where sid = 01
	) group by sid HAVING count(*) = (
			select count(*) from sc where sid = 01
		)
) group by sid having count(*) = (select count(*) from sc where sid = 01)) a
on s.sid = a.sid;


-- 查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息
select s.*
from student s
inner join (select Sid from sc where cid in(select cid from sc where sid = 01)and sid<>01 group by sid) a
on s.sid = a.sid


-- 查询没学过”张三”老师讲授的任一门课程的学生姓名
select sname
from student s 
where s.sid 
not in (select sc.Sid from sc inner join course c on c.cid=sc.cid inner join teacher t on t.tid = c.tid and tname='张三')


-- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT s.Sid,Sname,avg(score) 
FROM student s 
INNER JOIN sc 
ON s.Sid=sc.Sid AND score<60 
GROUP BY sc.Sid HAVING count(Cid)>=2;


-- 检索” 01 “课程分数小于 60，按分数降序排列的学生信息
SELECT s.* 
from student s 
INNER JOIN sc 
ON s.Sid=sc.Sid WHERE score<60 AND Cid='01' 
ORDER BY score DESC


-- 查询出只选修两门课程的学生学号和姓名
SELECT s.Sid,Sname 
FROM student s 
INNER JOIN sc 
ON s.Sid=sc.Sid 
GROUP BY Sid HAVING count(Cid)=2;


-- 查询名字中含有「风」字的学生信息
SELECT * FROM student WHERE Sname LIKE '%风%';


-- 查询 1990 年出生的学生名单
SELECT * FROM student WHERE YEAR(Sage)=1990;


-- 查询各学生的年龄，只按年份来算
SELECT Sname,YEAR(NOW())-YEAR(Sage)as age FROM student;


-- 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT s.*,max(score) 
FROM sc 
INNER JOIN student s 
ON sc.Sid=s.Sid 
INNER JOIN course c 
ON c.Cid=sc.Cid 
INNER JOIN teacher t 
ON t.Tid=c.Tid AND Tname='张三';