[SQL] SQL Basics Grooming (i)-Database and SQL

SQL Basics Grooming (i)-Database and SQL "Bo Master" anti-bone Aberdeen "original address" http://www.cnblogs.com/liqingwen/p/5902856.html sequence

Directory

• What ' s database
• Database structure
• SQL Overview
• Create a table
• deleting and updating tables

1-1 What ' s database

1. Database (DATABASE,DB): A collection of data that can be efficiently accessed by storing large amounts of data in a computer-processed form. Such as: large-bank-stored information, small-phone book.

2. Database management System (Batabase Management System,dbms): The computer system used to administer the database.

3. Relational database (relational Database,rdb): Currently the most widely used database.

4. Structured Query Language (structured Query language,sql): Designed to operate an RDB.

5.5 commonly-used relational database management systems (relational Management System,rdbs):

①oracle Database: Oracle Corporation

②sql Server: Microsoft Corporation

③DB2:IBM Corp.

④postgresql: Open Source

⑤mysql: Open Source

1-2 database structure

1. Server: A program (software) used to receive and process requests made by other programs, or a device (computer) that installs such programs.

2. Client: The program that makes the request to the server (software), or the device (computer) that installs such a program.

3. Table: A two-dimensional table that is similar to Excel, consisting of rows and columns.

4. Field: The column of the table (vertical direction).

5. Record: Row of table (horizontal direction). "Note" The relational database must read and write data in the behavior unit.

6. Cell: Row and column intersection. Note Unlike Excel, a cell can enter only one data.

1-3 SQL Overview

1.SQL statement: A statement that is composed of keywords, table names, and column names.

2.3 Types of SQL statements:

(1) DDL (data definition Language): Create, delete, or modify objects such as databases and tables in a database.

①create: Creating objects such as databases and tables

②drop: Deleting objects such as databases and tables

③alter: Modifying objects such as databases and tables

(2) DML (data manipulation Language): Querying or modifying records in a table.

①select: Querying data in a table

②insert: Inserting data into a table

③update: Modifying data in a table

④delete: Deleting data from a table

(3) DCL (Data Control Language): Verifies or cancels the execution of data changes in the database, and sets the permissions on objects in the user's operations database.

①commit: Confirming changes to data in the database

②rollback: Canceling changes to data in the database

③grant: Giving users permission to operate

④revoke: Canceling the user's operation rights

"Notes" DML is most frequently used.

3.SQL Statement Writing Specification:

① ends with a semicolon (;);

② is case insensitive and does not differentiate the case of keywords; "Note" The data inserted into the table is case sensitive, such as "Hi", "Hi", and "HI" are different.

③ This series of essays will be in the format "capitalization of keywords, the first letter of table name and column name".

④ words separated by a half-width space or line break

4. How constants are written:

String, date: enclosed in single quotation marks ('), such as ' Hello ', ' 2016-09-24 '.

Number: Write directly without adding single quotes, such as: 5.

　　

1-4 Creating a Table

1. Database creation:

-- Syntax: CREATE database < db name >

CREATE DATABASE -- Create a database named shop

　　

2. Creation of tables

 --  syntax:    CREATE table < table name;  -- Span style= "color: #008080;" > ( --  < Column name 1> < data type > < constraint ;,  --  < Column name 1> < data type > < constraint  --  ...  --  < table constraint 1>,  --  < table constraint 2>,  --  ...  -- )  

--to create a table named ShohinCREATE TABLEShohin (shohin_idCHAR(4) not NULL, Shohin_meiVARCHAR( -) not NULL, Shohin_bunruiVARCHAR( +) not NULL, Hanbai_tankaINTEGER, Shiire_tankaINTEGER, Torokubi DATE,PRIMARY KEY(shohin_id));

3. Naming rules:

① can only use half-width English letters, numbers, underscores (_) as the names of databases, tables, and columns;

The ② specification requires a name to begin with a half-width English character;

③ name cannot be duplicated.

4. Specifying the data type: declares the data type of the column, which is a constraint.

5. Data type description:

①integer: integer type, meaning cannot store decimals;

②char: The maximum length of a string, such as the parentheses in a char (10) and char (100) Table name, which can be stored by this column. It is a fixed-length string, such as CHAR (8), which is automatically saved as ' ABC ' (the trailing 5-corner space) when inserting ' abc ' in the column;

③varchar: String type, similar to CHAR, the difference is that it is "variable length string", such as VARCHAR (8) in the insertion of ' abc ', the preservation is the string ' abc ';

④date: Date type;

... ...

6. Constraints:

① non-null constraint: null is a keyword with a blank (no record) meaning, and NOT NULL represents a constraint that must be entered.

② PRIMARY KEY constraint: A primary key is a column that can determine a row of data, typically fetching a specific row of data, which is unique and does not allow duplication.

... ...

1-5 Updating and deleting tables

1. Delete a table

-- Syntax: DROP table < table name >

DROP TABLE -- to delete a table named Shohin

"Note" The deleted table cannot be recovered.

2. Update the table

(1) Adding columns

-- Syntax: ALTER table < table name > ADD < column name > < type >;

ALTER TABLE ADD VARCHAR (+--  Add a column named Shohin_mei_kana type VARCHAR (100) in Shohin

(2) Delete a column

-- Syntax: ALTER table < table name > DROP column < columns name >;

ALTER TABLE DROP COLUMN -- to delete a column named Shohin_mei_kana in the Shohin table

The "note" table cannot be recovered after the change is defined.

Note

The statements here are validated by SQL Server.

--preview version, after finishing and then publish to the homepage--

[SQL] SQL Basics Grooming (i)-Database and SQL