bthclsbthclsbthcls

Sql简单查询

创建数据库/表

进入数据库：mysql -uroot -p123456

支持中文字符：

Set character_set_database='utf8';
Set character_set_server='utf8';

---

1.创建数据库

create database demo;
use demo;

2.创建数据表

create table  score(
id int  primary key ,
class int,
name varchar(255),
chinese double,
math double,
english double);

3.插入数据

insert into score values (1,1,'关羽',85,56,70);
insert into score values (2,2,'张飞',70,75,70);
insert into score values (3,2,'赵云',90,65,95);
insert into score values (4,3,'刘备',97,50,50);
insert into score values (5,1,'曹操',88,90,80);
insert into score values (6,1,'司马懿',80,48,86);
insert into score values (7,3,'吕布',67,98,70);
insert into score values (8,2,'貂蝉',90,75,80);
insert into score values (9,2,'诸葛亮',100,98,100);
insert into score values (10,2,'诸葛亮',100,98,98);

题目

1.基础查询 select

1.1 查询所有数据；
1.2 查询某一列数据；
1.3 查询指定多列数据；

1.1
select * from score;

1.2
select id from score;

1.3
select id,class,name,chinese,math,english from score;

2.去重 distinct

2.1 检索不同的值，查看去重后的成绩信息。

select distinct name from score;
select distinct class from score;

3.排序 order by

3.1 对数据成绩进行排序。

select id,class,name,chinese,math,english from score order by math;

4.限制结果 limit

limit关键词可限制查询行数。
返回表的前n行：select 列名 from 表名 limit n；
返回表的特定行：select 列名 from 表明 limit n,m;(从n+1行开始，返回m行)

4.1 查看指定行信息

select * from score limit 3 //返回表的前三行
select * from score limit 3,3 //返回表的第4到第6行

Sql复杂查询

题目

1.计算字段，统计每个学生的成绩总分。

select name,sum(chinese+math+english) as total_score from score group by name;

2. 拼接字段

(1).将表中姓名和班级连起来，格式为：张飞（2）;
(2).指定分隔符(-)对数据中姓名、语数外进行连接。

select concat(name,'(',class,')') as 姓名（班级） from score;

select concat(name,'-',chinese,'-',math,'-',english) as 姓名语数外 from score; 

3.聚合函数

(1).计算各班级学生数学平均成绩

(2).计算1班中语文最高分

select class,avg(math) as avg_math from score group by class;

select class,max(chinese) as max_chinesescore from score where class=1;

4.分组, GROUP BY关键词可对数据进行分组，HAVING可对分组后的数据进行过滤。

(1).计算各班级学生数学平均成绩

(2).计算数学平均分80以上的班级

select class,avg(math) as avg_mathscore from score group by class;

select class,avg(math) as avg_mathscore from score group by class having avg_mathscore>=80;

5.case when检索函数

1.对英语成绩进行划分，>90为优秀，<=90 and >=60为良好，<60为不及格。
2.对英语成绩进行划分，>90为优秀，<60为不及格，null为缺考，其他为良好。
3.对英语成绩进行评估，统计有多少学生成绩为优秀

select name,english,case when english>90 then '优秀' 
when english>=60 and english<=90 then '良好'
when english<60 then '不及格' end as level
from score;

select name,english,case when english >90 then '优秀'
when english <60 then '不及格'
when english is null then '缺考'
else '良好' end as level
from score;

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

6.视图

1.创建视图名为‘班级人数’，按照班级分组，进行各班人数计数。

create view 班级人数 as select class,count(*) from score group by class;

7.子查询

业务场景：哪些学生的数学成绩比“张飞”的高？

select name from score where math>(select math from score where name='张飞');

8.关联子查询

1.筛选出各班级中高于班级数学平均分的人员信息。

select class,name,math from score s1 where math>(select avg(math) from score s2 where s1.class=s2.class);

Sql多表查询

创建数据库/表

进入数据库：

mysql -uroot -p123456

支持中文字符：

Set character_set_database='utf8';
Set character_set_server='utf8';

1.创建数据库

create database demo;
use demo;

2.创建学生数据表student

create table student(
           stuid int primary key ,
           student varchar(255));

3.插入学生数据

insert into student values (1,'关羽');
insert into student values (2,'张飞');
insert into student values (3,'赵云');
insert into student values (4,'刘备');
insert into student values (5,'曹操');

4.创建选课信息表class

create table class(
           id int primary key ,
           classname varchar(255),
           stuid int);

3.插入选课数据

insert into class values (1,'Python编程',1);
insert into class values (2,'SQL分析',4);
insert into class values (3,'WEB前端',3);
insert into class values (4,'统计分析',2);
insert into class values (5,'机器学习',7);

题目

1. 使用内连接查询操作列出所有选课学生信息。

select * from student inner join class on student.stuid=class.stuid;

2. 使用左连接查询所有学生的选课信息 .

select * from student left join class on student.stuid=class.stuid;

3. 将条件语句' s.stuid = 1'添加在上步骤左外连接中，比较join和where的不同。

select * from student s left join class c on s.stuid=c.stuid and s.stuid=1;

select * from student s left join class c on s.stuid=c.stuid where s.stuid=1;

3. 使用右连接查询所有课程的学生信息

select * from student s right join class c on s.stuid=c.stuid;

4. 使用全连接查询所有学生的全部选课信息。

select s.*,c.* from student s full join class c;

5. 组合查询

select stuid,student from student union select id,classname from class;

select stuid,student from student union all select id,classname from class;

6. 将两表两两连接，查询结果集。

select * from student cross join class;

select * from student,class;

7. 整合成绩单中每个班级的人数（仅做了解）

SELECT classname,COUNT(student.stuid) AS num 
FROM class 
LEFT JOIN student 
ON class.stuid=student.stuid 
GROUP BY classname;