MYSQL 基础

命令分类

  • DDL（data definition language）：
    DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用
  • DML（data manipulation language）：
    它们是INSERT、DELETE、UPDATE、SELECT，就象它的名字一样，这4条命令是用来对数据库里的数据进行“增删改查”操作的语言
  • DCL（Data Control Language）：s
    是数据库控制功能。是用来设置或更改数据库用户或角色权限的语句，包括GRANT、DENY、REVOKE等语句。在默认状态下，只有sysadmin, dbcreator, db_owner或db_securityadmin等人员才有权力执行DCL
    DCL(数据控制语句) ，DDL(数据定义语句），DML（数据操作语句）也是SQL 语言具有的三大功能。

一、控制命令（用户操作）

mysql -h 主机地址 -u 用户名 -p 用户密码;

查看用户和密码

--通过表查看用户信息
SELECT User, Host FROM mysql.user;

修改密码的三种办法

--修改密码
UPDATE mysql.user SET password=PASSWORD("new password")  WHERE user="root" AND Host="localhost";
--或者
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123456');
--也可以在shell环境下，用mysqladmin的方法修改密码
mysqladmin -u 用户名 -p 旧密码 password 新密码;

创建和删除用户，同一用户不用host可以设置不同密码

create user "username"@"host" identified by "password";
drop user 'username'@'host';

授权
可以直接在创建时授权，让他可以在任何主机上登录，并对一个数据库下面所有表有查询、插入、修改、删除的权限。首先用root用户连入MYSQL，然后键入以下命令：grant 操作 on 数据库.* to 用户名@登录主机 identified by “密码”

grant all privileges on zhangsanDb.* to zhangsan@'%'
identified by 'zhangsan';
flush privileges;
--flush privileges表示刷新权限变更。
--操作可以是 select,insert,update,delete 或者 all previliges
--identified 后面接密码

show grants for 'username'@'%' ; --查看用户权限

username@host表示授予的用户以及允许该用户登录的IP地址。Host有以下几种类型：
localhost：只允许该用户在本地登录，不能远程登录。
%：允许在除本机之外的任何一台机器远程登录（这里有的版本不包括本地）。
192.168.52.32：具体的IP表示只允许该用户从特定IP登录。
192.168.0.%，表示这个用户只能在192.168.0.XIP段登录

REVOKE是收回权限

revoke delete on test.* from 'jack'@'localhost';      --这里只回收了删除权限

DDL命令

创建表
CREATE DATABASE 数据库名;

CREATE TABLE 表名 (
id bigint unsigned AUTO_INCREMENT,
updated_at datetime DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
created_at datetime  DEFAULT CURRENT_TIMESTAMP,
name char(100) NOT NULL, 
address char(100), 
age int(10),
PRIMARY KEY (id)
);

DEFAULT CURRENT_TIMESTAMP
表示当插入数据的时候，该字段默认值为当前时间
再加上ON UPDATE CURRENT_TIMESTAMP
表示每次更新这条数据的时候，该字段都会更新成当前时间，这两个操作是mysql数据库本身在维护

表查询

查询表信息

show databases;
// 进入dataBase_name数据库
use database;
// 显示dataBase_name数据库的所有表
show tables;
show columns from tablename;
desc tablename;
表修改
ALTER TABLE `table name` ADD COLUMN `column name` int(11) DEFAULT NULL;
ALTER TABLE table_name ADD INDEX index_name (column_names)

SQL查询语句

Join

7种Join的类型和对应的语句


sql-join.png

Where\Order\Distinct

SELECT * FROM Websites WHERE country='CN' AND alexa > 50;

SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;
--如果有多个字段要用逗号分开设置 ORDER BY st.sAge DESC, st.sGrade DESC;
--如果列之间排序冲突，以前面的优先排序

select distinct expression[,expression...] from tables [where conditions];
--distinct只能放到所有字段前面，不能放中间的，而且distinct对null不会过滤，结果是包含null的

TOP

--在mysql里面是通过limit实现
SELECT * FROM Persons LIMIT 5;

Like

通配符 描述
% 替代 0 个或多个字符
_ 替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist]或[!charlist] 不在字符列中的任何单一字符

例子

select ** from ** where name LIKE 'Mc%'  --将搜索以字母 Mc 开头的所有字符串（如 McBadden）
LIKE '%inger' --将搜索以字母 inger 结尾的所有字符串（如 Ringer、Stringer）
LIKE '%en%' --将搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden)
LIKE '_heryl'  --将搜索以字母 heryl 结尾的所有六个字母的名称（如 Cheryl、Sheryl）
LIKE '[CK]ars[eo]n'  --将搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）
LIKE '[M-Z]inger' --将搜索以字符串 inger 结尾、以从 M 到 Z 的任何单个字母开头的所有名称（如 Ringer）
LIKE 'M[^c]%'  --将搜索以字母 M 开头，并且第二个字母不是 c 的所有名称（如MacFeather）

但是MySQL 、SQLite 只支持 % 和 _ 通配符，不支持 [^charlist] 或 [!charlist] 通配符

