MySQL-1-基础语法练习
来源：互联网 发布：淘宝推广有效方法 编辑：程序博客网 时间：2024/05/16 14:29
话说：
各位读者朋友，上一篇我们温故了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: 0mysql> 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 3mysql> 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 1mysql> )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 1mysql> 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 keymysql> 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: 0mysql> (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 1mysql> 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 1mysql> 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: 0mysql> 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 existmysql> 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 1mysql> 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 1mysql> 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 1mysql> 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大咖？
六、下期预告
下期，我们将“连载”基础的项目，笔者边写边发表博客吧，欢迎围观斧正~~
好了，晚安，各位读者盆友们。下期再会！
阅读全文
0 0
- MySQL-1-基础语法练习
- MySQL语法练习项目(1)
- MySQL——MySQL语法和基础练习
- C++基础语法练习
- 练习go基础语法
- mysql学习1 基础语法
- JAVA语法基础Demo练习
- JAVA语法基础Demo练习
- Rust基础语法练习(一)
- MySQL语法项目练习(2)
- mysql基础语句练习
- mysql基础练习
- MySQL基础语法总结
- mysql基础语法大全
- MySQL基础语法
- mysql基础语法大全
- Mysql---基础语法
- Mysql基础语法总结
- bootstrap弹出登录框【带合法性检查】
- Kotlin自定义View之TabItem控制Fragments
- Python IDLE快捷键
- C语言解决现实生活的问题1——判断比赛名次
- cc攻击ddos攻击怎么办？比较有效的解决方案分享给大家
- MySQL-1-基础语法练习
- JAVA的日期类DATE、日期格式化、Calendar日历类
- 楼层滚动(JQ)
- Shader小常识之——模型法线和法线纹理是如何工作的
- Slim 框架学习，第十二天 _Router（三）
- 使用模态框进行登录
- 图论基础Dijsktra:Poj1062
- Struts2_ajax交互
- ⚔疯狂输出⚔ 基本数据类型 与包装类
