實訓案例名稱：數(shù)據(jù)庫用戶管理一、任務介紹通過連接MySQL、新增用戶、修改用戶密碼等來實現(xiàn)數(shù)據(jù)庫用戶管理。二、實現(xiàn)步驟1.連接MySQL（1）數(shù)據(jù)庫本地連接#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.mysql>（2）數(shù)據(jù)庫遠程連接#使用用戶名root和相應密碼，連接遠程MySQLC:\Users\Administrator>mysql-h9-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.mysql>2.新增用戶#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#創(chuàng)建用戶并指定密碼mysql>CREATEUSERshopdb@'%'IDENTIFIEDBY'shopdb_123456';QueryOK,0rowsaffected(0.07sec)#授予對所有數(shù)據(jù)庫的所有權限mysql>GRANTALLPRIVILEGESON*.*TOshopdb@'%'WITHGRANTOPTION;QueryOK,0rowsaffected(0.01sec)#授予指定權限，把增、刪、改、查操作權限授予用戶shopdbmysql>GRANTSELECT,INSERT,UPDATE,DELETEon*.*toshopdb@"%"WITHGRANTOPTION;QueryOK,0rowsaffected,1warning(0.71sec)#授予對指定數(shù)據(jù)庫（test數(shù)據(jù)庫）的權限mysql>GRANTSELECT,INSERT,UPDATE,DELETEontest.*toshopdb@"%"WITHGRANTOPTION;QueryOK,0rowsaffected,1warning(0.71sec)#撤銷對指定數(shù)據(jù)庫（test數(shù)據(jù)庫）的SELECT權限mysql>REVOKESELECTONtest.*FROM'shopdb'@'%';QueryOK,0rowsaffected(0.00sec)#退出mysql>exit;Bye#使用用戶名shopdb和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-ushopdb-pshopdb_1234563.修改用戶密碼#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#授予reload權限mysql>GRANTreloadON*.*to'shopdb'@'%';QueryOK,0rowsaffected(0.08sec)#修改密碼mysql>ALTERUSER'shopdb'@'%'IDENTIFIEDBY'123456';QueryOK,0rowsaffected(0.02sec)#刷新權限mysql>flushprivileges;QueryOK,0rowsaffected(0.43sec)mysql>實訓案例名稱：數(shù)據(jù)庫操作一、任務介紹MySQL允許創(chuàng)建多個數(shù)據(jù)庫，每個數(shù)據(jù)庫承載不同的內(nèi)容，使用命令操作可以查看數(shù)據(jù)庫、創(chuàng)建數(shù)據(jù)庫、使用數(shù)據(jù)庫以及刪除數(shù)據(jù)庫。二、實現(xiàn)步驟1.查看數(shù)據(jù)庫#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#查看數(shù)據(jù)庫mysql>SHOWDATABASES;+------------------------------+|Database

|+------------------------------+|information_schema

||mysql

||performance_schema

||sys

||test

|+------------------------------+5rowsinset(0.00sec)2.創(chuàng)建數(shù)據(jù)庫#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#查看數(shù)據(jù)庫mysql>SHOWDATABASES;+------------------------------+|Database

|+------------------------------+|information_schema

||mysql

||performance_schema

||sys

||test

|+------------------------------+5rowsinset(0.00sec)3.使用數(shù)據(jù)庫#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechangedmysql>4.刪除數(shù)據(jù)庫#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#查看數(shù)據(jù)庫mysql>SHOWDATABASES;+-----------------------------+|Database

|+-----------------------------+|information_schema

||mysql

||performance_schema

||shop

||sys

||test

|+-----------------------------+6rowsinset(0.00sec)#刪除shop數(shù)據(jù)庫mysql>DROPDATABASEshop;QueryOK,0rowsaffected(0.46sec)#查看數(shù)據(jù)庫mysql>SHOWDATABASES;+-----------------------------+|Database

