抱歉，您的浏览器无法访问本站

本页面需要浏览器支持（启用）JavaScript


了解详情 >

Mysql常用指令

秃头王
MySql

发布于：2022年11月19日
次浏览

数据库常用指令 DDL

用户登录

mysql -u 用户名 -p 密码

1
mysql -u root -p

查看数据库

1
show databases;

创建一个简单默认字符集的数据库

create database ==数据库名称==;

1
create database test;

mysql的默认编码为latin1，存储中文时总会出现乱码

创建数据库，判断是否存在如果存在什么都不做

create database if not exists ==数据库名称==;

  • 不会报任何错误
1
create database if not exists test;

创建数据库，并指定字符集

create database ==数据库名== default character set ==字符集==;

1
create database test1 default character set gbk;

查看数据库

  • 查看所有的:但是看不到字符集
1
show databases;
  • 查看单个的数据库并查看基本信息

show create database ==数据名称==;

1
show create database test;
1
2
3
4
5
6
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库

drop database ==数据库名称==;

  • 删除怎么查看：show databases;
1
drop database test;

修改数据库

  • 修改字符集
  • 修改数据库的名称(建议直接新创建)

alter database ==数据名称== default character set ==字符集==;

1
alter database test default character set UTF8;

切换数据库

use ==数据库名称==;

1
use test;

查看目前正在使用的数据库

1
select database();

表的常用指令 DDL

创建表

建立数据表，就是建立表结构，指定数据表中的一共有多少列，每一列的数据类型

create 指的是【创建】，table 指的是【数据表】。

1
2
3
4
5
create table 表名 (
	-- 可以定义多个列
    字段名 类型(长度) 约束,
    字段名 类型(长度) 约束
);

【案例】

1
2
3
4
5
6
-- 创建用户表
create table t_user (
	uid int primary key auto_increment, -- 用户 id
	uname varchar(20) -- 用户名
);
-- auto_increment 标识自增 (前提是 int 类型)

字符类型

常用的类型有：

  • 数字型：int、integer、bigint、mediumint、smallint、tinyint
  • 浮点型：double、float、decimal（精确小数类型）
  • 字符型：char（定长字符串）、varchar（可变长字符串）
  • 日期类型：date（只有年月日）、time（只有时分秒）、datetime（年月日，时分秒）、year
    （年）
  • 二进制字符串类型：binary（定长，以二进制形式保存字符串）、varbinary（可边长）

单表约束

  • 一个表要有一个主键
  • 唯一约束
    • 如:身份证号
  • 非空约束(维护数据的唯一性完整性)
    • 不能不填:如:男女
1
2
3
主键约束：primary key
唯一约束：unique
非空约束：not null

注意

主键约束 = 唯一约束 + 非空约束

查看表

==要先选中数据库要不会报错==

描述一下表

  • Field(字段)
  • Type(类型)
  • Null(是不是非空)
  • Key(是不是主键)
  • Extra(扩展信息)
1
desc t_user;

查看数据中的所有表

1
show tables;

查看表的Sql语句

1
show create table t_user;

快速创建一个表

create table ==新表名== linke ==旧表名==;

  • 会和旧的表一样
1
create table tb_user like t_user;

删除一个表

drop table ==表名==;

1
drop table tb_user;
  • 删除一个表，判断一下删除表是否存在 让他不报错
    • 存在就删除不存在就不删
1
drop table if exists tb_user;

修改表

1
2
3
4
5
6
7
8
9
10
-- 1.修改表添加列
alter table 表名 add 列名 类型(长度) 约束;
-- 2.修改表修改列的类型长度及约束
alter table 表名 modify 列名 类型(长度) 约束;
-- 3.修改表修改列名
alter table 表名 change 旧列名 新列名 类型(长度) 约束;
-- 4.修改表删除列
alter table 表名 drop 列名;
-- 5.修改表名
rename table 表名 to 新表名;

[案例]/(有数据需要谨慎)

1
2
3
4
5
6
7
8
9
10
-- 修改表添加列
alter table user add address varchar(50);
-- 修改表修改列的类型长度及约束
alter table user modify address int(30);
-- 修改表修改列名
alter table user change address addr varchar(50);
-- 修改表删除列
alter table user drop addr;
-- 修改表名 
rename table user to  tb_user;

