【Mysql】Mysql常用指令（1）

Mysql学习笔记

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

0前言

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

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

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

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

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

1 mysql的数据类型简介

1.1 整数类型

INTEGER和INT是相同的，一般定义主键使用INT即可。
默认是有符号情况，无符号要特别说明。

1.2 浮点数和定点数类型

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

数据类型	精度	有无符号的范围
float(m,d)	单精度浮点型，(4字节)，m总个数，d小数位	10^38级别
double(m,d)	双精度浮点型，(8字节)，m总个数，d小数位	10^308级别
decimal(m,d)	m<65 是总个数，d<30 且 d<m 是小数位	$1

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

float和double精度问题：参考博客

1.3 日期与时间类型

常用data，time，datatime

1.4 字符串类型

数据类型	大小	用途
CHAR	0-255字节	定长字符串
VARCHAR	0-65535 字节	可变长字符串
TEXT	0-65 535字节	文本数据(TINYTEXT\LONGTEXT\MEDIUMTEXT)
ENUM		枚举类型（只能取一个）
SET		集合类型（可以取多个）

1.5 二进制类型

存一些图片视频，一般存在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 (
	属性名 数据类型 [完整性约束条件],
	属性名 数据类型 [完整性约束条件],
	.....
	属性名 数据类型 [完整性约束条件],
	);

表的常见约束条件：

创建图书类别表：

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 修改表结构

修改表名：

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 分组查询

一般不单独使用，常和聚合函数一起使用，一定注意使用的时候各个符号。

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（）函数

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（）函数

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

4.2.3 AVG（）函数

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

4.2.4 MAX（）函数

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

4.2.5 MIN（）函数

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 更新

表里的一些属性值发生了变化，及时更新

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%';	#错误，非字符串不能这么匹配