安装

  1. brew install mysql

环境变量

将mysql加入系统环境变量
进入/usr/local/mysql/bin,查看此目录下是否有mysql
执行vim ~/.bash_profile,在该文件中添加mysql/bin的目录,PATH=$PATH:/usr/local/mysql/bin
source ~/.bash_profile

重置密码

  1. mysql> USE mysql
  2. mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin123';
  3. mysql> FLUSH PRIVILEGES;

命令

  1. mysql -u root -p # 用mysql的root账号密码登录
  2. SET PASSWORD FOR 'root'@'localhost' = PASSWORD('admin123'); # 重置账号密码
  3. exit # 退出

挂起出错退出

  • 在末尾添加';(也许是不成对的单引号导致的)

资源

Mac下安装与配置MySQL
MySQL文档

深入浅出

http://www.headfirstlabs.com/books/hfsql/

https://resources.oreilly.com/examples/9780596526849/tree/master/tables

  • 数据库和表的名称不一定要大写

  • 数据库和表的名称里不可出现空格，用下划线取代空格

  • sql 能控制多位用户同时访问表的行为，能够授予或撤销对整个数据库的访问权

  • sql 本身不区分大小写，即命令小写也可以，但是命令大写是良好的 sql 编程习惯

数据库操作

  1. 连接 sql
  1. mysql -u root -p
  1. 创建一个数据库：CREATE DATABASE
  1. mysql> CREATE DATABASE gregs_list;
  1. 告诉 RDBMS 使用刚刚创建的数据库：USE
  1. mysql> USE gregs_list;

表操作

  1. 设定表：CREATE TABLE，需要知道 COLUMN NAMES 和 DATA TYPES

  • VARCHAR: 可变动字符(VARiable CHRacter),用于保存以文本格式存储的信息

  • 没有在 INSERT 语句中被赋值的列默认为 NULL

  • NULL 是未定义的值，它不等于零，也不等于空值。值可以是 NULL，但绝非等于 NULL。

  • 在考虑列不该为 NULL 时，主要考虑列是否会用于后继搜索或者列是否具有唯一性

  • 可以把列修改为不接受 NULL 值，这需要在创建表时使用关键字 NOT NULL

  • 使用 DEFAULT 值填满空白列的值

  1. mysql> CREATE TABLE doughnut_list
  2.          (
  3.                doughnut_name VARCHAR(10),
  4.                doughnut_type VARCHAR(6),
  5.         doughnut_cost DEC(3,2) NOT NULL DEFAULT 1.50
  6.                );
  1. CREATE TABLE my_contacts (
  2.   contact_id INT NOT NULL,
  3.   last_name varchar(30) NOT NULL,
  4.   first_name varchar(20) NOT NULL,
  5.   email varchar(50) ,
  6.   gender char(1),
  7.   birthday date ,
  8.   profession varchar(50),
  9.   location varchar(50),
  10.   status varchar(20),
  11.   interests varchar(100),
  12.   seeking varchar(100),
  13.   PRIMARY KEY (contact_id)
  14. );
  1. 查看表结构：DESC

DESC: describe

  1. mysql> DESC my_contacts;
  1. 删除表：DROP TABLE

如果需要新增列，必须得删除表，再重新新建表

  1. mysql> DROP TABLE my_contacts; # 会删除表和表里面所有的数据，务必要非常小心谨慎地使用这个命令
  1. 修改表：ALTER

关键字

  • FIRST：

  • AFTER your_column：

  • BEFORE your_column：

  • LAST：添加到表最后

  • CHANGE：可同时改变现有列的名称和数据类型

  • MODIFY：修改现有列的数据类型或位置

  • ADD：在当前表中添加一列 ———— 可自选类型

  • DROP：从表中删除某列

修改表名称: RENAME TO

  1. ALTER TABLE projekts
  2. RENAME TO projeket_list;

修改列名称和数据类型：CHANGE COLUMN

