MySQL第二天笔记+作业

笔记1，

1. 创建库 create database
2. 使用库 use database
3. 查看建库的语句 show create database
4. 查看所有库 show databases
5. 查看当前用户 select user();
6. 查看当前数据库的版本 select version();
7. 建表create table database(
   字段名 字段类型 约束，
   …
   字段名 字段类型 约束
   )
8. 字段类型
   int
   char 固定长度 10 2个字节 10
   varchar 可变长度 10 2个字节 2
   date time datetime
9. 约束
   主键 PK
   唯一 UK
   非空 not null
   默认值 default
   检查约束 CK
   外键 FK

 create  table   employee(
     empid int primary key auto_increment,
     empname varchar(50) not null,
     empsex char(2) default '男',
     empphone varchar(50) unique not null,
     empaddress varchar(80)
 );

10. 主键：功能相当于 唯一且非空
    选取主键的原则是少更改原则，最少列原则
    一个表只能有一个主键，这个主键不一定只有一列
    由多列组合而成的主键叫做复合主键
11. 重命名表名字 alter table tabname rename to newname
12. 添加一列 alter table tabname add colname coltype first|after colname;
13. 修改一列 alter table tabname change cololdname colnewname coltype;
    alter table tabname modify colname coltype;
14. 删除一列 alter table tabname drop colname

笔记2，

1. 数据表关系

部门[父表|主表]	员工[子表|从表]
一个部门下有多个员工	一个员工从属一个部门
一对多	一对多
部门	员工
1	N

2. 添加外键的语法

alter table tabname add constraint fk_tab1_tab2 foreign key(外键) references 主表(主键);

3. 第一题

班级表 Grade
班级编号 pk int 自增
班级名字
学生表 Stdent
学号 varchar(50) pk
密码
姓名
gradeid int FK

CREATE TABLE Grade(
	GradeId INT PRIMARY KEY AUTO_INCREMENT,
	GradeName VARCHAR(50) NOT NULL
);

CREATE TABLE Student(
	StudentNo VARCHAR(50) PRIMARY KEY,
	LogimPwd VARCHAR(50),
	StudentName VARCHAR(50),
	Sex CHAR(2),
	GradeId INT ,
	Phone VARCHAR(255),
	Address VARCHAR(255),
	BornDate DATETIME,
	Email VARCHAR(50)
);

ALTER TABLE Student ADD CONSTRAINT fk_stu_grd FOREIGN KEY(GradeId) REFERENCES Grade(GradeId);

SHOW CREATE TABLE Student

4. 第二题

部门信息
部门编号
部门名字
部门描述信息

员工信息
员工编号
员工姓名
员工性别
员工地址
电话
部门编号[FK]

员工登录信息
员工编号[PK FK]
登录名
登录密码
昵称
最后登录时间

CREATE TABLE department(
	departId INT PRIMARY KEY AUTO_INCREMENT,
	departName VARCHAR(50),
	departContent VARCHAR(50)
);
CREATE TABLE Person(
	Person_id INT PRIMARY KEY AUTO_INCREMENT,
	Person_name VARCHAR(20),
	Person_sex CHAR(2) DEFAULT '男',
	Person_address VARCHAR(50),
	Person_phone VARCHAR(50),
	Person_departId INT
);
CREATE TABLE login(
	login_personId INT PRIMARY KEY,
	login_admin VARCHAR(50),
	login_password VARCHAR(50),
	login_name VARCHAR(50),
	login_lasttime DATETIME
);
ALTER TABLE Person ADD CONSTRAINT fk_dep_per FOREIGN KEY(Person_departId) REFERENCES department(departId);

ALTER TABLE login ADD CONSTRAINT fl_log_per FOREIGN KEY(login_personId) REFERENCES Person(Person_id);
SHOW CREATE TABLE Person

5. 外键

1. 外键引用的是另一个表的主键
2. 外键列的数据类型必须和主表的主键的数据类型保持一致
3. 外键建立在子表上的

笔记3，

1. 一对多

多的为子健 alter table tabname add constratin 约束名 foreign key(字段) references 主表(主键);

2. 一对一

ALTER TABLE idcard ADD CONSTRAINT fk_card_person FOREIGN KEY(cid) REFERENCES person(pid);

3. 多对多

ALTER TABLE userroles ADD CONSTRAINT fk_ur_user FOREIGN KEY(uid) REFERENCES users(uid);
ALTER TABLE userroles ADD CONSTRAINT fk_ur_role FOREIGN KEY(rid) REFERENCES roles(rid);

 方法一：

