mysql 约束

/*默认值约束*/在见表的时候添加
CREATE TABLE student (
        sid INT,
        sname VARCHAR(10),
        addrss VARCHAR(10) DEFAULT '淄博'
)

INSERT INTO student VALUES(1,'袁一鸣','北京');
SELECT * FROM student;
INSERT INTO student(sid,sname) VALUES(2,'李瑞');
INSERT INTO student VALUES (3,'蜘蛛侠',NULL);
DROP TABLE student;
/*非空约束*/
CREATE TABLE student (
        sid INT,
        idcard VARCHAR(20) NOT NULL,
        sname VARCHAR(10) DEFAULT '佚名'
)
INSERT INTO student VALUES (1,370303,'钢铁侠');
INSERT INTO student(sid) VALUES(2);
INSERT INTO student(sid,idcard) VALUES(2,NULL);
SELECT * FROM student;
DROP TABLE student;
/*唯一约束*/
CREATE TABLE student(
        sid INT,
        sname VARCHAR(10) UNIQUE/*唯一性*/
)
INSERT INTO student VALUES (1,'黑寡妇');
INSERT INTO student VALUES (2,NULL);
SELECT * FROM student;
DROP TABLE student;
/*主键约束*/
CREATE TABLE student(
        sid INT PRIMARY KEY/*主键*/,
        sanme VARCHAR(10)
)
INSERT INTO student VALUE (1,'雷神');
INSERT INTO student VALUE (2,'洛基');
SELECT * FROM student;
DROP TABLE student;
/*自增长约束*/
CREATE TABLE student(
        sid INT PRIMARY KEY AUTO_INCREMENT,
        sanme VARCHAR(10)
)
INSERT INTO student(sanme) VALUES('蚂蚁');
INSERT INTO student(sanme) VALUES('蚂蚁');
SELECT * FROM student;
/*清空表2种*/
DELETE FROM dept WHERE did=6;
TRUNCATE student;
/*外键约束*/
DROP TABLE dept;
CREATE TABLE dept(
        did INT PRIMARY KEY AUTO_INCREMENT,
        dname VARCHAR(10)
);
CREATE TABLE emp(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(10),
    did INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY(did)
                REFERENCES dept(did)
)
INSERT INTO dept(dname) VALUES ('产品部');
INSERT INTO dept(dname) VALUES ('开发部');
INSERT INTO dept(dname) VALUES ('测试部');
INSERT INTO emp (ename,did) VALUES ('贝蒂',1);
INSERT INTO emp (ename,did) VALUES ('洛基',1);
INSERT INTO emp (ename,did) VALUES ('托尔',2);
INSERT INTO emp (ename,did) VALUES ('灭霸',3);
/*修改时：先修改副表，再修改主表*/
UPDATE emp SET did=1 WHERE eid=3;
UPDATE dept SET did=4 WHERE did=2;
/*删除时：先删除副表，再删除主表*/
DELETE FROM emp WHERE eid=4;
DELETE FROM dept WHERE did=3;

SELECT * FROM dept;
SELECT * FROM emp;

连接 
/*交叉连接*/
SELECT * FROM emp JOIN dept ;
/*内连接*/
SELECT * FROM emp JOIN dept ON emp.did=dept.did;
/*查询贝蒂的部门名称*/
SELECT dname,ename FROM emp JOIN dept ON emp.did=dept.did WHERE ename='贝蒂';
/*左外外连接=内链接+左表中不满足条件的数据*/
SELECT * FROM dept LEFT JOIN emp ON emp.did=dept.did;
/*右外外连接=内链接+右表中不满足条件的数据*/
SELECT * FROM emp RIGHT JOIN dept ON emp.did=dept.did;
/*子查询：把一个查询的结果当作另一个查询的条件*/
/*查询贝蒂的部门名称*/
/*先查询贝蒂的部门名称，在查询部门表中did为1的部门名称*/
SELECT did FROM emp WHERE ename='贝蒂'
SELECT dname FROM dept WHERE did IN (SELECT did FROM emp WHERE ename='贝蒂');
/*联合查询*/
CREATE TABLE classes(
        cid INT,
        cname VARCHAR(10)
)
SELECT * FROM classes;
INSERT INTO classes VALUES (1,'html');
INSERT INTO classes VALUES (2,'js');
SELECT * FROM dept UNION SELECT * FROM classes