SQL Usage Action Collections

First, the creation of the table

1. Create a table

Format:

1     CREATE table name 2        (column name data type (width) [DEFAULT expression][column constraint],3 ...           4           [TABLE constraint]5           [table_partition_clause]6     ];

Example:

1 CREATE TABLE book  (2     book_number  VARCHAR2 (5), 3     book_name  VARCHAR2 (+), 4     pub_com_ Number  VARCHAR2 (2), 5     author  VARCHAR2 (Ten), 6   publishing_date date,7     volume  INT (3), 8     Price  FLOAT9);

With the SQL statement above, we created a simple data table in the database, where we use MySQL to demonstrate

2. Create a table from an existing table

CREATE TABLE TableName (column_name1,column_name2) as SQL query statement

Eg:

(1) Complete duplication of table structure and data

CREATE TABLE Book1 as select * from book;

PS. There are two ways to view the structure of a data table:

A.describe (abbreviated DESC) ' table_name ';

B.SHOW full COLUMNS from ' table_name ';

As you can see, the difference between the two uses show full columns also shows some keys for the datasheet and a field comment.

Speaking of which, incidentally, how to use MySQL DOS terminal under win:

A. Enter the directory where the Mysql.exe is located

B. Execute mysql.exe-u user name-p password

C. Select the database you want to work with, the syntax is: usedatabase name , if you do not know what database, you can have a show databases;

D. View the data tables in the database, using show tables;

　　

(2) Copy only some of the characters in the data table Ganjian

Create table copy_table_name as select ' column_name1 ', ' column_name2 ',..., from table;

　　

If you want to rename these fields when copying, you can do so

CREATE TABLE Copy_table_name (' new_column_name1 ', ' new_column_name2 ',..) as select ' Column_name1 ', ' column_name2 ',... from table;

However, this syntax is not supported in MySQL and is supported in Oracle

(2) Delete data tables that have been created

DROP TABLE table_name;

　　

Second, the structure of the table modification

1. Add a new column to the data table, i.e. add a field

ALTER TABLE ' table_name '

ADD ' column_name ' DataType (maxLength) [Default ' defaults '] [field constraint];

(the brackets mean optional)

　　

2. Modify a column of a data table that is a field

Syntax and increase almost just change the Add keyword to modify

ALTER TABLE ' table_name '

