MySQL

一、環境搭建

以windows下5.7版本為例

• 下載解壓

https://downloads.mysql.com/archives/community/

下載64位壓縮包在預計安裝目錄解壓

• 創建本地配置文件

在mysql安裝根目錄下創建my.ini文件

[mysqld]

# 自定義服務端口
port=3306

# 自定義basedir
basedir=D:\\test\\mysql

# 自定義數據目錄
datadir=D:\\test\\mysql\\data

配置文件查找順序

C:\Windows\my.ini

C:\Windows\my.cnf

C:\my.ini

C:\my.cnf

path\to\mysql\my.ini

path\to\mysql\my.cnf

• 初始化mysql

管理員CMD窗口

"path\to\bin\mysqld.exe" --initialize -insecure

• 啟動mysql

臨時啟動：

"path\to\bin\mysqld.exe" 

關閉窗口即可停止服務

制作服務啟動：

"path\to\bin\mysqld.exe" --install server_name
net start server_name

停止服務

net stop server_name

移除服務

"path\to\bin\mysqld.exe" --remove server_name

• 測試連接mysql

"path\to\bin\mysql.exe" -h host_ip -P port_num -u username -p

show databases;
exit;

• 密碼相關

# 連接登錄mysql

# 設置/修改當前用戶密碼
set password = password("your_password");

密碼找回

修改my.ini配置文件，在[mysqld]節點下添加skip-grant-tables=1

[mysqld]
skip-grant-tables=1
# 統一編碼
# 創建my.ini配置文件，并保存進以下內容：
# [mysqld]
# character-set-server=utf8
# collation-server=utf8_general_ci
# [client]
# default-character-set=utf8
# [mysql]
# default-character-set=utf8

重啟mysql并無密碼進入mysql

net stop server_name
net start server_name
mysql -u root -p

修改密碼

use mysql;
update user set authentication_string=password("new_password"), password_last_changed=now() where user="root";

退出mysql并移除之前在my.ini配置文件中添加的內容

[mysqld]
# skip-grant-tables=1

重啟mysql即可持新密碼進入

net stop server_name
net start server_name
mysql -u root -p

• 基礎命令

# 查看數據庫
show databases;

# 創建數據庫
create database db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

# 刪除數據庫
drop database db_name;

# 進入數據庫
use db_name;

# 查看當前數據路下的數據表
show tables;

# 創建數據表
create table pymysql(
    id int auto_increment primary key,
    ip varchar(32) not null,
    type varchar(32) default "test",
    origin varchar(32) null
)default charset=utf8;

# 刪除數據表
drop table pymysql;

# 清空表
delete from pymysql;
truncate table pymysql;

# 修改表
alter table pymysql add email varchar(32) null auto_increment;
alter table pymysql drop column email;
alter table pymysql modify column ip varchar(64);
alter table pymysql change origin visitor int(5)zerfill not null primary key auto_increment;
alter table pymysql alter type set default "python"
alter table pymysql alter type drop default;

# 新增表數據
insert into pymysql (id, type) values (1, "test"), (2, "python");

# 刪除表數據
delete from pymysql; # 清空
delete from pymysql where id<2 and type="test";

# 修改數據
update pymysql set type="java" where id=2;
update pymysql set type=concat(type, "c") where id=2;

# 查詢數據
select * from pymysql;
select id, type as i, t from pymysql where id=2;

• python操作mysql

安裝pymysql

pip install pymysql

用python操作mysql數據庫

import pymysql

# 獲取數據庫連接
conn = pymysql.connect(host='*****', port=3306, user='***', password='****', charset='utf8')
# 獲取游標
cursor = conn.cursor()
# 執行查看數據庫命令
cursor.execute('show databases')
# 獲取命令執行結果
result = cursor.fetchall()

print(result)  # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))

# 創建數據庫
cursor.execute('create database pymysql DEFAULT CHARSET utf8 COLLATE utf8_general_ci')
conn.commit()
cursor.execute('show databases')
print(cursor.fetchall())  # (('information_schema',), ('mysql',), ('performance_schema',), ('pymysql',), ('sys',))

# 刪除數據庫
cursor.execute('drop database pymysql')
conn.commit()
cursor.execute('show databases')
print(cursor.fetchall())  # (('information_schema',), ('mysql',), ('performance_schema',), ('sys',))

# 查看數據庫表
cursor.execute('use mysql')
cursor.execute('show tables')
print(cursor.fetchall())

cursor.close()
conn.close()

• SQL注入

import pymysql

# 獲取數據庫連接
conn = pymysql.connect(host='****', port=3306, user='***', password='****', charset='utf8')
# 獲取游標
cursor = conn.cursor()

usr = input('請輸入用戶名：')
pwd = input('請輸入密碼：')
# 防止SQL注入，應使用cursor.execute()傳參完成字符串拼接
cursor.execute("select * from user where name=%s and password=%s", [usr, pwd])
# cursor.execute("select * from user where name=%(usr)s and password=%(pwd)s", {'usr':usr, 'pwd':pwd})

cursor.close()
conn.close()

存儲引擎

innodb:version5.5+默認引擎
myisam:version5.5-默認引擎
memory:內存引擎（數據全部存放在內存中）
blackhole:無論存什么，都立刻消失

基本數據類型

整型

TINYINT    1byte -128~127   (默認)               無符號：0~2^n-1
SMALLINT   2bytes -32768~32767
MEDUIMINT  3bytes -8388608~8388607
INT        4bytes -2147483648~2147483647
BIGINT     8bytes ~
#默認帶符號
#存入超出設定范圍的值，只存最近的臨界值
#指定無符號：create table test(id tinyint unsigned,name char(16));


#數據類型后面（）內跟的寬度限制一般是寬度位數，特殊情況：
ID int(8)
如果存入數字不足8位，用' '填充至8位，否則在類型范圍內放入幾位存幾位。
create table test4(id int(8) unsigned zerofill);

嚴格模式

# 查看嚴格模式
show variables like '%mode';

%:匹配任意多個字符，_匹配任意單個字符

#修改嚴格模式：
set session 僅當前界面(臨時)
set global 全局有效
set global sql_mode = 'STRICT_TRANS_TABLES';

浮點型

FLOAT:   float(255,30),共255位，小數部分30位
DOUBLE:  double(255,30),共255位，小數部分30位
DECIMAL: decimal(65,30),共65位，小數部分30位
精確度：FLOAT<DOUBLE<DECIMAL

字符型

char:定長     超長報錯，不足用' '補全 讀寫速度快

varchar:變長  超長報錯，不足按實際存（省空間）   使用較多

char_length：統計字段長度
select char_length(name) from test5;
# 顯示補全空格
set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_LENGTH';

時間類型

date:
time:
datetime:

枚舉與集合類型

枚舉：enum 多選一
集合：set  多選多