1. Database and SQL

1-1 introduction to database

Our common mysql, SQL Server, DB2, Oracle Database and Postgresql all belong to relational database management system (RDBMS)

The tables stored in the database are all sheets, which can be compared to excel data sheets. They are basically the same, just like that.

1-2 introduction to SQL

There are three types of sql statements:

1. DDL (Data Definition Language)

   • CREATE: CREATE databases and tables

   • DROP: delete databases and tables

   • ALTER: modify database and target structure

2. DML (Data Manipulation Language)

   • SELECT: query

   • INSERT: add

   • UPDATE: Change

   • DELETE: DELETE

   sql language is the most commonly used three board axe

3. DCL (Data Control Language)

   • COMMIT: confirm changes

   • ROLLBACK: cancel change

   • GRANT: GRANT operation permission

   • REVOKE: cancel operation permission

90% of DML in daily production and life belongs to three board axe

1-3. Create table

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

The official provides all examples and codes of after-school questions, but it is recommended that you type them by hand ~

1-4. Insert data into the table

-- DML:insert data

BEGIN TRANSACTION;

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;

In different database software, the writing and usage of some statements are different. If the operation reports an error, remember to check how the statements of your software are written

2. Query basis

Output all columns:

SELECT *
  FROM Product;

Output the specified column:

SELECT product_id, product_name, product_type, sale_price,
       purchase_price, regist_date
  FROM Product;

The above two are equivalent.

Select records according to the WHERE statement

SELECT product_name, product_type

  FROM Product

 WHERE product_type = 'clothes';

The writing order of SQL sub sentences is fixed and cannot be changed at will. For example, the WHERE clause must be immediately followed by the FROM clause, otherwise an error will be reported.

How to write notes

Single line note:--

Multiline comment: / **/

This multiline comment is much like the multiline comment of css

Comparison operator

SELECT product_name, product_type
  FROM Product
 WHERE sale_price = 500;

Not equal to < >

Precautions for using unequal signs on strings

Create a test table

-- DDL: Create table
CREATE TABLE Chars
(chr CHAR(3) NOT NULL,
PRIMARY KEY (chr));

--SQL Server PostgreSQL
-- DML: insert data
BEGIN TRANSACTION;
INSERT INTO Chars VALUES ('1');
INSERT INTO Chars VALUES ('2');
INSERT INTO Chars VALUES ('3');
INSERT INTO Chars VALUES ('10');
INSERT INTO Chars VALUES ('11');
INSERT INTO Chars VALUES ('222');
COMMIT;

Filter data greater than '2'

SELECT chr
  FROM Chars
 WHERE chr > '2';

You will get a magical result:

chr
-----
3
222

2 is different from '2'! Strings are compared in dictionary order, so the order of the above test table is:

• 1
• 10
• 11
• 2
• 222
• 3

Special NULL

• NULL, regardless of addition, subtraction, multiplication and division, the result is NULL, and even NULL divided by zero will not report an error. The result is still NULL... It is so magical that it absorbs everything like a black hole and becomes itself... (emmmm... I guess)

• NULL cannot use the comparison operator. You must use IS NULL or IS NOT NULL

  SELECT product_name, purchase_price
    FROM Product
   WHERE purchase_price IS NULL;
  

Logical operator

AND, OR, NOT will NOT be introduced, pediatrics. Note that the AND operator takes precedence over the OR operator

SELECT product_name, product_type, regist_date
  FROM Product
 WHERE product_type = 'Office Supplies'
   AND (   regist_date = '2009-09-11'
        OR regist_date = '2009-09-20');

Therefore, when you need to query the registration time is 2009-09-11 or 2009-09-20, you need to add parentheses to prevent the query from finding wrong results.
To be continued