数据库指令大全

数据库指令大全

### DDL操作数据库及表:

打开数据库: mysql -uroot -proot
创建数据库:CREATE DATABASE db1;
删除数据库:drop database db2;
修改数据库编码:alter database db3 character set utf8;
查看所有的数据库:show databases;
退出数据库: exit

查看正在使用的数据库:select database();
使用数据库:use db4;

**添加表列:ALTER TABLE 表名 ADD 列名 类型;**
**删除列:ALTER TABLE 表名 DROP 列名;**
修改列名:ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
**创建表:create table student(id int,name varchar(20),birthday date);**
创建相同的结构表:create table student2 like student;
删除表:1.DROP  TABLE 表名;(不推荐使用)    2.TRUNCATE TABLE student; (删除表,并创建同样的空表)
修改表名:alter table 表名 rename to 新表名;
修改表编码:alter table student character set gbk;
查看表编码:show create table student;

### DML操作表数据:
##### **-- 添加数据:**
**insert into 表 (id,name,sex)  values (1,'周凌云','男');**
**insert into 表  values (1,'周凌云','男');     -- 简写字段值,不推荐**

##### **-- 修改数据:**
**update 表名 set 字段=值,字段2=值2 where 条件;**

##### -- 删除数据:
delete from 表 where 条件;

### DQL查询表数据:
##### -- 查询表
select * from 表名;
select 列名1,列名2 from 表名; -- 查询指定列数据

### 案例1:

-- 1.创建数据库，数据库名为company
CREATE DATABASE company;
-- 查询表
SELECT * FROM employee;
-- 2.创建员工表(employee),表中字段有：员工编号(id),员工姓名(name),员工性别(gender),
出身日期(birthday),工资(salary)
CREATE TABLE employee(id INT,NAME VARCHAR(20),gender VARCHAR(10),birthday 
DATE,salary DOUBLE(10,2));
-- 3.给employee表增加三条数据 
INSERT INTO employee VALUES(1,'张三','男','1996-10-24',5600),(2,'李四','男','1995-
11-24',3600),(3,'王五','女','1998-09-08',3400);
-- 4.将所有员工薪水修改为5000元。 
UPDATE employee SET salary=5000;
-- 5.将姓名为’zhangsan’’的员工薪水修改为3000元。
UPDATE employee SET salary=3000 WHERE NAME LIKE '张三';
-- 6 将姓名为’lisi’的员工薪水修改为4000元,性别改为female。
UPDATE employee SET salary=4000,gender='female' WHERE NAME LIKE '李四';
-- 7 将编号为3的员工薪水在原有基础上增加1000元。
UPDATE employee SET salary=(salary+1000) WHERE id=3;
-- 8 删除表中编号为1的记录
DELETE FROM employee WHERE id=1;
-- 9 删除表中所有记录。
TRUNCATE TABLE student;

### 案例2:

-**- 创建数据库**
CREATE DATABASE db1;

**-- 创建表**
CREATE TABLE student3(id INT,NAME VARCHAR(20),age INT,sex VARCHAR(5),address 
VARCHAR(100),math INT,english INT);

##### *添加表数据

INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES
(1,'马云',55,'男','杭州',66,78),(2,'马化腾',45,'男','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'女','北京',86,NULL),(6,'刘德华',57,'男','香港',99,45),
(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65),(9,'德玛西亚',18,'男','南京',56,65);

##### * 修改表数据

UPDATE student3 SET age=23,english=78 WHERE id=5;

##### * 删除单行数据

DELETE FROM student3 WHERE id=9;

##### * 删除表

TRUNCATE TABLE student;
DROP TABLE student;   -- 删除表,并创建同样的空表

##### *查询表及表数据

SELECT * FROM student3;

-- 查询表指定列数据
SELECT NAME,sex,age FROM student3;	

-- 计算成绩之和
SELECT NAME,math,english,math+english FROM student3;

-- 如果有成绩为空
SELECT NAME,math,english,math+ IFNULL(english,0) FROM student3;

##### * 起别名

SELECT *,(math+ IFNULL(english,0)) AS 总成绩 FROM student3;

工作sql语句:

#按照15分钟间隔分组,查询历史表中一个用户名下一台矿机的每15分钟的起始时间和终止时间,并且区间值,和15分钟的平均算力,和15分钟里最后记录的真实time的id
SELECT MIN(TIME) AS mintime,MAX(TIME) AS maxtime,
       FROM_UNIXTIME(UNIX_TIMESTAMP(TIME)-(UNIX_TIMESTAMP(TIME)%(15*60)),'%Y-%m-%d %H:%i:%s') AS startTime,
       SUM(difficulty)/((TIME_TO_SEC(MAX(TIME)) - TIME_TO_SEC(MIN(TIME)))/60) hashrate,
       MAX(id) AS lastId
FROM shares_history WHERE id BETWEEN 0 AND 20000 AND username='daa.96x197' 
GROUP BY FROM_UNIXTIME(UNIX_TIMESTAMP(TIME)-(UNIX_TIMESTAMP(TIME)%(15*60)),'%Y-%m-%d %H:%i:%s')
ORDER BY TIME;
        

#查询历史表中一个用户名下所有矿机的一天开始到结束的每个小时的平均算力和总算力,按照时间分组
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(TIME)-(UNIX_TIMESTAMP(TIME)%(60*60)),'%Y-%m-%d %H:%i:%s') AS startTime,SUM(difficulty)/60 AS avghashrate,SUM(difficulty) AS realhashrate
FROM shares_history WHERE TIME>'2019-04-06 00:00:00' AND TIME<='2019-04-07 00:00:00' AND username LIKE 'daa.%' 
GROUP BY FROM_UNIXTIME(UNIX_TIMESTAMP(TIME)-(UNIX_TIMESTAMP(TIME)%(60*60)),'%Y-%m-%d %H:%i:%s')
ORDER BY TIME;
       

#查询历史表中每5分钟一个节点的数据和每5分钟的算力总和,并按用户名和时间分组
SELECT id,username,SUM(difficulty) AS difficulty,FROM_UNIXTIME(UNIX_TIMESTAMP(TIME)-(UNIX_TIMESTAMP(TIME)%(5*60)),'%Y-%m-%d %H:%i:%s') AS TIME,our_result
FROM shares_history WHERE id <=20000 
GROUP BY username,FROM_UNIXTIME(UNIX_TIMESTAMP(TIME)-(UNIX_TIMESTAMP(TIME)%(5*60)),'%Y-%m-%d %H:%i:%s');

#查询历史表中一个用户名下一台矿机的一天开始到结束的每个小时的总算力(实时算力)和每个小时中的总记录数,并按时间和记录数分组
SELECT username,SUM(difficulty) difficulty,COUNT(1) AS COUNT,FROM_UNIXTIME(UNIX_TIMESTAMP(TIME)-(UNIX_TIMESTAMP(TIME)%(60*60)),'%Y-%m-%d %H:%i:%s') AS TIME
FROM shares_history WHERE TIME BETWEEN '2019-04-06 00:00:00' AND '2019-04-08 00:00:00'AND username='daa.96x197'
GROUP BY  FROM_UNIXTIME(UNIX_TIMESTAMP(TIME)-(UNIX_TIMESTAMP(TIME)%(60*60)),'%Y-%m-%d %H:%i:%s'),COUNT;

#删除数据库并且创建原库结构
TRUNCATE TABLE shares_temporary;

#查询历史表中一个用户名下所有矿机的一天开始到结束的所有数据
SELECT * FROM shares_history WHERE TIME BETWEEN '2019-04-07 00:00:00' AND '2019-04-08 00:00:00' AND username LIKE 'daa.%';