SQL学习之案例

一、数据库的创建，表的创建和删除，记录的增删查改

一个班级数据库classbase，需要记录班级里的学生（学生属性为：sid, sname, phone)，表应该如下：

DROP DATABASE classbase;
CREATE DATABASE classbase;        /*创建数据库*/
USE classbase;                    /*使用数据库*/
DROP TABLE IF EXISTS students;
CREATE TABLE students(            /*创建学生表格*/
    sid int,
    sname varchar(10),
    phone varchar(11)
);
INSERT INTO students(sid, sname, phone)values(11000, '小明', '13314415516'),(12000, '小红', '13317718811');
SELECT *FROM students;             /*查看表*/
SHOW COLUMNS FROM students;        /*查看列的属性信息*/
UPDATE students SET phone='12345678901' WHERE sid=11000;  /*更新*/
DELETE FROM students WHERE sid=12000;                     /*删除*/
SELECT sid, sname FROM students;                          /*只看表中的sid,sname信息*/

---

二、数据库设计，表之间的联系

题目描述：一个公司的数据库需要存储有关雇员（属性为ssn、salary、phone）；部门（属性为dno、dname、budget）；雇员的子女（属性为name age）。雇员在部门中工作；每个部门由一名雇员来负责；在给定双亲的情况下，每个孩子能由他的姓名唯一确定（假设他们的双亲只有一个是公司的职员）。一旦雇员离开公司，那么我们不再对其子女信息感兴趣。画出E/R图，并标明主键，弱实体集等信息。

---

CREATE TABLE employees(
    ssn varchar(10),
    sphone varchar(11),
    salary int,
    primary key(ssn)
);

CREATE TABLE departments(
    dno int(10),
    dname varchar(11),
    budget int,
    primary key(dno)
);

CREATE TABLE work_in(
    ssn varchar(10),
    dno int,
    primary key(ssn, dno),
    foreign key(ssn) references employees(ssn) on delete cascade,
    foreign key(dno) references departments(dno) on delete cascade
);

CREATE TABLE manage(
    ssn varchar(10),
    dno int(10),
    primary key(dno),
    foreign key(ssn) references employees(ssn) on delete cascade,
    foreign key(dno) references departments(dno) on delete cascade 
);

CREATE TABLE has(
    ssn varchar(10),
    name varchar(20),
    age int,
    primary key(ssn, name),
    foreign key(ssn) references employees(ssn) on delete cascade
);

tips:
on delete cascade on update cascade，即如果外键对应数据被删除或者更新时，将关联数据完全删除或者相应地更新。否则由于外键的约束不可以进行删除或更新相关数据。
FOREIGN KEY: 用于预防破环表之前的连接行为，同时也能防止非法数据的插入，因为它必须指向那个表中的值之一。

三、SQL约束

SQL 约束用于规定表中的数据规则，如果存在违反约束的数据行为，行为会被约束终止，即无法对数据进行操作。约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE语句）。

SQL一般有如下约束：
1) NOT NULL - 指示某列不能存储 NULL 值。
2) UNIQUE - 保证某列的每行必须有唯一的值。
3) PRIMARY KEY-NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一识，有助于更容易更快速地找到表中的一个特定的记录。
4) FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
5) CHECK - 保证列中的值符合指定的条件。
6) DEFAULT - 规定没有给列赋值时的默认值。

拿上面第一个表做例子：

/*在创建时增加约束sid为NOT NULL*/
CREATE TABLE students(
    sid int NOT NULL,
    sname varchar(10),
    phone varchar(11)
);
/*在创建之后添加约束sid为unique*/
ALTER TABLE students ADD UNIQUE (sid);

/*撤销以上的unique约束*/
ALTER TABLE students DROP INDEX sid;

/*添加primary key约束sid*/
ALTER TABLE students ADD PRIMARY KEY (sid);

/*删除主键约束*/
ALTER TABLE students DROP PARIMARY KEY;

/*约束年龄>=7岁*/
ALTER TABLE students ADD CHECK (age >= 7);

/*约束电话号码默认为'11111000'*/
ALTER TABLE students ALTER phone SET DEFAULT '11111000';

SHOW COLUMNS FROM students;