MySQL基本操作大全_mysql操作

目录

MySQL初识

1、连接：

2、数据库的基本操作：

3、表的基本操作（demo）

4、存储引擎：

5、sql语言的分类 

 数据类型

1、整数类型

2、浮点数类型 

3、字符串类型 

4、时间/日期类型

5、枚举类型

6、集合类型

数据表的约束 

1、空属性

​编辑 2、默认值 

3、列描述 

4、zerofill格式化输出 

5、主键

6、复合主键  

7、自增长 

8、唯一键 

9、外键：

表的操作

 插入

修改

替换：

 查询

指定行查询（where条件） 

 结果排序：

筛选分页结果：

更新表 

 删除数据

 截断表（操作慎用）

聚合函数

group by 

内置函数：

 1、日期类函数​编辑

 2、字符串函数

 3、数学函数

 复合查询：

多表查询

自连接

子查询 （嵌套查询）

合并查询 

 内外连接

内连接  

外连接

---

MySQL初识

1、连接：

①启动mysql服务端：

• 启动：systemctl start mysqld
• 关闭：systemctl stop mysqld
• 重启：systemctl restart mysqld
• 自启动：systemctl enable mysqld
• 开机不启动：systemctl disable mysqld

②连接mysql服务端

mysql -h [服务端所在IP地址] -P [服务端侦听端口] -u [用户名] -p[密码]

一般情况下，不输入-h，默认连接本地（就是执行mysql客户端命令所在的机器），不输入-P，默认端口为3306 

注意：这里的root并不是linux操作系统的root用户，而是mysql服务端默认创建的用户，名字叫做root。 

2、数据库的基本操作：

1、查看当中服务端管理多少个数据库：

show databases;

2、查看如何创建某个数据库

show create database [数据库名字]； 

3、 修改数据库属性：

alter database [数据库名称] [属性] = [新属性];

4、删除数据库：

drop database [数据库名称];

5、查看当前数据库连接状态：

show processlist; 

6、数据库的备份：

mysqldump -P3306 -u root -p[密码] -B 数据库名 > 数据库备份的存储文件路径 

还原：

source 数据库备份存储的文件路径

7、数据库表的备份

mysqldump -u root -p 数据库名 表名1 表名 2 > D:/mytest.sql

还原：

如果备份一个数据库时，没有带上-B参数，在恢复数据库的时候，需要先创建空数据库，然后使用数据库，再使用source还原。 

3、表的基本操作（demo）

创建数据库（相当于创建文件夹）：create database test;

使用数据库(相当于切换到文件夹下)：use test；

创建数据库表(相当于创建文件):create table student(id int, name varchar(32));

表中插入数据（相当于再文件中写内容）：

insert into student (id, name, gender) vlaues (1, '张三')；

查询：select * from student；

删除表：drop table [表名称]

4、存储引擎：

存储引擎：数据库管理系统如何存储数据，如何为存储的数据建立索引和如何更新 、查询数据等技术的实现方法。

5、sql语言的分类 

DDL：数据定义语句，用来维护存储数据的结构，如create，drop，alter

DML：数据操纵语句，用来对数据进行操作，如：insert,delete,update；DML中又单独分了一个DQL，数据查询语言，代表指令：delect。

DCL：数据控制语句，主要负责权限管理和事务，如：grant，revoke，commit

 数据类型

1、整数类型

2、浮点数类型 

float(M, D)：占用4个字节，M表示显示位数，D表示小数位数，精度6-7位。

double(M, D)：8个字节，精度16-16位

decimal(M, D)：M最大为65，D最大为30

3、字符串类型 

char(size)：固定长度字符串，size是可存储的长度，单位为字符，最大长度值为255

varchar(size)：可变长度字符串，size表示字符长度，最大长度为65535字节

当数据为char（size）类型时，不管插入值得长度为多少,实际所占空间都是size个长度，空间浪费，效率高。

当数据为varchar(size)时，所占空间为实际长度加1，空间节省，效率低。

4、时间/日期类型

 data：日期    yyyy-mm-dd，占用三字节

datatime：时间日期格式  yyyy-mm-dd  HH:ii:ss表示范围从1000到9999，占用八字节

timestamp：时间戳，从1970年开始的yyyy-mm-dd HH:ii:ss格式和datetime完全一致，占用四个字节

5、枚举类型

 enum(‘选项1’，‘选项2’，‘选项3’，...)

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值。

6、集合类型

set（‘选项值1’，‘选项值2’，‘选项值3’，....）

该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值。

集合查询使用find_in_set函数：

find_in_set(sub, str_list)：如果sub在str_list中，则返回下标；如果不在，返回0；str_list用逗号分割的字符串。

数据表的约束 

表的约束，指的是除了列字段类型约束之外的额外约束。

1、空属性

null：可以为空

not null：不可以为空

 

 2、默认值 

default  [val];

当插入数据的时候，不插入设置又默认值的列时，采用默认值。

3、列描述 

comment：列描述，对于插入/删除/查询没有影响，相当于是列注释。

4、zerofill格式化输出 

设置了zerofill的字段会按照设定的宽度进行输出，如果本身宽度不够，高位补零。但是在真实存储的时候，还是按照原生的数字进行存储的。

