Introduction to Database

A database is a collection of data which can be accessed efficiently and stored in a large amount of data by computer processing. This data set is called a Database (DB). The computer System used to manage databases is called a Database Management System (DBMS).

species

  • Hierarchical database
  • Relational database
    • Oracle Database: Oracle’s RDBMS
    • SQL Server: Microsoft’s RDBMS
    • DB2: IBM’s RDBMS
    • PostgreSQL: Open source RDBMS
    • MySQL: Open source RDBMS
  • Object-oriented database
  • XML database
  • Key-value store database

Common system structures for relational databases

When using an RDBMS, the most common system structure is the client/server type (C/S type)

Database Installation

  • Ali cloud MySQL server use tianchi-media.oss-cn-beijing.aliyuncs.com/dragonball/ described…
  • Local MySQL environment building methods introduced tianchi-media.oss-cn-beijing.aliyuncs.com/dragonball/…

I met the SQL

The concept is introduced

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

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

SQL statements can be divided into the following three categories:

  1. DDL

Data Definition Language (DDL) is used to create or delete objects such as databases and tables used to store Data. The DDL contains the following instructions

  • CREATE: Creates objects such as databases and tables
  • DROP: Deletes objects such as databases and tables
  • ALTER: Alters the structure of objects such as databases and tables
  1. DML

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

  • SELECT: queries the data in the table
  • INSERT: Inserts new data into a table
  • UPDATE: Updates data in a table
  • DELETE: deletes data from a table
  1. DCL

The Data Control Language (DCL) is used to confirm or cancel changes to Data in the database. In addition, you can specify whether users of the RDBMS have permission to manipulate objects in the database (database tables, etc.). The DCL contains the following instructions.

  • COMMIT: Confirm changes to data in the database
  • ROLLBACK: Cancels changes to data in the database
  • GRANT: GRANT operation rights to the user
  • REVOKE: Revokes a user’s operation rights

90% of the SQL statements actually used are DML, and this course will focus on DML

Basic writing rules for SQL

  • SQL statements begin with a semicolon (;). At the end
  • SQL is not case-sensitive for keywords, but data inserted into a table is case sensitive
  • By default, win does not respect the case of table and field names
  • By default, Linux/MAC is case-sensitive on table names and field names
  • This tutorial has adjusted the table name and field name to lowercase, to facilitate beginners to learn to use.
  • Constants are written in a fixed way
  • Words need to be separated by half-space or line feeds

DATABASE creation (CREATE DATABASE statement)

Grammar:

CREATE DATABASE <Database name>
Copy the code

Create a database for use in this course

CREATE DATABASE shop;
Copy the code

TABLE creation (CREATE TABLE statement)

Grammar:

CREATE TABLE <The name of the table>
( <The column name1> <The data type> <This column requires constraints> ,
 <The column name2> <The data type> <This column requires constraints> ,
 <The column name3> <The data type> <This column requires constraints> ,
 <The column name4> <The data type> <This column requires constraints>.<Constraints on this table1> , <Constraints on this table2>,...) ;Copy the code

Create a list of items to use 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)
)  ;
Copy the code

Naming rules

  • The database, table, and column names can contain only letters, digits, and underscores (_)
  • The name must start with a letter

Specifies the data type

All columns of a table created by a database must have a specified data type, and each column cannot store data that does not match the data type of the column.

  1. INTEGER type

Used to specify the data type (numeric) of the column that stores integers, not decimals. 2. CHAR is used to store strings of fixed length. If the length of the string stored in a column does not reach the maximum length, half-corner Spaces are used to complement the string. 3. VARCHAR is used to store strings of variable length. If the number of fixed characters does not reach the maximum length, half-corner Spaces will be used to supplement the fixed characters. 4. The DATE type specifies the data type (DATE type) of the column that stores the DATE (year month day).

Constraint setting

Constraints are the ability to restrict or append conditions to the data stored in a column in addition to the data type.

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

The PRIMARY KEY is a PRIMARY KEY constraint that indicates that this column is a unique value that can be used to fetch data for a particular row.

Table deletion and update

  • Delete table syntax
DROP TABLE <The name of the table> ;
Copy the code

Ex. :

Note A deleted table cannot be restored and can only be inserted again. Exercise caution when deleting a table.

DROP TABLE product;
Copy the code
  • Add an ALTER TABLE statement for columns
ALTER TABLE <The name of the table> ADD COLUMN <The definition of the column>;
Copy the code

Example: Add a product_name_pinyin column that can store 100-bit variable-length strings

ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);
Copy the code
  • Drop the ALTER TABLE statement for the column
ALTER TABLE <The name of the table> DROP COLUMN <The column name>;
Copy the code

Delete product_name_pinyin column

ALTER TABLE product DROP COLUMN product_name_pinyin;
Copy the code

An ALTER TABLE statement, like a DROP TABLE statement, cannot be restored after execution. Columns added by mistake can be deleted using the ALTER TABLE statement, or the TABLE can be completely deleted and then created again.

  • Clear table contents
TRUNCATE TABLE TABLE_NAME;
Copy the code

Advantages: Compared with DROP/DELETE, TRUNCate deletes data with the highest speed.

  • Update of data

Basic syntax:

UPDATE <The name of the table>
SET <The column name> = <expression> [, <The column name2> =<expression2>...];  
WHERE <conditions>;  -- Optional, very important.
ORDER BYClause;- optionalLIMIT clause;- optional
Copy the code

When using update, be careful to add the WHERE condition, otherwise all rows will be modified according to the statement

