- LIMIT:用于规定要返回的记录的数目
LIMIT的语法结构:
SELECT column_name(s)
FROM table_name
LIMIT number;
复制代码
- LIKE:用于在 WHERE 子句中搜索列中的指定模式
LIKE的语法结构:
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
复制代码
其中pattern可能用到以下通配符:
-
% :替代 0 个或多个字符
-
_ :替代一个字符
-
[charlist]: 字符列中的任何单一字符
-
[^charlist]:不在字符列中的任何单一字符
-
除了上述通配符,REGEXP 或 NOT REGEXP也是用来操作正则表达式的通配符,只是不用LIKE引出,例如:
SELECT * FROM Websites
WHERE name REGEXP '^[GFs]';
复制代码
选取 name 以 "G"、"F" 或 "s" 开始的所有网站
- IN VS BETWEEN:
IN的语法结构:#允许在 WHERE 子句中规定多个值
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
BETWEEN的语法结构:#选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
复制代码
二者可以结合使用,例如选取 alexa 介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站:
SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND country NOT IN ('USA', 'IND');
复制代码
-
注意,在MYSQL中,BETWEEN语句搜索结果包括两个边界值
-
JOIN:把来自两个或多个表的行结合起来
JOIN的语法结构:
SELECT column_name(s) #选择要显示的行或列
FROM table1 #左表
INNER/LEFT/RIGHT JOIN table2 #右表
ON table1.column_name=table2.column_name
复制代码
-
INNER JOIN只返回两个表中均有匹配的行,如果 "Websites" 表中的行在 "access_log" 中没有匹配,则不会列出这些行
-
LEFT JOIN 关键字从左表(Websites)返回所有的行,即使右表(access_log)中没有匹配
-
RIGHT JOIN 关键字从右表(Websites)返回所有的行,即使左表(access_log)中没有匹配
-
UNION:在多个表格中搜索匹配的结果
UNION的语法结构:
SELECT column_name(s) FROM table1
UNION (ALL) #无ALL时返回不重复的值,有ALL时返回所有值
SELECT column_name(s) FROM table2;
复制代码
可以与WHERE结合使用:
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;
复制代码
-
使用UNION命令时需要注意,只能在最后使用一个ORDER BY命令,是将两个查询结果合在一起之后,再进行排序。
-
UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同
-
CREATE DATABASE VS CREATE TABLE:
CREATE DATABASE的语法结构:
CREATE DATABASE dbname
CREATE TABLE的语法结构:
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
)
复制代码
-
约束(暂时简单了解):
-
NOT NULL - 指示某列不能存储 NULL 值。
-
UNIQUE - 保证某列的每行必须有唯一的值。
-
PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
-
FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
-
CHECK - 保证列中的值符合指定的条件。
-
DEFAULT - 规定没有给列赋值时的默认值。
接下来是pymysql:
- 1.创建数据库:
import pymysql
#创建数据库
db=pymysql.connect(host='localhost',user='root',password='password',port=3306)
cursor=db.cursor()
cursor.execute('select version()')
print(cursor.fetchone())
cursor.execute('create database spiders default character set utf8')
db.close()
复制代码
- 2.创建表:
#创建表
db=pymysql.connect(host='localhost',user='root',password='password',port=3306,db='spiders')
cursor=db.cursor()
sql='create table if not exists students (my_id varchar(255)not null,my_name varchar(255)not null,my_age int not null,primary key (my_id))'
cursor.execute(sql)
db.close()
复制代码
-
3.插入数据:
-
对于数据的插入,更新,删除,都需要commit()方法
#低配版插入数据
id='20120001'
user='mike'
age=20
db=pymysql.connect(host='localhost',user='root',password='password',port=3306,db='spiders')
cursor=db.cursor()
sql='insert into students(my_id,my_name,my_age) values(%s,%s,%s)'
try:
cursor.execute(sql,(id,user,age))
db.commit()
except:
db.rollback() #若插入失败,回滚数据
db.close()
#中配版插入数据(比低配版更方便)
db=pymysql.connect(host='localhost',user='root',password='password',port=3306,db='spiders')
cursor=db.cursor()
data={
'my_id':'19990702',
'my_name':'Bob',
'my_age':20
}
table='students'
keys=','.join(data.keys())
values=','.join(['%s']*len(data))
sql='insert into {table}({keys}) values({values})'.format(table=table,keys=keys,values=values)
print(sql)
try:
if cursor.execute(sql,tuple(data.values())):
print('插入成功')
db.commit()
except:
print('插入失败')
db.rollback()
db.close()
#高配版插入数据(若主键存在则更新数据,若主键不存在则插入数据)
db=pymysql.connect(host='localhost',user='root',password='password',port=3306,db='spiders')
cursor=db.cursor()
data={
'my_id':'19990202',
'my_name':'Bob',
'my_age':25
}
table='students'
keys=','.join(data.keys())
values=','.join(['%s']*len(data))
sql='insert into {table}({keys}) values({values}) on duplicate key update'.format(table=table,keys=keys,values=values)
updata= ','.join(['{key}=%s'.format(key=key) for key in data])
sql=sql+' '+updata #注意空格的添加问题
try:
if cursor.execute(sql,tuple(data.values())*2):
print('成功')
db.commit()
except:
print('失败')
db.rollback()
db.close()
复制代码
- 4.更新数据:
#更新数据
db=pymysql.connect(host='localhost',user='root',password='password',port=3306,db='spiders')
cursor=db.cursor()
sql='update students set my_age=%s where my_name=%s'
try:
print('更新成功')
cursor.execute(sql,(25,'Bob'))
db.commit()
except:
print('更新失败')
db.rollback()
db.close()
复制代码
建议用高配版插入数据的方法
- 5.删除数据:
#删除数据
db=pymysql.connect(host='localhost',user='root',password='password',port=3306,db='spiders')
cursor=db.cursor()
table='students'
condition='my_age>25'
sql='delete from {table} where {condition}'.format(table=table,condition=condition)
try:
cursor.execute(sql)
db.commit()
except:
print('失败')
db.rollback()
db.close()
复制代码
- 6.查询数据:
db=pymysql.connect(host='localhost',user='root',password='password',port=3306,db='spiders')
cursor=db.cursor()
sql='select * from students where my_age>=20'
try:
cursor.execute(sql)
print('count:',cursor.rowcount) #输出匹配sql的行数
one=cursor.fetchone() #返回第一行数据,元组类型
print(one)
results=cursor.fetchall() #返回指针偏移后的所有数据,元组类型
print(results)
# 一般如果数据量很大,不建议用cursor.fetchall()获取全部数据,用迭代
# row=cursor.fetchone()
# while row:
# print('Row:',row)
# row=cursor.fetchone()
except:
print('ERROR')
复制代码