基础
-- 查询表结构
SELECT * FROM report WHERE 1<>1;
-- 包含左右
SELECT * FROM report WHERE id BETWEEN 2 AND 4;
-- 不等于
SELECT * FROM report WHERE id!=2;
-- 不等于
SELECT * FROM report WHERE id <>2;
-- 或者,满足任一条件即为真
SELECT * FROM report WHERE id=2 OR STATUS=0;
-- 并且,满足所以条件才为真
SELECT * FROM report WHERE id=2 AND STATUS=0;
-- and or 组合使用
SELECT * FROM report WHERE (id=2 OR id=4) AND STATUS=2;
-- 默认顺序,从小到大
SELECT * FROM report ORDER BY STATUS;
-- desc 降序,asc 升序
SELECT * FROM report ORDER BY id DESC,STATUS ASC;
-- 此写法每列均不能为空,注意时间的添加写法
INSERT INTO report VALUES(0,120,130,'ascasfaa',2,'2018-1-2');
-- 此写法注意一一对应
INSERT INTO report(report,be_report) VALUES(140,150);
-- set后面是要修改的新值
UPDATE report SET report=96,be_report=96,report_content='I am tired',STATUS=0,insert_time='2018-8-21 11:34:03' WHERE id=9;
-- 删除某条数据
DELETE FROM report WHERE id=8;
-- 删除表中所有数据,表的结构,属性和索引都在
DELETE FROM report;
-- 此写法错误
DELETE * FROM report;
-- 选取表中头两条数据
SELECT * FROM report LIMIT 2;
-- oracle用法
SELECT * FROM report WHERE ROWNUM<=5;
-- SQL Server用法
SELECT TOP 20 PERCENT * FROM report;
-- SQL Server用法
SELECT TOP 2 * FROM report;
-- 模糊查询,%通配符
SELECT * FROM report WHERE report_content LIKE '%a%';
-- 不含a的数据
SELECT * FROM report WHERE report_content NOT LIKE '%a%';
-- 第二个字是s,_占一个位
SELECT * FROM report WHERE report_content LIKE '_s%';
-- 查询report_content以q,w开头的数据,mysql不支持
SELECT * FROM report WHERE report_content LIKE '[qw]%';
-- 查询report_content不以q,w开头的数据,mysql不支持
SELECT * FROM report WHERE report_content LIKE '[!qw]%';
-- 查询id为1和3的数据
SELECT * FROM report WHERE id IN (1,3);
-- mysql前后都包含,在id为1和三之间的数据
SELECT * FROM report WHERE id BETWEEN 1 AND 3;
-- mysql前后都包含
SELECT * FROM report WHERE id NOT BETWEEN 1 AND 3;
-- mysql前后都包含
SELECT * FROM report WHERE report_content BETWEEN 'ascasfaa' AND 'ascasfaa';
-- 表别名加不加as均可
SELECT r.`id`,r.`insert_time` FROM report r;
-- 列别名加不加as均可
SELECT id,insert_time AS TIME FROM report;
-- 返回左表所有记录,右表如果没有对应的数据那么对应的表格里什么也没有
SELECT * FROM report r LEFT JOIN USER u WHERE r.`id`=u.r_id;
-- 与left join相似
SELECT * FROM report r RIGHT JOIN USER u WHERE r.`id`=u.r_id;
-- 返回左右表的所有数据,没有相对应的那么对应位置什么也不显示
SELECT * FROM report r FULL JOIN USER u WHERE r.`id`=u.r_id;
-- UNION合并多个select语句的结果集,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名.不允许重复的值
SELECT * FROM report UNION SELECT * FROM report1;
-- 基本等同于union,但允许重复
SELECT * FROM report UNION ALL SELECT * FROM report1;