|+-----------------------------+|information_schema

||mysql

||performance_schema

||sys

||test

|+-----------------------------+5rowsinset(0.06sec)實訓案例名稱：表操作一、任務介紹MySQL表操作是使用頻率最高的操作。用戶可以創(chuàng)建數(shù)據(jù)庫表，查看數(shù)據(jù)庫表，向數(shù)據(jù)庫表里插入數(shù)據(jù)、更新數(shù)據(jù)、刪除數(shù)據(jù)等，同時可以修改表結構、復制表、使用臨時表。二、實現(xiàn)步驟1.創(chuàng)建表#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#創(chuàng)建shop數(shù)據(jù)庫mysql>CREATEDATABASEshop;QueryOK,1rowaffected(0.00sec)#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#創(chuàng)建用戶表usermysql>CREATETABLEuser(idint(10)unsignednotnullauto_increment,namevarchar(25),sexvarchar(5),ageint(10),passwordvarchar(25),primarykey(id))engine=InnoDB;QueryOK,0rowsaffected(0.90sec)#查看表mysql>SHOWtables;+-----------------------+|Tables_in_shop

|+-----------------------+|user

|+-----------------------+1rowinset(0.04sec)mysql>2.查看表結構#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查看用戶表user的表結構mysql>DESCuser;+----------+--------------+------+-----+---------+----------------+|Field |Type

|Null

|Key

|Default

|Extra |+----------+--------------+------+-----+---------+----------------+|id |intunsigned

|NO

|PRI

|NULL

|auto_increment ||name |varchar(25)

|YES

|

|NULL

| ||sex |varchar(5)

|YES

|

|NULL

| ||age |int

|YES

|

|NULL

| ||password|varchar(25)

|YES

|

|NULL

| |+----------+--------------+------+-----+---------+----------------+5rowsinset(0.02sec)mysql>3.修改表結構#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#為用戶表user增加一列phonemysql>ALTERTABLEuserADDphonechar(11);QueryOK,0rowsaffected(0.03sec)Records:0Duplicates:0Warnings:0#查看用戶表user的表結構，其中多出來一列phonemysql>DESCuser;+----------+--------------+------+-----+---------+----------------+|Field

|Type

|Null

|Key

|Default

|Extra |+----------+--------------+------+-----+---------+----------------+|id

|intunsigned

|NO

|PRI

|NULL

|auto_increment ||name

|varchar(25)

|YES

|

|NULL

| ||sex

|varchar(5)

|YES

|

|NULL

| ||age

|int

|YES

|

|NULL

| ||password

|varchar(25)

|YES

|

|NULL

| ||phone

|char(11)

|YES

|

|NULL

| |+----------+--------------+------+-----+---------+----------------+6rowsinset(0.01sec)#為用戶表user刪除一列phonemysql>ALTERTABLEuserDROPphone;QueryOK,0rowsaffected(0.06sec)Records:0Duplicates:0Warnings:0mysql>DESCuser;+----------+--------------+------+-----+---------+----------------+|Field

|Type

|Null

|Key|Default

|Extra |+----------+--------------+------+-----+---------+----------------+|id

|intunsigned

|NO

|PRI|NULL

|auto_increment||name

|varchar(25)

|YES

||NULL

| ||sex

|varchar(5)

|YES

||NULL

| ||age

|int

|YES

||NULL

| ||password

|varchar(25)

|YES

||NULL

| |+----------+--------------+------+-----+---------+----------------+5rowsinset(0.00sec)4.復制表（1）復制表結構、數(shù)據(jù)、主鍵、索引①基于user表的結構和數(shù)據(jù)進行復制，user表有主鍵和索引，如圖3.1和圖3.2所示。圖3.1user表主鍵圖3.2user表索引②在shop數(shù)據(jù)庫的user表里添加一條記錄，命令操作如下所示。#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#向用戶表user中插入數(shù)據(jù)mysql>INSERTINTOuserVALUES(1,'kevin','男',20,'123456'),(2,'tom','男','30','123456');QueryOK,1rowaffected(0.07sec)#查詢用戶表user中的數(shù)據(jù)mysql>SELECT*FROMuser;+----+-------+------+------+----------+|id