Insert

当想插入数据到数据库时，这里不加列名就是默认所有列

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

通过 SQL insert，您可以从一个表复制信息到另一个表。

--方法一，插入一行,insert into select要求表scorebak 必须存在
INSERT INTO scorebak SELECT * from socre where neza='neza'   
insert into tbl_name1(col1,col2) select col3,col4 from tbl_name2;
--方法二，也是插入一行,去掉where一般用于整体备份，表scorebak可以不存在
SELECT * INTO scorebak FROM score  where neza='neza' 

--但mysql不支持第二行这种语法，我们可以这样在建表的时候整体复制结构和数据
CREATE TABLE 新表 AS SELECT * FROM 旧表 
--只想复制表结构时
CREATE table table_name_new like table_name_old

DELETE删除

DELETE FROM 表名 where id=2;

UPDATE更改

UPDATE table_name SET field1=new_value1, field2=new_value2 [WHERE Clause]
UPDATE vip SET product_id=-1 WHERE id=1491;

Having

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。也就是说再group+sum等聚合函数算出我们想要的值后，where不能对结果过滤。where和having可以一起使用：将where放在group前面，用于限制最初的数据，having放在后面用于限制合计函数后的结果

SELECT Customer,SUM(OrderPrice) as priceSum FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING priceSum > 1500
--查寻Bush和Adams订单金额是否大于1500

case完成IF功能

SELECT
    STUDENT_NAME,
    (CASE WHEN score < 60 THEN '不及格'
        WHEN score >= 60 AND score < 80 THEN '及格'
        WHEN score >= 80 THEN '优秀'
        ELSE '异常' END) AS REMARK
FROM
    TABLE
Mysql日期和时间

mysql日期的格式，操作都是按如下格式写的
DATE - 格式 YYYY-MM-DD
DATETIME - 格式: YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS

--如果字段设置的是datetime，可以这么设置
UPDATE sst_diagnose set updated_at = '2019-09-28 12:12:12' where id=8;
--查询
SELECT * FROM access_log WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

mysql里面也有几个时间函数
now(), current_timestamp() ; -- 当前日和期时间
current_date(); -- 当前日期
current_time(); -- 当前时间
date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp(); -- 获得unix时间戳
from_unixtime(); -- 从时间戳获得时间

select now(),current_date(),current_time();
这是时间减法，获得10年前的今天
select date_sub(sysdate(),interval 10 year)

DISTINCT

distinct这个关键字用来过滤掉多余的重复记录只保留一条，但往往只用它来返回不重复记录的条数，而不是用它来返回不重记录的所有值。其原因是distinct只有用二重循环查询来解决，而这样对于一个数据量非常大的站来说，无疑是会直接影响到效率的。

如果是放在多个列前面，它会同时对2个字段生效，也就是说组合是唯一的
select distinct name, id from table

且distinct必须放在开头
select id, distinct name from table除了错误信息你什么也得不到，distinct必须放在开头

如果你想得到某列的唯一值，且把其他列值也带上，可以把他放在聚合函数里面实现（最后一列不管）
select *, count(distinct name) from table group by name

Group By

group by的最常规的用法是配合聚合函数，利用分组信息进行统计，常见的是配合max、sum等聚合函数筛选数据后分析，以及配合having进行筛选。

TO DO

Select嵌套

例子：查询买了商品为’TNT2’的顾客信息
子查询中涉及3张表的订单。因此分三步，1. 在orderitems里找出TNT2的订单号；2. 在orders里找出第一步找出的订单号对应的客户id；3. 在customers中找出第二步对应客户的所有信息

SELECT * FROM customers
WHERE cust_id
IN (SELECT cust_id FROM orders WHERE order_num IN
(SELECT order_num FROM orderitems WHERE prod_id='TNT2'));

事务

BEGIN 或 START TRANSACTION；显示地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的；

索引

索引被创建于已有的表中，它可使对行的定位更快速更有效。可以在表格的一个或者多个列上创建索引，每个索引都会被起个名字。用户无法看到索引，它们只能被用来加速查询。简单说来，索引就是指向表中数据的指针。数据库的索引同书籍后面的索引非常相像。

索引能够提高 SELECT 查询和 WHERE 子句的速度，但是却降低了包含 UPDATE 语句或 INSERT 语句的数据输入过程的速度。索引的创建与删除不会对表中的数据产生影响。

从逻辑角度

MySQL数据库中，从用户逻辑角度，有五种索引种类：

  1. 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个（又叫聚集索引，由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引）
  2. 普通索引：仅加速查询
  3. 唯一索引：加速查询 + 列值唯一（可以有null）
  4. 组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
  5. 全文索引：对文本的内容进行分词，进行搜索(FULLTEXT INDEX)

添加索引

--创建普通索引
CREATE INDEX index_name ON table_name(col_name);
--创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);
--创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
--创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);
--也可以使用ALTER TABLE 的命令实现
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

删除和查询索引

