SQL 总结

mysql常用的数据类型

日期 时间

DATE

DATETIME

TIMESTAMP

-- 查询系统当前时间

SELECT NOW() ,CURRENT_TIMESTAMP(),CURRENT_DATE();

普通文本,大文本类型

CHAR 表示一个汉字或者一个字母

VARCHAR(n) n<=4000 ,一个汉字占2个字节 ,varchar最多存储2000个汉字

BLOB 图片 视频 音频 (2G)

数值类型

INT 长度11位

DOUBLE 双精度浮点数

布尔类型

BOOLEAN : TRUE FALSE

 

SQL -- 结构化查询语言

dml -- 数据操作语言

SELECT UPDATE DELETE INSERT

ddl -- 数据定义语言

CREATE DROP ALTER

dcl -- 数据控制语言

GRANT REVOKE

INSERT

INSERT INTO 表名 (字段) VALUES (字段对应的数据);

1.全表插入不用写字段了 ,按顺序插入即可

2.如果设置主键是自增的策略 , INSERT 时不用管主键这列

3.自增策略是oracle(序列)与mysql和sqlserver数据库的一个差异

4.新增时,还需要注意各种约束条件(检查约束,唯一约束,非空约束,主外键关联约束)

5.insert默认值的时候,使用default关键字

6.insert时,想插入空值,可以是使用 NULL 或'' ,null表示空对象, ''表示非空对象,但是内容是空

7.insert 表() VALUES() ,(),()....;

UPDATE

UPDATE 表名 SET 列名=值1,列名2=值... WHERE 条件=*** AND ****;

1.update 的where 很关键 ,如果不写where 条件,相当于全行修改 ,很容易出错

2. WHERE 条件可以包含多个 用 AND 或者 or连接

3. set后可以跟多个要修改的列 ,中间用逗号分隔

DELETE

DELETE FROM 表名 WHERE id=**** ;

1.delete 不能忘记加where条件

2. mysql数据库是自动提交事务的方式 , 与oracle数据库有区别

手动设置mysql数据库的事务提交为false

SET autocommit=0; -- 先手动设置mysql数据库的事务提交为false

-- 在执行sql语句 , 最后 可以手动提交或者rollback

ROLLBACK;

COMMIT; -- 当提交或回滚执行结束了 , mysql又变回到自动提交方式

SELECT

SELECT 投影 FROM 表

WHERE 筛选条件

GROUP BY 分组

ORDER BY 排序

 

select中别名和连接

;SELECT CONCAT(s.`id`,'.',s.`name`) AS 主键 FROM stu s

 

查询为空的列需要增加 IS NULL OR =''

SELECT * FROM stu WHERE birthday IS NULL OR birthday=''

 

查询分页 --oracle数据的差异之一

SELECT * FROM stu ORDER BY id LIMIT 0,10; -- 从索引0开始向后查询10条记录

SELECT * FROM stu ORDER BY id LIMIT 10,10; -- 从索引10开始向后查询10条记录

 

排序 ORDER BY

1.排序的列,不能包含中文的内容

2.排序默认是升序 , 降序用 DESC

SELECT * FROM stu ORDER BY id DESC;

3.可以进行多列排序 ,如果某一列内容相同了 ,再看第二列的顺序

SELECT * FROM stu ORDER BY score DESC ,id ASC;

 

模糊查询

LIKE

_ -- 模糊匹配一个字符或者一个汉字

% -- 模糊匹配任意多个字符或者0个字符

SELECT * FROM stu WHERE NAME LIKE '%张%'

BETWEEN AND

1. 范围查找 , 必须从小到大查找

2. 可以查找 日期 或数值类型 , 字符类型不可以

IN() -- 括号中表示一个集合

SELECT * FROM stu WHERE id IN (11,12,13);

 

mysql 常用函数 

SELECT NOW() ;

1.数学函数
2.日期函数
3.字符串函数
4.流程控制函数
5.格式化函数

6.聚合函数 (*)

1.数学函数  

ABS()  -- 绝对值 
SELECT ABS( -100) ;
CEILING() -- 返回大于x的最小整数
FLOOR() -- 返回小于x的最大整数

SELECT FLOOR(5.5) ,CEILING(5.5);

