数据库相关操作

最新推荐文章于 2024-04-05 20:00:35 发布

zaizai2108

最新推荐文章于 2024-04-05 20:00:35 发布

阅读量313

点赞数

本文链接：https://blog.csdn.net/zaizai2108/article/details/90574340

版权

启动服务
sudo service msyql start
停止
sudo service msyql stop
重启
sudo service msyql restart

数据库用户名: root
密码: mysql

-- 数据库的操作

-- 链接数据库
mysql -uroot -p

-- 退出数据库
exit ,quit ,ctr + d

-- 查看创建数据库
show databases;

-- 查看当前使用的数据库
select database();

-- 使用数据库
use python_info;

-- sql语句最后需要有分号;结尾
-- 显示数据库版本
select version();
-- 显示时间
select now();
-- 创建数据库 create
create database demo;
-- 指定字符集不是 utf-8
create database demo charset=utf8;

-- 查看数据库的创建语句
show create database demo;

-- 删除数据库
drop database demo;

-- 数据表的操作

-- 查看当前数据库中所有表
show tables;

-- 创建表
-- auto_increment表示自动增长
-- 创建一个学生的数据表(id、name、age、high、gender、cls_id)
-- create table 数据表名字 (字段类型约束[, 字段类型约束]);
-- 多个约束不分先后顺序
-- enum 表示枚举男: 原始值会有一个枚举值(从1开始)对应
-- 最后一个字段不要添加逗号
create table students(
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 default 0
);

-- 创建students表

-- 查看表的创建语句
show create table classes;

学生表
+--------+-------------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | 0.00 | |
| gender | enum('男','女','中性','保密') | YES | | NULL | |
| cls_id | int(10) unsigned | YES | | 0 | |
+--------+-------------------------------------+------+-----+---------+----------------+

-- 修改表结构 alter
-- 修改表-添加字段
-- alter table 表名 add 列名类型/约束;
-- 生日信息
alter table students add birthday datetime;

-- 修改表-修改字段：不重命名版
-- alter table 表名 modify 列名类型及约束;
alter table students modify birthday date;

-- 修改表-修改字段：重命名版
-- alter table 表名 change 原列名新列名类型及约束;
alter table students change birthday birth date;

-- 修改表-删除字段
alter table students drop birth;

-- 删除表
drop table students;

-- 数据增删改查(curd)

-- 增加 insert
-- 全列插入值和表的字段的顺序一一对应
-- 可有可无
-- insert [into] 表名 values(...)
-- [] 表示可有可无
-- id 字段不能够省略必须通过占位来解决 0 NULL default
-- SQL中枚举值默认从1 开始
insert into students values (0, "小乔", 25, 180, 1, 1);
insert into students values (NULL, "曹操", 35, 180, 1, 2);
insert into students values (NULL, "刘备", 35, 180, "保密", 2);
# 错误:insert into students values (NULL, "刘备", 35, 180, "不知道", 2);

-- 指定列插入
-- 值和列一一对应
-- insert into 表名(列1,...) values(值1,...)
insert into students (name, gender, cls_id) values ("大乔", 2, 1);

-- 多行插入批量插入
-- insert into 表名(列1,...) values (值1,...),(值1,...),...
insert into students (name, gender, cls_id) values ("貂蝉", 2, 1), ("昭君", 2, 2), ("勾践", 2, 1);

-- 修改
-- where 表示修改的范围
-- update 表名 set 列1=值1,列2=值2... where 条件
-- 全表更新
-- 搜易贷 P2P 易租宝
update students set gender = 2;
-- sql中通过一个等于号表示相等
update students set gender = 1 where id = 2;

-- 删除
-- 物理删除不可撤销
-- DELETE FROM tbname [where 条件判断]
# delete from students; # mysql 从删表到跑路去哪儿 dba(数据库管理员)
delete from students where id = 1;
-- 逻辑删除诺基亚手机物理删除对应的手机数据, 个人中心->我的订单-> 订单列表-> 某一个订单 -> 订单详情--> 商品详情页面
-- 给一个数据添加一个标记字段 bit 类型通过设置 0 或者1 来标识数据到底有没有被删除

