mysql 数据库命令速查
一、使用 mysql
下载链接:
链接:https://pan.xunlei.com/s/VMNHMWaZ-bLa5HltrBnjRPdVA1
提取码:v8rh
1.1 登录登出 mysql
默认登录:mysql -uroot -p
,然后输入密码登录即可
查看版本信息:mysql -V
1.2 mysql 退出
有三种退出方法
sql>exit;
sql>quit;
sql>\q
1.3 mysql 登录常用参数
1.4 mysql 命令提示符修改
当我们登录进 mysql 的时候,可以看到如下信息 mysql> ....
但是如何修改提示符呢?有两种方式
- 在链接客户端的时候通过参数指定
mysql -uroot -proot --prompt 提示符
- 连接上客户端后,通过 prompt 改变
mysql> propmt 提示符
提示符的参数
接下来把提示符修改为:用户名 + @ + 主机名 + 数据库的形式
prompt '\u@\h \d>'
补充:这里显示的 none 是因为我们刚登陆,没有指定是使用哪个数据库,所以会显示 none
因此我们可以使用如下命令选择指定数据库
show databases; --这句话的意思是 展示当前系统有哪些数据库可以用
use bank; -- 使用 bank 数据库
1.6 mysql 常用命令
数据类型 | 代表 |
---|---|
mysql -uroot -proot -P3306 -h127.0.0.1 (mysql -uroot -p [常用命令]) | 数据库连接命令 (可以连接指定服务器的数据库) |
select version() | 显示当前服务器版本 |
select now() | 显示当前日期时间 |
select user() | 显示当前用户 |
show databases; | 显示当前的数据库 |
show tables [from db_name] [like ‘pattern’ | where expr] |
show columns from tb1_name | 查看表结构 |
use tb_name | 使用数据库 |
select database() | 显示当前使用的数据库 |
create database tb_name | 创建一个数据库 |
drop database tb_name | 删除数据库 |
set names gbk(utf-8) | 解决编码格式,解决现实乱码问题 |
1.7 数据库用户及权限
1.7.1 用户
- 创建用户
create user username@'host' identified by "password"
eg:
create user happy@"localhost" identified by "happy"
- 用户授权 与 收回权限
权限分为系统权限 和 数据对象权限
系统权限:
- connect
- resource
- DBA
数据对象权限
- all
- select
- update
- alter
- insert
- delete
收回权限
- revoke
grant permission on database tables to user@'host'
grant connect,resource to 用户名
grant select on 数据库 表 to 用户
# 收回权限
revoke connect,xxx from 用户名
revoke select on 表名 from 用户名
1.8 mysql8 修改为原始登录密码
mysql8密码修改
alter user 'team01'@localhost IDENTIFIED WITH mysql_native_password by '123456';
二、数据库表操作
2.1 数据类型整理
整形
浮点数:
日期和时间型
字符类型:
2.2 常见数据表操作
2.2.1 DDL 数据库定义语言
- create 数据库(表)创建语言
- alter 数据库(表)修改语言
- drop、delete 数据库(表)删除语言
创建数据表 create
基本语法:
create table [if not exists] table_name(
column_name data_type,
.....
);
-- eg 创建一个用户表
create table user(
username varchar(20),
age tinyint(3) unsigned, -- 短整型 unsigned 表示无符号,也就是默认大于 0
salary float(8,2) unsigned
);
修改数据表 alter
【1】 修改表结构
- 增加数据表的列
alter table tb_name add [column] (col_name col_data_tyoe + 约束条件)
- 删除数据表的列
alter table tb_name drop [column] col_name;
- 修改数据表
alter table tb_name modify [column] col_name col_definition[约束类型] [first] ;
-- 修改列名称
alter table tb_name change old_name new_name col_definition;
- 数据表更名
不建议随意修改数据列和数据表的名称
--一、方式一
alter table rename [to|as] new_tb_name;
-- 二、方式二
rename table tb_name to new_tb_name [, tb_name1 to tb_new_name1]
【2】 修改表约束
- 增加约束
alter table tb_name add 关键字 data_type 约束条件;
-- 给余数增加名字,以主键约束为例
alter table tb_name add [constraint] pk_users2_id primary key (id);
alter table tb_name add unique key (username); -- 简写
- 删除约束
-- 删除主键约束
alter table tb_name drop primary key;
-- 删除唯一约束
alter table tb_name drop {index | key } index_name;
-- 删除外键约束
alter table tb_name drop foreign key 外键约束名;
【3】其他实用技巧
重新设置编号初始值
alter table 表名 auto_increment=1;
删除表,数据库和记录
- 删除表 (表和数据都会删除,一定要谨慎)
drop table 表名;
drop table if exists 表名;-- 存在即删除
- 删除所有记录 (不删除表自身,只删除表中记录的方法)删除所有数据
delete from 表名;
DDL 语句会隐式提交,不能 rollback
2.2.2 DML 数据库操作语言
- 插入数据 insert
- 更新数据 update
- 删除数据 delete
插入数据
-- col_name 和 val 的数量要一一对应
insert [into] tb_name (col_name,...) values (val1,---);
-- 插入方式 一 (tb_name 里面的字段有几个,值就对应几个)
insert into tb_name(字段一,字段二,....) values (值1,值2..)
-- 简化版 (不写字段,直接加 values,这里你必须插入表中所有的字段)
insert into tb_name values(字段1,字段2,字段3); -- 这样写的话,必须包含所有的字段,不然数据库会不匹配
-- eg Student 有 name 和 age
insert into student(name,age) values("xxx",18);
insert into student values("xxx",10);
更新数据
update tb_name set 字段名 = 'xxx'; -- 修改这列的所有数据
update tb_name set 字段名 = 'xxxx' where xxx; -- 修改指定列的数据
删除数据
delete from tb_name where xxxx; -- 删除指定数据
2.2.3 DQL 数据库查询语言
查询语法:
简单查询
select * from tb_name; --查询某表的所有数据
select col_name1,col_name2 .... from tb_name; -- 查询字段指定列数据
-- 给查询字段取别名
select col_name1 as xxx from tb_name; -- 给查询的字段的列使用别名替代
字段过滤查询
-- where
select * from tb_name where id%2 = 0; -- 查询偶数列的数据
-- group by 分组查询 (分组查询一般在多表中查询使用的比较多)
-- 分组条件 having
字段排序
order by 字段名 asc升序,desc 降序
分页查询
select * from tb_name limit 2; -- 得到两条记录
select * from tb_name limit 3,2; -- 从第三条记录开始,查询两条记录
2.3 高级用法
2.3.1 表复制(结构 | 数据)
复制表结构和数据
create table tb1A select * from tb1;
create table tb1B select * from tb2;
复制表的列结构
create table 新表名 like 元素名;
复制其他表的记录
insert into 表名 select * from 元素名;
选择一列进行复制
insert into tb1_emp(name) select emp_name from tb_emp; -- 注意参数类型,长度问题
2.3.4 创建索引
当数据库中的数据的数据量过大时,查询效率就会变得非常非常的低,在这种情况下,我们可以在表上建立索引(index)
索引类型:
- 主键索引 (primary key)
- 唯一索引 (unique)
- 普通索引
- 普通组合索引
如果事先建立了索引,查询就不会对全表进行扫描,而是利用索引扫描,这样可以减少查询时间。
我们常见创建的主键就是一种索引,它会随着主键的创建而创建
-- 创建普通索引
create index 索引名 on 表名 (列名);
-- 创建普通组合索引
create index 索引名 on 表名 (列名1,列明2,列名3...);
-- 格式化显示索引
show index from 表名 \G;
-- 删除索引
drop index 索引名 from 表名;
索引的创建是影响整个数据库处理效率的重要问题,我们把这种提高效率的策略称为调优(tuning)
索引的使用原则:
- 索引的创建为 where 子句 或连接子句构成的列
- 基数小的列,索引效果较差,没必要为此创建索引
- 使用短索引,字符串列需要截取前面一部分的长度
- 不要为了使用索引而使用索引
三、mysql 内置函数使用
3.1 字符函数
-
concat 字符连接函数
-
格式化 format
-
字符小写 lower()
-
字符全部大写
-
字符串截取 left right
-
获取字符串长度
-
删除字符串中的空格 trim
删除指定字符
-
替换 replace (还可以一换多)
-
字符串截取
mysql 截取的下标从 1开始截取所有字符串
-
模糊查询 like
% 代表任意字符
_ 代表一个字符
3.2 数值运算符和函数
3.3 比较运算与函数
between xxx and xxx 在 范围内
[not] in() 一系列数值之内
is [not] null 是否为空
3.4 日期和和函数
3.5 信息函数
3.6 聚合函数
3.7 加密函数
四、mysql 自定义函数
自定义函数须有两个必要条件
- 参数
- 返回值
使用 select fi() 就可以看到结果了
如果是这样的,那就是 unicode 编码问题,我们需要改变数据库的编码方式
五、mysql 优化
5.1 优化查询的方式
- SQL 中 FROM 子句中的表名,执行顺序上最先处理的表叫基表
- 在 FROM 子句中包含多个表,选择记录条数最少的表作为基础表
- 3个以上的表连接查询,选择交叉表作为基础表
使用 exists 代替 in
exists 只有 true 或 false,而 in 会做全扫描
5.2 其他
- 建表和索引
- sql优化
- 做模块开发使用存储过程