Mysql Mysql语句入门燕十八老师练习笔记

最新推荐文章于 2021-03-14 09:56:09 发布

忘喧

最新推荐文章于 2021-03-14 09:56:09 发布

阅读量341

点赞数

分类专栏：数据库文章标签： mysql 数据库 sql nosql

本文链接：https://blog.csdn.net/XiaoYige_97/article/details/107973771

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Mysql 笔记

文章目录

Mysql 笔记

数据库的入门语句

登录mysql
```
mysql -uroot -p;
```
查看数据库
```
 show databases;
```
选数据库
```
show tables;
```
显示数据表
```
show tables;
```
创建数据库
```
crete database test charset utf8;
```
删除数据库
```
drop database test;
```

创建表

 create table student(
     id int,
     name varchar(10)
    );

查看数据表
```
 show tables;
```
查看表结构
```
desc tablesName;
```
删除表
```
drop tables  student;
```
修改数据表名
```
rename table student to students;
```

插入数据

insert into students values
   (1,'xiaoYi');

清空表数据相当于把旧表删除，在创建相同结构的表
```
truncata  students;  
```
清空表数据从行的结构删除数据
```
delete   students；
```
drop 和delete 的区别
```
//方式的不同
```
选择编码
```
set names gbk;
```

建表：

create table class(
	id int primary key auto_increment,
 	sname varchar(10) not null default'',
	 gender char(5) not null default'',
	 company varchar(20) not null default'',
	 salary smallint not null default 0
 );

向表里添加整行数据

insert into class
    (id,sname,gender,company,salary)
    values
    (1,'曹操','男','字节跳动',888.45);

向表里添加部分列数据

insert into class
    (sname,gender,salary)
    values
    ('关羽','男','888.23');

插入所有列

insert into class
   values
   (3, '刘备','男','百度','2547.12');

整加多行

insert into class
    (sname,company,salary)
    values
    ('诸葛亮','麦当劳','2456.12'),
    ('李逵','黑水保安','998.35');

增加列

alter table class add age tinyint not null default 0;
alter table class add  fanbu int unsigned null default 0;  #unsigned 表示无符号

修改数据如果不加条件 where 的话，所有行和列都修改

update class
   set gender = '男'
   where id =  4;
    
 update class
    set salary = 9922
    where gender = '男' and salary =999;

删除数据
```
delete from class
    where id = 2;
```

查找数据

select sname,company
    from class
    where gender = '男';
    
select sname,company
    from class

mysql 的基础知识

mysql 的基础类型

数值型 1字节 = 8位
- unsigned 表示无符号位
- 整型型
  - Tinyint
    
    占据空间：1 字节储存范围[-127 ,+127] unsigned [0,255]
  - SmallInt
    
    占据空间：2 字节储存范围[-32768 ,+32767]
  - MediumInt
    
    占据空间：3 字节储存范围[-8388608 ,+8388607]
  - Int :
  占据空间：4 字节储存范围[-2147483648 ,+2147483647]
  - BigInt:
  占据空间：8 字节储存范围[]
- 小数型
  - Float(M,D)
    
    eg: float(6,2) 4444.44 3333.33
  - decimal(M,D) 定点型整数部分和小数部分，分开存储
- 字符型
  - char (M = 0-255) 定长字符串存储空间一定，若不够位，则用空格补位，但是如果数据尾部本身有空格，是输出不了的，速度与varchar 相比更快。M
    
    M 表示字符，不是字节。
    
    不管是UTF8 还是其他字符
  - varchar(M = 0-65535) 变长字符串不用空格补齐，在字符前有1-2个字节标志该列内容的长度
    
    M 表示字符，不是字节。
    
    不管是UTF8 还是其他字符
  - Text 文本类型
    
    可以存比较大单位文本段
    
    搜索速度较慢
    
    声明text 列时，不必给默认值
  - blonb类型
    
    是二进制类型，用来存储图像等信息
    
    Blob 在于防止字符集的问题，导致信息丢失
- 日期时间类型
  - Date 日期
    
    YYYY -MM-DD
  - Time 时间
    
    HH:MM:SS
  - Datetime
    
    YYYY-MM-DD HH:MM:SS
  - Year 年类型
    
    YYYY

修改alter

新建一个 member 表,用来练习

列名称	列类型	是否主键
id	Int unsigned	PRI
Username	Varchar(20)
gender	cahar(1)
weight	float(5)
Birth	Data
Salary	Decimal(8,2)
lastLogin	d
intro #简介	Varchar()

create table menber(
	id int unsigned auto_increment primary key,
    username char(20) not null default '',
    gender char(1) not null default '',
    weight tinyint unsigned not null default 0,
    birth date not null default '0000-00-00',
    salary decimal(8,2) not null default 0.00,
    lastLogin int unsigned not null default 0
)

修改 menber 表 alter

