1、数据库定义

1.1.数据库

创建数据库

create database db_school default character set gb2312 default collate gb2312_chinese_ci;   

修改数据库
修改数据库默认字符集

alter database db_school default character set gb2312 default collate gb2312_chinese_ci;

删除数据库

drop database db_school;

1.2.表

创建表

create table tb_student 
(
studentNo char(10) not null unique,
studentName varchar(20) not null,
sex char(2),
birthday date,
native varchar(20),
nation varchar(10),
classNo char(6)
) engine=innodb;
create table tb_student1
(
studentNo char(10) not null unique auto_increment,
studentName varchar(20) not null,
sex char(2),
birthday date,
native varchar(20),
nation varchar(10),
classNo char(6)
) engine=innodb;
create table tb_student2
(
studentNo char(10) not null unique,
studentName varchar(20) not null,
sex char(2),
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6)
) engine=innodb;

表的查看
use db_school;show tables;
show tables from db_school;
show tables in db_school;

表结构简单查看
show columns from tb_student2;
desc tb_student2;

表结构详细查看
show create table tb_student2\G

表的重命名
alter table tb_student rename to backup_tb_student;
rename table backup_tb_student to tb_student;

表的删除
drop table tb_student,tb_student1,tb_student2;

1.3.字段

添加字段
alter table tb_student2 add column id int not null unique auto_increment first;
alter table tb_student add column department varchar(16) default '信息学院' after nation;

修改字段名和数据类型
alter table tb_student change column birthday age tinyint null default 18;

删除列的默认值
alter table tb_student alter column department drop default;

修改列的默认值
alter table tb_student alter column department set default '经济学院';

修改列的位置
alter table tb_student modify column department varchar(20) not null after studentName;

删除字段
alter table tb_student2 drop column id;

1.4.数据完整性约束


定义实体完整性


主键约束
实现主键约束有两种方式
列级完整性约束

create table tb_student
(
studentNo char(10) primary key,
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6)
) engine=innodb;

表级完整性约束

create table tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
primary key(studentNo)
) engine-innodb;

完整性约束命名

create table tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
constraint pk_student primary key(studentNo)
) engine=innodb;

候选键约束

create table tb_class
(
classNo char(6) primary key,
className varchar(20) not null,
department varchar(30) not null,
grade smallint,
classNum tinyint,
constraint uq_class inique(classNum)
) engine=innodb;

定义参照完整性


列级创建外键

create table tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6) references tb_class(classNo),
constraint pk_student primary key(studentNo)
) engine=innodb;

表级创建外键

create table tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
constraint pk_student primary key(studentNo),
constraint fk_student foreign key(classNo) references tb_class(classNo)
)
engine=innodb;

创建带参照动作的外键

create table tb_student
(
studentNo char(10),
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
constraint pk_student primary key(studentNo),
constraint fk_student foreign key(classNo) references tb_class(classNo)
on update restrict
on delete cascade
)
engine=innodb;

用户定义完整性


非空约束
前面已经很多例子了
check约束

create table tb_course
(
courseNo char(6),
courseName varchar(20) not null,
credit int not null,
courseHour int not null,
term char(2),
priorCourse char(6),
constraint pk_course primary key(courseNo),
constraint fk_course foreign key(priorCourse) references tb_course(courseNo),
constraint ck_course check(credit=courseHour/16)
)
engine=innodb;
create table tb_score
(
studentNo char(10),
courseNo char(6),
score float check(score >=0 and score<=100),
constraint pk_score primary key(studentNo,courseNo),
constraint fk_score1 foreign key(studentNo) references tb_student(studentNo),
constraint fk_score2 foreign key(courseNo) references tb_course(courseNo)
)
engine=innodb;

更新完整性约束


删除命名的外键
alter table tb_score drop foreign key fk_score1;
删除无名的外键
show create table tb_score\G获取系统指定的外键名
删除外键

删除主键
alter table tb_student drop primary key;

删除无名候选键约束
alter table tablename drop index 字段名;

删除有名候选键约束
alter table tb_class drop index uq_course;

添加有名主键约束
alter table tb_student add constraint pk_student primary key(studentNo);

添加有名外键约束
alter table tb_score add constraint fk_score1 foreign key(studentNo) reerences tb_student(studentNo);

添加有名候选键约束
alter table tb_class add constraint uq_class unique key(className);

2、数据更新

2.1.插入数据


插入完整记录


insert into tb_student 
values ('2014210103','王玲','女','1998-02-21','安徽','汉','CS1401');

这种插入方法严格依赖于表中字段顺序,在表结构发生变化的时候会不安全,不推荐这种做法,一般使用下面语句

insert into tb_student (studentNo,studentName,sex,birthday,native,nation,classNo)
values ('2013110102','赵婷婷','女','1996-11-30','天津','汉','AC1301');   

