本文通过实战演练的方式介绍了MySQL的基础命令，包括DDL、DML、DQL等，通过具体实例加深了对MySQL基本语法的理解。
话说：
各位读者朋友，上一篇我们温故了HTML基础标签，这一篇也轻松一下，温习一下MySQL的基础命令。
现在是21:17分，我们通过cmd命令方式，通过具体例子，尽可能全的来温习基本命令,也许你觉得太基础，但是基本功永远是基本功；就如同“野蛮”的体魄永远是奋斗的前提。
难度：★☆☆☆☆
练习工具：cmd窗口+Notepad++
用时：1H左右
目录
一、效果图
二、DDL
三、DML
四、DQL
五、总结
六、下期预告
如果有一天，你对基本语句陌生了，打开它，从头到尾练习几遍，对基础知识就会有一个系统而快速的回顾，这是主要目的，不怕遗忘，怕的是遗忘后不作为。
MySQL数据库安装再次就省略了，网上资料很多。
一、效果图
二、DDL
顾名思义：DDL-Data Define Language 数据库定义语言。主要是用来“诞生”数据库和数据表的语句。定义好这样一个“容器”，为后期往里面存放数据做准备。就类似建一座房子，挖好一个地窖类似。语言主要有：create drop alter 等。
三、DML
顾名思义，DML-Data Maniufaction Language 数据库操作语言。操作，就是针对“房间”或者“地窖”这样的容器而言，给里面存“粮食”（数据），基本就是增删改。insert update delete 等。
四、DQL
顾名思义，DQL-Data Query Language 数据库查询语言，也就是select 。是日常运用最多的语句。这里面涉及的就是查询的一些关键字 select distinct from where group by having order by limit desc asc like 等等，单表查询、多表查询。多表查询是重点；怎么查，如何快速高效的查，方法很灵活。
练习过程如下：
#DDL
#1.登录
mysql -uroot -p119913;
#2.关于数据库的基本操作
show databases;
#创建数据库
create database db_xiaomei;
show databases;
#查看哪个数据库在使用
select database();
#如果结果为NULL,表明没有选中数据库。要找一本书，首先要进入阅读区-数据库，然后在找对应的书架-表
#使用数据库db_xiaomei
user db_xiaomei; #这里不用use database db_xiaomei;因为use只是针对数据库而言，加上就报错 成功提示 Database changed
#删除数据库
drop database db_xiaomei;#drop 不仅适用数据库，也适用表，所以要用database 和table区别开来。
#好，把以上命令重复一遍;数据库一般不修改，与其修改，还不如删除掉方便。
#3.关于数据表的基本操作
#查看表
show tables; #Empty
#创建一张表
create table t_01(
id int auto_increment primary key comment "人物Id",
name varchar(10) not null comment "人物姓名",
age int unsigned comment "年龄",
talentPoem int comment "诗词天赋值"
)engine=MyIsam charset=gbk comment="红楼梦人物简表";
#显示创建表的语句及详细信息
show create table t_01;
#以上故意修改了表的引擎，默认engine=InnoDB charset = utf8 注意字段注释与表注释的区别
#查看表结构
desc t_01;
#为t_01增加一个属性 loveScore varchar(10) not null;
alter table t_01 add loveScore varchar(10) not null ;
#修改t_01的loveScore字段varchar(10) 为varchar(8)，并增加注释
alter table t_01 modify loveScore varchar(8) not null comment "喜爱程度";
#修改t_01的loveScore字段为loveLevel
alter table t_01 change loveScore loveLevel varchar(11) not null comment "喜好水平";
#删除loveLevel字段
alter table t_01 drop loveLevel;
#修改表名
alter table t_01 rename t_001; #继续改回来
#好，到此为止，DDL语句告一段落。
#4.关于数据表内容的CUD操作
#为表t_01插入一条数据
insert into t_01 values(1,"宝玉哥哥",18,90);
#查看一下数据
select * from t_01;
#指定字段插入多条数据
insert into t_01 (name,age,talentPoem) values
("黛玉妹纸",17,98),
("宝钗妹妹",17,96),
("湘云小兄弟",18,90),
("香菱小丫头",16,65),
("探春",15,88),
("妙玉",16,93);
#修改妙玉的年龄为15岁。
update t_01 set age=15 where name="妙玉";
#再次新增一条数据
insert into t_01 (name,age,talentPoem) values ("凤辣子",33,78);
#删除Id为8的数据
delete from t_01 where id = 8;
#删除整张表
delete from t_01;#这里不忍心删除
#到此为止，关于DML的基本语句就练习到这里，框架部分的CURD操作也无非就是这些语句的灵活运用；其他读者请自行拓展奥~
#5.重点练习DQL语句也就是select
#通过一组题目来温习这些语句吧
#准备工作创建一张表t_hero;
create table t_hero (
id int auto_increment comment "英雄Id",
name varchar(10) not null comment "英雄姓名",
attack int comment "武力值",
country varchar(10) comment "国家",
primary key(id)
)comment="三国英雄表";
#为t_hero插入数据
insert into t_hero (id,name,attack,country) values
(101,"刘备",78,"蜀"),
(102,"曹操",83,"魏"),
(103,"孙权",89,"吴"),
(104,"关羽",96,"蜀"),
(105,"张飞",98,"蜀"),
(106,"吕布",99,NULL),
(107,"诸葛亮",70,"蜀"),
(108,"周瑜",90,"吴" ),
(109,"陆逊",89,"吴"),
(110,"司马懿",67,"魏"),
(111,"马超",90,NULL);
#开始练习喽;为了锻炼对大写的敏感度，全部大写
#1）查询所有武将的姓名及武力，并按武力降序排列。
SELECT NAME,ATTACK FROM T_HERO ORDER BY ATTACK DESC;
#2）查询魏国和蜀国所有的武将，并显示武将姓名和国家。
SELECT NAME,COUNTRY FROM T_HERO WHERE COUNTRY IN ("魏","蜀");
#3）查询武力值在 80~90 之间的所有武将的编号、姓名及武力。
SELECT ID,NAME,ATTACK FROM T_HERO WHERE ATTACK BETWEEN 80 AND 90;
#4)查询所有武将武力的最大、最小、平均值。
SELECT MAX(ATTACK) 武力最大,MIN(ATTACK) 武力最小,AVG(ATTACK) 武力平均 FROM T_HERO;
#5)查询所有姓张的武将编号及名称。
SELECT ID,NAME FROM T_HERO WHERE NAME LIKE "张%";
#6)查询各国家武将的数量，显示国家名和武将数量。
SELECT COUNTRY,COUNT(ID) 武将数量 FROM T_HERO GROUP BY COUNTRY;
#7)查询武力排行前三名的武将姓名和武力。
SELECT NAME,ATTACK FROM T_HERO ORDER BY ATTACK DESC LIMIT 3;
#8)查询所有的在野（无国家）武将姓名和武力。
SELECT NAME,ATTACK FROM T_HERO WHERE COUNTRY IS NULL;
#9)查询武将数大于3人的国家的国家名和人数。
SELECT COUNTRY,COUNT(ID) NUM FROM T_HERO GROUP BY COUNTRY HAVING COUNT(ID)>3;
#10)查询武力值和周瑜相同的所有武将的姓名和国家。
SELECT NAME,COUNTRY FROM T_HERO WHERE ATTACK = (SELECT ATTACK FROM T_HERO WHERE NAME = "周瑜");
cmd创建命令练习过程（部分）：
+----+------------+------+------------+
7 rows in set (0.00 sec)
mysql> update t_01 set age=15 where name="妙玉";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert into t_01 (name,age,talentPoem) values ("凤辣子",33,78);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_01;
+----+------------+------+------------+
| id | name | age | talentPoem |
+----+------------+------+------------+
| 1 | 宝玉哥哥 | 18 | 90 |
| 2 | 黛玉妹纸 | 17 | 98 |
| 3 | 宝钗妹妹 | 17 | 96 |
| 4 | 湘云小兄弟 | 18 | 90 |
| 5 | 香菱小丫头 | 16 | 65 |
| 6 | 探春 | 15 | 88 |
| 7 | 妙玉 | 15 | 93 |
| 8 | 凤辣子 | 33 | 78 |
+----+------------+------+------------+
8 rows in set (0.00 sec)
mysql> delete from t_01 where id = 8;
Query OK, 1 row affected (0.00 sec)
mysql> create table t_hero (
-> id int auto_increment comment "英雄Id",
-> name varchar(10) not null comment "英雄姓名";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
mysql> attack int comment "武力值",
-> country varchar(10) comment "国家";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'attack int comment "武力值",
country varchar(10) comment "国家"' at line 1
mysql> )comment="三国英雄表";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')comment="三国英雄表"' at line 1
mysql> create table t_hero (
-> id int auto_increment comment "英雄Id",
-> name varchar(10) not null comment "英雄姓名",
-> attack int comment "武力值",
-> country varchar(10) comment "国家"
-> )comment="三国英雄表";
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table t_hero (
-> id int auto_increment comment "英雄Id",
-> name varchar(10) not null comment "英雄姓名",
-> attack int comment "武力值",
-> country varchar(10) comment "国家",
-> primary key(id)
-> )comment="三国英雄表";
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------------+
| Tables_in_db_xiaomei |
+----------------------+
| t_01 |
| t_hero |
+----------------------+
2 rows in set (0.00 sec)
mysql> desc t_hero;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| attack | int(11) | YES | | NULL | |
| country | varchar(10) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)
mysql> insert into t_hero (id,name,attack,country) values
-> (101,"刘备",78,"蜀"),
-> (102,"曹操",83,"魏"),
-> (103,"孙权",89,"吴"),
-> (104,"关羽",96,"蜀"),
-> (105,"张飞",98,"蜀"),
-> (106,"吕布",99,NULL),
-> (107,"诸葛亮",70,"蜀"),
-> (108,"周瑜",90,"吴" ),
-> (109,"陆逊",89,"吴");
Query OK, 9 rows affected (0.05 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> (110,"司马懿",67,"魏"),
-> (111,"马超",90,NULL);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '110,"司马懿",67,"魏"),
(111,"马超",90,NULL)' at line 1
mysql> select * from t_hero;
+-----+--------+--------+---------+
| id | name | attack | country |
+-----+--------+--------+---------+
| 101 | 刘备 | 78 | 蜀 |
| 102 | 曹操 | 83 | 魏 |
| 103 | 孙权 | 89 | 吴 |
| 104 | 关羽 | 96 | 蜀 |
| 105 | 张飞 | 98 | 蜀 |
| 106 | 吕布 | 99 | NULL |
| 107 | 诸葛亮 | 70 | 蜀 |
| 108 | 周瑜 | 90 | 吴 |
| 109 | 陆逊 | 89 | 吴 |
+-----+--------+--------+---------+
9 rows in set (0.00 sec)
mysql> nsert into t_hero (id,name,attack,country) values
-> (110,"司马懿",67,"魏"),
-> (111,"马超",90,NULL);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'nsert into t_hero (id,name,attack,country) values
(110,"司马懿",67,"魏"),
' at line 1
mysql> delete from t_hero;
Query OK, 9 rows affected (0.05 sec)
mysql> select * from t_hero;
Empty set (0.00 sec)
mysql> insert into t_hero (id,name,attack,country) values
-> (101,"刘备",78,"蜀"),
-> (102,"曹操",83,"魏"),
-> (103,"孙权",89,"吴"),
-> (104,"关羽",96,"蜀"),
-> (105,"张飞",98,"蜀"),
-> (106,"吕布",99,NULL),
-> (107,"诸葛亮",70,"蜀"),
-> (108,"周瑜",90,"吴" ),
-> (109,"陆逊",89,"吴"),
-> (110,"司马懿",67,"魏"),
-> (111,"马超",90,NULL);
Query OK, 11 rows affected (0.05 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> select * from t_hero;
+-----+--------+--------+---------+
| id | name | attack | country |
+-----+--------+--------+---------+
| 101 | 刘备 | 78 | 蜀 |
| 102 | 曹操 | 83 | 魏 |
| 103 | 孙权 | 89 | 吴 |
| 104 | 关羽 | 96 | 蜀 |
| 105 | 张飞 | 98 | 蜀 |
| 106 | 吕布 | 99 | NULL |
| 107 | 诸葛亮 | 70 | 蜀 |
| 108 | 周瑜 | 90 | 吴 |
| 109 | 陆逊 | 89 | 吴 |
| 110 | 司马懿 | 67 | 魏 |
| 111 | 马超 | 90 | NULL |
+-----+--------+--------+---------+
11 rows in set (0.00 sec)
mysql> select name,attack from t_hero order by attack desc;
+--------+--------+
| name | attack |
+--------+--------+
| 吕布 | 99 |
| 张飞 | 98 |
| 关羽 | 96 |
| 周瑜 | 90 |
| 马超 | 90 |
| 孙权 | 89 |
| 陆逊 | 89 |
| 曹操 | 83 |
| 刘备 | 78 |
| 诸葛亮 | 70 |
| 司马懿 | 67 |
+--------+--------+
11 rows in set (0.00 sec)
mysql> SELECT name,attack FROM tbgeneral ORDER BY attack DESC
-> ;
ERROR 1146 (42S02): Table 'db_xiaomei.tbgeneral' doesn't exist
mysql> SELECT NAME,ATTACK FROM T_HERO ORDER BY ATTACK DESC;
+--------+--------+
| NAME | ATTACK |
+--------+--------+
| 吕布 | 99 |
| 张飞 | 98 |
| 关羽 | 96 |
| 周瑜 | 90 |
| 马超 | 90 |
| 孙权 | 89 |
| 陆逊 | 89 |
| 曹操 | 83 |
| 刘备 | 78 |
| 诸葛亮 | 70 |
| 司马懿 | 67 |
+--------+--------+
11 rows in set (0.01 sec)
mysql> SELECT NAME,COUNTRY FROM T_HERO WHERE COUNTRY IN ("魏","蜀");
+--------+---------+
| NAME | COUNTRY |
+--------+---------+
| 刘备 | 蜀 |
| 曹操 | 魏 |
| 关羽 | 蜀 |
| 张飞 | 蜀 |
| 诸葛亮 | 蜀 |
| 司马懿 | 魏 |
+--------+---------+
6 rows in set (0.00 sec)
mysql> SELECT ID,NAME,ATTACK FROM T_HERO WHERE ATTACK BETWEEN 80 AND 90;
+-----+------+--------+
| ID | NAME | ATTACK |
+-----+------+--------+
| 102 | 曹操 | 83 |
| 103 | 孙权 | 89 |
| 108 | 周瑜 | 90 |
| 109 | 陆逊 | 89 |
| 111 | 马超 | 90 |
+-----+------+--------+
5 rows in set (0.00 sec)
mysql> SELECT MAX(ATTACK) 武力最大,MIN(ATTACK) 武力最小,AVG(ATTACK) 武力平均 FROM T_HERO;
+----------+----------+----------+
| 武力最大 | 武力最小 | 武力平均 |
+----------+----------+----------+
| 99 | 67 | 86.2727 |
+----------+----------+----------+
1 row in set (0.00 sec)
mysql> SELECT ID,NAME FROM T_HERO WHERE NAME LIKE "张%";
+-----+------+
| ID | NAME |
+-----+------+
| 105 | 张飞 |
+-----+------+
1 row in set (0.00 sec)
mysql> SELECT COUNTRY,COUNT(COUNTRY) 武将数量 FROM T_HERO GRUOP BY COUNTRY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY COUNTRY' at line 1
mysql> SELECT COUNTRY,COUNT(NAME) 武将数量 FROM T_HERO GRUOP BY COUNTRY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY COUNTRY' at line 1
mysql> SELECT COUNTRY,COUNT(ID) 武将数量 FROM T_HERO GRUOP BY COUNTRY;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BY COUNTRY' at line 1
mysql> SELECT COUNTRY,COUNT(COUNTRY) 武将数量 FROM T_HERO GROUP BY COUNTRY;
+---------+----------+
| COUNTRY | 武将数量 |
+---------+----------+
| NULL | 0 |
| 吴 | 3 |
| 蜀 | 4 |
| 魏 | 2 |
+---------+----------+
4 rows in set (0.00 sec)
mysql> SELECT COUNTRY,COUNT(ID) 武将数量 FROM T_HERO GROUP BY COUNTRY;
+---------+----------+
| COUNTRY | 武将数量 |
+---------+----------+
| NULL | 2 |
| 吴 | 3 |
| 蜀 | 4 |
| 魏 | 2 |
+---------+----------+
4 rows in set (0.00 sec)
mysql> SELECT NAME,ATTACK FROM T_HERO ORDER BY ATTACK DESC LIMIT 3;
+------+--------+
| NAME | ATTACK |
+------+--------+
| 吕布 | 99 |
| 张飞 | 98 |
| 关羽 | 96 |
+------+--------+
3 rows in set (0.00 sec)
mysql> SELECT NAME,ATTACK FROM T_HERO WHERE COUNTRY IS NULL;
+------+--------+
| NAME | ATTACK |
+------+--------+
| 吕布 | 99 |
| 马超 | 90 |
+------+--------+
2 rows in set (0.00 sec)
mysql> SELECT COUNTRY,COUNT(ID) NUM FROM T_HERO GROUP BY COUNTRY HAVING COUNT>3;
ERROR 1054 (42S22): Unknown column 'COUNT' in 'having clause'
mysql> SELECT COUNTRY,COUNT(ID) NUM FROM T_HERO GROUP BY COUNTRY HAVING COUNT(ID)>3;
+---------+-----+
| COUNTRY | NUM |
+---------+-----+
| 蜀 | 4 |
+---------+-----+
1 row in set (0.00 sec)
mysql> SELECT NAME,COUNTRY FROM T_HERO WHERE ATTACK = (SELECT ATTACK FROM T_HERO WHERE NAME = "周瑜");
+------+---------+
| NAME | COUNTRY |
+------+---------+
| 周瑜 | 吴 |
| 马超 | NULL |
+------+---------+
2 rows in set (0.00 sec)
mysql>五、总结
现在是23:07分，时间总是过得很快。总结一下下：
1.这里未涉及多表查询，所以我们拭目以待吧；
2.语句都是最基础最基础的，当然，即便是最基础的语句，写sql的时候，不小心也容易犯错；
3.写出好的SQL，个人觉得对需求的理解是最关键的，说到底就是语文阅读理解能力；
4.有个疑问，怎样才算是SQL大咖？
六、下期预告
下期，我们将“连载”基础的项目，笔者边写边发表博客吧，欢迎围观斧正~~
好了，晚安，各位读者盆友们。下期再会！
扫一扫
473
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
