【MySQL】数据库基本操作

1 数据库 增删改查

/*******************************************************/


# 增: 创建数据库
create database if not exists sun character set utf8;

# 删除数据库
drop database sun_db;

# 改: 使用数据库
use sun;

# 查: 查询正在使用的数据库 查询所有数据库
show databases ;
select database();

/*******************************************************/

2 数据库表 增删改查

/*******************************************************/
# 增: 增加表 create table 表名 (字段 数据类型 约束)
create table cs(
    uid int primary key,
    name varchar(100) not null ,
    age int
);

# 删: 删除数据库表
drop table cs;

# 查: 查询 数据库表
show tables ;
desc cs1;

# 改: 修改数据库表名
rename table cs to cs1;

/*******************************************************/

3 数据表字段:增删改查

/*******************************************************/
# 增 : 增加字段
alter table cs add `desc` varchar(20);

# 删 : 删除字段
alter table cs drop age;

# 改: 修改字段名称
alter table cs change age ages int;
alter table cs change ages age int not null unique ;
alter table cs change uid uid int auto_increment;
# 查: 查询表中的字段
desc cs;
/*******************************************************/

4 数据表数据:增删改查

/*******************************************************/
# 增: 增加数据
insert into cs (uid, name, `desc`) values (null,'小子','帅气');
insert into cs values (null,'小子1','牛逼');
insert into cs values (null,'小子2','8888'),(null,'小子3','8888'),(null,'小子4','8888');
insert into cs values (null,'小子5','8888'),(null,'小子6','8888'),(null,'小子7','8888');

# 删: 删除数据
delete from cs where uid=3;
delete from cs;  # 注意: 全部删除 不重置主键自增
truncate cs; # 注意: 全部删除 重置主键自增

# 改: 修改数据
update cs set `desc`='9999' where uid = 5;
update cs set `desc`='9999'; # 注意: 不加条件 全部修改

# 查询
select * from product;
select p.pname as "商品",p.price as "价格" from product as p ;
select pname as "商品",price as "价格" from product;
# 条件查询 where
select * from product where price > 300;
select * from product where price >= 300;
select * from product where price < 300;
select * from product where price <= 300;
select * from product where price != 300;
select * from product where price <> 300;

# 逻辑查询 and or not
select * from product where price >200 and price<3000;
select * from product where price <200 or price>3000;
select * from product where not (price>200 and price<3000);

# 空判断
select * from product where category_id is null;
select * from product where category_id is not null;

# 排序查询
select * from product order by price;
select * from product order by price,category_id;
select * from product order by price desc ,category_id desc ;
select * from product order by price asc,category_id asc;

# 模糊查询 between and ,like % _ ,in
select * from product where price between 200 and 3000;
select * from product where price in (200,600);
select * from product where pname like "__斯";
select * from product where pname like "%斯%";
select * from product where pname like "%霸%";
select * from product where pname like "___";

# 聚合查询 count max min sum avg
select count(*) from product;
select count(*) from product where price>300;
select max(price) from product;
select min(price) from product;
select sum(price) from product;
select avg(price) from product;
select round(avg(price),5) from product;
# 分组查询 group by having 条件
select category_id from product group by category_id;
select category_id,sum(price) from product group by category_id;
select category_id,sum(price) from product group by category_id having sum(price)>300;

# 分页查询 limit m,n
select price,pname from product where price>300 order by price desc ;
select price,pname from product where price>300 order by price desc limit 0,3;

5 多表查询

5.1 内连接

关键字:inner join , inner可以省略
先写谁,谁是左表
只保存左右两表匹配成功的记录
格式:select 字段 from 左表 inner join 右表 on 查询条件 and 查询条件

/*******************************************************/
# 1 内连接 取交集 只保留左右两表匹配成功的记录
# 例1:查询所有英雄对应的功夫名称,如果没有则不显示 (求交集)
select * from kongfu inner join hero on kid=kongfu_id;
select * from kongfu k inner join hero h on k.kid=h.kongfu_id;
# 例2:查询所有功夫名称对应的英雄,如果没有则不显示 (求交集)
select kname,hname from kongfu k inner join hero h on k.kid=h.kongfu_id;
select hname,kname from hero h inner join kongfu k on k.kid=h.kongfu_id;

/*******************************************************/

5.2 外连接

5.2.1左连接

关键字:outer join , outer 可以省略
先写谁,谁是左表
左边全部显示,右边只显示匹配成功的
格式:select 字段列表 from 左表 left outer join 右表 on 查询条件

/*******************************************************/

# 左连接: 左表保存所有数据,右表保存匹配成功的数据
select * from hero left outer join kongfu on hero.kongfu_id = kongfu.kid;
select hname,kname from hero left outer join kongfu on hero.kongfu_id = kongfu.kid;


/*******************************************************/

5.2.2右连接

关键字:outer join , outer 可以省略
先写谁,谁是左表
右边全部显示,左边只显示匹配成功的
格式:select 字段列表 from 左表 right outer join 右表 on 查询条件

/*******************************************************/

# 右连接: 右表保存所有数据,左表保存匹配成功的数据
select * from hero right outer join kongfu on hero.kongfu_id = kongfu.kid;
select kname,hname from hero right outer join kongfu on hero.kongfu_id = kongfu.kid;
select kname,hname from hero left outer join kongfu on hero.kongfu_id = kongfu.kid;
# select kname,hname from kongfu right outer join hero on hero.kongfu_id = kongfu.kid;


/*******************************************************/

5.3自连接

关键字 : as
通过起别名的方式实现

/*******************************************************/

# 自连接查询
# 方式1 查询广东省的id 440000
select * from areas where title="广东省";
select * from areas where pid=440000;

# 方式2 自连接
select * from areas as c inner join areas as p on c.pid = p.id where p.title="河北省";

/*******************************************************/

5.4子查询

/*******************************************************/
# 方式3: 子查询
select id from areas where title="河北省";
select * from areas where pid=(select id from areas where title="河北省");

select k.title,areas.title from areas inner join (
    select * from areas where pid=(
        select id from areas where title="河北省")) as k where k.pid = areas.id;

/*******************************************************/