MySQL crash course reading notes

记录一些在读这本书《mysql 必知必会》时一些重要的概念。

1. 了解数据库

• database
• table(column, datatype, row)
• row == record
• schema(模式): 关于数据库和表的布局及特性的信息
• primary key
• 表中任何列都可以作为主键
  • 任意两行都不具有相同的主键值
  • 每个行都必须具有一个主键值 (主键列不允许 NULL 值)
• sql(structured query language): 结构化查询语言

2. MySQL 简介

MySQL 命令行登录

mysql -u root -p

3. 使用 MySQL

• 使用数据库 = use database_name;
• 显示数据库 = show databases;
• 获得数据库内表的列表 = show tables;
• 显示表列 = show columns from table_name;
• 自增量 = auto_increment

4. 检索数据

mysql 命令行必须加上分号来结束 sql 语句。 sql 语句不区分大小写，但是对关键字进行大写更易于阅读。

• 检索单个列

  select prod_name
  from table_name;
  

• 检索多个列 = 在 select 后给出多个列名，列名间有逗号。

  select col1, col2, col3
  from table_name;
  

• 检索所有列 (最后不要随便使用*)

  select *
  from table_name;
  

• distinct 关键字

• limit 关键字 (第一条记录号为 0)

  select col_name
  from table_name
  limit 5; // 指示 mysql 返回不多于 5 行
  

  select col_name
  from table_name
  limit 5, 5; // 从第 5 行开始检索 5 行 (不包括开始行)
  

• 完全限定表名引用列

  select table_name.col_name
  from table_name;
  

5. 排序检索 (order by)

order by 子句位于 from 子句之后。

• clause(子句)

  select col_name
  from table_name
  order by col_name, col_name2;
  

• order by 排序方向 (desc, asc)

  select col_name
  from table_name
  order by col_name asc/desc;
  

  // 实际上 asc 没什么用，默认是升序的。

• 使用order by和limit的组合，得到一个列中最高或最低的值

  select col_name
  from table_name
  order by col_name desc
  limit 1;
  

6. 过滤数据 (where)

• where clause 在 from clause 之后
• 同时使用where和order by时，order by 应该在 where 之后
• where 子句操作符
  • =
  • <> / !=
  • <
  • <=
  • >
  • >=
  • between = 在两个值之间，这两个值使用AND分隔
• is null = 检测空值

10. 计算字段

• as = 使用别名

  select col_name as ano.er_name
  

11. 数据处理函数

• upper(col_name) = 将文本转换为大写
• 很多日期处理函数
• abs()
• mod()
• rand()
• sqrt()
• round(col_name, a) = a 是小数点位数

12. 汇总数据

• aggregate function
  • avg() = 返回某列平均值
  • count() = 返回某列行数
  • max() = 返回某列最大值
  • min() = 返回某列最小值
  • sum() = 返回某列值之和

13. 分组数据 (group by / having)

• group by = 在 where 后，order by 之前

14. subquery

• 在 select 语句中，子查询总是从内向外处理

15. 联结表 (join)

• 外键 (foreigh key) = 为某个表的一列，它包含另一个表的主键值，定义了两个表的关系。
  • 好处 1 = 减少信息的重复
• 在引用的列可能出现二义性时，必须使用完全限定列名 (table_name.col_name)

16. 创建高级联结

1. 使用表别名 (AS)

• 缩短 sql 语句

17. update and delete

• don’t ignore where clause -updateTtable_name set xxx=‘xxx where xxx+08 = 00’

• delete a row

  delete from table_name
  where col_name = xxx;
  

18. alter table

• add a column

  alter table vendors
  add vend_phone char(20);
  

• delete table = drop table table_name;

19. view

• create view = create view view_name
• drop view = drop view view_name;

20. 其他

1. mysql 注释

   #
   --
   /* */
   

2. 在一行代码中插入多条数据

   insert into table_name values (), (), ...
   

3. mysql 修改表名 (两种方法)

   • rename

   RENAME TABLE table_name TO ano.er_table_name;
   

   • ALTER TABLE table_name RENAME TO another_name

4. replace 更改数据

   UPDATE table_name
   SET field_name = REPLACE(field_name, from_data, to_data)
   where xxx;
   

5. set 关键字更新列 (用逗号连接多个)

   set col_name=new_data, col_name2=new_data2...
   

6. 创建表

   CREATE TABLE IF EXISTS table_name
   (xxx,
   xxx,
   xxx,
   PRIMARY KEY(pid));
   

7. substr()

   substr(字符串，起始位置，长度） 起始位置 =截取的子串的起始位置（注意：字符串的第一个字符的索引是 1）。值为正时从字符串开始位置 开始计数，值为负时从字符串结尾位置开始计数。 长度 =截取子串的长度

8. add column in table

   ALTER TABLE table_name
   ADD column_name datatype
   

9. primary key and unique index

   主键约束比唯一索引约束严格，当没有设定主键时，非空唯一索引自动称为主键。对于主键和唯一索引的一些区别主要如下：

   • 主键不允许空值，唯一索引允许空值
   • 主键只允许一个，唯一索引允许多个

10. in 和 exists

SELECT * FROM A WHERE id IN (SELECT id FROM B);
-- 执行顺序：子查询 B -> 生成临时表 -> 主查询 A 从临时表查

SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE A.id = B.id);
-- 执行顺序：主查询 A 每行 -> 执行子查询验证 -> 返回结果

解释：先执行外查询，对外查询的每一行，执行子查询进行验证。

MySQL 必备神器–mycli(补全)

• Install(ubuntu): sudo apt-get install mycli
• Login(cli): mycli -u name -p passward