ROUND() -- 四舍五入,可以自定义保留位数
SELECT ROUND(55.54,1);

RAND() -- [0,1) 随机数

SELECT RAND();

MOD() -- 余数

SELECT MOD(5,3);

2.日期函数  

-- 分别对应  日期  ,时间  
SELECT NOW(), CURRENT_DATE(),CURRENT_TIME();
-- 分别取年月日 时分秒
SELECT YEAR(NOW()) AS 年 , MONTH(NOW()) AS yue , DAY(NOW()) AS ri ,
HOUR(NOW()) AS shi , MINUTE(NOW()) AS fen , SECOND(NOW()) AS miao;

DATE_ADD() -- 在某个日期上增加 , 允许参数为负数,就是反向操作 ,参数(整数)
DATE_SUB() -- 在某个日期上减少

type类型包括 DAY ,WEEK ,MONTH ,QUARTER ,YEAR

SELECT DATE_ADD(NOW(),INTERVAL 1 DAY); -- 查询是明天

SELECT DATE_ADD(NOW(),INTERVAL -1 DAY); -- 查询是昨天

SELECT DATE_ADD(NOW(),INTERVAL 2 YEAR);  -- 查询明年的今天

DATE_SUB()    -- 这个方法与上面用法一致,只不过是递减操作

DATEDIFF(date1,date2)  -- 求日期的差值
TIMEDIFF(time1,time2)  -- 求时间的差值

SELECT DATEDIFF(NOW(),'2018-9-1'); -- date1-date2的值 
SELECT  TIMEDIFF(NOW(),'2018-9-1 00:00:00'); --time1-time2  结果:109:22:02,后期再使用java语法操作即可

3.字符串函数

CONCAT(str1,str2,...) -- 将所有内容练成一个字符串

SELECT CONCAT('隔壁','泰山','人猿');

CONCAT_WS(间隔符,str1,str2,...)
SELECT CONCAT_WS('-','隔壁','泰山','人猿');

SELECT LENGTH('我'); -- utf8 一个汉字占3个字节  , 

SELECT LENGTH('abcde');
SELECT REVERSE('abcde'); -- 反转字符串 

SELECT TRIM('     abcdr     '); -- 去掉左右两侧的空格

SUBSTRING(str,0)
SUBSTRING(str,0,3) -- 截取字符串  ,索引从0开始 ,截取3个字节

SELECT SUBSTRING('abcdefg',0);

4. 流程控制函数 

SELECT
CASE
WHEN test1 THEN 结果1
WHEN test2 THEN 结果2
ELSE 结果3
END;

SELECT * ,
CASE
WHEN sex='f' THEN '男'
ELSE '女'
END AS 性别
FROM student;

IF(条件 , 结果1, 结果2)  -- 条件为真 ,返回结果1 ,否则返回结果2

;SELECT s.`StudentName`,s.`Sex` ,IF(s.`Sex`='f','男','女') AS 性别 FROM student s;

IFNULL(arg1,arg2)  -- 判断arg1不为空 ,返回arg1本身 ,否则返回arg2

;SELECT s.`StudentName` ,IFNULL(s.`BornDate`,'信息尚未录入') FROM student s;

5.格式化函数  

DATE_FORMAT()  -- 日期格式化

FORMAT() -- 数值格式化  

SELECT NOW() , DATE_FORMAT(NOW(),'%Y-%m-%d %h:%i:%s %p');

SELECT FORMAT(99999.546 ,2);

 

练习题  

查询result表  

查询 当天考试的数据信息

SELECT * FROM result WHERE DATEDIFF(examdate,NOW())=0;
或者
SELECT * FROM result WHERE DATE(examdate)=DATE(NOW());

查询 昨天考试的数据信息
SELECT * FROM result WHERE DATEDIFF(NOW(),examdate)=1;

查询 过去7天考试的数据信息
SELECT * FROM result WHERE DATE(examdate)>DATE_SUB(NOW(),INTERVAL 1 WEEK);

查询 过去30天考试的数据信息
SELECT * FROM result WHERE DATE(examdate)>DATE_SUB(NOW(),INTERVAL 1 MONTH);