指定的列顺序是可以打乱的

insert into tb_student (studentNo,studentName,native,nation,sex,birthday,classNo)
values ('2013110203','孟颖','上海','汉','女','1997-03-20','AC1302');   

指定列插入记录


insert into tb_student (studentNo,studentName,sex,nation,classNo)
values ('2014310103','孙新','男','傣','IS1401');

没有指定的字段使用默认值


同时插入多条记录


insert into tb_student (studentNo,studentName,sex)
values ('2014310104','陈卓卓','女'),
('2014310105','马丽','女'),
('2014310106','许江','男');
insert into tb_student
values ('2014310107','赵鹏','男','1997-10-16','吉林','朝鲜','IS1401'),
('2014310108','李菊','女','1998-01-24','河北','IS1401');

插入查询结果


insert into tb_student_copy (studentNo,studentName,native,nation,sex,birthday,classNo)
select studentNo,studentName,native,nation,sex,birthday,classNo from tb_student;

2.2.修改数据


修改特定记录


update tb_student
set studenName='黄涛',native='湖北',nation='汉'
where studentN='2014210101';

修改所有记录


update tb_score
set score=score*1.05;

带查询修改


update tb_score
set score=0
where courseNo=(select courseNo from tb_course where courseName='程序设计');

2.3.删除记录

删除特定数据记录
delete from tb_student where studentName='王一敏';

带子查询删除
delete from tb_score where courseNo=(select courseNo from tb_course where courseName='程序设计');

delete删除所有记录
delete from tb_score;

truncate删除所有记录
truncate tb_student_copy
truncate是删除表,再创建一张同名的表,所以速度要比delete快

3.数据查询

3.1.select


单表查询


查询某个字段
select department from tb_class;
去重查询某个字段
select distinct department from tb_class;
查询所有字段
select * from tb_class;
查询计算的值
select studentName,sex,'Age:',YEAR(now())-YEAR(birthday) from tb_student;
定义字段别名
select studentName as 姓名,sex 性别,YEAR(NOW())-YEAR(birthday) 年龄 from tb_student;
查询记录-比较
select courseName,credit,courseHour from tb_course where courseHour>=48;
也可写成
select courseName,credit,courseHour from tb_course where not courseHour<48;

select studentName,sex,native,nation from tb_student where nation != '汉';
也可写成
select studentName,sex,native,nation from tb_student where not nation='汉';

select studentName,sex,native,nation from tb_student where nation <> '汉';
查询记录-限定范围
select studentName,sex,birthday from tb_student where birthday between '1997-01-01' and '1997-12-31;'
select studentName,sex,birthday from tb_student where birthday not between '1997-01-01' and '1997-12-31';
查询记录-属于范围
select * from tb_student where native in ('北京','天津','上海');
select * from tb_student where native not in ('北京','天津','上海');
查询记录-like
select * from tb_student where studentNo like '2013110103';
select * from tb_student where studentNo not like '2013110103';
select * from tb_student where studentName like '王%';
select * from tb_course where courseName like '%#_%'escape'#';
select * from tb_student where studentName like '王__';

查询记录-正则
select * from tb_course where courseName regexp '系统';
该查询等价于
select * from tb_course where courseName like '%系统%';
select * from tb_course where courseName regexp '管理|信息|系统';

查询记录-空值查询
select * from tb_course where priorCourse is null;
select * from tb_course where priorCourse is not null;

查询记录-多条件查询
select courseName,credit,courseHour from tb_course where credit>=3 and courseHour>32;
select studentName,native,nation from tb_student where native='北京' or native='上海';
select studentName,native,nation from tb_student where (native='北京' or native='湖南') and nation != '汉' and sex='男';

查询结果排序
select studentName,native,nation from tb_student order by studentName;
select * from tb_score where score>85 order by studentNo,score desc;

限制查询结果的数量
select studentNo,courseNo,score from tb_score order by score desc limit 2,3;

select studentNo,courseNo,score from tb_score order by score desc limit 3 offset 2;


分组查询


聚合函数查询
select count(*) from tb_student;
select count(distinct studentNo) from tb_student;
select avg(score) from tb_score where courseNo='21001';
select max(score) from tb_score where courseNo='21001';

分组查询
select studentNo,count(*),avg(score),max(score) from tb_score group by studentNo having avg(score)>=80;
select studentNo,count(*) from tb_score where score>88 group by studentNo having count(*)>2;


连接查询


内连接
select studentNo,studentName,native,tb_student.classNo,className from tb_student join tb_class on tb_student.classNo=tb_class.classNo where department='会计学院';
select a.studentNo,a.studentName,score from tb_student as a join tb_course b join tb_score c on a.studentNo=c.studentNo and b.courseNo=c.courseNo where courseName='程序设计';

