mysql 常用命令技巧大全

#删除数据库DROP DATABASE IF  EXISTS learn;#创建数据库CREATE DATABASE learn CHARSET utf8;#选择数据库USE learn;#删除数据库表DROP TABLE IF  EXISTS tb1;DROP TABLE IF  EXISTS tb2;DROP TABLE IF  EXISTS tb3;#创建表CREATE TABLE tb1(id INT(11) AUTO_INCREMENT NOT NULL COMMENT '主键',nickname VARCHAR(50) COMMENT '昵称',create_time DATETIME NOT NULL COMMENT '创建时间',update_time TIMESTAMP  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',PRIMARY KEY(id));#存在则不执行创建表CREATE TABLE IF NOT EXISTS tb1(id INT(11) UNSIGNED  AUTO_INCREMENT NOT NULL COMMENT '主键',#UNSIGNED 不使用负数,扩大正数上限nickname VARCHAR(50) COMMENT '昵称',create_time DATETIME NOT NULL COMMENT '创建时间',update_time TIMESTAMP  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',PRIMARY KEY(id)#指定主键);CREATE TABLE IF NOT EXISTS tb2(id INT(11) UNSIGNED  AUTO_INCREMENT NOT NULL COMMENT '主键',#UNSIGNED 不使用负数,扩大正数上限username VARCHAR(50) NOT NULL COMMENT '账号',pass_word VARCHAR(50) NOT NULL COMMENT '密码',create_time DATETIME NOT NULL COMMENT '创建时间',update_time TIMESTAMP  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',PRIMARY KEY(id)#指定主键);#新增字段ALTER TABLE tb1 ADD COLUMN user_role INT(2) COMMENT '用户角色';#COLUMN可省略#修改以存在列ALTER TABLE tb1 MODIFY user_role INT(2) DEFAULT '0';#可以增加字符类型的列宽度#可以增加数值类型的宽度和精度#只有空列或空表才可以减少列宽度#空列才可以改变数据类型#改变列的默认值只会影响以后的操作#新增字段ALTER TABLE tb1 ADD COLUMN test1 INT(2) COMMENT '测试删除列';#删除字段 COLUMN可省略ALTER TABLE tb1 DROP COLUMN test1;#插入数据INSERT INTO tb1 (id,nickname,create_time,user_role) VALUES (1,'张三',NOW(),1);INSERT INTO tb1 (id,nickname,create_time,user_role) VALUES (3,'王五',NOW(),1);INSERT INTO tb1 (id,nickname,create_time,user_role) VALUES (4,'牛六',NOW(),1);INSERT INTO tb1 (id,nickname,create_time,user_role) VALUES (5,'陈7',NOW(),2);INSERT INTO tb1 (id,nickname,create_time,user_role) VALUES (6,'陈8',NOW(),2);INSERT INTO tb1 (id,nickname,create_time,user_role) VALUES (7,'陈9',NOW(),2);INSERT INTO tb1 (id,nickname,create_time,user_role) VALUES (8,'赵10',NOW(),3);INSERT INTO tb1 (id,nickname,create_time,user_role) VALUES (9,'赵11',NOW(),4);INSERT INTO tb1 (id,nickname,create_time,user_role) VALUES (10,'赵12',NOW(),4);INSERT INTO tb2 (id,username,pass_word,create_time) VALUES (1,'111','111',NOW());INSERT INTO tb2 (id,username,pass_word,create_time) VALUES (2,'222','222',NOW());INSERT INTO tb2 (id,username,pass_word,create_time) VALUES (3,'333','333',NOW());INSERT INTO tb2 (id,username,pass_word,create_time) VALUES (4,'444','444',NOW());INSERT INTO tb2 (id,username,pass_word,create_time) VALUES (5,'555','555',NOW());INSERT INTO tb2 (id,username,pass_word,create_time) VALUES (7,'777','777',NOW());INSERT INTO tb2 (id,username,pass_word,create_time) VALUES (8,'888','888',NOW());INSERT INTO tb2 (id,username,pass_word,create_time) VALUES (9,'999','999',NOW());INSERT INTO tb2 (id,username,pass_word,create_time) VALUES (10,'101010','101010',NOW());CREATE TABLE IF NOT EXISTS tb3(id INT(11) UNSIGNED  AUTO_INCREMENT NOT NULL COMMENT '主键',#UNSIGNED 不使用负数,扩大正数上限username VARCHAR(50) NOT NULL COMMENT '账号',pass_word VARCHAR(50) NOT NULL COMMENT '密码',create_time DATETIME NOT NULL COMMENT '创建时间',update_time TIMESTAMP  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',PRIMARY KEY(id)#指定主键);#使用子查询复制表数据到另一个表INSERT INTO tb3 (id,username,pass_word,create_time,update_time) SELECT id,username,pass_word,create_time,update_time FROM tb2;#更新表UPDATE tb1 SET nickname='张三昵称被更新' WHERE id=1;#删除表行数据DELETE FROM tb3 WHERE id=1;#创建索引#添加PRIMARY KEY（主键索引） #ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) #添加UNIQUE(唯一索引) #ALTER TABLE `table_name` ADD UNIQUE (`column`) #添加INDEX(普通索引) #ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) #添加FULLTEXT(全文索引) #ALTER TABLE `table_name` ADD FULLTEXT ( `column`) #添加多列索引 #ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )#删除索引#DROP INDEX index_name ON talbe_name#ALTER TABLE table_name DROP INDEX index_name#ALTER TABLE table_name DROP PRIMARY KEY#视图SELECT * FROM tb1GROUP BY user_role;#创建视图CREATE VIEW group_tb1 AS SELECT * FROM tb1GROUP BY user_role;#使用视图SELECT * FROM group_tb1;#内连接(最常用)SELECT t1.id,t1.nickname,t1.user_role,t1.create_time,t2.username FROM tb1 t1INNER JOIN tb2 t2ON t1.id=t2.id;#左外连接SELECT t1.id,t1.nickname,t1.user_role,t1.create_time,t2.username FROM tb1 t1LEFT JOIN tb2 t2ON t1.id=t2.id;#右外连接SELECT t1.id,t1.nickname,t1.user_role,t1.create_time,t2.username FROM tb1 t1RIGHT JOIN tb2 t2ON t1.id=t2.id;#全外连接(mysql不支持)#运算符 + - * /  ()SELECT 1+1+t1.user_role FROM tb1 t1;#字符串连接SELECT CONCAT_WS('-',t1.id,t1.nickname)  FROM tb1 t1;#条件限制语句 where#distinct 结果集去重复 升序SELECT DISTINCT t1.user_role FROM tb1 t1;SELECT DISTINCT t1.user_role ,t1.create_time FROM tb1 t1;#in关键字SELECT * FROM tb1 t1 WHERE t1.user_role IN(4,3);#使用 exists代替inSELECT * FROM tb1 t1 WHERE  EXISTS(SELECT * FROM tb1 t2 WHERE t2.id=t1.id AND  t2.user_role IN(4,3));#使用 between and 代替in  (必须由小到大)SELECT * FROM tb1 t1 WHERE t1.user_role BETWEEN 3 AND 4;#比较符 > < >= <= =  不等于 != <> ^=  #使用比较符代替inSELECT * FROM tb1 t1 WHERE t1.user_role >=3 AND t1.user_role <=4;#模糊搜索 like % 不确定多个字符  _ 不确定单个字符  搜索 % _时 要转义 \_ \%SELECT * FROM tb1 t1 WHERE t1.nickname LIKE '%陈%';#排序 desc 降序 升序SELECT * FROM tb1 t1 ORDER BY t1.user_role DESC;#多个排序级别SELECT * FROM tb1 t1 ORDER BY t1.user_role DESC ,t1.create_time ASC;#分组函数 1 2017-08-11 22:13:46SELECT * FROM tb1 t1 ORDER BY t1.create_time DESC;SELECT COUNT(id),t1.* FROM tb1 t1 GROUP BY t1.user_role; #查询每组最新的记录 #错误写法,默认显示第一次插入的行 即升序SELECT * FROM tb1 t1 GROUP BY t1.user_role ORDER BY t1.create_time DESC; #错误写法,子查询的排序不能起效SELECT * FROM (SELECT * FROM tb1 t1 ORDER BY t1.create_time )tmp GROUP BY tmp.user_role;#正确写法 获取最新的一条或n条SELECT * FROM tb1 t1WHERE (SELECT COUNT(*) FROM tb1 t2#分组字段连接WHERE t2.user_role=t1.user_role#排序字段比较AND t2.create_time >t1.create_time)<2ORDER BY t1.create_time DESC#计数 count()SELECT COUNT(*) FROM tb1 t1;SELECT COUNT(*) FROM tb1 t1 GROUP BY t1.user_role;#平均 avg()SELECT AVG(t1.user_role) FROM tb1 t1;#总和 sum()SELECT SUM(t1.user_role) FROM tb1 t1;#最小 min()SELECT MIN(t1.user_role) FROM tb1 t1;#最大 max()SELECT MAX(t1.user_role) FROM tb1 t1;#分组限制语句 havingSELECT COUNT(id),t1.* FROM tb1 t1GROUP BY t1.user_roleHAVING COUNT(t1.user_role) <=2;#子查询 可用在 select 子句 from 子句 where 子句 等等 SELECT (SELECT COUNT(*) FROM tb1 t1) FROM tb1;#临时表SELECT * FROM (SELECT * FROM tb1 t1) tmp#临时表分页SELECT * FROM (SELECT * FROM tb1 t1 LIMIT 0,5 ) tmp