MySql数据库基本用法

数据库概念

定义1

数据库（database）是按照数据结构来组织、存储、管理数据的建立在计算机存储设备上的仓库

定义2

严格来说，数据库是长期储存在计算机内、有组织的、可共享的数据集合。数据库中的数据指的是以一定的数据模型组织、描述和储存在一起、具有尽可能小的冗余度、较高的数据独立性和易扩展性的特点并可在一定范围内为多个用户共享。

数据库分类

数据库通常分为层次式数据库、网络式数据库和关系式数据库三种。而不同的数据库是按不同的数据结构来联系和组织的。而在当今的互联网中，最常见的数据库模型主要是两种，即关系型数据库和非关系型数据库

关系型数据库

关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。在关系型数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取等运算来实现数据库的管理。常见的关系型数据库有：Oracle MySql SqlServer Access

非关系型数据库

  1. 键值存储数据库（key-value）Memcached、Redis、MemcacheDB
  2. 列存储（Column-oriented）数据库 Cassandra、HBase
  3. 面向文档（Document-Oriented）数据库 MongoDB、CouchDB
  4. 图形数据库 Neo4J、InforGrid

MySql数据库的常用操作

MySql安装

  • 方法一

下载安装包，按照步骤安装下载地址

  • 方法二

命令：brew install mysql

MySql卸载

卸载前，先将mysql服务暂停

  • 方法一
  1. sudo rm /usr/local/mysql
  2. sudo rm -rf /usr/local/mysql*
  3. sudo rm -rf /Library/StartupItems/MySQLCOM
  4. sudo rm -rf /Library/PreferencePanes/My*
  5. rm -rf ~/Library/PreferencePanes/My*
  6. sudo rm -rf /Library/Receipts/mysql*
  7. sudo rm -rf /Library/Receipts/MySQL*
  8. sudo rm -rf /var/db/receipts/com.mysql.*
  9. 不同的安装方式有些东西的存储位置不一样，删除完检查一下一些问文件是否删除了，没有的话则删除掉：
    a)./usr/local/Cellar 里的mysql文件
    b)./usr/local/var 里的mysql文件
    c)./tmp 里的mysql.sock, mysql.sock.lock, my.cnf文件
    d).pid文件和err文件都在/usr/local/var/mysql里确保删除了
    e).brew安装的安装包存储在/usr/local/Library/Cache/Homebrew也可以一并删除
    f).执行brew cleanup
  • 方法二

brew uninstall mysql 或者 brew remove mysql

启动/暂停服务

  • 方法一

系统偏好设置->MySql->Start MySQL Server

  • 方法二
  1. sudo /usr/local/mysql/support-files/mysql.server start
  2. sudo /usr/local/mysql/support-files/mysql.server stop
  3. sudo /usr/local/mysql/support-files/mysql.server restart
  4. 若遇到权限问题sudo chmod -R 777 /usr/local/mysql/data

登录/退出MySql

  • 配置mysql环境变量

将mysql的路径配置在.bash_profile
export PATH=$PATH:
source .bash_profile

  • 登录本地数据库

命令：mysql –u 用户名 –p 密码

  • 登录远程数据库

命令：mysql　 -h 远程服务器地址 –P 端口号 –u 用户名 –p 密码
例如：mysql　 -h 192.168.52.153 –P 3306 –u learner –p 123456

  • 退出数据库

命令：exit

查看本链接下所有数据库

命令：show databases;

默认数据库

  • information_schema

作用：存储数据库的基本信息，例如：数据库名或表名，列的数据类型，访问权限等

  • mysql

作用：这个是mysql的核心数据库，主要负责存储数据库的用户、权限设置、关键字等

  • performance_schema

作用：mysql 5.5 版本 新增了一个性能优化的引擎

用户管理

创建用户

命令：create user 'username'@'host' identified by 'password';
例如：

  1. create user 'qzk'@'localhost' identified by '123456';
  2. create user 'qzk'@'192.168.1.101' identified by '';

