MySQL基礎筆記整理

MySQL安裝和啟動

Windows下安裝和啟動服務

下載地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads

安裝

1 在MySQL官網下載zip文件，

2 在任意位置保存解壓。這裡在E盤創建MySQL目錄

E:\MySQL\

3 E:\Monkey\mysql-5.7.16-winx64\目錄創建data目錄。

4 在命令行運行E:\MySQL\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --initialize-insecure進行mysql服務端初始化，預設的創建了root用戶 密碼為空

配置和啟動

1 啟動服務端：E:\MySQL\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld

2 啟動客戶端：E:\MySQL\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysql -uroot -p # 密碼為空

3 執行show databases;

結果如下：

4 添加環境變數，把E:\MySQL\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin添加到Windows的環境變數（添加環境變數：https://jingyan.baidu.com/article/47a29f24610740c0142399ea.html）

5 Windows服務：

註冊：

E:\MySQL\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --install
註冊之後可以用 "net start MySQL" 啟動服務 "net stop MySQL"停止服務

移除：

E:\MySQL\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --remove

Linux 下安裝和啟動服務

yum安裝

yum list installed |grep mysql  

//若Linux自帶mysql，將其卸載
yum -y remove mysql-libs.x86_64

在官網下載yum包 url：https://dev.mysql.com/downloads/repo/yum/
yum localinstall mysql57-community-release-el6-11.noarch.rpm

yum repolist enabled |grep "mysql.*-community.*"  # 看是否成功添加yum倉庫

yum install mysql-community-server    # 安裝

service mysqld start    # 啟動

Starting mysqld:[ OK ]  # 表示安裝成功

源碼安裝

安裝編譯源碼所需的包

 yum -y install make gcc-c++ cmake bison-devel  ncurses-devel

之後下載並解包文件

wget http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.14.tar.gz
tar xvf mysql-5.6.14.tar.gz

編譯安裝

cd mysql-5.6.14
cmake .

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DSYSCONFDIR=/etc \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci

make && make install

MySQL的初始化

cd /usr/local/mysql
scripts/mysql_install_db --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql

註意：在/etc目錄下會存在一個my.cnf，需要將此文件更名為其他的名字，如：/etc/my.cnf.bak，否則，該文件會幹擾源碼安裝的MySQL的正確配置，造成無法啟動。

註冊為系統服務：

cd /usr/local/mysql/support-files

cp mysql.server /etc/rc.d/init.d/mysql   # 註冊服務

cp my-default.cnf /etc/my.cnf    # 使用預設配置文件

服務啟動：

service mysql start 

MySQL之SQL基礎語句

資料庫相關

MySQL啟動後

連接資料庫：

mysql -uroot -ppassword    # u 後面跟用戶名 p 後面是密碼

查看資料庫：

show databases;

使用資料庫：

use databases;

查看資料庫中所有表：

show tables;　

創建資料庫：(create database + 資料庫名)

create database test_db_name

刪除資料庫：(drop database + 資料庫名)

drop database dbname;

用戶相關

創建用戶：

create user 'monkey’@'192.168.2.2’ identified by 'passwod’;
# 創建名為 ‘monkey’ 密碼為’password’ 登陸IP只能為：192.168.2.2 的用戶

create user 'monkey’@'192.168.2.%' identified by 'password’;
# 創建名為 ‘monkey’ 密碼為’password’ 登陸IP為：192.168.2 網段的用戶

create user 'monkey’@'%' identified by 'password’;
# 創建名為 ‘monkey’ 密碼為’password’ 登陸IP不限的用戶

對用戶進行授權：

grant select,insert,update  on test_db_name.table1 to 'monkey’@'%';
# 對 monkey 用戶 開放資料庫：test_db_name 下表：table1 的查 插 更新的許可權

grant all privileges  on test_db_name.table1 to 'monkey’@'%';
# 對 monkey 用戶 開放資料庫：test_db_name 下表：table1 的所有許可權

revoke all privileges  on test_db_name.table1 from 'monkey’@'%';			  
# 取消許可權

關於用戶：

在mysql資料庫下的user表，這張表存放著用戶的信息

use mysql;
select user,Host from user;

對用戶的操作也可以修改這張表，但是不建議這樣做！

數據表相關

數據的欄位類型

