mysql数据库CRUD

发表于

mysql数据库CRUD

本文从基础开始介绍mysql数据库的增删改查等基础知识

下载安装mysql数据库

启动服务

  1. services.msg

  2. 管理——>服务——>启动mysql

  3. cmd——>net start mysql

  4. 偏好设置——>run（Mac）

####停止服务

  1. net stop mysql
  2. net restart mysql(重启)
    注：以管理员身份运行
    如果要卸载，需要用installer卸载，否则将会有残余以后无法再次下载使用mysql

启动客户端工具：
在安装社区版mysql后会自动帮我们安装一个mysql的客户端，进入的方式有两种：
mysql -u root -p 进入本机数据库（注意配置路径）
或者从开始菜单进入
showdatabases; 查看所有数据库（自带4个数据库，mysql程序相关的元数据信息，mysql自己要用的）
select version();查看目前使用的版本
? 获取帮助
quit/exit 退出

####SQL(Structured Query Language)
我们这里使用navicat for mysql可视化工具学习sql,sql语言是不区分大小写的

  • DDL(数据定义语言): create/ drop/ alter
  • DML(数据操纵语言): insert/ delete/ update
  • DQL(数据查询语言): select
  • DCL(数据控制语言): grant/ revoke/ begin/ commit/ rollback

####数据定义语言

#####如果指定的数据库存在则删除
drop database if exists XXX;

#####创建数据库并指定默认字符集
create database XXX default charset utf8;

#####切换到XXX数据库
use XXX；

#####关系型数据库通过二维表来创建

######创建学生表,定义字段

######主键（primary key）-能够标识唯一一条记录的列

1
2
3
4
5
6
7
8
9
create table tb_student
(
stuid int not null comment '学号',
sname varchar(10) not null comment '姓名',
ssex bit default 1,
stel char(11),
sbirth date,
primary key(stuid)
);

键的类型：int, varchar, bit……
非空约束：not null
默认值约束：default
备注：comment
指定字符集：charset utf8
指定排序规则：collation utf8_bin (如果没有指定会默认)
自增长的字段：int … auto_increment

字段的三大类数据类型：
数值型（包括整数小数）
字符串
时间日期型

为表指定存储引擎，指定字符集：
看到表的结构：desc tb_student

#####修改表
插入新的字段
alter table tb_student add columu asddr varchar(100);
删除字段
alter table tb_student drop columu stel;

#####插入记录
一次插入一条学生纪录
insert into tb_student values(1001, ‘小白’, 1, ‘1999-3-08’, ‘北京’);
选择性插入学生纪录，指定顺序
insert into tb_student (sname, stuid) values (‘小红’, 1002)
批量插入学生纪录
insert into tb_student values
(1001, ‘小童’, 0, ‘1995-7-02’, ‘江苏南京’),
(1002, ‘小灰灰’, 1, ‘1989-3-23’, ‘上海’),
(1003, ‘小白’, 1, ‘1997-8-30’,’湖南长沙’),
(1004, ‘小花’, 0, ‘1995-7-08’, ‘江苏南京’),
(1005, ‘小江’, 1, ‘1989-3-23’, ‘上海’),
(1006, ‘小飞’, 1, ‘1997-8-30’,’湖南长沙’);

#####删除数据
删除全表(一般不会使用)
delete from tb_student;
删除某一条记录
delete from tb_student where stuid=1003;

#####更新数据
没有加条件，全表所有人的生日和住址都会被改变
update tb_student set sbirth=’1999-2-03’, saddr=’四川成都’;
更新某一条数据(通常情况下更新和删除单条数据都是以主键作为条件)
update tb_student set sbirth=’1999-2-03’, saddr=’四川成都’ where stuid=1002;

在sql中一个=是相等的意思，如果想要赋值前面加set

批量更新数据

  • 使用or连接
1
update tb_student set saddr='统一地址' where stuid=1004 or stuid=1005 or stuid=1006;
  • 使用集合
1
update tb_student set addr='统一地址' where stuid in (1004, 1005, 1006);

例子1:

创建课程表和学生表

创建数据库

1
create database student default charset utf8;

创建课程表

1
2
3
4
5
6
7
create table tb_course
(
courseid int not null,
cname varchar(20) not null,
ccredit int not null,
primary key (courseid)
);

插入数据

1
2
3
4
5
insert into tb_course(courseid, cname, ccredit) values 
(001, 'python程序设计', 3),
(002, 'java', 2),
(003, 'linux操作系统', 1),
(004, '数据库基础', 1);

记录学生选课记录，还需要一张学生选课记录表（中间表）

分析学生表和课程表之间的关系，ER图（实体关系图）

ER图

专业的数据库建模工具：PowerDesigner

外键约束

创建一对一的人员表和身份证表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table tb_person
(
personid int not null auto_increment,
pname varchar (20) not null,
pbirth date,
primary key (personid)
);

create table tb_idcard
(
cardid char(18) not null,
cpolice varchar(20) not null,
cexpire date not null,
pid int not null,
primary key (cardid)
);

alter table tb_idcard add constraint fk_idcard_pid foreign key (pid) references tb_person (personid);

pid 只能参照人员表的主键personid，表示二者的一对一关系

fk_idcard_pid:表示foreignkey在idcard这张表中的pid字段

外键：一个外来的主键

参照完整性

录入数据

