SQL---CHAPTER01 database and SQL

Database type:

• Hierarchical databases (hierarchical database, HDB)

The data is represented in a hierarchical structure (tree structure).

• relational databases (relational database, RDB)

Use specialized SQL (structured query Language, Structured Query Language) language to manipulate data information.

• Object-oriented databases (objects oriented database, OODB)

The data and the operation of the data are assembled and managed in the object unit.

• XML databases (XML database, XMLDB)

Large amounts of data in the form of XML can be processed at high speed.

• Key-Value Storage System (Key-value store, KVS) such as Redis

Simple to save the primary key used by the query and the database that is worth combining.

SQL statements and Types

• DDL (data definition Language) Definition language:

Create: Creating Database and Table objects

Drop: Delete database and Table objects

Alter: Modify the structure of the database and table objects

• DML (Data Manipulation Language database operating language) was 90%

SELECT

INSERT

UPDATE

DELETE

• DCL (Data Control Language database controls language)

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

ROLLBACK: Cancels the changes made to the data in the database.

Grant: gives the user permission to operate.

REVOKE: Cancels the user's operation permissions.

SQL Basic Writing Rules

The SQL statement ends with a semicolon (;).

SQL does not differentiate the case of keywords.

Writing rules:

1. 1. Keyword capitalization
   2. First word capitalization of table name
   3. Remainder (column name, etc.) lowercase

string and date constants need to be enclosed in single quotation marks ('). Numeric constants do not require a single quotation mark (write a number directly).

Use half-width spaces or line breaks between words to separate them.

Creation of tables

Database creation: CREATE DATABASE < DB name >;

Creation of tables:

CREATE table< Table name >

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

< column name 2> < data type > < constraints required for this column;

...

< constraints on the table 1>,< the constraint of the table 1>,...)

CREATE TABLEShohin (shohin_idCHAR(4) not NULL, Shohin_meiVARCHAR( -) not NULL, Shohin_bunruiVARCHAR( +) not NULL, Hanbai_tankaINTEGER, Shiire_tankaINTEGER, Torokubi DATE,PRIMARY KEY(shohin_id));

The following three characters can be used for database names, table names, and column names: half-width English letters, half-width numerals, and underscores (_).

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

Specifies the data type: INTEGER, CHAR (fixed-length string), VARCHAR (variable-length string), DATE

Constraint: NOT NULL, NULL, PRIMARY KEY.

Deletion and update of tables

Delete tables: drop table < table name >;

Add Column: ALTER table < table name > add column < column definition >;

Delete columns: ALTER table < table name > DROP column < columns name >;

Insert data:

STARTTRANSACTION;INSERT  intoShohinVALUES('0001','T-shirts','clothes', +, -,'2009-09-20');INSERT  intoShohinVALUES('0002','Hole Punching device','Office Supplies', -, the,'2009-09-11');INSERT  intoShohinVALUES('0003','Sports T-shirt','clothes',4000,2800,NULL);INSERT  intoShohinVALUES('0004','Chopper','Kitchen Utensils', the,2800,'2009-09-20');INSERT  intoShohinVALUES('0005','Pressure Cooker','Kitchen Utensils',6800, the,'2009-01-15');INSERT  intoShohinVALUES('0006','Fork','Kitchen Utensils', -,NULL,'2009-09-20');INSERT  intoShohinVALUES('0007','wiping the vegetable board','Kitchen Utensils',880,790,'2008-04-28');INSERT  intoShohinVALUES('0008','Ballpoint Pen','Office Supplies', -,NULL,'2009-11-11');COMMIT;

SQL---CHAPTER01 database and SQL