1. 修改表结构添加一个字段
alter table students add is_delete bit default 0;
update students set is_delete = 1 where id = 7;
-- 查询有哪些学生没有被删除

-- 查询基本使用
-- 查询所有列
-- select * from 表名;
select * from classes;

-- 指定字段查询

-- sql 中表示相等使用 = 而不是 ==

-- 指定条件查询

-- 查询指定列

-- 字段的顺序

-- 可以使用as为列或表指定别名

--------------------------------------------------------------------------------------------------------------------------------------------------------

简单的操作上面就够了，下面是高级查询：

-- 查询
   -- 查询所有字段
   -- select * from 表名;
   select * from students;

   -- 查询指定字段
   -- select 列1,列2,... from 表名;
   select name,age from students;

   -- 完全的形式
   select students.* from students;
   select python_test_1.students.* from students;
   select students.name,students.age from students;

-- 数据库名什么时候能够省略: 查询的数据表在当前正在被使用的数据库中存在
-- 数据表什么时候不能够省略: 当一个sql语句中有多个表的时候表名不建议省略

   -- 使用 as 给字段起别名
   -- select 字段 as 名字.... from 表名;
   select name as 名字,age as 年龄 from students;
   select name as '名字',age as '年龄' from students;
   -- select 表名.字段 .... from 表名;

   -- 可以通过 as 给表起别名
   -- select 别名.字段 .... from 表名 as 别名;
   select s.name,s.age from students as s;

   -- 消除重复行
   -- distinct 字段
   -- 查询班级学生的性别
   select gender from students;

   -- 查询班级有多少种性别
   select distinct gender from students;
   -- id, gender
   select distinct id,gender from students;

-- 条件查询 where 通过某种条件来筛选数据
   -- 比较运算符
       -- >
       -- 查询大于18岁的信息
       select name from students where age > 18;
       -- <
       -- 查询小于18岁的信息

       -- >=
       -- <=
       -- 查询小于或者等于18岁的信息

       -- = 而不是 '=='
       -- 查询年龄为18岁的所有学生的名字
       select name from students where age = 18;

-- != 或者 <> 实际开发中最好使用 ! 表示不等于
-- <> 不够通用

   -- 逻辑运算符
       -- and
       -- 18岁以上的女性
       select * from students where age > 18 and gender = 2;
       -- or
       -- 18以上或者身高超过180(包含)以上
       select * from students where age > 18 or height >= 180;

       -- not 非
       -- 年龄不是18岁的学生
       select * from students where age != 18;
       select * from students where not age = 18;

-- 年龄是小于或者等于18 并且是女性
select * from students where age <=18 and gender = 2;

   -- 模糊查询
       -- like
       -- % 表示任意字符可有可无
       -- 查询姓名中以 "小" 开始的名字
       select * from students where name like "小%";
       select * from students where name like "%杰%";

       -- _ 表示任意一个字符
       -- 查询有2个字的名字
       select * from students where name like "__"

-- 查询有3个字的名字
select * from students where name like "___"

       -- rlike 正则
       -- match sub findall
       -- r""

       -- 查询以周开始的姓名
       select * from students where name rlike "^周.*";
       select * from students where name rlike "^周.*伦$";

   -- 范围查询
       -- in表示在一个非连续的范围内
       -- 查询年龄为18、34岁的学生
       select * from students where age = 18 or age = 34;
       select * from students where age in (18,34,40);

       -- not in 不在非连续的范围之内
       -- 年龄不是 18、34岁的学生的信息
       select * from students where age not in (18,34);

