PRIMARY KEY constraint unique non-null check default etc use @
CREATE TABLE EMP (
EmpID int Auto_increment PRIMARY key NOT NULL,--auto_increment: autogrow, PRIMARY key PRIMARY KEY constraint, NOT NULL non-null.
EmpName varchar (+) NOT NULL,
Emno char (4) unique,--unique.
Enmgender int default 0 comment ' 0 male: 1 female ',--defaults, the default is male.
Enmpbirthday date,
Check (Enmpbirthday<now ()),--check checks to determine birthdays less than today
enmpage int Check (18>=enmpage and enmage<=60)
);
INSERT into EMP (empid,empname) VALUES (1, ' Xiaohong ');--add content, add columns and values!
Select*from EMP; --Replication is displayed.
Eg2:create Table EMP2 (
--EmployeeID int, DeptID int, primary KEY (Employeeid,deptid),
--EmployeeName varchar () NOT NULL,
--detname varchar () NOT NULL,
--Gender char (2) default 0 comment ' 0 male: 1 female ',
--Birthday Date Check (Birthday<now ()),
--Graduatedate Datetime Check (Graduatedate<now ()),
--Idcard char (+) is not null unique,
--degree varchar (12),
--Garauateschool varchar () NOT NULL,
--Specialty varchar () NOT NULL,
--Telephone varchar (+) NOT NULL,
--Salary Smallint,
--allowance Decimal (18.2),
--Skill varchar (2000)
-- );
--Select*from EMP2;
I. Guarantee the complete line of data!
1. Entity integrity (implemented by setting a PRIMARY KEY constraint unique constraint or identity column property)
2. Domain Integrity (check constraint defaults by foreign KEY constraints define non-null constraints)
3. Referential integrity
4. User-defined Integrity
Two. Create a non-null constraint.
CREATE TABLE Boxs (name varchar () NOT NULL
phone varchar (+) not null);
Non-null constraint:
insert INTO table name (column name) values (added value),
INSERT into Boxs (name,id) valuse (' Liuxi ', ' 123 ');
Three. Set the primary key.
PRIMARY KEY constraint: Primary key
1.create table Boxs (name varchar) primary key);
2.create table Boxs (name varchar, student varchar), primary key (name, student));
3.alter table Boxs Add primary key (Name,student);
Four. Set the default value.
drop table box;
Create Table box ()
Name varchar (+) NOT NULL,
Phone varchar (default ') ' Please tell phone number ' not null ',
;
Select*from box;
Defaults: Default
Eg:gender char (2) default 0 comment ' 0 male: 1 female ', (default = male)
Five. Set the CHECK constraint.
Check
Age int check (age between),
Six. Auto-grow.
Auto_increment
to be used on the primary key: Create TABLE box (ID int auto_increment primary key)
Seven. Foreign key foreign key
-Drop table emp;
--Create TABLE EMP (name varchar) primary key,gender enum (' Male ', ' female '), Birthday date check (Birthday<now ()));
--INSERT INTO EMP values (' abc ', ' m ');
--select * from EMP;
--delete from EMP;
--drop table Stu;
CREATE TABLE stu (ID int primary key,name varchar (50)); --Xiaoliu Zhang Guan
--constraint-foreign key constraint
CREATE TABLE Exam (ID int primary key,project varchar (), stuid int);--Xiaohong
--the value that the foreign key points to must be unique
--INSERT into Stu values (2, ' Xiaozhang ')
INSERT INTO exam values (1, ' C ', 2);
--delete from exam;
--ALTER TABLE exam
--Add foreign key (Stuid) references Stu (ID);
Select *from Stu;
SELECT * from exam;
Primary key in SQL. Use of foreign keys, etc.: