MYSQL

10、20（增，删，改，查）

CREATE TABLE `student` (   #创建一个表名称为student
  `id` INT ,   #列的名称为ID 类型为int
  `name` VARCHAR(64)
)

SELECT * FROM `student`;#查询student表中的数据*代表所有列
SELECT id FROM `student`#查询student表中id列中的所有数据

INSERT INTO `student`( `id` , `name` ) VALUE (1,"张三"); #在id与name列中插入数据，需保持列的顺序一致

UPDATE  `student` #修改student表中id列中的数据
SET  `id` =2 
WHERE  `id` =1;


DELETE FROM `student` WHERE `id` =2;#删除student表中id列为2的数据

定义别名使用as

as xxx

聚合函数

SELECT SUM(grade.chinese)  FROM `grade`

左链接，右链接，内链接

SELECT student.id,student.`name`,grade.chinese,grade.english,grade.math FROM student LEFT JOIN grade on student.id = grade.id;


SELECT student.id,student.`name`,grade.chinese,grade.english,grade.math FROM student RIGHT JOIN  grade on student.id = grade.id;


SELECT student.id,student.`name`,grade.chinese,grade.english,grade.math FROM student INNER JOIN  grade 

求和

SELECT student.id ,student.`name`,grade.chinese,grade.english,grade.math,(grade.chinese+grade.math+grade.english) as “总成绩” FROM student INNER JOIN  grade

排序
asc默认升序
desc降序

SELECT student.id ,student.`name`,grade.chinese,grade.english,grade.math,(grade.chinese+grade.math+grade.english) as “总成绩” FROM student INNER JOIN  grade ORDER BY “总成绩” DESC

分组
左链接，分组，求和，求平均值

SELECT student.`name`, grade1.* FROM grade1 LEFT JOIN student on grade1.id = student.id;
 
SELECT student.`name`, grade1.* FROM grade1 LEFT JOIN student on grade1.id = student.id GROUP BY id

SELECT student.`name`, grade1.id, SUM(score) FROM grade1 LEFT JOIN student on grade1.id = student.id GROUP BY id

SELECT grade1.type, AVG(score) FROM grade1 LEFT JOIN student on grade1.id = student.id GROUP BY grade1.type

子查询：当一个查询结果是另一个查询的条件的时候,那么就称为子查询

(SELECT student.id,student.`name`,grade.chinese,grade.english,grade.math,(grade.chinese+grade.english+grade.math)as “总成绩” FROM student INNER JOIN grade ORDER BY “总成绩” DESC
) AS biaoming

查询id = 1 和 id = 2 的结果（结果1 + 结果2）

SELECT * FROM grade WHERE id=1
UNION
SELECT * FROM grade WHERE id=2

查询id = 1 和 id = 2 的 结果（把条件放入表中进行判断 两个同时成立 结果为两个）

SELECT * FROM grade WHERE id = 1 OR id = 2

查询id = 1 的语文成绩 结果

（把两个条件放入表中进行判断）

SELECT * FROM grade1 WHERE id = 1 AND type = 1