Yadda za a rubuta SQL-queries - daki-daki misalai

Kowannen mu a kai a kai fuskantar da kuma amfani da dama databases. Lokacin da muka zabi e-mail address, muna aiki tare da database. database ta amfani da search sabis, bankuna domin ajiya na abokin ciniki data, da dai sauransu

Amma duk da akai amfani da database, har ma ga dama software tsarin developers ne mai yawa "fari aibobi" saboda daban-daban fassarori guda sharuddan. Mun bayar da taƙaitaccen definition daga cikin manyan sharuddan to databases na SQL shawara. So.

Database - fayil ko kafa na fayiloli adanar umurce data Tsarin da su dangantaka. Sau da yawa sosai, da database da aka kira a database management system (DBMS). Database - dai kawai wani mangaza na bayanai a wani takamaiman format da kuma iya aiki tare da daban-daban bayanai.

Table - tunanin fayil a wadda takardun da aka adana, harhada bisa ga wasu sigogi, kamar jerin umarni ga watan da ya gabata. Wannan shi ne tebur a kwamfuta database. A raba tebur na da musamman sunan.

Data Type - da irin bayanai a yarda da za a adana a wani guda shafi ko jere. Yana iya zama wani takamaiman adadin ko rubutu format.

Column da kuma jere - mu duka yi aiki tare da maƙunsar, wanda ma ba a cikin layuka da kuma ginshikan. Wani zumunci database aiki tare da alluna guda hanyar. Kirtani wani lokaci ana kira records.

A farko key - kowane layi na iya samun daya ko fiye ginshikan domin ta musamman ganewa. Babu farko key shi ne da wuya sosai sabunta, gyara da kuma share dacewa layuka.

Mene ne SQL?

Tambaya harshen SQL (English ginannun tambaya Harshe -. Ginannun tambaya Harshe) da aka tsara kawai yin aiki tare da bayanai da kuma shi ne a halin yanzu da misali ga dukan rare databases. A harshen ginin kalma an yi sama da karamin yawan aiki da kuma sauki koyi. Amma duk da bayyana sauki, shi damar halittar SQL queries ga hadaddun ayyukan da database na wani size.

Tun 1992, akwai wani na kowa misali, da ake kira ANSI SQL. Yana ma'anar asali ginin kalma da kuma ayyuka na aiki da kuma goyon bayan duk DBMS kasuwar shugabanni, kamar Oracle da Microsoft da SQL Server. Ka yi la'akari da abin da fasali na harshe a cikin wani gajeren labarin ne ba zai yiwu ba, saboda haka za mu bincika a ta aice kawai na asali SQL queries. Misalai nuna sauki da kuma siffofin da harshen:

• samar da bayanai da kuma allunan.
• data selection.
• Ƙara shigarwar.
• gyara da kuma shafewa na bayanai.

SQL Data Iri

All ginshiƙai a cikin wani database tebur adana guda data type. a SQL bayanai iri iri daya ne kamar yadda a wasu shirye-shirye da harsuna.

data type	description
int	integers
REAL	Shawagi ma'ana lambobin
rubutu	Character kirtani na m tsawon
DATE	SQL tambaya "kwanan wata" a cikin wani iri-iri-tsaren
TIME	lokaci
Char	Text kirtani na ajali tsawon

Ƙirƙiri alluna da databases

Create new databases, alluna, da kuma sauran queries a SQL Akwai hanyoyi biyu:

• SQL kalamai ta cikin database wasan bidiyo
• Amfani da m kayan aikin Administration, wani ɓangare na database uwar garke.

Wani sabon database sadarwarka Create BAYANANKA ; . Kamar yadda ka gani, da ginin kalma ne mai sauki da kuma dunkule.

Allunan cikin database haifar da Create BAYA sanarwa da wadannan sigogi:

• tebur sunan
• sunayen da bayanai iri ginshikan

A matsayin misali, haifar da wata haja tebur da wadannan ginshikan:

