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

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数据库