数据库基础

2023-03-18 08:56| 来源: 网络整理| 查看: 265

表操作增删改查连接内连接自连接自然连接外连接视图存储过程游标触发器事务管理权限管理

表操作-- 创建CREATE TABLE mytable ( id INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL DEFAULT 1,// 默认值1 col2 VARCHAR(45) NULL, col3 DATE NULL, PRIMARY KEY (`id`));-- 修改表ALTER TABLE mytable add col CHAR(20); -- 添加列ALTER TABLE mytable DROP COLUMN col; -- 删除列DROP TABLE mytable; -- 删除表

增删改查

插入

INSERT INTO mytable(col1, col2) VALUES(val1, val2);-- 插入检索出来的数据INSERT INTO mytable1(col1, col2) SELECT col1, col2 FROM mytable2; -- 将一个表的内容插入到一个新表CREATE TABLE newtable AS SELECT * FROM mytable;

更新

UPDATE mytable SET col = val WHERE id = 1;

删除

DELETE FROM mytable WHERE id = 1;-- 可以清空表，也就是删除所有行。TRUNCATE TABLE mytable;

查询

DISTINCT 所有列的值都相同的值只会出现一次。它作用于所有列

SELECT DISTINCT col1, col2 FROM mytable;

limit 限制返回的行数。有两个参数，第一个参数为起始行，从 0 开始；第二个参数为返回的总行数

SELECT * FROM mytable LIMIT 5; -- 返回前 5 行SELECT * FROM mytable LIMIT 0, 5; -- 返回第 1 ~ 5 行SELECT * FROM mytable LIMIT 2, 3; -- 返回第 3 ~ 5 行

case when

SELECT s.s_sex,CASE s.s_sex WHEN '1' THEN '男' ELSE '女' END as sex FROM t_b_student s;

排序 ASC ：升序（默认）, DESC ：降序

-- 可以按多个列进行排序，并且为每个列指定不同的排序方式SELECT * FROM mytable ORDER BY col1 DESC, col2 ASC;

过滤 WHERE子句

SELECT * FROM mytable WHERE col IS NULL;-- 不以 A 和 B 开头的任意文本SELECT * FROM mytable WHERE col LIKE '[^AB]%'; 操作符说明 = 等于大于 != 不等于小于等于 >= !=0 个任意字符 _ 匹配 ==1 个任意字符 [ ] 可以匹配集合内的字符

分组 GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出NULL 的行会单独分为一组大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型SELECT col, COUNT(*) AS num FROM mytable GROUP BY col HAVING num >= 2;

UNION

每个查询必须包含相同的列、表达式和聚集函数默认会去除相同行，如果需要保留相同行，使用 UNION ALL只能包含一个 ORDER BY 子句，并且必须位于语句的最后SELECT col FROM mytable WHERE col = 1UNIONSELECT col FROM mytable WHERE col =2;

行转列

PIVOT 后跟一个聚合函数来拿到结果，FOR 后面跟的科目是我们要转换的列，这样的话科目中的语文、数学、英语就就被转换为列。IN 后面跟的就是具体的科目值。

SELECT *FROM studentPIVOT ( SUM(score) FOR subject IN (语文, 数学, 英语)) 列转行

SELECT *FROM student1UNPIVOT ( score FOR subject IN ("语文","数学","英语"))

连接

连接用于连接多个表，使用 JOIN 关键字，并且条件语句使用 ON 而不是 WHERE。连接可以替换子查询，并且比子查询的效率一般会更快。可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。

内连接

内连接又称等值连接，使用 INNER JOIN 关键字。

SELECT A.value, B.value FROM tablea AS A INNER JOIN tableb AS B ON A.key = B.key;-- 可以不明确使用 INNER JOIN，而使用WHERE 中将两个表中要连接的列用等值方法连接起来。SELECT A.value, B.value FROM tablea AS A, tableb AS B WHERE A.key = B.key;

自连接

自连接可以看成内连接的一种，只是连接的表是自身而已。

-- 一张员工表，包含员工姓名和员工所属部门，要找出与 Jim 处在同一部门的所有员工姓名。-- 子查询SELECT name FROM employee WHERE department = (SELECT department FROM employee WHERE name = "Jim");-- 自连接SELECT e1.name FROM employee AS e1 INNER JOIN employee AS e2ON e1.department = e2.department AND e2.name = "Jim";

自然连接

自然连接是把同名列通过等值测试连接起来的，同名列可以有多个。内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。

SELECT A.value, B.value FROM tablea AS A NATURAL JOIN tableb AS B;

外连接

外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行。

-- 检索所有顾客的订单信息，包括还没有订单信息的顾客。SELECT Customers.cust_id, Orders.order_numFROM Customers LEFT OUTER JOIN OrdersON Customers.cust_id = Orders.cust_id;

customers 表

cust_id cust_name 1 a 2 b 3 c

orders 表

order_id cust_id 1 1 2 1 3 3 4 3

结果

cust_id cust_name order_id 1 a 1 1 a 2 3 c 3 3 c 4 2 b Null

视图

视图是虚拟的表，本身不包含数据，也就不能对其进行索引操作。对视图的操作和对普通表的操作一样。视图具有如下好处：

简化复杂的 SQL 操作，比如复杂的连接；只使用实际表的一部分数据；通过只给用户访问视图的权限，保证数据的安全性；更改数据格式和表示。 CREATE VIEW myview ASSELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_colFROM mytable WHERE col5 = val;

存储过程

存储过程可以看成是对一系列 SQL 操作的批处理。

使用存储过程的好处：

代码封装，保证了一定的安全性；代码复用；由于是预先编译，因此具有很高的性能。

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。包含 in、out 和 inout 三种参数。给变量赋值都需要用 select into 语句。每次只能给一个变量赋值，不支持集合的操作。

delimiter //create procedure myprocedure( out ret int ) begin declare y int; select sum(col1) from mytable into y; select y*y into ret; end //delimiter ;call myprocedure(@ret);select @ret;

游标

在存储过程中使用游标可以对一个结果集进行移动遍历。游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

声明游标，这个过程没有实际检索出数据；打开游标；取出数据；

关闭游标；

delimiter //create procedure myprocedure(out ret int) begin declare done boolean default 0; declare mycursor cursor for select col1 from mytable; # 定义了一个 continue handler，当 sqlstate '02000' 这个条件出现时，会执行 set done = 1 declare continue handler for sqlstate '02000' set done = 1; open mycursor; repeat fetch mycursor into ret; select ret; until done end repeat; close mycursor; end //delimiter ;

触发器

触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。

INSERT 触发器包含一个名为 NEW 的虚拟表。

CREATE TRIGGER mytrigger AFTER INSERT ON mytableFOR EACH ROW SELECT NEW.col into @result;SELECT @result; -- 获取结果

DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

事务管理

基本术语：

事务（transaction）指一组 SQL 语句；回退（rollback）指撤销指定 SQL 语句的过程；提交（commit）指将未存储的 SQL 语句结果写入数据库表；保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

MySQL 的事务提交默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

通过设置 autocommit 为 0 可以取消自动提交；autocommit 标记是针对每个连接而不是针对服务器的。

如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。

START TRANSACTION// ...SAVEPOINT delete1// ...ROLLBACK TO delete1// ...COMMIT

权限管理

MySQL 的账户信息保存在 mysql 这个数据库中。

USE mysql;SELECT user FROM user; -- 创建账户，新创建的账户没有任何权限。CREATE USER myuser IDENTIFIED BY 'mypassword';-- 修改账户名RENAME myuser TO newuser;-- 删除账户DROP USER myuser;-- 查看权限SHOW GRANTS FOR myuser;-- 授予权限-- 账户用 username@host 的形式定义，username@% 使用的是默认主机名。GRANT SELECT, INSERT ON mydatabase.* TO myuser;-- 更改密码，必须使用 Password() 函数SET PASSWROD FOR myuser = Password('new_password');

删除权限GRANT 和 REVOKE 可在几个层次上控制访问权限：

整个服务器，使用 GRANT ALL 和 REVOKE ALL；整个数据库，使用 ON database.*；特定的表，使用 ON database.table；特定的列；特定的存储过程。REVOKE SELECT, INSERT ON mydatabase.* FROM myuser;

【本文地址】

公司简介

联系我们