|name

|sex

|age

|password

|+----+-------+------+------+----------+|1

|kevin

|男

|20

|123456

||2

|tom

|男

|30

|123456

|+----+-------+------+------+----------+2rowsinset(0.00sec)mysql>③將user表的表結構、索引、主鍵復制到新的user_new表里，命令操作如下所示。#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#使用like將用戶表user的表結構、索引、主鍵復制到user_new表中mysql>CREATETABLEuser_newlikeuser;QueryOK,0rowsaffected(0.48sec)#查詢user_new表數(shù)據(jù)，mysql>SELECT*FROMuser_new;Emptyset(0.12sec)mysql>=4\*GB3④創(chuàng)建新的user_new表，只復制了表結構、主鍵和索引，并沒有數(shù)據(jù)，把舊的表user的數(shù)據(jù)復制到新的表user_new，命令操作如下所示。#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#把用戶表user的數(shù)據(jù)復制到user_new表中mysql>INSERTuser_newSELECT*FROMuser;QueryOK,2rowsaffected(0.13sec)#查詢user_new表中的數(shù)據(jù)mysql>SELECT*FROMuser_new;+----+-------+------+------+----------+|id

|name

|sex

|age

|password

|+----+-------+------+------+----------+|1

|kevin

|男

|20

|123456

||2

|tom

|男

|30

|123456

|+----+-------+------+------+----------+2rowsinset(0.00sec)mysql>（2）復制表結構、數(shù)據(jù)，不能復制主鍵、索引①將user表的表結構、索引、主鍵復制到新的user_new2表里，可以查看到user_new2表里已經(jīng)復制進來數(shù)據(jù)了，命令操作如下所示。#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#將用戶表user的表結構、數(shù)據(jù)復制到user_new2表中mysql>CREATETABLEuser_new2SELECT*FROMuser;QueryOK,2rowsaffected(0.68sec)Records:2Duplicates:0Warnings:0#查詢user_new2表mysql>SELECT*FROMuser_new2;+----+-------+------+------+----------+|id

|name

|sex

|age

|password

|+----+-------+------+------+----------+|1|kevin

|男

|20

|123456

||2|tom

|男

|30

|123456

|+----+-------+------+------+----------+2rowsinset(0.00sec)mysql>②將user表的表結構、索引、主鍵復制到新的user_new3表里，可以查看到user_new3表里沒有數(shù)據(jù)，命令操作如下所示。#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#復制表結構，不復制數(shù)據(jù)mysql>CREATETABLEuser_new3SELECT*FROMuserWHERE0;QueryOK,0rowsaffected(0.67sec)Records:0Duplicates:0Warnings:0#查詢user_new3表中的數(shù)據(jù)，數(shù)據(jù)為空mysql>SELECT*FROMuser_new3;Emptyset(0.00sec)mysql>4.臨時表和內(nèi)存表#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#創(chuàng)建臨時表mysql>CREATEtemporaryTABLEtmp1(idintnotnull);QueryOK,0rowsaffected(0.26sec)#查看創(chuàng)建的表mysql>SHOWCREATETABLEtmp1;+-------+------------------------------------------------------------------+|+-------+------------------------------------------------------------------+|tmp1

|CREATETEMPORARYTABLE`tmp1`(`id`intNOTNULL)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+-------+------------------------------------------------------------------+1rowinset(0.00sec)#創(chuàng)建內(nèi)存表mysql>CREATETABLEtmp2(idintnotnull)ENGINE=MEMORY;QueryOK,0rowsaffected(0.19sec)#查看創(chuàng)建的表mysql>SHOWCREATETABLEtmp2+-------+------------------------------------------------------------------+|Table

