1:select语句Python连接实现
import pymysql
con = pymysql.connect(host='localhost',port=3306,user='root',passwd='123456',db='learning_sql')
cursor = con.cursor()
cursor.execute('SELECT prod_id,prod_name,prod_price FROM products;')#注意选择多列时候,列名后面再逗号
#SELECT * FROM products;通配符*可以显示表prodcuts中所有列
cursor.fetchall()
#输出结果,下面显示的是终端命令窗口显示的结果
+---------+---------------------+------------+
| prod_id | prod_name | prod_price |
+---------+---------------------+------------+
| BNBG01 | Fish bean bag toy | 3.49 |
| BNBG02 | Bird bean bag toy | 3.49 |
| BNBG03 | Rabbit bean bag toy | 3.49 |
| BR01 | 8 inch teddy bear | 5.99 |
| BR02 | 12 inch teddy bear | 8.99 |
| BR03 | 18 inch teddy bear | 11.99 |
| RGAN01 | Raggedy Ann | 4.99 |
| RYL01 | King doll | 9.49 |
| RYL02 | Queen doll | 9.49 |
+---------+---------------------+------------+
9 rows in set (0.00 sec)
cursor.execute('SELECT vend_id FROM products;')
cursor.fetchall()
+---------+
| vend_id |
+---------+
| BRS01 |
| BRS01 |
| BRS01 |
| DLL01 |
| DLL01 |
| DLL01 |
| DLL01 |
| FNG01 |
| FNG01 |
+---------+
#此句子查询会显示所有结果,要只显示不同的值,可以用关键之**DISTINT**,比如 SELECT DISTINCT vend_id FROM products;
如果只想对检查结果显示指定行数的话,可以用关键之LIMIT 5(MYSQL),TOP 5(SQLserver),FETCH FIRST 5 ROWS ONLY(DB2),WHERE ROWNUM <=5(oracl)
mysql> SELECT prod_id,prod_price,prod_name
-> FROM products
-> ORDER BY prod_price DESC;#关键字order by对输出结果排序,DESC指定是降序排序,如果要对每个列指定降序排序的话,每个列都需要有关键字DESC
+---------+------------+---------------------+
| prod_id | prod_price | prod_name |
+---------+------------+---------------------+
| BR03 | 11.99 | 18 inch teddy bear |
| RYL01 | 9.49 | King doll |
| RYL02 | 9.49 | Queen doll |
| BR02 | 8.99 | 12 inch teddy bear |
| BR01 | 5.99 | 8 inch teddy bear |
| RGAN01 | 4.99 | Raggedy Ann |
| BNBG01 | 3.49 | Fish bean bag toy |
| BNBG02 | 3.49 | Bird bean bag toy |
| BNBG03 | 3.49 | Rabbit bean bag toy |
+---------+------------+---------------------+
9 rows in set (0.00 sec)
一般用关键之where对输出结果过滤的时候,可以应用的过滤比较操作如下:
= 等于
< > 不等于
!= 不等于
< 小于
<= 小于等于
! 不小于
> 大于
>= 大于等于
!> 不大于
BETWEEN 在指定的两个值之间
IS NULL 为 NULL 值
举例:SELECT prod_name,prod_price FROM products
WHERE prod_price BETWEEN 5 AND 10;
一些比较常用逻辑操作符:AND ,OR ,NOT,NOT IN ,IN等用这些操作符可以组合更加复杂的过滤条件。
如果有些过需要过滤的条件不是已知的,可以使用通配符,比较常用的通配符
%:表示任何字符出现的次数,比如fish%,表示以fish开头的所有产品,有关键词LIKE作为谓语;%不能匹配NULL值,单个字符的通配符为_,DB2不支持这个通配符,ACCESS单个字符统配符为?,任意通配符为*
mysql> SELECT prod_id,prod_name
-> FROM products
-> WHERE prod_name LIKE 'fish%';
+---------+-------------------+
| prod_id | prod_name |
+---------+-------------------+
| BNBG01 | Fish bean bag toy |
+---------+-------------------+
mysql字符串拼接的时候要用CONCAT()函数,DB2可以用+号拼接
TTRIM(),RTRIM(),LTRIM() 可以对字符串空格修剪。
NOW()返回当前时间函数
mysql> SELECT Concat(vend_name, ' (', vend_country, ')')
-> AS vend_title
-> FROM Vendors
-> ORDER BY vend_name;
+-------------------------+
| vend_title |
+-------------------------+
| Bear Emporium (USA) |
| Bears R Us (USA) |
| Doll House Inc. (USA) |
| Fun and Games (England) |
| Furball Inc. (USA) |
| Jouets et ours (France) |
+-------------------------+
一些函数在每个数据库的区别
提取字符串的组成部分: Access 使用 MID() ; DB2 、 Oracle 、 PostgreSQL 和 SQLite 使用 SUBSTR() ; MySQL 和 SQL Server 使用 SUBSTRING()
数据类型转换:Access 和 Oracle 使用多个函数,每种类型的转换有一个函数; DB2 和 PostgreSQL 使用 CAST() ; MariaDB 、 MySQL 和 SQL Server 使用CONVERT()
取当前日期:Access 使用 NOW() ; DB2 和 PostgreSQL 使用 CURRENT_DATE ; MariaDB 和 MySQL 使用 CURDATE() ; Oracle 使用 SYSDATE ; SQL Server
使用 GETDATE() ; SQLite 使用 DATE()
一些可以在每个数据库中可以共用的函数
UPPER()字符串转化为大写函数
LOWER()字符串转化为小写
LEFT(字符串,num)返回字符串左边NUM个字符串,right()类似
举列子:
mysql> SELECT vend_name,left(vend_name,4) as fist_name
-> FROM vendors;
+-----------------+-----------+
| vend_name | fist_name |
+-----------------+-----------+
| Bear Emporium | Bear |
| Bears R Us | Bear |
| Doll House Inc. | Doll |
| Fun and Games | Fun |
| Furball Inc. | Furb |
| Jouets et ours | Joue |
+-----------------+-----------+
sounder()按字符串发音相识匹配字符串
mysql> SELECT cust_contact
-> FROM customers
-> WHERE SOUNDEX(cust_contact) = SOUNDEX('michael green');
+----------------+
| cust_contact |
+----------------+
| Michelle Green |
+----------------+
Michael Green和Michelle green发音相似,所以得以匹配。
日期处理函数,在每个数据库的都略有不同,mysql用year()函数提取年份
数值处理函数一般都通用
abs()绝对值,cos()余弦值,exp()指数值,pi()圆周率,sin()正弦值,sqrt()平方根。
常用的聚合函数
AVG() 返回某列的平均值,忽略值为NULL的行
COUNT() 返回某列的行数
MAX() 返回某列的最大值
MIN() 返回某列的最小值
SUM() 返回某列值之和
count(*)对所有行的数目都计数,不忽略NULL,count(columns)只对指定列非空数据计数
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
关键字DISTINCT,会让AVG函数在求平均值的时候,只考虑价格值不同的行。
mysql> SELECT vend_id,COUNT(*) AS EEE
-> FROM products
-> GROUP BY vend_id;
+---------+-----+
| vend_id | EEE |
+---------+-----+
| BRS01 | 3 |
| DLL01 | 4 |
| FNG01 | 2 |
+---------+-----+
聚合函数使用,需要注意的:group by必须在where语句之后,order by语句之前,
select中非计算字段的列,都必须在group by语句中给出。
对分组过滤的时候,要用关键字HAVING,而WHERE是用于行过滤
mysql> SELECT CUST_ID,count(*)
-> FROM orders
-> group by cust_id;
+------------+----------+
| CUST_ID | count(*) |
+------------+----------+
| 1000000001 | 2 |
| 1000000003 | 1 |
| 1000000004 | 1 |
| 1000000005 | 1 |
+------------+----------+
4 rows in set (0.03 sec)
mysql> SELECT cust_id,COUNT(*)
-> FROM orders
-> GROUP BY cust_id
-> HAVING COUNT(*) >= 2;
+------------+----------+
| cust_id | COUNT(*) |
+------------+----------+
| 1000000001 | 2 |
+------------+----------+
1 row in set (0.01 sec)
等值联结又称为内联结,可以用两种方法实现
#用关键字inner join
mysql> SELECT vend_name, prod_name, prod_price
-> FROM Vendors INNER JOIN Products
-> ON Vendors.vend_id = Products.vend_id;
#用where
mysql> SELECT vend_name,prod_name,prod_price
-> FROM vendors,products
-> WHERE vendors.vend_id = products.vend_id;
+-----------------+---------------------+------------+
| vend_name | prod_name | prod_price |
+-----------------+---------------------+------------+
| Doll House Inc. | Fish bean bag toy | 3.49 |
| Doll House Inc. | Bird bean bag toy | 3.49 |
| Doll House Inc. | Rabbit bean bag toy | 3.49 |
| Bears R Us | 8 inch teddy bear | 5.99 |
| Bears R Us | 12 inch teddy bear | 8.99 |
| Bears R Us | 18 inch teddy bear | 11.99 |
| Doll House Inc. | Raggedy Ann | 4.99 |
| Fun and Games | King doll | 9.49 |
| Fun and Games | Queen doll | 9.49 |
+-----------------+---------------------+------------+
自联结,在同一个表中查找
mysql> SELECT c1.cust_id, c1.cust_name, c1.cust_contact
-> FROM Customers AS c1 inner join Customers AS c2
-> ON c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';
+------------+-----------+--------------------+
| cust_id | cust_name | cust_contact |
+------------+-----------+--------------------+
| 1000000003 | Fun4All | Jim Jones |
| 1000000004 | Fun4All | Denise L. Stephens |
+------------+-----------+--------------------+
如果是外联结的话,要用关键字 LEFT OUTER JOIN
组合查询关键字union,连接多个select语句,每个语句必须有相同的列,计算字段或者聚集函数,union all,返回的结果不取消重复的行。