This note is the learning content of Alibaba cloud Tianchi Longzhu plan SQL training camp. The link is: https://tianchi.aliyun.com/specials/promotion/aicampsql；

1, First knowledge of SQL

SQL statements can be divided into the following three categories:

1. DDL

DDL (Data Definition Language) is used to create or delete databases for storing data and tables in databases. DDL contains the following instructions.

• CREATE: CREATE objects such as databases and tables

• DROP: delete objects such as databases and tables

• ALTER: modify the structure of objects such as databases and tables

2. DML

DML (Data Manipulation Language) is used to query or change records in a table. The DML contains the following instructions.

• SELECT: query the data in the table

• INSERT: inserts new data into the table

• UPDATE: updates the data in the table

• DELETE: deletes data in a table

3. DCL

DCL (Data Control Language) is used to confirm or cancel changes to the data in the database. In addition, you can also set whether the RDBMS user has permission to operate objects (database tables, etc.) in the database. DCL contains the following instructions.

• COMMIT: confirm the changes made to the data in the database

• ROLLBACK: cancels changes to data in the database

• GRANT: GRANT user operation permission

• REVOKE: cancels the user's operation permission

2, Writing rules

• The SQL statement should end with a semicolon (;)
• SQL is not case sensitive for keywords, but the data inserted into the table is case sensitive
• The win system does not distinguish the case of table names and field names by default
• linux / mac strictly distinguishes the case of table names and field names by default
• This tutorial has uniformly adjusted the table name and field name to lowercase, so as to facilitate beginners to learn and use.
• The way constants are written is fixed
• Words need to be separated by half width spaces or line breaks

3, Create

1. Database creation

Syntax:

CREATE DATABASE < Database name > ;

2. Table creation

Syntax:

CREATE TABLE < Table name >
( < Column name 1> < data type > < Constraints required for this column > ,
  < Column name 2> < data type > < Constraints required for this column > ,
  < Column name 3> < data type > < Constraints required for this column > ,
  < Column name 4> < data type > < Constraints required for this column > ,
  .
  .
  .
  < Constraint 1 for this table> , < Constraints for this table 2> ,......);

3. Naming rules

• Only half width English letters, numbers and underscores () can be used as the names of databases, tables and columns
• The name must begin with a half width English letter

4. Data type

For a table created in a database, all columns must specify a data type, and each column cannot store data that does not match the data type of the column.

Four basic data types

• INTEGER type

Used to specify the data type (numeric) of the column where integers are stored. Decimal numbers cannot be stored.

• CHAR type

It is used to store fixed length strings. When the length of the string stored in the column is less than the maximum length, half width spaces are used to supplement. It is generally not used because it will waste storage space.

• VARCHAR type

It is used to store variable length strings. Fixed length strings will be supplemented with half width spaces when the number of characters does not reach the maximum length, but variable length strings are different. Even if the number of characters does not reach the maximum length, half width spaces will not be supplemented.

• DATE type

The data type (date type) of the column used to specify the storage date (month, year, day).

5. Constraint setting

Constraint is the function of restricting or appending conditions to the data stored in the column in addition to the data type.

NOT NULL is a non NULL constraint, that is, the column must enter data.

PRIMARY KEY is a PRIMARY KEY constraint, which means that the column is a unique value. The data of a specific row can be retrieved through this column.

6. Delete and update

• Syntax for deleting tables:

DROP TABLE < Table name > ;

• Delete product table

It should be noted that the deleted table cannot be recovered and can only be reinserted. Please be very careful when deleting.

DROP TABLE product;

• ALTER TABLE statement to add columns

ALTER TABLE < Table name > ADD COLUMN < Definition of columns >;

• Add a list of products that can store 100 bit variable length strings_ name_ Pinyin column

ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);

• Delete the ALTER TABLE statement for the column

ALTER TABLE < Table name > DROP COLUMN < Listing >;

• Delete product_name_pinyin column

ALTER TABLE product DROP COLUMN product_name_pinyin;

The ALTER TABLE statement, like the DROP TABLE statement, cannot be restored after execution. Mistakenly added columns can be deleted by ALTER TABLE statement, or the tables can be deleted and recreated.
[extended content]

• Empty table contents

TRUNCATE TABLE TABLE_NAME;

Advantages: compared with drop``/``delete, truncate is the fastest to clear data.

• Update of data

Basic syntax:

UPDATE <Table name>
SET <Listing> = <expression> [, <Column name 2>=<Expression 2>...];  
WHERE <condition>;  -- Optional, very important.
ORDER BY clause;  --Optional
LIMIT clause; --Optional

When using update, pay attention to adding the where condition, otherwise all rows will be modified according to the statement

-- Modify all registration times
UPDATE product
   SET regist_date = '2009-10-10';  
-- Modify the unit price of some items only
UPDATE product
   SET sale_price = sale_price * 10
 WHERE product_type = 'kitchenware';  

Using UPDATE, you can also UPDATE the column to NULL (this UPDATE is commonly referred to as NULL emptying). At this time, you only need to write the value on the right of the assignment expression as NULL directly.

-- Update the registration date of the data (ball point pen) with commodity number 0008 to NULL  
UPDATE product
   SET regist_date = NULL
 WHERE product_id = '0008';  