⚠️如果把数据改成另一种数据类型，你可能会丢失数据

  1. ALTER TABLE projeket_list
  2. CHANGE COLUMN number proj_id INT NOT NULL AUTO_INCREMENT,
  3. ADD PRIMARY KEY (proj_id);
  1. ALTER TABLE projeket_list
  2. CHANGE COLUMN descriptionofproj proj_desc VARCHAR(100),
  3. CHANGE COLUMN contractoronjob con_name VARCHAR(30);

只修改数据类型：MODIFY COLUMN

  1. ALTER TABLE project_list
  2. MODIFY COLUMN proj_desc VARCHAR(120);

新增列：ADD COLUMN

  1. ALTER TABLE projeket_list
  2. ADD COLUMN con_phone VARCHAR(10),
  3. ADD COLUMN start_date DATE,
  4. ADD COLUMN est_cost DEC(7,2);

删除列：DROP COLUMN

  1. ALTER TABLE projeket_list
  2. DROP COLUMN start_date;

移除主键设置

  1. ALTER TABLE projeket_list
  2. DROP PRIMARY KEY;

字符串函数

⚠️字符串函数不会改变存储在表中的内容，只是把字符串修改后的模样当成查询结果返回

字符串函数可以和 SELECT、UPDATE、DELETE 搭配使用

  • SUBSTRING(your_string, start_position, length): 从特定位置截取一定长度的子字符串

  • SUBSTRING_INDEX(your_string, mark_string, 第几次出现): 获取指定字符前的子字符串

  • RIGHT(your_string, length): 从字符串的右侧开始截取字符串

  • LEFT(your_string, length): 从字符串的右侧开始截取字符串

  • UPPER(your_string): 把整组字符串改为大写

  • LOWER(your_string): 把整组字符串改为小写

  • REVERSE(your_string): 反转字符串里的字符排序

  • LTRIM(your_string): 清除字符左侧的多余空格

  • RTRIM(your_string): 清除字符右侧的多余空格

  • LENGTH(your_string): 返回字符串中字符的数量

数据操作

  1. 新增数据

  • ⚠️顺序很重要！数据值的顺序必须和列名的顺序完全一样

  • 任何属于 VARCHAR、CHAR、DATE、BLOB 列类型的值都需要加单引号，DEC、INT等值不需要加单引号

  • 多数 SQL RDBMS 都会省略多余的引号，把 DEC 和 INT 的值视为数字，而不会因为引号就把这些值视为文本值

  • 省略所有列名，一定要填入所有数据值，而且要和表中的列顺序完全相同

  • 省略部分列，那填入部分值就好

  1. INSERT INTO my_contacts
  2. (
  3.   last_name,
  4.   first_name,
  5.   email,
  6.   gender,
  7.   birthday,
  8.   profession,
  9.   location,
  10.   status,
  11.   interests,
  12.   seeking
  13. )
  14. VALUES
  15. (
  16.  'Anderson',
  17.  'Jillian',
  18.  'jill_anderson@ \nbreakneckpizza.com',
  19.  'F',
  20.  '1980-09-05',
  21.  'Technical Writer',
  22.  'Palo Alto, CA',
  23.  'single',
  24.  'kayaking, reptiles',
  25.  'relationship, friends'
  26. );
  1. INSERT INTO my_contacts
  2. (
  3.  last_name,
  4.  first_name,
  5.  email
  6. )
  7. VALUES
  8. (
  9.   'Toth',
  10.   'Jillian',
  11.   'jill_anderson@breakneckpizza.com'
  12. );

常见错误：

  • 列的数量和值的数量不匹配ERROR 1136 (21S01): Column count doesn't match value count at row 1

  • 值忘记加上单引号

  1. 查看表数据
  1. SELECT * FROM my_contacts;
  1. 有条件的筛选数据(WHERE 字句)

如果 first_name 列的值是 Anne,就把整条记录显示给我看

  1. SELECT * FROM my_contacts WHERE first_name = 'Anne';
  1. 限制筛选数据的数量
  1. SELECT email FROM my_contacts WHERE first_name = 'Anne';
  1. 多条件结合查询(结合两个 WHERE 子句)

