mysql操作
mysql操作
Scofieldxs 发表于3年前
mysql操作
  • 发表于 3年前
  • 阅读 52
  • 收藏 0
  • 点赞 0
  • 评论 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 = '表名';
  • 打赏
  • 点赞
  • 收藏
  • 分享
共有 人打赏支持
粉丝 3
博文 24
码字总数 26524
×
Scofieldxs
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: