某储备粮的“学习笔记”

by 咳嗽di小鱼

Definition实在不知道怎么概括= =...

  • Intention of a relation: relation definition and constrains
  • Extention of a relation: the actual data, tuples

Relation properties

  • Set Theory

    • Attributes has order (not necessary)
    • Value are used to identify tuples
    • Tuples dont have order
    • Tuples cant have duplication
  • Attribute value are atomic
  • Degree: How many attributes in the schema
  • Cardinality: How many tuples in an instance

Constraints

  • Let the DBMS to ensure the entry or modification on data are legal
  • Give applications' bug no chance to ruin the data

Three Constraint Level

  • Tuple-level

    • Domain restrictions (datatype)
    • Attribute comparisons (好像是指check)
  • Relation-level

    • Special type of keys

      • Superkey: 能uniquely identifies a tuple的一个or一组attribute
      • Candidate key: Superkey的最小集, (最简单的一个or一组attribute)
      • Primary key: 被选中, 用来identity tuples的Candidate key
  • Database-level

    • Referential integrity

      • Foreign key: 其他relation中的primary key
      • Referential integrity: 不允许出现和对应relation中的记录不符的情况
    • Inclusion dependencies: = =`不懂

Definitions

  • Database: is a large and persistent collection of pieces of information organized in a way that facilitate efficient retrieval and modification.
  • DBMS: is a program that manages details related to storage and access for a database.
  • Schema: is a description of the data interface to the database.
  • Instance: is a database that conforms to a given schema.
  • Transaction: is an application-specified atomic and durable unit of work.
  • View: is a relation in the external schema whose instance is determined by the instances of the relations in the conceptual schema.
  • Trigger: is a procedure executed by the database in response to a change to the database instance

Three Level Schema Architecture

  • External schema (view): visualized data
  • Conceptual schema: logical structure
  • Physical schema: file/storage devices

Data Independence

  • Application access the data through an abstract data model provided by the DBMS, rather than direct access
  • Physical: applications cant touch the storage stucture
  • Logical: applications cant change the data organization

Four Transaction Properties

NoSQL据说就是要break these properties= =`IDK why...

  • Atomic: once the transaction happens, it happened. No intermediate stage!
  • Consistency: transaction follows the database consitences
  • Isolated: concurrent transactions wont effect each other
  • Durable: transaction results permanently

What DBMS do

  • Remoce common code from applications
  • Provide uniform access to data
  • Guarantee data integrity
  • manage concurrent access
  • Protect against system failure
  • Set access policies for data

SELECT

SELECT attribute-expression-list FROM relation-list [ WHERE condition ];

attribute-expression-list:

  • [relation-name.]attribute
  • [relation-name.]attribute [arithmatic computation] AS another name

    • i.e., E.Salary - 40000 AS SalaryDiff
  • CASE WHEN ... THEN ...
    ELSE ... END

    • i.e., CASE WHEN E.Salary < 40000 THEN 0
      ELSE E.Salary - 40000 END

relation-list:

  • list of table names
  • seperate by comma

condition:

  • arithmetic operation +, -, *, /
  • comparisions =, <>, <, <=, >. >=
  • logical connectives AND, OR, NOT
  • attribute IN (Q)
  • attribute NOT IN (Q)
  • attribute op SOME (Q)
  • attribute op ALL (Q)
  • EXISTS (Q)
  • NOT EXISTS (Q)
  • IS [NOT] NULL

Read more...


我的QQ邮箱现在已经有3T容量了...
原来玩WordPress的时候其实就一直拿来做数据库备份, 现在容量变大几倍有余, 自然是备份的不二选择. 这可比FDC的大容量VPS划算多了, 特别是当你的网站并没有多大的时候. (书苑现在的数据库也都还没有1G...)

这个脚本来自小夜博客, 经我略加修改了一下.
下载: backup_to_mail.sh


很简单的一段bash script, 只需要自定义下mysql的用户名密码, 邮箱地址和要备份的目录.

然后进行下边的步骤:

首先要建立好放置备份文件的目录

mkdir /home/backup

然后安装发送邮件的程序

yum install sendmail mutt

把backup_to_mail.sh放到刚刚建立的目录中, 然后给脚本增加运行权限

chmod a+x /home/backup/backup_to_mail.sh

最后一步是设置定时运行, 输入

crontab -e

然后在vi编辑界面中加入下面这行文字

00 00 * * * /home/backup/backup_to_mail.sh

:x + enter就ok了.

其中00 00表示在每日00分, 00点的时候运行该脚本.

下边是脚本的内容, 以备附件失效和以后学习...

#!/bin/bash
MYSQL_USER=root                             #mysql username
MYSQL_PASS=***********                      #mysql password
MAIL_TO=*******@***.**                      #mailling to
WEB_DATA=/home/wwwroot/                     #to be backup dir

#define variables
DataBakName=Data_$(date +%Y%m%d).tar.gz
WebBakName=Web_$(date +%Y%m%d).tar.gz
OldData=Data_$(date -d -3day +%Y%m%d).tar.gz
OldWeb=Web_$(date -d -3day +%Y%m%d).tar.gz
DataSubject="Backup: gregwym.info database "$(date +%Y%m%d)
WebSubject="Backup: gregwym.info web dir "$(date +%Y%m%d)

#delete data from 3days earlier
rm -rf /home/backup/$OldData /home/backup/$OldWeb
cd /home/backup

#export database to compressed gz files
for db in `/usr/local/mysql/bin/mysql -u$MYSQL_USER -p$MYSQL_PASS -B -N -e 'SHOW DATABASES' | xargs`; do
    (/usr/local/mysql/bin/mysqldump --single-transaction -u$MYSQL_USER -p$MYSQL_PASS ${db} | gzip -9 - > ${db}.sql.gz)
done

#compress database files
tar zcf /home/backup/$DataBakName /home/backup/*.sql.gz
rm -rf /home/backup/*.sql.gz
#mail database
echo $DataSubject | mutt -a /home/backup/$DataBakName -s "${DataSubject}" $MAIL_TO

#compress backup dir
tar zcf /home/backup/$WebBakName $WEB_DATA
#mail dir
echo $WebSubject | mutt -a /home/backup/$WebBakName -s "${WebSubject}" $MAIL_TO

echo "bye"
#END

如果不希望备份网页文件, 或者希望数据库和网页分别备份的, 可以自行拆分脚本为两个文件.

如果只是想降低备份网页文件的频率, 可以将#mail dir部分替换为:

if [ $(date +%A) = "Sunday" ]; then 
    echo $WebSubject | mutt -a /home/backup/$WebBakName -s "${WebSubject}" $MAIL_TO 
fi