mysql一些常用的操作

一、安装

mysql 解压版安装

  • 1.解压（windows7）
  • 2.进入到bin目录
    >mysqld install mysql(服务名称，随便起 默认mysql)
    >mysqld --initialize(5.6的版本不用这步 ，5.7的需要这布初始化data文件夹)
  • 3
    >net start mysql启动服务
  • 4.>登陆
    >mysql -hlocalhost -uroot -p(data文件夹下有一个.err结尾的文件。里面存放着一个临时登录密码)

一个电脑中安装多个mysql
mysqld install mysql2只是这一步不一样，其他一样

my.ini文件

[mysqld]
innodb_force_recovery = 1
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\\developer\\mysql-5.6.34-winx64   # 切记此处一定要用双斜杠\\，单斜杠我这里会出错，不过看别人的教程，有的是单斜杠。自己尝试吧
# 设置mysql数据库的数据的存放目录
datadir=D:\\developer\\mysql-5.6.34-winx64\\Data   # 此处同上
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password

二、开启binlog日志文件

log-bin=log-bin\\mysql_bin.log
#binlog日志格式，mysql默认采用statement，建议使用mixed
#mysql复制主要有三种方式：
#基于SQL语句的复制(statement-based replication, SBR)，
#基于行的复制(row-based replication, RBR)，
#混合模式复制(mixed-based replication, MBR)。
#对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。
binlog_format=MIXED   
#binlog过期清理时间                     
expire_logs_days=10   
#binlog每个日志文件大小                       
max_binlog_size= 100m   
#binlog缓存大小                   
binlog_cache_size= 4m   
最大binlog缓存大小                      
max_binlog_cache_size= 512m                     
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

三、MySQL修改root密码的4种方法(以windows为例)

  • 方法1：
    用SET PASSWOR命令
    首先登录MySQL。
    格式：mysql> set password for 用户名@localhost = password('新密码');
    例子：mysql> set password for root@localhost = password('123');

  • 方法2：用mysqladmin
    格式：mysqladmin -u用户名 -p旧密码 password 新密码
    例子：mysqladmin -uroot -p123456 password 123

  • 方法3：
    用UPDATE直接编辑user表
    首先登录MySQL。
    mysql> use mysql;
    mysql> update user set password=password('123') where user='root' and host='localhost';
    mysql> flush privileges;

  • 方法4：在忘记root密码的时候，可以这样
    以windows为例：

      1. 关闭正在运行的MySQL服务。
      1. 打开DOS窗口，转到mysql\bin目录。
      1. 输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
      1. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。
      1. 输入mysql回车，如果成功，将出现MySQL提示符 >。
      1. 连接权限数据库： use mysql;
      1. 改密码：update user set password=password("123") where user="root";（别忘了最后加分号） 。
      1. 刷新权限（必须步骤）：flush privileges;
      1. 退出 quit。
      1. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

四、MySQL典型的三种引擎

查看所有的数据库引擎
show engines;
show engines \G;更友好的页面

  • MyISAM:
    不支持事务、外键。访问速度快即查询插入快 删除更新慢
    MyISAM的表存储成3个文件。文件的名字与表名相同,拓展名为frm、MYD、MYI。
    其实，frm文件存储表的结构；MYD文件存储数据，是MYData的缩写；MYI文件存储索引，是MYIndex的缩写。MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

  • INNODB:
    提供了事务，回滚以及系统崩溃修复能力和多版本并发控制的事务的安全。
    支持AUTO_INCREMENT
    支持外键（FOREIGN KEY）
    外键所在的表叫做子表，外键所依赖（REFERENCES）的表叫做父表。
    父表中被字表外键关联的字段必须为主键。
    当删除、更新父表中的某条信息时，子表也必须有相应的改变，这是数据库的参照完整性规则。
    缺点是读写效率较差，占用的数据空间相对较大。

  • MEMORY：
    它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。
    MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。
    MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。
    如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

