mysql基础（1）

Wesley13
• 阅读 558

1.常见术语

  • 数据库: 数据库是一些关联表的集合。.

  • 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。

  • 列: 一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。

  • 行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。

  • 冗余：存储两倍数据，冗余可以使系统速度更快。

  • 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。

  • 外键：外键用于关联两个表。

  • 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。

  • 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。

  • 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

2.Mysql启动与连接

# 启动服务
1、mysqld --console　　
2、net start mysql　　

# 关闭服务
1、mysqladmin -uroot shudown　　
2、net stop mysql

# 连接服务器
1、mysql -u root -p # 本地连接
2、mysql -h host -u user -p # 远程连接

# 断开服务器
quit

# 创建数据库
1、create database 库名

# 使用数据库
1、use 库名

# 删除数据库
1、drop database 库名

# 创建表
create table 表名(字段名 字段类型)

# 删除表
drop table 表名

# 更新表，新增一列
alter table 表名 add 列名 数据类型

# 更新表，删除一列
alter table 表名 drop column 列名

# 更新表，更改字段数据类型
alter table 表名 modify 列名 数据类型

# 更新表，更新指定字段名称及数据类型
alter table 表名 change 列名 新列名 数据类型

3.数据写入

# 插入一行
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES
( value1, value2,...valueN );

# 插入多行
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES
( value1, value2,...valueN ),
( value1, value2,...valueN );

#忽视库中已存在数据，新增数据
INSERT IGNORE INTO table_name ( field1, field2,...fieldN ) VALUES
( value1, value2,...valueN )；

# 已存在替换，不存在新增（谨慎使用，待验证）
REPLACE INTO table_name ( field1, field2,...fieldN ) VALUES
( value1, value2,...valueN )；

4.查询数据

# 查询语句
select 列1,列2 from 表名;

# 去重查询
select distinct 列1,列2 from 表名;

# 子查询
select col_namefrom table_name 
where  col_name  = (select  col_name  from table_name where .... );

5.更新数据

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值

6.删除数据

# 删除指定数据
DELETE FROM 表名称 WHERE 列名称 = 值

# 删除全部数据
DELETE FROM 表名称

7.运算符

select * from employees where age > 20;
select * from employees where age > 20 and age <60;
select * from employees where age <20 or age >60;
select * from employees where (age <20 or age >60) and city = 'beijing' # and优先级高于or
select * from employees where city in ('beijing','shanghai')
select * from employees where city not in ('beijing','shanghai')
select * from employees where name like '%gr%'; # 查询包含'gr'数据
select * from employees where name like 'gr%' # 查询以'gr'开头数据
select * from employees where name like '%gr' # 查询以'gr'结尾数据
select * from employees where name like '_gr%' # _表示匹配单个字符，%表示匹配多个字符
select * from employees where age between 20 and 60;

8.Order by

# 用于对结果集进行排序
select * from employees order by age asc # 升序
select * from employees order by age desc # 降序
select * from employees order by name asc,age desc　# 指定升、降排序数据列

9.Limit

select * from employees limit 5
select * from employees limit 5,2　　# 跳过5条记录向后取2条数据记录
select * from employees limit 5 offset 2　# 从第2条记录开始选择5条数据记录

10.别名AS

select name as na from employees

11.表连接

# 引用2个表
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P 

# join引用2个表（同inner join）
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

# inner join内连接是将两个表中相同的部分查询出来，相当于两个表的交集。
# left join左连接相当于将左表的数据以及右表符合搜索条件的数据查询出来，如果右表没有该记录则为null
# right join与左连接相反，右连接会显示右表的所有数据以及符合搜索条件的左表记录

12.sql语句合并

# union用于合并两个或多个 SELECT 语句的结果集
# union内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。
# union不允许重复值出现
select column_name(s) from table_name1
union
select column_name(s) from table_name2

# union all用法同上，区别union all允许重复值出现

13.sql运算函数

平均值：avg()
计数：count()
最小值：min()
最大值：max()
求和：sum()
长度：length()
转大写：ucase()
转小写：lcase()
保留位数：round()
分组：order by
筛选分组：having

