更新来删除数据:UPDATE Student age= NULLWHERE name = '张三'
4:删除数据:DELETE FROM 表名 WHERE (过滤条
例如:DELETE FROMStudent WHERE name = '张三
注:删除全表数据除去过滤条件即可,也可使用TRUNCATE TABLE 表名
简单数据查询
1:查询:SELECT 列名 FROM 表名;
例如:SELECT id FROM Student;
查询全表数据:SELECT * FROM Student;
2: 表名前缀:SELECT Student.id FROM Student;
3:列表别名:SELECT T.列 A AS A,T.列B AS B,T.列 C AS C FROM 表 AS T;
4:计算列:SELECT id , mark*2 AS MarksFROM Student;
SELECT FirstName + ' ' +LastName AS FullName FROM Student;
5:排除重复数据:SELECT DISTINCT 列A FROM 表名;\
例如:SELECT DISTINCT name FROM Student;
6:限定行数查询:SELECT TOP rowCount ColumA,ColumB FROM Table;
例如: SELECT TOP5 id , name FROM Student;
7:条件查询 WHERE
8:范围查询 BETWEEN
9:定义集合关系 IN
10:模糊查询 LIKE(单个字符_ 多个字符%)
11:空值数据控制:SELECT 列A ,列B FROM 表名 WHERE 列C IS NOT NULL;
SELECT 列A,列B FROM 表名 WHERE 列C IS NULL;
12:排序:升序。。ASC
降序。。。DESC
聚合函数与分组
1:SELECT COUNT(规范) FROM 表名;
A:数目 COUNT
B:总和 SUM
C:平均值AVG
D:最大值MAX
F:最小值 MIN
2:分组 GROUP BY..
3:结果集处理:SELECT StudentID,AVG(Mark) AS AverageMark FROM StudentExam GROUP BYStudentID HAVING AVG(Mark) <50 OR AVG(Mark)>70;
4:EXISTS;
5:ALL;
6:ANY;
7:UNION;
8:保留重复行:UNION ALL;
9:交集和差分:INTERSECT EXCEPT
联接
1:内联接:JOIN
2:外连接:1:左外联:LEFT JOIN或 LEFT OUTER JOIN
2:右外联:RIGHTJOIN 或 RIGHT OUTERJOIN
3:全外联:FULL JOIN 或 FULL OUTER JOIN
以上就是小编为大家带来的细数MySQL中SQL语句的分类全部内容了,希望大家多多支持~
更多SQL内容来自木庄网络博客