[大数据]MYSQL基础笔记记录 一对一 一对多 多对多

安装MySQL
https://blog.csdn.net/qq_37350706/article/details/81707862?
管理员运行cmd
启动服务
net start mysql
关闭服务
net stop mysql

数据库创建、删除
表的创建、删除

登录
mysql -uroot -p密码

查看文件
use mysql

创建数据库  可以写中文
create database yunaodb charset utf8;
显示创建数据库
show create database yunaodb;

显示当前数据库
select database();

删除数据库
drop database yunaodb;

使用数据库
use db_name;

创建数据表
create table student(
  id int primary key auto_increment,
  name char(32) not null,
  age int not null);

查看表结构
show tables;

查看数据表结构
desc student;

删除表
drop table a;

表查看删除

添加内容
insert into student (name,age,register_date) values("LiSuWei",20,"2021-03-09");

查询数据库表
select * from student;
查询 id               限制两个  从第二个开始
select * from student limit 2 offset 1;

条件查询
select * from student where id > 3;
select * from student where id > 3 and age >20;
模糊查询
select * from student where register_date like "2000-03%";

改
update student set name="YunAo",age=21 where id=4;
update student set name="YunAo",age=21,register_date="2000-07-03" where id>4;
update student set register_date="2000-07-03" where id=2;


删
dalete from student where name="YunAo";

排序
升序
select * from student order by id;
降序
select * from student order by id desc;
时间排序
select * from student order by register_date;

分组统计
select name,count(*) from student group by name;
select register_date,count(*) from student group by register_date;
select register_date,count(*) as stu_num from student group by register_date;

求和
select name,sum(age) from student group by name;
select name,sum(age) from student group by name with rollup;
select name,sum(age) as singin_count from student group by name with rollup;

删除字段
#从student表删除register_date
alter table student drop register_date;
＃添加phone字段
alter table student add phone int(11) not null;

更改字段数据类型
alter table student modify sex enum("F","M") not null;

更改字段名和类型
alter table student change sex gender char(32) not null default "X";

表关联

创建关联表
一对一
create table student(
    id int auto_increment not null,
    name char(64) not null,
    class_id int(11) not null,
    primary key (id),
    key fk_class_key (class_id),
    constraint fk_class_key foreign key (class_id) references class (id));

-- 多对多
--   多对多关系   的外键约束 需要创建中间表 来约束2张表的关系
create table coder(
id int primary key auto_increment,
name varchar(20),
);
create table project(
id int primary key auto_increment,
name varchar(10)
);

create table coder_project(
coder_id int ,
project_id int ,
-- 添加外键
foreign key (coder_id) references coder(id),
foreign key (project_id )  references project(id )
);

create table teacher2class(
    id int not null primary key auto_increment,
    teacher_id int,
    class_id int,
    foreign key (teacher_id) references teacher(id),
    foreign key (class_id) references class(id));

查看表创建的结构
show create table study_record;
更改id 自增
alter table study_record modify id int not null auto_increment ;
查看有多少表
show tables;