MySQL查询数据

MySQL查询数据

在本教程中,您将学习如何使用MySQLSELECT语句从表或视图查询数据。

MySQL SELECT语句简介

使用SELECT语句从表或视图获取数据。表由行和列组成,如电子表格。 通常,我们只希望看到子集行,列的子集或两者的组合。SELECT语句的结果称为结果集,它是行列表,每行由相同数量的列组成。

请参阅示例数据库(yiibaidb)中的以下employees表的结构。它有8列:员工人数,姓氏,名字,分机,电子邮件,办公室代码,报告,职位等。

SELECT语句控制要查看哪些列和行。例如,如果只对所有员工的名字,姓氏和职位感兴趣,或者您只想查看其职位是销售代表的每位员工的信息,则SELECT语句可帮助您执行这些操作。

我们来看一下SELECT语句的语法:

SELECT 
    column_1, column_2, ...
FROM
    table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
    conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;

SELECT语句由以下列表中所述的几个子句组成:

  • SELECT之后是逗号分隔列或星号(*)的列表,表示要返回所有列。
  • FROM指定要查询数据的表或视图。
  • JOIN根据某些连接条件从其他表中获取数据。
  • WHERE过滤结果集中的行。
  • GROUP BY将一组行组合成小分组,并对每个小分组应用聚合函数。
  • HAVING过滤器基于GROUP BY子句定义的小分组。
  • ORDER BY指定用于排序的列的列表。
  • LIMIT限制返回行的数量。

语句中的SELECTFROM语句是必须的,其他部分是可选的。

在随后的教程中将更详细地了解每个子句。在本教程中,我们将重点介绍SELECT语句的简单形式用法。

MySQL SELECT语句示例

SELECT语句允许通过在SELECT子句中指定逗号分隔列的列表来查询表的部分数据。 例如,如果要仅查看员工的名字,姓氏和职位,请使用以下查询:

SELECT 
    lastname, firstname, jobtitle
FROM
    employees;

即使员工表中有很多列,SELECT语句只返回表中所有行的三列数据,如下图所示:

mysql> SELECT lastname, firstname, jobtitle FROM employees;
+-----------+-----------+----------------------+
| lastname  | firstname | jobtitle             |
+-----------+-----------+----------------------+
| Murphy    | Diane     | President            |
| Patterson | Mary      | VP Sales             |
| Firrelli  | Jeff      | VP Marketing         |
| Patterson | William   | Sales Manager (APAC) |
| Bondur    | Gerard    | Sale Manager (EMEA)  |
| Bow       | Anthony   | Sales Manager (NA)   |
| Jennings  | Leslie    | Sales Rep            |
| Thompson  | Leslie    | Sales Rep            |
| Firrelli  | Julie     | Sales Rep            |
| Patterson | Steve     | Sales Rep            |
| Tseng     | Foon Yue  | Sales Rep            |
| Vanauf    | George    | Sales Rep            |
| Bondur    | Loui      | Sales Rep            |
| Hernandez | Gerard    | Sales Rep            |
| Castillo  | Pamela    | Sales Rep            |
| Bott      | Larry     | Sales Rep            |
| Jones     | Barry     | Sales Rep            |
| Fixter    | Andy      | Sales Rep            |
| Marsh     | Peter     | Sales Rep            |
| King      | Tom       | Sales Rep            |
| Nishi     | Mami      | Sales Rep            |
| Kato      | Yoshimi   | Sales Rep            |
| Gerard    | Martin    | Sales Rep            |
+-----------+-----------+----------------------+
23 rows in set

注意比较以下两个语句返回列有什么区别 –

语句-1

SELECT lastname, firstname, jobtitle FROM employees;

语句-2

SELECT * FROM employees;

如果要获取employees表中所有列的数据,可以列出SELECT子句中的所有列名,或者只需使用星号(*)表示您想要从表的所有列获取数据,如下查询:

mysql> SELECT * FROM employees;
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
| employeeNumber | lastName  | firstName | extension | email                 | officeCode | reportsTo | jobTitle             |
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
|           1002 | Murphy    | Diane     | x5800     | dmurphy@yiibai.com    | 1          | NULL      | President            |
|           1056 | Patterson | Mary      | x4611     | mpatterso@yiibai.com  | 1          |      1002 | VP Sales             |
|           1076 | Firrelli  | Jeff      | x9273     | jfirrelli@yiibai.com  | 1          |      1002 | VP Marketing         |
|           1088 | Patterson | William   | x4871     | wpatterson@yiibai.com | 6          |      1056 | Sales Manager (APAC) |
|           1102 | Bondur    | Gerard    | x5408     | gbondur@gmail.com     | 4          |      1056 | Sale Manager (EMEA)  |
|           1143 | Bow       | Anthony   | x5428     | abow@gmail.com        | 1          |      1056 | Sales Manager (NA)   |
|           1165 | Jennings  | Leslie    | x3291     | ljennings@yiibai.com  | 1          |      1143 | Sales Rep            |
|           1166 | Thompson  | Leslie    | x4065     | lthompson@yiibai.com  | 1          |      1143 | Sales Rep            |
|           1188 | Firrelli  | Julie     | x2173     | jfirrelli@yiibai.com  | 2          |      1143 | Sales Rep            |
|           1216 | Patterson | Steve     | x4334     | spatterson@yiibai.com | 2          |      1143 | Sales Rep            |
|           1286 | Tseng     | Foon Yue  | x2248     | ftseng@yiibai.com     | 3          |      1143 | Sales Rep            |
|           1323 | Vanauf    | George    | x4102     | gvanauf@yiibai.com    | 3          |      1143 | Sales Rep            |
|           1337 | Bondur    | Loui      | x6493     | lbondur@yiibai.com    | 4          |      1102 | Sales Rep            |
|           1370 | Hernandez | Gerard    | x2028     | ghernande@gmail.com   | 4          |      1102 | Sales Rep            |
|           1401 | Castillo  | Pamela    | x2759     | pcastillo@gmail.com   | 4          |      1102 | Sales Rep            |
|           1501 | Bott      | Larry     | x2311     | lbott@yiibai.com      | 7          |      1102 | Sales Rep            |
|           1504 | Jones     | Barry     | x102      | bjones@gmail.com      | 7          |      1102 | Sales Rep            |
|           1611 | Fixter    | Andy      | x101      | afixter@yiibai.com    | 6          |      1088 | Sales Rep            |
|           1612 | Marsh     | Peter     | x102      | pmarsh@yiibai.com     | 6          |      1088 | Sales Rep            |
|           1619 | King      | Tom       | x103      | tking@gmail.com       | 6          |      1088 | Sales Rep            |
|           1621 | Nishi     | Mami      | x101      | mnishi@gmail.com      | 5          |      1056 | Sales Rep            |
|           1625 | Kato      | Yoshimi   | x102      | ykato@gmail.com       | 5          |      1621 | Sales Rep            |
|           1702 | Gerard    | Martin    | x2312     | mgerard@gmail.com     | 4          |      1102 | Sales Rep            |
+----------------+-----------+-----------+-----------+-----------------------+------------+-----------+----------------------+
23 rows in set

它返回employees表中的所有列和行。应该使用星号(*)进行测试。建议显式获取数据的列,原因如下:

  • 使用星号(*)可能会返回不使用的列的数据。 它在MySQL数据库服务器和应用程序之间产生不必要的I/O磁盘和网络流量。
  • 如果明确指定列,则结果集更可预测并且更易于管理。 想象一下,当您使用星号(*)并且有人通过添加更多列来更改表格数据时,将会得到一个与预期不同的结果集。
  • 使用星号(*)可能会将敏感信息暴露给未经授权的用户。

在本教程中,您已经了解并熟悉了MySQL SELECT语句的用法,并通过SELECT语句从MySQL表中查询数据。

 

移动端:请扫描本页面底部(右侧)二维码并关注微信公众号,回复:”教程” 选择相关教程阅读

