Summary of SQL Foundation operations

Summary of SQL Foundation operations

First, table operation

1. Creation of table (CREATE TABLE):

Basic statement format: CREATE TABLE table_name (col_name datatype, ... ）;

Explanation: table_name is the name of the table to be created (the table name must be unique for the same logged-on user), Col_name is the name of the property in the table, datatype is the corresponding data type for the property, and there are many fields in the same table. You can also append various constraints (see constraints) After the field data type.

Cases:

2. Modification of the table (ALTER table):

explanation : All statements that modify the table begin with alter table+ the name of the table to be modified, followed by a series of operations that only need to append the corresponding keyword statement, such as adding a field

ALTER TABLE table_name ADD col_name datatype

(Modify table name) Basic statement format :RENAME table_name to new_table_name; (To front of the original table name, followed by the new table name)

Cases:

(add field) Basic statement format :ALTER TABLE table_name ADD col_name datatype; (the Add keyword is used for adding fields)

Cases:

(Modify field name) Basic statement format :ALTER TABLE table_name RENAME COLUMN col_name to New_col_name; (RENAME column is the keyword that modifies the field name, followed by the new name, preceded by the original name)

Cases:

(Modify field data type) Basic statement format :ALTER TABLE tbale_name MODIFY col_name datatype;

Cases:

(delete field) basic statement format : ALTER TABLE table_name DROP COLUMN col_name; (drop column is the keyword deleted by the field)

Cases:

3. Deletion of table (drop table\truncate table):

① truncate DELETE table: Only the data in the table is emptied, the table is not deleted, and the fields in the table are not deleted.

Basic Statement Format :TRUNCATE TABLE table_name;

Cases:

② Delete a table completely: Deletes the entire table, including the structure and fields of the data and tables in the table.

Basic Statement Format :DROP TABLE table_name;

Cases:

4. Insert the data (INSERT INTO):

① Pure Insertion method

Basic statement format:INSERT into table_name (col_name1,col_name2 ...) VALUES (Value1,value2 ... );

Explanation: Values in parentheses are set of field names followed by corresponding values, the data type of the value must be the same as the data type of the field, otherwise it cannot be added, and the specified field can be added according to the contents of the values in parentheses, for example: Write only one username field, Values in parentheses only one "Zhangsan", that is, to add a value to a single field

Cases:

② Copying table data

Basic statement format (copy when creating table):CREATE table table_new as SELECT col_name1,col_name2 ... | * from Table_old;

Explanation: Copy the data from the Table_old table to the new table, you can specify the field, or copy all, copy all using *, specify the field name.

Cases:

(Copy on Add) Basic statement format: INSERT intotable_new (col_name1,col_name2 ...) SELECT col-name1,col-name2 ... | * FROM Table_old;

Cases:

5. Modify the data in the table (UPDATE):

(unconditional Update) BASIC statement format:update table_name SET col_name1 = value1,col_name2=value2...; (This method updates all the data in the table)

Cases:

This example sets all password field values to 11111

(conditional update) BASIC statement format:update table_name SET col_name1=value1,col_name2=value2. WHERE col-name=value ...

Cases:

6. Delete the table data (delete):

① Delete all data:

Basic statement format:DELETE from table_name; This sentence is consistent with the result truncated above, deleting all data in the table.

② Delete data for a specific row:

Basic statement format:DELETE from table_name WHERE col_name=value...;

Cases:

Ii. constraints

1. Primary KEY constraint (PRIMARY | CONSTRAINT)

Note: A table can have only one primary KEY constraint, and a PRIMARY key constraint can consist of multiple fields (federated primary key, composite primary key). Primary KEY constraints are implemented according to the keyword PRIMARY key and CONSTRAINT

To add a primary key constraint when creating a table:

①create TABLE table_name (col_name1 datatype PRIMARY KEY); //When creating a table, assign a primary key directly to a property

②constraint constraint_name PRIMARY KEY (col_name1,col_name2..); /table-level constraints, typically when you create a table, when all the fields are finished, append the statement to the following example:

CREATE TABLE UserInfo (

ID Number (6,0),

Username VARCHAR2 (20),

Rank VARCHAR2 (10),

Constraint Pk_key (id,username),

); Where Pk_key is an artificially named primary key name

Add primary KEY constraint when modifying table: Add CONSTRAINT constraint_name PRIMARY key (col_name1,col_name2..); /generally followed by the ALTER table table table_name, the primary key is appended, the following example:

ALTER TABLE userinfo ADD CONSTRAINT pk_id (ID);

Modify PRIMARY KEY constraint name: RENAME CONSTRAINT old_name to New_name; Use the change of the primary key name after the ALTER TABLE TABLE_NAME, as in the following example:

ALTER TABLE userinfo RENAME CONSTRAINT pk_id to Pk_newid;

To delete a PRIMARY KEY constraint:

①drop CONSTRAINT constraint_name; Completely remove the primary key constraint named Constraint_name, followed by ALTER TABLE TABLE_NAME.

②disable| ENABLE CONSTRAINT constraint_name;//this way for disable | The PRIMARY KEY constraint is turned on, and there is no physical deletion, which is also followed by the ALTER TABLE TABLE_NAME statement.

2. FOREIGN KEY constraint (REFERENCES)

To add a foreign key constraint when creating a table:

①create TABLE table1 (col_name1 datatype REFERENCES table2 (col-name));//through the keyword REFERENCES, select the field in Table2 as the foreign key, The referenced field must be a primary key of table2

②constraint constraint_name FOREIGN KEY (col_name1,col_name2 ...) REFERENCES table2 (Col-name,col-name) [on DELETE CASCADE]//table-level settings, also specify cascade Delete, so-called cascade Delete is, our main table is a piece of data is deleted, This row of data that references the Main table field from the tables is also deleted (via the on Delete CASCADE).

Setting foreign KEY constraints when modifying tables: ADD CONSTRAINT constraint_name FOREIGN key (Col_name1,col_name2 ...) REFERENCES table2 (Col-name,col-name) [on DELETE CASCADE]//followed by modify table statement after use

To delete a foreign KEY constraint:

①drop CONSTRAINT constraint_name; Completely remove the outer constraint named Constraint_name, followed by ALTER TABLE TABLE_NAME.

②disable| ENABLE CONSTRAINT constraint_name;//this way for disable | The foreign KEY constraint is turned on, and there is no physical deletion, which is also followed by the ALTER TABLE TABLE_NAME statement.

3, non-empty constraint (NOT null)

Add non-null constraint when creating: Create TABLE table_name (col_name datype not NULL);

Add non-null constraint when modifying table:ALTER table Tbale_name MODIFY col_name datatype not null;

Delete non-null constraint:ALTER TABLE tbale_name MODIFY col_name datatype NULL;

4. Unique constraint (unique)

NOTE: A unique constraint can have multiple, and a PRIMARY key constraint is limited to one, and both have a unique limit

Add non-null constraint when creating: Create TABLE table_name (col_name datype UNIQUE); or CONSTRAINT constraint_name UNIQUE (col-name,col-name);

Add non-null constraint when modifying table:ALTER table tbale_name add CONSTRAINT constraint_name UNIQUE (col-name,col-name);

Delete non-null constraint:disable|  ENABLE CONSTRAINT constraint_name; or DROP CONSTRAINT constraint_name;

5. Check Constraint

NOTE: Check the constraint is to limit the actual meaning of the data, such as real life wages will not appear negative, so in the table design, the wage field will be set to check the constraint, so that it is greater than or equal to 0

Add check constraint when creating table: CreateTable table_name (col_name datype Check (specific constraints)); or CONSTRAINT constraint_name CHECK (condition);

Add check constraint when modifying table:ALTER table tbale_name add CONSTRAINT constraint_name Check (condition);

Delete Check constraint:disable|  ENABLE CONSTRAINT constraint_name; or DROP CONSTRAINT constraint_name;

Third, query

1. Operators

Arithmetic operators: + 、-、 *,/

Comparison operators:>, >=, <, <=, =, <> Note: Mainly used after the WHERE keyword, the result is a Boolean value

Logical operators: Not, and, or to link conditions, corresponding to and, or, non.

expression = operand + operator The operand here can be a variable, a constant, a field in Oracle

2. Basic Lookup Statement (SELECT)

Basic statement format: SELECT col-name1,col-name2 ... | * FROM TABLE_NAME;

3. Conditional query (SELECT .... WHERE ...)

Basic statement format: SELECT col-name1,col-name2 ... | * FROM table_name WHERE specific conditional Expressions

Example: SELECT username,password from UserInfo WHERE id>=1 and Salary >500; Account and password information for all users with a query ID greater than 1 and a salary greater than 500

4. Fuzzy query (like)

The LIKE operator can be classified as one of the comparison operators, with the LIKE keyword mated to the wildcard character ' _,% ' to form an expression that implements a fuzzy query, where it is placed behind.

"_": A ' _ ' can replace only one character, such as A_, which represents the first letter A, followed by a combination of only one character.

"%": represents 0 or more characters, unlimited number, for example%a, representing all combinations of characters at the end of a

Basic statement format: SELECT * | Col-name1,col-name2 ... From table_name WHERE col-name like ' wildcard information '

For example: SELECT * from UserInfo WHERE username like ' a% '//query All information of user name with a first letter

SELECT email from userinfo WHERE username like ' _a% '//query user name The second character is a mailbox

SELECT username from UserInfo WHERE username like '%a% '//query user names for all users with a letter in the user name

5. Range inquiry (between ..... And .... )

Mainly through the combination of between and to form an attribute of the range of conditions, and the previous condition query using the <,> operator for conditional query effect consistent, while the combination of conditional expressions can also be combined with not, such as not between and 50, which is a range of less than 30 and greater than 50

Basic statement format: SELECT * | Col-name1,col-name2 ... From table_name WHERE col-name between? and?;

6, and Table query

and table query is mainly attached to the table of links (JOIN), table links are divided into, inner joins, outer joins, cross-connect.

INNER JOIN BASIC statement format: SELECT *from table1 [inner] join table2 on table1. field 1=table2. field 1 ...;

INNER JOIN BASIC Statement format 2:select * from Table1,table2 WHERE table1. field 1=table2. field 1 ...;

Cross join: Cartesian product between table and table, SELECT * from Table1,table2 ....

Outer connection can be subdivided into left outer connection, right outer connection and full connection

① left outer connection: OUTER Join

All the data in the table on the left will be displayed, but the data on the right side of the table will be queried only if the fields on the left side match! Otherwise it will show null!

Basic Statement Format 1:SELECT * FROM table1 left OUTER joins table2 on table1.col-name1=table2.col-name1;

Basic Statement Format 2:select* from table1 left OUTER JOIN table2 where table1.col-name1=table2.col-name1;

② right outer connection: OUTER Join

All the data in the table on the right will be displayed, but the data on the left table will be queried only if the fields on the right side match! Otherwise it will show null!

Basic Statement Format 1: SELECT * FROM table1-OUTER JOIN table2 on table1.col-name1=table2.col-name1;

Basic Statement Format 2: select* from table1 right OUTER JOIN table2 where table1.col-name1=table2.col-name1;

③ full outer connection: Full/all OUTER Join

Basic Statement format:SELECT * FROM table1 full OUTER joins table2 on table1.col-name1=table2.col-name1;

Summary of SQL Foundation operations