数据库相关操作

xiaoxiao2024-11-25 87

启动服务 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 = "广州市"; 适用于无限向下分级的业务场景

最新回复(0)