注意，同一个数据库也可以使用多种存储引擎的表。
如果一个表要求比较高的事务处理，可以选择InnoDB。
这个数据库中可以将查询要求比较高的表选择MyISAM存储。
如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

查看当前默认引擎
show variables like "storage_engine%";

修改表的引擎
这种转化方式需要大量的时间 和I/O，mysql要执行从旧表 到新表的一行一行的复制所以效率比较低,在转化这期间源表加了读锁,从一种引擎到另一种引擎做表转化，所有属于原始引擎的专用特性都会丢失，
比如从innodb到 myisam 则 innodb的索引会丢失！
alter table tablename engine = INnodb /MyISAM/Memory

配置文件修改默认引擎
my.in

[mysqld]
default-storage-engine=INNODB

五、完整性约束关键字

  • NOT NULL
  • DEFAULT
  • UNIQUE
  • PRIMARY KEY
  • AUTO_INCREMENT
  • FOREIGN KEY

六、索引（index|key）

索引按储存类型分为BTREE索引和HASH索引
msyql中6种索引：普通索引、唯一索引、全文索引、单列索引、多列索引、空间索引。
INNODB和MYISAM支持BTREE索引，MEMORY支持HASH|BTREE索引。
msyql所支持的存储引擎对每个表至少支持16个索引，总索引长度至少256字节。

  • 建表时增加索引

create table table_name(
id int,
name varchar(10),
age tinyint unsigned
INDEX|KEY 【index_name】(字段名 【（长度）】【ASC|DESC】)
)

CREATE TABLE `db1`.`tb3`  (
  `name` varchar(10)  DEFAULT NULL,
  `gg3` char(3) DEFAULT NULL,
  `mmm` varchar(255) DEFAULT NULL,
  `id` int(10) NOT NULL DEFAULT 0,
  `hh` int(11) DEFAULT NULL,
    KEY  (name),
    INDEX ggindex(gg3),
    INDEX hhkey(hh),
  PRIMARY KEY (`id`) USING BTREE
);
  • 分析(explain)是否用到了索引
    explain select * from table_name where name='dfad' \G
  • 在已存在的表上创建普通索引
    2.create index 索引名 on 表名（字段名【（长度）】 【ASC|DESC】）
    3.alter table table_naem add index|key index_name(属性名 【（长度）】【ASC|DESC】)
    example:
    create index mmm_index on db3(mmm(10) ASC);
  • 创建唯一索引与创建普通索引基本一致就多了个UNIQUEDROP TABLE IF EXISTStb4; 
CREATE TABLE `tb4` (
`id` INT ( 10 ),
`name` VARCHAR ( 10 ),
`age` int,
`address` VARCHAR ( 11 ) DEFAULT NULL,
UNIQUE INDEX|KEY ( `id` ) USING BTREE 
) ENGINE = INNODB;

CREATE UNIQUE INDEX hh_index ON tb4(address);
ALTER TABLE tb4 ADD UNIQUE INDEX|KEY name_index(nameASC);

  • 全文索引MySQL5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
    MySQL 5.6 及以后的版本，MyISAM 和 InnoDB（创建时，非常耗时）存储引擎均支持全文索引;
    只有字段的数据类型为char、varchar、text 及其系列才可以建全文索引。
  • 创建全文索引DROP TABLE IF EXISTStb4; 
CREATE TABLE `tb4` (
`id` INT ( 10 ),
`name` VARCHAR ( 10 ),
`name2` VARCHAR ( 10 ),
`name3` VARCHAR ( 10 ),
`age` int,
`address` VARCHAR ( 11 ) DEFAULT NULL,
FULLTEXT INDEX ( `name` ) 
) ENGINE = MYISAM;

CREATE FULLTEXT INDEX name2_index ON tb4(name2);
ALTER TABLE tb4 ADD FULLTEXT INDEX name3_index(name3ASC);

  • 删除索引
    drop index index_name on table_name ;
    alter table table_name drop index index_name ;
    alter table table_name drop primary key ;

其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

七、视图（view）

