0


MySQL基础篇-MySQL 命令大全

当然是为我的第一位粉丝->怡宝 准备的一篇MySQL咯
在这里插入图片描述
要看进阶篇的小伙伴点着哦!!!!
MySQL进阶篇 - 索引篇

1.启动MySQL,备份数据:

1.1启动MySQL服务:

  • cmd 命令行启动/关闭mysql服务:net start/stop mysql(以管理员身份运行)

1.2 登录MySQL客户端

1.2.1 登录本地MySQL服务
  • 命令行输入 :mysql -u root -ppassward:root
1.2.2 远程登录别人的MySQL服务(需要设置配置文件)
  • mysql -hip(ip地址,例本机127.0.0.1)-uroot -p(连接目标的密码)

1.3 退出MySQL客户端

  • quit/exit

1.4 备份与执行数据库文件

  • 备份sql文件:mysqldump -uroot -p密码 备份数据库的名称 > 路径(例:mysqldump -u root -p用户密码 --databases dbname > mysql.dbname )
  • 还原/执行sql文件:source sql文件路径

在这里插入图片描述

2.数据库术语及操作

  • DDL(Data Definition Language 数据定义语言)操作数据库和表
  • DML(Data Manipulation Language 数据操作语言) 对数据进行增删改查
  • DQL(Data Query Language 数据查询语言) 用来查询数据
  • DCL(Data Control Language 数据控制语言)授权,权限和安全访问

2.1.DDL 操作数据库和表

(Create,Retrieve(查询),update,Delete)操作数据库和表

2.1.1 操作数据库

  • 显示所有数据库:show databases;
  • 显示创建的数据库:show create database 数据库名称;
  • 创建一个数据库: create database 数据库名称;
  • 创建数据库前先判断是否存在:create database if not exists 数据库名称;
  • 创建数据库并设置字符编码格式:create database 数据库名 character set gbk;
  • 修改数据库的字符集:alter database 数据库名称 character set 字符集名称(utf8);
  • 删除一个数据库:drop database 数据库名;(drop database if exists 数据库名称; 先判断是否存在数据库)
  • 切换数据库:use 数据库名称;
  • 查询现在正在使用的数据库:select database();

2.1.2 操作数据表

创建表:
  • 单行注释: #…
  • 多行注释: //
createtable tablename(
    字段1 数据类型 约束1 约束2
    字段2 数据类型 约束
    …
);

如 :

createtable employee(
id intprimarykeyauto_increment,#id主键且自增
name varchar(10)notnull,
sex varchar(5)notnull,
department varchar(30)notnull)auto_increment=2;set @@ auto_increment_increment=3
  1. 设置自增 auto_increment=n ,从n开始。
  2. 设置自增 set @@ auto_increment_increment=m ,步长为m。
  • 查询某个数据库的所有表的名称: show tables;
  • 查询表结构:desc 表名;
  • 创建表:create table 表名(列名1 数据类型1 ,列名2,数据类型2…);
  • 复制表:create table 表名1 like 表名2;
  • 清空表的数据: delete from 表名;
  • 删除表:drop table if exists 表名;

2.1.3 操作数据列

  • 修改表名:alter table 表名 rename to 新的表名;
  • 显示表的字符创建信息: show create table 表名;
  • 修改表的字符集:alter table 表名 character set 字符集;
  • 添加一列:alter table 表名 add 列名 数据类型;
  • 修改列名称:alter table 表名 change 旧列名 新列名 数据类型;
  • 修改列数据类型: alter table 表名 modify 列名 数据类型;
  • 删除列:alter table 表名 drop 列名;

2.2 DDL 数据进行增删改查

DML(增删改查表中的数据)对数据进行增删改查

  • 添加数据:insert into 表名(列名1,列名2…)values{…};
  • 删除数据:delete from 表名 where 条件;
  • 删除全部记录:delete from 表名;
  • **删除表并创建一个同名的空表(效率更高)**:truncate table 表名;
  • 修改数据:update 表名 set 列名1=值1,列名2=值2 where 条件

2.3 DQL 查询数据

2.3.1 基本语法语法:

select 字段1,字段2....(字段列表)from 表一,表二....(表名列表)where 条件1,条件2...(条件列表)groupby 分组字段 
having 分组之后的条件 
orderby  按什么字段排序 
limit6 分页限定
  • 查询所有信息:select * from 表名;
  • (distinct)去除相同的数据:如 :select distinct 列名 from student;
  • **(between and)之间(包含之间)**:如 :select * from stu where age between 20 and 30;
  • (in/not in)集合之中:select * from stu where age in(18,19,20);
  • 在查询中使用列的别名:select 列名 AS 新列名 form 表名 where 查询条件;
  • (null)查询: select * from stu where id is null /is not null;
  • (like)模糊查询 select * from 表名 where 字段名 like 对应值(子串), (like,*单个任意字符 %多个任意字符)查询学好1开头的学生:select * from stu id like “1%”;
  • **(order by)排序(可叠加)**:select * from stu order by math asc/desc,english asc;(desc降序)
  • 聚合函数:1. count函数 : select count(ifnull(name,0)) from stu;(自动排除null)2. count(*)//有多少行数据3. max/min/sum/avg(最大/最下/和/平均):select max(math) from stu;4. 将字符串s中的所有字符改为大写 lower(s) /upper(s)5. 获取当前日期:curdate();6. 获取当前时间:curtime();7. 获取当前日期和时间:now();
  • (group by)分组查询(查询的字段只能是聚合函数或分组的字段)基本语法:select 字段名列表 form 表名 where 约束条件 group by分组的字段名如:select sex avg(math) from stu group by sex; 查询男女的平均分
  • limit 分页查询:select * from stu limit 3;(3条数据)
  • where和Having区别:1. where在分组之前进行判定,having在分组之后进行判定)2. where后不可以跟聚合函数,having后可以跟聚合函数
1.select sex,avg(math) from stu where math>70 group by sex;(分数>70分(分组之前))

2.select sex,avg(math),count(id) from stu where math>70 group by sex having count(id);(分数>70分 人数>2(分组之后))

2.3.2 连接查询

  • 隐式内联:select * from emp,dept where dept.id=emp.id;
  • 显式内联:select * from emp inner join dept on emp.id=dept.id;
外连接:

外连接查询中参与连接的表有主从之分,已主表的每行数据匹配从表的数据列,将符合连接条件的数据直接返回到结果集中,对不符合连接条件的列,将被填上null值再返回到结果集中。

  • 左外连接(查询的是左表的所有记录或交集),右边没有的会填充null;如 : select t1.*,t2.name from emp as t1 left join dept as t2 on t1.id=t2.id;
  • 右外连接(右外连接包含右表中所有的匹配行,右表中有的项在左表中没有对应的项将以null值填充)。如: select t1.*,t2.name from emp as t1 right join dept as t2 on t1.id=t2.id;

2.4 DCL 授权,权限和安全访问

  • 创建用户:create user ‘用户名’@‘主机名(%未任意主机)’ identified by ‘密码’;
  • 删除用户:drop user ‘用户名’@‘主机名’;
  • 修改用户密码:update user set password = password(‘新密码’) where user = ‘用户名’;set password for ‘用户名’@‘主机名’ where user=‘用户名’;
  • 查用户的权限:show Grants for ‘用户名’@‘主机名’;
  • 授予权限:grant 权限列表 to ‘用户名’@‘主机名’; 权限列表:delete,update,select on stu
  • 授予所有权限在任意数据库和表,grant all on . to ‘用户名’@‘主机名’;
  • 撤销权限:revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;

在这里插入图片描述

3. 约束:

对表中的数据进行限定,保证数据的正确性,有效性,完整性

