SQL 基础学习(1)：下载DB Browser for SQLite. 下载graphviz(为了使用Rails ERD的前提)出现❌，已debug.

2023-08-07 12:08:22

SQL is a standard language for storing, manipulating and retrieving data in databases.

关系型数据库：RDBMS(Relational Database Mangement System)

SQLite3:

Rails默认的轻量级数据库，集成于Rails中，在db/development.sqlite3这个档案中。

用途：单机用途。所以在实际部署的时候会换成MySQL等数据库服务器。

MySQL：

目前流行的开源数据库。这是一个数据库服务器，要连接它需要数据库的账号密码。

Mac上用 brew install mysql 安装MySQL，推荐再安装 Sequal Pro 这套Gui（Graphical User interface,图形用户界面）软件 , 也可以使用官方的workbench

补充：

安装中有提示设置root密码，可忽略，之后可使用mysql_secure_installation来设置。

默认只连接本地数据库localhost, 命令是mysql -uroot

To have launchd start mysql now and restart at login:

  • brew services start mysql
  • 关闭：brew services stop mysql

Or, if you don't want/need a background service you can just run:

  • mysql.server start
  • 关闭：mysql.server stop

connect database

  • mysql -u root    (备注可以选择端口用参数-p)

在命令行使用mysql --help查看所有的参数命令，变量等帮助信息。中文说明：https://www.jianshu.com/p/c604f04b9db5

如果有人问你数据库的原理，叫他看这篇文章http://blog.jobbole.com/100349/

一些进修学习资料：

rdbms的特点：

Relationship database mangement system:

一 schema:使用前先定义tables和columns,同时定义每个column的Data Type.

Data Type:

varchar或text:

Integer,Decimal,Float:

Blob二进制：可以存放档案。但是通常不建议把档案直接塞数据库,一来数据库塞太大不容易备份和管理、二来没有什么好处，因为你也没办法针对二进制档案进行条件搜寻和过滤。人们对于读档案也有心理准备会比较慢。所以通常只会在数据库里面纪录档案的 metadata 例如档名、大小、MimeType 等等，而实际的档案则放在档案系统上，或是上传到七牛或AWS S3等空间。

Boolean:

Date, Time, Datetime:

create_table :events do |t|

t.string :name

t.text :description

t.integer :capacity

t.integer :user_id, :null => false //这个是限制constraint,这里是不能为空；
大多验证一般放在rails model中，这样比较有弹性，在DB层是硬性条件，无法跳过。

t.timestamps

end

二 SQL standard language (Structured Query Language)

所有关系型数据库都使用SQL的结构化查询语言，来操作database.for example:

1. INSERT INTO events VALUES ("RubyConf", 100);   //插入一条数据到events表

2. SELECT * FROM events;  //拿出所有数据

三 ACID(4个特性)

Transaction:a process of doing business.把一组动作打包一起执行。使用BEGIN;...COMMIT;   可以保证数据存取的正确性；要么一起成功，要么一起失败。

Atomicity: 一个transaction就是一个原子。

Consistency:一致性，保证transaction前后数据库的完整性没有被破坏。

Isolation:隔离性：数据库允许多个并发的transaction同时进行，互不干扰。

Durability持久性：数据的修改是永久的。
 这4个特性，让关系型数据库在多人连线操作数据库的时候，保证数据的✅。

基础SQL(Structured Query Language)

SQL 分为DDL(Data Definition Language)定义,和DML(Data Manipulation Language)操作。

一 DDL：teach us How to definate Schema. 老师建议用GUi（graphical user interface）软件.

⚠️  ：建立database的时候，选择Encoding. MySQL可用utf8mb4编码。

打开数据库：SQLite3: 在Terminal用cli指令打开 sqlite3 your_db_name.db

MySQL 的话，指令是 mysql -u root -p。PostgreSQL 的指令是 psql <database_name>。

一个gul :  http://sqlitebrowser.org/

建立table：

CREATE TABLE events (name VARCHAR(50) NOT NULL, capacity INTEGER, date DATE);

改名： ALTER TABLE persons RENAME TO people;

新增字段： ALTER TABLE people ADD COLUMN status VARCHAR(50);

