特殊:Badtitle/NS100:MySQL QuickStartGuide：修订间差异

来自Ubuntu中文
跳到导航跳到搜索
Wikibot（留言 | 贡献）
87,256次编辑
小 创建新页面为 '{{From|https://help.ubuntu.com/community/MySQL%7EQuickStartGuide}} {{Languages|UbuntuHelp:MySQL%7EQuickStartGuide}} Edit this file using the required tags then copy an paste the...'
 
Wikibot（留言 | 贡献）
87,256次编辑
小无编辑摘要
 
第70行： 第70行：
So you have probably guessed that I am involved in no small way in bioinformatics research, if so you are correct.
So you have probably guessed that I am involved in no small way in bioinformatics research, if so you are correct.
-------
-------
== <<Anchor (Creating)>>Creating and populating your MySQL data tables ==
<<Anchor(Creating)>>
== Creating and populating your MySQL data tables ==
<<Anchor(FirstMySQLTable)>>
<<Anchor(FirstMySQLTable)>>
==== Create your first table ====
==== Create your first table ====
So you now now where to get a bunch of free data to send directly onto your MySQL server, but wait you need a table to put the data into!
So you now now where to get a bunch of free data to send directly onto your MySQL server, but wait you need a table to put the data into!
so lets create a nice shiny new table.... but wait you need a database to hold the table! OK so I'm getting a little carried ;) but I'm sure you get my point!
so lets create a nice shiny new table.... but wait you need a database to hold the table! OK so I'm getting a little carried away ;) but I'm sure you get my point!
so you will need to do the login with the following code.
so you will need to login with the following code.
<pre><nowiki>
<pre><nowiki>
mysql -u userName -p
mysql -u userName -p
</nowiki></pre>  
</nowiki></pre>  
You will now be connected to the MySQL server that you have installed, it has asked you for your password, and you get the standard < mysql > type prompt.
You will now be connected to the MySQL server that you have installed, it has asked you for your password, and you get the standard < mysql > type prompt.
No if as I have suggested you have downloaded the gene_data.gz file, now is the moment to unzip it to a directory of your choice, I'm going to choose the /tmp directory, and I'll name the file gene_info.txt, as that way there I can do less typing! Bear in mind however that if you do the same you will loose your file when you turn off your pc. Also you need to make sure you have read access to the file, so again the /tmp folder is good to ensure that you do. As I mentioned earlier this is a monster of a flat file, but we need to ensure that our table column names in same way match up to the data that this file contains, if you aren't willing to trust me you can check out the first line(s) with the following code in a terminal..
Now if as I have suggested you have downloaded the gene_data.gz file, now is the moment to unzip it to a directory of your choice, I'm going to choose the /tmp directory, and I'll name the file gene_info.txt, as that way I can do less typing!
Bear in mind however that if you do the same you will loose your file when you turn off your pc. Also you need to make sure you have read access to the file, so again the /tmp folder is good to ensure that you do. As I mentioned earlier this is a monster of a flat file, but we need to ensure that our table column names in same way match up to the data that this file contains, if you aren't willing to trust me you can check out the first line(s) with the following code in a terminal..
<pre><nowiki>
<pre><nowiki>
head /tmp/gene_info
head /tmp/gene_info.txt
</nowiki></pre>
</nowiki></pre>
Ok so after doing this you will have the first 10 lines of the file, the first line tells us what we want to know
Ok so after doing this you will have the first 10 lines of the file, the first line tells us what we want to know
第88行： 第90行：
#Format: tax_id GeneID Symbol LocusTag Synonyms dbXrefs chromosome map_location description type_of_gene Symbol_from_nomenclature_authority Full_name_from_nomenclature_authority Nomenclature_status Other_designations Modification_date (tab is used as a separator, pound sign - start of a comment)
#Format: tax_id GeneID Symbol LocusTag Synonyms dbXrefs chromosome map_location description type_of_gene Symbol_from_nomenclature_authority Full_name_from_nomenclature_authority Nomenclature_status Other_designations Modification_date (tab is used as a separator, pound sign - start of a comment)
</nowiki></pre>
</nowiki></pre>
so now we now what to call our table columns. The next code snippit will create a database, and then create a table that has the desired structure.
so now we now what to call our table columns. The next code snipit will create a database, and then create a table that has the desired structure.
<pre><nowiki>
<pre><nowiki>
create database NCBI;
create database NCBI;
第102行： 第104行：
</nowiki></pre>
</nowiki></pre>
Now if you are observant you will have noticed a few things here, firstly the column heading I've used are not in sync with the number of columns in the file - I've only actually used 6 out of the available 15. I've done this purposely to enable the demonstration of some of the clever stuff you can do with MySQL
Now if you are observant you will have noticed a few things here, firstly the column heading I've used are not in sync with the number of columns in the file - I've only actually used 6 out of the available 15. I've done this purposely to enable the demonstration of some of the clever stuff you can do with MySQL
When a line terminates with a ';' the MySQL server will interpret everything upto before as a single command. You could simply copy and paste the above lines into the monitor and you shouldn't get any errors. If you do, tell me, as I've obviously made a boob in my code, or you version is different and I need to put in a note for other to realise this.
When a line terminates with a ';' the MySQL server will interpret everything upto it as a single command. You could simply copy and paste the above lines into the monitor and you shouldn't get any errors. If you do, tell me, as I've obviously made a boob in my code, or your version of mysql may be different and I need to put in a note for other to realise this.
a quick explain of the commands. The first 2 should be obvious, creation of a new database called NCBI and then "use" this database hooks us into that particular database. We could of course use fully qualified database.table names, but I'll come to that later also in the [[UbuntuHelp:AdvancedQueries|Creating and understanding more advanced queries]] section.
A quick explaination of the commands. The first 2 should be obvious, creation of a new database called NCBI and then "use" this database hooks us into that particular database. We could of course use fully qualified database.table names, but I'll come to that later also in the [[UbuntuHelp:AdvancedQueries|Creating and understanding more advanced queries]] section.
For now the basic syntax for creating a table in MySQL is as follows
For now the basic syntax for creating a table in MySQL is as follows
<pre><nowiki>
<pre><nowiki>
第110行： 第112行：
So hopefull this is also fairly obvious.
So hopefull this is also fairly obvious.
tableName - is the name you have given to your table (again this could have been a fully qualified name in which case it would have looked < databaseName.tableName >  
tableName - is the name you have given to your table (again this could have been a fully qualified name in which case it would have looked < databaseName.tableName >  
columnName - again the name you have given to your column (from above this would be EntrezGeneID,  
columnName - again the name you have given to your column (from above this would be EntrezGeneID, symbol, full_nameHGNC, NCBI_Tax_ID, UniProt_ID, or EMBL_ID).
symbol, full_nameHGNC, NCBI_Tax_ID, UniProt_ID, or EMBL_ID).
dataType - this telly the server what type of data it can accept for this column, the options are mostly self explanatory such as INT, varchar (for "variable character" I suppose?), etc, there are lots more numeric and "string" types (such as BLOB (binary long object), longText) and date types [http://dev.mysql.com/doc/refman-5.0en.html-chapter/data-types this page] is the place to look in the online docs for the relevant pages.
dataType - this telly the server what type of data it can accept for this column, the option are mostly self explanatory such as INT, varchar (for "variable character" I suppose?), etc, there are lots more numeric and "string" types (such as BLOB (binary long object), longText) and date types [http://dev.mysql.com/doc/refman-5.0en.html-chapter/data-types this page] is the place to look in the online docs for the relevant pages.
specialStuff - I've included everything else in this note! - maybe not very sensible but here goes...
specialStuff - I've included everything else in this note! - maybe not very sensible but here goes...
the specialStuff that you can do with a column relates to things such as a column being a Key (primary or otherwise) foreignKey references, again I suggest that you have a look at the relevant pages, you should search for terms such as <Column Flags>, <Partitioning> and  <Storage Engines>. It is a little beyond the scope of this "beginers guide" to delve into these things too deeply, better still the text [[UbuntuHelp:Mysql in a nutShel|Mysql in a nutShel]] is where I learnt most of my stuff from.
the specialStuff that you can do with a column relates to things such as a column being a Key (primary or otherwise) foreignKey references, again I suggest that you have a look at the relevant pages, you should search for terms such as <Column Flags>, <Partitioning> and  <Storage Engines>. It is a little beyond the scope of this "beginers guide" to delve into these things too deeply, better still the text [[UbuntuHelp:Mysql in a nutShel|Mysql in a nutShel]] is where I learnt most of my stuff from.
Now you also need to now how to add and remove columns to a table, so I'll show you very quickly.  
Now you also need to now how to add and remove columns to a table, so I'll show you very quickly.  
<<Anchor(FirstMySQLTablePopulate>>
<<Anchor(FirstMySQLTablePopulate>>
==== populate the table with data ====  
==== populate the table with data ====
The follwing code will create a table in your newly made database.
<pre><nowiki>create table gene_info (
`EntrezGeneID` varchar(20) default NULL,
`LocalID` varchar(20) default NULL,
`nameHGNC` varchar(250) default NULL,
`NCBI_Tax_ID` varchar(20) default NULL,
`UniProt_ID` varchar(20) default NULL,
`EMBL_ID` varchar(20) default NUL)L;
</nowiki></pre>
<<Anchor(queries)>>
<<Anchor(queries)>>
==== Creating queries to get informatin from your database ====
==== Creating queries to get informatin from your database ====

2010年5月19日 (三) 23:29的最新版本

文章出处: 

{{#if: | {{{2}}} | https://help.ubuntu.com/community/MySQL%7EQuickStartGuide }}

点击翻译: 

English {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/af |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|Afrikaans| [[::MySQL QuickStartGuide/af|Afrikaans]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/ar |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|العربية| [[::MySQL QuickStartGuide/ar|العربية]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/az |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|azərbaycanca| [[::MySQL QuickStartGuide/az|azərbaycanca]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/bcc |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|جهلسری بلوچی| [[::MySQL QuickStartGuide/bcc|جهلسری بلوچی]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/bg |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|български| [[::MySQL QuickStartGuide/bg|български]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/br |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|brezhoneg| [[::MySQL QuickStartGuide/br|brezhoneg]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/ca |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|català| [[::MySQL QuickStartGuide/ca|català]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/cs |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|čeština| [[::MySQL QuickStartGuide/cs|čeština]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/de |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|Deutsch| [[::MySQL QuickStartGuide/de|Deutsch]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/el |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|Ελληνικά| [[::MySQL QuickStartGuide/el|Ελληνικά]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/es |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|español| [[::MySQL QuickStartGuide/es|español]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/fa |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|فارسی| [[::MySQL QuickStartGuide/fa|فارسی]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/fi |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|suomi| [[::MySQL QuickStartGuide/fi|suomi]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/fr |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|français| [[::MySQL QuickStartGuide/fr|français]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/gu |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|ગુજરાતી| [[::MySQL QuickStartGuide/gu|ગુજરાતી]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/he |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|עברית| [[::MySQL QuickStartGuide/he|עברית]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/hu |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|magyar| [[::MySQL QuickStartGuide/hu|magyar]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/id |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|Bahasa Indonesia| [[::MySQL QuickStartGuide/id|Bahasa Indonesia]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/it |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|italiano| [[::MySQL QuickStartGuide/it|italiano]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/ja |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|日本語| [[::MySQL QuickStartGuide/ja|日本語]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/ko |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|한국어| [[::MySQL QuickStartGuide/ko|한국어]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/ksh |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|Ripoarisch| [[::MySQL QuickStartGuide/ksh|Ripoarisch]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/mr |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|मराठी| [[::MySQL QuickStartGuide/mr|मराठी]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/ms |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|Bahasa Melayu| [[::MySQL QuickStartGuide/ms|Bahasa Melayu]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/nl |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|Nederlands| [[::MySQL QuickStartGuide/nl|Nederlands]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/no |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|norsk| [[::MySQL QuickStartGuide/no|norsk]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/oc |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|occitan| [[::MySQL QuickStartGuide/oc|occitan]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/pl |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|polski| [[::MySQL QuickStartGuide/pl|polski]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/pt |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|português| [[::MySQL QuickStartGuide/pt|português]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/ro |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|română| [[::MySQL QuickStartGuide/ro|română]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/ru |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|русский| [[::MySQL QuickStartGuide/ru|русский]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/si |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|සිංහල| [[::MySQL QuickStartGuide/si|සිංහල]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/sq |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|shqip| [[::MySQL QuickStartGuide/sq|shqip]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/sr |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|српски / srpski| [[::MySQL QuickStartGuide/sr|српски / srpski]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/sv |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|svenska| [[::MySQL QuickStartGuide/sv|svenska]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/th |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|ไทย| [[::MySQL QuickStartGuide/th|ไทย]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/tr |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|Türkçe| [[::MySQL QuickStartGuide/tr|Türkçe]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/vi |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|Tiếng Việt| [[::MySQL QuickStartGuide/vi|Tiếng Việt]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/yue |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|粵語| [[::MySQL QuickStartGuide/yue|粵語]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/zh |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|中文| [[::MySQL QuickStartGuide/zh|中文]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/zh-hans |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|中文（简体）| [[::MySQL QuickStartGuide/zh-hans|中文（简体）]]}}|}} {{#ifexist: {{#if: UbuntuHelp:MySQL%7EQuickStartGuide | UbuntuHelp:MySQL%7EQuickStartGuide | {{#if: | :}}MySQL QuickStartGuide}}/zh-hant |  • {{#if: UbuntuHelp:MySQL%7EQuickStartGuide|中文（繁體）| [[::MySQL QuickStartGuide/zh-hant|中文（繁體）]]}}|}}

{{#ifeq:UbuntuHelp:MySQL%7EQuickStartGuide|:MySQL QuickStartGuide|请不要直接编辑翻译本页，本页将定期与来源同步。}}

{{#ifexist: :MySQL QuickStartGuide/zh | | {{#ifexist: MySQL QuickStartGuide/zh | | {{#ifeq: {{#titleparts:MySQL QuickStartGuide|1|-1|}} | zh | | }} }} }} {{#ifeq: {{#titleparts:MySQL QuickStartGuide|1|-1|}} | zh | | }}

Edit this file using the required tags then copy an paste the completed page onto the web site. this is the page https://help.ubuntu.com/community/MySQL~QuickStartGuide

MySQL

A quick start Guide

pages under construction please visit regularly to see the updates Appologies for the slow generation of this page. I'm a bit stuck at the moment, I've got a lot of projects to finish for my masters and this has now come to a halt for a little while. Hopefully I'll be able to get back to it in a month or so. When this page becomes live I will put in a link to a forum page that I will "inhabit"

First off, let me apologise for this page, it is my first in the wiki, so it probably doesn't meet any of the ubuntu wiki standards.

To Business

so you have come to this page as you hope to get a quick start guide to the syntax of MySQL This is good, as that is what I intend to give!

Why did I start this page?

When I first started using MySQL I obviously needed to creates tables and all sorts of stuff, I did this by using public data information (which I will point you too). I had persistently been put off delving into MySQL as I didn't have the time, or inclination, to create tables that where going to be a catalogue of my CD / DVD / Book collection. So as mentioned earlier, this guide will aim to do the following...

  • Help you create your first tables

. giving syntax for the tables and how to populate them with data . pointers for where to get a large quantity of data, all of it free ;-)

  • Create select statement

. demonstrate how you can drill down into your new data. . cross referencing and joining tables.

<<Anchor(menu)>>

menu

  • [[UbuntuHelp:[preRequisites| Pre Requisites]]] what you need to before you get started.

.[[UbuntuHelp:[PreRequisitesMysql|MySQL]]] Ok so that may be a bit obvious ;-) .[[UbuntuHelp:[PreRequisitesMySQLUsers|Add users]]] Create Admin and General Users.

  • [[UbuntuHelp:[FindingData|Finding Data]]] Pointers to were to get your data.

.[[UbuntuHelp:[FindingDataResourceLocation|usefull links]]] simple a list of links and the files you are looking out for.

  • [[UbuntuHelp:[Creating|Creating and populating your MySQL data tables]]]

.[[UbuntuHelp:[FirstMySQLTable|Create your first table]]] .[[UbuntuHelp:[FirstMySQLTablePopulate|populate the table with data]]]

  • [[UbuntuHelp:[Queries|Creating queries of your data]]]

.[[UbuntuHelp:[AdvancedQueries|Creating and understanding more advanced queries]]]

  • [[UbuntuHelp:[References | References]]]

.[[UbuntuHelp:[ReferencesNetography|Netography]]] links to useful pages. .[[UbuntuHelp:[ReferencesBibliography|Bibliography]]] some text books to read. .[[UbuntuHelp:[ReferencesThanks|Thanyou]]] a big thankyou to some specific people who have made these pages possible.

<<Anchor(preRequisites)>>

Pre Requisites

So these are the things you will obviously require prior to getting started. <<Anchor(PreRequisitesMysql)>>

MySQL

avilable in the repo's, there are numerous guides on installing MySQL, and the ones on the ubuntu wiki are preety hot stuff, check them out, here are the links. MYSQL5FromSource I would currently recommend version 5 (or even 6), I have version 4 at a location and some of these commands may not work as you would expect (eg. a search with an inner sub search that contains an inner subsearch... I know it sounds confusing but I'll get to this later, so don't worry about it for now). I installed from source, and in fact it was so easy I have started to install other stuff from source now also, and it isn't as scary as you may at first expect. Also on the wiki there are lots of other usefull informatin, try this search <<Anchor(PreRequisitesMySQLUsers)>>

MySQL Users

I am also asuming that you have set up a new MySQL admin user and new non-admin user also, this is generaly good practice, however for the majority of this tutorial due to the nature of what I will be asking you to do you will need to be logged into the MySQL monitor via an admin login. unserstandably that is about it for the pre-requisites page, If you have an instal of MySQL and have organised yourself with admin / user / password combination you should be set to go.

<<Anchor(FindingData)>>

Finding data for your tables.

Before we can start really understanding the MySQL syntax we need to find data to put into our tables. <<Anchor(FindingDataResourceLocation)>> Here are the links to various site. The NCBI Home page Is one of the largest collections of data that there is, what makes it so large is the nature of the data (information on all the human genetic code, mouse, ecole, various yeasts, and a whole load of other medically interesting creatures that grunt and smell!). It is also a good site to demonstrate the power of MySQL as all of their data is stored in MySQL tables. However to be really usefull you want to get hold of some of the flat files they create, so you'll want to look at their ftp site here and download the gene_info_gz file, the other files are also of interest but this is the "biggie" - later on you will unzip it and import it into a nice shiny new MySQL table, then you will use a MySQL function to count the lines, and notice that there are over 5 million of them - all stored in a tab separated text file. The Mouse Genome Institute A repository of the current mouse genome data (most of which is replicated within the NCBI data tables, but the MGI have it organised in a different manner, which is better for our purposes. This is their ftp site data page you'll notice that this is a regular file that is updated every week... give or take. This will be important later on! Ensembl this is the european version of the NCBI, interestingly you can get direct access to their MySQL tables (unlike the NCBI where you will need to download their special tools for direct access), instructions to do this are here, I will come back to this later on. So you have probably guessed that I am involved in no small way in bioinformatics research, if so you are correct.

<<Anchor(Creating)>>

Creating and populating your MySQL data tables

<<Anchor(FirstMySQLTable)>>

Create your first table

So you now now where to get a bunch of free data to send directly onto your MySQL server, but wait you need a table to put the data into! so lets create a nice shiny new table.... but wait you need a database to hold the table! OK so I'm getting a little carried away ;) but I'm sure you get my point! so you will need to login with the following code. 

mysql -u userName -p

You will now be connected to the MySQL server that you have installed, it has asked you for your password, and you get the standard < mysql > type prompt. Now if as I have suggested you have downloaded the gene_data.gz file, now is the moment to unzip it to a directory of your choice, I'm going to choose the /tmp directory, and I'll name the file gene_info.txt, as that way I can do less typing! Bear in mind however that if you do the same you will loose your file when you turn off your pc. Also you need to make sure you have read access to the file, so again the /tmp folder is good to ensure that you do. As I mentioned earlier this is a monster of a flat file, but we need to ensure that our table column names in same way match up to the data that this file contains, if you aren't willing to trust me you can check out the first line(s) with the following code in a terminal.. 

head /tmp/gene_info.txt

Ok so after doing this you will have the first 10 lines of the file, the first line tells us what we want to know 

#Format: tax_id GeneID Symbol LocusTag Synonyms dbXrefs chromosome map_location description type_of_gene Symbol_from_nomenclature_authority Full_name_from_nomenclature_authority Nomenclature_status Other_designations Modification_date (tab is used as a separator, pound sign - start of a comment)

so now we now what to call our table columns. The next code snipit will create a database, and then create a table that has the desired structure. 

create database NCBI;
use NCBI;
create table NCBI (
EntrezGeneID varchar(20) default NULL,
symbol varchar(20) default NULL,
full_nameHGNC varchar(250) default NULL,
NCBI_Tax_ID varchar(20) default NULL,
UniProt_ID varchar(20) default NULL,
EMBL_ID varchar(20) default NULL);

Now if you are observant you will have noticed a few things here, firstly the column heading I've used are not in sync with the number of columns in the file - I've only actually used 6 out of the available 15. I've done this purposely to enable the demonstration of some of the clever stuff you can do with MySQL When a line terminates with a ';' the MySQL server will interpret everything upto it as a single command. You could simply copy and paste the above lines into the monitor and you shouldn't get any errors. If you do, tell me, as I've obviously made a boob in my code, or your version of mysql may be different and I need to put in a note for other to realise this. A quick explaination of the commands. The first 2 should be obvious, creation of a new database called NCBI and then "use" this database hooks us into that particular database. We could of course use fully qualified database.table names, but I'll come to that later also in the Creating and understanding more advanced queries section. For now the basic syntax for creating a table in MySQL is as follows 

create table tableName (columnName dataType(length) specialColumnStuff specialTableStuff;

So hopefull this is also fairly obvious. tableName - is the name you have given to your table (again this could have been a fully qualified name in which case it would have looked < databaseName.tableName > columnName - again the name you have given to your column (from above this would be EntrezGeneID, symbol, full_nameHGNC, NCBI_Tax_ID, UniProt_ID, or EMBL_ID). dataType - this telly the server what type of data it can accept for this column, the options are mostly self explanatory such as INT, varchar (for "variable character" I suppose?), etc, there are lots more numeric and "string" types (such as BLOB (binary long object), longText) and date types this page is the place to look in the online docs for the relevant pages. specialStuff - I've included everything else in this note! - maybe not very sensible but here goes... the specialStuff that you can do with a column relates to things such as a column being a Key (primary or otherwise) foreignKey references, again I suggest that you have a look at the relevant pages, you should search for terms such as <Column Flags>, <Partitioning> and <Storage Engines>. It is a little beyond the scope of this "beginers guide" to delve into these things too deeply, better still the text Mysql in a nutShel is where I learnt most of my stuff from. Now you also need to now how to add and remove columns to a table, so I'll show you very quickly. <<Anchor(FirstMySQLTablePopulate>>

populate the table with data

The follwing code will create a table in your newly made database. 

create table gene_info (
 `EntrezGeneID` varchar(20) default NULL,
`LocalID` varchar(20) default NULL,
`nameHGNC` varchar(250) default NULL,
`NCBI_Tax_ID` varchar(20) default NULL,
`UniProt_ID` varchar(20) default NULL,
`EMBL_ID` varchar(20) default NUL)L;

<<Anchor(queries)>>

Creating queries to get informatin from your database

<<Anchor(AdvancedQueries)>>

Creating and understanding more advanced queries

I guess really I am now moving past the idea of this being a beginner guide, but as I'm getting a little carried away I would kindly ask that you stay with me on this one!

Example

sample code

Display

xxx

<<Anchor(References)>>

References

web references

<<Anchor(Mysql in a nutShel)>>mysqlresources This is a page started by the guy who wrote the excelent book <Mysql in a nutShel> The Home page Obviously I can't miss out the main MySQL home page The Documentation pages Links to all the versions documentation pages. Normalising your tables Read this often whenever you start a new database project, as a reminder

取自“https://wiki.ubuntu.org.cn/index.php?title=特殊:Badtitle/NS100:MySQL_QuickStartGuide&oldid=139160”