文档章节

mysql操作

Scofieldxs
 Scofieldxs
发布于 2015/08/17 13:58
字数 1535
阅读 54
收藏 0

####1.resultMap

	<resultMap type="Blog" id="BlogResult">  
        <id column="id" property="id"/>  
        <result column="title" property="title"/>  
        <result column="content" property="content"/>  
        <result column="owner" property="owner"/>  
    </resultMap>
  • column表示从数据库中查询的属性
  • Property则表示查询出来的属性对应的值赋给实体对象的哪个属性

####2.列变行+count

  • 需求:需要对某一列的数据做统计,看每一种类型出现了多少次:

      SELECT name,
      		COUNT( CASE ad_format WHEN 'A' THEN 'A' END)AS a,
      		COUNT( CASE ad_format WHEN 'B' THEN 'B' END)AS b,
      		FROM ad
      		<where>
      		     name=#{name, jdbcType=VARCHAR} AND type=1
      		</where>
      		GROUP BY adp_name
    

####4.批处理表字段名: DESC tablename

####5.查询不为空的字段

    appsid IS NOT NULL AND appsid!=''

####6.数据库间倒数据

  • 读数据到file:

    mysql -h(ip) -P(port) -u(user) -p(password) --default-character-set=utf8 database -Ne "select a from table b">file(filename)

  • load file到数据库

      LOAD DATA LOCAL INFILE 'data' INTO TABLE table_name (colum1,colum2);
    

执行: sh mysql.sh < load_data.sql

####7.两个表的差集

select table1.id from table1 
      left join table2
      on table1.id=table2.id
       where table2.id is null;

####8.数据类型选择 #####1.CHAR && VARCHAR

  • CHAR:固定长度,处理快,但是浪费空间
  • VARCHAR:变长,innodb建议使用VARCHAR

#####2.TEXT && BLOB

  • BLOB可以存储二进制文件,如图片

#####3.float && demical

  • float,double浮点数超过精度四舍五入
  • demical实际存的是字符串,超过精度四舍五入并警告

#####4.整数 1. TINYINT:8位 2. SMALLINT:16 3. MEDIUMINT:24 4. INT:32 5. BIGINT:64

指定宽度没有多大意义,对于存储和计算INT(1)和INT(20)是相同的

#####5. datetime && timestamp

  • datetime:从1001年--9999年,精度秒,范围大

  • timestamp:1970年--2038年,默认更新

####9.大块数据查询:可以建立MD5索引,根据索引查快:

CREATE TABLE t(id VARCHAR(100),context BLOB,hash_value VARCHAR(50));//建表
//
INSERT INTO t VALUES(1,REPEAT('hello this is me',20),MD5(context));//插入数据

SELECT * FROM t WHERE hash_value=MD5(REPEAT('hello this is me',20))//查询

称为合成索引,只适用于精确匹配

####10.大块数据模糊匹配,建立前缀索引 只为前几列建立索引

CREATE INDEX index_blob ON t(context(10))

SELECT * FROM t WHERE context LIKE 'xiaobai%'

####11.mysql批量导入数据 应用场景:从一个数据库把数据导入另一个数据库

把数据存成txt格式,数据间用tab分隔

执行sql语句:

LOAD DATA INFILE 'C:/data.txt' INTO TABLE department_site_trade LINES TERMINATED BY '\r\n' (dept_id,first_trade_id,create_time); 因为数据库第一列为自增,所以得指定插入的列

参数:

terminated by分隔符:意思是以什么字符作为分隔符,默认情况下是tab字符(\t)(在word里面转换tab用^t)

escaped by描述的转义字符。默认的是反斜杠(\ )

####12.sql模糊查询

select * from tablename where ID like 'Mc%'

"_"表示与任一个字符相匹配

"_"表示与任一个字符相匹配

"%"匹配任意多个字符

"[]"匹配指定的一系列字符串 [吉辽]% 表示以吉或辽开头的字符

"[^]"表示否定意思 [^吉辽]% 表示不是以吉或辽开头的字符

####13.sql <include>代替重复出现的语句

