ruby

http://www.geneontology.org/GO.downloads.database.shtml
http://archive.geneontology.org/latest-full/

go_-

YYYYMM is the release date (the release export usually follows some
time after the monthly release, due to time taken to build)

the DATASET is one of:
——-

* termdb – a database containing just the information on the
GO terms and relationships. These are the table that are populated:

term GO controlled vocab terms
term2term relationships between GO terms
term_definition definitions of terms
dbxref external database identifier entities
term_dbxref links from terms to other databases
term_synonym synonyms for terms
graph_path transitive closure (all paths) in graph

* assocdb – a database containing both the GO vocabulary and
associations between GO terms and gene products. This database
subsumes termdb. These are the extra tables that are populated:

gene_product gene or protein or entity annotated
association link between gene product and GO term
evidence evidence type and reference for an assoc
gene_product_count recursive product counts per GO term

*seqdb – a database containing GO terms, gene products and the
sequences associated with these gene products. This db subsumes the
two above. It populates these additional tables:

seq biological sequence
gene_product_seq link between a product and a sequence
seq_dbxref external database links for a sequence

NOTE: there are other unpopulated tables – we may or may not decide to
populate these at some point in the future.

NOTE: The production version of seqdb with the full database has been
suspended until further notice.

*seqdblite – this is the same as seqdb, except all IEA associations
have been removed. The IEA associations provide relatively little
value compared to the curated associations, and they slow querying
down immensely. This is the distribution that AmiGO runs off of. We
are working on optimisations to allow AmiGO to run off of the full
seqdb release.

the TYPE is either
—-

.rdf-xml – RDF XML export of the database. this comes as one single
file. Note there is no RDF XML export of seqdb, as we do not include
sequences in the xml yet. We do not include IEA evidence associations
in the xml. We may decide to split this xml file into multiple files
at a later date.

.obo-xml – OBO XML Export. Currently ontology only

.owl – OWL Export. Currently ontology only

.tables – this is a directory containing the MySQL dump, see below

.sql – SQL CREATE TABLE and INSERT statements for building a local
instance of the database. equivalent to the .tables TYPE (but slower
to load)

安装termdb – a database containing just the information on the GO terms and relationships. These are the table that are populated:
下载termdb的所有文件:(go_200902-termdb-tables 这个文件是mysql的，只需要这个就可以了。)
1。
The database export was prepared from a mysql db – you should have no
problem importing it:

tar -zxvf go-YYYYMM-TYPE-tables.gz
cd
echo “create database mygo” | mysql -u root -p
cat *.sql | mysql mygo -u root -p
mysqlimport -L mygo *.txt -u root -p

Note: if you are using Windows, you may see warning messages when
loading some tables; to avoid this, load tables this way:

mysql> load data infile
“c:\\download\\GO\\july-release\\go_200307-assocdb-table
s\\association.txt” into table association lines terminated by ‘\r\n’;

