数据库大讲解第五讲

**

数据库大讲解第五讲

**

第一节数据库表创建 作者：董哥

预先创建好需要创建的数据库，有databases:employe,t1,test1 ,test等等

创建数据库，及各数据库表

基本命令联系，命令由浅入深，建议跟着文档自己敲一遍
CREATE DATABASE employe /*!40100 DEFAULT CHARACTER SET utf8 */;

Host: 28.5.8.60 (Version 5.7.17)

Date: 2019-08-30

Writer: 煮不烂的鸡蛋 (Build 1.26)

注意：建表数据后期因为讲解习题插入的数值改动较大，数值只供参考

alter table student add Snam varchar(10) null;
alter table student drop Snam ;
create database Test1;//创建数据库
use test1;//使用数据库
create table Student(//创建表
Student_id char(9) not null,
Student_sname varchar(10) not null,
Student_city varchar(10) not null,
Student_phone integer(10) null);
alter table student add Student_st varchar(10) null;
alter table student add Student_age datetime null;

insert into student values(‘100’,‘赵雷’,‘1997-01-09’,‘北京’,‘1234’,‘m’);
insert into student values(‘101’,‘王楠’,‘1997-01-09’,‘北京’,’’,’’);
insert into student(Student_sname) values(‘gdsa’),(‘s’),(‘sdh’);
select *from student;
create table employee_tbl(
emp_id char(9) not null primary key,
emp_name varchar(20) not null,
emp_page integer(10) null);
create table employee_tbl(
emp_id char(9) not null ,
emp_name varchar(20) not null,
emp_page integer(10) null,
primary key(emp_id));
alter table student add constraint student_st primary key (student_id);
create table employee_tb2(
emp_id char(9) not null ,
emp_name varchar(20) not null,
emp_page integer(10) null,
emp_phone integer(13) null unique,//该值只能唯一
primary key(emp_id));
create table employee(
emp_id char(9) not null ,
position varchar(15) not null,
emp_name varchar(20) not null,
emp_page integer(10) null,
emp_phone integer(13) null unique,
pay_rate number(4,2）not null,
constraint foreign key (emp_id)references Student(Student_id));
alter table employee_tb2
add constraint foreign key (emp_id)
references employee_tb1(emp_id);
create table employee1(
emp_id char(9) not null ,
position varchar(15) not null,
emp_name varchar(20) not null,
emp_page integer(10) null,
emp_phone integer(13) null unique,
primary key(emp_id),
constraint chk_emp_page check(emp_page=‘4662’));
constraint chk_emp_page check(emp_page in(‘4623’,‘4627’,‘4625’));
alter table employee1 drop constraint emp_page;//去除约束
alter table employee1 drop primary key;//去除主键约束

select Student_id from student;
select distinct student_city from student;//除去重复记录