|CreateTable|+-------+------------------------------------------------------------------+|tmp2

|CREATETABLE`tmp2`(`id`intNOTNULL)ENGINE=MEMORYDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci|+-------+------------------------------------------------------------------+1rowinset(0.00sec)mysql>實訓案例名稱：數(shù)據(jù)操作一、任務介紹插入、查詢、修改和刪除是MySQL數(shù)據(jù)庫的4種最基本的操作，在項目開發(fā)的過程中也使用得最頻繁。在MySQL里，插入使用INSERT關鍵字、查詢使用SELECT關鍵字、修改使用UPDATE關鍵字、刪除使用DELETE關鍵字。二、實現(xiàn)步驟1.數(shù)據(jù)插入和查詢#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表user的結構mysql>DESCuser;+----------+------------------+-----+-----+--------+----------------+|Field

|Type

|Null|Key|Default|Extra

|+----------+------------------+-----+-----+--------+----------------+|id

|int(10)unsigned

|NO

|PRI|NULL

|auto_increment

||name

|varchar(25)

|YES

|

|NULL

|

||sex

|varchar(5)

|YES

|

|NULL

|

||age

|int(10)

|YES

|

|NULL

|

||password|varchar(25)

|YES

|

|NULL

|

|+----------+------------------+-----+-----+--------+----------------+5rowsinset(0.03sec)#插入數(shù)據(jù)，指定列名mysql>INSERTINTOuser(id,name,sex,age,password)VALUES(3,'david','男',

28,'111111');QueryOK,1rowaffected(1.59sec)#插入數(shù)據(jù)，不指定列名mysql>INSERTINTOuserVALUES(4,'lili','女',25,'222222');QueryOK,1rowaffected(0.00sec)#查詢所有字段mysql>SELECT*FROMuser;+----+-------+------+------+----------+|id

|name

|sex

|age

|password|+----+-------+------+------+----------+|1

|kevin

|男

|20

|123456

||2

|tom

|男

|30

|123456

||3

|david

|男

|28

|111111

||4

|lili

|女

|25

|222222

|+----+-------+------+------+----------+4rowsinset(0.00sec)#查詢指定字段mysql>SELECTnameFROMuser;+-------+|name

|+-------+|kevin

||tom

||david

||lili

|+-------+4rowsinset(0.00sec)#查詢性別為男的數(shù)據(jù)mysql>SELECT*FROMuserWHEREsex='男';+----+-------+------+------+----------+|id|name

|sex

|age

|password

|+----+-------+------+------+----------+|1|kevin

|男

|20

|123456

||2|tom

|男

|30

|123456

||3|david

|男

|28

|111111

|+----+-------+------+------+----------+3rowsinset(0.00sec)#查詢性別為男且名字是david的數(shù)據(jù)mysql>SELECT*FROMuserWHEREsex='男'andname='david';+----+-------+------+------+----------+|id|name

|sex

|age

|password

|+----+-------+------+------+----------+|3|david

|男

|28

|111111

|+----+-------+------+------+----------+1rowinset(0.00sec)#批量插入多行數(shù)據(jù)mysql>INSERTINTOuser(id,name,sex,age,password)VALUES(5,'zhansan','男',29,'111111'),(6,'lisi','男',30,'111111'),(7,'wangwu','男',31,'111111');QueryOK,3rowsaffected(0.01sec)Records:3Duplicates:0Warnings:0#查詢用戶表usermysql>select*fromuser;+----+---------+------+------+----------+|id|name

|sex

|age

|password

|+----+---------+------+------+----------+|1|kevin

|男

|20

|123456

||2|tom

|男

|30

|123456

||3|david

|男

|28

|111111

||4|lili

|女

|25

|222222

||5|zhansan

|男

|29

|111111

||6|lisi

|男

|30

|111111

||7|wangwu