select c1.* from tb_course as c1 join tb_course c2 on c1.credit=c2.credit where c2.courseName='数据库' and c1.courseName != '数据库';

外连接
select a.studentNo,studentName,sex,classNo,courseNo,score from tb_student as a left join tb_score b on a.studentNo=b.studentNo;
select courseNo,score a.studentNo,studentName,sex,classNo from tb_score c right join tb_student a on c.studentNo=a.stuudentNo;


子查询


子查询-IN
select studentName from tb_student where tb_student.studentNo in (select distinct studentNo from tb_score)

select distinct studentName from tb_student,tb_score where tb_student.studentNo=tb_score.studentNo;

子查询-比较
select studentNo,studentName,classNo from tb_student where classNo=(select classNo from tb_class where className='计算机 14-1班)'

select studentName,year(birthday) from tb_student where sex='男' and year(birthday) >any(select year(birthday) from tb_student where sex='女');

select studentName,year(birthday) from tb_student where sex='男' and year(birthday) >all(select year(birthday) from tb_student where sex='女');

子查询-exists
select studentName from tb_student where exists (select * from tb_score where tb_student.studentNo=tb_score.studentNo and courseNo='31002');
等价于
select studentName from tb_student where studentNo in (select studentNo from tb_score where courseNo='31002');

双重否定
select studentName from tb_student where not exists (select * from tb_course where not exists (select * from tb_score where tb_student.studentNo=tb_score.studentNo and courseNo=''31002));
`


联合查询


select studentNo from tb_course,tb_score where tb_course.courseNo=tb_score.courseNo and courseName='管理学' union select studentNo from tb_course,tb_score where tb_course.courseNo=tb_score.courseNo and courseName='计算机基础';

4.索引

查看索引
show index from tb_score\G
创建表时创建普通索引

create table tb_student1
(
studentNo char(20) not null,
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6),
index (studentName)
);

创建表时创建唯一索引

create table tb_student1
(
studentNo char(20) not null unique,
studentName varchar(20) not null,
sex char(2) not null,
birthday date,
native varchar(20),
nation varchar(10) default '汉',
classNo char(6)
);

创建表时创建主键索引
创建主键和外键的同时就会自动创建主键索引

create index创建索引
create index index_stu on tb_student(studentNo);
create index index_course on tb_course(courseName(3) desc);
create index index_book on tb_book(bclassNo,bookName desc);

alter 创建索引
alter table tb_student1 add index idx_studentName(studentName);

删除索引
drop index idx_studentName on tb_student1;
alter table tb_student drop index index_stu;

5.视图

创建视图
create or replace view v_student as select * from tb_student where sex='男' with check option;

删除视图
drop view v_student;

修改视图
alter view v_student (studentNo,studentName,classNo) as slect studentNo,studentName,classNo from tb_student where sex='男' and nation='汉' with check option;
查看视图
show create view v_student\G

通过视图更新数据
与表更新一样

查询视图
与表查询一样

6.触发器

创建触发器
create trigger tb_student_trigger_insert after insert on tb_student for each row set @str='abc';
插入数据
insert into tb_student values ('2013410101','王腾','男','1999-11-11','湖北','汉','AC1201';)
查询触发器
select @str;

删除触发器
drop trigger tb_student_trigger_insert;

查看触发器
show triggers from db_school\G

使用触发器
create trigger tb_student_trigger_insert after insert on tb_student for each row set @ str=new.studentNo;
插入数据后
select @str;

create trigger tb_student_trigger_update before update on tb_student for each row set new.nation=old.native;

7.存储过程函数

创建存储过程

delimiter $$
create procedure sp_update(in sno char(10),in char(2) ssex)
begin
update tb_student set sex=ssex where studentNo=sno;
end $$  

调用存储过程
call sp_update('2013110201','男');

删除存储过程
drop procedure sp_update;

创建存储函数

delimiter $$
create function fn_search(sno char(10),cno char(5))
return float
deterministic
begin
declare sscore float;
select score into sscore from tb_score where studentNo=sno and courseNo=cno;
if sscore is  null then
    return (select 0);
else return sscore;
end if;
end $$

调用存储函数
select fn_search('2012022200','21004');

删除存储函数
drop function fn_search;

8、开发

执行数据库操作

<? php
    $con=mysql_connect("localhost:3306","root","123456") or die("数据库连接失败!<br>");
    mysql_select_db("db_school",$con) or die("数据库选择失败");
    mysql_query("set names 'gbk'");
    $sql="insert into tb_student(studentNo,studentName,sex,birthday,native,nation,classNo)";
    $sql=$sql.values('......');
    if (mysql_query($sql,$con))
        echo "学生添加成功<br>";
    else
        echo "学生添加失败";
    fi
?> 

上面是一个插入数据的操作,更新删除类似