shafi	description
commodity_id	samfurin ID
vendor_id	Mai sayarwa ID (Key dillalai waje tebur)
commodity_name	samfurin sunan
commodity_price	kudin na
commodity_desc	description

Create a tebur:

Create BAYA kayayyaki

(Commodity_id Char (15) BA ɓãci,

vendor_id Char (15) BA ɓãci,

commodity_name Char (254) Mara doka,

commodity_price gidan goma (8,2) Mara doka,

commodity_desc VARCHAR (1000) null).

Tebur ƙunshi biyar ginshikan. Bayan da sunan ne irin data ginshikan rabu da wakafi. A shafi darajar iya ɗauka null dabi'u (null) ko Tilas a cika a (BA null), kuma shi ne niyya don ƙirƙirar wani tebur.

Samfurin data daga tebur

Samfur sadarwarka data - da aka fi amfani SQL queries. Don ƙarin bayani, kana bukatar ka saka cewa muna so mu zaɓi daga wani tebur. A farko mai sauki misali:

Zabi commodity_name DAGA kayayyaki

Bayan da Zabi sanarwa nuna sunan shafi don samun bayanai, amma DAGA gano tebur.

tambayar sakamakon zai zama duk tebur layuka da Commodity_name dabi'u a cikin tsari a cikin abin da suka kasance shiga database Ina nufin ba tare da wani kasawa. Don shirya da sakamakon yin amfani da tilas sadarwarka domin BY.

Don tambayi a kan fannoni daban daban lissafa su rabu da wakafi kamar yadda a cikin wadannan misali:

Zabi commodity_id, commodity_name, commodity_price DAGA kayayyaki

Yana yiwuwa a samu kamar yadda sakamakon da tambaya kirtani darajar duk ginshikan. Domin wannan dalili, da alamar "*":

Zabi * DAGA kayayyaki

• Zabi bugu da žari yana goyan bayan:
• Sort data (domin BY bayani)
• Selection bisa ga yanayin (INA)
• Ra'ayoyi lokaci (GROUP BY)

ƙara layi

Don ƙara layuka zuwa teburin yin amfani da SQL kalamai tare da saka bayani. Bugu da kari za a iya sanya a cikin hanyoyi uku:

• ƙara wani sabon dukan line.
• na line.
• query da sakamakon.

Don ƙara cikakken line, dole ne ka saka tebur sunan da dabi'u ginshikan (filayen) na wani sabon line. Ga wani misali:

Sawa a cikin kayayyaki dabi'u ( '106', '50', 'Coca-Cola', '1,68', 'A'a Alcogol,)

Misali in ji wani sabon samfurin da tebur. Dabi'u ana kayyade bayan dabi'u ga kowane shafi. Idan babu daidai darajar da shafi, shi wajibi ne don saka ɓãci. Ginshikan suna cike da dabi'u a cikin tsari kayyade lokacin da samar da wani tebur.

A cikin hali na ƙara kawai wani sashi na kirtani dole ne baro-baro saka sunan shafi, kamar yadda a Example:

Sawa a cikin kayayyaki (commodity_id, vendor_id, commodity_name)

Dabi'u ( '106', '50', 'Coca-Cola', )

Mun gabatar da kawai samfurin identifiers, maroki, kuma da sunan da, da kuma sauran filin ajiye komai.

Ƙara tambaya sakamakon

Amma, saka ake amfani da su ƙara a jere, amma za a iya amfani da shi don kara wani Zabi sanarwa sakamakon.

canji data

Don canja bayanai a cikin database tebur filayen bukatar amfani da wani UPDATE sanarwa. Mai aiki za a iya amfani da hanyoyi biyu:

• Updated duk layuka a cikin tebur.
• Kawai ga wani line.

UPDATE kunshi uku main abubuwa:

• a tebur a wadda ka ke so ka yi canje-canje.
• filin sunayen da sabon dabi'u;
• Yanayi na jere selection canje-canje.

