sql学习用到的db脚本

DROP TABLE student

INSERT INTO student(stu_name,stu_id) VALUE
('小六','A1000'),
('小六','A1001'),
('小白','A1002'),
('小白','A1003'),
('小红','A1004'),
('小黑','A1005'),
('丸子','A1006'),
('静香','A1007')
CREATE TABLE student(
 `id` INT(11)  PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 stu_name VARCHAR(50) COMMENT '学生名称',
 stu_class VARCHAR(50) COMMENT '学生班级',
 stu_sex VARCHAR(50) COMMENT '学生性别'
)
SELECT  *  FROM  student

DROP TABLE student

INSERT INTO student(stu_name,stu_class,stu_sex) VALUE
('张文华','二班','男'),
('李思雨','一班','女'),
('王小凤','一班','女'),
('李智瑞','四班','男'),
('徐文杰','四班','男'),
('徐雨秋','一班','女'),
('张青云','一班','男'),
('孙皓然','三班','男'),
('李春山','一班','男'),
('刘诗迪','三班','女')
SELECT  stu_class,stu_sex,COUNT(stu_sex)  FROM  student GROUP  BY stu_class,stu_sex

SELECT *  FROM  student WHERE stu_name LIKE '张%'

SELECT *  FROM  student ORDER BY id ASC LIMIT 2,3


CREATE TABLE score_table(
 `id` INT(11)  PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
 stu_name VARCHAR(50) COMMENT '学生名称',
 stu_class VARCHAR(50) COMMENT '学生班级',
 stu_score INT  COMMENT '学生成绩'
)
SELECT  *  FROM  score_table

INSERT INTO score_table(stu_name,stu_class,stu_score) VALUE
('张文华','二班',88),
('李思雨','一班',70),
('王小凤','一班',92),
('李智瑞','四班',55),
('徐文杰','四班',77),
('徐雨秋','一班',77),
('张青云','一班',56),
('孙皓然','三班',91),
('李春山','一班',93),
('刘诗迪','三班',57)

SELECT *  FROM score_table WHERE stu_score<60 AND stu_name  LIKE '张%'

CREATE TABLE month_table(
 id VARCHAR(4)  COMMENT '主键',
 person_name VARCHAR(6) COMMENT '姓名',
 month_num  INT COMMENT '销冠次数' 
)
SELECT *  FROM  month_table

TRUNCATE TABLE month_table

INSERT INTO month_table(id,person_name,month_num) VALUE
('E002','王小凤',1),
('E001','张文华',2),
('E003','孙皓然',3),
('E001','张文华',4),
('E002','王小凤',5),
('E001','张文华',6),
('E004','李智瑞',7),
('E002','王小凤',8),
('E003','孙皓然',9)

SELECT  person_name,COUNT(month_num)  FROM  month_table 
GROUP BY person_name HAVING  COUNT(month_num)<2

CREATE TABLE sale_table(
  year_num VARCHAR(4) COMMENT '年',
  month_num VARCHAR(2) COMMENT '月',
  sales INT COMMENT '销售额'
)
SELECT  *  FROM  sale_table
INSERT INTO sale_table(year_num,month_num,sales) VALUE
('2019','1',2854),
('2019','2',4772),
('2019','3',3542),
('2019','4',1336),
('2019','5',3544),
('2018','1',2293),
('2018','2',2559)
SELECT * FROM sale_table WHERE year_num='2019'