MySQL常用语法

create table test_xia_10 (id varchar (600),name varchar (600),comments varchar (600)); # 创建表
desc test_xia_10;#--查看表结构
show tables;#--列出所有表
DROP table test_xia_10; # 删除表
SELECT * FROM student; -- *是所有列的意思。（--注释）
SELECT s_name,Sex,Age FROM student; -- 查询多行 3列
SELECT * FROM student WHERE s_name ='张三'; -- WHERE 条件语句。
SELECT * FROM student WHERE Sex='男';
SELECT * FROM student WHERE s_name='张三' AND sex='女'; -- and(和)必须满足两个条件
SELECT * FROM student WHERE s_name='张三' OR sex='女'; -- or(或)无论满足任何一个条件都会显示
SELECT * FROM student WHERE s_name LIKE '%阳%'; -- lIKE 操作符用于在WHERE 子句中搜索列中的指定模式。%替代一个或多个字符
SELECT * FROM student WHERE s_name ='李四' OR s_name ='王二';
SELECT * FROM student WHERE s_name IN ('李四' ,'王二');-- IN 操作符允许我们在WHERE 子句中规定多个值。
SELECT * FROM student WHERE s_name NOT IN ('baby','王二'); -- not IN 不包含。
SELECT * FROM student ORDER BY Age ; -- 升序
SELECT * FROM student ORDER BY Age DESC; -- 降序-- ORDER BY 语句用于对结果集进行排序。desc 降序排列
SELECT * FROM student WHERE s_name='阿克琉斯' ORDER BY s_id DESC;
SELECT AVG(Grade) FROM score; -- AVG 函数返回数值列的平均值。NULL 值不包括在计算中。
SELECT MAX(Grade) FROM score; -- MAX 函数返回一列中的最大值。NULL 值不包括在计算中。
SELECT MIN(Grade) FROM score; -- MIN 函数返回一列中的最小值。NULL 值不包括在计算中。
SELECT SUM(Grade) FROM score; -- sum函数返回一列中总数。NULL 值不包括在计算中
SELECT COUNT(*) FROM score WHERE Grade=100; -- 统计表中有多少行 -- COUNT() 函数返回匹配指定条件的行数。
SELECT * FROM student LIMIT 2; -- LIMIT 在mysql里面中选取前几行
SELECT * FROM student LIMIT 3,4; -- 选取第4-7行的数据 -- 范围（从左边的行数开始不包含当前值，右边行数是左边加右边的和）
INSERT INTO student (S_ID,s_name,Sex,Age) VALUES ('015','王二','男','46'); -- INSERT INTO 插入一行
UPDATE student SET Age=47 WHERE S_ID='015'; -- UPDATE 更改表数据
DELETE FROM student WHERE S_ID='015'; -- 删除表数据
SELECT sex FROM student GROUP BY Sex;对性别分组;
SELECT S_ID FROM score GROUP BY S_ID;对序列分组;
SELECT S_ID, MAX(Grade) FROM score GROUP BY S_ID ; -- GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。
SELECT S_ID, MAX(Grade) FROM score GROUP BY S_ID HAVING S_ID=003; -- having 条件，结合group by使用
SELECT student.`s_name`,score.`Grade` FROM student LEFT JOIN score ON student.`S_ID`=score.`S_ID` WHERE student.`s_name`='王二'; -- 王二考了多少分 --多表连查： LEFT JOIN + NO 左连接
SELECT student.`s_name`,course.`C_NAME`,score.`Grade` FROM student LEFT JOIN score ON student.`S_ID`=score.`S_ID` LEFT JOIN course ON course.`C_ID`=score.`C_ID` WHERE student.`s_name`='王二'; -- 王二每门课程考了多少分 --多表连查： LEFT JOIN + NO 左连接