HelloWorld
登录

mysql基础（1）

Wesley13

2021-10-11

1016 0 0

1.常见术语

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

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

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

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

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

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

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

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

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

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

2.Mysql启动与连接

1# 启动服务
21、mysqld --console　　
32、net start mysql　　
4
5# 关闭服务
61、mysqladmin -uroot shudown　　
72、net stop mysql
8
9# 连接服务器
101、mysql -u root -p # 本地连接
112、mysql -h host -u user -p # 远程连接
12
13# 断开服务器
14quit
15
16# 创建数据库
171、create database 库名
18
19# 使用数据库
201、use 库名
21
22# 删除数据库
231、drop database 库名
24
25# 创建表
26create table 表名(字段名 字段类型)
27
28# 删除表
29drop table 表名
30
31# 更新表，新增一列
32alter table 表名 add 列名 数据类型
33
34# 更新表，删除一列
35alter table 表名 drop column 列名
36
37# 更新表，更改字段数据类型
38alter table 表名 modify 列名 数据类型
39
40# 更新表，更新指定字段名称及数据类型
41alter table 表名 change 列名 新列名 数据类型

3.数据写入

1# 插入一行
2INSERT INTO table_name ( field1, field2,...fieldN ) VALUES
3( value1, value2,...valueN );
4
5# 插入多行
6INSERT INTO table_name ( field1, field2,...fieldN ) VALUES
7( value1, value2,...valueN ),
8( value1, value2,...valueN );
9
10#忽视库中已存在数据，新增数据
11INSERT IGNORE INTO table_name ( field1, field2,...fieldN ) VALUES
12( value1, value2,...valueN )；
13
14# 已存在替换，不存在新增（谨慎使用，待验证）
15REPLACE INTO table_name ( field1, field2,...fieldN ) VALUES
16( value1, value2,...valueN )；

4.查询数据

1# 查询语句
2select 列1,列2 from 表名;
3
4# 去重查询
5select distinct 列1,列2 from 表名;
6
7# 子查询
8select col_namefrom table_name 
9where  col_name  = (select  col_name  from table_name where .... );

5.更新数据

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

6.删除数据

1# 删除指定数据
2DELETE FROM 表名称 WHERE 列名称 = 值
3
4# 删除全部数据
5DELETE FROM 表名称

7.运算符

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

8.Order by

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

9.Limit

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

10.别名AS

select name as na from employees

11.表连接

1# 引用2个表
2SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
3FROM Persons, Orders
4WHERE Persons.Id_P = Orders.Id_P 
5
6# join引用2个表（同inner join）
7SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
8FROM Persons
9INNER JOIN Orders
10ON Persons.Id_P = Orders.Id_P
11ORDER BY Persons.LastName
12
13# inner join内连接是将两个表中相同的部分查询出来，相当于两个表的交集。
14# left join左连接相当于将左表的数据以及右表符合搜索条件的数据查询出来，如果右表没有该记录则为null
15# right join与左连接相反，右连接会显示右表的所有数据以及符合搜索条件的左表记录

12.sql语句合并

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

13.sql运算函数

1平均值：avg()
2计数：count()
3最小值：min()
4最大值：max()
5求和：sum()
6长度：length()
7转大写：ucase()
8转小写：lcase()
9保留位数：round()
10分组：order by
11筛选分组：having
12
13WHERE 语句和HAVING配合的使用。
14WHERE在HAVING之前。
15WHERE 过滤针对的是行，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函数

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

示例如下：

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

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）

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

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

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

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

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

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

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

1DELETE FROM user 
2WHERE
3	nick_name IN ( SELECT nick_name FROM ( SELECT nick_name FROM user GROUP BY nick_name HAVING count( nick_name ) > 1 ) AS tmp1 ) 
4	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）

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

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

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

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

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

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

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

1DELETE FROM user 
2WHERE
3	( 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 ) 
4	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 );
点赞
收藏

评论区

加载中...

相关推荐

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

Oracle 分组与拼接字符串同时使用

SELECTT.,ROWNUMIDFROM(SELECTT.EMPLID,T.NAME,T.BU,T.REALDEPART,T.FORMATDATE,SUM(T.S0)S0,MAX(UPDATETIME)CREATETIME,LISTAGG(TOCHAR(

皕杰报表之UUID

​在我们用皕杰报表工具设计填报报表时，如何在新增行里自动增加id呢？能新增整数排序id吗？目前可以在新增行里自动增加id，但只能用uuid函数增加UUID编码，不能新增整数排序id。uuid函数说明：获取一个UUID，可以在填报表中用来创建数据ID语法：uuid()或uuid(sep)参数说明：sep布尔值，生成的uuid中是否包含分隔符''，缺省为

手写Java HashMap源码

HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22

sql注入

反引号是个比较特别的字符，下面记录下怎么利用0x00SQL注入反引号可利用在分隔符及注释作用，不过使用范围只于表名、数据库名、字段名、起别名这些场景，下面具体说下1）表名payload：select\from\users\whereuser\_id1limit0,1;!(https://o

2020年前端实用代码段，为你的工作保驾护航

有空的时候，自己总结了几个代码段，在开发中也经常使用，谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )

mysql基础（1） - HelloWorld