前言
知识无穷无尽,学海无边无涯。知识点虽简单,但仍值得写一篇博客来梳理 MySQL 的基础知识,以便自己日后查阅,同时也能分享给大家。
Mysql操作方式
主要包括以下几种:
命令行操作
使用 Mysql 自带的命令行工具或者使用mysql容器,进行数据库管理和操作。
1、使用 docker ps
找到 mysql
容器
2、使用docker exec -it mysql /bin/bash
进入容器内部
3、使用 mysql -u root -p 123456
进行数据库连接
4、使用 show databases;
查看所有数据库
5、使用 use 表名
选择操作那个数据库
6、使用 show tables
显示指定数据库的所有表
7、使用 select * from t_emp;
查看表内所有的数据
8、使用 show columns from t_emp;
/ show tables;
显示表的相关信息
注释:
Field 列名
Type 属性类型(int表示整数)
Null 是否为空(no表示不能为空,yes表示可以为空)
Key 主键信息
Defanlt 是否是默认值
Extra
9、使用 show index from t_emp;
显示数据表的详细索引信息,包括PRIMARY KEY(主键)。
10、使用 show table status from tankaihua2022;
输出Mysql数据库管理系统的性能及统计信息。
图形化界面操作
使用图形化界面的数据库管理工具(如 MySQL Workbench、Navicat 等)进行数据库管理和操作,这种方式更直观。
增加数据库、增加数据表
创建数据库
1、最简单的创建 MySQL 数据库的语句
CREATE DATABASE 数据库名称;
例如:
CREATE DATABASE demo;
删除数据库
drop database 数据库名称;
例如:
DROP DATABASE demo;
创建数据表
CREATE TABLE 表名(
-> 列名1 INT AUTO_INCREMENT PRIMARY KEY,
-> 列名2 VARCHAR(100) NOT NULL,
-> 列名3 VARCHAR(40) NOT NULL
);
例如:
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
删除数据表
drop table 表名;
例如:
drop table users;
增加数据
插入数据(insert into)
1、给指定字段插入数据
insert into 表名 (列名1,列名2,列名3,列名4,列名5) values (数据1,数据2,数据3,数据4,数据5);
备注:如果列的类型是字符串形式。需要在数据加上 双引号
例如:
insert into users (id,name) values (001,'kaihua');
2、批量添加数据
insert into 表名 (列名1,列名2,列名3,列名4,列名5) values (数据01,数据02,数据03,数据04,数据05),(数据11,数据22,数据33,数据44,数据55);
例如:
insert into users (id,name) values (001,'kaihua1'),(002,'kaihua2'),(003,'kaihua3');
3、给全部字段添加数据
insert into 表名 values(数据01,数据02);
例如:
insert into users values (004,'kaihua4'),(002,'kaihua2'),(003,'kaihua3');
查数据
查询数据(where)
1、查询表内所有的数据
select * from 表名;
例如:
select * from users;
2、查询表中符合条件的内容
select * from 表名 where 列名=’字段‘;
例如:
select * from users where name='kaihua';
3、别名
select 旧列名 as 新列名 from 表名 where 列名=’字段‘;
例如:
select name as name1 from users where name='kaihua';
4、去重
select distinct 列名 from 表名;
例如:
select distinct name from users;
改数据
更新数据(update)
1、更新表中某个值
update 表名 set 列名='新值' where 列名='旧值';
例如:
update users set name='kaihua2' where name='kaihua1';
2、更新表中所有的数据
update 表名 set 列名='新值';
例如:
update users set name='kaihua2';
3、更新表中符合条件的字段
UPDATE 表名 set deleted='1' WHERE 列名 like 'auto-user-%';
删数据
删除数据(delete)
1、删除表中符合条件的某个数据
delete from 表名 where 列名=字段;
例如:
delete from users where name='kaihua0';
delete from users where name='kaihua0' and id=1;
2、删除表中所有数据
delete from 表名;
例如:
delete from users;
3、删除表中id大于7的
delete from 表名 where 列名>字段;
例如:
delete from users where id>7;
delete,drop,truncate 都有删除表的作用,区别在于:
1、delete 和 truncate 仅仅删除表数据,drop 连表数据和表结构一起删除。
2、delete 是 DML 语句,操作完以后如果没有不想提交事务还可以回滚,truncate 和 drop 是 DDL 语句,操作完马上生效,不能回滚。
3、执行的速度上,drop>truncate>delete
模糊查询(like)
1、获取表中以66结尾的所有字段
select * from 表名 where 列名 LIKE '%66';
例如:
select * from users where name like '%66';
2、获取表中以demo开头的所有字段
select * from 表名 where 表名 LIKE 'demo%';
例如:
select * from users where name like 'demo%';
3、获取表中包含o的所有字段
select * from 表名 where 表名 LIKE '%o%';
例如:
select * from users where name like '%o%';
排序(order by)
1、把表中某个列进行升序
select * from 表名 order by 列名 asc;
例如:
select * from users order by name asc;
2、把表中某个列进行倒序
select * from 表名 order by 列名 desc;
例如:
select * from users order by name desc;
分组(group by)
1、将数据表按名字进行分组
select 列名 from 表名 group by 列名;
例如:
select name from users group by name;
2、将数据表按名字进行分组,并统计每个人有多少条记录:
select 列名,count(*) from 表名 group by 列名;
例如:
select name,count(*) from users group by name;
分页(limit)
1、展示前2条数据
select * from 表名 limit 2;
例如:
select * from users limit 2;
2、展示第2条到第5条的数据
select * from 表名 limit 2,5;
例如:
select * from users limit 2,5;
函数
1、count 统计数量(统计name出现的次数)
例如:
select count(name) from users group by name;
select name,count(*) from users group by name;
2、max 最大值(统计id的最大值)
例如:
select max(id) from users;
3、min 最小值(统计id的最小值)
例如:
select min(id) from users;
4、avg 平均值(求id的平均值)
算法:平均值 = 总和 / 数量
select avg(id) from users where 列名=字段;
例如:
select avg(id) from users where name='kaihua9';
5、sum 求和(求id的和)
select sum(id) from users where 列名=字段;
例如:
select sum(id) from users where name='demo11';
select sum(id) from users;
运算符
逻辑运算符
1、and 是,多条件成立为真
select * from 表名 where 列名=字段 and 列名=字段1;
例如:
select * from users where name='demo22' and id='2';
2、or 或,单条件成立为真
select * from 表名 where 列名=字段 or 列名=字段1;
例如:
select * from users where name='demo22' or id='12';
3、not 非,不是
select * from 表名 where not 列名=字段1;
例如:
select * from users where not name='demo22';
比较运算符
1、> 大于
select * from 表名 where 列名>字段;
例如:
select * from users where id>6;
2、>= 大于等于
select * from 表名 where 列名>=字段;
例如:
select * from users where id>=6;
3、<= 小于等于
select * from 表名 where 列名<=字段;
例如:
select * from users where id<=6;
4、< 小于
select * from 表名 where 列名<字段;
例如:
select * from users where id<6;
5、= 等于
select * from 表名 where 列名=字段;
例如:
select * from users where id=6;
6、<> 或者!= 不等于
select * from 表名 where 列名!=字段;
例如:
select * from users where id!=6;
select * from users where id<>6;
7、in 查询第2个和第5个id对应的值
select * from 表名 where 列名 in (值,值1);
例如:
select * from users where id in (2,5);
8、is null 查询为空的列
select * from 表名 where 列名 is null or 列名='';
例如:
select * from users where name is null or name='';
9、between ... and ... 在某个范围之内(包含最小值、最大值)
select * from 表名 where 列名 between 字段 and 字段;
例如:
select * from users where id between 2 and 5;
10、is not null 不为空
select * from 表名 where 列名 is not null or 列名='';
例如:
select * from users where name is not null or name='';
11、round 取小数点,
select round(deleted) from 表名;
例如:
select round(deleted) from users;
内连接(inner join)
select 表1.列,表2.列 from 表1 inner join 表2 on 表1.列名 = 表2.列名;
内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行。
说人话就是,查询结果只包含它们匹配的行,不匹配的就不要了。
例如:
select users.id,users.name,info.sex,info.age from users inner join info on users.id = info.id;
左连接(left join)
select 表1.列,表2.列 from 表1 ieft join 表2 on 表1.列名 = 表2.列名;
左连接:两个表在连接过程中除了返回满足连接条件的行以外,还返回左表中不满足条件的行。
例如:
select users.id,users.name,info.sex,info.age from users left join info on users.id = info.id;
右连接(right join)
select 表1.列,表2.列 from 表1 ieft join 表2 on 表1.列名 = 表2.列名;
右连接:两个表在连接过程中除了返回满足连接条件的行以外,还返回右表中不满足条件的行。。
例如:
select users.id,users.name,info.sex,info.age from users right join info on users.id = info.id;
三表联查(join)
SELECT 表1.列,表2.列,表3.列 FROM 表1 JOIN 表2 ON 表1.关联字段 = 表2.关联字段 JOIN 表3 ON 表2.关联字段 = 表3.关联字段;
三表查询,可以将不同的数据关联起来,以便进行更深入的分析和处理。
例如:
select users.id,users.name,infos.sex,infos.age,dateils.sity from users JOIN infos on users.id=infos.id JOIN dateils on infos.id=dateils.id;
复杂查询数据
1、子查询是嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句中的查询语句。可以将子查询的结果作为其他查询的条件或数据源。
这条查询语句的含义是从 infos 表中选择 sex 和 age 两列数据,其中 name 列的取值为 'demo1',并且 sity 列的取值在 dateils 表中的 sity 列里面。
具体解释如下:
SELECT sex, age FROM infos: 从 infos 表中选择 sex 和 age 两列数据。
WHERE name='demo1': 对符合条件的行进行筛选,即 name 列的取值为 'demo1'。
AND sity IN (SELECT sity FROM dateils): 这是一个子查询,它会返回 dateils 表中所有的 sity 列的值,然后将这些值与 infos 表中的 sity 列进行比较。如果 infos 表中的某一行的 sity 列的取值在 dateils 表的 sity 列里面,那么这一行就符合查询条件。
例如:
select sex,age from infos where name='demo1' and name IN (select name from users);
索引
索引是一种数据库对象,用于提高对数据库表中数据的检索速度。索引类似于书籍的目录,它们存储了指向实际数据位置的指针,使得数据库系统能够更快速地定位和访问数据行。
具体来说,索引在数据库表的一个或多个列上创建,它们可以大大减少数据库系统需要扫描的数据量,从而提高查询的效率。当执行查询时,数据库系统首先查找索引以定位到符合条件的数据行,然后再获取相应的数据。没有索引的情况下,数据库系统可能需要逐行扫描整个表来查找匹配的数据,这会导致查询速度变慢,尤其在处理大量数据时。
常见的索引类型包括主键索引、唯一索引、普通索引等。主键索引用于唯一标识每一行数据,唯一索引要求索引列的值是唯一的,普通索引则没有此限制。除了单列索引外,还可以在多个列上创建复合索引,以进一步优化查询性能。
然而,需要注意的是,虽然索引可以提高查询速度,但索引也会占用额外的存储空间,并在插入、更新和删除操作时增加额外的处理成本。因此,在设计数据库时,需要权衡索引的使用,避免过度索引导致性能下降。