MySQL入门基础操作总结

MySQL入门基础操作总结

这些都是本人在CSDN上的视频教程中从零开始学习MySQL过程中整理的一些基本的操作，在此整理出来一个作用是给自己做个备忘录，便于以后工作中进行查找，再一个是将此分享给大家，期望能够给您带来那么一点点的帮助，水平有限，文中有不足之处请大家多多指教，期待与大家共同学习，共同进步

MySQL下载地址

打开链接后如图所示：
我下载的是第二个482.9M的这个，不过MySQL更新很快，有可能你登录这个界面的时候已经不是这个版本的数据库了。。。
数据库安装的教程往上一搜一大堆，此处不做详细介绍，直接进入正题：

一般情况下，mysql会默认提供多种存储引擎,你可以通过下面的查看:
看你的mysql现在已提供什么存储引擎:
mysql> show engines;
看你的mysql当前默认的存储引擎:

mysql> show variables like '%storage_engine%';

目前mysql的默认引擎是：InnoDB
你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):

mysql> show create table 表名;

上述命令是安装好mysql后打开如下所示窗口进行查看：

mysql数据引擎更换方式
1、更改数据库引擎
1.1、更改方式1：修改配置文件my.ini
将my-small.ini另存为my.ini，在[mysqld]后面添加default-storage-engine=InnoDB，重启服务，数据库默认的引擎修改为InnoDB
1.2、更改方式2:在建表的时候指定
建表时指定：

create table mytbl( id int primary key, name varchar(50) )type=MyISAM;

2.3、更改方式3：建表后更改

alter table mytbl2 type = InnoDB;

2、查看修改结果
方式1：

show table status from mytest;

方式2：

show create table table_name

InnoDB原理
1.InnoDB支持事务，他的AUTO_COMMIT默认是打开的。意味着每条SQL语句都会被默认封装成一个事务，自动提交。由于事务的处理会影响速度，所以最好是把多条SQL语句组成一个事务一起提交，减小数据库多次提交导致的开销，以此提高性能
2.支持行级锁，大幅度提高了多用户并发操作的功能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE还是会锁全表
3.支持外键，如果没有设定主键或者非空唯一索引，会自动生成一个6字节的主键（用户不可见）
MyISAM原理
1.MyISAM强调性能，每次查询具有原子性，不提供事务支持
2.只支持表级锁，select,update,delete,insert语句都会给表自动加锁。MyISAM在同一个表上的读锁和写锁是互斥的，如果既有读请求又有写请求，即使读请求先到，写请求优先级也会更高。
3.所以MyISAM不适合于有大量查询和修改并存的情况，那样查询进程会长时间阻塞。因为MyISAM是锁表，在某项读操作比较耗时会使其他写进程饿死。
MyISAM场景
使用MyISAM最常见的就是报表系统；
1.基于Hadoop生态，用HDFS来存储数据，然后基于Hive来进行数仓建模，每次Hive跑出来的数据都用Sqoop导出到MySql中去。
2.基于MySQL,搭建J2EE的在线报表查询Web系统，每个报表开发一套代码，写对应的查询SQL，组织数据，按照前端要求的格式返回数据，展现出来一个报表
3.这种报表系统，是最适合MySQL的MyISAM存储引擎的，因为并不需要事务支持，就是在最初的时候一次性批量导入大量数据，然后基于这些数据开始查询。
mysql常用引擎
MySQL开源，组件都是不同公司基于插件形式开发的。不同的引擎提供不同的功能。
InnoDB支持事务和强一致性。用于银行、交易，对数据准确性要求非常高的系统。
MyIASM不支持事务和强一致性。主要用在大数据、数据仓库领域，对数据的准确性要求不高。

MySQL查询所有用户

select user from mysql.user;

创建用户

Create  user douban_user identified by '123456';

修改用户密码
–@是连接符，%代表Host
– mysql.user表中的Host
–%是不安全的,代表所有的客户端都可以使用这个用户连接mysql 数据库服务器。

alter user 'douban_user'@'%' identified by '1234567'

重命名用户

rename user douban_user to douban_new;

删除用户
–drop user 用户名称

drop user douban_new;

赋权用户
对新创建的用户赋权
–all privileges 所有的权利
–on 关键字 . 所有 数据库实例名称.*
–with grant option 添加了这个后可以给其他用户赋权。 douban_new 可以执行grant

grant  all privileges on *.*  to 'douban_new'@'%' with grant option;--所有的权利(dba)

