【Mysql】Mysql常用指令（1）

素颜马尾好姑娘i 2022-03-07 17:36 187阅读 0赞

# Mysql学习笔记 #

学习过程参考视频教程：[java1234网站《一头扎进mysql》][java1234_mysql]  
源码及视频网盘：[链接][Link 1]

## 0前言 ##

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

我们也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

使用关系型数据库管理系统（RDBMS）来存储和管理的大数据量。所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

*  1.数据以表格的形式出现
 *  2.每行为各种记录名称
 *  3.每列为记录名称所对应的数据域
 *  4.许多的行和列组成一张表单
 *  5.若干的表单组成database

## 1 mysql的数据类型简介 ##

### 1.1 整数类型 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R6Z19jaGF0_size_16_color_FFFFFF_t_70]  
INTEGER和INT是相同的，一般定义主键使用INT即可。  
默认是有符号情况，无符号要特别说明。

### 1.2 浮点数和定点数类型 ###

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th align="center">精度</th> 
   <th>有无符号的范围</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>float(m,d)</td> 
   <td align="center">单精度浮点型，(4字节)，m总个数，d小数位</td> 
   <td>10^38级别</td> 
  </tr> 
  <tr> 
   <td>double(m,d)</td> 
   <td align="center">双精度浮点型，(8字节)，m总个数，d小数位</td> 
   <td>10^308级别</td> 
  </tr> 
  <tr> 
   <td>decimal(m,d)</td> 
   <td align="center">m&lt;65 是总个数，d&lt;30 且 d&lt;m 是小数位</td> 
   <td>$1</td> 
  </tr> 
 </tbody> 
</table>

float32位中，有1位符号位，8位指数位，23位尾数位，实际的范围是-2128—2127，约为-3.4E38—3.4E38  
double64位中，1位符号位，11位指数位，52位尾数位，范围约是-1.7E308—1.7E308

float和double精度问题：[参考博客][Link 2]

### 1.3 日期与时间类型 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R6Z19jaGF0_size_16_color_FFFFFF_t_70 1]

常用data，time，datatime

### 1.4 字符串类型 ###

<table> 
 <thead> 
  <tr> 
   <th>数据类型</th> 
   <th align="center">大小</th> 
   <th>用途</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>CHAR</td> 
   <td align="center">0-255字节</td> 
   <td>定长字符串</td> 
  </tr> 
  <tr> 
   <td>VARCHAR</td> 
   <td align="center">0-65535 字节</td> 
   <td>可变长字符串</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td align="center">0-65 535字节</td> 
   <td>文本数据(TINYTEXT\LONGTEXT\MEDIUMTEXT)</td> 
  </tr> 
  <tr> 
   <td>ENUM</td> 
   <td align="center"></td> 
   <td>枚举类型（只能取一个）</td> 
  </tr> 
  <tr> 
   <td>SET</td> 
   <td align="center"></td> 
   <td>集合类型（可以取多个）</td> 
  </tr> 
 </tbody> 
</table>

### 1.5 二进制类型 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R6Z19jaGF0_size_16_color_FFFFFF_t_70 2]

存一些图片视频，一般存在web目录下读取快，存数据库慢。

## 2 数据库（database）的基本操作 ##

### 2.1 数据库组件 ###

Mysql Shell：自带的一个高级的mysql命令行工具。  
MySQL Workbench：为MySQL设计的ER/数据库建模工具，实现可视化管理数据库。  
Mysql Command Line Client：MySQL的DOS界面，客户端工具。  
Mysql Command Line Client-Unicode同上。  
Mysqladmin：运维和管理工具  
退出Mysql：quit

### 2.2 显示数据库 ###

显示所有数据库：`show databases;`

### 2.3 创建数据库 ###

创建数据库：`create database Name;`  
Name最好有一定规范，如db\_book1

### 2.4 删除数据库 ###

删除数据库:`drop database Name;`

## 3 数据表（Tables）的基本操作 ##

表是数据库存储数据的基本单位，一个表包含若干字段或记录。

### 3.1 创建表 ###

CREATE TABLE table_name (
    	属性名 数据类型 [完整性约束条件],
    	属性名 数据类型 [完整性约束条件],
    	.....
    	属性名 数据类型 [完整性约束条件],
    	);

表的常见约束条件：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R6Z19jaGF0_size_16_color_FFFFFF_t_70 3]

创建图书类别表：

create table t_bookType(
    id int primary key auto_increment,
    bookTypeName varchar(20),
    bookTypeDesc varchar(200)
    );

创建图书类型表：  
并将图书的类别ID，做外键关联使用。

create table t_book(
    id int primary key auto_increment,
    bookname varchar(20),
    author varchar(10),
    price decimal(6,2),
    bookTypeId int,
    constraint `fk` foreign key(`bookTypeId`) references `t_booktype`(`id`)
    );