修改和移除字段：sqlite3没有支援，需要新开一个table然后复制过去。

Migration机制：

通过migration的功能修改数据库，在开机时坚持当前的版本和database里的版本是否一致。同时migration代码会放到Git里面，方便整个开发团队的开发者在不同的服务器上，利用migration来一致管理Schema.

这个功能就是大家熟悉的Rails Migration.

二 SQL 语言 ：DML

：就是做crud操作。

新增资料用insert into

INSERT INTO events(capacity, name) VALUES(200,"Jdstore");

相当于Rails语法：Event.create(capacity:200, name:"Jdstore")

//也可以插入多笔

INSERT INTO events(capacity, name) VALUES(300,"coscup"), (200, ''hello");

查找资料用select * from table_name ORDER BY column_name ASC LIMIT 20 OFFSET 10;

order by:设定排序

limit 20: 设定最多返回20条record

offset 10: 设定按照设定好的顺序，从第十一条record捞取。 必须和limit配合使用。

修改资料 用UPDATE table_name SET column_name = value;

对应的Rails: Tablename.update_all(:column_name => value);

WHERE column_name = "value";  //用where来指定修改的条件。例子：

SQL:  UPDATE events SET capacity=200 WHERE name="chentianwei";对应的rails语法见下：

Rails: Event.where(:name => "chentianwei").update_all(:capacity => 200);

WHERE .. BETWEEN .. AND ..; 某个区间SELECT * FROM events WHERE date BETWEEN '2015-03-15' AND '2015-03-30';

WHERE .. OR ..  条件或

WHERE..AND..   条件且

WHERE .. LIKE.. 模糊对比：SELECT * FROM events WHERE name LIKE '%Ruby%';

WHERE .. IS NOT NULL 不可为空

WHERE .. IN  也是一个范围选择 ：

SELECT * FROM Employees WHERE LastName IN ('Smith' , 'Doe');  

对比时，注意大小写，每个数据库默认不一样，MySQL是 case insensitive不敏感。

在Rails中比较常见的：

@event = Event.find(21)

@event.update(:capacity => 200)

对应的SQL：

SELECT * FROM events WHERE id=21;

UPDATE events SET capacity=200 WHERE id=21;

删除数据：

DELETE FROM table_name;全部删除，对应的rails语法是Table_name.delete_all

例子：

SQL：  DELETE FROM events WHERE name="RubyConf";

Rails:   Event.where(:name => "RubyConf").delete_all

在rails中常见的是：

@event = Event.find(123)

@event.destroy

对应的SQL是：

SELETE * FROM events WHERE id=123;

DELETE FROM events WHERE id=123;

查有哪些tables和columns，各家语法不一样：

mysql: show tables 和 describe tablename

sqlite3: .tables 和 .schema tablename

PostgreSQL:  \dt 和 \dt tablename

在Rails console: Table_name.columns 会反射出有哪些字段。

Indexes

WHERE ,ORDER等条件字段最好加上数据库索引，这样搜索速度快。

  • 加索引 CREATE INDEX events_user_id_idx ON events(user_id);
  • 索引并且值是唯一 CREATE UNIQUE INDEX xxx_idx ON xxx(yyy);

LIKE模糊查询是Full Table Scan,几万笔数据内还能接受，再大就要用搜索引擎了，如ElasticSearch

在Rails Migration中加上索引的话，用add_index语法，例如： add_index :events, :date

⚠️ ：加索引会在写入数据时变慢，也会增加储存空间，但查询时会变快。

在安装gul的时候提示使用homebrew cask.竟然要输入密码，我没理解。经过google，这个应该是homebrew的扩展，可以一句命令就能下载appstore之外的一些软件，密码可能是用户登陆密码，我输入appstore的密码不成功提示❌。然后9次以后自动从git上下载。但速度及其慢，失败后，从新再来一次，输入用户名密码迅速下载成功！✅。

brew cask list  ／／可以看到下载的列表。

SQL语法是否区分大小写？

关键字不区分大小写（select, from, where等）。一般表名和列名区分大小写，最好统一用小写。

数据库规范化 Normalization （点击进*）

数据库设计的一系列原理和技术，目的是减少数据冗余，增进数据的一致性。节省空间，增加修改数据时的效率，避免数据不一致的错误。

一阶规范化： 不能有重复组，不能少了唯一识别码。

1.不要用很多字段来表达同一个事实（设计column的时候，不要有目的完全一样的列 ，喜欢的食物1，喜欢的食物2）

2.每条记录应有一个id,这是防止完全相同的记录（如两笔完全一样的交易记录）。

3.每个字段的值都只能是单一值（不能一个字段，有多个有意义的值并用逗号隔开）。

二阶规范化：移除重复语意的row

就是拆分表。

1.要求数据表里的所有数据都和该数据表的键（主键和候选键）有完全依赖关系。

2.如果有哪些数据只和一个键的一部分有关的话，就得把它们独立出来变成另一个数据表。

Rails 是建立多对多关系。增加一个relationship的表格储存关键id.

三阶规范化：要求所有非键属性都只和候选键有相关性，也就是说非键属性之间应该是独立无关的。

。。。

数据库设计实务

用model的关系来思考。

http://www.vivekmchawla.com/erd-crows-foot-relationship-symbols-cheat-sheet/

这个图定义了一对一，一对多，多对多关系等图表。 Compound

Primary Key 主键：

不null,不重复。Rails只支持单一id,不支持组合key.

种类：

1.自动递增的id.Rails 默认方式；

2.UUID(点击看*)Universally Unique Identifier

  • 分布式系统喜欢用
  • 或是当作 token URL 功能
3. Natural key (例如身分证号码, ISBN, 国码 ISO ALPHA-2) 等等，不过你需要真的确认不会重复。

加primary key 的 SQL：

CREATE TABLE events (id INTEGER NOT NULL PRIMARY KEY, name TEXT, ...);

加auto increment primary key的 SQL（各家语法不一样，以下是SQLite3）

CREATE TABLE events(id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT,...)；

//Rails自动产生递增的id.

Foreign Key (Reference Key)外键：

外键字段通常以_id结尾。在Rails中，写belongs_to的Model,就是foreign key 字段的那个model。

在Rails Migration中可以用add_foreign_key语法，数据库会提供验证。

不过在Rails中偏好在应用层解决数据的正确性。 例如dependent属性来处理删除情况。

class Event < ApplicationRecord

has_many :registrations, :dependent => :destroy

end
  • :destroy 把依赖的registrations也一并删除，并执行Registration的destroy回叫。
  • :delete  如destroy但不执行回叫。
  • :nullify  这是默认值，不会帮忙删除registrations, 但会把registrations的外部键event_id设为NULL。
  • :restrict_with_exception 限定，如果有任何依赖的registrations资料，则连event都不能删除。执行删除时会报告❌，ActiveRecord::DeleteRestrictionError
  • :restrict_with_error不允许删除。执行删除时会回传false，在@event.errors中会留下❌信心。

逆规范化 denormalized

数据库规范化并不是完全的真理，在不同场景下甚至会做逆规范化的设计。如olap用做分析用途。

另外，在一些需要局部效能最佳化的场景，也会做一些逆规范化的设计，例如 Rails 的 计数快取 Counter Cache功能，将数量额外用一个字段先存下来，免去之后计算的查询时间。这也是一种逆规范化的设计。

下载graphviz,时候出现❌。

>brew install graphviz

...

/usr/local/Homebrew/Library/Homebrew/brew.rb:12:in `<main>': Homebrew must be run under Ruby 2.3! You're running 2.0.0. (RuntimeError)

我复制粘贴到google：找到一个相关的已解决问题帖 https://github.com/Homebrew/brew/issues/3299

解决办法：

1. Ran brew update and retried you prior step? 如果不行。

2. Ran brew doctor, fixed all issues and retried your prior step? 如果不行

3. Ran brew config and brew doctor 。

我的办法，第二遍使用 brew install graphviz，然后出现下载，但下载部分出现error.

在 brew update后，再次brew install graphviz,出现一次error,但brew list 中有graphviz.

我在jdstore3/jdstore中安装Rails-erd ，并bundle install成功，然后执行rake erd成功，建立了erd.pdf。

上一篇：【转】android camera(四)：camera 驱动 GT2005


下一篇：分布式系列十五: MongoDB数据库