SQL 简介

SQL 简介

简介

我用 mysql 做的实验。

SQL Structured Query Language:结构化查询语言
SQL 基于关系代数和元组关系演算,包括一个数据定义语言和数据操纵语言。

上面的定义不懂。

此文讲数据存储与获取,存储的方法是 表结构

我们在图书馆看书,一般一个架子放一种类型的书。(言情架,历史架)
在计算机中,我们不能放具体的书,只能放书的抽象数据(属性)。

比如:

序号 书名 作者 出版年月 书的内容
1 平凡的世界 路遥 某年某月 在一个下雨天…..
2 .. .. .. ..

我们在定义属性时,要定义属性的数据类型,这里用的是计算机存储的知识。其他都可以从生活中抽象出来。

结构定义与删除

  • 创建
    CREATE DATABASE test;
  • 查看
    SHOW databases;
  • 删除
    DROP DATABASE test;

对表操作时要对库约束一下

use DATABASEName; 
  • 创建
    1
    2
    3
    4
    5
    6
    CREATE TABLE mytable (
    id INT NOT NULL AUTO_INCREMENT,
    col1 INT NOT NULL DEFAULT 1,
    col2 VARCHAR(45) NULL,
    col3 DATE NULL,
    PRIMARY KEY (`id`));

  • 使用旧表创建新表
    create table tab_new like tab_old 
  • 修改
    • 添加列
      ALTER TABLE mytable ADD col CHAR(20);
    • 删除列
      ALTER TABLE mytable DROP COLUMN col;
    • 删除表
      DROP TABLE mytable;
  • 查看表结构
    desc tabl_name;
    describe tabl_name;

数据处理 (条目的 添加,删除,修改)

  • 插入
    • 普通插入
      INSERT INTO mytable VALUES (2,"xiaozhang","19880401");
      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;

表连接

连接

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

  • 内连接
    SELECT A.value, B.value
    FROM tablea AS A INNER JOIN tableb AS B
    ON A.key = B.key;

  • 自连接

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

    SELECT e1.name
    FROM employee AS e1 INNER JOIN employee AS e2
    ON 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_num
    FROM Customers LEFT OUTER JOIN Orders
    ON Customers.cust_id = Orders.cust_id;

  • 组合查询
    SELECT col
    FROM mytable
    WHERE col = 1
    UNION
    SELECT col
    FROM mytable
    WHERE col =2;

视图

视图是虚拟的表,本身不包含数据,也就不能对其进行索引操作。

CREATE VIEW myview AS
SELECT Concat(col1, col2) AS concat_col, col3*col4 AS compute_col
FROM mytable
WHERE col5 = val;

存储过程

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

create procedure myprocedure( out ret int )
begin
declare y int;

select sum(col1)
from mytable
into y;

select y*y into ret;
end //
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 mytable
FOR 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 中指定该保留点,则会回退到该保留点。

1
2
3
4
5
6
7
START TRANSACTION
// ...
SAVEPOINT delete1
// ...
ROLLBACK TO delete1
// ...
COMMIT

字符集

  • 字符集为字母和符号的集合;
  • 编码为某个字符集成员的内部表示;
  • 校对字符指定如何比较,主要用于排序和分组。

除了给表指定字符集和校对外,也可以给列指定:

CREATE TABLE mytable
(col VARCHAR(10) CHARACTER SET latin COLLATE latin1_general_ci )
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

可以在排序、分组时指定校对:

1
2
3
SELECT *
FROM mytable
ORDER BY col COLLATE latin1_general_ci;

权限管理

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;
  • 删除权限

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

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

    必须使用 Password() 函数

    SET PASSWROD FOR myuser = Password('new_password');
  • 无密码登录

    配置 *$HOME/.my.cnf * 文件

    [client]
    password = test
    $ chmod 400 .my.cnf

数据查询输出

查找满足某 属性(条件) 的数据 ,属性用 where 指定。

  • SELECT
    SELECT * FROM student WHERE name LIKE "%li%";
  • DISTINCT (不同的)

    一列不同就满足 DISTINCT

  • LIMIT 限制条数
    LIMIT 0, 5;
  • 排序
    ORDER BY col1 DESC, col2 ASC;
    ASC DESC
  • 过滤 WHERE
    等于 =
    小于 <
    大于 >
    不等于 <> 或 !=
    小于等于 <=
    大于等于 >=
    在两个值之间 BETWEEN
    为 NULL 值 IS NULL
    连接 AND 和 OR
    匹配一组值 IN
    排除 NOT
    通配 LIKE

    % 匹配 >=0 个任意字符
    _ 匹配 ==1 个任意字符
    [ ] 匹配集合内的字符 ^ 用来排除

    少用匹配

字段处理

  • 字段取别名 as
  • 函数
    函数 描述
    AVG 平均值
    COUNT 计数(不含 Null)
    FIRST 第一个记录的值
    MAX 最大值
    MIN 最小值
    STDEV 样本标准差
    STDEVP 总体标准差
    SUM 求和
    VAR 样本方差
    VARP 总体方差
    UCASE 转化为全大写字母
    LCASE 转化为全小写字母
    MID 取中值
    LEN 计算字符串长度
    INSTR 获得子字符串在母字符串的起始位置
    LEFT 取字符串左边子串
    RIGHT 取字符串右边子串
    ROUND 数值四舍五入取整
    MOD 取余
    NOW 获得当前时间的值
    FORMAT 字符串格式化
    DATEDIFF 获得两个时间的差值

    使用 DISTINCT 可以让汇总函数值汇总不同的值。

    SELECT AVG(DISTINCT col1) AS avg_col
    FROM mytable;

  • 文本处理
    左边的字符 LEFT(str,len)
    右边的字符 RIGHT(str,len)
    转换为小写字符 LOWER()
    转换为大写字符 UPPER()
    去除左边的空格 LTRIM()
    去除右边的空格 RTRIM()
    长度 LENGTH()
    转换为语音值 SOUNDEX()
  • 日期和时间处理
    • 日期格式:YYYY-MM-DD
    • 时间格式:HH:MM:SS
    增加一个日期(天、周等) AddDate()
    增加一个时间(时、分等) AddTime()
    返回当前日期 CurDate()
    返回当前时间 CurTime()
    返回日期时间的日期部分 Date()
    计算两个日期之差 DateDiff()
    高度灵活的日期运算函数 Date_Add()
    返回一个格式化的日期或时间串 Date_Format()
    返回一个日期的天数部分 Day()
    对于一个日期,返回对应的星期几 DayOfWeek()
    返回一个时间的小时部分 Hour()
    返回一个时间的分钟部分 Minute()
    返回一个日期的月份部分 Month()
    返回当前日期和时间 Now()
    返回一个时间的秒部分 Second()
    返回一个日期时间的时间部分 Time()
    返回一个日期的年份部分 Year()
  • 数值处理
    正弦 SIN()
    余弦 COS()
    正切 TAN()
    绝对值 ABS()
    平方根 SQRT()
    余数 MOD()
    指数 EXP()
    圆周率 PI()
    随机数 RAND()

分组

分组就是把具有相同属性的数据行放在同一组中。

GROUP BY col;
WHERE 过滤行,HAVING 过滤分组,行过滤应当先于分组过滤。

子查询

子查询就是 第二道第三道 。。。 查询

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;

0%