作业

2020年4月28日

SELECT * FROM users WHERE id=1
SELECT * FROM users WHERE id=1 OR password='12345'；
SELECT * FROM users ORDER BY id ASC;
SELECT * FROM users ORDER BY id DESC;
SELECT * FROM users WHERE id>5 ORDER BY password ASC；
SELECT * FROM users ORDER BY id DESC LIMIT 1;
SELECT * FROM users LIMIT 0,2;
SELECT * FROM users LIMIT 2,2;
SELECT * FROM users LIMIT 4,2;
SELECT * FROM users WHERE name LIKE '%狠%'；
SELECT * FROM users WHERE name LIKE '__';
SELECT * FROM users WHERE name LIKE '___';
SELECT * FROM users WHERE name REGEXP '[1|3|7]$';
SELECT * FROM users WHERE name NOT REGEXP '[1|3|7]$';
SELECT * FROM birds WHERE name LIKE '%PIGEONS%';
SELECT * FROM birds WHERE name LIKE '%PIGEONS%' ORDER BY id DESC;
SELECT * FROM birds LIMIT 0,10;
SELECT * FROM birds WHERE name REGEXP '[PIGEON|Dove]$';
SELECT * FROM users WHERE id=1
SELECT * FROM users WHERE id=1 OR password='12345'；
SELECT * FROM users ORDER BY id ASC;
SELECT * FROM users ORDER BY id DESC;
SELECT * FROM users WHERE id>5 ORDER BY password ASC；
SELECT * FROM users ORDER BY id DESC LIMIT 1;
SELECT * FROM users LIMIT 0,2;
SELECT * FROM users LIMIT 2,2;
SELECT * FROM users LIMIT 4,2;
SELECT * FROM users WHERE name LIKE '%狠%'；
SELECT * FROM users WHERE name LIKE '__';
SELECT * FROM users WHERE name LIKE '___';
SELECT * FROM users WHERE name REGEXP '[1|3|7]$';
SELECT * FROM users WHERE name NOT REGEXP '[1|3|7]$';
SELECT * FROM birds WHERE name LIKE '%PIGEONS%';
SELECT * FROM birds WHERE name LIKE '%PIGEONS%' ORDER BY id DESC;
SELECT * FROM birds LIMIT 0,10;
SELECT * FROM birds WHERE name REGEXP '[PIGEON|Dove]$';
SELECT * FROM users WHERE id=1
SELECT * FROM users WHERE id=1 OR password='12345'；
SELECT * FROM users ORDER BY id ASC;
SELECT * FROM users ORDER BY id DESC;
SELECT * FROM users WHERE id>5 ORDER BY password ASC；
SELECT * FROM users ORDER BY id DESC LIMIT 1;
SELECT * FROM users LIMIT 0,2;
SELECT * FROM users LIMIT 2,2;
SELECT * FROM users LIMIT 4,2;
SELECT * FROM users WHERE name LIKE '%狠%'；
SELECT * FROM users WHERE name LIKE '__';
SELECT * FROM users WHERE name LIKE '___';
SELECT * FROM users WHERE name REGEXP '[1|3|7]$';
SELECT * FROM users WHERE name NOT REGEXP '[1|3|7]$';
SELECT * FROM birds WHERE name LIKE '%PIGEONS%';
SELECT * FROM birds WHERE name LIKE '%PIGEONS%' ORDER BY id DESC;
SELECT * FROM birds LIMIT 0,10;
SELECT * FROM birds WHERE name REGEXP '[PIGEON|Dove]$';

笔记：
查询语句语法：
：所有字段
查询处users表的所有字段：
SELECT 字段 FROM 表名；
SELECT FROM users；

查询出users表的指定字段：
获取一个字段的数据：SELECT id FROM users；
获取多个字段的数据：SELECT name,id,phone FROM users；

WHERE 字句
给查询添加筛选条件
=：表示的是运算符
还可以使用AND和OR来设置多个筛选条件
语法：SELECT 字段 FROM 表名 WHERE 字段=值；
例如：查询处id为1或密码为12345的用户信息
SELECT FROM users WHERE id=1 OR password=’12345’；
ORDER BY 字句
添加排序功能；要写在WHERE后面且WHERE字句需要存在；
ORDER BY 字段排序方式：
DESC:降序
ASC(默认)：升序
例如：数据通过id进行降序排序：
SELECT FROM users ORDER BY id DESC;
与WHERE搭配一起用
SELECT * FROM users WHERE id>5 ORDER BY password ASC；

LIMIT 字句
限定结果集合:限制获取数据的数量
查询出最后添加到表中的数据
SELECT FROM users ORDER BY id DESC LIMIT 1;
分页 规定每页显示两条数据
第一页：SELECT FROM users LIMIT 0,2;
第二页：SELECT FROM users LIMIT 2,2;
第三页：SELECT FROM users LIMIT 4,2;
LIKE 运算符
模糊查找 属于WHERE下面的运算符
%表示匹配任意内容，不限值长度
_表示匹配任意内容，长度限制一个字符
搜索用户名中带有“狠”字的用户
SELECT * FROM users WHERE name LIKE ‘%狠%’；
搜索用户名为两个字的时候匹配

SELECT * FROM users WHERE name LIKE ‘ ‘;

REGEXP运算符
WHERE 字段 REGEXP [BINARY] 值
NOT REGEXP [BINARY]匹配上的内容丢弃，没匹配上作为结果
默认查找时不分大小斜；让它区分大小写 加上BINARY
查找用户名结尾为137的用户
SELECT FROM users WHERE name REGEXP ‘[1|3|7]$’;
SELECT FROM users WHERE name NOT REGEXP ‘[1|3|7]$’;

对查询结果进行计数
COUNT()计数函数
SELECT count(id) FROM users;
SELECT count(*) FROM users;
SELECT count(id) FROM users WHERE id>5;

对查询数据进行分组
GROUP BY 字段
SELECT * FROM users GROUP BY password；