Mysql笔记

#导入数据库 [1]
1、首先建空数据库
mysql > create database abc;

2、导入数据库
方法一：
（1）选择数据库
    mysql > use abc;
（2）设置数据库编码
    mysql > set names utf8;
（3）导入数据（注意sql文件的路径）
    mysql > source /home/abc/abc.sql;
方法二：
    mysql -u用户名 -p密码 数据库名 < 数据库名.sql
    #mysql -uabc_f -p --default-character-set=utf8 abc < abc.sql

建议使用第二种方法导入。
注意：有命令行模式，有sql命令

#根据积分查询用户排名 [1]
select * from (
  SELECT @rownum:=@rownum+1 as rownum, id, nickname FROM `wx_user`, (select @rownum:=0) t order by user_score desc
) as a where a.id = 2

#修改自增ID
ALTER TABLE wx_user_test auto_increment = 40 ;

#查看连接
show processlist;
kill  id

#Mysql 获取一小时前时间
select date_sub(now(), interval 1 hour);
select date_sub(now(), interval -1 hour);　　　　　　//一小时后
select date_add(now(), interval 1 hour);　　　　　　 //一小时后, 更多可选值参考[1] [2]

#日期时间转时间戳 [1]
SELECT UNIX_TIMESTAMP( DATE_ADD( NOW( ) , INTERVAL -3 DAY ) ) ;

#时间戳转时期时间
SELECT *, FROM_UNIXTIME( created_at, '%Y-%m-%d %H:%i:%s' ) as date1 FROM `user`;

#查看数据库中所有表的记录数 [1]
select table_name,table_rows from information_schema.tables 
where TABLE_SCHEMA = 'test'   
order by table_name desc;

#创建一个和已经存在表结构相同的表
CREATE TABLE wx_user1 LIKE wx_user;

#重命名表
RENAME TABLE  `wx_user1` TO  `wx_user` ;

#查看索引
SHOW INDEX FROM wx_user;

#添加多字段唯一索引
ALTER TABLE  `book` ADD UNIQUE (`book_id`,`page_num`)

#删除索引 [1]
ALTER TABLE wx_user DROP INDEX openid_2;

#新建数据库
CREATE DATABASE  `test2` DEFAULT CHARACTER SET utf8    COLLATE utf8_general_ci;
CREATE DATABASE  `test2` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

#查看数据库编码
SHOW VARIABLES LIKE '%character%'

#查询数据库中指定表的所有字段名 [1]
SELECT GROUP_CONCAT( COLUMN_NAME SEPARATOR ", " ) FROM information_schema.COLUMNS WHERE table_name = 'wx_user' AND table_schema = "database_name"

#查询数据库中符合条件的表名
SELECT
  GROUP_CONCAT( TABLE_NAME SEPARATOR ", " ) 
FROM information_schema.TABLES
WHERE
  TABLE_SCHEMA='testdb' and TABLE_NAME not like "t_%"

#用select的数据更新另一个表中数据 [1]
UPDATE wx_user AS a INNER JOIN (
	SELECT uid, COUNT( 1 ) AS read_count_total
	FROM wx_book_read
	WHERE status = 1
	GROUP BY uid
) AS b ON a.id = b.uid
SET a.read_count = b.read_count_total

UPDATE wx_book_read_page AS a 
LEFT JOIN wx_book_read AS b 
ON  a.book_id = b.book_id 
AND a.uid = b.uid 
SET a.book_read_id = b.id

#查看mysql配置文件在哪
/usr/sbin/mysqld --verbose --help|grep -A 1 'Default options'

#替换mysql字段中部分内容
UPDATE wx_user SET image = REPLACE(image , 'http://', 'https://') WHERE id = 1;

#mysql字符串拼接
UPDATE  `goods_cat` SET name = CONCAT( name,  '2' ) WHERE TYPE =2;

#mysql字符串处理函数
LENGTH(TRIM(url)), LENGTH(url)
CHAR_LENGTH

#mysql按in中内容排序 [1]
SELECT id, title, description, 
TYPE FROM wx_article
WHERE id
IN ( 784, 743, 596, 659, 685, 617, 836, 815, 813, 774 ) 
ORDER BY INSTR(  ',784,743,596,659,685,617,836,815,813,774,', CONCAT(  ',', id,  ',' ) ) 

SELECT id, title, description,  FIELD( id, 3, 5, 1, 4, 2 ) as order_data
FROM wx_article
WHERE ID
IN ( 3, 5, 1, 4, 2 ) 
ORDER BY FIELD( id, 3, 5, 1, 4, 2 ) 

SELECT FIELD( 1, 3, 5, 1, 4, 2 );//3

#随机阅读次数100-200次
select @i:=100;select @j:=200;
UPDATE `wx_article_test` SET `view_count` = FLOOR(@i + rand() * (@j - @i + 1)) WHERE 1;

#将查询的数据写入另一表中
INSERT INTO wx_article_view_count( count1, uid )
SELECT COUNT( * ) AS count1, uid
FROM `wx_article_view_log`
GROUP BY uid
ORDER BY `count1` DESC

#修改表注释
ALTER TABLE `wx_user` COMMENT = '用户表';

#查表时新构建一列，想查看原所有列又不想输入所有字段名称时可用表别名简单处理
SELECT FROM_UNIXTIME( ctime,'%Y-%m-%d' ) as date1,a.* FROM `wx_user` as a;

#创建临时表
CREATE TEMPORARY TABLE wx_user_temp AS
(
    SELECT * FROM wx_user where id < 10
);

#MySQL查看当前数据库 [1]
select database();
status;

#建表
CREATE TABLE IF NOT EXISTS `wx_user2` LIKE `wx_user`;

#MySQL正则
SELECT id,name,sn FROM `wx_goods` WHERE sn REGEXP '^[\.a-zA-Z0-9]+$';
SELECT id,name,sn FROM `wx_goods` WHERE sn NOT REGEXP '^[\.a-zA-Z0-9]+$';

SELECT id,name,sn FROM `wx_goods` WHERE sn REGEXP binary '^[\.a-z]+$'; -- 正则匹配区分大小写 [1]

#将查询的值保存变量
SELECT @max_id:=(SELECT max(id) FROM news);
SELECT * FROM news WHERE id > @max_id;

#通过分隔符截取
select substring_index("www.test.com", '.', 1);  -- www
select substring_index("www.test.com", '.', -1); -- com

#查询字符串所在位置
select position("." in "www.test.com"); -- 4

#给已成存在的列设置主键和自增ID
ALTER TABLE `test` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY;