MySQL常用操作和主從配置

FuyunWang發表於2019-02-19
MySql

介紹關於MySQL的常用操作和注意事項

MySQL的基本操作,增刪改查

按照資料庫的操作物件分成3類,

資料庫操作

1.建立資料庫:create database 資料庫名稱(庫選項)

  • 庫選項,用來約束資料庫的。
  • 字符集設定: charset/character set 具體字符集(資料儲存的編碼格式),常用的字符集有GBK和UTF8
  • 校對集設定:collate 具體校對集(資料比較的規則)
  • --是單行註釋,#也可以作為註釋
  • 建立資料庫:create database mvcdemo charset utf8;
  • 注意在控制檯使用mysql資料庫的時候首先應該指定其字符集:set names gbk

2.檢視資料庫:show databases

  • 檢視指定的資料庫(模糊查詢),%表示匹配多個字元,_表示匹配任意一個字元:show databases like ``
  • 注意轉移字元使用
  • 檢視資料庫的建立語句:show create database mvcdemo

3.更新資料庫:

  • 資料庫名字不可以修改,資料庫的修改僅限於庫選項,庫選項包括字符集和校對集
  • alter database 資料庫的名字 庫選項 —————>alter database mvcdemo charset gbk

4.刪除資料庫:drop database mvcdemo

資料庫中的表的操作

  1. 建立表:create table [if not exists] 表名(欄位名字 列型別)[表選項] 其中if not exists表示如果表存在就不執行後面的語句,否則就執行後面的語句並建立表

  2. 表選項:控制表的表現:

    • 字符集:charset/character set 具體字符集
    • collate:校對集
    • 儲存引擎:engine:具體的儲存引擎(innodb和myisam)預設是innodb
      create table if not exists student(name varchar(10),gender varchar(10),number varchar(10),age int)charset utf8;
    • 檢視有結構的表的建立語句:show create table studentG
    • 檢視錶的結構:desc student/describe student/show columns from student

3.修改表:

  • 修改表名:rename table 舊錶名 to 新表名
  • 修改表選項:字符集、校對集和儲存引擎。alter table my_student charset=gbk
  • 修改表欄位(欄位的新增,修改,重新命名,刪除)
  • 新增欄位:alter table 表名 add [column] 欄位名 資料型別 [列屬性] [位置]
    • 其中欄位名可以存在表中的任意的位置,first:第一個位置;after欄位名:預設是在最後一個欄位之後
  • 給學生表增加id:alter table my_student add column id int first
  • 修改欄位:修改欄位通常是指修改屬性或者說是資料型別。
    alter table 表名 modify 欄位名 資料型別 [屬性] [位置]
    • 將學號欄位變成固定長度且放在第二位:
    • alter table my_student modify number char(10) after id
    • 重新命名欄位
    • alter table my_student change gender sex varchar(10)
    • 刪除欄位
    • alter table my_student drop age

4.刪除表:drop table my_student

5.刪除資料:delete from my_student

檢視資料庫伺服器支援哪一些字符集:

show character set

檢視資料庫預設使用的對外資料處理的字符集:

show variables like `character_set%`

檢視所有的校對集

show collation

MySQL中的資料型別

  1. 數值型:數值型包括整數型和小數型。

    • Tinyint：迷你整型,使用一個位元組儲存,表示的狀態可以達到256種
    • Smallint:小整型,使用2個位元組儲存,表示的狀態可以達到65536種
    • mediumint:中整型,使用3個位元組儲存
    • int:標準整型,使用4個位元組儲存
    • Bigint:大整型,使用8個位元組儲存

  2. 小數型:帶有小數點或者範圍超出整型的資料型別。

    • 在SQL中,將小數型細分成2種，浮點型和定點型

    • 浮點型:小數點浮動,精度有限,而且會丟失精度

    • 定點型:小數點固定,精度固定,不會丟失精度

    • 浮點型資料是一種精度型資料,因為超出精度之後會丟失精度(自動四捨五入),其中float(M,D)表示有M位有效數字,其中D表示有D位小數

  3. 時間日期型:

    • Datetime:時間日期,格式是YYYY-mm-dd HH:ii:ss表示的範圍是從1000到9999,年有0值。
    • Date:日期就是指的時間日期型中的日期部分
    • Time:時間段，指定的，某個區間之間,-時間到+時間
    • Timestamp:從1970開始計算的時間,其中的格式與Datetime時間日期型別完全一致
    • Year:年份
    • create table my_date(d1 datetime,d2 date,d3 time,d4 timestamp,d5 year) charset utf8
    • insert into my_date values(`2017-4-11 18:54:50`,`2017-4-11`,`18:55:00`,`2017-4-11 18:54:50`,2017);

#MySQL中的欄位屬性

  1. 主鍵:primary key。
    `

     mysql> create table my_pri1(
 name varchar(20) not null comment `姓名`,
 number char(10) primary key comment `學號,不能重複`)charset utf8;
 

 mysql> create table my_pri2(
 -> number char(10) comment `學號`,
 -> course char(10) comment `課程程式碼`,
 -> score tinyint unsigned default 60 comment `成績`,
 -> -- 增加複合主鍵
 -> primary key(number,course));
 

 追加主鍵方式一:alter table my_pri3 modify course char(10) primary key;
 追加主鍵方式二:alter table my_pri3 add primary key(course);

 刪除主鍵:alter table 表名 drop primary key;
複製程式碼

2. 檢視自增長:

	show variables like `auto_increment%`;檢視起始值和步長
	set auto_increment_increment=5;//設定自增長的步長為5	
`
複製程式碼

  1. 刪除唯一鍵:
    `

     alter table 表名 drop index 索引的名字;//索引可以通過建表語句來得到,預設是欄位的名字
複製程式碼

    `

