数据库基本操作——MySQL为例
MySQL基础知识
MySQL基本配置
1、终端进入mysql
mysql -u root -p 密码
2、终端查看数据库
show databases;
3、终端创建数据库
create database 库名;
4、Navicat进入mysql
一、库操作
SQL语句不区分大小写
1、创建数据库
create database 库名;
2、指定使用数据库
use 库名;
3、查看当前使用的数据库
select database();
4、查看当前数据库所有表
select tables;
5、删除数据库
drop database 库名;
二、表操作
1、mysql数据类型
数值型、字符串型、日期时间型
a、数值型
常用数值型类型:
整数型:(常用)INT/INTEGER(4字节,默认有符号)
非常用:tinyint、smallint、mediumint、bigint
--有符号
age int
--无符号
age int unsigned
小数型:(常用)float(4字节,精度7位)
非常用:double(精度15位)、decimal(精度28位)
salary float(总位数,小数位)
--如果超过总数位会报错
b、字符型
字符串型
char:定长字符串(内存空间固定)
varchar:变长字符串(内存空间可变)
char(20) --内存空间固定为20字节
varchar(20) --内存空间根据存入字符串确定
文本字符串
text:(常用)用于长文本数据储存,无法设置长度,占据空间固定(65535字节)
不常用:tinytext(255字节)、mediumtext(16777215字节)、longtext(4294967295字节)
c、日期时间型
date:(3字节)存储日期值、YYYY-MM-DD
datetime:(8字节)存储混合日期和时间值、YYYY-MM-DD hh:mm:ss
timestep: (4字节)时间戳
d、枚举类型
限制存入内容,只能是固定值选一个
设置枚举内容是尽量设置英文,不要使用汉字,可能会有报错几率
gender enmu("man","female");
中文无法识别可能是数据库没有设置为utf8格式,右键数据库,点击编辑数据库,将字符集格式改为utf8即可
2、数据表创建
1、建表规则
创建数据表
create table 表名(
变量名 变量类型,
变量名 变量类型
);
查看数据表
desc 表名;
删除表,加入if exists判断是否存在表
drop table if exists 表名;
插入数据:insert
全字段插入(插入值必须与表字段顺序完全一致)
insert into 表名 values(对应变量的值,以","隔开);
部分字段插入(插入值必须与指定字段的顺序完全一致)
insert into 表名(变量名1,变量名2,...) values(值1,值2,...);
多行数据插入
全字段数据插入
insert into 表名
values(对应变量的值,以","隔开),
(对应变量的值,以","隔开),
(对应变量的值,以","隔开);
部分字段数据插入
insert into 表名(变量名1,变量名2,...)
values(值1,值2,...),
(值1,值2,...),
(值1,值2,...);
修改数据:update
修改数据可以省略条件,但是不添加条件则会修改整张表的值
update 表名 set 字段名1=值1,字段名2=值2,...[where 条件];
删除数据:delete
条件可以省略,省略条件则是删除整张表的数据;delete语句不能删除某个字段的值,可以用update进行删除
delete from 表名 [where 条件];
and条件编写要按表的字段顺序进行编写
查询数据:select
a、基本查询:
1、查询多个字段
select 字段1,字段2,字段3...from 表名;
select * from 表名;
2、设置别名
select 字段1 as 别名1,字段2 as 别名2...from 表名;
3、去除重复记录
select distinct 字段列表 from 表名;
b、条件查询:where
select 字段名 from 表名 where 条件列表;
逻辑运算符,and(&&),or(||),not(!)
条件运算符:
between…and… 在某个范围之内(含最小、最大值)
in(…) 在in之后的列表中的值,多选一
like 占位符 模糊匹配(占位符是_匹配单个字符,占位符是%匹配任意个字符)
is null 判断是否为空
c、聚合函数:将一列作为一个整体,进行纵向计算
select 聚合函数(字段列表) from 表名;
--常见聚合函数
count:统计数量
max:最大值
min:最小值
avg:平均值
sum:求和
d、分组查询:group by, 常与聚合函数配合使用,where>聚合函数>having
where是分组之前进行过滤,优先级高于having;where不能对聚合函数进行判断,但是having可以;
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
分组之后,查询的字段是聚合字段和分组字段,查询其他字段无任何意义。
e、排序查询:order by
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
--排序方式:
asc:升序(默认值)
desc:降序
f、分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数;
--起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数
--分页查询是数据库的方言,不同的数据库有不同的实现,mysql中是limit
--如果查询的是第一页数据,起始索引可以省略,直接简写为limit 10
管理用户
主机名可以使用%通配
a、查询用户
use mysql;
select * from user;
b、创建用户
create user "用户名"@"主机名" identified by "密码";
c、修改用户密码
alter user "用户名"@"主机名" identified with mysql_native_password by "新密码";
d、删除用户
drop user "用户名"@"主机名";
权限控制
多个权限之间,使用逗号分隔;授权时,数据库名和表名可以使用*进行通配,代表所有。
--权限列表
all,all privileges --所有权限
select --查询数据
insert --插入数据
update --修改数据
delete --删除数据
alter --修改表
drop --删除数据库/表/视图
create --创建数据库/表
a、查询权限
show grants for "用户名"@"主机名";
b、授予权限
grant 权限列表 on 数据库名.表名 to "用户名"@"主机名";
c、撤销权限
revoke 权限列表 on 数据库名.表名 from "用户名"@"主机名";
2、数据表约束规则
非空限制,not null,如果限制非空,如果没有赋值则无法增加数据
not null
create table person(
personid int not null,
personname varchar(9) not null,
sex enum("男","女")
);
设置默认值,default,如果数据为空字段,则赋默认值进行填补
在enum中只能指定enum中包含的值
create table person(
personid int not null,
personname varchar(9) not null,
sex enum("男","女") default "男"
);
唯一性,unique
create table person(
personId int unsigned unique
);
自动增长,auto_increament,用于主键
create table person(
personId int unsigned auto_increment
);
3、主键与外键
设置主键,primary key,主键非空且唯一
主键字段,插入数据,可以使用null占位,也可直接忽略字段,在使用null占位时要确保主键设置为自增
--方法一
create table person(
personId int unsigned primary key auto_increment
);
--方法二
create table person(
personId int unsigned auto_increment,
primary key(personId)
);
外键设置:foreign key(指定变量名)reference 链接表(指定变量名)
--添加外键
--方法一:
create table 表名(
字段名 数据类型,...
[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名)
);
--方法二:
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);
--删除外键
alter table 表名 drop foreign key 外键名称;
外键删除/更新行为
alter table 表名 add constraint 外键名称 foreign key (外键字段) references 主表名(主表字段名) on update cascade on delete cascade;
--行为
no action/restrict --父表删除/更新记录时,检查是否存在外键,如果有则不允许删除/更新
cascade --父表删除/更新记录时,检查是否存在外键,如果有,则也删除更新外键子表记录
set null --父表删除/更新记录时,检查是否存在外键,如果与,子表外键值设置为null
set default --父表有变更时,子表将外键列设置成一个默认的值
3、函数
a、字符串函数
concat(s1,s2,...,sn) --字符串拼接
lower(str) --字符串全部转为小写
upper(str) --字符串全部转为大写
lpad(str,n,pad) --左填充,用pad进行字符串填充
rpad(str,n,pad) --右填充,用pad进行字符串填充
trim(str) --去掉字符串头部和尾部的空格
substring(str,start,len) --返回字符串str从start位置起的len个长度的字符串
b、数值函数
ceil(x) --向上取整
floor(x) --向下取整
mod(x,y) --返回x/y的模,即求余运算
rand() --返回0~1内的随机数
round(x) --求参数x的四舍五入的值,保留y位小数
c、日期函数
curdate() --返回当前日期
curtime() --返回当前时间
now() --返回当前日期和时间
year(date) --获取指定date的年份
month(date) --获取指定date的月份
day(date) --获取指定date的日期
date_add(date,interval expr type) --返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,date2) --返回起始时间date1和结束时间date2之间的天数
d、流程函数
if(value,t,f) --如果value为true,则返回t,否则返回f;
ifnull(value1,value2) --如果value1不为空,返回value1,否则返回value2;
case when [val1] then [res1] ... else [default] end --如果val1为true,返回res1,...否则返回default默认值
case [expr] when [val1] then [res1] ... else [default] end --如果expr的值等于val1,返回res1,...否则返回default默认值
4、约束
约束是作用于表中字段上的规则,用于限制储存在表中的数据
not null --非空约束
unique --唯一约束
primary key --主键约束
default --默认约束
check --检查约束
foreign key --外键约束
5、多表查询
a、多表分析
1、一对一 :用于单表拆分,将一张表的基础字段放在一张表中,其他详情字段放在另一张表中
实现:在任意一方加入外键,关联另外一方的主键,并且设置外键为唯一的(unique)
2、一对多
在多的一方建立外键,指向一的一方的主键
3、多对多
建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
b、多表查询
从多张表中查询数据
在多表查询时,需要消除无效的笛卡尔积
--笛卡尔积消除
select * from emp,dept where emp.dept_id=dept.id;
连接查询——内连接:查询A,B交集部分数据
--隐式内连接
select 字段列表 from 表1,表2 where 条件;
--显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件;
连接查询——外连接:左外连接查询左表所有数据,以及两张表交集部分数据,右外连接查询右表所有数据,以及两张表交集部分数据
连接查询——自连接:当前表与自身的连接查询,自连接必须使用表别名