作业】:
建表语句
#创建班级表
#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
|
|