数据库SQL之DML语句的使用

作者：叁念

---

数据库SQL之DML语句的使用

1.DML 数据操控语句

• 用于添加、删除、更新和查询数据库记录，并检查数据完整性。
  常用的语句关键字包括insert、delete、udpate、select等。

2.基本用法 :

2.1【数学函数】

求绝对值SELECT ABS(-5.1);求出比这个数大的最小的整数SELECT CEILING(0.1);求出比这个数小的最大的整数SELECT FLOOR(-0.1);求出最大值SELECT GREATEST(5,3,2,12);求出最小值SELECT LEAST(5,3,2,12);取余数SELECT MOD(3, 2);四舍五入SELECT ROUND(10.555999, 3);截断SELECT TRUNCATE(10.999, 1);求随机数SELECT RAND(10);开根号SELECT SQRT(25);求几的几次方SELECT POW(2,3);

【字符串函数】

求字符串的长度SELECT LENGTH('hello world');SELECT LENGTH('你好');全部变小写SELECT LCASE('HeLLo');SELECT LOWER('HeLLo');全部变大写SELECT UCASE('hello');SELECT UPPER('hello');字符串比较 前面一个大结果就是1，前面一个小结果就是-1，前后一样就是0SELECT STRCMP('zello','yes');子串的位置SELECT POSITION('lo' IN 'hello');替换SELECT REPLACE('yes','y','hh');插入 在hello的第2个位置开始，替换1个字符，插入xxSELECT INSERT('hello',2,1,'xx');拼接SELECT CONCAT('hello ','world ','sql');SELECT CONCAT_WS(';',1,2,3);取左边的字符SELECT LEFT('hello', 3);取右边的字符SELECT RIGHT('hello', 3);左边填充SELECT LPAD('hello',10,'x');右边填充SELECT RPAD('hello',10,'y');去空格SELECT LTRIM('          hello          ');SELECT RTRIM('          hello          ');SELECT TRIM('          hello          ');子串SELECT SUBSTRING('51code',3,4);

【日期与时间函数】

当前的时间SELECT NOW();SELECT SYSDATE();当前日期SELECT CURRENT_DATE();当前的时间SELECT CURRENT_TIME();当前的时间戳SELECT CURRENT_TIMESTAMP();打印年份SELECT YEAR('97-08-24');打印月份SELECT MONTH('97-08-24');打印月份名SELECT MONTHNAME('97-08-24');打印是一年中第几天SELECT DAYOFYEAR('2017-8-24');日期的加法SELECT DATE_ADD(NOW(),INTERVAL 3 DAY);

【聚合函数】

求平均值SELECT AVG(sheight) FROM student;求和SELECT SUM(sage) FROM student;求最大值SELECT MAX(sheight) FROM student;求最小值SELECT MIN(sheight) FROM student;求个数SELECT COUNT(sid) FROM student;求标准差SELECT STDDEV(sheight) FROM student;求方差SELECT VARIANCE(sheight) FROM student;

【单表查询】

SELECT * FROM student;SELECT sname, sage FROM student;别名查询SELECT sname AS 姓名, sage AS 年龄 FROM student;SELECT sname 姓名, sage 年龄 FROM student;去重查询查询有多少老师SELECT DISTINCT teacher FROM lesson;查询有参加考试的学生的学号SELECT DISTINCT sid FROM score;运算查询查询所有人的分数 - 20 分之后的分数SELECT score - 20 FROM score;查询所有学生的年龄增加一岁之后的年龄SELECT sage + 1 FROM student;条件记录查询查询分数大于70分的所有记录SELECT * FROM score WHERE score > 70;查询身高在170到180之间的所有记录SELECT * FROM student WHERE sheight BETWEEN 170 AND 180;SELECT * FROM student WHERE sheight >= 170 AND sheight <= 180;查询身高小于160或大于190的所有记录SELECT * FROM student WHERE sheight < 160 OR sheight > 190;空值查询查询年龄是空的记录SELECT * FROM student WHERE sage IS NULL;查询年龄不是空的记录SELECT * FROM student WHERE sage IS NOT NULL;集合中记录查询查询身高是170,180,190的所有记录SELECT * FROM student WHERE sheight IN(170,180,190);查询身高不是170,180,190的所有记录SELECT * FROM student WHERE sheight NOT IN(170,180,190);模糊查询 %表示多个字符 _表示一个字符查询姓王的学生的记录SELECT * FROM student WHERE sname LIKE '王%';查询姓张但是名字只有2个字的学生记录SELECT * FROM student WHERE sname LIKE '张_';查询第二个字是飞的学生记录SELECT * FROM student WHERE sname LIKE '_飞%';排序查询默认是升序SELECT * FROM student ORDER BY sheight;SELECT * FROM student ORDER BY sheight ASC;降序SELECT * FROM student ORDER BY sheight DESC;多字段排序，当身高一样的时候，年龄小的在前面SELECT * FROM student ORDER BY sheight DESC, sage ASC;限制数量查询（重点）总共读5条记录SELECT * FROM student LIMIT 5;从第(2+1)条记录开始，再读5条记录SELECT * FROM student LIMIT 2,5;分组查询SELECT lid, AVG(score) FROM score GROUP BY lid;

【多表查询】

交叉连接 10 * 4 , 9 * 3 = 90 * 7SELECT * FROM student, score;SELECT * FROM student CROSS JOIN score;内连接SELECT * FROM student,score WHERE student.sid = score.sid;SELECT * FROM student INNER JOIN score WHERE student.sid = score.sid;SELECT * FROM score, student WHERE student.sid = score.sid;外连接左外连接 - 左表记录完全保留的情况下，把右表拼接上去SELECT * FROM student LEFT JOIN score ON student.sid = score.sid;右外连接SELECT * FROM score RIGHT JOIN student ON student.sid = score.sid;1. 打印出所有学生的基本信息，以及1号课程的考试分数SELECT * FROM student LEFT JOIN score ON student.sid = score.sid AND score.lid = 1;2. 打印出所有考试分数的信息，以及对应的学生信息，以及对应的任课老师SELECT score.*, student.sname, student.sage, student.sheight, teacherFROM score,student,lesson WHERE score.sid = student.sid AND score.lid = lesson.lid;【子查询】单行单列子查询查询比王五年龄大的学生信息SELECT * FROM student WHERE sage > (SELECT sage FROM student WHERE sname = '王五');查询考了100分的学生记录SELECT * FROM studentWHERE sid = (SELECT sid FROM score WHERE score = 100)SELECT *FROM student,scoreWHERE student.sid = score.sid AND score = 100;单行多列子查询查询年龄和身高都和王五一样的学生信息SELECT *FROM studentWHERE (sage, sheight) = (SELECT sage, sheight FROM student WHERE sname = '王五');多行单列子查询1. IN 关键字 - 查询的条件在子查询的查询结果中查询考了85分的学生信息SELECT *FROM studentWHERE sid IN (SELECT sid FROM score WHERE score = 85);2. ANY 关键字 - 查询的条件满足子查询结果的任意一条记录查询比张三、李四、王五中任意一个人年龄小的学生信息SELECT *FROM studentWHERE sage < ANY (SELECT sage FROM student WHERE sname IN('张三','李四','王五'));3. ALL 关键字 - 查询的条件满足子查询的所有记录查询比张三、李四、王五中所有人的年龄都小的学生信息