数据操作语音 DML

插入记录：insert

语法：

1
2
3
4
5
6
7
8
9
-- 1.向表中插入某些列
insert into 表 (列名1,列名2,列名3..) values (值1,值2,值3..);
-- 2.向表中插入所有列
insert into 表 values (值1,值2,值3..);

-- 3.从另外一张表查某些列的结果插入当前表
insert into 表 (列名1, 列名2, 列名3..) values select (列名1,列名2,列名3..) from 表
-- 4.从另外一张表查所有列的结果插入当前表
insert into 表 values select * from 表

【案例】

1
2
3
4
-- 向表中插入某些列，必须写列名
insert into user (uid, uname) values (001, 'cuihua');
-- 向表中插入所有列
insert into user values (002, 'aqiang');

注意：

  • ① 列名数与 values 后面的值的个数相等
  • ② 列的顺序与插入的值得顺序一致
  • ③ 列名的类型与插入的值要一致.
  • ④ 插入值得时候不能超过最大长度.
  • ⑤ 值如果是字符串或者日期需要加引号’’ （一般是单引号或者双引号）

更新记录：update

语法格式：update 更新、set 修改的列值、where 指定条件。

1
2
3
4
-- 1.不指定条件，会修改表中当前列所有数据
update 表名 set 字段名=值, 字段名=值;
-- 2.指定条件，符合条件的才会修改
update 表名 set 字段名=值, 字段名=值 where 条件;

【案例】

1
2
3
4
-- 更新所有字段的值
update user set uname='xiaodong';
-- 根据指定的条件来更新
update user set uname='hashiqi' where uid = 2;

注意：

  • ① 列名的类型与修改的值要一致
  • ② 修改值得时候不能超过最大长度
  • ③ 值如果是字符串或者日期需要加==``==引号

删除记录：delete & truncate

语法格式:

1
delete from 表名 [where 条件];
1
truncate table 表名

注意

删除表中所有记录使用【delete from 表名】，还是用【truncate table 表名】？

删除方式的区别：

1
2
delete:一条一条删除，不清空 auto_increment 记录数。
truncate:直接将表删除，重新建表，auto_increment 将置为零，从新开始

【案例】

1
2
3
4
-- 删除所有数据
delete from user;
-- 删除 uid = 1 的用户
delete from user where uid = 1;

查询DQL

查询语句的顺序
select 列表 from where 条件 group by(分组) having(过滤) order by(排序)

1
select * | 列名, 列名  from 表名 [where 条件];
  • select是查询指令，可以读1-n行数据；
  • 列名换成==*==号,可以查询所有字段数据；
  • 使用where来指定对应的条件

为了后续查询语句准备工作【样例表】

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE products (
    -- 自增加 AUTO_INCREMENT
    pid INT PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(20), -- 商品名称
    price DOUBLE, -- 商品价格
    pdate DATE, -- 日期
    sid VARCHAR(20) -- 分类ID
);

INSERT INTO products VALUES(NULL,'泰国大榴莲', 98, NULL, 's001');
INSERT INTO products VALUES(NULL,'新疆大枣', 38, NULL, 's002');
INSERT INTO products VALUES(NULL,'新疆切糕', 68, NULL, 's001');
INSERT INTO products VALUES(NULL,'十三香', 10, NULL, 's002');
INSERT INTO products VALUES(NULL,'老干妈', 20, NULL, 's002');
INSERT INTO products VALUES(NULL,'老干妈', 20, NULL, 's002');

简答查询

1
2
-- 查询所有
select * from 表名

【案例】

1
2
-- 查询所有
select * from products

查询所有商品的指定列

1
select 列名, 列名... from products;

【案例】

1
select pname, sid from products;

别名查询

1
2
3
4
5
6
-- 语法
-- 使用关键字as, as可以写上也可以省略
-- 表的别名、列的别名
select 列名 as 别名, 列名 as 别名 .... from 表名 as 表别名
-- or
select 列名 别名, 列名 别名 .... from 表名 表别名

【案例】

