MySQL知识点技能点总结贴--个人版！

mchdba

-- ==================
-- 设置创建函数功能
-- ==================

导入存储函数出错：
Error Code: 1418
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

(you *might* want to use the less safe log_bin_trust_function_creators variable)

【】解决方法：
1，在线设置
SET GLOBAL log_bin_trust_function_creators = 1

2，配置文件设置
vim my.cnf文件在里面添加
[mysqld]
log_bin_trust_function_creators=1
设置完了，service mysql restart;重启mysql服务。

mchdba

-- ===============================================================
-- 设置mysql root账号密码
-- ===============================================================
shell>/usr/local/mysql/bin/mysqladmin –u root password ‘password’

mchdba

-- ========================
-- innodb 设置单独的数据文件
-- ========================
[msyqld]
innodb_file_per_table

mchdba

-- =============================================================================================
-- 设置 删除A、B、C表的记录
-- =============================================================================================

删除A、C和B表的纪录，一起删除，不用not in或者not exists之类的子查询sql写法。

Delete A,B,C FROM trans A  Join Transdetail B on B.TransID=A.TransID  Join TransPayment C on C.Transid=A.TransID
where A.businessdate<date_add('2011-04-08',interval -3 month);

mchdba

-- =============================================================================================
-- mysql 添加外键
-- =============================================================================================
CREATE TABLE `child` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `pid` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `parent` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ALTER TABLE `test`.`child` CHANGE COLUMN `id` `id` INT(11) NOT NULL DEFAULT NULL , ADD PRIMARY KEY (`id`);
alter table parent change column id id int not null  primary key;
alter table child add constraint fk_pid foreign key child(pid) references parent(id) on update cascade;


-- =============================================================================================
-- mysql 添加索引
-- =============================================================================================

ALTER TABLE `test`.`company` ADD INDEX `ixu_csf_code ` (`csf_code` ASC) ;
ALTER TABLE `test`.`equtity` ADD INDEX ixu_exhg_board (cpny_id asc, exhg_cd asc, board_cd asc);

mchdba

不知道为什么，已经习惯在mysql命令行里面，修改表结构了。

mchdba

-- ===============================================================
-- MySQL Workbench 导出 csv乱码
-- ===============================================================

先点击保存按钮，下载lost_cmp.csv，这个时候文件时utf-8编码的，用excel打开，乱码。
先吧lost_cmp以文本方式打开，你就会看到里面没有乱码是正常中文汉字，这个时候选择
另存为，在编码选择ansi编码，文件类型上选择所有类型，然后点击保存按钮，然后直接覆盖，搞定。
你再打开lost_cmp.csv的时候，就正常了。

mchdba

-- ===============
-- kill掉当前的sql线程命令
-- ===============
kill query 97132;



-- ================
-- 查看mysql连接数
-- ================

最大允许连接数：show variables like '%max_connections%';
当前连接数：show status like '%threads_connected%';
设置：
[mysqld]
max_connections=32000

mchdba

-- =======================================================
-- 创建一张比较齐全的表
-- =======================================================

1        创建一张新闻表csf_news,主键id自增, 字段2 新闻标题 item, 新闻内容content(内容可能超过500W汉字), 新闻状态status

（-2无效状态、-1未编辑状态、1审核状态、2审核完成状态、3再修改状态、4重新审核状态）只允许录入括号里面允许的几个状态值，

录入人 insert_user， 录入时间insert_time，审核人check_user，审核时间check_time，修改人update_user，修改时间update_time

。
其中录入人、审核人、修改人全为外键 关联用户表use的id字段。
某个业务场景，经常根据录入人、审核人、修改人、新闻状态、录入时间（审核人查询的次数最多、录入人次之，修改人再次之）进行

查询。
要求：写出较合理的建表的sql语句。

CREATE TABLE `csf_news` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item` varchar(512) NOT NULL comment '标题',
  `content` longtext comment '新闻内容',
  `status`  enum('-2','-1','1','2','3','4') comment '-2无效状态、-1未编辑状态、1审核状态、2审核完成状态、3再修改状态、

4重新审核状态',
  `insert_user` int(11) NOT NULL comment ‘录入人’,
  `insert_time` datetime NOT NULL comment ‘录入时间’,
  `check_user` int(11) NOT NULL comment '审核人 ',
  `check_time` datetime NOT NULL comment '审核时间',
  `update_user` int(11) DEFAULT NOT NULL comment '修改人',
  `update_time` datetime DEFAULT NOT NULL comment '修改时间',
  PRIMARY KEY (`id`),
  KEY `in_user` (`status`,`check_user`,`insert_user`,`update_user`),
  KEY `fk_inUserId` (`insert_user`),
  KEY `fk_chUserId` (`check_user`),
  KEY `fk_upUserId` (`update_user`),
  CONSTRAINT `fk_upUserId` FOREIGN KEY (`update_user`) REFERENCES `user` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_chUserId` FOREIGN KEY (`check_user`) REFERENCES `user` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_inUserId` FOREIGN KEY (`insert_user`) REFERENCES `user` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='新闻表'

mchdba

-- =======================================================
-- 还原my.cnf
-- =======================================================
[root@ldap-mysql-svn-trac var]# cd /usr/local/mysql/share/mysql/
[root@ldap-mysql-svn-trac mysql]# find |grep my
./mysql_system_tables_data.sql
./my-huge.cnf
./mysql-log-rotate
./mysql_fix_privilege_tables.sql
./mysql_test_data_timezone.sql
./my-small.cnf
./mysql_system_tables.sql
./mysqld_multi.server
./mysql.server
./my-innodb-heavy-4G.cnf
./my-large.cnf
./my-medium.cnf

[root@ldap-mysql-svn-trac mysql]# cp ./my-large.cnf  /etc/my.cnf

[root@ldap-mysql-svn-trac mysql]# service mysqld restart