SQL学习笔记

平凡无痕

已于 2024-09-05 14:50:49 修改

阅读量208

点赞数 2

文章标签： sql 学习笔记

于 2024-09-05 10:38:51 首次发布

本文链接：https://blog.csdn.net/wuhen_DP/article/details/141924612

版权

SQL的建表相关知识

一、建表语句？

常规创建

create table if not exists 目标表

CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))
# PRIMARY KEY主键，一般在建表语句后面加入comment进行备注

复制表格

create table table_name like table_name1;(复制表结构）

将table1的部分拿来创建table2

create teble if not exists table_name() select * from table table_name1;数据复制
Note: 分号在select语句后面

drop table if exists actor_name;
CREATE TABLE  actor_name  (
   first_name  varchar(45) NOT NULL COMMENT '名字',
   last_name  varchar(45) NOT NULL COMMENT '姓氏'
)
select first_name,last_name from actor;

在原来的表上新增列

题目链接：牛客SQL234
最后一列增加列：alter table 表名 add [column] <新增列名> [NOT NULL] [DEFAULT value]
指定位置增加列：alter table 表名 add [column] <新增列名> [NOT NULL] [DEFAULT value] after <列名>
在第一列增加列：alter table 表名 add [column] <新增列名> [NOT NULL] [DEFAULT value] first
其中[] 内的内容可以省略；当不指定默认值时，MySQL会自动设置该字段默认为NULL。

ALTER TABLE actor
ADD create_date datetime NOT NULL DEFAULT '2020-10-01 00:00:00' after last_update

二、批量插入语句

2.1 初始化插入语句

对表批量插入数据。Mysql
insert into 表名 values(v1,v2,…),values(v1,v2,…),…

insert into actor 
values(1,"PENELOPE","GUINESS",'2006-02-15 12:34:33'),
(2,"NICK","WAHLBERG",'2006-02-15 12:34:33')

这条语句高度依赖表中列的定义顺序。虽然简单，但是并不安全。如果表的结构发生变化，就会报错。最好表名后给出列名（如下）。这样即使表的结构发生变化，语句依然可以执行。

insert into actor
(actor_id,first_name,last_name,last_update)
values
(1,"PENELOPE","GUINESS","2006-02-15 12:34:33"),
(2,"NICK","WAHLBERG","2006-02-15 12:34:33")

2.2 插入语句过程中遇到重复数据如何处理

例如已经执行了语句

drop table if exists actor;
CREATE TABLE actor (
   actor_id  smallint(5)  NOT NULL PRIMARY KEY,
   first_name  varchar(45) NOT NULL,
   last_name  varchar(45) NOT NULL,
   last_update  DATETIME NOT NULL);
insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');

如果对于表actor插入如下数据,如果其中主键3的数据已经存在了，请忽略。

actor_id	first_name	last_name	last_update
‘3’	‘ED’	‘CHASE’	‘2006-02-15 12:34:33’

# mysql中常用的三种插入数据的语句: 
# insert into表示插入数据，数据库会检查主键，如果出现重复会报错； 
# replace into表示插入替换数据，需求表中有PrimaryKey，
#             或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样； 
# insert ignore表示，INSERT IGNORE 在遇到特定错误（如唯一键冲突）时，会忽略错误并继续执行插入操作。
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");

3 创建索引

3.1 使用Alter创建索引

3.1.1 添加主键

特点：数据列不允许重复，不能为null,一张表只能有一个主键；Mysql主动将该字段进行排序

ALTER TABLE tbl_name ADD PRIMARY KEY (col_list);
// 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

3.1.2 添加唯一索引

特点：索引列是唯一的，可以null；Mysql主动将该字段进行排序

ALTER TABLE 表名 ADD unique <索引名> (col1, col2, ...col3);
// 这条语句创建索引的值必须是唯一的。

3.1.3 添加普通索引

特点：添加普通索引，索引值不唯一，可为null

Alter table 表名 ADD index <索引名> (col1, col2, ...,);
// 添加普通索引，索引值可出现多次。

3.1.4 添加全文索引

特点：只能在文本类型CHAR，VARCHAR, TEXT类型字段上创建全文索引；

ALTER TABLE 表名 ADD Fulltext <索引名> (col)
// 该语句指定了索引为 FULLTEXT ，用于全文索引。

3.1.5 添加多列索引(联合索引)

特点：多列是唯一的

ALTER TABLE 表名 ADD UNIQUE (col1, col2, ..., )

3.2 使用Create创建索引

语法：create index 索引名 on 表名(字段)

3.2.1 添加普通索引

create index 索引名 on 表名(col1, col2, ..., )

3.2.2 添加唯一索引

create unique index 索引名 on 表名(col1, col2, ..., )

3.3 两种创建索引方式的区别

Alter可以省略索引名。如果省略索引名，数据库会默认根据第一个索引列赋予一个名称；Create必须指定索引名称。
Create不能用于创建Primary key索引；
Alter允许一条语句同时创建多个索引；Create一次只能创建一个索引

ALTER TABLE 表名 ADD Primary key (id), ADD index <索引名> (col1, col2, ...,)

3.4 索引执行效率分析

主键索引 > 唯一性索引 > 普通索引

3.5 删除索引

第一种方式

drop index 索引名 on 表名;

第二种方式

Alter table 表名 drop index 索引名;

第三种方式

Alter table 表名 drop primary key

分析：

第三种方式只在删除primary key中使用。因一个表只能存在一个primary key索引，则不需要指定索引名；
对于第三种方式，若没有创建primary key索引，但表中具有一个或多个unique索引，则默认删除第一个unique索引；
若删除表中的某列，索引会受到影响。对于多列组合的索引，如果删除其中的某一列，则该列会从对应的索引中被删除(删除列，不删除索引)；多删除组成索引的所有列，则索引将被删除(不仅删除列，还删除索引)。</索引名></索引名></索引名></索引名>

3.6 强制索引

强制索引FORCE INDEX
FORCE INDEX强制查询优化器使用指定的命名索引。查询优化器是MySQL数据库服务器中的一个组件，它为SQL语句提供最佳的执行计划。查询优化器使用可用的统计信息来提出所有候选计划中成本最低的计划。
参考链接: MySQL 强制索引
题目链接: 牛客SQL233

# 书写顺序
SELECT……
FROM ……
FORCE INDEX(index_name)
WHERE……

4 创建视图（Mysql）

创建一个视图并重新命名字段名

直接在视图名的后面用小括号创建视图中的字段名

create view 视图名称(col1_v,col2_v) as
select col1,col2 from 表名

在select后面对列重命名为视图的字段名

create view 视图名称 as
select col1 as col1_v,col2 as col2_v from 表名

5 触发器

题目链接：牛客SQL235
“触发器”（Trigger）是一种特殊的存储过程，它会在特定的数据库事件（如 INSERT、UPDATE、DELETE 操作）发生时自动执行。触发器通常用于在数据更改时执行某些业务逻辑，如数据验证、记录日志或触发其他操作。
很多人都不提倡使用触发器，因为这是一个隐藏的过程不好管理。

创建触发器的语法

CREATE TRIGGER trigger_name
{BEFORE | AFTER}   # 触发时间
{INSERT | UPDATE | DELETE}  #  监视事件 trigger_event
ON table_name # 监视地点 table
FOR EACH ROW  # 这句话在mysql中是固定的
BEGIN 
    -- 触发器逻辑  #触发事件  trigger_stmt 注意这里要有分号
END;

注意对同一个表相同触发时间的相同触发事件，只能定义一个触发器；
触发器只能建立在永久表上，不能对临时表使用
可以使用old和new来引用触发器中发生变化的记录内容。
old表示监视事件发生之前的原列，new表示监视事件发生后的新列

示例1：创建一个触发器，当员工的薪水employees表被更新时，记录更改到另一个表salary_log 中。

CREATE TRIGGER after_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date) # salary_log 表中的字段
    VALUES (OLD.id, OLD.salary, NEW.salary, NOW()) # employees表中的字段;
END;

示例2：假设有一个 employees 表，我们希望在插入一条新记录时，确保 salary 字段大于 0。如果不是，则将 salary 设置为 0。

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 0 THEN
        SET NEW.salary = 0;
    END IF;
END;
# BEFORE INSERT 指定在 INSERT 之前触发。
# NEW 表示将要插入的新行。
# 如果新行的 salary 小于 0，则在插入之前将 salary 的值改为 0。

6 修改表内容

6.1 更新

UPDATE 语句用于修改现有表中的数据。可以根据条件更新单条或多条记录。
sql表格内容更新标准格式：

update 表名
set column1 = value1, column2 = value2, ... 
where 条件表达式

6.2 删除

DELETE 语句用于从表中删除一行或多行数据。它可以结合 WHERE 子句一起使用，以删除满足特定条件的记录。
基本语法如下

DELETE FROM table_name
WHERE condition;
# 如果忘记加 WHERE 子句，会删除所有记录。

区别 DELETE 和 TRUNCATE:
DELETE 删除指定记录，支持 WHERE 子句，可以逐条删除，也可以触发器。
TRUNCATE 会删除所有记录，效率更高，但不支持 WHERE，且不会触发触发器。