mysql命令练习

-- 创建库
CREATE DATABASE teacher;
-- 创建student表
CREATE TABLE student3(id INT,NAME VARCHAR(20),age INT,sex VARCHAR(100),math INT,english INT);

-- 插入字段
-- 插入全表数据
INSERT INTO student3 VALUES(1,'高圆圆',43,'女',88,22);
-- 插入全表数据，批量插入
INSERT INTO student3 VALUES(2,'张三',32,'女',78,56),(3,'高圆',55,'女',84,22);
-- 插入部分字段
INSERT INTO student3(id,NAME,math)VALUES(6,'新技术',44);
-- 插入部分字段，批量插入
INSERT INTO student3(id,NAME,math)VALUES(7,'新技术',45),(6,'新技术',44);

-- 删除
-- 删除全部delete from 表名；只是删除了当前表中的所有记录数据,但是表的结构还存在,不影响数据库数据约束"自增长约束"的id值
DELETE FROM student3;
-- 删除全表数据
TRUNCATE TABLE student3;

-- --------------------------------------------------------------------------------------
CREATE TABLE student ( 
 id INT, -- 编号
 NAME VARCHAR(20), -- 姓名
 age INT, -- 年龄 
 sex VARCHAR(5), -- 性别 
 address VARCHAR(100), -- 地址 
 math INT, -- 数学
 english INT -- 英语 
 );