1
2
3
4
5
6
7
8
-- as or No as
select * from products as p;
select * from products p;
-- 表的别名为什么是 表别名.列明【是不是脱了裤子放p】
-- 实则不是的 因为在多表查询中会可能会有重复列名，就会用到【表别名.列明】
-- 表的别名 还可以简化SQL,区分表
-- 列别名的作用：改变查询结果中列名的名称, 还可以增加可读性
select p.pid pname  from products as p;

去掉重复值

1
select distinct 列名, 列名 from 表名;

【案例】

1
select distinct pname,sid from products;

为一列上所有的的数据加上2

1
select 列名 + 数值 from 表名;

【案例】

1
select pname, price + 2 from products;

条件查询

使用条件查询，可以根据当下具体情况直查想要的那部分数据，对记录进行过滤。

SQL 语法关键字： WHERE

语法格式：

1
select * from 表名 where 条件;

条件怎么写:

操作符 描述 实例
= 等号,检测两个值是否相等，如果相等返回true (A = B) 返回false
<>,!= 不等于,检测两个值是否相等，如果不相等返回true (A != B) 返回 true
> 大于号，检测左边的值是否大于右边的值,如果左边的值大于右边的值返回true (A > B) 返回 false
< 小于号,检测左边的值是否小于右边的值,如果左边的值小于右边的值返回true (A < B) 返回 true
>= 大于等于号,检测左边的值是否大于或等于右边的值,如果左边的值大于或等于右边的值返回true (A >= B) 返回 false
<= 小于等于号,检测左边的值是否小于于或等于右边的值,如果左边的值小于或等于右边的值返回true (A <= B) 返回 true

运算符：局限性只能针对一个字段(不使用逻辑运算符的前提下)

1
2
3
4
5
6
7
8
9
-- 查询商品名称为十三香的商品所有信息
select * from products where pname='十三香';

-- 查询商品名价格 >60 商品所有信息
select * from products where price > 60;

-- 不等于
select * from products where price <> 60;
select * from products where price != 60;

逻辑运算符

NOT 逻辑非【!】
AND 逻辑与【&&】
OR 逻辑或【||】

逻辑运算符：作用是将两个及以上的条件组合在一起

逻辑运算符：

  • NOT 【非】
  • AND 【与】
  • OR 【或】
1
2
3
4
5
-- 语法
-- 查询大商品价格 大于40且小于30的所有商品 【逻辑有问题，只是语法没有错误】
select * from 表名 where 条件1 逻辑运算符 条件2;
-- 查询大商品价格 大于40或小于30的所有商品
 select * from 表名 where 条件1 逻辑运算符 条件2;

【案例】

1
2
-- 查询大商品价格 大于40或小于30的所有商品
 select * from products where price > 30 OR  price < 30;

查询条件in

作用指定范围内的所有数据

1
select * from products where 列名 in (3, 2);

【案例】

1
2
3
4
-- 在范围内
select * from products where pid in (3, 2);
-- 不在范围内
select * from products where pid  not in (38, 38);

范围查询

范围查询:作用查询指定条件内的数据，价格[30 - 100]

  • 01写法查询
1
select *from products where price >= 38 && price <= 98;
  • 02写法: Between … and … 【>= and <=】
1
select * from products where price between 38 and 98;

模糊查询

模糊查询：作用是帮助匹配模糊的字段内容

如有字段：一号学生、二号学生、三号学生、四号学生… …

模糊查询：查询学生只要字段中包含了学生就回查询到!

1
-- 语法: where 之后加上 like 语法。【通配符有】 % (百分号)， _ (下划线)

% (百分号) 代表==任意长度==的内容模糊匹配：

  1. 【%xxx】以 xxx 结尾的任意内容。
  2. 【xxx%】以 xxx 开头的任意内容。
  3. 【%xxx%】包含xxx的任意内容。

_ (下划线) 代表==任意一个字符==的内容模糊匹配：

  1. 【_xxx】以任意单个字符开头的 xxx 的内容。

  3. 【xxx__x】以 xxx 中间任意二个字符以 x 结尾的内容。

1
2
3
4
5
-- 语法
-- 查询所有新疆的产品
select * from 表名 where 列名 like 'xx%';
-- 以打大枣结尾的，但是头两个字符任意
select * from 表名 where 列名 like '__xx';

