一、SQL语句
结构化查询语言(Structured Query Language)简称SQL,这是一种特殊的语言,它专门用于数据库的操作。每一种数据库都支持SQL,但是他们之间会存在一些细微的差异,因此不同的数据库都存在自己的“方言”。
SQL语句不区分大小写(关键字推荐使用大写),它支持多行,并且需要使用
;
进行结尾!
SQL也支持注释,通过使用
--
或是#
来编写注释内容,也可以使用/*
来进行多行注释。
create database
创建数据库:
create database 数据库名
为了能够支持中文,我们在创建时可以设定编码格式:
CREATE DATABASE
IF NOT EXISTS 数据库名
DEFAULT CHARSET utf8
COLLATE utf8_general_ci;
utf8
是字符集,utf8_general_ci
是编码规则
drop database
删除一个数据库:
drop database 数据库名
create table 表名(列名 数据类型[列级约束条件],
列名 数据类型[列级约束条件],
...
[,表级约束条件])
用于字符串存储:
用于存储数字:
用于存储时间:
列级约束有六种:主键Primary key
、外键foreign key
、唯一 unique
、检查 check
(MySQL不支持)、默认default
、非空/空值 not null/ null
表级约束有四种:主键(PRIMARY KEY
)、外键、唯一(UNIQUE
)、检查(CHECK
)
为表增加外检约束条件的语法:
[CONSTRAINT <外键名>]
FOREIGN KEY 字段名 [,字段名2,…]
REFERENCES <主表名> 主键列1 [,主键列2,…]
外键名的作用是在报错时可以更好的检查,可以不写 说明:
在创建时设置
CREATE TABLE Employees (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
department_id INT,
FOREIGN KEY (department_id) REFERENCES Departments(id),
CHECK (id > 0)
);
创建后追加:
ALTER TABLE Orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES Customers(customer_id);
引用多个列时,被引用列的顺序和数据类型必须与被引用表格中的列定义相对应
ALTER TABLE 表名 ADD COLUMN 列名 类型(约束条件);
ALTER TABLE 表名 RENAME COLUMN 原列名 TO 新列名;
ALTER TABLE 表名 ALTER COLUMN 列名 类型(约束条件);
ALTER TABLE 表名 DROP COLUMN 被删除的列名;
ALTER TABLE teach DROP FOREIGN KEY 约束名字, DROP INDEX 约束名字;
查看外键约束名字:
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '数据库名字' AND TABLE_NAME = '表名' AND COLUMN_NAME = '列名' AND REFERENCED_TABLE_NAME IS NOT NULL;
DROP TABLE 表名[restrict|cascade]
restrict或cascade,默认是restrict,表示如果此列作为其他表的约束或视图引用到此列时,将无法删除,而cascade会强制连带引用此列的约束、视图一起删除,表同理
通过使用insert into
语句来向数据库中插入一条数据(一条记录):
INSERT INTO 表名 VALUES(值1, 值2, 值3)
如果插入的数据与列一一对应,那么可以省略列名,但是如果希望向指定列上插入数据,就需要给出列名:
INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2)
一次性向数据库中插入多条数据:
INSERT INTO 表名(列名1, 列名2) VALUES(值1, 值2), (值1, 值2), (值1, 值2)
我们可以通过update
语句来更新表中的数据:
UPDATE 表名 SET 列名=值,... WHERE 条件
注意,SQL语句中的等于判断是=
警告: 如果忘记添加WHERE
字句来限定条件,将使得整个表中此列的所有数据都被修改!
我们可以通过使用delete
来删除表中的数据:
DELETE FROM 表名
通过这种方式,将删除表中全部数据,我们也可以使用where
来添加条件,只删除指定的数据:
DELETE FROM 表名 WHERE 条件
单表查询是最简单的一种查询,我们只需要在一张表中去查找数据即可,通过使用select
语句来进行单表查询:
-- 指定查询某一列数据
SELECT 列名[,列名] FROM 表名
-- 会以别名显示此列
SELECT 列名 别名 FROM 表名
-- 查询所有的列数据
SELECT * FROM 表名
-- 只查询不重复的值
SELECT DISTINCT 列名 FROM 表名
可以添加where
字句来限定查询目标:
SELECT * FROM 表名 WHERE 条件
我们可以通过order by
来将查询结果进行排序:
SELECT * FROM 表名 WHERE 条件 ORDER BY 列名 ASC|DESC
使用ASC
表示升序排序
,使用DESC
表示降序排序
,默认为升序
。
我们也可以可以同时添加多个排序:
SELECT * FROM 表名 WHERE 条件 ORDER BY 列名1 ASC|DESC, 列名2 ASC|DESC
这样会先按照列名1进行排序,每组列名1相同的数据再按照列名2排序。
聚集函数一般用作统计,包括:
count([distinct]*)
统计所有的行数(distinct表示去重再统计,下同)count([distinct]列名)
统计某列的值总和sum([distinct]列名)
求一列的和(注意必须是数字类型的)avg([distinct]列名)
求一列的平均值(注意必须是数字类型)max([distinct]列名)
求一列的最大值min([distinct]列名)
求一列的最小值一般聚集函数是这样使用的:
SELECT count(distinct 列名) FROM 表名 WHERE 条件
通过使用group by
来对查询结果进行分组,它需要结合聚合函数一起使用:
SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名
我们还可以添加having
来限制分组条件:
SELECT sum(*) FROM 表名 WHERE 条件 GROUP BY 列名 HAVING 约束条件
我们可以通过limit
来限制查询的数量,只取前n个结果:
SELECT * FROM 表名 LIMIT 数量
我们也可以进行分页:
SELECT * FROM 表名 LIMIT 起始位置,数量
多表查询是同时查询的两个或两个以上的表,多表查询会提通过连接转换为单表查询。
SELECT * FROM 表1, 表2
直接这样查询会得到两张表的笛卡尔积,也就是每一项数据和另一张表的每一项数据都结合一次,会产生庞大的数据。
SELECT * FROM 表1, 表2 WHERE 条件
这样,只会从笛卡尔积的结果中得到满足条件的数据。
注意: 如果两个表中都带有此属性吗,需要添加表名前缀来指明是哪一个表的数据。
自身连接,就是将表本身和表进行笛卡尔积计算,得到结果,但是由于表名相同,因此要先起一个别名:
SELECT * FROM 表名 别名1, 表名 别名2
其实自身连接查询和前面的是一样的,只是连接对象变成自己和自己了。
JOIN ... ON ... //连接起来之后,表的拼接不是笛卡尔积,效率更高
外连接就是专门用于联合查询情景的,比如现在有一个存储所有用户的表,还有一张用户详细信息的表,我希望将这两张表结合到一起来查看完整的数据,我们就可以通过使用外连接来进行查询,外连接有三种方式:
inner join
进行内连接,只会返回两个表满足条件的交集部分:left join
进行左连接,不仅会返回两个表满足条件的交集部分,也会返回左边表中的全部数据,而在右表中缺失的数据会使用null
来代替(右连接right join
同理,只是反过来而已,这里就不再介绍了):我们可以将查询的结果作为另一个查询的条件,比如:
SELECT * FROM 表名 WHERE 列名 = (SELECT 列名 FROM 表名 WHERE 条件)
我们来再次尝试编写一下在最开始我们查找某教师所有学生的SQL语句。
庞大的数据库不可能由一个人来管理,我们需要更多的用户来一起管理整个数据库。
我们可以通过create user
来创建用户:
CREATE USER 用户名 identified by 密码;
也可以不带密码:
CREATE USER 用户名;
我们可以通过@来限制用户登录的登录IP地址,%
表示匹配所有的IP地址,默认使用的就是任意IP地址。
首先需要添加一个环境变量,然后我们通过cmd去登陆mysql:
login -u 用户名 -p
输入密码后即可登陆此用户,我们输入以下命令来看看能否访问所有数据库:
show databases;
我们发现,虽然此用户能够成功登录,但是并不能查看完整的数据库列表,这是因为此用户还没有权限!
我们可以通过使用grant
来为一个数据库用户进行授权:
grant all|权限1,权限2...(列1,...) on 数据库.表 to 用户 [with grant option]
其中all代表授予所有权限,当数据库和表为*
,代表为所有的数据库和表都授权。如果在最后添加了with grant option
,那么被授权的用户还能将已获得的授权继续授权给其他用户。
我们可以使用revoke
来收回一个权限:
revoke all|权限1,权限2...(列1,...) on 数据库.表 from 用户
视图本质就是一个查询的结果,不过我们每次都可以通过打开视图来按照我们想要的样子查看数据。既然视图本质就是一个查询的结果,那么它本身就是一个虚表,并不是真实存在的,数据实际上还是存放在原来的表中。
我们可以通过create view
来创建视图;
CREATE VIEW 视图名称(列名) as 子查询语句 [WITH CHECK OPTION];
WITH CHECK OPTION是指当创建后,如果更新视图中的数据,是否要满足子查询中的条件表达式,不满足将无法插入,创建后,我们就可以使用select
语句来直接查询视图上的数据了,因此,还能在视图的基础上,导出其他的视图。
通过drop
来删除一个视图:
drop view apptest
在数据量变得非常庞大时,通过创建索引,能够大大提高我们的查询效率,就像Hash表一样,它能够快速地定位元素存放的位置,我们可以通过下面的命令创建索引:
-- 创建索引
CREATE INDEX 索引名称 ON 表名 (列名)
-- 查看表中的索引
show INDEX FROM student
我们也可以通过下面的命令删除一个索引:
drop index 索引名称 on 表名
虽然添加索引后会使得查询效率更高,但是我们不能过度使用索引,索引为我们带来高速查询效率的同时,也会在数据更新时产生额外建立索引的开销,同时也会占用磁盘资源。
触发器就像其名字一样,在某种条件下会自动触发,在select
/update
/delete
时,会自动执行我们预先设定的内容,触发器通常用于检查内容的安全性,相比直接添加约束,触发器显得更加灵活。
触发器所依附的表称为基本表,当触发器表上发生select
/update
/delete
等操作时,会自动生成两个临时的表(new表和old表,只能由触发器使用)
比如在insert
操作时,新的内容会被插入到new表中;在delete
操作时,旧的内容会被移到old表中,我们仍可在old表中拿到被删除的数据;在update
操作时,旧的内容会被移到old表中,新的内容会出现在new表中。
CREATE TRIGGER 触发器名称 [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON 表名/视图名 FOR EACH ROW DELETE FROM student WHERE student.sno = new.sno
FOR EACH ROW表示针对每一行都会生效,无论哪行进行指定操作都会执行触发器!
通过下面的命令来查看触发器:
SHOW TRIGGERS
如果不需要,我们就可以删除此触发器:
DROP TRIGGER 触发器名称
当我们要进行的操作非常多时,比如要依次删除很多个表的数据,我们就需要执行大量的SQL语句来完成,这些数据库操作语句就可以构成一个事务!只有Innodb引擎支持事务,我们可以这样来查看支持的引擎:
SHOW ENGINES;
MySQL默认采用的是Innodb引擎,我们也可以去修改为其他的引擎。
事务具有以下特性:
我们通过以下例子来探究以下事务:
begin; #开始事务
...
rollback; #回滚事务
savepoint 回滚点; #添加回滚点
rollback to 回滚点; #回滚到指定回滚点
...
commit; #提交事务
-- 一旦提交,就无法再进行回滚了!