MySQL增删改查的一些练习代码

SHOW DATABASES;
SHOW TABLES;-- 查询所有数据
SELECT
    * 
FROM
    stu;-- 给指定列添加数据
-- INSERT INTO 表名(列名1, 列名2，.....) VALUES(值1，值2，......);
INSERT INTO stu ( id, NAME )
VALUES
    ( 1, '张三' );-- 给所有列添加数据
INSERT INTO stu ( id, NAME, gender, birthday, score, email, tel, STATUS )
VALUES
    ( 2, '李四', '男', '1999-11-11', 88.88, 'lisi@itcast.cn', '1388888888', 1 );-- 给所有列添加数据，列名和列表可以省略
INSERT INTO stu
VALUES
    ( 2, '李四', '男', '1999-11-11', 88.88, 'lisi@itcast.cn', '1388888888', 1 );
-- 修改数据 UPDATE 表名 SET 列名1=值1，列名2=值2，...[WHERE 条件];
-- 将张三的性别改为女

UPDATE stu SET gender = '女' WHERE name = '张三';
SELECT * from stu;
UPDATE stu SET gender = '女', birthday = '1999-12-12', score = 99.99 WHERE name = '张三';

-- 注意: 如果update语句没有加where条件，则会将表中所有数据全部修改！

-- 删除数据 DELETE FROM 表名 [WHERE 条件];

-- 删除张三记录

DELETE from stu WHERE name = '张三';
DELETE from stu;

-- 注意:  如果delete语句没有加where条件，则会将表中所有数据全部修改

desc stu;

-- 删除表
drop table stu;

-- 创建表

CREATE TABLE stu(
id INT, -- 编号
name VARCHAR(20), -- 姓名
age INT, -- 年龄
gender VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math DOUBLE(5, 2), -- 数学成绩
english DOUBLE(5, 2), -- 英语成绩
hire_date DATE -- 入学成绩
);


