mysql语句

常用语句

修改数据库视图存储过程权限：

grant all privileges on *.* to ''@"%" identified by "root";
flush privileges;

grant all privileges on *.* to root@"%" identified by ".";

grant all PRIVILEGES on *.* to 'root'@'%';

SELECT * FROM mysql.user;
select user, grant_priv, host from mysql.user;

以管理员身份运行cmd,命令方式登录mysql数据库，修改root账号host为%
mysql -u root -p
mysql>use mysql;
mysql>update user set host = ’%’ where user = ’root’;mysql>select host, user from user; 
mysql>flush privileges;

-- 检查并删除已存在的数据库
DROP DATABASE IF EXISTS `date_time_test`;

-- 创建数据库
CREATE DATABASE `date_time_test`;

-- 使用新创建的数据库
USE `date_time_test`;

-- 检查并删除已存在的表
DROP TABLE IF EXISTS sales_orders;

-- 创建表
CREATE TABLE sales_orders (
id INT AUTO_INCREMENT PRIMARY KEY,
create_time DATETIME,
order_no VARCHAR(50)
);

-- 删除存储过程
DROP PROCEDURE InsertTestData IF EXISTS InsertTestData
-- 定义分隔符
DELIMITER $$

-- 创建插入数据的存储过程
CREATE PROCEDURE InsertTestData(IN num_rows INT)
BEGIN
DECLARE i INT DEFAULT 1;
START TRANSACTION; -- 开始事务
WHILE i <= num_rows DO
INSERT INTO sales_orders (create_time, order_no)
VALUES (NOW() - INTERVAL FLOOR(RAND() * 365) DAY, CONCAT('Order_', LPAD(i, 8, '0')));
IF i % 10000 = 0 THEN -- 每10000条数据提交一次事务
COMMIT;
START TRANSACTION;
END IF;
SET i = i + 1;
END WHILE;
COMMIT; -- 最后提交剩余的事务
END$$

-- 恢复默认分隔符
DELIMITER ;

-- 调用存储过程以生成数据
CALL InsertTestData(100000);

 

-- 删除存储过程
drop procedure if exists InsertTestData

 

-- 年份+流水号: 20200001
DECLARE @code INT=123,@year VARCHAR(50)=CAST(YEAR(GETDATE()) AS VARCHAR(50))
-- SELECT @year
-- SELECT RIGHT('0000'+CAST(@code AS VARCHAR(50)),4)
-- SELECT @year+ RIGHT('0000'+CAST(@code AS VARCHAR(50)),4)
-- SELECT @year+ CAST(@code AS VARCHAR(50))

SELECT @year + (CASE WHEN LEN(@code)<4 THEN RIGHT('0000'+CAST(@code AS VARCHAR(50)),4) ELSE CAST(@code AS VARCHAR(50)) END)

SELECT LPAD('2',3,0);

SELECT SYSDATE(),rownum FROM dual ;

declare @cx int DEFAULT VALUES 0;
set @cx = 1;
SELECT @cx;
--循环
SET @i = 0;
WHILE @i < 10 DO
-- 在此处编写需要执行的逻辑代码
SELECT @i;
SET @i = @i + 1;
END WHILE;

 

-- 获取日期
SELECT NOW();
SELECT CURDATE();
SELECT DATE(NOW());
SELECT CAST(NOW() AS DATE) ;
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00') ;
SELECT DATE_ADD(ADDDATE(DATE(NOW()), 1), INTERVAL -1 SECOND);
-- 日期计算
BEGIN
set @j = -100;
SELECT DATE_ADD(DATE(NOW()), INTERVAL @j DAY) ;
END
-- 查询数据编码
SHOW VARIABLES LIKE '%character_set%';

--查询数据库表

SHOW TABLE STATUS FROM your_database_name;

-- 查数据库表和记录

SELECT t.TABLE_SCHEMA,t.table_name, TABLE_ROWS
FROM information_schema.tables AS t
JOIN information_schema.table_constraints AS tc
ON t.table_schema = tc.table_schema
AND t.table_name = tc.table_name
WHERE -- tc.constraint_type = 'PRIMARY KEY' AND
tc.TABLE_SCHEMA = 'your_database_name'
ORDER BY t.TABLE_ROWS DESC;

-- 方法一
SELECT TABLE_SCHEMA,table_name, TABLE_ROWS -- (SELECT COUNT(*) FROM table_name) AS record_count
FROM information_schema.tables
WHERE TABLE_SCHEMA = 'your_database_name'
AND table_type = 'BASE TABLE' ORDER BY TABLE_ROWS DESC;

-- 方法二
SELECT t.table_schema,t.table_name, COUNT(*) AS record_count
FROM information_schema.tables AS t
JOIN information_schema.table_constraints AS tc
ON t.table_schema = tc.table_schema
AND t.table_name = tc.table_name
WHERE tc.constraint_type = 'PRIMARY KEY'
AND t.table_schema = 'your_database_name'
GROUP BY t.table_name,t.table_schema;

 

-- 导出表结构
SELECT
COLUMN_NAME 列名,
COLUMN_TYPE 数据类型,
DATA_TYPE 字段类型,
CHARACTER_MAXIMUM_LENGTH 长度,
IS_NULLABLE 是否为空,
COLUMN_DEFAULT 默认值,
COLUMN_COMMENT 备注
FROM
INFORMATION_SCHEMA.COLUMNS
where
-- developerclub为数据库名称，到时候只需要修改成你要导出表结构的数据库即可
table_schema ='developerclub'
AND
-- article为表名，到时候换成你要导出的表的名称
-- 如果不写的话，默认会查询出所有表中的数据，这样可能就分不清到底哪些字段是哪张表中的了，所以还是建议写上要导出的名名称
table_name = 'tablename' ORDER BY ordinal_position;