MySql基础

作者: 小飞船1号 | 来源:发表于2020-12-09 18:14 被阅读0次

1. MySQL 基础
MySql数据库基础及IDE
2018-04-09 数据仓库技能要求
mysql基础
mysql索引，锁机制，主从同步，日志分析
MySQL基础语句及常见关键字使用总结
MySQL基础语句与其在Python中的使用
高性能的索引策略
MySQL学习笔记---基础概念和一些基础SQL语句（一）
mysql学习

数据类型与约束

数据类型

int ：整数，有符号(包括负数)和无符号(非负数)，长度没有意义
varchar：字符串，字母和中文或数字都是一个字符
decimal：小数
decimal(3,1)：小数点后1位，整数2位

主键

`id` INT UNSIGNED  PRIMARY KEY AUTO_INCREMENT,
或
 `id` INT UNSIGNED AUTO_INCREMENT,
PRIMARY KEY ( `id` )

表操作

1.建表

create table 表名 （
  键名 类型 约束,
  键名 类型 约束
）
eg:
CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL DEFAULT "我是默认值" COMMENT "注释",
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

UNSIGNED ：无符号的意思，可以理解为非负数
AUTO_INCREMENT：定义列为自增的属性，一般用于主键，数值会自动加1
NOT NULL、NULL：是否可为空NULL
DEFAULT "字符串"：定义默认值
COMMENT "注释"：添加注释
PRIMARY KEY：关键字用于定义列为主键
ENGINE：设置存储引擎
CHARSET：设置编码

2、删表

drop table 表名 
drop table if exists 表名;#如果存在则删除

增删改

insert into 表名 values (值1,值2,值3);
insert into 表名 (字段1,字段2) values (值1,值2);
INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) 
VALUES ("学习 PHP", "菜鸟教程", NOW());

delete from 表名 where 条件 

update 表名 set 字段1=新值1,字段2=新值2 where 条件

增：

不标明字段后边全部值都要写上，标明字段，后边值对应上就行，不用在意顺序
如果数据是字符型，必须使用单引号或者双引号，如："value"

删：

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除
当你想删除数据表中指定的记录时 WHERE 子句是非常有用的

改：

当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的

查询

select * from 表名

1、别名：as

select name as 姓名 ，sex as 性别 from stu
select name ,sex from stu as s
select  s.name , s.sex, r.title from stu as s,runoob_tbl as r

给字段起别名，查询结果表头为别名
给表起别名(多个表之间的操作有意义)
查询两个表的时候，如果两个表有重复字段用别名，容易区分出来是哪个表里的字段

2、DISTINCT：过滤重复数据

select distinct 字段1，字段2 from 表名

若DISTINCT后边字段为多个，查询结果：字段组合只要有一个不一样就不算重复，全部一样才算重复数据，

[图片上传中...(image.png-9523a5-1607507995940-0)]

3、条件查询 where

select 字段 from 表名 where 条件

select * from stu not sex ='女'
select *  from stu where name like "__"

select * from stu  name in ('值1','值2','值3')#相当于下面or
select * from stu name= '值1' or  name='值2' or name='值3' 

select * from stu age between 18 and 20#18-20相当于下面and
select * from stu age>=18 and age<=20
#非
select * from stu not age between 18 and 20# 加not表示18-20之外

比较运算符
= > >= < <=
不等于 != 或<>
逻辑运算符
and or not
模糊查询 like
% 表示任意多个字符
_ 表示一个任意字符，两个_表示要有两个任意字符
范围查询
in 表示一个非连续的范围内，相当于 or
between A and B 表示在一个连续的范围内，A必须<B ，相当于and
空判断
注意 null 与''是不同的,一个是空，一个是空字符串
判空is null、is not null
=null不可以

4、排序 order by

select * from 表名 order by 列1  asc,列2 desc
select * from stu order by convert(name using gbk)

先按字段1进行排序，如果字段1的值相同，在按照字段2排序，以此类推
默认升序：ASC，可省略不写
降序：DESC
中文排序：使用convert(name using gbk)转换编码utf8为gbk(汉字编码字符集)

5、聚合函数