Like the INSERT statement, the UPDATE statement can also use NULL as a value.
**However, only columns without NOT NULL constraint and primary key constraint can be cleared to null** If the column with the above constraint set is updated to null, an error will occur, which is the same as the INSERT statement.

Multi column update

The SET clause of the UPDATE statement supports multiple columns as UPDATE objects at the same time.

-- Basic writing, one UPDATE Statement updates only one column
UPDATE product
   SET sale_price = sale_price * 10
 WHERE product_type = 'kitchenware';
UPDATE product
   SET purchase_price = purchase_price / 2
 WHERE product_type = 'kitchenware';  

This writing method can get the correct results, but the code is cumbersome. You can simplify the code by merging.

-- Combined writing
UPDATE product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
 WHERE product_type = 'kitchenware';  

It should be clear that the columns in the SET clause can be not only two columns, but also three columns or more.

7. Insert data into the table

To learn the usage of the INSERT statement, we first create a table named productins. The table creation statement is as follows:

CREATE TABLE productins
(product_id    CHAR(4)      NOT NULL,
product_name   VARCHAR(100) NOT NULL,
product_type   VARCHAR(32)  NOT NULL,
sale_price     INTEGER      DEFAULT 0,
purchase_price INTEGER ,
regist_date    DATE ,
PRIMARY KEY (product_id)); 

Basic syntax:

INSERT INTO <Table name> (Column 1, Column 2, Column 3, ......) VALUES (Value 1, Value 2, Value 3, ......);  

When performing full column INSERT on a table, you can omit the column list after the table name. At this time, the VALUES of the VALUES clause will be assigned to each column from left to right by default.

-- Include column list
INSERT INTO productins (product_id, product_name, product_type, 
sale_price, purchase_price, regist_date) VALUES ('0005', 'pressure cooker', 'kitchenware', 6800, 5000, '2009-01-15');
-- Omit column list
INSERT INTO productins 
VALUES ('0005', 'pressure cooker', 'kitchenware', 6800, 5000, '2009-01-15');  

In principle, executing an INSERT statement once inserts a row of data. When inserting multiple rows, you usually need to cycle through the INSERT statement a corresponding number of times. In fact, many RDBMS support inserting multiple rows of data at one time

-- Usual INSERT
INSERT INTO productins VALUES ('0002', 'Punch', 
'Office Supplies', 500, 320, '2009-09-11');
INSERT INTO productins VALUES ('0003', 'motion T Shirt', 
'clothes', 4000, 2800, NULL);
INSERT INTO productins VALUES ('0004', 'kitchen knife', 
'kitchenware', 3000, 2800, '2009-09-20');
-- Multiline INSERT ( DB2,SQL,SQL Server, PostgreSQL and MySQL Multiline insert)
INSERT INTO productins VALUES ('0002', 'Punch', 
'Office Supplies', 500, 320, '2009-09-11'),
('0003', 'motion T Shirt', 'clothes', 4000, 2800, NULL),
('0004', 'kitchen knife', 'kitchenware', 3000, 2800, '2009-09-20');  
-- Oracle Multiple lines in INSERT
INSERT ALL INTO productins VALUES ('0002', 'Punch', 'Office Supplies', 500, 320, '2009-09-11')
INTO productins VALUES ('0003', 'motion T Shirt', 'clothes', 4000, 2800, NULL)
INTO productins VALUES ('0004', 'kitchen knife', 'kitchenware', 3000, 2800, '2009-09-20')
SELECT * FROM DUAL;  
-- DUAL yes Oracle A temporary table unique (required during installation) A. So“ SELECT *FROM DUAL" Part is only temporary and has no practical significance.  

When you want to assign a null value to a column in the INSERT statement, you can directly write null in the value list of the VALUES clause. The NOT NULL constraint must not be set for columns that want to INSERT null.

INSERT INTO productins (product_id, product_name, product_type, 
sale_price, purchase_price, regist_date) VALUES ('0006', 'Fork', 
'kitchenware', 500, NULL, '2009-09-20');  

You can also insert DEFAULT values (initial values) into the table. You can set the DEFAULT value by setting the DEFAULT constraint in the CREATE TABLE statement that creates the table.

CREATE TABLE productins
(product_id CHAR(4) NOT NULL,
((omitted)
sale_price INTEGER
((omitted)	DEFAULT 0, -- The default value of sales unit price is set to 0;
PRIMARY KEY (product_id));  

You can use the INSERT... SELECT statement to copy data from other tables.

-- Copy data from the item table to the item table
INSERT INTO productocpy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, 
purchase_price, regist_date
FROM Product;  

• This course uses tables to insert data sql as follows:

- DML : insert data
STARTTRANSACTION;
INSERT INTO product VALUES('0001', 'T shirt', 'clothes', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', 'Punch', 'Office Supplies', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', 'motion T Shirt', 'clothes', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', 'kitchen knife', 'kitchenware', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', 'pressure cooker', 'kitchenware', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', 'Fork', 'kitchenware', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', 'Dish cleaning board', 'kitchenware', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', 'ball pen', 'Office Supplies', 100, NULL, '2009-11-11');
COMMIT;