MySQL 学习

mysqladmin -uroot -pmysql password yuan121423 修改密码

mysql -h 192.168.113.121 -uroot -p 连接远程数据库

update user set host = ‘%’ where user = ‘root’; 更改root远程访问权限，

FLUSH PRIVILEGES 每次修改刷新权限

sudo service mysql stop 关闭服务

sudo service mysql start 启动服务

sudo service mysql restart 重启服务

mysql -uroot -pmysql 登陆mysql

show databases; 显示mysql所有数据库

select database(); 显示当前连接的数据库

use demo 连接数据库

select version(); 显示数据库版本

select now(); 显示日期

drop database demo; 删除数据库

create database demo charset=utf8; 创建数据库

show create database demo; 显示创建数据库信息

创建表

create table focus (
id int unsigned primary key not null auto_increment,
name varchar(10) not null,
age tinyint unsigned default 0,
high decimal(5,2) default 0,
gender enum("男","女","中性","保密"),
cls_id int unsigned not null
);

show tables; 显示数据库中所有表

alter table students modify name varchar(20) not null; 修改字段数据类型

alter table students change brithday brith data default “2017-11-11” 修改字段名字

alter table students add is_delete bit; 添加字段。

insert into classes values (0,”python一期”),(0,“python二期”); 批量插入数据，0 是占位符；

update students is_delete=0 where id=2; 修改is_delete字段第二行数据；

update students set name=”小花”,gender =2 where =2; 可以同时修改多个字段数据；

delete from students where id =2; 删除数据

update students set is_delete=1 where id =3; 逻辑删除1代表删除，0代表未删除，查询的时候判断该字段；

select name as xx from students; 字段别名

select s.name,s.age from students as s; 表别名

select * from students where name like “金%”; 以金开头的

select * from students where name like “%金%”; 模糊查找，包含金

select * from students where name like “%金”; 以金结尾的

select * from students where name like “__”; name是2位的

select * from students where name rlike “^周.*”; 正则式 rlike

select * from students where name rlike “^周.*伦$”; 正则式 以伦结尾

select * from students where age in(12,34); 包含12,34

select * from students where age not in (12,34); 不包含12,34

select * from students where age between 18 and 34; 18和34之间的包含18和34；

select * from students where age not between 18 and 34; 不包含18-34之间的数据；

select * from students where height is null; 查询为空的数据

select * from students where height is not null； 查询不为空的数据

select * from students order by age asc; 降序

select * from students order by age desc; 升序

select * from students order by age asd,height desc; age降序，height升序

select * from students where age between 18 and 34 and gender =2 order by height desc,id asc;

select count(*) from students where gender = 1; 统计男生人数

select max(age) from students; 查询最大年龄

select min(age) from students; 查询最小年龄

select name from students where age=(select max(age) from students); 子查询

select avg(height) from students; avg平均函数，查询平均年龄

select sum(height)/count(height) from students; 求平均年龄

select round(avg(height),2) from students; 对平均年龄进行四舍五入

select distinct gender from students; 进行性别去重

select gender from students group by gender; 进行分组去重

select gender,count(*) from students group by gender; 统计每个性别的人数；

select gender,group_concat(name) from students group by gender; 查询每个性别都有那些姓名；

select gender,group_concat(name,height) from students group by gender; 查询同种性别中的姓名和身高

select gender,count(*) from students group by gender having gender =1; 分组查询性别中的人数量

select gender,count(*) from students group by gender having gender !=1; 分组 除男生以外的性别数量

select gender,count(*) from students group by gender having not gender =1; 分组 除男生以外的性别数量

select gender,avg(age),max(age),avg(height),max(height) from students group by gender; 查询每种性别中的平均年龄avg(age), 最大年龄,平均身高,最高身高；

select gender,group_concat(name) from students group by gender having avg(age)>30; 查询性别中平均年龄大于30的人姓名

having 一定在group by 后面 ，where 在表后面；

select * from students limit 5; 查询前5行数据；

select * from students limit 0,5; 查询前5行数据；

select * from students limit 5,5; 从第5行起不包含第5行，从第6行算起查5行数据

select * from students limit 10,5; 从第10行起不包含第10行，从第6行算起查5行数据

select * from students order by age asc limit 5,5; limit是写在最后面；

例子：select * from students limit (n-1)*m,m; 这是一个分页显示的例子；

select * from students inner join classes on students.cls_id=classes.id; 内关联数据

select * from students left join classes on students.cls_id=classes.id; 左关联数据；

select * from students right join classes on students.cls_id = classes.id; 右关联数据，很少用

select s.name,c.name from students as s left join classes as c on s.cls_id = c.id; 别名关联

内连接的其他写法

select c.name, s.name from students as s join classes as c on s.cls_id = c.id;
select c.name, s.name from students as s cross join classes as c on s.cls_id = c.id;