基本语法:
  1. 表创建好了: alter table 表名 modify 列名 类型 约束;
  2. 表还没创建好,对于字段的约束: id int 约束(可以是 primary key auto_increment)
  • primary key (特殊)删除唯一约束:alter table 表名 drop primary key;
  • not null(非空约束):alter table 表名 modify 列名 类型 not null;
  • unique (特殊)删除唯一约束:alter table 表名 drop index 列名;
  • auto_increment(自动增长) 例id自动增长 id int primary key auto_increment
  • 删除自动增长 alter table stu modify id int;
  • (foreign key)创建表的时候添加外键:constraint 外键名称 foreign key(外键列名称) references 主表名称(主表列名称)1. 添加外键(表建好了):alter table 表名 add constraint 外键名称 foreign key(外键列名称) references 主表名称(主表列名称)2. 删除外键:alter table 表名 drop foreign key 列名;如:create table grade( id int(5) primary key,auto_increment name varchar(5));insert into stu(name) valuse('大一','大二');create table stu( id int(5) primary key, name varchar(5) not null, gradeid int(5), #添加 grade表的外键 constraint fk_id forrign key (grandid) references grade(id))
  • **级联(更新/删除)**:(添加外键的时候同时级联操作)alter table 表名 add constraint 外键名称 foreign key(外键列名称) references 主表名称(主表列名称)on update cascade on delete cascade;

在这里插入图片描述

4.事务

如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败

  • 开启事务:start transaction ;
  • 提交事务:commit
  • 回溯: rollback;

例:

starttransaction....(业务逻辑)
commit

若成功,则提交到数据库(commit)
失败,就回滚回去(rollback)

  • select @@autocommit; (查询看事务的默认提交方式 1自动提交 0手动提交)
  • 修改事务的默认提交方式:set @@autocommit =0;

事务4大特征:

  1. 原子性(不可分割的最小操作单位)
  2. 持久性(事务提交或回滚,数据库持久化保存数据)
  3. 隔离性(事务之间相互独立)
  4. 一致性(事务操作前后总量不变)

隔离:多个事务之间是隔离的,相互独立的,但如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决问题

多线程读取同一数据存在问题:

  1. 读脏:一个事务,读取到另一个事务没有提交的数据
  2. 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样
  3. 幻读:一个事务操作(DML)数据表中的所有记录,另一个书屋添加一条数据。则第一个事务查询不到自己的修改

隔离级别 1->4 安全性越高 效率越低:

  1. read uncommitted (读未提交) 产生1,2,3的问题
  2. read committed 产生2,3的问题
  3. repeatable read (可重复度)产生 3的问题
  4. serializable (串行化,把表锁上) 解决所有问题

查询数据库数据隔离级别:select @@transaction_isolation;
设置数据库隔离级别:set global transaction isolation level 隔离级别;(需重新启动mysql)

乐观锁:

它总认为不会出现问题,无论干什么都不去上锁,如果出现问题,再次更新值测试

乐观锁实现方式(底层为CAS)步骤如下:

  1. 取出记录时,获取当前version
  2. 更新时,带上这个version
  3. 执行更新时, set version = newVersion where version = oldVersion
  4. 如果version不对,就更新失败

悲观锁:

它总认为会出现问题,无论干什么都会上锁,再去操作

5. 索引

索引是帮助MySQL高效获取数据数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往是存储在磁盘上的文件中的(可能存储在单独的索引文件中,也可能和数据一起存储在数据文件中) 数据量大时减少查询时间效果明显

  • 优点:可以提高数据检索的效率,降低数据库的IO成本,通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。如果按照索引列的顺序进行排序,对应order by语句来说,效率就会提高很多。
  • 缺点:索引会占据磁盘空间,索引虽然会提高查询效率,但是会降低更新表的效率。比如每次对表进行增删改操作,MySQL不仅要保存数据,还有保存或者更新对应的索引文件。

索引名命名格式为 :表名 _字段名

5.1索引的操作:

  • **创建索引(普通索引,可重复)**:create index 索引名 on 表名(‘字段名’); 如create index stu_name on student(name);
  • 删除索引 :drop index stu_name;
  • 查看表的索引 : show index from table_name(表名)
  • 唯一性索引:uniquecreate unique index 索引名 on 表名(列名);create unique index ind_name on school(phone);
  • 位图(分类)索引:bitmap数据量比较大,基数比较小 比如:男/女create bitmap index 索引名 on 表名(列名);create bitmap index ind_sid on student(sid);

创作不易,看完小伙伴来波3连呗

标签: sql mysql

本文转载自: https://blog.csdn.net/The_xiaoke/article/details/124444044
版权归原作者 The小可 所有, 如有侵权,请联系我们删除。

“MySQL基础篇-MySQL 命令大全”的评论:

还没有评论