视图的建立和删除不影响基本表。当时图来自一个表时，对视图的更新（添加删除和修改）直接影响基本表。当时图来自多个基本表时，不允许添加和删除数据。

  • 创建视图
    CREATE VIEW 视图名(列1，列2...) AS SELECT 列1，列2... FROM ...;
    使用视图当成表使用就好
  • 修改视图
    CREATE OR REPLACE VIEW 视图名 AS SELECT [...] FROM [...];
    alter view view_name as 查询字句；
  • 删除视图
    drop view view_nama;
  • 查看数据库已有视图
    SHOW TABLES [like...];（可以使用模糊查找）
    show table status 【from dbname】 【like 'pattern'】；
    show create view view_name;
  • 查看视图详情
    DESC 视图名或者SHOW FIELDS FROM 视图名 视图条件限制 [WITH CHECK OPTION]
  • 查看视图数据
    select * from view_name;insert into view_name (字段，字段) values(真实值，真实值);（只对单表示图有效）
    update view_name set 字段="" where 字段="";delete from view_name where 字段=“”；

八、 触发器（trigger）

  • mysql在执行insert、update、delete语句时会触发触发器。
  • 创建触发器语法
create trigger trigger_name before|after trigger_event on table_name for each row trigger_stmt;

CREATE
[DEFINER = { user | CURRENT_USER }]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_body:sql语句

example:

CREATE TRIGGER trigger_insert_stu
BEFORE INSERT
ON tb_stu FOR EACH ROW
INSERT INTO t_log(id,table_name,event,time) VALUES("","t_stu","INSERT",NOW());
  • 创建多条执行语句的触发器语法
CREATE
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW      
BEGIN
      trigger_body      
END

example:

delimiter $$
DROP TRIGGER IF EXISTS trigger_insert_stu;
CREATE TRIGGER trigger_insert_stu
BEFORE INSERT
ON tb_stu FOR EACH ROW
BEGIN
INSERT INTO tb_log(id,table_name,event,time) VALUES("","t_stu","INSERT",NOW());
INSERT INTO tb_log(id,table_name,event,time) VALUES("","t_stu2","INSERT2",NOW());
END
$$
delimiter ;
  • 查看触发器
    show triggers;
  • 切换系统表查看
    use information_schemadesc triggers;
    select * from triggers \G
  • 删除触发器
    drop trigger trigger_name;

九、存储过程（procedure)和函数(function)

CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement

  • 创建存储过程
delimiter $$
CREATE PROCEDURE proce_employee_sal()
COMMENT '查询所有雇员工资'
BEGIN
    SELECT sal FROM t_employee;
END$$
delimiter ;
  • 创建函数
delimiter $$
CREATE FUNCTION func_employee_sal(empno INT(11))
RETURNS DOUBLE(10,2)
COMMENT '查询某个雇员工资'
BEGIN
   RETURN (SELECT sal FROM t_temployee 
     WHERE t_employee.empno=empno);
END$$
delimiter ;
  • 修改存储过程

alter procedure procedure_name [characteristic ...] characteristic: | {
CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER } |comment 'string'

  • 修改函数

alter function function_name [characteristic ...] characteristic: | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER } |comment 'string'

  • 删除存储过程和函数
    drop procedure procedure_name;
    drop function function_name;

存储过程与函数有待补充

十、mysql事务

  • 1.原子性（atomicity）
    事务中所涉及的程序对数据库的修改操作要么全部成功，要么全部失败。
  • 2.一致性(consistency)
    事务执行前和执行后来源和去向保持平衡。
  • 3.隔离型(isolation)
    并发时每个事务是隔离的，相互不影响。
  • 4.持久性(durability)
    一旦事务成功提交，应该保证数据的完整存在。

show session variables like 'autocommit';
set @@autocommit = 0;
show session variables like 'autocommit';

mysql通过以下语句支持事务

  • 1、 事务开始
    begin或start transaction;
  • 2、 事务提交
    commit或commit work；
  • 3、 回滚
    rollback或rollback work；
  • 4、 保存点设置
    savepoint 标识；
  • 5、 回滚到保存点
    rollback to savepoint 标识；
  • 6、 删除保存点
    release savepoint 标识；