更改用户密码

命令1：set password for 'user'@'host' = password('newpassword')
命令2:set password = password('new');//为当前用户设置密码

查看当前连接登录用户

命令：select user();

用户授权

命令：grant privileges on databasename.tablename to 'username'@'host'
说明：privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 。如果要授予所的权限则使用ALL;databasename - 数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*.
usage权限：建立一个用户，就会自动授予其usage权限（默认授予）。该权限只能用于数据库登陆，不能执行任何操作；且usage权限不能被回收，也即REVOKE用户并不能删除用户。
例如：

  1. grant select, insert on test.user to 'qzk'@'localhost';
  2. grant all on * 。 * to 'qzk'@'localhost';

查看授权

命令：show grants for user@host;
例如：show grants for qzk1@localhost;

取消授权

命令：revoke privileges from databasename.tablename to 'username'@'host'

删除用户

命令：drop user 'username'@'host';

数据库引擎

mysql可以将数据以不同的技术存储数据，这每一种存储引擎会使用不同的存储机制，索引技巧，锁定水平，最终提供广泛切不同的功能
相关概念：
并发控制：当多个链接对记录进行修改时，保证数据的一致性和完整性
锁：共享锁（读锁）：同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。
排他锁：在任何时候只有一个用户写入资源，当进行写时会阻塞其他读锁或者写锁
锁颗粒：表锁(开销最小的锁策略) 行锁(开销最大的锁策略)
查看mysql支持的存储引擎
命令：show engines;

Innodb引擎

Innodb引擎提供了对数据库ACID事务的支持,并且该引擎还提供了行级锁和外键约束，它的设计目标是处理大容量数据库系统，它本身其实就是基于MySQL后台的完整数据库系统，MySQL运行时Innodb会在内存中建立缓冲池，用于缓冲数据和索引。但是该引擎不支持FULLTEXT(全文索引)类型的索引，而且它没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时，该引擎当然是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表。

MyIASM引擎

MyIASM没有提供对数据库事务的支持，也不支持行级锁和外键，因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表，效率便会低一些。不过和Innodb不同，MyIASM中存储了表的行数，于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持，那么MyIASM也是很好的选择

创建数据库

命令：CREATE DATABASE 库名 charset=utf8;

删除数据库

命令：DROP DATABASE 库名;

选择数据库

命令：use 库名

创建数据表

命令：Create table 表名
(列名 类型(长度) default '默认值' 列级完整性约束条件);
约束：
NOT NULL：强制列不接受NULL值
UNIQUE :唯一标示约束
PRIMARYKEY:主键约束
例如： create table test1(column1 int primarykey auto_increment,column2 int unique,column3 char(20) not null);

查看表结构

命令：desc 表名

修改数据表

添加字段 ：alter table 表名 add 新列名 类型 约束
例如：alter table test1 add column4 int unique
修改字段名：alter table 表名 change 旧字段名 新字段名
修改字段类型：alter table 表名 modify 字段名 类型
删除字段：alter table 表名 drop 字段名

删除数据表

命令：drop table 表名

查看数据表

命令：show tables;

插入表数据

命令1：insert into 表名(列1，列2，列3)values(v1,v2,v3)(v11,v12,v13);
命令2:insert into 表名(列1,列2,列3)select(列1，列2，列3)from 表名 where 条件

更新表数据

命令：update 表名 set 列名1=value1,列名2=value2 where 条件

删除表数据

命令：delete from 表名 where 条件

清空表数据

命令:truncate table 表名
和delete from 表名的区别:

  1. truncate是整体删除（速度较快）， delete是逐条删除（速度较慢）。
  2. truncate不写服务器log，delete写服务器log，也就是truncate效率比delete高的原因
  3. truncate不激活trigger(触发器)，但是会重置Identity（标识列、自增字段），相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的ID数。而delete删除以后，Identity依旧是接着被删除的最近的那一条记录ID加1后进行记录。
  4. 如果只需删除表中的部分记录，只能使用DELETE语句配合where条件

