MySQL基础总结

MySQL：结构化查询语言

数据库历史发展：
刚诞生时属于微小数据库
瑞典人（最容易出现乱码） charset（字符集）
sun（开源）
5.5（闭源）
MariaDB(开源)

数据库database(DB)存储数据的基本单位：
表（表格、库表、数据库表）table
列（字段）col
行（记录）row

DBMS:数据库管理系统

默认端口号
mysql默认端口号：3306
SQL Server默认端口号：1433
Oracle默认端口号：1521

数据类型
整型：int 4个字节（32位）
小数类型：
decimal(5,2) (3,总长度，2，小数位)
float 4个字节(小数点后6-7位)
字符型：
char --固定长度字符串（定长）数据占用的存储空间是按建表设计长度来计算的
varchar --可变长度字符串（变长）数据占用的存储空间是按数据实际长度来计算的
char(10)--10是字符个数
日期型
date (YYYY-MM-DD)
time(HH:ii:ss)
datetime(YYYY-MM-DD HH:ii:ss)

启动/关闭mysql服务
net start/stop [服务名]

net start mysql57

命令行进入数据库
mysql -h[IP地址] -P[端口号] -u[用户名] -p[密码]
-h IP 连接别人的机器（用户需要权限）

mysql -u root -p

查看数据库
(系统数据库：mysql、sys、performance_schema、information_schema)
show databases;
查看数据库下的库表
show tables;
创建数据库（带字符集）
create database [数据库名称] charset=utf8;

create database Student charset=utf8;

删除数据库
drop database [数据库名];

drop database Student;

使用数据库
use [数据库名];

use Student;

创建表
create table 库表名(
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
......
);

create table stu(
no int,
name varchar(10),
sex char,
height decimal(3,2),
bir date
);

查看表结构
desc[describe] [表名];

desc stu;

销毁表
drop table [表名];

drop table stu;

向表插入数据
insert into 表名(字段列表) values(值列表);

insert into stu(no,name,sex,height,bir) values(1,'路飞','男',1.76,'2000-05-05');

同时插入多行数据
insert into 表名(字段列表) values(值列表1),(值列表2)...;

insert into stu(no,name,sex,height,bir) values
(2,'索隆','男',1.81,'1998-11-11'),
(3,'娜美','女',1.78,'2000-07-03');

查看表数据
select [字段列表] from [表名];

select * from stu;

删除数据
delete from [表名];
truncate table [表名];

delete from stu;
truncate table stu;

修改表结构
alter table [表名] add [字段名] [数据类型]; --增加字段
alter table [表名] modify [字段] [数据类型]; --修改字段
alter table [表名] drop [字段] [数据类型]; --删除字段
alter table [表名] change [原字段名] [新字段名] [数据类型];
约束
alter table [表名] add constraint [约束名] [约束类型] (字段);

唯一性约束（unique）
unique
primary key
数据唯一性特点：
1.允许字段为空
2.一张表允许多个唯一性约束

主键
/*
主键的特性：
1.不允许重复值
2.不能为空
3.一张表只有由一个主键
4.允许有联合主键（多字段共同主键）
5.索引
*/

alter table stu add constraint pk_stu_no primary key(no);

外键
作用：
1.从表不允许写入主表不存在的数据
2.主表不允许删除从表还存在的数据

alter table stu add constraint fk_stu_class_no foreign key(no) references class(no);

拷贝表结构
create table [表2] select * from [表1] where [不成立条件];

create table temp select * from stu where 1=2;

造数据
insert into [表1]（字段列表） select [字段列表] from [表2];

insert into temp(no) select no from stu;

去重

1.提取唯一数据放入备份表
create table 备份表 select 唯一数据 from 源表;

create table stu_bak select distinct * from stu;

2.删除重复数据（所有数据）distinct
delete from 源表;

delete from stu;

3.唯一数据写回到源表
insert into 源表 select * from 备份表;

insert into stu select * from stu_bak;

数据排序
select 字段列表 from 表名 [where...] order by 字段名;

order by 默认按字段数据的升序asc排列
降序desc

1.order by 放在select最后
2.order by 可用别名
3.order by 复合排序（联合排序）

--先按年龄，再按学号
select * from stu order by age,no;

模糊查询
select * from stu where 字段 like 值
% 任意位
_ 一位
指定范围查询（in）

select * from stu where no in(1);
select * from stu where no not in (1);

