MySQL database important points of knowledge (Cicada Hall learning Experience)

MySQL database knowledge points 1. Manage Database statements:

Working with databases:

Use test;

To add a database:

Create database name;

Create DATABASE test;

To modify a database:

ALTER DATABASE name;

ALTER DATABASE test;

To delete a database:

drop database name;

drop database test;

View all databases:

show databases;

2. Manage Table statements:

To add a data table:

CREATE TABLE Table name (

Column name data type data constraints,

Column name data type data constraint

);

CREATE TABLE Student (

Name varchar () is not NULL,

Age Int (4)

)

To modify a data table:

ALTER TABLE name;

1) Add new fields to the table

ALTER TABLE student Add COLUNM name varchar (20);

2) Delete The fields in the table

ALTER TABLE student drop name;

3) Modify the type of the field in the table

ALTER TABLE student modify name varchar (10);

To delete a data table:

drop table name;

drop table student;

View All tables:

Show tables;

3. Manage Data statements:

Insert data:

Insert into table name (column 1, column 2 ...) Values (value 1, value 2 ...);

INSERT into student (Name,age) VALUES (' Zhang San ', 20);

Update data:

Update table name Set column = modify value where condition;

Update student set Name= ' John Doe ' where Name= ' Zhang San ';

Delete data:

Delete from table name where condition;

Delete from student where name= ' John Doe ';

Query data:

Selete * FROM table name where condition;

Selete * from student where Name= ' Zhang San ';

4. Various query statements:

Specify aliases when querying:

1) Selete ID as ' number ', name as ' name ' from student;

2) Selete ID ' number ', name ' name ' from student; ( as can be omitted )

Merge columns when querying:

Requirements: Query each student's total score

Selete name as ' name ', (servlet+mysql) as ' total ' from student;

Note: The field of the merged column must be a field of numeric type

Remove duplicates when querying (distinct):

Requirements: Find out which students are in the region

Selete DISTINCT address from student;

Another type of syntax

Selete DISTINCT (address) from student;

Conditional Query

logical condition : And OR

Requirements: Students whose ID is 1 and whose name is Zhang San are queried

Selete * from student where id=1 and Name= ' Zhang San ';(intersection)

Requirements: Check student ID 2, or student named Zhang San

Selete * FROM student where id=2 or Name= ' Zhang San ';(and set)

Comparison criteria: > < >= <= = <> (between and--in ... Between package before package)

Requirement: Query for students with servlet scores greater than 80 points

Selete * FROM student where servlet>80;

Requirements: Query MySQL score less than or equal to 85 points of students

Selete * FROM student where mysql<=85;

Requirement: Query servlet scores greater than or equal to 80 points, and less than or equal to 85 points of students

Selete * from student where servlet>=80 and servlet<=85;

Instead of the syntax of the above statement

Selete * FROM student where servlet between and 85;

Requirements: Check for students who are not equal to 30 of age

Selete * FROM student where age<>30;

Null condition: Is null, was not NULL, = "' , <> "'

Null: Indicates no data

Null character: Indicates that there is data

Requirement: Query for students without gender data (data ' male ' or ' female ')

Selete* from student where gender is NULL OR gender= ';

Requirements: Students who have sex data are queried

Selete * from student where gender are not NULL and gender<> ';

Fuzzy condition: Like

Fuzzy substitution symbols:

%: Substitute any character

_: Replace one character

Requirements: Inquiry for students with the surname "Li"

SELECT * FROM student WHERE NAME like ' li % ';

Requirements: Search for students whose names contain the word ' four '

SELECT * FROM student WHERE NAME like '% four ';

Requirements: Query surname ' Li ', full name only two words of students

SELECT * FROM student WHERE NAME is like ' li _ ';

aggregate function queries (for statistical results )

Max () takes the maximum min () takes the minimum AVG () takes the average count () the record number of the statistic mark sum () sums

Requirements: Query the maximum score for the servlet

SELECT MAX (servlet) from student;

Requirements: Check MySQL's minimum score

SELECT MIN (MySQL) from student;

Requirements: Query the average of the servlet

