MySQL常用操作

MySQL常用操作

登录

mysql -uroot -p密码

DDL

查询

show databases; # 查询目前有哪几个数据库
show tables; # 查询当前数据库中有哪些表
desc tablename; # 查询某张表的结构 describe table

创建

create DATABASE 数据库名称;
create DATABASE if not exists 数据库名称; #创建之前先判断是否存在该数据库

create table 表名{
	字段名1 数据类型1,
	字段名2 数据类型2,
	...
	字段名n 数据类型n
};

删除

drop DATABASE 数据库名称;
drop DATABASE if exists 数据库名称; #若存在该数据库则删除

使用

select database(); #展示当前所处的数据库
use DATABASE; #使用某一数据库

修改

alter table 表名 rename to 新的表名;
alter table 表名 add 列名 数据类型; # 添加一列
alter table 表名 modify 列名 新数据类型; # 修改数据类型
alter table 表名 change 列名 新列名 新数据类型; # 同时修改列名和列数据类型
alter table 表名 drop 列名; # 删除表中某一列

常用数据类型

double(总长度, 小数点后保留位数)
varchar(n) # 变长字符串
char(n) # 定长字符串, 效率比变长高
date # 日期 年-月-日

DML

insert

insert into 表名(列名1, 列名2, ...) values (值1, 值2, ...); # 给指定列添加数据
insert into 表名 values (值1, 值2, 值3, ...); # 添加一行数据到表中
# 批量添加
insert into 表名(列名1, 列名2, ...) values (值1, 值2, ...), values (值1, 值2, ...), values (值1, 值2, ...)...;
insert into 表名 values (值1, 值2, 值3, ...), (值1, 值2, 值3, ...), (值1, 值2, 值3, ...)...;

update

update 表名 set 列名1 = 值1, 列名2 = 值2, ... [where ...];
# 若update中没有加where条件,则会将表中所有数据全部修改!!!

delete

delete from tablename [where 条件];

DQL

select
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段
having
	分组后条件
order by
	排序字段
limit
	分页限定
-- distinct 去除重复记录
-- as 可以为查询结果的表项重命名
select math as 数学成绩 from stu;
-- where 中 != 和 <> 都表示不等号, and(&&) or(||)表示 与和
-- 比较null值需要使用 is null 或者 is not null
-- 不能使用= 或 !=
-- where age in (11, 22, 33) 筛选在该集合中的age
-- like模糊查询
-- _匹配单个任意字符, %匹配任意字符
-- 排序 order by
select 字段列表
from 表名
order by
	排序字段名1 [排序方式1],
	排序字段名2 [排序方式2],
	...;
-- asc 升序排列(默认) ascend
-- desc 降序排列 descend
-- 分组查询 group by
-- 聚合函数 不统计null值
-- count(列名) 统计数量,  count(*) 或者 count(主键)
-- max(列名) 最大值
-- min(列名) 最小值
-- sum(列名) 求和
-- avg(列名) 平均值
select 聚合函数 from table;

select 字段列表 from 表名 [where 分组条件限定] group by 分组字段名 [having 分组后条件过滤]

where和having的区别

  • 执行时机不一样:where用于分组前筛选,having对分组结果筛选;
  • 可判断条件不一样,where不能对聚合函数进行判断,而having可以(having是对结果筛选的,知道聚合函数的结果,当然可以)
-- 分页查询 limit
select 字段列表 from 表名 limit 起始索引, 查询条目数;
-- 索引是从0开始的
-- 起始索引 = (当前页码 - 1) * 每页显示条目数;

约束

  • 非空约束(not null):保证列中所有数据不为空;
  • 唯一约束(unique):保证列中的所有数据各不相同;
  • 主键约束(primary key):主键是某一表项的唯一标识,要求非空且唯一;
  • 检查约束(check):保证某一属性的值满足某条件;
  • 默认约束(default):保存数据时,若未指定值则采用默认值;
  • 外键约束(foreign key):外键用来与其他表之间建立连接,用来保证数据的一致性和完整性;

注:MySQL不支持检查约束。

auto_increment 自增长

外键约束

image-20230105190223706

