SQL基础操作指南：数据库、数据表、查询与事务处理-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_51986979/article/details/134407060

数据库定义语言

数据库操作

# 创建数据库
create database 数据库名
# 设定编码格式
create database if not exists 数据库名 default charset utf8 COLLATE utf8_general_ci;
# 删除数据库
drop database 数据库名

数据表操作

# 建表语句
create table 表名(列名 数据类型[列级约束条件],
                 列名 数据类型[列级约束条件],
                 ...
            [,表级约束条件])
# 例子
create table tb_test(
	id int not null auto_increment,
	name varchar(50),
	sex varchar(10),
	create_time time,
	primary key(id)
);

create table tb_stu (
  stu_id int not null auto_increment,
  nickname varchar(255),
  test_id int,
  primary key (stu_id),
  foreign key (test_id) references tb_test(id)
);

# 修改表 (默认restrict，表示如果此列作为其他表的约束或视图引用到此列时，将无法删除，而cascade会强制连带引用此列的约束、视图一起删除。)
alter table 表名 [add column 新列名 数据类型[列级约束条件]]
			    [drop column 列名[restrict|cascade]]
				[modify column 列名 新数据类型]          # 只改数据类型
				[change column 旧列名 新列名 新数据类型]  # 改列名及对应的数据类型

# 删除表
drop table 表名 [restrict|cascade]

SQL 数据类型

字符串存储类型：

char(n) 可以存储固定长度为n的任意字符串，如果插入长度小于定义长度，则用空格填充。
varchar(n) 也可以存储不固定长度的任意字符串，但不能超过n，不会用空格填充。
tinytext：非常小的文本
mediumtext：中等长度文本
text：普通文本
longtext：极大文本
binary：定长二进制字符串
varbinary：变长二进制字符串

数字存储类型：

tinyint：非常小的整数
smallint：小型整数
mediumint：中型整数
int或 INTEGER：标准整数
bigint：大型整数
float用于存储单精度小数
double用于存储双精度的小数

时间存储类型：

date 存储日期（格式为YYYY-MM-DD）
time 存储时间（格式为HH:MM:SS）
datetime 存储日期+时间（格式为YYYY-MM-DD HH:MM:SS）
year 存储年份（2位或4位格式）
timestamp：日期和时间的组合，通常用于记录插入或更新行的时间（格式为yyyy-mm-dd hh:mm:ss），范围比 datetime 小，但精度更高，主要用来记录插入或更新行的时间戳。

列级约束

主键 primary key
外键 foreign key
唯一 unique
空值/非空 null/ not null
默认 default
检查 check （MySQL不支持）

表级约束

主键
外键
唯一
检查（MySQL不支持）

数据库操作语言

插入数据

# 插入的数据与列名一一对应时可以省略列名，主键自增或有默认值时可以省略部分列名
# 如果插入主键值大于自增基准，那么插入后基准会变更为插入主键值 +1
insert into 表名 values(值1, 值2, 值3)
insert into 表名(列名1, 列名2) values(值1, 值2)   # 列名顺序可以变动
# 插入多个值
insert into 表名(列名1, 列名2) values(值1, 值2), (值1, 值2), (值1, 值2)

修改数据

update 表名 set 列名=值,... where 条件

删除数据

delete from 表名
delete from 表名 where 条件
# 删除所有数据并重置主键自增基准
truncate table 表名;
# 修改主键自增基准为 n
alter table 表名 auto_increment=n

数据库查询语言

# 单表查询
select 列名 from 表名
select * from 表名 where 条件

# 对查询结果进行排序，默认升序
select * from 表名 where 条件 order by 列名 asc|desc
# 添加多个排序，先按列名1排序，再按列名2排序
select * from 表名 where 条件 order by 列名1 asc|desc, 列名2 asc|desc
 
# 使用聚集函数进行查询
select count(distinct 列名) from 表名 where 条件
# 例
select count(1) from tb_test;

# 分组和分页查询
# 通过使用group by来对查询结果进行分组，它可以结合聚合函数一起使用
select sum(*) from 表名 where 条件 group by 列名
select sum(*) from 表名 where 条件 group by 列名 having 约束条件
# 例
select sex from tb_test group by sex;
select sum(id), sex  from tb_test group by sex;
# 通过limit来限制查询的数量，只取前n个结果
select * from 表名 limit 数量
# 分页
select * from 表名 limit 起始位置, 数量



# 多表查询（多表查询会提通过连接转换为单表查询）
select * from 表1, 表2
select * from 表1, 表2 where 条件

