首页  思维导图  详情

mysql

2021-04-05 19:22:21   0  举报





AI智能生成

系统性记录mysql的知识点,更新中

mysql;sql

作者其他创作

大纲/内容

基础知识

守护进程

独立在后台运行的程序

独立于控制终端、不能与用户直接交互

通过ps axj命令查看系统中的守护进程，守护进程通常是以d结尾的名字

mysql-server服务端安装

1、首先你要保证你的服务器上没有安装过，或者已经彻底卸载了mysql其他版本。卸载流程：

① sudo apt-get autoremove --purge mysql-server-*

② sudo apt-get remove mysql-server

③ sudo apt-get autoremove mysql-server

④ sudo apt-get remove mysql-common (非常重要)

⑤ 清理残留数据：

dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P

sudo find /etc -name "*mysql*" |xargs rm -rf

⑥ 最后用 dpkg -l | grep mysql 检查，如无返回即干净卸载

2、 add-apt-repository 'deb http://archive.ubuntu.com/ubuntu trusty main universe'

3、apt-get update（这一步花时间较长）

4、apt-cache search mysql | grep mysql-server

5、从返回的结果查看有哪些版本可以安装，用下边的命令安装相应的版本。
如果没有5.6版本（一般是有的），尝试如下操作：
编辑/etc/apt/sources.list和/etc/apt/sources.list.save, 搜索deb http://archive.ubuntu.com/ubuntu trusty main，如果这一行的结尾没有universe，手动加上，变成deb http://archive.ubuntu.com/ubuntu trusty main universe。然后执行apt update或者apt-get update。

6、apt install mysql-server-5.6 安装5.6版本 (这里没用apt-get，而是apt)或者apt install mysql-server-5.5安装5.5版本 (这里没用apt-get，而是apt)。

等待安装完毕之后MySQL服务端会自动启动

service mysql status

服务启动

sudo service mysql start

sudo service mysql stop

sudo service mysql restart

systemctl start mysql

use mysql rather than mysqld on Debian-based and SLES systems.

安装客户段

sudo apt-get install mysql-client

查看用户名和密码

cat /etc/mysql/debian.cnf

进入数据库新建用户名

mysql -u debian-sys-maint -p

update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';

update mysql.user set plugin='mysql_native_password';

flush privileges;

重新以新建用户和密码进入数据库

mysql -u root -p

设置远程连接

新建一个host%

grant all privileges on *.* to souvc@"%" identified by "abc123" with grant option;

flush privileges;

登录Ubuntu服务器，修改配置文件。

cd /etc/mysql/mysql.conf.d/

vim/etc/mysql/mysql.conf.d/mysqld.cnf

bind-address= 127.0.0.1注释

重启数据库

etc/inint.d/mysqlstart

用新建的用户名密码连接

ifconfig

获取IP地址

端口

3306

在ubuntu各个文件位置

服务位置

/etc/init.d/mysqld

没有！！！

数据库位置

/var/lib/mysql

ubuntu中文件的位置

/usr/bin 客户端程序和mysql_install_db
/var/lib/mysql 数据库和日志文件
/var/run/mysqld 服务器
/etc/mysql 配置文件my.cnf
/usr/share/mysql 字符集，基准程序和错误消息
/etc/init.d/mysql 启动mysql服务器

默认端口

netstat -tnlp 3306

远程连接的配置位置

/etc/mysql/mysql.conf.d/mysqld.cnf

ubuntu命令

查找进程

ps aux|grep mysql|grep 'my.cnf'

查找文件位置

which mysqld

whereis

切换用户

sudo su

包管理

dpkg命令是Debian Linux系统用来安装、创建和管理软件包的实用工具。

yum

centos

apt-get

ubuntu

教程

数据库操作

库名和表名大小写敏感

创建

create database 数据库名 charset=utf-8;

删除

drop database 数据库名；

查看

show databases;

select database()

use test;

表操作

创建表

create table pet (name varchar(20),owner varchar(20),species varchar(20),sex char(1),birth date, death date);

default

primary key

auto_increment

删除表

drop table 表名

改变表

alter table 表名 add|change|drop 列名类型;

rename table 原表名 to 新表名；

查看表

describe pet;

子主题

show tables;

show create table 表名；

SHOW INDEX FROM tbl_name

数据操作

载入数据

load data local infile into方法

LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';

windows上

LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

/N代表NULL

insert into values方法

INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL),( ),( );

删除数据

delete from table where

