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