小白学Python---Mysql数据库操作

MySQL简介

MySQL是最流行的的关系型数据库管理系统，在web应用方面MySQL是最好的RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。
数据库是按照数据结构来组织、存储和管理数据的仓库，每个数据库都有一个或多个不同的API用于创建，访问，管理，搜索和复制所保存的数据。我们当然也可以将数据储存在文件中，但是在文件中读写数据的速度相对较慢，因此，我们使用关系型数据库管理系统(RDBMS)来存储和管理大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。
关系数据库管理系统的特点：

• 数据以表格的形式出现
• 每行为各种记录名称
• 每列为记录名称所对应的数据域
• 许多的行和列组成一张表单
• 若干的表单组成database

MySQL事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读
  未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

本文在Navicat for Mysql软件中进行代码编写，主要通过一个实例来了解增删改查的基本操作。

-- SQL：Structured Query Language结构化查询语言
-- DDL：create / drop / alter 数据定义语言Data Definition Language
-- DML：insert / delete / update数据操纵语言Data Manipulation Language
-- DCL：grant / revoke 数据库控制语言Data Control Language


-- 创建数据库scc，如果之前存在就先删除再创建
drop database if exists scc;
create database scc default charset utf8;
use scc;

-- 创建tbstudent表，添加多个字段，定义每个字段的数据类型，是否能空，定义主键key
drop table if exists tbstudent;
create table tbstudent
(
stuid int not null,
stuname varchar(20) not null,
stusex bit default 1,
stubirth datetime not null,
stutel char(11),
stuaddr varchar(255),
stuphoto longblob,
primary key(stuid)
);

-- 修改tbstudent表，删除stutel这一列，并展示表信息
alter table tbstudent drop column studtel;
desc tbstudent;

drop table if exists tbcourse;
create table tbcourse
(
cosid int not null,
cosname varchar(50) not null,
coscredit tinyint not null,
cosintro varchar(50),
primary key(cosid)
);

-- alter table tbcourse add constraint pk_course primary key(cosid);
-- 2种定义主键的方式

drop table if exists tbsc;
create table tbsc
(
scid int auto_increment,
sid int not null,
cid int,
scdate date,
score float,
primary key(scid),
-- foreign key(sid) references tbstudent (stuid) 也可以这样定义外键
);

-- 定义外键，on delete (restrict)删除时增加限制条件cascade 或者set null 相当于 主表的东西删除之后，从表的关联东西也发生相应变化
alter table tbsc add constraint fk_sid foregin key (sid) references tbstudent(stuid) on delete cascade on update cascade;
alter table tbsc add constraint fk_cid foregin key (cid) references tbcourse(cosid) on delete set null on update cascade;

insert into tbstudent valuesinsert into tbstudent values (1001,'张三丰', default, '1978-1-1','成都市一环路西二段17号',null);
insert into tbstudent (stuid, stuname, stubirth) values (1002,'郭靖','1980-2-2');
insert into tbstudent (stuid, stuname, stusex, stubirth, stuaddr) values (1003,'黄蓉', 0, '1982-3-3','成都市三环路南四段123号');
insert into tbstudent values (1004,'张无忌',1,'1990-4-4',null,null);
insert into tbstudent values
(1005,'丘处机',1,'1983-5-5','北京市海淀区宝盛北里西区28号',null),
(1006,'王处一',1,'1985-6-6','深圳市宝安区宝安大道5010号',null),
(1007,'刘处玄',1,'1987-7-7','郑州市金水区纬五路21号',null),
(1008,'孙不二',0,'1989-8-8','武汉市光谷大道61号',null),
(1009,'平一指',1,'1992-9-9','西安市雁塔区高新六路52号',null),
(1010,'老不死',1,'1993-10-10','广州市天河区元岗路310号',null),
(1011,'王大锤',0,'1994-11-11',null,null),
(1012,'隔壁老王',1,'1995-12-12',null,null),
(1013,'郭啸天',1,'1977-10-25',null,null);

-- 按条件删除某条数据
delete from tbstudent where stuid=1004;
-- 按条件更新某条数据
update tbstudent set stubirth='1980-12-12',stuaddr='上海市宝山区同济支路199号' where stuid=1002;

insert into tbcourse values
(1111,'C语言程序设计',3,'大神级讲师授课需要抢座'),
(2222,'Java程序设计',3,null),
(3333,'数据库概论',2,null),
(4444,'操作系统原理',4,null);

-- 添加学生选课记录
insert into tbsc values
(default,1001,1111,'2016-9-1',95),
(default,1002,1111,'2016-9-1',94),
(default,1001,2222,now(),null),
(default,1001,3333,'2017-3-1',85),
(default,1001,4444,now(),null),
(default,1002,4444,now(),null),
(default,1003,2222,now(),null),
(default,1003,3333,now(),null),
(default,1005,2222,now(),null),
(default,1006,1111,now(),null),
(default,1006,2222,'2017-3-1',80),
(default,1006,3333,now(),null),
(default,1006,4444,now(),null),
(default,1007,1111,'2016-9-1',null),
(default,1007,3333,now(),null),
(default,1007,4444,now(),null),
(default,1008,2222,now(),null),
(default,1010,1111,now(),null);

