发布网友 发布时间:2022-04-08 09:12
共4个回答
懂视网 时间:2022-04-08 13:33
1、mysql查询的执行路径
2、查询的分类
单表查询:简单查询
多表查询:连续查询
联合查询:
3、查询类型
投影:挑选要显示的字段。
语句说明:SELECT 字段1, 字段2, ... FROM tb_name; //挑选指定字段
SELECT * FROM tb_name; //挑选所有字段
选择:挑选符合条件的行。
语句说明:SELECT 字段1, ... FROM tb_name WHERE 子句; //子句也就是布尔表达式
4、布尔表达式
比较操作符,用以做数值比较:
= :等值比较
<=> :控制安全比较,用以与NULL做比较,
<> :不等于
<
<=
>
>=
判断空值比较:
IS NULL :是空值
IS NOT NULL :不是空值
模糊匹配,用来做字符比较:
LIKE: 支持的通配符: %(任意长度的任意字符), _(任意单个字符)
RLIKE, REGEXP: 支持使用正则表达式 。
组合条件表达式:
NOT, !
AND, &&
OR, ||
其他一些表达式:
IN: 判断指定字段的值是否在给定在列表中;
BETWEEN ... AND ...:位于指定的范围之间 ;
ORDER BY .... ASC | DESC :ASC为升序并为默认排序方式,DESC为降序排序;
聚合函数:
SUM(), AVG(), MAX(), MIN(), COUNT()
SELECT语句的执行流程:
FROM clause --> WHERE clause --> GROUP BY --> HAVING clause --> ORDER BY ... --> SELECT --> LIMIT
SELECT常用修饰符:
DISTINCT:指定的结果相同的只显示一次;
SQL_CACHE:缓存于查询缓存中;
SQL_NO_CACHE:不缓存查询结果;
二、举例
1、创建students表
MariaDB [test]> CREATE TABLE students (SID INT UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE KEY,Name CHAR (30) NOT NULL,Age TINYINT UNSIGNED NOT NULL,Gender ENUM(‘F‘,‘M‘)NOT NULL,Tutor CHAR(20),ClassID TINYINT UNSIGNED); Query OK, 0 rows affected (0.03 sec) MariaDB [test]> DESC students; +---------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------+------+-----+---------+----------------+ | SID | int(10) unsigned | NO | PRI | NULL | auto_increment | | Name | char(30) | NO | | NULL | | | Age | tinyint(3) unsigned | NO | | NULL | | | Gender | enum(‘F‘,‘M‘) | NO | | NULL | | | Tutor | char(20) | YES | | NULL | | | ClassID | tinyint(3) unsigned | YES | | NULL | | +---------+---------------------+------+-----+---------+----------------+ 6 rows in set (0.00 sec)
2、插入数据
MariaDB [test]> SELECT * FROM students; +-----+---------------+-----+--------+-------------+---------+ | SID | Name | Age | Gender | Tutor | ClassID | +-----+---------------+-----+--------+-------------+---------+ | 1 | Feng QinYang | 25 | M | Lu Zhishen | 2 | | 2 | Yang Guo | 23 | M | Xiao Longnv | 1 | | 3 | Pan Jinlian | 28 | F | Xi Menqing | 2 | | 4 | Duan Yu | 20 | M | Mu Wanqing | 3 | | 5 | Xu Zhu | 26 | M | Yuan Ji | 3 | | 6 | Xiao Feng | 35 | M | Jue Yuan | 3 | | 7 | Mie Jueshitai | 56 | F | Zhou Zhiruo | 1 | | 8 | Guo Jing | 30 | M | Song Jiang | 1 | | 9 | Zhao Ming | 24 | F | Zhang Wuji | 2 | +-----+---------------+-----+--------+-------------+---------+ 9 rows in set (0.00 sec)
3、查询举例
查询年龄在24之30之间的人:
MariaDB [test]> SELECT Name,Age FROM students WHERE Age BETWEEN 24 AND 30; +--------------+-----+ | Name | Age | +--------------+-----+ | Feng QinYang | 25 | | Pan Jinlian | 28 | | Xu Zhu | 26 | | Guo Jing | 30 | | Zhao Ming | 24 | +--------------+-----+ 5 rows in set (0.05 sec)
查询年龄是24,25,26的人:
MariaDB [test]> SELECT Name,Age FROM students WHERE Age IN (24,25,26); +--------------+-----+ | Name | Age | +--------------+-----+ | Feng QinYang | 25 | | Xu Zhu | 26 | | Zhao Ming | 24 | +--------------+-----+ 3 rows in set (0.00 sec)
查询Name以Z开头的人:
MariaDB [test]> SELECT Name FROM students WHERE Name LIKE ‘Z%‘; +-----------+ | Name | +-----------+ | Zhao Ming | +-----------+ 1 row in set (0.00 sec)
也可以使用正则:
MariaDB [test]> SELECT Name FROM students WHERE Name RLIKE ‘^Z.*‘; +-----------+ | Name | +-----------+ | Zhao Ming | +-----------+ 1 row in set (0.06 sec)
查询Tutor为NULL值的人
MariaDB [test]> INSERT INTO students VALUES (10,‘Xiao Ming‘,10,‘M‘,NULL,4); Query OK, 1 row affected (0.00 sec) MariaDB [test]> SELECT Name,Tutor FROM students WHERE Tutor IS NULL; +-----------+-------+ | Name | Tutor | +-----------+-------+ | Xiao Ming | NULL | +-----------+-------+ 1 row in set (0.02 sec)
查询年龄小于30且Gender为F的人:
MariaDB [test]> SELECT Name,Age,Gender FROM students WHERE Age < 30 AND Gender =‘F‘; +-------------+-----+--------+ | Name | Age | Gender | +-------------+-----+--------+ | Pan Jinlian | 28 | F | | Zhao Ming | 24 | F | +-------------+-----+--------+ 2 rows in set (0.00 sec)
上例的输出结果以降序输出,若不指定即为升序输出:
MariaDB [test]> SELECT Name,Age,Gender FROM students WHERE Age < 30 AND Gender =‘F‘ ORDER BY Name DESC; +-------------+-----+--------+ | Name | Age | Gender | +-------------+-----+--------+ | Zhao Ming | 24 | F | | Pan Jinlian | 28 | F | +-------------+-----+--------+ 2 rows in set (0.00 sec)
更加性别分组并进行求和:
MariaDB [test]> SELECT Gender,SUM(Age) FROM students GROUP BY Gender; +--------+----------+ | Gender | SUM(Age) | +--------+----------+ | F | 108 | | M | 169 | +--------+----------+ 2 rows in set (0.05 sec)
根据班级分组并求出每班级的人数及年龄之和:
MariaDB [test]> SELECT ClassID,Count(Name),Sum(Age) FROM students GROUP BY ClassID; +---------+-------------+----------+ | ClassID | Count(Name) | Sum(Age) | +---------+-------------+----------+ | 1 | 3 | 109 | | 2 | 3 | 77 | | 3 | 3 | 81 | | 4 | 1 | 10 | +---------+-------------+----------+ 4 rows in set (0.00 sec)
查询班级人数大于等于2人的班级:
MariaDB [test]> SELECT ClassID FROM students GROUP BY ClassID HAVING Count(Name) >= 2; +---------+ | ClassID | +---------+ | 1 | | 2 | | 3 | +---------+ 3 rows in set (0.00 sec)
查询students表仅显示前两个:
MariaDB [test]> SELECT * FROM students LIMIT 2; +-----+--------------+-----+--------+-------------+---------+ | SID | Name | Age | Gender | Tutor | ClassID | +-----+--------------+-----+--------+-------------+---------+ | 1 | Feng QinYang | 25 | M | Lu Zhishen | 2 | | 2 | Yang Guo | 23 | M | Xiao Longnv | 1 | +-----+--------------+-----+--------+-------------+---------+ 2 rows in set (0.00 sec)
查询students表跳过前两行后在显示三行:
MariaDB [test]> SELECT * FROM students LIMIT 2,3; +-----+-------------+-----+--------+------------+---------+ | SID | Name | Age | Gender | Tutor | ClassID | +-----+-------------+-----+--------+------------+---------+ | 3 | Pan Jinlian | 28 | F | Xi Menqing | 2 | | 4 | Duan Yu | 20 | M | Mu Wanqing | 3 | | 5 | Xu Zhu | 26 | M | Yuan Ji | 3 | +-----+-------------+-----+--------+------------+---------+ 3 rows in set (0.02 sec)
本文出自 “粗茶淡饭” 博客,请务必保留此出处http://cuchadanfan.blog.51cto.com/9940284/1690615
MySQL(六)
标签:mysql、数据库
热心网友 时间:2022-04-08 10:41
order by是设置结果排序,这里的order by 6表示按照select后面的第6个(从1开始数)字段升序排列。热心网友 时间:2022-04-08 11:59
一、order by的原理 1、利用索引的有序性获取有序数据 当查询语句的 order BY 条件和查询的执行计划中所利用的 Index 的索引键(或前面几个索引键)完全一致,且索引访问方式为 range,ref 或者 index 的时候,MySQL 可以利用索引顺序而直接取得 …热心网友 时间:2022-04-08 13:34
这是sql语句,意思是对某一列进行排序