MySQL学习总结之路(第二章:表)
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL学习总结之路(第二章:表),小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含18242字,纯文字阅读大概需要27分钟。
内容图文
![MySQL学习总结之路(第二章:表)](/upload/InfoBanner/zyjiaocheng/1334/fb932a8fd15f420d8a475fb82fcbf8cc.jpg)
目录
MySQL学习总结之路(第三章:数据类型)
......
1、创建表
1.1、创建表基本语法:
CREATE TABLE tablename (column_name_1 column_type_1 constraints, column_name_2 column_type_2 constraints , ……)
1.1.1、创建一张简单的表
mysql>createtable orders (ordername varchar(10),createtime date,ordermoney decimal(10,2),ordernumber int(2)); Query OK, 0 rows affected (0.23 sec)
1.1.2、查看创建表定义
1.1.2.1、结构化定义
mysql>desc orders; +-------------+---------------+------+-----+---------+-------+| Field | Type |Null|Key|Default| Extra |+-------------+---------------+------+-----+---------+-------+| ordername |varchar(10) | YES ||NULL||| createtime | date | YES ||NULL||| ordermoney |decimal(10,2) | YES ||NULL||| ordernumber |int(2) | YES ||NULL||+-------------+---------------+------+-----+---------+-------+4 rows inset (0.00 sec)
1.1.2.2、表详细定义
查看详细的表定义
mysql> show createtable orders \G; ***************************1. row ***************************Table: orders CreateTable: CREATETABLE `orders` ( `ordername` varchar(10) DEFAULTNULL, `createtime` date DEFAULTNULL, `ordermoney` decimal(10,2) DEFAULTNULL, `ordernumber` int(2) DEFAULTNULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row inset (0.00 sec) ERROR: No query specified
由此可以看到表的 ENGINE(存储引擎)是InnoDB
CHARSET(字符集)是Latin1
2、删除表
命令:
DROP TABLE tablename
删除orders:
mysql>droptable orders -> ; Query OK, 0 rows affected (0.14 sec)
3、修改表
3.1、修改表类型命令:
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
mysql>altertable orders modify ordername varchar(20); Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>desc orders; +-------------+---------------+------+-----+---------+-------+| Field | Type |Null|Key|Default| Extra |+-------------+---------------+------+-----+---------+-------+| ordername |varchar(20) | YES ||NULL||| createtime | date | YES ||NULL||| ordermoney |decimal(10,2) | YES ||NULL||| ordernumber |int(2) | YES ||NULL||+-------------+---------------+------+-----+---------+-------+4 rows inset (0.00 sec)
3.2、字段改名命令:
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST|AFTER col_name]
例:表 orders 上将ordernumber修改为ordernumbers
mysql>altertable orders change column ordernumber ordernumbers int(4); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>desc orders; +--------------+---------------+------+-----+---------+-------+| Field | Type |Null|Key|Default| Extra |+--------------+---------------+------+-----+---------+-------+| ordername |varchar(20) | YES ||NULL||| createtime | date | YES ||NULL||| ordermoney |decimal(10,2) | YES ||NULL||| ordernumbers |int(4) | YES ||NULL||+--------------+---------------+------+-----+---------+-------+4 rows inset (0.00 sec)
特别说明:change 和 modify 都可以修改表的定义,不同的是 change 后面需要写两次列名,不方便。但是 change 的优点是可以修改列名称,modify 则不能。
3.3、增加表字段命令:
ALTER TABLE tablename ADD[COLUMN] column_definition [FIRST | AFTER col_name]
mysql>altertable orders addcolumn username varchar(30); Query OK, 0 rows affected (0.39 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>desc orders; +-------------+---------------+------+-----+---------+-------+| Field | Type |Null|Key|Default| Extra |+-------------+---------------+------+-----+---------+-------+| ordername |varchar(20) | YES ||NULL||| createtime | date | YES ||NULL||| ordermoney |decimal(10,2) | YES ||NULL||| ordernumber |int(2) | YES ||NULL||| username |varchar(30) | YES ||NULL||+-------------+---------------+------+-----+---------+-------+5 rows inset (0.00 sec)
3.4、删除表列字段命令
ALTER TABLE tablename DROP[COLUMN]col_name
例:表 orders 上删除字段 username:
mysql>altertable orders dropcolumn username; Query OK, 0 rows affected (0.53 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>desc orders; +-------------+---------------+------+-----+---------+-------+| Field | Type |Null|Key|Default| Extra |+-------------+---------------+------+-----+---------+-------+| ordername |varchar(20) | YES ||NULL||| createtime | date | YES ||NULL||| ordermoney |decimal(10,2) | YES ||NULL||| ordernumber |int(2) | YES ||NULL||+-------------+---------------+------+-----+---------+-------+4 rows inset (0.00 sec)
3.5、表改名命令
ALTER TABLE tablename RENAME [TO] new_tablename
例:表 orders 名字改为goodsorders
mysql>altertable orders rename goodsorders; Query OK, 0 rows affected (0.16 sec) mysql>desc orders; ERROR 1146 (42S02): Table‘ordermanage.orders‘ doesn‘t exist mysql> desc goodsorders; +--------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+---------+-------+ | ordername | varchar(20) | YES | | NULL | | | createtime | date | YES | | NULL | | | ordermoney | decimal(10,2) | YES | | NULL | | | ordernumbers | int(4) | YES | | NULL | | +--------------+---------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
4、DML 语句
4.1、插入记录 命令
INSERT INTO tablename (field1,field2,……fieldn) VALUES(value1,value2,……valuesn);
例:表 goodsorders 中插入一条记录,ordername 为zhang,createtime为2021-05-12,ordermoney为100.00,ordernumbers为:1
mysql>insertinto goodsorders (ordername,createtime,ordermoney,ordernumbers) values(‘zhang‘,‘2021-05-12‘,100.00,1); Query OK, 1 row affected (0.03 sec)
也可以省略(field1,field2,……fieldn)这一部分
mysql>insertinto goodsorders values(‘zhang1‘,‘2021-05-12‘,1001.00,11); Query OK, 1 row affected (0.05 sec)
4.2、查看插入数据命名
4.2.1、查询全部
SELECT * FROM tablename [WHERE CONDITION]
例:查看goodsorders中所有插入数据
mysql>select*from goodsorders; +-----------+------------+------------+--------------+| ordername | createtime | ordermoney | ordernumbers |+-----------+------------+------------+--------------+| zhang |2021-05-12|100.00|1|| zhang1 |2021-05-12|1001.00|11|+-----------+------------+------------+--------------+2 rows inset (0.00 sec)
4.2.2、查询不重复记录命令关键字
distinct
例:查询非goodsorders中非重复创建时间(createtime)的数据
mysql>select*from goodsorders; +-----------+------------+------------+--------------+| ordername | createtime | ordermoney | ordernumbers |+-----------+------------+------------+--------------+| zhang |2021-03-11|50.00|1|| li |2020-05-12|70.00|15|| li |2020-03-12|70.00|15|| li |2020-03-11|70.00|15|| li |2021-03-11|70.00|15|+-----------+------------+------------+--------------+5 rows inset (0.00 sec) mysql>selectdistinct createtime from goodsorders; +------------+| createtime |+------------+|2021-03-11||2020-05-12||2020-03-12||2020-03-11|+------------+4 rows inset (0.00 sec)
由此可以看到,将重复的一条时间数据2021-03-11去掉了
4.2.3、多条件查询关键字
where 后面的条件是一个字段的‘=’比较,还可以使用>、<、>=、<=、!=等比较运算符; 多个条件之间还可以使用 or、and 等逻辑运算符进行多条件联合查询,
例:查询非goodsorders中 ordername=‘li‘并且createtime为2020-03-11
mysql>select*from goodsorders where ordername=‘li‘and createtime =‘2020-03-11‘; +-----------+------------+------------+--------------+| ordername | createtime | ordermoney | ordernumbers |+-----------+------------+------------+--------------+| li |2020-03-11|70.00|15|+-----------+------------+------------+--------------+1 row inset (0.00 sec)
4.2.4、排序查询命名
SELECT * FROM tablename [WHERE CONDITION][ORDER BY field1 [DESC|ASC] , field2 [DESC|ASC],……fieldn [DESC|ASC]]
例:把 goodsorders表中的记录按照创建时间高低进行排序显示
mysql>select*from goodsorders orderby createtime; +-----------+------------+------------+--------------+| ordername | createtime | ordermoney | ordernumbers |+-----------+------------+------------+--------------+| li |2020-03-11|70.00|15|| li |2020-03-12|70.00|15|| li |2020-05-12|70.00|15|| zhang |2021-03-11|50.00|1|| li |2021-03-11|70.00|15|+-----------+------------+------------+--------------+5 rows inset (0.01 sec)
4.2.5、显示一部分,而不是全部,指令
SELECT ……[LIMIT offset_start,row_count]
mysql>select*from goodsorders orderby createtime limit 3; +-----------+------------+------------+--------------+| ordername | createtime | ordermoney | ordernumbers |+-----------+------------+------------+--------------+| li |2020-03-11|70.00|15|| li |2020-03-12|70.00|15|| li |2020-05-12|70.00|15|+-----------+------------+------------+--------------+3 rows inset (0.00 sec)
例如2:如果要显示 goodsorders表中按照 createtiem 排序后 从第二条记录开始,显示3条数据:
mysql>select*from goodsorders orderby createtime limit 2,3; +-----------+------------+------------+--------------+| ordername | createtime | ordermoney | ordernumbers |+-----------+------------+------------+--------------+| li |2020-05-12|70.00|15|| zhang |2021-03-11|50.00|1|| li |2021-03-11|70.00|15|+-----------+------------+------------+--------------+3 rows inset (0.00 sec)
4.2.6、统计数据,聚合指令
SELECT [ field1,field2,……fieldn ] fun_name FROM tablename [ WHERE where_contition ] [ GROUP BY field1,field2,……fieldn [WITH ROLLUP ] ] [ HAVING where_contition ]
参数说明:
1、fun_name 表示要做的聚合操作,也就是聚合函数,常用的有 sum(求和)、count(*)(记录数)、max(最大值)、min(最小值)
2、GROUP BY 关键字表示要进行分类聚合的字段,比如要按照部门分类统计员工数量,部门就应该写在 group by 后面。
mysql>selectcount(1) from goodsorders; +----------+|count(1) |+----------+|5|+----------+1 row inset (0.00 sec)
例2:在此基础上,按照创建日期(createtime)进行分组统计
mysql>select createtime,count(1) from goodsorders groupby createtime; +------------+----------+| createtime |count(1) |+------------+----------+|2020-03-11|1||2020-03-12|1||2020-05-12|1||2021-03-11|2|+------------+----------+4 rows inset (0.00 sec)
例3:在此基础上,既要按照创建日期(cretetime)进行分组统计,又要计算总数
mysql>select createtime,count(1) from goodsorders groupby createtime with rollup; +------------+----------+| createtime |count(1) |+------------+----------+|2020-03-11|1||2020-03-12|1||2020-05-12|1||2021-03-11|2||NULL|5|+------------+----------+5 rows inset (0.02 sec)
最有一行,null所展示的数字,就是总数
例4:按照创建日期(createtime)进行分组统计,并且数量大于1
mysql>select createtime,count(1) from goodsorders groupby createtime havingcount(1)>1; +------------+----------+| createtime |count(1) |+------------+----------+|2021-03-11|2|+------------+----------+1 row inset (0.00 sec)
例5:查询goodsorders表中,订单金额(ordermoney)的总额、最低额、最高额
mysql>select*from goodsorders; +-----------+------------+------------+--------------+| ordername | createtime | ordermoney | ordernumbers |+-----------+------------+------------+--------------+| zhang |2021-03-11|50.00|1|| li |2020-05-12|70.00|15|| li |2020-03-12|70.00|15|| li |2020-03-11|70.00|15|| li |2021-03-11|70.00|15|+-----------+------------+------------+--------------+5 rows inset (0.00 sec) mysql>selectsum(ordermoney),max(ordermoney),min(ordermoney) from goodsorders; +-----------------+-----------------+-----------------+|sum(ordermoney) |max(ordermoney) |min(ordermoney) |+-----------------+-----------------+-----------------+|330.00|70.00|50.00|+-----------------+-----------------+-----------------+1 row inset (0.02 sec)
4.2.7、表连接
1、左连接:包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录;关键指令:left join
2、右连接:包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录;关联指令:right join
例1:现在我们又创建一张用户表(member),使用goodorders进行左连接,查询关联的用户表信息
mysql>select*from member; +------+------------+| id | membername |+------+------------+|15| zhang ||1| li ||13| liss |+------+------------+3 rows inset (0.00 sec) mysql>select*from goodsorders; +-----------+------------+------------+--------------+----------+| ordername | createtime | ordermoney | ordernumbers | memberid |+-----------+------------+------------+--------------+----------+| zhang |2021-03-11|50.00|1|15|| li |2020-05-12|70.00|15|1|| li |2020-03-12|70.00|15|1|| li |2020-03-11|70.00|15|3|| li |2021-03-11|70.00|15|1|+-----------+------------+------------+--------------+----------+5 rows inset (0.00 sec) mysql>select*from goodsorders leftjoin member on goodsorders.memberid = member.id; +-----------+------------+------------+--------------+----------+------+------------+| ordername | createtime | ordermoney | ordernumbers | memberid | id | membername |+-----------+------------+------------+--------------+----------+------+------------+| zhang |2021-03-11|50.00|1|15|15| zhang || li |2020-05-12|70.00|15|1|1| li || li |2020-03-12|70.00|15|1|1| li || li |2021-03-11|70.00|15|1|1| li || li |2020-03-11|70.00|15|3|NULL|NULL|+-----------+------------+------------+--------------+----------+------+------------+5 rows inset (0.00 sec)
例2:member和goodsorders中数据不变,我们再来看一下右连接的查询,以及结果:
mysql>select*from goodsorders rightjoin member on goodsorders.memberid = member.id; +-----------+------------+------------+--------------+----------+------+------------+| ordername | createtime | ordermoney | ordernumbers | memberid | id | membername |+-----------+------------+------------+--------------+----------+------+------------+| zhang |2021-03-11|50.00|1|15|15| zhang || li |2020-05-12|70.00|15|1|1| li || li |2020-03-12|70.00|15|1|1| li || li |2021-03-11|70.00|15|1|1| li ||NULL|NULL|NULL|NULL|NULL|13| liss |+-----------+------------+------------+--------------+----------+------+------------+5 rows inset (0.00 sec)
这里发生了翻转,变为左侧goodsorders 表中的一条数据为空了
4.2.8、子查询,相关关键字
主要包括 in、notin、=、!=、exists、notexists 等
例:从goodsorders表中查询所有用户在memeber表中的记录
mysql>select*from member; +------+------------+| id | membername |+------+------------+|15| zhang ||1| li ||13| liss |+------+------------+3 rows inset (0.00 sec) mysql>select*from goodsorders; +-----------+------------+------------+--------------+----------+| ordername | createtime | ordermoney | ordernumbers | memberid |+-----------+------------+------------+--------------+----------+| zhang |2021-03-11|50.00|1|15|| li |2020-05-12|70.00|15|1|| li |2020-03-12|70.00|15|1|| li |2020-03-11|70.00|15|3|| li |2021-03-11|70.00|15|1|+-----------+------------+------------+--------------+----------+5 rows inset (0.00 sec) mysql>select*from goodsorders where memberid in(select id from member); +-----------+------------+------------+--------------+----------+| ordername | createtime | ordermoney | ordernumbers | memberid |+-----------+------------+------------+--------------+----------+| zhang |2021-03-11|50.00|1|15|| li |2020-05-12|70.00|15|1|| li |2020-03-12|70.00|15|1|| li |2021-03-11|70.00|15|1|+-----------+------------+------------+--------------+----------+4 rows inset (0.05 sec)
4.2.9、记录联合,指令
SELECT * FROM t1 UNION | UNION ALL SELECT * FROM t2 …… UNION | UNION ALL SELECT * FROM tn;
例1:将member表和goodsorders表中的用户编号id(memberid)的集合显示出来
mysql>select memberid from goodsorders unionallselect id from member; +----------+| memberid |+----------+|15||1||1||3||1||15||1||13|+----------+8 rows inset (0.00 sec)
例2:如果希望将上面的结果去掉重复记录后显示
mysql>select memberid from goodsorders unionselect id from member; +----------+| memberid |+----------+|15||1||3||13|+----------+4 rows inset (0.00 sec)
4.3、更新记录命令
UPDATE tablename SET field1=value1,field2.=value2,……fieldn=valuen [WHERE CONDITION]
例:将表 goodsorders 中ordername为zhang的订单金额(ordermoney)改为50
mysql>update goodsorders set ordermoney=50.00where ordername=‘zhang‘; Query OK, 1 row affected (0.09 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql>select*from goodsorders; +-----------+------------+------------+--------------+| ordername | createtime | ordermoney | ordernumbers |+-----------+------------+------------+--------------+| zhang |2021-05-12|50.00|1|| zhang1 |2021-05-12|1001.00|11|+-----------+------------+------------+--------------+2 rows inset (0.00 sec)
4.4、删除记录命名
DELETE FROM tablename [WHERE CONDITION]
例:将表 goodsorders 中ordername为zhang1的记录全部删除
mysql>deletefrom goodsorders where ordername =‘zhang1‘; Query OK, 1 row affected (0.06 sec) mysql>select*from goodsorders; +-----------+------------+------------+--------------+| ordername | createtime | ordermoney | ordernumbers |+-----------+------------+------------+--------------+| zhang |2021-05-12|50.00|1|+-----------+------------+------------+--------------+1 row inset (0.02 sec)
5、DCL 语句
DCL语句主要是为了管理数据库系统中的操作对象权限
5.1创建数据库用户
mysql>grantselect,inserton ordermanage.*to‘user1‘@‘localhost‘ identified by‘123‘; Query OK, 0 rows affected, 1 warning (0.06 sec) mysql>exit Bye C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uuser1 -p123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands endwith ; or \g. Your MySQL connection id is82 Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘or‘\h‘for help. Type ‘\c‘to clear the current input statement. mysql> show databases; +--------------------+|Database|+--------------------+| information_schema || ordermanage |+--------------------+2 rows inset (0.00 sec)
在此基础上,将此用户(user1)的insert权限进行收回
mysql>revokeinserton ordermanage.*from‘user1‘@‘localhost‘; Query OK, 0 rows affected (0.02 sec) mysql>exit Bye C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -uuser1 -p123 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands endwith ; or \g. Your MySQL connection id is84 Server version: 5.7.17-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘or‘\h‘for help. Type ‘\c‘to clear the current input statement. mysql>use ordermanage; Database changed mysql>insertinto member values(‘11‘,‘ss‘); ERROR 1142 (42000): INSERT command denied touser‘user1‘@‘localhost‘fortable‘member‘ mysql>
由此可以看出插入权限不足,插入失败
此章节完事儿嘞
原文:https://www.cnblogs.com/19930521zhang/p/14756371.html
内容总结
以上是互联网集市为您收集整理的MySQL学习总结之路(第二章:表)全部内容,希望文章能够帮你解决MySQL学习总结之路(第二章:表)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。