CREATE TABLE userroles(
uid INT ,
rid INT,
PRIMARY KEY(uid,rid)
);
方法二：
ALTER TABLE userroles2 ADD CONSTRAINT pk_ur2 PRIMARY KEY(uid,rid);

笔记4

1.数据添加

insert into 表名 values(值列表)
insert into 表名(字段列表) values(值列表); 值列表必须和列举出的字段对应，个数类型对应
给表中所有的列添加数据
插入多条记录
insert into 表名 values(值列表1),(值列表2),(值列表3);
create table 表名 as select… 数据表不存在，语句执行后会创建
insert into 表名 select …:插入表中字段必须和查询的字段保持一致

2.修改

update 表名 set 列=值,列=值 … where 条件

3.删除

delete from 表名 where 条件
如果删除父表中的记录，必须得看子表中是否有记录引用，没有引用的数据才可以删除
如果delete 后面没有条件，就会删除表中的所有的记录

delete from 表名 属于 dml语句
truncate table 表名 属于 ddl 功能相当于 delete from 表名 会删除表中的所有的记录
delete 删除的数据可以回复，不会清除主键的自增量
truncate 删除的数据不能恢复,主键自增从1开始，不能用在有主外键约束的关系的表中

truncate table 与 delete from table的区别:

作业

1、创建数据库MySchool
2、创建年级表Grade和科目表Subject，具体结构如下：

Grade表

年级名必须唯一性
3、创建学生信息表Student，并完善表结构，具体要求如下：

密码默认 000000
Sex 默认 男 只能写 男或女
Address 默认 陕西西安锦业路
4、创建成绩表Result

5、建立以上四张表之间的引用关系，具体如下：

数据修改与删除
1.将地址是西安市的更新为 西安科技二路
2.将S1001的email修改为空的字符串
3.将第二门课的名字更新为 java基础，课时为60 ，班级是二年级
4.将S1001 ，课程编号为 2 的成绩 提高 5分
5.将 S1004 ,课程编号为3 的成绩更新为60，考试时间更新为 2015-10-10
6.经核实 S1004 课程编号为2 的 缺考
7.将计算机网络课程删除掉

CREATE DATABASE MySchool

CREATE TABLE Grade(
	GradeId INT PRIMARY KEY,
	GradeName VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE Subjects(
	SubjectId INT PRIMARY KEY,
	SubjectName VARCHAR(20) NOT NULL,
	ClassHour INT NOT NULL,
	GradeId INT NOT NULL
);
ALTER TABLE subjects ADD CONSTRAINT fk_sub_gra FOREIGN KEY(GradeId) REFERENCES Grade(GradeId)
CREATE TABLE Student(
	StudentNo VARCHAR(50) PRIMARY KEY,
	LoginPwd VARCHAR(50) DEFAULT '000000',
	StudentName VARCHAR(50),
	Sex CHAR(2) DEFAULT  '男',
	
	GradeId INT ,
	
	Phone VARCHAR(255),
	Address VARCHAR(255) DEFAULT  '陕西西安锦业路',
	BornDate DATETIME NOT NULL,
	Email VARCHAR(50) 
);
ALTER TABLE Student ADD CONSTRAINT fk_stu_gra FOREIGN KEY(GradeId) REFERENCES Grade(GradeId)
 
CREATE TABLE Result(
	Id INT PRIMARY KEY AUTO_INCREMENT,
	StudentNo VARCHAR(50) ,
	SubjectId INT ,	
	StudentResult INT ,
	ExamDate DATETIME NOT NULL
);
ALTER TABLE Result ADD CONSTRAINT fk_res_stu FOREIGN KEY(StudentNo) REFERENCES Student(StudentNo)
ALTER TABLE Result ADD CONSISTENT fk_res_sub FOREIGN KEY(SubjectId) REFERENCES Subjects(SubjectId)


INSERT INTO Grade VALUES('1','一')
INSERT INTO Grade VALUES('2','二')

UPDATE Student SET Address ='西安科技二路 ' WHERE Address='西安';
UPDATE Student SET email =' ' WHERE StudentNo = 's1001';
UPDATE subjects SET SubjectName = 'java基础' ,ClassHour = 60,GradeId = 2 WHERE SubjectId =2;
UPDATE Result SET studentScore = studentScore +5 WHERE studentNo = 's1001' AND SubjectId = 2 ;
UPDATE Result SET ExamDate = '2015-10-10' AND StudentResult = 60 WHERE StudentNo = 'S1004'AND SubjectId = 3;
UPDATE StudentResult SET NULL WHERE StudentNo = 's1004' adn SubjectId = 2;
DELETE FROM subjects WHERE SubjectName='计算机网络'