<sql id="findAllWhere">  

    where mm = #{love}  

</sql>  

####14.特殊符号做转义

&lt; <   	
&gt; >   	
&amp; &   	
&apos; '   	
&quot; "

if test用法:

<if test="biz == 11"> biz = 11</if> 
<if test="biz != 11"> biz &lt;=10</if>

  

<select id="findAll" resultMap="helloResultMap">  

    select * from HELLO  

    <include refid="findAllWhere"/>  

    order by create_time  

</select> 

####15.group by与count

select count(*)
from information a
group by(a.age)
  • count出来的是每一个组别中的数目,外层嵌套count只能求出有多少个组

      select count(*)
      from (
      	select count(*)
      	information a
      	group by(a.age)
      )aa
    

比如内层count结果:3,1 外层count=2

####16.timestamp

  • 如果你不更新,它可以自己更新字段,常用于数据表中“更新时间”这个字段类型

  • timestamp字段设置:DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

    CREATE TABLE t1 (
    p_c int(11) NOT NULL,

    p_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP )

  • 相对创建时间字段,一般用datetime

####17windows mysql跨表批量导入数据 #####导出数据:

  1. 命令行进入到mysql的bin文件夹

  2. 导出文件

     mysqldump -h(地址) -P(端口) -u(用户名) -p(密码)db(数据库名) detail(表名) --where="date>'2011-01-01'" > d:/test.sql
    

#####导入数据

  1. mysqldump方式导入,容易出现乱码:

     mysqldump -h(地址) -P(端口) -u(用户名) -p(密码)db(数据库名) < d:/test.sql
    

把表中每条sql拼接为insert语句

  1. source导入
    1. mysql进入目标数据库:

       mysql -h{hostname} -p{port}] -u{username} -p{password}
      
    2. source导入

       mysql > source d:/test.sql;
      

####18.计数器表设计

  • 应用场景:需要存储网站访问数,文件下载数,可以单独建一个计数器表

      CREATE TABLE hit_counter(
      	cnt INT UNSIGNED NOT NULL
      )ENGINE=INNODB
    
  • 更新语句:

      UPDATE hit_counter SET cnt=cnt+1
    

改进:因为只有一个字段,每次更新都会锁,变成串行操作,太慢。

因此加个槽,每次随机一个数据

插入100行数据

更新语句:

	UPDATE hit_counter SET cnt=cnt+1 WHERE slot=RAND()*100

统计的时候SUM一下就行

####19.删除重复数据 直接删除会报:You can’t specify target table ‘students’ for update in FROM clause错误。你不能删数据的同时查询

方法是把表复制一份,再查询删除 1.复制表和数据:

CREATE TABLE pinyin_temp SELECT * FROM pinyin

2.删除

DELETE FROM pinyin WHERE pinyin_key IN (SELECT pinyin_key FROM pinyin_temp GROUP BY word_value HAVING COUNT(word_value)>1)

####20.mysql select ifnull 默认值

 select a.*,ifnull(b.phone,0) as phone from name a left join phone

####20.mysql授权外网访问

1.切换到mysql库,给root用户授权host为所有地址,flush privileges表示立即生效

    use mysql;
    update user set host='%' where user='root' and host='localhost';
    flush privileges;

2.让root使用密码'admin456'从任何主机连接到mysql服务器

    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'  IDENTIFIED BY 'admin456'  WITH GRANT OPTION;
    flush privileges;

如果你想允许用户root从ip为192.168.1.104的主机连接到mysql服务器

    GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.104'   IDENTIFIED BY             'admin123'  WITH GRANT OPTION;

####21. mybatis where id in 循环

    <select id="findByIds" parameterType="list" resultType="org.date.com.bo.Account">
	*
	from 	
		account a 			
	where
		a.id in 
		<foreach item="item" collection="ids" separator="," open="(" close=")" index="">  
  			#{item, jdbcType=INTEGER}  
		</foreach>
</select>

