MySQL数据库1

1、MySQL数据库

（1）数据库简介

1、发展史
第一代：网状、层次型：使用指针来表示数据之间的联系。
第二代：关系型（1970.6 IBM研究员 埃德加▪考特 于ACM发表《大大型共享数据库数据的关系模型》
第三代：面向对象模型

2、概念
数据库（database，DB）是按照数据结构来组织、存储和管理数据的仓库，是一个长期存储于计算机中的、有组织的（区别于word）、可共享的（区别于Excel，并发操作）、统一管理的数据的集合。（实际就是磁盘中的文件+一个管理系统）

3、RDBMS术语
关系：二维表
元组：一行，也叫作记录。
属性：一列，也叫作字段。
域：属性的取值范围。
关键字：可以唯一标识元组的属性，在数据库中常称为主键。

4、常见的关系型数据库
Oracle（IBM）：运行稳定、可移植性高、功能齐全、性能超群。适用于大型企业领域。
DB2（IBM）：速度快、可靠性好、适用于海量数据、恢复性极强。适用于大中型企业领域。
SQL Server（MS）：全面、效率高、界面友好、操作容易、但是不跨平台。适用于中小型领域。
MySQL（MySQLAB->SUN->Oracle）:开源、体积小、速度快。适用于中小型企业领域。

（2）控制台下操作

创建数据库
create database test;

切换数据库
use test;

删除数据库
drop database test;

备份与还原
mysqldump –uroot –p databasename > d:/db.bak
mysql –uroot –p databasename < d:/db.bak

如果数据库过大,使用source进行恢复
source d:/db.bak

创建表

create table school(
  id int AUTO_INCREMENT PRIMARY KEY,  
  name varchar(15) not null,  
  address date,  
  cdate date,  
  phone varchar(15)
);

删除表
drop table school;

对表结构进行更改
alter table school add person varchar(15);
alter table school drop column cdate;

更新表的字段类型
alter table school modify column address varchar(15);

插入操作
insert into school values(1,'陈**','东北石油大学','151xxxxxxxx','人类');

（3）MySQL常用数据类型

2、结构化查询语言

（1）表的基本操作

查询数据库
show databases;

数据操作
create database 1801db;

锁定数据库
use 1801db;

显示数据库中的表
show tables;

创建学生表

drop table if exists stu_info;
create table stu_info(
    sno int primary key auto_increment,
    sname varchar(20) not null comment '姓名',
    age int check(age>=1 and age<=150),
    sex char(1) check(sex='男' or sex='女'),
    address varchar(20) default '地址不详',
    scope float
);

插入单条数据
insert into stu_info values(1,'张三',18,'男','北京',87);

插入多条的数据

insert into stu_info values
(2,'李四',19,'女','上海',87),
(3,'王五',20,'女','广州',98),
(4,'赵六',18,'男','深圳',88);

插入指定的数据属性
insert into stu_info(sno,sname,age,sex) values(5,'孙七',18,'男');

创建教师表

create table tea_info(
    tno varchar(10) primary key,
    tname varchar(20) not null,
    subject varchar(30)
);

insert into tea_info values
('1001','张老师','JAVA'),
('1002','王老师','JSP'),
('1003','刘老师','H5');

创建学生-教师关系表

create table stu_tea(
    sno int,
    tno varchar(10),
    foreign key(sno) references stu_info(sno),
    foreign key(tno) references tea_info(tno)
);

不能通过
insert into stu_tea values(1,1004);

通过
insert into stu_tea values (1,1001),(2,1001),(3,1002),(4,1002),(5,1003);

（2）CURD

创建（Create）、更新（Update）、读取（Retrieve）和删除（Delete）

更新

update stu_info set sex='女';
update stu_info set sex='男',age=24 where sno=1;
update stu_info set scope=scope+1;

删除

-- 如果没有外键限制,下面可以成功执行
delete from stu_info where scope<70;

查询(sql struct query languege)

select * from stu_info;
select sno,sname from stu_info;

desc 降序 / asc 升序

select sname,scope from stu_info
where scope is not null
order by scope desc;

起别名 限制数据条数
select sno as 学号,sname as 姓名 from stu_info limit 0,2;

查询成绩最高的学员信息
select * from stu_info order by scope desc limit 0,1;

（3）表间关系

三泛式:1.属性原子化 2.拥有主键 3.建立主外键的关系

一对一

身份证表:主表

drop table if exists card;
create table card(
  id varchar(18) primary key,
  address varchar(20) not null comment '发证机关',
  sendDate date not null comment '发证时间',
  maxAge int
);

人员表:外表

drop table if exists person;
create table person(
  pid varchar(18) not NULL primary key,
  pname varchar(20),
  address varchar(20),
  sex varchar(2),
  foreign key (pid) references card(id)
);

外键必须建立在主表的主键上
外键建立的时候外键和主表的主键类型必须要相同

insert into card values('1001','海港公安局','1995-05-06',10);
insert into card values('1002','天津公安局','1990-05-06',10);
select * from card;

insert into person values('1001','张三','北大荒','男');
insert into person values('1002','李四','北大荒','男');
select * from person;
-- 错误
insert into person values('1003','李四','北大荒','男');

一对多

部门表

drop table if exists dept;
create table dept(
  deptno int PRIMARY key,
  dname varchar(20) not null,
  loc varchar(50)
);

员工表

drop table if exists emp;
create table emp(
  eno int primary key auto_increment,
  ename varchar(30) unique,
  birthday date,
  job varchar(20) comment '工作',
  sal float default 1000,
  sex char(1),
  comm float comment '员工补助',
  deptno int comment '员工所在部门',
  foreign key (deptno) references dept(deptno)
);

insert into dept values(10,'财务部','-------');
insert into dept values(20,'研发部','-------');
insert into dept values(30,'人事部','-------');
select * from dept;

insert into emp values
(1001,'rose','1990-06-07','老师',2000,'女',1000,10),
(1002,'jack','1989-06-07','导游',4000,'男',1000,10),
(1003,'tom','1985-06-07','工程师',7000,'男',3000,20);
select * from emp;

插入失败
insert into emp values (1004,'jim','1990-06-07','出纳',2000,'女',1000,50);

多对多

创建学生表

drop table if exists stu_info;
create table stu_info(
    sno int primary key auto_increment,
    sname varchar(20) not null comment '姓名',
    age int check(age>=1 and age<=150),
    sex char(1) check(sex='男' or sex='女'),
    address varchar(20) default '地址不详',
    scope float
);

插入单条数据
insert into stu_info values(1,'张三',18,'男','北京',87);

插入多条的数据

insert into stu_info values
(2,'李四',19,'女','上海',87),
(3,'王五',20,'女','广州',98),
(4,'赵六',18,'男','深圳',88);

插入指定的数据属性
`insert into stu_info(sno,sname,age,sex) values(5,'孙七',18,’男’);`

创建教师表

create table tea_info(
    tno varchar(10) primary key,
    tname varchar(20) not null,
    subject varchar(30)
);
insert into tea_info values
('1001','张老师','JAVA'),
('1002','王老师','JSP'),
('1003','刘老师','H5');

创建学生-教师关系表

create table stu_tea(
  sno int,
    tno varchar(10),
    foreign key(sno) references stu_info(sno),
    foreign key(tno) references tea_info(tno)
);
-- 不能通过
insert into stu_tea values(1,1004);
-- 通过
insert into stu_tea values
(1,1001),(2,1001),(3,1002),(4,1002),(5,1003);