|男

|31

|111111

|+----+---------+------+------+----------+7rowsinset(0.00sec)mysql>2.修改記錄#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表user的數(shù)據(jù)mysql>SELECT*FROMuser;+----+---------+------+------+----------+|id|name

|sex|age

|password

|+----+---------+------+------+----------+|1|kevin

|男

|20

|123456

||2|tom

|男

|30

|123456

||3|david

|男

|28

|111111

||4|lili

|女

|25

|222222

||5|zhansan

|男

|29

|111111

||6|lisi

|男

|30

|111111

||7|wangwu

|男

|31

|111111

|+----+---------+------+------+----------+7rowsinset(0.00sec)#修改一個字段的值，將david的性別設置為女mysql>UPDATEuserSETsex='女'WHEREname='david';QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0#查詢用戶表user中的數(shù)據(jù)mysql>SELECT*FROMuser;+----+---------+------+------+----------+|id|name

|sex

|age

|password|+----+---------+------+------+----------+|1|kevin

|男

|20

|123456

||2|tom

|男

|30

|123456

||3|david

|女

|28

|111111

||4|lili

|女

|25

|222222

||5|zhansan

|男

|29

|111111

||6|lisi

|男

|30

|111111

||7|wangwu

|男

|31

|111111

|+----+---------+------+------+----------+7rowsinset(0.02sec)3.刪除記錄#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表user中的數(shù)據(jù)mysql>SELECT*FROMuser;+----+---------+------+------+----------+|id|name

|sex

|age

|password

|+----+---------+------+------+----------+|1|kevin

|男

|20

|123456

||2|tom

|男

|30

|123456

||3|david

|女

|28

|111111

||4|lili

|女

|25

|222222

||5|zhansan

|男

|29

|111111

||6|lisi

|男

|30

|111111

||7|wangwu

|男

|31

|111111

|+----+---------+------+------+----------+7rowsinset(0.00sec)#刪除id為4的數(shù)據(jù)mysql>DELETEFROMuserWHEREid=4;QueryOK,1rowaffected(0.00sec)#查詢用戶表user中的數(shù)據(jù)mysql>SELECT*FROMuser;+----+---------+------+------+----------+|id|name

|sex

|age

|password

|+----+---------+------+------+----------+|1|kevin

|男

|20

|123456

||2|tom

|男

|30

|123456

||3|david

|女

|28

|111111

||5|zhansan

|男

|29

|111111

||6|lisi

|男

|30

|111111

||7|wangwu

|男

|31

|111111

|+----+---------+------+------+----------+6rowsinset(0.00sec)#刪除性別為男的數(shù)據(jù)mysql>DELETEFROMuserWHEREsex='男';QueryOK,5rowsaffected(0.01sec)#查詢用戶表user中的數(shù)據(jù)mysql>SELECT*FROMuser;+----+-------+------+------+----------+|id|name

|sex

|age

|password

|+----+-------+------+------+----------+|3|david

|女

|28

|111111

|+----+-------+------+------+----------+1rowinset(0.00sec)mysql>4.對查詢結果排序#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#向用戶表user中批量插入數(shù)據(jù)mysql>INSERTINTOuser(id,name,sex,age,password)VALUES(4,'小紅','女',27,'123456'),(5,'小明','男',10,'123456'),(6,'小剛','男',12,'123456'),(7,'小王','男',14,'111111'),(8,'小綠','女',34,'222222'),(9,'曉峰','男',15,'333333'),(10,'小影','女',26,'444444'),(11,'大梅','女',27,'555555');QueryOK,8rowsaffected(0.01sec)Records:8Duplicates:0Warnings:0#查詢用戶表user中的所有數(shù)據(jù)mysql>SELECT*FROMuser;+----+-------+------+------+----------+|id|name

|sex

|age

|password

|+----+-------+------+------+----------+|3|david

|女

|28

|111111

||4|小紅