查询 上个月考试的数据信息(8/1~8/31)
-- 错误的 , 会包含既往所有年份上个月的信息 
-- SELECT * FROM result WHERE month(examdate) = month(DATE_SUB(NOW(),INTERVAL 1 MONTH));
SELECT * FROM result 
WHERE DATE_FORMAT(examdate,'%y%m') = DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 MONTH),'%y%m');

查询 当月考试的数据信息(9/1~今天)
SELECT * FROM result WHERE DATE_FORMAT(examdate,'%y%m') = DATE_FORMAT(NOW(),'%y%m');

查询去年考试的数据信息(2017/1/1~2017/12/31)
SELECT * FROM result WHERE YEAR(examdate) = YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));

查询当年考试的数据信息(2018/1/1~今天)
SELECT * FROM result WHERE YEAR(examdate) = YEAR(NOW());

6. 聚合函数 

-- sum()  sum求的是列内容的总和  
-- count()  count求的是总共有多少条记录
-- avg()
-- min()
-- max()

COUNT(*)  对全部查询出来的行,求行数

COUNT(列) 当对某一列求行数 ,会去掉null内容的列  

SELECT COUNT(*) ,COUNT(StudentResult)  FROM result;

GROUP BY  分组 通常跟聚合函数配合使用 

1.分组后  , 投影区域只能 加入分组的列 和聚合函数(聚合某一组的总值,多几组就有几个值)
2.分组后 ,如果想再加筛选条件 ,不能使用where ,而要使用having对分组后的数据再次筛选
3.允许多列分组 (以班级先分组 ,再以性别在分组)
4.分组如果还有排序的情况 , 要先分组 ,再排序

-- 练习1

-- 查询学生总人数
SELECT COUNT(*) FROM student ;

-- 查询1年级的总学时
SELECT SUM(su.classhour) FROM SUBJECT su WHERE su.gradeid='1';

-- 学号为'001'的学生第一学年考试总成绩
;SELECT SUM(r.`StudentResult`) 
FROM result r ,SUBJECT su
WHERE r.`SubjectId`=su.`SubjectId`
AND su.`gradeid`=1
AND r.`StudentNo`='001';

-- 学号为'001'的学生第一学年所有考试的平均分
SELECT AVG(r.`StudentResult`) 
FROM result r ,SUBJECT su
WHERE r.`SubjectId`=su.`SubjectId`
AND su.`gradeid`=1
AND r.`StudentNo`='001';

-- 查询2017年3月29日科目“C语言”的最高分、最低分、平均分
SELECT MAX(r.`StudentResult`) AS 最高分, MIN(r.`StudentResult`) AS 最低分,
ROUND(AVG(r.`StudentResult`),2) AS 平均分 
FROM result r, SUBJECT su 
WHERE r.`SubjectId`=su.`SubjectId`
AND su.`SubjectName`='c语言'
AND r.`ExamDate`='2017-3-29';

-- 查询2017年3月29日科目“C语言”及格学生的平均分
SELECT 
AVG(r.`StudentResult`) AS 平均分
FROM result r, SUBJECT su 
WHERE r.`SubjectId`=su.`SubjectId`
AND su.`SubjectName`='c语言'
AND r.`ExamDate`='2017-3-29'
AND r.`StudentResult`>=60;

-- 查询所有参加“C语言”科目考试的平均分
SELECT 
AVG(r.`StudentResult`) AS 平均分 FROM result r, SUBJECT su 
WHERE r.`SubjectId`=su.`SubjectId`
AND su.`SubjectName`='c语言';

-- 练习2: 参考供应商品等表  s  p   sp

-- 查询供货商总数。
SELECT COUNT(DISTINCT sno)
FROM s

-- 查询一次供应茶叶的最多斤数。
SELECT MAX(qty)
FROM sp

-- 查询各个供应商编号及供应茶叶的总斤数。
SELECT sno , SUM(qty)
FROM sp
GROUP BY sno

-- 查询提供了2种以上茶叶的供货商编号。
SELECT sno , COUNT(pno) AS num
FROM sp
GROUP BY sno
HAVING num>1;

SELECT sno
FROM sp
GROUP BY sno
HAVING COUNT(pno)>1;