mysql-----基础

基础

-- 查询表结构

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;