Mysql中的DQL语句

1.基本语法

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number]; 

* ORDER BY column_name [ASC | DESC] 是一个可选的子句，用于指定结果集的排序顺序，默认是升序（ASC）。
* LIMIT number 是一个可选的子句，用于限制返回的行数。

2. 其他功能

* 函数concat()
select concat('姓名:',StudentName) as 新名字 from student
--将查出的结果与字符串进行拼接

* select后跟表达式
* 发现重复数据，进行去重
select distinct 'StudentNo' from result
* 查询系统版本
select version()
* 用来计算
select 100*3+1 as 计算结果
* 查询自增的步长
select @@auto_increment_increment
* 查询学生成绩后进行+1操作
select 'StudentNo','StudentResult'+1 as '提分后' from result

3. 操作符列表，可以用在where子句中

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

4. 模糊查询

比较运算符：

运算符	语法	描述
is null	a is null	操作符为null,结果为真
is not null	a is not null	操作符不为null,结果为真
between	a between b and c	a在b和c之间,结果为真
like	a like b	a匹配b,结果为真
in	a in (a1,a2...)	a在a1,a2..里,结果为真

SELECT * FROM [user] WHERE u_name LIKE '%三%';
--%表示任意 0 个或多个字符
SELECT * FROM [user] WHERE u_name LIKE '_三_';
--表示任意单个字符。
SELECT * FROM [user] WHERE u_name in ('张三'，'李四');
--匹配在（）中的内容
SELECT * FROM [user] WHERE u_name is not null;
--查询名字不为空的user

5. 连表查询

连接方法	描述
INNER JOIN	如果表中有至少一个匹配，则返回行
LEFT JOIN	即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN	即使左表中没有匹配，也从右表返回所有的行
FULL JOIN	只要其中一个表中存在匹配，则返回行(oracle里面有full join,但在mysql中没有full join)

内连接：INNER JOIN （JOIN）关键字在表中存在至少一个匹配时返回行。

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
--* columns：要显示的列名。
--* table1：表1的名称。
--* table2：表2的名称。
--* column_name：表中用于连接的列名。

实例验证：
"Websites" 表的数据：

"access_log" 网站访问记录表的数据：

--inner join：
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count;

--left join：
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

--right join：
SELECT websites.name, access_log.count, access_log.date
FROM websites
RIGHT JOIN access_log
ON access_log.site_id=websites.id
ORDER BY access_log.count DESC;

--MySQL中不支持 FULL OUTER JOIN，你可以在 SQL Server 测试以下实例。
--full join：
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
FULL OUTER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;
--FULL OUTER JOIN 关键字返回左表（Websites）和右表（access_log）中所有的行。如果 "Websites" 表中的行在 "access_log" 中没有匹配或者 "access_log" 表中的行在 "Websites" 表中没有匹配，也会列出这些行。

自连接：用户登录表
用户登录表login(id，uid，update_time)

计算回访用户数（在一前一后的不同日期登录过）

--自连接：
select count(DISTINCT a.uid) as 回访人数
from login a left join login b
on a.uid = b.uid
where a.update_time < b.update_time;

6.order by

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

• column1, column2, ...：要排序的字段名称，可以为多个字段。
• ASC：表示按升序排序。
• DESC：表示按降序排序。

7. limit

select _column,_column 
from _table 
[where Clause] 
[limit N][offset M]

• select * : 返回所有记录
• limit N : 返回 N 条记录
• offset M : 跳过 M 条记录, 默认 M=0, 单独使用似乎不起作用
• limit N,M : 相当于 limit M offset N , 从第 N 条记录开始, 返回 M 条记录

8. 子查询
   查询不存在年龄为20岁的员工的部门。
   select dname from department where did not in(select did from employee where age=20);

9. mysql函数
   常用函数：

函数	返回值
SELECT ABS(-1);	1
SELECT CEILING(1.5);	2
SELECT FLOOR(1.5);	1
SELECT EXP(3);	计算 e 的三次方,返回20.085536923188
SELECT GREATEST(3, 12, 34, 8, 25);	返回以下数字列表中的最大值34
SELECT LEAST(3, 12, 34, 8, 25);	返回以下数字列表中的最小值3
SELECT LN(2);	返回 2 的自然对数：0.6931471805599453
SELECT RAND();	返回 0 到 1 的随机数，若()里面有数字,RAND(x),x相同时，返回值相同
SELECT ROUND(1.23456);	返回离 x 最近的整数：1
SELECT LENGTH('1234');	返回字符串 s 的字符数：4
SELECT CONCAT('hel','llo');	多个字符串合并为一个字符串：返回hello
SELECT TRIM(' RUNOOB ');	去掉字符串 s 开始和结尾处的空格：返回RUNOOB
SELECT REPEAT('runoob',3);	将字符串 s 重复 n 次：返回runoobrunoobrunoob
SELECT REVERSE('abc');	将字符串s的顺序反过来：返回cba
SELECT CURRENT_TIME();	返回11:40:45
SELECT CURRENT_TIMESTAMP();	返回当前日期和时间

聚合函数：

函数	返回值
SELECT SUM(age) AS totalAage FROM Student;	返回age的总和
SELECT MAX(age) AS maxAge FROM Student;	age最大值
SELECT MIN(age) AS minAge FROM Student;	age最小值
SELECT AVG(age) FROM student;	age的平均值
SELECT COUNT(*);	扫描整个表的所有行，不考虑任何列的值，包括含有NULL值的行
SELECT COUNT(column);	遍历该列的每一行，检查是否为NULL，然后累计计数。
SELECT COUNT(1);	忽略所有列中的NULL值，只计算非空行的数目

10. 分组过滤

SELECT id,sex,COUNT(*) AS total_peo 
FROM employee 
GROUP BY sex 
HAVING COUNT(*)>3;

11. 数据库级别的MD5加密

-- =====================测试MD5加密=======================
-- MD5信息摘要算法
CREATE TABLE `testMD5 01`(
 `id` INT(4) NOT NULL,
 `name` VARCHAR(10) NOT NULL, 
 `pwd` VARCHAR(50) NOT NULL,
 PRIMARY KEY(`id`)
)ENGINE=INNODB  DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO `testmd5 01` VALUES (1,'张san','123456'),(2,'张六','123456'),(3,'李四','123456'),(4,'王五','123456')

-- 加密
UPDATE `testmd5 01` SET pwd =MD5(pwd)  WHERE id=2  -- 14e1b600b1fd579f47433b88e8d85291

-- 在插入的时候加密
INSERT INTO `testmd5 01` VALUES (6,'huawei',MD5(123456))
INSERT INTO `testmd5 01` VALUES (7,'oppo',MD5(123456789))

-- 如何校验  将用户传递进来的密码，进行MD5加密，然后比对加密后的值90(传递进来的值一样，加密后的值也一样)

--                           接收用户传来的用户名和密码
SELECT * FROM `testmd5 01` WHERE `name`='oppo' AND pwd=MD5('123456789')-- 25f9e794323b453885f5181f1b624d0b

12. select 的完整语法

SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[LIMIT…]

执行步骤：

* 先从from字句一个表或多个表创建工作表
* 将where条件应用于1）的工作表，保留满足条件的行
* GroupBy 将2）的结果分成多个组
* Having 将条件应用于3）组合的条件过滤，只保留符合要求的组。
* Order By对结果进行排序。
* Limit对结果进行分页