前面我分享了使用 Python 爬虫来获取网站上的数据,其实在企业中,数据通常是放在数据库中,要获取这些数据,需要使用数据库查询语句。
今天我就分享一些数据库常用查询语句。
数据库的常用操作语句
--显示数据库版本 select version(); --显示时间 select now(); --查看所有数据库 show databases; --创建数据库 create database 数据库名 charset=utf8; --查看创建数据库的语句 show create database 数据库名; --查看当前使用的数据库 select database(); --使用数据库 use 数据库名; --删除数据库 drop database 数据库名;
数据表的常用操作语句
--查看当前数据库中的所有表 show tables; --创建表 --auto_increment 表示自动增长 --not null 表示不能为空 --primary key表示主键 --default 默认值 create table 数据表名(字段 类型 约束,字段 类型 约束);例如:create table students( id int primary key not null auto_increment, name varchar(30) );--查看表的结构 desc 数据表名;--查看表的创建语句 show create table 表名;--修改表(添加字段) 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 default "2000-01-01";--修改表(删除字段) alter table 表名 drop 列名; 例如:alter table students drop high;--删除表 drop table 表名;
数据的增删改查语句
--增加 --全列插入 insert [into] 表名 values(...); --主键字段可以用 0 null default 来占位 例如:insert into students values(0, '小李', 20, '女', 1, '1991-01-01'); 例如:insert into students values(null, '小李', 20, '女', 1, '1991-01-01'); 例如:insert into students values(default, '小李', 20, '女', 1, '1991-01-01'); --部分插入 insert into 表名(列1,...) values(值1,...); 例如:insert into students(name, gender) values('小王', '男'); --多行插入
例如:insert into students(name, gender) values('小王', '男'), ('小文', '女');
--删除 --物理删除 delete from 表名 where 条件 --逻辑删除 --用一个字段来表示这条信息是否已经不能再使用了 --例如:给 students 表添加一个 is_delete 字段 bit 类型 alter table students add is_delete bit default 0; update students set is_delete=1 where id=6;
--修改 update 表名 set 列1=值1,列2=值2,... where 条件; --全部修改 update students set gender=1; --只要 name 是小李的全部修改 update students set gender=1 where name='小李'; --只要 id 为 3 的进行修改 update students set gender=1 where id=3;
--查询 --查询所有列 select * from 表名; 例如:select * from students; --指定条件查询 例如:select * from students where name='小李'; 例如:select * from students where id=3; --查询指定列 select 列1,列2...from 表名; 例如:select name,gender from students; --可以使用 as 为列指定别名 例如:select name as 姓名, gender as 性别 from students; --可以通过 as 给表指定别名例如:select s.name, s.age from students as s; --消除重复行 例如:select distinct gender from students;
数据库查询进阶语句
条件查询
--比较运算符 --等于: = 例如:select * from students where age = 18; --大于: > 例如:select * from students where age > 18; --大于等于: >= 例如:select * from students where age >= 18; --小于: < 例如:select * from students where age < 18; --小于等于: <= 例如:select * from students where age <= 18; --不等于: != 或 <> 例如:select * from students where age != 18;--逻辑运算符 --and 与 例如:select * from students where age > 18 and age < 64; --or 或 例如:select * from students where age > 18 or gender = '女'; --not 非 例如:select * from students where not (age > 18 or gender = '女'); 例如:select * from students where (not age > 18) or gender = '女'; --xor 有且只能一个 例如:select * from students where age > 18 xor gender = '女'; --模糊查询 --like --% 表示任意多个任意字符 例如:select name from students where name like '小%'; 例如:select name from students where name like '%小%'; --_ 表示一个任意字符 例如:select name from students where name like '__'; --查询有两个字的名字 例如:select name from students where name like '__%'; --查询至少有两个字的名字 --rlike 正则 例如:select name from students where name rlike '^周.*'; --查询以周开始的名字 例如:select name from students where name rlike '^周.*伦$'; --查询以周开始,以伦结尾的名字--查询含有单引号 --查询 name 叫 Eugene O'Neill 的学生,Eugene O'Neill 的单引号在查询时需要写两次,不是写成双引号 例如:SELECT * FROM students WHERE name = 'Eugene O''Neill'--范围查询 --in 表示在一个非连续的范围内 例如:select * from students where age in (18, 34, 36); --not in 表示在一个不非连续的范围内 例如:select * from students where age not in (18, 34, 36); --between ... and ... 表示在一个连续的范围内 例如:select * from students where age between 18 and 42; --not between ... and ...表示不在一个连续的范围内 例如:select * from students where age not between 18 and 42;--空判断(注意:null 与 '' 是不同的) --判空 is null 例如:select * from students where height is null; --判非空 is not null 例如:select * from students where height is not null;--排序 --order by 字段,默认按照列值从小到大排列(asc) --asc 从小到大排序,即升序 --desc 从大到小排序,即降序 --语法: select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...] --例如:查询年龄在 18 到 34 岁的女性,身高从高到矮排序,身高相同的情况下按照年龄从小到大排序,年龄也 相同的情况下按照 id 从大到小排序 select * from students where (age between 18 and 34) and gender = 2 order by height desc, age asc, id desc ; --case when .. then a else b end :如果满足 when 后面的条件为 a,否则为 b
例如:select (case when avg(age)=2 then 1 else 0 end) as score from students where gender = 1;
聚合函数
--总数 count
--count(*)表示计算总行数,括号中写 * 与列名,结果是相同的 例如:select count(*) from students where gender = 1; --最大值 max --max(列)表示求此列的最大值 例如:select max(age) from students where gender = 1; --最小值 min --min(列)表示求此列的最小值 例如:select min(age) from students where gender = 1; --求和 sum --sum(列)表示求此列的和 例如:select sum(age)/count(*) from students where gender=1; --平均值 avg --avg(列)表示求此列的平均值 例如:select avg(age) from students where gender = 1; --四舍五入 round --round(112.223, 1)保留 1 位小数 例如:select round(avg(age), 1) from students where gender = 1; --字符串连接 concat --concat('a','b')=='ab' 例如:select concat(avg(age), '%') from students where gender = 1;
分组 --group by 将查询结果按照 1 个或多个字段进行分组,字段值相同的为一组 --group by 可用于单个字段分组,也可用于多个字段分组 例如:select gender from students group by gender; --group by + group_concat() group_concat(字段名)可以作为一个输出字段来使用 --根据分组结果,使用 group_concat() 来放置每一组的某字段的值的集合 例如:select gender, group_concat(name) from students group by gender; 例如:select gender, group_concat(name, age, id) from students group by gender; 例如:select gender, group_concat(name, '_', age, ' ', id) from students group by gender; --group by + 集合函数 例如:select gender, avg(age) from students group by gender ; --group by + having 用来分组查询后指定一些条件来输出查询结果 --having 作用和 where 一样,但 having 只能用于 group by 例如:select gender, group_concat(name), avg(age) from students group by gender having avg(age) > 30; --group by + with rollup --with rollup 的作用是:在最后新增一行,来记录当前列里所有记录的总和 例如:select gender, count(*) from students group by gender with rollup;
分页 --select * from 表名 limit start,count --表示从 start 开始,获取 count 条数据 --限制查询出来的数据的个数 例如:select * from students where gender = 1 limit 2; --查询前五个数据 例如:select * from students limit 0, 5; --查询 6~10 的数据 例如:select * from students limit 5, 5; --limit 需放在语句最后 例如:select * from students where gender = 1 order by age asc limit 2;
连接查询 --inner join 内连接查询:查询的结果为两个表匹配到的数据 例如:select s.*, c.name from students as s inner join classes as c on s.cls_id = c.id; --left join 左连接查询:查询的结果为两个表匹配到的数据,左表特有的数据,对于右表中不存在的数据使用null填充 例如:select s.*, c.name from students as s left join classes as c on s.cls_id = c.id; --right join 右连接查询:查询的结果为两个表匹配到的数据,右表特有的数据,对于左表中不存在的数据使用null填充 --相当于将数据表名字互换位置,用left join完成 例如:select s.*, c.name from students as s right join classes as c on s.cls_id = c.id;
自关联 --一张表上同时包含关联信息, --例如省市区资料 select * from areas as province inner join areas as city on province.aid = city.pid having province.atitle = '河南省'; --子查询:在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语 句 --例如查询最高的男生信息 select * from students where (gender = 1) and height = (select max(height) from students);
最后分享一张图片,可以帮助快速记住数据库查询语句的顺序