基础的mysql语句+将mysql做成服务

SELECT now();    //执行mysql函数用select

select DATE_ADD(FROM_UNIXTIME(create_time/1000,'%Y-%m-%d %h:%i:%s'),INTERVAL 0 HOUR),`hash`,order_no  from tb_transaction where create_time>=UNIX_TIMESTAMP('2018-05-08 00:00:00')*1000 order by id desc;

SELECT SUM(a.amount) as '金额',COUNT(1) as "数量",b.ch_name AS '币种类型' from tb_transaction a  JOIN tb_coin b  on b.type = a.coin  WHERE  a.state = 1 AND a.type = 4 GROUP BY a.coin;

 

SELECT coin_id,task_status,COUNT(1) FROM tb_address WHERE  is_use = 1  GROUP BY coin_id,task_status;

 

SELECT coin_id,count(t1.id),count(t2.id) from tb_address t1 LEFT join tb_address_keys t2 ON t1.id = t2.ta_id GROUP BY t1.coin_id 

 

SELECT t.address,b.private_key from tb_address t JOIN tb_address_keys b ON t.id = b.ta_id WHERE t.address in ("1NdnyEJZBx9weQNuQ8skNUdbSMeqADmJ1o","151EbbZBuiSdfnh7rXWAirmg3wKUd8BGPc","182rHAnUQP9tP9P73udYZhstUGnjVnM4D6","1Bm5Zyszog5kea77HqfFaX6JZmpKLgn9ix","36jMV1xrCHMXAV5Rmx5tUNFUdYErRCJ9vS","3LvSUVC2TmB9ByaWGS2wM7C6cDB4wMYUuB","3DCg3acYLMYBAHbqe1bE3H8GzsxxRSnX77"); 

 

 

  UNIX_TIMESTAMP()*1000   //当前时间戳

 

SELECT id FROM tb_push_info WHERE `hash` NOT in(SELECT `hash` FROM tb_hash)  OR to_address NOT in(SELECT address from tb_address)    //查询

 

select * from tb_address_keys where ta_id in ( select ta_id from tb_address_keys group by ta_id having count(*)>=2 )

 select sum(task_status='0') as '未处理',
sum(task_status='1') as '已处理',
sum(task_status='2') as  '处理中'
from tb_address  

 

select distinct(coin_id) from tb_address where status = 2;

 

 SELECT count(hash) FROM tb_transaction a
WHERE a.from_address NOT in(SELECT b.address from tb_address b)
and a.coin='00300000' 

 

UPDATE tb_coin set abi_address = LOWER(abi_address);  // 自己更新自己
UPDATE tb_coin set collect_address = LOWER(collect_address);

 

select FROM_UNIXTIME(a.create_time/1000) as '创建时间', 
FROM_UNIXTIME(a.update_time/1000)  as '更新时间'
from tb_address a
where a.address='aaaaaaaa'

SELECT c.ch_name,
COUNT(t.coin_id) 总,
SUM(CASE WHEN t.is_use=1 THEN 1 ELSE 0 END) AS 已用,
SUM(CASE WHEN t.is_use=0 THEN 1 ELSE 0 END) AS 未用,
SUM(CASE WHEN t.is_use=0 AND t.is_activate=0  THEN 1 ELSE 0 END) AS 未用未激活,
SUM(CASE WHEN t.is_use=0 AND t.is_activate=1  THEN 1 ELSE 0 END) AS 未用已激活

FROM 
tb_address t
LEFT JOIN tb_coin c ON c.id = t.coin_id

GROUP BY t.coin_id

 

SELECT 
a.id as id,
a.user_id as 用户id,
a.role_id as 角色id,
b.permis_id as 权限id ,
c.url as 菜单
from sys_user_role a
LEFT JOIN sys_role_permis b ON a.role_id = b.role_id 
LEFT JOIN sys_permis c on b.permis_id = c.id WHERE a.user_id=2;

 

 mysql的数据类型--基础

int            范围小     整数

bigint      范围大

float       float(m,n)  #m为几位数，n为有效小数位

data       yyyy-mm-dd

time       hh:mm:ss

datetime  yyyy-mm-dd hh:mm:ss

char       范围小

varchar 范围大

timestamp       YYYY-MM-DD HH:MM:SS   1970-01-01 00:00:01 ~ 2038（相比datetime范围小但可以自动写）

 

例子：

create table t8 (
  `id1` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `id2` datetime default NULL
);

 

ALTER TABLE `login` ADD PRIMARY KEY (`uid`);

ALTER TABLE `login` MODIFY `uid` int(11) NOT NULL AUTO_INCREMENT;  //设置主键和自增

 

//将mysql做成服务

C:\xampp\mysql\bin> mysqld --install mysqld

                                      服务名字 端口号默认3306

##net start  mysql  记得要用管理员打开cmd

 

0.在之前要用户权限

cmd  -->mysql -uroot

1.创建一个数据库

create database database_namecharset=utf8;

设置库的时候要设置库的编码格式

2.删除一个数据库

drop database database_name;

3.选择一个数据库

use database_name;

4.创建数据表

create table table_name (name type);

例子：

crate table std(

useId int,

useName VARCHAR(64)

)

5.查看有哪些数据库

show databases;

6.查看数据库中有哪些表

show tables;

7.插入数据

insert into table_name values('value1','value2')

8.查看表里面的所有数据

select * from mysql.user   在不指定数据库的情况下用 .

select * from table_name 

select * from table_name where id=1

select name from table_name where id=1

select field1,field2 from table1,table2

select * from table_name order by  ID  asd升序/desc降序  //这个就会显示升序或降序的查询结果了

select * from table_name limit m,n       //限制查询个数m为起始位置m从0 开始   n为个数

查询当前这周的数据 
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now()); 

查询上周的数据 
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1; 

查询当前月份的数据 
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m') 

查询距离当前现在6个月的数据 
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now(); 

查询上个月的数据 
select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m') 

select * from `user` where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ; 

select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now()) 
 

9.修改表数据

update table_name set name="..."

update table_name set name="..." where id=1 [and pwd=.....] ;

10.where的用法

where id=".." and name="...."

where id=".." or name="..."

where id=".." and name="..." or pwd="...."

where sn is null    //查询默认值是null的数据用 is

注意and优先级比较高

11 删除数据

delete from table_name [ where id=1 ]

 

12 sql 设置默认值以及使用场合

     create table table_name(

   id int primary key auto_increment,

  complete int not null default 1

);

 insert into table_name(id) values(null);

只有这样complete 才出来默认值1

   insert into table_name(id,complete) values(null,null);//会报错 complete 不能为null