删除表中的数据

更新数据

update pet set birth = '1989-08-31' where name = 'Bowser';

查询数据

条件筛选

SELECT name, species, birth FROM pet WHERE species = 'dog' OR species = 'cat';

去重

select distinct 列名 from 表名

只能放在最前面，当有多个字段都不相同时才会被过滤

排序

SELECT name, species, birth FROM pet ORDER BY species, birth DESC;

先按照字段1排序，然后再在字段1内部按照字段2排序

分页

select * from 表 limit start,count;

索引从0 开始

计算

日期计算

SELECT name, birth, death, TIMESTAMPDIFF(YEAR,birth,death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age

month(birth) 生日月

year（）

day()

上一天

SELECT '2018-10-32' + INTERVAL 1 DAY;

interval

下一月

MOD(MONTH(CURDATE()), 12) + 1;

month(date_add(curdate(),interval 1 month)

NULL

is null

is not null

不能用null进行加减乘除和比较

会得到null

测试0和空字符是否为null

select 0 is null;

模式识别

where field like pattern/regexp_like(field, pattern)

% _ * ^ $ {repeat times} [单个字符]

正则表达式中没有%,小括号表示模式的选择

计数

ONLY_FULL_GROUP_BY 不开启

SET sql_mode = '';

count不与group by使用也可以有结果，返回所有行数

联表查询

两个不同的表

SELECT pet.name, TIMESTAMPDIFF(YEAR,birth,date) AS age, remark FROM pet INNER JOIN event ON pet.name = event.name WHERE event.type = 'litter';

连接自己

on也可以用于过滤筛选

SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species FROM pet AS p1 INNER JOIN pet AS p2 ON p1.species = p2.species AND p1.sex = 'f' AND p1.death IS NULL AND p2.sex = 'm' AND p2.death IS NULL;

如果是left join

on只对右表数据进行筛选

连表方式

left/right/inner/full join on

mysql没有完全连接模式

可以用left join union right join

多表全连接

用union+left join.. on +left join..on

两表全连接

select * from stu left outer join cls on(stu.cid=cls.cid) union select * from stu right outer join cls on (stu.cid=cls.cid);

两个表各自的元素

[left join] + [where A.column is null] + union + right join + [where B.column is null]

子主题

batch模式查询

优势

方便重复查询，每次可以直接从txt里面执行sql语句

windows

mysql -e "source batch-file"

linux

mysql -h host -u user -p < batch-file

mysql < batch-file > mysql.out

事务

管理复杂查询，必须成批执行的mysql操作

产生依赖关系的动作能够同时操作成功或同时返回初始状态

A给B转账但是没有输对银行卡号，不能A卡里少了B卡里没多

常见的查询例子

一列的最大值

SELECT MAX(article) AS article FROM shop;

最大值所在的一行

排序+limit

SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;

子查询单值比较

select article, dealer,price from shop where price=(select max(price) from shop);

left join 自身 on后面的条件不止可以是等号

SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;

每类的某列最大值

max和group by

select 后面如果有不是group by 的字段且没有加聚合函数的话，会报错

SELECT article, MAX(price) AS price FROM shop GROUP BY article ORDER BY article;

每类的某列最大值所在一行

相关查询+where后单值比较

SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article) ORDER BY article;

不相关查询 from后面join

SELECT s1.article, dealer, s1.price FROM shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article) AS s2 ON s1.article = s2.article AND s1.price = s2.price ORDER BY article;

left join 自身

select s1.article,s1.dealer,s1.price from shop s1 left join shop s2 on s1.article=s2.article and s1.price

窗口函数

WITH s1 AS ( SELECT article, dealer, price, RANK() OVER (PARTITION BY article ORDER BY price DESC ) AS `Rank` FROM shop ) SELECT article, dealer, price FROM s1 WHERE `Rank` = 1 ORDER BY article;

定义变量存储结果

SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;

SELECT * FROM shop WHERE price=@min_price OR price=@max_price;

外键约束

可以在定义列时使用REFERENCES tbl_name（col_name）子句，该子句没有实际作用，并且仅作为备忘录或注释来提示您当前定义的列是预期的

获取当前编号

select @last:=last_insert_Id();

用两个键搜索

union

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1' UNION SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';

union all和union都是对两个结果集进行并集操作，但是union all包括重复行，不进行排序；而union不包括重复行，同时默认规则排序

查看一个用户在一个月内有几天上个某个网站

