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);

標籤：MySQL

上一篇：有個問題困擾很久，求大神解答

下一篇：MySQL 字符集和校驗規則作業原理