/*MYSQL常用DDL语句*//*创建表*/DROP TABLE IF EXISTS `student`;CREATE TABLE `student`(  `sid` INT(8),  `sname` VARCHAR(128),  `spasswd` VARCHAR(128),  `sex` VARCHAR(4),  `sClass` VARCHAR(20))/*添加列*/ALTER TABLE `student` ADD COLUMN `ss` INT(8);/*删除列*/ALTER TABLE `student` DROP COLUMN `ss`;/*添加主键,设置自增*/ALTER TABLE `student` ADD PRIMARY KEY(`sid`);ALTER TABLE `student` MODIFY COLUMN `sid` INT AUTO_INCREMENT;/*添加索引*/ALTER TABLE `student` ADD UNIQUE INDEX(sname) ;CREATE INDEX `index_sname` ON `student`(`sname`,`sex`);/*删除索引*/ALTER TABLE `student` DROP INDEX `sname`;/*修改索引*/ALTER TABLE `student` DROP INDEX `index_sname`;ALTER TABLE `student` ADD UNIQUE INDEX `index_sname`(`sname`);/*修改列名*/ALTER TABLE `student` CHANGE COLUMN `sClass` `class` VARCHAR(16);/*修改字段数据类型*/ALTER TABLE `student` MODIFY COLUMN `sex` VARCHAR(2);/*查看索引*/SHOW INDEX FROM student;/*MYSQL  独有的DML语句*//*插入数据*/INSERT INTO `student`(`sname`,`spasswd`,`sex`,`class`) VALUES('xiaoxiaoxin8','112344','男','一年级');/*插入冲突则修改*/INSERT INTO `student`(`sid`,`sname`,`spasswd`,`sex`,`class`) VALUES(1,'xiaoxiaoxin8','112344','m','1')ON DUPLICATE KEY UPDATE `sname`='xiaoxiaoxin8',`spasswd`='112344',`sex`='m',class='1';/*插入多行*/INSERT INTO `student`(`sid`,`sname`,`spasswd`,`sex`,`class`) VALUES(1,'xiaoxiaoxin1','112344','m','1'),(2,'xiaoxiaoxin23','112344','m','2'),(2,'xiaoxiaoxinxx','112344','w','1'),(3,'xiaoxiaoxin5','112344','m','1')ON DUPLICATE KEY UPDATE`sname` = VALUES(`sname`),`spasswd`=VALUES(`spasswd`),`sex`=VALUES(`sex`),`class`=VALUES(`class`);/*插入数据后返回主键*/SELECT LAST_INSERT_ID() AS `sid`;INSERT INTO `student`(`sname`,`spasswd`,`sex`,`class`) VALUES('xiaoxiaoxin9','112344','m','1');

/*创建存储过程*/DROP PROCEDURE pro10;DELIMITER //CREATE PROCEDURE pro10()BEGINDECLARE i INT;SET i=0;WHILE i<100000 DOINSERT INTO indextest(spwd,filename,state,sname) VALUES('password','filename',i,'xiaoxiaoxin8');SET i=i+1;END WHILE;END;//CALL pro10();

/**通过hygeia_table表，统计医疗机构*/

SELECT COUNT(DISTINCT t.table_name) FROM (

SELECT DISTINCT SUBSTRING(table_name,6,6) table_name FROM hygeia_tables WHERE table_name LIKE 'kc21%'

UNION ALL

SELECT DISTINCT SUBSTRING(table_name,6,6) table_name FROM hygeia_tables WHERE table_name LIKE 'kc22%'

UNION ALL

SELECT DISTINCT SUBSTRING(table_name,6,6) table_name FROM hygeia_tables WHERE table_name LIKE 'kc27%'

UNION ALL

SELECT DISTINCT SUBSTRING(table_name,6,6) table_name FROM hygeia_tables WHERE table_name LIKE 'kc28%'

UNION ALL

SELECT DISTINCT SUBSTRING(table_name,6,6) table_name FROM hygeia_tables WHERE table_name LIKE 'kcd1%'

UNION ALL

SELECT DISTINCT SUBSTRING(table_name,6,6) table_name FROM hygeia_tables WHERE table_name LIKE 'kcd2%'

UNION ALL

SELECT DISTINCT SUBSTRING(table_name,6,6) table_name FROM hygeia_tables WHERE table_name LIKE 'kcd7%'

UNION ALL

SELECT DISTINCT SUBSTRING(table_name,6,6) table_name FROM hygeia_tables WHERE table_name LIKE 'kcd8%'

UNION ALL

SELECT DISTINCT SUBSTRING(table_name,6,6) table_name FROM hygeia_tables WHERE table_name LIKE 'kce4%'

UNION ALL

SELECT DISTINCT SUBSTRING(table_name,6,6) table_name FROM hygeia_tables WHERE table_name LIKE 'kce5%'

) t

 

关闭mysql的bin log日志

1、用root账号登录MySQL

2、查看当前mysql中的bin log日志文件

   show binary logs;

3、重置并删除mysql的bin log日志

   reset master 

4、修改/etc/my.cnf

   在log-bin=前面加一个#号即可

   #log-bin=/mysql/log/mysql-bin

5、重启mysql数据库

 

/*kill  批量kill */select concat('KILL ',id,';') from information_schema.processlist where user='root';select concat('KILL ',id,';') from information_schema.processlist where user='root' into outfile '/tmp/a.txt';source /tmp/a.txt; /*查看链接数*/ show processlist; /*查看表被占用数*/ show open tables where in_use > 1;