数据库基本操作——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 连接条件;
连接查询——外连接：左外连接查询左表所有数据，以及两张表交集部分数据，右外连接查询右表所有数据，以及两张表交集部分数据
连接查询——自连接：当前表与自身的连接查询，自连接必须使用表别名
1097
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
