mysql

MySql 基础知识点(测试)

规范书写格式

select 
	gender,university,
	count(device_id) user_num,
	avg(active_days_within_30) avg_active_day,
	avg(question_cnt) avg_question_cnt
from 
	user_profile 
GROUP BY 
	gender,university;

插入一万条数据

DROP PROCEDURE IF EXISTS proc_initData;-- 如果存在此存储过程则删掉
 
DELIMITER $ -- 将结束符改成$
 
CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=10000 DO
        INSERT INTO stinfo (id,name,age,height,isdelete,sex) VALUES(i,"张三",22,1.85,0,'男');
        SET i = i+1;
    END WHILE;
END $
#创建函数成功后调用该函数
CALL proc_initData();

---

linux导入数据库

1.首先建空数据库

• create database XXX;

2.导入数据库

（1）选择数据库

use XXX;

(2)设置数据库编码

set names utf8;

(3)导入数据

source /home/abc/abc.sql(数据库的源文件地址)

相关函数

1.保留小数位

# 舍入到指定的长度或精度
round(字段/聚合函数,X) #X：代表小数点 X 位之后的四舍五入。 X=1 数据=1.85 进位后=2
# 例
select count(gender),round(avg(gpa),1) from user_profile where gender='male';

---

一:数据类型与约束

1.常用数据类型

整数：int，有符号范围（-2147483648 ~ 2147483647），无符号（unsigned）范围（0 ~ 4294967295）

小数：decimal，例如：decimal（5，2）表示共存5位数，小数占2位，整数占3位

字符串：varchar，范围（0 ~ 65533），例如：varchar（3）表示最多存3个字符，一个中文或一个字母都占一个字符

日期时间：datetime，范围（1000-01-01 00:00:00 ~ 9999-12-31 23:59:59），例如：‘2020-01-01 12:29:59’

2.约束

• 主键（primary key）：能唯一标识表中的每一条记录的属性组

• 非空（not null）：此字段不允许填写空值

• 唯一（unique）：此字段的值不允许重复

  

• 默认值（default）：当不填写此值会使用默认值，如果填写时以填写为准

• 外键（foreign key）：一个表的一个字段引用另一个表的主键

二:数据库的操作(增删改查知识)

1.创建数据库(create)

• 语法格式

#创建数据库
create database 数据库名 [charset] [字符编码] [collate] [校验规则];
#创建结果查看
show create database 数据库名;

• 例

#创建一个叫 python 的数据库:
create database python charset=utf8 collate=utf8_general_ci;
#查看创建结果
show create database python;

2.使用数据库(use)

• 语法格式

#使用数据库
use 数据库名;
#查看当前使用的数据库
select database();

• 例

#使用python的数据库
use python;
#查看当前使用的数据库
select database();

3.修改数据库(alter)

• 语法格式

alter database [数据库名] 
[default] character set <字符集合>
[default] collate <校对规则>;

• 例

#创建 test 数据库，字符集为 gb2312
create database test charset=gb2312
#修改 test 的指定字符集修改为 utfmb4，默认校对规则修改为utf8mb4_general
alter database test
default character set utf8mb4
default collate utf8mb4_general_ci;

4.删除数据库(drop)

• 语法格式

drop database 数据库名;

• 例

#删除 python 数据库
drop database python;

5.查看所有数据库(show)

show databases;

6.备份数据库/恢复数据库

• 备份

mysqldump -u root -p 数据库名 > python.sql;

• 恢复

mysql -u root -p 新数据库名 < python.sql

7.创建表

• 语法格式

#创建表
create table 表名(
	字段名 类型 约束,
	字段名 类型 约束,
	...
)

• 例

create table if not exists students(
	name varchar(10),
	age int unsigned
)

create table students(
    #主键:primary key auto_increment:自动增长
	id int unsigned primary key auto_increment,
	name varchar(10),
	age int unsigned,
	height decimal(5,2)#5位数 小数占两位 整数三位
)

8.查看表结构(desc)

• 语法格式

desc 表名;
#例
desc students;

9.删除表

1.drop table 表名;
2.drop table if exists 表名;
#例
drop table students;

三:数据的操作(数据增删改查)

1.增加数据(insert)

• 所有字段设置值，值的顺序与表中字段的顺序对应

主键列是自动增长，插入时需要占位，通常使用 0 或者 default 或者 null 来占位，后以实际数据为准。

insert into 表名 values(...)
#例
insert into students values(0,'亚瑟',22,177.56)

• 部分字段设置值，值的顺序与给出的字段顺序对应

insert into 表名(字段1,2,...) values(值1,2,...)
#例
insert into students(name) values('tom')

• 插入多行数据

#1
insert into 表名 values(...),(...)...
#2
insert into 表名(列1,...) values(...),values(...)

2.修改数据(update)

• 语法格式

#where 不能省略否则会修改全部数据
update 表名 set 列1=值1,列2=值2... where 条件
#例
update stinfo set name='jack',age=20,height=2.12 where id=1;

3.删除数据(delete,truncate,drop)

• 语法格式

#where 不能省略否则会删除全部数据
delete from 表名 where 条件; #删除数据但是不会重置主键字段的计数
#例
delete from stinfo where id=1;

#删除表所有数据，保留表结构
truncate table 表名;#删除所有数据，并重置主键字段的计数
#删除表数据和结构
drop from 表名;

• 逻辑删除

#增加一个字段用于判断是否要删除，isdelete 若0为默认 则1是删除。表面删除，筛选isdelete是0的可以展示。
update stinfo set isdelete=1 where id=3; 
select * from stinfo where isdelete=0;