AND: 满足所有条件

  1. SELECT location FROM doughnut_ratings
  2. WHERE type = 'plain glazed'
  3. AND
  4. rating = 10;
  1. SELECT last_name, first_name, email
  2. FROM my_contacts
  3. WHERE
  4. location = 'San Antonio, TX'
  5. AND
  6. gender = 'M'
  7. AND
  8. status = 'single'
  9. AND
  10. birthday = '1975-09-05';

OR: 只要符合一项条件

  1. SELECT drink_name FROM easy_drinks
  2. WHERE
  3. main = 'orange juice'
  4. OR
  5. main = 'apple juice';

IS NULL: 选择表中的 NULL

  1. SELECT drink_name FROM drink_info
  2. WHERE
  3. calories IS NULL;

LIKE: 配合通配符，查找部分文本字符串并返回所有符合匹配条件的row

  1. SELECT * FROM my_contacts
  2. WHERE location LIKE '%CA';

通配符
%: 任意数量的未知字符的替身
_: 一个未知字符的替身

BETWEEN: 相当于<=加>=,但不等于<加>

  1. SELECT drink_name FROM drink_info
  2. WHERE calories
  3. BETWEEN 30 AND 60;

IN(NOT IN): 找出值在集合中的记录(找出值不在集合中的记录)

  1. SELECT date_name FROM black_book
  2. WHERE rating IN ('innovative','fabulous','delightful','pretty good');

NOT: 反转查询结果

  1. SELECT date_name from black_book
  2. WHERE NOT date_name LIKE 'A%'
  3. AND main IS NOT NULL
  4. AND NOT date_name LIKE 'B%';
  1. 比较运算符

=: 等于
<>: 不等于
<: 小于
>: 大于
<=: 小于等于
>=: 大于等于

  1. SELECT drink_name FROM drink_info
  2. WHERE
  3. cost >= 3.5
  4. AND
  5. calories < 50;

返回首字母为“L”或位于其后的但要早于“M”的饮料

  1. SELECT drink_name FROM drink_info
  2. WHERE
  3. drink_name >= 'L'
  4. AND
  5. drink_name < 'M';
  1. 删除数据: DELETE

  • 不能删除单一列中的值或表中某一列的所有值

  • 可用于删除一行或者多行，根据 WHERE 子句决定

  • 删除表中的所有行 DELETE FROM your_table

  • 慎用 DELETE，每次删除记录时，都有意外删除你不想删除的记录的风险，因此要反复确认自己加入了非常精确的WHERE 子句，可以只选出你真正想要删除的行

  • 除非你可以非常确定 WHERE 子句只会删除你打算删除的行，否则都应该用 SELECT 确认情况。

  1. DELETE FROM clown_info
  2. WHERE activities = 'dancing';
  1. 更新数据：UPDATE

  • 通过 SET 子句指定新值

  • 可以改变单一列或所有列的值，在 SET 子句中加入更多 column = value 组，其间用逗号分隔

  • 可用于更新单一行或多行，一切都交给 WHERE 子句决定

  • 如果不加 WHERE 子句，则默认选中所有行

  • 可以和基础数学运算符一起使用，可以操作数值数据

  1. UPDATE doughnut_ratings
  2. SET type = 'glazed', date = '9/21';
  3. WHERE type = 'plain glazed';
  1. UPDATE drink_info
  2. SET cost = cost + 1;
  3. WHERE drink_name = 'Blue Moon'
  4. OR drink_name = 'Oh My Gosh'
  5. OR drink_name = 'Lime Fizz';
  1. CASE 表达式

  • CASE 表达式可以搭配 SELECT、INSERT、DELETE、UPDATE 语句使用

  • 可以在关键字 END 后加上 WHERE 子句，CASE 就会套用在符合 WHERE 条件的列上

  1. UPDATE movie_table
  2. SET catagory =
  3. CASE
  4.  WHEN dzama = 'T' THEN 'dzama'
  5.  WHEN comedy = 'T' THEN 'comedy'
  6.  WHEN action = 'T' THEN 'action'
  7.  WHEN hozzoz = 'T' THEN 'hozzoz'
  8.  WHEN scibi = 'T' THEN 'scibi'
  9.  WHEN for_kids = 'T' THEN 'family'
  10.  WHEN cartoon = 'T' AND rating = 'G' THEN 'family'
  11.  ELSE 'misc' 
  12. END;
  1. 排序：ORDER BY 能按任何列的字母顺序来排列查询结果
  1. SELECT * FROM  movie_table
  2. ORDER BY catagory, purchased, title;
  1. # 字符顺序
  2.  !"#$%'()*+,-./0123:;<=>?@ABCD[\]^_`abcd{|}~

