Month: June 2018

MySQL:多表查询, 连表操作和GROUP_CONCAT函数的使用, GROUP BY结合GROUP_CONCAT, GROUP_CONCAT DISTINCT multiple columns

在开发网站的时候,数据库操作是必须,复杂的网站有时要涉及到几张表的同时操作。以下简单讲解一下多表查询和GROUP_CONCAT函数的使用。例如有三张表 jd_role:角色表,jd_role_privilege:角色权限id关联表,jd_privilege:权限表,三张表的结构如下: 1、要求查询每个角色拥有的权限: 使用left join左外连进行连表操作: 此时,就可以查询出每个角色拥有哪些权限,但是这样查询不是很符合实际开发,比如说在修改角色权限的时候,如何把一个角色的权限放入下拉选择框中,这需要把一个角色拥有的所有的权限转换成字符串或者数组,这时候可以使用GROUP_CONCAT函数: 2、以id分组,把权限名称打印成一行,用分号隔开: 3、以id分组,把权限名称打印成一行,去除重复字段: 4、以id分组,把权限名称打印成一行,用逗号隔开,根据权限id排序: 本质上就是用到了下面的样板句: group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符']) 例如: select id,group_concat(name) from aa group by id; // separator 提供自定义分割线,默认是逗号, select id,group_concat(name separator ';') from aa group by id; SELECT student_id,… Read More

Mysql: 外键约束, CASCADE、SET NULL、RESTRICT、NO ACTION分析和作用, Cannot delete or update a parent row: a foreign key constraint fails

  MySQL有两种常用的引擎类型:MyISAM和InnoDB。目前只有InnoDB引擎类型支持外键约束。InnoDB中外键约束定义的语法如下: ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option] 例如: ALTER TABLE `user_resource` CONSTRAINT `FKEEAF1E02D82D57F9` FOREIGN KEY (`user_Id`) REFERENCES `sys_user` (`Id`) InnoDB也支持使用ALTER TABLE来删除外键: ALTER TABLE `user_resource` DROP… Read More

Mysql: 一对多查询, 将多条记录合并成一条记录, mysql两表联合查询,一对多

