目录
- 1、创建表
- 1.1、创建表基本语法
- 1.1.1、创建一张简单的表
- 1.1.2、查看创建表定义
- 2、删除表
- 3、修改表
- 3.1、修改表类型命令
- 3.2、字段改名命令
- 3.3、增加表字段命令
- 3.4、删除表列字段命令
- 3.5、表改名命令
- 4、dml 语句
- 4.1、插入记录 命令
- 4.2、查看插入数据命令
- 4.2.1、查询全部
- 4.2.2、查询不重复记录命令关键字
- 4.2.3、多条件查询关键字
- 4.2.4、排序查询命名
- 4.2.5、显示一部分，而不是全部，指令
- 4.2.6、统计数据，聚合指令
- 4.2.7、表连接
- 4.2.8、子查询，相关关键字
- 4.2.9、记录联合，指令
- 4.3、更新记录命令
- 4.4、删除记录命名
- 4.5、初始化表
- 5、dcl 语句
- 5.1创建数据库用户
1、创建表
1.1、创建表基本语法
create table tablename (column_name_1 column_type_1 constraints， column_name_2 column_type_2 constraints ， ……）
column_name 是列的名字
column_type 是列的数据类型
contraints 是这个列的约束条件
1.1.1、创建一张简单的表
mysql> create table orders (ordername varchar(10),createtime date,ordermoney decimal(10,2),ordernumber int(2)); query ok, 0 rows affected (0.23 sec)
1.1.2、查看创建表定义
结构化定义：
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 in set (0.00 sec)
表详细定义：
查看详细的表定义：
mysql> show create table orders \g;
*************************** 1. row ***************************
table: orders
create table: create table `orders` (
`ordername` varchar(10) default null,
`createtime` date default null,
`ordermoney` decimal(10,2) default null,
`ordernumber` int(2) default null
) engine=innodb default charset=latin1
1 row in set (0.00 sec)
error:
no query specified
由此可以看到表的 engine（存储引擎）是innodb
charset（字符集）是latin1
“\g”选项的含义是使得记录能够按照字段竖着排列，对于内容比较长的记录更易于显示。
2、删除表
命令：
drop table tablename
删除orders：
mysql> drop table orders
-> ;
query ok, 0 rows affected (0.14 sec)
3、修改表
3.1、修改表类型命令
alter table tablename modify [column] column_definition [first | after col_name]
例：修改表 orders 的 name 字段定义，将 varchar(10)改为 varchar(20) ：
mysql> alter table 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 in set (0.00 sec)
3.2、字段改名命令
alter table tablename change [column] old_col_name column_definition [first|after col_name]
例：表 orders 上将ordernumber修改为ordernumbers
mysql> alter table 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 in set (0.00 sec)
特别说明：change 和 modify 都可以修改表的定义，不同的是 change 后面需要写两次列名，不方便。但是 change 的优点是可以修改列名称，modify 则不能。
3.3、增加表字段命令
alter table tablename add [column] column_definition [first | after col_name]
例：表 orders 上新增加字段 username，类型为 varchar(3) ：
mysql> alter table orders add column 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 in set (0.00 sec)
3.4、删除表列字段命令
alter table tablename drop [column] col_name
例：表 orders 上删除字段 username：
mysql> alter table orders drop column 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 in set (0.00 sec)
3.5、表改名命令
alter table tablename rename [to] new_tablename
例：表 orders 名字改为goodsorders
mysql> alter table 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、插入记录 命令
insert into tablename (field1,field2,……fieldn) values(value1,value2,……valuesn);
例：表 goodsorders 中插入一条记录，ordername 为zhang，createtime为2021-05-12，ordermoney为100.00,ordernumbers为：1
mysql> insert into 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> insert into 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 in set (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 in set (0.00 sec) mysql> select distinct createtime from goodsorders; +------------+ | createtime | +------------+ | 2021-03-11 | | 2020-05-12 | | 2020-03-12 | | 2020-03-11 | +------------+ 4 rows in set (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 in set (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 order by 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 in set (0.01 sec)
4.2.5、显示一部分，而不是全部，指令
select ……[limit offset_start,row_count]
offset_start 表示记录的起始偏移量
row_count 表示显示的行数
例如1：显示 goodsorders表中按照 createtiem 排序后的前 3 条记录：
mysql> select * from goodsorders order by 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 in set (0.00 sec)
例如2：如果要显示 goodsorders表中按照 createtiem 排序后 从第二条记录开始，显示3条数据：
mysql> select * from goodsorders order by 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 in set (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关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，部门就应该写在groupby 后面。 - 3、
with rollup是可选语法，表明是否对分类聚合后的结果进行再汇总。 - 4、
having关键字表示对分类后的结果再进行条件的过滤。
注意：having 和 where 的区别在于 having 是对聚合后的结果进行条件的过滤，而 where 是在聚合前就对记录进行过滤，如果逻辑允许，我们尽可能用 where 先过滤记录，这样因为结果集减小，将对聚合的效率大大提高，最后再根据逻辑看是否用 having 进行再过滤。
例1：查询统计goodsorders表中，记录总数
mysql> select count(1) from goodsorders; +----------+ | count(1) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec)
例2：在此基础上，按照创建日期（createtime）进行分组统计
mysql> select createtime,count(1) from goodsorders group by createtime; +------------+----------+ | createtime | count(1) | +------------+----------+ | 2020-03-11 | 1 | | 2020-03-12 | 1 | | 2020-05-12 | 1 | | 2021-03-11 | 2 | +------------+----------+ 4 rows in set (0.00 sec)
例3：在此基础上，既要按照创建日期（cretetime）进行分组统计，又要计算总数
mysql> select createtime,count(1) from goodsorders group by 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 in set (0.02 sec)
最有一行，null所展示的数字，就是总数
例4：按照创建日期（createtime）进行分组统计，并且数量大于1
mysql> select createtime,count(1) from goodsorders group by createtime having count(1)>1; +------------+----------+ | createtime | count(1) | +------------+----------+ | 2021-03-11 | 2 | +------------+----------+ 1 row in set (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 in set (0.00 sec) mysql> select sum(ordermoney),max(ordermoney),min(ordermoney) from goodsorders; +-----------------+-----------------+-----------------+ | sum(ordermoney) | max(ordermoney) | min(ordermoney) | +-----------------+-----------------+-----------------+ | 330.00 | 70.00 | 50.00 | +-----------------+-----------------+-----------------+ 1 row in set (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 in set (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 in set (0.00 sec) mysql> select * from goodsorders left join 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 in set (0.00 sec)
例2：member和goodsorders中数据不变，我们再来看一下右连接的查询，以及结果：
mysql> select * from goodsorders right join 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 in set (0.00 sec)
这里发生了翻转，变为左侧goodsorders 表中的一条数据为空了
4.2.8、子查询，相关关键字
主要包括 in、not in、=、!=、exists、not exists 等
例：从goodsorders表中查询所有用户在memeber表中的记录
mysql> select * from member; +------+------------+ | id | membername | +------+------------+ | 15 | zhang | | 1 | li | | 13 | liss | +------+------------+ 3 rows in set (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 in set (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 in set (0.05 sec)
4.2.9、记录联合，指令
select * from t1 union|union all select * from t2 …… union|union all select * from tn;
union 和 union all 的主要区别：
union all 是把结果集直接合并在一起，
union 是将union all 后的结果进行一次 distinct，去除重复记录后的结果。
例1：将member表和goodsorders表中的用户编号id（memberid）的集合显示出来
mysql> select memberid from goodsorders union all select id from member; +----------+ | memberid | +----------+ | 15 | | 1 | | 1 | | 3 | | 1 | | 15 | | 1 | | 13 | +----------+ 8 rows in set (0.00 sec)
例2：如果希望将上面的结果去掉重复记录后显示
mysql> select memberid from goodsorders union select id from member; +----------+ | memberid | +----------+ | 15 | | 1 | | 3 | | 13 | +----------+ 4 rows in set (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.00 where 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 in set (0.00 sec)
更新时，如遇到错误代码1175：
error code: 1175. you are using safe update mode and you tried to update a table without a where that uses a key column. to disable safe mode, toggle the option in preferences
解决方法：
1、先进行状体查询：
show variables like 'sql_safe_updates';
2、执行下面的sql，关闭safe-updates模式：
set sql_safe_updates = 0;
或者
set sql_safe_updates = false;
4.4、删除记录命名
delete from tablename [where condition]
例：将表 goodsorders 中ordername为zhang1的记录全部删除
mysql> delete from 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 in set (0.02 sec)
4.5、初始化表
例：将表中的所有数据清空
mysql> select * from varc; +------+------+ | v | c | +------+------+ | abc | abc | +------+------+ 1 row in set (0.03 sec) mysql> truncate table varc; query ok, 0 rows affected (0.25 sec) mysql> select * from varc; empty set (0.00 sec)
5、dcl 语句
dcl语句主要是为了管理数据库系统中的操作对象权限
5.1创建数据库用户
例：创建一个数据库用户 user1,初始密码为123，具有对 ordermanage 数据库中所有表的 select/insert 权限：
mysql> grant select,insert on 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 end with ; or \g. your mysql connection id is 82 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 in set (0.00 sec)
在此基础上，将此用户（user1）的insert权限进行收回
mysql> revoke insert on 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 end with ; or \g.
your mysql connection id is 84
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> insert into member values('11','ss');
error 1142 (42000): insert command denied to user 'user1'@'localhost' for table 'member'
mysql>
由此可以看出插入权限不足，插入失败
到此这篇关于mysql表的介绍的文章就介绍到这了,更多相关mysql表内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！