周末作业和笔记

sql笔记
连接数据库mysql -h (ip) -P 3306 -u (账号) -p
连接自己的mysql -u root -p
断开数据库 exit
展示数据库 show databases;
选择数据库 use tetstdb;

多表联查例子
1.select * from t_student a left join t_class b on a.cid=b.id where sex='男’ order by a.age;
2.insert i into t_class (id,name) valaues (3,’la’);
3.delete from t_class where id=4;
4.update t_class set teacher=’啦啦’ where teacher is nul;
逻辑判断：
like、in、not in、is null、is not null、and、or、and not、or not
like ‘man%’’_man%’（_代表一个字符，%代表一个或多个字符）
内关联 inner join 交集
左关联left join 左表+交集
右关联right join 右表+交集

排序：默认是asc即正序，倒序用desc
limit 0,1 第0行开始取1行（0可以省略即为limit 1）

聚合函数 sum,min,max,avg,count,使用聚合函数自动排除空值
分组前查询用where，分组后查询用having
条件语句
case…when…then…end
1)以case开始end结束为一列，中间可以有多个when then
例：case when age>=30 and age <60 then ‘壮年’
when age<30 then ‘青年’
else ‘老年’
end ‘状态’
2)多列
例：CASE WHEN cname = ‘语文’ THEN score END chinese,
CASE WHEN cname = ‘数学’ THEN score END math
SQL作业

1)select avg(english) from mark;
2)select math,Name,Assress,Telno from count c join mark m on cStudentno=m.Studentno where c.Studentno in (11,22,33,44,55);
3)select Name;computer from count c join mark m on c.Studentno=m.Studentno order by computer desc;
4)select * from (select c.Studentno,Name,(english+math+computer) zcj from count c join mark m on c.Studentno=m.Studentno) where zcj>240 order by zcj desc;
2.

1)select count(*) from Student where name like ‘王%’;
2)SELECT sid FROM (SELECT sid, max(CASE WHEN cname = ‘语文’ THEN score END) chinese,max( CASE WHEN cname = ‘数学’ THEN score
END) math FROM sc JOIN course c ON sc.cid = c.id JOIN student s ON sc.sid = s.id GROUP BY sidHAVING chinese < math ) biao;
3)Select sid ,avg(score) from SC grouop by sid having avg(score)>90;
3.用SQL语句创建以下三张表，要求字段属性要求如图所示。
学生表：t_student
班级表：t_class
成绩表：t_grade
create table t_student (
id int(16) not null primary key,
sname varchar(255) not null,
sex varchar(255),
age int(16),
phone varchar(255),
address varchar(255),
cid int(255),
remark varchar(255));

create table t_class(
id int(11) not null primary key,
cname varchar(255),
teacher varchar(255),
remark varchar(255));

create table t_grade(
id int(11) not null primary key,
sid int(11),
chinese varchar(255),
math varchar(255),
english varchar(255),
remark varchar(255));
2、用SQL语句，分别在三张表中插入数据。
注意：学生表的id等于成绩表的sid，班级表的id等于学生表的cid
insert into t_student (id,sname,sex,age,phone,addres,cid,)
values(1,’詹飒’,’男’,21,’134456’,’南京’,1)，
(2,’李思’,’女’,22,34545673,’上海’,2)；

insert intot_class(id,cname,teacher)
values(1,’火箭班’,’李华’),
(2,’潜艇班’,’李媛’);

insert into t_grade (id,sid,chinese,math,english)
values(1,1,56,78,89),
(2,2,55,87,67);
3、用SQL语句对班级表学生表进行多表联查，显示班级表的班级名称和学生的id，姓名
select cname,s.id,sname from t_student s join t_class c on gs.cid=c.id;
4、用SQL语句对学生表成绩表多表联查，显示学生的id,sname和成绩表的语数外的成绩。
select s.id,sname, chinese,math,english from t_student s join t_grade g ons.id=g.id;
5、用SQL语句，对三张表进行多表联查，显示学生表的id,sname班级表的cname,成绩表的语数外成绩。
select s.id,sname,cname,chinese,math,english from t_student s join t_grade g ons.id=g.id join t_class c on gs.cid=c.id;

1、通过SQL语句，查看你写的所有文章，不显示已经删除了的。
2、通过SQL语句，查看你对所有点赞过的文章。
3、通过SQL语句，查看你所有收藏过的文章。
4、通过SQL语句，查出你所有评论过的文章。
1.
SELECT a.title,a.content,u.username FROM t_article a JOIN t_user u ON a.uid = u.id
WHERE a. STATUS != 1 AND username = ‘yinmin’;

SELECT a.title,a.content,u.username,gstatus FROM t_article_user_status aus join t_article a on aus.aid=a.id JOIN t_user u ON aus.uid = u.id WHERE username=‘yinmin’ and gstatus=0

SELECT a.title,a.content,u.username,cstatus FROM t_article_user_status aus join t_article a on aus.aid=a.id JOIN t_user u ON aus.uid = u.id WHERE username=‘yinmin’ and cstatus=0

SELECT a.title,a.content,u.username,uc.comment FROM t_user_comments uc join t_article a on uc.fid=a.id join t_user u on uc.uid=u.id
WHERE username = ‘yinmin’;