MySQL的基本操作

数据库

数据库基础操作

-- 链接数据库
mysql -uroot -p

-- 查看所有库
show databases;

-- 创建数据库
create database python13 default charset=utf8;

-- 删除数据库
drop database li;

-- 打开数据库
use python13;

-- 查看当前库中的所有表
show tables;

-- 查看建表语句
show create table python13\G;

-- 创建表
creat table classinfo(
    id int(10) unsigned not null auto_increment primary key,
    name varchar(20)
)default charset=utf8;

-- 插入数据
insert into test(id,name) values(1,'小明');

-- 查询指定表中的所有数据
select * from classinfo;
-- 删除指定表中的数据
delete from classinfo;

-- 删除表
drop table classinfo;

-- 更新数据 更新classinfo中的id=1的数据name='韩梅梅'
-- 如果后面不加where条件则会修改表中所有name的数据
update classinfo set name='韩梅梅' where id=1;

-- 查看表结构
desc classinfo;

-- 返回结果
mysql> desc classinfo;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)      | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

修改表结构

格式： alter table 表名 action（更改选项）;

添加字段

-- alter table 表名 add 新字段名 字段类型和约束;
alter table classinfo add phone varchar(11) not null;
在指定字段后面添加新字段: 
-- alter table 表名 add 新字段名 字段类型和约束 after 字段名;
alter table classinfo add age int(4) after email;
在最前面添加字段:
-- alter table 表名 add 新字段名 字段类型和约束 first;
alter table classinfo add aaa varchar(10) first;
删除字段:
-- alter table 表名 drop 字段名;
alter table classinfo drop aaa;

修改字段信息: change(可以修改字段名) modify(只修改字段信息)
注意:使用change修改字段信息时要写两次字段名
-- 修改user表中age字段信息（类型）;
alter table classinfo modify age varchar(10) not null;

-- 修改user表的pwd字段改为password字段并添加了默认值;
alter table classinfo change pwd password varchar(32) not null;

索引操作

唯一索引 unique
-- 为classinfo表中的name字段添加唯一性索引，索引名为username;
alter table classinfo add  unique username(name);
普通索引 index
-- 给user表中的phone 字段添加一个普通索引 索引名叫po
alter table classinfo add index po(phone);
删除索引:
-- 删除唯一索引:
alter table user drop index username;
-- 删除普通索引
alter table user drop index po;

修改表名: alter table 旧表名 rename as 新表名
-- 修改classinfo表的表名
alter table classinfo rename as class;

更改AUTO_INCREMENT初始值:
ALTER TABLE 表名称 AUTO_INCREMENT=1

更改表类型:
ALTER TABLE 表名称 ENGINE="InnoDB"

事务

将一组操作看做一个事物,如果有一个操作失败,都是失败;都成功,才成功

表的类型

myisam

存储时会有三个文件 frm：数据的存储 MYD：数据的结构 MYI：数据索引
不支持事务

innodb

储存时有两个文件 frm：数据结构 ibd：索引和数据
支持事务

InnoDB 查询效率低于 myisam

数据存的地址：datadir = /var/lib/mysql
innodb引擎当服务重启后会重新计算自增值
myisam引擎重启服务不会重新计算自增值

数据的操作

添加数据

-- 基本添加(指定所有的字段,给对应值)
insert into class(id,username,password,age,sex,phone,height) values(null,'zhangsan',md5(123456),'男',18,'110',170);
-- 指定部分字段添加
insert into users(username,phone) values('lisi','135'); 
-- 不指定字段
insert into users values(null,'zhaoliu',null,null,'女',32,null,null);
-- 批量添加
insert into users(username,phone) values('tianqi','男'),('wangba','女'),('pijiu','男');

更新数据

update 表名 set username='houhouhou' where 条件信息;

-- 将id为4的age改为35，sex改为男值
update class set age=35,sex='男' where id=4;
-- 将id值为2和4的数据值sex改为女，phone改为138438
update class set sex='女',phone='138438' where id=2 or id=4;

删除数据

格式：delete from 表名 [where 条件]

-- 删除users表中age值为25到30的数据
delete from class where age>=25 and age<=30;

查询数据

基本查询

select * from 表名;
where条件查询:
-- 1. 查询班级为py13期的学生信息
select * from class where classname='py13';
-- 2. 查询班级为py13期的男学生信息
select * from class where classname="py13" and sex="男";
-- 3. 查询年龄在30以上的男生
select * from class where age>30 and sex="男";
-- 4. 查询年龄在20至25岁的学生信息
select * from class where age>=20 and  age<=25;
select * from class where age between 20 and 25;
-- 5. 查询年龄不在20至25岁的学生信息
select * from class where age<20 or age>25;
select * from class where age not between 20 and 25;
-- 6. 查询py13和py9期的女生信息
select * from class where classname in('py13','py9') and sex='女';
select * from class where (classname='py13' or classname='py9') and sex='女';

like 子句查询(模糊查询)

