-- 数据库的操作
-- 连接数据库 启动数据库
mysql -uroot -p
mysql -uroot -pxiaoye1014
-- 退出数据库
exit/quit/ctrl + d
-- sql语句最后需要有分号;结尾
-- 不区分大小写
-- 显示数据库版本
select version();
-- 显示时间
select now();
-- 查看所有数据库
show databases;
-- 创建数据库
create database python04;
create database python04 charset=utf8;
-- 查看创建的数据库
show create databse python04;
-- 查看当前使用的数据库
select database();
-- 使用数据库
use pyhton04; -- 确定当前使用数据库
-- 删除数据库
drop database python04
drop table `python-04`
-- 数据表的操作
-- 查看当前数据库中所有表
show tables;
-- 创建表
-- auto_increment 表示自动增长
-- not null 表示不能为空
-- primary key 表示主键
-- default 默认值
-- create table 数据表名字 (字段 类型 约束, ... )
-- 创建students表(id, name, age, heigh, gender, cls_id)
create table students(
id int unsigned not null auto_increment primary key,
name varchar(30),
age tinyint unsigned default 0,
high decimal(5, 2),
gender enum("男", "女", "中性", "保密") default "保密",
cls_id int unsigned
); -- 最后一个字段不能加逗号 ,
-- 查看表的创建语句
show create table students;
-- 查看表结构
desc table01;
-- 修改表-添加字段
alter table students add birthday datetime;
-- 修改表-修改字段:不重名版
alter table students modify birthday date;
-- 修改表-修改字段:重命名版
alter table students change birthday birth date default "2020-01-01";
-- 修改表-删除字段
alter table students drop high;
-- 删除表
drop table students;
-- 数增删改查(curd)
-- 增加
-- 全列插入
insert into classes values(0, "菜鸟班");
-- 备注:
-- 插入students中,由于id是主键、自动增长,所以可以
insert into students values(0, "张三", 20, "女", 1, "2020-01-01");
-- id 还可以用 null default
-- 插入enum类型,不能超过范围,同时可以用1,2 代替 男 女 插入
-- 部分插入
insert into students (name, gender) values (“李四”, 2);
-- 多行插入
insert into students (name, gender) values ("小乔", 18), ("貂蝉", 19);
-- 修改
update students set gender="男"; -- gender =1 -- 全部都改
update students set gender="男" where id=3 -- 改特定条件
update students set gender="男", age=22 where id=3; -- 改多个
-- 删除
-- 物理删除
delete from students; -- 删除整个表
delete from studnts where id=3; -- 删除特定条件,删除某一行。
-- 逻辑删除
-- 一般不推荐物理删除,数据难得
-- 如何逻辑删除?用一个字段来表示,这条信息是否已经不能再使用了
-- 给students表添加一个is_delete字段 bit 类型
alter students add is_delete bit default 0; -- 看不见,但是该怎么办酒怎么办。
-- update students set is_delete=1 where id=6
-- 查询详细
-- 查询所有字段
select * from students;
-- 查询指定字段
select name, age from students;
-- 使用 as 给字段起别名
select name as 姓名, age as 年龄 from students
-- 通过 as 给表写名字 多个表的字段
select students.name, students.age from students -- 用 .
select s.name, s.age from students as s;
-- 不行 select students.name, students.age from students as s;
-- 消除重复行
select distinct name from students;
-- 条件查询
-- 比较运算符
-- > < >= <= = !=
select .. from students where ...
-- 逻辑运算符
-- and or not
-- 模糊查询
-- like
-- % 替代0个、1个或者多个
-- _ 替换一个
select name from students where name like "%小%";
select name from students where name like "__"; -- 名字两个字
-- rlike 正则
select name from students where name rlike "^周.*论$"; -- 名字是以周开头,论结尾中间是.*,正则还有贪婪和非贪婪
-- 范围查询
-- in (1, 3, 8) 表示在一个非连续的范围内查询
select name where students where age in (12, 18, 30);
-- not in 不在某个范围之内
select name where students where age not in (12, 18, 30);
-- between .. and .. 在连续范围之内 首尾端包含
select name where students where age between 18 and 34;
-- 不在连续 not between .. and ..
select name where students where age not between 18 and 34;
-- 空判断
--列判断空为 is null
select * from students where heigh is null;
-- 列判断非空为 is not null
select * from students where heigh is not null;
-- 排序
-- order by 字段 asc 升序 desc 降序
select * from students where (age between 18 and 34) and gender=1 order by age; -- 默认是升序
-- order by 多个字段 第一个相同的情况下,按照第二个 ......
select * from students where (age between 18 and 34) and gender=1 order by age, id desc; -- 默认是升序
-- 如果第一个字段都不相同,则第二个排序字段就没有用。
-- 聚合函数
-- 总数:count()
select count(*) as 男性人数 from students where gender=1;
-- 最大值:max()
select max(age) as 男性最大年龄 from students where gender=1;
-- 最小值:min() 平均值:avg() 求和:sum()
-- 计算平均值
select sum(age)/count(*) from students; -- select后面可以放表达式。
-- 四舍五入 round()
select round(sum(age)/count(*), 2) from students;
--分组
-- group by
select gender from students group by gender;
select gender, count(*) from students where gender=1 group by gender;
-- 注意先是gender,后面是count(*) * 是针对每个组的。
-- 查看每组中的name有哪些?
select gender, group_concat(name) from students group by gender;
select gender, group_concat(name, "_", age, "_", id) from students group by gender;
-- having 对分组后的每组进行过滤
select gender, group_concat(name), avg(age) from students group by gender having avg(age) > 30;
-- having后的avg(age)是针对每个组的,count(*)中*也是针对每个组的。
-- where Vs having:where是对原始表进行过滤,having是对查询的结果进行过滤。
-- 分页
-- limit
-- 查询前5个数据
select * from students where gender=1 limit 5;
select * from students where gender=1 limit 1, 5;
-- 表示从1开始的5个,索引是0, 1, 2, 3 ... 其中limit 1, 5:就是索引1到5
-- limit 5 如果没有5个,则实际多少个就显示多少个。
-- limit位置在最后。
-- select * from students where gender=1 limit 2*(6 - 1), 5; -- 不行,这个地方不支持表达式。
-- 连接查询
-- 内连接:取交集
-- 取所有
select * from students inner join classes;
-- students中每一行都有classes所有记录 总记录数 n * m
-- 取交集
select * from students inner join classes on students.cls_id=classes.id;
-- 上面是内部连接,输出交集结果。
-- 取部分字段
select sutdents.*, classes.name from students inner join classes on students.cls_id=classes.id;
-- 取别名
select s.*, c.name from students as s inner join classes as c on s.cls_id=c.id;
-- 左连接:哪个表在左边,就按照该表为标准,右表取不出来的就为null。
select s.*, c.name from students as s left join classes as c on s.cls_id=c.id;
-- 右连接:哪个表在右边,就按照该表为标准,左表取不出来的就为null。
-- 从查询的结果当做一个集,然后对结果进行过滤
select s.*, c.name from students as s left join classes as c on s.cls_id=c.id having c.id is null;
-- 自关联,自连接
select * from areas as province inner join areas as city on city.pid = province.aid having province.atitle="山东省";
-- 子查询 select 嵌套 select
select * from students where height = (select max(height) from students);
-- 数据库设计实例
-- 创建商品表
create table goods(
id int unsigned 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)
);
-- 通过goods_brands数据表来更新goods数据表
update goods as g inner join goods_brands as b on g.brand_name=b.name set g.brand=b.id;
-- 修改表结构 给表字段改名字和类型约束
alter table goods
change cate_name cate_id int unsigned not null,
change brand_name brand_id int unsigned not null;
-- 对于已经存在的数据表如何更新外键的约束
alter table goods add foreign key (brand_id) references goods_brands(id);
-- 注意goods中的cate_id 和 goods_brands中的id类型需要一致。
-- 如何取消外键的约束
alter table goods drop foreign key 外键名称。
-- 在实际开发中,很少使用外键的约束,会极大的降低表的更新效率。
-- 补充
-- 插入sql
-- 由很多insert into ... valus ... 组成的.sql文件 如何整体插入?
-- 到所属数据库,然后
source areas.sql;
-- 一般子查询比自关联查询会慢点。
-- 一边查询,一边创建表,用select查询结果创建表。
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; -- 这里brand_name必须改名为name,以和前面创建表的对应。
06_数据库-表数据的操作
最新推荐文章于 2024-09-22 21:13:03 发布