MYSQL的基础操作

备份一下基础命令，以便以后查阅
：P

一、数据库操作
二、表操作
三、数据操作
四、关系
五、内置函数
六、备份与恢复
七、与Python交互

一、数据库操作

• 查看所有数据库
  show databases;
• 查看当前选择的数据库
  select database();
• 删除数据库
  drop database 数据库名;
• 创建数据库
  create database 数据库名 charset=utf8;
• 切换数据库
  use 数据库名

---

二、表操作

• 查看表
  show tables;
• 创建表
  create table [表名](列及类型)；
  如下方代码:

create table student(
id int auto_increment primary key not null,
name varchar(10) not null,
gender bit default 1,
birthday datetime
);

• 查看表的结构
  desc 表名;
• 修改表
  alter table 表名 add|change|drop 列名 类型;
  如下方代码:

alter table student add birthday datetime;

• 删除表
  drop table 表名;
• 更改表名
  rename table 原表名 to 新表名;
• 查看表的创建语句
  show create table 表名;

---

三、数据操作

查询

select * from 表名;
select 列名,... from 表名 where 条件;

• 消除重复行
  select distinct 列名,... from 表名;

• 模糊查询 (like)
  %表示任意多个字符
  _表示任意一个字符
  select * from students where sname like 'huang%';

• 范围查询
  in表示在一个非连续的范围内
  select * from student where id in(1,3,5);
  between ... and ...表示在一个连续的范围内
  select * from student where id between 3 and 8;

• 空判断
  null与''是不一样的
  判空条件is null

增加

• 全列插入(对于id列，虽然传进去的值不会影响id的值，但是传入时必须添加一个占位符，例如说0)
  insert into 表名 values(与列对应的全部值);
• 缺省插入
  insert into 表名(列1，列2...) values(值1,值2....);
• 多条插入
  insert into 表名 values(...),(...)...;
insert into 表名(列1，列2) values(值1，值2),(值1，值2)...;

修改

update 表名 set 列1=值1,... where 条件

删除

• 物理删除
  delete from 表名 where 条件;
• 逻辑删除(通过设置一个flag甄别是否被抛弃，并不是真的删除)

alter table 表名 add isDelete bit default=0;
update 表名 set isDelete=1 where 条件;

聚合

• count(*)表示计算总行数,括号里写*或列名，结果是相同的
  select count(*) from students;
• max(列)
  select max(id) from student where gender=0;
• min(列)
  select min(id) from student where isdelete=0;
• sum(列)
  select sum(id) from student where gender=1;
• avg(列)
  select avg(id) from student where isdelete=0 and gender=0;

分组

• group by
  select gender, count(*) from student group by gender;

• 分组后的筛选

select 列1,..,聚合 from 表名
group by 列1,...
having 列1,...聚合

排序

order by 列1 asc|desc, 列2 asc|desc
asc升序（默认）

分页

select * from 表名 limit start, count;
从start开始获取count条数据
start从0开始

完整的select语句（顺序应该如下所示）

select distinct * 
from 表名
where 条件
group by 列名 having 条件 
order by 列名
limit start，count

---

四、关系

外键引用

创建表时
foreign key(当前表的列名) references 外表(外表主键)
创建表后
alter table 表名 add constraint 约束名 forign key(列名) reference 外表名（列名）

外键的级联操作

• restrict(限制)
  默认值，抛异常
  *cascad(级联)
  如果主键的记录被删，则关联的记录都将被删除
  *set null
  将外键设置为空
  *no action
  什么都不做

连接

• 内连接 (表A inner join 表B)
  表A与表B匹配的行才会出现在结果中
• 左连接表 (A left join 表B)
  表A与表B匹配的行会出现在结果中，外加表A独有的数据，未对应的数据使用null填充
• 右连接类似

select student.name, sum(scores.score)
from scores
inner join student on scores.stuid=student.id
where student.gender=1
group by student.name;

自关联

• 构造自关联表

create table areas(
id int primary key auto_increment not null,
title varchar(20),
pid int,
foreign key(pid) references areas(id)
);

• 自关联查询数据

select city.* from areas as city
inner join areas as province on city.pid=province.id
where province.title='山西'
从sql文件中导入数据
source areas.sql;

视图

• 对于查询结果的一个封装

create view v_1 as
select stu.*,sco.score,sub.title from scores as sco
inner join student as stu on sco.stuid=stu.id
inner join subject as sub on sco.subid=sub.id;

• 对封装好的视图进行修改

alter view v_1 as
select stu.*,sco.score,sub.title from scores as sco
inner join student as stu on sco.stuid=stu.id
inner join subject as sub on sco.subid=sub.id
where stu.isdelete=0 and sub.isdelete=0;

事务

使用事务的情况: 当数据被更改时，包括insert，update，delete

• 数据更改前
  begin;
• 数据更改后
  commit;
• 放弃修改:
  rollback;

索引

• 查看索引
  show index from 表名;
• 创建索引
  create index 索引名 on 表名(列名(列本来的长度),...);
• 删除索引
  drop index 索引名字 on 表名;

开启时间监测
set profiling=1;
查看执行时间
show profiles;
可以用来查看建立索引前后时间差距

---

五、内置函数

字符串函数

• 查看字符的ascii码值
  select ascii('a');
• 查看ascii码值对应的字符
  select char(97);
• 拼接字符串
  select concat(12,34,'ab');

数学函数

• 求绝对值
  select abs(-32);
• 求余数
  select mod(10,3);
select 10%3;
• 求不大于2.3的最大整数
  select floor(2.3);
• 求不小于2.3的最小整数
  select ceiling(2.3)

类型转换函数

cast(value as type)
convert(value,type)

---

六、备份与恢复

备份

• 1.进入mysql库目录
  cd /var/lib/mysql
• 2.运行mysqldump命令
  mysqldump -uroot -p 数据库名 > ~/Desktop/备份文件.sql

恢复

• 1.进入mysql创建一个数据库(因为备份文件不含有创建数据库操作，所以需要自己手动创建一个)
• 2.退出mysql，运行如下命令
  mysql -uroot -p 数据库名 < ~/Desktop/备份文件.sql

---

七、与Python交互

• 1.安装mysql模块
  sudo apt-get install python-mysqldb
• 2.在文件中导入模块
  import MYSQLdb
  适用于python2