from 使用子查询：对数据来源做二次加工

嵌套查询、子查询
select (select ...)
from (select ...)
where (select ...)

.查询分数最高的信息:学号、科目、分数
/from 中包含子查询
--from使用子查询:对数据来源做二次加工/
select *
from score a,
(select max(score) x from score) b
where a.score=b.x;

SELECT *
FROM
(SELECT * FROM stu WHERE LEFT(NAME,1)='张') a,
(SELECT * FROM ck WHERE SUBSTR(ontime,12,5)>'08:20') b
WHERE a.no=b.no;

--where使用子查询:将一个select的数据作为条件引用
select * from score
where score=(select max(score) from score);

--select 使用子查询:select中需要对数据做二次加工
SELECT (SELECT COUNT() FROM score WHERE score<60)
/
(SELECT COUNT() FROM score);

select count(*)/(select count(*) from ck)
from   ck where SUBSTR(ontime,12,5)>'08:20';

多表关联查询
外连接 left/right/full [outer] join
外键（多表之间数据一致性的约束）
条件：先确定数据的主、从关系
由从表的非主键字段向主表的主键字段建立外键
外键所关联的两个字段名可以不相同
但数据类型、长度必须相同
作用：1.从表不能写入主表不存在的数据
2.主表不能删除、修改从表中还存在的数据
constraint foreign key
create table 主表(主键字段数据类型...);
create table 从表(
非主键字段数据类型,...,
constraint fk_从表_主表_外键字段名
foreign key(外键字段) references 主表（外键字段）;
);

alter table 从表名 add constraint fk_从表_主表_外键
foreign key(外键字段) references 主键(外键字段);

嵌套查询
select 字段 from 表名
where 条件
group by 分组子句
having 聚合函数条件语句
order by 排序子句;

-- 合并 union
select no,sex from stu
union
select no,grade from gra;

-- case 语句
用法1：
case 表达式
when 值1 then 返回值
when 值2 then 返回值
when 值3 then 返回值
....
end

SELECT NO,NAME,
(CASE sex
WHEN '男' THEN '1'
WHEN '女' THEN '2'
else '不明确'
END) '性别'
FROM stu;

SELECT NO,NAME,
(CASE LEFT(bir,4)
WHEN 1998 THEN '小鲜肉'
WHEN 1997 THEN '鲜肉'
WHEN 1996 THEN '肉'
WHEN 1995 THEN '腊肉'
WHEN 1994 THEN '老腊肉'
WHEN 1993 THEN '风吹腊肉'
ELSE '木乃伊'
END ) '个性化标识'
FROM stu;

用法2：
	case 
	when 表达式1 then ''
	else
	end

SELECT NO,NAME,
(CASE
WHEN LEFT(bir,4)>=2000 THEN '小鲜肉'
WHEN LEFT(bir,4)=1997 THEN '鲜肉'
WHEN LEFT(bir,4)=1996 THEN '肉'
WHEN LEFT(bir,4)=1995 THEN '腊肉'
WHEN LEFT(bir,4)=1994 THEN '老腊肉'
WHEN LEFT(bir,4)=1993 THEN '风吹腊肉'
ELSE '木乃伊'
END ) '个性化标识'
FROM stu;

-- 按分数段统计各人数(竖表)：
+--------+----------+
| 分数段 | COUNT(*) |
+--------+----------+
| 60～70 | 2 |
| 70～80 | 1 |
| 80～90 | 2 |
| 90以上 | 4 |
| 不及格 | 2 |
+--------+----------+

SELECT
(CASE
WHEN grade BETWEEN 60 AND 70 THEN '60~70'
WHEN grade BETWEEN 70 AND 80 THEN '70~80'
WHEN grade BETWEEN 80 AND 90 THEN '80~90'
WHEN grade>90 THEN '90以上'
ELSE '不及格'
END)'分数段',COUNT(*)
FROM gra
GROUP BY 分数段;

SELECT NO,NAME,(CASE sex
WHEN '男' THEN '1'
WHEN '女' THEN '2'
END) '性别'

FROM stu;

	.按分数段统计各人数(横表)：
	+--------+--------+--------+--------+--------+
	| 不及格 | 60～70 | 70～80 | 80～90 | 90以上 |
	+--------+--------+--------+--------+--------+
	|      2 |      2 |      1 |      2 |      4 |
	+--------+--------+--------+--------+--------+