为了快速得到统计数据，经常用到如下5个聚合函数
count(*/列名) ：
count(*)一条数据任何一个字段有值就会统计在内
count(列名)如果有null就不会统计在内
max(列名)：表示此列的最大值
min(列名)：表示此列的最小值
sum(列名)：表示此列的和
avg(列名)：表示求此列的平均值
聚合函数不能在where中使用

select count(*) from stu

6、分组

(1) 分组 group by
各有多少，每种等字眼

select * from 表名 group by 列名
select sex,count(*) from stu group by sex
select class, sex,count(*) from stu group by sex,class
#统计每个班级中每种性别的同学人数

按照字段进行分组，表示此字段相同的数据会被放到一个组内
分组后，分组的依据列会在结果集中，其他列不会显示在结果集中
可以对分组后的数据进行统计，做聚合运算
多个字段分组，字段组合一样才是一个组的

(2) 分组后过滤数据 having

select 列1，列2，聚合函数 from 表名 group by 列名 having 条件
select sex,count(*) from stu group by sex having sex='男'

having 后面的条件运算和where的相同
having 必须跟在group by 后边才可以

(3) where having对比

where ：是对from后面指定的表进行数据筛选，属于对原始数据的筛选--先筛选后分组
having ：是对group by的结果进行筛选 ---先分组后筛选

7、获取部分行 limit

select * from 表名  limit start,count

select  * from stu order by age limit 0,3
select  * from stu  limit 0,3
#和顺序有关，先排序在取前三行，因此上面两个语句结果不一样
select  * from stu  limit 3#取前三条
select * from stu order by age limit 1
#年龄最小的信息，只适用于最小只有一人

当数据量非常大的时候在一页中查看数据是一件非常麻烦的事情
从start开始，获取count条数据
start 索引从0开始
和顺序是有关系的
简写省略的是start

8、连接查询

(1) 等值连接 =

select * from 表1 as b1，表2 as b2 where b1.字段=b2.字段
再有其他条件，直接加在后边即可
select * from stu,course,score 
where stu.stuid=score.stuid and  course.cid=score.cid

必须要出现后边的等值条件，按照=的两边，取交集
不会出现新表，只是一个显示结果
先连接成一个笛卡尔积（生成记录总数=表1的总数*表2的总数），在根据条件筛选
会产生临时表，占内存

(2) 内连接 inner join.....on.......

select * from 表1 
inner join 表2   on 表1.字段=表2.字段
where 条件
order by .....
limit ....

select * from stu
inner join  score  on where stu.stuid=score.stuid
inner join  course on course.cid=score.cid
where stu.name='王昭君'
#先两个表连接，在和第三个表连接
#*要写成列名时，列名要加前缀表明是哪个表的，有重复的
#起别名，只能用别名

先根据条件筛选，在连接两个表
不会产生临时表，不占内存，性能高一些

(3) 左连接

select * from 表1 
left join 表2   on 表1.字段=表2.字段

select * from stu
left join  score  on where stu.stuid=score.stuid
#join前边生成的结果作为左表
left join  course on course.cid=score.cid
#查询所有学生的成绩，包括没有成绩的学生，需要显示课程名

join前面生成的结果作为左边，join后面的是右表，把左表的数据全部显示
取左表所有结果，右表符合的连接，没有符合的就null
和内连接的区别就是，没找到符合的不会显示null,该条不会存在表中，所以只要有右表不符合的情况，左表结果不会全部显示

(4) 右连接

select * from 表1 
right join 表2   on 表1.字段=表2.字段

select * from score 
right join  course on where course.cid=score.cid 
left join  stu on  stu.stuid=score.stuid
#查询所有课程的成绩，包括没有成绩的课程，需要显示学生信息

join前面生成的结果作为左边，join后面的是右表，把右表的数据全部显示
取右表所有结果，左表符合的连接，没有符合的就null

使用左右连接（题目中包含所有，没有字眼需要用到左右连接）

只要明白那个是左表、右表；
那个需要所有结果；
那个需要null值；

9、自关联

select * from areas as sheng,areas  as shi
where sheng.aid=shi.pid

select * from areas as sheng,areas  as shi，areas  as qu
where sheng.aid=shi.pid and shi.aid=qu.pid

数据之间有上下级关系
在一个表中存储所有数据
从一个表中查询多次，必须起别名

10、子查询