-- 年龄不是 18、34岁之间的信息
select * from students where age < 18 or age > 34;

       -- 18 ~ 34
       select * from students where not (age < 18 or age > 34);
       select * from students where age >= 18 and age <=34;

       -- between ... and ...表示在一个连续的范围内两边都会包含
       -- 查询年龄在18到34之间的的信息
       select * from students where age between 18 and 34;

       -- not between ... and ...表示不在一个连续的范围内
       -- 查询年龄不在在18到34之间的的信息
       select * from students where age not between 18 and 34;
       错误的: select * from students where age not (between 18 and 34);

   -- 空判断 null 不能够使用比较运算符
       -- 查询身高为空的信息
       select * from students where height is null;

-- 查询身高不为空的学生
select * from students where height is not null;

-- 排序从大到小--> 降序排序从小到大-->升序排序
   -- order by 字段默认就是升序排序 asc 可以省略
   -- asc从小到大排列，即升序

   -- 查询年龄在18到34岁之间的男性，按照年龄从小到大排序
   select * from students where age between 18 and 34 and gender = 1 order by age asc;

   -- 降序 desc
   -- desc从大到小排序，即降序
   -- 查询年龄在18到34岁之间的女性，身高从高到矮排序
   select * from students where age between 18 and 34 and gender = 2 order by height desc;

   -- order by 多个字段 order by age asc, height desc
   -- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序
   select * from students where age between 18 and 34 and gender = 2 order by height desc, age asc;
   -- 查询年龄在18到34岁之间的女性，身高从高到矮排序, 如果身高相同的情况下按照年龄从小到大排序, 如果年龄也相同那么按照id从大到小排序
   select * from students where age between 18 and 34 and gender = 2 order by height desc, age asc, id desc;
   -- 按照年龄从小到大、身高从高到矮的排序
   select * from students order by age asc, height desc;

-- 聚合函数
   -- 总数
   select count(*) from students;
   -- count()
   -- count(*) 以行单位来进行统计个数
   -- count(*) 效率更高, 效率略差:count(id)--> 获取对应的行--> 获取该行对应字段是否为NULL

   -- 查询男性有多少人，女性有多少人
   select count(*) from students where gender = 1;
   select count(*) from students where gender = 2;

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

-- 查询女性的最高身高

-- 查询最大年龄的学生的名字
select name, max(age) from students;

   # 错误: select name, max(height) from students where gender = 2
   -- 静香,小乔 180

   -- 最小值
   -- min()
   select min(age) from students;

   -- 求和
   -- sum()
   -- 计算所有人的年龄总和
   select sum(age) from students;

-- 计算平均年龄
select sum(age)/count(age) from students;

# 错误select sum(height)/count(*) from students;

   -- 平均值
   -- avg()
   -- 计算平均年龄
   select avg(age) from students;

-- 计算平均身高
select avg(height) from students

   -- 四舍五入 round(123.23 , 1) 保留1位小数
   -- 计算所有人的平均年龄，保留2位小数
   select round(avg(age),2) from students;

-- 计算男性的平均身高保留2位小数
select round(avg(height),2) from students where gender = 1;

-- 分组
   -- group by 字段
   -- 查询班级学生的性别
   select gender from students;

   -- 查看有哪几种性别
   select distinct gender from students;

   -- 按照性别分组
   select gender from students group by gender;

-- 计算每种性别中的人数
select count(*) from students group by gender;

   -- group_concat(...)
   # 错误的 select gender,name from students group by gender;
   select gender, group_concat(name,"-",age) from students group by gender;
   -- 查询同种性别中的姓名和身高
   select gender, group_concat(name, "-", height) from students group by gender;

   -- 计算男性的人数
   select count(*) from students where gender = 1;
   -- 通过分组来实现
   select gender, count(*) from students group by gender having gender = 1;

   -- 使用having
   -- 可以使用having 表示对于已经分组的数据做进一步的筛选
   -- 除了男生以外的分组的人数
   select gender, count(*) from students group by gender having not gender = 1;
   select gender, count(*) from students group by gender having gender != 1;

   -- having 对于分组之后的数据做进一步的筛选
   -- 查询每种性别中的平均年龄avg(age)
   select gender,avg(age) from students group by gender;
   -- 查询每种性别中的平均年龄avg(age), 最大年龄,平均身高,最高身高
   select gender,avg(age),max(age), avg(height), max(height) from students group by gender;
   -- 查询平均年龄超过30岁的性别，以及姓名
   select gender,group_concat(name) from students group by gender having avg(age) > 30;

   -- having 和 where 的区别
   where: 对于源数据的筛选
   having: 需要对于分组之后的数据做进一步的筛选操作有having 就一定有group by, 有group by 不一定有having

