1、路径:\phpstudy_pro\Extensions\MySQL5.7.26\bin
2、进入数据库:
命令: mysql.exe -h 127.0.0.1 -u root -p
3、查看数据库
命令:show databases;
特别注意:谁动谁死
提供数据:mysql
配置信息:(维护):information_schema
权限信息:performance_schema
4、创建库
命令:create database 库名;
5、使用数据库
命令: use aa;
6、删除数据库
命令:drop database 库名;
7、编码格式
命令:set names utf8;
8、读取sql文件(D:/(D盘))
命令:source D:/data.sql;
9、查看有几张表
命令:show tables;
10、创建表
命令: create table aa(
-> id int primary key auto_increment,
-> name varchar (10)) engine=myisam;
主键:primary
自增:auto_increment
数据库引擎:engine=myisam
添加一列:
alter table 表名add column class_id tinyint(11);
11、查看表结构
命令:desc 表名;
一、常用语句
添加、查询、修改、删除
添加:insert into 表名 set name=“xiao”;
查询:select * from 表名;
修改:update表名 set age=10 where id=1;
删除:delete from 表名 where id=1;
删除:
二、常用函数
dir 查看数据
- 最大值:max()
例子:1题、(成绩最高的学生?)
①select * from 表名 order by score desc limit 1;
②select * from 表名 where score = (select max(score) as score from aa);
-
最小值:min()
例子:2题、(成绩最低的学生?)
select * from 表名 where score = (select min(score) as score from aa);
-
平均数:avg()
例子:3题、(学生的平均成绩?)
select avg(score) from 表名;
-
总条数:count()
例子:4题、(求出有多少学生?)
select count(*) from 表名; -
求和:sum()
例子:
三、关键字
group by having =====分组
order by ====排序
limit ====分页
in () ====包含以内的
not in ()====包含以外的
练习:.获取每个班级里面成绩最高的学生
四、连接查询
- 左连接===left以左表为基准
- 右连接====right join
- 内连接==== inner join ===双方都满足条件的数据才显示出来
解释:
左链接 :是有数据显示数据、 没有就是null
右链接 :是有数据的凭接上去、 没有就直接删除
内链接 :把两个表的共同条件获取出来
练习题:
一、
<?
#$x==公鸡
#$y==母鸡
#$z==小鸡
for($x=1;$x<20;$x++){//公鸡
for($y=1;$y<33;$y++){//母鸡
for($z=1;$x<300;$z++){//小鸡
if(($x+$y+$z==100)&&(5*$x+3*$y+$z/3==100)){
echo $x;
echo '|';
echo $y;
echo '^';
echo $z;
echo '&';
}
}
}
}
?>
二、
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
- 思路:先查一个学生 在查所有成绩 在把成绩作对比(科目01.score > 02.socre)大于
- mysql> select * from student st inner join score sc on st.s_id = sc.s_id and sc.c_id=“01” inner join score sc_2 on st.s_id = sc_2.s_id and sc_2.c_id=“02” where sc.s_score > sc_2.s_score;
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
- 思路:先查一个学生 在查所有成绩 在把成绩作对比(科目01.score < 02.socre)小于
- mysql> select * from student st inner join score sc on st.s_id = sc.s_id and sc.c_id=“01” inner join score sc_2 on st.s_id = sc_2.s_id and sc_2.c_id=“02” where sc.s_score < sc_2.s_score;
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
- 思路:先查学生 在算平均成绩 然后算大于60
- mysql> select st.*,avg(sc.s_score) s_score from student st inner join score sc on st.s_id = sc.s_id group by st.s_id having avg(sc.s_score) > 60;
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
- 思路:先查学生 在算平均成绩 然后算小于60
- mysql> select st.*,avg(sc.s_score) s_score from student st inner join score sc on st.s_id = sc.s_id group by st.s_id having avg(sc.s_score) < 60;
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
- 思路:
- mysql> select * from student st inner join (select count(*) as num,s_id,sum(s_score) as exam_total from score group by s_id) as tmp on st.s_id=tmp.s_id;
6、查询"李"姓老师的数量
- 思路:在老师的表中 ,查询带李字的
- select * from teacher where t_name like “李%”;
7、查询学过"张三"老师授课的同学的信息
- 思路:
- mysql> select * from student where s_id in (select s_id from score where c_id = ( select c_id from teacher tea inner join course cou on tea.t_id= cou.t_id where t_name = “张三”));
8、查询没学过"张三"老师授课的同学的信息
- 思路:
- mysql> select * from student where s_id in (select distinct s_id from score where c_id in (select c_id from course where t_id in (select t_id from teacher where t_name != “张三”)));
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
- 思路:
- mysql> select * from student st inner join score sc on st.s_id = sc.s_id and sc.c_id = “01” inner join score sco on st.s_id = sco.s_id and sco.c_id = “02”;
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
- 思路:
- mysql> select a.* from student a where a.s_id in (select s_id from score where c_id=‘01’ ) and a.s_id not in(select s_id from score where c_id=‘02’);
11、查询没有学全所有课程的同学的信息
- 思路:
- 命令:
select * from student where s_id in (select s_id from score group by s_id having count(*) < (select count(*) num from course));
12、查询至少有一门课与学号为"01"的同学所学相同的同学信息
- 思路:
- 命令:
select * from student inner join (select s_id from score group by s_id having count(*) = (select count(*) as total from score where s_id= "01")) tmp on student.s_id = tmp.s_id;
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
-
思路:
-
mysql>
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
- 思路:
- mysql> select * from student where s_id in (select distinct s_id from score where c_id in (select c_id from course where t_id not in ( select t_id from teacher where t_name = “张三”)));
15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
- 思路:
- mysql> select * from student inner join (select s_id,avg(s_score) total from score where s_score <=60 group by s_id having count(*) on student.s_id = tmp.s_id;
(少了个括号)
16、检索"01"课程分数小于60,按分数降序排列的学生信息
- 思路:
- mysql> select * from student right join (select * from score where c_id = “01” and s_score <60 order by s_score desc) as tmp on student.s_id = tmp.s_id;
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
- 思路:
- mysql> select * from (select s_id,sum(s_score) sum_score,avg(s_score) avg_score from score group by s_id) tmp inner join student st on tmp.s_id= st.s_id;
18、查询每门课程被选修的学生数
- 思路:
- mysql> select count(c_id) as num,c_id from score group by c_id;
19、查询出只有两门课程的全部学生的学号和姓名
- 思路:
- mysql> select * from student where s_id in (select s_id from score group by s_id having count(*) =2);
20、查询男生、女生人数
- 思路:
- mysql> select count(*) total,s_sex from student group by s_sex;
21、查询名字中含有"风"字的学生信息
- 思路:
- mysql> select * from student where s_name like “%风%”;
mysql5.7 group by 、by 错误
-
\s :查看MySQL版本号
-
解决方式:
SET @@GLOBAL.sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;