学习 python 少不了和数据库打交道, 常见的数据库有: MySQL,SQLite,MongoDB,Redis 等, 这里主要介绍一下 MySQL 数据库的基本操作.
数据库听起来感觉好高大上, 当你接触之后有种恍然大悟的感觉, 不就是把我们的数据存到一个表格中吗. 可以简单的这么理解, 大家都知道表格都是由表名, 表头, 数据等几部分组成的, 数据库跟这类似, 只不过叫法不一样, 这里叫数据库名, 表名和字段. 数据库就简单介绍这么多, 说的不一定完全正确, 下面就说一下怎么操作数据库.
一, MySQL 字段类型
常用的有: 数值 (int, float), 字符串 (varchar, text), 日期 (date, datetime)
二, MySQL 约束
主键: primary key
外键: foreign key
默认值: default
唯一: unique
非空: not null
三, 物理删除和逻辑删除
在使用数据库的时候, 一定会遇到删除数据库中的内容, 这个时候一定要小心操作, 不然就会被老板直接开除, 网络上不是有个很火的命令操作叫 "程序员如何执行一条命令让老板开除", 所以操作数据库时一定要小心谨慎.
说一下什么是物理删除和逻辑删除, 物理删除就是把一条数据从数据库中永远删除, 就是再也找不到也不能恢复; 逻辑删除就是给你设计的表再增加一列 (比如: isDelete, 设默认值为 0), 当你想删除数据时将这个字段的值设为 1, 当你再操作数据时把 isDelete 值为 1 的筛选掉就可以了, 这样数据也不会丢失. 对于重要的数据一定要设置这个字段, 对于不重要的数据自己想怎么操作就怎么操作, 大不了全部删除再重新建数据库.
四, 数据库基本操作 (增删改查)
1, 创建数据库
create database [if not exists] db_name [character set utf8];
注意:[if not exists]: 最好加上, 这样可以避免麻烦, 每操作一次数据库就多一次风险
[character set utf8] 就是设置编码格式, 也可以不指定
2, 查看数据
show databases;
3, 查看数据库创建方式
show create database db_name;
4, 修改数据库
alter database db_name [character set xxx];
5, 删除数据库
drop database [if exists] db_name;
注意: [if exists]: 如果你不确定数据库是否存在最好加上, 存在就删除, 不存在也不会报错
6, 使用数据库
use db_name;
7, 查看当前使用数据库
select database();
五, 数据库管理
1, 通过 grant 命令, 给 test 数据库, 添加用户 ***, 密码 123456
grant select,insert,update,delete,create,drop on test to '***'@'localhost' identified by '123456';
2, 修改用户密码
grant select,insert,update,delete,create,drop on test to '***'@'localhost' identified by 'asdfasdf';
3, 显示用户
select user from mysql.user;
4, 删除用户
drop user '***'@'localhost';
六, 数据表的基本操作 (增删改查)
1, 查看数据表
show tables;
2, 创建数据表
- create table student(
- id int primary key auto_increment,
- name varchar(20) not null,
- age int not null,
- gender tinyint(1) default 1,
- birthday date,
- hobby varchar(20)
- );
3, 查看数据表结构
desc student;
4, 查看创建数据表语句
show create table student;
5, 增加数据 (列, 字段)
alter table student add address varchar(30);
6, 增加多个数据
- alter table student add address varchar(30),
- add age int not null,
- add height int not null;
7, 修改一列列名
alter table student change address addr varchar(20);
8, 修改一列类型
alter table student modify age tinyint default 20;
9, 删除一列
alter table student drop height;
10, 修改表名
rename table student to stu;
11, 修改表所用的字符集
alter table student character set utf8;
12, 删除表
- drop table student;
- create table users(id int not null,
- name varchar(10),
- age int,
- height int
- );
13, 添加主键
alter table users add primary key (id);
14, 删除主键
alter table users drop primary key;
15, 添加唯一索引
alter table users add unique (name);
16, 添加唯一索引设置索引名
alter table users add unique key user_name(name);
17, 添加联合索引
alter table users add unique index name_age(name,age);
18, 删除索引
- alter table users drop index name;
- create table student( id int primary key auto_increment,
- name varchar(20),
- birthday varchar(20),
- age int
- );
19, 插入一条数据
insert into student(name,birthday,age) values('学生 1','2001-1-1',11);
20, 插入多条数据
- insert into student(name,birthday,age) values('学生 2','2001-1-2',11),
- ('学生 3','2001-1-3',11),
- ('学生 4','2001-1-4',11);
21, 修改数据
update student set birthday='2001-1-10' where id=1;
22, 数据表中的数据可以直接运算
update student set age = age + 5;
23, 删除数据
delete from student where id = 1;
注意: 删除数据时一定要加条件限制, 不然整张数据表都被删除了
24, 删除表中的所有数据
delete from student;
七, 查询数据
- create table grade( id int primary key auto_increment,
- name varchar(20),
- js double,
- java double,
- python double );
- insert into grade (name,js,java,python) values ('Tom',68,89,87),('Jim',70,91,92),('Jake',71,73,74),('Mike',80,84,85),('Jame',85,88,83);
1,select 查询
查询所有数据: select * from grade;
查询姓名和 js 成绩: select name,js from grade;
过滤表中重复数据: select distinct js from grade;
给所有 js 成绩 + 5, 并使用 as 别名: select name,js+5as 'js 成绩' from grade;
2,where 过滤查询
查询姓名 XX 的学生信息: select * from grade where name='Tom';
查询总分大于 250 分的所有学生: select name,js+java+python as '总成绩' from grade where js+java+python>250;
查询 js 分数和 java 分数大于 90 的学生: select * from grade where js>90 and java>90;
查询 java 分数 80~90 的学生: select * from grade where java between 80 and 90;
查询 java 分数在 80 或 90 的学生: select * from grade where java in (80,90);
/* 模糊查询,% 表示多个字符,_表示一个字符 */
查询姓名 j 开头的学生: select * from grade where name like 'j%';
八, order by 排序
/* asc: 升序, 默认值 desc: 降序 */
根据 js 分数升序排序: select * from grade order by js;
更加总分数从高到低排序: select name,(ifnull(js,0)+ifnull(java,0)+ifnull(python,0)) as '总分数' from grade order by '总分数' desc;
九, group by 分组查询
- create table product_tab( id int primary key auto_increment,
- product_name varchar(20),
- price float(6,2),
- product_date date,
- class varchar(20) );
- insert into product_tab (product_name,price,product_date,class) values ('苹果',10,'20180812','水果'),
- ('香蕉',20,'20180826','水果'),
- ("水壶",120,'20170612',"电器"),
- ("被罩",70,'20170612',"家具"),
- ("音响",420,'20171012',"电器"),
- ("电视",2000,'20170912',"电器"),
- ("床单",55,'20171112',"家具"),
- ("草莓",34,'20170512',"水果");
按位置分组: select * from product_tab group by 5;
按产品类别分类并显示平均价格: select class,avg(price) from product_tab group by class;
按产品分类显示每一种商品价格总和超过 200 的商品: select class,sum(price) from product_tab group by class having sum(price)>200;
分组显示所有产品: group_concat select id,group_concat(product_name) from product_tab group by class;
十, where 和 having 之间的区别
首先, 明确一点就是能使用 where 的地方都能使用 having;where 只能用于分组之前数据的筛选; having 只能用于分组之后数据的筛选,
而且 having 中可以使用聚合函数.
十一, 聚合函数
COUNT(列名): 统计行的个数
统计学生个数: select count(id) from grade;
SUM(列名): 统计总量
统计所有 js 总成绩: select sum(js) as 'js 总成绩' from grade;
AVG(列名): 平均数
统计所有 js 平均分: select avg(js) as 'js 平均分' from grade;
- MAX,MIN (最高, 最低)
- select max(js) as 'js 最高分' from grade;
- select min(js) as 'js 最低分' from grade;
十二, SQL 语句执行顺序
from----where----select----group by----having----order by
十三, limit 和正则表达式
limit
查询前 3 条数据: select * from grade limit 3;
跳过 1 条, 查询 3 条数据: select * from grade limit 1,3;
正则表达式
查询 j 开头的学生: select * from grade where name regexp '^j';
查询名字中 m 出现 2 次的学生: select * from grade where name regexp 'm{2}';
十四, 多表操作
1, 外键约束
/* 一个班级对应多个学生, 一个学生只能对应一个班级 */
主表: 创建班级表 create table class( id int primary key auto_increment, name varchar(20), stu_nums int );
子表: 创建学生表 create table student( id int primary key auto_increment, name varchar(20), class_id int, foreign key(class_id) references class(id) );
注意: 作为外键一定要和关联主键的数据类型保持一致
插入数据: insert into class (name,stu_nums) values ('班级一',10),('班级二',12),('班级三',13),('班级四',14),('班级五',15);
insert into student (name,class_id) values ('Tom',1),('Jim',1),('Jake',2),('Mike',3),('Jane',4);
增加外键: alter table student add constraint student_fk_class foreign key(class_id) references class(id);
删除外键: alter table student drop foreign key student_ibfk_1;
2,INNODB 支持的 on 语句
外键约束对子表的含义: 如果在父表中找不到候选键, 则不允许在子表进行 insert/update
外键约束对父表的含义: 在父表上进行 update/delete 以更新或删除在子表中的一条或多条对应数据,
父表的行为取决于: 在定义子表的外键指定的 on update/on delete 语句
ON DELETE CASCADE 级联删除: 父表记录被删除, 子表对应的记录自动被删除
foreign key(class_id) references class(id) on delete cascade;
ON DELETE SET NULL 置空: 父表 update/delete 记录时, 子表设为 null
foreign key(class_id) references class(id) on set null;
RESTRICT: 拒绝对父表进行删除操作
NO ACTION: 在 mysql 中同 RESTRICT, 如果子表中有匹配的记录, 则不允许对父表对应候选键
3, 多表查询
- /* 创建 2 张表: 员工表和部门表 并插入相关数据 */
- create table employee( emp_id int primary key auto_increment, emp_name varchar(20), age int, dept_id int );
- create table department( dept_id int primary key auto_increment, dept_name varchar(100) );
- insert into employee(emp_name,age,dept_id) values ('A',19,200), ('B',26,201), ('C',30,201), ('D',24,202), ('E',20,200), ('F',38,204);
- insert into department values (200,'人事部'), (201,'技术部'), (202,'销售部'), (203,'财政部');
笛卡尔积查询: 查询结果是 m*n
select * from employee,department;
内连接: 查询两张表中都有的关联数据
select * from employee,department where employee.dept_id=department.dept_ id;
外连接:
左外连接: 在内连接的基础上增加左边有右边没有的结果
select * from employee left join department on employee.dept_id=departmen t.dept_id;
右外连接: 在内连接的基础上增加右边有左边没有的结果
select * from employee right join department on employee.dept_id=departme nt.dept_id;
4, 多条件查询
查询员工大于 25 岁的信息:
- select employee.emp_name,department.dept_name
- -> from employee,department
- -> where employee.dept_id=department.dept_id and age>25;
以内连接方式查询 employee 和 department 表, 并以 age 字段升序显示:
- select employee.emp_id,employee.emp_name,employee.age,department.dept_name
- -> from employee,department
- -> where employee.dept_id=department.dept_id
- -> order by age asc;
子查询: 查询 employee 表, dept_id 在 department 表中的所有信息
select * from employee where dept_id in (select dept_id from department);
子查询:
select * from employee where dept_id in (select dept_id from department where age>25);
使用 EXISTS 关键字
EXISTS 关字键字表示存在. 在使用 EXISTS 关键字时, 内层查询语句不返回查询的记录.
而是返回一个真假值. Ture 或 False
当返回 Ture 时, 外层查询语句将进行查询; 当返回值为 False 时, 外层查询语句不进行查询
- select * from employee whereEXISTS (SELECT dept_name from department where dept_id=205);
- select * from employee whereEXISTS (SELECT dept_name from department where dept_id=203);
来源: https://www.cnblogs.com/ccmldl/p/9550351.html