SQL语句(二)

#创建数据库
create database day01;
#删除数据库
drop database day01;

#切换到指定数据库
use _08_
create table student_info(
id int,
`name` varchar(20),
#共18位,其中2位小数,16位整数
salary decimal(18,2)
)engine = innodb default charset = uft8;

#显示创建表语句
show create table student;

#删除表
drop table student; 

# 查询学生表中,成绩为80分的所有学生信息
select * from student where score = 80;

# 查询所有学生信息
select * from student;

# 查询所有学生的id和姓名
select id,name from student;

#创建一个表
create table teacher (
id int,
name varchar(100),
#decimal 小数 (总位数,小数位数)
salary decimal(18,2)
);

#查看表结构
desc teacher;

# 查看建表语句
show create table teacher;

#把一个已经建好的表的存储引擎修改为其他的
alter table teacher engine = innodb;

#修改表名
alter table teacher rename teacher_newname;

#给表增加一个字段
alter table teacher_newname add birthday datetime;

#添加到指定字段后面
alter table student  add sex char(2) after `name`; 

#添加到第一列
alter table student add sex1 char(2) first;

#给表删除一个字段
alter table student drop sex1;

#将表的字段改类型
alter table student modify sex decimal(18,2);

#给表的字段改名(同时也可以把类型改了)
alter table student change name student_name varchar(200);

#向student表中 插入一条记录
insert into student(id,name,score) values(4,'老刘',35.65);

# 一次性添加多条数据,用逗号隔开
insert into student(id,name,score) values(5,'老王',80),(6,'老张',90),
(7,'老李',100);

# 删除符合条件的记录
delete from student where name='老李';

#将符合where条件的所有对应的字段更新为新值
update student set score = 100,name ='老李' where id= 6;

#建表时添加主键
CREATE TABLE person2(
id INT NOT NULL DEFAULT 0,
name VARCHAR(20) NOT NULL DEFAULT '',
income DECIMAL(18,0) NOT NULL DEFAULT 0.0,
primary key(id)
);

#建表后通过修改表结构添加主键
ALTER TABLE person2 add PRIMARY KEY(id);

#建表时添加外键
CREATE TABLE student2(
id INT NOT NULL DEFAULT 0,
name VARCHAR(20) NOT NULL DEFAULT '',
score DECIMAL(18,2) NOT NULL DEFAULT 0.0,
teacher_id INT NULL DEFAULT 0,
PRIMARY KEY(id),
FOREIGN KEY(teacher_id)  references student(id)
);

#建表时添加约束
create table person3(
id int not null default 0,
name varchar(20) not null default '',
income decimal(18,2) not null default 0.0
);

#建表后添加约束
ALTER TABLE person2 MODIFY id INT NOT NULL DEFAULT 0;

#一个比较标准的保证数据完整性的建表语句
CREATE TABLE student2(
id INT NULL DEFAULT 0,
name VARCHAR(20) NOT NULL DEFAULT '',
score DECIMAL(18,2) NOT NULL DEFAULT 0.0,
PRIMARY KEY (id) 
)ENGINE INNODB DEFAULT CHARSET=utf8;

#查询指定内容
SELECT id 
FROM student
WHERE `name` is NOT NULL;

#查看mysql版本
SELECT VERSION();

#查看所在数据库
SELECT DATABASE();

#查看字符长度
SELECT CHAR_LENGTH('中国');
SELECT CHAR_LENGTH(name) from person;

#查看长度  一个汉字占三个长度
SELECT LENGTH('中国');
SELECT LENGTH(name)from person;

#字符拼接成字符串
SELECT CONCAT('a','b','c','d');

#第一个字符为连接符,连接后面每两个字符
SELECT CONCAT_WS('=','a','b','c','d');

#字母转换为大写
SELECT UPPER('aabcd');

#字母转换为小写
SELECT LOWER('ABCD');

#字符串截取(字符串,起始位置(包含),终止位置)
SELECT SUBSTRING('字符串截取',1,3);

#去掉两边空格
SELECT TRIM('    a   bc   ' );

#查看当前系统日期
SELECT CURDATE();

#查看当前系统时间
SELECT CURTIME();

#查看当前系统日期时间
SELECT NOW();

#查看当前日期时间对应的时间戳(单位秒),1970/1/1 8:00 到现在的秒数
SELECT UNIX_TIMESTAMP();

#查看到指定时间的秒数
SELECT UNIX_TIMESTAMP('2018-07-25 18:42:00');

#查看时间原点到指定秒数的时间
SELECT FROM_UNIXTIME(1527163397);

#返回两个日期相差天数,第一个减第二个
SELECT DATEDIFF('2018-07-25',NOW());

#返回指定天数前/后日期
SELECT ADDDATE(NOW(),-2)

#查看当前年数
SELECT YEAR(NOW());

#查看当前月份
SELECT month(NOW());

#查看当前天数
select DAY(NOW());

#条件判断if(<判断条件>,<条件为真时的返回值>,<条件为假时的返回值>);
SELECT if(1=1,2,3);

#select ifnull(<表达式或者表达字段>,<表达式或者字段为null时的返回值>);
SELECT IFNULL(name,'该数据为空') from person;

#CASE WHEN THEN END 语句
SELECT id, name, score,
CASE
WHEN score<60 THEN '差'
WHEN score<80 AND score>=60 THEN '良'
WHEN score>=80 THEN '优'
END AS '等级' FROM test_01;

#CASE WHEN THEN END 语句 手动创建视图
CREATE VIEW test1 AS
SELECT id, name, score,
CASE
WHEN score<60 THEN '差'
WHEN score<80 AND score>=60 THEN '良'
WHEN score>=80 THEN '优'
END AS 'rank' FROM test_01;

