学习网址链接:https://www.bilibili.com/video/av39807944/?p=1
show databases; -- 查看数据库
use linyueyue; -- 使用linyueyue数据库
show tables; -- 查看数据库中的表
describe sc; -- 查看创建好的数据表结构
select * from student; -- 查看学生表数据内容
insert into student values (12,'王晓飞',23,'男'); -- 添加学生信息
delete from student where Sid = 12; -- 删除Sid为12的学生信息
update student set Sname = "沉浸" where Sname = "王晓飞"; -- 修改学生名字为王晓飞的信息
test101104-手动09
create table userdemo(
Sid int primary key,
Sname varchar(20));
– 联合主键约束
create table userdemo2(
Sid int,
Sname varchar(20),
PASSWORD varchar(20),
PRIMARY key (Sid,Sname)
);
– 自增约束
create table userdemo3(
id int PRIMARY key auto_increment,
Sname varchar(20));
insert into userdemo3(Sname) value("zhangsan");
SELECT * from userdemo3;
– 主键约束的的增加和删除
create table userdemo4(
Sid int,
Sname varchar(20));
desc userdemo4;
alter table userdemo4 add primary key (Sid);
alter table userdemo4 modify Sid int primary key;
alter table userdemo4 drop primary key;
– 唯一约束
– 约束修饰的字段的值不可重复
create table userdemo5(
Sid int,
Sname varchar(20));
desc userdemo5;
alter table userdemo5 add unique(Sname);
create table userdemo6(
Sid int,
Sname varchar(20),
unique(Sname));
desc userdemo6;
-- 删除唯一约束
alter table userdemo6 drop index Sname;
-- 通过modify添加位于约束
alter table userdemo6 modify Sname varchar(20) unique;
-- 非空约束
create table userdemo7(
Sid int,
Sname varchar(20) not null);
desc userdemo7;
– 默认值
create table userdemo8(
Sid int,
Sanme varchar(20),
Sage int default 10);
desc userdemo8;
insert into userdemo8(Sid,Sanme) values(3,"张晓");
select * from userdemo8;
– 外键约束
– 涉及到两个表:父表,子表
create table classes(
id int primary key,
Sname varchar(20));
create table student(
id int primary key,
Cname varchar(20),
class_id int,
foreign key(class_id) references classes(id));
-- 创建学生表
create table student1(
sno varchar(20) primary key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday datetime,
class varchar(20));
-- 创建课程表
create table course1(
cno varchar(20) primary key,
cname varchar(20) not null,
tno varchar(20) not null,
foreign key(tno) references teacher1(tno));
-- 创建教师表
create table teacher1(
tno varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbirtday datetime,
prof varchar(20) not null,
depart varchar(20) not null);
-- 创建成绩表
create table score1(
sno varchar(20),
cno varchar(20) not null,
degree decimal,
foreign key(sno) references student1(sno),
foreign key(cno) references course1(cno),
primary key(sno,cno));
-- 添加学生表信息
insert into student1 values('101','曾华','男','1992-09-01','95033');
insert into student1 values('102',