MySQL基础练习一

学习网址链接: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',