Task01: getting to know database and SQL

catalogue
1, First knowledge database
1.1 types of DBMS
1.2 common system structure of RDBMS
1.3 database installation
1.3.1 introduction to Alibaba cloud MySQL server
1.3.2 introduction to building local MySQL environment
2, First knowledge of SQL
2.1 concept introduction
2.2 basic writing rules of SQL
2.3 database creation (CREATE DATABASE statement)
2.4 table creation (CREATE TABLE statement)
2.5 naming rules
2.6 designation of data type
2.7 setting of constraints
2.8 deletion and update of tables
2.9 inserting data into the product table
3, Exercises
3.1
3.2
3.3
3.4

This chapter mainly introduces the database. Considering ease of use and popularity, the course mainly uses MySql for introduction.

SQL training camp page address: AI training camp SQL - Alibaba cloud Tianchi
Tianchi Longzhu training camp address: AI training camp - Alibaba cloud Tianchi

1, First knowledge database

Database is a data set that can be accessed efficiently by saving a large amount of data and processing it by computer. This data set is called a database (DB). The computer system used to manage the database is called Database Management System (DBMS).

1.1 types of DBMS

DBMS is mainly classified by data storage format (type of database). At present, there are mainly the following five types

• Hierarchical   Database (HDB)
• Relational   Database (RDB)

This type of DBMS is called Relational Database Management System (RDBMS). There are five representative RDBMS.

* Oracle Database: Oracle RDBMS
* SQL Server: Microsoft RDBMS
* DB2: IBM Corporate RDBMS
* PostgreSQL: GPl  RDBMS
* MySQL: GPl  RDBMS

• Object Oriented Database (OODB)
• XML Database (XMLDB)
• Key value Store (KVS), for example: MongoDB

This course will introduce you to the operation method of database management system using SQL language, that is, relational database management system (RDBMS).

1.2 common system structure of RDBMS

When using RDBMS, the most common system structure is the client / server type (C/S type) (Figure 1-3)

1.3 database installation (must learn)

In this study, you can choose to use Alibaba cloud database server or install the database locally. The corresponding learning tutorial below also tells you how to create the database tables and data required for this study. Therefore, you must install the database in one way to complete the following study.

1.3.1 introduction to Alibaba cloud MySQL server

Save space, and write the specific introduction and pdf to you. You can click the link to view:
http://tianchi-media.oss-cn-beijing.aliyuncs.com/dragonball/SQL/other/ Introduction to Alibaba cloud MySQL server.pdf
advantage:   Easy to operate and use, future trend (data on the cloud), easy to import and export data and fast running speed.
Disadvantages:   You need to pay for it, but now there are preferential activities for developers. The basic version has 1 core and 1G, and the storage space is 20G. At present, the preferential price is only 9.9 yuan for half a year, and less than a cup of milk tea.

1.3.2 introduction to building local MySQL environment

Save space, and write the specific introduction and pdf to you. You can click the link to view:
http://tianchi-media.oss-cn-beijing.aliyuncs.com/dragonball/SQL/other/ Introduction to building local MySQL environment.pdf
advantage:   Free, enhance hands-on ability.
Disadvantages:   Installation and configuration are troublesome, and data import and export take a long time.

2, First knowledge of SQL

2.1 concept introduction

The table structure stored in the database is similar to the rows and columns in excel. In the database, rows are called records, which is equivalent to a record, and columns are called fields, which represent the data items stored in the table.

The place where rows and columns meet is called a cell. Only one record can be entered in a cell.

SQL is a language developed for operating databases. The international organization for Standardization (ISO) has formulated corresponding standards for SQL. SQL based on this is called standard SQL (please refer to the column - standard SQL and specific SQL for relevant information).

There are few RDBMS completely based on standard SQL. It is usually necessary to write specific SQL statements according to different RDBMS. In principle, this course introduces the writing method of standard SQL.

According to the different types of instructions given to RDBMS, SQL statements can be divided into the following three categories

• 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

• 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

• 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

90% of the SQL statements actually used belong to DML. This course will focus on DML.

2.2 basic writing rules of SQL

• 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

'abc', 1234, '26 Jan 2010', '10/01/26', '2010-01-26'...

• Words need to be separated by half width spaces or line breaks

Half width spaces or line breaks should be used to separate words in SQL statements, and full width spaces should not be used as word separators, otherwise errors will occur and unexpected results will appear.

Please refer to Appendix 1 - SQL syntax specification carefully to develop standard writing habits.

2.3 database creation (CREATE DATABASE statement)

Syntax:

CREATE DATABASE < Database name > ;

Create a database for this course

CREATE DATABASE shop;

2.4 table creation (CREATE TABLE statement)

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> ,......);

Create the product list used in this course

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)
 )  ;

2.5 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

Table 1-3 correspondence between column names in product table and product table

2.6 designation of 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).

2.7 setting of constraints

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.

2.8 deletion and update of tables

• 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.

2.9 inserting data into the product 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. You must not set the NOT NULL constraint for the column you 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 a DEFAULT value (initial value) into a 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;

3, Exercises

3.1

Write a CREATE TABLE statement to create a table Addressbook containing the items listed in table 1-A, and set the primary key constraint for the register_no column

Table 1-A columns in Addressbook

3.2

Suppose you forgot to add the following column postal_code when creating the Addressbook table in Exercise 1.1, please add this column to the Addressbook table.

Column name: postal_code

Data type: fixed length string type (length 8)

Constraint: cannot be NULL

3.3

Write an SQL statement to drop the Addressbook table.

3.4

Write an SQL statement to recover the deleted Addressbook table.

The answer to this exercise can be found in Tianchi official account: Tianchi big data research platform.   Reply: SQL training camp.

>Author: Datawhale members: Wang Fuzhen, Yang Yu, Yan Zhongfeng, Yang Mengdi, Su Peng

If you have any questions about this study, welcome to join Alibaba cloud Tianchi Longzhu program SQL training camp for learning and exchange.