一、SQL基础与DDL
1、定义
(1)SQL全称:Structured Query Language,结构化查询语言,用于访问和处理数据库的标准的计算机语言。
(2)SQL语法特征:
· SQL语言,大小写不敏感
· SQL可以单行或多行书写,最后以;结尾
· SQL支持注释:
·单行注释: -- 注释内容(--后面一定要有一个空格)
·单行注释: # 注释内容 (#后面可以不加空格)
·多行注释:/* 注释内容 */
2、库操作
查看数据库
SHOW DATABASES;
使用数据库
USE 数据库名称;
创建数据库
CREATE DATABASE 数据库名称 [CHARSET UTF8];
删除数据库
DROP DATABASE 数据库名称;
查看当前使用的数据库
SELECT DATABASE();
3、表操作
查看有哪些表
SHOW TABLES; 注意:需要先选择数据库
删除表
DROP TABLE 表名称;
DROP TABLE IF EXISTS 表名称;
创建表
CREATE TABLE 表名称(
列名称 列类型,
列名称 列类型,
......
);
---列类型有
int --整数
float --浮点数
varchar(长度) --文本,长度为数字,做最大长度限制
date --日期类型
timestamp --时间戳类型
二、SQL-DML
1、定义
DML是指数据库操作语言,英文全称是Data Manipulation Language,用来对数据库中表的数据记录进行更新。
关键字:
· 插入INSERT
· 删除DELETE
· 更新UPDATE
2、数据插入INSERT
基础语法:
INSERT INTO 表 [(列1,列2,......,列N)] VALUES(值1,值2,......,值N)[,(值1,值2,......,值N)]
CREATE DATABASE table2; USE table2; CREATE TABLE student ( id INT, NAME VARCHAR ( 10 ), age INT ); INSERT INTO student(id) VALUES(1),(2),(3); INSERT INTO student(id,name,age) VALUES(4,'张三',31),(5,'李四',45); INSERT INTO student VALUES(6,'王五',31); /*当所有列都提供值,可以不用在代码中标出列*/
3、数据删除DELETE
基础语法:
DELETE FROM 表名称 [WHERE 条件判断]
条件判断:列 操作符 值
操作符:= ,<, >, <= ,>=,!=等等
DELETE FROM student WHERE id=1; /* 删除ID号为1的数据 */
4、数据更新UPDATE
基础语法:UPDATE 表名 SET 列=值 [WHERE 条件判断];
DELETE FROM student WHERE id=1; /* 删除ID号为1的数据 */
5、注意事项
字符串的值,出现在SQL语句中,必须要用单引号包围起来。
三、SQL-DQL
1、基础数据查询
基础语法:
SELECT 字段列表 | * FROM 表含义是:从(FROM)表中,选择(SELECT)某些列进行展示。
SELECT * FROM student; /* 查看student表内全部列 */ SELECT id,Name from student; /* 查看表内id和NAME列 */
2、基础数据查询 -过滤
查询也可以带有指定条件,语法如下:
SELECT 字段列表 | * FROM 表 WHERE 条件判断
# 查询id和name 两个列,年龄小于33岁 SELECT id,name FROM student WHERE age < 33; # 查询全部列,id等于100003 SELECT id,name FROM student WHERE id = 100003;
3、分组聚合
基础语法:
SELECT 字段 | 聚合函数 FROM 表 [WHERE 条件] GROUP BY 列
聚合函数有:
- SUM(列)求和
- AVG(列)求平均值
- MIN(列)求最小值
- MAX(列)求最大值
- COUNT(列|*)求数量
# 按照性别分组,并求平均年龄 SELECT gender,AVG(age) FROM student GROUP BY gender;
注意:非聚合函数中,GROUP BY后出现哪个列,哪个列才能出现在SELECT的非聚合中。
4、排序和分页
结果排序
可以对查询的结果,使用GROUP BY关键字,指定某个列进行排序,语法:
SELECT 列 | 聚合函数 | * FROM 表
WHERE ...
GROUP BY ...
ORDER BY ... [ASC | DESC]
关键字:ASC表示从小到大,DESC表示从大到小。默认为ASC
# 筛选出年纪大于20岁的学生,并按照年龄降序排序 SELECT * FROM student WHERE age > 20 ORDER BY age DESC;
结果分页限制
可以使用LIMIT关键字,对查询结果进行数量限制或分页显示,语法:
SELECT 列 | 聚合函数 | * FROM 表
WHERE ...
GROUP BY ...
ORDER BY ...[ASC | DESC]
LIMIT n[,m]
# 限制输出只有5条 SELECT * FROM student LIMIT 5; # 限制输出从第11条(跳过前10条)开始,向后取5条 SELECT * FROM student LIMIT 10,5;
四、总结
· WHERE , GROUP BY , ORDER BY , LIMIT均可按需求省略
· SELECT 和 FROM 是必写的
· 执行顺序:
FORM -> WHERE -> GROUP BY 和 聚合函数 -> SELECT -> ORDER BY ->LIMIT
五、python & SQL
在Python中,使用第三方库:pymysql来完成对MySQL数据库的操作。
1、创建到MySQL的数据库链接
from pymysql import Connection # 获取到MySQL数据库的链接对象 conn = Connection(host='localhost', # 主机名(或IP地址) port=3306, # 端口,默认3306 user='root', # 账号名 password='abc123', # 密码(自己安装SQL数据库时设置的密码) ) # 打印MySQL数据库软件信息 print(conn.get_server_info()) # 输出MySQL版本 # 关闭到数据库的链接 conn.close()
2、执行SQL语句
通过链接对象调用cursor()方法,得到游标对象
· 游标对象.execute()执行SQL语句
· 游标对象.fetchall()可获得查询结果并将结果分装到元组内
非查询性质的SQL语句
from pymysql import Connection # 获取到MySQL数据库的链接对象 conn = Connection(host='localhost', # 主机名(或IP地址) port=3306, # 端口,默认3306 user='root', # 账号名 password='abc123', # 密码(自己安装SQL数据库时设置的密码) ) # 执行非查询性质SQL cursor = conn.cursor() # 获取游标对象 # 创建数据库 database_name = 'test' # 设置新数据库名称 sql = f"CREATE DATABASE {database_name}" cursor.execute(sql) # 选择数据库 conn.select_db('test') # 执行sql cursor.execute('create table if not exists test_table(id int, name varchar(20))') # 关闭到数据库的链接 conn.close()
查询性质的SQL语句
from pymysql import Connection # 获取到MySQL数据库的链接对象 conn = Connection(host='localhost', # 主机名(或IP地址) port=3306, # 端口,默认3306 user='root', # 账号名 password='abc123', # 密码(自己安装SQL数据库时设置的密码) ) # 执行非查询性质SQL cursor = conn.cursor() # 获取游标对象 # 选择数据库(前提数据库要存在) conn.select_db('table2') # 执行sql cursor.execute("select * from student") # (前提表要存在) # 获取查询结果 results = cursor.fetchall() for row in results: print(row) # 关闭到数据库的链接 conn.close()
运行结果中的result是元组类型。
3、数据插入
pymysql在执行数据插入或其他产生数据更改的SQL语句时,默认是需要提交更改的。
即,需要通过代码“确认”这种更改行为。(手动确认)
通过链接对象.commit()即可确认此行为。
from pymysql import Connection # 获取到MySQL数据库的链接对象 conn = Connection(host='localhost', # 主机名(或IP地址) port=3306, # 端口,默认3306 user='root', # 账号名 password='abc123', # 密码(自己安装SQL数据库时设置的密码) ) # 执行非查询性质SQL cursor = conn.cursor() # 获取游标对象 # 选择数据库(前提数据库要存在) conn.select_db('table2') # 执行sql cursor.execute("insert into student values(1,'张三',20)") #提交事务 conn.commit() # 关闭到数据库的链接 conn.close()
自动commit确认
from pymysql import Connection # 获取到MySQL数据库的链接对象 conn = Connection(host='localhost', # 主机名(或IP地址) port=3306, # 端口,默认3306 user='root', # 账号名 password='abc123', # 密码(自己安装SQL数据库时设置的密码) autocommit=True # 设置自动提交 )