文档章节

mysql操作

Scofieldxs
 Scofieldxs
发布于 2015/08/17 13:58
字数 1535
阅读 52
收藏 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 = '表名';

© 著作权归作者所有

共有 人打赏支持
Scofieldxs
粉丝 3
博文 24
码字总数 26524
作品 0
海淀
MySQL 主从复制与读写分离概念及架构分析

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

摩西
2016/10/27
8
0
史上最简单的 MySQL 教程

温馨提示:本系列博文已经同步到 GitHub,如有需要的话,欢迎大家到「mysql-tutorial」进行和操作! 1 前言   数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,它产生于距...

qq_35246620
2017/04/26
0
0
MySQL之双向主从加keepalived高可用

最近在做MySQL数据库的双向主从,了解到keepalived能够自动判断并切换到可用数据库,自己试了一下,整理出文档来。 先声明一下环境 iptables开启3306端口或者关掉,关闭selinux MySQL-01:1...

sunyuansheng7
2015/04/28
0
0
python编写mysql类实现mysql的操作

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

icenycmh
06/29
0
0
MYSQL远程登录权限设置 ,可以让Navicat远程连接服务器的数据库

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

xiaocao13140
06/06
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

iOS开发用到的图片尺寸汇总

启动图 型号 竖屏 横屏 iPhone SE 640px × 1136px 1136px × 640px iPhone 6s 750px × 1334px 1334px × 750px iPhone 6s Plus 1242px × 2208px 2208px × 1242px iPhone 7 750px × 1334......

业界小白
17分钟前
0
0
浅谈redis

redis是一个开源,内存式的健值存储数据库,也被称为健值存储的字典服务器。健值类型有字符串,hash(哈希类型),set(集合),list(列表) 和有序集合 特征细节: 内存式:redis将健值存储在主...

拐美人
25分钟前
0
0
无限扩容,按需使用!ZStack推出基于阿里云NAS的文件存储服务

日前,ZStack发布2.6.0版本,正式宣布推出基于阿里云NAS的文件存储服务。得益于业界领先的阿里云分布式存储架构,融合NAS后的ZStack 2.6.0拥有高性能、高可靠、容量无限扩展、一键操作、按需...

ZStack社区版
27分钟前
1
0
崛起于Springboot2.X之Mongodb多数据源处理(35)

多数据源:4个mongodb库! 目录结构图: 1、添加pom依赖 <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-mongodb</artifactId>......

木九天
33分钟前
0
0
如何获取显示器的EDID信息

Q1: 为什么要写这篇文章? A1:在最近的工作中遇到了不少问题,其中很多都是和EDID相关的。可以说,作为一家以“显示”为生的企业,我们时时刻刻在与EDID打交道。EDID这东西很简单,但是如果...

DB_Terrill
33分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部