SQL 基础
|
608
|
折腾
本文发布于 791 天前。

SQLite 3

.tables
.shema
.mode column

选定数据库

选定sql_store库

USE sql_store

创建数据库

CREATE TABLE student(
    sID INTEGER NOT NULL,
    sName VARCHAR(50) NOT NULL,
    sAddress VARCHAR(255),
    sYear INTEGER DEFAULT 1,
    sClass INTEGER,
    CONSTRAINT pk_student
        PRIMARY KEY (sID)
    CONSTRAINT class
        FOREIGN KEY (sClass)
        REFERENCING class(cID)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

Same as

CREATE TABLE student(
    sID INTEGER
        NOT NULL PRIMARY KEY,
    sName VARCHAR(50) NOT NULL,
    sAddress VARCHAR(255),
    sYear INTEGER DEFAULT 1
);

Foreign keys

CREATE TABLE Enrolment (
    sID INTEGER NOT NULL,
    mCode CHAR(8) NOT NULL,
    PRIMARY KEY (sID, mCode),
    FOREIGN KEY (sID)
        REFERENCES student(sID),
    FOREIGN KEY (mCode)
        REFERENCES Module(mCode)
);

删除数据库

DROP TABLE student

插入数据

INSERT INTO student (sID, sName, sAddress, sYear)
    VALUES (1, 'John S', '1 Sun St', 1);

也可以写

INSERT INTO student VALUES (1, 'John S', '1 Sun St', 1);

sql会自动填充主键

INSERT INTO student
    (sName, sAddress, sYear)
VALUES
    ('A', 'aa', 1),
    ('B', 'bb', 2);

或

```sql
INSERT INTO student VALUES (NULL, 'John S', '1 Sun St', 1);

选择语句

基本顺序

USE sql_store;
SELECT * FROM customers
WHERE customer_id < 5
ORDER BY first_name

基本用法

SELECT
    last_name,
    first_name,
    points
FROM customers

计算并赋予新Attribute Name

SELECT
    points * 10 + 100 AS "discount factor" -- 计算并更改显示的Attribute Name
    -- 也可以省略AS
    points * 10 + 100 "discount factor" -- 计算并更改显示的Attribute Name

FROM customers

数学运算

  • COUNT – returns the number of rows
  • SUM – returns the sum of a column
  • AVG – returns the average of a column
  • MIN – returns the minimum value of a column
  • MAX – returns the maximum value of a column
  • ROUND – rounds a number to a specified number of decimal places
SELECT COUNT(*)
    AS "Number of Students"
FROM Student;

去重

SELECT DISTINCT state FROM customers

关键字冲突

USE sql_inventory;
SELECT
    `name`,
    unit_price
FROM products

WHERE

USE sql_store;
SELECT *
FROM Customers
-- 引用字符串时，单引号''和双引号""都可以，但更推荐前者
WHERE state = 'VA' AND points > 1000

日期的比较

日期要按照字符串处理，但是可以比较

SELECT *
FROM Customers
WHERE birth_date > '1970-01-01'

SQL运算符

  • >
  • >=
  • <
  • <=
  • =

    Not equal signs

  • !=

  • <>
  • AND
  • OR
  • NOT

字符串运算

|| – 连接字符串
LENGTH – returns the length of a string
LOWER – converts a string to lowercase
UPPER – converts a string to uppercase

SELECT s.sContent as Content, e.eID as Episodes, e.eTitle as Title, e.eLink || "?t=" || s.sStart || "s" as Link
FROM Subtitles as s
JOIN Episodes as e ON s.sEP = e.eID
WHERE sContent LIKE '%普京%';

IN

SELECT *
FROM customers
WHERE state IN ('VA', 'FL', 'GA')
-- 等价于
WHERE state = 'VA' or state = 'FL' or state = 'GA'
-- 否定
WHERE state NOT IN ('VA', 'FL', 'GA')

BETWEEN

SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000
-- 等价于
WHERE points >= 1000 AND points <=3000

LIKE

SELECT *
FROM customers
WHERE last_name LIKE 'B%'
表示内容 标识符 正则（类比）
任意长度任意字符 % *
1个任意字符 _ . 
SELECT *
FROM customers
WHERE   address LIKE '%TRAIL%' OR 
        address LIKE '%AVENUE%' OR
        phone LIKE '%9'

REGEXP

SELECT *
FROM customers
WHERE last_name REGEXP 'field'
-- 等价于
WHERE last_name LIKE '%field%'

IS NULL

SELECT *
FROM customers
WHERE phone IS NULL
WHERE phone IS NOT NULL

ORDER BY

SELECT *
FROM customers
ORDER BY first_name DESC
ORDER BY state ASC, first_name DESC

升序/降序：ASC DESC
即使select了部分列，也可以用其他列的数据来进行排序，如下

SELECT first_name, last_name
FROM customers
ORDER BY birth_date

可以指定以选中的列排序，例如下段：(不推荐，不利于修改)

SELECT first_name, last_name, 10 AS points
FROM customers
ORDER BY 1, 2
-- 等价于
ORDER BY first_name, last_name

LIMIT

LIMIT 一定要写在最下面

只筛选结果的前3条

SELECT *
FROM customers
LIMIT 3

从第6条开始筛选三条 (6,6+3]

SELECT *
FROM customers
LIMIT 6, 3

连接

CROSS JOIN

返回Cartessian Join的结果（无匹配依据，返回全部可能）

Select * from student cross join module where student.sid=module.sid;

等价于

Select * from student, module where student.sid=module.sid;

内连接 INNER JOIN

连接多张表
INNER JOIN 可以直接简写为 JOIN

条件不能写WHERE，要写ON 

SELECT order_id, first_name, last_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id

注意：不能在SELECT语句中选中两个表中都有的attribute，应当写作

SELECT order_id, orders.customer_id, first_name, last_name

可以给表明简写

SELECT order_id, o.customer_id, first_name, last_name
FROM orders o
JOIN customers  c ON o.customer_id = c.customer_id

跨数据库连接

USE sql_store;
SELECT *
FROM order_items oi
JOIN sql_inventory.products p ON oi.product_id = p.product_id

等价于：

USE sql_inventory;
SELECT *
FROM sql_store.order_items oi
JOIN products p ON oi.product_id = p.product_id

自连接 SELF JOIN

USE sql_hr;
SELECT e.employee_id, e.first_name 'Employee', m.first_name 'Manager'
FROM employees e
JOIN employees m ON e.reports_to = m.employee_id

多表连接 Multiple JOIN

USE sql_store;

SELECT
    o.order_id, o.order_date,
    c.first_name, c.last_name,
    os.name AS status
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id
JOIN order_statuses os
    ON o.status = os.order_status_id

复合连接条件

USE sql_store;
SELECT *
FROM order_items oi
JOIN order_item_notes oin
    ON oi.order_id = oin.order_id
    AND oi.product_id = oin.product_id

隐式连接 Implict Join

USE sql_store;
SELECT *
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id

等价于

USE sql_store;
SELECT *
FROM orders o, customers c
WHERE o.customer_id = c.customer_id

如果不加上WHERE，则会变成 Cartesian product (CROSS JOIN)

外连接 OUTER JOIN

USE sql_store;

SELECT
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
JOIN orders o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id

以上为默认的(INNER) JOIN，只会选择同时在c和o表中出现的顾客。
对于OUTER JOIN，SQL有LEFT JOIN和RIGHT JOIN，使用LEFT JOIN时，所有左表的内容会被返回，反之亦然。
LEFT JOIN的完整形式是LEFT OUTER JOIN，一般可省略。

USE sql_store;

SELECT
    c.customer_id,
    c.first_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
ORDER BY c.customer_id

Multiple Tables

USE sql_store;

SELECT c.customer_id, c.first_name, o.order_id, sh.name 'Shipper'
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
LEFT JOIN shippers sh
    ON o.shipper_id = sh.shipper_id
ORDER BY c.customer_id

自外连接 SELF OUTER JOIN

USE sql_hr;

SELECT
    e.employee_id,
    e.first_name,
    m.first_name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.reports_to = m.employee_id

USING

如果JOIN的条件句ON是两个表的同名字段，则可以用USING

USE sql_store;

SELECT o.order_id, c.first_name
FROM orders o
JOIN customers c
    ON o.customer_id = c.customer_id

等价于

USE sql_store;

SELECT o.order_id, c.first_name
FROM orders o
JOIN customers c
    USING(customer_id)

GROUP BY

SELECT
    mID
        AS "Module ID",
    AVG(grade)
        AS "Average Grade"
FROM Grade
GROUP BY mID;

UNION

更新数据

UPDATE & SET

UPDATE Student
SET
    firstName = 'Johnathan',
    lastName = 'Creek'
WHERE sID = 1;

数据库正规化(Normalization)

请参考：Normalization of Database——数据库的正规化
1NF
2NF
3NF

标题：SQL 基础
作者：IKK
除转载和特殊声明外，所有文章采用 CC BY-NC-SA 4.0协议。
暂无评论

发送评论 编辑评论 


				

			

						

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
						

					

				

				

					

						

							

								
							

							
							
													

					

				

			

			
			

				

					
				

			

				

											

							
							
						

																					

							
							
						

										
					
											
						

	

		
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	

	

		
颜文字
Emoji
小恐龙
花!
	


									

			

			
			
		

	






上一篇
下一篇