MySQL Database Operation statement Summary

Simply review the following:

Add insert INTO--

Delete delete from--

Change Update table name set--

Check SELECT * FROM--

A SQL definition

SQL (Structure query Language) Structured Query Language:

(i) DDL (Data Definition Language):

Data definition language, used to define database objects: libraries, tables, columns, etc.;

(ii) DML (Data manipulation Language):

Data manipulation language, used to define database records (data);

(iii) DCL (Data Control Language):

Data Control language used to define access rights and security levels;

(d) DQL (Data Query Language):

A data query language used to query records (data).

In development, DML statements and DQL statements are frequently used when manipulating data from database tables.

The DDL statement is the operation of the database or table, which is usually done only once at the time of initialization. DCL Less use!

In layman's words: DML statements are additions, deletions, and modifications to tables.

The DQL statement is the query operation of the table, why do you want to separate the query operations into a large category?

Because queries sometimes involve more complex queries such as querying multiple tables for related information,

or query the information table after the arrangement of multiple attributes (example: first by class, then by grade, and then by the number of students to display the results)

Two The format of the statement

The database is not sensitive to the case of the keyword, and the table name is automatically converted to lowercase

(i) DDL statements

1. Operations on the database

(1) Create the database, if the data already exists, then will be an error.

Creating a database: Create databases mydb1;

(2) Delete the database, if the database does not exist, will not error.

Delete databases: drop database mydb1;

(3) Modify the encoding properties of the database:

ALTER DATABASE mydb1 CHARACTER SET UTF8

Modify the encoding of the database mydb1 to UTF8. Note that all UTF-8 encodings in MySQL cannot use the middle "-",

That UTF-8 to be written as UTF8.

(4) View all database names: show DATABASES;

Switch database: Use MYDB1, switch to MYDB1 database;

2. Working with tables in the database

Before the operation, understand the basic data types in the database!

Common types:

L INT: Integral type

L Double: floating point type, for example double (5,2) represents up to 5 bits, which must have 2 decimal places, i.e. the maximum value is 999.99;

L Char: fixed length string type;

L VARCHAR: variable length string type;

L Text: String type;

L BLOB: Byte type;

L Date: Type, format: YYYY-MM-DD;

L Time: The format of the type: HH:MM:SS

(1) Adding table operations

L. Create a table format:

CREATE Table Table name (

Column list Type,

Column list Type,

......

);

2. For example: Create a Student information table with ID name sex age

Requirements: ID is a number with the keyword Integer

ID is primary key (unique) primary key

ID is the self-growing keyword auto_increment

Name cannot be empty

Other according to the actual situation is set!

The design code is as follows:

CREATE TABLE Student (

ID Integer PRIMARY key auto_increment,

Name varchar (3) is not NULL,

Age Integer (2),

Sex varchar (1));

(2) Delete a table

drop table student;//delete student table and DELETE statement remove a row of different content;

(3) Modification of the table (there are also increased deletion check)

1. Modified Add Column: Add classname column to student table:

ALTER TABLE student ADD (classname varchar (10));

2. Modify the Delete column: Delete the classname column of the student table:

ALTER TABLE student DROP classname;

3. Modified column type: The sex column type of the modified student table is char (2):

ALTER TABLE Student MODIFY Gender CHAR (2);

4. Modified Column Name: Modify the student table for the sex column named Gender:

ALTER TABLE student Change Sex gender CHAR (2);

5. Modified Table Name: Modify the Student table name to Studentinfo:

ALTER TABLE student RENAME to student;

(4) View table

1. View all table names in the current database: show TABLES;

2. View the creation statement for the specified table student: Show create table student;

3. View table Student structure: DESC student;

(ii) DML statements

The data inside the table is added and manipulated.

1. Add Data

INSERT into table name (column name 1, column Name 2, ...) Values (value 1, value 2) or

INSERT into table name values (value 1, value 2,...)

2. Delete data

DELETE from table name [WHERE condition] or

TRUNCATE table name (higher efficiency)

3. Modify the data

UPDATE table name set column name 1= value 1, ... Column name n= value n [WHERE condition]

(iii) DCL statement

Not much to use, no explanation!

(iv) DQL statement (with emphasis on understanding)

DQL is the data query language, the database executes the DQL statement does not change the data, but lets the database send the result set to the client.

Detailed syntax:

SELECT selection_list/* The name of the column to query */

From table_list/* Table name to query */

WHERE Condition/* Line Condition */

GROUP BY Grouping_columns/* Group results */

Having condition * * After grouping the row condition */

ORDER by Sorting_columns/* Group results */

Limit Offset_start, row_count/* Result limit */

Several examples of simple query formats:

SELECT * from Stu;

SELECT SID, Sname, age from Stu;

SELECT * from Stu WHERE gender= ' female ' and ge<50;

The following query is for data reading between multiple tables (selected readings):

Multi-Table Query

There are several types of multi-table queries:

L merging result sets;

L Connection Query

Internal connection

External connection

2 Left Outer connection

2 Right outer connection

2 full external connection (MySQL not supported)

Natural connection

L Sub-query

1 Merging result sets

Effect: Merging result sets is the result of merging the query results of two SELECT statements together!

There are two ways of merging result sets:

L UNION: Remove duplicate records, for example: SELECT * FROM t1 UNION select * from T2;

L UNION All: Do not remove duplicate records, for example: SELECT * from t1 UNION all SELECT * from T2.

Requirement: Two results to be merged: Number of columns, column type must be the same.

2 Connection Query

Connection query is to find out the product of multiple tables, such as T1 connection T2, then the result of the query is T1*T2.

The connection query produces a Cartesian product, assuming the set a={a,b}, set b={0,1,2}, then the Cartesian product of two sets is {(a,0), (a,1), (a,2), (b,0), (b,1), (b,2)}. Can be extended to multiple collections of cases.

So many table queries produce such results is not what we want, then how to remove duplicate, unwanted records, of course, through the conditional filtering. There is an association relationship between the multiple tables that are usually queried, and the Cartesian product is removed by the association relationship.

Can you imagine the results of an EMP and a Dept Table connection query? EMP Total 14 rows of records, Dept table total 4 rows of records, then the connection after the query results are 56 rows of records.

You just want to query the EMP table at the same time, each employee's department information displayed, then you need to use the primary foreign key to remove useless information.

Use the primary foreign key relationship as a condition to remove useless information

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

The above query results will query all the columns of the two tables, perhaps you do not need so many columns, then you can specify the column to query.

SELECT Emp.ename,emp.sal,emp.comm,dept.dname From Emp,dept WHERE Emp.deptno=dept.deptno;

You can also specify an alias for the table, and then use the alias when referencing the column.

SELECT E.ename,e.sal,e.comm,d.dname From EMP as e,dept as D WHERE E.deptno=d.deptno;

2.1 Internal Connection

The above connection statement is an inner join, but it is not the SQL standard query method, can be understood as a dialect! The SQL standard internal connection is:

SELECT * From EMP E INNER JOIN Dept D On E.deptno=d.deptno;

Internal connection features: The query results must meet the conditions. For example, we insert a record into the EMP table:

Where Deptno is 50, and in the Dept table only 10, 20, 30, 40 departments, then the above query results will not appear in the "Zhang San" this record, because it can not meet e.deptno=d.deptno this condition.

2.2 Outer connection (left, right connection)

The characteristics of the outer joins: The result of the query has the possibility of not satisfying the condition.

Left JOIN connection:

SELECT * from EMP E Left OUTER JOIN Dept D On E.deptno=d.deptno;

Left join is the first 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, does not meet the condition of the display null.

So you may not be quite sure, we still use the above example to illustrate. In the EMP table "Zhang San" This record, the department number is 50, and the Dept table does not have a department number 50 records, so "Zhang San" This record, can not meet e.deptno=d.deptno this condition. But in the left connection, because the EMP table is the left table, so the records in the left table will be queried, that is, "Zhang San" This record will also be detected, but the corresponding right table section shows null.

2.3 Right Connection

The right connection is to first query all the records in the right table, and then the left table satisfies the condition display, does not satisfy the display null. For example, the 40-door Dept Table does not have an employee, but in the right connection, if the Dept table is the right table, 40 doors are still detected, but the corresponding employee information is null.

