MySQL学习总结之路（第二章：表）

• Z时代
• 2024-01-10
• 分类：综合

目录

MySQL学习总结之路（第一章：服务与数据库管理）

MySQL学习总结之路（第二章：表）

MySQL学习总结之路（第三章：数据类型）

 ......

1、创建表

1.1、创建表基本语法：

CREATETABLE tablename (column_name_1 column_type_1 constraints，
column_name_2 column_type_2 constraints ， ……）

column_name 是列的名字

column_type 是列的数据类型

contraints 是这个列的约束条件

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

“G”选项的含义是使得记录能够按照字段竖着排列，对于内容比较长的记录更易于显示。

2、删除表

命令：

DROPTABLE tablename

删除orders：

mysql>droptable orders
-> ;
Query OK, 0 rows affected (0.14 sec)

3、修改表

3.1、修改表类型命令：

ALTERTABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]

例：修改表 orders 的 name 字段定义，将 varchar(10)改为 varchar(20)：

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、字段改名命令：

ALTERTABLE 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、增加表字段命令：

ALTERTABLE tablename ADD[COLUMN] column_definition [FIRST | AFTER col_name]

例：表 orders 上新增加字段 username，类型为 varchar(3)：

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、删除表列字段命令

ALTERTABLE 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、表改名命令

ALTERTABLE 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 语句

插入（insert）、查询（select）、更新（update）、删除（delete）

4.1、插入记录 命令

INSERTINTO 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]

offset_start 表示记录的起始偏移量

row_count 表示显示的行数

例如1：显示 goodsorders表中按照 createtiem 排序后的前 3 条记录：

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 后面。

 3、WITH ROLLUP 是可选语法，表明是否对分类聚合后的结果进行再汇总。

4、HAVING 关键字表示对分类后的结果再进行条件的过滤。

注意：having 和 where 的区别在于 having 是对聚合后的结果进行条件的过滤，而 where 是在聚合前就对记录进行过滤，如果逻辑允许，我们尽可能用 where 先过滤记录，这样因为结果集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用 having 进行再过滤。

例1：查询统计goodsorders表中，记录总数

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|UNIONALL
SELECT*FROM t2
……
UNION|UNIONALL
SELECT*FROM tn;

UNION 和 UNION ALL 的主要区别：

   　　UNION ALL 是把结果集直接合并在一起，

　　   UNION 是将UNION ALL 后的结果进行一次 DISTINCT，去除重复记录后的结果。

例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、删除记录命名

DELETEFROM 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创建数据库用户

例：创建一个数据库用户 user1,初始密码为123，具有对 ordermanage 数据库中所有表的 SELECT/INSERT 权限：

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 FilesMySQLMySQL Server 5.7in>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 FilesMySQLMySQL Server 5.7in>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>

由此可以看出插入权限不足，插入失败

此章节完事儿嘞

以上是 MySQL学习总结之路（第二章：表） 的全部内容， 来源链接： utcz.com/z/535604.html