MySQL 常用SQL语句

CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;#创建数据库
SHOW DATABASES;#显示所有数据库
SHOW CREATE DATABASE `test`;#显示创建数据库语句
SHOW ENGINES;#显示数据库引擎
DROP DATABASE IF EXISTS `test`;#删除数据库
SHOW VARIABLES LIKE 'storage_engine';


#删除表
DROP TABLE IF EXISTS `log_user_login`;

#创建表
CREATE TABLE `log_user_login`(
	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自动编号',
	`user_id` INT UNSIGNED NOT NULL COMMENT '会员 id',	
	`login_time` DATETIME NOT NULL DEFAULT NOW() COMMENT '登录时间',
	`login_ip` VARCHAR(15) NOT NULL DEFAULT '0.0.0.0' COMMENT '登录 IP',
	`platform` TINYINT NOT NULL DEFAULT 0 COMMENT '登录平台[0 PC|1 手机浏览器|2 微信安卓端|3 微信IOS端]',
	PRIMARY KEY (`id`),
	CONSTRAINT `fk_user_user_login` FOREIGN KEY(`user_id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_general_ci COMMENT '[商城管理系统]会员登录记录表';

#复制表结构
CREATE TABLE `log_user_login_scheme` LIKE `log_user_login`

#复制表结构和数据
CREATE TABLE tbl SELECT * FROM tbl2;
INSERT INTO tbl(col) SELECT col FROM tbl2 WHERE ...

#查看表创建语句
DESC `log_user_login`;
SHOW TABLES;
SHOW CREATE TABLE `log_user_login`;

#表重命名
ALTER TABLE `log_user_login` RENAME `user_login_log`;

#表结构修改 ALTER TABLE <表名> MODIFY <字段名> <数据类型>
ALTER TABLE `log_user_login` MODIFY `user_id` BIGINT;

#表字段名重命名 ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <数据类型>
ALTER TABLE `log_user_login` CHANGE `login_IP` VARCHAR(20);

#显示表结构
DESC `log_user_login`;

#表添加字段
ALTER TABLE `log_user_login` ADD `test` VARCHAR(20) NOT NULL AFTER `login_ip`;

#查看索引
SHOW INDEX FROM `test`;

#表创建索引
CREATE INDEX idx ON tbl(col);
CREATE UNIQUE INDEX idx tbl(col);
ALTER TABLE tbl ADD INDEX `idx_col`(`col`);

# 删除索引
DROP INDEX idx ON tbl;

# 表删除字段
ALTER TABLE `log_user_login` DROP `test`;

# 表存储引擎修改
ALTER TABLE `log_user_login` ENGINE=InnoDB;

# 表删除外键
ALTER TABLE `log_user_login` DROP FOREIGN KEY `fk_xxx`

# 创建存储过程
CREATE PROCEDURE SelectNow()
BEGIN
	SELECT NOW();
END;

# 调用存储过程
call selectnow();

CREATE PROCEDURE CountProduct(OUT c INT)
BEGIN
	SELECT COUNT(*) INTO c FROM products;
END;

#创建函数
CREATE FUNCTION SelectNOW() 
RETURNS DATETIME
	RETURN (SELECT NOW());

#创建触发器
CREATE TRIGGER `trig_course_add_student` 
	BEFORE INSERT ON `wp_user_xp` FOR EACH ROW 
    update wp_posts set student_count = student_count + 1 where id = new.course_id

#查看触发器
SHOW TRIGGERS;

#SELECT 语句
SELECT c.id,o.num 
FROM orders o  INNER JOIN customers c
ON c.id = o.customer_id
ORDER BY c.id


SELECT * FROM employee WHERE (YEAR(NOW())-YEAR(hireday)) >=15;#获取工龄超过15年的员工
SELECT * FROM products WHERE title REGEXP '^iphone'
SELECT * FROM products WHERE price>25 AND EXISTS(SELECT name FROM suppliers WHERE id=25)
SELECT num FROM tbl WHERE num IS NOT NULL;
SELECT DISTINCT user_id FROM orders;
SELECT order_no FROM orders LIMIT 5,5;
SELECT order_no FROM orders LIMIT 5 OFFSET 5;
SELECT num FROM tbl1 WHERE num > ANY(SELECT num FROM tbl2)
SELECT num FROM tbl1 WHERE num > ALL(SELECT num FROM tbl2)
SELECT id FROM orders WHERE order_no IN('S12121,','S121962')
SELECT * FROM products WHERE name REGEXP '^iphone'

#使用光标
DECLARE cursor_product CURSOR FORM SELECT name,price FROM products;
OPEN cursor_product;
FETCH cursor_product INTO name,price;
CLOSE cursor_product;

#常用函数
UPDATE `test` SET `title` = REPLACE(`title`,' ','');#字符串替换
SELECT ABS(2),ABS(-2);#绝对值
SELECT PI();#查看PI
SELECT SQRT(9);#求平方根
SELECT MOD(31,8);#求余数
SELECT CEIL(-4.2),CEIL(4.2);#求整数 -4,5
SELECT FLOOR(-4.2),FLOOR(4.2);#求整数 -5,4
SELECT RAND(),RAND(1),RAND(1);#生成随机数，种子相同随机数相同
SELECT ROUND(-3.14),ROUND(-4.56),ROUND(1.21);#四舍五入
SELECT TRUNCATE(19.99,-1),TRUNCATE(19.99,0),TRUNCATE(19.99,1);#截取保留指定小数点位值
SELECT SIGN(-2),SIGN(0),SIGN(2);#符号函数、
SELECT POW(2,3);#幂函数
SELECT EXP(3);#求自然数e的乘方
SELECT LOG(3);#求自然数e的对数
SELECT LOG10(3);#求基数为10的对数
SELECT RADIANS(90);#角度转弧度
SELECT DEGREES(PI());#弧度转角度
SELECT SIN(1),ROUND(SIN(PI()));#求正弦
SELECT ASIN(SIN(1)),ASIN(3);#求反正弦
SELECT CHAR_LENGTH('我爱你中国'),CHAR_LENGTH('abcde');#求字符个数5,5
SELECT LENGTH('我爱你中国'),LENGTH('abcde');#求字符串长度,15,5
SELECT CONCAT('ABC','DE','FG','HI'),CONCAT('ABC',NULL,'DE');#合并字符串，'ABCDEFGHI'，NULL
SELECT INSERT('Question',2,4,'Change');#字符串部分替换，'QChangeion'
SELECT LEFT('I Love China',6),RIGHT('I Love China',5);#获取子字符串
SELECT LPAD('hello',4,'?*'),LPAD('hello',8,'?*'),RPAD('hello',4,'?*'),RPAD('hello',8,'?*');#字符串填充到指定长度
SELECT LTRIM('     我爱你中国      '),RTRIM('     我爱你中国      '),RTRIM(LTRIM('     我爱你中国      ')),TRIM(' 我爱你中国 ');#删除空格
SELECT TRIM('ab' FROM 'abfijefiabereabReEFab');#删除两端子字符串
SELECT REPEAT('mysql',3);#字符串重复
SELECT CONCAT('(', SPACE(6),')');#空格函数
SELECT STRCMP('txt','txtab'),STRCMP('txtab','txt'),STRCMP('txt','txt');#字符串比较
SELECT SUBSTRING('I love China',3,5),SUBSTRING('I love China',-5);#截取子字符串
SELECT REVERSE('abc');#字符串逆序
SELECT NOW(),CURRENT_DATE(),CURRENT_TIME();#日期时间函数
SELECT UNIX_TIMESTAMP(),FROM_UNIXTIME(UNIX_TIMESTAMP());#时间戳
SELECT MONTH('2019-2-19'),MONTHNAME('2019-2-19'),MONTH(NOW());#月份函数
SELECT DAYNAME(NOW()),DAYOFWEEK(NOW());#星期函数
SELECT YEAR(NOW());#年函数
SELECT QUARTER(NOW());#季度函数
SELECT MINUTE(NOW());#分钟函数
SELECT SECOND(NOW());#秒函数
SELECT IF(1>2,1,2);#IF 条件函数
SELECT IFNULL(1,2),IFNULL(NULL,2);#空判断条件函数
SELECT VERSION();#获取MYSQL版本号
SELECT CONNECTION_ID();#获取当前用户的连接数
SHOW PROCESSLIST;#查看用户连接信息
SELECT USER();#获取登录用户名
SELECT MD5('abc'),SELECT DECODE(ENCODE('secret','key'),'key');#加密解密函数key 为密钥
SELECT CAST(100 AS CHAR(2)),CONVERT(NOW(),TIME);#改变数据类型
SELECT LAST_INSERT_ID();#获取最后插入的id，自增表
SELECT userId,GROUP_CANCAT(title ORDER BY title DESC SEPARATOR ' ') from user_title;
SELECT * from movies ORDER BY IF(ISNULL(actors),0,1),actors,price;
SELECT actors,sum(price) FROM movies GROUP BY actors WITH ROLLUP;#分组汇总
with m1 as (select * from movices where price >50),
m2 as (select * from movices where price>=65)
select * from m1 where m1.id not in (select m2.id from m2) and m1.actors = 'xxx';# 子查询提前
INSERT IGNORE INTO xxx; 
REPLACE INTO xxx ;