常用数据库操作

MySql数据库

1 linux 下启动mysql

service mysql stop #停止数据库服务器
service mysql start
service mysql restart
mysql -u root -p #启动连接数据库

2 MySQL操作

show databases; #显示所有数据库
use database_name; #选定一个数据库
show tables; #显示所有表
system clear; #清屏

3 SQL

3.1 DDL(数据库模式定义语言)

3.1.1 create

create database if not exists database_name; #如果不存在，则创建数据库

create table student
(
	id char(15) primary key COMMENT "学号",  #primary主键, COMMENT标识名称
	name varchar(15) not null COMMENT "姓名", #varchar可变长度 not null不为空
	sex  enum("man", "women") COMMENT "性别", #enum枚举类型
	age int default 18 COMMENT "年龄" #default 默认18
);  

create table result
(
	id char(15) COMMENT "学生信息",
	pid varchar(15) COMMENT "课程编号",
	grade float default 0 COMMENT "成绩", 
	foreign key(id) references student(id) #设置外键，id来自于student表里的id
);

3.1.2 show

show create database database_name;

desc table_name;
show create table table_name;

3.1.3 drop

drop database if exists database_name;

drop table table_name;

3.1.4 alter

#修改表名
alter table table_name rename table_name1;
#修改字段类型 modify
alter table table_name modify name varchar(20); #改变属性name的长度为20
#修改字段名称 change
alter table table_name change sex
	ssex enum("man","women");  #改变熟悉sex的名字为ssex并设置类型
#添加字段 add
alter table table_name
	add id_card char(18) unique; #添加属性id_card
#删除字段 drop
alter table student drop id_card; #删除属性id_card

时间类型

类型	名称	字节	日期格式	最小值	最大值
YEAR	年	1	YYYY或YY	1901	2155
TIME	时间	3	HH:MM:SS	-838:59:59	838:59:59
DATA	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATATIME	时间日期	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	时间日期	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07 UTC

3.2 DML(数据操纵语言)

3.2.1 insert

insert into table_name values("001","zhangsan","man",19); #插入数据
insert into table_name(id,name,sex) values("002","lisi","women"); #插入部分数据
#批量插入
insert into table_name values
	("003","zhaoliu","man",19),
	("004","wangwu","women",15),
	("005","qiansan","man",16);

load data local infile 'data.txt' into table table_name(id,name,sex); #选择数据文件插入指定列数据

3.2.2 delete

 delete from table_name;
 delete from table_name where id="002"; #删除id为002的数据

3.2.3 update

update table_name set sex="women" where id="003"; #更新id为003的数据性别为women

3.2.4 select

select * from table_name;
select name,age from table_name where id>"003"; #查询id大于003数据的name和age
#去重查询 distinct
select distinct age from table_name; #除去重复年龄数据
#排序查询 order by desc降序 asc升序
select distinct age from table_name order by age desc; #除去重复年龄并从大到小排列
#分组查询 group by
select id,SUM(grade) from table_name group by id; #通过id分组，并计算每个id的总grade

1. 等值查询

笛卡尔积进行匹配

select table_name1.id grade from table_name1,table_name2
where table_name1.id=table_name2.id and age<20 and grade>60 order by grade; #查询年龄小于20成绩大于60的数据显示其id和grade

2. 连接查询

#左外连接查询
select a.id,grade
from
(select id from table_name1 where age<20) a  
left join #左外连接：左表缩小后的结果集必须在结果里出现，如果右表没有数据，则用NULL代替
(select id.grade where grade >60) b
on a.id = b.id
where b.grade is not NULL; #去除多余的NULL

#内连接查询
select a.id,grade
from
(select id from table_name1 where age<20) a
inner join #内连接：对于匹配后失败的结果不报存，相当于最后的去NULL
(select id.grade where grade >60) b
on a.id = b.id;

#全外连接查询
select a.id,grade
from
(select id from table_name1 where age<20) a
full join #全外连接：左右表缩小后的结果集必须全部在结果里出现，如果左右表没有数据，则用NULL代替
(select id.grade where grade >60) b
on a.id = b.id;

3. 联合查询

select tid,name,sex,age from table_name1
union #查询表1和表2中的数据并进行拼接，union自带结果去重，不去重用union all
select id,name,sex,age from table_name2;

4. 练习

