数据库(database) - 保存有组织的数据的容器(通常是一个文件或一组文件)。
数据表(table) - 某种特定类型数据的结构化清单。
模式(schema) - 关于数据库和表的布局及特性的信息。模式定义了数据在表中如何存储,包含存储什么样的数据,数据如何分解,各部分信息如何命名等信息。数据库和表都有模式。
列(column) - 表中的一个字段。所有表都是由一个或多个列组成的。
行(row) - 表中的一个记录。
主键(primary key) - 一列(或一组列),其值能够唯一标识表中每一行。
SQL(Structured Query Language),标准 SQL 由 ANSI 标准委员会管理,从而称为 ANSI SQL。各个 DBMS 都有自己的实现,如 PL/SQL、Transact-SQL 等。
SQL 语法结构包括:
子句 - 是语句和查询的组成成分。(在某些情况下,这些都是可选的。)
表达式 - 可以产生任何标量值,或由列和行的数据库表
谓词 - 给需要评估的 SQL 三值逻辑(3VL)(true/false/unknown)或布尔真值指定条件,并限制语句和查询的效果,或改变程序流程。
查询 - 基于特定条件检索数据。这是 SQL 的一个重要组成部分。
语句 - 可以持久地影响纲要和数据,也可以控制数据库事务、程序流程、连接、会话或诊断。
-- 一行 SQL 语句
UPDATE user SET username='robot', password='robot' WHERE username = 'root';
-- 多行 SQL 语句
UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';
## 注释1
-- 注释2
/* 注释3 */
数据定义语言(Data Definition Language,DDL)是 SQL 语言集中负责数据结构定义与数据库对象定义的语言。
DDL 的主要功能是 定义数据库对象。
DDL 的核心指令是 CREATE、ALTER、DROP。
数据操纵语言(Data Manipulation Language, DML)是用于数据库操作,对数据库其中的对象和数据运行访问工作的编程语句。
DML 的主要功能是 访问数据,因此其语法都是以读写数据库为主。
DML 的核心指令是 INSERT、UPDATE、DELETE、SELECT。这四个指令合称 CRUD(Create, Read, Update, Delete),即增删改查。
事务控制语言 (Transaction Control Language, TCL) 用于 管理数据库中的事务 。这些用于管理由 DML 语句所做的更改。它还允许将语句分组为逻辑事务。
TCL 的核心指令是 COMMIT、ROLLBACK 。
数据控制语言 (Data Control Language, DCL) 是一种可 对数据访问权进行控制的指令,它可以控制特定用户账户对数据表、查看表、预存程序、用户自定义函数等数据库对象的控制权。
DCL 的核心指令是 GRANT、REVOKE。
DCL 以控制用户的访问权限为主,因此其指令作法并不复杂,可利用 DCL 控制的权限有: CONNECT、SELECT、INSERT、UPDATE、DELETE、EXECUTE、USAGE、REFERENCES。
根据不同的 DBMS 以及不同的安全性实体,其支持的权限控制也有所不同。
增删改查,又称为 CRUD,数据库基本操作中的基本操作。
INSERT INTO 语句用于向表中插入新记录。
INSERT INTO user
VALUES (10, 'root', 'root', 'xxxx@163.com');
INSERT INTO user(username, password, email)
VALUES ('admin', 'admin', 'xxxx@163.com');
INSERT INTO user(username)
SELECT name
FROM account;
UPDATE 语句用于更新表中的记录。
UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';
DELETE 语句用于删除表中的记录。
TRUNCATE TABLE 可以清空表,也就是删除所有行。
DELETE FROM user
WHERE username = 'robot';
TRUNCATE TABLE user;
SELECT 语句用于从数据库中查询数据。
DISTINCT 用于返回唯一不同的值。它作用于所有列,也就是说所有列的值都相同才算相同。
LIMIT 限制返回的行数。可以有两个参数,第一个参数为起始行,从 0 开始;第二个参数为返回的总行数。
ASC :升序(默认)
DESC :降序
SELECT prod_name
FROM products;
SELECT prod_id, prod_name, prod_price
FROM products;
ELECT *
FROM products;
SELECT DISTINCT
vend_id FROM products;
-- 返回前 5 行
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;
-- 返回第 3 ~ 5 行
SELECT * FROM mytable LIMIT 2, 3;
子查询的子查询
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE order_num IN (
SELECT order_num
FROM orderitems
WHERE prod_id = 'RGAN01'
)
);
SELECT * FROM Customers
WHERE cust_name = 'Kids Place';
UPDATE Customers
SET cust_name = 'Jack Jones'
WHERE cust_name = 'Kids Place';
DELETE FROM Customers
WHERE cust_name = 'Kids Place';
SELECT *
FROM products
WHERE vend_id IN ('DLL01', 'BRS01');
SELECT *
FROM products
WHERE prod_price BETWEEN 3 AND 5;
SELECT prod_id, prod_name, prod_price
FROM products
WHERE vend_id = 'DLL01' AND prod_price <= 4;
SELECT prod_id, prod_name, prod_priceFROM productsWHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
SELECT *
FROM products
WHERE prod_price NOT BETWEEN 3 AND 5;
SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '%bean bag%';
SELECT prod_id, prod_name, prod_price
FROM products
WHERE prod_name LIKE '__ inch teddy bear';
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM customers c1, customers c2
AND c2.cust_contact = 'Jim Jones';
SELECT *
FROM Products
NATURAL JOIN Customers;
SELECT customers.cust_id, orders.order_num
FROM customers LEFT JOIN orders
ON customers.cust_id = orders.cust_id;
右连接(RIGHT JOIN)
SELECT customers.cust_id, orders.order_num
FROM customers RIGHT JOIN orders
ON customers.cust_id = orders.cust_id;
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM customers
WHERE cust_name = 'Fun4All';
🔔 注意:不同数据库的函数往往各不相同,因此不可移植。本节主要以 Mysql 的函数为例。
其中, SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。
SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')
mysql> SELECT NOW();
2018-4-14 20:25:11
AVG() 会忽略 NULL 行。
使用 DISTINCT 可以让汇总函数值汇总不同的值。
SELECT AVG(DISTINCT col1) AS avg_colFROM mytable
可以按多个列进行排序,并且为每个列指定不同的排序方式
指定多个列的排序方向
SELECT *
FROM products
ORDER BY prod_price DESC, prod_name ASC;
GROUP BY
GROUP BY 子句将记录分组到汇总行中。
GROUP BY 为每个组返回一个记录。
GROUP BY 通常还涉及聚合:COUNT,MAX,SUM,AVG 等。
GROUP BY 可以按一列或多列进行分组。
GROUP BY 按分组字段进行排序后,ORDER BY 可以以汇总字段来进行排序。
SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name;
SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY cust_name DESC;
使用 WHERE 和 HAVING 过滤数据
SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;
DDL 的主要功能是定义数据库对象(如:数据库、数据表、视图、索引等)。
CREATE DATABASE test;
DROP DATABASE test;
USE test;
CREATE TABLE user (
id int(10) unsigned NOT NULL COMMENT 'Id',
username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用户名',
password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密码',
email varchar(64) NOT NULL DEFAULT 'default' COMMENT '邮箱')
COMMENT='用户表';
CREATE TABLE vip_user AS
SELECT * FROM user;
DROP TABLE user;
ALTER TABLE userADD age int(3);
ALTER TABLE userDROP COLUMN age;
ALTER TABLE `user`MODIFY COLUMN age tinyint;
ALTER TABLE userADD PRIMARY KEY (id);
ALTER TABLE userDROP PRIMARY KEY;
CREATE VIEW top_10_user_view AS
SELECT id, username
FROM user
WHERE id < 10;
DROP VIEW top_10_user_view;
通过索引可以更加快速高效地查询数据。
用户无法看到索引,它们只能被用来加速查询。
更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间,这是由于索引本身也需要更新。因此,理想的做法是仅仅在常常被搜索的列(以及表)上面创建索引。
唯一索引表明此索引的每一个索引值只对应唯一的数据记录。
CREATE INDEX user_indexON user (id);
CREATE UNIQUE INDEX user_indexON user (id);
ALTER TABLE userDROP INDEX user_index;
创建表时使用约束条件:
CREATE TABLE Users (
Id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增Id',
Username VARCHAR(64) NOT NULL UNIQUE DEFAULT 'default' COMMENT '用户名',
Password VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '密码',
Email VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT '邮箱地址',
Enabled TINYINT(4) DEFAULT NULL COMMENT '是否有效',
PRIMARY KEY (Id))
ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
-- 开始事务START TRANSACTION;-- 插入操作 A
START TRANSACTION;
-- 插入操作 AINSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');
-- 创建保留点 updateA
SAVEPOINT updateA;
-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');
-- 回滚到保留点 updateA
ROLLBACK TO updateA;
-- 提交事务,只有操作 A 生效
COMMIT;
USE mysql;SELECT user FROM user;
CREATE USER myuser IDENTIFIED BY 'mypassword';
UPDATE user SET user='newuser' WHERE user='myuser';
FLUSH PRIVILEGES;
DROP USER myuser;
SHOW GRANTS FOR myuser;
GRANT SELECT, INSERT ON *.* TO myuser;
REVOKE SELECT, INSERT ON *.* FROM myuser;
SET PASSWORD FOR myuser = 'mypass';
DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
DECLARE c int;
if a is null then set a = 0;
end if;
if b is null then set b = 0;
end if;
set sum = a + b;
END;
DELIMITER;
set @b=5;
call proc_adder(2,@b,@s);
select @s as sum;
DELIMITER $
CREATE PROCEDURE getTotal()
BEGIN
DECLARE total INT;
-- 创建接收游标数据的变量
DECLARE sid INT;
DECLARE sname VARCHAR(10);
-- 创建总数变量
DECLARE sage INT;
-- 创建结束标志变量 DECLARE done INT DEFAULT false;
-- 创建游标
DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;
-- 指定游标循环结束时的返回值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
SET total = 0;
OPEN cur;
FETCH cur INTO sid, sname, sage;
WHILE(NOT done)
DO
SET total = total + 1;
FETCH cur INTO sid, sname, sage;
END WHILE;
CLOSE cur;
SELECT total;
END $
DELIMITER ;
-- 调用存储过程
call getTotal();
触发器是一种与表操作有关的数据库对象,当触发器所在表上出现指定事件时,将调用该对象,即表的操作事件触发表上的触发器的执行。
可以使用触发器来进行审计跟踪,把修改记录到另外一张表中。
MySQL 不允许在触发器中使用 CALL 语句 ,也就是不能调用存储过程。
当触发器的触发条件满足时,将会执行 BEGIN 和 END 之间的触发器执行动作。
🔔 注意:在 MySQL 中,分号 ; 是语句结束的标识符,遇到分号表示该段语句已经结束,MySQL 可以开始执行了。因此,解释器遇到触发器执行动作中的分号后就开始执行,然后会报错,因为没有找到和 BEGIN 匹配的 END。
这时就会用到 DELIMITER 命令(DELIMITER 是定界符,分隔符的意思)。它是一条命令,不需要语句结束标识,语法为:DELIMITER new_delemiter。new_delemiter 可以设为 1 个或多个长度的符号,默认的是分号 ;,我们可以把它修改为其他符号,如 $ - DELIMITER $ 。在这之后的语句,以分号结束,解释器不会有什么反应,只有遇到了 $,才认为是语句结束。注意,使用完之后,我们还应该记得把它给修改回来。
提示:为了理解触发器的要点,有必要先了解一下创建触发器的指令。
CREATE TRIGGER 指令用于创建触发器。
语法:
CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGIN
trigger_statements
END;
DELIMITER $
CREATE TRIGGER `trigger_insert_user`
AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
INSERT INTO `user_history`(user_id, operate_type, operate_time)
VALUES (NEW.id, 'add a user',
now());
END $
DELIMITER ;
SHOW TRIGGERS;
DROP TRIGGER IF EXISTS trigger_insert_user;