Mysql基础知识

MySQL相关命令

登录mysql客户端命令:  mysql –u root –p 123 –h localhost

     -u:  root是用户名

     -p: 密码

     -h  服务器主机名,可以省略

退出客户端:  quit或者 exit

 

mysql常见概念:

数据库服务器: 安装MySQL软件的机器

数据库: 数据库表的集合,数据库服务器可以创建多个数据库

表: 类似于excel表格,用于存储数据

表记录: 表中的一行数据

 

SQL语句

sql(Structured Query Language): 指结构化查询语言

     SQL是操作关系型数据库的通用语言

     SQL是非过程性语言

     SQL是一个标准,各大数据库厂商根据该标准提供了实现

SQL基本类型

类型	含义
char(n)	存放固定长度的字符串,用户指定长度为n
varchar	可变长度字符串,用户指定最大长度n,char的改进版
int	整数类型
smallint	小整数类型
numeric(p,d)	定点数,精度由用户指定(p是小数点左侧,d是小数点右侧)
read,double,precision	浮点数和双精度浮点数
float(n)	精度至少n位浮点数

SQL日期类型

类型	格式	用途
DATE	YYYY-MM-DD	日期值
TIME	HH:MM:SS	时间值或者持续时间
YEAR	YYYY	年份值
DATETIME	YYYY-MM-DD HH:MM:SS	混合日期
TIMESTAMP	YYYYMMDDHHMMSS	混合日期/时间戳

特殊关键字

类型	含义	实例
primary	主键,后面括号中是作为主键的属性	primary key(student_id)
foreign key reference	外键 reference后为外键的表	foreign key(course_id) references Course
not null	不为空,前面属性的定义	name varchar(10) not null

SQL插入删除修改语句(增,删,改)

类型	含义
create table	创建一张表
insert into ….  values	向表中插入一条信息
delete from	从表中删除一条信息
update…set…where	将满足where条件的数据更新内容为set的值
drop table	删除表
alter table….add	向表中添加某个属性
alter table…..drop	将表中的某个属性删除

语句示例

create table

创建表student,表中有四个属性,ID,name,age,class主键为ID

insert into…values

向表中添加一行信息,age允许为空,所以可以不添加

delete from

删除name=tom的一行数据

update…set….where

将所有name=tom的信息中age设置为18

drop table

删除student表

alter table….add

向表中添加列sex,

alter table…drop

从表中将class类删除

 

SQL查询语句

单关系查询关键字

类型	含义
select	表示要查处的表含有的属性
from	表示要操作的表
where	判断条件,根据该判断条件选择信息
distinct	在select加入关键字distinct表示结果去重
all	在select加入关键字all表示不去重(默认)
and	在where中使用and表示将判断条件连接起来
or	在where中使用or表示判断条件多选一
not	在where使用not 表示条件取反

示例

表示将age=18并且class=”A”的学生的名字,并且将结果去重

 

多关系查询(多表查询)

类型	含义
A,B	在from后面通过逗号连接多张表,并将这些表进行笛卡尔积运算
natural join	将natural join关键字前后的两张表进行自然连接运算
A join B using(c)	将A和B通过C属性自然连接

示例

将student表和math表自然连接,所得到的的结果查询出不是B班的男生的成绩

附加运算查询

类型	含义
as	将as前的关系起一个别名,可以用别名来代指这个表
*	select 中通过表明.*表示查找这个表中的所有属性
order by	让查询结果中的信息按照给定的属性排序
desc	在order by之后的属性后使用,采用降序排序
asc	在order by之后的属性后使用,采用降序排序
between	在where中使用between表示一个数在连个数之间的取值
not between	between的反义词,在两个数之外取值
union/union all	将两个sql语句做并运算,并自动去重,添加all表示不去重
intersect/intersect all	将两个SQL语句做交运算,并自动去重,添加all表示不去重
except/except all	将两个sql做差运算,并自动去重,添加all表示不去重
is null	在where中使用is null表示这个值是空值
is not null	在where中使用is not null 表示这个值不是空值
limit(page,rows)	分页查询,page=第几页,rows=每页数据量
like	模糊查询, % : 匹配0个或者多个字符 _ : 匹配一个字符