–'douban_new’没有执行grant权限。

grant  all privileges on *.*  to 'douban_new'@'%' 

–权限细致划分可以参照官网

创建数据库实例
–Create database 数据库实例名称；
–Create database 数据库实例名称 Default Character set utf8;

create database db_douban;
create database db_taobao Default Character set utf8;

显示数据库实例

show databases;

切换数据库实例
–use 数据库实例名称

use db_taobao;

删除数据库实例（从删库到跑路）

drop database db_taobao;

创建表
–bigint 64位的整型

Create table `db_douban`.`douban_tv`(
 id bigint(20)  not null  auto_increment comment '电视剧ID',
tv_name varchar(256) not null  default ''  comment '电视剧名称',
tv_director varchar(256) not null  default ''  comment '导演',
primary key( `id`)
)engine=InnoDB Default Charset=utf8mb4;

注意此处

不是单引号，是键盘上的数字1左边的那个按键，在ＴＡＢ键上面（英文模式下）
是 MySQL 的转义符，避免和 mysql 的本身的关键字冲突，只要你不在列名、表名中使用 mysql 的保留字或中文，就不需要转义。
所有的数据库都有类似的设置，不过mysql用的是`而已。通常用来说明其中的内容是数据库名、表名、字段名，不是关键字

mysql主键
primary key主键

primary key（`id`,`tv_name`）

主键可以是一个列或者多个列。组合的这些列可以唯一的描述这一行数据。
12306车次表 主键是日期+车次
2021-2-1 G1334
自增主键：数据库默认给一个自增值。

mysql常见的数据类型
int 32位整型
bigint 64位整型
date 日期类型
timestamp 时间戳类型
varchar 文本类型
decimal 金额类型【资金的余额】
double浮点型【金额类型的时候不安全】

mysql添加列
–alter table 修改表的关键字
–add column 添加列的关键字
–hot 列的名字
–int 列的类型是整型
–not null 非空
–default 默认值 100
–after 在tv_name列之后

alter table db_douban.douban_tv add column hot int not null default 100 
comment '热度'  after tv_name ;

mysql删除列
–alter table 修改表的关键字
–drop column 删除列的关键字
–hot 待删除的列名称

alter table db_douban.douban_tv drop column hot;
alter table db_douban.douban_tv drop column `hot`;

mysql修改列
–alter table 修改表的关键字
–change column 修改列的关键字
–tv_name 待修改列的名称
–tv_name varchar(300) not null default ‘’ comment ‘电视剧名称’ --新的列

alter table db_douban.douban_tv change column tv_name tv_name
 varchar(300)  not null  default ''  comment '电视剧名称';

mysql删除表(高危操作）

drop table  db_douban.douban_tv;

mysql表增删改查
–select 查询的关键字
–* 查询所有的列
–from 从那张表查询的关键字
–db_douban.douban_tv;数据库实例.表名

select * from db_douban.douban_tv;

mysql新增表记录
–insert into 写入表的SQL关键字
–()括号内写入列的名称
–values写入值的关键字

insert into db_douban.douban_tv(id,tv_name,tv_director) values(123456,'电视剧名称','未知');

mysql更新表记录
–关闭安全模式，在安全模式下mysql无法执行更新操作

SET SQL_SAFE_UPDATES=0;

全表更新

update db_douban.douban_tv set tv_director='未知'；

过滤修改

update db_douban.douban_tv set tv_director='西蒙兰顿'  where id=123456；

mysql过滤删除表记录

delete from db_douban.douban_tv where id=123456;

mysql删除全表记录
–这个操作很危险，这个操作可以恢复。
–如果表的记录非常多（百万条）删除非常慢，因为有mysql回滚日志。

delete from db_douban.douban_tv;

mysql永久删除表记录
–优点是删除非常的快。数据仓库+大数据
–永久删除的原理：只是做了一个指针的重置。

truncate table db_douban.douban_tv;

mysql虚拟列查询
–t是表的别名
–1就是虚拟列
–as 是列名的别名

select 1 as `number`, t.* from db_douban.douban_tv t;

mysql条件枚举查询
枚举：有限集合的元素 A={傲慢与偏见、神探夏洛克}
–条件枚举查询使用的关键字是in

select * from db_douban.douban_tv where tv_name in('傲慢与偏见','神探夏洛克');
select * from db_douban.douban_tv where tv_name not in('傲慢与偏见','神探夏洛克');

条件且查询

select * from db_douban.douban_tv where tv_name not in('傲慢与偏见','神探夏洛克')
and tv_director='西蒙兰顿'

条件或查询

select * from db_douban.douban_tv where tv_name not in('傲慢与偏见','神探夏洛克')
or tv_director='西蒙兰顿'

mysql内置函数
mysql数学函数
–绝对值

select  abs(id) from db_douban.douban_tv;

–pi运算

select pi() from db_douban.douban_tv;

–对100取模

sellect mod(id,100) from db_douban.douban_tv;

还有很多需自行百度。。。

mysql字符串函数
–需要将字符串转成小写

select lower('A') ,lower(tv_name) from db_douban.douban_tv;

–需要将字符串转成大写

select upper('a'),upper(tv_name) from db_douban.douban_tv;

–多个字符串进行拼接 concat

select  concat('a','b') as c,concat(tv_name,'-',tv_director) as d from
 db_douban.douban_tv;

–对字符串进行部分截取substr

select substr('abcd',2),--bcd
           substr('abc',1,2),--ab
           substr(tv_name,3) as d
from  db_douban.douban_tv;

–返回字符串的长度

select length('abcd'), length(tv_name) as d from  db_douban.douban_tv;

–查询子字符串的位置

select instr('abcd','bc'),--2
          instr('abcd','ca'),--0,没有找到
  instr(tv_name,'傲'） as d from db_douban.douban_tv;

–字符串去空格 trim()

select trim('  abcd  ') from  db_douban.douban_tv;

还有很多需自行百度。。。

mysql日期时间函数
–系统日期时间函数

select Now(),CURRENT_DATE(),CURRENT_TIME(),CURRENT_TIMESTAMP() from db_douban.douban_tv t;

–日期时间的增减

select date_add('2021-02-12' interval 1 YEAR) FROM db_douban.douban_tv t;
select date_add('2021-02-12' interval -1 MONTH) FROM db_douban.douban_tv t;
select date_add('2021-02-12' interval -1 DAY) FROM db_douban.douban_tv t;

–比较两个日期和时间

select DATEDIFF('2021-02-12','2021-02-11') AS T1,--RESULT:1
TIMEDIFF('2021-02-12 23:59:59','2021-02-12 23:59:58') AS T2,--RESULT:00:00:01
TIMEDIFF('2021-02-12 23:59:58','2021-02-12 23:59:59') AS T3 --TESULT:-00:00:01
FROM db_douban.douban_tv t;

–将字符串转成日期或时间 --%Y-%m-%d %H:%i:%s

select str_to_date('2021-02-12','%Y-%m-%d') AS T1--返回DATE类型
from db_douban.douban_tv t;

聚合函数
–SUM、AVG、MAX、COUNT、MIN
–最后返回只有一条记录
–聚合函数就是将所有的行进行汇总处理

select  count(1),sum(hot),avg(hot),max(hot),min(hot) from db_douban.douban_tv t;

分页查询

select * from  db_douban.douban_tv t order by hot desc limit  2--第一页两条记录，默认不写0
select * from  db_douban.douban_tv t order by hot desc limit 0,2--第一页两条记录
select * from  db_douban.douban_tv t order by hot desc limit 2,2--第二页两条记录
select * from  db_douban.douban_tv t order by hot desc limit 4,2--第三页两条记录

内联查询
–inner join关键字
–on 联合查询的条件

select * from  a inner join b on t1.id=t2.id;

内联查询的数据来源
1.左表的记录和右表的记录逐一匹配
2.左表没有匹配到右表的记录，这一行废弃
3.右表没有匹配到左表的记录，这一行废弃
4.左表和右表都要匹配到

左联查询
–left join
左联查询来源分析
1.左表的记录和右表的记录逐一匹配
2.左表的数据全部保留
3.右表的数据如果匹配到左表，那么保留右表的数据
4.右表的数据如果没有匹配到左表，那么保留左表数据，右表记录为空
左联查询和内联查询的区别
左联查询的结果比内联查询多出了一部分，多出的部分是没有匹配到右表的左表记录。

右联查询
–right join 关键字

全联查询（mysql并没有全联查询的关键字）
使用左联、右联、UNION共同完成了全联查询
–其他数据库的全联查询 Full join Mysql不支持，sqlserver、oracle支持
–union 将两个结果取并集，相同的结果只出现一次
–union all 将两个结果取并集，相同的结果可以重复出现

select * from a left join b on a.id=b.id
union
select * from a right join b on a.id=b.id