每一張表通常會有一個且只有一個主鍵,來表示每條數(shù)據(jù)的唯一性。
特性:值不能重復,不能為空 null
格式:create table test (ID int primary key)
主鍵 + 自增的寫法:
格式:create table test (ID int primary key auto_increment)
注意:自增只能配合主鍵來使用(如果單獨定義則會報錯)
mysql> alter table test modify Name varchar(12) comment '用戶名';
mysql> create table A(ID int primary key auto_increment,Name varchar(12),Department int); mysql> create table B(ID int primary key auto_increment,Name varchar(12)); mysql> insert into B(Name) values("財務"),("市場"); mysql> insert into A(Name,Department) values("張三",1),("李四",2),("王五",2);
mysql> select B.Name 部門,A.Name from B,A where B.ID=2 and A.Department=2;
數(shù)據(jù)庫:關系型數(shù)據(jù)庫(支持事務);非關系型數(shù)據(jù)庫(不支持)
一個事務中包含多條 SQL 語句,而且這些 SQL 語句之間存在一定的關系:
事務特性 | 作用 |
---|---|
原子性(Atomic) | 事務的所有操作,要么全部完成,要么全部不完成,不會結(jié)束在某個中間環(huán)節(jié)。 |
一致性(Consistency) | 事務開始之前和事務結(jié)束之后,數(shù)據(jù)庫的完整性限制未被破壞。 |
隔離性(Isolation) | 當多個事務并發(fā)訪問數(shù)據(jù)庫中的同一數(shù)據(jù)時,所表現(xiàn)出來的是相互關系。 |
持久性(Durability) | 事務完成之后,所做的修改會進行持久化保存,不會丟失。 |
區(qū)別:
隔離級別:
隔離級別 | 作用 |
---|---|
SERIALIZABLE (串行化) |
避免臟讀、不可重復讀、幻讀 |
REPEATABLE-READ (可重復讀) |
避免臟讀、不可重復讀 |
READ-COMMITTED (讀已提交) |
避免臟讀 |
READ-UNCOMMITTED (讀未提交) |
無作用 |
MySQL 支持上面 4 種隔離級別,默認為可重復讀。如若想修改隔離級別需: sed -i '/\[mysqld]/a transaction-isolation = SERIALIZABLE' /etc/my.cnf
mysql> show variables like '%tx_is%'; mysql> exit [root@MySQL ~]# sed -i '/\[mysqld]/a transaction-isolation = SERIALIZABLE' /etc/my.cnf [root@MySQL ~]# systemctl restart mysqld [root@MySQL ~]# mysql -uroot -p123123 -e "show variables like '%tx_is%';"
管理事務的三個命令:
mysql> create table C(ID int); mysql> insert into C values(1),(2); mysql> select * from C; mysql> BEGIN; mysql> insert into C values(3); mysql> COMMIT; mysql> select * from C;
mysql> show variables like 'autocommit'; #查看是否開啟自動提交事務 mysql> BEGIN; mysql> insert into C values(4) mysql> select * from C; mysql> exit [root@localhost ~]# mysql -uroot -p123123 -e "select * from Coco.C where ID=4"
set autocommit=0
:在數(shù)據(jù)庫中修改為臨時生效(如若想永久修改需 sed -i '/\[mysqld]/a autocommit=0' /etc/my.cnf
來修改)
mysql> set autocommit=0; mysql> select * from Coco.C; mysql> insert into Coco.C values(4); mysql> select * from Coco.C where ID=4; [root@localhost ~]# mysql -uroot -p123123 -e "select * from Coco.C where ID=4"
注意:
mysql> select ID as "編號",Name as "姓名",Department as "部門" from A where ID=1; mysql> select ID "編號",Name "姓名",Department "部門" from A where ID=1;
mysql> select distinct Department from A;
AND:邏輯與(條件都要滿足);OR:邏輯或(條件只需要滿足一個)。
mysql> select * from A where ID >= 3 and Department = 2; mysql> select * from A where ID >= 3 or Department = 2;
mysql> select * from A where ID in(1,3,4); mysql> select * from A where ID not in(1,3,4); mysql> select * from A where ID between 1 and 3;
mysql> select * from A where Name like "%三%"; mysql> select * from A where Name like "%三%" or Name like "%四";
mysql> select * from A order by ID desc; mysql> select * from A order by Department,ID desc;
mysql> select * from C; mysql> select * from C limit 2; mysql> select * from C limit 0,2;
到此這篇關于MySQL主鍵與事務的文章就介紹到這了,更多相關MySQL主鍵與事務內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!