请选择 进入手机版 | 继续访问电脑版
 找回密码
 立即注册
  • 便民服务
  • 关注我们
  • 社区新手
作业】:
建表语句
#创建班级表
#create table grade(class int(4),chinese int(4),english int(4),math int(4),name varchar(20),age int(4),sid int(4));

#插入数据
/*insert into grade values(1833,86,90,40,'zhangsan',1,21),(1832,55,86,66,'lisi',2,22),(1833,93,57,98,'zhaoliu',3,23),
(1832,84,90,88,'wangwu',4,24),(1833,93,57,22,'lijiu',5,25),
(1832,84,98,77,'niuqi',6,26),(1832,56,57,77,'liuli',7,27),(1833,48,58,88,'wangbo',8,28),
(1832,78,57,88,'wangsan',9,29),(1833,87,60,65,'wanggan',10,30),
(1832,80,76,88,'wangping',11,31),(1833,NULL,79,88,'wanghui',12,32); */

#作业
#查询1832班级的成绩信息
#select class,chinese,english,math from grade where class = 1832;

#查询1833班,语文成绩大于80小于90的成绩信息
#select class,chinese,english,math from grade where chinese > 80 and chinese < 90;

#查询学生表中5到10行的数据
#select * from grade limit 4,6;

#显示1832班英语成绩为98,数学成绩为77的姓名与学号
#select name,sid from grade where class = 1832 and english = 98 and math = 77;

#查询出1832班成绩并且按语文成绩排序(降序)
#select class,chinese,english,math from grade where class = 1832 order by chinese desc;

#查询1833与1832班,语文成绩与数学成绩都小于80的姓名
#select name from grade where (class = 1833 or class = 1832) and chinese<80 and math <80;
#select name from grade where class in(1833,1832) and chinese < 80 and math < 80;


#查询出没有参加语文考试的学生姓名和班级
#select name,class from grade where chinese is null;

#求出班上语文成绩不及格的学生姓名
#select name from grade where chinese <60 or chinese is null;

#求出每个班的数学平均成绩
#select avg(math),class from grade group by class;

#求出每个班级的语文成绩总分--涉及到每个的时候需要分组
#select sum(chinese),class from grade group by class;

#将语文成绩不及格的学生成绩改为60
#update grade set chinese = 60 where chinese < 60 or chinese is null;

#三科分数都大于70分的人名和年纪
#select name,age from grade where chinese >70 and english>70 and math >70;

#求出英语分数高于70且其他任何一科大于60的人和班级
#select class,name from grade where  english>70 and (math >60 or chinese >60);

#统计每个班的人数
#select class,count(*) from grade group by class;

#求每个班数学成绩大于80的人数
#select class,count(*) from grade where math>80 group by class;

#求出每个班英语成绩最高的那个人的姓名和班级名称
#
select class,max(english)g from grade group by class;   ==》先求出每个班英语最高分然后和grade 表继续连表
select * from grade;
select t.name,t.class from grade t,(select class,max(english)g from grade group by class)b where t.class =b.class and t.english = b.g;



12、navicat  的应用

快捷键
ctrl+q   新建一个窗口
ctrl+w   关闭窗口
ctrl+r   运行语句


navicat 如果连接不上
1)service mysqld restart    ==》重启数据库
2)service iptables stop    ==》关闭防火墙
3)grant all privileges on *.* to  'root'@'%' identified  by '123456';  ==》开放远程权限

注释
#   ==》单行注释

连续多行注释   /*     */

/*    ==>段注释
/*create table aa(id int(1) PRIMARY key,name char(20));
create table cc(s_id int(1) PRIMARY key,score char(20));
insert into aa(id,name)values(1001,'zhangsan'),(1002,'lisi');
insert into cc(s_id,score)values(1001,'99'); */

1、基本连接 ==》2个表中有字段值相同则可以进行连接,结果只展示相同字段的数据
select *from aa,cc where aa.id = cc.s_id;

2、内连接  ==》2个表中有字段值相同则可以进行连接,结果只展示相同字段的数据
select * from aa inner join cc on aa.id = cc.s_id;

3、左连接  ==》以左表为主,展示左表的全部数据,右表没有的数据以null填充
select * from aa left join cc on aa.id = cc.s_id;

4、右连接  ==》以右表为主,展示右表的全部数据,左表没有的数据以null填充
select * from aa right join cc on aa.id = cc.s_id;

5、硬链接  ==》2张表的字段必须相同
select * from aa union select * from cc;

求zhansan 的成绩?
select score from aa,cc where aa.id = cc.s_id and name = 'zhangsan';

6、临时表法
select * from aa,cc where aa.id = cc.s_id  t临时表
select t.score from (
select * from aa,cc where aa.id = cc.s_id)t where t.name = 'zhangsan';

7、嵌套法  =
select id  from aa where name = 'zhangsan';  ==>1001  

select score from cc where s_id = (select id  from aa where name = 'zhangsan');  ==>1001

8、嵌套 in

select id  from aa where name = 'zhangsan';  ==>1001  

select score from cc where s_id in(select id  from aa where name = 'zhangsan');


求出没有参加考试
select id from aa;   ==>1001 1002
select name from aa where id not in(select s_id from cc); ==>1001

select name from aa LEFT JOIN cc on aa.id = cc.s_id where score is null;

select name from cc RIGHT JOIN aa on aa.id = cc.s_id where score is null;
【处理msyql数据库中文乱码的问题】
1)vim /etc/my.cnf   ==>数据库的配置文件
2)加入这行 character_set_server=utf8
3)重启数据库
4)dcs这个库右键属性把latin 为utf8编码格式(utf8--UTF-8 Unicode)
   排序规则 utf8_general_ci


分享至 : QQ空间
收藏

0 个回复

您需要登录后才可以回帖 登录 | 立即注册