t_user表如下 cj表如下: 要获得的查询结果: 解决办法: 方法一: select cjc.uid,u.uname,cjc.数学,cjc.英语,cjc.语文,u.type from   (select uid,max(case subj when '数学' then score else 0 end) 数学,  max(case subj when '语文' then score else 0 end) 语文,  max(case subj when '英语' then score else 0 end) 英语   from … Read More

Mysql: 数据存在更新,不存在插入, Insert if not exist otherwise update, mysql update or insert if not exists without primary key, replace into

  一般有三种方法: 我们用名为 books 的表做演示 mysql> SELECT * FROM books LIMIT 3; +----+-------------------------+---------------------+----------------+ | id | title | author | year_published | +----+-------------------------+---------------------+----------------+ | 1 | In Search of Lost

Linux: 搭建邮件服务器, 命令行发送邮件, sendgrid服务器搭建, Postfix服务器搭建

  1. 获取root权限 sudo su - umask 077 2. 安装 Postfix代理软件 Postfix Mail Transport Agent. #DEBIAN apt-get update && apt-get install postfix libsasl2-modules -y #CENTOS yum install postfix cyrus-sasl-plain cyrus-sasl-md5 -y 如果有弹出窗口的话,选择 Local Only 3. 安装成功后,修改postfix的配置文件: vi /etc/postfix/main.cf 4. 注释掉下面两行代码:… Read More

Shell: 删除某时间之前的文件,Argument list too long错误, Remove all files created before a certain date,Argument list too long error

基于 find 来的做: 先了解 find 命令,  基于名字搜索 # 单条件 -name只针对文件名 find . -name '*.jpg' -print ./bar/foo.jpg # or 或条件 find . \( -name '*.mp3' -o -name '*.jpg' \) -print ./bar/foo.jpg ./foo.mp3 # and 和条件 find . -name '*.mp3' -name '*.jpg'… Read More

MySQL为什么要关闭Query Cache?

当你的数据库打开了Query Cache(简称QC)功能后,数据库在执行SELECT语句时,会将其结果放到QC中,当下一次处理同样的SELECT请求时,数据库就会从QC取得结果,而不需要去数据表中查询。 在这个”Cache为王”的时代,我们总是通过不同的方式去缓存我们的结果从而提高响应效率,但一个缓存机制是否有效,效果如何,却是一个需要好好思考的问题。在MySQL中的Query Cache就是一个适用较少情况的缓存机制。在上图中,如果缓存命中率非常高的话,有测试表明在极端情况下可以提高效率238%。但实际情况如何? Query Cache有如下规则,如果数据表被更改,那么和这个数据表相关的全部Cache全部都会无效,并删除之。这里“数据表更改”包括: INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等。举个例子,如果数据表posts访问频繁,那么意味着它的很多数据会被QC缓存起来,但是每一次posts数据表的更新,无论更新是不是影响到了cache的数据,都会将全部和posts表相关的cache清除。如果你的数据表更新频繁的话,那么Query Cache将会成为系统的负担。有实验表明,糟糕时,QC会降低系统13%的处理能力。 如果你的应用对数据库的更新很少,那么QC将会作用显著。比较典型的如博客系统,一般博客更新相对较慢,数据表相对稳定不变,这时候QC的作用会比较明显。如果数据库一共往QC中写入了约800W次缓存,但是实际命中的只有约500W次。也就是说,每一个缓存的使用率约为0.66次。很难说,该缓存的作用是否大于QC系统所带来的开销。但是有一点是很肯定的,QC缓存的作用是很微小的,如果应用层能够实现缓存,将可以忽略QC的效果。 ————-下面是关于Query Cache相关参数—————– mysql> show variables like '%query_cache%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | have_query_cache

Mysql:认识查询缓存, MySQL查询缓存打开、设置、参数查询、性能变量, MySQL缓存的查询

  第一: query_cache_type 使用查询缓存的方式 一般,我们会把 query_cache_type 设置为 ON,默认情况下应该是ON mysql> select @@query_cache_type; +--------------------+ | @@query_cache_type | +--------------------+ | ON | +--------------------+ query_cache_type有3个值 0代表关闭查询缓存OFF,1代表开启ON,2(DEMAND)代表当sql语句中有SQL_CACHE关键词时才缓存,如:select SQL_CACHE user_name from users where user_id = ‘100’; 这样 当我们执行 select id,name from tableName; 这样就会用到查询缓存。 ①在 query_cache_type 打开的情况下,如果你不想使用缓存,需要指明… Read More

git: 回复误删文件, 从服务端拉取删除的文件, 删除文件后如何恢复, git checkout 用法总结

有时候不小心在git中rm了文件。怎么恢复呢?别急,咱们一步步来。 首先git status一把,看看此时工作区的状态 git status # On branch master # Changes to be committed: # (use "git reset HEAD <file>..." to unstage) # # deleted: system...... # 如果我们想要恢复,怎么办呢? git checkout -- system 这样一般就可以了,如果发现有报错: error: pathspec 'system' did not match any… Read More

MySQL 中事务的实现

MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,文章等等,这样,这些数据库操作语句就构成一个事务! 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。 事务处理可以用来维护数据库的完整性,保证成批的 SQL 语句要么全部执行,要么全部不执行。 事务用来管理 insert,update,delete 语句 一般来说,事务是必须满足4个条件(ACID)::原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。 原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。 一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。 隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。 持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。 在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交。 事务控制语句: BEGIN或START TRANSACTION;显式地开启一个事务; COMMIT;也可以使用COMMIT WORK,不过二者是等价的。COMMIT会提交事务,并使已对数据库进行的所有修改称为永久性的; ROLLBACK;有可以使用ROLLBACK… Read More

浅入浅出了解 MySQL 和 InnoDB 区别

作为一名开发人员,在日常的工作中会难以避免地接触到数据库,无论是基于文件的 sqlite 还是工程上使用非常广泛的 MySQL、PostgreSQL,但是一直以来也没有对数据库有一个非常清晰并且成体系的认知,所以最近两个月的时间看了几本数据库相关的书籍并且阅读了 MySQL 的官方文档,希望对各位了解数据库的、不了解数据库的有所帮助。 本文中对于数据库的介绍以及研究都是在 MySQL 上进行的,如果涉及到了其他数据库的内容或者实现会在文中单独指出。 数据库的定义 很多开发者在最开始时其实都对数据库有一个比较模糊的认识,觉得数据库就是一堆数据的集合,但是实际却比这复杂的多,数据库领域中有两个词非常容易混淆,也就是数据库和实例: 数据库:物理操作文件系统或其他形式文件类型的集合; 实例:MySQL 数据库由后台线程以及一个共享内存区组成; 对于数据库和实例的定义都来自于 MySQL 技术内幕:InnoDB 存储引擎 一书,想要了解 InnoDB 存储引擎的读者可以阅读这本书籍。 数据库和实例 在 MySQL 中,实例和数据库往往都是一一对应的,而我们也无法直接操作数据库,而是要通过数据库实例来操作数据库文件,可以理解为数据库实例是数据库为上层提供的一个专门用于操作的接口。 在 Unix 上,启动一个 MySQL 实例往往会产生两个进程,mysqld 就是真正的数据库服务守护进程,而 mysqld_safe 是一个用于检查和设置 mysqld 启动的控制程序,它负责监控 MySQL 进程的执行,当 mysqld 发生错误时,mysqld_safe 会对其状态进行检查并在合适的条件下重启。… Read More

MySQL: 锁(行锁、表锁、页锁、乐观锁、悲观锁等)

锁,在现实生活中是为我们想要隐藏于外界所使用的一种工具。在计算机中,是协调多个进程或县城并发访问某一资源的一种机制。在数据库当中,除了传统的计算资源(CPU、RAM、I/O等等)的争用之外,数据也是一种供许多用户共享访问的资源。如何保证数据并发访问的一致性、有效性,是所有数据库必须解决的一个问题,锁的冲突也是影响数据库并发访问性能的一个重要因素。从这一角度来说,锁对于数据库而言就显得尤为重要。 MySQL锁 相对于其他的数据库而言,MySQL的锁机制比较简单,最显著的特点就是不同的存储引擎支持不同的锁机制。根据不同的存储引擎,MySQL中锁的特性可以大致归纳如下: 行锁 表锁 页锁 MyISAM √ BDB √ √ InnoDB √ √ 开销、加锁速度、死锁、粒度、并发性能 表锁: 开销小,加锁快;不会出现死锁;锁定力度大,发生锁冲突概率高,并发度最低 行锁: 开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高 页锁: 开销和加锁速度介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般   从上述的特点课件,很难笼统的说哪种锁最好,只能根据具体应用的特点来说哪种锁更加合适。仅仅从锁的角度来说的话: 表锁更适用于以查询为主,只有少量按索引条件更新数据的应用;行锁更适用于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用。(PS:由于BDB已经被InnoDB所取代,我们只讨论MyISAM表锁和InnoDB行锁的问题) MyISAM表锁 MyISAM存储引擎只支持表锁,这也是MySQL开始几个版本中唯一支持的锁类型。随着应用对事务完整性和并发性要求的不断提高,MySQL才开始开发基于事务的存储引擎,后来慢慢出现了支持页锁的BDB存储引擎和支持行锁的InnoDB存储引擎(实际 InnoDB是单独的一个公司,现在已经被Oracle公司收购)。但是MyISAM的表锁依然是使用最为广泛的锁类型。本节将详细介绍MyISAM表锁的使用。 查询表级锁争用情况 可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺: mysql> show status like ‘table%’; +———————–+——-+ | Variable_name         | Value |… Read More