反转顺序: DESC(description); ASC: 默认排序方式

  1. SELECT title, purchased
  2. FROM movie_table
  3. ORDER BY title ASC, purchased DESC;

多表操作

  • 模式(schema): 对数据库内的数据描述(列和表)，以及任何相关对象和各种连接方式的描述

外键(foreign key)

  • 外键是表中的某一列，它引用到另一个表的主键(primary key)

  • 外键可能与它引用的主键名称不同

  • 外键使用的主键也被称为父键(parent key)

  • 主键所在的表又被称为父表(parent table)

  • 外键能用于确认一张表中的行与另一张表中的行相对应

  • 外键的值可以是NULL,即时主键值不可为 NULL

  • 外键值不需要唯一 ———— 事实上外键通常都没有唯一性

约束(constraint)

  • 用 CREATE 或 ALTER 语句来指定外键，否则都不算是真的外键

  • 约束(constraint)：创建在结构内的键

  • 主键约束

  • 外键约束

  • UNIQUE

  • CHECK

  • 引用完整性(referential integrity): 插入外键列的值必须已经存在于父表的来源列中

  • 加强连接：外键约束能确保引用完整性(换句话说，如果表中的某行有外键，约束能确保该行通过外键与另一张表中的某一行对应)。如果我们试着删除主键表中的行或试着改变主键值，而这个主键是其他表的外键约束时，你就会收到错误警告

  • 可以使用外键来引用父表中某个唯一的值，外键不一定必须是父表的主键，但必须有唯一性

  1. 创建带有外键的表
  1. CREATE TABLE interests (
  2.   int_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.   interest VARCHAR(50) NOT NULL,
  4.   contact_id INT NOT NULL,
  5.   CONSTRAINT my_contacts_contact_id # 约束的命名方式能告知我们键的来源表、键的名称，还能说明它是一个外键
  6.   FOREIGN KEY (contact_id) # 括号中的列名就代表外键，可以随意命名
  7.   REFERENCES my_contacts(contact_id) # 指定外键的来源，还有外键列在另一张表中的名称
  8. );
  1. DESC interests # MUL: 表示这一列可以存储多个相同的值，它也是追踪主键对应该表信息的关键

数据模式

  • 一对一：父表只有一行与子表的某行相关；schema 中的连接线是单纯的实线

  • 抽出数据或许能让你写出更快速的查询。例如，如果大多数时候你只需要查询 SSN，就可以查询较小的 SSN 表

  • 如果有列包含还不知道的值，可以单独存储这一列，以免主要表中出现 NULL

  • 我们可能希望某些数据不要太常被访问，隔离这些数据即可管制访问次数。以员工表为例，他们的薪资信息最好存为另一张表

  • 如果有一大块数据，例如 BLOB 类型，这段数据或许存为另一张表会更好

  • 一对多：A表 中的某一条记录可以对应到 B表 中的多条记录，但 B表 中的某一条记录只能对应到 A表 中的某一条记录；schema 中的连接线是单端带有黑色箭头的

  • 多对多：直接连接两张表，最后都会因为它们的多对多本质而产生重复的数据，需要用 (junction table) 存储两个原始表的主键，把关系转化为一对多的关系，schema 中的连接线是两端都带有黑色箭头的