|女

|27

|123456

||5|小明

|男

|10

|123456

||6|小剛

|男

|12

|123456

||7|小王

|男

|14

|111111

||8|小綠

|女

|34

|222222

||9|曉峰

|男

|15

|333333

||10|小影

|女

|26

|444444

||11|大梅

|女

|27

|555555

|+----+-------+------+------+----------+9rowsinset(0.00sec)#按id升序查詢mysql>SELECT*FROMuserORDERBYidASC;+----+-------+------+------+----------+|id|name

|sex|age|password

|+----+-------+------+------+----------+|3|david

|女|28|111111

||4|小紅

|女|27|123456

||5|小明

|男|10|123456

||6|小剛

|男|12|123456

||7|小王

|男|14|111111

||8|小綠

|女|34|222222

||9|曉峰

|男|15|333333

||10|小影

|女|26|444444

||11|大梅

|女|27|555555

|+----+-------+------+------+----------+9rowsinset(0.00sec)#按id降序查詢mysql>SELECT*FROMuserORDERBYidDESC;+----+-------+------+------+----------+|id|name|sex|age|password|+----+-------+------+------+----------+|11|大梅|女|27|555555||10|小影|女|26|444444||9|曉峰|男|15|333333||8|小綠|女|34|222222||7|小王|男|14|111111||6|小剛|男|12|123456||5|小明|男|10|123456||4|小紅|女|27|123456||3|david|女|28|111111|+----+-------+------+------+----------+9rowsinset(0.00sec)#先按性別降序查詢，再按年齡升序查詢mysql>SELECT*FROMuserORDERBYsexDESC,ageASC;+----+-------+------+------+----------+|id|name

|sex|age

|password

|+----+-------+------+------+----------+|5|小明|男|10|123456

||6|小剛|男|12|123456

||7|小王|男|14|111111

||9|曉峰|男|15|333333

||10|小影|女|26|444444

||4|小紅|女|27|123456

||11|大梅|女|27|555555

||3|david|女|28|111111

||8|小綠|女|34|222222

|+----+-------+------+------+----------+9rowsinset(0.01sec)5.對查詢結果分組#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id|name

|sex|age

|password

|+----+-------+------+------+----------+|3|david|女|28|111111

||4|小紅|女|27|123456

||5|小明|男|10|123456

||6|小剛|男|12|123456

||7|小王|男|14|111111

||8|小綠|女|34|222222

||9|曉峰|男|15|333333

||10|小影|女|26|444444

||11|大梅|女|27|555555

|+----+-------+------+------+----------+9rowsinset(0.00sec)#根據(jù)性別進行分組，sex字段的全部值只有兩個('男'和'女')，所以分為兩組mysql>SELECTsex,count(*)FROMuserGROUPBYsex;+------+----------+|sex

|count(*)

|+------+----------+|女

|5

||男

|4

|+------+----------+2rowsinset(0.00sec)#根據(jù)性別和id進行分組，因為id字段的值是沒有重復的，所以按性別把女和男分開顯示mysql>SELECT*FROMuserGROUPBYsex,id;+----+-------+------+------+----------+|id|name

|sex

|age

|password

|+----+-------+------+------+----------+|3|david

|女

|28

|111111

||4|小紅

|女

|27

|123456

||5|小明

|男

|10

|123456

||6|小剛

|男

|12

|123456

||7|小王

|男

|14

|111111

||8|小綠

|女

|34

|222222

||9|曉峰

|男

|15

|333333

||10|小影

|女

|26

|444444

||11|大梅

|女

|27

|555555

|+----+-------+------+------+----------+9rowsinset(0.00sec)#按性別分組，輸出用戶的姓名mysql>SELECTsex,group_concat(name)FROMuserGROUPBYsex;+------+---------------------------+|sex

|group_concat(name)

|+------+---------------------------+|女

|david,小紅,小綠,小影,大梅