事务隔离级别

  • READ-UNCOMMITTED 读未提交
    所有事务都可以看到没有提交事务的数据。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据（脏读）。

  • READ-COMMITTED 读已提交（Oracle，SQL Server默认）
    事务成功提交后才可以被查询到。本事务读取到的是最新的数据（其他事务提交后的）。问题是，在同一个事务里，前后两次相同的SELECT会读到不同的结果（不重复读）。在同一个事务生命周期内，也就是这个事务还未提交之前。如果另外一个事务，对数据进行了编辑(update)或者删除(delete)操作。那么A事务就会读取到。简单理解，就是在一个事务生命周期内，多次查询数据，每次都可能查出来的不一样。

  • REPEATABLE-READ 重复读(mysql默认级别)
    同一个事务多个实例读取数据时，可能将未提交的记录查询出来，而出现幻读。即同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象。幻读的结果其实和不可重复读是一样的表现，差异就在于，不可重复读，主要是针对其他事务进行了编辑(update)和删除(delete)操作。而幻读主要是针对插入(insert)操作。也就是在一个事务生命周期内，会查询到另外一个事务新插入的数据。

  • SERIALIZABLE 序列化
    读操作会隐式获取共享锁，可以保证不同事务间的互斥。强制的进行排序，在每个读读数据行上添加共享锁。会导致大量超时现象和锁竞争。

四个级别逐渐增强，每个级别解决一个问题。

  • 脏读
    最容易理解。另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据。
  • 不重复读
    解决了脏读后，会遇到，同一个事务执行过程中，另外一个事务提交了新数据，因此本事务先后两次读到的数据结果会不一致。
  • 幻读
    解决了不重复读，保证了同一个事务里，查询的结果都是事务开始并且第一次查询时的状态（一致性）。但是，如果另一个事务同时提交了新数据，虽然本事务再次按照相同的条件查找会得到相同的结果集，但是本事务指定更新时(看了后面的演示你就会知道)，就会“惊奇的”发现了这些新数据，貌似之前读到的数据是“鬼影”一样的幻觉。

不同的隔离级别有不同的现象，并有不同的锁定/并发机制，隔离级别越高，数据库的并发性就越差，4种事务隔离级别分别表现的现象如下表：

隔离级别 脏读 不重复读 幻读
read uncommitted YES YES YES
read committed NO YES YES
repeatable read NO NO YES
serializable NO NO NO

参考MySQL InnoDB事务的隔离级别

查看隔离级别

  • 1.查看当前会话隔离级别
    select @@tx_isolation;

  • 2.查看系统当前隔离级别
    select @@global.tx_isolation;

  • 3.设置当前会话隔离级别
    set session transaction isolatin level repeatable read;

  • 4.设置系统当前隔离级别
    set global transaction isolation level repeatable read;

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE；

十一、传播行为

  • PROPAGATION_REQUIRED：如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该设置是最常用的设置。
  • PROPAGATION_SUPPORTS：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行。‘
  • PROPAGATION_MANDATORY：支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常。
  • PROPAGATION_REQUIRES_NEW：创建新事务，无论当前存不存在事务，都创建新事务。
  • PROPAGATION_NOT_SUPPORTED：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
  • PROPAGATION_NEVER：以非事务方式执行，如果当前存在事务，则抛出异常。
  • PROPAGATION_NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。

1.PROPAGATION_REQUIRED

加入当前正要执行的事务不在另外一个事务里，那么就起一个新的事务
比如说，ServiceB.methodB的事务级别定义为PROPAGATION_REQUIRED, 那么由于执行ServiceA.methodA的时候，
ServiceA.methodA已经起了事务，这时调用ServiceB.methodB，ServiceB.methodB看到自己已经运行在ServiceA.methodA
的事务内部，就不再起新的事务。而假如ServiceA.methodA运行的时候发现自己没有在事务中，他就会为自己分配一个事务。
这样，在ServiceA.methodA或者在ServiceB.methodB内的任何地方出现异常，事务都会被回滚。即使ServiceB.methodB的事务已经被
提交，但是ServiceA.methodA在接下来fail要回滚，ServiceB.methodB也要回滚

