mysql常用语句及操作

mysql常用命令行级操作
1.show databases – 查看当前mysql服务中的的数据库
2.use database;show tables ；–查看所使用的数据库中的所有的表和视图
3.sqlyog中任何操作在history窗口都有相应的语句生成
4.查看表结构的方法和语句：
（1）任何表对象执行完语句，都能在Object窗口看到对应的表结构信息以及建表语句
（2）select * from information_schema.columns where – table_schema =‘db’ and table_name = ‘testtable’ ;
5.mysql中改变字段名或者字段属性统一 用change；oracle中改名用rename，改属性用modify
6.利用concat函数生成批量删除表的语句
select concat("drop table ",table_name,’;’) from TABles
where table_name REGEXP ‘^vo’ and TABLE_SCHEMA = ‘zsx_sql’ —table_schema其实就是表所处的数据库实例计划
7.查询表中列信息 select * from COLUMNs where table_name = ‘test’ and table_schema=‘zsx_sql’
8查询某张表的索引 select * from STATISTICS where TABLE_SCHEMA = ‘zsx_sql’ and table_name = ‘test’
9.mysql中的正则不识别\d select * from test where user_id rlike或者regexp ‘¹.*’
10.mysql中取当前日期时间，以及按照格式显示日期时间
select curdate(),CURTIME(),now(),UNIX_TIMESTAMP(now()),
week(now()),DATE_FORMAT(now(),’%Y/%m/%d %h:%i:%s’) from testtable
11.mysql中取当前数据库名、当前用户名、当前数据库版本的sql
select database(),version(),user() from user limit 0 ,1
12.mysql默认数据库自动提交 ，可以设置本次客户端不自动提交： set autocommit=0 关闭自动提交 ；查看是否自动提交 ：show VARIABLES like ‘%autocommit%’
https://blog.csdn.net/xiaoyi23000/article/details/51690601/
13.查看当前数据库的字符集 ，查服务器字符集则换database为server
show variables like ‘character_set_database’
14.手动锁表的语句 lock tables tablename read/write;
unlock tables ;
15.查看当前 sql_mode select @@sql_mode 有可能在sqlyog中查不出，得使用dos；
设置不同的sql_mode 语句：set session/global sql_mode=‘模式’ session表示本次会话有效，global表示全局有效
16.已有的表上加分区
alter table testpartition partition by RANGE/List (或者加上column表示非整数分区)(id)
(
PARTITION p3 VALUES LESS THAN/IN (4) ,
PARTITION p5 VALUES LESS THAN/IN (6) ,
);
17.不报错的删除一张表 drop table if exists testpartition ;
18-- 删除分区数据
alter table testpartition drop partition p20190414
19.-- 查看某张表的 分区情况
select * from information_schema.PARTITIONS where table_name=‘testpartition’
20select sleep(5)线程阻塞5s ,模拟阻塞
21 group by 字段 with rollup 能够展示分组的整体统计值

22.mysql中查看锁表的语句

#查看锁表
show OPEN TABLES  where In_use>0
#查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
show status like 'table%'
#Table_locks_immediate  指的是能够立即获得表级锁的次数
#Table_locks_waited  指的是不能立即获取表级锁而需要等待的次数

23.查看最大连接数 以及当前连接状态

#最大连接数
show variables like '%max_connection%'
#查看所有进程连接情况
show full processlist

24. 存储过程批量造数

首先检查 否则会报错
 show global variables like 'log_bin_trust_function_creators';
 set global log_bin_trust_function_creators=1;
#--创建随机字符串
DROP FUNCTION IF EXISTS rand_string;
delimiter $$
CREATE FUNCTION rand_string (n INT) RETURNS VARCHAR (255)
BEGIN
    DECLARE chars_str VARCHAR (100) DEFAULT 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM' ;
    DECLARE return_str VARCHAR (255) DEFAULT '' ;
    DECLARE i INT DEFAULT 0 ;
    WHILE i < n DO
        SET return_str = concat(return_str,substring(chars_str,floor(1 + rand() *52),1) );
        SET i = i + 1 ;
    END WHILE; 
    RETURN return_str;
END$$

#--创建随机数 
DROP FUNCTION IF EXISTS rand_num;
delimiter $$
CREATE FUNCTION rand_num (n INT) RETURNS INT (15)
BEGIN
    DECLARE i INT DEFAULT 1 ;
    DECLARE total INT DEFAULT 10 ;
    DECLARE random INT DEFAULT 0 ;
    WHILE i < n DO
        SET total = total * 10 ;
        SET i = i + 1 ;
    END WHILE;
    SET random = FLOOR(RAND() * total) ; RETURN random ;
END$$

#--创建存储过程
DROP PROCEDURE IF EXISTS student_insert_procedure;
DELIMITER $$
CREATE PROCEDURE student_insert_procedure (count INT)
BEGIN
    DECLARE n INT DEFAULT 0;
    DECLARE days INT DEFAULT 0;
    WHILE n < count DO
        SET days = rand_num (3);
             INSERT INTO `world`.`student`
(
`stuname`,
`birthday`,
`stuno`,
`comment`)
VALUES
(
rand_string (5),
DATE_ADD(CURDATE(), INTERVAL days DAY),
rand_num(5),
rand_string (15));
COMMIT;
        SET n = n + 1;
    END WHILE;
COMMIT;
END $$
#执行
call student_insert_procedure(500);

---

1. 0-9 ↩︎