數值型：

字元串類型

日和期時間類型

數據表的創建和增加

創建表：

create table tablename(
				id int auto_increment primary key,
				name varchar(32),
				age int
			)engine=innodb default charset=utf8;

其中 語法：create table +tablename(列名1 類型 其他,

　　　　　　　　　　　　　　　　　列名2 類型 其他　) engine = innodb default charset=utf-8

engine 指明資料庫建表引擎為 innodb （支持事務操作）。

myisam 不支持事務。

default charset=utf-8 指明數據表的編碼字元集 為 utf-8。

在表中添加數據（三種）

insert into tablename(name,age) values('monkey’,18);
			
insert into tablename(name,age) values('JIAJIA’,18),('xiaoliu’,18);
# 可以跟多條記錄，一元組的形式添加
			
insert into tablename(name,age) select name,age from tablename2;
# 從別的表查找數據 寫入 

刪除表中數據不刪除表（三種）：

delete from tablename;    # 刪除數據，但是自增計數不會被刪除 單純的清掉數據
truncate table tablename;    # 清空表，相當於新的表 自增計數 0
delete from tb1 where id > 10    # 跟條件

刪除表:

drop table tablename;    # 刪除表 

表中插入數據：

insert into t1(id,name) values(100,'monkey’);

修改數據

update tablename set age=1024;
update tablename set age=2048 where age=18;

查看數據

select * from tablename; # 查看表的所有行列
select  name,age from tablename;    # 查看name和age 列

外鍵

create table tablename1(id int auto_increment primary key ,
name char(32));

create table tablename2(id int auto_increment primary key ,
name char(32),
friend_id int,
constraint fk_t1_t2 foreign key (friend_id) references tablename1(id));

　　外鍵的創建：定義表的列時預料外鍵欄位，之後 constraint fk_t1_t2 foreign key (friend_id) references tablename1(id)); constaint ... foreign key 預留欄位名 references 被關聯表名（欄位名）

表的補充

desc t;    # 查看t 的表結構
		
show create table t;    # 查看創建t的創建語句
		
show create table t \G;    # 橫向查看
		
alter table t10 AUTO_INCREMENT=10000;    # 設置自增的起始值
			

MySQL的自增問題

自增的起始值：

alter table t10 AUTO_INCREMENT=10000;    # 設置自增的起始值

MySQL: 自增步長
基於會話級別：

show session variables like 'auto_inc%';	查看全局變數
set session auto_increment_increment=2; 設置會話步長
# set session auto_increment_offset=10;

基於全局級別：

show global variables like 'auto_inc%';	查看全局變數
set global auto_increment_increment=2; 設置會話步長
# set global auto_increment_offset=10;

SqlServer：自增步長：

基於表級別：

CREATE TABLE `table1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
) ENGINE=InnoDB AUTO_INCREMENT=3, 步長=2 DEFAULT CHARSET=utf8
# 自增起始值為 3 步長為 2

CREATE TABLE `table2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
) ENGINE=InnoDB AUTO_INCREMENT=9, 步長=3 DEFAULT CHARSET=utf8
# 自增起始值為 9 步長為 3

約束與索引

三種約束關係

一對多約束

外鍵約束本身就實現了一對多的約束

create table tablename1(id int auto_increment primary key ,
name char(32));

create table tablename2(id int auto_increment primary key ,
name char(32),
friend_id int,
constraint fk_t1_t2 foreign key (friend_id) references tablename1(id));

一對一約束

實現一對一約束 外鍵 加 唯一索引

create table class(id int auto_increment primary key ,
name char(32));

create table teacher(id int auto_increment primary key ,
name char(32),
class_id int,
unique (class_id),
constraint fk_class_teacher foreign key (class_id) references class(id));

多對多約束

通過一張關係表和外鍵約束建立多對多的映射關係

create table class(id int auto_increment primary key ,
name char(32));

create table student(id int auto_increment primary key ,
name char(32),
class_id int);

create table student_class_relation(id int auto_increment primary key ,
student_id int,
class_id int,
constraint fk_relation_class foreign key (class_id)references class(id),
constraint fk_relation_student foreign key (student_id)references student(id));

主鍵外鍵唯一約束

所有的索引都有一個共有的作用：加速查找

主鍵：