SELECT AVG (servlet) from student;

Requirements: Query currently has several students

SELECT COUNT (*) from student;

Requirements: Query the sum of servlet scores

Selete SUM (servlet) from student;

Paged query (limit)

Limit start rows, number of rows queried

Starting line number starting from 0

Requirements: Query 1th, 2 data

SELECT * FROM student LIMIT 0, 2;

Paging query need to know: Current page number, per page display

Conclusion Paging Query the current page data: SELECT * from student limit (current page-1) * Show the number of bars per page, the number of bars per page;

Requirements: Students a total of 20 data, per page 5, total 4 pages

Check the Student data on page 3rd (11th to 15th): SELECT * FROM Student limit 10, 5;

Post-query sort (order by)

DESC: Descending. Value from large to small, letter Z-a

ASC: Ascending. Values from small to large, letters A-Z

By default, sorted in the order in which they were inserted

Requirements: Sort by ID in ascending order

SELECT * FROM student order by ID ASC;

Requirements: Sort by servlet score descending

SELECT * FROM student ORDER by servlet DESC;

Multiple sort conditions: Sort by the preceding criteria, when duplicate records occur, and then follow the following criteria

Requirements: Sorted in ascending order of age, followed by servlet score ascending

SELECT * FROM Student ORDER by age asc,servlet ASC;

Grouped queries (group by)

Requirements: Find out how many people are in each region

SELECT Address,count (*) from student GROUP by address;

Needs: Statistics on the number of men and women

Note: The Where condition must be placed before the group by group

SELECT Gender,count (*) from student WHERE gender are not NULL and gender<> "GROUP by gender;

Post-grouping filtering (having)

Requirements: Find out which areas are more than 2 in size

Query which areas how many people 2) filter more than 2 of the region

Note: Having used after group by grouping, filtering the conditions after grouping

SELECT Address,count (*) from student GROUP by address have COUNT (*) >2;

5. Data constraints ( adding data constraints to a table, thus constraining the behavior of the user in manipulating table data )

1) Default value constraint

CREATE TABLE Test (

Name varchar (20),

Gender varchar (default ' male ')

)

Requirements : assign a ' male ' default value when not inserting gender

Note : 1) When the gender field is not inserted , assign a default value

2) non-null constraint (NOT NULL)

CREATE TABLE Test (

Name varchar () is not NULL,

Gender varchar (20)

)

The name field must have a value ( You cannot insert the data, it cannot be null), this is the name Add a non-null constraint

1) non-null constraint, cannot insert value

Insert into Test (gender) VALUES (' Male ');

2) non-null constraint, cannot insert null

Insert into Test (name,gender) VALUES (null, ' Male ');

3) Unique constraint (unique)

CREATE TABLE Test (

Id int Unique,

Name varchar (20)

)

Requirement: The value of ID cannot be duplicated. You should add a unique constraint to the ID.

1) cannot insert duplicate values

2) Unique constraint, you can insert more than one null. So the unique constraint cannot constrain null

Insert into Test (id,name) VALUES (1, ' Zhang San ');

4) PRIMARY KEY constraint (primary key) (unique + non-null)

Note

1) Typically, we will set a primary key field for each table to mark the uniqueness of the record

2) However, it is not recommended to use the business meaning field as the primary key, as business fields may appear duplicated as the business changes

3) It is recommended that each table be independently added a field called ID, the ID field is set to the primary key, used as the uniqueness of the record

CREATE TABLE Test (

Id int PRIMARY KEY,

Name varchar (20)

)

1) Uniqueness

2) Non-cavitation

5) Self-growth constraint (auto_increment)

CREATE TABLE Test (

Id int primary Key auto_increment,

Name varchar (20)

)

Self-growth constraint: The initial value is 0, incremented by 1 each time

When using TRUNCATE table to delete data, you can set the initial value of self-growth to 0

6) FOREIGN KEY constraint

Employee Table ( sub-table : table constrained by another table, foreign key set in sub-table )