-- 分页
   -- limit start, count
   -- start 默认值为0 可以省略
   -- 每页取多少条数据
   -- start 表示跳过多少条数据
   每页显示5条数据                           已知每页显示m条数据,和当前要显示的页码n(从1开始), 求需要显示哪些数据
                                           limit (n - 1) * m, m;

第1页
select * from students limit 0,5;

第2页
select * from students limit 5,5;

第3页
select * from students limit 10,5;

第4页
select * from students limit 15,5;

   -- 每页显示4个，显示第3页的信息, 按照年龄从小到大排序
   # 错误 select * from students limit 8,4 order by age asc;
   -- 正确的顺序
   select * from students order by age asc limit 8,4;

-- 连接查询将两个表按照某种条件合并到一起
-- 学生名: students;
-- 班级名: classes

   -- 查询学生的信息和学生对应的班级名字
   -- 触发笛卡尔积查询
   -- 效率低
   select students.*, classes.* from students, classes where students.cls_id = classes.id;

   -- inner join ... on 内连接查询
   select students.*, classes.* from students inner join classes on students.cls_id = classes.id;
   select * from students inner join classes on students.cls_id = classes.id;
   -- 连接查询
   -- 按照要求显示姓名、和学生对应的班级的名字(学生所在的班级)
   select students.name,classes.name from students inner join classes on students.cls_id = classes.id;

-- 在以上的查询中，将班级名字显示在第1列
select classes.name ,students.name from students inner join classes on students.cls_id = classes.id;

   -- 查询学生所在的班级, 按照班级进行排序
   -- select c.xxx s.xxx from student as s inner join classes as c on .... order by ....;
   -- 按照条件连接students和classes
   select classes.name ,students.name from students inner join classes on students.cls_id = classes.id order by classes.name;
   select c.name ,s.name from students as s inner join classes as c on s.cls_id = c.id order by c.name;

   -- 外连接查询: left join + right join
   -- left join 左外连接查询
   -- 查询每位学生对应的班级信息
   select * from students left join classes on students.cls_id = classes.id;

   -- right join 右外连接查询使用的比较少
   -- 将数据表名字互换位置，用left join完成
   select * from students right join classes on students.cls_id = classes.id;
   select * from classes left join students on students.cls_id = classes.id;

   -- 内连接和外连接的其他写法
   -- 内连接的其他写法
   select * from students join classes on students.cls_id = classes.id;
   select * from students cross join classes on students.cls_id = classes.id;

-- 外连接的其他写法
select * from students left outer join classes on students.cls_id = classes.id;

-- 自关联自己关联自己 a inner join a

准备数据
create table areas(
aid int primary key,
atitle varchar(20),
pid int
);

-- 通过 source 指令导入一个sql文件
-- 省级联动 url:http://demo.lanrenzhijia.com/2014/city0605/

-- 查询所有省份
select * from areas where pid is null;

   -- 查询出广东省有哪些市
   -- 发挥想象力一张表想象成两张表
   -- 连接查询
   select p.atitle, c.atitle from areas as p inner join areas as c on c.pid = p.aid where p.atitle = "广东省";

   -- 查询出广州市有哪些区县
   -- p: parent s: son
   select p.atitle, s.atitle from areas as p inner join areas as s on s.pid = p.aid where p.atitle = "广州市";

适用于无限向下分级的业务场景