本篇博客主要介绍mysql的各种各样的查询命令,关于其他的创建用户,改表的结构就不在这里说了
因为还带了执行结果,所以用目录引索还是方便点
首先看看表的结构
MariaDB [Products]> desc products;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| prod_id | int(11) | NO | | NULL | |
| prod_name | varchar(20) | NO | | NULL | |
| prod_price | float | NO | | NULL | |
| prod_country | varchar(2) | YES | | CN | |
| vend_id | int(11) | YES | | NULL | |
| prod_desc | varchar(100) | YES | | No desc | |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
再看看表中的内容
MariaDB [Products]> select * from products;
+---------+----------------------+------------+--------------+---------+-----------------------+
| prod_id | prod_name | prod_price | prod_country | vend_id | prod_desc |
+---------+----------------------+------------+--------------+---------+-----------------------+
| 1 | snacks | 2 | CN | 2 | Delicious cheap |
| 2 | Mi6 | 2100 | CN | 3 | National mobile phone |
| 3 | IPhone7 | 5700 | US | 2 | Special nouveau riche |
| 4 | Inter Chip | 200 | US | 1 | No desc |
| 5 | Flower | 2.2 | CN | 1 | No desc |
| 6 | U disk | 92.3 | CN | 2 | Useful |
| 7 | Book of nuclear phys | 450.2 | RS | 3 | reliable |
| 8 | dog | 400.3 | CN | 3 | Lovely |
| 9 | Picture | 20.5 | CN | 2 | No desc |
| 10 | Poker | 2.5 | CN | 3 | can pass the time |
+---------+----------------------+------------+--------------+---------+-----------------------+
10 rows in set (0.00 sec)
现在开始介绍查询命令
1.最简单的select语句(检索数据)
检查多个列
MariaDB [Products]> select prod_id,prod_name from products;
+---------+----------------------+
| prod_id | prod_name |
+---------+----------------------+
| 1 | snacks |
| 2 | Mi6 |
| 3 | IPhone7 |
| 4 | Inter Chip |
| 5 | Flower |
| 6 | U disk |
| 7 | Book of nuclear phys |
| 8 | dog |
| 9 | Picture |
| 10 | Poker |
+---------+----------------------+
10 rows in set (0.00 sec)
检索不同的行
MariaDB [Products]> select distinct vend_id from products;
+---------+
| vend_id |
+---------+
| 2 |
| 3 |
| 1 |
+---------+
3 rows in set (0.00 sec)
限制结果
MariaDB [Products]> select prod_id from products limit 5;
+---------+
| prod_id |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+---------+
5 rows in set (0.01 sec)
从第3开始查询,查5行
MariaDB [Products]> select prod_id,prod_name from products limit 3,5;
+---------+----------------------+
| prod_id | prod_name |
+---------+----------------------+
| 4 | Inter Chip |
| 5 | Flower |
| 6 | U disk |
| 7 | Book of nuclear phys |
| 8 | dog |
+---------+----------------------+
5 rows in set (0.00 sec)
2.按顺序输出
从小到大(默认的)
MariaDB [Products]> select prod_name,prod_price from products order by prod_price;
+----------------------+------------+
| prod_name | prod_price |
+----------------------+------------+
| snacks | 2 |
| Flower | 2.2 |
| Poker | 2.5 |
| Picture | 20.5 |
| U disk | 92.3 |
| Inter Chip | 200 |
| dog | 400.3 |
| Book of nuclear phys | 450.2 |
| Mi6 | 2100 |
| IPhone7 | 5700 |
+----------------------+------------+
10 rows in set (0.00 sec)
从大到小
MariaDB [Products]> select prod_name,prod_price from products order by prod_price desc;
+----------------------+------------+
| prod_name | prod_price |
+----------------------+------------+
| IPhone7 | 5700 |
| Mi6 | 2100 |
| Book of nuclear phys | 450.2 |
| dog | 400.3 |
| Inter Chip | 200 |
| U disk | 92.3 |
| Picture | 20.5 |
| Poker | 2.5 |
| Flower | 2.2 |
| snacks | 2 |
+----------------------+------------+
10 rows in set (0.00 sec)
给多个列排序(这个不好意思,没效果,假如有3个价格都是2块的,还会对vend_id排序的)
MariaDB [Products]> select prod_name,prod_price,vend_id from products order by prod_price,vend_id desc;
+----------------------+------------+---------+
| prod_name | prod_price | vend_id |
+----------------------+------------+---------+
| snacks | 2 | 2 |
| Flower | 2.2 | 1 |
| Poker | 2.5 | 3 |
| Picture | 20.5 | 2 |
| U disk | 92.3 | 2 |
| Inter Chip | 200 | 1 |
| dog | 400.3 | 3 |
| Book of nuclear phys | 450.2 | 3 |
| Mi6 | 2100 | 3 |
| IPhone7 | 5700 | 2 |
+----------------------+------------+---------+
10 rows in set (0.00 sec)
3.过滤数据
找出大于100块的物品。
MariaDB [Products]> select prod_name,prod_price from products where prod_price>100;
+----------------------+------------+
| prod_name | prod_price |
+----------------------+------------+
| Mi6 | 2100 |
| IPhone7 | 5700 |
| Inter Chip | 200 |
| Book of nuclear phys | 450.2 |
| dog | 400.3 |
+----------------------+------------+
5 rows in set (0.00 sec)
LIKE 通配符过滤
MariaDB [Products]> select prod_id,prod_desc from products where prod_desc like 'No%';
+---------+-----------+
| prod_id | prod_desc |
+---------+-----------+
| 4 | No desc |
| 5 | No desc |
| 9 | No desc |
+---------+-----------+
3 rows in set (0.00 sec)
like 还是很low的。其中%代表任何字符出现任何次数。_代表的是匹配一个字符
下面说的是更强大的regexp可以匹配只要有这个单词就匹配。跟grep有点像
MariaDB [Products]> select prod_id,prod_desc from products where prod_desc regexp 'No';
+---------+-----------------------+
| prod_id | prod_desc |
+---------+-----------------------+
| 3 | Special nouveau riche |
| 4 | No desc |
| 5 | No desc |
| 9 | No desc |
+---------+-----------------------+
4 rows in set (0.00 sec)
这里的匹配就和shell脚本里的的正则表达式就很像似了(\\就是\转移)
MariaDB [Products]> select prod_id,prod_price from products where prod_price regexp '\\.';
+---------+------------+
| prod_id | prod_price |
+---------+------------+
| 5 | 2.2 |
| 6 | 92.3 |
| 7 | 450.2 |
| 8 | 400.3 |
| 9 | 20.5 |
| 10 | 2.5 |
+---------+------------+
6 rows in set (0.00 sec)
小结一下,[123]表示1,2,3都有可能出现一次 或者 1|2|3
而[^123]就是不可能是1,2,3中的一个。^和$就是表示可能开头和结尾是什么。是不是和shell一模一样呢。对了还有[:alnum:]等等用法和.代表任意一个字符。?匹配前面的字符出现0,1次.+代表前面的字符出现1或者多次。*代表匹配0到多次。
MariaDB [Products]> select prod_id,prod_price from products where prod_price regexp '[[:digit:]]{4}';
+---------+------------+
| prod_id | prod_price |
+---------+------------+
| 2 | 2100 |
| 3 | 5700 |
+---------+------------+
2 rows in set (0.00 sec)
4.创建计算字段
为了演示这个我再创建一列
MariaDB [Products]> alter table products add prod_num int NOT NULL after prod_price;
Query OK, 10 rows affected (0.15 sec)
Records: 10 Duplicates: 0 Warnings: 0
创建两个列合成一个列输出
MariaDB [Products]> select Concat(prod_name,'(',prod_country,')') as prod_title from products order by prod_name;
+--------------------------+
| prod_title |
+--------------------------+
| Book of nuclear phys(RS) |
| dog(CN) |
| Flower(CN) |
| Inter Chip(US) |
| IPhone7(US) |
| Mi6(CN) |
| Picture(CN) |
| Poker(CN) |
| snacks(CN) |
| U disk(CN) |
+--------------------------+
10 rows in set (0.00 sec)
使用Concat和并列,并用as起个别名。其实as是很实用的一个命令。
使用算数查找,个计算产品单价和产品个数的乘积。而且本来并不存在产品总价这列
MariaDB [Products]> select prod_name,prod_price,prod_num,prod_price*prod_num as item_price from products ;
+----------------------+------------+----------+--------------------+
| prod_name | prod_price | prod_num | item_price |
+----------------------+------------+----------+--------------------+
| snacks | 2 | 1 | 2 |
| Mi6 | 2100 | 23 | 48300 |
| IPhone7 | 5700 | 2 | 11400 |
| Inter Chip | 200 | 2 | 400 |
| Flower | 2.2 | 4 | 8.800000190734863 |
| U disk | 92.3 | 7 | 646.1000213623047 |
| Book of nuclear phys | 450.2 | 18 | 8103.6002197265625 |
| dog | 400.3 | 20 | 8005.999755859375 |
| Picture | 20.5 | 0 | 0 |
| Poker | 2.5 | 7 | 17.5 |
+----------------------+------------+----------+--------------------+
10 rows in set (0.00 sec)
后面的那个是因为flout类型的5.使用函数
使用mysql的函数。
MariaDB [Products]> select upper(prod_name) from products;
+----------------------+
| upper(prod_name) |
+----------------------+
| SNACKS |
| MI6 |
| IPHONE7 |
| INTER CHIP |
| FLOWER |
| U DISK |
| BOOK OF NUCLEAR PHYS |
| DOG |
| PICTURE |
| POKER |
+----------------------+
10 rows in set (0.00 sec)
还有soundex()匹配发音类似的单词。
还有专门和日期和时间匹配的处理函数
AddDate() 增加一个日期
AddTime() 增加一个时间
Date_Add() 高度灵活的日期运算函数
CurDate() 返回当前日期
CurTime() 返回当前时间
Now() 返回当前日期和时间
DateDiff() 返回2个日期之差
Date_Format() 返回一个格式化的日期或时间串
Day() 返回日期中的天数
DayOfWeek() 返回日期中的星期
Hour() 返回日期中的小时部分
Minute() 返回日期中的分钟部分
Month() 返回日期中的月份部分
Second() 返回日期中的秒数部分
Time() 返回日期中的时间部分
Date() 返回日期中的日期部分
Year() 返回日期中的年份部分
Year() 返回日期中的年份部分
MariaDB [Products]> select upper(prod_name),Now() as Time from products;
+----------------------+---------------------+
| upper(prod_name) | Time |
+----------------------+---------------------+
| SNACKS | 2016-12-01 10:29:46 |
| MI6 | 2016-12-01 10:29:46 |
| IPHONE7 | 2016-12-01 10:29:46 |
| INTER CHIP | 2016-12-01 10:29:46 |
| FLOWER | 2016-12-01 10:29:46 |
| U DISK | 2016-12-01 10:29:46 |
| BOOK OF NUCLEAR PHYS | 2016-12-01 10:29:46 |
| DOG | 2016-12-01 10:29:46 |
| PICTURE | 2016-12-01 10:29:46 |
| POKER | 2016-12-01 10:29:46 |
+----------------------+---------------------+
AVG() 返回某列的平均值
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列的总和
MariaDB [Products]> select AVG(prod_price) as average from products;
+-------------------+
| average |
+-------------------+
| 897.0000003099442 |
+-------------------+
1 row in set (0.00 sec)