python学习之数据库mariadb操作

数据库简介
数据库分类
关系型数据库:指采用了关系模型来组织数据的数据库。关系模型指的就是二维表格模型,而一
个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。主流的关系型数据库有:
Oracle、Microsoft SQL Server、MySQL、PostgreSQL,SQLite、MariaDB(MySQL的一个分
支)Microsoft Access、SAP。
非关系型数据库:指非关系型的,分布式的,以键值对存储且结构不固定,可以减少一些
时间和空间的开销。非关系型数据库都是针对某些特定的应用需求,主要分为以下几类:
1). 面向海量数据访问的面向文档数据库:MongoDB、Amazon DynamoDB、Couchbase等。
2). 面向高性能并发读写的key-value数据库: Redis、 Memcached等。
3). 面向搜索数据内容的搜索引擎:Elasticsearch,Splunk,Solr,MarkLogic和Sphinx等。
4). 面向可扩展性的分布式数据库:Cassandra,HBase等。
当前物理的数据库都是按照E-R模型进行设计的,
• E表示entry,实体
• R表示relationship,关系
• 一个实体转换为数据库中的一个表
关系描述两个实体之间的对应规则,包括: 一对一 ,一对多, 多对多

经过研究和对使用中问题的总结,对于设计数据库提出了一些规范,这些规范被称为范式
• 第一范式(1NF):列不可拆分 , 即无重复的域。
• 第二范式(2NF):唯一标识 ,即拥有实体的唯一标识(eg: 身份证、id号等)。
• 第三范式(3NF):引用主键 ,即每列数据都与主键直接相关。
说明:关系型数据库有六种范式。一般说来,数据库只需满足第三范式(3NF)就行了。
MySQL常用存储引擎分析
数据库存储引擎是数据库底层软件组织,进行创建、查询、更新和删除数据。不同的存储引
擎提供不同的存储机制、索引技巧、锁定水平等功能, MySQL的核心就是存储引擎。
MySQL查询存储引擎SQL语句:SHOW ENGINES
安装

数据库服务管理

安全性密码设置

1. 关闭mysql服务器的防火墙
   

2. 用户授权: 允许root用户通过westos密码 在任意主机(%)远程登陆并操作数据库;
   
   允许远程连接
   
   找回密码
   
   数据库操作
   
   表操作
   
   表创建: 数据完整性
   • 一个数据库就是一个完整的业务单元,可以包含多张表,数据被存储在表中
   • 在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为
   表添加一些强制性的验证, 包括数据字段的类型、约束
   在mysql中包含的数据类型很多,这里主要列出来常用的几种:
   • 数字:int,decimal, float
   • 字符串:varchar,text
   • 日期:datetime
   • 布尔:bool
   表的创建: 约束
   • 主键 primary key
   • 非空 not null
   • 惟一 unique
   • 默认 default
   • 外键 foreign key
   • 自动增长 auto_increment
   数据操作
   
   备份与恢复
   
   查询的基本语法
   select from 表名;
   • from关键字后面写表名,表示数据来源于是这张表
   • select后面写表中的列名,如果是表示在结果中显示表中所有列
   • 在select后面的列名部分,可以使用as为列起别名,这个别名出现在结果集中
   • 如果要查询多个列,之间使用逗号分隔
   消除重复行
   在select后面列前使用distinct可以消除重复的行
   select distinct gender from students;
   条件
   使用where子句对表中的数据筛选,结果为true的行会出现在结果集中
   select * from 表名 where 条件;
   优先级
   •小括号,not,比较运算符,逻辑运算符
   •and比or先运算,如果同时出现并希望先算or,需要结合()使用
   
   分组
   • 按照字段分组,表示此字段相同的数据会被放到一个组中
   • 分组后,只能查询出相同的数据列,对于有差异的数据列无法出现在结果集中
   • 可以对分组后的数据进行统计,做聚合运算
   select 列1,列2,聚合... from 表名 group by 列1,列2,列3...
   分组后的数据筛选
   having后面的条件运算符与where的相同
   对比where与having
   • where是对from后面指定的表进行数据筛选,属于对原始数据的筛选
   • having是对group by的结果进行筛选
   聚合
   为了快速得到统计数据,提供了5个聚合函数
   
   排序
   为了方便查看数据,可以对数据进行排序:
   • 将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推
   • 默认按照列值从小到大排列
   • asc从小到大排列,即升序, desc从大到小排序,即降序
   获取部分行
   当数据量过大时,在一页中查看数据是一件非常麻烦的事情:
   • 从start开始,获取count条数据
   • start索引从0开始
   实验: 客户端可以远程连接服务端数据库

   • 客户端: 172.25.254.197
   • 服务端: 172.25.254.18
     服务端操作:
     $ mysql -uroot -p
     #查看mysql数据库中user数据库表的三列内容: Host,User,Password.
     MariaDB [(none)]> select Host,User,Password from mysql.user;
     #172.25.254.197这台主机以root用户身份远程登录， 密码为westos， 访问数据库的所有内容(.)
     MariaDB [(none)]> grant all on . to root@'172.25.254.197' identified by 'westos';
     #任意一台主机以root用户身份远程登录， 密码为westos， 访问数据库的所有内容(.)
     #sql语句中的%等价于.
     MariaDB [(none)]> grant all on . to root@'%' identified by 'westos';
     #任意一台主机以root用户身份远程登录， 密码为westos， 访问mysql数据库的所有表(mysql.)
     MariaDB [(none)]> grant all on mysql.* to root@'%' identified by 'westos';
     MariaDB [(none)]> select Host,User,Password from mysql.user;

   #删除用户授权(远程登录)
   MariaDB [(none)]> drop user root@'%';

客户端测试:
#指定主机名为172.25.254.18， 用户名为root远程登录.
$ mysql -h 172.25.254.18 -uroot -pwestos
#创建数据库Blog并指定编码格式为utf8(存储的数据为中文， 需要设置);
MariaDB [(none)]> create database Blog default charset='utf8';
#显示所有数据库名称;
MariaDB [(none)]> show databases;
#选择/切换数据库Blog;
MariaDB [(none)]> use Blog;
#查看当前选择的数据库;
MariaDB [Blog]> select database();
#查看当前数据库的所有数据库表;
MariaDB [Blog]> show tables;
#创建数据库表userinfo, 两列数据。
#varchar可变长字符串, not null数据非空,unique数据唯一。
MariaDB [Blog]> create table userinfo(
-> username varchar(20) not null unique,
-> password varchar(20) not null)
-> ;
#查看表结构
MariaDB [Blog]> desc userinfo;
#添加数据到数据表中;
MariaDB [Blog]> insert into userinfo values('user2', 'passwd');
MariaDB [Blog]> insert into userinfo values('张三', 'passwd');
#数据查询;
MariaDB [Blog]> select * from userinfo;
#修改表结构: 添加一列信息、修改一列信息、删除一列信息.
MariaDB [Blog]> alter table userinfo add gender varchar(3);
MariaDB [Blog]> alter table userinfo change gender sex varchar(3);
MariaDB [Blog]> alter table userinfo drop sex;
#数据表重命名;
MariaDB [Blog]> rename table userinfo to users;
#查看表的常见语句;
MariaDB [Blog]> show create table users;
#删除数据表;
MariaDB [Blog]> drop table users;
#删除数据库;
MariaDB [Blog]> drop database Blog;
MariaDB [(none)]> create database Blog default charset='utf8';
MariaDB [(none)]> use Blog;
MariaDB [Blog]> create table users(
-> id int primary key auto_increment,
-> username varchar(20) unique not null,
-> password varchar(20) not null default '000000');
MariaDB [Blog]> desc users;
MariaDB [Blog]> insert into users values(1, 'user1', 'password');
MariaDB [Blog]> insert into users(username) values('user2');
MariaDB [Blog]> insert into users(username) values('user3'),('user4'), ('user5');
MariaDB [Blog]> update users set password='666666' where username='user4';

MariaDB [Blog]> select from users where username='user4';
MariaDB [Blog]> select from users;
MariaDB [Blog]> delete from users where username='user4';
MariaDB [Blog]> select * from users;
create table student(
sno varchar(12) primary key,
sname varchar (10) comment '学生姓名',
sex varchar (2) comment '性别',
age int,
address varchar(50),
classno varchar (5)
);
#*****关于查询条件****

1、 查询students表中的所有记录的sname、ssex和class列。
MariaDB [Blog]> select sname,ssex,class from students;

2、 查询教师所有的单位即不重复的Depart列。
MariaDB [Blog]> select distinct depart from teachers;

3、 查询students表的所有记录。
MariaDB [Blog]> select * from students;

4、 查询scores表中成绩在60到80之间的所有记录。
MariaDB [Blog]> select * from scores where degree between 60 and 80;

5、 查询scores表中成绩为85，86或88的记录。
MariaDB [Blog]> select from scores where degree=85 or degree=86 or degree=88;
MariaDB [Blog]> select from scores where degree in (85,86,88);

6、 查询students表中“95031”班或性别为“女”的同学记录。（作业）
select * from students where xxxxxx or xxxxx;

