DQL：Data Query Language,数据查询语言
SELECT
.SELECT col1,col2,... FROMtbl_name[WHERE clause]
[ORDER BY 'col_name'[DESC]] [LIMIT [m,]n];
Limit m,n跳过m个，要n个
如以下语句跳过2个，取接下来的三个数值
MariaDB [sunny]> select* from fenshu order by englishlimit2,3;
+----+------+--------+-------+---------+
| id | name | gender |math| english |
+----+------+--------+-------+---------+
| 22 | cd| m|99 |89.00 |
| 10 | tom| NULL| 60.05 |90.00 |
| 16 | lin| NULL| 60.05 |90.00 |
以下语句表示将表fenshu中english这一列分数大于88的id,name,english这三列筛选出来
select id,name,englishfrom fenshu where english > 88;
.字段表示法：
*:所有字段
as：字段别名，col1 ASalias1
亲自as这个关键字可以不写，如name姓名
例子如下
select id as学号,name as姓名,english
as英语from fenshu where english > 88;
也可以给表起别名，一般用于多表查
%是通配符，表示任意字符，下划线通配符，表示支持单个字符
rlike表示支持正则表达式，如果不支持正则表达式，就不能用.*表示所有的值
例子
MariaDB [sunny]> select* from fenshu where name like "ti.*";
Empty set (0.00 sec)
MariaDB [sunny]> select* from fenshu where name rlike "ti.*";
+----+------+--------+------+---------+
| id | name | gender |math | english |
+----+------+--------+------+---------+
| 23 | tim| f|80 |78.00 |
| 26 | tiac | f|80 |78.00 |
+----+------+--------+------+---------+
2 rows in set (0.00 sec)
多表的连接
selectd.id,d.t4,s.math,s.english from datetb d,score s where d.id=s.id;
DQL语句
.WHERE clause:
操作符：
>, <, >=, <=,==, !=
==判断相等，用两个等号来表示
BETWEEN ... AND ...
LIKE:
%：任意长度的任意字符
_：任意单个字符；
RLIKE：正则表达式模式匹配
IS NULL，IS NOT NULL
IN（val1,val2,…）
select * from score t where t.english in(88,90,70) order by t.english desc;
条件逻辑操作：
and，or，not
示例
.DESC students;
.INSERT INTO students
VALUES(1,'tom'，'m'),(2,'alice','f');
.INSERT INTOstudents(id,name) VALUES(3,'jack'),(4,'allen');
.SELECT * FROM studentsWHERE id < 3;
.SELECT * FROM studentsWHERE gender='m';
.SELECT * FROM studentsWHERE gender IS NULL;
.SELECT * FROM studentsWHERE gender IS NOT NULL;
.SELECT * FROM studentsORDER BY name DESC LIMIT 2;
.SELECT * FROM studentsORDER BY name DESC LIMIT 1,2;
.SELECT * FROM studentsWHERE id >=2 and id <=4
.SELECT * FROM studentsWHERE BETWEEN 2 AND 4
.SELECT * FROM studentsWHERE name LIKE ‘t%’
.SELECT * FROM studentsWHERE name RLIKE '.*[lo].*';
.SELECT id stuid,name as stuname FROM students
网友评论