进击RDC的第三天（3.23）

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')