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:条件运算
- 大于 > , 小于 <
- 等于 =
- 不等于 != , <>
7.2:逻辑运算
-
and 与
-
not 非
#not 单边条件 select * from stinfo where not age > 22 and age <= 22;
-
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:聚合函数
- 常用的聚合函数
-
count():查询总记录数
select count(*) from stinfo;
-
max():查询最大值
select max(age) from stinfo;
-
min():查询最小值
select min(age) from stinfo;
-
sum():求和
select sum(age) from stinfo;
-
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;
select * from 表1 left join 表2 on 表1.列=表2.列;
#例
select * from stinfo left join addres on stinfo.info_id=addres.info_id;
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);