INSERT INTO student(id,NAME,age,sex,address,math,english)
VALUES (1,'马云',55,'男',' 杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩 ',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港 ',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);

SELECT 
	s.id AS '编号',
	s.name '姓名',
	s.age '年龄',
	s.sex '性别',
	s.address '地址',
	s.math '数学成绩',
	s.`english` '英语成绩' 
FROM student AS s ; -- 给表起了一个别名(as 可以省略)

-- 查询年龄大于等于20岁的学生信息(编号,姓名,年龄,数学,英语)
SELECT
 a.`id`,
 a.`NAME`,
 a.`age`,
 a.`math`,
 a.`english`
FROM
 student AS a;
WHERE
age >= 20;

-- 查询年龄在20和30岁之间的学生信息(所有信息)
SELECT
 *
FROM
 student
WHERE
age>=20 AND age<=30;

-- 查询年龄不是20岁的学生的信息
SELECT
*
FROM
student
WHERE
age<>20;

-- 查询英语成绩是null的学生信息
SELECT
*
FROM
student
WHERE
english
IS NOT NULL;

-- 查询年龄是18岁或者20岁或者55岁的学生信息 --- or
SELECT
*
FROM
student
WHERE
age = 18 OR age = 55;
-- 优化 多个或者的关系 in集合语句:in(值1,值2,值3) ;
SELECT
*
FROM
student
WHERE
age IN(18,55,20);

-- 查询所有的信息,包括数学和英语总成绩
SELECT
s.`id`,
s.`NAME`,
s.`age`,
s.`sex`,
s.`address`,
(s.`math`+IFNULL(s.`english`,0))'总成绩'
FROM
student s;

-- 模糊查询
-- select 指定的字段列表 from 表名 where 字段名称 like '%xx%'
-- 模糊查询所有姓马的学生信息
SELECT 
*
FROM
student
WHERE
NAME LIKE '%马%'

-- 模糊查询第二个字包含化的学生信息
SELECT
*
FROM
student
WHERE
NAME LIKE '%_化%'

-- 模糊查询姓名是三个字的学生信息
SELECT
*
FROM
student
WHERE
NAME LIKE '%___%'

-- 聚合函数查询--->查询的结果:单行单例的
-- count(字段):通过指定字段:查询总记录数
-- avg(字段):针对某个字段求平均分
-- sum(字段):针对某个字段求和
-- max(字段):针对某个字段获取里面的最大值
-- min(字段):针对某个字段获取里面的最小值
-- 查询当前表中记录数(开发中都是使用的非业务字段:
-- 一般情况每一张表主键自增长的id值),业务字段不去使用它

-- --------------------------------------------------------------------------------------
-- 查询英语的总人数
-- 有一个值是null，没有统计上
SELECT
 COUNT(english)'总人数'
FROM
student;

-- 查询英语的总人数
SELECT
COUNT(IFNULL(english,0))
FROM
student;

-- 查id的总记录数
SELECT
COUNT(id)'总记录数'
FROM
student;

-- 求数学成绩的平均分avg
SELECT
AVG(math)'数学平均分'
FROM
student;

-- 求英语成绩的平均分avg
SELECT
AVG(english)'英语平均分'
FROM
student;

-- 求数学成绩的总分sum
SELECT
SUM(math)'数学总分'
FROM
student;

-- 求英语成绩的总分sum
SELECT
SUM(english)'英语总分'
FROM
student;

-- 求数学成绩的最大值max
SELECT
MAX(math)'数学成绩的最大值'
FROM
student;

-- 求英语成绩的最大值max
SELECT
MAX(english)'英语成绩的最大值'
FROM
student;

-- 求数学成绩的最小值min
SELECT
MIN(math)'数学成绩的最小值'
FROM
student;

-- 求英语成绩的最小值min
SELECT
MIN(english)'英语成绩的最小值'
FROM
student;

-- --------------------------------------------------------------------------------------
-- select 嵌套select语句
-- 按学生的数学成绩进行升序排序
-- 排序查询order by 字段名称 (排序规则) asc 或者desc
-- 按学生的数学成绩进行升序排序
SELECT 
s.`id`,
s.`NAME`,
s.`math`
FROM
student s
-- 不加默认升序
ORDER BY math;

-- 按学生的数学成绩进行降序排序
SELECT
s.`id`,
s.`NAME`,
s.`math`
FROM
student s
ORDER BY math DESC;

-- 按学生的英语成绩进行升序排序
SELECT
s.`id`,
s.`NAME`,
s.`english`
FROM
student s
ORDER BY english ASC;

-- 按学生的英语成绩进行降序排序
SELECT
s.`id`,
s.`NAME`,
IFNULL(s.`english`,0)
FROM
student s
ORDER BY english DESC;


-- 同时先数学成绩升序,英语成绩降序排序
-- select 字段列表 from 表名 order by 字段名称1 排序方式1,字段名称 排序方式2...
SELECT
*
FROM
student
ORDER BY 
math ASC,english DESC;

-- --------------------------------------------------------------------------------------
-- 分组查询group by
-- group by 的后面可以跟的分组字段,
-- select 分组字段 ,聚合函数 from 表名 group by 分组字段;

-- 将学生按照性别进行分组同时查询出数学平均分
SELECT
sex'性别',
AVG(math)'数学平均分'
FROM
student 
GROUP BY -- 分组查询
sex;

-- 将学生按照性别进行分组同时查询出英语平均分
SELECT
sex,
AVG(math)'英语平均分'
FROM
student
GROUP BY -- 分组查询
sex;

-- group by 和 where 一块使用
-- 需求:按照性别分组,查询他们的对应的平均分
-- 条件:数学成绩大不于70分的人不参与分组  where math > 70 ;
SELECT
sex'性别',
AVG(math)'数学平均分'
FROM
student
WHERE math > 70
GROUP BY sex;

-- 需求:按照性别分组,查询他们的对应的平均分
-- 条件:英语成绩大不于60分的人不参与分组 
SELECT
sex'性别',
AVG(english)'英语平均分'
FROM
student
WHERE english > 60
GROUP BY sex;

-- --------------------------------------------------------------------------------------
-- hving :筛选查询
-- having后面可以聚合函数的
-- group by之前是where 条件,group by分组之后,后面可以使用having 

-- 需求:按照性别分组,查询他们的对应的平均分
-- 条件:数学成绩大不于70分的人不参与分组  where math > 70 ;
-- 筛选出总人数大于2的信息
SELECT
  sex'性别',
  AVG(math)'数学平均分',
  id'人数'
FROM
  student
WHERE 
  math > 70  -- 条件满足
GROUP BY 
  sex -- 参与分组
HAVING 
  COUNT(id)>2; 



SELECT 
   sex '性别',
   AVG(math) '数学平局分',
   COUNT(id)  人数
FROM
student 
WHERE math > 70  -- 条件满足
GROUP BY sex    -- 参与分组

HAVING 人数 >2 ;

-- --------------------------------------------------------------------------------------
-- 创建Student2表
CREATE TABLE Student2(id INT,NAME VARCHAR(10),sex VARCHAR(10),age INT);

-- 插入数据
INSERT INTO student2(id,NAME,sex,age)
VALUES(1,'name1','man',23),(2,'name2','woman',21),(3,'name3','man',30);

-- 查询年龄大于25岁的男同学信息
SELECT * FROM student2 WHERE age>25;

-- 修改id为2的同学的年龄为26
UPDATE student2 SET age = 26 WHERE id = 2;

-- 删除掉名字为name3的学生
DELETE 
FROM
student2
WHERE
NAME = 'name3';

-- 求学生中年龄最大的同学信息
SELECT * FROM student2 WHERE age = (SELECT MAX(age) FROM student2);

-- 求所有学生年龄的平均值
SELECT
AVG(age)'学生年龄平均值'
FROM
student2;

-- `student2`
SELECT
*
FROM
student2
WHERE
age > (SELECT AVG(age) FROM student2);

-- 分页查询
SELECT
*
FROM
student
LIMIT
4,2;

-- 获取所有信息
SELECT* FROM student2;
-- --------------------------------------------------------------------------------------