Python-study-39

Integrity constraints for tables

the complete syntax for creating a table#Syntax:CREATE table library name. Table name (field name 1 type [(width) constraint], field name 2 Type [(width) constraint], field name 3 Type [(width) constraint]); constraint: is an additional restriction on a field beyond the data type#Note:1, after the last field, you cannot add a comma2. In the same table, the field names are not the same3. Width and constraints are optional, field names and types are required two notNULL is used in conjunction with default: NOT NULL, if empty, CREATE TABLE t1 (ID int primary key auto_increment, name varchar (16) notnull, sex enum ('male','female') notNull default'male'INSERT into T1 (name) VALUES ('Egon'),('LXX'),('Alex'); three unique key#limit only unlimited empty#the default constraint name and field name are the same, you can specify the constraint name with constraint#Two field names can be federated uniqueCREATE TABLE t2 (x int unique), CREATE table t3 (x int, y varchar (5), unique key (x)), CREATE table t4 (x int, y varchar (5), constraint uni_x unique key (x)#), CREATE TABLE service (IP varchar (15), port int, unique key (Ip,port)), insert into service values ('1.1.1.1', 3306),('1.1.1.1', 3306) Four primary key station in the constraint angle see primary key= notNULL unique after the establishment of the table, it must be noted that:1, must have and have only one primary key2, usually the ID field is set as the primary key3, ID int PRIMARY Key auto_increment This paragraph can be used as a fixed usage4, if there is no primary key, automatically in the field to find a non-empty and unique field key, and no, with the hidden5, the storage engine we use is typically InnoDB, which requires that a primary key be6, Key =index storage engine increases query speed with primary key as index7, Union primary key, not commonly used, several fields made a primary key CREATE TABLE T5 (ID int primary key auto_increment); Five foreign key: foreign key coupling degree, cause bad expansion, try not to use analogy object programming later The duck thought in the Limit association table the value of a field must be from a field in the associated table.#FOREIGN Key Note:#1. The associated field must be a key, usually an ID field#2. When creating a table: The associated table must be established before the associated table can be establishedCREATE TABLE DEP (ID int primary KEY auto_increment, dname varchar (20), Info varchar (50) ; CREATE table emp (ID int primary key auto_increment, name varchar (15), age int., dep_id int, foreign KEY (dep_id) references dep (ID) on UPDATE cascade on DELETE cascade); #3. When inserting a record: You must insert a record into the associated table before you can insert a record into the associated tableINSERT INTO DEP (Dname,info) VALUES ('IT','Technical Ability Limited Department XXX'),('Sale','literacy is not high'),('HR','Department of the people who can't recruit'INSERT INTO EMP (NAME,AGE,DEP_ID) VALUES ('Egon', 18,1),('Alex', 28,2),('WSJ', 38,2),('LXX', 30,1),('Xiaohou', 18,3);p S: Delete: You should delete the records in the associated table EMP before deleting the records corresponding to the associated table

View Code

Storage Engine

#Supplemental Storage Engine:CREATE table T12 (x int) engine='MyISAM'CREATE table t13 (x int) engine='InnoDB'CREATE table t14 (x int) engine='Memory'CREATE TABLE t15 (x int) engine='blackhole'MYDQL different tables, different table organization structure, need to use different storage engine to deal with the analogy relationship is actually a bunch of different functions of code InnoDB=Text Editor: Working with text formats MyISAM=MP3 player: Handles show engines in MP3 format; View all engine InnoDB: Index Organization table Index+Data############### #参考http://www.cnblogs.com/linhaifeng/articles/7213670. HTML in real life we used to store data in different types of files, each file type corresponding to the different processing mechanism: for example, processing text with the TXT type, processing the table with Excel, processing the image in a database such as PNG tables should have different types, table type is different, will correspond to MySQL's different access mechanisms, and the table type is also known as the storage engine. 

View Code

The relationship between tables

Relationships between tables: the extension of the foreign key:#tips for finding the two-sheet relationshipEMP DEP#1, the first stand in the angle of the left table: to find the left side of the EMP multiple records can correspond to a record of the right table DEPtranslation: Can multiple employees belong to one department#2, then stand on the right side of the table angle: To find the right table dep multiple records can correspond to the left table emp a recordtranslation: Can multiple departments have the same employee#Many-to-one: the judgment of the result#1, if only one-way multi-to-one set up, then the ultimate relationship is more to a#2, add a field dep_id in the EMP table, the Field Foreign Key association DEP (ID)#Many-to-many: the judgment of the result#1, two-way multi-to one is many-to-many#2, need to create a third table, there is a field value FK left table, a field value FK right TableCREATE table author (id int primary key auto_increment, name varchar (16), age int); CREATE table book (ID int primary KEY auto_increment, bname varchar (20), price int); CREATE table Author2book (ID int primary key auto_increment, author_id int, book_id int, Foreign KEY (author_id) references author (id) on the update cascade on the DELETE CASCADE, foreign key (book_id) references book ( ID) on the update cascade on DELETE cascade);#one to one: no reference tips, the left table is a single piece of data corresponding to the right table of a recordFk+unique

View Code

Modify, copy, delete a table

http://www.cnblogs.com/linhaifeng/articles/7232894.html#_label1

View Code

Last Class Review

Last Lesson review:1, basic SQL statement Library add CREATE Database db1 charset UTF8;            Delete drop database db1;            Change ALTER DATABASE DB1 CharSet GBK;                Check show create Database db1;        show databases;            Table use DB1;            Select Database (); Add CREATE TABLE t1 (ID int,name varchar (15));            Delete the drop table T1; Change ALTER TABLE t1 add sex enum ('male','female'); ALTER TABLE T1 modify name varchar (16); ALTER TABLE t1 change name name varchar (16);                Check show tables;        Show CREATE table T1; Record add insert into T1 values (1,'Egon'), (2,'Egon2'), (3,'Egon3'); Delete Delete fromT1 where ID > 3;            Empty table: Truncate t1; Change update db1.t1 set name='XXX'Where ID > 3; Check1Egon12Egon23Egon34Egon45egon5 Select ID fromDB1.T1 where ID > 3; 2, creating the table's data type today content:1, table integrity constraint table between three kinds of relationships many to one many-to-many modification table copy table Delete Table preview:2, single-table query3, multi-table Query jobs: http:Www.cnblogs.com/linhaifeng/articles/7238814.html#_label7

View Code

Python-study-39