####22.TO_DAYS 返回一个日期从0年开始的天数,可用于日期的比较

    select a.Id
    from Weather a,Weather b
    where TO_DAYS(a.Date)=TO_DAYS(b.Date)+1
        and a.Temperature>b.Temperature

####23.查看表所占用的磁盘大小

    SELECT CONCAT(ROUND(SUM(data_length/1024/1024),2),'MB') AS data_length_MB,  
        CONCAT(ROUND(SUM(index_length/1024/1024),2),'MB') AS index_length_MB  
    FROM TABLES WHERE  
        table_schema='库名'  
        AND table_name = '表名';

© 著作权归作者所有

上一篇: java操作
下一篇: web架构提纲
Scofieldxs
粉丝 4
博文 24
码字总数 26524
作品 0
海淀
私信 提问
MySQL 主从复制与读写分离概念及架构分析

1.MySQL主从复制入门 首先,我们看一个图: 影响MySQL-A数据库的操作,在数据库执行后,都会写入本地的日志系统A中。 假设,实时的将变化了的日志系统中的数据库事件操作,在MYSQL-A的3306端...

摩西
2016/10/27
8
0
《史上最简单的MySQL教程》系列分享专栏

《史上最简单的MySQL教程》系列分享专栏 《史上最简单的MySQL教程》已整理成PDF文档,点击可直接下载至本地查阅 https://www.webfalse.com/read/207399.html 简介 整理史上最简单的MySQL教程...

开元中国2015
2018/10/27
0
0
MYSQL远程登录权限设置 ,可以让Navicat远程连接服务器的数据库

MYSQL远程登录权限设置 ,可以让Navicat远程连接服务器的数据库 Mysql默认关闭远程登录权限,如下操作允许用户在任意地点登录: 1. 进入mysql,GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDE...

xiaocao13140
2018/06/06
0
0
Mysql权限控制 - 允许用户远程连接

Mysql为了安全性,在默认情况下用户只允许在本地登录,可是在有此情况下,还是需要使用用户进行远程连接,因此为了使其可以远程需要进行如下操作: 一、允许root用户在任何地方进行远程登录,...

whoisliang
04/03
0
0
python编写mysql类实现mysql的操作

前言 我们都知道利用python实现mysql的操作是件很简单的事情,只需要熟练使用MySQLdb模块就能实现mysql的增删改查操作。 为了更好地整合mysql的操作,使用python的类讲mysql的操作整合到一起...

icenycmh
2018/06/29
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Webpack打包优化:使用外链与拆包模式

一、发现问题 这是一个基于 vue-cli 的管理后台项目,由于依赖较多,打包结果如下 二、查找原因 为什么 vendor 体积这么大? 引用的库太多时,vendor的体积会很大,借助 Webpack 的分析工具,...

AI考拉
26分钟前
1
0
MSSQL-最佳实践-Always Encrypted

author: 风移 摘要 在SQL Server安全系列专题月报分享中,往期我们已经陆续分享了:如何使用对称密钥实现SQL Server列加密技术、使用非对称密钥实现SQL Server列加密、使用混合密钥实现SQL S...

阿里云云栖社区
28分钟前
3
0
ES 集群上,业务单点如何优化升级?

摘要: 原创出处 https://www.bysocket.com 「公众号:泥瓦匠BYSocket 」欢迎关注和转载,保留摘要,谢谢! ES 基础 ES 集群 ES 集群上业务优化 一、ES 基础 ES 的安装下载,网上一大片,我这...

泥瓦匠BYSocket
44分钟前
6
0
input accept属性限制文件上传格式

上传文件的类型;具体做法如下所示: 注意:accept属性可以限制上传格式,其有兼容性如下 《1》上传.csv格式的 <input text="file" accept=".csv" /> 《2》上传.xls格式 <input text="file"......

Jack088
51分钟前
2
0
使用scp命令在多个Linux系统间进行文件复制

一,什么是scp scp是linux系统下基于ssh登陆进行安全的远程文件拷贝命令。scp命令可以在linux服务器之间复制文件和目录.scp使用ssh安全协议传输数据,具有和ssh一样的验证机制,从而安全的远...

老孟的Linux私房菜
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部