#CASE WHEN THEN END 语句 
SELECT id, name, score,
CASE rank
when '差' then 'C'
when '良' then 'B'
when '优' then 'A'
END AS 'rank' FROM test1;

#成绩大于 60 或 id 为 3 , 且 名字叫小黑的
SELECT * from student where (score>60 or id = 3 ) 
and name = '小黑';

#成绩大于 60 , 或 id 为 3  且 名字叫小黑的
SELECT * from student where score>60 or (id = 3 
and name = '小黑');

#分数在60和80之间,包括60和80
select * from student WHERE score>=60 and score<=80
select * from student where score BETWEEN 60 and 80;

#分数是60或者20的信息
select * from student where score =60 or score = 20
select * from student where score in (60,20);

#分数不是是60或者20的信息
select * from student where score not in (60,20);

#把名字中 第一个字为'小'的查询出来
#%: 匹配0~N个任意字符
SELECT * from student where name like '小%'

#把名字中 第一个字为'小'并且名字只有两个字的查询出来
#_: 匹配一个任意字符
SELECT * from student where name like '小_'

#把名字中 第一个字为'小'并且名字只有三个字的查询出来
#_: 匹配一个任意字符
SELECT * from student where name like '小__'

#去重,去掉重复名字
select distinct name from student

#排序 order by 字段名[desc|asc]
# 按照成绩降序
select * from student order by score desc

#排序 order by 字段名[desc|asc]
# 默认按照成绩升序排序
select * from student order by score 

#先按成绩降序 如果成绩相同 再按id升序
select * from student order by score desc,id asc;

#限制条数limit 条. 通常与order by 配合使用,升降序之后,再取数据
#求成绩在前三名的学生
SELECT * from student ORDER BY score DESC  LIMIT 3;

#限制条数limit 条. 通常与order by 配合使用,升降序之后,再取数据
#做分页使用
#从第一条开始,取两个,不包含
select * from student limit 0,2;

#求数量 count(1)等价于count(*)
#求最大值 max(字段名)
#求最小值 min(字段名)
#求平均值 avg(字段名)
#求总和   sum(字段名)
#用于计算student表中一共有多少条记录
select count(*) from student;
#用于计算student表中满足条件A的记录的条数
SELECT COUNT(*) from student where id=1;

#用于展示每个老师带了几个学生
select teacher_id,count(*) as stu_count
from student 
group by teacher_id;

#用于展示每个老师带的学生中的最高成绩
select teacher_id,count(*) as stu_count,
max(score) as stu_max_score
from student 
group by teacher_id;

#用于统计每个老师所带学生的总成绩与平均分
select teacher_id,SUM(score) as sum,
avg(score) as avg 
from student 
group by teacher_id;

#group by , having , order by 
#先分组,再过滤,最后排序
#用于统计每个老师所带学生的平均成绩大于等于60分的记录
select teacher_id,sum(score) as sun,
avg(score) as avg 
from student 
group by teacher_id
having avg>=60;

#查询所有学生信息并显示老师名字
select*,(
select name from teacher where id=teacher_id
)as teacher_name
from student 

#查询每个老师的学生的 最大分数,最小分数,平均分数,分数总和,学生人数,老师名字
select MAX(score),MIN(score),AVG(score ),SUM(score),COUNT(*),
(SELECT name from teacher where id = teacher_id)as teacher_name
from student group by teacher_id;

#使用子查询 对成绩划分等级, score<60 ,评级C 并且是差,score>=60
#且 score<80 评级B并且是良,score>=80 评级是A并且是优
select*,
case rank
when 'A' then '优'
when 'B' then '良'
when 'C' then '差'
end as rank_
from
(select *,
CASE
when score<60 then'C'
when score>=60 and score <80 then 'B'
when score>=80 then 'A'
end as rank
from student
) as a;

#在不知道teacher_id 和 老师名字的对应关系的情况下,想查询出张老师
#下面的所有学生信息
select*
from student 
where teacher_id in(
	select id from teacher where name='张老师'
);

#查询出 teacher_id = 1 的所有学生信息
select * from student where teacher_id = 1;

#查询出 学生分数大于60的所有学生信息
select * from student where score > 60

#查询出 学生分数大于60 或 teacher_id = 1 的所有学生信息(去除重复)
select * from student where teacher_id = 1 or score > 60

select* from student where teacher_id=1
UNION
select * from student where score>60;

#查询出 学生分数大于60 或 teacher_id = 1 的所有学生信息(可重复)
select* from student where teacher_id=1
UNION ALL
select * from student where score>60;

#内连接
select * 
from teacher tea 
inner join student stu on tea.id=stu.teacher_id;

select * 
from student stu
inner join teacher tea on tea.id=stu.teacher_id;

#左外连接
select * 
from teacher tea
left join student stu on stu.teacher_id=tea.id;

select * 
from student stu
left join teacher tea on tea.id=stu.teacher_id;

#右外连接
select * 
from teacher tea
right join student stu on stu.teacher_id=tea.id;

select * 
from student stu 
right join teacher tea on tea.id=stu.teacher_id;

#模拟full join,MySQL不支持,Oracle支持
#full join 就是两个表都有,不符合条件用null代替
select * from student stu
left join teacher tea on stu.teacher_id=tea.id
UNION
select * from student stu
right join teacher tea on stu.teacher_id=tea.id

#行转列,分组,各科成绩为一列
SELECT  name,
max(case course
when 'java' then score else 0
END )java,
max(case course
when 'MySQL' then score else 0
END) MySQL
 FROM test_9 group by name;

#多行转为一行一列
select name,
group_concat(course,'=',score order by course asc SEPARATOR ' | ') as '各科成绩'
from test_9 group by name