--直接删除索引
DROP INDEX index_name ON table_name;
--修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;
--查看索引
show index from  table_name;
从物理存储角度为两类：

  • 聚簇索引：(InnoDB使用)将数据存储与索引放到了一块，将主键组织到一棵B+树中，而行数据就储存在叶子节点上,找到索引也就找到了数据，一个表仅有一个聚簇索引。在聚簇索引之外创建的索引称之为辅助键索引，InnoDB下辅助索引访问数据总是需要二次查找，辅助索引叶子节点存储的不再是行的物理位置，而是主键值

  • 非聚簇索引：(也叫二级索引，Myisam使用）将数据存储和索引分开，索引结构的叶子节点指向了数据的对应行，myisam采用的就是非聚簇索引，通过key_buffer把索引先缓存到内存中，当需要访问数据时，在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key_buffer命中时，速度慢的原因


    聚簇和非聚簇索引.png
数据结构角度

从创建索引的数据结构的角度来讲，Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

  1. FULLTEXT
    即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。

  2. HASH
    由于HASH的唯一性（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。仅仅能满足"=","IN"和"<=>"查询的高效，对于范围查询、排序及组合索引仍然效率不高。
    由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；
    我们平时利用组合索引时可以采用部分索引键查询，但是Hash 索引不能这样。因为对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。
    对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。
    Mysql里面目前只有Memory引擎显示支持Hash索引。这也是Memory引擎表的默认索引类型

CREATE TABLE Student (
name VARCHAR(10),
KEY USING HASH(name)
)ENGINE=MEMORY;

  1. B+TREE
    B+TREE索引就是一种将索引值按一定的算法，存入B+Tree中，这是MySQL里默认和最常用的索引类型。
    B+TREE在MyISAM里的储存形式和Innodb稍有不同，具体的上面讲聚簇索引就有提到

  2. RTREE
    RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

索引注意事项

1.索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
2.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4.like语句操作
一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5.不要在列上进行运算
这将导致索引失效而进行全表扫描，例如SELECT * FROM table_name WHERE YEAR(column_name)<2017
6.不使用not in和<>操作

复制原理

mysql主(称master)从(称slave)复制的原理：

  1. master将数据改变记录到二进制日志(binary log)中,也即是配置文件log-bin指定的文件(这些记录叫做二进制日志事件，binary log events)
  2. slave将master的binary log events拷贝到它的中继日志(relay log)
  3. slave重做中继日志中的事件,将改变反映它自己的数据(数据重演)

日志

mysql日志一般分为5种
错误日志：-log-err (记录启动，运行，停止mysql时出现的信息)
二进制日志：-log-bin （记录所有更改数据的语句，还用于复制，恢复数据库用）
查询日志：-log （记录建立的客户端连接和执行的语句）
慢查询日志: -log-slow-queries （记录所有执行超过long_query_time秒的所有查询）

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。

Mysql读写分离

为了提升业务系统性能，优化用户体验，可以通过做主从复制（读写分离）来减轻主数据库的负载。

而且如果主数据库宕机，可快速将业务系统切换到从数据库上，可避免数据丢失。

MySQL主从复制（读写分离）和集群的区别：

  • 主从复制（读写分离）：一般需要两台及以上数据库服务器即可（一台用于写入数据，一台用于同步主的数据并用于数据查询操作）。
    局限性：
    配置好主从复制之后，同一张表，只能对一个服务器写操作。如果在从上执行了写操作，而之后主也操作了这张表，或导致主从不同步。
    主数据库服务器宕机，需要手动将业务系统切换到从数据库服务器。无法做到高可用性（除非再通过部署keepalive做成高可用方案）。

  • 集群是由N台数据库服务器组成，数据的写入和查询是随机到任意一台数据库服务器的，其他数据库服务器会自动同步数据库的操作。任何一台数据库宕机，不会对整个集群造成大的影响。
    局限性：
    我经过测试才知道目前mysql集群版本（MySQL Cluster）只能对NDB存储引擎的数据进行集群同步，如果是INNODB或其他的MySQL存储引擎是不行的。这个也导致了我放弃了在业务系统中应用这种方案。

MySQL 驱动

从show engine中可以看到innoDB是MySQL默认的存储引擎，InnoDB的最大特色就是支持了ACID兼容的事务，它支持事务，支持行级锁，支持外键。适合高并发操作。
MyISAM是MySQL的默认数据库引擎（5.5版之前）

  1. 存储结构
    MyISAM：每个MyISAM在磁盘上存储成三个文件。
    InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
  2. 存储空间
    MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认)、动态表、压缩表。
    InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
  3. 可移植性、备份及恢复
    MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
    InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
  4. 事务支持
    MyISAM：强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。
    InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
  5. 表锁差异
    MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
    InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的性能。但是InnoDB的行锁，只是在WHERE是主键时有效，非主键的WHERE都会锁全表的。
  6. 索引
    InnoDB：使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上
    MyISM：使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同
  7. 外键
    MyISAM：不支持
    InnoDB：支持
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成，浏览时请结合常识与多方信息审慎甄别。
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

友情链接更多精彩内容