数据库简单查询
简单查询
语法句式如下:
SELECT filed1,filed2 ... filedn
FROM tablename
[WHERE CONDITION11]
[GROUP BY filedm [HAVING CONDITION2]]
[ORDER BY filedn [ASC|DESC]]
filed1 - filedn 参数表示需要查询的字段名
tablename 参数表示表的名称
CONDITION1 参数表示查询条件
filedm 参数表示按该字段中的数据进行分组
CONDITION2 参数表示满足该表达式的数据才能输出
filen 参数表示按该字段中的数据进行排序 ASC 表示 升序 DESC 表示降序
查询所有字段数据
- 列出表的所有字段
SELECT filed1,filed2,...filedn FROM tablenamee;
mysql> SELECT name,sex,age FROM t_stu;
+------+-------+------+
| name | sex | age |
+------+-------+------+
| jack | woman | 18 |
| jim | woman | 15 |
| tom | man | 15 |
+------+-------+------+
3 rows in set (0.00 sec)
- ”*“ 的使用
SELECT filed1,filed2,...filedn FROM tablenamee;
查询指定字段数据
例如:从学生表中姓名、性别和年龄字段
SELECT name,sex,age FROM t_student;
mysql> SELECT *FROM t_stu;
+------+-------+------+
| name | sex | age |
+------+-------+------+
| jack | woman | 18 |
| jim | woman | 15 |
| tom | man | 15 |
+------+-------+------+
3 rows in set (0.00 sec)
DISTINCT 查询
实现查询不重复数据
SELECT DISTINCT filed1,filed2...filedn FROM tablename;
mysql> SELECT DISTINCT sex FROM t_stu;
+-------+
| sex |
+-------+
| woman |
| man |
+-------+
2 rows in set (0.00 sec)
mysql> SELECT DISTINCT age FROM t_stu;
+------+
| age |
+------+
| 18 |
| 15 |
+------+
2 rows in set (0.00 sec)
IN 查询
判断字段的数值是否在指定集合的条件查询
1. 在集合中的记录查询
SELECT filed1,filed2,...,filedn FROM tablename WHERE filedm IN(value1,value2,...,valuen);
mysql> SELECT name,age FROM t_stu WHERE age IN(15 ,17,16);
+------+------+
| name | age |
+------+------+
| jim | 15 |
| tom | 15 |
+------+------+
2 rows in set (0.00 sec)
2. 不在集合中的记录查询
SELECT filed1,filed2,...,filedn FROM tablename WHERE filedm NOT IN(value1,value2,...,valuen);
mysql> SELECT name,age FROM t_stu WHERE age NOT IN(15 ,17,16);
+------+------+
| name | age |
+------+------+
| jack | 18 |
+------+------+
1 row in set (0.00 sec)
3. 集合查询的注意点
在具体使用关键字IN时
查询的集合中如果存在NULL,则不会影响查询
使用关键字NOT IN时,则不会有任何查询结果
mysql> SELECT name,age FROM t_stu WHERE age IN(15 ,17,16,NULL);
+------+------+
| name | age |
+------+------+
| jim | 15 |
| tom | 15 |
+------+------+
2 rows in set (0.00 sec)
mysql> SELECT name,age FROM t_stu WHERE age NOT IN(15 ,17,16,NULL);
Empty set (0.00 sec)
BETWEEN AND 查询
实现判断字段的数值是否在指定范围内的条件查询
SELECT filed1,filed2,...,filedn
FROM tablename WHERE filedm BETWEEN minvalue AND maxvalue;
mysql> SELECT *FROM t_stu WHERE age BETWEEN 15 AND 30;
+--------+-------+------+
| name | sex | age |
+--------+-------+------+
| jack | woman | 18 |
| jim | woman | 15 |
| tom | man | 15 |
| 小花 | woman | 25 |
+--------+-------+------+
4 rows in set (0.00 sec)
mysql> SELECT *FROM t_stu WHERE age BETWEEN 16 AND 30;
+--------+-------+------+
| name | sex | age |
+--------+-------+------+
| jack | woman | 18 |
| 小花 | woman | 25 |
+--------+-------+------+
2 rows in set (0.00 sec)
一点小问题:关于字符编码
mysql> insert into t_stu values("小花",'woman',25);
ERROR 1366 (HY000): Incorrect string value: '\xE5\xB0\x8F\xE8\x8A\xB1' for column 'name' at row 1
插入数据失败 查看建表信息
解决连接CSDN
LIKE 模糊查询
语法句式如下:
SELECT filed1,filed2,...,filedn
FROM tablename WHERE filedm LIKE value;
LIKE关键字支持的通配符
符号 | 功能描述 |
---|---|
_ | 该通配符能匹配单个字符 |
% | 该通配符能匹配任意长度的字符串 |
- 带有 % 通配符的查询
- 带有 _ 通配符的查询
对查询结果进行排序
语法句式如下:
SELECT filed1,filed2,...,filedn
FROM tablename ORDER BY filedm [ASC|DESC];
简单分组查询
语法句式如下:
SELECT function()
FROM tablename WHERE CONDITION GROUP BY filed;
统计分组查询
语法句式如下:
联合查询
内连接查询
1. 自连接查询
自连接:表与其自身进行连接
select ts1.stuid ,ts1.name,ts1.classno
from t_student as ts1, t_student as ts2
WHERE ts1.classno = ts2.classno and ts2.name = 'Alicia Florric';