This can be avoided if you disable “TAR file smart CR/LF conversion”
when using Winzip (thanks to Henrik Edgren for the tip

We are unable to support Windows users – please refer to your MySQL
documentation; if you experience other problems, you may wish to try
posting a question to the go-database mail list to see if other
Windows users have any advice.
2.安装好了之后，term 里面就有数据了
use GO::AppHandle;
my $dbname = “mygo”;
my $dbhost = “localhost”;

# my $mysqlhost = “localhost”;
# connect to a database on a specific host
$apph = GO::AppHandle->connect(-dbname=>$dbname, -dbhost=>”localhost”,-dbuser=>”root”,-dbauth=>”password”);

# EXAMPLE 1
# fetching a GO term from the datasource
$term = $apph->get_term({acc=>”GO:0003677″});
printf
“GO term; name=%s GO ID=%s\n”,
$term->name(), $term->public_acc();

that’s ok.

example:

http://wiki.geneontology.org/index.php/Example_Queries

http://cpansearch.perl.org/src/CMUNGALL/go-db-perl-0.01/doc/go-db-perl-doc.html

http://www.ensembl.org/info/data/ftp/index.html

http://hgdownload.cse.ucsc.edu/downloads.html

EnsEMBL 简介

[编辑] 主办机构

EnsEMBL是由Sanger中心和EMBL-EBL共同维护的基因组注释系统。该项目的主要组织者就是BioPerl的元老Ewan Birney

[编辑] 目标

EnsEMBL力图实现以下目标：

• 准确的基因组自动注释
• 基于注释信息的全面分析
• 向全世界公开发布注释与分析结果
• 以开源合作的方式开发注释与分析所需要的软件（因为是E.B.在管，这里说的软件当然主要是基于Perl/BioPerl。另外这里说的软件仅能用于以EnsEMBL database格式存储的基因组信息）

[编辑] EnsEMBL Perl API 概述

[编辑] EnsEMBL Core

用于组织和分析：

• 基因组的原始序列
• 染色体名字等最高级的注释(Top-level annotation)
• Gene,Transcript(mRNA/ncRNA等),Translation(peptide)的注释
• microarray(芯片)探针对应的基因组位置的信息
• 其它外部注释信息的链接（例如GO）

[编辑] EnsEMBL Compara

用于组织和分析：

• 物种间的局部多序列比对结果
• 共线性区(Synteny Region)的注释
• 旁系同源(Paralogue)/直系同源(Orthologue)基因的注释
• 蛋白家族的定义

[编辑] EnsEMBL Variation

用于组织和分析：

• SNP(单核甘酸多态)/in-del/CNV等形式的variation注释
• 群体/基因型/等位基因状态
• 连锁不平衡信息

[编辑] EnsEMBL EST

软件上是和EnsEMBL Core一样的，只是数据不一样

[编辑] EnsEMBL Perl API 安装

[编辑] 安装BioPerl

需要先安装BioPerl(请参考BioPerl安装)

$ cvs -d :pserver:cvs@code.open-bio.org:/home/repository/bioperl login

Logging in to :pserver:cvs@code.open-bio.org:2401/home/repository/bioperl

CVS password: cvs

$ cvs -d :pserver:cvs@code.open-bio.org:/home/repository/bioperl checkout -r bioperl-release-1-2-3 bioperl-live

另外数据库(一般是MySQL)也是必需的

[编辑] 安装EnsEMBL的模块

文件会被安装到当前目录下

$ cvs -d :pserver:cvsuser@cvs.sanger.ac.uk:/cvsroot/ensembl login

Logging in to :pserver:cvsuser@cvs.sanger.ac.uk:2401/cvsroot/ensembl

CVS password: CVSUSER

$ cvs -d :pserver:cvsuser@cvs.sanger.ac.uk:/cvsroot/ensembl checkout -r branch-ensembl-45 ensembl

上面最后一行安装的是EnsEMBL Core，若需要安装EnsEMBL Variation，则需将最后的ensembl换成ensembl-variation，即：

$ cvs -d :pserver:cvsuser@cvs.sanger.ac.uk:/cvsroot/ensembl checkout -r branch-ensembl-45 ensembl-variation

[编辑] 设置环境变量

目的是让Perl可以找到相关模块：

PERL5LIB=${PERL5LIB}:/path/to/BioPerl/modules

PERL5LIB=${PERL5LIB}:/path/to/ensembl-core/modules

PERL5LIB=${PERL5LIB}:/path/to/ensembl-variation/modules

PERL5LIB=${PERL5LIB}:/path/to/ensembl-compara/modules

export PERL5LIB

请把”/path/to/BioPerl/modules”换成BioPerl模块实际安装的目录。同理，后面的”/path/to/ensembl-core/modules”等，也应该换成EnsEMBL模块实际安装的目录。

[编辑] 下载EnsEMBL数据库

该步不是必须的，本地化与否的优劣，请大家自己考虑。

请到EnsEMBL的FTP下载mysql数据库文件并导入本地mysql数据库，即可在本地使用EnsEMBL的数据。

注意：数据库很大，特别是EnsEMBL Compara。

[编辑] EnsEMBL Perl API 更新

EnsEMBL的数据和API的版本是一一对应的，数据更新过之后必须更新对应的API。用CVS可以很方便地更新，只需在模块安装的目录下运行：

cvs -q update -d -P -r branch-ensembl-45

[编辑] EnsEMBL Perl API 基本用法

要使用EnsEMBL的数据库，首先需要链接上数据库，由于EnsEMBL定义了很多对象及其Adaptor，所以提供了统一的 Bio::EnsEMBL::Registry模块来方便地获取这些Adaptor(曾几何时，要自己建DBAdaptor，然后再建对象的 Adaptor)，同时可以避免建立多余的DBAdaptor。

• 获取预设的数据库连接

use Bio::EnsEMBL::Registry;

my $registry = ‘Bio::EnsEMBL::Registry’;

$registry->load_registry_from_db(-host => ‘ensembldb.ensembl.org’,-user => ‘anonymous’);

• 获取您所需要的注释信息

上面提到的所有注释信息都可以经由对应的对象的Adaptor获得，例如Gene：

my $gene_adaptor = $registry->get_adaptor( ‘Human’, ‘Core’, ‘Gene’ ); #获得GeneAdaptor对象，可用于读取人的EnsEMBL Core数据库内的信息

my $gene = $gene_adaptor->fetch_by_stable_id(”ENSG00000099889″); #获得一个基因序列及其他注释信息

又例如基因组片段（Slice）：

my $slice_adaptor = $registry->get_adaptor(”Human”,”Core”,”Slice”);

my $slice = $slice_adaptor->fetch_by_region(”chromosome”,”14″,”5623425″,”5673425″); # 获得基因组上的一段序列

上面提到的基因组片段（Slice）还可以结合其他Adaptor，查找该片段上的任何注释信息（基因、Exon、多态、芯片探针、等等等等……）。

[编辑] 可以使用EnsEMBL的物种

目前只有部分物种有EnsEMBL化的注释系统：

• 来自EnsEMBL

Human 人 / Mouse 小鼠 / Zebrafish 斑马鱼 / C.elegans 秀丽隐杆线虫 / Cat 猫 / Chicken 鸡 / Chimpanzee 黑猩猩 / Cow 牛 / Dog 狗 / Elephant 象 / Fruitfly 果蝇 / Platypus 鸭嘴兽 / Rabbit 兔 / Rat 大鼠 / S.cerevisiae 酵母 ， 等等（主要是动物）

• 来自Gramene

Oryza sativa 栽培稻 / Oryza rufipogon 野生稻 / Zea mays 玉米 / Arabidopsis thaliana 拟南芥

Introduction

All data sets in the Ensembl system are stored in relational databases (MySQL). For each of the Ensembl databases the project provides a specific Perl API. As Ensembl takes also advantage of code provided by the BioPerl project; installation of the BioPerl package is included in these instructions.

Ensembl uses the Concurrent Versions System (CVS) for storing the source code and keeping track of source code revisions. This system will help you keeping up to date with developments and bug fixes. You will need CVS installed if you want to download Ensembl code. Graphical CVS clients are also available for Windows, e.g. WinCVS or TortoiseCVS.

• Additional tips for Windows users →

If your computer system is protected by a firewall, this firewall needs to allow outgoing connections to TCP port 2401. There is also a web-based CVS repository, which allows you to download Unix tar archives in case CVS access through a firewall is not possible.

Installation Procedure

1. Create an installation directory

   $ cd
   $ mkdir src
   $ cd src

2. Log into the BioPerl CVS server (using a password of cvs):

   $ cvs -d :pserver:cvs@code.open-bio.org:/home/repository/bioperl login
   Logging in to :pserver:cvs@code.open-bio.org:2401/home/repository/bioperl
   CVS password: cvs

3. Install BioPerl (version 1.2.3)

   $ cvs -d :pserver:cvs@code.open-bio.org:/home/repository/bioperl checkout -r bioperl-release-1-2-3 bioperl-live

   Important note: you must install version 1.2.3, not a more recent version. Starting with 1.2.4, major changes were made to the BioPerl API which have made it incompatible with Ensembl

4. Log into the Ensembl CVS server at Sanger (using a password of CVSUSER):

   $ cvs -d :pserver:cvsuser@cvs.sanger.ac.uk:/cvsroot/ensembl login
   Logging in to :pserver:cvsuser@cvs.sanger.ac.uk:2401/cvsroot/ensembl
   CVS password: CVSUSER

5. Install the Ensembl Core Perl API for version 52

   $ cvs -d :pserver:cvsuser@cvs.sanger.ac.uk:/cvsroot/ensembl checkout -r branch-ensembl-52 ensembl

6. If required, install the Ensembl Variation Perl API for version 52

   $ cvs -d :pserver:cvsuser@cvs.sanger.ac.uk:/cvsroot/ensembl checkout -r branch-ensembl-52 ensembl-variation

7. If required, install the Ensembl Functional Genomics Perl API for version 52

   $ cvs -d :pserver:cvsuser@cvs.sanger.ac.uk:/cvsroot/ensembl checkout -r branch-ensembl-52 ensembl-functgenomics

8. If required, install the Ensembl Compara Perl API for verion 52

   $ cvs -d :pserver:cvsuser@cvs.sanger.ac.uk:/cvsroot/ensembl checkout -r branch-ensembl-52 ensembl-compara

   NB: You can install as many Ensembl APIs as you need in this way. To install all the APIs in one go, use the command:

   $ cvs -d :pserver:cvsuser@cvs.sanger.ac.uk:/cvsroot/ensembl checkout -r branch-ensembl-52 ensembl-api

9. Set up your environmentYou have to tell Perl where to find the modules you just installed. You can do this by using the use lib clause in your script but if you want to make these modules available for all your scripts, the best way is to add them into the PERL5LIB environment variable.
   • Under bash, ksh, or any sh-derived shell:

     PERL5LIB=${PERL5LIB}:${HOME}/src/bioperl-live
     PERL5LIB=${PERL5LIB}:${HOME}/src/ensembl/modules
     PERL5LIB=${PERL5LIB}:${HOME}/src/ensembl-compara/modules
     export PERL5LIB

   • Under csh or tcsh:

     setenv PERL5LIB ${PERL5LIB}:${HOME}/src/bioperl-live
     setenv PERL5LIB ${PERL5LIB}:${HOME}/src/ensembl/modules
     setenv PERL5LIB ${PERL5LIB}:${HOME}/src/ensembl-compara/modules

   • Under Windows (assuming you installed the APIs in C:\src\):

     set PERL5LIB=C:\src\bioperl-live;C:\src\ensembl\modules;C:\src\ensembl-compara\modules

   NB: If you installed extra Ensembl APIs, don’t forget to add their path to the PERL5LIB environment variable.

Additional Tips for Windows users

• You will of course need Perl installed to use the API! This is available free of charge from ActiveState.
• You will also need to install the DBD::MySQL package using PPM (Perl Package Manager), a command-line tool which is bundled with ActivePerl.
• If the existing mysql-driver (”libmysql.dll”) doesn’t work, replace it – e.g. with the one distributed with “php-5-2-3-win32-installer.msi”

Additional Modules

Additional modules for accessing GO data may be found here:

• http://search.cpan.org/~cmungall/go-db-perl-0.01/

Update Procedure

In case you want to update the Perl APIs to a more recent version, keep in mind that the API and database versions must be identical; you can use a simple CVS command to achieve this.

1. Change the working directory to the directory into which you originally installed the APIs.

   $ cd
   $ cd src

2. For each of the APIs, change into its top-directory before issuing the CVS update command. So for the Ensembl Core API, which has been automatically installed into the ensembl directory use the following commands:

   $ cd ensembl
   $ cvs -q update -d -P -r branch-ensembl-52
   $ cd ..

   CVS will automatically add, modify or delete files so that your working directory will resemble the ensembl-branch you selected.

   In case you are asked for a password, repeat the login steps in the installation procedure above. (The password is normally stored in encrypted form in a file in your home directory and remembered between CVS operations.)

使用date_select时出错：不能更新为1970年以前的年份。抛出的异常是ActiveRecord::MultiparameterAssignmentErrors。

debug发现该字段使用的是Time类型。因为表字段类型声明为datetime。将表字段类型改为date类型就好了，对应的是Date类型。

对应关系是这样的。
Ruby Mysql
:datetime
Time datetime
:date Date
date

对于生日这样的字段不能使用:datetime

DATETIME、DATE 和 TIMESTAMP 类型是相似的。这个章节描述了它们的特性以及它们的相似点与不同点。

DATETIME 类型可用于需要同时包含日期和时间信息的值。MySQL 以 ‘YYYY-MM-DD HH:MM:SS’ 格式检索与显示 DATETIME 类型。支持的范围是 ‘1000-01-01 00:00:00′ 到 ‘9999-12-31 23:59:59′。(“支持”的含义是，尽管更早的值可能工作，但不能保证他们均可以。)

DATE 类型可用于需要一个日期值而不需要时间部分时。MySQL 以 ‘YYYY-MM-DD’ 格式检索与显示 DATE 值。支持的范围是 ‘1000-01-01′ 到 ‘9999-12-31′。

TIMESTAMP 列类型提供了一种类型，通过它你可以以当前操作的日期和时间自动地标记 INSERT 或UPDATE 操作。如果一张表中有多个 TIMESTAMP 列，只有第一个被自动更新。

自动更新第一个 TIMESTAMP 列在下列任何条件下发生：

* 列值没有明确地在一个 INSERT 或 LOAD DATA INFILE 语句中被指定。
* 列值没有明确地在一个 UPDATE 语句中被指定，并且其它的一些列值已发生改变。(注意，当一个 UPDATE 设置一个列值为它原有值时，这将不会引起 TIMESTAMP 列的更新，因为，如果你设置一个列值为它当前值时，MySQL 为了效率为忽略更新。)
* 明确地以 NULL 设置 TIMESTAMP 列。

第一个列以外其它 TIMESTAMP 列，可以设置到当前的日期和时间，只要将该列赋值 NULL 或 NOW()。

任何 TIMESTAMP 列均可以被设置一个不同于当前操作日期与时间的值，这通过为该列明确指定一个你所期望的值来实现。这也适用于第一个 TIMESTAMP 列。这个选择性是很有用的，举例来说，当你希望 TIMESTAMP 列保存该记录行被新添加时的当前的日期和时间，但该值不再发生改变，无论以后是否对该记录行进行过更新：

* 当该记录行被建立时，让 MySQL 设置该列值。这将初始化该列为当前日期和时间。
* 以后当你对该记录行的其它列执行更新时，为 TIMESTAMP 列值明确地指定为它原来的值。

另一方面，你可能发现更容易的方法，使用 DATETIME 列，当新建记录行时以 NOW() 初始化该列，以后在对该记录行进行更新时不再处理它。

示例(译者注)：

mysql> CREATE TABLE `tA` (
-> `id` int(3) unsigned NOT NULL auto_increment,
-> `date1` timestamp(14) NOT NULL,
-> `date2` timestamp(14) NOT NULL,
-> PRIMARY KEY (`id`)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `tA` SET `id` = 1;
Query OK, 1 row affected (0.02 sec)

# 没有明确地指定第一个 timestamp 列值，该列值被设为插入的当前时刻
# 没有明确地指定其它的 timestamp 列值，MySQL 则认为插入的是一个非法值，而该列值被设为0

mysql> INSERT INTO `tA` S (2, NOW(), NULL);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM `tA`;
+—-+—————-+—————-+
| id | date1 | date2 |
+—-+—————-+—————-+
| 1 | 20030503104118 | 00000000000000 |
| 2 | 20030503104254 | 20030503104254 |
+—-+—————-+—————-+
2 rows in set (0.00 sec)

mysql> UPDATE `tA` SET `id` = 3 WHERE `id` = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

# 对某一记录行进行了更新，第一个 timestamp 列值也将被更新

mysql> UPDATE `tA` SET `id` = 2 WHERE `id` = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

# MySQL 忽略了这次操作，第一个 timestamp 列值不会被更新

mysql> SELECT * FROM `tA`;
+—-+—————-+—————-+
| id | date1 | date2 |
+—-+—————-+—————-+
| 3 | 20030503104538 | 00000000000000 |
| 2 | 20030503104254 | 20030503104254 |
+—-+—————-+—————-+
2 rows in set (0.00 sec)

mysql> UPDATE `tA` SET `id` = 1,`date1`=`date1` WHERE `id` = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

# 明确地指定了第一个 timestamp 列值为它原有值，该值将不会被更新

mysql> SELECT * FROM `tA`;
+—-+—————-+—————-+
| id | date1 | date2 |
+—-+—————-+—————-+
| 1 | 20030503104538 | 00000000000000 |
| 2 | 20030503104254 | 20030503104254 |
+—-+—————-+—————-+
2 rows in set (0.00 sec)

* 以上结果在 MySQL 4.0.12 中测试

CREATE DATABASE `protein6` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE `protein6`.`westerns` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`预约日期` VARCHAR( 400 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`预约人` VARCHAR( 400 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`项目编号` VARCHAR( 400 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`目的蛋白分子量` VARCHAR( 400 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`tag抗体` VARCHAR( 400 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`内参抗体` VARCHAR( 400 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`自动生成的信息` VARCHAR( 400 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
`膜编号` VARCHAR( 400 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_unicode_ci;

好了，就这样设置好了。

utf8:

use chem_development;
delete from companies;
set names ‘utf8′;

load data local infile ‘/home/wanguan2000/companynot.txt’ into table companies
character set utf8
fields terminated by ‘\t’
(nation,
province,
area,
zip_code,
company_number,
credit_grade,
chinese_name,
chinese_short_name,
english_name,
english_short_name,
address,
contact_man,
phone,
fax,
email,
home_page,
attachment,
company_summary);

不设定字符：

load data local infile ‘/home/wanguan2000/companynot.txt’ into table companies
fields terminated by ‘\t’
(nation,
province,
area,
zip_code,
company_number,
credit_grade,
chinese_name,
chinese_short_name,
english_name,
english_short_name,
address,
contact_man,
phone,
fax,
email,
home_page,
attachment,
company_summary);

mysql> use mysql;
Database changed

运行该脚本文件:

mysql> source hi.sql
Query OK, 0 rows affected (0.08 sec)

mysql -uroot -ppassword < path_to_import_file –default-character-set=utf8

shell>mysql -hxx -uxx -pxx database
mysql>set names utf8;
mysql>load data infile ‘/path_to_file/xx.csv’ into table xx ;
做类似上面的步骤即可

SELECT * FROM store_development.items i;

mysql -u root -ppassword < path_to_import_file –default-character-set=utf8

load data infile ‘D:\2.csv’ into table items;

load data infile ‘D:\2.csv’ into table items
fields terminated by ‘,’
lines terminated by ‘\n’;

show variables like ‘character_set\_%’;
set names ‘utf8′;

create table bb(

id int not null auto_increment,
address varchar(100),
company_summary text,

primary key(id)
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

insert into bb(id,address,company_summary)
values(’1′,’撒旦’,'东方’),(”,’很快’,'文艺’),(’3′,’把’,'北大’);

数据录入
insert into titles(title,publID,langID,year)
values(’My SQL’,1,2,2008),(’C++’,2,1,2001),(’Visual C’,3,3,2003),(’Oracle’,1,5,1987),
(’SQL’,2,4,2005),(’VB’,1,2,2003);

insert into publishers (publID,publName)
values(1,’chunfeng’),(2,’wenyi’),(3,’renmin’);

insert into authors(authID,authName)
values(1,’Jack’),(2,’Jackson’),(3,’Lucy’),(4,’Mary’),(5,’Rebecca’);

insert into language(langID,langName)
values(1,’Chinese’),(2,’English’),(3,’German’),(4,’Japanese’),(5,’Spanish’);

insert into rel_title_author(authID,titleID)
values(1,1),(4,2),(5,3),(3,4),(2,5),(5,6);

补入新纪录（包括一名新作者和一本新书）
insert into authors(authName)
values(’Isza’);
select last_insert_id();
insert into titles(title,publID,langID,year)
values(’pearl’,1,3,2003);
select last_insert_id();
insert into rel_title_author(titleID,authID)
values(8,6),(8,7);

显示结果
select publID from publishers where publName=’chunfeng’;

SELECT title,year,authName,publName,langName
from titles,publishers,authors,language,rel_title_author
where titles.publID=publishers.publID
and titles.langID=language.langID
and rel_title_author.titleID=titles.titleID
and rel_title_author.authID=authors.authID
order by title;

select authNAme from authors order by authName desc;（desc 表示倒序）

select * from titles; （*表示全部）

改变数据值
update titles set langID=2 where titleID=7;
update titles set title=’php’ where publID=4;

删除记录
delete titles from titles where year=0;（注意限定条件不要忘了）

禁止、打开外键约束条件
set foreign_key_checks=0;
set foreign_key_checks=1;

录入中文数据
set names ‘utf8′;
show variables like ‘character_set\_%’;
再建表 改变table options 为utf8
录入数据

待确定
alter table titles add  title unique;

数据库中数据表导出
mysqldump -u root -p mylibrary > c:\testdumpfile2.sql（在dos 命令下，具体见onenote）
mysqldump -u username -p db_name >存盘路径和命名

数据库中数据表导入
mysql -uroot -hlocalhost -p mylibrarycpy < testdumpfile2.sql
mysql -u username -hlocalhost -p target_database_name < 文件名.sql