SQL

概念

SQL 语句命令

SQL语句不区分大小写

创建的表就是一个文件

DLC - 连接和控制

/* 调用存储过程 */
CALL 
/* 创建/取消 DBMS 和 DBMS之间的连接 */
CONNECT
DISCONNECT
RETURN
SET CONNECTION
SET CONSTRAINT

DQL - 事务

COMMIT
RELEASE SAVEPOINT
ROLLBACK
SAVEPOINT
START TRANSACTION

DDL - 数据

/* 判断筛选值 */
SELECT name FROM table1 natural join table2 WHERE expression comparison {ALL | ANY | SOME | AND | OR | NOT | EXISIT | BETWEEN | IN | LIKE } (subquery)

/* FROM 语句嵌套 */
SELECT name FROM (
  SELECT name from tb1 WHERE age > 25
) WHERE salary > 6000

/* 去重查询 */
SELECT DISTINCT name from table1 WHERE salary > 2000

/* JOIN 和交并差运算 */
(SELECT name, title 
FROM (instructor natrual join teaches) join courses USING (course_id))
UNION | UNION ALL | INTERSECT | EXCEPT
(SELECT name as name2, title
FROM instructor WHERE salary > 7000)

/* INNER OUTTER JOIN 以及 ON */
SELECT * FROM table1 join table2 on table1.ID = table2.ID;
SELECT * FROM table1 natural left outer join table2;
SELECT * FROM table1 natural full outer join table2;
SELECT * FROM table1 natural right outer join table2;

/* 空值运算 */
SELECT A FROM table1 WHERE name is null

/* 查询函数运算 */
SELECT avg(A) FROM table1 GROUP BY Y
SELECT min | max | sum | count(X) FROM table1

/* 关闭 or 创建服务器端游标 */
CLOSE CURSOR
DELCARE CURSOR

DELETE from table_name WHERE expression
INSERT into table_name VALUES(val1,val2,val3 ...)
UPDATE table_name set 'fieldName' = val1 WHERE expression

EXPECT
FETCH
INTERSECT
UNION
IS
JOIN
MERGE
OPEN
ORDER BY
SUBQUERY
TURCNATE TABLE

DML - 语句

REVOKEALTER DATABASE

CREATE DATABASE name
CREATE table IF NOT EXISTS database_name.table_name (
  /* name type props index */
    `id` char(16) NOT NULL,
  `var_char` VARCHAR(22),
  `text` TEXT,
  `bolb` BLOB,

  `num` int(1 | 2 | 4 | 8) TINYINT | SMALLINT | MEDIUMINT | INT | BIGINT,
  `float_num` FLOAT(M, D) | DOBULE(M, D) | DECIMAL(M, D),

  `year` YEAR, /* yyyy */
  `date` DATE, /* yyyy-mm-dd */
  `time` TIME, /* hh:mm:ss */
  `date_time` DATETIME,
  `time_stamp` TIMESTAMP,
  `null` NULL,

  `created_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `updated_at` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `name` varchar(255) NOT NULL,
  `icon` varchar(255) NOT NULL,
  `description` varchar(255),
  `read_count` char(16),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP table_name IF table_name EXISITS
GRANT

ALTER table R add A D;
ALTER table R drop B C;

SQL - Session

SET

Index

CREATE index row_index_name on table1(row_name)

View

CREATE VIEW view_name as 
    SELECT ID, name, age FROM table1;

Function

Trigger

存储过程

PROCEDURE

关系

/* one to one */
student: student_id, first_name, last_name, address_id
address: address_id, address, city, zipcode, student_id

/* one to many */
teachers: teacher_id, first_name, last_name # the "one" side
classes:  class_id, class_name, teacher_id  # the "many" side

/* many to many */
student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id     # the junction table

/* example query */
-- Getting all students for a class:
    SELECT s.student_id, last_name
      FROM student_classes sc 
INNER JOIN students s ON s.student_id = sc.student_id
     WHERE sc.class_id = X

 -- Getting all classes for a student: 
    SELECT c.class_id, name
      FROM student_classes sc 
INNER JOIN classes c ON c.class_id = sc.class_id
     WHERE sc.student_id = Y

模糊查询

执行数据库查询时，有完整查询和模糊查询之分，一般模糊语句如下：
SELECT 字段 FROM 表 WHERE 某字段 Like 条件

一、四种匹配模式

关于条件，SQL提供了四种匹配模式：

1、%

表示任意0个或多个字符，可匹配任意类型和长度的字符。有些情况下是中文，需用两个百分号（%%）表示：
SELECT FROM [user] WHERE u_name LIKE ‘%三%’
将会把 u_name 为“张三”、“张猫三”、“三脚猫”、“唐三藏”等有“三”的记录全找出来
另外，如果须要找出 u_name 中既有“三”又有“猫”的记录，请运用 and 条件
SELECT FROM [user] WHERE u_name LIKE ‘%三%’ AND u_name LIKE ‘%猫%’
如果运用：
SELECT * FROM [user] WHERE u_name LIKE ‘%三%猫%’
虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”

2、_

表示任意单个字符。匹配单个任意字符，它常用来限定表达式的字符长度语句：
SELECT FROM [user] WHERE u_name LIKE ‘三’
只找出“唐三藏”这样 u_name 为三个字且中间一个字是“三”的；
再比如
SELECT FROM [user] WHERE uname LIKE ‘三_’；
只找出“三脚猫”这样 name 为三个字且第一个字是“三”的；

3、[ ]

表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个：
SELECT FROM [user] WHERE u_name LIKE ‘[张李王]三’
将找出“张三”、“李三”、“王三”（而非“张李王三”）；
如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e”
SELECT FROM [user] WHERE u_name LIKE ‘老[1-9]’
将找出“老1”、“老2”、……、“老9”；

4、[^ ]

表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符：
SELECT FROM [user] WHERE u_name LIKE ‘[^张李王]三’
将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；
SELECT FROM [user] WHERE u_name LIKE ‘老[^1-4]’;
将排除“老1”到“老4”，寻找“老5”、“老6”、……

Ref

W3CSchool

sql

SQL

概念