MySQL · 基本操作
MYSQL数据库
-------------------------------------------------------------------------
- 关系模型的基本术语
关系：整个二维表
关系名：表格名称
元组：行数据（记录）
属性：列数据（字段/分量）
属性名：列名称（字段名）
主键：唯一确定元组的属性组（关键字）
域：属性的取值范围
关系模式：关系的描述，表示为：关系名（属性列表）
例如：学生（学号，姓名，性别，年龄，系别）
关系的约束：域完整性约束，实体完整性约束，参照完整性约束
-------------------------------------------------------------------
本地连接：
mysql -u shiyi -p
远程连接：
mysql -h 192.144.137.179 -P 3306 -u shiyi -p
mysql -h 192.144.137.179 -u shiyi -p
- 一、数据库设计
—————————————————————————————————
1.1 >>>创建数据库：（urban_cloud_reservation）
create database if not exists urban_cloud_reservation
default character set=utf8
default collate=utf8_general_ci;
1.2 >>>修改数据库：
alter database urban_cloud_reservation
default character set=utf8
default collate=utf8_general_ci;
1.3 >>>删除数据库：
drop database if exists urban_cloud_reservation;
drop schema if exists urban_cloud_reservation;
1.4.1 >>>查看建库信息：
show create datebase urban_cloud_reservation;
1.4.2 >>>选择使用数据库：
use urban_cloud_reservation;
1.4.3 >>>查看所有数据库：
show databases;
show schemas;
- 二、数据表设计
--------------------------------------------------------------------
2.1 >>>定义表
--------------------------------------------------------------------
2.1.1 >>>MYSQL常见数据类型：
--------------------------------------------------------------------
数值类型： 类型 无符号 有符号
bit(M) 位字段类型 M表示数值范围1~64
tinyint(M) [unsigned] [zerofill] 很小整数型 无符号表示0~255 有符号表示-128~127
bool,boolean==tinyint(1) 0~1
smallint (M) 小的整数 0~65535 -32768~32767
mediumint(M) 中等大小整数 0~1677 7215 -8388608~8388607
int(M)==integer 普通大小整数 0~42 9496 7295 -2147483648~2147483647
bigint(M) 大整数 0~1844 6744 0737 0955 1615
float(M) 单精度浮点数值
double(M,D)) 双精度浮点数, M为小数总位数 D为小数点后位数(M,D受系统限制)
decimal(M,D)==dec 压缩的严格定点数, M为小数总位数 D为小数点后位数M<50,D<30
--------------------------------------------------------------------
时间和日期类型：
date 日期型 1000-01-01~9999-12-31
datetime 日期时间型 1000-01-01 00:00:00~9999-12-31 23:59:59
timestamp 时间戳 1970-01-01 00:00:00~2037年
time 时间型 -838:59:59~838:59:59
year 年型(2或4位) 1901~2155&0000 70~69(1970~2069)
--------------------------------------------------------------------
字符串类型：
char 定长字符串 0-255字节
varchar 变长字符串 0-65535 字节
tinyblob 不超过 255 个字符的二进制字符串 0-255字节
tinytext 短文本字符串 0-255字节
blob 二进制形式的长文本数据 0-65 535字节
text 长文本数据 0-65 535字节
mediumblob 二进制形式的中等长度文本数据 0-16 777 215字节
mediumtext 中等长度文本数据 0-16 777 215字节
longblob 二进制形式的极大文本数据 0-4 294 967 295字节
longtext 极大文本数据 0-4 294 967 295字节2^30*4
--------------------------------------------------------------------
--------------------------------------------------------------------
2.1.2 >>>创建user表：
create table if not exists ucr_user(
userID bigint unsigned not null auto_increment,
Uname varchar(50) not null default "向日葵",
Upicture mediumblob,
Usex varchar(1) default "女",
Uage tinyint default 18,
Unickname varchar(10),
Uphone bigint(12),
Uqq bigint(12),
Uemail varchar(30),
constraint PK_student primary key(userID) on update restrict on delete cascade
)engine=innoDB auto_increment=0 default charset=utf8;
~~~~~~~~~~
create table db_school.tb_class(
classNo char(6) primary key,
className varchar(20) not null unique,
department varchar(30) not null,
grade smallint,
classNum tinyint
)engine=InnoDB;
~~~~~~~~~~
create table db_school.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 cascade
on delete cascade
)engine=InnoDB;
~~~~~~~~~~
create table db_school.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),
on update cascade
on delete cascade
constraint CK_course check(credit=courseHour/16)
)engine=InnoDB;
~~~~~~~~~~
create table if not exists tb_score(
studentNo char(10),
courseNo char(6),
score float check(score>=0 and score <=100),
constraint PK_score primary key(studentNo,courseNo),
constraint FK1_score foreign key(studentNo) references tb_student(studentNo),
on update cascade
on delete cascade
constraint FK2_score foreign key(courseNo) references tb_course(courseNo)
)engine=innoDB;
--------------------------------------------------------------------
2.1.3-1 >>>查看数据表的基本结构
desc table ucr_user;
desc ucr_user;
describe ucr_user;
show columns from ucr_user from urban_cloud_reservation;
show columns in ucr_user in urban_cloud_reservation;
show columns in ucr_user;
-------------------------------------------------------------------
2.1.3-2 >>>查看数据表的详细结构
show create table ucr_user;
-------------------------------------------------------------------
DROP TABLE语句
--------------------
2.1.4 >>>删除表
drop table ucr_user;
drop table if exists ucr_user,user;
drop table if exists urban_cloud_reservation.ucr_user;
-------------------------------------------------------------------
ALTER TABLE语句
--------------------
2.1.5 >>>重命名表
alter table ucr_user rename to user;
alter table user rename ucr_user;
rename table ucr_user to user;
rename table ucr_user to user,user to ucr_user;
-------------------------------------------------------------------
2.1.6-1 >>>添加表字段
alter table urban_cloud_reservation.ucr_user
add column Udescription varchar(200) null default "我是一只小小鸟！" after Uemail;
alter table urban_cloud_reservation.ucr_user
add column UU bigint(20) not null unique first;
-------------------------------------------------------------------
2.1.6-2 >>>修改表字段
>change： 改列名，类型，约束
alter table ucr_user change column UU Up tinyint(4) unsigned not null;
>alter ： 改默认值
alter table ucr_user alter column Up set default 66669999;
>alter ： 删除默认
alter table ucr_user alter column Up drop default;
>modify： 改类型，约束，
alter table ucr_user modify Up bigint(20) not null unique first;
-------------------------------------------------------------------
2.1.6-3 >>>drop：删除表字段
alter table ucr_user drop column Up;
-------------------------------------------------------------------
-------------------------------------------------------------------
2.2 >>>数据的完整性约束
-------------------------------------------------------------------
2.2.1 >>>定义实体完整性
-------------------------------------------------------------------
>>>主键约束
>单键约束方式一：
create table if not exists ucr_test(
id bigint,
name varchar(50),
userID bigint,
constraint UCR
primary key(id)
,unique key(name)
,foreign key(userID) references ucr_user(userID)
,check(id<100)
);
>单键约束方式二：
create table if not exists ucr_test(
id int unsigned not null,
name varchar(50),
primary key(id)
);
>>>复合主键约束：
create table if not exists ucr_test(
id int unsigned not null,
name varchar(50),
primary key(id,name)
);
-------------------------------------------------------------------
>>>完整性约束的命名
constraint UCR
primary key(id)
,unique(name)
,foreign key(id) references ucr_user(id)
,check(id<100)
}
-------------------------------------------------------------------
>>>候选键约束
>单键约束方式一：
create table if not exists ucr_test(
id bigint unique,
name varchar(50),
userID bigint,
);
>单键约束方式二：
create table if not exists ucr_test(
id int unsigned not null,
name varchar(50),
age tinyint(4),
constraint UCR primary key(id),unique key(age)
);
-------------------------------------------------------------------
>候选键复合健约束
create table if not exists ucr_test(
id int unsigned not null,
name varchar(50),
age tinyint(4),
constraint UCR primary key(id),unique(name,age),unique(age)
);
-------------------------------------------------------------------
2.2.2 >>>定义参照完整性
-------------------------------------------------------------------
>>>外键约束
create table db_school.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 FK1_student foreign key(classNo) references tb_class(classNo),
constraint FK2_student foreign key(classNo) references tb_student(studentNo)
)engine=InnoDB;
-------------------------------------------------------------------
>>>参照完整性约束的违约处理策略
1、restrict 限制策略
2、cascade 级联策略
3、set null 置空策略
4、no action 不采取措施(同限制策略)
5、set default 默认值策略
参照动作适用语句
1、update
2、delete
create table db_school.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;
-------------------------------------------------------------------
2.2.3 >>>用户自定义的完整性
-------------------------------------------------------------------
1、设置非空约束 列级
2、check约束 列级&表级
3、触发器
create table db_school.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 UK_course unique key(credit),
constraint FK_course foreign key(priorCourse)
references tb_course(courseNo),
constraint CK_course check(credit=courseHour/16)
)engine=InnoDB;
-------------------------------------------------------------------
2.2.3 >>>更新完整性约束
-------------------------------------------------------------------
>删除约束->外键
alter table tb_course drop foreign key FK_course;
>删除约束->主键
alter table tb_course drop primary key;
>删除约束->候选键
alter table tb_course drop index UK_course;
>添加约束->主键
alter table tb_course add constraint PK_course primary key(courseNo);
>添加约束->外键
alter table tb_course add constraint FK_course foreign key(priorCourse) references tb_course(courseNo);
>添加约束->候选键
alter table tb_course add constraint UK_course unique key(credit);
-------------------------------------------------------------------
- 三、数据查询
3.1 select语句
3.2 select子句
3.3 分组聚合查询
3.4 连接查询
3.5 子查询
3.6 联合查询
—————————————————————————————————
3.1 @@@select语句
select [All|distinct|distinctrow] <目标表达式1>[,<目标表达式2>]...
from <表达式1或视图1>[,<表达式2或视图名2>]...
[where<条件表达式>]
[group by <列名1>[having<条件表达式>]]
[order by <列名2>[asc|desc]]
[limit [m,]n];
—————————————————————————————————
3.2 @@@select子句
—————————————————————————————————
3.2.1-1 @@@选择字段查询
select courseName from tb_course;
select distinct courseName from tb_course;
select distinctrow courseName from tb_course;
—————————————————————————————————
3.2.1-2 @@@查询所有字段
select * from tb_course;
—————————————————————————————————
3.2.1-3 @@@查询经过计算的值
select studentName,sex,'Age:',year(now())-year(birthday) from tb_student;
—————————————————————————————————
3.2.1-4 @@@定义字段别名
select studentName as 姓名,sex as 性别,year(now())-year(birthday) as 年龄 from tb_student;
select studentName as '姓 名',sex as 性别,year(now())-year(birthday) as 年龄 from tb_student;
select studentName 姓名,sex 性别,year(now())-year(birthday) 年龄 from tb_student;
—————————————————————————————————
3.2.2 @@@where子句选择指定记录
比较 =、 <>、!=、<、<=、>、 >=、!>、!<、not+含比较运算的表达式
确定范围 between and、not between and
确定集合 in、not in
字符匹配 like 、not like
空值 is null、is not null
多重查询 and、or
—————————————————————————————————
3.2.2-1 @@@比较
select courseName,credit,courseHour from tb_course where courseHour>28;
select courseName,credit,courseHour from tb_course where not courseHour<28;
select studentName,sex,native,nation from tb_student where nation!='汉';
select studentName,sex,native,nation from tb_student where nation<>'汉';
select studentName,sex,native,nation from tb_student where not nation='汉';
—————————————————————————————————
3.2.2-2 @@@between and关键字
select * from tb_student where birthday between '1991-01-01' and '2018-04-06';
select * from tb_student where birthday not between '1991-01-01' and '2018-04-06';
—————————————————————————————————
3.2.2-3 @@@in关键字
select * from tb_student where native in('北京','上海','河南省周口市');
select * from tb_student where native not in('北京','上海');
—————————————————————————————————
3.2.2-4 @@@like关键字
select * from tb_student where studentNo like '1601141019';
select * from tb_student where studentName like '晏%';
select * from tb_student where studentName not like '晏%';
select * from tb_student where studentName like '%沈%';
select * from tb_student where studentName like '晏_';
select * from tb_student where studentName like '晏__';
select * from tb_student where studentName like '_沈_';
select * from tb_student where studentName like '__沈';
—————————————————————————————————
3.2.2-5 @@@正则表达式regexp 常用字符匹配列表
<串> 匹配包括指定字符串的文本
[] 匹配[ ]中的任意一个字符
[^] 匹配不在[ ]中的任意一个字符
^ 匹配文本的开始字符
$ 匹配文本的结尾字符
. 匹配任意单个字符
* 匹配0个或多个*前面指定的字符
+ 匹配+前面的字符至少1次或多次
{n} 匹配前面的字符至少n次
select * from tb_course where courseName regexp 'sy'; == select * from tb_course where courseName like '%sy%';
select * from tb_course where courseName regexp '[sy]';
select * from tb_course where courseName regexp '[^sy]';
select * from tb_course where courseName regexp '^sy';
select * from tb_course where courseName regexp 'sy$';
select * from tb_course where courseName regexp 's.y';
select * from tb_course where courseName regexp 's*y';
select * from tb_course where courseName regexp 'sy+';
select * from tb_course where courseName regexp 's{3}y';
select * from tb_course where courseName regexp 'shiyi|jdd|lm';
—————————————————————————————————
3.2.2-6 @@@is null关键字空值查询
select * from tb_course where priorCourse is null;
select * from tb_course where priorCourse is not null;
—————————————————————————————————
3.2.2-7 @@@带and和or的多条件查询
select * from tb_course where courseName regexp 'sy' and priorCourse is null;
select * from tb_course where courseName regexp 'sy' or priorCourse is null;
select * from tb_course where courseName regexp 'sy' or priorCourse is null and credit>3;
—————————————————————————————————
3.2.3 @@@对查询结果排序
select * from tb_student order by studentNo desc;
select * from tb_student order by studentName asc;
select * from tb_student order by studentName asc,score desc;
select * from tb_student where score>85 order by studentNo,score desc;
—————————————————————————————————
3.2.4 @@@限制查询结果的数量
select * from tb_student order by studentName limit 1,5;
select * from tb_student order by studentName limit 5 offset 1;
—————————————————————————————————
3.3 @@@分组聚合查询
—————————————————————————————————
3.3.1 @@@聚合函数查询
select count(distinct *) from tb_student;
select count(*) from tb_student;
select count(all *) from tb_student;
select count(all courseName) from tb_student;
select max(all courseName) from tb_student;
select min(all courseName) from tb_student;
select sum(all courseName) from tb_student;
select avg(all score) from tb_score;
—————————————————————————————————
3.3.2 @@@分组聚合查询 [group by 字段列表] [having <条件表达式>]
select courseNo,count(studentNo) from tb_score group by courseNo;
select studentNo,count(*) 选课门数,avg(score) 平均分,max(score) 最高分 from tb_score group by studentNo;
select studentNo,count(*) 选课门数,avg(score) 平均分,max(score) 最高分 from tb_score group by studentNo having avg(score)>=95;
select studentNo,count(*) 选课门数,avg(score) 平均分,max(score) 最高分 from tb_score group by studentNo having count(*)>2;
SELECT typeID,GROUP_CONCAT(Tname) FROM ucr_supplier_reservation_type GROUP BY typeID;
—————————————————————————————————
3.4 @@@连接查询(多表查询)
—————————————————————————————————
3.4.1 @@@连接查询(多表查询)->交叉连接
select * from tb_student cross join tb_score;
select * from tb_student,tb_score;
—————————————————————————————————
3.4.2 @@@连接查询(多表查询)->内连接
3.4.2-1 @等值与非等值连接
select tb_student.*,tb_score.*
from tb_student,tb_score
where tb_student.studentNo=tb_score.studentNo;
select tb_student.*,tb_score.*
from tb_student inner join tb_score
on tb_student.studentNo=tb_score.studentNo;
select tb_student.*,tb_score.*
from tb_student join tb_score
on tb_student.studentNo=tb_score.studentNo;
select studentNo,studentName,native,tb_student.classNo,className
from tb_student,tb_class
where tb_student.classNo=tb_class.classNo and department='信息工程学院';
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,studentName,score
from tb_student as a,tb_course b,tb_score c
where a.studentNo=c.studentNo and b.courseNo=c.courseNo and courseName='计算机网络';
select a.studentNo,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='计算机组成原理';
—————————————————————————————————
3.4.2 @@@连接查询(多表查询)->内连接
3.4.2-2 @自连接
select c1.*
from tb_course c1,tb_course c2
where c1.credit=c2.credit and c2.courseName='数据库原理';
select c1.*
from tb_course c1 join tb_course c2
on c1.credit=c2.credit
where c2.courseName='数据库原理';
select c1.*
from tb_course c1 join tb_course c2
on c1.credit=c2.credit
where c2.courseName='数据库原理' and c1.courseName!='数据库原理';
—————————————————————————————————
3.4.2 @@@连接查询(多表查询)->内连接
3.4.2-3 @自然连接
select a.studentNo,studentName,courseNo,score
from tb_student a natural join tb_score b;
—————————————————————————————————
insert into tb_student values('1601141027','一静','女','1998-7-24','苏州','汉','3');
3.4.3 @@@连接查询(多表查询)->外连接
3.4.3-1 @左外连接
select a.studentNo,studentName,sex,classNo,courseNo,score
from tb_student a left outer join tb_score b
on a.studentNo=b.studentNo;
select a.studentNo,studentName,sex,classNo,courseNo,score
from tb_student a left join tb_score b
on a.studentNo=b.studentNo;
—————————————————————————————————
3.4.3 @@@连接查询(多表查询)->外连接
3.4.3-2 @右外连接
select courseNo,score,b.studentNo,studentName,sex,classNo
from tb_score a right outer join tb_student b
on a.studentNo=b.studentNo;
—————————————————————————————————
3.5 @@@子查询
3.5.1 @带in关键字子查询
select studentNo,studentName
from tb_student
where tb_student.studentNo in
(select distinct tb_score.studentNo from tb_score);
select studentNo,studentName
from tb_student
where tb_student.studentNo not in
(select distinct tb_score.studentNo from tb_score);
—————————————————————————————————
3.5 @@@子查询
3.5.2 @带比较关键符的子查询
select studentNo,studentName from tb_student
where classNo=(select classNo from tb_class where className='物联网工程');
select studentNo,studentName from tb_student
where classNo<>(select classNo from tb_class where className='物联网工程');
select studentNo,studentName from tb_student
where classNo='1';
select studentNo,studentName,classNo from tb_student s1
where classNo=
(select classNo from tb_student s2 where studentName='一静');
select studentNo,studentName,classNo from tb_student s1
where classNo=
(select classNo from tb_student s2 where studentName='一静') and studentName<>'一静';
select studentNo,studentName,classNo from tb_student s1
where classNo=
(select classNo from tb_student s2 where studentNo='1601141019') and studentNo!='1601141019';
select studentNo,studentName,year(birthday) from tb_student
where sex='男' and year(birthday)<any(
select year(birthday) from tb_student where sex='女');
使用聚合函数查询
select studentNo,studentName,year(birthday) from tb_student
where sex='男' and year(birthday)<(
select max(year(birthday)) from tb_student
where sex='女');
—————————————————————————————————
3.5 @@@子查询
3.5.3 @带exists关键字子查询
select studentNo,studentName from tb_student a
where exists(
select * from tb_score b
where a.studentNo=b.studentNo and courseNo='1');
等价in关键字查询
select studentNo,studentName from tb_student
where studentNo in(
select studentNo from tb_score where courseNo='1');
select studentNo,studentName from tb_student a
where not exists(
select * from tb_score b
where a.studentNo=b.studentNo and courseNo='1');
等价in关键字查询
select studentNo,studentName from tb_student
where studentNo not in(
select studentNo from tb_score where courseNo='1');
not exists嵌套使用，双重否定表示肯定
select studentNo,studentName from tb_student x
where not exists
(select * from tb_course c where not exists
(select * from tb_score where studentNo=x.studentNo and courseNo=c.courseNo));
—————————————————————————————————
3.6 @@@联合查询(支持并运算)
select studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and courseName='计算机网络'
union
select studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and courseName='计算机组成原理';
等价查询语句
select distinct studentNo
from tb_score,tb_course
where tb_score.courseNo=tb_course.courseNo
and (courseName='计算机网络' or courseName='计算机组成原理');
select studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and courseName='计算机网络'
union all
select studentNo from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and courseName='计算机组成原理';
等价查询语句
select studentNo
from tb_score,tb_course
where tb_score.courseNo=tb_course.courseNo
and (courseName='计算机网络' or courseName='计算机组成原理');
select studentNo 学号
from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and courseName='计算机网络' limit 0,5
union all
select studentNo 学生学号
from tb_score,tb_course where tb_score.courseNo=tb_course.courseNo and courseName='计算机组成原理'
limit 0,3;
交运算与差运算用子查询实现
select studentNo
from tb_score,tb_course
where tb_score.courseNo=tb_course.courseNo and courseName='计算机网络'
and studentNo in (
select studentNo
from tb_score,tb_course
where tb_score.courseNo=tb_course.courseNo and courseName='计算机组成原理');
select studentNo
from tb_score,tb_course
where tb_score.courseNo=tb_course.courseNo and courseName='计算机网络'
and studentNo not in (
select studentNo
from tb_score,tb_course
where tb_score.courseNo=tb_course.courseNo and courseName='计算机组成原理');
—————————————————————————————————
- 四、数据更新
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.1 ###插入数据
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.1.1 ###插入完整的数据记录
insert into db_school.tb_student
values('1601141001','鸿达','男','1998-7-14','周口','汉','3');
insert into db_school.tb_student(studentNo,studentName,sex,birthday,native,nation,classNo)
values('1601141012','李梦','女','1998-6-24','周口','汉','3');
insert into db_school.tb_student(studentNo,nation,classNo,studentName,sex,birthday,native)
values('1601141013','汉','3','子龙','男','1998-6-24','上海');
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.1.2 ###为表指定字段插入数据
insert into db_school.tb_student(studentNo,studentName,sex)
values('1601141022','张良','男');
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.1.3 ###同时插入多条数据
insert into db_school.tb_student(studentNo,nation,classNo,studentName,sex,birthday,native)
values('1601141018','汉','3','子龙','男','1998-6-24','北疆'),
('1601141020','汉','3','子龙','男','1998-6-24','北疆'),
('1601141021','汉','3','子龙','男','1998-6-24','北疆');
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.1.4 ###插入查询结果(可用于复制表数据)
create table db_school.tb_student_copy(
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 cascade
on delete cascade
)engine=InnoDB;
insert into db_school.tb_student_copy(studentNo,studentName,native,nation,sex,birthday,classNo)
select studentNo,studentName,native,nation,sex,birthday,classNo from tb_student;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.1.5 ###使用replace语句插入表数据(在没有定义外键时，可用于覆盖原来的插入语句)
replace into tb_student_copy
values('1601141012','李梦','女','1998-6-24','周口','汉','3');
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.2 ###修改数据记录
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.2.1 ###修改特定数据记录
update db_school.tb_student
set studentName='简单点',native='河南周口沈丘'
where studentNo='1601141019';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.2.1 ###修改所有数据记录
update db_school.tb_student_copy
set studentName='简单点',native='河南周口沈丘';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.2.3 ###带子查询的修改
update db_school.tb_score
set score=0
where courseNo=(select courseNo from tb_course where courseName='计算机组成原理');
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.3 ###删除数据记录
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.3.1 ###删除特定数据记录
delete from tb_student
where studentName='李梦';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.3.2 ###带子查询的删除
delete from db_school.tb_score
where courseNo=(select courseNo from tb_course where courseName='计算机组成原理');
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
4.3.3 ###删除所有数据记录
delete from db_school.tb_student_copy;
truncate db_school.tb_student_copy;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- 五、索引
—————————————————————————————————
5.1 &&&索引分类（单列索引&组合索引）
—————————————————————————————————
1、普通索引
2、唯一性索引
3、主键索引
4、聚簇索引
5、全文索引
—————————————————————————————————
5.2 &&&查看表索引
show index from tb_student;
show indexes from tb_student;
show keys from tb_student;
show keys in tb_student;
show keys in tb_student from db_school;
show keys in tb_student in db_school;
show index from tb_student \G
show indexes from tb_student \G
show keys from tb_student \G
show keys in tb_student \G
show keys in tb_student from db_school\G
show keys in tb_student in db_school\G
—————————————————————————————————
5.3 &&&创建索引
—————————————————————————————————
5.3.1 &&&使用create table语句创建索引
&创建普通索引
create table if not exists tb_test1(
studentNo char(10),
courseNo char(6),
score float check(score>=0 and score <=100),
index(studentNo)
)engine=innoDB;
&创建唯一性索引
create table if not exists tb_test2(
studentNo char(10),
courseNo char(6),
score float check(score>=0 and score <=100),
unique(studentNo)
)engine=innoDB;
&创建主键同时创建主键索引
create table if not exists tb_test3(
studentNo char(10),
courseNo char(6),
score float check(score>=0 and score <=100),
primary key(studentNo)
)engine=innoDB;
&创建外键同时创建外键索引
create table if not exists tb_test4(
studentNo char(10),
courseNo char(6),
score float check(score>=0 and score <=100),
constraint tb_test4 foreign key(studentNo) references tb_test1(studentNo)
on update cascade
on delete cascade
)engine=innoDB;
—————————————————————————————————
5.3.2 &&&使用create index语句创建索引
create index index_stuNo on db_school.tb_test(studentNo);
create index index_stuNo on db_school.tb_test(studentNo(3) desc);
create index index_stuNo on db_school.tb_test(studentNo desc,courseNo asc);
—————————————————————————————————
5.3.3 &&&使用alter table语句创建索引
alter table db_school.tb_test add index index_stuNo(studentNane);
alter table db_school.tb_test add constraint PK_student primary key(studentNo),
alter table db_school.tb_test add constraint FK_student foreign key(courseNo) references tb_score(scoreNo)
on update cascade
on delete cascade
—————————————————————————————————
5.4 &&&删除索引
drop index index_stuNo on db_school.tb_test;
alter table db_school.tb_test1 drop index studentNo;
alter table db_school.tb_test3 drop primary key;
alter table db_school.tb_test4 drop foreign key tb_test4;
—————————————————————————————————
5.5 &&&关于索引的使用
问题：降低更新表中数据的速度
增加存储空间
使用原则：
在插入，修改，删除，操作较多的数据表上避免过多建立索引
数据较小的表不建索引
查询表达式中经常使用，有较多不同值的字段上建立索引
使用link关键字时开头使用%会时索引失效
—————————————————————————————————
- 六、视图
—————————————————————————————————
6.1 %%%视图概述
视图是提供一种对查询操作的封装
—————————————————————————————————
6.2 %%%创建视图
create or replace view db_school.v_student
as
select * from db_school.tb_student where sex='男'
with check option;
create view db_school.v_score_avg(studentNo,scoreNo)
as
select studentNo,avg(score) from tb_score
group by studentNo;
create view v_score
as
select * from db_school.tb_score where score<90
with check option;
create view v_score_local
as
select * from db_school.v_score where score>80
with 。 check option;
create view v_score_cascaded
as
select * from db_school.v_score where score>80
with cascaded check option;
insert into db_school.v_score values('1601141001','2',92);
insert into db_school.v_score_local values('1601141002','2',92);
insert into db_school.v_score_cascaded values('1601141003','2',92);
—————————————————————————————————
6.3 %%%删除视图
drop view if exists db_school.v_score;
drop view if exists db_school.v_score_local;
drop view if exists db_school.v_score_cascaded;
—————————————————————————————————
6.4 %%%修改视图
alter view db_school.v_student(studentNo,studentName,classNo)
as
select studentNo,studentName,classNo from db_school.tb_student
where sex='男' and nation='汉'
with check option;
—————————————————————————————————
6.5 %%%查看视图定义
show create view v_student \G
—————————————————————————————————
6.6 %%%更新视图数据
insert 语句插入
insert into db_school.v_score values('1601141001','2',92);
update语句修改基础表的数据
update db_school.v_student set studentName='黎黎' where studentNo='1601141038';
delete语句删除
delete from db_school.v_student where studentName='文文';
—————————————————————————————————
6.7 %%%查看视图数据
select * from v_student where studentNo='10601141038';
—————————————————————————————————
- 七、触发器
—————————————————————————————————
7.1 >>>创建触发器
create trigger db_school.tb_student_insertAfterEvent
after insert
on db_school.tb_student
for each row set @str='one student add!';
insert into db_school.tb_student
values('1601141023','123','男','1998-7-14','周口','汉','3');
—————————————————————————————————
7.2 >>>删除触发器
drop trigger if exists db_school.tb_student_insertAfterEvent;
—————————————————————————————————
7.3 >>>使用触发器
insert触发器
create trigger db_school.tb_student_insertAfterEvent
after insert
on db_school.tb_student
for each row set @str=new.studentNo;
delete触发器
create trigger db_school.tb_student_deleteAfterEvent
before delete
on db_school.tb_student
for each row set @onum=old.studentNo;
update触发器
create trigger db_school.tb_student_updateAfterEvent
before update
on db_school.tb_student
for each row set new.nation=old.nation;
insert into db_school.tb_student
values('1601141026','789','女','1998-7-14','沈丘','藏','3');
—————————————————————————————————
创建触发器
在MySQL中，创建触发器语法如下：
代码如下:
CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
其中：
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。
由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。
另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。
trigger_event 详解
MySQL 除了对 INSERT、UPDATE、DELETE 基本操作进行定义外，还定义了 LOAD DATA 和 REPLACE 语句，这两种语句也能引起上述6中类型的触发器的触发。
LOAD DATA 语句用于将一个文件装入到一个数据表中，相当与一系列的 INSERT 操作。
REPLACE 语句一般来说和 INSERT 语句很像，只是在表中有 primary key 或 unique 索引时，如果插入的数据和原来 primary key 或 unique 索引一致时，会先删除原来的数据，然后增加一条新数据，也就是说，一条 REPLACE 语句有时候等价于一条。
INSERT 语句，有时候等价于一条 DELETE 语句加上一条 INSERT 语句。
INSERT 型触发器：插入某一行时激活触发器，可能通过 INSERT、LOAD DATA、REPLACE 语句触发；
UPDATE 型触发器：更改某一行时激活触发器，可能通过 UPDATE 语句触发；
DELETE 型触发器：删除某一行时激活触发器，可能通过 DELETE、REPLACE 语句触发。
BEGIN … END 详解
在MySQL中，BEGIN … END 语句的语法为：
BEGIN
[statement_list]
END
其中，statement_list 代表一个或多个语句的列表，列表内的每条语句都必须用分号（;）来结尾。
而在MySQL中，分号是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL可以开始执行了。因此，解释器遇到statement_list 中的分号后就开始执行，然后会报出错误，因为没有找到和 BEGIN 匹配的 END。
这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思），它是一条命令，不需要语句结束标识，语法为：
DELIMITER new_delemiter
new_delemiter 可以设为1个或多个长度的符号，默认的是分号（;），我们可以把它修改为其他符号，如$：
DELIMITER $
在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了$，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。
一个完整的创建触发器示例
假设系统中有两个表：
班级表 class(班级号 classID, 班内学生数 stuCount)
学生表 student(学号 stuID, 所属班级号 classID)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：
代码如下:下载地址 springmvc+mybatis+spring 整合SSM
DELIMITER $
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end$
DELIMITER ;
变量详解
MySQL 中使用 DECLARE 来定义一局部变量，该变量只能在 BEGIN … END 复合语句中使用，并且应该定义在复合语句的开头，
即其它语句之前，语法如下：
DECLARE var_name[,...] type [DEFAULT value]
其中：
var_name 为变量名称，同 SQL 语句一样，变量名不区分大小写；type 为 MySQL 支持的任何数据类型；可以同时定义多个同类型的变量，用逗号隔开；变量初始值为 NULL，如果需要，可以使用 DEFAULT 子句提供默认值，值可以被指定为一个表达式。
对变量赋值采用 SET 语句，语法为：
SET var_name = expr [,var_name = expr] ...
DELIMITER $$
ALTER ALGORITHM=TEMPTABLE DEFINER=root@localhost SQL SECURITY DEFINER VIEW `ucr_supplier_reservation_type_view_group_upn` AS
SELECT
`u`.`Tlevel` AS `Tlevel`,
`u`.`typeID` AS `typeID`,
`u`.`Tname` AS `Tname`,
`u`.`T_reservationID` AS `T_reservationID`
FROM `urban_cloud_reservation`.`ucr_user_order_reservation_type` `u`
WHERE (3 > (SELECT
COUNT(0)
FROM `urban_cloud_reservation`.`ucr_user_order_reservation_type`
WHERE ((`urban_cloud_reservation`.`ucr_user_order_reservation_type`.`T_reservationID` = `u`.`T_reservationID`)
AND (`urban_cloud_reservation`.`ucr_user_order_reservation_type`.`typeID` = `u`.`typeID`)
AND (`urban_cloud_reservation`.`ucr_user_order_reservation_type`.`Tlevel` < `u`.`Tlevel`))))
ORDER BY `u`.`T_reservationID`,`u`.`typeID`,`u`.`Tlevel`$$
DELIMITER ;
—————————————————————————————————
- 八、事件
—————————————————————————————————
8.1 >>>开启事件调度器
1、set global event_scheduler=1;
2、set global event_scheduler=true;
3、在my.ini配置文件中加上event_schedule=1或set global event_scheduler=on;
查看是否开启：
show variables like 'event_scheduler';
select @@event_scheduler;
显示系统连接进程：
show processlist;
自定义结束符
delimiter $$
delimiter ;
—————————————————————————————————
8.2 >>>创建事件
delimiter $$
create event if not exists event_insert
on schedule
every 1 month
starts curdate()
ends '2018-6-3'
do
begin
if year(curdate()) > 2016 then
insert into tb_student_test
values('1601141019','dream','女','1999-8-12','周口沈丘','汉','3');
end if;
end $$
delimiter ;
—————————————————————————————————
8.3 >>>修改事件
临时关闭事件
alter event event_insert disable;
再次开启事件
alter event event_insert enable;
修改事件名称
alter event event_insert rename to e_insert;
—————————————————————————————————
8.4 >>>删除事件
drop event if exists event_insert;
—————————————————————————————————
- 九、存储过程与存储函数
—————————————————————————————————
10.1 >>>存储过程
—————————————————————————————————
10.1.1 >>>创建存储过程
delimiter @@
create procedure sp_update_sex(in sno char(20),in ssex char(20))
begin
update tb_student set sex=ssex where studentNo=sno;
end @@
delimiter ;
查看存储过程：
show procedure status;
show create procedure sp_update_sex;
—————————————————————————————————
10.1.2 >>>存储过程体
存储过程体的常用语法元素
1、局部变量
declare var_name varchar(50) default '拾忆';
2、set 语句
set var_name='简单点';
3、select...into语句
select studentName into var_name from where studentNo='1601141019';
4、流程控制语句
a.条件判断语句
b.循环语句
5、游标
delimiter @@
create procedure sp_sumofrow(out rows int)
begin
declare sno varchar(10);
declare found boolean default true;
declare cur cursor for
select studentNo from tb_student;
declare continue handler for not found
set found=false;
set rows=0;
open cur;
fetch cur into sno;
while found do
set rows=rows+1;
fetch cur into sno;
end while;
close cur;
end@@
delimiter ;
—————————————————————————————————
10.1.3 >>>调用存储过程
call sp_update_sex('1601141038','男');
call sp_sumofrow(@rows);
—————————————————————————————————
10.1.4 >>>删除存储过程
drop procedure sp_sumofrow;
—————————————————————————————————
10.2 >>>存储函数
—————————————————————————————————
10.2.1 >>>创建存储函数
delimiter @@
create function fn_search(sno varchar(10))
returns varchar(10)
deterministic
begin
declare ssex char(2);
select sex into ssex from tb_student where studentNo=sno;
if ssex is null then return(select '没有该学生！');
else if ssex='女' then return(select '女');
else return(select '男');
end if;
end if;
end @@
delimiter ;
—————————————————————————————————
>>>调用存储函数
select fn_search('1601141019');
—————————————————————————————————
>>>删除存储函数
drop function if exists fn_search;
—————————————————————————————————
- 十、访问控制与安全管理
—————————————————————————————————
10.1 >>>用户账号管理
—————————————————————————————————
10.1.1 >>>创建用户账号
create user 'dream'@'localhost' identified by '234',
'forever'@'localhost' identified by password '*565B1B47FD7BC0488435D2B707071F5EF873197B';
获取加密返回的散列值：
select password('234');
—————————————————————————————————
10.1.2 >>>删除用户
drop user forever@localhost;
—————————————————————————————————
10.1.3 >>>修改用户账号
rename user 'forever'@'localhost' to 'hope'@'localhost';
—————————————————————————————————
10.1.4 >>>修改用户口令
set password for 'hope'@'localhost'=password('234');
set password for 'hope'@'localhost'='*565B1B47FD7BC0488435D2B707071F5EF873197B';
—————————————————————————————————
10.2 >>>账户权限管理
—————————————————————————————————
10.2.1 >>>权限的授予
grant select(studentNo,studentName)
on db_school.tb_student
to 'dream'@'localhost';
grant select,update
on db_school.tb_student
to 'dream'@'localhost','forever'@'localhost';
grant all
on db_school.*
to 'dream'@'localhost';
grant create user
on *.*
to 'dream'@'localhost';
—————————————————————————————————
10.2.2 >>>权限的转移与限制
权限转移：
grant select,update
on db_school.tb_student
to 'hope'@'localhost' identified by '234'
with grant option;
权限限制：
grant delete
on db_school.tb_student
to 'hope'@'localhost'
with max_queries_per_hour 1;
—————————————————————————————————
10.2.3 >>>权限的撤销
revoke select
on db_school.tb_student
from 'dream'@'localhost';
revoke all privileges,grant option
from 'dream'@'localhost';
—————————————————————————————————
连接名=mysql_connect("主机","用户名","密码");
连接名=mysql_qconnect("主机","用户名","密码");
mysql_select_db("数据库名",连接名);
查询结果=mysql_query(SQL语句,连接名);
读取结果(数字索引方式与关联索引方式的数组)=mysql_fetch_array(查询结果);
读取结果(数字索引方式与关联索引方式的数组)=mysql_fetch_array(查询结果,MYSQL_BOTH);
读取结果(数字索引方式的数组)=mysql_fetch_row(查询结果);
读取结果(数字索引方式的数组)=mysql_fetch_array(查询结果,MYSQL_NUM);
读取结果(关联索引方式的数组)=mysql_fetch_assoc(查询结果);
读取结果(关联索引方式的数组)=mysql_fetch_array(查询结果,MYSQL_ASSOC);
读取结果指定行=mysql_data_seek(查询结果,行号);
结果行数=mysql_num_rows(查询结果);
mysql_close();
