一、背景
数据库知识还是比较基础和重要的,大学毕业之后对这块的深入学习有所懈怠,工作中实践的时候发现基础比较薄弱,还是要着重练习,并且深入底层系统性的学习。在此过程中我列了一份MySQL学习计划,贴在这里,以供其他刚毕业的同学参考。学习过程也会记录在博客里,对以后回顾能有所帮助。
二、MySQL学习计划
6.7-6.20(两周)
SQL基础语法熟练
包括sql各种查询语句、建库建表、建立索引等
6.21-6.27
MySQL体系结构和InnoDB存储引擎
包括MySQL体系结构、MySQL表存储引擎等
6.28-7.4
索引与算法
包括InndoDB存储引擎索引、B+树索引、哈希算法等
7.5-7.11
锁和事务
包括InndoDB存储引擎中的锁、锁的算法、锁的问题、事务的实现、分布式事务等
7.12-7.18
MySQL性能调优
包括内存的重要性、硬盘对数据库性能的影响、不同的文件系统对数据库性能的影响
7.19-7.25
文件
包括参数文件、日志文件、pid文件、套接字文件、表结构定义文件、InndoDB存储引擎文件
7.26-8.1
表
包括InndoDB逻辑存储结构、InndoDB物理存储结构、InndoDB行记录格式、InndoDB数据页结构
8.2-8.8
表
包括约束、视图、分区表
8.9-8.15
备份与恢复
包括冷备份、热备份、逻辑备份、快照备份、复制
三、MySQL 基础语法回顾
数据库的安装初始化这些就不赘述了,下面列举一些常用命令和示例
一些最重要的 SQL 命令
- SELECT - 从数据库中提取数据
- UPDATE - 更新数据库中的数据
- DELETE - 从数据库中删除数据
- INSERT INTO - 向数据库中插入新数据
- CREATE DATABASE - 创建新数据库
- ALTER DATABASE - 修改数据库
- CREATE TABLE - 创建新表
- ALTER TABLE - 变更(改变)数据库表
- DROP TABLE - 删除表
- CREATE INDEX - 创建索引(搜索键)
- DROP INDEX - 删除索引
一、数据库层面
1、登录
mysql -u user_name -p 输入后会提示输入密码(user_name在我本机是root)
2、查看当前的数据库
show databases;
3、使用数据库
use database_name; database_name为某个数据库的名称
4、创建数据库
create database database_name; 例如,
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
5、删除数据库
drop database database_name;
二、表层面
1、查看当前数据库的表,需要选择数据库之后才能使用
show tables
如果没有选数据库,会出现以下错误提示
2、查看某个表中的所有列名(属性信息)
describe table_name;(也可以简写为:desc table_name;)
一系列操作如下图
3、也可以选择某一列数据
select column_name from table_name;
或者select * from table_name; 可以查看某个表中的所有数据,不过实际操作一般要加limit限制
4、创建表语句
举例
CREATE TABLE IF NOT EXISTS `runoob_tbl`( `runoob_id` INT UNSIGNED AUTO_INCREMENT, `runoob_title` VARCHAR(100) NOT NULL, `runoob_author` VARCHAR(40) NOT NULL, `submission_date` DATE, PRIMARY KEY ( `runoob_id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8;
其中 NOT NULL 是表示该字段不为NULL,PRIMARY KEY (`runoob_id`)表示runoob_id是该表的主键
5、重命名某张表
alter table table_name rename new_name;
6、向表中添加/删除列名(属性名)
alter table table_name add column_name type;
alter table table_name drop column_name;
对表级别增加或删除列的操作是会锁表的,执行时间和表的大小有关,我这张user_tab中列并不多,但是有一千万数据,导致增加一列耗时34s,删除一列耗时22s,这种操作在生产环境中也是比较耗时的,对于数量亿级别的大表,很有可能因为操作时间过久而无法新增列,此时业务需要考虑分库分表了。
7、修改表中的某个列
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;
可见,修改列名的操作也是十分耗时间的
8、查看表的索引
show index from <表名>;
可以看到,我的user_tab中有两个索引,其中id是主键索引,username是唯一索引
9、创建索引
CREATE INDEX indexName ON table_name (column_name);
此外,新增索引的方式也有修改表结构
ALTER table tableName ADD INDEX indexName(column_name)
或者创建表的时候直接指定。
10、删除索引
DROP INDEX [indexName] ON mytable;
一系列操作如下图
唯一索引:
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它与普通索引创建方式有所不同:
CREATE UNIQUE INDEX indexName ON mytable(username(length));
如果增加索引的列存在相同值,那么加唯一索引会失败
三、数据行层面
1、向表中添加一行记录
insert into table_name values(....); (括号里面的值必须要对应创建表时列的前后顺序,且用逗号隔开)
除此之外,还可以用这种形式:
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );
2、删除表中特定的行
delete from table_name where P;
P是一个条件,一般格式是:col_name = value,即属性等于某个值的一行,它用来筛选特定的行。省略where子句将会删除整个表的行。
3、修改/更新行
update table_name set col_name = new_val where P;
操作如下图:
MySQL查询
上面是一些常用命令的举例,下面进入稍复杂一些的sql查询
AND 和 OR
AND - 如果第一个条件和第二个条件都成立;
OR - 如果第一个条件和第二个条件中只要有一个成立;
and和or可以在where语句中使用,用来判断一些条件
eg:
ORDER BY
语句默认按照升序对记录进行排序。
ORDER BY
- 语句用于根据指定的列对结果集进行排序。DESC
- 按照降序对记录进行排序。ASC
- 按照顺序对记录进行排序。
//按更新时间倒序排列
SELECT * FROM user_tab order by update_time desc limit 10;
按更新时间正序排列
SELECT * FROM user_tab order by update_time asc limit 10;
IN
IN - 操作符允许我们在 WHERE 子句中规定多个值。
IN - 操作符用来指定范围,范围中的每一条,都进行匹配。IN取值规律,由逗号分割,全部放置括号中。
语法:SELECT "字段名"FROM "表格名"WHERE "字段名" IN ('值一', '值二', ...);
例子见上上张图。
NOT
NOT - 操作符总是与其他操作符一起使用,用在要过滤的前面。
select * from user_tab where not id = 2 order by update_time desc limit 5;
eg:
UNION
UNION - 操作符用于合并两个或多个 SELECT 语句的结果集。此类语句在业务中应该尽量少用,关联查询效率较低,比较好的办法是查到A表的某一个值,再去B表中去查询
-- 列出所有在中国表(Employees_China)和美国(Employees_USA)的不同的雇员名
SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA
-- 列出 meeting 表中的 pic_url,
-- station 表中的 number_station 别名设置成 pic_url 避免字段不一样报错
-- 按更新时间排序
SELECT id,pic_url FROM meeting UNION ALL SELECT id,number_station AS pic_url FROM stati
AS
as - 可理解为:用作、当成,作为;别名
一般是重命名列名或者表名。
语法:select column_1 as 列1,column_2 as 列2 from table as 表
SELECT * FROM Employee AS emp
-- 这句意思是查找所有Employee 表里面的数据,并把Employee表格命名为 emp。
-- 当你命名一个表之后,你可以在下面用 emp 代替 Employee.
-- 例如 SELECT * FROM emp.
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
-- 列出表 Orders 字段 OrderPrice 列最大值,
-- 结果集列不显示 OrderPrice 显示 LargestOrderPrice
-- 显示表 users_profile 中的 name 列
SELECT t.name from (SELECT * from users_profile a) AS t;
-- 表 user_accounts 命名别名 ua,表 users_profile 命名别名 up
-- 满足条件 表 user_accounts 字段 id 等于 表 users_profile 字段 user_id
-- 结果集只显示mobile、name两列
SELECT ua.mobile,up.name FROM user_accounts as ua INNER JOIN users_profile as up ON ua.id = up.user_id;
JOIN
用于根据两个或多个表中的列之间的关系,从这些表中查询数据。
-
JOIN
: 如果表中有至少一个匹配,则返回行 -
INNER JOIN
:在表中存在至少一个匹配时,INNER JOIN 关键字返回行。 -
LEFT JOIN
: 即使右表中没有匹配,也从左表返回所有的行 -
RIGHT JOIN
: 即使左表中没有匹配,也从右表返回所有的行 -
FULL JOIN
: 只要其中一个表中存在匹配,就返回行
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName;
SQL 函数
COUNT
COUNT 让我们能够数出在表中有多少行被选出来。
语法:SELECT COUNT("字段名") FROM "表格名";
-- 获取user表有多少行。
select count(*) from user_tab;
MAX
MAX 函数返回一列中的最大值。NULL 值不包括在计算中。
语法:SELECT MAX("字段名") FROM "表格名"
-- 列出表 user表中 字段 id 列最大值,
select MAX(id) from user_tab;
总结
以上回顾了一些MySQL常用的语法和关键词,自己联系和借鉴了一些例子,去帮助自己回顾和熟悉MySQL的基础语法,更加复杂的sql查询需要在实际中多加练习,下一章节会更加深入去学习MySQL。参考资料《MySQL技术内幕:InnoDB存储引擎》。