MySQL

MySQL 的使用

安装

启动MySQL

systemctl start mariadb

• 查看MySQL运行状态

ps -ef | grep mysql

• 进入MySQL

mysql -u root -p

在Navicat下的使用准备

创建用户并给权限：

create user 'root'@'%' identified by '123456';
flush privileges;
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

实例

-- 如果存在名为school的数据库就删除它

drop database if exists school;

-- 创建名为school的数据库并指定默认的字符集为utf-8

create database school default charset utf8;

-- 切换到school数据库上下文环境

use school;

-- 创建学生表

create table tb_student
(
stuid int not null,
stuname varchar(20) not null,
stusex bit default 1,
stubirth date,
primary key (stuid)
);

-- 修改学生表

alter table tb_student add column stuaddr varchar(255);
alter table tb_student change column stuaddr stuaddr varchar(511);
alter table tb_student drop column stuaddr;

-- 修改学生表添加学院编号(colid)列

alter table tb_student add column colid int;

-- 修改学生表添加外键约束(参照完整性)

alter table tb_student add constraint fk_student_colid foreign key (colid) references tb_college (colid);

-- 更新学生表为学生指定所属学院

update tb_student set colid=1 where stuid between 1001 and 1006;
update tb_student set colid=2 where stuid in (1007, 1008);
update tb_student set colid=3 where stuid=1009;

-- 创建老师表

create table tb_teacher
(
teaid int not null comment '工号',
teaname varchar(20) not null comment '姓名',
teasex bit default 1 comment '性别',
teabirth date comment '生日',
teatitle varchar(10) default '助教' comment '职称',
colid int not null comment '所在学院'
-- primary key (teaid),
-- foreign key (colid) references tb_college (colid)
);

alter table tb_teacher add constraint pk_teacher_teaid primary key (teaid);

alter table tb_teacher add constraint fk_teacher_colid foreign key (colid) references tb_college (colid);