SELECT (SELECT COUNT() FROM gra WHERE grade<60) '不及格' ,
(SELECT COUNT() FROM gra WHERE grade BETWEEN 60 AND 70) '60~70',
(SELECT COUNT() FROM gra WHERE grade BETWEEN 70 AND 80) '70~80',
(SELECT COUNT() FROM gra WHERE grade BETWEEN 80 AND 90) '80~90',
(SELECT COUNT(*) FROM gra WHERE grade>90) '90以上';

SELECT
SUM(CASE WHEN grade<60 THEN 1 END) '不及格',
SUM(CASE WHEN grade BETWEEN 60 AND 70 THEN 1 END) '60~70',
SUM(CASE WHEN grade BETWEEN 70 AND 80 THEN 1 END) '70~80',
SUM(CASE WHEN grade BETWEEN 80 AND 90 THEN 1 END) '80~90',
SUM(CASE WHEN grade>90 THEN 1 END)'不及格'
FROM gra;

排名
SELECT (@row:=@row+1) 名次,NAME,NO,bir
FROM stu a,(SELECT @row:=0) b ORDER BY bir DESC;

select * from stu order by bir desc;

select row_number()over (order by bir) from stu;

SELECT * FROM stu ORDER BY bir DESC;

SELECT row_number()over(ORDER BY bir) FROM stu;

-- 取出2019年全年每天的日期、星期
-- 计算1..100的累加求和

information_schema.columns

索引、视图
1、大数据量情况下提高查询/检索速度
2、索引是建立在表的字段上的
3、索引的使用：建立索引的字段需要出现在where子句中
4、索引的原理：
对字段数据进行排序存储在索引中
用空间换时间
5、索引的副作用：
1.消耗额外存储空间
2.建立索引需要消耗时间
3.插入、更新、删除
create index [索引类型] 索引名 on 表名 (字段名列表)；
create index idx_stu_bir on stu(bir);

视图：对表的数据建立查询窗口/通道
create view v_x select no,name from stu where ...;

select no,name from v_x;

使用：方便数据的操作
限制权限、安全性

常用函数

--floor(x) 向下取整
--ceil(x) 向上取整(ceiling)
--round(x,y) 四舍五入,y小数点后的位数
select floor(1.23),ceil(1.23),round(1.23);

--left(x，y) 提取自取，从左起取y位
--right(x，y) 右取
select left('abcd',1); --左取1位
select left(name,1) from stu;

--now() 当前系统年份
--length() 字节数，一个汉字两个字节
--char_length 字符数，一个汉字一个字符

--substr(字段名,x,y) 第x位开始,取y位
select bir,substr(bir,6,2) from stu;

--concat(x,y,z) 将xyz拼成字符串
select concat('#'name,tel) from stu;

date_add('2000-01-01',interval 30 year);

日期函数
curdate()
current_date() 返回当前日期

curtime() 返回当前时间
current_time()

date_add(now(),interval 10 year) //往后推多少个单位
year年
month 月
day 日
nour 取时
minute 取分
second 取秒

星期、天数
dayofweek() 天数
dayname ( ) 星期

/*日期函数:*/
	/*取当前系统日期和时间*/
	now()
	/*及时两个日期之间相差的天数
	datediff(日期1,日期2)*/
		select (now(),'2020-01-01');
		select no,name,bir,
			datediff(now(),bir)/365,
			truncate(datediff(now(),bir)/365,1) a,
			round(datediff(now(),bir)/365,1) b,
			floor(datediff(now(),bir)/365) c,
			ceil(datediff(now(),bir)/365) d
		from stu;	
	/*将时间向前或后退多少单位
	  date_add(日期时间,interval n 时间单位)
	*/
		select date_add(now(),interval 50 minute);
	/*计算两个日期之间相差多少单位)
	timestampdiff(时间单位,日期时间1,日期时间2)*/
	select timestampdiff(year,'2001-01-01',now());

--聚合函数:把多个数据汇集计算出单一的1个数据值
--max() 计算列的最大值
--min() 计算列的最小值
--count() 统计行数量
--sum() 获取单个列的合计值
--avg() 计算某个列的平均值

posted @ 2019-10-28 19:22 心脏鼓点阅读(385) 评论(0) 编辑收藏举报

刷新页面返回顶部

强风吹拂

MySQL基础总结

常用函数

公告