*关于排序***
7、 以class降序查询students表的所有记录。
MariaDB [Blog]> select from students order by class desc;
MariaDB [Blog]> select from students order by class;（默认升序）

8、 以cno升序、degree降序查询scores表的所有记录。
以cno升序、degree降序: 当cno相同时， 按照degree降序排列。
cno degree
1 3
1 2
2 3
MariaDB [Blog]> select * from scores order by cno,degree desc;

****关于聚合函数*****
9、 查询“95031”班的学生人数。
MariaDB [Blog]> select from students where class='95031';
MariaDB [Blog]> select count() from students where class='95031';
MariaDB [Blog]> select count(*) as studentCount from students where class='95031'; (最终版)

10、查询‘3-105’号课程的平均分。
MariaDB [Blog]> select avg(degree) as avgScore from scores where cno='3-105';

***关于group by 和having*
11、查询scores表中至少有5名学生选修的并以3开头的课程的平均分数。
12、查询最低分大于70，最高分小于90的Sno列。

13、查询scores表中的最高分的学生学号和课程号。
14、查询所有学生的Sname、Cno和Degree列。
15、查询所有学生的Sno、Cname和Degree列。
16、查询所有学生的Sname、Cname和Degree列。
17、查询“95033”班所选课程的平均分。
USE Blog;

CREATE TABLE IF NOT EXISTS students
(sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME,
class VARCHAR(5));

CREATE TABLE IF NOT EXISTS courses
(cno VARCHAR(5) NOT NULL,
cname VARCHAR(10) NOT NULL,
tno VARCHAR(10) NOT NULL);

CREATE TABLE IF NOT EXISTS scores
(sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL,
degree NUMERIC(10, 1) NOT NULL);

CREATE TABLE IF NOT EXISTS teachers
(tno VARCHAR(3) NOT NULL,
tname VARCHAR(4) NOT NULL, tsex VARCHAR(2) NOT NULL,
tbirthday DATETIME NOT NULL, prof VARCHAR(6),
depart VARCHAR(10) NOT NULL);

INSERT INTO students (sno,sname,ssex,sbirthday,class) VALUES (108 ,'曾华' ,'男' ,'1977-09-01',95033);
INSERT INTO students (sno,sname,ssex,sbirthday,class) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
INSERT INTO students (sno,sname,ssex,sbirthday,class) VALUES (107 ,'王丽' ,'女' ,'1976-01-23',95033);
INSERT INTO students (sno,sname,ssex,sbirthday,class) VALUES (101 ,'李军' ,'男' ,'1976-02-20',95033);
INSERT INTO students (sno,sname,ssex,sbirthday,class) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
INSERT INTO students (sno,sname,ssex,sbirthday,class) VALUES (103 ,'陆君' ,'男' ,'1974-06-03',95031);

INSERT INTO courses(cno,cname,tno)VALUES ('3-105' ,'计算机导论',825);
INSERT INTO courses(cno,cname,tno)VALUES ('3-245' ,'操作系统' ,804);
INSERT INTO courses(cno,cname,tno)VALUES ('6-166' ,'数据电路' ,856);
INSERT INTO courses(cno,cname,tno)VALUES ('9-888' ,'高等数学' ,100);

INSERT INTO scores(sno,cno,degree)VALUES (103,'3-245',86);
INSERT INTO scores(sno,cno,degree)VALUES (105,'3-245',75);
INSERT INTO scores(sno,cno,degree)VALUES (109,'3-245',68);
INSERT INTO scores(sno,cno,degree)VALUES (103,'3-105',92);
INSERT INTO scores(sno,cno,degree)VALUES (105,'3-105',88);
INSERT INTO scores(sno,cno,degree)VALUES (109,'3-105',76);
INSERT INTO scores(sno,cno,degree)VALUES (101,'3-105',64);
INSERT INTO scores(sno,cno,degree)VALUES (107,'3-105',91);
INSERT INTO scores(sno,cno,degree)VALUES (108,'3-105',78);
INSERT INTO scores(sno,cno,degree)VALUES (101,'6-166',85);
INSERT INTO scores(sno,cno,degree)VALUES (107,'6-106',79);
INSERT INTO scores(sno,cno,degree)VALUES (108,'6-166',81);

INSERT INTO teachers(tno,tname,tsex,tbirthday,prof,depart) VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teachers(tno,tname,tsex,tbirthday,prof,depart) VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teachers(tno,tname,tsex,tbirthday,prof,depart) VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teachers(tno,tname,tsex,tbirthday,prof,depart) VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');