1、登录mysql
mysql -u root -p
表
-
show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| mysql |
| performance_schema |
| selecttest |
| sys |
±-------------------+
5 rows in set (0.02 sec) -
use selecttest
Database changed -
show tables;
±---------------------+
| Tables_in_selecttest |
±---------------------+
| bank |
| course |
| score |
| student |
| teacher |
±---------------------+
5 rows in set (0.01 sec) -
describe student;
±-----------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±-----------±------------±-----±----±--------±------+
| s_no | varchar(20) | NO | PRI | NULL | |
| s_name | varchar(20) | NO | | NULL | |
| s_sex | varchar(10) | NO | | NULL | |
| s_birthday | datetime | YES | | NULL | |
| s_class | varchar(20) | YES | | NULL | |
±-----------±------------±-----±----±--------±------+
5 rows in set (0.01 sec)
2、检索数据
-
检索单个列 select
select s_no from student;
±-----+
| s_no |
±-----+
| 101 |
| 102 |
| 103 |
| 104 |
| 105 |
| 106 |
| 107 |
| 108 |
| 109 |
±-----+
9 rows in set (0.02 sec) -
检索多个列
select s_no,s_name,s_sex from student;
±-----±-------±------+
| s_no | s_name | s_sex |
±-----±-------±------+
| 101 | 曾华 | 男 |
| 102 | 匡明 | 男 |
| 103 | 王丽 | 女 |
| 104 | 李军 | 男 |
| 105 | 王芳 | 女 |
| 106 | 陆军 | 男 |
| 107 | 王尼玛 | 男 |
| 108 | 张全蛋 | 男 |
| 109 | 赵铁柱 | 男 |
±-----±-------±------+
9 rows in set (0.00 sec) -
检索所有列
select * from student;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
9 rows in set (0.00 sec) -
检索不同的行
distinct
select distinct s_class from student;
±--------+
| s_class |
±--------+
| 95033 |
| 95031 |
±--------+
2 rows in set (0.00 sec) -
限制结果
-
limit+n
limit后直接加数字表示:前多少行,有两个数字1,3,前一个数字表示从第一行开始,第二个表示有几行。行数从0行开始计算。
select * from student limit 5;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
5 rows in set (0.00 sec) -
limit+n,m
select * from student limit 1,5;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
5 rows in set (0.00 sec) -
完全限定的表名字
同时使用表名与列名;
3 排序检索数据
3.1 排序数据
使用order by 后面接着需要排序的列名,默认是从小到大 的排序
select s_birthday from student order by s_birthday;
±--------------------+
| s_birthday |
±--------------------+
| 1974-06-03 00:00:00 |
| 1974-06-03 00:00:00 |
| 1975-02-10 00:00:00 |
| 1975-02-10 00:00:00 |
| 1975-10-02 00:00:00 |
| 1976-01-23 00:00:00 |
| 1976-02-20 00:00:00 |
| 1976-02-20 00:00:00 |
| 1977-09-01 00:00:00 |
±--------------------+
9 rows in set (0.00 sec)
3.2 多个列排序
在order by 之后紧跟着多个列名就可以,并且每个列名之间用,分割
select s_no,s_name, s_birthday from student order by s_birthday,s_class;
±-----±-------±--------------------±--------+
| s_no | s_name | s_birthday | s_class |
±-----±-------±--------------------±--------+
| 106 | 陆军 | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱 | 1974-06-03 00:00:00 | 95031 |
| 105 | 王芳 | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 1975-02-10 00:00:00 | 95031 |
| 102 | 匡明 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 1976-02-20 00:00:00 | 95033 |
| 101 | 曾华 | 1977-09-01 00:00:00 | 95033 |
±-----±-------±--------------------±--------+
9 rows in set (0.00 sec)
3.3 指定排序的方向
order by 默认是降序排序,但可以通过在列名后面加上“DESC”关键字,而将对于该列的排序方向改为从高到低,从而实现灵活的排序。而ASC是用做升序排序的,但默认就是升序排序的,所以一般不需要写出来。
select s_no,s_name, s_birthday ,s_class from student order by s_birthday,s_class DESC;
±-----±-------±--------------------±--------+
| s_no | s_name | s_birthday | s_class |
±-----±-------±--------------------±--------+
| 106 | 陆军 | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱 | 1974-06-03 00:00:00 | 95031 |
| 105 | 王芳 | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 1975-02-10 00:00:00 | 95031 |
| 102 | 匡明 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 1976-02-20 00:00:00 | 95033 |
| 101 | 曾华 | 1977-09-01 00:00:00 | 95033 |
±-----±-------±--------------------±--------+
9 rows in set (0.00 sec)
3.4 一些说明
ORDER BY的位置,应该在from之后,因为order by 是对已经筛选出来的数据进行处理,必须得通过select 和from语句,将数据从数据库里面筛选出来。而limit又必须位于order by 之后,这个也是因为limit 是对order by 进行的进一步的操作。
4 过滤数据
4.1 基本数据筛选
where是用来筛选数据库数据的函数,在select之后,再对select里面的数据进行筛选。
select * from student where s_sex=‘男’;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
7 rows in set (0.00 sec)
操作符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 再指定的两个数之间 |
4.2 范围值检查
btween a and b
select * from student where s_no between 103 and 105;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
3 rows in set (0.00 sec)
4.3 空值检查
is null
select * from student where s_sex is null;
Empty set (0.00 sec)
4.4 组合条件过滤
操作符 | 意思 |
---|---|
and | 将两个条件合在一起 |
or | 任意一个条件满足即可 |
注意事项 | 不加括号前 and的优先级会高于or |
IN | 指定条件范围 |
NOT | 否定之后的条件 |
select * from student where s_sex=‘男’ and s_class=95031;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
4 rows in set (0.00 sec)
select * from student where s_sex=‘男’ or s_class=95031;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
8 rows in set (0.00 sec)
select * from student where s_class in(95031);
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
5 rows in set (0.00 sec)
select * from student where s_class not in(95031);
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
±-----±-------±------±--------------------±--------+
4 rows in set (0.00 sec)
5 通配符操作
5.1 百分号(%)通配符
表示任何字符出现的任意次数
通配符:用来匹配值的一部分的特殊字符
搜索模式:由字面值、通配符或者组合构成的搜索的条件。
通配符本身实际是SQL的WHERE子句中的有特殊含义的字符。
LIKE+通配符 表示是利用通配符匹配而不是直接相等匹配;
%可以匹配零个一个多个。但不可以匹配NULL;
select * from student where s_name like ‘王%’;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
±-----±-------±------±--------------------±--------+
3 rows in set (0.00 sec)
like之后的通配符必须加’ '使用
select * from student where s_class like ‘95%’;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明 | 男 | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军 | 男 | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军 | 男 | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男 | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男 | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男 | 1974-06-03 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
9 rows in set (0.00 sec)
select * from student where s_birthday like ‘1977-09-01%’;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 101 | 曾华 | 男 | 1977-09-01 00:00:00 | 95033 |
±-----±-------±------±--------------------±--------+
1 row in set (0.00 sec)
5.2 下划线_ 通配符
与%通配符的区别在于,下划线_ 匹配的是一个字符而不是多个字符。只能是一个,0个也不行。
select * from student where s_name like ‘王_’;
±-----±-------±------±--------------------±--------+
| s_no | s_name | s_sex | s_birthday | s_class |
±-----±-------±------±--------------------±--------+
| 103 | 王丽 | 女 | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳 | 女 | 1975-02-10 00:00:00 | 95031 |
±-----±-------±------±--------------------±--------+
2 rows in set (0.00 sec)
5.3 注意事项
通配符很有效但也需要付出响应的代价,搜索时间相对于其他筛选方式而言,速度会比较慢。
所以一定不要把通配符放在搜索的开始处。
6 正则表达式搜索
7、创建计算字段
7.1 拼接字段
concat()括号里面就是想要拼接的内容,注意是以字符串的形式来拼接。
select concat(s_name,’–’,s_sex) from student;
±--------------------------+
| concat(s_name,’–’,s_sex) |
±--------------------------+
| 曾华–男 |
| 匡明–男 |
| 王丽–女 |
| 李军–男 |
| 王芳–女 |
| 陆军–男 |
| 王尼玛–男 |
| 张全蛋–男 |
| 赵铁柱–男 |
±--------------------------+
9 rows in set (0.00 sec)
7.2 删除多余的括号
删除数据左侧多余的空客 Trim();
删除数据右侧多余的空格 LTrim();
7.3 使用别名
AS :AS后面接别名
select concat(s_name,’–’,s_sex) AS StuInfor from student;
±-----------+
| StuInfor |
±-----------+
| 曾华–男 |
| 匡明–男 |
| 王丽–女 |
| 李军–男 |
| 王芳–女 |
| 陆军–男 |
| 王尼玛–男 |
| 张全蛋–男 |
| 赵铁柱–男 |
±-----------+
9 rows in set (0.00 sec)
7.4 执行算数计算
可以直接利用字段相乘得到结果;
select s_no*s_class AS Rubbish from student;
±---------+
| Rubbish |
±---------+
| 9598333 |
| 9693162 |
| 9788399 |
| 9883432 |
| 9978255 |
| 10073286 |
| 10168531 |
| 10263348 |
| 10358379 |
±---------+
9 rows in set (0.00 sec)
select s_nos_class from student;
±-------------+
| s_nos_class |
±-------------+
| 9598333 |
| 9693162 |
| 9788399 |
| 9883432 |
| 9978255 |
| 10073286 |
| 10168531 |
| 10263348 |
| 10358379 |
±-------------+
9 rows in set (0.00 sec)
操作符 | 说明 |
---|---|
+ | 加 |
- | 减 |
* | 乘 |
/ | 除 |
8 字符操作函数
9 汇总数据
函数 | 说明 |
---|---|
AVG() | 返回某列的平均数 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | $ 返回某列值之和 |
9.1 avg()
select avg(sc_degree) as avg_score from score;
±----------+
| avg_score |
±----------+
| 81.1111 |
±----------+
1 row in set (0.00 sec)
通过这个示例可以看出来,avg处理的是已经筛选出来的数据,应该说,它的执行顺序是在where后面。
还可以通过where进一步的筛选,再对筛选之后的数据进行筛选。
select avg(sc_degree) as avg_score from score where c_no=‘3-245’;
±----------+
| avg_score |
±----------+
| 76.3333 |
±----------+
1 row in set (0.00 sec)
9.2 COUNT()
在使用count()函数的时候,count *与count(确定的列名)均可以得到行数,但有一个小区别,就是通过 count * 会计算NULL,而使用确定的列名是不会计算字段值是NULL的行数的。
select count(s_no) from student;
±------------+
| count(s_no) |
±------------+
| 9 |
±------------+
1 row in set (0.03 sec)
9.3 MAX()
max()返回所有行里面最大的值,对于字段值是null的行不会进行统计。并且要求指定列名。
select max(sc_degree) from score;
±---------------+
| max(sc_degree) |
±---------------+
| 92 |
±---------------+
1 row in set (0.00 sec)
9.4 MIN()
min()函数刚好相反,限制条件也都是一样的。
9.5 聚集不同行值
DISTINCT 可以在函数内部加上distinct() 参数,AVG(DISTINCT sc_degree);
9.6 组合聚集函数
所有的聚集函数均可以拥在一起。
select max(s_no),avg(sc_degree) from score;
±----------±---------------+
| max(s_no) | avg(sc_degree) |
±----------±---------------+
| 109 | 81.1111 |
±----------±---------------+
1 row in set (0.00 sec)
10 分组数据
9.1 GROUP BY 创建分组
select c_no,avg(sc_degree) from score group by c_no;
±------±---------------+
| c_no | avg(sc_degree) |
±------±---------------+
| 3-105 | 85.3333 |
| 3-245 | 76.3333 |
| 6-166 | 81.6667 |
±------±---------------+
3 rows in set (0.38 sec)
9.2 having 对分组之后的组 进行过滤
select c_no,avg(sc_degree) from score group by c_no having avg(sc_degree)>=80;
±------±---------------+
| c_no | avg(sc_degree) |
±------±---------------+
| 3-105 | 85.3333 |
| 6-166 | 81.6667 |
±------±---------------+
2 rows in set (0.00 sec)
having 与 where 的区别,两者处理的对象不一样,where 处理的对象是行,而having处理的对象是分组数据,两者的执行的顺序也不一样,where先于having
select c_no,avg(sc_degree) from score where s_no between 103 and 105 group by c_no having avg(sc_degree)>=80;
±------±---------------+
| c_no | avg(sc_degree) |
±------±---------------+
| 3-105 | 90.0000 |
| 3-245 | 80.5000 |
| 6-166 | 82.0000 |
±------±---------------+
3 rows in set (0.07 sec)