Create Table Employee (

Id int primary Key auto_increment,

Name varchar (20),

DeptID int,

Add FOREIGN KEY constraint (foreign key)

Constraint EMPLOYEE_DEPT_FK foreign KEY (DeptID) references dept (ID)

Foreign Key name foreign key field

)

Department table ( Main Table : A table that constrains others )

Create Table Dept (

Id int primary Key auto_increment,

Name varchar (20)

)

Under what circumstances does a foreign key constraint work?

Insert data: Foreign keys work when data is inserted into a secondary table that does not exist in the primary table

Modify data: Foreign keys work when you modify data that does not exist in the primary table in a secondary table

Delete data: When you delete the primary table data, the foreign key works when the data in the secondary table is associated with the main table

When you have a foreign key, how do you manage the data?

Insert data: Insert data from primary table and insert secondary table data

Modify the data: First modify the main table data, and then modify the secondary table data

Delete data: Delete the secondary table data before deleting the main table data

7) Cascade Technology (CASCADE)

Cascade: When you have a foreign key, when we want to modify or delete the data, when we modify or delete the main table data, but also can affect the data of the secondary table, then you can use the Cascade

Create Table Employee (

Id int primary Key auto_increment,

Name varchar (20),

DeptID int,

Add FOREIGN KEY constraint (foreign key)

Add Cascade Modification: ON UPDATE cascade

Add Cascade modification: ON DELETE cascade

Constraint EMPLOYEE_DEPT_FK foreign KEY (DeptID) references dept (ID) on UPDATE cascade on DELETE CASCADE

Foreign Key name foreign key field

);

Department table ( Main Table : A table that constrains others )

Create Table Dept (

Id int primary Key auto_increment,

Name varchar (20)

);

6. Three main paradigms of database design

The first paradigm requires that each field of a table must be independent of an indivisible unit

Student table: Student name---violate the first paradigm

Zhang San | dog Doll

Wang Han | Zhangxiao

Queries: Students with ' Zhang ' in their name

Select * FROM student the where name like '% Zhang %';

Student table: Student name Old_name---conform to the first paradigm

Zhang San Dog doll

Wang Han Zhangxiao

Second paradigm: On the basis of the first paradigm, fields other than the primary key are required to have a dependency on the primary key

A table only expresses one meaning

Employee table: Employee

Employee Number Employee Name Department name Order name---violate second normal form

Employee table: Employee

Employee Number Employee Name Department name---in accordance with the second paradigm

Order Form:

Order Number Order name

The third paradigm: on the basis of the second paradigm, fields other than the primary key are required to have a direct dependent relationship with the primary key.

Employee table: Employee

Employee Number Employee Name Department Number Department name---violate the third paradigm ( data redundancy appears )

1 three 1 software Development Department

2 John Doe 1 Software Development Department

Employee table: Employee

Employee Number Employee Name Department number---conform to the third paradigm

1 sheets of three 1

2 John Doe 1

Department Table: Dept

Department Number Department Name

1 Software Development Department

8) Multi-table query

1. Cross Join query ( Cartesian product : 4*3=12, the reason for producing Cartesian product is that there are not enough connection conditions ) ( generally not used )

Requirements: Query employees and their department names

SELECT Employee.name,dept.name

From Employee,dept;

2. internal Connection Query ( most used )

Multi-Table Query steps: 1) determine which tables to query

2) determine which fields to query

3) Determine the connection condition ( rule : condition = number of tables -1)

SELECT Employee.name,dept.name

From Employee,dept

WHERE employee.deptid=dept.id;

Another type of syntax

SELECT E.name,d.name

From Employee E

INNER JOIN Dept D

On E.deptid=d.id;

3. LEFT outer join query ( the left table data is all displayed, if the right side is not satisfied, then NULL is displayed )

Requirements: Query the staff of the Department and its department

SELECT D.name,e.name

From Dept D

Left OUTER JOIN employee E

On D.id=e.deptid;

4. right outer join query ( the right table data is all displayed, if left is not satisfied, then NULL is displayed )

SELECT D.name,e.name

From Employee E

Right OUTER JOIN Dept D

On E.deptid=d.id;

MySQL database important points of knowledge (Cicada Hall learning Experience)