CREATE TABLE t1 (year YEAR(4), month INT UNSIGNED, day INT UNSIGNED);

INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), (2000,2,23),(2000,2,23);

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) as days from t1 group by year,month;

查询每类中的前n名

EXISTS subqueries

不返回列表的值的.
只是返回一个ture或false的结果

in()后面的子查询是返回结果集的

select a.*from tb t1 where exists( select count(distinct salary) count where depno=t1.depno and sal>t1.sal having count < 5

窗口函数

select * from
(select deptno,ename,sal,row_number() over (partition by deptno
order by sal desc) rn
from emp)
where rn<3;

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

查询某个部门最高工资

select deptno,ename,sal from
(select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) as sal_order
from scott.emp) where sal_order <2

查询每个科目成绩比平均成绩高的同学

select * from (select *, AVG(fenshu) over(partition by kecheng) as flag from studentss)t where fenshu >t.flag;

删除重复记录

select * from (select *, ROW_NUMBER() over(partition by name,kecheng,fenshu) as flag from studentss)t where flag>1;

找出单科成绩高于该科平均成绩的同学名单（该学生所有科都必须满足）

select distinct a.name from studentss a left outer join (select * from (select *, AVG(fenshu) over(partition by kecheng) as flag from studentss)t where t.flag<=fenshu) b on a.name = b.name and a.kecheng=b.kecheng and a.fenshu=b.fenshu and b.fenshu is not null;

每只队伍总成绩

SELECT UserID,Number,TeamID,SUM(Number) OVER(PARTITION BY TeamID) AS "TotalNumber" FROM Test

每个月加上前几个月的汇总

SELECT [month],SUM([count]) OVER(ORDER BY [month]) AS "TotalCount" FROM Test_2

每个月销量占总销量比例

select month(date) as month, 100*sum(val)/sum(sum(val)) over() as percentage from t7 group by month(date)

筛选需要聚合的列

select *, sum(case when a=4 then c else b end) over() from t3 order by a,b

查看顾客上次的购买时间可以这样去查询

select *,lag(orderdate,1,'1900-10-22') over(partition by name order by orderdate) as last_one from shop

对每个月的groupby函数再次汇总

select count(*), sage from age group by sage with rollup

select coalesece(name,"总金额"),name,sum(money) from test group by name with rollup

查询出每门课都大于80 分的学生姓名

select name from table group by name having min(fenshu)>80

删除除了自动编号不同, 其他都相同的学生冗余信息

delete tablename where 自动编号 not in(select min( 自动编号) from tablename group by学号, 姓名, 课程编号, 课程名称, 分数)

函数对序列每一元素做出条件判断，并把所有的True元素返回，返回对象是一个迭代器

查找重复邮件

select email from table group by email having count(email)>1;

从TestDB 数据表中查询出所有月份的发生额都比101 科目相应月份的发生额高的科目。

select a.*
from TestDB a
,(select Occmonth,max(DebitOccur) Debit101ccur from TestDB where AccID='101' group by Occmonth) b
where a.Occmonth=b.Occmonth and a.DebitOccur>b.Debit101ccur

数据类型

数字类型

准确值

integer

int

smallint

tinyint

mediumint

bigint

decimal

salary decimal(5,2)

一共5位保留2位小数

numeric

相当于 decimal

约数

float

四舍五入

double

Bit-Value Type - BIT

属性

ZEROFILL

默认的空格填充将替换为零

UNSIGNED

无符号

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';

使得两个unsigned相减可以是负的

定义位数

FLOAT(M,D) or REAL(M,D)or double(M,D)（约数） M位D位小数
DECIMAL（M,D）固定位数的（精确类型

日期类型

类型

DATE 只有日期 'YYYY-MM-DD'

DATETIME 日期+时间 'YYYY-MM-DD hh:mm:ss'

TIMESTAMP 日期+时间 '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC

TIME 时间

'-838:59:59' to '838:59:59'

YEAR(4)

转换

TIME和DATETIME值转换为数字形式

SELECT CURTIME(), CURTIME()+0, CURTIME(3)+0;

字符串类型

子主题

sql函数

数值计算

数值函数

FLOOR(X) 不大于x的最大整数
TRUNCATE(X,D)截断小数后D位
ROUND(X), ROUND(X,D) FORMAT(X,D) 保留小数四舍五入
PI()
MOD(N,M), N % M, N MOD M

运算规则

如果两个数都是整数，会得到bigint(-2^63-2^64)

