MySQL Foundation Part II, for future Python use

#外键 table linked to table column name MUL foreign key referential integrity cannot add a field that does not have another table
CREATE TABLE Study_record (
ID int auto_increment PRIMARY key,
Day int is not NULL,
Status char (+) NOT NULL,
stu_id int NOT NULL,
#创建外键
Key ' Fk_student_key ' (' stu_id '), #起个名字
Constraint ' Fk_student_key ' foreign Key ("stu_id") References ' student ' (' ID ')
)
#若表1是参照的 Table 2 is followed if Delete 2 is possible! But if you delete 1, it's not possible.


#NULL Value Processing
#IS NULL returns TRUE if the current value is null
#IS NOT NULL is not empty
#不能用 = NULL or! = NULL always remembers null value and any comparison returns false

#mysql连接多表查询
# LEFT Join
#right Join
Get two table match records #inner join connection
#full Join

CREATE Table A (a int not null)
CREATE TABLE B (b int not null)
Insert into a (a) values (1);
Insert into a (a) values (2);
Insert into a (a) values (3);

INSERT INTO B (b) values (4);
INSERT INTO B (b) VALUES (5);
INSERT INTO B (b) VALUES (6);
Join Inner joins within the intersection
SELECT * FROM A inner join B on a.a = b.b;
Select A.*, b.* from A, b where a.a=b.b;

Find difference set LEFT JOIN
SELECT * from A LEFT join B on a.a = b.b;

Find the left join of the differential set reverse
SELECT * from A right join B on a.a = b.b;

Seek and set full join MySQL is not supported for full join general and set useless
SELECT * from a full join B on a.a = b.b;

and set MySQL
SELECT * from a LEFT join B in A.A = B.B Union SELECT * from a RIGHT join B on a.a = b.b;


A transaction must meet four conditions
Atomicity (atomicity) either succeeds or withdraws
Stability (consistency) transaction recall when there is illegal data
Isolation isolation transactions run independently of a transacted result affects a transaction name other transactions are recalled
Reliability durability hardware crashes when InnoDB data is reconstructed using log file to modify reliable and high speed incompatible
#数据库 batches or both succeed or fail.
Modify Delete Insert only requires transaction
Default is INNODB only Innodb support transaction MySQL sum does not support transactions

Start a transaction begin;
SELECT * from student;
INSERT into student (name, register_date, gender) VALUES (' LJC ', ' n ', ' M ');
INSERT into student (name, register_date, gender) VALUES (' LJC ', ' n ', ' M ');
has been inserted into the table
If there is a problem, roll back the input rollback; Or it will be automatically rolled back (ID will be self-increasing but not affected)

Begin
INSERT into student (name, register_date, gender) VALUES (' LJC ', ' n ', ' M ');
INSERT into student (name, register_date, gender) VALUES (' LJC ', ' n ', ' M ');
Commit Confirm Commit


Index #使表查询更快 Index can have a lot of!! MySQL index is implemented by binary tree (B tree) and hash almost
Single-column index and combined index (two columns) just want to add up to two columns unique!!!
When you create an index, remember to query the query with an index!!
The index is also a table with a list of sorted numbers
But too many indexes will also have the Defect Update table, also need to update the number table, need to rearrange the data table!! So the update speed slows down
Increase disk space!!
The primary key is the index!!!

View index show index from table name
Show index from table name;
CREATE index index_name on table name (column name ()) #index_name is the key_name is not related to the original table arbitrarily set
Create INDEX index_name on student (name (32))

Modify Table Structure Additions
Alter student Add index index_name on (column name ())

CREATE TABLE Additions
CREATE TABLE student (ID int not NULL, index index_name (column name (length)))
CREATE TABLE student (ID int not NULL, index Index_name (name (32))); ************

Delete Index
Drop index index_name on table name; Show index from student
Drop index index_name on student;

Unique indexed index column values must be unique can have a null value primary key is the difference is to add a unique
Alter student add unique index_name on (username (32))
CREATE TABLE student (unique index_name (username (32))
Create unique index index_name on student (username (32))

Use alter to add and remove primary keys
ALTER TABLE student Modify ID int not NULL;
ALTER TABLE student Add primary key (ID);

Alter DELETE PRIMARY key
ALTER TABLE student drop primary key; Delete When index name is not known
ALTER TABLE student drop PRIMARY key (ID); ？？？？？？？？？？？

Show index from STUDENT\G does not need to be added; Display a list of values list names

MySQL Foundation Part II, for future Python use