1、创建数据库
mysql> CREATE DATABASE testdb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
2、创建数据库专属用户
mysql>grant all privileges on testdb.* to test@'%' identified by '123156';
3、创建一个表
mysql> create table test(
-> id int,
-> name varchar(100),
-> age int,
-> create_time timestamp);
4、插入数据
mysql>insert into test(id,name,age,create_time) values(1,'liuzd',20,now());
5、查询数据
mysql>select * from test;//全表查询
mysql>select id,name from test;//指定列查询
mysql>select * from test where age>10;//条件查询，where关键字，后方跟条件，结果为true的显示
mysql>select * from test where age>10 and name='liuzd'; //多条件查询,查询年龄大于10且名字叫liuzd的
mysql>select * from test where age>10 or name='liuzd';//查询年龄大于10或姓名为liuzd的
mysql>select * from test where name like '%zd%' ;//查询所有姓名包含zd的数据，%代表任意数量任意字符
mysql>select * from test where name like '_iu%';//查询姓名第二三位是iu的所有数据，_是占位符，代表任意单个字符
mysql>select * from test order by age;//按照age对查询结果排序，默认是asc，升序
mysql>select * from test order by age desc;//按照age对查询结果降序排列
mysql>select * from test limit 1;//查询结果只显示1条
mysql>select * from test limit 5,10;//分页查询常用，查询结果从第五条开始，显示10条
mysql>select count(1) from test where age>10;//查询年龄大于10的数据有几条
mysql>select count(1) c,name from test group by name//查看每个名字重复的次数,c是别名
mysql>select * from (select count(1) c,name from test group by name) t where t.c>1//查询出所有名字重复的姓名和重复次数
mysql>select count(0),name from test group by name having count(1) >1;//该sql和上一个sql等效
mysql>select
-> a.aid,a.aname,
-> b.bid,b.bname,b.age
-> from testa as a
-> left join testb as b on a.aid=b.bid
-> left join testc as c on b.aid=c.bid;//以testa为标准，testb、testc分别匹配显示，假设testa有10条数据，那最终查询结果肯定是10条，testb、tsetc匹配部分不存在则为空，多余的不显示。left join对应的right join，left join只需要调换表的顺序即可直接实现right join，即a left join b等价于b rigth join a
mysql>select
->a.aid,a.aname,
->b.bid,b.bname,b.age
->from testa as a
->inner join testb as b on a.aid=b.bid;//用testb匹配testa，如果testb中不存在testa的匹配，则不显示该数据，类似于where的关联查询，inner join等价于join
mysql>from
->(select '2018-05-12' as day,aid,aname from testa
->union
->select '2018-05-11' as day,aid,aname from testa) a
->left join testc as c
->on a.day=DATE_FORMAT(c.cretime, '%Y-%m-%d')
->and a.aid=c.cid
->where c.cid is not null;//union用来把查询结果合并，在这里是为了人为的把维度数据加个时间维度，然后再去左连接数据表，得到时间维度的数据情况，然后根据需要过滤掉不合条件的数据
6、更新数据
mysql>update test set name='liuzd2' where id=1;
7、删除数据
delete from test where id=1;