() 里表示的是子查询结果
子查询返回的结果作为主查询的条件：标量子查询、行级子查询、列级子查询
子查询返回的结果作为数据源：表子查询

(1) 标量子查询，自查询结果是一行一列

#查询大于平均年龄的学生
select * from stu age>(select avg(age) from stu)
select * from stu age=(select min(age) from stu)
#查询王昭君的成绩，并显示成绩
select * from score where stuid=(select stuid from stu where name='王昭君')

(2) 列级子查询，子查询的结果是一列多行，只能一列，用in

#查询18岁的学生成绩，显示成绩
select * from score where stuid in (select stuid from stu where age=18)

(3) 行列级子查询，子查询的结果是一行多列，只能一行

select * from stu where sex='男' and age=26 
#可以写成
select * from stu where (sex,age)=('男',26)
#因此 ('男',26)里面两个值可以写成语句
select * from stu where (sex,age)=
(select sex,age from stu where sex='男' order by age limit 1)
#不大用，了解一下

(4) 表子查询，子查询返回的结果是一个表，多行多列

必须给子查询部分起别名

select * from (select * from stu) as s

select * from 表1 
inner join (可以是查询出来的结果当作数据源使用) as 别名   
on 表1.字段=表2.字段
where 条件

#查询数据库和系统测试的课程成绩
select * from score
inner join (select * from course where cname in ('数据库','系统测试')) as c
on score.cid=c.cid
#比起
select * from score
inner join course on score.cid=c.cid
where course.cname in ('数据库','系统测试')
#性能高一点

(5) 子查询中特定的关键词使用

in 范围
any| some 任意一个，格式：主查询 where 列=any(列子查询)
=any 等于=some等于in：等于子查询返回的结果
>any 等于>some ：大于子查询返回的结果中任意一个值，就是大于最小值
<any 等于<some ：小于子查询返回的结果中任意一个值，就是小于最大值
!=any|some：没有意义，就是查了所有
all 所有，格式：主查询 where 列<>all(列子查询)
>all：大于子查询返回的结果中所有值，就是大于最大值
<all：小于子查询返回的结果中所有值，就是小于最小值
!=all <>all：不等于子查询返回的结果中所有值，就是非
=all：没有意义，一个值不可能同时等于多个值

select * from score where stuid>any(select stuid from stu where age=18)
select * from score where stuid>all(select stuid from stu where age=18)

练习：
1.查询所有价格大于平均价格(保留2位小数)的商品，并按价格降序排序

select * from goods where price>
(select  round(avg(price),2) from goods ) order by price desc

查询价格大于或等于“超极本”价格的商品，并按价格降序排序
子查询返回的是一列多行

select * from goods where price >=any
(select  price from goods  where cate='超极本' ) order by price desc

1. MySQL 基础
1 MySQL 安装和基础使用 1.1 MySQL 简介 1.1.1 MySQL 版本 MySQL MariaDB...
MySql数据库基础及IDE
一、基础操作--sql语句启动MySQL：service mysql start停止MySQL：service ...
2018-04-09 数据仓库技能要求
一、基础技能1 关系数据库基础1.1. 关系数据库-mysql1.1.1 mysql 应用1.1.2 mysql ...
mysql基础
mysql添加外键的4种方式 MySQL基础考点1 MySQL基础考点2 MySQL数据库六大设计规范总结1 My...
mysql索引，锁机制，主从同步，日志分析
mysql架构 mysql学习 MySQL系列（一）---基础知识大总结 --- 棒棒的！MySQL系列（三）--...
MySQL基础语句及常见关键字使用总结
一、MySQL基础语句 1. 进入MySQL >> mysql -u root -p (有密码时)>> mysql...
MySQL基础语句与其在Python中的使用
一、MySQL基础语句 $ mysql -u root -p (有密码时)$ mysql -u root ...
高性能的索引策略
MySQL查询基础-查询执行过程 MySQL聚簇索引 MySQL覆盖索引 MySQL索引扫描排序 MySQL冗余和...
MySQL学习笔记---基础概念和一些基础SQL语句（一）
MySQL学习笔记---基础概念和一些基础SQL语句（一）作者：zhangjunqiang MySQL学习笔记，...
mysql学习
选择mysql理由 mysql架构 mysql查询执行过程概述相关概念相关基础概念数据类型 mysql ...