数据库之SQL语言
表的结构
- 表的列称为字段
- 表的行称为记录
SQL书写规则
- SQL语句以分号 ; 结束(英文形式)
- SQL语句不区分大小写
- 常数的书写方式
- 字符串的日期常数需要单引号 ’ 括起来
- 数字常数无需单引号，直接写数字即可
- 单词之间需要使用半角空格或换行符进行分隔
SQL语言及种类
1.DDL ：数据定义语言
- create 创建数据库和表对象
- drop 删除数据库和表等对象
- alter 修改数据库和表等对象的结构
2.DML：数据操纵语言
- select 查询表中的数据
- insert 向表中插入新数据
- update 更新表中的数据
- delete 删除表中的数据
3.DCL：数据控制语言
- commit 确认对数据库中的数据进行的变更
- rollback 取消对数据库中的数据进行的变更
- grant 赋予用户操作权限
- revoke 取消用户的操作权限
数据库的基本使用
-
创建数据库
create database 数据库名; -
显示数据库的时间
-
select now(); -
显示所有的数据库
show databases; -
查看创建数据库的sql
show create database 数据库名; -
进入数据库
use 数据库名; -
查看当前数据库
select database(); -
删除数据库 慎用!
drop database 数据库名; -
查看数据库中的表
--前提:进入到要查的数据库中 show tables
表的创建删除修改
-
创建表
create table 表名( 列名1 数据类型 列的约束, 列名2 数据类型 列的约束, ... 表的约束1,表的约束2,... )--例子1,创建学生表Student,学号是主码,姓名取值唯一 create table Student (Sno CHAR(9) PRIMARAY KEY not null auto_increment, --列级完整性约束条件,主键,不为空,自动增长 Sname CHAR(20) UNIQUE, --Sname取唯一值 Ssex ENUM("男","女","保密") default "保密", --枚举 Sage SMALLINT unsigned, --无符号 Sdept CHAR(20) ); --建立一个课程表Course create table Course (Cno CHAR(4) PRIMARY KEY， Cname CHAR(40)， Cpno CHAR(4) ， Ccredit SMALLINT， FOREIGN KEY (Cpno) REFERENCES Course(Cno) --Cpno是外码,被参照表是Course,被参照列是Cno ); --建立一个学生选课表SC create table SC (Sno CHAR(9)， Cno CHAR(4)， Grade SMALLINT， PRIMARY KEY (Sno，Cno)， /* 主码由两个属性构成，必须作为表级完整性进行定义*/ FOREIGN KEY (Sno) REFERENCES Student(Sno)， /* 表级完整性约束条件，Sno是外码，被参照表是Student */ FOREIGN KEY (Cno) REFERENCES Course(Cno) /* 表级完整性约束条件， Cno是外码，被参照表是Course*/ ); --例子2,创建学生表students create table students( id int unsigned primary key not null auto_increment, name varchar(20), age int unsigned, high decimal(5,2), gender enum("男","女","保密","中性") default "保密", cls_id int ); -
删除表
drop table 表名;常规情况下表和列的删除无法恢复，请在操作前确认
-
修改字段 在一个已存在的表中添加一个新的字段
alter table Student add birthday datetime; -
修改字段 修改已存在的字段的类型
alter table Student modify birthday date not null; -
修改字段 修改已存在的字段的名称和类型
alter table Student change birthday birth datetime not null; -
修改字段 删除已存在字段
alter table Student drop birth;常规情况下表和列的删除无法恢复，请在操作前确认
-
变更表名
- Oracle & PostgreSQL
ALTER TABLE 旧表名 RENAME TO 新表名; - SQL Server
sp_rename '旧表名','新表名'; - MySQL
RENAME TABLE 旧表名 to 新表名;
- Oracle & PostgreSQL
数据类型
常见约束
- 主键约束 primary key
- 非空约束 not null
- 惟一约束 unique
- 默认约束 default
- 外键约束 foreign key
表数据的增删改查crud
- 插入数据
--insert into 表名 values(...)
insert into students values(0,"张三",18,168.9,"女","1");
insert into students (name,age,high) values ("黄蓉",19,168.88);
- 删除数据
--delete from 表名 where 条件
delete from students where id = 6;
-- 在真实使用数据库的过程中一般不使用delete
-- 物理删除
delete from students where id =6;
-- 逻辑删除
update students set isdelete = 1 where id =4;
- 修改数据
--update 表名 set 列1 = 值1,列2 = 值2...where 条件
update students set cls_id = 1 where id =1;
- 查找数据
--查询所有学生数据
select * from students;
--as别名
select id as 序号,name as 姓名 from students;
--distinct去重
select name,distinct gender from students;
--比较运算符>,<,=,!=,<=,>=
select * from students where age >= 25;
--like关键字 %任意字符一个或多个,_任意一个
--1.查询姓名中包括"云"字的学生信息
select * from students where name like "%云%";
--2.查询姓名中第二个字是"云"字的学生信息
select * from students where name like "_云%";
-- 范围查询
-- 1.查询 id 为 3 6 9
select * from students where id in (3,6,9);
-- 2.查询id 为3-9 闭区间 between..and / not between...and
select * from students where id between 3 and 9;
--排序order by 升序asc-默认 降序-desc
-- 字段排序 设置多个字段 首先按照第一个字段来进行排序,如果相同就按照第二个 如果没有设置第二一般是按照id
-- 1.按照id 降序进行排序
select * from students order by id desc;
-- 2.查询20-35岁之间的人 按照年龄大小进行排序 升序
select * from students where age between 20 and 35 order by age;
-- 3.查询20-35岁之间性别为女的人 按照年龄大小排序 降序 如果年龄相同 按照身高 升序排列
select * from students where (age between 20 and 35) and (gender = "女") order by age desc,high asc;
-- 分页查询limit start 从哪个开始查 默认 0, count 一页有个多少个数据
-- 1.查询学生 分页查询 一页五条数据 查询第一页
-- 拿出来的数据是 0 1 2 3 4
select * from students limit 0,5;
-- 2.查询学生 分页查询 一页五条数据 查询第二页
-- 第二页拿出来的数据 5 6 7 8 9
select * from students limit 5,5;
--3.查询数据 分页查询 一页显示 n条数据 查询第m页 m-1
-- 第一 0 第二 0+n 第三 0+n+n 第四 0 + n + n+ n
select * from students limit n*(m-1),n;
--优先级 条件查询> 排序 > 分页
-- 1.取5个 按照年龄从大到小排列
select * from students order by age desc limit 0,5;
-- 2.取一页5个 年龄20-25 按照年龄从打到小排列
select * from students where age between 20 and 25 order by age desc limit 0,5;
-- 聚合函数
-- 1.统计个数count(col): 表示求指定列的总行数
-- 统计有多少个学生
select count(*) from students;
-- 2.最大max(col): 表示求指定列的最大值
-- 查看身高最高的学生
select max(high) from students;
-- 3.最小min(col): 表示求指定列的最小值
--查看身高最矮的学生
select min(high) from students;
-- 4.和sum(col): 表示求指定列的和
-- 查看所有学生的身高的总和
select sum(high) from students;
-- 5.平均值avg(col): 表示求指定列的平均值
-- 查看学生的平均身高
select avg(high) from students;
select sum(high)/count(*) from students;
-- 保留两位小数 四舍五入 round(结果,需要保留的小数)
select round(avg(age),2) from students;
select round(sum(high)/count(*),2) from students;
-- 分组查询 group by
--group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割
-- 1.把学生按照性别分组
select distinct gender from students;
select gender from students group by gender;
-- 2.统计每种性别的人数是多少个
select count(*),gender from students group by gender;
-- 3.查看每个分组的每个人的身高
select gender,group_concat(high) from students group by gender;
--分组条件查询,不使用where使用having
-- 4.查询每种性别 超过20岁的平均年龄
select gender from students group by gender having avg(age)>20;
-- 5.查看那个性别的人数大于3 人 把姓名列举出来
select gender,group_concat(name) from students group by gender having count(*)>3
-- 连接查询
-- 内连接 取两个表的交集
select * from students inner join classes on students.cls_id = classes.id;
select s.id,s.name,s.age,s.high,s.gender,s.cls_id,s.isdelete,c.name from students as s inner join classes as c on s.cls_id = c.id;
-- 左连接
select s.id,s.name,s.age,s.high,s.gender,s.cls_id,s.isdelete,c.name from classes as c left join students as s on s.cls_id = c.id order by s.cls_id,s.id asc;
--右连接
select * from students as s right join classes as c on s.cls_id = c.id;
-- 自连接
select * from areas as c inner join areas as p on c.pid = p.id where p.title = '陕西省';
--子查询
-- 1.查询年龄大于平均年龄的学生信息
select * from students where age > (select avg(age) from students)
-- 2.查询年龄最大,身高最高的学生
select * from students where (age, height) = (select max(age), max(height) from students);
事务
事务就是用户定义的一系列执行SQL语句的操作, 这些操作要么完全地执行，要么完全地都不执行， 它是一个不可分割的工作执行单元。
事物的四大特性
- 原子性
- 一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚.强调事务中的多个操作是一个整体
- 一致性
- 数据库总是从一个一致性的状态转换到另一个一致性的状态.强调数据库中不会保存不一致状态
- 隔离性
- 一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的.强调数据库中事务之间相互不可见
- 持久性
- 一旦事务提交，则其所做的修改会永久保存到数据库.强调数据库能永久保存数据，一旦提交就不可撤销
事物的使用
在使用事务之前，先要确保表的存储引擎是 InnoDB 类型, 只有这个类型才可以使用事务，MySQL数据库中表的存储引擎默认是 InnoDB 类型。
-- 查看MySQL数据库支持的表的存储引擎
show engines;
-- 查看goods表的存储引擎
show create table goods;
--修改表的存储引擎
alter table goods engine = "InnoDB";
--开启事务
begin;
--或者
start transaction;
--mysql取消自动提交事务模式,需要手动提交
set autocommit = 0;
--提交事务
commit;
--回滚事务
rollback;
--pymysql中
conn.commit() --提交事务
conn.rollback() --回滚事务
注意:
- 开启事务后执行修改命令，变更数据会保存到MySQL服务端的缓存文件中，而不维护到物理表中
- MySQL数据库默认采用自动提交(autocommit)模式，如果没有显示的开启一个事务,那么每条sql语句都会被当作一个事务执行提交的操作
- 当设置autocommit=0就是取消了自动提交事务模式，直到显示的执行commit和rollback表示该事务结束。
- set autocommit = 0 表示取消自动提交事务模式，需要手动执行commit完成事务的提交
PyMySQL的使用
python程序操作mysql数据库
在安装路径python\Scripts下使用语句
--安装pymysql
pip install pymysql
pymysql的使用
#1.导入pymysql包
import pymysql
#2.创建连接对象
conn = pymysql.connect(host = "localhost",port = 3306,user = "root",password = "root",database = "test",charset = "utf8")
#3.获取游标对象
cursor = conn.cursor()
try:
#4.查询sql语句
sql = "select * from students;"
#5.执行sql语句 返回值就是sql语句在执行过程中影响的行数
row_count = cursor.execute(sql);
# 6.取结果
# 6.1取出一行数据
print(cursor.fetchone())
# 6.2取出所有数据
for line in cursor.fetchall():
print(line)
# 6.3取出前5行的数据
print(cursor.fetchmany(5))
# 7.提交数据到数据库
conn.commit()
except Exception as e:
# 8.回滚数据,即撤销刚刚的sql语句操作
conn.rollback()
# 9.关闭游标,关闭连接
cursor.close();
conn.close();
防止sql注入:
sql注入 : 用户提交带有恶意的数据与sql语句进行字符串方式的拼接,从而影响了sql语句的语义,最终产生数据泄露的现象
如何防止sql注入 :
- sql语言中的参数使用%s来占位,而不是用字符串格式化操作
- 将sql语句中%s占位所需要的参数存在一个列表中,把参数列表传递给execute方法中第二个参数
#防止登录sql注入
def login(username,pwd):
sql = f'select * from users where username = "{username}" and pwd = %s'
row_count = cursor.execute(sql,pwd)
if row_count > 0:
print("登录成功")
else:
print("登陆失败")
6420
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