示例:

将student和math表中id相同的字段做笛卡尔积,并且取出结果中属于student部分作为结果

将student和math表做自然连接.找出class=”A”并且sex=”women”的人的名字并且把结果按照score字段降序排列

在math表中找出score字段值在60-90之间的name,并且让按照score升序排列

将上面两个查询结果做并集,而且去重

在sql表中记录的年龄未填写的所有人的姓名查出来

 

 

聚合函数

类型	含义
avg	平均值
min	最小值
max	最大值
sum	总和
count	计数
distinct	将distinct后的属性去重
group by	将group by上取值相同的信息分在同一组
having	对group by 产生的分组进行筛选,可以使用聚集函数

在Student与Math表自然连接的结果中按照班级分组，并且去除那些班级的平均成绩没到60的班级，剩下的班级和该班成绩的平均数（该班成绩的平均数这个属性被重命名为avg_score）作为一张新表被输出出来。

where和having的区别:

where和having都可以进行数据过滤,但是where语句是在对数据分组之前进行过滤,不能使用聚合函数和列别名.having子句是对分组之后的数据进行过滤,可以使用聚合函数和别名.where子句一般可以用having替换,但是使用having的地方不能使用where替换

字段的约束

主键约束	primary	保证所约束的列的值唯一且不能为空
唯一约束	unique	保证所约束的列的值唯一且不能重复
非空约束	not null	保证所约束的列的值不能为空
外键约束	foreign key	用来通知数据库两张表字段之间的对应关系,确保数据库完整性和一致性和

添加主键约束

alter table tabName add primary key(colName)

设置主键自动增长:

colName datatype primary key auto_increment

添加外键约束(建表时)

foreign key(colName) references tab(colName)

 

数据库的备份与恢复

备份数据库,在cmd窗口中

备份命令: mysqldump –u用户名 –p 数据库名 > 数据文件位置

例如: mysqldump –uroot –p mydb1 > e:/1.sql

恢复数据到数据库:

方式一: 在cmd中

     命令: mysql –u用户名 –p 数据库名 < 数据文件位置

     例如: mysql –uroot –p mydb2 < e:/1.sql

方式二: 在mysql客户端中

     命令: SOURCE 数据文件位置

     例如: source e:/1.sql

 

多表设计:

1. 一对一

在任意一方添加列保存另一方的主键作为外键,来保存两张表之间的关系

1. 一对多

在多个一方添加列保存一的一方的主键作为外键,保存两张表之间的关系

1. 多对多

在第一张第三方表中分别保存两张表的主键作为外键来保存两张表之间的关系,可以把多对多的关系拆分成两个一对多的关系来理解

 

笛卡尔积查询

     两张表相乘得出的结果,如果左边表有m条记录,右边有n条记录,则查询的结果就是m*n条数据,这些查询结果中包含了大量错误的结果,通常不会使用这种查询

 

内连接查询:

     查询出左边表(dept)有且右边表中也有 的记录

     select * from inner join dept on dept.id=dept_id

左外连接查询:

     在内连接的基础上,加上右边表有而左边表没有的数据

     select * from dept right join emp on dept.id=dept_id

右外连接查询: 在内连接查询基础上,加上左边表有而右边表没有的数据

     select * from dept left join emp on dept.id=dept_id

全外连接查询: 在内连接的基础上,加上左边表有而右边表没有的记录和右边表没有而左边表有的记录

     select * from dept full join emp on dept.id=dept_id

使用union模拟全外连接查询

     select * from dept right join emp on dept.id=dept_id

     union

     select * from dept left join emp on dept.id=dept_id