数据库的操作
链接数据库
mysql -uroot -p
mysql -uroot -p[password]
退出数据库
exit/quit/ctrl+d
sql语句最后需要有分号;结尾
显示数据库版本
select version();
显示时间
select now();
查看所有数据库
show databases;
创建数据库
create database 数据库名 [charset = utf8];
create database python04;
create database python04new charset=utf8;
查看创建数据库的语句
show create database ....
show create database python04;
查看当前使用的数据库
select database();
使用数据库
use 数据库的名字
use python04new;
删除数据库
drop database 数据库名;
drop database python04;
数据表的操作
查看当前数据库当中所有的表
show tables;
创建表
auto_increment表示自动增长
not null 表示不能为空
primary key 表示主键
default 默认值
create table 数据表名字 (字段 类型 约束[, 字段 类型 约束]);
创建classes表(id, name)
create table xxxx(id int, name varchar(30));
create table yyyy(id int primary key not null auto_increment, name varchar(30));
create table zzzz(
id int primary key not null auto_increment,
name varchar(30)
);
查看表的信息
desc 表的名字;
desc xxxx;
创建student表(id, name, age, high, gender, cls_id)
create table students(
id int unsigned auto_increment primary key not null,
name varchar(30),
age tinyint unsigned default 0,
high decimal(5,2),
gender enum("男", "女", "中性", "保密") default "保密",
cls_id int unsigned
);
insert into students values(0, "laowang", 18, 188.50, "男", 0);
select * from students;
创建classes表(id, name)
create table classes(
id int unsigned not null primary key auto_increment,
name varchar(30)
);
查看表的创建语句
show create table 表名字;
show create table students;
修改表-添加字段
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 database 数据库;
drop table 数据表;
drop table xxxx;
增删改查(curd)
增加
全列插入
insert [into] 表名 values(...)
主键字段 可以用 0 null default 来占位
向classes表中插入一个班级
insert into classes values(0, "菜鸟班");
+--------+-------------------------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------------+------+-----+------------+----------------+
| id | int unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| age | tinyint unsigned | YES | | 0 | |
| gender | enum('男','女','中性','保密') | YES | | 保密 | |
| cls_id | int unsigned | YES | | NULL | |
| birth | date | YES | | 2000-01-01 | |
+--------+-------------------------------------+------+-----+------------+--------------
向students插入一个学生信息
insert into students values(0, "小李飞刀", 20, "女", 1, "1990-01-01");
insert into students values(null, "小李飞刀", 20, "女", 1, "1990-01-01");
insert into students values(default, "小李飞刀", 20, "女", 1, "1990-01-01");
失败: insert into students values(default, "小李飞刀", 20, "第4性别", 1, "1990-01-01");
枚举中的下标从1 开始 1---"男" 2---"女" 3---"保密"
insert into students values(default, "小李飞刀", 20, 1, 1, "1990-01-01");
部分插入
insert into 表名(列1,...) values(值1,...);
insert into students(name, gender) values("小乔", "女");
多行插入
insert into students(name, gender) values("大乔", "女"), ("貂蝉", "女");
insert into students values(default, "西施", 20, "女", 1, "1990-01-01"), (default, "王昭君", 20, "女", 1, "1990-01-01");
修改
update 表名 set 列1=值1, 列2=值2... where 条件;
update students set gender=1; 全部都改
update students set gender=1 where name="小李飞刀"; 只要name是小李飞刀的全部修改
update students set gender=1 where id=3; 只要id为3的就修改
update students set age=22, gender=1 where id=4; 只要id为4的，同时修改两处
删除
物理删除
delete from 表名 where 条件;
delete from students; 整个数据表中所有数据全部删除
delete from students where name="小李飞刀";
逻辑删除
用一个字段表示，这条信息已经不能再使用了
给students表添加一个is_delete字段 bit类型
alter table students add is_delete bit default 0;
查询基本使用
查询所有列
select * from 表名;
select * from students;
定条件查询
select * from students where name="小李飞刀"; 查询name为小李飞刀的所有信息
select * from students where id>3; 查询id>3的信息
查询指定列
select name, gender from students;
可以用as为列或表指定别名
select 字段(as 别名) , 字段(as 别名) from 数据表 where...;
select name as 姓名, gender as 性别 from students;
字段的顺序
select id as 序号, gender as 性别, name as 姓名 from students;
数据的准备
创建一个数据库
create database python_test charset=utf8;
使用一个数据库
use python_test;
显示当前使用的数据库
select database();
创建数据表
students表
create table students(
id int unsigned primary key not null auto_increment,
name varchar(20) default "",
age tinyint unsigned default 0,
height decimal(5,2),
gender enum("男", "女", "保密") default "保密",
cls_id int unsigned default 0,
is_delete bit default 0
);
classes表
create table classes(
id int unsigned primary key not null auto_increment,
name varchar(20) not null
);
数据准备
insert into students values
(0, '小明', 18,180.00,2,1,0),
(0, '小月月', 18,180.00,2,2,1),
(0, '彭大侠', 29,185.00,1,1,0),
(0, '刘小华', 59,175.00,1,2,1),
(0, '黄小小', 38,160.00,2,1,0),
(0, '凤凰' , 28,150.00,4,2,1),
(0, '王大侠', 18,172.00,2,1,1),
(0, '周来伦', 36, NULL,1,1,0),
(0, '程二坤', 27,181.00,1,2,0),
(0, '刘宜兴', 25,166.00,2,2,0),
(0, '金星', 33,162.00,3,3,1),
(0, '静香', 12,180.00,2,4,0),
(0, '郭大侠', 12,170.00,1,4,0),
(0, '周杰出', 34,176.00,2,5,0);
insert into classes values
(0, "python_01期"),
(0, "python_02期"),
(0, "python_04期");
查询
查询所有字段
select * from students;
select * from classes;
查询指定字段
select 列1, 列2 from 表名;
select name, age from students;
使用as给字段起别名
select 字段 as 名字... from 表名;
select name as 姓名, age as 年龄 from students;
select 表名.字段 .... from 表名;
select students.name , students.age from students;
可以通过as给表起别名
select 别名.字段 .... from 表名 as 别名;
select s.name , s.age from students as s;
消除重复行
distinct 字段
select distinct gender from students;
条件查询
比较运算符
select ... from 表名 where ....;
>
查询大于18岁的信息
select * from students where age>18;
select id, name, gender from students where age>18;
<
查询小于18岁的信息
select * from students where age<18;
>=
<=
查询小于或者等于18岁的信息
select * from students where age<=18;
=
查询年龄为18岁的所有学生的名字
select * from students where age=18;
逻辑运算符
and
18 到 28之间的所有学生信息
select * from students where age>18 and age<28;
18岁以上的女性
select * from students where age>18 and gender="女";
or
18岁以上或身高超过180以上(包含)
select * from students where age>18 or height >= 180;
not
不在18以上的女性 这个范围内的信息
select * from students where gender=2 and not age > 18;
加括号可以改变优先级
select * from students where not (age > 18 and gender=2);
年龄不是小于或者等于18 并且是女性
select * from students where (not age<=18) and gender=2;
模糊查询
like
% 替换零个，一个或多个
_ 替换一个
查询姓名中 以"小" 开始的名字
select name from students where name like "小%";
查询名字中 有"小" 的所有名字
select name from students where name like "%小%";
查询有2个字的名字
select name from students where name like "__";
查询有3个字的名字
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 "^周.*出$";
范围查询
in (1, 3, 8) 表示在一个非连续的范围内
查询年龄为 18， 34 的姓名
select name, age from students where age = 18 or age = 34;
select name, age from students where age = 18 or age = 34 or age=12;
select name, age from students where age in (12, 18, 34);
not in 不在非连续的范围之内
年龄不是 18， 34岁之间的信息
select name, age from students where age not in (18, 34);
bteween ... and ... 表示在一个连续的范围之内 (左闭右闭)
查询 年龄在18到34之间的信息
select name, age 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); 因为not between是一个整体**
正确的用法是---> **select * from students where not age between 18 and 34;**
判断空
判空 is null
查询身高为空的信息
select * from students where height is null;
select * from students where height is nuLL;
select * from students where height is NULL;
判非空
is not null;
select * from students where height is not null;
排序
order by 字段
asc从小到大排列，即升序(默认)
desc 从大到小排列，即降序
查询年龄在18 到 34岁之间的男性，按照年龄从小到大排序
select * from students where (age between 18 and 34) and gender=1 order by age；
select * from students where (age between 18 and 34) and gender=1 order by age asc;
查询年龄在18 到 34岁之间的女性，按照身高从高到矮排序
select * from students where (age between 18 and 34) and gender=2 order by height desc;
order by 多个字段
若第一个字段相同，按第二个字段，若第二个也相同，按第三个，以此类推
查询年龄在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;
聚合函数
总数
count
查询男性有多少人，女性有多少人
select * from students where gender=1;
select count(*) from students where gender=1;
select count(*) as 男性人数 from students where gender=1;
select count(*) as 女性人数 from students where gender=2;
最大值
max
查询最大的年龄
select max(age) as 最大年龄 from students;
查询女性的最高身高
select max(height) as 女性最高身高 from students where gender=2;
最小值
min
求和
sum
计算所有人的年龄总和
select sum(age) as 年龄总和 from students;
平均值
avg
计算平均年龄
select avg(age) as 年龄平均 from students;
计算平均年龄sum(age)/sum(*)
select sum(age)/sum(*) from students;
四舍五入 round(123.1, 1)保留一位小数
计算所有人的平均年龄，保留2位小数
select round(sum(age)/sum(*), 2) from students;
计算男性的平均身高，保留两位小数
select round(avg(height),2) from students where gender=1;
失败--->select name, round(avg(height),2) from students where gender=1;
分组
group by
按照性别分组，查询所有的性别
失败--->select name from students group by gender;
失败--->select * from students group by gender;
select gender from students group by gender;
计算每种性别当中的人数
select gender, count(*) from students group by gender;
计算男性的人数
select gender, count(*) from students where gender=1 group by gender;
group_concat(...)
查询同种性别中的姓名
select gender, group_concat(name) from students where gender=1 group by gender;
select gender, group_concat(name,age,id) from students where gender=1 group by gender;
select gender, group_concat(name," ",age," ",id) from students where gender=1 group by gender;
having (对分组进行条件判断)
查询平均年龄超过30岁的性别，以及姓名 having avg(age) > 30
select gender, group_concat(name), avg(age) from students group by gender having avg(age) > 30;
查询所有性别中的人数多于2个的信息
select gender, group_concat(name) from students group by gender having count(*)>2;
分页
limit start, count; (limit放在最后)
限制查询出来的数据个数
select * from students limit 2;
查询5个数据
select * from students limit 0, 5;
查询id 6-10(包含) 的分页
select * from students limit 5, 5;
每页显示两个，第1个页面
select * from students limit 0, 2;
每页显示两个，第2个页面
select * from students limit 2, 2;
每页显示两个，第3个页面
select * from students limit 4, 2;
每页显示两个，第4个页面
select * from students limit 6, 2; --->limit (第N页-1)*每页的个数，每页的个数
每页显示两个，显示第6页的信息， 按照年龄从小到大排序
失败--->select * from students limit 2*(6-1), 2;
失败--->select * from students limit 10, 2 order by age;
select * from students order by age limit 10, 2;
连接查询(mysql支持内连接和外连接，其中外连接分为左连接和右连接)
inner join ... on
select ... from 表A inner join 表B;
select * from students inner join classes;
查询 有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;
按要求显示姓名，班级
select students.*, classes.name 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;
给数据表起名字
select s.name, c.name from students as s inner join classes as c on s.cls_id=c.id;
查询 有能够对应班级的学生以及班级信息， 显示学生的所有信息， 只显示班级名称
select s.*, c.name from students as s inner join classes as c on s.cls_id=c.id;
在以上查询中，将班级名字放在第一列
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id;
查询 有能够对应班级的学生以及班级信息,按照班级进行排序
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by s.cls_id;
当同一个班级的时候，按照学生的id进行从小到大排序
select c.name, s.* from students as s inner join classes as c on s.cls_id=c.id order by s.cls_id, s.id;
left join
查询每位学生对应的班级信息
select * from students as s left join classes as c on s.cls_id=c.id;
查询没有对应班级信息的学生
select ... from xxx as s left join xxx as c on ... where ...;
select ... from xxx as s left join xxx as c on ... having ...;
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;(多个表的集使用having)
select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;(一般原表使用where)
right join
将数据表名字互换位置，用left join完成
自关联
通俗理解：一张表里面的某两个或者几个字段之间有关系，简称自关联(我关联我自己)
创建表
create table areas(
aid int primary key not null,
atitle varchar(20) default null,
pid int default null
);
所需数据下载
https://pan.baidu.com/s/1Rk5-Xm5a4Xtckc3RmotYnA 提取码: yn78
在mysql内使用命令 source xxx.sql(需要在文件当前路径)即可执行文件内所有命令添加数据
查询出山东省有哪些市
select * from areas as province inner join areas as city on province.aid=city.pid having province.atitle="山东省";
select province.atitle, city.atitle from areas as province inner join areas as city on province.aid=city.pid having province.atitle="山东省";
子查询
子查询通俗来讲，就是一个select里面还包含有另一个子查询的信息，相当于函数的结果作为另一个函数的参数一样
标量子查询
查询出高于平均身高的信息
select * from students where height>(select avg(height) from students);
查询最高的男生信息
select * from students where height = (select max(height) from students);
列级子查询
查询学生的班级号能对应的学生信息
select * from students where cls_id in (select id from classes);
网友评论