一、准备实例(建表)
1. 表一(商店区域表)
[root@localhost ~]# mysql -uroot -p666520
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table location (region char(20),store_name char(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into location values('North','Beijing');
Query OK, 1 row affected (0.00 sec)
mysql> insert into location values('Eaet','shanghai');
Query OK, 1 row affected (0.00 sec)
mysql> insert into location values('South','Guangzhou');
Query OK, 1 row affected (0.00 sec)
mysql> insert into location values('South','Shenzhen');
Query OK, 1 row affected (0.01 sec)
mysql> select * from location;
+--------+------------+
| region | store_name |
+--------+------------+
| North | Beijing |
| East | Shanghai |
| South | Guangzhou |
| South | Shenzhen |
+--------+------------+
4 rows in set (0.00 sec)
2. 表2(商店销售表)
mysql> create table store_info (store_name char(20),sales int(10),date char(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into store_info values('Guangzhou',1500,'2020-12-05');
Query OK, 1 row affected (0.00 sec)
mysql> insert into store_info values('Shenzhen',250,'2020-12-07');
Query OK, 1 row affected (0.00 sec)
mysql> insert into store_info values('Guangzhou',300,'2020-12-08');
Query OK, 1 row affected (0.00 sec)
mysql> insert into store_info values('Beijing',700,'2020-12-08');
Query OK, 1 row affected (0.00 sec)
mysql> select * from store_info;
+------------+-------+------------+
| store_name | sales | date |
+------------+-------+------------+
| Guangzhou | 1500 | 2020-12-05 |
| Shenzhen | 250 | 2020-12-07 |
| Guangzhou | 300 | 2020-12-08 |
| Beijing | 700 | 2020-12-08 |
+------------+-------+------------+
4 rows in set (0.00 sec)
3. 表3(城市表
mysql> create table city(city_name char(20));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into city values('beijing'),('nanjing'),('shanghai');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into city values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into city values();
Query OK, 1 row affected (0.00 sec)
mysql> insert into city values('');
Query OK, 1 row affected (0.00 sec)
mysql> select * from city;
+-----------+
| city_name |
+-----------+
| beijing |
| nanjing |
| shanghai |
| NULL |
| NULL |
| |
+-----------+
6 rows in set (0.00 sec)
4. 表4(total_sales)
mysql> create table total_sales (name char(20),sales int(5));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into total_sales values('zhangsan',10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into total_sales values('lisi',15);
Query OK, 1 row affected (0.01 sec)
mysql> insert into total_sales values('wangwu',20);
Query OK, 1 row affected (0.01 sec)
mysql> insert into total_sales values('zhaoliu',40);
Query OK, 1 row affected (0.00 sec)
mysql> insert into total_sales values('sunqi',50);
Query OK, 1 row affected (0.00 sec)
mysql> insert into total_sales values('zhouba',20);
Query OK, 1 row affected (0.01 sec)
mysql> insert into total_sales values('wujiu',30);
Query OK, 1 row affected (0.00 sec)
mysql> select * from total_sales;
+----------+-------+
| name | sales |
+----------+-------+
| zhangsan | 10 |
| lisi | 15 |
| wangwu | 20 |
| zhaoliu | 40 |
| sunqi | 50 |
| zhouba | 20 |
| wujiu | 30 |
+----------+-------+
7 rows in set (0.00 sec)
二、SQL语句
1. select
显示表格中一个或数个栏位的所有资料
语法:select “栏位” from “表名”;
2. distinct
不显示重复的资料
语法:select dstinct “栏位” from “表名”;
mysql> select distinct store_name from store_info;
+------------+
| store_name |
+------------+
| Guangzhou |
| Shenzhen |
| Beijing |
+------------+
3 rows in set (0.00 sec)
3. where
有条件查询
语法:select “栏位” from “表名” where “条件”;
mysql> select store_name from store_info where sales>1000;
+------------+
| store_name |
+------------+
| Guangzhou |
+------------+
1 row in set (0.00 sec)
4. and与or
且、或
语法:select “栏位” from “表名” where “条件1” {[and|or] “条件2”}…;
mysql> select store_name from store_info where sales>1000 or (sales<500 and sales>200);
+------------+
| store_name |
+------------+
| Guangzhou |
| Shenzhen |
| Guangzhou |
+------------+
3 rows in set (0.00 sec)
5. in
显示已知的值的资料
语法:select “栏位” from “表名” where “栏位” in (“值1”,“值2”,…);
mysql> select * from store_info where store_name in ('Beijing','Shenzhen');
+------------+-------+------------+
| store_name | sales | date |
+------------+-------+------------+
| Shenzhen | 250 | 2020-12-07 |
| Beijing | 700 | 2020-12-08 |
+------------+-------+------------+
2 rows in set (0.00 sec)
6. between
显示两个值范围内的资料
语法:select “栏位” from “表名” where “栏位” between ‘值1’ and ‘值2’;
mysql> select * from store_info where date between '2020-12-06' and '2020-12-10';
+------------+-------+------------+
| store_name | sales | date |
+------------+-------+------------+
| Shenzhen | 250 | 2020-12-07 |
| Guangzhou | 300 | 2020-12-08 |
| Beijing | 700 | 2020-12-08 |
+------------+-------+------------+
3 rows in set (0.00 sec)
7. limit
(1)显示前几行内容
语法:select “栏位” from “表名” limit ‘行数’;
mysql> select * from store_info limit 2;
+------------+-------+------------+
| store_name | sales | date |
+------------+-------+------------+
| Guangzhou | 1500 | 2020-12-05 |
| Shenzhen | 250 | 2020-12-07 |
+------------+-------+------------+
2 rows in set (0.00 sec)
(2)显示第几行后的前几行
语法:select “栏位” from “表名” limit “第几行后,显示行数”;
mysql> select * from store_info limit 2,2;
+------------+-------+------------+
| store_name | sales | date |
+------------+-------+------------+
| Guangzhou | 300 | 2020-12-08 |
| Beijing | 700 | 2020-12-08 |
+------------+-------+------------+
2 rows in set (0.00 sec)
8. 通配符
通常通配符都是跟like一起使用的
% 百分号表示零个、一个或多个字符
_ 下划线表示单个字符
例如:
‘A-Z’
表示所有以’A’起头,另一个任何值的字符,且以’Z’为结尾的字符串。例如,'ABZ’和’A2Z’都符合这一个模式,而’ABCZ’并不符合(因为A和Z之间有两个字符,而不是一个字符)
‘ABC%’
表示所有以’ABC’起头的字符串。例如,'ABCD’和’ABCABC’都符合这个模式。
‘%XYZ’
表示所有以’XYZ’结尾的字符串。例如,'WXYZ’和’ZZXYZ’都符合这个模式。
‘%AN%’
表示所有含有’AN’这个模式的字符串。例如,'SHANGHAI’和’XIAN’都符合这个模式。
‘_AN%’
表示第二个字母为’A’且第三个字母为’N’的字符串。例如,'HANGZHOU’和’LANZHOU’都符合这个模式,而’SHANGHAI’和’XIAN’则不符合这个模式。
9. like
匹配一个模式来搜查资料
语法:select “栏位” from “表名” where “栏位” like {模式};
mysql> select * from store_info where store_name like '%e%';
+------------+-------+------------+
| store_name | sales | date |
+------------+-------+------------+
| Shenzhen | 250 | 2020-12-07 |
| Beijing | 700 | 2020-12-08 |
+------------+-------+------------+
2 rows in set (0.00 sec)
10. order by
按关键字排序
语法:select “栏位” from “表名” [where “条件”] order by “栏位” [ASC,DESC];
ASC是按照升序进行排序的,是默认的排序方式。
DESC是按降序方式进行排序。
mysql> select store_name,sales,date from store_info order by sales desc;
+------------+-------+------------+
| store_name | sales | date |
+------------+-------+------------+
| Guangzhou | 1500 | 2020-12-05 |
| Beijing | 700 | 2020-12-08 |
| Guangzhou | 300 | 2020-12-08 |
| Shenzhen | 250 | 2020-12-07 |
+------------+-------+------------+
4 rows in set (0.00 sec)
mysql> select store_name,sales,date from store_info order by sales asc;
+------------+-------+------------+
| store_name | sales | date |
+------------+-------+------------+
| Shenzhen | 250 | 2020-12-07 |
| Guangzhou | 300 | 2020-12-08 |
| Beijing | 700 | 2020-12-08 |
| Guangzhou | 1500 | 2020-12-05 |
+------------+-------+------------+
4 rows in set (0.00 sec)
三、函数
1. 数学函数
常用的数学函数 | 说明 |
---|---|
abs(x) | 返回x的绝对值 |
rand() | 返回0-1的随机数 |
mod(x,y) | 返回x除以y以后的余数 |
power(x,y) | 返回x的y次方 |
round(x) | 返回离x最近的整数,即四舍五入到个位 |
round(x,y) | 返回x的y位小数且四舍五入后的值 |
sqrt(x) | 返回x的平方根 |
truncate(x,y) | 返回数字X截断为y位小数的值 |
ceil(x) | 返回大于或等于x的最小整数 |
floor(x) | 返回小于或等于x的最大整数 |
greatest(x1,x2…) | 返回集合中最大的值 |
least(x1,x2…) | 返回集合中最小的值 |
例:
(1)abs(x)
返回x的绝对值
mysql> select abs(-3.2);
+-----------+
| abs(-3.2) |
+-----------+
| 3.2 |
+-----------+
1 row in set (0.01 sec)
(2)rand()
返回0-1的随机数
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.1485054010045765 |
+--------------------+
1 row in set (0.00 sec)
(3)mod(x,y)
返回x除以y以后的余数
mysql> select mod(6,4);
+----------+
| mod(6,4) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
(4)power(x,y)
返回x的y次方
mysql> select power(3,2);
+------------+
| power(3,2) |
+------------+
| 9 |
+------------+
1 row in set (0.00 sec)
(5)round(x)
返回离x最近的整数,即四舍五入到个位
mysql> select round(3.423421)
-> ;
+-----------------+
| round(3.423421) |
+-----------------+
| 3 |
+-----------------+
1 row in set (0.00 sec)
mysql> select round(3.623421);
+-----------------+
| round(3.623421) |
+-----------------+
| 4 |
+-----------------+
1 row in set (0.00 sec)
(6)round(x,y)
返回x的y位小数且四舍五入后的值
mysql> select round(3.144,2);
+----------------+
| round(3.144,2) |
+----------------+
| 3.14 |
+----------------+
1 row in set (0.00 sec)
mysql> select round(3.147,2);
+----------------+
| round(3.147,2) |
+----------------+
| 3.15 |
+----------------+
1 row in set (0.00 sec)
(7)sqrt(x)
返回x的平方根
mysql> select sqrt(16);
+----------+
| sqrt(16) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> select sqrt(15);
+-------------------+
| sqrt(15) |
+-------------------+
| 3.872983346207417 |
+-------------------+
1 row in set (0.00 sec)
(8)truncate(x,y)
返回数字X保留为y位小数的值
mysql