若之前表已经存在了，则：

create table Connect(
    con DOUBLE,
    nectid INT
    );
    
    alter table Connect add constraint `fkid` foreign key(`nectid`) references `t_booktype`(`id`)

注意这里的符号是数字1左侧英文输入状态下的 \` 不是单引号 ’

### 3.2 查看表结构 ###

查看基本表结构：DESCRIBE（DESC）表名；  
详细结构：SHOW create table 表名；  
显示本数据库内的所有表：SHOW tables

show create table t_booktype;
    desc t_booktype; 
    show tables；

### 3.3 修改表结构 ###

![在这里插入图片描述][20190318230444376.PNG]

修改表名：

alter table t_book rename book111;

修改t\_book表的id字段名为book\_id，数据类型为 int

alter table t_book change id book_id int;

删除字段名

alter table t_book drop author;

增加字段名

alter table t_book add authorage varchar(10);

带约束增加

alter table t_book add testfile varchar(10) first;
    #加到最前面一列
    alter table t_book add testfile varchar(10) after author;
    #加到指定属性后面

### 3.4 删除表 ###

drop table 表名；

有依赖关系的时候先删掉子表再删主表。

## 4 表内容的相关操作 ##

先创建一个数据表：

create table t_student(
    id int primary key auto_increment,
    stuName varchar(60),
    age int,sex varchar(30),
    gradeName varchar(60)
    );

插入数据： 注意里面是使用的**单引号**

insert into db_book.t_student (id,stuName,age,gender,gradeName) values (1,'张一',18,'男','大一');
    insert into db_book.t_student (id,stuName,age,gender,gradeName) values (2,'张二',19,'男','大二');
    insert into db_book.t_student (id,stuName,age,gender,gradeName) values (3,'张三',20,'男','大三');
    insert into db_book.t_student (id,stuName,age,gender,gradeName) values (4,'张四',21,'男','大四');
    insert into db_book.t_student (id,stuName,age,gender,gradeName) values (5,'张五',22,'男','大一');
    insert into db_book.t_student (id,stuName,age,gender,gradeName) values (6,'张六',23,'男','大二');
    insert into db_book.t_student (id,stuName,age,gender,gradeName) values (7,'张七',18,'男','大三');
    insert into db_book.t_student (id,stuName,age,gender,gradeName) values (8,'张八',20,'男','大四');

或者后面多组：

insert into db_book.t_student (id,stuName,age,gender,gradeName) values (9,'张九',20,'女','大二'),(10,'张十',20,'女','大四');

给表取别名：表 别名；

select * from t_book where id=1;
    等价
    select * from t_book tb where tb.id=1;

字段取别名：属性 \[AS\] 别名

select tb.bookName [AS] tbNa from t_book tb where tb.id=1;

### 4.1 查询表内字段 ###

#### 4.1.1 查询所有字段： ####

select 字段1，字段2，字段3…from 表明；  
select \*from 表名；

select id ,stuName,age,gender,gradeName from t_student;		#顺序可调
    select * from t_student;

#### 4.1.2 查询指定字段： ####

select 指定字段1，指定字段3…from 表；

select stuName,gender from t_student;

#### 4.1.3 where条件查询 ####

select 字段1，字段2，…from 表 where 条件表达式；

select * from t_student where id=1;
    select * from t_student where age>19;

指定条件删除字段：

delete from t_student where id=4;

### 4.1.4 带in关键字查询 ###

select 字段1，字段2，字段3… from 表名 whaere 字段 \[NOT\]IN (元素1.元素2…)；

select * from t_student where age in (18,19); #只能取这两个，不是范围
    select * from t_student where age not in (18,19);

### 4.1.5 带between and的范围查询 ###

select 字段1，字段2，字段3… from 表名 whaere 字段 \[NOT\]BETWEEN 取值a AND 取值b；

select * from t_student where age between 18 and 20；		#18 19 20 都可以取，是范围
    select * from t_student where age not between 18 and 20；

### 4.1.6 带like模糊查询 ###

select 字段1，字段2，字段3… from 表名 whaere 字段 \[NOT\]LIKE ‘字符串’；  
"%"代表任意字符 '\_'代表单个字符，前后都可

select * from t_student where age like '18';
    select * from t_student where age like '1%'; #带1开头的
    select * from t_student where age like '1_'; #_只能占一个位。多个就写多个_ _

### 4.1.7 空值查询 ###

select 字段1，字段2，字段3… from 表名 whaere 字段 IS\[NOT\] NULL；

select * from t_student where age is NULL;
    select * from t_student where age is NOT NULL;

### 4.1.8 带and的多条件查询 ###

select 字段1，字段2，字段3… from 表名 whaere 条件表达式1 AND 条件表达式2 AND 条件表达式3 AND…

**注意是没有分号的 ；**

select * from t_student where age=20 and gradeName='大四'

### 4.1.9 带or的多条件查询 ###

select 字段1，字段2，字段3… from 表名 whaere 条件表达式1 OR 条件表达式2 OR 条件表达式3 OR…

或，语句末尾也是无分号；

select * from t_student where age=20 or gradeName='大四'

### 4.1.10 distinct去重复查询 ###

select distinct 字段名 from 表；

select distinct age from t_student;

### 4.1.11 对查询结果排序 ###

select 字段1，字段2，…from 表名 order by 属性名 \[ASC|DESC\]  
默认升序

select age from t_student order by age asc;

### 4.1.12 group by 分组查询 ###

![在这里插入图片描述][20190318230618668.PNG]  
一般不单独使用，常和聚合函数一起使用，一定注意使用的时候各个符号。

select age,GROUP_CONCAT(stuName) from t_student group by age;		#按age分组，看每个组有哪些stuname
    
    select age,COUNT(age) from t_student group by age;		#计数
    
    select age,COUNT(age) from t_student group by age having count(age)>1;	#对查询结果进一步筛选
    
    select age,COUNT(age) from t_student group by age with rollup;		#多加一行计算总和，文本为所有元素都包含

### 4.1.13 limit 分页查询 ###

select 字段1，字段2…from 表名 limit 初始位置，记录数；

select * from t_student LIMIT 0,5;
    select * from t_student LIMIT 5,5;

## 4.2 使用聚合函数查询（统计） ##

先创建一个相关数据表：

create table t_garde(
    int primary key auto_increment,
    stuName varchar(60),
    course varchar(50),
    score int
    );
    
    insert into t_garde (id,stuName,course,score) values (1,'张一一','语文',88),(2,'张一一','数学',89),(3,'张一一','英语',90),(4,'张三三','语文',92),(5,'张三三','数学',93),(6,'张三三','英语',94),(7,'张五五','语文',76),(8,'张五五','数学',78),(9,'张五五','英语',80);

### 4.2.1 COUNT（）函数 ###

![在这里插入图片描述][20190318230646588.PNG]

select COUNT(*) from t_grade;
    select COUNT(*) as total from t_grade;
    select studName,COUNT(*) from t_grade group by stuName;

### 4.2.2 SUM（）函数 ###

![在这里插入图片描述][20190318230715268.PNG]

select stuName,SUM(score) from t_grade where stuName='张一一'；
    select stuName,SUM(score) from t_grade group by stuName;

### 4.2.3 AVG（）函数 ###

![在这里插入图片描述][20190318230726348.PNG]

select stuName,AVG(score) from t_grade where stuName='张一一'；
    select stuName,SUM(score) from t_grade group by stuName;

### 4.2.4 MAX（）函数 ###

![在这里插入图片描述][20190318230738747.PNG]

select stuName,course,MAX(score) from t_grade where stuName='张一一';
    select stuName,MAX(score) from t_grade group by stuName;		#分组前面属性只能写一个属性

### 4.2.5 MIN（）函数 ###

![在这里插入图片描述][20190318230749316.PNG]

select stuName,course,MIN(score) from t_grade where stuName='张一一';
    select stuName,MIN(score) from t_grade group by stuName;

## 4.3 多表连接查询 ##

将两个或两个以上的表按照某个条件连接起来，从中选取需要的数据。

### 4.3.1 内连接查询 ###

可以查询两个或两个以上的表，最常用。

select * from t_book,t_bookType; 数量取两表笛卡尔乘积
    
    select * from t_book,t_bookType where t_book.bookTypeId=t_booktype.id;	#限制查询条件
    
    select bookName,author,bookTypeName from t_book,t_bookType where t_book.bookTypeId=t_booktype.id;	#限制显示字段
    
    select tb.bookName,tb.author,tby.bookTypeName from t_book tb,t_bookType tby where tb.bookTypeId=tby.id;
    #设置别名，防止多表字段名有重合

### 4.3.2 外连接查询 ###

外链接可以查询某一张表的所有信息，就是把一张表不全的部分补进来，left right决定谁合并到谁之中。

select 属性列表 from 表名1 left|right join 表名2 on 表1.属性1=表2.属性2  
left连接把表1所有属性列出来，right连接吧表2所有属性列出来。空值NULL，on限制条件。

1、左连接查询

select * from t_book left join t_booktype on  bookTypeId=t_booktype.id;	
    #显示表t_bokk所有属性

2、右连接查询

select tb.bookName,tb.author,tby.bookTypeName from t_book tb right join t_bookType tby on tb.bookTypeId=tby.id;	
    #显示表t_bookType部分属性，推荐使用别名。

### 4.3.3 多条件查询 ###

多个条件之间使用AND连接。

select tb.bookName,tb.author,tby.bookTypeName from t_book tb,t_bookType tby where tb.bookTypeId=tby.id AND price>70;

## 4.4 子查询 ##

在表2中进行查询，查询的某属性是限定在表一的条件中的。

### 4.4.1 带IN关键字的子查询 ###

select * from t_book where booktypeID [NOT]in (select id in t_booktype);	
    #选择表t_book的所有属性显示，筛选booktypeID 在表t_booktype里的

### 4.4.2 带比较运算条件的子查询 ###

select * from t_book where price >= (select id from t_pricelevel where pricelevel>1);	
    #后面筛选完成的条件要是数字，不是集合

### 4.4.3 带Exits关键字的子查询 ###

判断性质：假如子查询语句查询到记录则进行外层查询，子查询为空不执行外层查询。

select * from t_book where [NOT]EXITS (select * from t_booktype);

### 4.4.4 带ANY关键字的子查询 ###

满足任一条件即可。

select * from t_book where price >= any (select price from t_pricelevel);

### 4.4.5 带ALL关键字的子查询 ###

满足所有条件。

select * from t_book where price >= all (select price from t_pricelevel);

## 4.5 合并查询 ##

使用UNION关键字将所有查询结果合并到一起，去除相同记录。UNION ALL不去除重复记录。

select id from t_book;
    select id from t_booktype;
    select id from t_book UNION select id from t_booktype;
    select id from t_book UNION ALL select id from t_booktype;

## 4.6 插入、更新、删除数据 ##

### 4.6.1 插入 ###

所有字段插入数据：  
insert into 表名 values（值1，值2，值…）；

insert into t_book values(NULL,'围城',,'钱钟书',1)；
    #自增的属性可以设为NULL

指定字段插入数据：  
insert into 表名（属性1，属性2，属性n） values（值1，值2，值…）；

insert into t_book(id,bookName,price,author,bookTypeId) values(NULL,'围城',28,'钱钟书',1)；
    #也可以取指定的属性插入，其他默认为NULL

同时插入多条记录：  
insert into 表名\[（属性列表）\]  
values（取值列表1），（取值列表2），…（取值列表n）；

### 4.6.2 更新 ###

表里的一些属性值发生了变化，及时更新  
![在这里插入图片描述][20190318230810138.PNG]

update t_book set bookName='围城'，price=40 where id=5；	#逗号分隔
    
    update t_book set bookName='钱钟书的书' where like '%围%'；
    #使用模糊查询，将表中含有 围 字书名的书全部改为 钱钟书的书

### 4.6.3 删除 ###

delete from 表名 where \[条件\]  
条件不是主键可能会报错，需要百度修改数据库模式。

delete from t_book where id=6;	#删除一条
    
    delete from t_student where age>=20;	#符合条件多条
    delete from t_student where age='2%';	#错误，非字符串不能这么匹配

[java1234_mysql]: http://www.java1234.com/a/yuanchuang/mysql/
[Link 1]: https://pan.baidu.com/s/1hrISqlm#list/path=/
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R6Z19jaGF0_size_16_color_FFFFFF_t_70]: /images/20220307/6b57d16a97594afbadfe5b55aa80c049.png
[Link 2]: https://www.cnblogs.com/c-primer/p/5992696.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R6Z19jaGF0_size_16_color_FFFFFF_t_70 1]: /images/20220307/c2a8dd4de2f54987b4570092aba0a86e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R6Z19jaGF0_size_16_color_FFFFFF_t_70 2]: /images/20220307/aecb83e6702f417ca1d388e8984df065.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R6Z19jaGF0_size_16_color_FFFFFF_t_70 3]: /images/20220307/b1c733be8c2544e8a53a11c02c44271f.png
[20190318230444376.PNG]: /images/20220307/3eeccca081dd4589a18a4ad95741cbce.png
[20190318230618668.PNG]: /images/20220307/d561dc9cc3654016bd2d6ea2241329c6.png
[20190318230646588.PNG]: /images/20220307/d00caae8ffed4a23b9cf41e20c6009ce.png
[20190318230715268.PNG]: /images/20220307/21e72287bc5f4fc8b302f3ac58e80c86.png
[20190318230726348.PNG]: /images/20220307/a3aaa64aa68c4965a58c90ec1a8403eb.png
[20190318230738747.PNG]: /images/20220307/ab113b153af6488c8af64bd9c3508791.png
[20190318230749316.PNG]: /images/20220307/8729a239524e4885a51f6bf4e53e8a16.png
[20190318230810138.PNG]: /images/20220307/0885a4b8ea5d4eff97af48a5c99e9295.png