为了方便,我们依然使用昨天的表,昨天的表的数据如下:
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+----------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+----------------+
| 1 | Pep E. LaPew | 100 Main Street | Los Angeles | CA | 90046 | USA | NULL | NULL |
| 3 | The Fudds | 100Main Street | Los Angeles | CA | 90046 | USA | NULL | elmer@fudd.com |
| 4 | Pep E. LaPew | 100 Main Street | Los Angeles | CA | 90046 | USA | NULL | NULL |
| 5 | M. Martian | 42 Galaxy Way | New York | NY | 11213 | USA | NULL | NULL |
+---------+--------------+-----------------+-------------+------------+----------+--------------+--------------+----------------+
1. 排序检索数据
(1) 按单列排序
select cust_name from customers order by cust_name;
输出结果按cust_name排序后的结果,得到的结果为:
+--------------+
| cust_name |
+--------------+
| M. Martian |
| Pep E. LaPew |
| Pep E. LaPew |
| The Fudds |
+--------------+
4 rows in set (0.00 sec)
(2)按多个列排序
select cust_name,cust_id from customers order by cust_name,cust_id;
这个排序是先按照cust_name排序,如果cust_name相同,就排序cust_id。得到的结果为:
+--------------+---------+
| cust_name | cust_id |
+--------------+---------+
| M. Martian | 5 |
| Pep E. LaPew | 1 |
| Pep E. LaPew | 4 |
| The Fudds | 3 |
+--------------+---------+
4 rows in set (0.00 sec)
(3)按照降序排列
select cust_id from customers order by cust_id desc;
得到的结果为:
+---------+
| cust_id |
+---------+
| 5 |
| 4 |
| 3 |
| 1 |
+---------+
4 rows in set (0.00 sec)
(4)按照前者前者降序后者升序
select cust_id,cust_name from customers order by cust_name desc,cust_id;
这里是按照名字进行降序,然后再按照ID进行升序排列输出,这种排序的本身不会对原来数据库的信息进行操作,只是看出来排序而已。输出的结果为:
+---------+--------------+
| cust_id | cust_name |
+---------+--------------+
| 3 | The Fudds |
| 1 | Pep E. LaPew |
| 4 | Pep E. LaPew |
| 5 | M. Martian |
+---------+--------------+
4 rows in set (0.00 sec)
2. 数据过滤
(1) AND操作符
select cust_id,cust_name from customers where cust_id > 2 and cust_id <5;
上面的SQL语句表示选出id在(2,5)之间的id和name。运行的结果如下:
+---------+--------------+
| cust_id | cust_name |
+---------+--------------+
| 3 | The Fudds |
| 4 | Pep E. LaPew |
+---------+--------------+
2 rows in set (0.02 sec)
(2) OR操作符
select cust_id,cust_name from customers where cust_id > 2 or cust_id <5;
这句表示选择id<2或者>5的所对应的id和name,其实就是全部,因为任何一个数都可以表示为>2或<5,结果如下:
+---------+--------------+
| cust_id | cust_name |
+---------+--------------+
| 1 | Pep E. LaPew |
| 3 | The Fudds |
| 4 | Pep E. LaPew |
| 5 | M. Martian |
+---------+--------------+
4 rows in set (0.00 sec)
(3)计算次序
SQL在处理OR操作符之前,优先处理AND操作符。也就是AND操作符的优先级要高于OR。但其实对于原来的OR而言,一点改变也没有,前面是真的就是真的,遇到and的时候既可以用括号括起来在进行判断。
(4)IN操作符
select cust_id,cust_name from customers where cust_id in (1,5);
选择的cust_id要在(1,5)中,注意这不是区间,是元组(python),元组是什么东西,额,就是数组吧,你当成这是包含两个元素的list,cust_id需要在里面出现才可以算对的,所以运行的结果为:
+---------+--------------+
| cust_id | cust_name |
+---------+--------------+
| 1 | Pep E. LaPew |
| 5 | M. Martian |
+---------+--------------+
2 rows in set (0.00 sec)
(5)NOT 操作符
如果我们需要输出这个cust_id不是1也不是5就可以使用以下的SQL语句:
select cust_id,cust_name from customers where cust_id not in (1,5);
输出的结果第(4)个互补就是整一个数据表:
+---------+--------------+
| cust_id | cust_name |
+---------+--------------+
| 3 | The Fudds |
| 4 | Pep E. LaPew |
+---------+--------------+
2 rows in set (0.00 sec)
3. 用通配符进行数据过滤
(1)百分号(%)通配符
在搜索中,%表示任何字符出现任意次数。为了方便,我们打算查找所有名字以'Pe'开头的列,其SQL语句为:
select cust_id,cust_name from customers where cust_name like 'Pe%';
搜索的结果为:
+---------+--------------+
| cust_id | cust_name |
+---------+--------------+
| 1 | Pep E. LaPew |
| 4 | Pep E. LaPew |
+---------+--------------+
2 rows in set (0.00 sec)
那怎么在字符串中间任意匹配呢,只需要在前面也加一个'%'就可以了,SQL语句如下:
select cust_id,cust_name from customers where cust_name like '%Pe%';
这里为了能看出结果,我特意加了一个新的列,所以输出的结果为:
+---------+--------------+
| cust_id | cust_name |
+---------+--------------+
| 1 | Pep E. LaPew |
| 4 | Pep E. LaPew |
| 6 | APe |
+---------+--------------+
3 rows in set (0.00 sec)
那只在结尾呢,我想聪明的你一定想到了吧,没错,就是'%Pe'
(2)下划线(_)通配符
下划线通配符和百分号通配符的作用是一样的,只是这只匹配单个字符而不是多个字符,举个例子:
select cust_id,cust_name from customers where cust_name like '_Pe';
这个SQL语句运行的结果只包含3个字符,第一个随机匹配,第二和第三个分别是'P'和'e',所以结果为:
+---------+-----------+
| cust_id | cust_name |
+---------+-----------+
| 6 | APe |
+---------+-----------+
1 row in set (0.00 sec)
(3)通配符的使用技巧
* 不要过度使用通配符,如果其他操作符可以满足需求就需要使用其他操作符
* 在确实需要使用通配符的时候,除非需要,否则不要把它们放在模式匹配的开头,这样的效率很低。
* 需要注意通配符的位置,放错,很可能会返回错误的结果。
4. 用正则表达式进行搜索
(1)使用Mysql正则表达式:
select cust_id,cust_name from customers where cust_name regexp 'Pe';
这一行和上面的like '%Pe%'类似,就是找出字符串里面出现Pe的列都输出出来,运行结果如下:
+---------+--------------+
| cust_id | cust_name |
+---------+--------------+
| 1 | Pep E. LaPew |
| 4 | Pep E. LaPew |
| 6 | APe |
+---------+--------------+
3 rows in set (0.00 sec)
在正则表达式中,'.'可以匹配任意一个字符,因此如果是这样'.e'表示字符串里面有e并且e不是首字符,相应的sql语句如下:
select cust_id,cust_name from customers where cust_name regexp '.e';
得到的结果为:
+---------+--------------+
| cust_id | cust_name |
+---------+--------------+
| 1 | Pep E. LaPew |
| 3 | The Fudds |
| 4 | Pep E. LaPew |
| 6 | APe |
+---------+--------------+
4 rows in set (0.00 sec)
(2)使用OR匹配
现在我们需要找出所有名字里面有'a'和'A'对应的列的id和姓名,就可以使用下面的SQL语句:
select cust_id,cust_name from customers where cust_name regexp 'a|A';
这样得到的结果如下:
+---------+--------------+
| cust_id | cust_name |
+---------+--------------+
| 1 | Pep E. LaPew |
| 4 | Pep E. LaPew |
| 5 | M. Martian |
| 6 | APe |
+---------+--------------+
4 rows in set (0.00 sec)
(3)匹配几个字符之一
如果我们需要匹配出名字里含有一个M或L或P后面跟着a的情况,则可以使用下面的SQL语句:
select cust_id,cust_name from customers where cust_name regexp '[M|L|P]a';
输出的结果为:
+---------+--------------+
| cust_id | cust_name |
+---------+--------------+
| 1 | Pep E. LaPew |
| 4 | Pep E. LaPew |
| 5 | M. Martian |
+---------+--------------+
3 rows in set (0.00 sec)
(4)匹配范围
select cust_id,cust_name from customers where cust_id regexp '[1-5]';
这句SQL语句表示匹配cust_id的范围在[1,5]。所以输出的结果为:
+---------+--------------+
| cust_id | cust_name |
+---------+--------------+
| 1 | Pep E. LaPew |
| 3 | The Fudds |
| 4 | Pep E. LaPew |
| 5 | M. Martian |
+---------+--------------+
4 rows in set (0.00 sec)
(5)匹配特殊字符
匹配特殊的字符需要加上转义字符,比如'.',正则匹配这个表示匹配任意一个字符,所以是不能匹配'.'的,这时候需要加上转义字符'\\',这样就可以使用了,其他的特殊字符还有如下:
元字符 | 说明 |
\\f | 匹配换页 |
\\n | 匹配换行 |
\\r | 匹配回车 |
\\t | 匹配制表 |
\\v | 匹配纵向制表 |
\\\ | 匹配\ |
(6)匹配字符类
类 | 说明 |
[:alnum:] | 任意字母和数字 |
[:alpha:] | 任意字母 |
[:blank:] | 空格和制表 |
[:cntrl:] | ASCII控制字符 |
[:dight:] | 任意数字 |
[:graph:] | 与[:print:]相同,但不包括空格 |
[:lower:] | 任意小写字母 |
[:print:] | 任意可打印字符 |
[:punct:] | 既不在[:alnum:]也不再[:cntrl:]中的任意字符 |
[:space:] | 包括空格在内的任意空白字符 |
[:upper:] | 任意大写字母 |
[:xdight:] | 任意十六进制数字 |
(7)匹配多个实例
元字符 | 说明 |
* | 0个或对个匹配 |
+ | 1个或多个匹配 |
? | 0个或1个匹配 |
{n} | 指定数目的匹配 |
{n,} | 不少于指定数目的匹配 |
{n,m} | 匹配数目的范围 |
(8)定位符
元字符 | 说明 |
^ | 文本的开始 |
$ | 文本的结尾 |
5. Auto_increment
建立表的时候对其进行初始值的设定:
CREATE TABLE STATE (ID INT PRIMARY KEY AUTO_INCREMENT)AUTO_INCREMENT=100;
若建立表格之后,由于某种原因,比如Python进行数据修改完了提交,就会导致Auto_increment的值变成不连续的,这个时候就需要对其进行重新复制,语句如下:
alter table STATE AUTO_INCREMENT = 2;
6. 创建计算字段
(1)拼接字段
拼接:将值联结到一起构成单个值
假设有这样的需要,我们需要获取成员的ID和Name,并且安装ID(Name)的格式输出所有的列,那么就需要拼接。下面的SQL语句使用Concat()函数来拼接两个列:
select Concat(cust_id,'(',cust_name,')') from customers order by cust_id;
输出的结果如下:
+-----------------------------------+
| Concat(cust_id,'(',cust_name,')') |
+-----------------------------------+
| 1(Pep E. LaPew) |
| 3(The Fudds) |
| 4(Pep E. LaPew) |
| 5(M. Martian) |
| 6(APe) |
+-----------------------------------+
5 rows in set (0.00 sec)
RTrim函数和LTrim函数:去掉值右边所有的空格和去掉左边所有的空格。
(2)使用别名
像上面一样的名字,又长又没有意义,这个时候就可以使用别名,给这个虚的表输出一个名字,SQL语句如下:
select Concat(RTrim(cust_id),'(',RTrim(cust_name),')') as cust_title from customers order by cust_id;
输出的结果如下:
+-----------------+
| cust_title |
+-----------------+
| 1(Pep E. LaPew) |
| 3(The Fudds) |
| 4(Pep E. LaPew) |
| 5(M. Martian) |
| 6(APe) |
+-----------------+
5 rows in set (0.00 sec)
(3)运行算术计算
这里使用的表有点不同,所以只好重新建一个:
create table orderitems(
prod_id char(60) not null primary key,
quantity integer,
item_price integer
);
建完表之后插入数据:
insert into orderitems values
('ANVO1',10,6),
('ANVO2',3,10),
('TNT2',5,10),
('FB',1,10);
此时数据库里面的信息为:
+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| ANVO1 | 10 | 6 |
| ANVO2 | 3 | 10 |
| FB | 1 | 10 |
| TNT2 | 5 | 10 |
+---------+----------+------------+
4 rows in set (0.00 sec)
好,接下来就是算术运算的SQL语句:
select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems;
输出的结果为:
+---------+----------+------------+----------------+
| prod_id | quantity | item_price | expanded_price |
+---------+----------+------------+----------------+
| ANVO1 | 10 | 6 | 60 |
| ANVO2 | 3 | 10 | 30 |
| FB | 1 | 10 | 10 |
| TNT2 | 5 | 10 | 50 |
+---------+----------+------------+----------------+
4 rows in set (0.00 sec)
mysql支持的算术运算有+,-,*,/这四种运算。
7. 使用数据处理函数
(1)文本处理函数
先给个例子,现在想要将所有的Name以大写的形式输出出来,可以使用以下的SQL语句:
select cust_name,Upper(cust_name) from customers;
输出的结果为:
+--------------+------------------+
| cust_name | Upper(cust_name) |
+--------------+------------------+
| Pep E. LaPew | PEP E. LAPEW |
| The Fudds | THE FUDDS |
| Pep E. LaPew | PEP E. LAPEW |
| M. Martian | M. MARTIAN |
| APe | APE |
+--------------+------------------+
5 rows in set (0.00 sec)
常用的文本处理函数有:
函数 | 说明 |
Left() | 返回串左边的字符 |
Length() | 返回串的长度 |
Locate() | 找出串的一个子串 |
Lower() | 将串转换成小写 |
LTrim() | 去掉串左边的空格 |
Right() | 返回串右边的字符 |
RTrim() | 去掉串右边的空格 |
Soundex() | 返回串的SOUNDEX值 |
SubString() | 返回子串的字符 |
Upper() | 将串改成大写 |
对SubString(),我解释下,这个函数的参数有3个,第一个为主串,第二个为Begin,即开始节点,第三个为Number,即需要截获的串的长度,比如下面的SQL表示获取Name第二个字符开始之后的2个字符:
select cust_name,SubString(cust_name,2,2) from customers;
输出的结果为:
+--------------+--------------------------+
| cust_name | SubString(cust_name,2,2) |
+--------------+--------------------------+
| Pep E. LaPew | ep |
| The Fudds | he |
| Pep E. LaPew | ep |
| M. Martian | . |
| APe | Pe |
+--------------+--------------------------+
5 rows in set (0.00 sec)
(2)日期和时间处理函数
这里主要用在WHERE里面,来判断时间是否符合要求。
常见的日期和时间处理函数如下:
函数 | 说明 |
AddDate() | 增加一个日期 |
AddTime() | 增加一个时间 |
CurDate() | 返回当前日期 |
CurTime() | 返回当前时间 |
Date() | 返回日期时间的日期部分 |
DateDiff() | 计算两个日期的差 |
Date_Add() | 高度灵活的日期运算函数 |
Date_Format() | 返回一个格式化的日期或时间串 |
Day() | 返回一个日期的天数部分 |
DayofWeek() | 对于一个日期,返回对应的星期几 |
Hour() | 返回一个时间的小时部分 |
Minute() | 返回一个时间的分钟部分 |
Month() | 返回一个日期的月份部分 |
Now() | 返回当前日期和时间 |
Second() | 返回一个时间的秒部分 |
TIme() | 返回一个日期时间的时间部分 |
Year() | 返回一个日期的年份部分 |
书上使用的例子:
select cust_id,order_num from orders where Date(order_date) = '2005-09-01';
(3)数值处理函数
函数 | 说明 |
Abs() | 返回一个数的绝对值 |
Cos() | 返回一个角度的余弦 |
Exp() | 返回一个数的指数值 |
Mod() | 返回除操作的余数 |
PI() | 返回圆周率 |
Rand() | 返回一个随机数 |
Sin() | 返回一个角度的正弦 |
Sqrt() | 返回一个数的平方根 |
Tan() | 返回一个角度的正切 |
使用示例:
select Mod(cust_id,3) as d from customers;
这里的意思是将ID都去除以3,然后输出它们的余数:
+------+
| d |
+------+
| 1 |
| 0 |
| 1 |
| 2 |
| 0 |
+------+
5 rows in set (0.00 sec)
8。 聚集函数
聚集函数:运行在行组上,计算和返回单个值的函数。
SQL聚集函数如下:
函数 | 说明 |
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列之和 |
(1)AVG函数
select avg(cust_id) from customers;
计算ID的平均值:
+--------------+
| avg(cust_id) |
+--------------+
| 3.8000 |
+--------------+
1 row in set (0.00 sec)
(2)COUNT函数
count有两种使用的方式:
* 使用count(*)对表中的数目进行计数,不管表列中包含的是空值还是非空值
* 使用count(column)对特定的列具有的值进行计数,忽略NULL值
select count(cust_id) from customers;
输出结果如下:
+----------------+
| count(cust_id) |
+----------------+
| 5 |
+----------------+
1 row in set (0.00 sec)
(3)MIN函数与MAX函数
这两个函数是兄弟,只是一个输出最小值,一个输出最大值而已。
示例:
select max(cust_id),min(cust_id) from customers;
输出的结果如下:
+--------------+--------------+
| max(cust_id) | min(cust_id) |
+--------------+--------------+
| 6 | 1 |
+--------------+--------------+
1 row in set (0.00 sec)
(4)SUM函数
示例:
select sum(cust_id) from customers;
输出:
+--------------+
| sum(cust_id) |
+--------------+
| 19 |
+--------------+
1 row in set (0.00 sec)
(5)聚集不同值
下面的SQL句是在不同的Name里面找出最小的Name
select min(distinct cust_name) from customers;
输出:
+-------------------------+
| min(distinct cust_name) |
+-------------------------+
| APe |
+-------------------------+
1 row in set (0.00 sec)