MySQL中的觸發器:

觸發器:

觸發器有三個要素,分別是事件的型別、事件的觸發時機和事件的觸發物件。其中，事件的型別有三種，分別是對錶的增刪改；觸發的時間有兩個分別是操作之前和之後；觸發的物件指代的是表中的某一條記錄。注意一張表中只能最多有6個觸發器。
複製程式碼

建立觸發器:

create trigger 觸發器名 觸發時間 事件型別 on 觸發物件(表名) for each row begin     		
	每一行都有一個語句的結束符;
end; 

create table my_goods(id int primary key auto_increment,name varchar(20) not null,price decimal(10,2) default 1,inv int comment `庫存數量`)charset utf8;

insert into my_goods(name,price,inv) values(`iphone6ps`,5888,102),(`戴爾筆記本`,5999,76);

create table my_order(id int primary key auto_increment,g_id int not null comment `商品id`,g_number int comment `商品數量`)charset utf8;

-- 觸發器,訂單生成一個商品的數量就減少
-- 指定臨時語句的結束符
delimiter $$

create trigger after_order after insert 
on my_order for each row
begin
update my_goods set inv=inv-1 where id=2;
end
$$
delimiter ;

-- 檢視所有觸發器
show triggers ;
複製程式碼

MySQL中的外來鍵

新建外來鍵

	create table my_foreign1(
		id int primary key auto_increment,
		name varchar(20) comment `學生姓名`,
		c_id int comment `班級id`,
		-- 增加外來鍵
		foreign key(c_id) references my_class(id)
	)charset utf8;
	
	//在建立表之後增加外來鍵
	alter table 表名 add [constraint 外來鍵名字] foreign key(外來鍵欄位) references 父表(主鍵欄位);
	
`
複製程式碼

刪除外來鍵

外來鍵不可修改,只能先刪除然後再新增.
alter table my_foreign drop foreign key 外來鍵名
複製程式碼

MySQL中的檢視

建立檢視

create view 檢視名 as select語句

create view my_student_v1 as select * from student;

create view my_book_v1 as select * from book;
複製程式碼

檢視內容不可以修改,但是檢視的來源可以修改,結構也可以修改

alter view my_v1 as select name from book;
複製程式碼

刪除檢視:

drop view 檢視名 
複製程式碼

mysql的關鍵字:

  1. order by。在sql語句中是針對於具體的列進行排序,預設是進行升序的排序並顯示所查資料。如果是多列共同orderby那麼是隻有在前面的列完全相同的時候才考慮後面的列是否有序並進行排序。

MySQL伺服器的主從同步:

通過MySQL的主從同步的配置,實現資料庫的讀寫分離,從而提高過大的壓力下資料庫的可靠性。

主從同步的理論支援:

  1. Master首先將主資料庫伺服器中對資料的操作記錄到二進位制日誌(Binary log)當中,換句話說,Mysql將每次一操作的事務都儲存在了二進位制檔案(Binary log)當中,這一次的操作也叫做一次Binary log 事件;
  2. Slave子伺服器會在本地開啟一個IO執行緒,該IO執行緒會在Master主伺服器上開啟一個連線,然後將遠端的Binary log檔案拷貝到本地,如果讀取Binary log得到的資訊與Master保持的是一致的,此時子伺服器的IO執行緒就會睡眠避免在Master上產生事件。最後,IO執行緒將事件寫入到Relay log之中。
  3. Slave子伺服器重做Relay log中的事件。就是主MySQL伺服器中的事務操作會在子伺服器上重新的做一遍。子伺服器上會建立一個SQL Thread執行緒專門完成這一步的操作。

首先準備兩臺已經安裝好Mysql的主機,我這裡採用的是虛擬機器,ip地址分別是:192.168.33.13和192.168.33.14。其中192.168.33.13作為主伺服器,192.168.33.14作為從伺服器來實現主從同步。

  1. 首先配置主伺服器:192.168.33.13:vim /etc/my.cnf

     			[mysqld]
 			server-id=1
 			log-bin=master-bin
 			log-bin-index=master-bin.index
 			datadir=/var/lib/mysql
 			socket=/var/lib/mysql/mysql.sock
 			user=mysql
 			# Disabling symbolic-links is recommended to prevent assorted security risks
 			symbolic-links=0
 			
 			[mysqld_safe]
 			log-error=/var/log/mysqld.log
 			pid-file=/var/run/mysqld/mysqld.pid
複製程式碼

之後service mysqld restart重啟mysql,登入本地伺服器檢視log-bin檔案:show master status;

  1. 然後配置從伺服器:192.168.33.14:vim /etc/my.cnf

     			[mysqld]
 			server-id=2
 			relay-log-index=slave-relay-bin.index
 			relay-log=slave-relay-bin
 			datadir=/var/lib/mysql
 			socket=/var/lib/mysql/mysql.sock
 			user=mysql
 			# Disabling symbolic-links is recommended to prevent assorted security risks
 			symbolic-links=0
 			
 			[mysqld_safe]
 			log-error=/var/log/mysqld.log
 			pid-file=/var/run/mysqld/mysqld.pid
複製程式碼

之後service mysqld restart重啟mysql,登入本地伺服器檢視資訊:show slave status;

  1. 在主庫上配置一個從庫的賬號用於登入實現主從同步:

    grant replication slave on *.* to `beautifulsoup`@`192.168.33.14` identified by `password`;

    flush privileges;

    在從庫上配置主庫連線的資訊:
    change master to master_host=`192.168.33.13`,master_port=3306,master_user=`beautifulsoup`,master_password=`password`,master_log_file=`master-bin.000001`,master_log_pos=0;

    start slave;

相關文章