MYSQL基础

/*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()
BEGIN
DECLARE i INT;
SET i=0;
WHILE i<100000 DO
INSERT 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;