1
2
3
4
5
6
7
8
9
insert into tb_person (pname, pbirth) values
('小飞飞', '1992-09-01'),
('小可爱', '1992-10-01'),
('小红花'. '1982-09-01');

insert into tb_idcard values
('847584848373728323', '成都市武侯区公安局','2019-09-30', 1),
('818181818282738374', '成都市金牛区公安局','2022-09-30', 2),
('818184848373827283', '成都市锦江区公安局','2033-01-09', 3);

外键参照另一张表，有完整性约束，必须参照另一张表的主键，没有的主键也不能添加外键

建立一对一的关联关系

唯一性约束

1
alter table tb_idcard add constraint uk_idcard_pid unique(pid);

外键对应的记录不能重复添加，且有且仅有关联到另一张表的另一个唯一的主键，一对一的关联关系完成

如果没有唯一性约束就是一对多的关联关系了

且建立外键关系后，如需删除被参照的记录，需要先删除参照的记录行

外键列是加到多的那一边的

建立多对多的关联关系

建立第三张中间表，将两张表链起来

1
2
3
4
5
6
7
8
create table tb_sc
(
scid int not null auto_increment comment '专门作为主键的自增字段',
sid int not null comment '学号',
cid int not null comment '课程编号',
score float,
primary key (scid)
);

关系型数据库中也可以使用复合主键，primary key (sid, cid)，或者用sid，cid，和选课时间三个键作为复合主键。但是实际开发中，不会用这样的主键，因为不可重复操作，可能导致麻烦很多，且影响你的表的范式级别。

建立关系
1
2
3
4
alter table tb_sc add constraint fk_sc_sid 
foreign key (sid) references tb_student (stuid);
alter table tb_sc add constraint fk_sc_cid
foreign key (cid) REFERENCES tb_course (courseid);
插入数据
1
2
3
4
5
6
7
insert into tb_sc (sid, cid, score) values
(1001, 001, 89),
(1002, 003, 90),
(1003, 001, 100),
(1004, 004, 97),
(1005, 002, 65),
(1006, 001, 80);

通过这张中间表，学生和课程的多对多关系就建立起来了。

例子2

用户订单对应表

1
2
3
4
5
6
7
8
create table tb_user
(
username varchar(20) not null,
userpass varchar(20) not null,
nickname varchar(50) not null,
email varchar(255),
primary key(username)
);

如果关键字作为字段名称，可以加上反引号，就可以建立起来的

1
2
3
4
5
6
7
8
9
10
11
create table tb_order
(
orderid char(12) not null,
makedata datetime not null,
userid varchar(20) not null
);

alter table tb_order add primary key (orderid);
alter table tb_order add constraint fk_order_userid
foreign key (userid) references tb_user (username)
on delete restrict;

级联删除：删除用户时将所属订单全部删除

删除的几种情况：

cascade：级联删除

restrict：默认的情况，不让删除(一般都是这样设置)

set null：当删除用户时，订单中的用户id设置为空(加了非空约束，不成立)

删除基本不会用，只是标志为不可用了

及联更新：

允许修改的键不会设置为主键，可以修改的用户名不会设置为主键。

1
on updata cascade # 一般不用

一对多的数据关系：

插入数据：

1
2
3
4
5
6
7
insert into tb_user (username, userpass, nickname) values
('xiaohua', '123123', 'huahua'),
('xiaobai', '123456', 'abai');
insert into tb_order values
('112233445566', now(), 'xiaohua'),
('112233445567', now(), 'xiaobai'),
('112233445568', now(), 'xiaohua');

now()方法是mysql独有的，取到系统时间日期其他数据库没有

例子3:

多对多的读者管理系统

在可视化工具中建立，查看sql预览

建读者表：

1
2
3
4
5
6
7
8
create table tb_reader
(
readerid int not null auto_increment,
rname varchar(20) not null,
rtel char (11) not null,
remail varchar(255),
primary key (readerid)
);

只有主键能够自增

建书籍表：

可视化建表

同等对应的sql语句为：

1
2
3
4
5
6
7
8
9
create table tb_book 
(
bookid int not null, 
bname varchar(50) not null,
bauthor varchar(255) not null,
bpublisher varchar(255) not null,
bpubdate date not null,
primary key (bookid)
);

建立外键(举个栗子)

可视化建立外键

同等sql语句为：

1
2
3
alter table tb_book add constraint fk_book_author foreign key (bauthor)  references tb_user(username) 
on delete cascade
on update cascade;

sql预览如图所示：

image-20180827204209018

外键约束如上

中间表：图书借阅记录表（表示多对多关系）
1
2
3
4
5
6
7
8
9
create table tb_borrow_record
(
recid int not null auto_increment,
rid int not null,
bid int not null,
borrowdate datetime not null,
returndate datetime,
primary key (recid)
);
建立外键约束
1
2
3
alter table tb_borrow_record add constraint fk_borrow_record_rid foreign key (rid) references tb_reader (readerid);
alter table tb_record add CONSTRAINT fk_record_bid 
FOREIGN key (bid) references tb_book (bookid);

强制性要求还书日期大于借书日期

建立检查约束:保证数据的有效性（域完整性）
1
2
alter table tb_record add constraint ck_record_returndate 
check (returndate > borrowdate);

​ 写了没有报错，但是没有用。在MySQL中，和SQLite不生效（外键约束也米有用）。但是在oracle和db2和SQLserver等中支持

​ 在商业级应用中应该在程序里加这个检查，不是留给数据库检查