MySQL

MySQL 横表和纵表转换 (1) 表tb1有如下数据: 姓名 语文 数学 物理 张三 68 89 99 李四 90 66 78 现在要求写出查询语句得到如下查询结果 name subject score 张三 语文 68 张三 数学 89 张三 物理 99 李四 语文 90 李四 数学 66 李四 物理 78 sql语句如下: select 姓名 as name,‘语文’ as subject,语文 as score from tb1 union select 姓名 as name,‘数学’ as subject,数学 as score from tb1 union select 姓名 as name,‘物理’ as subject,物理 as score from tb1 ...

MySQL if IF表达式 IF(expr1,expr2,expr3) 如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。 select *,if(sva=1,"男","女") as ssva from taname where sva != "" 作为表达式的if也可以用CASE when来实现: select CASE sva WHEN 1 THEN '男' ELSE '女' END as ssva from taname where sva != '' 在第一个方案的返回结果中， value=compare-value 而第二个方案的返回结果是第一种情况的真实结果。如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE 部分，则返回值为 NULL。 SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END as testCol -- 将输出one http://outofmemory.cn/code-snippet/1149/MySQL-if-case-statement-usage-summary ...

‘MySQL 字符串连接 CONCAT()’ MySQL> SELECT CONCAT('My', 'S', 'QL'); -> 'MySQL' MySQL CONCAT () 函数用于将多个字符串连接成一个字符串 MySQL CONCAT(str1,str2,…) 返回结果为连接参数产生的字符串。如有任何一个参数为NULL ,则返回值为 NULL。或许有一个或多个参数。 如果所有参数均为非二进制字符串,则结果为非二进制字符串。 如果自变量中含有任一二进制字符串,则结果为一个二进制字符串。一个数字参数被转化为与之相等的二进制字符串格式；若要避免这种情况,可使用显式类型 cast, 例如: SELECT CONCAT(CAST(int_col AS CHAR), char_col) MySQL> SELECT CONCAT(‘My’, ‘S’, ‘QL’); -> ‘MySQL’ MySQL> SELECT CONCAT(‘My’, NULL, ‘QL’); -> NULL MySQL> SELECT CONCAT(14.3); -> ‘14.3′ MySQL CONCAT_WS(separator,str1,str2,…) CONCAT_WS() 代表 CONCAT With Separator ,是CONCAT()的特殊形式。 第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数。如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。 MySQL> SELECT CONCAT_WS(’,’,‘First name’,‘Second name’,‘Last Name’); -> ‘First name,Second name,Last Name’ ...

MySQL 里的IFNULL、NULLIF和ISNULL用法 今天用到了MySQL里的isnull才发现他和MSSQL里的还是有点区别,现在简单总结一下: **MySQL中isnull,ifnull,nullif的用法如下: ** **isnull(expr) 的用法: ** 如expr 为null,那么isnull() 的返回值为 1,否则返回值为 0。 MySQL> select isnull(1+1); -> 0 MySQL> select isnull(1/0); -> 1 使用= 的null 值对比通常是错误的。 isnull() 函数同 is null比较操作符具有一些相同的特性。请参见有关is null 的说明。 **IFNULL(expr1,expr2)的用法: ** 假如expr1 不为 NULL,则 IFNULL() 的返回值为 expr1; 否则其返回值为 expr2。IFNULL()的返回值是数字或是字符串,具体情况取决于其所使用的语境。 MySQL> SELECT IFNULL(1,0); -> 1 MySQL> SELECT IFNULL(NULL,10); -> 10 MySQL> SELECT IFNULL(1/0,10); -> 10 MySQL> SELECT IFNULL(1/0,‘yes’); -> ‘yes’ IFNULL(expr1,expr2)的默认结果值为两个表达式中更加"通用"的一个,顺序为STRING、 REAL或 INTEGER。假设一个基于表达式的表的情况, 或MySQL必须在内存储器中储存一个临时表中IFNULL()的返回值: CREATE TABLE tmp SELECT IFNULL(1,’test’) AS test； ...

