MySQL 數(shù)據(jù)庫

MySQL 教程

• 什么是數(shù)據(jù)庫待锈？

  數(shù)據(jù)庫（Database）是按照數(shù)據(jù)結(jié)構(gòu)來組織译打、存儲(chǔ)和管理數(shù)據(jù)的倉庫，

  每個(gè)數(shù)據(jù)庫都有一個(gè)或多個(gè)不同的API用于創(chuàng)建次询，訪問，管理瓷叫，搜索和復(fù)制所保存的數(shù)據(jù)屯吊。

  我們也可以將數(shù)據(jù)存儲(chǔ)在文件中，但是在文件中讀寫數(shù)據(jù)速度相對(duì)較慢摹菠。

  所以盒卸，現(xiàn)在我們使用關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（RDBMS）來存儲(chǔ)和管理的大數(shù)據(jù)量。所謂的關(guān)系型數(shù)據(jù)庫次氨，是建立在關(guān)系模型基礎(chǔ)上的數(shù)據(jù)庫蔽介，借助于集合代數(shù)等數(shù)學(xué)概念和方法來處理數(shù)據(jù)庫中的數(shù)據(jù)。

• RDBMS即關(guān)系數(shù)據(jù)庫管理系統(tǒng)(Relational Database Management System)的特點(diǎn)：

  1.數(shù)據(jù)以表格的形式出現(xiàn)

  2.每行為各種記錄名稱

  3.每列為記錄名稱所對(duì)應(yīng)的數(shù)據(jù)域

  4.許多的行和列組成一張表單

  5.若干的表單組成database

• 數(shù)據(jù)庫作用

  1. 持久保存

  2. 方便查詢并提取滿足條件的數(shù)據(jù), 數(shù)據(jù)訪問速度快

  3. 處理并發(fā)訪問

  4. 權(quán)限管理

• 常見數(shù)據(jù)庫:

  關(guān)系型數(shù)據(jù)庫 MySQL ->庫->表->數(shù)據(jù) MySQL Oracle PostgreSQL SQL Server ....

  非關(guān)系型數(shù)據(jù)庫 Redis ->數(shù)據(jù) MongoDB ... 概念：

  數(shù)據(jù)： data

  數(shù)據(jù)庫： DB

  數(shù)據(jù)庫管理系統(tǒng)：DBMS

  數(shù)據(jù)庫系統(tǒng)：DBS

一煮寡、MySQL基礎(chǔ)操作

數(shù)據(jù)庫管理系統(tǒng)中, 可以有很多庫, 每個(gè)數(shù)據(jù)庫中可以包括多張數(shù)據(jù)表

• 操作數(shù)據(jù)庫的步驟

  連接, 打開庫, 操作, 關(guān)閉退出

• SQL ( Structure query language ) 結(jié)構(gòu)化查詢語言

  SQL語言分為4個(gè)部分：
      DDL（Data Definition Languages） :數(shù)據(jù)的定義
      DML（Data Manipulation Language）:數(shù)據(jù)的操作        
      DQL（Data Query Language）       :數(shù)據(jù)的查詢操作
      DCL（Date Control Language）     :數(shù)據(jù)控制語句
  

• SQL語句中的快捷鍵

  \G 格式化輸出（文本式虹蓄，豎立顯示）

  \s 查看服務(wù)器端信息

  \c 結(jié)束命令輸入操作

  \q 退出當(dāng)前sql命令行模式

  \h 查看幫助

• MySQL 語法特點(diǎn)

  1. SQL 語句可以換行, 要以分號(hào)結(jié)尾
  2. 命令不區(qū)分大小寫. 關(guān)鍵字和函數(shù)建議用大寫
  3. 如果提示符為 '> 那么需要輸入一個(gè)'回車
  4. 命令打錯(cuò)了換行后不能修改, 可以用 \c 取消

二、MySQL 數(shù)據(jù)類型

1. 數(shù)值類型

2. 日期和時(shí)間類型

3. 字符串類型

三、MySQL 運(yùn)算符

算術(shù)運(yùn)算符：+ - * / %

比較運(yùn)算符：= > < >= <= <> !=

數(shù)據(jù)庫特有的比較：in，not in, is null,is not null,like, between and

邏輯運(yùn)算符：and or not

like: 支持特殊符號(hào)%和_ ; 其中 %表示任意數(shù)量的任意字符聚谁，_表示任意一位字符冻晤。

四、MySQL 庫操作

