mysql哟普话_MySQL 常用SQL语句

CREATE DATABASE `test` DEFAULT CHARACTER SETutf8 COLLATE utf8_general_ci;#创建数据库

SHOW DATABASES;#显示所有数据库

SHOWCREATE DATABASE`test`;#显示创建数据库语句

SHOW ENGINES;#显示数据库引擎DROP DATABASE`test`;#删除数据库

SHOW VARIABLESLIKE '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`

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

#查看表创建语句DESC`log_user_login`;

SHOW TABLES;

SHOWCREATE 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 NULLAFTER `login_ip`;

#查看索引

SHOWINDEX FROM`test`;

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

#删除索引DROP INDEX idx ONtbl;

#表删除字段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 PROCEDURESelectNow()BEGIN

SELECTNOW();END;CREATE PROCEDURE CountProduct(OUT c INT)BEGIN

SELECT COUNT(*) INTO c FROMproducts;END;

#创建函数CREATE FUNCTIONSelectNOW()RETURNS DATETIME

RETURN (SELECTNOW());

#创建触发器CREATE TRIGGER`trig_course_add_student`

BEFOREINSERT ON `wp_user_xp` FOREACH ROWupdate wp_posts set student_count = student_count + 1 where id =new.course_id

#查看触发器

SHOW TRIGGERS;

#SELECT语句SELECTc.id,o.numFROM orders o INNER JOINcustomers cON c.id =o.customer_idORDER BYc.idSELECT * 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 FROMordersSELECT num FROM tbl1 WHERE num > ANY(SELECT num FROMtbl2)SELECT num FROM tbl1 WHERE num > ALL(SELECT num FROMtbl2)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 FROMproducts;OPENcursor_product;FETCH cursor_product INTOname,price;CLOSEcursor_product;

#常用函数UPDATE `test` SET `title` = REPLACE(`title`,' ','');#字符串替换SELECT ABS(2),ABS(-2);#绝对值SELECT PI();#查看PISELECT 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();#日期时间函数SELECTUNIX_TIMESTAMP(),FROM_UNIXTIME(UNIX_TIMESTAMP());#时间戳SELECT MONTH('2019-2-19'),MONTHNAME('2019-2-19'),MONTH(NOW());#月份函数SELECTDAYNAME(NOW()),DAYOFWEEK(NOW());#星期函数SELECT YEAR(NOW());#年函数SELECTQUARTER(NOW());#季度函数SELECTMINUTE(NOW());#分钟函数SELECTSECOND(NOW());#秒函数SELECT IF(1>2,1,2);#IF条件函数SELECT IFNULL(1,2),IFNULL(NULL,2);#空判断条件函数SELECTVERSION();#获取MYSQL版本号SELECTCONNECTION_ID();#获取当前用户的连接数

SHOW PROCESSLIST;#查看用户连接信息SELECT USER();#获取登录用户名SELECT MD5('abc'),SELECT DECODE(ENCODE('secret','key'),'key');#加密解密函数key 为密钥SELECT CAST(100 AS CHAR(2)),CONVERT(NOW(),TIME);#改变数据类型SELECTLAST_INSERT_ID();#获取最后插入的id，自增表