事务的处理

事务必须满足的四个条件

  1. 原子性(Atomicity)
    事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
  2. 一致性(Consistency)
    确保数据库正确地改变状态后，成功提交的事务。
  3. 隔离性(Isolation)
    使事务操作彼此独立的和透明的
  4. 持久性(Durability)
    确保提交的事务的结果或效果的系统出现故障的情况下仍然存在
    语法：begin或start transaction；显式地开启一个事务；
    commit；也可以使用commit work，不过二者是等价的。commit会提交事务，并使已对数据库进行的所有修改称为永久性的；
    rollback；有可以使用rollback work，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
  5. 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
  6. 事务用来管理 insert,update,delete 语句

查询表数据

单表查询

命令: select 列1,列2,列3 from 表名 where 条件
where条件:

  1. 查询数值型数据 查询谓词 >,=,<,<>,!=,=>,=<,between and
  2. 查询字符串 =,like 例如:column1 = 'str' and column1 like '%key%'
  3. 查询日期型数据
  4. 子句连接可用and 或 or

正则查询

命令：select 列名 from table where 列名 regexp '正则';
例如：select * from table where clounm1 regexp '^s';//查询的结果集 是column1 以s开头
参考正则表达式

常用的聚合函数

  1. avg(列名)返回某列的平均值
  2. count(列名)返回某列的行数
  3. max(列名)返回某列的最大值
  4. min(列名)返回某列的最小值
  5. sum(列名)返回某个列之和
  6. 可配合as使用