主鍵約束即在表中定義一個主鍵來唯一確定表中每一行數據的標識符。主鍵可以是表中的某一列或者多列的組合，其中由多列組合的主鍵稱為複合主鍵。主鍵應該遵守下麵的規則：

• • • 每個表只能定義一個主鍵。
    • 主鍵值必須唯一標識表中的每一行，且不能為 NULL，即表中不可能存在兩行數據有相同的主鍵值。這是唯一性原則。
    • 一個列名只能在複合主鍵列表中出現一次。
    • 複合主鍵不能包含不必要的多餘列。當把複合主鍵的某一列刪除後，如果剩下的列構成的主鍵仍然滿足唯一性原則，那麼這個複合主鍵是不正確的。這是最小化原則。

主鍵聲明的兩種方式：

1、直接在定義欄位的時候聲明為主鍵，但是這樣只能聲明單一欄位為主鍵無法聲明 複合主鍵。　

create table class(id int auto_increment primary key , 
name char(32));

2、在欄位定義全部結束之後聲明主鍵，這樣既可以聲明單一的主鍵，也可以聲明為 複合主鍵。

create table person(name char(32) not null,
part char(32) not null ,
company char(32) not null ,
primary key (part,company,name));

聯合主鍵

當建立多對多關係的兩個表，通過關係表來唯一確定關係的時候，關係表的主鍵稱為 聯合主鍵。

例如： 多對多示例中的

student_class_relation 表中的 id int auto_increment primary key 欄位。

創建表後修改主鍵約束

create table user(id int, name char(32), sex char(4));
# 創建 user表 包含 id name sex 三個欄位 沒有主鍵

# 添加主鍵
alter table user add primary key(id);     # 將id欄位設置為主鍵

# 修改主鍵
alter table user drop primary key,add primary key(name);    # 刪除原主鍵 將name設置為新的主鍵

# 將主鍵改為 複合主鍵
alter table username drop primary key,add primary key(id,name);  # 主鍵改為id 和 name的聯合主鍵

外鍵：

主表和從表：

主表（父表）：對於兩個具有關聯關係的表而言，相關聯欄位中主鍵所在的表就是主表。

從表（子表）：對於兩個具有關聯關係的表而言，相關聯欄位中外鍵所在的表就是從表。

• • • 父表必須已經存在於資料庫中，或者是當前正在創建的表。如果是後一種情況，則父表與子表是同一個表，這樣的表稱為自參照表，這種結構稱為自參照完整性。
    • 必須為父表定義主鍵。
    • 主鍵不能包含空值，但允許在外鍵中出現空值。也就是說，只要外鍵的每個非空值出現在指定的主鍵中，這個外鍵的內容就是正確的。
    • 在父表的表名後面指定列名或列名的組合。這個列或列的組合必須是父表的主鍵或候選鍵。
    • 外鍵中列的數目必須和父表的主鍵中列的數目相同。
    • 外鍵中列的數據類型必須和父表主鍵中對應列的數據類型相同。

外鍵的聲明：

　　　　定義時聲明本篇上述 一對多對應關係

創建表後修改外鍵：

alter table <數據表名> add constraint <索引名>
foreign key(<列名>) refrences <主表名> (<列名>);

alter table user add part_id int;    # 為user表添加 part_id列 作為外鍵
alter table user add constraint fk_part_user foreign key(part_id) references part(id);     # 為user表添加外鍵約束

唯一約束

• • • 唯一約束也叫唯一索引，MySQL唯一約束（Unique Key）要求該列唯一，允許為空，但只能出現一個空值。唯一約束可以確保一列或者幾列不出現重覆值。
    • 唯一約束允許為空，但是不允許有重覆，主鍵約束不允許為空，不允許重覆。
    • 唯一約束可以對多個欄位設置，聯合唯一索引。
    • 註意 唯一索引中的空值允許重覆，即可以有多個空值記錄存在。

聲明唯一約束的兩種方式：

1、在定義欄位時聲明，這樣的方式像聲明主鍵一樣，同樣的無法聲明聯合唯一。

