mysql基础（1）-常用操作

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

14.窗口函数

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 );

posted @ 2019-12-19 17:31 麦小秋阅读(260) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

麦小秋

记录学习历程！个人Q群：870467632（Python学习交流群）欢迎Python爱好者加入，一起学习，共同进步！

mysql基础（1）-常用操作

1.常见术语

2.Mysql启动与连接

3.数据写入

4.查询数据

5.更新数据

6.删除数据

7.运算符

8.Order by

9.Limit

10.别名AS

11.表连接

12.sql语句合并

13.sql运算函数

14.窗口函数

15.case...when函数

14.窗口函数

15.查重与去重

公告