函数 及 关键字

  • SUM()：加总

  • AVG(): 平均值

  • MAX() 和 MIN()

  • COUNT(): 返回行数，如果数据值为NULL，则不纳入计算

  • GROUP BY ：分组

  • DISTINCT: 选出与众不同的值，即返回没有重复的值

  • LIMIT：查询结果的数量

SUM & GROUP BY

  1. SELECT first_name, SUM(sales) # 加总 sales 列的所有数据
  2. FROM cookie_sales
  3. GROUP BY first_name # 根据 first_name 值分组
  4. ORDER BY SUM(sales) DESC; # 根据加总所用的列安高至低排序

COUNT

  1. SELECT COUNT(sale_date)
  2. FORM cookie_sales;

DISTINCT & ORDER BY

  1. SELECT DISTINCT sale_date
  2. FORM cookie_sales
  3. ORDER BY sale_date;

DISTINCT 结合 COUNT

  1. SELECT COUNT(DISTINCT sale_date)
  2. FROM cookie_sales;

LIMIT

  1. SELECT first_name, SUM(sales)
  2. FROM cookie_sales
  3. GROUP BY first_name
  4. ORDER BY SUM(sales) DESC
  5. LIMIT 2; # 只返回两条记录

SQL 从0开始计数，第一个参数是查询结果的起始处，第二个参数是返回查询结果的数量。

  1. SELECT first_name, SUM(sales)
  2. FROM cookie_sales
  3. GROUP BY first_name
  4. ORDER BY SUM(sales) DESC
  5. LIMIT 1,1; # 只显示第二条记录，

获取命令

  1. SHOW CREATE DATABASE gregs_list; # 显示重建数据库所需的语句
  2. SHOW CREATE TABLE my_contacts; # 显示重建表所需的语句
  3. SHOW COLUMNS FROM my_contacts; # 显示表的所有列及其数据类型，还有其他关于各列的细节信息
  4. SHOW INDEX FROM my_contacts; # 显示任何编了索引的列以及索引类型

数据类型

  • CHAR(10) 或 CHARACTER(10): 很严谨，负责的数据必须是事先设定好的长度.例如电话号码必须是10位数字，并且是文本字符串，因为虽然是数字，却不需要任何数字运算

  • VARCHAR(10): 存储文本数据，最大长度可达255个字符，很灵活，可以配合我们的数据长度进行调整

  • BLOB：喜欢大量文本数据，需要很大的存储空间，有些重要的字符串运算无法操作 BLOB 类型的数据

  • DEC(6,2): 提供数值空间，直到装满为止，第一个参数代表总位数，第二个参数是小数点后的位数.如5678.39

  • INT：提供数值空间

  • DATE：记录日期，但是不喜欢插手 TIME 的事情

  • DATETIME(更适合存储将来的时间) 或 TIMESTAMP(记录“省下”这个时刻)：负责记录日期和时间

  • TIME：不插手 DATE 的事情

规范化

  • 使用数据的方式将影响设置表的方式

  • LIKE 搜索精确数据的能力不够，你会冒着找到你不需要的数据的风险

  • 简短的查询优于较长的查询

  • 规范化表中没有重复的数据，可以减小数据库的大小

  • 因为查找的数据较少，查询会更加快速

表都是关于关系的

  • 挑出事物，挑出你希望表描述的某样事物：主要事物

  • 列出一份关于那样事物的信息列表，这些信息都是使用表时的必要信息：如何使用

  • 使用信息列表，把关于那样事物的综合信息拆分成小块信息，以便用于组织表：如何才能最轻松地查询这张表？

原子性数据和你的表

  • 你的表在描述什么事物？

  • 以何种方式使用表取得描述的事物呢？

  • 列是否包含原子性数据，可让查询既简短又直逼要害

  • 具有原子性数据的列中不会有多个类型相同的值

  • 具有原子性数据的表中不会有多个存储同类数据的列