外连接的其他写法

select * from students as s left outer join classes as c on s.cls_id = c.id;

执行顺序为：

× from 表别名
× where ....
× group by ...
× select distinct *
× having ...
× order by ...
× limit start,count

所谓的标量子查询 ，就是一行一列，类似excle 一个单元格

select * from students where height = (select max(height) from students);

所谓的列级子查询 这个就是一列多行 这样语句的必须使用in，不然会报错或没结果。

select s.name from students as s where s.cls_id in(select id from classes);

所谓的行级子查询 where 后面用小括号 这个语句意思一个人同时满足最大身高和年龄；存在查不到结果的现象，语法没有错

select * from students where (height, age) = (select max(height),max(age) from students);

所谓的表级子查询，就是查询整个表的结果的

insert into goods_cates(name) select cate_name from goods group by cate_name;

create table goods_brands (
id int unsigned primary key auto_increment,
name varchar(40) not null) select brand_name as name from goods group by brand_name; 这条创建语句，就可以查询，将查询的数据插入进去，必须和创建的字段名字一致，不然报错；

create table if not exists goods_brands(
id int unsigned primary key auto_increment,
name varchar(40) not null
) select brand_name as name from goods group by brand_name; if not exists 意思是在创建表的时候判断该表是否存在。

连接表更新，是需要关键字update 和set 中间是关联结果。

update goods inner join goods_brands on goods.brande_name = goods_brands.name set goods.brande_name = goods_brands.id;

这个是插入整个查询结果，可以多个字段插入。

insert into goods_cates(name) select cate_name from goods group by cate_name;

三张表内连接查询，使用了别名；

select b.name,c.name,g.name,g.price from goods as g inner join goods_cates as c on g.cate_id =c.id inner join goods_brands as b on g.brand_id = b.id;

三张表左关联，千万别left he inner组合关联。

select * from goods as g left join goods_cates as c on g.cate_id = c.id left join goods_brands as b on g.brand_id =b.id;

三张表关联查询 为 ‘超极本’ 商品名称、价格

select b.name,c.name,g.name,g.price from goods as g inner join goods_cates as c on g.cate_id = c.id inner join goods_brands as b on g.brand_id = b.id where c.name = "超级本";

三张表关联 查询商品的种类

select c.name from goods as g inner join goods_cates as c on g.cate_id = c.id inner join goods_brands as b on g.brand_id = b.id group by c.name;

三张表关联显示 商品的平均价格

select c.name,avg(g.price) as "平均" from goods as g inner join goods_cates as c on g.cate_id = c.id inner join goods_brands as b on g.brand_id = b.id group by c.name;

三张表关联显示 大于所有商品平均价位的商品 按降序排序

select g.name,g.price from goods as g inner join goods_cates as c on g.cate_id = c.id inner join goods_brands as b on g.brand_id =b.id where g.price > (select avg(price) from goods) order by g.price desc;

三张表关联 查询每种类型中最贵的电脑信息

select g.name as a,c.name as e,b.name as t,abc.max_price from goods as g inner join goods_cates as c on g.cate_id=c.id inner join goods_brands as b on g.brand_id=b.id inner join ( select cc.name as ss,max(gg.price) as max_price from goods as gg inner join goods_cates as cc on gg.cate_id = cc.id group by cc.name) as abc on abc.ss = c.name and abc.max_price = g.price;

创建视图

create view v_sts as select g.name as a,c.name as e,b.name as t,abc.max_price from goods as g inner join goods_cates as c on g.cate_id=c.id inner join goods_brands as b on g.brand_id=b.id inner join ( select cc.name as ss,max(gg.price) as max_price from goods as gg inner join goods_cates as cc on gg.cate_id = cc.id group by cc.name) as abc on abc.ss = c.name and abc.max_price = g.price;

删除视图

drop view v_sts;

delete from goods where id =22; 删除表的id=22行数据

alter table goods add foreign key(brand_id) references goods_brands(id); 添加外键 不常用

alter table goods add foreign key(cate_id) references goods_cates(id); 添加外键 如果现有的数据有不符合的，将不能添加外键；

alter table goods drop foreign key goods_ibfk_1; 删除外键 ,删除的时候，需要show create table goods; 查看下键标示

select * from goods\G 按行显示，后面不能加;

索引

show index from goods; 查看表的索引
show index from test_index; 查看表的索引
create index test_index on test_index(title(10)); 创建索引
create index test_in on test_index(title(10)); 创建索引

drop index test_index on test_index; 删除索引

set profiling=1; 开启运行时间监测


show profiles; 查看执行的时间


表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
select (case when a>b then a else b end ),(case when b>c then b esle c end) from my_table

一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
select id, Count（*) from tb group by id having count(*)>1