2.PROPAGATION_SUPPORTS

如果当前在事务中，即以事务的形式运行，如果当前不再一个事务中，那么就以非事务的形式运行

3.PROPAGATION_MANDATORY

必须在一个事务中运行。也就是说，他只能被一个父事务调用。否则，他就要抛出异常

4.PROPAGATION_REQUIRES_NEW

这个就比较绕口了。 比如我们设计ServiceA.methodA的事务级别为PROPAGATION_REQUIRED，ServiceB.methodB的事务级别为PROPAGATION_REQUIRES_NEW，
那么当执行到ServiceB.methodB的时候，ServiceA.methodA所在的事务就会挂起，ServiceB.methodB会起一个新的事务，等待ServiceB.methodB的事务完成以后，
他才继续执行。他与PROPAGATION_REQUIRED 的事务区别在于事务的回滚程度了。因为ServiceB.methodB是新起一个事务，那么就是存在
两个不同的事务。如果ServiceB.methodB已经提交，那么ServiceA.methodA失败回滚，ServiceB.methodB是不会回滚的。如果ServiceB.methodB失败回滚，
如果他抛出的异常被ServiceA.methodA捕获，ServiceA.methodA事务仍然可能提交。

5.PROPAGATION_NOT_SUPPORTED

当前不支持事务。比如ServiceA.methodA的事务级别是PROPAGATION_REQUIRED ，而ServiceB.methodB的事务级别是PROPAGATION_NOT_SUPPORTED ，
那么当执行到ServiceB.methodB时，ServiceA.methodA的事务挂起，而他以非事务的状态运行完，再继续ServiceA.methodA的事务。

6.PROPAGATION_NEVER

不能在事务中运行。假设ServiceA.methodA的事务级别是PROPAGATION_REQUIRED， 而ServiceB.methodB的事务级别是PROPAGATION_NEVER ，那么ServiceB.methodB就要抛出异常了。

7.PROPAGATION_NESTED

理解Nested的关键是savepoint。他与PROPAGATION_REQUIRES_NEW的区别是，PROPAGATION_REQUIRES_NEW另起一个事务，将会与他的父事务相互独立，而Nested的事务和他的父事务是相依的，他的提交是要等和他的父事务一块提交的。也就是说，如果父事务最后回滚，他也要回滚的。
而Nested事务的好处是他有一个savepoint。

来自 数据库事务4种隔离级别和7种传播行为

十二 、其它

  • 查看帮助
    help contents;

  • 查看数据类型
    help data types;

  • 查看int类型
    help int;

  • 查看某个数据库某张表的相关信息(有可能第三方客户端查看不了，黑窗口命令行可以)
    show table status from database_name where name="table_name" \G;

  • 查看表结构
    show create table table_name;

创建数据库

create database database_name;

create database db1;(创建一个名为db1的数据库)

  • 展示所有数据库
    show databases;

  • 查看数据库版本
    select version();

  • 使用某个数据库
    use db1;

  • 删除数据库

drop database database_name;

  • 查看表结构

show create table table_name;

创建表

create table table_name(
属性名 数据类型,
属性名 数据类型,
......
)

查看表结构

DESC table_name;
describe table_name;
show create table table_name;

删除表

drop table table_name;

修改表名

alter table old_table_name rename [to] new_table_name;

增加字段

alter table table_name add 属性名 属性类型；
alter table table_name add 属性名 属性类型 first；(字段位置为第一)
alter table table_name add 属性名 属性类型 after 字段名；（在某子断后增加一个字段）

删除字段

alter table table_name drop 字段名；

修改字段数据

alter table table_name modify 字段名 字段类型；

修改字段名字

alter table table_name change 旧字段名 新字段名 旧数据类型；