4.查询数据(select)

• 语法格式

#查询所有数据
select * from 表名;
#查询指定字段
select 字段 from 表名
select name from stinfo
#查询表中筛选内容
select * from 表名 where 条件
select * from stinfo where isdelect=0;

5.字段起别名(as)

• 语法格式

select 字段1 as 'xx',字段2 as 'xx' from stinfo; 
#例
select name as '姓名' from stinfo;
#可以省略不写 as ''
select name 姓名 from stinfo

6.查询中去重(distinct)

#不显示重复的内容
select distinct 字段1,... from 表名;

7.复杂查询

• 语法格式

select 字段1,... from 表名 where 条件;

7.1:条件运算

1. 大于 > , 小于 <
2. 等于 =
3. 不等于 != , <>

7.2:逻辑运算

1. and 与

2. not 非

   #not 单边条件
   select * from stinfo where  not age > 22 and age <= 22;
   

3. or 或

7.3:模糊查询(like)

# % 表示任意多个任意字符
# _ 表示任意一个字符
select * from 表名 where 字段 like '%'  
#例
select * from stinfo where name like 'j%';# % j开头后面多个任意字符
select * from stinfo where name like '%j';# 表示前面任意字符，后面含有j的内容
select * from stinfo where name like '%j_';# 前面任意多个字符后面内容带一个字符
select device_id,age,university from user_profile where university like '北京%';

7.4:范围查询(in，between xx and xx)

select * from 表名 where 字段 in('xx','xx','xx');
# 连续范围查询
select * from 表名 where 字段 between xx and xx;
# 例：连续范围查询
select * from stinfo where age between 16 and 22;

7.5:判断空

# 在 mysql 中 null 内容为空，其余非空。
# 判断空
select * from 表名 where 字段 is null;
# 非空
select * from 表名 where 字段 is not null;

7.6:排序(order by)

# asc 从小到大排序，升序
# desc 从大到小排序，降序
select * from 表名 order by 列1 asc|desc,列2 asc|desc,..;
# 例
select * from stinfo order by age asc;

7.7:聚合函数

• 常用的聚合函数

1. count():查询总记录数

   select count(*) from stinfo;
   

2. max():查询最大值

   select max(age) from stinfo;
   

3. min():查询最小值

   select min(age) from stinfo;
   

4. sum():求和

   select sum(age) from stinfo;
   

5. avg():求平均值

   select avg(age) from stinfo;
   

7.8:分组（group by）

• 语法格式

select 字段1,字段2,聚合... from 表名 group by 字段1,字段2...;
#例
select sex,count(*) from stinfo group by sex;

7.9:分组后筛选(having)

select 字段1,字段2,聚合... from 表名 group by 字段1,字段2... having 字段1,...聚合...;
#例
select sex,count(*) from stinfo group by sex having sex='男';

7.10:分页(limit)

• 语法格式

#默认 start 默认零是第一条数据
select * from 表名 limit start,count;# start：起始行 count：结束行
#例
select * from stinfo limit 0,3;

• 公式

#整页:每页显示m条数据，显示第n页的数据
select * from 表名 limit (n-1)*m,m;
#每页显示5条数据，展示第2页的数据内容
select * from stinfo limit(2-1)*5,5;#5,5

7.11:连接查询

1.基本连接方式（两张表）

• 内连接：查询的结果为两个表匹配到的数据（有关联）

select * from 表1 inner join 表2 on 表1.列=表2.列;
#例
select * from stinfo inner join addres on stinfo.info_id=addres.info_id;

• 左连接：查询结果为两个表匹配到的数据加左表特有的数据，对于右表中不存在的数据使用null（有关联加上左表所有数据）

select * from 表1 left join 表2 on 表1.列=表2.列;
#例
select * from stinfo left join addres on stinfo.info_id=addres.info_id;

• 右连接：查询结果为两个表匹配到的数据加右表特有的数据，对于左表中不存在的数据使用null（有关联加上右表所有数据）

select * from 表1 right join 表2 on 表1.列=表2.列;
#例
select * from stinfo right join addres on stinfo.info_id=addres.info_id;

2.自关联（一张表）

• 数据表有两个字段之间有某种联系

#内连接
select * from addres a1 inner join addres a2 on a1.id=a2.pid;
#左连接
select * from addres a1 left join addres a2 on a1.id=a2.pid;
#右连接
select * from addres a1 right join addres a2 on a1.id=a2.pid;

3.子查询

• 子查询：在一个 select 语句中，嵌入了另一个 select 语句，那么嵌入的 select 语句，那么嵌入的 select 语句称之为子查询语句
• 主查询：外层的 select 语句称之为主查询语句

#查询年龄高于平均年龄的信息
select * from stinfo where age > (select avg(age) from stinfo);

elect * from stinfo right join addres on stinfo.info_id=addres.info_id;

##### 2.自关联（一张表）

- 数据表有两个字段之间有某种联系

```sql
#内连接
select * from addres a1 inner join addres a2 on a1.id=a2.pid;
#左连接
select * from addres a1 left join addres a2 on a1.id=a2.pid;
#右连接
select * from addres a1 right join addres a2 on a1.id=a2.pid;

3.子查询

• 子查询：在一个 select 语句中，嵌入了另一个 select 语句，那么嵌入的 select 语句，那么嵌入的 select 语句称之为子查询语句
• 主查询：外层的 select 语句称之为主查询语句

#查询年龄高于平均年龄的信息
select * from stinfo where age > (select avg(age) from stinfo);