1. 查看數(shù)據(jù)庫 show databases;
2. 創(chuàng)建數(shù)據(jù)庫 create database 庫名 default charset=utf8;
3. 打開數(shù)據(jù)庫 use 庫名;
4. 查看當(dāng)前庫 select database();
5. 刪除數(shù)據(jù)庫 drop database 庫名;

五这刷、MySQL 表操作

1. 字段約束

unsigned : 無符號(hào)

int(4) : 顯示寬度

zerofill : 零填充, 位數(shù)不夠的時(shí)候用前導(dǎo)零填充

not null : 不能為空 在操作數(shù)據(jù)庫時(shí)如果輸入該字段的數(shù)據(jù)為NULL 涎跨，就會(huì)報(bào)錯(cuò)

default : 設(shè)置默認(rèn)值

auto_increment : 定義列為自增的屬性，一般用于主鍵崭歧，數(shù)值會(huì)自動(dòng)加1隅很。

primary key : 主鍵 不能為空 唯一性

unique : 唯一索引 可以為空 唯一性

index : 普通索引 可以為空 可以重復(fù)

2. 基本操作

查看當(dāng)前庫中所有表：show tables;

創(chuàng)建表 ：create table 表名(字段1 類型 [字段約束],字段2 類型 [字段約束]...)default charset=utf8;

查看表結(jié)構(gòu)：desc 表名;

查看建表語句：show create table 表名\G

刪除表：drop table 表名;

查看當(dāng)前表中所有數(shù)據(jù): select * from 表名;

3. 表數(shù)據(jù)操作

3.1 增刪改

數(shù)據(jù)的DML操作：添加數(shù)據(jù)，修改數(shù)據(jù)率碾，刪除數(shù)據(jù)

• 插入：格式 insert into 表名[(字段列表)] values(值列表...);

  ? 1. 標(biāo)準(zhǔn)添加: insert into 表名(所有字段) values(對(duì)應(yīng)的值);

? 2. 指定部分字段添加值: insert into 表名 (部分字段) value (對(duì)應(yīng)的值);

? 3. 不指定字段添加值: insert into 表名 value(值1,值2,...);

? 4. 批量添加值: insert into 表名 values(a值1,a值2,...),(b值1,b值2,...);

• 刪除：delete from 表名 where 字段=某個(gè)值;

• 修改：update 表名 set 字段=某個(gè)值 where 條件;

? update 表名 set 字段1=值1,字段2=值2 where 條件;

? update 表名 set 字段=字段+值 where 條件;

3.2 查詢

書寫流程：where group by -->having order by limit

