当然是为我的第一位粉丝->怡宝 准备的一篇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
- 设置自增 auto_increment=n ,从n开始。
- 设置自增 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. 约束:
对表中的数据进行限定,保证数据的正确性,有效性,完整性
基本语法:
表创建好了: alter table 表名 modify 列名 类型 约束;
表还没创建好,对于字段的约束: 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大特征:
- 原子性(不可分割的最小操作单位)
- 持久性(事务提交或回滚,数据库持久化保存数据)
- 隔离性(事务之间相互独立)
- 一致性(事务操作前后总量不变)
隔离:多个事务之间是隔离的,相互独立的,但如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决问题
多线程读取同一数据存在问题:
- 读脏:一个事务,读取到另一个事务没有提交的数据
- 不可重复读(虚读):在同一个事务中,两次读取到的数据不一样
- 幻读:一个事务操作(DML)数据表中的所有记录,另一个书屋添加一条数据。则第一个事务查询不到自己的修改
隔离级别 1->4 安全性越高 效率越低:
- read uncommitted (读未提交) 产生1,2,3的问题
- read committed 产生2,3的问题
- repeatable read (可重复度)产生 3的问题
- serializable (串行化,把表锁上) 解决所有问题
查询数据库数据隔离级别:select @@transaction_isolation;
设置数据库隔离级别:set global transaction isolation level 隔离级别;(需重新启动mysql)
乐观锁:
它总认为不会出现问题,无论干什么都不去上锁,如果出现问题,再次更新值测试
乐观锁实现方式(底层为CAS)步骤如下:
- 取出记录时,获取当前version
- 更新时,带上这个version
- 执行更新时, set version = newVersion where version = oldVersion
- 如果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(表名)
- 唯一性索引:unique
create 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连呗
版权归原作者 The小可 所有, 如有侵权,请联系我们删除。