什么是SQL:结构化查询语言简称SQL,是一种数据库查询语言。
- DDL (Data Definition Language) 数据定义语言,用来定义数据库对象:数据库、表、列
- DML (Data Mainpulation Language) 数据操作语言,用来
- DQL (Data Query Language)
- DCL (Data Control Language)
作用:用于存取数据、查询、更新和管理关系数据库系统。
什么是MySQL:MySQL是一个关系型数据库管理系统,由瑞典MYSQLAB公司开发,属于Oracle旗下的产品。MySQL是最流行的关系型数据库管理系统之一,在WEB应用方面,MySQL是最好的 RDBMS(RelationalDatabaseManagementSystem,关系数据库管理系统)应用软件之一。在Java企业级开发中非常常用,因为MySQL是开源免费的,并且方便扩展。
数据库三大范式
- 第一范式:每个列都不可以在拆分
- 在第一范式的基础上,非主键完全依赖于主键。而不时能依赖于主键的一部分。
- 在第二范式的基础上,非主键列只能依赖于主键,不依赖于其他非主键。
在设计数据库结构的时候,要尽量遵守三范式,如果不遵守,必须有足够的理由,比如性能,事实上我们经常会为了性能而妥协数据库的设计。
多表查询
- 表连接
-
- 内连接
- 外连接(左连接,子链接)
- 子查询
- 总结:
-
- 表连接问题:笛卡尔积,数据越多,笛卡尔积越大,性能越慢。
- 子查询问题:子查询需要建立虚拟表,小号内存空间,消耗性能比表连接还大。
- 建议:大多数情况一般表连接性能是优于子查询的,但不绝对,如果笛卡儿足够大,性能比子查询差。
语法
- 忘记数据库密码
# 打开mysql的 my.ini文件,在文件的末尾最后一行添加"skip-grant-tables" # 在服务重启mysql #通过命令行进入mysql的bin目录,输入:mysql -u root -p 回车提示输入密码的时候, 在回车即可数据库。 #执行 use mysql #执行 update user set authentication_string=PASSWORD("root") where user = 'root' #打开MYSQL目录下的my.ini文件,删除最后一行的"skip-grant-tables",保存关闭文件, #重启数据库。
- 连接数据库
# 账号和密码要紧跟-u-p,不能有空格 mysql -u账号 -p密码 # 回车后输入密码 mysql -u 账号 -p # 退出MYSQL exit quit
- 数据库语句
# 查看所有数据库 SHOW DATABASES; # 创建一个数据库 CREATE DATABASE 数据库名; # 删除数据库 DROP DATABASE 数据库名; # 使用数据库 USE 数据库名;
- 表语句
# 查看所有的表 SHOW TABLES; # 主键约束(PRIMARY KEY):没有明确的概念定义,是唯一索引的一种,不能重复,一个表只有一个主键。 # 自动增加(AUTO_INCREMENT):自动增加(需要和主键PRIMARY KEY一起使用)。 # 非空约束(NOT NULL):要求被装饰的字段非空。 # 外键约束(FOREIGN KEY):用来在表与表的数据之间建立连接,它可以是一列或者多列。 # 唯一约束(UNIQUE KEY):指所有记录中的字段的值不能重复出现,可以联合非空UNIQUE(字段1,字段2) # AS 别名或者连接语句的操作符 # 创建一个表,拥有主键 CREATE TABLE 表名(id INT, name VARCHAR(10), PRIMARY KEY(id)); # 创建另一个表,拥有主键,并包含前一个表的外键约束,以及唯一约束 CREATE TABLE 表名(id INT, name VARCHAR(10), PRIMARY KEY(id), FOREING KEY(id) REFERENCES 表名(id), UNIQUE(name)); # 直接将查询结果导入或复制到,一个新建的表 CREATE TABLE 表名1 SELECT * FROM 表名2; CREATE TABLE 表名1 AS SELECT * FROM 表名2; # 将一个已存在的数据结构克隆到,一个新创建的表 CREATE TABLE 表1 LIKE 表2存在; # 创建一个临时表,各种方式 # 临时表将在你连接MYSQL期间存在,当断开连接时,MYSQL将自动删除表并释放所用空间,也可手动删除 CREATE TEMPORARY 表名(id INT, name VARCHAR(10)); CREATE TEMPORARY 表名 SELECT * FROM 表名; # 删除存在的表 DROP TABLE IF EXISTS 表名; # 更改存在表的名称 ALTER TABLE 原表名 RENAME 更改的表名; RENAME TABLE 更改的表名 TO 表名; # 查看表的结构 DESC 表名; DESCRIBE 表名; SHOW COLUMNS IN 表名; SHOW COLUMNS FROM 表名; EXPLAIN 表名; # 查看表的创建语句 SHOW CREATE TABLE 表名;
- 表结构
# 添加表字段 ALTER TABLE 表名 ADD age VARCHAR(2); # 删除表字段 ALTER TABLE 表名 DROP age; # 更改表字段和表字段属性 ALTER TABLE 表名 CHANGE age age_change INT; # 只更改字段属性 ALTER TABLE 表名 MODIFY age VARCHER(7); # 查询所有表的信息 SHOW TABLE STATUS;
- 表数据
# 增加表数据 INSERT INTO VALUES(...),(...),(...); # 增加查询之后的表数据 INSERT INTO 表名 SELECT * FROM 表名; # 删除表数据 DELETE FROM 表名 WHERE id = 2; # 更改表数据 UPDATE 表名 SET name = 'UPDATE' WHERE id = 1; # 知识点提示 # SELECT DISTINCT * FROM 表名 WHERE 限制条件 GROUP BY 分组依据 HAVING 过滤条件 # ORDER BY LIMIT 展示条数 OFFSET 跳过的条数 # 以上的关键字使用顺序不能错误,否则会产生错误信息 # DISTINCT 去重,注意如果是多个表字段去重,只有每个表字段都相同才会认为相同 # * 代表通配符,会返回所有的字段信息数据 # WHERE 语句用来查询包含查询条件,对原始记录过滤 # HAVING 语句也是用来包含查询条件,但是HAVING是对WHERE查询分组之后的记录继续过滤 # ORDER BY 排序默认正序也就是升序,DESC表示反序就是降序 # LIMIT 属性来设定返回的记录数,一般用于列表的分页 # OFFSET 属性来设定跳过的返回的记录数,一般配合LIMIT # 查找表数据 SELECT * FROM 表名; # 查询去除重复数据之后的表数据 SELECT DISTINCT 字段名 FROM 表名; # 根据表字段查找表数据 SELECT * FROM 表名 WHERE 字段名 # 查找name为zeta并且id大于30的表数据 SELECT * FROM test WHERE name = 'zeta' HAVING id > 30; # 根据id分组查询age小于20的表数据 SELECT * FROM 表名 GROUP BY id HAVING age < 20; # 查找根据name排序之后的表数据 SELECT * FROM test ORDER BY name; # 查找根据name反序排序之后的表数据 SELECT * FROM test ORDER BY name DESC; # 查找先根据name排序,再根据age排序之后的表数据 SELECT * FROM test ORDER BY name, age; # 查找先根据age反序,再根据name排序之后,年龄最大的前三位的表数据 SELECT * FROM test ORDER BY age DESC, name LIMIT 3; # 查找先根据age反序,再根据name排序之后,年龄最大的前三位,并跳过第一条的表数据 SELECT * FROM test ORDER BY age DESC, name LIMIT 3 OFFSET 1; SELECT * FROM test ORDER BY age DESC, name LIMIT 1,3; # 使用正则查询name表字段中包含字母g的表数据 SELECT * FROM test WHERE name regexp '.*[g]+.*';
- 表查询 ----- 连接查询
# ======================================================= # 为了方便大家更为直观的看到连接查询的效果,我再重新建一个用于连接的表 CREATE TABLE test_join(id INT, join_name VARCHAR(20), join_desc VARCHAR(100)); INSERT INTO test_join VALUES (10, 'join_a', 'desc_a'), (11, 'join_b', 'desc_b'), (10, 'gamma', 'join_gamma'); # ======================================================= # 内连接查询为什么会有三种 # 第一种为不设置的别名的写法 # 第二种为简写AS 设置别名的写法 # 第三种为有AS关键字设置别名的写法 # 内连接查询 # 注意JOIN连接默认使用内连接 SELECT * FROM test INNER JOIN test_JOIN ON test.id = test_join.id; SELECT * FROM test m INNER JOIN test_join ON m.id = n.id; SELECT * FROM test AS m INNER JOIN test_inert AS n ON m.id = n.id; # 左外连接查询 SELECT * FROM test m LEFT JOIN test_join n ON m.id = n.id; # 左外连接查询,左表独有数据 SELECT * FROM test m LEFT JOIN test_join n ON m.id = n.id WHERE n.id IS NULL; # 右外连接查询 SELECT * FROM test m RIGHT JOIN test_join n ON m.id = n.id; # 右外连接查询,右表独有数据 SELECT * FROM test m RIGHT JOIN test_join n ON m.id = n.id WHERE m.id IS NULL; # 交叉连接查询,又叫笛卡尔连接 SELECT * FROM test CROSS JOIN test_join; SELECT * FROM test,test_join; # 全连接查询 # UNION默认不返回重复的数据,UNION ALL则会返回重复的数据 SELECT id,name FROM test UNION SELECT id,join_name FROM test_join; SELECT id,name FROM test UNION ALL SELECT id,join_name FROM test_join;
- 键
# 添加主键 ALTER TABLE test ADD PAIMARY KEY (id); # 删除主键 ALTER TABLE test DROP PRIMARY KEY; # 添加外键 ALTER TABLE test_key ADD CONSTRAINT fk_id FOREIGN KEY (id) REFERENCES test_foreign_key(id); # 删除外键
- 视图
# 创建视图 CREATE VIEW v AS SELECT id, name FROM test; CREATE VIEW sv(sid, sname) AS SELECT id, name FROM test; # MYSQL视图规定FROM关键字后后面不能包含子查询 # 修改了视图对基表数据会有影响,反之,修改了基表数据对视图也会有影响 # 查看创建视图语句 SHOW CREATE VIEW v; # 查看视图结构 DESC v; DESCRIBE v; SHOW COLUMNS IN v; SHOW COLUNNS FROM v; EXPLAIN v; # 查看视图数据 SELECT * FROM v; # 修改视图 CREATE OR REPLACE VIEW v AS SELECT name,age FROM test; ALTER VIEW v AS SELECT age FROM test; # 删除视图
事务
- 事务指的是满足ACID特性的一组操作,可以通过commit提交一个事务,也可以使用Rollback进行回滚。
ACID
- 原子性:事务被视为不可分割的最小单元,事务的所有操作要么是全部提交成功,要么全部失败回滚。回滚可以用日志来实现,日志记录着事务所执行的修改操作,在回滚时反向执行这些修改操作即可。
- 一致性:数据库在事务执行前后都保持一致性状态,在一致性状态下,所有事务对一个数据的读取结果都是相同的。
- 隔离性:一个事务所做的修改在最终提交以前,对其他事务时不可见的。
- 持久性:一旦事务提交,则其所作的修改会永远保存到数据库中,即使系统发生奔溃,事务执行的结果也不能丢失,可以通过数据库备份和恢复来实现,在系统发生奔溃时,使用备份的数据库进行数据恢复。
-
- 只有在满足一致性,事物的执行结果才是正确的。
- 在无并发的情况下,事务串行执行,隔离性一定能够满足。此时只要能满足原子性,就一定能满足一致性。
- 在并发的情况下,多个事务并发执行,事务不仅要满足原子性,还需要满足隔离性,才能满足一致性。
- 事务满足持久化是为了能应对数据库崩溃的情况。