-- 1、查询所有学生信息
select * from tbstudent s join (select * from tbsc s join tbcourse c on s.cid=c.cosid) x on x.sid=s.stuid;

-- 2、查询所有课程名称及学分
select cosname as '课程学分', coscredit as '学分' from tbcourse;

-- 3查询所有女生的姓名和出生日期
select stuname as '姓名', stubirth as '出生日期' from tbstudent where stusex=0;

-- 4查询所有80后学生的姓名、性别和出生日期
select stuname as '姓名', stusex as '性别', stubirth as '出生日期' from tbstudent t where t.stubirth > '1980-1-1' and t.stubirth < '1990-1-1' -- between '1980-1-1' and '1990-1-1';

-- 5查询姓王的学生姓名和性别
select stuname as '姓名', stusex as '性别' from tbstudent t where t.stuname like '王%';

-- 6查询姓郭名字总共2个字的学生的姓名(模糊查询% _ like)
select stuname as '姓名' from tbstudent t where t.stuname like '郭_';

-- 7查询姓郭名字总共3个字的学生的姓名
select stuname as '姓名' from tbstudent t where t.stuname like '郭__';

-- 8查询名字中有王字的学生的姓名；
select stuname as '姓名' from tbstudent t where t.stuname like '%王%';

-- 9查询没有录入家庭住址和照片的学生姓名；
select stuname as '姓名' from tbstudent t where t.stuaddr is null or t.stuphoto is null;

-- 10查询学生选课的所有日期；
select scdate as '日期' from tbsc s group by scdate having count(*) >= 1; 

-- 11查询学生的姓名和生日按照年龄从大到小排序
select stuname as '姓名', stubirth as '生日' from tbstudent t  order by t.stubirth ;

-- 12 查询所有录入了家庭住址的男同学的姓名、出生日期和家庭住址按照年龄从小到大排序
select stuname as '姓名', stubirth as '生日', stuaddr as '家庭住址' from tbstudent t where stuaddr is not null and stusex='1' order by stubirth desc;

-- 13查询年龄是最大的同学的出生日期
select stubirth  from tbstudent t2 where t2.stubirth = (select min(stubirth) from tbstudent);

-- 14查询年龄最小的学生的出生日期；
select stubirth from tbstudent t1 where t1.stubirth = (select max(stubirth) from tbstudent);

-- 15查询男女学生的人数；
select count(*), stusex from tbstudent s group by s.stusex; 

-- 16查询课程编号为1111的课程的平均成绩；
select avg(score) from tbsc s where s.cid =1111;

-- 17查询学号为1001的学生所有课程的总成绩；
select sum(score) as '总成绩' from tbsc s where s.sid=1001;

-- 18查询每个学生的学号和平均成绩，null处理为0
select t1.avgscore, s1.stuname, s1.stuid from tbstudent s1 join (select ifnull(avg(score),0) as avgscore, sid from tbsc s group by s.sid) t1 on t1.sid =s1.stuid;

-- 19查询平均成绩大于等于90分的学生的学号和平均成绩；
select t1.avgscore, s1.stuname, s1.stuid from tbstudent s1 join (select ifnull(avg(score),0) as avgscore, sid from tbsc s group by s.sid) t1 on t1.sid =s1.stuid and t1.avgscore >= 90;

-- 20查询年龄最大的学生的姓名
select stuname from tbstudent t1 where t1.stubirth =(select min(stubirth)from tbstudent);

-- 21查询选了两门以上的课程的学生姓名；
select stuname, x.c from tbstudent t1 join (select count(*) as c, sid from tbsc s1 group by s1.sid) x on t1.stuid =x.sid and x.c>2;
 
 -- 22查询选课学生的姓名和平均成绩
 select s.stuname, t.avgscore from tbstudent s join (select ifnull(avg(score),0) as avgscore, sid from tbsc s group by s.sid) t on t.sid=s.stuid where t.avgscore !=0 
 
 select stuname, avgscore from Tbstudent t1 inner join (select sid, avg(score) as avgscore from tbsc where score is not null group by sid) t2 on t1.stuid=t2.sid;
 
 -- 23查询学生姓名、所选课程名称和成绩
 select stuname,x.score, x.cosname from tbstudent t  join (select sid,score,c.cosname from tbsc s join tbcourse c on c.cosid=s.cid) x on x.sid=t.stuid;
 
 -- 24查询每个学生的姓名和选课数量
 select stuname, x.c as '选课数量' from tbstudent t join (select count(*) as c , sid from tbsc s group by sid) x on x.sid = t.stuid;