常用的流程控制函数

  1. case value when [compare-value1] then result1 [when [compare-value2] then result2 [else result3] end
    解释：用value值来匹配，如果value1和value匹配，则返回result1 ，如果value2和value匹配，则返回result2，以此类推；否则，返回ELSE后的result3。；如果没有ELSE部分的值，则返回值为NULL。这种句型类似于Java当中的switch···case···default···。
    例如：select sum(case type when 0 then 2 when 2 then 3 when 4 then 1 else 0 end) as score from recordinfo_1 where matchId = ? and teamId = ? and type in(0,2,4) group by stage order by stage
  2. IF(expr1,expr2,expr3)
    解释：如果表达式expr1是TRUE ，则 IF()的返回值为expr2; 否则返回值则为 expr3。类似于三目运算符。
  3. IFNULL(expr1,expr2)
    解释：假如expr1不为NULL，则函数返回值为 expr1; 否则，如果如expr1为NULL，函数返回值为expr2。
  4. NULLIF(expr1,expr2)
    解释：如果expr1 = expr2成立，那么返回值为NULL，否则返回值为expr1

去除重复数据

命令：select distinct * FROM 表名;全部行去重

数据排序

命令：select 列名 from 表名 where 条件 order by 列名 ASC|DESC

  1. 可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
  2. 可以设定多个字段来排序。先按照第一列排序，再按照其他列排序
  3. 可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。

数据分组

命令：select 列名 from 表名 where 条件 group by 列名
根据某列去除重复数据 select *, count(distinct 列名) from table group by 表名

  1. 语句根据一个或多个列对结果集进行分组 ,多字段分组时，先按第一次分组的结果在进行其他列分组
  2. 在分组的列上我们可以使用 COUNT, SUM, AVG,等函数

Having用法

命令：select　列名 from 表名 where 条件 group by 列名 having 条件
与where 的区别
having字句可以筛选成组后的各种数据，where字句在聚合前先筛选记录，也就是说作用在group by和having字句前。而having子句在聚合后对组记录进行筛选。因此，WHERE子句不能包含聚集函数；因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。相反，HAVING 子句总是包含聚集函数。

limit用法

命令：select　列名 from 表名 where 条件 limit n,m
limit子句可以被用于强制selct语句返回指定的记录数。limit接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
例如：select * from table where 1=1 limit 5,10;检索记录行 6-15
select * from table where 1=1 limit 5;等同与limit 0,5

多表查询

union操作符

命令：select 列名 from 表名1 union (all) select 列名 from 表名2
union 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行。若不去除重复行，则联合all一块使用

  1. union内部的select语句必须拥有相同数量的列，列也必须拥有相似的数据类型
  2. 每条select语句中的列的顺序必须相同
  3. union结果集中的列名总是等于union中第一个select语句中的列名

连接查询

内链接

笛卡尔乘积：
笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尓积，又称直积，表示为X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。
假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为：
{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
命令:select 列名 from 表名1 join 表名2 on 条件

左连接

获取左表所有记录，即使右表没有对应匹配的记录 ,null值补充
命令：select 列名 from 表名1 left join 表名2 on 条件

右连接

获取右表所有记录，即使左表没有对应匹配的记录 ,null值补充
命令：select 列名 from 表名1 right join 表名2 on 条件

子查询

定义：子查询允许把一个查询嵌套在另一个查询当中
分类：

  1. 标量子查询：返回单一值的标量，最简单的形式。
  2. 列子查询：返回的结果集是 N 行一列。
  3. 行子查询：返回的结果集是一行 N 列。
  4. 表子查询：返回的结果集是 N 行 N 列。
标量子查询

是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式。 可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧
例如: select * from table1 as t1 WHERE column1 = (select column from table2 as t2 where t1.id=t2.id order by column1 desc limit 1)

列子查询

指子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回。
可以使用 in、any、some 和 all 操作符，不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。
some是any的别名，用的比较少。
例如：

  1. select * from table where column1 in(select column1 from table2 where column2=1)
  2. select s1 from table1 where s1 > any (select s2 from table2)
  3. select s1 from table1 where s1 > all (select s2 from table2)
    特殊情况：
  4. 如果 table2 为空表，则 all 后的结果为 TRUE；
  5. 如果子查询返回如 (0,NULL,1) 这种尽管 s1 比返回结果都大，但有空行的结果，则 all后的结果为 null 。
行子查询

指子查询返回的结果集是一行 N 列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集
例如：select * from table where (c1,c2,c3) = (select cc1,cc2,cc3 from table2 where 条件)

表子查询

指子查询返回的结果集是 N 行 N 列的一个表数据。
例如：select * from table WHERE (c1,c2,c3) in (select cc1,cc2,cc3 from table2)

exists谓词

exists是sql中的逻辑运算符号。如果子查询有结果集返回，那么就为True。exists代表“存在”的意义，它只查找满足条件的那些记录。一旦找到第一个匹配的记录后，就马上停止查找。

索引

优点：建立索引可以大大提高检索速度
索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
缺点：

  1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
  2. 建立索引会占用磁盘空间的索引文件

普通索引

索引创建

命令：create index indexName on table (列(长度));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

命令：alter table 表名 add index indexName(columnName)

创建表的时候直接指定

命令：create table 表名(column1 类型 NOT NULL,index indexName (列(长度)));

删除索引

命令：drop index [indexName] ON 表名;

唯一索引

索引列的值必须唯一，但允许有空值

创建索引

命令：create uniqe index indexName on table (列(长度));

修改表结构(添加索引)

命令：alter table 表名 add unique indexName(columnName)

创建表的时候直接指定

命令：create table 表名(column1 类型 NOT NULL,unique indexName (列(长度)));

Null值处理

  1. IS NULL: 当列的值是 NULL,此运算符返回 true。
  2. IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
  3. = 和 != 对判断Null值无效

临时表

保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。使用show tables命令显示数据表列表时,不限时临时表
创建临时表命令：create temporary table 表名()

数据库的导出导入

导出

命令：mysqldump -u 用户名 -p 数据库名 > 导出的文件名

导入

命令：source 数据库文件;

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