# 内连接返回的结果集是两个表中所有相匹配的数据，而舍弃不匹配的数据。没有匹配条件则返回笛卡尔积
# 当两张表为同一张表时，即自身连接查询
select * from 表名 别名1, 表名 别名2
select * from 表名1 inner join 表名2
where 表名1.字段1 = 表名2.字段2

# 外连接查询
# 左外连接：返回包含左表中的所有记录和右表中联结字段相等的记录的结果集。
# 如果左表中的某个记录在右表中没有匹配的记录，则结果集中该记录对应的右表中的字段为NULL，右外连接同理
select * from 表名1 
left join 表名2
where 表名1.字段1 = 表名2.字段2

# 嵌套查询
select * from 表名 where 列名 = (select 列名 from 表名 where 条件)

常用查询条件

一般的比较运算符，包括 =、>、<、>=、<=、!= 等。
是否在集合中：in、not in
字符模糊匹配：like，not like
多重条件连接查询：and、or、not
是否处于某个范围 between and

常用聚集函数

count([distinct]*) 统计所有的行数（distinct表示去重再统计）
count([distinct]列名)统计某列的值总和
sum([distinct]列名)求一列的和（注意必须是数字类型的）
avg([distinct]列名)求一列的平均值（注意必须是数字类型）
max([distinct]列名)求一列的最大值
min([distinct]列名)求一列的最小值

数据库控制语言

# 创建用户
create user 用户名 identified by 密码

# 登录用户
# 首先添加环境变量，然后通过cmd 登录：
login -u 用户名 -p

# 用户授权
grant all|权限1,权限2...(列1,...) on 数据库.表 to 用户 [with grant option]
# 回收权限
revoke all|权限1,权限2...(列1,...) on 数据库.表 from 用户

视图

# 创建视图，with check option是指当创建后，如果更新视图中的数据，是否要满足子查询中的条件表达式，不满足将无法插入
# 若视图是由两个以上基本表导出、视图字段来自集函数等，则此视图不允许更新
create view 视图名称(列名) as 子查询语句 [with check option]

# 删除视图
drop view 视图名称

索引

# 索引能够快速地定位元素存放的位置
# 创建索引
create index 索引名称 on 表名(列名)

# 查看表中的索引
show index from student

# 删除索引
drop index 索引名称 on 表名

触发器

# 触发器所依附的表称为基本表，当基本表上发生 insert/update/delete 等操作时，会自动生成两个临时的表（new表和old表，只能由触发器使用）
# 比如在insert操作时，新的内容会被插入到new表中；在delete操作时，旧的内容会被移到old表中，我们仍可在old表中拿到被删除的数据；在update操作时，旧的内容会被移到old表中，新的内容会出现在new表中。
# 创建触发器
create trigger 触发器名称 [before|after] [insert|update|delete] 
on 表名/视图名 for each row delete from student    # for each row 表示针对每一行都会生效
where student.sno = new.sno

# 查看触发器
show triggers

# 删除触发器
drop trigger 触发器名称

事务

# 只有Innodb引擎支持事务 
show engines;
begin;   #开始事务
...
rollback;  #回滚事务
savepoint 回滚点;  #添加回滚点
rollback to 回滚点; #回滚到指定回滚点
...
commit; #提交事务

public static void main(String[] args) {
    Connection conn = null;
    PreparedStatement pstmt1 = null;
    PreparedStatement pstmt2 = null;
    try {
        // 加载数据库驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 获取连接对象
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "password");
        // 设置手动提交模式
        conn.setAutoCommit(false);
        // 执行第一个SQL语句
        String sql1 = "UPDATE account SET balance = balance - 100 WHERE id = 1";
        pstmt1 = conn.prepareStatement(sql1);
        pstmt1.executeUpdate();
        // 执行第二个SQL语句
        String sql2 = "UPDATE account SET balance = balance + 100 WHERE id = 2";
        pstmt2 = conn.prepareStatement(sql2);
        pstmt2.executeUpdate();
        // 提交事务
        conn.commit();
        System.out.println("转账成功！");
    } catch (Exception e) {
        try {
            // 发生异常，回滚事务
            if (conn != null)
                conn.rollback();
            e.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                // 关闭资源
                if (pstmt1 != null)
                    pstmt1.close();
                if (pstmt2 != null)
                    pstmt2.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}

常用语法

保存或更新

注：需要指定一个或多个字段为唯一键

insert into user(name, email, password)
    values
        ('Pixel', 'pixel.com', '12345678')
on duplicate key 
update password=values(password);
# 批量保存或更新（mybatis） 
insert into user(name, email, password)
    values
		<foreach collection="list" item="user" separator=",">
          (#{user.name}, #{user.email}, #{user.password})
		</foreach>
on duplicate key 
update password=values(password);