MySQL Common operations statement

I. Data integrity
1. Entity integrity
2. Domain integrity (column integrity constraints):
A column (that is, a field) of a database table must conform to a specific data type or constraint.

Constraints:
Type constraint: ID int
Length constraint: ID int (3)
Non-NULL constraint: Username varchar (TEN) not NULL must have a value
Unique constraint: Idcardnum varchar unique can be null, and some words must be unique

User name: General website user name must be, and unique (do not let him as the primary key)
Username varchar (+) not NULL UNIQUE


Example:
CREATE TABLE User (
ID int PRIMARY KEY,
Username varchar () not NULL UNIQUE,
Idcardnum varchar () UNIQUE,
Gender varchar (TEN) not NULL
);

Topic: The primary key for an integer type grows automatically. The value of the primary key is automatically inserted by the database itself
CREATE TABLE T1 (
ID int PRIMARY KEY auto_increment, #主键自动增长
Name varchar (100)
);
INSERT into T1 (id,name) VALUES (1, ' A ');
INSERT into T1 (name) VALUES (' B '); Recommended
INSERT into T1 VALUES (null, ' C ');

Recommendation: Try not to have the database maintain primary keys. (Not all databases have auto-grow this feature)
To maintain primary keys by application

3, referential integrity (focus: multi-table. Foreign key)
3.1 Multi-table design
A, one-to-many (most frequently occurring)
CREATE TABLE Department (
ID int PRIMARY KEY,
Name varchar (100),
Addr varchar (100)
);
CREATE TABLE Employee (
ID int PRIMARY KEY,
Name varchar (100),
Gender varchar (10),
Salary float (8,2),
depart_id int,
CONSTRAINT depart_id FOREIGN KEY (depart_id) REFERENCES Department (ID)
);

Foreign key definition syntax:
CONSTRAINT foreign Key Name FOREIGN key (foreign key field) REFERENCES primary table name (primary key field);
FOREIGN key Name: arbitrarily defined, must be unique in the current library
Foreign key field: The field in the current table is a foreign key

　　

B, many-to-many (occurrence frequency second)
CREATE TABLE Teacher (
ID int PRIMARY KEY,
Name varchar (100),
Salary Float (8,2)
);
CREATE TABLE Student (
ID int PRIMARY KEY,
Name varchar (100),
Grade varchar (10)
);
#定义关系表
CREATE TABLE Teacher_student (
t_id int,
s_id int,
PRIMARY KEY (t_id,s_id),
CONSTRAINT t_id_fk FOREIGN KEY (t_id) REFERENCES teacher (ID),
CONSTRAINT s_id_fk FOREIGN KEY (s_id) REFERENCES student (ID)
);

INSERT into teacher VALUES (1, ' WYJ ', 10000);
INSERT into teacher VALUES (2, ' dh ', 10001);
INSERT into student VALUES (1, ' WF ', ' A ');
INSERT into Student VALUES (2, ' qhs ', ' A ');
INSERT into Teacher_student VALUES (+);
INSERT into Teacher_student VALUES;
INSERT into Teacher_student VALUES (2,1);
INSERT into Teacher_student VALUES (2,2);

C, one-to-one (there is almost no frequency)
CREATE TABLE Person (
ID int PRIMARY KEY,
Name varchar (100)
);
CREATE TABLE Id_card (
ID int PRIMARY KEY,
Num varchar (18),
CONSTRAINT ID_FK FOREIGN KEY (ID) REFERENCES person (ID)
);

Second, multi-table query

1. Connection Query
Basic syntax: Select XXX from T1 connection type T2 [on join condition][where filter condition]
Convention: T1 on the left side of the connection type, called the left table T2 is the right table
Connection type:
Cross join: Crossover connection
INNER JOIN: Explicit in-connection

Outer join: Left OUTER JOIN
Right outer join: Left OUTER JOIN