增加列 intro 列
在gender 后增加 height 列
在最前面增加一列 work
删除 worK 列
修改列 gender, 把类型 char(1) 改为 char (5)
修改列名和列类型 id ==> uid int(10) unsigned ==> int

   #增加列 intro 列
   alter table menber add intro char(150) not null default '';
   #在gender 后增加 height 列
   alter table menber add height decimal(3,2) not null default 0 after gender;
   #在最前面 增加一列  work
   alter table menber add work char(25) not null default '' first;
   #删除 worK 列
   alter table menber drop pid;
   #修改列 gender, 把类型 char(1)  改为 char (5)
   alter table menber modify gender char(5) not null default '';
   #修改列名和列型    id ==> uid   int(10) unsigned ==> int 
   alter table menber change id uid int;

查询

查询
- where 添加查询
- group by 分组
- having 筛选
- order by 排序
- limit 限制结果条数
比较运算符

运算符说明运算符说明
< 小于 != 不等于
<= 小于或等于 >= 大于或等于
= 等于 > 大于
in 在集合内 between…and … 在某范围内
逻辑运算符

运算符说明运算符说明
NOT 或！逻辑非 OR 或 || 逻辑或
AND 或 && 逻辑与 like 模糊查询

运算符	说明	运算符	说明
<	小于	!=	不等于
<=	小于或等于	>=	大于或等于
=	等于	>	大于
in	在集合内	between…and …	在某范围内

运算符	说明	运算符	说明
NOT 或！	逻辑非	OR 或 \|\|	逻辑或
AND 或 &&	逻辑与	like	模糊查询

students表结构

id	name	age	grade	english
int	varchar(10)	int(2)	int(3)	int(3)

#创建表
create table students(
id int unsigned auto_increment primary key,
name varchar(10) not null default "",
age int(2) default 0,
grade int(3) default 0,
english int(3) default 0
);
#插入数据
insert into students
values
(1,"xiaoYi",19,100,90),
(2,"xiaoLin",23,90,95),
(3,"小红",10,60,90),
(4,"小桃子",25,99,98);

实例

# 查找id=1的数据
select * from students where id=1;

#查找id不等1的数据
select * from students where id!=1;

#查找 age大于10的数据
select * from students where age>10;

#查找 age 小于age 小于或等于19
select * from students where age <=19;

#取出 age 为23或age 为19 的数据
select * from students where age in(19,23);

#取出 10 <= age <= 23 的数据
select * from students where 10 <= age <= 23;
select * from students where age between 10 and 23;
select * from students where age>=10 and age<=23;

#取出 age 不等10 且age不等19 的数据
select * from students where age!=10 and age !=19;
select * from students where age not in (10,19);

#取出 age 大于10且小于18 或者大于18且小于30 的数据
select * from students where age>10 and age <18 or age >18 and age<30;

#取出name 以小开头的数据
select * from students where name like "小%";

广义投影

表里本来没有"grade-60" 的，列L
这一列其实是一个运算结果，术语叫“广义投影”
列的运算结果，可以当成一个列看，也可以给这个列起一个别名

#不及格的小朋友
select name,grade,(grade-60) as fail from students;

#超过及格线 30分的小朋友友
select  name,grade,(grade-60) as fail
from students
where (grade-60)>30;

like

模糊查询

#Like用来匹配一部分的，% 任何字符出现任何位置区分大小写。

SELECT * FROM TABLE WHERE col Like '%a';//检索以a结尾的内容

SELECT * FROM TABLE WHERE col Like '%a%';//检索包含a的内容

SELECT * FROM TABLE WHERE col Like 'a%';//检索以a开头的内容

SELECT * FROM TABLE WHERE col Like 'a%b';//检索以a开头以b结尾的内容

SELECT * FROM TABLE WHERE col Like 'ab_';//检索以ab开头，后接一个字符的内容

group

常用函数
- max 最大
- min 最小
- sum 总和
- avg 平均
- count 总行数
count函数
- select count(*) from 表名
  
  查询的就是绝对的行，哪怕某一行所有字段全为null ，也计算在内
- select count(列名) from 表名
  
  查询的是该列不为null 的所有的行数

函数实例

#求grade 最大的值
select max(grade) from students;

#求grade 最小的值
select min(grade) from students;

#求全班的grede的（总分）
select sum(grade) from students;

#求全班的平均分
select avg(grade) from students;

#求全班有多少人
select count(*) from students;  // * 包含null 
select count(id) from students;  // 不包含 id=null的行

#全班及格的人数
select count(*) from students
where grade >-60;

分组查询
- group by
问题
- 计算每一位同学的平均分 select id,name,avg(grade) from students group by id;

group by

# 计算每一位同学的总分 
select id,name,sum(grade+english) from students group by id;

#?? 计算每一位同学的平均分
select id,name,avg(grade,english) from students group by id;
??????/

having

查询的发生过程

having 是针对结果的操作

