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 tb1
GROUP BY user_role;
#创建视图
CREATE VIEW group_tb1 AS SELECT * FROM tb1
GROUP BY user_role;

#使用视图
SELECT * FROM group_tb1;


#内连接(最常用)
SELECT t1.id,t1.nickname,t1.user_role,t1.create_time,t2.username FROM tb1 t1
INNER JOIN tb2 t2
ON t1.id=t2.id;

#左外连接
SELECT t1.id,t1.nickname,t1.user_role,t1.create_time,t2.username FROM tb1 t1
LEFT JOIN tb2 t2
ON t1.id=t2.id;

#右外连接
SELECT t1.id,t1.nickname,t1.user_role,t1.create_time,t2.username FROM tb1 t1
RIGHT JOIN tb2 t2
ON 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代替in
SELECT * 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;

#比较符 > < >= <= =  不等于 != <> ^=  
#使用比较符代替in
SELECT * 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:46
SELECT * 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 t1
WHERE 
(
SELECT COUNT(*) FROM tb1 t2
#分组字段连接
WHERE t2.user_role=t1.user_role
#排序字段比较
AND t2.create_time >t1.create_time
)<2
ORDER 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;

#分组限制语句 having
SELECT COUNT(id),t1.* FROM tb1 t1
GROUP BY t1.user_role
HAVING 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