WHERE 语句和HAVING配合的使用。
WHERE在HAVING之前。
WHERE 过滤针对的是行，HAVING过滤针对的是组。

14.窗口函数

sql中有一类函数叫聚合函数，比如count、sum、avg、min、max等，这些函数可将多行数据按照规整聚集为一行，一般聚集前的数据行大于聚集后数据行，有时候，我们不急需要聚集前的数据，还想要聚集后的数据，此时，便引入了窗口函数。

窗口函数是对一组值进行操作，不需要使用GROUP BY 子句对数据进行分组，还能够在同一行中同时返回基础行的列和聚合列。举例来说，我们要得到一个年级所有班级所有学生的平均分，按照传统的写法，我们肯定是通过AVG聚合函数来实现求平均分。这样带来的”坏处“是我们不能轻松地返回基础行的列（班级，学生等列），而只能得到聚合列。因为聚合函数的要点就是对一组值进行聚合，以GROUP BY 查询作为操作的上下文，由于GROUP BY 操作对数据进行分组后，查询为每个组只返回一行数据，因此，要限制所有表达式为每个组只返回一个值。而通过窗口函数，基础列和聚合列的查询都轻而易举。

常见窗口函数如下：

排序：rank() over、row_number over()、dense_rank(),常结合partition by 分组、order by 排序进行使用

窗口函数参考文章：

https://zhuanlan.zhihu.com/p/92654574

https://www.cnblogs.com/jeffwongishandsome/archive/2010/12/04/1896672.html

15.case...when函数

select name as '英雄',age as '年龄',
case
    when age < 18 then '少年'
    when age > 60 then '老年'
    else '青年'
end '状态'
from user;
 
16.其他未完待续....
# 字符串连接
select concat('I\'m from ',name) from world.country where code = 'CHN';

示例如下：

SELECT ts_code as '股票代码',
(CASE 
    WHEN list_status = 'L' THEN '是'
    WHEN list_status = 'P' THEN '否'
END) as '上市状态'
FROM quant.all_stock

mysql基础（1）

14.窗口函数

窗口函数是对一组值进行操作，不需要使用GROUP BY 子句对数据进行分组，还能够在同一行中同时返回基础行的列和聚合列。举例来说，我们要得到一个年级所有班级所有学生的平均分，按照传统的写法，我们肯定是通过AVG聚合函数来实现求平均分。这样带来的”坏处“是我们不能轻松地返回基础行的列（班级，学生等列），而只能得到聚合列。因为聚合函数的要点就是对一组值进行聚合，以GROUP BY 查询作为操作的上下文，由于GROUP BY 操作对数据进行分组后，查询为每个组只返回一行数据，因此，要限制所有表达式为每个组只返回一个值。而通过窗口函数，基础列和聚合列的查询都轻而易举。
常见窗口函数如下：
排序：rank() over、row_number over()、dense_rank(),常结合partition by 分组、order by 排序进行使用
窗口函数参考文章：
https://zhuanlan.zhihu.com/p/92654574
https://www.cnblogs.com/jeffwongishandsome/archive/2010/12/04/1896672.html

15.查重与去重

假设有一个表user，字段分别有id–nick_name–password–email–phone，分情况如下（注意删除多余记录时要创建临时表，不然会报错）

1）单字段（nick_name）

查出所有有重复记录的所有记录

SELECT *  FROM user
WHERE 
　　　　nick_name IN  ( SELECT nick_name FROM user GROUP BY nick_name HAVING count( nick_name ) > 1 );

查出有重复记录的各个记录组中id最大的记录

SELECT * FROM user 
WHERE 
　　　　id IN  ( SELECT max( id ) FROM user GROUP BY nick_name HAVING count( nick_name ) > 1 );

查出多余的记录，不查出id最小的记录

SELECT * FROM user 
WHERE
    nick_name IN ( SELECT nick_name FROM user GROUP BY nick_name HAVING count( nick_name ) > 1 ) 
    AND id NOT IN ( SELECT min( id ) FROM user GROUP BY nick_name HAVING count( nick_name ) > 1 );

删除多余的重复记录，只保留id最小的记录