%  任意个数的任意字符
_  一个任意字符
-- 查询姓名以小结尾的数据
select * from class where name like '%小';
select * from class where name regexp '.+小$';
-- 查询姓名为两个字符的
select * from class where name like '__';
-- 查询名字当中包含小的数据
select * from class where name like '%小%'

MySQL的统计函数（聚合函数）

max() min() count() sum() avg()

-- 当前表中有多少条数据？
select count(*) from class;
-- 获取学生表中最大、最小以及平均年龄是多少？
select max(age),min(age),avg(age) from class;
-- 获取表中男生的数量
select count(sex) from class where sex="男";
-- 统计py13的平均年龄
select avg(age) from class where classname='py13';
GROUP BY 语句 分组
-- 统计每个班级的人数
select classname,count(*) from users  group by classname;
-- 统计班级信息，按性别分组，并统计每组人数；
select sex,count(*) from class group by sex;
-- 统计每个班级的，男生和女生各多少人数。
select classname,sex,count(*) from users group by classname,sex;
ORDER BY 排序 -- asc 默认升序 desc 降序
-- 按年龄进行排序 升序
select * from class order by age;
-- 降序 desc
select * from class order by age desc;
-- 如果有两个字段排序 县排序第一个字段,然后在第一个字段排序的基础上在此进行排序
-- 只写一个关键字
-- 班级升序,然后对每个班级的年龄降序
select * from class order by classname ,age desc;

limit 分页

格式:select * from 表名 limit 每一页数据的开始,每一页的数量;

-- 查找前5条数据
select * from class limit 5;
-- 以4条数据分一页，取第一页。
select * from class limit 0,4;
-- 取第二页
select * from class limit 4,4;
-- 取第三页
select * from class limit 8,4;
-- start = (p-1)*4  4

-- 统计班级为py13的男女生各多少人？
select sex,count(*) from class where classname='py13' group by sex;

-- 获取年龄最大的5位学生信息？
select * from class order by age desc limit 5;

-- 获取每个班级的平均年龄，并按平均年龄降序，
select classname,avg(age) from users group by classname order by avg(age) desc;
select classname,avg(age) avgage from users group by classname order by avgage desc;


-- 统计每个班级的人数，按人数从大到小排序，取前3条。
select classname,count(*) c from class group by classname order by c desc limit 3;

数据导出

退出mysql命令行
导出库
mysqldump -u用户名 -p 要导出的库名>./导出的文件名
导出表
mysqldump -u用户名 -p 要导出的库名 表名>./导表的文件名

导入:
导入库的时候,数据库里要有这个库
mysql -u用户名 -p 库名<备份的库文件/表文件

高级特性

多表联查

嵌套查询

将一个sql语句作为另一个sql语句的查询条件
不推荐使用，影响查询效率

-- 查询小红所在的班级
select * from student where name ='小红';
select * from class where id=10;

select * from class where id=(select * from student where name='小红');

where 关联查询

格式：select * from 表1,表2 where 表1.外键=表2.关联字段 [查询条件(分组、排序、分页)];

-- 查询所有学员对应的班级
select * from student,class where student.classid=class.id;
-- 查询学员的姓名和对应的班级信息
select student.id,student.name,class.classname from student,class where student.classid=class.id;
-- 简写(别名))
select s.classid,s.name,c.classname from sutdent s,class c where s.classid=c.id;
-- 统计每个班级的人数
select c.classname,count(s.id) from student s,class c where s.classid=c.id group by c.classname;

join 链接查询

内链接 inner join

获取两个表中字段匹配关系的记录
只查询两个表有对应匹配的信息

-- 查询新闻信息，并补齐新闻类别信息
select * from news inner join newtype on news.typeid=newtype.id;

左链接 left join

获取左表的所有记录，即使右表没有对应匹配的记录
写在left join左侧为左表，右侧为右表
右表没有对应匹配的记录用null补齐

-- 查询新闻信息，并补齐新闻类别信息
select * from news left join newtype on news.typeid=newtype.id;

右链接 right join

- 获取右表的所有记录，即使左表没有对应匹配的记录
左表没有对应匹配的记录用null补齐

select * from news right join newtype on news.typeid=newtype.id;

练习

-- 统计每个新闻类别下的新闻数量，采用where关联统计
select newtype.type,count(*) from news,newtype where news.typeid=newtype.id group by newtype.type;
-- 统计每个新闻类别下的新闻数量，采用内链接统计
select newtype.type,count(*) from news inner join newtype on news.typeid=newtype.id group by newtype.type;
-- 统计每个新闻类别下的新闻数量，采用左链接统计
select newtype.type,count(*) from news left join newtype on news.typeid=newtype.id group by newtype.type;

事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

事务的原子性：一组事务，要么成功；要么撤回。
稳定性 ：有非法数据（外键约束之类），事务撤回。
隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得,innodb_flush_log_at_trx_commit选项(即执行commit语句)，决定什么时候把事务保存到日志里。

事物的处理方法

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

开启事务
BEGIN;
执行sql...

成功执行 事务提交
commit

失败执行 事务回滚
rollback