數(shù)據(jù)的DQL操作：數(shù)據(jù)查詢

 select [字段列表]  /*  from 表名

    [where 搜索條件]

    [group by 分組字段 [having 子條件]]

    [order by 排序 asc|desc]

    [limit 分頁參數(shù)];

• 基礎(chǔ)查詢 : select * from 表名;

• where條件查詢

  • 你可以在 WHERE 子句中指定任何條件叔营。
  • 你可以使用 AND 或者 OR 指定一個(gè)或多個(gè)條件。
  • WHERE 子句也可以運(yùn)用于 SQL 的 DELETE 或者 UPDATE 命令所宰。
  • WHERE 子句類似于程序語言中的 if 條件绒尊，根據(jù) MySQL 表中的字段值來讀取指定的數(shù)據(jù)。

  查詢lamp138和lamp94期的女生信息
  mysql> select * from stu where classid in('lamp138','lamp94') and sex='w';
  

• like子句

  • LIKE 通常與 % 一同使用仔粥，類似于一個(gè)元字符的搜索婴谱。
  • 你可以使用 AND 或者 OR 指定一個(gè)或多個(gè)條件蟹但。
  • 你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句來指定條件。
  • 可以使用regexp正則來代替 like

  查詢name字段值是以zh開頭的所有信息
  mysql> select * from stu where name like "zh%";
  

• MySQL的統(tǒng)計(jì)函數(shù)(聚合函數(shù)): select max(),min(),sum()avg(),count(*) from 表名;

• GROUP BY 語句 分組 和 having

  • GROUP BY 語句根據(jù)一個(gè)或多個(gè)列對(duì)結(jié)果集進(jìn)行分組谭羔。
  • 在分組的列上我們可以使用 COUNT, SUM, AVG,等函數(shù)华糖。
  • having 對(duì)分組后的數(shù)據(jù)進(jìn)行刪選

  MySQL> select classid,sex,count(*) from stu  group by classid,sex;
  

• ORDER BY 排序 ------ asc 升序 / desc 降序

  • 可以使用任何字段來作為排序的條件，從而返回排序后的查詢結(jié)果瘟裸。
  • 可以設(shè)定多個(gè)字段來排序客叉。
  • 可以使用 ASC 或 DESC 關(guān)鍵字來設(shè)置查詢結(jié)果是按升序或降序排列。 默認(rèn)為asc 按升序排列
  • 可以添加 WHERE...LIKE 子句來設(shè)置條件话告。

  SELECT field1, field2,...fieldN table_name1, table_name2...
  ORDER BY field1, [field2...] [ASC [DESC]]
  

• limit 關(guān)鍵字 查詢部分?jǐn)?shù)據(jù)

  • limit m; 查詢數(shù)據(jù)只顯示前m條
  • limit m,n; 排除前m條兼搏，然后再查詢出前n條

4. 表結(jié)構(gòu)操作

格式: alter table 表名 action(更改選項(xiàng));

添加字段：alter table 表名 add 字段名 類型 [字段約束];

刪除字段：alter table 表名 drop 字段名;

修改字段：change 可改字段名和類型; modify 可改字段類型, 不可改字段名
        alter table 表名 change 原字段名 修改后的字段名 類型 [字段約束];
        alter table 表名 modify 字段名 修改后的類型 [字段約束];

添加唯一性索引：alter table user add unique uni_name(name);

添加普通索引：alter table user add index index_email(email);

刪除索引：alter table user drop index index_email;

修改表名：alter table 表名 rename as 新表名；

更改auto_increment初始值：alter table 表名稱 auto_increment=數(shù)值沙郭；

更改表類型：alter table 表名 engine=‘innodb’;

          alter table 表名 engine='myisam';

六佛呻、數(shù)據(jù)的導(dǎo)入和導(dǎo)出

1. 數(shù)據(jù)導(dǎo)出

-- 導(dǎo)出整個(gè)數(shù)據(jù)庫
mysqldump -u 用戶名 -p 數(shù)據(jù)庫名 >./導(dǎo)出的文件名.sql
Enter password:
-- 導(dǎo)出一個(gè)表
mysqldump -u 用戶名 -p 數(shù)據(jù)庫名 表名 >./導(dǎo)出的文件名.sql
Enter password:

2. 數(shù)據(jù)導(dǎo)入

-- 將數(shù)據(jù)庫導(dǎo)入
-- 需在本地?cái)?shù)據(jù)庫中創(chuàng)建相對(duì)應(yīng)同名的數(shù)據(jù)庫
mysql -u 用戶名 -p 數(shù)據(jù)庫名<./文件名.sql
Enter password:

七、授權(quán)病线、改密

1. 授權(quán)

格式：grant 允許操作 on 庫名.表名 to 賬號(hào)@來源 identified by '密碼';

實(shí)例：創(chuàng)建zhangsan賬號(hào)吓著，密碼123，授權(quán)l(xiāng)amp61庫下所有表的增/刪/改/查數(shù)據(jù),來源地不限
 mysql> grant select,insert,update,delete on lamp61.* to zhangsan@'%' identified   by '123';
 mysql> grant all on *.* to zhangsan@'%' identified by '123';
 Query OK, 0 rows affected (0.00 sec)

 添加用戶, 并授予在lamp189的數(shù)據(jù)庫中可以對(duì)user表進(jìn)行 查詢和添加數(shù)據(jù)操作
 GRANT select,insert ON lamp189.user TO 'xxoo'@'%' IDENTIFIED BY 'abcd'

 刪除用戶
 drop user 'xxoo'@'%'

2. 改密

1.找到mysql配置文件 C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
2.在[mysqld] 下面添加 skip-grant-tables 保存
3.重啟mysql服務(wù)
4.打開命令行 mysql -uroot -p  不用輸入密碼
5.use mysql 切換到權(quán)限庫
6.執(zhí)行修改密碼 Update user set authentication_string=password('1234567') where user='root';
7:執(zhí)行刷新權(quán)限 flush privileges;
修改成功后：要把配置文件改回來 然后在重啟服務(wù)
使用新密碼鏈接數(shù)據(jù)庫

八氧苍、Python 連接數(shù)據(jù)庫

Python3 MySQL 數(shù)據(jù)庫連接

1. PyMySQL安裝

pip install pymysql

2. 連接數(shù)據(jù)庫

通過如下代碼測(cè)試數(shù)據(jù)庫連接

 import pymysql
 
 # 打開數(shù)據(jù)庫連接
 db = pymysql.connect("localhost","root","123456","mydb" )

 # 使用 cursor() 方法創(chuàng)建一個(gè)游標(biāo)對(duì)象 cursor
 cursor = db.cursor()

 # 使用 execute()  方法執(zhí)行 SQL 查詢 
 cursor.execute("SELECT VERSION()")

 # 使用 fetchone() 方法獲取單條數(shù)據(jù).
 data = cursor.fetchone()

 print ("Database version : %s " % data)

 # 關(guān)閉數(shù)據(jù)庫連接
 db.close()

3. 執(zhí)行數(shù)據(jù)添加

import pymysql

# 打開數(shù)據(jù)庫連接
db = pymysql.connect("localhost","root","123456","mydemo" )

# 使用 cursor() 方法創(chuàng)建一個(gè)游標(biāo)對(duì)象 cursor
cursor = db.cursor()

# SQL 插入語句
sql = "INSERT INTO stu(name,sex,age,classid) values('%s','%c','%d','%s')" % ('uu142','m',22,'lamp180') 

try:
   # 執(zhí)行sql語句
   cursor.execute(sql)
   # 執(zhí)行sql語句
   db.commit()
   print("ok: %d " % (cursor.rowcount))
except:
   # 發(fā)生錯(cuò)誤時(shí)回滾
   db.rollback()

# 關(guān)閉數(shù)據(jù)庫連接
db.close()

4. 執(zhí)行刪除操作

 import pymysql

 # 打開數(shù)據(jù)庫連接
 db = pymysql.connect("localhost","root","","mydemo" )

 # 使用 cursor() 方法創(chuàng)建一個(gè)游標(biāo)對(duì)象 cursor
 cursor = db.cursor()

 # SQL 刪除語句
 sql = "delete from stu where id = '%d'" % (13)
 try:
    # 執(zhí)行SQL語句
    cursor.execute(sql)
    # 提交修改
    db.commit()
 except:
    # 發(fā)生錯(cuò)誤時(shí)回滾
    db.rollback()

 # 關(guān)閉數(shù)據(jù)庫連接
 db.close()

5. 執(zhí)行數(shù)據(jù)修改/更新

import pymysql

# 打開數(shù)據(jù)庫連接
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 使用cursor()方法獲取操作游標(biāo) 
cursor = db.cursor()

# SQL 更新語句
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1
                          WHERE SEX = '%c'" % ('M')
try:
   # 執(zhí)行SQL語句
   cursor.execute(sql)
   # 提交到數(shù)據(jù)庫執(zhí)行
   db.commit()
except:
   # 發(fā)生錯(cuò)誤時(shí)回滾
   db.rollback()

# 關(guān)閉數(shù)據(jù)庫連接
db.close()

6. 執(zhí)行數(shù)據(jù)查詢

Python查詢Mysql使用 fetchone() 方法獲取單條數(shù)據(jù), 使用fetchall() 方法獲取多條數(shù)據(jù)夜矗。

fetchone(): 該方法獲取下一個(gè)查詢結(jié)果集。結(jié)果集是一個(gè)對(duì)象

fetchall(): 接收全部的返回結(jié)果行.

rowcount: 這是一個(gè)只讀屬性让虐，并返回執(zhí)行execute()方法后影響的行數(shù)紊撕。

 import pymysql

 # 打開數(shù)據(jù)庫連接
 db = pymysql.connect("localhost","root","","mydemo" )

 # 使用 cursor() 方法創(chuàng)建一個(gè)游標(biāo)對(duì)象 cursor
 cursor = db.cursor()

 # SQL 查詢語句
 sql = "select * from stu limit %d" % (3)
 #sql = "select * from stu"

 try:
    # 執(zhí)行SQL語句
    cursor.execute(sql)
    # 獲取所有記錄列表
    results = cursor.fetchall()
    for row in results:
       id = row[0]
       name = row[1]
       sex = row[2]
       age = row[3]
       classid = row[4]
        # 打印結(jié)果
       print ("id=%d,name=%s,sex=%s,age=%d,classid=%s" % (id,name,sex,age,classid))
 except:
    print ("Error: unable to fetch data")

 # 關(guān)閉數(shù)據(jù)庫連接
 db.close()