DELETE FROM user 
WHERE
    nick_name IN ( SELECT nick_name FROM ( SELECT nick_name FROM user GROUP BY nick_name HAVING count( nick_name ) > 1 ) AS tmp1 ) 
    AND id NOT IN ( SELECT id FROM ( SELECT min( id ) FROM user GROUP BY nick_name HAVING count( nick_name ) > 1 ) AS tmp2 );

2）多字段（nick_name,password）

查出所有有重复记录的记录

select * from user 
where 
　　  (nick_name,password) in (select nick_name,password from user group by nick_name,password where having count(nick_name)>1);

查出有重复记录的各个记录组中id最大的记录

select * from user 
where 
　　　id in (select max(id) from user group by nick_name,password where having count(nick_name)>1);

查出各个重复记录组中多余的记录数据，不查出id最小的一条

select * from user 
where (nick_name,password) in
     (select nick_name,password from user group by nick_name,password having count(nick_name)>1)
　　  and id not in (select min(id) from user group by nick_name,password having count(nick_name)>1);

删除多余的重复记录，只保留id最小的记录

DELETE FROM user 
WHERE
    ( nick_name, PASSWORD ) IN (SELECT nick_name,password FROM( SELECT nick_name, password FROM user GROUP BY nick_name, password HAVING count( nick_name ) > 1 ) AS tmp1 ) 
    AND id NOT IN (SELECT id FROM( SELECT min( id ) id FROM user GROUP BY nick_name, PASSWORD HAVING count( nick_name ) > 1 ) AS tmp2 );
selectmysql update语句mysql创建数据库mysql运维开发运维开发
点赞
收藏
评论区
推荐文章
blmius blmius
•
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时，报错：原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等，这样可以更容易地在不同的环境中使用MySQL。全局s
4243
0
0
helloworld_34035044 helloworld_34035044
•
2年前
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为
1205
0
0
待兔 待兔
•
5个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
1133
0
0
Jacquelyn38 Jacquelyn38
•
3年前
2020年前端实用代码段，为你的工作保驾护航
有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
2445
0
0
Easter79 Easter79
•
3年前
sql注入
反引号是个比较特别的字符，下面记录下怎么利用0x00SQL注入反引号可利用在分隔符及注释作用，不过使用范围只于表名、数据库名、字段名、起别名这些场景，下面具体说下1）表名payload：select\from\users\whereuser\_id1limit0,1;!(https://o
814
0
0
Stella981 Stella981
•
3年前
Python3：sqlalchemy对mysql数据库操作，非sql语句
Python3：sqlalchemy对mysql数据库操作，非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
1178
0
0
Easter79 Easter79
•
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中，有一些需要使用全局唯一ID的场景，这种时候为了防止ID冲突可以使用36位的UUID，但是UUID有一些缺点，首先他相对比较长，另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID，并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求，最初Twitter把存储系统从MySQL迁移
1213
0
0
Wesley13 Wesley13
•
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败，请联系管理员！');中国在东8区所以加8方法二：selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
1381
0
0
Wesley13 Wesley13
•
3年前
thinkphp 基本配置
12returnarray(34//定义数据库连接信息5'DB\_TYPE''mysql',//指定数据库是mysql67'DB\_HOST''localhost',89'DB\_NAME''uchome',//数据库名1011'DB\_USER''root
870
0
0
京东云开发者 京东云开发者
•
2年前
为什么mysql不推荐使用雪花ID作为主键
作者：毛辰飞背景在mysql中设计表的时候，mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一)，而是推荐连续自增的主键id，官方的推荐是auto_increment，那么为什么不建议采用uuid，使用uuid究
1088
0
0
Wesley13
Wesley13
Lv1
最大的安心是自律温柔和爱自己。
文章
1.2w
粉丝
22
获赞
1.7k
热门文章
JavPlayer：AI破坏马赛克，大量马赛克破坏版影片流出
2020年最新可用的磁力搜索引擎&种子搜索网站，持续整理分享
8个在线接收手机短信验证码的免费网络服务整理
IDM 激活破解工具
java.net.ConnectException：Connection timed out：connect 出现连接超时的问题？