-- 添加数据
INSERT INTO stu(id,name,age,gender,address,math,english,hire_date)
VALUES
(1,'马运',55,'男','杭州',66,78,'1995-09-01'),
(2,'马花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'马斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
(6,'刘德花',57,'男','香港',99,99,'1998-09-01'),
(7,'张学右',22,'女','香港',99,99,'1998-09-01'),
(8,'德玛西亚',18,'男','南京',56,65,'1994-09-02');


SELECT * from stu;


-- 基础查询 =====================

-- 查询 name age两列

SELECT name,age from stu;


-- 查询所有列的数据, 列名的列表可以使用*替代。不要使用！代码规范问题，不容易理解查询的是什么东西


SELECT * FROM stu;

-- 查询地址信息

SELECT address from stu;


-- 去除重复记录

SELECT DISTINCT address from stu;


-- 查询姓名、数学成绩、英语成绩，as：起别名，可以不写，别名和本名空格隔开
SELECT name,math  数学成绩,english  英语成绩 from stu;


-- 条件查询 =========================

-- 1.查询年龄大于20岁的学员信息

SELECT * FROM stu WHERE age > 20;

-- 2.查询年龄大于等于20岁的学员信息

SELECT * FROM stu WHERE age >= 20;

-- 3.查询年龄大于等于20岁 并且年龄小于等于30岁的学员信息

SELECT * FROM stu WHERE age >= 20 && age <= 30; -- 不推荐
SELECT * FROM stu WHERE age >= 20 and age <= 30;
SELECT * FROM stu WHERE age BETWEEN 20 AND 30;

-- 4.查询入学日期在'1998-0901' 到 '1999-09-01' 之间的学员信息

SELECT * FROM stu WHERE hire_date BETWEEN '1998-09-01' and '1999-09-01';

-- 5.查询年龄等于18岁的学员信息

SELECT * FROM stu WHERE age = 18;

-- 6.查询年龄不等于18岁的学员信息

SELECT * FROM stu WHERE age != 18;
SELECT * FROM stu WHERE age <> 18;

-- 7.查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息

SELECT * FROM stu WHERE age = 18 or age = 20 or age = 22;
SELECT * FROM stu WHERE age = 18 || age = 20 || age = 22; -- 不推荐
SELECT * FROM stu WHERE age in (18,20,22);
  
-- 8.查询英语成绩为null的学员信息
-- 注意： null值的比较不能使用 = != 需要使用is is not
SELECT * FROM stu WHERE english is not null;

-- 模糊查询 like ===================
/*
   通配符：
       (1) _: 代表单个任意字符 
         (2) %: 代表任意个数字符
*/

-- 1.查询姓'马'的学员信息

SELECT * FROM stu WHERE name LIKE '马%'; 

-- 2.查询第二个字是'花'的学员信息

SELECT * FROM stu WHERE name LIKE '_花%';
 
-- 3.查询名字中包含'德'的学员信息

SELECT * FROM stu WHERE name LIKE '%德%';

-- 排序查询 ===============================

/*
   排序查询：
        * 语法：SELECT 字段列表 FROM 表名  ORDER BY 排序字段名1 排序方式1, 排序字段名2 排序方式2......;
            * 排序方式：
                   * ASC: 升序排列(默认值)
                         * DESC：降序排列

*/

-- 1. 查询学生信息，按照年龄升序排列

SELECT * FROM stu ORDER BY age ASC;

-- 2. 查询学生信息，按照数学成绩降序排列

SELECT * FROM stu ORDER BY math DESC;

-- 3.查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列

SELECT * FROM stu ORDER BY math DESC, english ASC;

-- 聚合函数 =====================================

/*
  概念：将一列数据作为一个整体，进行纵向计算
    分类：
         1.count(列名)   统计数量(一般选用不为null的列)
               * 取值：
                      1. 主键(非空且唯一)
                            2. * (推荐，只要一行有一个有数据就可以统计上，而且会计算最快的那一行)
             2.max(列名)     最大值
       3.min(列名)     最小值
             4.sum(列名)     求和
       5.avg(列名)     平均值

  语法：
          SELECT 聚合函数名(列名)  FROM 表；
              注意：null值不参与所有聚合函数运算
*/

-- 1.统计班级一共有多少个学生

SELECT * FROM stu;
SELECT COUNT(id) FROM stu; 
SELECT COUNT(english) FROM stu; -- count 统计的列名不能为null
-- 2.查询数学成绩的最高分

SELECT max(math) FROM stu;

-- 3.查询数学成绩的最低分

SELECT min(math) FROM stu;

-- 4.查询数学成绩的总分

SELECT sum(math) FROM stu;

-- 5.查询数学成绩的平均分

SELECT avg(math) FROM stu;

-- 6.查询英语成绩的最低分

SELECT min(english) FROM stu;


-- 分组查询 ===========================

/*
   分组函数
         SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤].......;
        注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
        WHERE 和 HAVING 区别：
        *  执行时机不一样：WHERE是分组之前进行判定，不满足where条件, 则不参与分组，而having是分组之后对结果进行过滤。
        *  可判断的时机不一样：WHERE 不能对聚合函数进行判断，HAVING可以。
        
        执行顺序：WHERE > 聚合函数 > having
        
             
*/
SELECT * FROM stu;
-- 1. 查询男同学和女同学各自的数学平均分

SELECT gender, avg(math) FROM stu GROUP BY gender;

-- 2. 查询男同学和女同学各自的数学平均分，以及各自人数

SELECT gender, avg(math), count(*) FROM stu GROUP BY gender;

-- 3. 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组

SELECT gender, avg(math), count(*) FROM stu WHERE math >= 70 GROUP BY gender;

-- 4. 查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2


SELECT gender, avg(math), count(*) FROM stu WHERE math >= 70 GROUP BY gender HAVING count(*) > 2;

-- 分页查询 ================================
/*
   分页查询：
             SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询条目数
                     * 起始索引：从0开始
                     起始索引 = (当前页码 - 1) * 每页显示的条数;
                     tips：
                     分页查询limit是mysql数据库的方言
                     oracle分页查询使用rownumber
                     sqlserver 分页查询使用top
*/

SELECT * FROM stu;

-- 1. 从0开始查询，查询3条数据

SELECT * FROM stu LIMIT 0, 3;

-- 2. 每页显示3条数据，查询第1页数据

SELECT * FROM stu LIMIT 0, 3;

-- 3. 每页显示3条数据，查询第2页数据

SELECT * FROM stu LIMIT 3, 3;

-- 4. 每页显示3条数据，查询第3页数据 

SELECT * FROM stu LIMIT 6, 3;