MySQL Base operation statement

1. Data Entry integrity: Create a data table to add constraints to it: Classification: entity integrity, domain integrity, referential integrity

entity: A row in a table ( one record ) represents an entity ( Entity )

The role of entity integrity: identifies each row of data that is not duplicated. A primary key; uniquely identifies the database each row is a column that uniquely identifies each record in the database, in each table

Constraint: PRIMARY KEY constraint (primary key) data unique, cannot be null, first create primary key to self-increment attribute, primary key value same SQL error

Unique constraint (unique), autogrow (auto_increment)

2. Create a PRIMARY KEY constraint three ways:

　　　

The Federated primary Key is essentially a primary key PRIMARY key (ID,CLASSID);

　　3. Add a primary key after creating the table: ALTER table student Add PRIMARY key (ID)

4. Unique constraint: Gender unique: This column value cannot have duplicate elements, cannot have more than one NULL, allows null values

　　　

5. Auto-Grow column:

3. Referential integrity: FOREIGN key: The table has the primary key of the other table, if you want to have a foreign key, you must have an existing primary key, the primary key and foreign key type must be consistent

Student is the parent table, score is a child table, and a FOREIGN key constraint is added to the child table: the sub-tables do not allow values outside the main table:

FOREIGN key keyword: FOREIGN key FOREIGN KEY constraint constraint constraint name primary key between constraint keyword appearances

FOREIGN KEY constraint two ways: Create Time 2.ALTER Modify:

int pirmary key,name varchar (null, sex varchar (default  ' Male '); Create TABLE score (     int ,     int ,     int ,-- the data type of the foreign key column must be consistent with the type of the primary key    CONSTRAINT fk_score_sid foreign KEY (SID) references student (ID));

2. ALTER: keyword constarint constraint name references Association

ALTER TABLE score ADD CONSTRAINT fk_stu_score FOREIGN KEY (SID) REFERENCES Stu (SID);

4. The relationship between tables and tables:

　　　　1, one-to-ones relationship: the relationship between people and identity card, master-slave relationship design: T_person, T_card said ID card

　　　　　　Add a foreign key column to the ①t_card table, and add a foreign key unique constraint

② Adds a foreign key constraint to the primary key of the T_card table, that is, the t_card table primary key is also a foreign key, reference and Constain keywords

2. One-to-many:to many and many to one, this is from which angle to see. T_userand thet_sectionthe relationship, fromT_userto see is a pair of more, and fromt_sectionpoint of view is more to one! This situation is created in the multi-party foreign key, from the table SID is not the primary key

3. Many-to-many: For example T_stu and the T_teacher table, that a student can have more A teacher , and a teacher can have more than one student. This situation usually requires creating an intermediate table to handle many-to-many relationships, creating an intermediate table, giving two foreign keys, a corresponding t_stu, and another corresponding T_tea

5. Why to split the table: 1. Resolve table redundancy data, 2.

--------------------------------------------------------------------------------------------------------------- ----------

1. Multi-Table query: 1. Merge result set Union, UNION ALL

2. Connection query join 3. Subquery

1. Bulk INSERT; INSERT into A VALUES (' A ', Ten), (' B ', +), (' C ', 30);

Merge Query set: SELECT * FROM Table_a UNION SELECT * from table_b;//Note: merge query column data types must be consistent

Union vs UNION ALL difference: the former queries two unions, UNION ALL displays all two tables of data

2. Connection query: very important : inner connection, outer connection, natural connection

Join query to find out the product of multiple tables, T1*T2:

From the above results show that the direct connection query appears Cartesian effect, there are multiple extension sets, there are a lot of duplicate sets, in the query process must add filtering conditions to remove the Cartesian effect through the relationship between the main foreign key to remove redundant information

1. use the primary foreign key relationship as a condition to remove useless information: Note: Multiple table queries must specify a subordinate table for the column: Emp.deptno;

SELECT * from Emp,dept, where Emp.deptno=dept.deptno;

2.1 The above connection statement is an inner join, but not a standard query in SQL, standard query statement in SQL: on only for internal connections MySQL Default connection method is the internal connection

SELECT * FORM emp e INNER JOIN Dept D on E.deptno=  d.deptno;//inside the connection keyword Innerjoin,on E,d alias, as omitted, if * replaced E.score,e.name

2.2 External links: Connect from left, connect from right, full outer connection

Left Outer connection:

SELECT * FROM student S left joins score C on S.stuid=c.stuid;

The left connection is the first to query out the left table (that is, the left table is the main), and then query the right table, the right table to meet the conditions of the display, do not meet the condition of the display NULL. Left and right tables are displayed separately

Connection Query Experience : Two tables at least one primary foreign key condition, three tables need at least two primary foreign key relationship

Connection Unlimited with two tables, connection query can also be three, four, even N -Table connection query. It is not usually possible to connect queries that require the entire Cartesian product, but only a subset of them, so you need to use conditions to remove unwanted records. This condition is most often removed using the primary foreign key relationship.

Two table connection query must have a primary foreign key relationship, three table connection query there must be two primary foreign key relationship, so in everyone is not very familiar with the connection query, the first to learn to remove the useless Cartesian product, then is the main foreign key relationship as a condition to deal with. If two tables are queried, then there is at least one primary foreign key condition, and three table connections have at least two primary foreign key conditions .

3. Natural connection: Connection query obviously appears useless Cartesian product, need to use the relationship between the main foreign key removal useless, natural connection automatically find this type of relationship: conditions:

1. The name in two tables is exactly the same as the data type, and the connection query requires only the same data type.

4. Sub-query: Very Important

A SELECT statement nested another SELECT, the subquery is a nested query, if there is two or more than two select in a statement, then the subquery　　　　　

The location where the subquery appears:

• Where , as a bar is a part of the condition being queried;
• From after, make a table;

L can also use the following keywords when a subquery appears as a condition after the Where:

• Any
• All

L sub-query result set form:

• Single row (for conditions)
• Single-row multi-column (for conditions)
• Multiline single row (for conditions)
• Multi-row multiple columns (for tables)

Practice:

1. Query with Scott's Department staff: find out which department Scott belongs to

SELECT * from emp WHERE enam= ' SCOTT ';//

SELECT deptno from emp where ename= ' SCOTT ';

Above statement two query statements are merged select * from EMP where deptno= (select Deptno from emp where enam= ' SCOTT ');

2. Pay more for salary than JONES

SELECT sal from emp WHERE ename= ' JSON '; SELECT * from EMP WHERE sal> ($ First Step query statement)

Result: SELECT * from emp where sal > (SELECT sal from emp where ename= ' JONES ')

3. Information on wages higher than the 30-door owner;

SELECT * from emp where sal> (SELECT MAX (SAL) from EMP where deptno=30);

4. query work and salary with Martin(Martin) exactly the same employee information multiple phase equivalents in

SELECT * from emp where (job,sal) in (select Job,sal from emp where ename= ' Martn ')

5. Check employee name, employee's salary, department name, department address , employee number 7788

SELECT e.ename,e.sal,d.dname,d.loc from emp e,dept d WHERE e.deptno=d.deptno and ename= ' 7788 '; This one

Using subqueries: Select e.ename,e.sal,d.dname,d.locfrom emp, (SELECT * from dept) d WHERE E.deptno=d.deptno and Ename= ' 7788 ';

6. Database backup:

Generate SQL Script: mysqldump-uroot-p123 database name > Path mysqldump keep the code established by the database, the code does not exist the statement that created the database

Database for recovery: If the database SOURCE C:\mysql.sql first

Do not use the source Mysql-u root-p123 in this way mydb1<d:\mydb1.sql;

MySQL Base operation statement