【案例】

1
2
3
4
5
-- 查询所有新疆的产品 %
select * from products where pname like '新疆%';

-- 以打大枣结尾的，但是头两个字符任意 __
select * from products where pname like '__大枣';

排序查询

排序查询：作用将查询结果进行排序

1
2
3
-- 语法
select * from 表名 where 字段=值 order by 字段名 排序规则[升序、降序]
-- 升序 asc、降序desc

【案例】

1
2
3
4
-- 根基价格 升序
select *from products order by price asc;
-- 查询所有包含'新'的商品，并且价格是从大大小
select * from products where pname like '%新%' order by price desc;

聚合查询

聚合查询：作用对单列数据进行聚合分析【求和、求平均值、求最大值，求最小值】

1
2
-- 语法
select 聚合函数 from 表 where 条件;

聚合函数例子：

  • 求和sum()
  • 求平均值avg()
  • 求最大值max()
  • 求最小值min()
  • 求元素个数count()

==注意==：SQL语句是基于需求实现的，同一个需求可能出现n多种SQL写法。如果SQL语句没有需求背景，SQL如无根之水，毫无意义。

【案例】

1
2
3
4
5
6
7
8
9
10
-- 求所有商品价格的总和
select sum(price) from products;
-- 显示的比较模糊 就用别名
select sum(price) as 所有商品总价 from products;

-- 求所有商品的平均价格/最小价格/最高价格
select avg(price) as 商品平均价格, max(price) as 最大商品价格, min(price) as 商品最小价格 from  products;

-- 元素个数
select count(*) from products;

分组查询

分组查询：作用将查询结果按照某列数据来进行分组呈现

1
2
-- 语法格式
select 列名 from 表 where group by 分组字段 having 条件;

注意：

  • select 语句中的列必须出现在 group by 的子句中。
  • group by的列，不一定非要出现在select列中。
  • 聚合函数只能出现在select列，或者是having语句中，一定不能出现在where条件中。

【案例】

1
2
3
4
5
6
-- 需求
-- 统计，指定分类下商品的个数
select sid as 商品分类, count(*) as 商品个数 from products group by sid;

-- 统计，某类商品的平均价格，商品的平均价不能低于60
select sid as 商品分类 , avg(price) as 商品的平均价格 from products group by sid  having avg(price) >= 60;

分页查询

关键字: limit[offset] rows

分页查询:作用是将查询的结果按照一页一页的形式输出。

1
2
-- 语法
select 列 from 表 limit offset, lenght

【案例】

1
2
3
4
-- 分页查询，将数据分为三页，查询第一页数据
select * from products limit 0, 2;
-- 0 代表偏移量（开始行数）# 这个值是算出来的 那么么第二页开始就是 2 2
-- 2 每页显示的行数

==limit== 关键字不是SQL92标准提出的关键字，它是==MySQL独有的语法==。

通过 limit 关键字，MySQL实现了物理分页。

分页分为逻辑分页和物理分页

逻辑分页：将数据库中的数据查询到内存之后再进行分页。

物理分页：通过 LIMIT 关键字，直接再数据库中进行分页，最终返回的数据，只是分页后的数据。

【输出】

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# 第一页输出
mysql> select * from products limit 0, 2;
+-----+-----------------+-------+-------+------+
| pid | pname           | price | pdate | sid  |
+-----+-----------------+-------+-------+------+
|   1 | 泰国大榴莲      |    98 | NULL  | s001 |
|   2 | 新疆大枣        |    38 | NULL  | s002 |
+-----+-----------------+-------+-------+------+
# 第二页输出
mysql> select * from products limit 2, 2;
+-----+--------------+-------+-------+------+
| pid | pname        | price | pdate | sid  |
+-----+--------------+-------+-------+------+
|   3 | 新疆切糕     |    68 | NULL  | s001 |
|   4 | 十三香       |    10 | NULL  | s002 |
+-----+--------------+-------+-------+------+

==纸上得来终觉浅，觉知代码要躬行==

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议

本文永久链接是：https://tutouwang.vercel.app/2022/11/19/MySql/Mysql%E5%B8%B8%E7%94%A8%E6%8C%87%E4%BB%A4/

更新于：2022年11月21日

评论