1. Overview
SQL is a database-oriented structured Query language. is a computer standard language that conforms to ANSI, the national standardization organization of the United States.
SQL has the operation of the database: adding and deleting changes. Create a database, create a table, create a stored procedure, create a view, and more
RDBMS relational database management system, refers to a set of database software, has mysql,db2. Oracle, etc.
In addition to the SQL standard, each database software has its own private extension.
The data in the database management system is stored in the database object of the table. A table is a collection of related data items that consists of columns and rows.
SQL is divided into two parts: database operation language DML (DB manipulation Language) and database Definition Language DDL (db defination Language)
DML contains: Add insert INTO, delete delete, change update, check Select
DDL contains:
To a database (CREATE DATABASE, ALTER DATABASE)
Tables on the database (create table in database, ALTER TABLE, delete table drop tables)
Index created (create INDEX, DROP index)
Note: SQL is not sensitive to uppercase and lowercase.
In SQL, the text value is caused by the single-quote ' STR ', the value does not need to use the
2. DML database Operation language
1. Query Select
Use of select:
SELECTColumnName1, ColumnName2
fromTableName
: Returns the specified column
SLELCT*
fromTableName
: Returns the results of all columns that are stored in an object called the result set.
1.DISTINCT
Distinct is used with select to query for different values:
SELECT DISTINCTColumnname1,columnname2
fromTableName: Return different values in the query process, often the same value, such as query a column, this column will have the same value; query two columns, there will be two columns at the same time the same value.
Distinct is to be in the query results. Remove those same values and keep only one.
Note: Assuming that multiple columns are selected, the same is true if the values of the multiple columns satisfy the same. So the ability to determine the same, there is a column of values of different, it belongs to different, need to show.
2.WHERE
The WHERE clause is used to specify the criteria for selection. is the prerequisite for satisfaction.
SELECTColumn Name
fromTable name
WHEREColumn Name
Operator value
: Select the value operator that satisfies the prerequisite column name operator value: =, <> (or! =), >, <, >=, <=, between and, like, between and is the value of a range selected. Like is search by some pattern
3.AND and OR
The and and or operators are used in the WHERE clause to combine two or more conditions.
SELECTColumn Name
fromTable name
WHEREColumn 1 operator 1 value 1
and (OR)Column 2 operator 2 value 2: Selecting values that meet multiple prerequisites can also use parentheses () to assemble complex expressions
SELECTColumn Name
fromTable name
WHERE(column 1 operator 1 value 1
ORColumn 2 operator 2 value 2)
andColumn 3 operator 3 value 3
4.ORDER by
Sort the result set by default according to the ascending rule
SELECTColumn Name
fromTable name
ORDER byColumn 1
(ASCOr
DESC), column 2
(ASCOr
DESC): Sort By column, assuming multiple columns. So first sort by column 1, in column 1 with the same value, and then sorted by column 2, ASC ascending, desc is descending. It is also possible to write both, using the default sort note. If you sort by more than one column, each column needs to specify how to sort, with ASC or DESC behind the specified column. or default
2. Inserting INSERT INTO
INSERT intoTable name
VALUES(value 1, value 2, ...) : Inserting data into a table that includes all column values
INSERT intoTable name (column 1, column 2, ...)
VALUES(value 1, value 2, ...) : Inserting a data into a table that contains only the values of the specified column note: When you do not specify a column name, the data you insert must indicate the values of all the columns in the table, otherwise the error is in MySQL, and insert does not have to be in conjunction with into. Not into is correct, do not know how other databases.
3. Updating update
UPDATETable name
SETColumn 1= value 1, column 2= value 2
WHEREColumn 3 operator value 3: A more pre-requisite (can be a combination of multiple conditions), to navigate to the specified entry (can be more than one). Update this data
4. Remove DELETE from
DELETE fromTable name
WHEREColumn operator value: The data selected according to the prerequisites. Delete this data
DELETE * fromTable name, or
DELETE fromTable name: Deletes all the data in the table. But do not delete the table, preserving the structure of the table. can also use
TRUNCATE TABLETable name, same effect note: DELETE must be in conjunction with from. No from error
5. TOP
The TOP clause is the number of data that is specified to be returned. The top sentence is important for large tables with tens of millions of entries. Concept syntax: SELECT top num * FROM table name: Returns the previous NUM record that satisfies the condition from the table, or select Top num PERCENT * FROM table name: Returns the previous num% record that satisfies the condition from the table for the conceptual syntax, each database software uses a different 。 Used in MySQL
LIMITKeyword
SELECT * fromTable name
LIMITUsed in num Oracle
ROWNUMKeyword with where:
SELECT * fromTable name
WHERE ROWNUM<=num
6. Like
Likekeyword is intended to be used in the WHERE clause. Follow the specified pattern for a column. Search for the corresponding entry usage:
SELECT * fromTable name
WHEREColumn Name
likeThe ' pattern ' pattern is usually used with wildcard connect prompt, with wildcards having%, _, [charlist], [^charlist] or [!charlist]. For example, using%: '%str ', string ending with str, ' str% ' string starting with str, '%str% ' including Str's string using Notkeyword, selecting data that does not conform to ' pattern '
SELECT * fromTable name
WHEREColumn Name
not-like' Mode ' NOTE: Assuming that the column name does not have a wildcard in the like ' pattern ', then it is equivalent to the column name = ' pattern ' 7, wildcard
Wildcard characters are:
%, _, [charlist], [^charlist] or [!charlist]。 Wildcard characters must be in use with like, in which case the% representation replaces one or more characters; _ replaces only one character. For example, ' _ADDC ' means that the first letter is followed by a ADDC string. or ' a_d_s ' can. A random single character in the [charlist] character. For example, ' [abc]% ' means a string [^charlist] or [!charlist] not in the character set that begins with a random single character in ' ABC ' note that somehow the [charlist] wildcard is not available in MySQL and needs to be identified. All else can.
8, in
SELECT * fromTable name
WHEREColumn Name
inch(value 1.) Value 2, ...) Inkeyword, given a collection, selects the value of the column in the combined data entry. The value can be a string, or it can be a number, in detail by the data type of the column
9. Between and
SELECT * fromTable name
WHEREColumn Name
betweenValue 1
andValue 2: Select data from the specified column between the value 1 and the value 2.
Note that different database software handles between differently, some of which include a value of 1 and a value of 2, some of which are not included. Some include only one of them. MySQL is included both before and after. Assuming that the column type is numeric, it is greater than the value 1. Data that is less than the value 2 (must be greater than the front.) Less than the back, cannot be reversed) assuming that the type of the column is a string, then it is in alphabetical order. Data between the value 1 and the value 2. Note that ' BB ' is after ' B ', assuming that the value 2 is ' B ', then ' BB ' cannot be included.
10. Aliases Alias
Use aliases for data tables or columns, using
asKeyword to table:
SELECTData table aliases. Column names
fromTable name
asData table Aliases
WHEREData table aliases. Column name operator value: Use a data table alias to be able to name the column:
SELECTColumn Name
asColumn Aliases
fromTable name
WHEREColumn name operator value: When displayed, the name of the column is displayed as an alias. Note that the alias of the column cannot be used in the WHERE clause such as: SELECT Po.orderid, P.firstname, p.lastname from persons as P, product_orders as PO Note that the result shown is Produc The product of the number of columns of t_orders and the number of columns of persons
11. JOIN
Joinkeyword. The goal is to implement queries for two related tables at the same time, and the two tables generally have a column that is relevant. In the Aliases alias section. The result of querying two tables at the same time shows the display of the product of two tables, that is, each of the tables in one table will be displayed in the corresponding table. So, a query for two tables. Plus prerequisites, such as: SELECT P.id,p.firstname, P.lastname, po.order_id from persons as P, products_orders as Po WHERE p.id=po.p_id;
So, it's in the product table. Find out the display that meets the prerequisites. Such a query. The ability to use Joinkeyword is complete:
SELECTTable 1 aliases. column name, table 2 alias. Column Name
fromTable 1
asTable 1 Aliases
JOINTable 2
asTable 2 Aliases
onTable 1 aliases. Column name operator table 2 aliases. Column names can also be used without aliases, but the name of the table to write. Column name. would be more troublesome. Assume that the on and behind conditions are not written. The number of data for the entire product table is displayed. All data entries for two tables may not meet the prerequisites. For example, in table 1, there are a few unsatisfied, table 2 has a few unsatisfied, then how to show that these do not meet the prerequisites? is to use the left join and right join
Left JOIN
SELECTTable 1 aliases. column name, table 2 alias. Column Name
fromTable 1
asTable 1 Aliases
Left JOINTable 2
asTable 2 Aliases
onTable 1 aliases. Column name operator table 2 alias. Column Name
The left join is shown in table 1 that does not meet the prerequisites, and the column in the Display results for table 2 is null
Right JOIN
SELECTTable 1 aliases. column name, table 2 alias. Column Name
fromTable 1
asTable 1 Aliases
Right JOINTable 2
asTable 2 Aliases
onTable 1 aliases. Column name operator table 2 alias. Column Name
The right join is shown in table 2 that does not meet the prerequisites, and the column in the display results for table 1 is null
Note that when using left/right join, there must be a prerequisite for on, otherwise an error occurs.
Full JOIN
Full join is actually a collection of left joins and right joins, showing data items in two tables that do not meet the prerequisites in MySQL, full join is not supported. Instead, use the left join to combine with right join:
SELECTTable 1 aliases. column name, table 2 alias. Column Name
fromTable 1
asTable 1 Aliases
Left JOINTable 2
asTable 2 Aliases
onTable 1 aliases. Column name operator table 2 alias. Column Name
UNION
SELECTTable 1 aliases. column name, table 2 alias. Column Name
fromTable 1
asTable 1 Aliases
Right JOINTable 2
asTable 2 Aliases
onTable 1 aliases. Column name operator table 2 alias. Column Name
12. UNION
The Union function is to concatenate two SELECT statements to implement a collection of multiple select displays. With union, the number of columns to select for each SELECT statement and the data type of the corresponding column should be consistent, otherwise an error assumes that the same result is found in the results of multiple SELECT statements (multiple columns are selected, each column is the same). Then just show one of them. Usage:
SELECTColumn Name 1
fromTable Name 1
UNION (All) SELECTColumn Name 2
fromTable Name 2
UNION AllShow all the results and the same results all show up
13. SELECT into
Select INTO is used to select data from one table and then insert it into a table. Frequently used to create a table backup concept syntax: Select column name into table name 2 from table name 1, Value data from table 1 to table 2 However, MySQL does not support the SELECT into statement. MySQL has two ways to do this: when the backup table does not exist, you need to create a new table:
CREATE TABLETable _ Backup
(SELECT * fromTable name
), you must enclose the SELECT statement in parentheses. When the backup file exists,
INSERT intoBackup table name
SELECT * fromTable name, the structure of the existing backup table and the structure of the selected columns to be consistent.
3. Database Definition Language DDL
1. Create DATABASE:
CREATE DATABASEDatabase name
2. CREATE TABLE:
CREATE TABLETable name
(Column Name 1 data type 1 constraint 1, column name 2 data type 2 constraint 2, column name 3 data type 3 constraint 3
)When you create a table, you must indicate the columns and data types of the table, and the constraints can be given according to the requirements.
The primary data types are shaping: int (size). smallint (size). tinyint (size) floating-point type: Double (size,d), Decimal (size,d) string: CHAR (size), VARCHAR (size) Dates: date note. The ability to specify a data type without specifying a data length of size is generally specified as good.
The date type is not required and cannot be specified.
3, constraint constraints constraints, refers to the definition of the data table, the specified column is added to a constraint condition. such as non-null, unique, primary key, and so on.
SQL constraints are: non-null, unique, primary key primary key, foreign key foreign key, restricted check, default
Not NULL, the position is written behind the type of the column, and the column is not null constrained, and in principle must have data for that column when inserting data and updating data. That is, the value of the column cannot be empty. As a matter of fact. In MySQL, a column with a NOT NULL constraint can be inserted as an empty value, but a warning is issued, so the general case. For columns of this class, you should have data as well.
CREATE TABLE persons_back (id INT (+) not null,firstname VARCHAR (+), birth DATE);
A unique constrained column can only have a unique value and cannot have the same value.
Assuming the unique constraint of multiple columns at the same time: Unique (column name 1, column name 2), then only the values of multiple columns appear the same time, only to be judged as the same. cannot be inserted, there is a random value in one column is different. will be able to insert data.
Suppose unique has a separate constraint on each column: Unique (column name 1), Unique (column name 2), then each column cannot have the same data, regardless of the other columns. Unique location in MySQL. Can be placed behind a type, and can be defined separately at the end of the column definition Uinque
CREATE TABLE persons_back (id INT (+) not NULL unique,firstname VARCHAR () Unique,birth DATE);
Or
CREATE TABLE persons_back (ID INT () not NULL, FirstName VARCHAR (+), birth date,unique (ID), UNIQUE (FirstName));
Each of these columns is constrained individually, and the following is the same time constraint for multiple columns:
CREATE TABLE persons_back (ID INT () not NULL, FirstName VARCHAR (+), birth date,unique (Id,firstname));
In fact, each unique has a name, the default is that the column name, you can also specify: UNIQUE alias (column name). or CONSTRAINT alias UNIQUE (column name)
CREATE TABLE persons_back (ID INT () not NULL, FirstName VARCHAR (+), birth date,unique Uc_personsid (id,firstname));
Or
CREATE TABLE persons_back (ID INT () not NULL, FirstName VARCHAR (+), birth date,constraint uc_personsid UNIQUE (id,firstn AME));
If you do not specify an alias, constrain more than one column to a unique name for the first column. This type of alias can also be a single
Assuming that the table has been created, you can also add a unique constraint to the table's columns by ALTER TABLE add unique (column name) in MySQL, the following two ways are available:
ALTER TABLE Persons_back add unique (id,firstname) ALTER TABLE persons_back add CONSTRAINT uc_personsid UNIQUE (Id,firstnam E
You can also delete a unique constraint by using ALTER TABLE drop INDEX name
ALTER TABLE persons_back DROP INDEX ID
Using the below method is not supported in MySQL. Oracle applies:
ALTER TABLE persons_back DROP CONSTRAINT uc_personsid
Note: When a unique constraint is assumed, no not NULL is specified, and the column is nullable.
PRIMARY keypeimary Key Usage and unique is the same, but the meaning of the representative of some differences.
The column for the primary KEY constraint. cannot be a null value. It cannot have the same value. That is, primary key combines not NULL and UNIQUE, and the other point is that only one column in each table is constrained to be the primary key. Otherwise, it is best to have a primary key in each table. Assuming there are no and no errors
CREATE TABLE persons_back (id INT (PRIMARY) key,firstname VARCHAR (+), birth DATE);
Or
CREATE TABLE persons_back (id INT (+), FirstName VARCHAR (+), birth date,primary KEY (id));
Musql, the primary key does not have an alias, and it is not necessary to specify the alias, delete only need to specify primary key, because a table has only one primary key.
ALTER TABLE person DROP PRIMARY KEY
PRIMARY KEY can also constrain several columns at the same time, only if the value of all the columns is the same, the ability to determine the two data is also not inserted. But multiple columns can be null at the same time, but only one can exist, otherwise it is the same.
FOREIGN key foreign keys. The goal is to have two tables associated with each other. A column of table B is specified as a foreign key, and a column of table A is associated. Foreign keys prevent illegal data from being inserted into foreign key columns. Because he must be one of the values that points to the table that the foreign key is associated with.
CREATE TABLE Persons (ID INT (PRIMARY) key,firstname varchar (+) not null,lastname varchar (+) not null,city varchar (32)) CREATE TABLE products_orders (order_id int (PRIMARY) key,order_name VARCHAR (+), p_id Int (+), FOREIGN KEY (p_id) REFERENCES persons (ID))
In MySQL, the definition of a foreign key cannot be defined in a separate row between the column type, otherwise an error occurs.
Foreign keys are named in the same way as unique,
FOREIGN KEY NS (p_id) REFERENCES persons (ID)
Or
CONSTRAINT NS FOREIGN KEY (p_id) REFERENCES persons (ID)
If you do not name aliases, the default is to delete the column names
ALTER TABLE products_orders DROP FOREIGN KEY p_id
Note: I do not know why when the foreign key was created, when the foreign Key,mysql implementation, it became a key. Deletion is also the case. Why? (pending resolution)
and using the MyISAM engine in MySQL can create foreign keys, do not work; using the InnoDB engine cannot create foreign keys, for some reason? Online search information, very much said in the development of the use of foreign keys can not be used. Because the operation of the related table is equivalent to a transaction, there is a concurrency operation for the large amount of data, and performance cannot be raised.
Checkcheck constraints are limits on the values of columns in a table. MySQL does not support check words at the moment, but it does not error. Will ignore them on their own initiative. For example, limit integer values greater than 0:check (id>0) to multiple column limits. Connect using and because MySQL is not supported. It's not much to say.
CREATE TABLE persons_back (id INT (+), FirstName VARCHAR (+), birth date,p_id INT (+), CHECK (Id>0 and FirstName in (' My ') )) ;
DefaultDefault the default value for the column itself, it is possible to insert data without inserting it into the column, the column has default values, and it can insert data for updating.
Usage. Add default ' str ' or default value directly behind the category of the column
CREATE TABLE persons_back (id INT (+), FirstName VARCHAR (+) DEFAULT ' my ', Birth date,p_id INT (16));
Delete: ALTER TABLE name alter LIST DROP DEFAULT
Alter TABLE Persons_back ALTER FIRSTNAME DROP DEFAULT
Note that the alter on the table is first, in the pair of columns. Then drop DEFAULT
4. What is the index index? is a data structure or algorithm. Helps to find data in the database at high speed. Can only be used to search and query using: Create a normal index, agree to create the index row has different values.
CREATE INDEXIndex name
onTable name (column name 1, column name 2) Delete index:
DROP INDEXIndex name
onTable name Note: Updating a table that includes an index requires a lot of other time than updating a table that does not include an index, because the index itself is updated. You can also use ALTER to join and delete indexes
ALTER TABLETable name
ADD INDEXIndex name (column name 1, column name 2)
ALTER TABLETable name
DROP INDEXIndex name
The index is divided into general index, unique index and focus index three categories 1. Unique Indexes Unique index: there cannot be two of the same data in the indexed column.
CREATE UNIQUE INDEXIndex name
onTable name (column name 1, column name 2), delete do not have to indicate or write drop UNIQUE INDEX, normal delete can.
2. Primary KEY index: A special type of unique index note that the PRIMARY KEY constraint itself establishes the primary key index, uniquely constraining itself to establish a unique index with the same name
3. Focus Index: The physical order of the rows in the table and the logical properties of the key values are also
5, Dropdrop used to delete the database, delete the table, delete the index, delete the constraint drop database name drop table Name drop index name on table name MySQL, sense constraints and indexes are always inseparable. Delete one another will also be deleted, pending verification 6, Alteralter can add, delete, and alter columns in an existing table join column:
ALTER TABLETable name
ADDColumn list type constraint Delete column:
ALTER TABLETable name
DROPColumn name or
ALTER TABLETable name
DROP COLUMNColumn Name
Change column deletion, add constraint, index, etc. alter COLUMN type CONCEPT syntax: ALTER TABLE table name ALTER column name type but in MySQL, use modify
ALTER TABLETable name
MODIFYColumn name Type 7, self-actively add the primary key data each time you insert new data. Inserting data does not have to specify primary key data. In different databases, this feature is implemented differently in MySQL: After the primary key column is added
auto_increment, but it must be under the premise of defining the primary key. The keyword must also be written after the primary key column, otherwise an error occurs.
CREATE TABLE persons ( ID INT (+) NOT NULL auto_increment, firstname varchar (+) NOT NULL, LastName varchar (3 2) Not NULL, City VARCHAR (+) default NULL, email INT (one) default null, PRIMARY KEY (ID))
4. Function functions can use some functions in SQL. Perform an operation or filter on the selected data. There are very many built-in functions in SQL that are used to calculate and count these functions into aggregate functions and scalar function aggregate functions: For a series of values. and returns a single value. For example, the AVG (), SUM () scalar function: Returns a single value, such as UCASE (), based on the input's single value, so that the column is displayed in uppercase, note. The values in the database do not change the aggregate function avg () computes the average of a column and returns the floating-point data
SELECT AVG (column name) fromTable name
WHEREThe limitations, in AVG (), include multiple columns, the ability to use the statement of this select in the form of AVG (column 1), AVG (column 2), the value returned as AVG, and the ability to continue as a constraint:
SELECT * FROM persons WHERE num > (SELECT AVG (num) as number from persons)
COUNT () returns the number of rows of data for the matching criteria
SELECT COUNT (Column
) fromTable: Returns the number of data for the specified column. Null is not counted, but an empty string is counted.
SELECT COUNT (*) fromTable: Returns the number of data rows for the entire table
SELECT COUNT (DISTINCTColumn
) fromTable: Returns the number of values in the column that are not the same as first () returns the data for the specified column in row one in the table, but MySQL does not support this function, but is completed by Limitkeyword
SELECT City from persons LIMIT 1
Last () MySQL also does not support the final function, the function is to get the table in the specified column of data, MySQL can curve implementation, such as the first to obtain the largest ID, and then the last column according to the ID of the data max Minmax and min return the specified column maximum and minimum value of data, For numeric values, it is size, and for strings, the Min is arranged before and after. Of The number string is the SUM function of the preceding sum of the alphabetic string, and the sum of a column of the whole table is generally used by the group by indicating the grouping sum
Group the tables, and then sum the columns according to the group
SELECT column 1, SUM (column 2) from table GROUP by column 1
After grouping the table according to column 1, sum each group. Of course, you can also select columns without writing column 1, use column 1GROUP by in group by to group the table according to one column or columns, and general and aggregate functions
SELECT column 1, SUM (column 2) from table GROUP by column 1
Assuming that group by IS removed, column 1 shows in the original table that the sum column of each row is the sum value of the entire column 2 Havinghavingkeyword is used to make a selection of the result of the aggregate function, using Wherekeyword when the selection criteria are limited. But where cannot be used with aggregate functions, it is only possible to use the having with the aggregate function as the limiting precondition of the selection
SELECT column 1, SUM (column 2) from table WHERE Prerequisites have SUM (column 2) > value 1
Group selection out, group sum greater than value 1
SQL database Query Language
