PeytonWu's Blog
0%

SQL

发表于 更新于 分类于

DDL

库级别

查看所有数据库: show databases;
创建数据库: create database if not exists 数据库名;
删除数据库: drop database if exists 数据库名;
查看当前使用的数据库: select database();
选择数据库:use 数据库名;

表级别

查询当前数据库下(use 选定数据库)
查看所有表:show tables;
查看某一特定表结构信息:desc table1 table1 为表名,desc 即description含义。
创建表:

1
2
3
4
5
6
create table 表名(字段名1 数据类型1,字段名2 数据类型2); 

eg: create table tb_use(
id int,username varchar(20),password varchar(32)
);

char(0~255 bytes),当username 如果小于20字符，仍然占20个字符，当大于20个字符时，则报错。
因为常见固定长度，所以存储性能高，但浪费空间。

varchar(0~65535 bytes),当username 如果小于20字符，只占实际字符.大于也同样报错。性能差点，但节约空间。

删除表:drop table if exists 表名;

操作表中数据

改表名：alter table 表名 rename to 新表名;
新增列:alter table 表名 add 列名 数据类型(字段名约束);
新增多列:ALTER table 表名 add CLOUMN 数据类型(字段名约束) AFTER 想放的字段名后面如果需要;

1
2
3
4
5
-- int(11)只影响int的显示位数(跟数据库客户端有关)，不影响它所占空间大小和所能存储的数值范围。且11代表的不是2进制下几位，而是10进制下几位
ALTER TABLE tb_use
ADD COLUMN `version` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '乐观锁',
ADD COLUMN `deleted` int UNSIGNED NOT NULL DEFAULT 0 COMMENT '软删除' AFTER `version`;

修改数据类型:alter table 表名 modify 列名 新数据类型;
修改列名和数据类型:alter table 表名 change 列名 新列名 新数据类型;
删除列:alter table 表名 drop 列名;

// TODO Navicat or DBeaver

DML

增

给指定列添加数据: insert into 表名(列名1，列名2,...) values(值1，值2...);
给全部列添加数据: insert into 表名 values(列名1,列名2,...);
批量添加数据:

1
2
insert into 表名(列名1，列名2,...) values(值1，值2...),(值1，值2...),(值1，值2...)...;
insert into 表名 values(值1，值2...),(值1，值2...),(值1，值2...)...;

删

删除: delete from 表名 [where 条件];

改

修改表数据: update 表名 set 列名1=值1,列名2=值2,...[where 条件];

约束

非空约束:NOT NULL
唯一约束:UNIQUE
主键约束(自带非空且唯一):PRIMARY KEY
默认约束:DEFAULT(不能传值，才为默认值，即使传null也会为null)
外键约束:FOREIGN KEY
检查约束:CHECK(Mysql不支持)
//当列算是数字类型并且唯一约束 可以添加auto_increment，即使传null也会自动自增

1
2
3
4
5
-- 删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
-- 将某一列设为主键。也可同时用于增加或删除 auto_increment约束
ALTER TABLE 表名 MODIFY 列名 列数据类型 PRIMARY KEY;

外键约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建表时添加外键约束
create table 表名(列名 数据类型
...
CONSTRAINT 外键名称 FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);
 
-- 建表万能后添加外键约束

ALTER TABLE 表名 ADD 外键名称 FOREIGN KEY(外键列名) REFERENCES 主表(主表列名);

-- 删除外键
 
ALTER TABLE 表名 DROP  FOREIGN KEY 外键名称;

外键永远放于多的那边关联另一方

一对一:用于拆分表，经常用的放一张，不经常的放另外一张。其实就是简化版的一对多(多对一)，
只需要将外键列多加个UNIQUE约束
多对多:借助第3张中间表，中间2列(或多列)外键指向2张或多张主表主键，中间表还可以添加一些额外字段.

多表查询

内连(交集)

1
2
3
4
-- 隐式内连
SELECT 字段列表 FROM 表1,表2[,表3] ... WHERE 条件 [and 另外条件];
-- 显式内连
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件 [[INNER] JOIN 表3 ON 另外条件];

外连

1
2
3
4
5
6
-- 左外连(交集+左表)
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连(交集+右表)
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
一般情况下我们都用左连，因为左连=右连+表1 表2位置换下

子查询

单行单列:
SELECT 字段列表 FROM 表 WHERE 字段名 = (子查询)
多行查询:
SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询)
多行多列:
SELECT 字段列表 FROM (子查询) WHERE 条件

事务

1
2
3
4
5
6
7
8
-- 开启事务
BEGIN;/START TRANSACTION;
...SQL...
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

事务四大特定

  • 原子性(Atomicity):不可分析最小操作单位,要么同时成功，要么同时失败
  • 一致性(Consistency):事务完成时，必须时所有的数据保持一致状态
  • 隔离性(Isolation):多个事务之间，操作可见性
  • 持久性(Durability)：事务一旦成功或回滚，它对数据库中的数据的改变就是永久的

查询事务的默认提交方式:
select @@autocommit; -- 1则是默认自动提交
修改默认提交方式:
set @@autocommit = 0;