92 Responses

  1. Kjamvp说道:

    order generic escitalopram brand escitalopram 10mg buy naltrexone for sale

  2. Nxtftk说道:

    purchase lasix without prescription order acticlate buy ventolin medication

  3. Thstwa说道:

    strattera 25mg cheap order zoloft 50mg for sale brand sertraline 50mg

  4. Fjujei说道:

    buy urso 150mg online cheap order generic zyban 150mg zyrtec canada

  5. Rzbayk说道:

    buy azithromycin 250mg without prescription cheap zithromax 250mg neurontin 100mg oral

  6. Meqozf说道:

    medicine for ulcer liquid how to stop tooting fart smells like death

  7. Vzxilo说道:

    deltasone pill deltasone 10mg cheap order amoxicillin 250mg online

  8. Cwcotu说道:

    birth control pills online prescription micturition syncope nhs top rated semen enhancers

  9. Glsaxq说道:

    purchase promethazine pill buy erectile dysfunction drugs stromectol liquid

  10. Yyqcnq说道:

    endoscopy images of gastritis vasodilators adverse effects get uti prescription online

  11. Ihuifj说道:

    duloxetine 40mg uk glucotrol 5mg drug provigil pill

  12. Xnudhw说道:

    what is lamisil cream used for most common antifungal drugs cheapest online prescription refill

  13. Pzjevj说道:

    antiviral medication for hsv 2 ivax salamol cfc free inhaler type 2 diabetes cure breakthrough

  14. Zmjeqn说道:

    medication to quit smoking antidepressant list of medicine for arthritis strongest pain relief medication

  15. Gzbkqp说道:

    medroxyprogesterone 5mg pills cheap biltricide 600 mg purchase microzide online cheap

  16. Nezaud说道:

    super strong sleeping pills best diet pills from mexico weight loss drugs prescription online

  17. Eyugyu说道:

    letrozole 2.5 mg cheap albendazole 400 mg pill buy abilify without a prescription

  18. Evjhas说道:

    uroxatral us prescription vs over the counter medicines for heartburn by doctor

  19. Phdkre说道:

    minocycline where to buy order minocycline 100mg pills ropinirole over the counter

  20. Zlkxrb说道:

    best cure for teenage acne adult acne medication at ulta buy oxcarbazepine sale

  21. Jwqyfd说道:

    clonidine cost buy clonidine 0.1mg sale tiotropium bromide buy online

  22. Yeplcm说道:

    buy rocaltrol 0.25 mg for sale oral labetalol 100mg order tricor without prescription

  23. Lgucxa说道:

    trimox 250mg price order arimidex sale biaxin cheap

  24. Rwivkq说道:

    best college paper writing service real casino games quick hits free online slots

  25. Lzuqay说道:

    writing paper online write a literature review brand cefixime 200mg

  26. Oseahz说道:

    order aspirin online cheap online casinos paying real money gambling meaning

  27. Ubdncm说道:

    buy lamisil generic terbinafine canada online card games

  28. Ytusvu说道:

    how to buy trazodone order suhagra 100mg buy clindamycin for sale

  29. Yyhyik说道:

    cefuroxime 500mg cost buy ceftin 250mg methocarbamol ca

  30. Dyqced说道:

    order tadalafil 10mg for sale purchase tadacip for sale indocin 75mg over the counter

  31. Ahuvnx说道:

    order tamoxifen 20mg for sale order tamoxifen 10mg online cheap rhinocort order

  32. Nzmmoj说道:

    order tretinoin cream generic avanafil 200mg cheap stendra pill

  33. Mlxdcw说道:

    cheap clindamycin buy ed meds sildenafil 100mg cheap

  34. Cbjzqc说道:

    lamotrigine 200mg canada lamotrigine 200mg drug buy nemazole tablets

  35. Sbweff说道:

    order flagyl 400mg pills buy keflex 250mg without prescription order cephalexin 125mg sale

  36. Etpswu说道:

    buy aurogra online order estradiol 2mg pill estrace 1mg generic

  37. Nmjasi说道:

    buy generic fluconazole for sale forcan without prescription order cipro 1000mg pills

  38. Iqpksf说道:

    buy aldactone 25mg pill buy finasteride 1mg proscar online buy

  39. Kxoxqd说道:

    essays online to buy buy my essay buy dissertation online

  40. Mbnhju说道:

    order domperidone 10mg for sale sumycin 500mg without prescription sumycin 250mg canada

  41. Ntsnlj说道:

    cheap ranitidine 150mg zantac cost buy celebrex tablets

  42. Zxhceh说道:

    buspar order online buy cheap cordarone buy cordarone

  43. Wbfqop说道:

    sumatriptan 25mg cheap order levaquin pills order avodart 0.5mg pill

  44. Lgfadi说道:

    order allopurinol 100mg without prescription order crestor 10mg generic rosuvastatin 20mg us

  45. Dtuewm说道:

    buy esomeprazole sale nexium 40mg drug buy generic topamax

  46. Ykevvl说道:

    azelastine 10 ml tablet order acyclovir 400mg online cheap order avalide generic

  47. Muddxu说道:

    brand pepcid buy generic pepcid online buy tacrolimus 5mg sale

  48. Fzwvav说道:

    oral warfarin 5mg buy reglan 10mg without prescription buy maxolon paypal

  49. Uymgoj说道:

    how to buy xenical order diltiazem sale buy diltiazem 180mg pills

  50. Egtbig说道:

    order nortriptyline 25 mg order paracetamol 500 mg online cheap anacin 500 mg cost

  51. Fboaiw说道:

    purchase glimepiride generic buy arcoxia 60mg online buy arcoxia cheap

  52. Ktlykn说道:

    order inderal generic buy inderal order clopidogrel 150mg sale

  53. Wgljqg说道:

    alendronate 70mg canada order macrodantin 100 mg pills buy nitrofurantoin tablets

  54. Pvwacs说道:

    order ozobax generic amitriptyline 10mg price ketorolac price

  55. Twatma说道:

    loratadine pills buy loratadine medication dapoxetine 90mg brand

  56. Smajcq说道:

    baclofen 25mg usa buy amitriptyline 10mg online cheap buy ketorolac generic

  57. Wejfas说道:

    phenytoin 100 mg over the counter flexeril online buy generic oxybutynin

  58. Qkyuwe说道:

    buy coversyl generic desloratadine brand purchase fexofenadine

  59. Gsrbxe说道:

    buy levitra 20mg sale cost zanaflex tizanidine ca

  60. Rmslan说道:

    methylprednisolone 8mg over counter methylprednisolone 8 mg online buy aristocort without prescription

  61. Zgqltd说道:

    clomiphene 50mg price imuran over the counter imuran 25mg drug

  62. Qxclrx说道:

    online casino real money no deposit amoxiclav oral synthroid 150mcg us

  63. Azncmz说道:

    order symmetrel for sale symmetrel 100mg uk aczone 100mg cost

  64. Qudbjg说道:

    real money slots free spins no deposit stromectol 12mg without prescription stromectol coupon

  65. Tgotui说道:

    parx casino online play slots ventolin online buy

  66. Skijhf说道:

    buy protonix 40mg online cheap order protonix 20mg for sale phenazopyridine pills

  67. Pztbow说道:

    play online blackjack real money casino real money buy generic furosemide 40mg

  68. Bxertm说道:

    order azipro cost prednisolone 20mg buy neurontin 800mg

  69. Bqmxdz说道:

    oral atorvastatin 10mg order generic albuterol amlodipine 5mg pills

  70. Mpnbrf说道:

    buy isotretinoin 20mg without prescription accutane usa order generic azithromycin 500mg

  71. Vsjbyw说道:

    buy generic cefdinir over the counter glycomet 500mg us prevacid online order

  72. Fzhumu说道:

    order modafinil 200mg online buy modafinil online cheap buy prednisone pills for sale

  73. Tjiukl说道:

    order tadalafil 10mg order sildenafil 50mg sildenafil mail order

  74. Ammzlw说道:

    order micardis 80mg online cheap cost plaquenil molnupiravir canada

  75. Nqsntt说道:

    buy omeprazole for sale order omeprazole generic buy generic metoprolol

  76. Aowcmq说道:

    purchase premarin without prescription buy premarin 0.625mg sildenafil buy online

  77. Pammfd说道:

    buy generic latanoprost purchase zovirax without prescription buy rivastigmine 6mg generic

  78. Wghkio说道:

    buy vasotec 10mg sale bicalutamide where to buy duphalac uk

  79. Deezbh说道:

    buy pyridostigmine generic maxalt 5mg price buy maxalt online

  80. Vqutpk说道:

    ferrous 100 mg without prescription sotalol 40mg brand buy betapace pills

  81. Swwobn说道:

    buy etodolac 600 mg without prescription cilostazol 100mg generic cilostazol price

  82. Qibytm说道:

    buy prasugrel medication dimenhydrinate over the counter buy tolterodine 1mg online cheap

  83. Txxxif说道:

    fludrocortisone 100 mcg for sale imodium over the counter imodium 2 mg for sale

  84. Ckvbvk说道:

    buy duphaston for sale purchase dydrogesterone online cheap order jardiance 10mg online cheap

  85. Sqpytj说道:

    meloset pill aygestin 5mg brand danocrine generic

  86. Lewkpx说道:

    dipyridamole us order felodipine generic pravastatin pills

  87. Zkvjdr说道:

    aspirin medication buy zovirax paypal zovirax cheap

  88. Pdvbtz说道:

    precose 50mg usa fulvicin price order fulvicin 250mg without prescription

  89. Wpdnkc说道:

    purchase minoxytop online buy flomax medication the blue pill ed

  90. Meghna说道:

    where to buy ketotifen without a prescription how to buy zaditor tofranil online buy

  91. Imrpvr说道:

    cheap cialis pill cialis india order viagra pill

  92. Agnpgp说道:

    tricor pills buy fenofibrate 160mg pills order fenofibrate 200mg generic

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

CAPTCHAis initialing...