此文将以MYSQL数据库做为例子,pymysql库作为驱动进行学习
安装MYSQL数据库与pymysql第三方库
安装MYSQL数据库,请点击https://dev.mysql.com/downloads/installer/安装MYSQL的社区版本
安装pymysql库不多做叙述
-
安装navicat for mysql,此程序用来管理MYSQL数据库
注意: 连接过程中可能会出现1251错误
-
解决办法,在cmd命令下登录mysql后输入:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mysql的密码';注意:引号中的为你自己设置的sql数据库的密码FLUSH PRIVILEGES;
pymysql 连接mysql数据库的驱动库
-
Connection（host = None，user = None，password =''，database = None，port = 0，unix_socket = None，charset =''，sql_mode = None，read_default_file = None，conv = None，use_unicode = None，client_flag = 0，cursorclass = <class'pymysql.cursors.Cursor'>，init_command = None，connect_timeout = 10，ssl = None，read_default_group = None，compress = None，named_pipe = None，自动提交=假，分贝=无，passwd的=无，local_infile =假，max_allowed_packet个= 16777216，defer_connect =假，auth_plugin_map =无，read_timeout =无，write_timeout =无，bind_address =无，binary_prefix =假，程序名=无，server_public_key =无）类- read_timeout - 以秒为单位读取连接的超时（默认值：无 - 无超时）
- write_timeout - 以秒为单位写入连接的超时（默认值：无 - 无超时）
- charset - 你要使用的Charset。
- sql_mode - 要使用的默认SQL_MODE。
- read_default_file - 指定my.cnf文件以从[client]部分下读取这些参数。
- conv - 使用转换字典而不是默认字典。这用于提供类型的自定义编组和解组。见转换器。
- use_unicode - 是否默认为unicode字符串。对于Py3k，此选项默认为true。
- client_flag - 要发送给MySQL的自定义标志。在constants.CLIENT中查找潜在值。
- cursorclass - 要使用的自定义游标类。
- init_command - 建立连接时要运行的初始SQL语句。
- connect_timeout - 连接时抛出异常之前的超时。（默认值：10，最小值：1，最大值：31536000）
- ssl - 类似于mysql_ssl_set（）参数的参数的dict。目前，不支持capath和cipher参数。
- read_default_group - 要在配置文件中读取的组。
- compress - 不支持
- named_pipe - 不支持
- autocommit - 自动提交模式。无表示使用服务器默认值。（默认值：False）
- local_infile - 允许使用LOAD DATA LOCAL命令的布尔值。（默认值：False）
- max_allowed_packet - 发送到服务器的最大数据包大小（以字节为单位）。（默认值：16MB）仅用于限制小于默认值（16KB）的“LOAD LOCAL INFILE”数据包的大小。
- defer_connect - 不要明确连接contruction - 等待连接调用。（默认值：False）
- auth_plugin_map - 插件名称的一个字典，用于处理该插件的类。该类将Connection对象作为构造函数的参数。该类需要一个认证方法，将认证包作为参数。对于对话框插件，可以使用提示（echo，prompt）方法（如果没有authenticate方法）从用户返回字符串。（实验）
- server_public_key - SHA256 authenticnticaiton插件公钥值。（默认：无）
- db - 数据库的别名。（与MySQLdb兼容）
- passwd - 密码的别名。（与MySQLdb兼容）
- binary_prefix - 在字节和bytearray上添加_binary前缀。（默认值：False）
-
常用方法
select_db(db)设置db为当前数据库-
cursor（）创建一个游标对象,继而对数据进行操作游标对象方法:
execute（query，args = None ）执行sql语句executemany（query，args ）运行多个sql语句fetchall（）获取所有行fetchmany（size = None ）获取指定行数fetchone（）获取下一行close（）关闭游标,将缓存的数据全部释放到数据库中
commit（）提交对数据库中数据的修改,使之持久化到数据库中close（）关闭套接字连接退出
SQL语句
SQL 是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统
-
MySQL 数据类型
- Text 类型
image- Number 类型
image- Date 类型
image
SQL的语句分为两大类,一类是DML,另一类是DDL
数据定义语言 (DDL) 用于创建或删除表格
-
CREATE DATABASE 创建新数据库
- 语句:
CREATE DATABASE database_name
import pymysql con = pymysql.connect('localhost','root','123456') sql = '''CREATE DATABASE test ''' with con.cursor()as cursor: cursor.execute(sql) con.close() - 语句:
-
CREATE TABLE 创建新表
- 语句为以下形式,其中约束可以没有
CREATE TABLE 表名称 ( 列名称1 数据类型 约束, 列名称2 数据类型 约束, 列名称3 数据类型 约束, .... )-
约束条件
NOT NULL 值不能为空
-
UNIQUE 设置索引,一个表中可以有多个索引
- 在创建表时,末尾加上该语句
UNIQUE(列名称)- 表创建以后添加索引
ALTER TABLE 表名称 ADD UNIQUE (列名称)- 撤销索引
ALTER TABLE 表名称 DROP INDEX 列名称 -
PRIMARY KEY 设置主键,一个表中只能有一个主键
- 在创建表时,末尾加上该语句
PRIMARY KEY (列名称)- 表创建后添加主键
ALTER TABLE表名称 ADD PRIMARY KEY (列名称)- 撤销表中的主键
ALTER TABLE 表名称 DROP PRIMARY KEY -
FOREIGN KEY 一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY
- 在创建表时,末尾加上该语句
FOREIGN KEY (列名称) REFERENCES 表名称(列名称)- 表创建后添加FOREIGN KEY
ALTER TABLE 表名称1 ADD FOREIGN KEY (列名称) REFERENCES 表名称2(列名称)- 删除表中的PRIMARY KEY
ALTER TABLE 表名称 DROP FOREIGN KEY 约束名称 -
CHECK 限制列中的值的范围
- 在创建表时,末尾加上该语句
CHECK (列名称 范围限制)- 表创建后添加限制范围
ALTER TABLE 表名称 ADD CHECK (列名称 范围限制)- 撤销范围限制
ALTER TABLE 表名称 DROP CONSTRAINT 约束名称 -
DEFAULT 向列中插入默认值
- 在创建表时,向数据后添加默认值
列名称 数据类型 DEFALUT 默认值 表创建后添加默认值
ALTER TABLE 表名称 ALTER 列名称 SET DEFAULT 默认值- 删除默认值
ALTER TABLE 表名称 ALTER 列名称 DROP DEFAULT -
AUTO_INCREMENT 新记录插入表中时生成一个唯一的数字
- 在创建表时,在后面添加该字段即可
列名称 int NOT NULL AUTO_INCREMENT- 默认以1开始递增,但也可以通过赋值来改变初始值
ALTER TABLE 表名称 AUTO_INCREMENT=100- 创建新数据是不必向该数据进行赋值,但应加上NULL,代替该数据,会自动进行赋值
# 新建一个名为person的表,定义有id,name,age,address 其中id设置为主键,并采取自增形式
import pymysql
con = pymysql.connect('localhost','root','123456')
con.select_db('test')
sql = '''CREATE TABLE person
(
id INT(255) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255),
age INT(100),
address VARCHAR(255)
)
'''
with con.cursor()as cursor:
cursor.execute(sql)
con.close()
-
CREATE INDEX 创建表中的引索
用于加速搜索数据,用户无法看见,只需设置常用的列
使用以下语句
CREATE INDEX 引索名称 ON 表名称 (列名称) -
DROP 删除索引、表和数据库
- 删除表
DROP TABLE 表名称- 删除数据库
DROP DATABASE 数据库名称- 删除表中数据,但保留表
TRUNCATE TABLE 表名称 -
ALTER TABLE 在已有的表中添加、修改或删除列
- 向表中添加列
ALTER TABLE 表名称 ADD 列名称 列类型- 向表中删除列
ALTER TABLE 表名称 DROP COLUMN 列名称- 更改表中某一列的数据类型
ALTER TABLE 表名称 ALTER COLUMN 列名称 列类型
数据操作语言 (DML) 包含用于更新、插入和删除记录的语法
-
SELECT 选取数据
SELECT 列名称 FROM 表名称 -
SELECT DISTINCT 去掉重复数据
SELECT DISTINCT 列名称 FROM 表名称 -
WHERE 有条件的选取数据
SELECT 列名称 FROM 表名称 WHERE 列 运算符 值- 运算符
= 等于 <> 不等于 > 大于 < 小于 >= 大于等于 <= 小于等于-
操作符
- 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 AND 和 OR 对一个以上的条件对记录进行过滤
SELECT * FROM 表名称 WHERE 列名称 运算符 值 AND 列名称 运算符 值总结: 可以将AND OR WHERE 组合起来使用,用于查找精确的数据
-
LIKE 于在 WHERE 子句中搜索列中的指定模式,类似于正则表达式
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern- 通配符
image-
ORDER BY 语句用于对结果集进行排序
- 默认顺序排序,字母以ABC形式,数字从大到小
SELECT 列名称 FROM 表名称 ORDER BY 列名称- 添加DESC可变为逆序排序
SELECT 列名称 FROM 表名称 ORDER BY 列名称 DESC LIMIT 规定返回数据集合的最大值
"SELECT column_name(s) FROM table_name LIMIT number" -
INTO 从一个表中选取输出并复制到另一个表中,用于备份数据库
- 备份到另一个数据库中
SELECT * INTO new_table_name IN externaldatabase FROM old_tablename- 备份到另一个表中
"SELECT * INTO Persons_backup FROM Persons -
INSERT INTO 项数据库添加新行
- 向一行中添加数据
INSERT INTO 表名称 VALUES (值1, 值2,....) 添加所有值- 向一行中选取的列中添加数据
INSERT INTO 表名称 (列1, 列2,...) VALUES (值1, 值2,....) -
UPDATE 修改表中的数据
- 修改某列数据为某值的数据为新值
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值- 向某行中的多列修改数据
UPDATE 表名称 SET 列名称1 = 新值1,列名称2 = 新值2,…. WHERE 列名称 = 某值 -
DELETE 删除表中的行
- 删除列名称为某值的所有行
DELETE FROM 表名称 WHERE 列名称 = 值- 删除所有行,即清空所有数据
DELETE * FROM table_name
向数据库的person表中写入一行数据
import pymysql
con = pymysql.connect('localhost','root','123456')
con.select_db('test')
sql = ''' INSERT INTO person VALUES(NULL,'jack',18,'Beijing')
'''
with con.cursor()as cursor:
cursor.execute(sql)
con.commit()
con.close()
写入多行数据
import pymysql
con = pymysql.connect('localhost','root','123456')
con.select_db('test')
list = [(None,'jack',18,'beijing'),(None,'bob',20,'shanghai'),(None,'alice',10,'beijing'),(None,'luna',18,'shengzheng')]
with con.cursor()as cursor:
cursor.executemany('INSERT INTO person VALUES(%s,%s,%s,%s)',list)
con.commit()
con.close()
查找所有数据
import pymysql
con = pymysql.connect('localhost','root','123456')
con.select_db('test')
sql = 'SELECT * FROM person'
with con.cursor()as cursor:
cursor.execute(sql)
data = cursor.fetchall()
for i in data:
print(i)
con.close()
查找所有地址为beijing的数据
import pymysql
con = pymysql.connect('localhost','root','123456')
con.select_db('test')
sql = "SELECT * FROM person where address = 'beijing'"
with con.cursor()as cursor:
cursor.execute(sql)
data = cursor.fetchall()
for i in data:
print(i)
con.close()
查找所有age大于10的数据
import pymysql
con = pymysql.connect('localhost','root','123456')
con.select_db('test')
sql = "SELECT * FROM person where age > 10"
with con.cursor()as cursor:
cursor.execute(sql)
data = cursor.fetchall()
for i in data:
print(i)
con.close()
查找age大于10并且address等于bejing的数据
import pymysql
con = pymysql.connect('localhost','root','123456')
con.select_db('test')
sql = "SELECT * FROM person where age > 10 AND address = 'beijing'"
with con.cursor()as cursor:
cursor.execute(sql)
data = cursor.fetchall()
for i in data:
print(i)
con.close()
将所有数据按年龄大小进行逆序排序
import pymysql
con = pymysql.connect('localhost','root','123456')
con.select_db('test')
sql = "SELECT * FROM person ORDER BY age DESC"
with con.cursor()as cursor:
cursor.execute(sql)
data = cursor.fetchall()
for i in data:
print(i)
con.close()
修改表中jack的age为16
import pymysql
con = pymysql.connect('localhost','root','123456')
con.select_db('test')
sql = "UPDATE person set age = 16 WHERE name = 'jack'"
with con.cursor()as cursor:
cursor.execute(sql)
con.commit()
con.close()
删除数据库中jack的数据
import pymysql
con = pymysql.connect('localhost','root','123456')
con.select_db('test')
sql = "DELETE FROM person WHERE name = 'jack'"
with con.cursor()as cursor:
cursor.execute(sql)
con.commit()
con.close()
删除表中所有数据
import pymysql
con = pymysql.connect('localhost','root','123456')
con.select_db('test')
sql = "DELETE FROM person"
with con.cursor()as cursor:
cursor.execute(sql)
con.commit()
con.close()
向表中添加sex列,类型为varchar
import pymysql
con = pymysql.connect('localhost','root','123456')
con.select_db('test')
sql = '''ALTER TABLE person
ADD sex VARCHAR(255)
'''
with con.cursor()as cursor:
cursor.execute(sql)
con.commit()
con.close()
删除表
import pymysql
con = pymysql.connect('localhost','root','123456')
con.select_db('test')
sql = '''DROP TABLE person
'''
with con.cursor()as cursor:
cursor.execute(sql)
con.commit()
con.close()
删除数据库
import pymysql
con = pymysql.connect('localhost','root','123456')
sql = '''DROP DATABASE test
'''
with con.cursor()as cursor:
cursor.execute(sql)
con.commit()
con.close()