||男

|小明,小剛,小王,曉峰

|+------+---------------------------+2rowsinset(0.00sec)#按性別分組，輸出用戶的idmysql>SELECTsex,group_concat(id)FROMuserGROUPBYsex;+------+------------------+|sex

|group_concat(id)

|+------+------------------+|女

|3,4,8,10,11

||男

|5,6,7,9

|+------+------------------+2rowsinset(0.00sec)#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id|name

|sex

|age

|password

|+----+-------+------+------+----------+|3

|david

|女

|28

|111111||4

|小紅

|女|27

|123456||5

|小明

|男|10

|123456||6

|小剛

|男|12

|123456||7

|小王

|男|14

|111111||8

|小綠

|女|34

|222222||9

|曉峰

|男|15

|333333||10

|小影

|女|26

|444444||11

|大梅

|女|27

|555555|+----+-------+------+------+----------+9rowsinset(0.00sec)#對age列進行分組，MySQL8.0之前版本分組后age列是有序的mysql>SELECTcount(*),ageFROMuserGROUPBYage;+----------+------+|count(*)|age

|+----------+------+|1|10||1|12||1|14||1|15||1|26||2|27||1|28||1|34|+----------+------+8rowsinset(0.00sec)#對age列進行分組，MySQL8.0分組后age列是無序的mysql>SELECTcount(*),ageFROMuserGROUPBYage;+----------+------+|count(*)|age

|+----------+------+|1|28||2|27||1|10||1|12||1|14||1|34||1|15||1|26|+----------+------+8rowsinset(0.00sec)#對age列進行分組，MySQL8.0分組后age列是無序的，需要自己加ORDERBY子句mysql>SELECTcount(*),ageFROMuserGROUPBYageORDERBYageDESC;+----------+------+|count(*)|age

|+----------+------+|1|34||1|28||2|27||1|26||1|15||1|14||1|12||1|10|+----------+------+8rowsinset(0.00sec)6.設置分組條件#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id|name

|sex

|age

|password

|+----+-------+------+------+----------+|3 |david

|女|28

|111111

||4 |小紅

|女|27

|123456

||5 |小明

|男|10

|123456

||6 |小剛

|男|12

|123456

||7 |小王

|男|14

|111111

||8 |小綠

|女|34

|222222

||9 |曉峰

|男|15

|333333

||10 |小影

|女|26

|444444

||11 |大梅

|女|27

|555555

|+----+-------+------+------+----------+9rowsinset(0.00sec)#按性別分組，且查詢在分組后記錄數(shù)大于2的性別，輸出性別及數(shù)量mysql>SELECTsex,count(sex)FROMuserGROUPBYsexHAVINGcount(sex)>2;+------+------------+|sex

|count(sex)

|+------+------------+|女|5

||男|4

|+------+------------+2rowsinset(0.00sec)#按性別分組，且查詢在分組后記錄數(shù)大于4的性別，輸出性別及數(shù)量mysql>SELECTsex,count(sex)FROMuserGROUPBYsexHAVINGcount(sex)>4;+------+------------+|sex

|count(sex)

|+------+------------+|女|5|+------+------------+1rowinset(0.00sec)7.限制查詢數(shù)量#使用用戶名root和相應密碼，連接本地MySQLC:\Users\Administrator>mysql-uroot-p123456mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.#使用shop數(shù)據(jù)庫mysql>USEshop;Databasechanged#查詢用戶表usermysql>SELECT*FROMuser;+----+-------+------+------+----------+|id|name|sex|age|password|+----+-------+------+------+----------+|3 |david |女|28|111111||4 |小紅 |女|27|123456||5 |小明 |男|10|123456||6 |小剛 |男|12|123456||7 |小王 |男|14|111111||8 |小綠 |女|34|222222||9 |曉峰 |男|15|333333||10 |小影 |女|26|444444||11 |大梅 |女|27|555555|+----+-