SQL --- Chapter01 database and SQL, SQL --- chapter01sql

SQL --- Chapter01 database and SQL, SQL --- chapter01sql
Database Type:

• Hierarchical Database (HDB)

Data is displayed in a hierarchical structure (tree structure.

• Relational Database (RDB)

Use a special Structured Query Language (Structured Query Language) to operate data information.

• Object-Oriented Database (OODB)

Manages data and data operations in units of objects.

• XML Database (XML Database, XMLDB)

It can process a large amount of data in XML format at high speed.

• Key-Value Store (KVS) systems such as redis

It is simply used to save the primary key used for the query and the database worth combining.

SQL statements and types

• Data Definition Language (DDL:

CREATE: CREATE a database and table object

DROP: deletes database and table objects.

ALTER: modify the structure of database and table objects

• DML (Data Manipulation Language database operating Language) accounts for 90%

SELECT

INSERT

UPDATE

DELETE

• DCL (Data Control Language Database Control Language)

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

ROLLBACK: cancels changes to data in the database.

GRANT: GRANT the user operation permissions.

REVOKE: cancels the user's operation permission.

Basic SQL writing rules

The SQL statement ends with a semicolon.

SQL statements are case insensitive to keywords.

Writing rules:

The string and date constant must be enclosed by single quotation marks. The numeric constant does not need to be enclosed in single quotes (you can directly write a number ).

Words must be separated by spaces or line breaks.

 

Table Creation

DATABASE creation: create database <DATABASE Name>;

Table creation:

Create table <TABLE Name>

(<Column name 1> <data type> <constraints required for this column>,

<Column name 2> <data type> <constraints required for this column>,

...

<Constraint 1>, <constraint 1> ,...)

CREATE TABLE Shohin(shohin_id     CHAR(4)      NOT NULL, shohin_mei    VARCHAR(100) NOT NULL, shohin_bunrui VARCHAR(32)  NOT NULL, hanbai_tanka  INTEGER , shiire_tanka  INTEGER , torokubi      DATE , PRIMARY KEY (shohin_id));

The database name, table name, and column name can contain the following three characters: letters, numbers, and underscores (_).

The name must start with a half-width English letter.

 

Data Types: INTEGER, CHAR (fixed-length string), VARCHAR (variable-length string), and DATE

Constraint: not null, NULL, and primary key.

 

 

Table deletion and update

Delete TABLE: drop table <TABLE Name>;

Add column: alter table <TABLE Name> add column <COLUMN definition>;

Delete COLUMN: alter table <TABLE Name> drop column <COLUMN Name>;

Insert data:

Start transaction; insert into Shohin VALUES ('123456', 't', 'clothes ', 0001, '1996-09-20'); insert into Shohin VALUES ('201709-20 ', 'puncher ', 'Office supplies', 500,320, '2017-09-11 '); insert into Shohin VALUES ('20170901', 'Sports T-shirt', 'clothes ', 2009 0, NULL); insert into Shohin VALUES ('20170101', 'kitchen utensils ', 0004 0, '2017-09-20'); insert into Shohin VALUES ('201709-20 ', 'pressure cooker', 'kitchen utensils ', 6800,500 0, '2017-01-15'); insert into Shohin VALUES ('201701', 'fort', 'kitchen utensils ', 2009, NULL, '2017-09-20 '); insert into Shohin VALUES ('20170901', 'chopping board', 'kitchen utensils ', 2009, '2017-04-28 '); insert into Shohin VALUES ('20170101', 'ballpoint pen ', 'Office supplies', 0008, NULL, '2017-11-11 '); COMMIT;