create table unique_table0(name char(32) unique ,
company char(32);

2、在欄位聲明全部結束的時候單獨的聲明唯一索引，這樣可以聲明成多個欄位的唯一索引 即聯合唯一索引。

create table unique_table1(name char(32) ,
part char(32) ,
company char(32) ,
unique (part,company,name));

***創建表後修改唯一約束：*

**

*創建唯一約束：*

create table t1(id int, name char(32));

# way 1:  alter table '數據表名' add  constraint '索引名'  unique(‘要添加的欄位名’)；
alter table t1 add  constraint unique_id_name unique(id,name);

# way 2:  create unique index 索引名 on 數據表名(欄位名);
create unique index unique_id_name unique(id,name);

*刪除唯一約束*

# way 1:  alter table 數據表名 drop index 刪除的索引名；
alter table t1 drop index unique_id_name;

# way 2:  drop index 索引名 on 數據表名；
drop index unique_id_name on t1;

索引

索引的詳細介紹戳這裡url：

普通索引

　　普通索引(由關鍵字KEY或INDEX定義的索引)的唯一任務是加快對數據的訪問速度。因此，應該只為那些最經常出現在查詢條件(WHERE column = …)或排序條件(ORDER BY column)中的數據列創建索引。只要有可能，就應該選擇一個數據最整齊、最緊湊的數據列(如一個整數類型的數據列)來創建索引。

索引的通用方法

# 查看索引
show index from table_name;
# 其中 Key_name 欄位為索引名

# 刪除索引 
drop index index_name on table_name；

普通索引方法

–直接創建索引(length表示使用名稱前1ength個字元)  
CREATE INDEX index_name ON table_name(column_name(length))  
–修改表結構的方式添加索引  
ALTER TABLE table_name ADD INDEX index_name ON (column_name)  
–創建表的時候同時創建索引  
CREATE TABLE table_name (  
id int(11) NOT NULL AUTO_INCREMENT ,  
title char(255) NOT NULL ,  
PRIMARY KEY (id),  
INDEX index_name (title)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;   
建立複合索引 。  
CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);  
  註意命名時的習慣了嗎？使用“表名欄位1名欄位2名”的方式  

唯一索引

　　唯一索引就是唯一約束，與普通索引類似，除索引列的值必須唯一外跟普通索引無異。創建方法和普通索引類似。也就是說唯一約束的創建方式也可以由索引的創建方式創建。

　　唯一索引簡化了MySQL對這個索引的管理工作，這個索引也因此而變得更有效率。

　　唯一索引是MySQL會在有新記錄插入數據表時，自動檢查新記錄的這個欄位的值是否已經在某個記錄的這個欄位里出現過了。

主鍵索引

　　必須為主鍵欄位創建一個索引，這個索引就是所謂的”主索引”。主索引與唯一索引的唯一區別是：前者在定義時使用的關鍵字是PRIMARY而不是UNIQUE。

外鍵索引

　　如果為某個外鍵欄位定義了一個外鍵約束條件，MySQL就會定義一個內部索引來幫助自己以最有效率的方式去管理和使用外鍵約束條件。

全文索引

　　MySQL從3.23.23版開始支持全文索引和全文檢索，fulltext索引僅可用於 MyISAM 表；他們可以從CHAR、VARCHAR或TEXT列中作為CREATE TABLE語句的一部分被創建，或是隨後使用ALTER TABLE 或CREATE INDEX被添加。////對於較大的數據集，將你的資料輸入一個沒有FULLTEXT索引的表中，然後創建索引，其速度比把資料輸入現有FULLTEXT索引的速度更為快。不過切記對於大容量的數據表，生成全文索引是一個非常消耗時間非常消耗硬碟空間的做法。
文本欄位上的普通索引只能加快對出現在欄位內容最前面的字元串(也就是欄位內容開頭的字元)進行檢索操作。如果欄位里存放的是由幾個、甚至是多個單詞構成的較大段文字，普通索引就沒什麼作用了。這種檢索往往以LIKE %word%的形式出現，這對MySQL來說很複雜，如果需要處理的數據量很大，響應時間就會很長。
　　這類場合正是全文索引(full-text index)可以大顯身手的地方。在生成這種類型的索引時，MySQL將把在文本中出現的所有單詞創建為一份清單，查詢操作將根據這份清單去檢索有關的數據記錄。全文索引即可以隨數據表一同創建，也可以等日後有必要時再使用下麵這條命令添加：
　　ALTER TABLE table_name ADD FULLTEXT(column1, column2)
　　有了全文索引，就可以用SELECT查詢命令去檢索那些包含著一個或多個給定單詞的數據記錄了。下麵是這類查詢命令的基本語法：
　　SELECT * FROM table_name
　　WHERE MATCH(column1, column2) AGAINST(‘word1’, ‘word2’, ‘word3’)
　　上面這條命令將把column1和column2欄位里有word1、word2和word3的數據記錄全部查詢出來。

–創建表的適合添加全文索引  
CREATE TABLE table_name (  
id int(11) NOT NULL AUTO_INCREMENT ,  
content text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,  
PRIMARY KEY (id),  
FULLTEXT (content)  
);  
–修改表結構添加全文索引  
ALTER TABLE table_name ADD FULLTEXT index_name(column_name)  
–直接創建索引  
CREATE FULLTEXT INDEX index_name ON table_name (column_name)  

單列、多列索引

　　多個單列索引與單個多列索引的查詢效果不同，因為執行查詢時，MySQL只能使用一個索引，會從多個索引中選擇一個限制最為嚴格的索引。

組合(複合)索引

　　平時用的SQL查詢語句一般都有比較多的限制條件，所以為了進一步榨取MySQL的效率，就要考慮建立組合索引。例如上表中針對title和time建立一個組合索引：ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))。建立這樣的組合索引，其實是相當於分別建立了下麵兩組組合索引：
–title,time
–title
為什麼沒有time這樣的組合索引呢？這是因為MySQL組合索引“最左首碼”的結果。簡單的理解就是只從最左面的開始組合。並不是只要包含這兩列的查詢都會用到該組合索引，如下麵的幾個SQL所示
–使用到上面的索引
SELECT * FROM article WHREE title=’測試’ AND time=1234567890;
SELECT * FROM article WHREE title=’測試’;
–不使用上面的索引
SELECT * FROM article WHREE time=1234567890;

