SQL基础

最新推荐文章于 2021-01-27 13:01:40 发布

cf_wu95

最新推荐文章于 2021-01-27 13:01:40 发布

阅读量177

点赞数

分类专栏：计算机基础

本文链接：https://blog.csdn.net/cf_wu95/article/details/90115102

版权

计算机基础专栏收录该内容

1 篇文章 0 订阅

订阅专栏

基础

主键的值不允许修改，也不允许复用（不能使用已经删除的主键值赋给新数据行的主键）。
SQL 语句不区分大小写。
使用更新和删除操作时一定要用 WHERE 子句，不然会把整张表的数据都破坏。可以先用 SELECT 语句进行测试，防止错误删除。
SQL语句中----删除表数据drop、truncate和delete的用法
DISTINCT关键字：相同值只会出现一次。它作用于所有列，也就是说所有列的值都相同才算相同。
NULL 与 0、空字符串都不同。空值是不占用空间的，mysql中的NULL其实是占用空间的（one bit）。
AND 和 OR 用于连接多个过滤条件，优先处理 AND。
IN 操作符用于匹配一组值，其后也可以接一个 SELECT 子句，从而匹配子查询得到的一组值。
NOT 操作符用于否定一个条件。
通配符也是用在过滤语句（where）中，但它只能用于文本字段。% 匹配 >=0 个任意字符；_ 匹配 ==1 个任意字符；[ ] 可以匹配集合内的字符，例如 [ab] 将匹配字符 a 或者 b。用脱字符 ^ 可以对其进行否定，也就是不匹配集合内的字符。
使用 Like 来进行通配符匹配。注意：不要滥用通配符，通配符位于开头处匹配会非常慢。
AVG() 会忽略 NULL 行。使用 DISTINCT 可以让汇总函数值汇总不同的值。
子查询中只能返回一个字段的数据。
连接可以替换子查询，并且比子查询的效率一般会更快。
可以用 AS 给列名、计算字段和表名取别名，给表名取别名是为了简化 SQL 语句以及连接相同表。
内连接和自然连接的区别：内连接提供连接的列，而自然连接自动连接所有同名列。
在存储过程中使用游标可以对一个结果集进行移动遍历。游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。
事务：不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。
如果没有设置保留点，ROLLBACK 会回退到 START TRANSACTION 语句处；如果设置了保留点，并且在 ROLLBACK 中指定该保留点，则会回退到该保留点。
COLLATE:校对字符指定如何比较，主要用于排序和分组。例如utf8_unicode_ci，其实是用来排序的规则。对于mysql中那些字符类型的列，如VARCHAR，CHAR，TEXT类型的列，都需要有一个COLLATE类型来告知mysql如何对该列进行排序和比较。简而言之，COLLATE会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响**DISTINCT**、**GROUP BY**、**HAVING**语句的查询结果。

#####插入


#普通插入
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 不能删除行数据,要删就要把表清空。
TRUNCATE TABLE mytable;
drop table  mytable; -- 直接删除表

#DISTINCT
SELECT DISTINCT col1, col2
FROM mytable;

#LIMIT 返回第3~5行
SELECT *
FROM mytable
LIMIT 3, 5;

#ASC ：升序（默认），默认升序的。DESC ：降序，可以按多个列进行排序，并且为每个列指定不同的排序方式：
SELECT *
FROM mytable
ORDER BY col1 DESC, col2 ASC;

#通配符
SELECT *
FROM mytable
WHERE col LIKE '[^AB]%'; -- 不以 A 和 B 开头的任意文本

#计算字段
SELECT CONCAT(TRIM(col1), '(', TRIM(col2), ')') AS concat_col
FROM mytable;#as用来取别名，可作用于列以及表。


SELECT AVG(DISTINCT col1) AS avg_col --AVG忽略null值
FROM mytable;


SELECT col, COUNT(*) AS num
FROM mytable  
GROUP BY col;  --指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num; --GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;
ORDER BY num;  --WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。HAVING主要用于聚合函数。
/*
GROUP BY分组规定：

GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前；
除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出；
NULL 的行会单独分为一组；
大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型。

书写和执行顺序：where、group by、having、order by 
当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是： 
1.执行where xx对全表数据做筛选，返回第1个结果集。 
2.针对第1个结果集使用group by分组，返回第2个结果集。 
3.针对第2个结集执行having xx进行筛选，返回第3个结果集。 
4.针对第3个结果集中的每1组数据执行select xx，有几组就执行几次，返回第4个结果集。 
5.针对第4个结果集排序。
*/

SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
               FROM mytable2); --子查询中只能返回一个字段的数据。


SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name; --检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次


#自连接
SELECT e1.name
FROM employee AS e1 INNER JOIN employee AS e2
ON e1.department = e2.department
      AND e2.name = "Jim"; --注意要用on 

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;  --检索所有顾客的订单信息，包括还没有订单信息的顾客。左外连接


SELECT col
FROM mytable
WHERE col = 1
UNION --默认会去除相同行，如果需要保留相同行，使用 UNION ALL。
SELECT col
FROM mytable
WHERE col =2;
/*
使用 UNION 来组合两个查询，如果第一个查询返回 M 行，第二个查询返回 N 行，那么组合查询的结果一般为 M+N 行。
每个查询必须包含相同的列、表达式和聚集函数。
默认会去除相同行，如果需要保留相同行，使用 UNION ALL。
只能包含一个 ORDER BY 子句，并且必须位于语句的最后。
*/

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

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;
/*
存储过程
可以看成是对一系列 SQL 操作的批处理。（与事务还不相同有点类似于函数）
使用存储过程的好处：
代码封装，保证了一定的安全性；
代码复用；
由于是预先编译，因此具有很高的性能。
命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。
包含 in、out 和 inout 三种参数。
给变量赋值都需要用 select into 语句。
每次只能给一个变量赋值，不支持集合的操作。
*/

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 ;
/*
使用游标的四个步骤：

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


CREATE TRIGGER mytrigger AFTER INSERT ON mytable
FOR EACH ROW SELECT NEW.col into @result;--触发器,在mytable插入之后触发触发器，将修改记录到另外一张表中。

SELECT @result; -- 获取结果
/*
触发器会在某个表执行以下语句时而自动执行：DELETE、INSERT、UPDATE。
触发器必须指定在语句执行之前还是之后自动执行，之前执行使用 BEFORE 关键字，之后执行使用 AFTER 关键字。BEFORE 用于数据验证和净化，AFTER 用于审计跟踪，将修改记录到另外一张表中。
INSERT 触发器包含一个名为 NEW 的虚拟表。
DELETE 触发器包含一个名为 OLD 的虚拟表，并且是只读的。
UPDATE 触发器包含一个名为 NEW 和一个名为 OLD 的虚拟表，其中 NEW 是可以被修改的，而 OLD 是只读的。
MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。
*/

#事务
START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT 


GRANT SELECT, INSERT ON mydatabase.* TO myuser; --赋予权限
Grant All privileges mydatabase.* TO myuser； --赋予所有的权限
REVOKE All privileges On Student From myuser; --回收所有权限