Ga wani misali. Bari mu ce da samfurin tare da ID = 106 ya canza farashin, saboda haka wannan layi to sabunta. Rubuta da wadannan bayani:

UPDATE kayayyaki SET commodity_price = '3.2' INA commodity_id = ' 106'

Mun nuna tebur sunan, a cikin wannan yanayin, da kayayyaki, wanda za a sabunta, sa'an nan kuma bayan da SET - sabon darajar da shafi da kuma samun ake so abu, tantancewa da INA so darajar ID.

Don canja yawan ginshikan bayan da SET sanarwa ƙayyade dama nau'i-nau'i daga shafi-darajar, rabu da wakafi. Dubi misali a cikin abin da sabunta sunan da farashin na dukiya:

UPDATE kayayyaki SET commodity_name = 'Fanta' , commodity_price = '3.2' INA commodity_id = ' 106'

Don share bayanai a shafi, za ka iya saita shi zuwa ɓãci, idan shi damar da tsarin da tebur. Yana dole ne a tuna da cewa null - wannan ne "ba" darajar, ba sifili matsayin rubutu ko lambobi. Cire abun da bayanin:

UPDATE kayayyaki SET commodity_desc = null INA commodity_id = ' 106'

share layuka

SQL buƙatun don share layuka a cikin tebur ake yi share sadarwarka. Akwai hanyoyi biyu don amfani:

• Table share wasu layuka.
• cire duk layuka a cikin tebur.

MISALI cire daya jere daga tebur:

Shafe daga kayayyaki INA commodity_id = '106'

Bayan Shafe daga kayyade tebur sunan da ake so a share layuka. INA sadarwarka ya hada da wani yanayin karkashin wanda line za a zaba domin shafewa. A misali mu cire samfurin line tare da ID = 106. INA nuna muhimmanci sosai, saboda wuce wannan sadarwarka privedt cire duk layuka a cikin tebur. Wannan ya shafi canza filin dabi'u.

A share sanarwa ba saka shafi sunayen, da kuma meta-haruffa. Ya gaba daya ta kawar da layin da kuma share guda shafi, ya iya ba.

Amfani da SQL a Microsoft Access

Microsoft Access ne yawanci amfani interactively don ƙirƙirar falle, database, management canje-canje, da bincike na data a cikin database kuma domin aiwatar da queries SQL Access via wani m m tambaya gini (Tambaya Designer), ta amfani da ba za ka iya gina da kuma kashe SQL kalamai nan da nan wani rikitaccen.

Har ila yau, goyon yanayin damar yin amfani da uwar garke, cikinsa da database Access za a iya amfani da SQL-query janareta ga wani ODBC data Madogararsa. Wannan yanayin sa aikace-aikace don sadarwa tare da Access database a cikin wani format.

SQL kari

Saboda SQL queries ba su da duk procedural shirye-shirye da harshen fasali kamar madaukai, sashe, da dai sauransu, da DBMS masana'antun su inganta version na SQL tare da ci-gaba fasali. Na farko shi ne goyon baya ga adana hanyoyin da misali aiki procedural harsuna.

Mafi na kowa yaruka na harshe:

• Oracle Database - PL / SQL
• Interbase, Firebird - PSQL
• Microsoft SQL Server - ma'amala-SQL
• PostgreSQL - PL / pgSQL.

SQL Internet

MySQL database da aka saki a karkashin wani free lasisi GNU Janar Jama'a License. Akwai kasuwanci lasisi tare da ikon ci gaba al'ada kayayyaki. Kamar yadda wani ɓangare daga cikin rare ɓangare na ginawa Internet sabobin kamar XAMPP, WAMP da fitilar, kuma shi ne mafi m database aikace-aikace ci gaba a cikin Internet.

An ci gaba da Sun Microsystems, kuma a halin yanzu da goyan bayan Oracle. Goyan bayan database size up to 64 terabytes, da SQL ginin kalma: 2003, rubanyawar databases da girgije sabis.