#查询总成绩大于150的同学
#使用 having 方法
select id,name,sum(grade+english) as zong from students
group by id
having zong>150;
where (grade+english) >150
 
#使用 普通方法
select id,name,sum(grade+english) as zong from students
group by id
where (grade+english) >150;
 
#查询平均分小于95同学

having 练习
表结构

name subject score
varchar(20) varchar(20) tinyint
表数据

name subject score
张三数学 90
张三语文 50
张三地理 40
李四语文 55
李四政治 45
王五政治 30

name	subject	score
varchar(20)	varchar(20)	tinyint

name	subject	score
张三	数学	90
张三	语文	50
张三	地理	40
李四	语文	55
李四	政治	45
王五	政治	30

创建表

create table result(
	name varchar(20),
    subject varchar(20),
    score tinyint
)engine myisam charset utf8;

插入数据

insert into result
values
("张三","数学",90),
("张三","语文",50),
("张三","地理",40),
("李四","语文",55),
("李四","政治",45),
("王五","政治",30);

insert into result 
values
("赵六","数学",100),
("赵六","地理",98),
("赵六","政治",95);

查出挂科2门及2门以上的学员的平均分

思路一：先求平均分，在就挂科数大于等于2的

# 1、查询所以的平均分
select name,avg(score) from result group by name;
#2、计算出每一个人挂科的情况
select name,subjuect,score,score<60 as g from result;
#3、综合以上，得到所有的平均分，和挂科数 
select name,avg(score),sum(score<60) as gks
from result
group by name;
#4、在上的基础上，筛选出挂科数大于等于2 的
select name,avg(score),sum(score<60) as gks
from result
group by name
having gks>=2;

思路二：先求挂科数大于等于2的学员，再求这些学院的平均分

#1、挂科数大于等于2 的学员
select name,count(1) as gks from result
where score<60
group by name
having gks>=2;
#2、求挂科数大于等于2的学员 子查询和嵌套
	
select name,avg(score)
from result
where name in
    (select name from
        (select name,count(1) as gks from result
            where score<60
            group by name
            having gks>=2)
        as tem)
group by name;

order by 与 limit

order by 可以对最终结果集进行排序
order by 要放在where /group by /having 后面

order by 升序降序

 升序 order by id asc 
 降序 order by id  desc

多字段排序

#有优先级，第一次排序后，有相同的，然后再用第二种排序
order by id asc ,
order by name  desc

limit

#前三条数据
limit 3
#第3 到 第5 条数据
limit 2,3

where子查询

where 型子查询

把内层查询结果作为外层查询的比较条件

内层的返回结果只能是单列，可以是多行
查询结果集在结构上可以当作表看

查出 grade 成绩最高的学生

select * from students where
     grade = (select max(grade) from students);

exists 子查询

exists 型自查询

奇怪的null

为什么建表是时，加not null default’’/ default 0（不填默认也是不为 null）
```
不想让表中出现null值
    
```

为什么不想要null

null 是一种类型，比较时，只能用专门的 is null 和 is not null 来比较
碰到运算符，一律放回null
效率不高影响提高索引效果
因此在建表时加not null default''/ default 0

两表全连接

集合的性质

1、无序性
2、唯一性（集合的元素都是唯一的）
3、集合的运算：
	求并集
    求交集
    笛卡尔积

笛卡尔集运算

集合A:2,3,5
集合B:6,8
A * B:{  // * 不是相相乘，是笛卡尔积
    (2,6),(2,8),
    (3,6),(3,8),
    (5,6),(8,8)
}

集合A有 M 个元素集合B有 N 个元素若：C = A*B 则 C 有 M * N 个元素

表和集合的关系

一张表就是一个集合
每一行就是一个元素

集合的元素不能重复
在Mysql 中，就算有两行相同的数据，
他们也不相同，因为他们内部还有一个rowid

建立两张表

create table test1(
	id int,
    sname varchar(20)
)engine myisam charset utf8;


create table test2(
	cat_id int,
    cname varchar(20)
)engine myisam charset utf8;

分别给两个表插入数据
实现两个表全相乘
select 4*4

  求交集
   笛卡尔积


1. 笛卡尔集运算

```javascript
集合A:2,3,5
集合B:6,8
A * B:{  // * 不是相相乘，是笛卡尔积
    (2,6),(2,8),
    (3,6),(3,8),
    (5,6),(8,8)
}

集合A有 M 个元素集合B有 N 个元素若：C = A*B 则 C 有 M * N 个元素

表和集合的关系

一张表就是一个集合
每一行就是一个元素

集合的元素不能重复
在Mysql 中，就算有两行相同的数据，
他们也不相同，因为他们内部还有一个rowid

建立两张表

create table test1(
	id int,
    sname varchar(20)
)engine myisam charset utf8;


create table test2(
	cat_id int,
    cname varchar(20)
)engine myisam charset utf8;