#查询平均分大于60的学生平均成绩
select id,avg(grade) score
from result
group by id
having avg(score)>60; #where是在聚合前筛选的，having和group by是组合着用的

#查询名字z开头的学生所有信息
select id, name,sex,age
from student
where name like "z%"; #近似查询用like代替等号，用%代替模糊字符

#查询班级前三名的学生的各科成绩
select a.id,pid,grade
from 
(select id from result 
group by id 
order by sum(grade) desc
limit 3) a
left join
result b
on a.id = b.id;

3.3 DCL(数据控制语言)

3.3.1 create update delete

create user 'username'@'host' identified by 'password'; # @用于连接
/*
host:主机ip   %【任意ip】 localhost【本机】 192.168.31.22【指定ip】
identified by:将密码用默认的加密方式进行加密后放入表中，不直接存放可以明码
*/

update user set host="localhost" where user="username"; #修改用户host

delete user where user="username"; #删除用户

3.3.2 grant revoke

grant privileges on dbname.tabname to username;
# privileges :权限   select【查询】,update【更新】,delete【删除】, all【所有】
# 如果想让这个用户可以授权其他用户，在后面再加上 with grant option

revoke privileges on dbname.tabname from username; #回收用户权限

show grants for username; #查询用户权限

4 MySQL小知识

4.1 存储引擎

存取方式	描述
InnoDB	支持外键，支持事务，不支持全文索引，B+锁，行锁(锁一行) 。聚集索引，没有主键，没有唯一键
MyISAM	不支持外键，不支持事务，支持全文索引，B+锁，表锁(锁 整张表)，非聚集索引（数据和索引分离设计），myd存放数据，myi存放索引，适合在线分析处理操作
MEMORY	存储在内存上，效率高，默认哈希索引，适合存储临时数据，不支持text、BOLB类型字段，会交给MyISAM存放在磁盘上
ARCHIVE	归档存储引擎，1:10比例压缩后存储，主要存储日志数据(记录执行过程中的每一个操作)，只支持insert,select

4.1 事务

ADID	描述
A:原子性	要么全部执行成功，要么执行失败
C:一致性	保证完整性约束：A有200，B有100，A转B 100，加起来还是300
I:隔离性	消除事务之间的相互影响：读未提交、读提交、可重复读、串行化
D:持久性	事务执行的结果永久保存

4.1.1 多个并发事务可能引发的问题

脏读：一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据，随后数据回滚，但另一个事务已经用了。
不可重复读：一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。
幻读：事务B前后两次读取同一个范围的数据，在事务B两次读取的过程中事务A新增了数据，导致事务B后一次读取到前一次查询没有看到的行。

4.1.2 事务隔离级别

1、读未提交
2、读提交
3、可重复读
4、串行化
从上往下，隔离强度逐渐增强，性能逐渐变差。其中，可重复读是 MySQL 的默认级别。

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化	不可能	不可能	不可能

4.1 锁机制

锁	描述
共享锁	1. 读锁，如果一个用户要去读一个数据，可以加上共享锁，这样其他用户就不能对该数据进行写操作，只有用户读完数据，释放读锁，其他用户才可以进行写操作； 2. 可以同时有多个共享锁。
更新锁	1. U锁，对一个数据上更新锁，说明过一会就要对这个数据进行写操作（更新锁升级为排他锁）； 2. U锁可以解决部分由共享锁和排他锁导致的死锁问题； 3. U锁与共享锁是兼容的，U锁和其他U锁和排他锁是不兼容的。
排他锁	1. 写锁，X锁，对数据进行修改的时候，加上写锁，此时数据不允许读和写，不允许访问； 2. 写锁和共享锁、更新锁都是不兼容的。

锁	描述	适合场景
悲观锁	1.进行修改之前先加锁 2. 行锁，表锁，读锁，写锁均为悲观锁。	在写比较多的场景下，乐观锁会因为版本不一致，不断重试更新，产生大量自旋，消耗 CPU，影响性能。这种情况下，适合悲观锁
乐观锁	1. 操作数据时不会对操作的数据进行加锁，只有到数据提交的时候才通过一种机制来验证数据是否存在冲突 2. 乐观锁通常是通过在表中增加一个版本(version)或时间戳(timestamp)来实现	适用于读多写少的场景，可以省去频繁加锁、释放锁的开销，提高吞吐量