mysql学习笔记（新）

启动

net start mysql服务名

停止

net stop mysql服务名

登录mysql

mysql -h localhost -P 3306 -u root -p

查看所有数据库

show databases;

使用指定数据库

use 数据库名

查看当前所用的数据库

select database();

查看表结构

desc 表名;

查看版本

登录mysql后

select version();

登录mysql前

mysql --version  或 mysql -V

注释

单行

#注释文字
或
 -- 注释文字  (注意：--之后要用空格与注释文字分开)

多行

/*注释文字*/

查看数据库容量

where 条件指定后，则查询指定库的容量

select 
table_schema as '数据库' ,
table_name as '表名称',
table_rows as '记录数',
TRUNCATE(data_length/1024/1024,2) as '数据容量（MB）',
TRUNCATE(index_length/1024/1024,2) as '索引容量（MB）'
from information_schema.TABLES
 -- where table_schema='数据库名称'
ORDER BY table_schema asc,data_length desc,index_length desc;

update 中使用select

场景：

表A中存在字段ref_id 和 img ，表B中存在字段id和img。表A中的ref_id 对应表B中的id，但是表B中的img值为空，表A中的img存在值。

要求

将表A中与表B关联的img更新到表B的img中。

sql

update B as bb INNER JOIN 
(
	select  
		a.ref_id  as aid,
		a.img,
		b.id as bid 
	from 
		A as a,
		B as b
	where 
		b.img is null 
		and 
		a.ref_id = b.id 
) as t 
on bb.id = t.aid
set  bb.img=  t.img

创建用户并授权

-- 创建mysql用户
-- % 所有地址均可访问，也可指定特定的IP地址
CREATE USER '用户名'@'可访问的主机地址' IDENTIFIED BY '密码';

-- 给用户授权 
-- GRANT *.* 表示全部权限
-- on *.* 表示全部数据库全部表 
GRANT  
SELECT, INSERT, UPDATE, 
REFERENCES, DELETE, CREATE, DROP, 
ALTER, INDEX, CREATE VIEW, SHOW VIEW

ON 数据库名称.* TO '用户名'@'可访问的主机地址';

创建触发器

/*
	TRIGGER_NAME ：触发器名称
	TABLE_NAME：数据表名，作用在哪个表
	[after|before] : 操作顺序，after和before二选一，，分别表示之后和之前
	[update|insert|delete] ：操作，三选一
*/
delimiter ||
DROP TRIGGER if EXISTS TRIGGER_NAME||
create  TRIGGER TRIGGER_NAME [after|before] [update|insert|delete] on  TABLE_NAME for each row 
begin
		-- todo
		-- 这里写触发器的内容
end||
delimiter ;

创建视图

/*
	VIEW_NAME：视图名称
*/
drop view if EXISTS VIEW_NAME; -- 若存在同名的视图，则先删除后创建
create view VIEW_NAME as 
	-- todo
	-- 视图内容

创建数据表

/*
 TABLE_NAME： 表名
 
*/
drop table if EXISTS TABLE_NAME; -- 若存在同名的表，则先删除后创建
create table TABLE_NAME (
	id int(11) not null PRIMARY key auto_increment COMMENT 'id',
	sort int(11) not null default '0' comment '排序',	
 	create_time int(11) not null COMMENT '创建时间',
	update_time int(11) null comment '更新时间',
	delete_flag TINYINT(2) not null DEFAULT '0' comment '是否删除 0否未删除，1是已删除',
	state TINYINT(1) not null DEFAULT '1' COMMENT '状态是否可用, 0不可用，1可用',
	
	-- todo
	-- 其他字段
	
	remark varchar(255) null comment '备注'
)COMMENT '表注释' default charset=utf8 engine=INNODB;

创建数据库

/*
dbName ：数据库名称
*/
drop database if EXISTS dbName; 
create database dbName charset=utf8 collate=utf8_general_ci;

创建索引