SELECT * from EMP E Right OUTER JOIN Dept D On E.deptno=d.deptno;

Connection Query Experience:

Connection is not limited to two tables, the connection query can also be three, four, or 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 Connections

As you all know, a connection query produces a useless Cartesian product, and we usually use the primary foreign key relationship equation to remove it. And the natural connection does not require you to give the primary foreign key equation, it will automatically find this type:

L Two connected tables have a consistent column name and type as conditions, such as EMP and dept tables have DEPTNO columns, and the type is the same, so it will be found by natural connection!

Of course there are other ways to find the natural connection, but there are other ways that can be problematic!

SELECT * from EMP NATURAL JOIN dept; SELECT * from EMP NATURAL left JOIN dept; SELECT * from EMP NATURAL right JOIN dept;

4 Sub-query

A subquery is a nested query, that is, select contains a SELECT, if there are two or more than two select in a statement, then it is a subquery statement.

The location where the subquery appears:

Where, as part of the condition;

From after, as a table to be queried;

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:

Employees with a higher salary than ganning.

Analysis:

Query conditions: wages > Ganning wages, where ganning wages require a subquery.

The first step: query ganning Wages

SELECT sal from emp WHERE ename= ' ganning '

Step two: Query employees who are above ganning wages

SELECT * from emp WHERE sal > (${First step})

Results:

SELECT * from emp where sal > (SELECT sal from emp where ename= ' ganning ')

L sub-query as condition

L subquery Form single row

Employee information for wages above 30 department owners

Analysis:

Search conditions: Wages are higher than the 30-door owner's wages, of which 30 are the sub-query of the owner's wages. Above all you need to use the ALL keyword.

Step one: Check the 30-door owner's salary

SELECT sal from EMP WHERE deptno=30;

Step Two: Query employee information that is higher than the 30-door owner's salary

SELECT * from emp WHERE sal > All (${first step})

Results:

SELECT * from emp where sal > All (select Sal from emp where deptno=30)

L sub-query as condition

L subquery is a multiline column (all or any keywords can be used when the subquery result set is a multi-row column)

Query employee information for work and payroll exactly the same as Yan Tianjong

Analysis:

Query conditions: Work and wages are exactly the same as Yan Tianjong, which is a subquery

The first step: to find out Yan Tianjong work and wages

SELECT job,sal from emp WHERE ename= ' Yintian positive '

The second step: to find out with Yintian is working and pay the same person

SELECT * from emp WHERE (job,sal) in (${first step})

Results:

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

L sub-query as condition

L subquery as single-row multi-column

Query Employee number 1006 Employee name, employee salary, department name, department address

Analysis:

Query columns: Employee name, employee salary, department name, department address

Query table: EMP and dept, analyzed, no external connection required (external connection characteristics: one row (or some rows) record will appear half of the value, half of the null value)

Condition: Employee number is 1006

The first step: To remove the multi-table, only one table, here to remove the Department table, only check the employee table

SELECT ename, sal from emp e WHERE empno=1006

Step two: Let the first step and dept do inner JOIN query, add main foreign key condition to remove useless cartesian product

SELECT E.ename, E.sal, D.dname, D.loc From EMP E, Dept D WHERE E.deptno=d.deptno and empno=1006

The Dept table in the second step represents a complete table for all the columns of all rows, where you can replace dept with all rows, but only the dname and LOC column tables, which require subqueries.

The third step: Query the Dept table in the Dname and loc two columns, because DEPTNO will be used as a condition to remove the useless Cartesian product, so you need to query it.

SELECT Dname,loc,deptno from Dept;

Fourth Step: Replace the Dept in the second step

SELECT E.ename, E.sal, D.dname, D.loc From EMP E, (SELECT dname,loc,deptno from dept) d WHERE E.deptno=d.deptno and e.empno=1006

Three Integrity constraints

Integrity constraints are for the correctness of table data! If the data is incorrect, it cannot be added to the table at the outset.

Let's talk about their keywords:

Specify PRIMARY KEY constraint: PRIMARY key

Self-increment: auto_increment

Non-empty: NOT NULL

Unique: Unique

MySQL Database Operation statement Summary