-- Change all registration times
UPDATE product
   SET regist_date = '2009-10-10';  
-- Only modify the unit price of some goods
UPDATE product
   SET sale_price = sale_price * 10
 WHERE product_type = 'Kitchen appliances'; 
Copy the code

Columns can also be updated to NULL using UPDATE (colloquially known as NULL emptying). Write the value to the right of the assignment expression as NULL.

-- Update the registration date of item 0008 (ballpoint pen) to NULL
UPDATE product
   SET regist_date = NULL
 WHERE product_id = '0008';  
Copy the code

Like INSERT statements, UPDATE statements can use NULL as a value.

** However, only columns that do NOT have NOT NULL constraints and primary key constraints can be cleared to NULL. ** If you update a column with the above constraint to NULL, you will get an error, as with the INSERT statement.

  • More columns to update

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

In basic form, an UPDATE statement updates only one column
UPDATE product
  SET sale_price = sale_price * 10
WHERE product_type = 'Kitchen appliances';
UPDATE product
  SET purchase_price = purchase_price / 2
WHERE product_type = 'Kitchen appliances'; 
Copy the code

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

-- written after merging
UPDATE product
   SET sale_price = sale_price * 10,
       purchase_price = purchase_price / 2
 WHERE product_type = 'Kitchen appliances';  
Copy the code

To be clear, the columns in the SET clause can be not just two columns, but three or more.

Insert data into the Product table

Create table productins (productins, productins, productins, productins, productins)

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));
Copy the code

Basic syntax:

INSERT INTO <The name of the table>(column1Column,2Column,3, ……) VALUES(value1And the value2And the value3,...) ;Copy the code

When doing a full column INSERT into a table, you can omit the column list after the table name. By default, the VALUES clause is assigned to each column in left-to-right order.

-- Contains lists
INSERT INTO productins (product_id, product_name, product_type, 
sale_price, purchase_price, regist_date) VALUES ('0005'.Pressure cooker.'Kitchen appliances'.6800.5000.'2009-01-15');
-- Omit the list
INSERT INTO productins 
VALUES ('0005'.Pressure cooker.'Kitchen appliances'.6800.5000.'2009-01-15');  
Copy the code

In principle, an INSERT statement inserts a row of data. When inserting multiple rows, you typically need to loop through INSERT statements a corresponding number of times. In fact, many RDBMSS support multiple rows of data being inserted at once

-- The usual INSERT
INSERT INTO productins VALUES ('0002'.'hole punch'.'Office supplies'.500.320.'2009-09-11');
INSERT INTO productins VALUES ('0003'.'Sport T-shirt'.'clothes'.4000.2800.NULL);
INSERT INTO productins VALUES ('0004'.'chopper'.'Kitchen appliances'.3000.2800.'2009-09-20');
DB2, SQL, SQL Server, PostgreSQL, MySQL
INSERT INTO productins VALUES ('0002'.'hole punch'.'Office supplies'.500.320.'2009-09-11'),
('0003'.'Sport T-shirt'.'clothes'.4000.2800.NULL),
('0004'.'chopper', 'Kitchen appliances'.3000.2800.'2009-09-20');  
-- Multiple rows of inserts in Oracle
INSERT ALL INTO productins VALUES ('0002'.'hole punch'.'Office supplies'.500.320.'2009-09-11')
INTO productins VALUES ('0003'.'Sport T-shirt'.'clothes'.4000.2800.NULL)
INTO productins VALUES ('0004'.'chopper'.'Kitchen appliances'.3000.2800.'2009-09-20')
SELECT * FROM DUAL;  
-- DUAL is an Oracle-specific (mandatory) temporary table A. Therefore, the "SELECT *FROM DUAL" section is only temporary and has no practical significance.
Copy the code

When you want to assign a NULL value to a column in an INSERT statement, you can write NULL directly to the list of VALUES in the VALUES clause. Columns that want to insert NULL must NOT set the NOT NULL constraint.

INSERT INTO productins (product_id, product_name, product_type, 
sale_price, purchase_price, regist_date) VALUES ('0006'.'the fork.'Kitchen appliances'.500.NULL.'2009-09-20'); 
Copy the code

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_priceINTEGER(abbreviated)DEFAULT 0.-- The unit price is set to 0 by default;
PRIMARY KEY (product_id)); 
Copy the code

You can use INSERT… The SELECT statement copies data from other tables.

Copy data from the commodity table to the commodity replication 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;  
Copy the code

SQL > insert data into table;

-DML: insert data STARTTRANSACTION;INSERT INTO product VALUES('0001'.'t-shirts'.'clothes'.1000.500.'2009-09-20');
INSERT INTO product VALUES('0002'.'hole punch'.'Office supplies'.500.320.'2009-09-11');
INSERT INTO product VALUES('0003'.'Sport T-shirt'.'clothes'.4000.2800.NULL);
INSERT INTO product VALUES('0004'.'chopper'.'Kitchen appliances'.3000.2800.'2009-09-20');
INSERT INTO product VALUES('0005'.Pressure cooker.'Kitchen appliances'.6800.5000.'2009-01-15');
INSERT INTO product VALUES('0006'.'the fork.'Kitchen appliances'.500.NULL.'2009-09-20');
INSERT INTO product VALUES('0007'.'Chopping board'.'Kitchen appliances'.880.790.'2008-04-28');
INSERT INTO product VALUES('0008'.'Ballpoint pen'.'Office supplies'.100.NULL.'2009-11-11');
COMMIT;
Copy the code