/*
TABLE_NAME：表名
[INDEX|UNIQUE |FULLTEXT|SPATIAL] ： 索引类型
INDEX_NAME：索引名称
(col_1,col_2,...) ：建立索引的字段名称，一个或多个字段
*/
ALTER table TABLE_NAME add  [INDEX|UNIQUE |FULLTEXT|SPATIAL]  INDEX_NAME  (col_1,col_2,...);
 

判断集合中是否存在指定元素

/*
	判断字符是否在一个集合中
*/
set @list='a,b,c,d';-- 集合
-- 返回true 在集合中，false不在集合中
SELECT if (FIND_IN_SET('a', @list)>0,'true','false');

输入中文查找首拼

CREATE TABLE IF NOT EXISTS `tcosler` (
  `fPY` char(1) NOT NULL,
  `cBegin` int(11) NOT NULL,
  `cEnd` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

INSERT INTO `tcosler` (`fPY`, `cBegin`, `cEnd`) VALUES
('A', 45217, 45252),
('B', 45253, 45760),
('C', 45761, 46317),
('D', 46318, 46825),
('E', 46826, 47009),
('F', 47010, 47296),
('G', 47297, 47613),
('H', 47614, 48118),
('J', 48119, 49061),
('K', 49062, 49323),
('L', 49324, 49895),
('M', 49896, 50370),
('N', 50371, 50613),
('O', 50614, 50621),
('P', 50622, 50905),
('Q', 50906, 51386),
('R', 51387, 51445),
('S', 51446, 52217),
('T', 52218, 52697),
('W', 52698, 52979),
('X', 52980, 53640),
('Y', 53689, 54480),
('Z', 54481, 55289),
('a', 97, 97),
('b', 98, 98),
('c', 99, 99),
('d', 100, 100),
('e', 101, 101),
('f', 102, 102),
('g', 103, 103),
('h', 104, 104),
('i', 105, 105),
('j', 106, 106),
('k', 107, 107),
('l', 108, 108),
('m', 109, 109),
('n', 110, 110),
('o', 111, 111),
('p', 112, 112),
('q', 113, 113),
('r', 114, 114),
('s', 115, 115),
('t', 116, 116),
('u', 117, 117),
('v', 118, 118),
('w', 119, 119),
('x', 120, 120),
('y', 121, 121),
('z', 122, 122),
('A', 65, 65),
('B', 66, 66),
('C', 67, 67),
('D', 68, 68),
('E', 69, 69),
('F', 70, 70),
('G', 71, 71),
('H', 72, 72),
('I', 73, 73),
('J', 74, 74),
('K', 75, 75),
('L', 76, 76),
('M', 77, 77),
('N', 78, 78),
('O', 79, 79),
('P', 80, 80),
('Q', 81, 81),
('R', 82, 82),
('S', 83, 83),
('T', 84, 84),
('U', 85, 85),
('V', 86, 86),
('W', 87, 87),
('X', 88, 88),
('Y', 89, 89),
('Z', 90, 90),
('0', 48, 48),
('1', 49, 49),
('2', 50, 50),
('3', 51, 51),
('4', 52, 52),
('5', 53, 53),
('6', 54, 54),
('7', 55, 55),
('8', 56, 56),
('9', 57, 57);


-- 文字
set @text = "张";

SELECT c. *
FROM tcosler c 
WHERE
    CONV(
        HEX(
            LEFT(
                CONVERT( @text
                USING gbk ) ,
            1 )
        ) , 16, 10
    )
 BETWEEN c.cBegin
 AND c.cEnd

mysql 异常解决

连接MySQL时提示 mysql unblock with 'mysqladmin flush-hosts

说明

此异常有可能是因为某个连接失败次数达到最大值，mysql限制了连接。

解决方法

1. 使用root登录mysql 命令行界面。
2. 使用以下sql语句查询最大失败连接数。

show global variables like 'max_connect_errors';

3. 使用以下sql语句查询缓存的连接对象失败连接数

select ip,host,sum_connect_errors from performance_schema.host_cache;

说明：查询结果中显示，host_cache缓存表中，IP为192.168.0.1的连接失败总数为104,大于默认最大连接失败数。

4. 执行以下sql语句刷新缓存表中的数据

flush hosts;

5. 刷新成功之后，再次查询连接失败数。低于最大值即可。