有关各种数据库知识及学习心得
7月2

sql 多表关联更新方法

21:11数据库  From: 本站原创
#表结构:
1、表一:Test1
Id  name  age
1      
2      

2、表二:Test2
Id  name  age
1  小明  10
2  小红  8


#实现将表Test2的name和age字段数据更新到表Test1中,按照id相等的条件

1、SQLServer多表更新方法:
语法:

UPDATE { table_name WITH ( < table_hint_limited > [ ...n ] ) &#124; view_name &#124; rowset_function_limited }
SET { column_name = { expression &#124; DEFAULT &#124; NULL } &#124; @variable = expression &#124; @variable = column = expression } [ ,...n ]
{ { [ FROM { < table_source > } [ ,...n ] ] [ WHERE < search_condition > ] } &#124; [ WHERE CURRENT OF { { [ GLOBAL ] cursor_name } &#124; cursor_variable_name } ] } [ OPTION ( < query_hint > [ ,...n ] ) ]
例子:

update test1
set test1.name=test2.name,test1.age=test2.age
from test1
inner join test2
on test1.id=test2.id

2、Oracle 多表更新方法:
语法:

UPDATE updatedtable
SET (col_name1[,col_name2...])= (SELECT col_name1,[,col_name2...]
FROM srctable [WHERE where_definition])
例子:

update test1
set (test1.name,test1.age)=
(select test2.name,test2.age from test2 where test2.id=test1.id)

3、MySql多表更新方法:
语法:

UPDATE table_references
SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]
例子:

update test1,test2
set test1.name=test2.name,test1.age=test2.age
where test1.id=test2.id

4、通用方法:(*^__^*)
update test1
set name=(select name from test2 where test2.id=test1.id),
age=(select age from test2 where test2.id=test1.id)
4月14
数据库中的数据 需要批量的替换 ,替换某个词或特殊符号

sql语句:

update 表名 set 字段名=REPLACE (字段名,'原来的值','要修改的值')

update  pmsprojectmember set projectMemberType=REPLACE(projectMemberType,'BusinessOprator','BusinessOperator')

update user_item set addr=REPLACE (addr,'长沙','湘府')

添加条件:
update user_item set addr=REPLACE (addr,'长沙','湘府') where time<'2021-1-5';
1月25

mongodb 设置用户密码权限

09:03数据库  From: 本站原创
1、设置管理员账户

use admin
db.createUser({ user: "useradmin", pwd: "adminpassword", roles: [{ role: "userAdminAnyDatabase", db: "admin" }] })
mongodb中的用户是基于身份role的,该管理员账户的 role是 userAdminAnyDatabase。 ‘userAdmin’代表用户管理身份,’AnyDatabase’ 代表可以管理任何数据库。

2、验证

db.auth("useradmin", "adminpassword")
如果返回1,则表示成功。

3、修改配置文件(注意缩进)

sudo vi /etc/mongod.conf
#security:
security:
  authorization: enabled

4、重启mongodb

sudo service mongod restart
5、创建普通用户

进入mongodb,用第3步的 管理员账户登录,用该账户创建其他数据库管理员账号

  +先进行管理员认证

use admin
db.auth("useradmin", "adminpassword")
  +设置其他库的账号密码及权限

use yourdatabase
db.createUser({ user: "youruser", pwd: "yourpassword", roles: [{ role: "dbOwner", db: "yourdatabase" }] })
  rote:dbOwner 代表数据库所有者角色,拥有最高该数据库最高权限。比如新建索引等
12月23

MySQL的replace方法

09:20数据库  From: 本站原创
mysql中replace函数直接替换mysql数据库中某字段中的特定字符串,不再需要自己写函数去替换,用起来非常的方便,mysql 替换函数replace()
Update `table_name` SET `field_name` = replace (`field_name`,’from_str’,'to_str’) Where `field_name` LIKE ‘%from_str%’

实例:把'病假' 替换为 '--':UPDATE users SET username=REPLACE(username,'病假','--')  WHERE username LIKE '%病假%';
说明:
table_name —— 表的名字
field_name —— 字段名
from_str —— 需要替换的字符串
to_str —— 替换成的字符串

---------------------------------

mysql 里面可以用uuid()语句来生成一个UUID:

select uuid();
或 select replace(uuid(), '-', '');

直接在insert语句中插入UUID作主键的用法(简便):

insert into Price( Name, UUID, Price, BID) values('FEIFEI_TEST', uuid(), 32, 3);

------------------------------
在mysql中,可以使用uuid 来生成主键,但是用mysql的uuid()函数 ,生成的uuid是36位的,其中包含32个字符以及4个分隔符(-),往往这个分隔符对我们来说是没有用的,可以使用mysql自带的replace函数去掉分隔符

replace(uuid(),'-','')   ---->将uuid()中的‘-’,去掉,即替换成空串;

此外

upper(replace(uuid(),'-',''))用于将字符转换为大写
--------------------------------

一、replace函数
语法:replace(object,search,replace)
语义:把object对象中出现的的search全部替换成replace。
实例:

update hellotable set 'helloCol' = replace('col','heck','heckjj.com')

二、replace into函数
为什么会接触到replace into函数,是因为业务需要向数据库中插入数据,前提是重复的不能再次插入。以前用where解决的,今天才知道还有一个更简洁的方法replace。
replace具备替换拥有唯一索引或者主键索引重复数据的能力,也就是如果使用replace into插入的数据的唯一索引或者主键索引与之前的数据有重复的情况,将会删除原先的数据,然后再进行添加。
语法:replace into table( col1, col2, col3 ) values ( val1, val2, val3 )
语义:向table表中col1, col2, col3列replace数据val1,val2,val3

实例:
REPLACE INTO users (id,name,age) VALUES(123, ‘chao’, 50);
9月14
SELECT COUNT(*) 

FROM (SELECT 分组字dao段 FROM 表 

GROUP BY 分组字段 

)别名 

或者 

SELECT COUNT(*) 

FROM (SELECT distinct 分组字段 FROM 表)别名

扩展资料:
SQL分组查询

在SQL Server中使用的分组查询是ORDER BY子句,使用ORDER BY子句要同聚合函数配合使用才能完成分组查询,在SELECT查询的字段中如果字段没有使用聚合函数就必须出现在ORDER BY子句中(即SELECT后边的字段名要么出现在聚合函数中,要么在ORDER BY子句中使用)

在分组查询中还可以配合使用HAVING子句,定义查询条件。

使用group by进行分组查询

在使用group by关键字时,在select列表中可以指定的项目是有限制的,select语句中仅许以下几项:

1、被分组的列

2、为每个分组返回一个值得表达式,例如用一个列名作为参数的聚合函数

3、group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面


要得到每组的合计可以用下面的,要总计用上面的,想一次性得到分组合计以内及总计,可以这么写:

SELECT 分组容字段 FROM 表
GROUP BY 分组字段
compute sum(COUNT(*))


那就这样
SELECT COUNT(*)
FROM (SELECT 分组字段 FROM 表
GROUP BY 分组字段
)别名

或者

SELECT COUNT(*)
FROM (SELECT distinct 分组字段 FROM 表)别名
7月9
MySQL中,查询表(crawl_content)中字段(url)值重复的记录:

SELECT url, COUNT(*) AS sumCount FROM crawl_content GROUP BY url HAVING sumCount > 1;
说明:先用GROUP BY 对 url进行分组,同时使用COUNT(*)进行统计,再用HAVING来过滤大于1的,这样查找出来的就是重复的记录了。
4月3
1、水平切分:
通俗的来讲
例:QQ的用户表。假设QQ的用户有100亿,如果只有一张表,每个用户登录的时候数据库都要从这100亿中查找,会很慢很慢。如果将这一张表分成100份,每张表有1亿条,就小了很多,比如qq_user_0,qq_user_1,qq_user_2...qq_user_99表。

用户登录的时候,可以将用户的id%100,那么会得到0-99的数,查询表的时候,将表名qq跟取模的数连接起来,就构建了表名。比如123456789用户,取模的89,那么就到qq_user_89表查询,查询的时间将会大大缩短。

这就是水平切分。

2、垂直切分:

垂直切分指的是:表的记录并不多,但是字段却很长,表占用空间很大,检索表的时候需要执行大量的IO,严重降低了性能。这时需要把大的字段拆分到另一个表,并且该表与原表是一对一的关系。

例如学生答题表tt:有如下字段:

Id name 分数 题目 回答

其中题目和回答是比较大的字段,id name 分数比较小。

如果我们只想查询id为8的学生的分数:select 分数 from tt where id = 8;虽然知识查询分数,但是题目和回答这两个大字段也是要被扫描的,很消耗性能。但是我们只关心分数,并不想查询题目和回答。这就可以使用垂直分割。我们可以把题目单独放到一张表中,通过id与tt表建立一对一的关系,同样将回答单独放到一张表中。这样我们插叙tt中的分数的时候就不会扫描题目和回答了。

3、其他要点:

1)存放图片、文件等大文件用文件系统存储。数据库只存储路径,图片和文件存放在文件系统,甚至单独存放在一台服务器(图床)。

2)数据参数配置。

最重要的参数就是内存,我们主要用的innodb引擎,所以下面两个参数调的很大:

innodb_additional_mem_pool_size=64M

innodb_buffer_pool_size=1G

对于MyISAM,需要调整key_buffer_size,当然调整参数还是要看状态,用show status语句可以看到当前状态,以决定该调整哪些参数。
10月31

常用MySQL集群解决方案

10:39数据库  From: 本站原创
1、mysql企业常用集群架构
点击在新窗口中浏览此图片

在中小型互联网的企业中。mysql的集群一般就是上图的架构。WEB节点读取数据库的时候读取dbproxy服务器。dbproxy服务器通过对SQL语句的判断来进行数据库的读写分离。读请求负载到从库(也可以把主库加上),写请求写主库。

这里的dbproxy是数据库集群的唯一出口所以也需要做高可用。

drproxy是数据库读写分离的常用软件,amoeba、mycat、cobar也很常用。这类软件不仅带有读写分离功能,还可以实现负载均衡以及后端节点的健康检查。

数据库的读写分离除了通过这类数据库中间件软件实现,还可以写在程序中。

通常我们的主库要做双主高可用,实现主库挂掉另一个主库立刻接管。如果不做双主,从库接管主库的时候需要做状态迁移,会有延迟。

数据库主库的高可用重点需要考虑的是数据同步。比较常用的高可用方案有:

1、keepalived+mysql replication。通过keepalived实现VIP飘逸,通过mysql自带的同步方案replication实现数据同步。

2、hearbeat+drbd。通过drbd实现双主数据的同步,这个数据同步是基于块设备的。比一般的同步方案要快很多。通过heartbeat实现VIP漂移以及drbd资源的切换管理。

3、keepalived+mha。

对于从库,最好不要超过5个。我们可以把其中的三个作为用户访问的节点,把另外一个作为内部人员的查询节点。因为内部人员查询节点的时候一般是按照时间段查询,不经过索引,占用的资源比较多,所以要把这个节点单独专用,以免影响客户访问。最后我们应该留一个从库进行数据库的数据备份。

从库的数据一致性保持可以通过直接于主库进行主从辅助,也可以从其他从库那进行主从复制(优点是减少主库压力,缺点是延迟稍大)。

2、MYSQL数据架构
数据库服务器==》数据库(多个实例)==》多个库==》多个表==》多个字段行列(数据)

在一台数据库服务器上可以跑多个实例,一个实例中有多个库,一个库有多个表,一个表有多个行列。
3月8
很多MySQL程序都会带有匿名登录的功能。

在刚刚安装完MySQL之后,就能够登录数据库啦。

这对于平时使用MySQL来说也基本没有什么,可是假设我们想部署数据库的时候。这样的登录方式式绝对不能存在的!试想一下,假设你的数据库随便就能够进入的话,我想你一定会在半夜收到电话,说数据出问题啦!

以下介绍一下删除匿名用户的方式:


首先使用命令进入数据库


[root@localhost raul]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


然后切换数据库,进入我们须要操作的数据库mysql
3月8
在导入的时候,用phpMyAdmin去导入30多M左右的mysql数据库文件时出错:

您可能正在上传很大的文件,请参考文档来寻找解决方法。

1.很明显,是文件太大,无法导入。即上传文件大小有限制。所以要去解除此限制。

2.其实这个很简单的只要更改php.ini里三个配置即可。(见下面加粗部分,改成你自己的需求即可)

; Maximum allowed size for uploaded files.
; http://php.net/upload-max-filesize
;upload_max_filesize = 2M
upload_max_filesize = 300M

; Maximum amount of memory a script may consume (128MB)
; http://php.net/memory-limit
;memory_limit = 128M
memory_limit = 300M

; Maximum size of POST data that PHP will accept.
; http://php.net/post-max-size
;post_max_size = 8M
post_max_size = 300M

刷新一下phpMyAdmin,重新导入试试,结果还是不行。

很多朋友们肯定也是很是纳闷,春哥在此提醒大家,改完以后,一定要记得重启服务。

还有,一定是3个都得相应的修改,否则不成功。
分页: 1/3 第一页 1 2 3 下页 最后页 [ 显示模式: 摘要 | 列表 ]