5、主键

primary key用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。

创建主键：

1. 创建表时指定：create table [表名称](字段1 字段1类型 primary key, ....)
2. alter：alter table[名称] add primary key(列名称)

 

删除主键：

6、复合主键  

当表中的字段的值有重复的可能，但是还想要建立主键，可以使用多个列建立复合主键；符合主键的列的值，在表当中是唯一的。

假设id和name组合起来的值没有重复，则可以使用id,name作为复合主键。

create table [表名称]（字段1 类型，字段2 类型，....， primary key(字段1，字段2)）；

7、自增长 

auto_increment：当对应的字段不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值。

1. 任何一个字段要做自增长，前提本身是一个索引（key一栏有值）
2. 自增长字段必须是整数
3. 一张表最多只能有一个自增长

8、唯一键 

一张表中往往有很多字段需要唯一性，数据不能重复，但是一张表只能有一个主键：唯一键就可以解决表中有多个字段需要唯一性约束的问题。

唯一键：unique，不能重复，可以为空。

9、外键：

 外键用于定义主表和从表之间的关系：外键约束主要定义在从表上，主表则必须是有主键约束或unique约束。当定义外键后，要求外键数据必须在主键的主键列存在或者为null。

从表定义的外键，本质上是通过主表的字段值，来约束从表当中的外键列。

foreign key (字段名) references 主表（列）

表的操作

 插入

单行全列插入：

insert into [表名] values(value1, value2, value3, ....) ;

单行指定列插入：

insert into [表名](colname1, colname2, ...) values(value1, value2, ....);

多行全列插入：

insert into [表名] values(value1, value2, value3, ...)，(value1, value2, value3, ....)

修改

①添加新字段：

②修改gender，将其变为char(20)

 ③删除gender列

注意：删除字段后，对应的列的数据都没了。

④修改表名为t2(to可以省略)

⑤将name列修改为xingming

替换：

主键或者唯一键没有冲突，则直接插入；

主键或者唯一键有冲突，则删除后再插入；

 查询

 ①全列查询

②指定列查询

③查询字段为表达式

④为查询结果指定别名

⑤结果去重

指定行查询（where条件） 

逻辑运算符：

 ①语文成绩在[80, 90]

②数学成绩是58、59、98或0=99的同学的数学成绩

 ③姓孙和孙某同学

 结果排序：

asc为升序

desc为降序

默认为asc

select ... from table_name [where ...] order by column [asc|desc], [...]

数学成绩升序排序：

筛选分页结果：

 从s开始，筛选n条结果：

select ... from table_name [where...] [order by...] limit s,n;

select ... from table_name [where...] [order by...] limit n offset s;

更新表 

把孙悟空的数学成绩变为88：

将总成绩倒数前三的同学的数学成绩加上30：

 删除数据

delete from table_name [where...] [order by ...] [limit ...]

 

 截断表（操作慎用）

truncate [table] table_name

只能对整表操作，不能像delete一样针对部分数据操作。

实际上mysql不对数据操作，所以比delete更快，但是truncate在删除数据的时候，并不是真正的事务，所以无法回滚。

会重置auto_increment项

聚合函数

数学总分：

平均总分：

group by 

在select中使用group by子句可以对指定列进行分组查询：

select column1, column2, ... from table group by column;

内置函数：

 1、日期类函数

 

 2、字符串函数

 3、数学函数

 复合查询：

多表查询

自连接

自连接是指在同一张表当中查询。

 

子查询 （嵌套查询）

 子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

①单行子查询

显示smith同一部门的员工：

②多行子查询

• in关键字：查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的。

• all关键字：显示工资比部门30的所有员工的工资高的员工姓名、工资和部门号。 

• any关键字：显示工资比部门30的任意员工的工资高的员工姓名、工资和部门号（包含自己部门的员工） 

③多列子查询

• 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人。 

 ④在from子句中使用子查询

显示每个高于自己部门平均工资员工的姓名、部门、工资、平均工资。

 

获取各个部门的平均工资，将其看成临时表。

合并查询 

① union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行：

例：将工资大于2500或职位是manager的人找出来：

②union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

例：将工资大于25000或职位是manager的人找出来

 内外连接

内连接  

内连接实际上就是利用where子句对两种表形成的笛卡尔积进行筛选（前面的都是内连接）

语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件

 例：显示smith的名字和部门名称

以前：select ename,dname from EMP, DEPT where EMP.deptno=DEPT.deptno and ename = 'smith';

标准：select ename,dname from emp inner join dept on emp.deptno = dept.deptno and ename = 'smith'; 

外连接

①左外连接

如果联合查询，左侧的表完全显示我们就说是左外连接。

语法：

select 字段名 from 表名1 left join 表2 on 连接条件 

例：查询所有学生的成绩，如果这个学生没有成绩，也将学生的个人信息显示出来 

当左表和右表没有匹配时，也会显示左边表的数据 ：

 ②右外连接：

如果联合查询，右侧的表完全显示我们就说是右外连接

语法：

select 字段 from 表名1 right join 表名2 on 连接条件；

例：对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应。