1、MySql中常用数据类型介绍
在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制性的验证,包括数据字段的类型、约束。
常见的数据类型
- 整数:int
- 小数:decimal (decimal表示浮点数,如decimal(5,2)表示共存5位数,小数占2位)
- 字符串:varchar,char (char表示固定长度的字符串,如char(3),如果填充’ab’时会补一个空格为’ab ‘,varchar表示可变长度的字符串,如varchar(3),填充’ab’时就会存储’ab’)
- 日期时间: date, time, datetime
- 枚举类型:enum
特别说明的类型
字符串text表示存储大文本,当字符大于4000时推荐使用
对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个服务器上,然后在表中存储这个文件的保存路径
约束
(重要内容)
- 主键primary key:物理上存储的顺序
- 非空not null:此字段不允许填写空值
- 惟一unique:此字段的值不允许重复
- 默认default:当不填写此值时会使用默认值,如果填写时以填写为准
- 外键foreign key:对关系字段进行约束,当为关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,如果不存在则填写失败并抛出异常
举例:
连接数据库:
从cmder直接连接
数据库的连接、创建:
"""
cmder操作数据库流程
1、连接数据库 mysql -uroot -p
2、选择数据库 use `数据库`;
3、数据的增删改查
"""
-----链接数据库(先注意系统环境添加sql的路径 C:\Program Files\MySQL\MySQL Server 8.0\bin)
mysql -u用户名 -p
即:mysql -uroot -p
再输入密码:root
不安全的登陆方式
mysql -u用户名 -p密码
即:mysql -uroot -proot
-----退出数据库
exit/quit
----查看数据库
show databases; (分号要加)
--大小写都可以识别
-----查看数据库版本
select version(); (分号要加)
-----显示时间
select now(); (分号要加)
-----创建数据库
create database `数据库` charset=utf8; (` 是tab上面的按钮)
即:create database `python-02` charset=utf8;
-----查看创建数据库语句
show create database ...;
即:show create database 'python-02';
-----查看当前使用的数据库
select database();
-----使用数据库
use `数据库的名字`;
即:use `python-02`;
-----删除数据库
drop database `数据库名`;
即:drop database `python-02`;
数据表的操作:
# 数据表的操作
'''
(操作数据表之前需要先进入和使用数据库;例:use `python-02`;)
-----展示当前数据库中所有表的数据
show tables;
-----创建表
create table 数据表名字(字段 类型 约束[, 字段 类型 约束]);
即:create table demo(id int,name varchar(20)); (不加约束也可以创建)
约束:auto_increment 表示自动增长
not null 表示不能为空
primary key 表示主键
default 表示默认值
-----描述表 ---这里查看的是表的各种属性
desc 数据表的名字;
即:desc demo;
-----创建students表(id,name,age,high,gender,cls_id) # 不能连接数据库之后直接创建表,需要先选择不同名称的数据库
CREATE TABLE student(
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, ---无负数的 unsigned只能在类型后面,否则报错,修饰int的
`name` VARCHAR(30),
age TINYINT UNSIGNED DEFAULT 0,
high DECIMAL(5,2), ---decimal(5,2)代表5位数保留小数点后2位
gender ENUM('男','女','保密','中性') DEFAULT '保密',
cls_id INT UNSIGNED ----这里不能有,号
);
---插入数据
insert into students values(0,"王",18,175.88,"男",1); ----0 是自动填充的意思
----选择表
select * from students;
-----创建classes表(id、name)
create table classes(
id int unsigned not null primary key auto_increment;
name varchar(30),
);
---插入数据
insert into classes values(0,"python1"); ----0 是自动填充的意思
insert into values(0,"python04大神")
----选择表
select * from classes; ---这里能看到已经存在的具体数据对象
-----查看表的创建语句
show create table 表名字;
show create table 表名字\G
-----修改表-添加字段
---alter tables 表名 add 列名 类型;
alter table students add brithday datetime; ---datatime 代表年月日时分秒 只精确到年月日可以用date
-----修改表-修改字段:不重复命名版
--alter table 表名 change 原名 新名字 类型及约束;
alter table students change brithday birth date default '1997-01-01';
-----修改表-删除字段
--alter table 表名 drop 列名;
alter table students drop high;
-----删除表 -----慎用
--drop table students;
-----删除数据库 -----慎用
--drop database 数据库名称;
drop database 'python_01';
数据库中表中的数据的增删改查(curd);
DML(数据管理语言)增删改查 查询数据操作(重要)
-----增删改查(curd)
--增加
--全列插入
-insert [into] 表名 values(...)
--主键字段 可以用 0 null default 来占位
--向classes表中插入一个班级
insert into classes values(0,"pyhton班级");
--向students表中插入一个学生信息
+--------+-------------------------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------------------------+------+-----+------------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | 0 | |
| high | decimal(5,2) | YES | | NULL | |
| gender | enum('男','女','保密','中性') | YES | | 保密 | |
| cls_id | int(10) unsigned | YES | | NULL | |
| birth | date | YES | | 1997-01-01 | |
+--------+-------------------------------+------+-----+------------+----------------+
insert into students values(null,'张三',18,178,'男',1,'1997-01-01'); # 给出的数据数目不匹配的时候创建不成功
insert into students values(default,'2',18,178,'男',1,'1997-01-01');
insert into students values(0,'3',18,178,'男',1,'1997-01-01');
---失败的类型
insert into students values(0,'666',18,178,'第五性别',1,'1997-01-01'); --不匹配创建表的属性会报错
---枚举类型插入 1-->男 2---> 女 3-->保密 4--->中性 下标从1开始
insert into students values(0,'4',18,178,1,1,'1997-01-01');
insert into students values(0,'5',18,178,2,1,'1997-01-01');
---部分插入
--insert into 表名(如列1,...) values(值1,...) --注意非空字段(当表中有属性需要非空时候,必须在列属性中添加)
insert into students(name,gender) values('小乔',2);
---多行插入
insert into students(name,gender) values('周瑜',1),('空格',1); --,隔开就可以
-----修改
--update 表名 set 列1=值1,列2=值2... where 条件;
update students set age=18; --修改所有年龄为18
update students set age=28 where name='1';
update students set age=28 where id=5;
update students set gender=2 where id=5;
update students set gender=2 where id=25; --没有id=25的,不报错,不改数据
-----删除
--物理删除
--delete from 表名 where 条件;
delete from students where name='1';
delete from students where id=1; --id 主键唯一,防止删除name相同的数据
--逻辑删除
--用一个字段来表示 这条信息是否已经不能再使用了
--给students表添加一个is_delete字段 int 类型 0代表未删除 1代表删除
alter table students add is_delete int default 0;
update students set is_delete = 1 where id = 6;
此时再查询数据 显示的数据不包含上面设定is_delete = 1的数据
select * from students where is_delete=0;
如果输入
select * from students; 数据都会显示
-----查询基本使用
--select 去重选项 字段列[as 字段名] from 数据表 where [group by 子句] [having子句][order by 子句]
--查询所有列
select * from students;
---去除重复字段的查询
select distinct from students;
select distinct name,id from students;
----查询指定列
--select distinct 列名1,列名2... from 表名; --查询的列名整体进行去重 列1,列2等
select distinct name,id from students;
-----可以使用as为列或表指定别名
select distinct id,cls_id from students;
+----+--------+
| id | cls_id |
+----+--------+
| 1 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
+----+--------+
8 rows in set (0.00 sec)
select distinct id,cls_id as 班级 from students;
+----+------+
| id | 班级 |
+----+------+
| 1 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 1 |
| 7 | NULL |
| 8 | NULL |
| 9 | NULL |
+----+------+
8 rows in set (0.00 sec)
'''
# 数据库的查询
--查询所有字段
select * from students;
--查询指定字段
select 列1,列2,...from 表名;
select name from students;
--使用as 给字段起别名
select 字段 as 名字... from 表名;
select name as 姓名 from students;
--select 表名.字段 ... from 表名;
select students.name as 姓名 from students;
--消除重复行
distinct 字段
select disctinct name as 姓名 from students;
# 比较运算符
--条件查询
-比较运算符
select ... from 表名 where ...;
-查询大于18岁的信息 大于等于 >=
select * from students where age > 18;
-查询小于18岁的信息 小于等于 <=
select * from students where age < 18;
--查询年龄为18岁的学生名字 不等于 !=
select name from students where age = 18;
# 逻辑运算符
--and
--18到28之间所有学生的信息
select * from students where age >18 and age < 28;
--查询ID大于2的女同学
select * from students where id > 2 and gender =2;
select * from students where id > 2 and gender ="女";
--or
--查询编号小于4或没有被删除的学生 --is_delete = 0
select * from students where id <4 or is_delete = 0;
--not
--不在18岁以上的女性 范围 先找18以上的女性再加not
select * from students where not age > 18 and gender =2;
select * from students where (not age > 18) and gender =2; --()优先级高
select * from students where not (age > 18 and gender =2); --与前面两个语句的查询信息不同,表达意思不同
--年龄不是小于或者等于18 并且是女性
select * from students where (not age <= 18) and gender =2;
# 模糊查询
--like
--% 替换一个或者多个字符
--_ 替换一个
--查询姓名中以“a”开始的名字
select * from students where name like 'a%';
--查询姓名中包含有“a”的名字
select * from students where name like '%a%';
--查询姓名中2个字的名字
select * from students where name like '__';
--查询姓名中至少有2个字的名字
select * from students where name like '__%';
# 范围查询
--in(1,3,8)表示在一个非连续的范围内
--查询ID编号是1或者3或8的学生
select * from students where id =1 or id = 3 or id = 8;
select * from students where id in(1,3,8); --效果等同上面
--查询年龄为18,34的姓名
select name,age from students where age=18 or age=34;
select name,age from students where age in(18,34);
select name,age from students where name in('a','b');
--not in 不在非连续的范围之内
--年龄不是18,34岁之间的信息
select name,age from students where age not in(18,34);
--between ... and ... 表示一个连续的范围内
--查询ID是3到8的学生
select name,age from students where id between 3 and 8;
--查询ID是3到8的男学生
select name,age from students where id between 3 and 8 and gender=1;
select name,age from students where (id between 3 and 8) and gender=1;
--not between ... and ... 表示不在一个连续的范围内
--查询年龄不在18到34之间的信息
select name,age from students where age not between 18 and 34;
select name,age from students where not age between 18 and 34; --一般不用这种语句
# 空判断
--判空is null null != "" 注意区分null 和" "的区别
--查询cls_id为空的信息
select * from students where cls_id is null;
select * from students where cls_id = " "; --注意和上面语句不同意思
--判非空is not null
select * from students where cls_id is not null;
# 聚合函数
--总数
--count()
--查询学生总数
select * from students;
select count(*) from students;
select count(*) as 总人数 from students; --将count(*)改名称
--查询男学生总数,女生总数
select count(*) as 男性总人数 from students where gender =1;
select count(*) as 女性总人数 from students where gender =2;
--最大值
--max()
--查询最大的年龄
select max(age) from students;
--查询男生最大的ID
select max(id) from students where gender =1;
--查询女生最高的身高
select max(high) from students where gender =2;
--最小值
--min()
--查询未删除的学生最小的编号
select min(id) from students where is_delete=0;
--求和
--sum() --如果对其他类型数据求和,不会计算出数值,如果是数值int,float等类型会被计算
--查询男生的年龄和
select sum(age) from students where gender =1;
--平均值
--avg()
--查询未删除女生的ID的平均值
select avg(id) from students where gender =2 and is_delete = 0; --默认保留四位小数
--四舍五入
--round(123.23,1) 保留一位小数
--计算所有人的平均年龄,保留两位小数
select round(avg(age),2) from students;
表的分组、排序、查询:
# 分组
--group by
--按照性别分组,查询表中的出现的所有的性别
select gender from students group by gender; --一般会写聚合函数
--计算男生女生的人数
select gender as 性别,count(*) from students where gender=1;
select gender as 性别,count(*) from students where gender=2;
select gender as 性别,count(*) from students group by gender; --这句相当于上面两句语句的效果
--男女同学最大年龄
select gender as 性别,max(age) from students group by gender;
select gender as 性别,max(age),name from students group by gender;
--group_concat(...) --查看组内的信息
--查询同种性别中的姓名
select gender as 性别,max(age),group_concat(name) from students group by gender;
--查询组内成员年龄,姓名
select gender as 性别,max(age),group_concat(name,age) from students group by gender;
select gender as 性别,group_concat(name,age) as `组内姓名-年龄` from students group by gender;
select gender as 性别,group_concat(name,'_',age) as `组内姓名-年龄` from students group by gender;
--分组之后的筛选
--having
--查询男生女生总数大于2
select gender,count(*) from students group by gender;
select gender,count(*) from students group by gender having count(*)>2; --group by 后面只能用having 不能用where
--查询男生女生总数大于2的姓名
select gender,count(*),group_concat(name,'_',age) from students group by gender having count(*)>2;
----查询男生女生平均年龄超过18的姓名
--having avg(age)>18
select gender,group_concat(name),avg(age) from students group by gender having avg(age)>18;
####--------------------------------------------------------------------------------------------------------------------
--排序
-order by 字段
-asc从小到大排序,即升序,默认是asc
-desc是从大到小排序,即降序
--查询年龄在18到26岁之间的男同学,按照年龄从小到大排序
select *from students where age between 18 and 26 and gender =1 order by age asc;
select *from students where age between 18 and 26 and gender =1 order by age;
--查询年龄在18到34岁之间的女同学,按照身高从高到矮排序
select *from students where age between 18 and 34 and gender =2 order by high desc;
select *from students where (age between 18 and 34) and gender =2 order by high desc;
-order by 多个字段
--查询年龄在18到28岁之间的女性,身高从高到矮排序,如果身高一样就按照年龄从小到大排序
select *from students where (age between 18 and 34) and gender =2 order by high desc,age asc;
--按照年龄从小到大,身高从高到矮排序
select * from students order by age asc,high desc;
--分页
--limit start,count;
--限制查询出来的数据个数
select * from students limit 2;
--查询前5条数据
select * from students limit 5;
select * from students limit 0,5; --与上面的语句效果一样 这里的数字0代表的是start,是从id为1开始的数据输出;5代表的是count,总数据数目
--查询id为6-10(包含)的数据
select * from students limit 5,5;
--制作分页 --limit (第N页-1)*每页的个数,每页的个数;
--每页显示2个,第1个界面
select * from students limit 0,2;
--每页显示2个,第2个页面
select * from students limit 2,2;
--每页显示2个,第3个页面
select * from students limit 4,2;
--每页显示2个,第4个界面
select * from students limit 6,2;
--错误写法
select * from students limit 2*(5-1),2; --不能写公式
select * from students limit 2 order by age; --limit只能放在最后 分页是对结果的操作
--连接查询
--inner join...on 内连接 交集
-select ... from 表A inner join 表B on...;
select * from students inner join classes; --显示students表和classes的表会出现的所有可能结果
--查询有能够对应班级的学生以及班级信息 交集
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;
--给数据表起名字
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.name from students as s inner join classes as c on s.cls_id = c.id order by c.name;
--当同一个班级的时候,按照学生的id进行从小到大排序
select s.id,s.name,c.name from students as s inner join classes as c on s.cls_id = c.id order by c.name,s.id;
--左连接 left join on 交并补集 对左边的表占主导
--查询每位学生对应的班级信息
select * from students as s left join classes as c on s.cls_id =c.id;
--查询没有对应班级信息的学生
-select ... from 表A as s left join 表B 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; --注意不能用 c.id=null
--右连接 right join on
--可以用数据表位置换一下,以left join 完成
--子查询
--查询最高的男生的信息
select * from students where high = (select max(high) from students where gender =1); --用这个语句,条件在括号里面加
select * from students where high = (select max(high) from students) and gender =1;
--查询高于班级平均身高的信息
select * from students where high >(select avg(high) from students);
--列级子查询
--查询有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id = classes.id;
select * from students where cls_id in(select id from classes); --两句结果相同
--自关联
--省市区的关联
select * from areas where pid =1;
select * from areas where type =1;
select * from areas where name = '黑龙江';
select * from areas where pid=12;
select * from areas where pid=167;
--黑龙江下面的市、区等 (重要)
select * from areas as province inner join areas as city on province.id = city.pid having province.name = '黑龙江'; --查询黑龙江省下面包含的市
select * from areas as province inner join areas as city on province.id = city.pid having province.name = '哈尔滨'; --查询哈尔滨是下面包含的区