mysql的常用操作

远程链接数据库
mysql -u lzpddd -pmypassword -h 192.168.1.88 -P 3306 -D mydb
例如:mysql -u root -ppassword -h rm-bp1946fwc6849bu2iyo.mysql.rds.aliyuncs.com -P 3306 -D mysql_test001
注意:
-p和password之间没有空格
阿里云的数据库表外网链接:rm-bp1946fwc6849bu2iyo.mysql.rds.aliyuncs.com
数据库名称:mysql_test001


为数据库创建用户:
# 创建用户meiduo,密码:meiduo
create user oldeleven identified by 'oldeleven'; 
# 授权products数据库下的所有表（products.*）的所有权限（all）给用户meiduo在以任何ip访问数据库的时候（'oldeleven'@'%'）
grant all on products.* to 'oldeleven'@'%'; 
# 刷新生效用户权限
flush privileges;


数据库的相关操作:
查看所有已创建的数据库:show databases;
查看当前正在使用的数据库: select database();
创建数据库:create database meiduo_mall default charset=utf8;
查看数据库的相关信息:show create database databaseName;
删除数据库:drop database databaseName;
更改数据库名:RENAME DATABASE db_name TO new_db_name(据说会出现丢失数据的情况,小心使用)

表的相关操作:
创建表: create table users;
删除表: drop table users;
更改表名: ALTER  TABLE table_name RENAME TO new_table_name(不知道会不会丢失数据,暂时未经过测试)
有人可能要问了,为什么要更改表名?
很简单的一个原因:有些表名在最初设计的时候有可能出现命名不规范的情况.

表字段的相关操作:
添加字段:
alter table user add COLUMN FieldName VARCHAR(20) DEFAULT NULL; //增加一个字段，默认为空
alter table user add COLUMN FieldName VARCHAR(20) NOT NULL;  //增加一个字段，默认不能为空 
更改字段:
alter table user MODIFY FieldName VARCHAR(10); 　//修改一个字段的类型
alter table user CHANGE oldname newname int;　 //修改一个字段的名称，此时一定要重新指定该字段的类型
删除字段:
alter table user DROP COLUMN FieldName; 　 //删除一个字段
查询表的字段:
show create table users;

修改表字段顺序：
# show create table tb_sku;
# 把default_image_url字段移动到goods_id字段的后面 （此操作不会删除表数据）
alter table tb_sku modify default_image_url varchar(200) DEFAULT NULL after goods_id;

表数据的相关操作:
添加数据:insert into  table_name (field1,field2) values (value1,value2) # field和value相对应
添加多条数据：insert into  table_name (field1,field2) values (value1,value2)，
                          (field1,field2) values (value1,value2)，
          (field1,field2) values (value1,value2)
删除数据:delete from table_name where id=1 # 如果没有where，则删除整个表
修改数据: update table_name set field=value [where id=1] # 当没有where时候，整个字段都会修改
查询数据: select * from table_name limit 1,10 #查询前十条数据
多表查询:SELECT
    s.id sid,
    s.name,
    s.gender,
    s.score,
    c.id cid,
    c.name cname
FROM students s, classes c
WHERE s.gender = 'M' AND c.id = 1;

连接查询:
1.内连接
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
INNER  JOIN classes c
ON s.class_id = c.id;
2.左外链接
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
LEFT OUTER  JOIN classes c
ON s.class_id = c.id;
3.右外连接
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
RIGHT OUTER  JOIN classes c
ON s.class_id = c.id;
4.全连接
SELECT s.id, s.name, s.class_id, c.name class_name, s.gender, s.score
FROM students s
FULL OUTER  JOIN classes c
ON s.class_id = c.id;

更新数据的高级操作
如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录
REPLACE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);
如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99) ON DUPLICATE KEY UPDATE name='小明', gender='F', score=99;
如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略
INSERT IGNORE INTO students (id, class_id, name, gender, score) VALUES (1, 1, '小明', 'F', 99);

生成快照
如果想要对一个表进行快照，即复制一份当前表的数据到一个新表
-- 对class_id=1的记录进行快照，并存储为新表students_of_class1:
CREATE TABLE students_of_class1 SELECT * FROM students WHERE class_id=1;

写入查询结果集
创建一个统计成绩的表statistics，记录各班的平均成绩：
CREATE TABLE statistics (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    average DOUBLE NOT NULL,
    PRIMARY KEY (id)
);
然后，我们就可以用一条语句写入各班的平均成绩：
INSERT INTO statistics (class_id, average) SELECT class_id, AVG(score) FROM students GROUP BY class_id;
(group by : 也就是说把相同班级id的所有同学加起来,然后求AVG),,,一定要有group by
确保INSERT语句的列和SELECT语句的列能一一对应，就可以在statistics表中直接保存查询的结果：


聚合函数:
SUM()
AVG()
MAX()
MIN()
GROUP BY
ORDER BY
......

1.查询总消费小于1000的用户
HAVING: SELECT customer ,SUM(price) from orders  (基本查询语句)
GROUP BY customers  (按照用户聚合数据)
HAVING  SUM(price)  < 1000  (条件是:总消费<1000)


2.查询2019.07.12  15:00 到 20:00总消费小于1000的用户
HAVING: SELECT customer ,SUM(price) , time FROM orders WHERE  time BETWENNT '2019-07-12 15:00:00' AND ''2019-07-12 20:00:00''
GROUP BY customers  (按照用户聚合数据,假设用户是没有重名的)
HAVING  SUM(price)  < 1000  (条件是:总消费<1000)

3.查询店内顾客的收手机联系方式
select customers ,tel from orders
GROUP BY customers 

select DISTINCT customers ,tel from orders