1.1 Cross-connect (Learn): crosses join
SELECT * from the customer cross JOIN orders;
The result returned is a Cartesian product that returns two table results. That is, table 1 has 5, table 2 has 7, the return of the 5*7=35 bar
1.2 Internal connection: Inner JOIN (natural connection)
A, implicit internal connection: Do not use the ON keyword (that is, do not explicitly specify the connection condition), using the Where
Check information and order information for customers with orders
SELECT * FROM Customer c,orders as O WHERE c.id=o.customer_id;
b, explicit internal connection: using the ON keyword
Check information and order information for customers with orders
SELECT * FROM customer C INNER JOIN orders o on c.id=o.customer_id;
Inquire about customer's information and order information of order amount above 200 yuan
SELECT * FROM customer C INNER JOIN orders o c.id=o.customer_id WHERE o.price>=200;
1.3 External connection: outer JOIN
A, LEFT OUTER join: Returns the result that satisfies the join condition, and returns other records remaining in the left table
Query customer information while showing his order
SELECT * FROM customer C left OUTER JOIN orders o on c.id=o.customer_id;
Query all employees, the name of the department where the printing is located
SELECT * FROM employee E left OUTER joins Department D on E.depart_id=d.id;
b, right outer join: Returns the result that satisfies the join condition, and returns other records remaining in the right table
Query customer information while showing his order
SELECT * FROM Orders O-OUTER JOIN customer C on c.id=o.customer_id;
Query all orders and display his customer information
SELECT * from the customer C right OUTER JOIN orders o on c.id=o.customer_id;
2, sub-query (simple):
Subquery: Nested query (inner statement). One query statement is a condition of another query statement. The subquery's statement must be enclosed within parentheses
SELECT * from t1 where id= (select ID from T2);

The name of the student whose ID is 2 has been taught by the teacher
Way One: Multiple statements
Select s_id from teacher_student where t_id=2;
SELECT * from student where ID in (;
Mode Two: Connection query
Select s.* from Teacher_student ts,student s where ts.s_id=s.id and ts.t_id=2;
Method Three: Subquery (a single row result)
SELECT * from student where ID in (select s_id from teacher_student where t_id=2);

Query all order information with the name ' Edison ' (sub-query)
SELECT * FROM Orders where customer_id= (select ID from customer where name= ' Edison ');

3. Joint query: union
A federated query merges the query results of two query statements, removes the duplicate rows from them, and returns the results of the query without repeating rows of data.

Take the set of multiple statements (no duplicate records)
Query customer id=1 and Order Amount >=200 order information

SELECT * from orders where price>=200 and customer_id=1;

Check the order information of customer id=1 or order Amount >=200
SELECT * FROM orders where price>=200 or customer_id=1;
Use a federated query: query the order information for customer id=1 or order Amount >=200
SELECT * FROM orders where price>=200 UNION SELECT * from orders where customer_id=1;

4. Report query (built-in function of database)
Report query group statistics on rows of data
[Select ...] From ... [Where ...] [GROUP By ... [Having ...] [Order BY ...]
GROUP BY: grouping by those fields
Having: Filtering the contents after grouping (where is not available)


How many students are there in a class?
Mysql>select Count (*) from student;
How many students with a statistical math score greater than 90?
Mysql>select COUNT (*) from student where math>90;
What is the number of people with total statistics greater than 250?
Mysql>select COUNT (*) from student where (math+chinese+english) >250;
Statistic a class math total?
Mysql>select SUM (math) from student;
Statistics of a class of Chinese, English, mathematics, the total scores of various subjects
Mysql>select sum (math), SUM (Chinese), sum (中文版) from student;
Statistics a class of Chinese, English, mathematics, the sum of the results
Mysql>select SUM (math+chinese+english) from student;
Statistic the average score of a class's Chinese score
Mysql>select SUM (Chinese)/count (*) from student;

Ask for a class math average score?
Mysql>select AVG (math) from student;
To find the average score of a class total
Msyql>select AVG (math+chinese+english) from student;

To find the highest and lowest score of class language
Mysql>select MAX (Chinese) from student;
Mysql>select MIN (Chinese) from student;

Note: You cannot use the keyword as the table name or column name, and if you must use it, use the anti-quote ' (below the ESC key) to cause

Displays the total price of each type of item after sorting the items in the order form
Mysql>select product,sum (price) from the orders GROUP by product;
Inquire about the purchase of several kinds of goods, and each kind of total price more than 100 of goods
Mysql>select product,sum (price) from orders GROUP by product have sum (price) >100;


Third, database backup and recovery (MySQL)
1. Backing up the database (table structure and table data)
C:/>mysqldump-h localhost-u root-p Mydb1>d:/mydb1.sql

2, restore the database: The database name must be created manually, and select
Method One: Import data inside MySQL
Mysql>create database mydb1;
Mysql>use mydb1;
Mysql>source D:/mydb1.sql;
Mode two: Do not enter MySQL for data recovery
C:/>mysql-u root-p Mydb1<d:/mydb1.sql

MySQL Common operations statement