同时修改字段名字和数据类型

alter table table_name change 旧字段名 新字段名 新数据类型；

修改字段顺序

alter table table_name modify 字段1 数据类型 first|after 字段2；

插入数据

insert into table_name[(fiele1,field2....)]values("","",.....),("","",....);

更新数据

update table_name set field1=value1,field2=value2,.... [where condition];

删除数据

delete from table_name [where condition];

单表查询

select * from table_name;(查询所有数据)
select fiele1,field2.... from table_name;

去除重复数据（distinct）

select distinct fiele form table_name;

实现数学四则运算查询

select field1 [as] otherfield1,field2 [as] otherfield2....from table_name;

concat()合并字符串和数值

select concat(field1,field2...) [as] field from table_name;

条件数据查询

select * from table_name where conditioncondition:

  • 1.关系运算符(>,<,=,>=,<=,<>|!=)，逻辑运算符(and(&&),or(||),xor(异或)，not(!))
  • 2.带between and (在之间，是范围)关键字的条件查询
  • 3.is null is not null
  • 4.in（在之内，是集合） where name in("abc",null)(只查询name是abc的，name为null的不查询) where name not in("abc",null) 查不到数据
  • 5.like not like "-"匹配单个字符 "%"匹配任意个字符

排序查询(order by )

select field1,field2... from table_name where condition order by field1 [ASC|DESC],field2 [ASC|DESC],

分页查询

select * from table_name where condition limit [offset_start ,]row_count;
offset_start:起始偏移量，起始位置默认为0.
row_count：查询行数

统计函数

  • count():统计总记录数
  • avg():平均值
  • sum():求和
  • max():最大值
  • min():最小值

分组查询
group by having
全部查询结构

SELECT DISTINCT #去重 id,
NAME 
FROM
    tb_stu 
WHERE
    NAME = "gg" 
GROUP BY #分组 tb_stu.NAME 
HAVING
    id > 5 
ORDER BY #排序 tb_stu.NAME ASC 
    LIMIT 0,5 #分页

连接查询与子查询比较

  • 连接查询效率高，但是会导致重复数据。如果确定连接查询不会重复，那么用连接查询会比较好。
  • 子查询效率低，但是可以避免重复数据。如果要到子查询，尽量使用索引提高效率。有些情况必须避免重复数据，那就只能用子查询。
  • 但是具体问题还是要具体分析。如果主表很小，子表很大，并且有索引，是可以使用子查询的，如果主表很大，那就尽量避免子查询了。
    ;

内连接inner join on

select * from a_table a inner join b_table b on a.a_id = b.b_id;
相同
SELECT * FROM a_table a,b_table b WHERE a.a_id = b.b_id;

外连接左外连接

select * from a_table a left join b_table b on a.a_id = b.b_id;

left join是 left outer join的简写，它的全称是左外连接，是外连接中的一种。左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

右外连接

select * from a_table a right outer join b_table b on a.a_id = b.b_id;

right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

合并查询（union）(查询字段要相同)

SELECT id,name,age FROM tb_stu UNION SELECT id,name,age FROM tb_stu2;
#（去重，三个字段的直都相同，才是重复数据）
SELECT id,name,age FROM tb_stu UNION ALL SELECT id,name,age FROM tb_stu2;
#（不去重）

子查询where型子查询

查出每个栏目最新的商品(以good_id为最大为最新商品)：
goods货物表，good_id表的主键,cat_id栏目的编号

select cat_id,good_id,good_name from goods where good_id in(selct max(good_id) from goods group by cat_id);

form型子查询
查出每个栏目最新的商品(以good_id为最大为最新商品)：

select * from (select cat_id,good_id,good_name from goods order by cat_id asc, good_id desc) as tep group by cat_id;

in子查询
查询年龄为20岁的员工部门

select * from department where did in(SELECT did from employee where age=20);

exists子查询
查询是否存在年龄大于21岁的员工

select * from department where EXISTS (SELECT did from employee where age>21);

