mysql常用命令

数据库操作：

mysql -u root -p
or
mysql -uroot -p密码
库以及表的选择操作
查看库--->show databases;
创建库---->create database 
删除库----->drop database 库名; 
选库----->use test;
查看库下的表---->show tables;
删除表------>drop tabel 表名 ; 
查询表属性---->desc 表名；

创建表：

create table users(
   id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(100) NOT NULL,
   age INT NOT NULL,
   date DATE,
   PRIMARY KEY ( id )
);

基本语句：

insert into users (uid,name,age) values (1,'战幕',23);
#单行
insert into users values(3,"kimi",25); 
#多行插入
insert into users values(131,"kid",3),
(129,"kid",3),(130,"kid",3); 
#修改某一跳
update users set age=99 where name='kimi'；
#删除某一条
delete from users where uid=7;
#查询所有列
SELECT * FROM users;
#查询部分列
select id,name from users where uid>=2;   
#查询一行
select * from users where uid=2; 
#查询多行 
select * from users where uid<=10;  
#将id+1取出
select id+1,name from users where uid<=8; 
#不等于!=
select * from users where uid!=2;
select * from users where uid!=2; and id!=4 ORDER BY id ASC;
#模糊匹配
select * from users where name like '%杰';
select * from users where name like '杰%';
select * from users where name like '%杰%';
#或者
select * from users where id not in (3,11) ORDER BY id ASC;
#in（4,11）表示在某集合或的意思
select id,name from users where id in (1,2);   
#between 某值 and 
select id,name,uid from users where uid  
between 10 and 200; 
#查询或or
select id,name,uid from users id=40 or id=60;
#查询id大于30小于60 或者 id大于40小雨70
select id,name,uid from users where id >30 and id<60 
or 
id>40 and id<70 ORDER BY id ASC;
#and
select * from users where id=2 and (id<1000 or id>3000) and id>5;
#limit
select * from users limit  5,8;
SELECT count(*) from user;

#分组
SELECT name FROM users GROUP BY name
#分组统计年龄最大
SELECT max(age) from users group by name;
#合并
select * from users where id in (select id from b);
#exists子句
select *from users where exists 
(select *from users1 where users.id=users1.gift_id);
#HAVING 分组后用户id >2 , having一般跟在group by之后
SELECT user_id FROM cas GROUP BY user_id HAVING user_id>2;

函数：

select avg (id) from admin;
select avg (id) AS avg from admin;# 平均值
select count (id) AS count from admin; # 总数
select sum (id) AS sum from admin;# 总和
select max (id) AS max from admin; # 最大值
select min (id) AS min from admin; # 最小值

#删除某一列
ALTER TABLE users  DROP age;
#增加列
ALTER TABLE users ADD age INT;
#列位置
ALTER TABLE users ADD t INT first;
ALTER TABLE users ADD i INT AFTER b;
#修改表名
ALTER TABLE users RENAME TO user;
#创建索引默认情况下，如果未指定索引类型，MySQL将创建B-Tree索引
CREATE INDEX idx_age ON users(age);
ALTER TABLE users ADD INDEX (b1);
ALTER TABLE users ADD INDEX (b1,b2);
#查看索引；
show index from users;
show KEYS from t;
#####查看
SHOW CREATE TABLE t;

CREATE TABLE `b` (
  `b6` int(11) DEFAULT NULL,
  `b5` int(11) DEFAULT NULL,
  `b1` int(11) NOT NULL,
  `b2` int(11) NOT NULL,
  `b3` int(11) NOT NULL,
  `b4` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`b1`),
  KEY `b2` (`b2`,`b3`),
  KEY `idx4` (`b4`),
  KEY `b6` (`b6`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

#删除索引 索引名
alter table b drop index b1;

#测试列
EXPLAIN SELECT * FROM excel_tels ;
EXPLAIN SELECT name FROM excel_tels ;
EXPLAIN SELECT name FROM excel_tels ;
EXPLAIN select * from excel_tels order by name;
EXPLAIN select * from excel_tels where id>3;
explain select title from excel_tels group by title;

联接查询：

# 内联接:INNER JOIN在表中存在至少一个匹配时返回行。
# INNER JOIN 与 JOIN 是相同的。
SELECT * FROM m INNER JOIN n ON m.id = n.id;
# 左外联接:LEFT JOIN 关键字从左表（table1）返回所有的行，
# 即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL
SELECT * FROM m LEFT JOIN n ON m.id = n.id;
# 右外联接 从右表（table2）返回所有的行，
# 即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。
SELECT * FROM m RIGHT JOIN n ON m.id = n.id;
# 交叉联接
SELECT * FROM m CROSS JOIN n;   # 标准写法
SELECT * FROM m, n;
# 类似全连接full join的联接用法
SELECT id,name FROM m
UNION
SELECT id,name FROM n;

select * from user1 a,user2 b where a.id=b.id;

select 
campaign_user_gifts.id,campaign_user_gifts.gift_id,campaign_user_gifts.broker_uid,
campaign_gifts.gift_name,
ex_broker_info.broker_name,
ex_houses_info.houses_name,
ex_houses_info.houses_id,
campaigns.campaign_name
from campaign_user_gifts   LEFT JOIN campaign_gifts on campaign_user_gifts.gift_id = campaign_gifts.id
LEFT JOIN ex_broker_info on ex_broker_info.broker_uid = campaign_user_gifts.broker_uid
LEFT JOIN ex_houses_info on ex_houses_info.houses_id = campaign_gifts.project_id
LEFT JOIN campaigns on campaigns.id = campaign_user_gifts.campaign_id;

• RIGHT JOIN

SELECT 
user1.id, 
user2.name
FROM user2
RIGHT JOIN user1
ON 
user1.id = user2.id
ORDER BY user2.id ASC;

• UNION ALL

SELECT id FROM user1
UNION ALL
SELECT id FROM user2
ORDER BY id;

SELECT id FROM user1
UNION 
SELECT id FROM user2
ORDER BY id;

union查询就是把2条或多条sql的查询结果,合并成1个结果集
sql1 返回N行
sql2 返回M行
sql1 union sql2 ,返回 N+M行 两行的合集

• 数据库备份

# 数据库备份
mysqldump -u root -p db_name > file.sql
mysqldump -u root -p db_name table_name > file.sql
# 数据库还原
mysql -u root -p < C:\file.sql