SQL Structured Query Language

Like the story of a storyteller in drinking, he was immersed in the story of Death, and the story was completely unrelated to him.

From beginning to finish, he is that gavel, is the folding fan, is the benches, is the storyteller's hand the teacup, is a prop, but is not that story the person.

SQL is not a database, but a familiar data manipulation language. However, there are many kinds of database operations in the software development environment, there are many different versions of the SQL language, but in order to be compatible with ANSI standards, they must be in a similar way to support some key keywords (such as SELECT, UPDATE, DELETE, INSERT, WHERE And so on).

As a junior programmer, I take it to complete the task, first of all, the most basic building database to build a table.

Under SQL Server:

New Database

Use master

if exists (SELECT * from dbo.sysdatabases where name = ' Mybill ')

drop table Mybill

Go

Create DATABASE Mybill

On

name = ' Mybill_data ',

filename = ' D:\MyData\MyBill.mdf ',

Size = 10,

MaxSize = 20,

FILEGROWTH = 4

)

Log On (

name = ' Mybill_log ',

filename = ' D:\MYDATA\MYBILL.IDF ',

Size = ten MB,

maxsize = MB,

FileGrowth = 4 MB

)

Go

New Table

Use Mybill

New Users Table

if exists (SELECT * from dbo.sysobjects WHERE name = ' Users ')

Drop Users

Go

CREATE TABLE Users (

ID int PRIMARY KEY NOT NULL,//Add PRIMARY KEY constraint to ID column while building the table

Name varchar NOT NULL unique,//Add a unique constraint to the Name column while building the table

PWD varchar NOT NULL

)

Go

New EMP Table

if (SELECT * from dbo.sysobjects WHERE name = ' EMP ')

drop table Emp

Go

CREATE TABLE EMP (

Eid int PRIMARY key NOT NULL,

Job varchar NOT NULL,

Sal money is not NULL,

u_id int foreign key references Users (ID)//Add primary FOREIGN KEY constraint while building the table. <emp foreign key points to the primary of users key>

)

Go

If the table is already built and you want to add a constraint, you can use the following statement:

To add a PRIMARY KEY constraint:

ALTER TABLE Users

ADD primary KEY (ID)

ALTER TABLE Users

ADD consrtaint pk_id primary key (ID)

Revocation <mysql>

ALTER TABLE Users

Drop PRIMARY Key

<oracle,sqlserver>

ALTER TABLE Users

Drop Consrtaint pk_id

add Unique constraint < single column;:

ALTER TABLE Users

ADD Unique (Name)

< multi-column >

ALTER TABLE Users

Add Constraint un_id Unique (id,name)

Revocation : <oracle,sqlserver,ms access>

ALTER TABLE Users

Drop Constraint un_id

<mysql>

ALTER TABLE Users

Drop INDEX un_id

To add a primary foreign KEY constraint:

ALTER TABLE EMP

ADD Constraint Fk_constraint

Foreign KEY (U_ID)

References Emp (ID)

revoke <oracle,sqlserver,ms access under >

ALTER TABLE EMP

Drop Constraint Fk_constraint

> Under <mysql

ALTER TABLE EMP

Drop FOREIGN Key Fk_constraint

To add a check constraint:

ALTER TABLE Users

Add Consrtaint ck_no Check (id>0)

Revocation: <sqlserver under Oracle MS Access >

ALTER TABLE Users

Drop Consrtaint Ck_no

> Under <mysql

ALTER TABLE Users

Drop Check Ck_no

Add default constraint <sqlserver Oracle under >

ALTER TABLE Users

ALTER COLUMN Name Set default = ' JEEP '

> Under <mysql

ALTER TABLE Users

Alter Name Set default = ' JEEP '

Revoke <sqlserver Oracle under >

ALTER TABLE Users

ALTER COLUMN Name drop default

> Under <mysql

ALTER TABLE Users

Alter Name drop default

Basic additions and deletions to check:

Increase:

INSERT into Users values (' Admin ', ' 123 ');

By deleting:

Delete from Users where id = 1;

Check:

SELECT * FROM Users

Select Name from Users < query a column of values >

Distinct keywords

SELECT DISTINCT Name Users < return unique values >

or keyword

SELECT * from Emp where job = ' Supervisor ' or SAL = 6000

and keyword

SELECT * from Emp where job = ' employee ' and sal = 5000

SELECT * from Emp where (job = ' implement ' or Sal =6000) and id = 1

ORDER BY keyword

SELECT * from EMP where ORDER by Eid

SELECT * from EMP where ORDER by Eid DESC

TOP clause

Select Top 1 * from EMP-------SQL Server

Select top percent * from Emp--sqlserver query 50% content

SELECT * from EMP where rownum <= 1---Oracle

SELECT * from EMP where limit 1--mysql

Like clause

SELECT * from Emp where Sal like ' A% '--queries all information starting with ' A ' in the job title

SELECT * from Emp where Sal like '%A '--queries all information that ends with ' A ' in the job title

SELECT * from Emp where Sal like '%a% '--Query job title contains all information of ' a '

SELECT * from EMP where Sal is not like '%a% '--query all information that does not contain ' A ' character in the job title

Wildcard characters:

SELECT * from Emp where Sal like ' _a_d_ '--the second character is a and the fourth character is D

SELECT * from Emp where Sal like ' [abc]% '--all at the beginning of any ABC

Select *from Emp where SQL like ' [! abc]% '--not all at the beginning of ABC any

In keyword:

SELECT * from EMP where Eid in (0,1)

Between keywords

SELECT * from EMP where Eid between 1 and 3--eid between 1 ~ 3

SELECT * from EMP where Eid is not between 1 and 3

As keyword

Select Eid as ' number ', Sal as ' revenue ', job as ' position ' from EMP

Select E.id,e.sal from EMP as E

Inner Join

Select E.eid,e.sal,e.job,u.name from EMP as e inner join Users as u on u.id = e.u_id

Left Join

Select E.eid,e.sal,u.name from Users as U left join EMP as E on u.id = e.u_id------will return all columns in the left table (Users) Even if there are no matching entries in the right list

Rigth Join

Select E.eid,e.sal,u.name from Users as U rigth jion emp as E on u.id = e.u_id------will return all columns in the right table (EMP) Even if there are no matching entries in the left list

Full Join

Select E.eid,e.sal,u.name from Users as U full jion Emp as E on u.id = e.u_id-----Return all regardless of whether there is a matching

Union ribs

Select name from Student

Union

Select name from Teacher

SELECT * INTO

SELECT * Into Test from EMP--backup

Select E.sal,u.id to Test1 from EMP as E full jion Users as u on e.u_id = U.id

Change:

Update Users Set Name = ' Tom ' where id = 2;

Data type:

int (lenth) integer (lenth) smallint (lenth) tinyint (lenth)

char (lenth) varchar (lenth)

Decimal (lenth,i) data (YYYY-MM-DD)

Not finished ....

SQL Structured Query Language