SQL网上一些浏览（3）

SQL CHECK Constraint on CREATE TABLE
The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0.
MySQL:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

ALTER TABLE Persons
ADD CHECK (P_Id>0)

ALTER TABLE Persons
DROP CHECK chk_Person

SQL DEFAULT Constraint
The DEFAULT constraint is used to insert a default value into a column.
The default value will be added to all new records, if no other value is specified.

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

ALTER TABLE Persons
ALTER City DROP DEFAULT

SQL CREATE INDEX Syntax 

建立索引，为表table_name的column_name字段建立索引
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column_name)

DROP INDEX Syntax for MySQL:
ALTER TABLE table_name DROP INDEX index_name

删除索引

DROP DATABASE database_name

DROP TABLE table_name

TRUNCATE TABLE table_name //删除表里的内容，而不删除表

删除一行记录： delete from mytable(表明) where name(字段)=‘’；

UPDATE tbl_name SET datemeet='2010-12-19' WHERE ID = 333;

更改某一个字段的值

AUTO INCREMENT a Field
Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.

Syntax for MySQL
The following SQL statement defines the "P_Id" column to be an auto-increment primary key field in the "Persons" table:
CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:
ALTER TABLE Persons AUTO_INCREMENT=100

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')   //在某个字段插入

分组：

SELECT DEPT, MAX(SALARY) AS MAXIMUM
FROM STAFF
GROUP BY DEPT

创建视图（虚表）

mysql> CREATE OR REPLACE VIEW  t_view AS SELECT NAME FROM user LIMIT 1,3;  

mysql> SELECT *FROM t_view; 

DROP VIEW view_name；