MYSQL Little Note

MYSQL Easy to read
1.sql Overview
1. What is SQL?
2.sql development process?
What is the relationship between 3.sql standards and dialects?
4. Common database?
5.MYSQL database installation?

2. Key Concepts
Table Structure-----------Properties of the > class
Line-------------> An Object

3. Build the library code
1.create database name
2. With character set
3. Collate validation rules with validation rules
Create database name character Set UTF8 collate utf8_general_ci;
4.show CREATE DATABASE name
5. Show all databases
6. Delete a database
7. Use a Database

4. Build a table
*create Table Name (
Column name Type,
Column name Type
);

2. View the code for the build table
3. View all the tables
4. View the table's Details DESC table name
5. Common operations for columns in a table
1. Add a column
2. Delete a column
3. Modifying the data type of a column
4. Modify column names
6. Modify the table name
7. Delete Table structure

Insert Data
Insert into table name (column name) values (value, value);
*6. Deleting data
Delete from table name where condition
TRUNCATE TABLE name
*7. Modifying data:
Update table name set column name = new value, column name = new value WHERE condition
*8. Viewing data in a table
SELECT * FROM table name where condition GROUP by group have (post-group condition) Order by sort field (ASC|DESC)

9. Data integrity
1. Domain Integrity
NOT NULL
Unique

2. Entity integrity
Primary KEY (autogrow): Uniqueness, non-nullability
Primary key

3. Referential integrity
FOREIGN key
Constraint foreign Key Name foreign key (foreign key field name) References primary key table (primary key name)
ALTER TABLE name drop Foregin key foreign key name;
Table-to-table relationships:
One-to-many

Many-to-many

One
1. Foreign key + UNIQUE constraint
2. Primary key + foreign key

*10. Connection Query
1. Cross-Connect
A Cross Join B
From a A B
2. Internal connection
From A inner join B on (a.id=b.bid) Where condition
3. External connection
Left outer connection
From A left join B on (a.id = b.bid) Where condition
Right outer connection
From A right join B on (a.id = b.bid) Where condition

4. Sub-query
Select Name= (select name from B) from A where ID in (select ID from b where condition)

Select (subquery) from (subquery) A where ID in (subquery)

5. Joint queries
Union

SELECT * FROM A
Union
SELECT * FROM B

6. Report Query
Max ()
Min ()
AVG ()
SUM ()
Count ()

7. Common functions
Select Now ();
Select Current_time ();
Select Current_date ();
Select MD5 (")

8. Backup and Recovery
1.windows command
Mysqldump-h localhost-u root-p Mydb1>mydb1.sql
2. Recovery
1.windows command
Mysql-u root-p Mydb2<d:/mydb1.sql
2.mysql command
Create Database mydb3;
Use MYDB3;
SOURCE D:/mydb1.sql

MYSQL Little Note