SQL高級查找

條件查詢

排序 order by

select * form table_name order by id desc    # 從大到小
select * form table_name order by id asc    # 從小到大

限制 limit

select * form table_name order by id asc limit 10    # 取查詢結果的前10條

select * form table_name order by id asc limit 20,10    # 取查詢結果從第20條開始 往後查10條

select * form table_name order by id asc limit 10 offset 20   # 取查詢結果從第20條開始 往後查10條

模糊查尋 like

%表示任意長度的字元 _表示單字元

select * from table_name where name like "張%"    # 以張開頭的

select * from table_name where name like "%張%"    # 包含張的

select * from table_name where name like "%張"    # 以張結尾的

select * from table_name where name like "張_"    # 以張開頭的 兩個字的  

select * from table_name where name like "_浩_%"    # 三個字的 並且中間是 浩 的 

日期查詢

select * form table_name where date_key between '2019-10-10' and '2019-10-10';

MySQL比較運算符

分組聚合

聚合函數：

sum() 求和

avg() 求平均（期望）

max() 最大值

min() 最小值

conut() 計數（求有多少個記錄）

對聚合函數的結果進行二次篩選的時候，條件 應該跟在having後 而不是 where 後面。

創建表：part 並插入數據：

create table part(id int,
name char(32));

創建表：person 並插入數據：

create table person(name char(32),
part_id int,
constraint fk_part_person foreign key(part_id) references part(id));

select part_id,count(part_id) from person group by(part_id);

內鏈接查詢

select * from person,part where part.id = person.part_id;  # 有時候性能可能會不如下麵 
select * from person inner join part on part.id = person.part_id;  # 性能可能會更好一些

註意 使用 where 子句定義連接條件比較簡單明瞭，而 inner join 語法是 ANSI SQL 的標準規範，使用 inner join 連接語法能夠確保不會忘記連接條件，而且 where 子句在某些時候會影響查詢的性能。

外鏈接查詢

select * from person left join part on part.id = person.part_id; 

select * from person right join part on part.id = person.part_id;  

註意 關於 left 和 right ：兩張表 連表查詢，left 就是left 左邊的表呃記錄要全部顯示出來 right 就是 right 右邊的表要全部現實出來

分組聚合查詢

每個部門有多少個人：

select part.name,count(person.name) from person left join part on part.id = person.part_id group by(part.name);