MODIFY ' column_name ' DataType (maxLength) [Default value '] [field constraint];

　　

Note: Field names cannot be modified!!! If you want to change the column name, you can delete the column first and then add it again. Other parts can be modified, if no new definition is given, indicating that the partial property is unchanged.

　　

Modifying a column definition also has some of the following features:

(1) The width of the column can be increased or decreased, and the width may be reduced if no data or data is NULL for the column in the table.

(2) The data type can be changed when there is no data or data in the column of the table, and the char and VARCHAR2 are freely convertible.

(3) constraint not NULL can be increased only if the value of the column is not empty.

(4) Modify the default value of the column, affecting only the data that is inserted later.

3. Delete a column of a data table that is a field

ALTER TABLE ' table_name ' DROP COLUMN ' column_name ';

　　

Third, the table data update

1. Insert

INSERT into ' table_name ' (' column_name1 ', ' column_name2 ',..) VALUES (Val1,val2,..);

　　

A table with many fields, if you want to insert the values of all fields, a field name declaration is cumbersome, you can omit this step directly

INSERT into ' table_name ' VALUES (Val1,val2,...)

　　

Sometimes we want to copy data from a table, okay? No problem

INSERT into ' table_name ' (' column_name1 ', ' column_name2 ',..) SELECT

(' column_name1 ', ' column_name2 ',..) From ' Other_table_name ';

　　

2. Modify the data

Use the UPDATE keyword to modify the data, but you often need to use the WHERE clause to constrain the condition, otherwise all rows will be modified.

UPDATE ' table_name ' SET ' column1 ' = val1, ' column2 ' = Val2,... WHERE condition;

Special usage: Update data with other query results

UPDATE Table name SET (field name 1, field Name 2, ...) =select (field name 1, field Name 2, ...) From additional table name where condition;

　　

3. Delete data

It is as sensitive as deleting data, and it is important to check if there is a where condition before execution, otherwise all data in the data table will be deleted.

DELETE from ' table_name ' WHERE condition;

　　

If we want to clear all the data in the data table, we can use:

Druncate ' table_name '

Here you may naturally think that I use the delete from ' table_name ' can not achieve the same effect? Yes, but there are some differences between the two:

Both delete all data in the data table, but using truncate is faster and uses less system and transaction log resources, and each DELETE statement deletes a row in the transaction log for each deleted line Instead, truncate deletes data by releasing the data page used to store the table data, and records the release of the page only in the transaction log. Truncate DELETE content free space, delete delete content does not free space.

4. Query data

(1) Specify an index field

SELECT field list from table name Whrer condition

(2) Display computed columns

There can be arithmetic expressions in a query statement, including + 、-、 *

　　

(3) using aliases

　　

(4) string concatenation

Using "| |" In Oracle, using the concat () function in MySQL

To achieve the same effect, the SQL statement should be used in Oracle:

Select ' Title: ' | | ' Book_name ' | | ' Price is: ' | | ' Price ' as ' descript ' from Book2;

(5) Eliminate duplication

If duplicate rows are present in the display results, you can use the keyword distinct to eliminate duplicate displays

SELECT distince field name from table name

Join us to go heavy on multiple fields, then the result of the display is that the combination of multiple fields does not repeat the result

　　

(6) Sort

Using the ORDER BY keyword, there are ascending descending points, ASC is ascending, and Desc is descending

Ascending :

SELECT * FROM table name ORDER by ' field name ' ASC;

　　

Descending :

　　SELECT * FROM table name ORDER by ' field name ' DESC;

Sort multiple Columns :

　　SELECT * FROM table name ORDER by ' field name 1 ', ' Field Name 2 ',...;

In multi-column sorting, the precedence of the preceding field names is higher, that is, the sorting of the subsequent fields is sorted based on the result of the previous field sort.

PS. Field names in multi-column sorting include renaming fields and custom fields

5. Conditional query

(1) Simple condition query

To qualify a displayed row, you can use the WHERE clause after the FROM clause to give a qualified condition in the WHERE clause, because the qualification is an expression, so it is called a conditional expression. A conditional expression can contain a comparison operation, and a record with a true value for the expression will be displayed.

PS. Only fields of numeric and date types can be compared in size, and character fields can only be compared for equality

(2) Eligible queries

You can use logical operators to form compound conditional queries that concatenate two or more conditions into a single condition with logical operators. There are 3 logical operators, as shown in:

The order of precedence for the operation is not,and,or. If you want to change the order of precedence, you can use parentheses.

(3) Special calculation notation

A.between

For numeric or date data, the following operations are used when representing ranges

[NOT] Between ... And ...

　　

B.in

Display values that meet the results of a particular collection

[NOT] In ...

C.like

Use the LIKE operator to find a string by wildcard character for fuzzy queries

[NOT] Like matching pattern

In addition to a fixed character, the matching pattern can contain the following wildcard characters:

%: represents 0 or more arbitrary characters.

_: Represents an arbitrary character.

Eg:

Find information about books that begin with "Me"

　　

If you want to find a book with the title second character "+"

　　

D. Determine if a field is a null value

[NOT] Null

Iv. advanced query of the table

1, multi-table joint query

A multi-table query can be established through a connection, and data from multiple table queries can come from more than one table, but the tables must have appropriate connection conditions. In order to query from multiple tables, you must identify the public columns that are connected to more than one table. The condition of a connection is typically indicated by a comparison operator in the WHERE clause.

Forgetting the connection condition of the table is a common error, when the query produces a Cartesian product of table joins (that is, each record in one table is connected to each record in the other table). Normally n tables are connected and require at least N-1 connection conditions to be properly connected. Two table joins are the most common case, and only one connection condition needs to be explained.

More than two tables can also be connected, not specifically described here.

There are four ways to connect two tables:

• Equal connections.
• Unequal connections.
• External connections.
• Self-connect.

(1) Equal connection

You can establish an equal join condition by using two tables that have the same meaning. When querying with two tables using an equal join, only rows that appear in the two tables on the Join column and have equal values appear in the query results.

As an example:

We assume there is a student table student and a teacher table teacher. Student table contains students ' information, one of the key fields is the teacher's t_id, corresponding to the teacher table ID, here we simplify the teacher and student relationship is one-to-two, that a teacher can only teach a student, a student can only be taught by a teacher

We ask for the correspondence between the student and teacher and the student ID, then we can query the equality of the table:select S.id,s.name,t.name from student s,teacher t where s.t_id = t.id;

(2) Unequal connection

Similar to the above

(3) External connection

Using equal or unequal connections above creates a problem, that is, we can only see the information of the students who have a teacher's class and cannot see the information of the students who do not have the teacher's class, if we want to show the information of all the students when we connect with the teacher's table. External connection can achieve this goal!

An outer join displays not only records that meet the conditions of an equal join, but also rows that do not meet a conditional join.

There are three types of outer joins, namely:

Left OUTER join: No limit on left table

Right outer join: No restrictions on the right table

Full outer connection: No restrictions on the left and right tables

Here we use the relationship between teachers and students to illustrate these three kinds of external connections:

As shown, there is a t_id in the student's table indicating the teacher's ID, and the teacher has a s_id to show the teacher's student ID, and the teacher and student are one-on-one relationships.

　　

We initialize the data, students in the table Chen Liu was not coached by any teacher, the teacher Kalay also did not teach any student.

When we want to inquire about the students who are being taught and the corresponding teacher information, use the following query:

What if we also want to show the information of other students who have not been coached? Use LEFT Join

　　

What if we want to show all the information about the teacher's coaching situation? Use RIGHT Join

　　

If we want to show the normal teachers and the students who are normally coached, do you want to show the students who are not coached and the teachers who are not coached? Full join is used, but in MySQL it is not supported in the total joins, how do you achieve the effect of the complete join? Looking closely at the left and right connections above, we found that the left-to-the-connected collection is a fully connected set, where we can use the Union to regain the full join effect.

　　

(4) Self-connected

A self-connection is a table that is connected to itself. For self-joins you can imagine that there are two identical tables (a copy of tables and tables) that can be distinguished by different aliases two identical tables

Eg:

Both the manager and the employee are in the Employees table, each employee has a management manager, who wants to query the employee and the name of the corresponding manager. This kind of query is also common in an infinite classification table.

Assuming that the primary key for the employee table is an ID, the employee name is a name and the manager ID is man_id, the above query requires that the corresponding SQL should be

SELECT work.name,manager.name from ' employ ' worker, ' employee ' manager WHERE work.man_id = manager.id;

2. Statistical inquiry

Statistics are often required to summarize the statistics of the database. For example, you may want to know the total number of companies and total salary, or the number of departments and salary, this function can be completed by statistical query.

The database provides functions to accomplish statistical work, called Group functions, which are different from the functions described and used earlier (single-line functions). Group functions can sum and average the grouped data. Group functions can only be applied to a SELECT clause, a HAVING clause, or an ORDER BY clause. Group functions can also be called statistical functions.

3, sub-query

In many cases, we need to do this by taking the result of one query as part of another query, which can appear as part of a second query in the condition of the second query, which is called a subquery.

Subqueries generally appear in the WHERE clause of the SELECT statement, and Oracle also supports subqueries in the FROM or HAVING clause. The subquery executes before the main query, and as a condition of the main query, it is written with parentheses, and placed on the right side of the comparison operator. Subqueries can be nested, with the innermost query executing first. Subqueries can be used in statements such as SELECT, INSERT, UPDATE, delete, and so on.

Here's an example of the employee-to-salary relationship, assuming that there is an employee form EMP, each row represents an employee's information, and that the ename, Sal, empno, Job, deptno fields represent employee names, employee salaries, employee numbers, jobs, and departments, respectively.

(1) Single-line subquery

If the subquery returns only one row of results, we call this a single-row subquery

Example: Query for an employee's name and salary that is higher than Scott's salary

Analysis: Scott pay query for sub-query, only to find out this before the gap Scott's salary to find the results we want to query

SQL statement: Select ename,sal from emp where Sal > (SELECT sal from emp where ename = = ' SCOTT ');

(2) Multi-row sub-query

If the subquery returns multiple rows of results, it is called a multiline subquery. Multiline subqueries often use unused comparison operators, which are in, any, and all

Example: Query employee information for wages below any one "clerk" wage

Analysis: There are any keywords, so use the any comparison operator

SQL statement: SELECT * from emp where Sal < No (SELECT sal from emp where job = ' clerk ') and job! = ' Clerk ';

Example: Query the number, name, and salary of employees whose wages are higher than all "salesman"

Analysis: There are all keywords, using the all comparison operator

SQL statement: Select empno,ename,sal from emp where Sal > All (SELECT sal from emp where job = ' salesman ');

Example: Query the Employee information in department 20, same as 10 employees in department

Analysis: There is a range of keywords in what, with in comparison operator

SQL statement: SELECT * from emp where job in (the SELECT job from emp where Deptno = ten) and deptno = 20;

(3) Multi-column sub-query

If a subquery returns multiple columns, multiple columns should also appear in the corresponding comparison criteria, which becomes a Dolez query.

Example: Querying for information about the job title and the employee of the same department as Scott

SQL statement: SELECT * from EMP where (JOB,DEPTNO) = (select Job,deptno from emp where ename = ' SCOTT ');

4. Set operation

The results of multiple query statements can be set and the field type, quantity, and order of the result set should be the same.

and set

　　　　

　　

　　

Subtraction

MySQL does not support minus

To achieve a similar effect, you can use in, no in, or union all to do so, but it is recommended to use only when the amount of data is small, otherwise the efficiency is lower.

　　

Using UNION ALL to implement difference sets

Using not in to implement intersection

　　

Intersection

Using union all to implement intersection

　　

Using in to implement intersection

　　

SQL Usage Action Collections