any子查询
查询满足条件的部门

select * from department where did> any (SELECT did from employee );

all子查询
查询满足条件的部门

select * from department where did> all(SELECT did from employee );

总结：

  • where型子查询：指把内部查询的结果作为外层查询的比较条件。
  • from型子查询：把内层的查询结果当成临时表，供外层sql再次查询。
  • in子查询：内层查询语句仅返回一个数据列，这个数据列的值将供外层查询语句进行比较。
  • exists子查询：把外层的查询结果，拿到内层，看内层是否成立，简单来说后面的返回true,外层（也就是前面的语句）才会执行，否则不执行。
  • any子查询：只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件。
  • all子查询：内层子查询返回的结果需同时满足所有内层查询条件。
    比较运算符子查询：子查询中可以使用的比较运算符如 “>” “<” “= ” “!=”

慢查询

慢查询相关的参数

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | OFF                              |
| slow_query_log_file       | /mysql/data/localhost-slow.log   |
+---------------------------+----------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

慢查询配置两种方式：

  1. 命令配置（mysql服务重启后失效）
# 将 slow_query_log 全局变量设置为“ON”状态
mysql> set global slow_query_log='ON'; 

#设置慢查询日志存放的位置
mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';

#查询超过1秒就记录
mysql> set global long_query_time=1;

不许重启服务，本次生效

注意
set global long_query_time=1;配置了不生效是因为配置是作用于下次回话，关掉这个回话，开启看一个新的回话即可

  1. 配置到配置文件中（重启后也可以生效）
[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

重启服务即可

profile命令的使用

1.查版本号

无论做什么都要确认版本号，不同的版本号下会有各种差异。

>Select  version();

2.执行状态分析

显示哪些线程正在运行

>show processlist;

show profile默认的是关闭的，但是会话级别可以开启这个功能，开启它可以让MySQL收集在执行语句的时候所使用的资源。

显示数据库列表

>show databases;

>SELECT @@profiling;

输出0说明profiles功能是关闭的

开启profiles功能

> set profiling=1;


需要注意为了明确看到查询性能，我们启用profiling并关闭query cache：

>SET profiling = 1;
>SET query_cache_type = 0;
>SET GLOBAL query_cache_size = 0;

执行2条SQL语句

> show profiles;

根据query_id 查看某个查询的详细时间耗费
> show profile for query 3;

ALL
显示所有性能信息

>show profile all for query 3;

ALL
显示所有性能信息
BLOCK IO
显示块IO（块的输入输出）的次数
CONTEXT SWITCHES
显示自动和被动的上下文切换数量
IPC
显示发送和接收的消息数量。
MEMORY
MySQL5.6中还未实现，只是计划实现。
SWAPS
显示swap的次数。

bye! (*_*)

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

推荐阅读更多精彩内容

  • MySQL优化
    一、MySQL优化 MySQL优化从哪些方面入手： （1）存储层（数据） 构建良好的数据结构。可以大大的提升我们S...
    宠辱不惊丶岁月静好阅读 2,415评论 1赞 8
  • mysql
    什么是数据库？ 数据库是存储数据的集合的单独的应用程序。每个数据库具有一个或多个不同的API，用于创建，访问，管理...
    chen_000阅读 4,028评论 0赞 19
  • mysql笔记(转)
    今天看到一位朋友写的mysql笔记总结，觉得写的很详细很用心，这里转载一下，供大家参考下，也希望大家能关注他原文地...
    信仰与初衷阅读 4,724评论 0赞 30
  • oracle错误集合
    ORA-00001: 违反唯一约束条件 (.) 错误说明：当在唯一索引所对应的列上键入重复值时，会触发此异常。 O...
    我想起个好名字阅读 5,169评论 0赞 9
  • 真爱你的人，怎么舍得让你受这些苦
    你说， 他爱你， 只是， 方式不一样， 而已。 小回半夜给我发短信说：我想他了，很想很想。 小回的事，已经是朋友圈...
    谜婷姑娘阅读 339评论 0赞 0