数据库简单查询

简单查询

语法句式如下：

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 表示降序

查询所有字段数据

1. 列出表的所有字段

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)

1. ”*“ 的使用

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关键字支持的通配符

符号	功能描述
_	该通配符能匹配单个字符
%	该通配符能匹配任意长度的字符串

1. 带有 % 通配符的查询

2. 带有 _ 通配符的查询

对查询结果进行排序

语法句式如下：

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';

更多请点击这里