mysql命令复习笔记

show DATABASES ;

create database nulige character set utf8;

use nulige;

show tables;

#创建表
CREATE TABLE ExamResult(

   id INT PRIMARY KEY  auto_increment,
   name VARCHAR (20),
   JS DOUBLE ,
   Django DOUBLE ,
   flask DOUBLE
);


#往表中插入数据
INSERT INTO ExamResult VALUES  (1,"yuan",98,98,98),
                               (2,"xialv",35,98,67),
                               (3,"alex",59,59,62),
                               (4,"wusir",88,89,82),
                               (5,"alvin",88,98,67),
                               (6,"yuan",86,100,55);

#查询表中所有学生的信息
select * from ExamResult;

#查询表中所有学生的姓名和对应的英语成绩
SELECT name,JS FROM ExamResult;

#过滤表中重复数据
select DISTINCT Js,name from ExamResult;


SELECT name,JS,Django,flask FROM ExamResult;


SELECT name,JS+10,Django+10,flask+20 FROM ExamResult;


SELECT name as 姓名,JS+10 as JS成绩,Django+10,flask+20 FROM ExamResult;


SELECT name JS from ExamResult;

select * from ExamResult WHERE name=‘yuan‘

SELECT name,Js from ExamResult WHERE JS>90;

SELECT name,JS from ExamResult WHERE JS!=88;

#between在什么之间  在88=100之间
SELECT name,JS FROM ExamResult WHERE JS BETWEEN 88 and 100;

#in在什么之内
SELECT name,JS FROM ExamResult WHERE JS IN (88,99,77);

#like 模糊查询，查询名字内有y的同学
SELECT name,JS FROM ExamResult WHERE name LIKE "y%";


SELECT name,JS FROM ExamResult WHERE name LIKE "a____";

#查询js分=98同学
SELECT name,JS from ExamResult WHERE name=‘yuan‘ and JS=98;


insert into ExamResult (name) VALUE (‘刘洋‘);

SELECT * from ExamResult;

#查询值为空
SELECT name from ExamResult WHERE JS is NULL;


#排序
SELECT name,JS from ExamResult WHERE JS>70 ORDER BY JS;

SELECT name,JS FROM ExamResult WHERE JS>70 ORDER BY Js DESC ;

#as重命名
SELECT name,JS+Django+flask as 总成绩 from ExamResult ORDER BY 总成绩 DESC;

#按yuan 总成绩进行降序排列（mysql语句有执行顺序：from where select group by having order by)
SELECT name,JS+Django+flask as 总成绩 from ExamResult WHERE name="yuan" ORDER BY 总成绩;


#分组查询 group by
SELECT * from ExamResult;

#按名字进行分组
SELECT * from ExamResult GROUP BY name;

#按JS进行分组
SELECT * from ExamResult GROUP BY JS;

#按第3列进行分组
SELECT * from ExamResult GROUP BY 3;

#按名字进行排序
SELECT name,sum(JS) from ExamResult GROUP BY name;

#对成绩表按名字分组后，显示每一类名字的Django的分数总和>150的
SELECT name,sum(Django) from ExamResult GROUP BY name having sum(Django)>150;

#having和where两者都可以对查询结果进行进一步的过滤，差别有：
# <1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；
# <2>使用where语句的地方都可以用having进行替换
# <3>having中可以用聚合函数，where中就不行。

SELECT * from ExamResult WHERE id=3;
SELECT * from ExamResult HAVING id=3;

#聚合函数：先把要求的内容查出来再包上聚合函数即可。

#count(列名)：统计行的个数
SELECT count(name) from ExamResult WHERE js>70;

#统计一个班级共有多少学生
select count(*) from ExamResult;

SELECT sum(JS)/count(name) from ExamResult;

SELECT AVG(JS) from ExamResult;


#遇到ifnull的时候转换成0
#统计总分大于280的人数有多少？
select count(name) from ExamResult where (ifnull(JS,0)+ifnull(Django,0)+ifnull(flask,0))>280;


SELECT * from ExamResult;

#max最大值
SELECT max(JS) FROM ExamResult;

#遇到null的时候，就转成0
#min最小值
SELECT min(ifnull(JS,0)) FROM ExamResult;
SELECT max(JS+Django+flask) from ExamResult;

#limit 跳过几条显示几条
SELECT * FROM  ExamResult LIMIT 1;

#跳过1,从2开始到5
SELECT * FROM  ExamResult LIMIT 1,4;