MySQL coalesce coalesce函数表示可以返回参数中的第一个非空表达式,当你有N个参数时选取第一个非空值 (从左到右) 。 实例一: select coalesce (null,“carrot”,“apple”) 返回结果: carrot 实例二: select coalesce(1,“carrot”,“apple”) 返回结果: 1 友情提示: coalesce里的参数如果是字符串的话,务必用单引号或者双引号廓起来； 这些语句可以直接在MySQL里运行。 http://hi.baidu.com/luoganet/item/7ec497dec10c88e2795daa1f

MySQL limit SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset SELECT 语句返回指定的记录数 LIMIT 接受一个或两个数字参数。参数必须是整数常量。如果输入两个参数,第一个参数指定返回记录的偏移量,第二个参数指定返回记录行的最大数目。第一条记录行的偏移量是 0(不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持语法: LIMIT # OFFSET #。 SELECT * FROM table LIMIT 2,1; //从第2条开始读,读取1条信息。 MySQL> SELECT * FROM table LIMIT 5,10; // 检索 6-15 行 //为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1: SELECT FROM table LIMIT 95,-1; // 检索记录行 96-last. //如果只给定一个参数,它表示返回最大的记录行数目: SELECT FROM table LIMIT 5; //检索前 5 个记录行 //换句话说,LIMIT n 等价于 LIMIT 0,n。 如果是oracle 可以用rownum实现相同的功能。 ...

MySQL修改密码 /usr/bin/MySQLadmin -u root password 'haCahpro' MySQL改root密码 MySQL -u root -p MySQL> SET PASSWORD FOR 'root'@'localhost'=PASSWORD('pa55word'); MySQL> QUIT SQL Error (1130): Host '192.168.1.126' is not allowed to connect to this MySQL server 通过HeidiSQL连接MySQL数据库报错: SQL Error (1130): Host ‘192.168.1.126’ is not allowed to connect to this MySQL server 说明所连接的用户帐号没有远程连接的权限，只能在本机(localhost)登录。 需更改 MySQL 数据库里的 user表里的 host项把localhost改称% 首先按下面的步骤登录MySQL服务器 登录MySQL需要切换到dos下的MySQL的bin目录，进行如下操作: MySQL>use MySQL; MySQL>update user set host = '%' where user ='root'; MariaDB [MySQL]> update user set host = ‘%’ where host = ’localhost’; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0 ...

MySQL 数据类型 datetime, timestamp 两者都是时间类型字段，格式都一致。两者主要有以下几点区别: 最主要的区别-受时区影响不同。timestamp会跟随设置的时区变化而变化，而datetime保存的是绝对值不会变化。 详细可以阅读这篇博客的演示: MySQL: Datetime Versus Timestamp Data Types 一个timestamp字段，一个datetime字段，修改时区SET TIME_ZONE = “america/new_york”;后，timestamp字段的值变了! 因此，如果应用场景有跨时区要求的要特别注意这点。 占用存储空间不同。timestamp储存占用4个字节，datetime储存占用8个字节: 12.8 Data Type Storage Requirements 可表示的时间范围不同。timestamp可表示范围:1970-01-01 00:00:00~2038-01-09 03:14:07，datetime支持的范围更宽1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 索引速度不同。timestamp更轻量，索引相对datetime更快。 数值类型 MySQL 的数值数据类型可以大致划分为两个类别，一个是整数，另一个是浮点数或小数。许多不同的子类型对这些类别中的每一个都是可用的，每个子类型支持不同大小的数据，并且 MySQL 允许我们指定数值字段中的值是否有正负之分或者用零填补。 各种数值类型以及它们的允许范围和占用的内存空间。 TINYINT 大小: 1 字节 范围 (有符号) : -128 ~ 127 范围 (无符号) : 0 ~ 255 用途: 小整数值 SMALLINT 大小: 2 字节 范围 (有符号) : -32,768 ~ 32,767 范围 (无符号) : 0 ~ 65,535 用途: 大整数值 MEDIUMINT 大小: 3 字节 范围 (有符号) : -8,388,608，8,388,607 范围 (无符号) : 0，16,777,215 用途: 大整数值 INT 或 INTEGER 大小: 4 字节 范围 (有符号) : -2 147 483 648，2 147 483 647 范围 (无符号) : 0，4 294 967 295 用途: 大整数值 类型 - 大小 范围 (有符号) 范围 (无符号) 用途 BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) FLOAT 4 字节 (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值 DOUBLE 8 字节 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值 DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值小数值 ...

MySQL用户与权限管理 //创建新用户 user0 cREATE USER user0 IDENTIFIED BY 'password0';``` \***密码要带引号 授权: ```bash grant all privileges on database0.* to user0@localhost identified by 'password0'; 授权之后该用户才能用他自己的用户名密码访问MySQL. 从MySQL删除用户账户 要想移除账户，应使用Drop USER语句. Drop USER user [, user] … Drop USER语句用于删除一个或多个MySQL账户。要使用Drop USER，您必须拥有MySQL数据库的全局Create USER权限或Delete权限。使用与GRANT或REVOKE相同的格式为每个 账户命名；例如，‘jeffrey’@’localhost’。 账户名称的用户和主机部分与用户表记录的User和Host列值相对应。 www.87717.com 使用Drop USER，您可以取消一个账户和其权限，操作如下: Drop USER user; 该语句可以删除来自所有授权表的帐户权限记录。 drop user [username@’%’][1] drop user username@localhost 改密码: grant all privileges on DBNAME.* to user0@localhost identified by ‘password4’; ###’@localhost’ 这个一定要有….. 为root加上密码xxx123: ./bin/MySQLadmin -u root password xxx123 或写成 ...

MySQLdump MySQL有很多可以导入数据的方法，然而这些只是数据传输中的一半，另外的一般是从MySQL数据库中导出数据。有许多的原因我们需要导出数据。一个重要的原因是用于备份数据库。数据的造价常常是昂贵的，需要谨慎处理它们。经常地备份可以帮助防止宝贵数据地丢失；另外一个原因是，也许您希望导出数据来共享。 在这个信息技术不断成长的世界中，共享数据变得越来越常见。 比方说Macmillan USA维护护着一个将要出版的书籍的大型数据库。这个数据库在许多书店之间共享，这样他们就知道哪些书将会很快出版。医院越来越走向采用无纸病历记录，这样这些病历可以随时跟着你。世界变得越来越小，信息也被共享得越来越多。有很多中导出数据得方法，它们都跟导入数据很相似。因为，毕竟，这些都只是一种透视得方式。从数据库导出的数据就是从另一端导入的数据。这里我们并不讨论其他的数据库各种各样的导出数据的方法，您将学会如何用MySQL来实现数据导出。 使用MySQLdump: (MySQLdump命令位于MySQL/bin/目录中) MySQLdump工具很多方面类似相反作用的工具MySQLimport。它们有一些同样的选项。但MySQLdump能够做更多的事情。它可以把整个数据库装载到一个单独的文本文件中。这个文件包含有所有重建您的数据库所需要的SQL命令。这个命令取得所有的模式 (Schema，后面有解释) 并且将其转换成DDL语法 (CREATE语句，即数据库定义语句) ，取得所有的数据，并且从这些数据中创建INSERT语句。这个工具将您的数据库中所有的设计倒转。因为所有的东西都被包含到了一个文本文件中。这个文本文件可以用一个简单的批处理和一个合适SQL语句导回到MySQL中。这个工具令人难以置信地简单而快速。决不会有半点让人头疼地地方。 因此，如果您像装载整个数据库Meet_A_Geek的内容到一个文件中，可以使用下面的命令: bin/MySQLdump –p Meet_A_Geek > MeetAGeek_Dump_File.txt 这个语句也允许您指定一个表进行dump(备份/导出/装载?)。如果您只是希望把数据库Meet_A_Geek中的表Orders中的整个内容导出到一个文件，可以使用下面的命令: bin/MySQLdump –p Meet_A_Geek Orders >MeetAGeek_Orders.txt 这个非常的灵活，您甚至可以使用WHERE从句来选择您需要的记录导出到文件中。要达到这样的目的，可以使用类似于下面的命令: bin/MySQLdump –p –where=“Order_ID > 2000” Meet_A_Geek Orders > Special_Dump.txt MySQLdump工具有大量的选项，部分选项如下表: 选项/Option 作用/Action Performed -add-drop-table 这个选项将会在每一个表的前面加上DROP TABLE IF EXISTS语句，这样可以保证导回MySQL数据库的时候不会出错，因为每次导回的时候，都会首先检查表是否存在，存在就删除 -add-locks 这个选项会在INSERT语句中捆上一个LOCK TABLE和UNLOCK TABLE语句。这就防止在这些记录被再次导入数据库时其他用户对表进行的操作 -c or - complete_insert 这个选项使得MySQLdump命令给每一个产生INSERT语句加上列 (field) 的名字。当把数据导出导另外一个数据库时这个选项很有用。 -delayed-insert 在INSERT命令中加入DELAY选项 -F or -flush-logs 使用这个选项，在执行导出之前将会刷新MySQL服务器的log. -f or -force 使用这个选项，即使有错误发生，仍然继续导出 -full 这个选项把附加信息也加到CREATE TABLE的语句中 -l or -lock-tables 使用这个选项，导出表的时候服务器将会给表加锁。 ...

MySQL 备份 还原 导入 导出 export import # Export MySQLdump -uwiloon -pPASSWORD --default-character-set=utf8 enlab >enlab.sql # -u 与 username 之前可以有空格, -p 与 password 之间可以有空格, -p 后也可以不跟密码, 命令执行后会提示输入密码. #Import: #1. MySQL>source /path/to/sql/abc.sql; #2. #MySQL -u用户名 -p密码 数据库名 < 数据库名.sql MySQL -uusername -ppassword db_name < db_name.sql http://blog.csdn.net/myron_sqh/article/details/13016945

MySQL批量导入sql文本文件 source abc.sql;

MySQL str to datatime str_to_date(‘29-AUG-10 03.26.32 PM’,’%d-%b-%y %h.%i.%s %p’)

修改 MySQL 字符集 查看字符集设置 show variables like 'character_set_%'; set character_set_client=utf8; set character_set_connection=utf8; set character_set_database=utf8; set character_set_results=utf8; set character_set_server=utf8; set character_set_database=utf8; ..character_set_client ..character_set_connection ..character_set_database sudo emacs /etc/MySQL/my.cnf 在[client]下添加 default-character-set=utf8 在[MySQLd]下添加 default-character-set=utf8 (!!!MySQL 5.5.20 以后的版本要这样改 character-set-server=utf8) - 查看表编码 SHOW CREATE TABLE tbl_name; 改表的字符集. ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8; 重启MySQL; sudo /etc/init.d/MySQL restart; MySQL修改表、字段、库的字符集 http://fatkun.com/2011/05/MySQL-alter-charset.html/embed#?secret=tmPf4RUxzk http://www.unix.com/red-hat/168071-MySQL-MySQLd-fails-start.html

MySQL string to date MySQL中DATE_FORMAT(date, format)函数可根据format字符串格式化日期或日期和时间值date，返回结果串。 也可用DATE_FORMAT( ) 来格式化DATE 或DATETIME 值，以便得到所希望的格式。根据format字符串格式化date值: 下面是函数的参数说明: %S, %s 两位数字形式的秒 ( 00,01, . . ., 59) %i 两位数字形式的分 ( 00,01, . . ., 59) %H 两位数字形式的小时，24 小时 (00,01, . . ., 23) %h, %I 两位数字形式的小时，12 小时 (01,02, . . ., 12) %k 数字形式的小时，24 小时 (0,1, . . ., 23) %l 数字形式的小时，12 小时 (1, 2, . . ., 12) %T 24 小时的时间形式 (hh : mm : s s) ...

MySQL 函数 MySQL> select date_format(now(),'%Y-%m-%d'); MySQL> select time_format(now(),'%H-%i-%S'); -- 连接字符串, CONCAT select CONCAT('My', 'S', 'QL');

MySQL 复制 1， MySQL的复制原理以及流程。 先问基本原理流程，3个线程以及之间的关联。 答: MySQL复制的三个线程: 主库线程，从库I/O线程，从库sql线程； 复制流程: (1) I/O线程向主库发出请求 (2) 主库线程响应请求，并推binlog日志到从库 (3) I/O线程收到线程并记入中继日志 (4) Sql线程从中继日志读取sql，并记入从库binlog日志，flush进硬盘； (2) 再问一致性延时性，数据恢复； 答: (1) 主从复制一致性由binlog执行顺序保证 (timespan+pos) ； 日志越详细，主从一致性越容易保证； (2) 延时性: 延时表现为behind_master_pos后面的数字，其实并不准确； 5.5.30以前版本都属于异步复制，因此都有延时。因为是主库执行完成后从库才执行，一先一后就有了延迟； 主从延迟的准确计算方法是: 延迟时间=从库执行sql完成的时刻-主库开始执行sql的时间； (3) 数据恢复: 备份时记录的binlog位置点 (timespan+pos) ； (3) 再问各种工作遇到的复制bug的解决方法； 答: 这个问题感觉描述并不准确，不清楚是主从复制故障还是bug； 故障一般由于主键冲突，链接不上主库，找不到对应的binlog位置等引起； 解决方法是跳过冲突，检查主从链接，找正确的pos； bug不常见，笔者碰到过一次，分享如下: 环境: 主库从库都是虚机，每十分钟与宿主机同步一次时间，大约每次与主机相差2秒； 表现: 从库复制时重复执行两秒之内的日志； 从库show slave status\G，behind_master_pos在60000和0之间循环，每两秒一次； 2， MySQL中myisam与innodb的区别，至少5点。 (1) 问5点不同 答: 1、 存储成本不一样，存储限制不一样； 2、CPU使用成本不一样，innodb缓存数据和索引； 3、锁粒度不一样，支持MVCC； 4、缓存机制不一样(buffer_pool和key_buffer) 5、事务支持； 6、索引支持: 全文索引(myisam),外键(innodb)，hash(innodb) 7、读写速度； 8、备份； (2) 、问各种不同MySQL版本的2者的改进； 最近测5.1.38和5.5.35 Innodb: (1) adeptive_innodb_index可控； (2) innodb变为默认引擎； ...

MySQL basic install # client sudo pacman -S mariadb-clients # server + client sudo pacman -S mariadb mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql systemctl start mysql ### 查看有哪些数据库 show databases; ### 创建数据库, 数据库名 mydb create database db_0; #创建数据库指定数据库的字符集 create database mydb character set utf8; ### 创建新用户 user_0 CREATE USER user_0 IDENTIFIED BY 'password_0'; ### 授权用户 user0 拥有数据库 database_0 的所有权限 grant all privileges on newbee_mall.* to user_0@'%' identified by 'password_0'; # 表, 增加字段 ALTER TABLE table_name ADD field_name field_type; alter table tbl_user add email varchar(255) NOT NULL; 查看表结构 desc table_name; mysqldump mysqldump -h 192.168.50.100 -uroot -p --databases rssx --tables user --where=user_id='0' mysql GUI client for Linux Jetbrain.Datagrip ...