之前已经发布一篇《mysql练习题答案》的博客,想接着这篇博客再写一篇真正的总结。
其实mysql数据库结构很简单,从大往小来说:库–>表–>字段–>数据。对mysql数据库的操作也主要在这4方面。当然除了4方面外还有授权操作,数据库的维护,数据库服务器的启动等方面。
哦!对了还有件事要达成共识,一下文章中会出现一些表,为了方便理解下面把表的实例展示出来:
- 库操作
创建
create database BASE_NAME [charset utf8]; # 创建BASE_NAME 的数据库
删
drop database BASE_NAME; # 删除数据库
改
alter database BASE_NAME charset NEW_CODE; # 修改数据库字符编码格式
查
show databases;
select database();
- 表操作
创建
create table TABLE_NAME(
字段1 int(3) [约束条件],
字段2 char(5) [约束条件],
字段3 int(3) [约束条件],
...
)
# 1. 在同一张表中,字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的
插入数据
1.插入全部字段值
insert into TABLE_NAME values(
(字段1值,字段2值,字段3值),
(字段1值,字段2值,字段3值),
(字段1值,字段2值,字段3值)
);
2.插入指定字段值
insert into TABLE_NAME(字段2,字段3) values(
(字段2值,字段3值),
(字段2值,字段3值),
(字段2值,字段3值)
);
修改结构
alter table TABLE_NAME rename NEW_TABLE_NAME;
#修改表明
alter table TABLE_NAME add 字段名 数据类型 约束条件 [ frist \ after 字段名 ];
# 添加字段
alter table TABLE_NAME drop 字段名;
# 删除字段
alter table TABLE_NAME change 旧字段名 新字段名 新约束条件;
# 修改字段(当重命名字段时,新约束条件应与旧约束条件相同)
#添加外键
alter table TABLE_NAME add constraint foreign key 字段 references 表名(字段)
删除
drop table TABLE_NAME;
- 数据类型
数值
int
范围(无符号):0~4294967295 # 40亿 最大int (10)
tinyint
范围(无符号):0~256 #最大tinyint(3)
bigint
范围(无符号):0~18446744073709551615 # 懒得数了
SQL 中直接使用int或其他数值类型默认为有符号,则范围减半,正负值都有。例如tinyint()–>范围:-128~127。tinyint unsigned–>无符号。
当然了,在实际应用中数值主要记录ID、等级、年龄这些,用int足够了。
float
float(m,d)
单精度浮点数(非准确小数值),m是数字总个数,d是小数点后个数。m最大值为255,d最大值为30.
字符串
char和varchar:
char类型:定长,简单粗暴,浪费空间,存取速度快
varchar类型:变长,精准,节省空间,存取速度慢
枚举类型 和 集合类型
枚举类型:enum(‘A’,‘B’,‘C’…)–>只能从ABC中选某 1 个。
集合类型: set(‘a’,‘b’,‘c’…) -->可以从abc中选择多个,或不选。
- 完整性约束
非空
not null [ defaul ‘XXX’ ]
create table student(
name varchar(20) not null,
age int(3) unsigned not null default 18
);
# name字段不能为空,
age字段无符号(0~255)非空,如果没有设置则为默认18
唯一
unique
方法一:
create table department1(
id int,
***name varchar(20) unique,***
comment varchar(100)
);
方法二:
create table department2(
id int,
name varchar(20),
comment varchar(100),
***unique(name)***
);
# name字段唯一,不能有重复。
在实际应用中唯一约束,可能不是对单一字段,例如学生和课程、IP和端口等。两者分别不唯一,但组合唯一,约束条件可以这样写
create table server(
host char(20) not null,
port int(4) not null
unique(host,port)
)
主键
primary key
要求字段的值不为空且唯一(not null unique()),可以单列主键也可以复合主键,但一个表内只能有一个主键primary key。
方法一:
create table department2(
id int primary key, #主键
name varchar(20),
comment varchar(100)
);
方法二:
create table department3(
id int,
name varchar(20),
comment varchar(100),
primary key(id) # 主键
复合主键:
create table service(
ip varchar(15),
port char(5),
primary key(ip,port)
);
自增长
auto_increment
约束字段为自动增长,被约束的字段必须同时被primary key约束。
create table student(
id int primary key auto_increment
) #被primary key 约束的自增长。
在删除数据时使用delete删除自增长字段并不能清除自增长的count,应使用truncate进行删除。
外键
foreign key
表类型必须是innodb存储引擎,且被关联的字段,即references指定的另外一个表的字段,必须保证唯一
#假设现在已经有这样一张表:
class_grade | |
---|---|
cid | grade |
1 | 一年级 |
2 | 二年级 |
3 | 三年级 |
现在需要创建这样一张表:
class | |
---|---|
cid | grade_id |
1 | 1 |
2 | 1 |
3 | 2 |
创建class表结构代码
create table class(
cid int not null primary key,
grade_id int not null,
froeign key grade_id references class_grade(cid) # 创建外键
on update cascade # 开启更新同步
on delete cascade # 开启删除同步
)
- 数据的操作
插入数据
insert into TABLE_NAME values(
(字段1,字段2,字段3...),
(字段1,字段2,字段3...),
......
);
删
delete from TABLE_NAME where 字段名 = 条件;
改
update from TABLE_NAME set
字段1 = 字段1值,
字段2 = 字段2值,
........
where 条件;
查
1.单表查询
where
对表进行初步筛选
1.比较运算符:><>= <= <> !=
例如:查找学号小于10 的学生
select * from student where sid<10;
2.between 80 and 100 值在80到100之间
例如:查找成绩在80到90之间学生的学号
select * from score where score between 80 and 90;
3.in(80,90,100) 值是10或20或30
例如:查找课程ID是2,3,5的课程名
select * from course where course_id in(2,3,5); # in 的作用类似‘或’
4.like pattern
pattern可以是%或_,
%表示任意多字符
_表示一个字符
例如:1.查找姓'李'同学的学号
select sid from student where sname like '李%';
2.查找叫‘李【某】’同学的班级
select class_id from student where sname like '李_';
5.逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not
例如:1.查找班级1,姓‘李’的同学
select * from student where class_id = 1 and sname like '李%';
2.查找**不是**一年级一班的同学
select * from student where class_id not 1
group by
将所有记录按照某个相同字段进行归类。一般来说提到‘每’基本上跟分组有关,并且‘每’后面的字就是分组依据。
group by之后的语句可以使用聚合函数,聚合函数我们后面会提到
例如:查找每门课程选课的人数
select count(student_id) from score group by course_id;
# count(字段)就是聚合函数,按照分好组的字段进行统计
提示:以PRIMARY KEY 或UNIQUE 进行分组无意义,分区的前提是字段中有重复值
having
having与where用法相同,也是筛选用。不同点再与它可以使用聚合函数。
例如:查找报名人数高于40的课程
select * ,count(student_id) from score group by course_id having count(student_id)>50;
#按照课程ID分组,然后分别统计每门课的学生人数
order by
排序,asc–>升序;desc–>降序
例如:1.按照学号升序显示学生信息
select * from studnet order by sid asc;
2.按照课程升序,成绩降序显示同学信息
select * from score order by course_id asc, score desc;
limit
限制打印/输出行数
例如:查找课程1 成绩最高的学生ID
select * from score where course_id =1 order by score desc limit 1;
#先筛选课程1,按照成绩降序排列,最后只显示第一行(成绩最高)
执行顺序(切记)
from–>where–>group by–>having–>select–>distinct–>order by–>limit
2.多表查询
多表连接查询
内连接
语法:
select * from TABLE_NAME_1
inner join
TABLE_NAME_2
on
TABLE_NAME_1.字段 = TABLE_NAME_2.字段;
例如:
查找每门课程的老师姓名
select * from course
inner join
teacher
on course.teacher_id = teacher.tid;
外连接-----左连接、右连接
语法:
select * from TABLE_NAME_1
left join \ right join
TABLE_NAME_2
on
TABLE_NAME_1.字段 = TABLE_NAME_2.字段;
# left join 优先显示左表全部记录,如右表没数据,则为NULL
right join 优先显示右边全部记录,如左表没数据,则为NULL
全外连接
语法:
select * from TABLE_NAME_1
union \ union all
TABLE_NAME_2
on
TABLE_NAME_1.字段 = TABLE_NAME_2.字段;
# 1.在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
2.union与union all的区别:union会去掉相同的纪录
复合条件多表连接查询
在多表连接查询的基础上进行条件筛选等操作
select * from TABLE_1
inner join
TABLE_2
on TABLE1.字段 = TABLE_2.字段
where 条件
group by 字段
having 条件
order by 字段
limit num;
子查询
#1:子查询是将一个查询语句嵌套在另一个查询语句中。
#2:内层查询语句的查询结果,可以为外层查询语句提供查询条件。
#3:子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
#4:还可以包含比较运算符:= 、 !=、> 、<等
语法:
select * from TABLE_1
inner join
(
select * from Table_A where 条件
) as TABLE_2
on
TABLE_1.字段 = TABLE_2.字段;
# '()'内的查询语句是一个查询结果
利用这个结果与其他表进行连接或其他操作
-
总结单表查询和连接查询
单表查询顺序
from–>where–>group by–>having–>select–>distinct–>order by -->limit
单标查询主要使用在一张表字段包含所有查找内容。如student表中查找所有女性ID。因student表中有gender字段也有ID字段,根据gender字段能够筛选出所有女性,但是结果需要SID,只需在select语句中选择需要输出的字段名即’select sid’便可。
在实际应用中单标查询一般都是作为子查询的一部分,利用单标查询先找到ID或其他 唯一键 与其他表进行连表操作最终找到答案。连表查询
连表操作在应用中比较常见,在实际应用中连表的 内连接 更为常见,内连接机制是先生成笛卡尔积,通过‘on’语句筛选复合条件的值输出。使用连表操作前需要明确两个(多个)表之间相对关系(表结构)。可能一次连表操作并不能得到理想的结果,这需要多次连表,将连表操作的结果作为子查询的一张表,再与另外一张表相连,几次处理后方可获取理想答案。
在练习中我使用的也是这种方式,先确定结果与那几张表相关,再考虑几张表之间如何连接。多数情况外键是连表操作的突破口,找到表之间的关系,通过表中外键字段可连接两张表,这就让操作更加快捷。
例如:有class表、class_grade表。想找到class_id 与grade关系,class有class_id字段,但没有grade,这样就需要连表,通过grade_id字段将两个表相连,获得目标字段。
再例如:查找‘NATA’(某学生)在哪个年级。首先确定跟三张表student(sname)、class(grade_id) 、class_garde(gname)在实际查找中先在student中查找’NATA’,获取他的班级号class_id,通过与class(cid)内连接找到grade_id,将找到的结果作为一张表,内连接class_grade(gid)找到最终结果。
1.
select class_id from student where sname = 'NATA'; # 在student中找到NATA,获取class_id T1
2.
select grade_id from class
inner join
(
select class_id from student where sname = 'NATA'
) as T1
on class.cid = T1.class_id; # 内连接class获取grade_id 通过class.cid = T1.class_id连接 T2
3.
select gname from class_grade
inner join
(
select grade_id from class
inner join
(
select class_id from student where sname = 'NATA'
) as T1
on class.cid = T1.class_id;
) as T2
on class_grade.gid = T2.grade_id # 内连接class_grade,获取gname 通过class_grade.gid = T2.grade_id连接
好啦,写到这里mysql库、表、字段、值得操作和使用基本介绍差不多了,重点在于使用。另外对于权限管理mark一下授权语句:
# 以root身份进入mysql
1.
grant all priviliges on *.* to 'USER_NAME'@'LOCALHOST \IP' indentifice by 'YOUR_PASSWORD';
# 给USER_NAME.LOCALHOST \IP授权,密码是'YOUR_PASSWORD'
2.
flush priviliges; # 切记刷新!!!
在创建用户后,使用用户登录mysql发现只能显示test库和另外一个库,那么很幸运,我找到了这篇文章。
http://www.cnblogs.com/jukan/p/5773232.html
可以拜读一下大神…毕竟现在的我还很稚嫩。
OK,这就是这篇的全部内容,谢谢各位看到这行,希望与您能一起共同进步。
诚谢!