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','助教','電子工程系');