-- 创建表的时候添加外键约束
create table name(
	列名 数据类型,
    ...
    [constraint] [外键名称] foreign key(外键列名) references 主表(主表列名)
);

-- 建完表之后添加外键约束
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表名称(主表列名);

-- 删除外键约束
alter table 表名 drop foreign key 外键名称;

如图表emp中的外键dep_id是表department主键id的外键。

外键会降低数据库性能,具体实践中不直接设置外键。

关系数据库通过外键可以实现一对多、多对多和一对一的关系。
image

具体可参考外键 - 廖雪峰的官方网站 (liaoxuefeng.com)

表关系实现

  • 一对多实现方式:在多的一方建立外键,指向一的一方的主键。
  • 多对多实现方式:建立第三张中间表,表中至少包含两个外键,分别关联两方主键。

image

可以看到,同一个商品可以对应多个订单;同一个订单可以包含多个商品。

image

  • 一对一:多用于表的拆分,将常用信息属性与不常用信息属性分离,用于提升查询性能

    实现方式:在任意一方加入外键,关联另一方主键,并且设置外键为UNIQUE;

多表查询

笛卡尔积:取A、B集合所有组合情况

多表查询—连接查询

  • 内连接:相当于查询A B交集数据
  • 外连接
    • 左外连接:查询A表所有数据和交集数据
    • 右外连接:查询B表所有数据和交集数据
-- 内连接分为隐式、显式
-- 隐式
select t1.name, t1.gender t2.dname
from emp t1, dept t2
where t1.dep_id = t2.did;
-- 显式
select t1.name, t1.gender t2.dname
from emp t1 inner join dept t2 on t1.dep_id = t2.did; # inner可省略
-- 外连接
-- 左外连接
select 字段列表
from 表1 left [outer] join 表2
on 条件;
-- 右外连接
select 字段列表
from 表1 right [outer] join 表2
on 条件;

多表查询—子查询

查询中嵌套查询

select *
from emp 
where salary > (select salary from emp where name = '猪八戒');

-- 分类
# 单行单列
select 字段列表 from 表 where 字段名 = (子查询);
# 多行单列
select 字段列表 from 表 where 字段名 in (子查询);
# 多行多列
select 字段列表 from (子查询) where 条件;

事务

  • 事务(Transaction)是一种机制、一个操作序列,包含了一组数据库操作命令;
  • 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么同时成功,要么同时失败
  • 事务是一个不可分割的工作逻辑单元(类似操作系统的原语)。
-- 在mysql中建立如下表
CREATE TABLE account(
	id int PRIMARY KEY auto_increment,
	name VARCHAR(10),
	money double(10,2)
);
insert into account(name, money) values('刘伟', 1000), ('微凉', 1000);
select * from account;
begin;
-- 1.查询刘伟的余额
-- 2.刘伟金额减500
update account set money = money - 500 where name = '刘伟';
在这里出现了异常... # 出现异常后最后回滚,数据库会回到事务执行前的状态
-- 3. 微凉金额加500
update account set money = money + 500 where name = '微凉';

rollback;

-- 若异常已经解决, 则用commit提交事务, 使得数据库中数据永久性更改

事务四大特征

ACID

  • 原子性(atomicity):事务是不可分割的最小操作单位,要么同时成功,要么同时失败
  • 一致性(consistency):事务完成时,必须使所有的数据都保持一只状态
  • 隔离性(isolation):多个事物之间,操作的可见性(隔离性越强,操作越不可见,性能越低)
  • 持久性(durability):事务一旦提交或者回滚,它对数据库中的数据的改变就是永久的
-- 查询事务的默认提交方式
-- mysql中默认自动提交, oracle手动提交
select @@autocommit # 结果为1说明是默认自动提交,即执行一条修改语句后自动提交, 相当于执行修改语句后系统自动为你执行了一条commit
-- 修改默认提交方式为手动提交
set @@autocommit = 0; # 这样的话你执行一条修改语句后不会立刻更新数据库中的数据, 只有你commit之后才会更新数据库
posted @ 2023-01-07 16:49  Dre_am_tale  阅读(25)  评论(0编辑  收藏  举报