Mysql的学习知识

-- encoding: utf-8 --

“”"
@File : mysql_knowledge.py
@Time : 2019/9/9 22:02
@Author : chen

“”"

数据库操作

‘’’

-----链接数据库（先注意系统环境添加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 databasepython-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’;

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;