复制表

复制表结构

create table 目标表名 like 原表名;
只复制表结构，不复制数据

create table classinfo like class;

复制表数据

insert into 目标表名 select * from 原表名;
常用于暴力添加数据

insert into classinfo select * from class;

数据库视图

类似于表，也有字段和记录
依赖于源数据，当源数据发生变化时，视图里的数据自动发生变化
简化sql查询操作，主要用于数据的展示

创建视图：
create view 视图名字 as sql查询语句;
删除视图：
drop view 视图名字;

MySQL常用内置函数

字符串处理函数

链接字符传：concart(s1,s2...sn);
返回值为字符串长度:length(str);

数值函数

abs(x):返回x的绝对值
round(x,y):返回参数x的四舍五入的有y位小数的值

日期和时间函数

now() 返回当前日期和时间,
unix_timestamp(date) 返回date时间的unix时间戳
date_fomat(date,fmt) 返回按字符串fmt格式化日期date值
datediff(expr,expr2) 返回起始时间和结束时间的间隔天数

其他常用函数

database() 返回当前数据库名
version() 返回当前服务器版本
user() 返回当前登陆用户名
inet_aton 返回当前IP地址的数字表示inet_aton("192.168.80.250");
inet_ntoa(num) 返回当前数字表示的ip inet_ntoa(3232256250);
password(str) 返回当前str的加密版本
md5(str) 返回字符串str的md5值

触发器

-- 创建一个触发器,当对users表中数据进行添加时,修改统计的数据
\d //
create trigger countuser after insert on user for each row
begin
update user_count set num = num+1;
end;
//

有一个库来存储users表中删除的数据
create trigger deluser before delete on users for each row
begin
insert into del_users values(old.id,old.username,old.password,old.email,old.age,old.sex,old.phone,old.classid,old.money);
end;

恢复数据库

bin-log日志储存地址：/var/log/mysql/mysql-bin.log
查看日志文件:show binary logs;

步骤

前期准备

必须开启bin-log日志

数据库恢复步骤

导出日志文件，并修改日志文件，删除删库操作
导入备份数据
导入日志文件

SQL优化

优化sql步骤

慢查询日志
找出执行慢的sql语句
进行具体语句分析,优化或建立索引

有关慢查询操作

-- // 查看“慢查询”的配置信息
show variables like "%slow%";
 -- // 查看“慢查询”的时间定义
show variables like "long%";

 -- //设置“慢查询”的时间定义
set long_query_time=0.2;
-- //开启慢日志
set global slow_query_log='ON';

扩展内容

-- 查看有关mysql数据库服务器的一些信息
show [session|global] status;
     session:当前连接
     global：数据库服务器启动之后
show global status;

show status like 'Com_%'  一般查看以com开头的

// 根据执行的sql语句（次数）
Com_select:查询
Com_update:修改次数
Com_insert:插入次数
Com_delete:删除的次数

// 此处是影响的行数（比如查询一次显示20行，那么行数增加20行）
InnoDB_rows_read:执行select操作的次数
InnoDB_rows_updated:执行update的次数
InnoDB_rows_inserted:执行insert操作的次数
InnoDB_rows_deleted:执行delete操作的次数

connections：连接mysql的数量
Uptime:服务器已经工作的秒数

Slow_queries:慢查询的次数


mysql> show variables like '%slow%';
+---------------------------+--------------------------------------------+
| Variable_name             | Value                                      |
+---------------------------+--------------------------------------------+
| log_slow_admin_statements | OFF                                        |
| log_slow_slave_statements | OFF                                        |
| slow_launch_time          | 2                                          |
| slow_query_log            | OFF                                        |
| slow_query_log_file       | /var/lib/mysql/yc-virtual-machine-slow.log |
+---------------------------+--------------------------------------------+

mysql> show variables like "long%";  
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

最后编辑于：2018.11.02 12:55:26

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,189评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,577评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,857评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,703评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,705评论 5赞 366
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,620评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,995评论 3赞 396
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,656评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,898评论 1赞 298
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,639评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,720评论 1赞 330
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,395评论 4赞 319
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,982评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,953评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,195评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 44,907评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,472评论 2赞 342

MySQL的基本操作

数据库

数据库基础操作

修改表结构

添加字段

索引操作

事务

表的类型

myisam

innodb

InnoDB 查询效率低于 myisam

数据的操作

添加数据

更新数据

删除数据

查询数据

基本查询

like 子句查询(模糊查询)

MySQL的统计函数（聚合函数）

limit 分页

数据导出

高级特性

多表联查

嵌套查询

where 关联查询

join 链接查询

内链接 inner join

左链接 left join

右链接 right join

练习

事务

事物的处理方法

复制表

复制表结构

复制表数据

数据库视图

MySQL常用内置函数

字符串处理函数

数值函数

日期和时间函数

其他常用函数

触发器

恢复数据库

步骤

前期准备

数据库恢复步骤

SQL优化

优化sql步骤

有关慢查询操作

扩展内容

推荐阅读更多精彩内容