mysql常用语法_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 左连接