数据库操作:
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