mysql（数据库操作）

简单基础操作mysql（数据库操作）
创建数据库 # 指定编码方式为utf8， 防止中文乱码

create database 数据库名 charset=utf8;
例如:create database stu charset=utf8;

查看所有的数据库

show databases;

查看当前使用的数据库

select database();

使用数据库：

use 数据库名;
use stu;

查看创建数据库的语句：

show create database 数据库名;
show create database stu;

删除数据库

drop database 数据库名;
例如:drop database stu;

12.4.1 创建表的命令 ★★★★★
创建表

int unsigned 无符号整形

auto_increment 表示自动增长

not null 表示不能为空

primary key 表示主键 数据库主键，指的是一个列或多列的组合，

其值能唯一地标识表中的每一行，通过它可强制表的实体完整性。

主键主要是用于其他表的外键关联，以及本记录的修改与删除。

default 默认值

create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);

创建数据表：

create table 数据表名(
    id int unsigned primary key not null auto_increment,
    name varchar(20) not null
);
例如：创建班级表，要求：班号、班级名称两个字段；

create table classes(
    id int unsigned primary key auto_increment not null,
    name varchar(20) not null
);
例如：创建学生表，要求：学号、姓名、年龄、身高、性别、班级号六个字段；

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

查看表

查看当前数据库中的所有数据表：

show tables;

查看表结构

desc 表名;
例如:desc students;

例如:desc students;
查看刚刚所创表的语句

show create table 表名字;
例如:show create table students;

删除表

drop table 表名;
例如:drop table class;

修改表
修改表-添加字段：

alter table 表名 add 列名 类型;
例如:alter table students add birthday datetime;

修改表-修改字段：重命名版

在表中已有字段 但是字段名不满足要求 类型或约束满足或者不满足均可

alter table 表名 change 原名 新名 类型及约束;
例如:alter table students change birthday birth datetime not null;

修改表-修改字段：不重命名版

alter table 表名 modify 列名 类型及约束
例如:alter table students modify birth date not null;

修改表-删除字段：

alter table 表名 drop 列名;
例如:alter table students drop birthday;

修改表-数据表重命名

alter table 表原名 rename to 新表名;
例如:alter table classes rename to clas；

增加数据的命令

-- 1. 全列插入：值的顺序与表结构字段的顺序完全一一对应
insert into 表名 values (...)
例:
insert into students values(0, 'xx', default, default, '男');
-- 2. 部分列插入：值的顺序与给出的列顺序对应
insert into 表名 (列1,...) values(值1,...)
例:
insert into students(name, age) values('王二小', 15);
-- 3. 全列多行插入
insert into 表名 values(...),(...)...;
例:
insert into students values(0, '张飞', 55, 1.75, '男'),(0, '关羽', 58, 1.85, '男');
-- 4. 部分列多行插入
insert into 表名(列1,...) values(值1,...),(值1,...)...;
例：
insert into students(name, height) values('刘备', 1.75),('曹操', 1.6);

数据修改

update students set 要修改的字段='' where 条件
例如:update students set name='小黑' where id=1;

数据删除

delete from 表名 where 条件
例如:delete from students where id=5;

关系型数据库： MYSQL、 Oracle 、MS SQL server、sqlite3

MySQL(免费)、Oracle(商业版) 属于同一个公司(Oracle)

MS SQL server 针对Windows平台，属于微软

Sqlite3 用在 嵌入式设备中， 如 手机、平板等

关系型数据库所用的语言都是 SQL语言

非关系型数据库

MongoDB

Redis：内存型数据库，一般用来做缓存\

select * from cate; -- * 代表所有字段
select * from goods;

数据操作

插入数据

insert into cate values(0, "手机");  -- 插入表中所有字段的数据，只有主键中插入0，代表自增
insert into cate values(0, "电脑");
insert into cate values(0, "家居");
insert into cate values(0, "家具"),(0, "厨具"),(0, "餐具");
-- 指定字段插入
insert into goods(goods_name, price, cate_id) values("Apple Iphone 11",5899.00,1);
insert into goods(goods_name, price, cate_id) values("联想 小新 Air14",4599.00,2),("荣耀 4Tpro", 1489.00, 1);

查询数据

删除数据

delete from goods where id=3;  -- 删除id为3的记录

修改数据

update cate set cate_name='家具' where id=3;8
​
update goods set price=price*1.2 where price<2000; -- 低于2000的商品价格上调20%
update goods set price=price*0.9 where price>5000; -- 超过5000的商品 打9折

查询
条件查询
比较运算符

select * from goods where price > 2000; -- 查询价格大于2000的商品
select * from goods where id>=3;   -- 查询id不小于3的商品信息
​
select * from goods where price < 2000; -- 查询价格小于2000的商品
select * from goods where id<=3;   -- 查询id不大于3的商品信息
​
select * from goods where id=2; -- 查询id为2的商品信息
select * from goods where id!=2; -- 查询id不为2的商品信息
select * from goods where id<>2; -- 查询id不为2的商品信息

逻辑运算符

select * from goods where price>2000  and id>=2; -- 查询价格大于2000 且 id不小于2 的商品信息
select name, price from goods where id=1 or id=3; -- 查询id为1 或 id为3 的商品名称和价格

范围查询

-- 查询id在5到50之间的商品信息
select * from goods where id>=5 and id<=50;
select * from goods where id between 5 and 50; -- 用来判断连续的范围
-- 查询id为1 或3 或 5 的商品信息
select * from goods where id=1 or id=3 or id=5;
select * from goods where id in (1,3,5);  -- 用来判断不连续的范围

NULL判断

select * from goods where price is null; -- 查询价格为空的商品信息
 select * from goods where price is not null; -- 查询价格不为空的商品信息
 
 select * from goods where goods_name=""; -- 判断是有内容，只不过内容为空字符串

模糊查询

select * from goods where goods_name like "%电脑%"; -- 查询名称含有“电脑”的商品信息，%任意个任意字符
select * from goods where goods_name like "电脑%"；  -- 查询名称以"电脑"开头的商品信息
select * from goods where goods_name like "%电脑"；  -- 查询名称以"电脑"结尾的商品信息
​
select * from person where name like "李_"; -- 查询姓李且名为1个字的 人的信息，_ 一个任意字符

排序

select * from goods order by price; -- 默认升序排序
select * from goods order by price asc; -- asc确定升序
​
select * from goods order by price desc; -- desc 降序排序
​
 select * from goods order by price desc, id desc; -- 多个字段排序，先按照前面的字段排序；只有前面字段值相等，才会按照下一个字段排序

分页

select * from goods limit 0,10; -- 代表每页10条数据，第1页
select * from goods limit 10,10; -- 代表每页10条数据，第2页
select * from goods limit 40,10; -- 代表每页10条数据，第5页
​
select * from goods limit (n-1)*m, m; -- n代表第几页，m代表每页多少条数据
 

select * from goods limit 10 offset 0; -- 代表每页10条数据，第1页
select * from goods limit 10 offset 10; -- 代表每页10条数据，第2页
select * from goods limit 10 offset 20; -- 代表每页10条数据，第3页
select * from goods limit 10 offset 40; -- 代表每页10条数据，第5页
​
select * from goods limit m offset (n-1)*m; -- n代表第几页，m代表每页多少条数据

分组
select cate_id as “类别”, count(*) as “数量”, max(price) as “最高价”, min(price) as “最低价”, round(avg(price),2) as “平均价格” from goods group by cate_id;
– 分组+聚合函数
max(): 最大值

min():最小值

count(*): 统计数量

sum()：求和

avg(): 求平均值

round():四舍五入的方式保留位数

select cate_id, group_concat(goods_name)  from goods group by cate_id;
-- 列举每个分类下的商品名称
group_concat(): 列举每组成员的信息

select publish,round(avg(price), 2) from book group by publish  having publish="作家出版社";
​
select publish,round(avg(price), 2) from book  where publish="作家出版社";

注意：group by分组之后的条件判断，不能使用 where关键字，但是可以使用 having

物理外键与逻辑外键
外键的作用：约束多表中的数据必须是在主表存在

公司里一般不用外键，公司里常用的是逻辑外键。

所谓的逻辑外键就是一个普通的字段(类型为int)

物理外键：就是使用Forimary key来约束过的字段

物理外键和逻辑外键不同：orm来查的时候必须使用物理外键

一对多建表

create table goods(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
    foreign key(cate_id) references goods_cates(id),
    foreign key(brand_id) references goods_brands(id)
);

多对多建表

create table hero(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
);
​
create table arms(
    id int primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    number int 
);
​
create table hero_arms(
    id int primary key auto_increment not null,
    h_id int,
    a_id int,
    foreign key(h_id) references hero(id),
    foreign key(a_id) references arms(id)
  
);

自关联建表

create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
);
 

关联查询
– 一对多关联查询

select * from students inner join classes on students.cls_id = classes.id;
​
-- 多对多关联查询
select * from hero h inner join hero_arms ha on h.id=ha.h_id inner join arms a on a.id=ha.a_id;
​
-- 自关联查询
select city.* from areas as city inner join areas as province on city.pid=province.aid
where province.atitle='山西省';
 

创建areas表的语句如下：

create table areas(
    aid int primary key,
    atitle varchar(20),
    pid int
);
 查询所有省份
select * from areas where pid is null;
 查询所有城市
select c.aid as "编号",p.atitle as "省份",c.atitle as "市区" from areas as c inner join areas as p on p.aid=c.pid;
 查询所有北京市的市区
select c.aid as "编号",p.atitle as "省份",c.atitle as "市区" from areas as c inner join areas as p on p.aid=c.pid where p.atitle="北京市";