如果两个数有一个是实数或者字符，会得到精度最大的值

DIV 整数除法，不四舍五入，得到bigint

/ 除法默认四位四舍五入会根据两个数的小数位变化

Mod % 取余数

类型转换

CONVERT(expr,type), CONVERT(expr USING transcoding_name)
CAST(expr AS type [ARRAY])

控制流功能

case两种用法，一种是case value when other_value then .. end用于比较值
另一种用于不同情况的不同处理

IF（expr1, res1, res2）满足表达式就返回res1，不满足返回res2

IFNULL（expr1,expr2）expr1不是null就返回expr1，否则返回expr2

NULLIF（expr1,expr2）如果两个表达式相同，就返回NULL,否则返回表达式1

时间和日期

日期/时间加减

DATE_ADD(date,INTERVAL expr unit),
DATE_SUB(date,INTERVAL exprunit)
SUBDATE(date,INTERVAL expr unit),
SUBDATE(expr,days)
SUBTIME(expr1,expr2)

TIMEDIFF(expr1,expr2)
DATEDIFF(date1,date2)
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
PERIOD_DIFF(P1,P2)

获取日期/时间

CURDATE() 日期 DATE()
CURTIME() 时间
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP LOCALTIME(), LOCALTIME now（）、SYSDATE([fsp]) 日期+时间
STR_TO_DATE(str,format)

提取年/月/日/周/时/分/秒

DAYOFMONTH() 一个月的那天（0-31）
MONTH(date) 所属月份提取月份
Year(date)
QUARTER(date) 所属季度
WEEKDAY(date) 工作日的索引
WEEK(date[,mode]) 是一年中的第几周

聚合函数

1） AVG,COUNT,SUM,MIN,MAX，忽略NULL值，如果不写group by就相当于对所有行操作

2）大部分聚合函数可以用来当作窗口函数

3）对于数字，sum/avg返回DECIMAL类型（若输入是integer/decimal）;返回double（若输入是FLOAT/DOUBLE）,这两个函数只对数字型的可以操作,其他类型需要cast成数字型

语法

AVG([DISTINCT] expr) [over_clause] distinct 不匹配返回NULL
COUNT(expr) [over_clause] BIGINT 只统计非NULL的值COUNT(*) 统计NULL行
COUNT(DISTINCT expr,[expr...]) 统计不同的非NULL的行
GROUP_CONCAT(expr) 返回字符串，拼接非NULL的值，如果没有非NULL的值就返回NULL
GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])
JSON_ARRAYAGG(col_or_expr) [over_clause] 聚合一个结果集成一个json，没有结果的话就返回NULL
JSON_OBJECTAGG(key, value) [over_clause]
MAX([DISTINCT] expr) [over_clause]
MIN([DISTINCT] expr) [over_clause]
SUM([DISTINCT] expr) [over_clause]

窗口函数

窗口函数执行类似聚合的操作，但是聚合的组查询结果只有一行，一个窗口函数会为每行产生一个结果

窗口函数只能写在select语句和order by中,from-where-group by-having-window functions-order by-limit-select

Over 语句可以没有的情况

AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()

Over语句必须有的情况

CUME_DIST() over_clause
一个值在一个组中的累计分布，也就是小于等于这个数的所有数的比例，要和order by一起使用
PERCENT_RANK() over_clause
小于等于该值的行数/总行数 over order by
FIRST_VALUE(expr) [null_treatment] over_clause
返回每个分区的第一个值
LAG(expr [, N[, default]]) [null_treatment] over_clause
返回每个expr的前N行的数
LEAD(expr [, N[, default]]) [null_treatment] over_clause
返回每个expr的后n行的数
LAST_VALUE(expr) [null_treatment] over_clause
返回每个expr(字段））））的最后一个值
NTH_VALUE()
NTILE(N) over_clause
括号里面写数字，指定分为几个区 over order by
RANK() over_clause
该行在分区所有行的排名，有并列第1就没有第2直接第3了，with gaps
DENSE_RANK() over_clause
该行在分区所有行的排名，没有gaps
ROW_NUMBER() over_clause
每个分区的行数over order by，每个分区从第一行开始

sql语法

数据定义句法

1）ALTER
2) CREATE
3) DROP
4) RENAME
5) TRUNCATE TABLE

数据操作句法

limit

SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15

union

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

子查询

单行查询

SELECT * FROM t1 AS t
WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

non_subquery_operand comparison_operator (subquery)
= > < >= <= <> != <=>