数据库学习代码

SELECT NOW();

--四大完整性

--域完整性、实体完整性、引用完整性、自定义完整性

--五大约束 CONSTRAINT

--主键约束 PRIMARY KEY PK_

--外键约束 FOREIGN KEY FK_

--默认约束 DEFAULT KEY DF_

--唯一约束 UNIQUE KEY UQ_

--检查约束 CHECK KEY CK_

--参考:REFERENCES 

--新建数据库

CREATE DATABASE newdream_T80;

--删除数据库

DROP DATABASE newdream_T80;

--使用数据库

USE newdream_T80;

--新建表1：学生信息表

--方式1：在字段后面加约束

CREATE TABLE students
(
SCode INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
SName CHAR(10) NOT NULL,
SAddress VARCHAR(50) DEFAULT '湖南长沙',
SGrade FLOAT,
SEmail VARCHAR(50)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--方式2：在表后面加约束

CREATE TABLE students1
(
SCode INT NOT NULL AUTO_INCREMENT ,
SName CHAR(10) NOT NULL,
SAddress VARCHAR(50) DEFAULT '湖南长沙',
SGrade FLOAT,
SEmail VARCHAR(50),
PRIMARY KEY(SCode)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--在表外加约束

CREATE TABLE students2
(
SCode INT,
SName CHAR(10) NOT NULL,
SAddress VARCHAR(50) DEFAULT '湖南长沙',
SGrade FLOAT,
SEmail VARCHAR(50)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--表外加约束 CONSTRAINT 约束

--修改表：加约束

ALTER TABLE students2
ADD CONSTRAINT pk_scode_2 PRIMARY KEY(SCode);

--修改地址的默认值

ALTER TABLE students2
ALTER COLUMN SAddress SET DEFAULT 'changsha';

--显示表结构

DESC students

--建表信息

SHOW CREATE TABLE students;

--删除表

dorp TABLE students2;

--alter 为了需求的变更，要写升级的SQL脚本

--加一个性别字段 新需求

ALTER TABLE students
ADD ssex INT CHECK(ssex=1 OR ssex=0);

--新增一个时间字段，并取当前时间为默认值

ALTER TABLE students ADD CreateTime DATETIME DEFAULT NOW();
ALTER TABLE students ADD CreateTime DATETIME DEFAULT '2017-01-01';

--删除字段

ALTER TABLE students
DROP ssex;

--改字段名字

ALTER TABLE students
CHANGE ssex sex INT;

ALTER TABLE students
CHANGE sex ssex INT;

--改字段类型

ALTER TABLE students
MODIFY sname CHAR(20);

--改回

ALTER TABLE students
MODIFY sname CHAR(10);

------------改表名-----------------

RENAME TABLE `students2` TO student3;

-------------------------------------------------------

------ 约束------

--邮箱加check约束 %表示任意字符

ALTER TABLE students
ADD CONSTRAINT ck_semail
CHECK(semail LIKE '%@%');

--添加性别约束

ALTER TABLE students
ADD CONSTRAINT CK_ssex CHECK( ssex BETWEEN 0 AND 1);

--删除表：

DROP TABLE students1;
DROP TABLE students2;

---------------------------------------

--创建表2：course

CREATE TABLE course
(
Courseid INT PRIMARY KEY,
CourseName NVARCHAR(50)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--创建表3：

CREATE TABLE score (
ScodeID INT(11) NOT NULL AUTO_INCREMENT,
Studentid INT(11) NOT NULL,
Courseid INT(11) NOT NULL,
score SMALLINT(6) DEFAULT NULL,
PRIMARY KEY (`ScodeID`),
CONSTRAINT `score_ibfk_1` FOREIGN KEY (`Studentid`) REFERENCES `students` (`SCode`),
CONSTRAINT `score_ibfk_2` FOREIGN KEY (`Courseid`) REFERENCES `course` (`CourseId`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

--REFERENCES 参考

注意：1. 在从表上建外键 去关联主表

2. 从表的数据必须来源于主表

--表一插入数据:学习信息表

INSERT INTO students(sname,saddress) VALUES('张三','上海');
INSERT INTO students(SName,SAddress,SGrade,SEmail,ssex)
VALUES('李四','上海',4,'ls@dream.com',0);
INSERT INTO students(SName,SAddress,SGrade,SEmail,ssex)
VALUES('王五','北京',4,'ww@dream.com',1);
INSERT INTO students(SName,SAddress,SGrade,SEmail,ssex)
VALUES('赵六','深圳',4,'zl@dream.com',0);
INSERT INTO students(SName,SAddress,SGrade,SEmail,ssex)
VALUES('田七','广州',4,'tq@dream.com',1);

--表2：课程表插入数据

INSERT INTO course(Courseid,CourseName) VALUES(1,'语文');
INSERT INTO course(Courseid,CourseName) VALUES(2,'数学');
INSERT INTO course(Courseid,CourseName) VALUES(3,'测试');
INSERT INTO course(Courseid,CourseName) VALUES(4,'英语');

--表三输入数据

--1. 科目1成绩 要求有人不及格

INSERT INTO score(Studentid,CourseID,score)VALUES(1,1,98);
INSERT INTO score(Studentid,CourseID,score)VALUES(2,1,43);
INSERT INTO score(Studentid,CourseID,score)VALUES(3,1,23);
INSERT INTO score(Studentid,CourseID,score)VALUES(4,1,86);
INSERT INTO score(Studentid,CourseID,score)VALUES(5,1,67);

--2. 科目2成绩 要求有人缺考

INSERT INTO score(Studentid,CourseID,score)VALUES(1,2,65);
INSERT INTO score(Studentid,CourseID,score)VALUES(2,2,77);
INSERT INTO score(Studentid,CourseID,score)VALUES(3,2,49);

--3. 科目1补考成绩

INSERT INTO score(Studentid,CourseID,score)VALUES(2,1,88);
INSERT INTO score(Studentid,CourseID,score)VALUES(3,1,75);

--------------------------------------------------

-------数据库操作DML--------------

--1. INSERT

--插入数据

INSERT INTO Students (SName,SAddress,SGrade,SEmail,SSEX)
VALUES ('