第一范式(1NF)

First Normal Form

  • 每个数据行必须包含具有原子性的值

  • 每个数据行必须有独一无二的识别项，人称主键(Primary Key)

主键

  • 主键是表中的某个列，它可以让每一条记录成为唯一的

  • 主键用于独一无二地识别出每条记录，即主键列中的数据不能重复

  • 主键不可以为 NULL

  • 插入新记录时必须指定主键值

  • 主键必须简洁

  • 主键值不可以被修改

  • 通过 PRIMARY KEY (contact_id) 指定主键列

  • 通过添加 AUTO_INCREMENT (或者 INDEX 1 1) 关键字使得主键列自动递增

  • 组合键(composite key)：由多个数据列构成的主键，组合各列后形成具有唯一性的键

  • 当某列的数据必须随着另一列的数据的改变而改变时，表示第一列函数依赖于第二列

  • 函数依赖：当某列的数据必须随着另一列的数据的改变而改变时，表示第一列函数依赖于第二列

  • 依赖列中包含了可能随其他列的改变而改变的数据，不依赖的列则完全置身事外

  • 部分函数依赖：非主键的列依赖于组合主键的某个部分(但不是完全依赖于组合依赖)

  • 传递函数依赖(Transitive functional dependency)：任何非键列与另一个非键列有关联

  1. T.x -> T.y # 在关系表 T 中，y 列函数依赖于 x 列
  1. CREATE TABLE my_contacts (
  2.   contact_id INT NOT NULL AUTO_INCREMENT,
  3.   last_name varchar(30) NOT NULL,
  4.   first_name varchar(20) NOT NULL,
  5.   email varchar(50) ,
  6.   gender char(1),
  7.   birthday date ,
  8.   profession varchar(50),
  9.   location varchar(50),
  10.   status varchar(20),
  11.   interests varchar(100),
  12.   seeking varchar(100),
  13.   PRIMARY KEY (contact_id)
  14. );

ALTER: 为现有的表添加主键

  • FIRST: 要求软件把新列放在最前面。是一个可选关键字，但把主键列放在最前面是个不错的习惯

  • ADD COLUMN：添加新列

  1. USE gregs_list # 切换到该数据库
  1. ALTER TABLE my_contacts
  2. ADD COLUMN contact_id INT NOT NULL AUTO_INCREMENT FIRST,
  3. ADD PRIMARY KEY (contact_id);

第二范式(2NF)

Second Normal Form

  • 先符合 1NF

  • 没有部分函数依赖性

  • 已符合 1NF 的表若只拥有一列主键，也会符合 2NF

  • 没有人工主键且没有组合主键，则符合 2NF

第三范式(3NF)

Third Normal Form

  • 符合 2NF

  • 没有传递函数依赖性

  1. ALTER TABLE my_contacts
  2. ADD COLUMN interest1 VARCHAR(50),
  3. ADD COLUMN interest2 VARCHAR(50),
  4. ADD COLUMN interest3 VARCHAR(50),
  5. ADD COLUMN interest4 VARCHAR(50); # 创建新的列
  7. # 把第一项兴趣移至新的 interest1 列
  8. UPDATE my_contacts
  9. SET interest1 = SUBSTRING_INDEX(interests, ',', 1);
  11. # 从原始的 interest 字段中移除第一项兴趣
  12. # 利用字符串函数移除第一个逗号左侧的所有内容
  13. UPDARTE my_contacts SET interests = TRIM(RIGHT(interests, (LENGTH(interests) - LENGTH(interest1) - 1)))
  15. UPDATE my_contacts SET interest2 = SUBSTRING_INDEX(interests, ',', 1);
  16. UPDATE my_contacts SET interests = TRIM(interests, ',', 1);

遇到的问题

  1. sudo /usr/local/mysql-8.0.11-macos10.13-x86_64/support-files/mysql.server start