mysql知识更新

windows安装

..自己去看（后面又碰到问题的解决方法）
https://www.cnblogs.com/winton-nfs/p/11524007.html

授权不同：5.7与8.0

GRANT ALL PRIVILEGES ON *.* TO  g19@"%" IDENTIFIED BY "123456";
5.7直接创建用户并授权会出现警告提示，但不会报错
8.0会报错
8.0写法
drop user admin@'localhost';删除用户
CREATE USER 'tom'@'localhost' IDENTIFIED BY '123123'; 先创建用户
GRANT ALL PRIVILEGES ON *.* TO 'tom'@'localhost' ;再授权
ALTER USER 'test'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '新密码';修改用户密码。

打开命令窗口

mysql -u root -p -P
大写的P代表端口号，小写的p代表密码

C:\Users\Administrator>mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 633
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Database changed
mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| tom              | localhost |
| wms              | localhost |
| wmsRoot          | localhost |
+------------------+-----------+
7 rows in set (0.00 sec)
//创建新的用户gzc密码gzc
mysql> create user 'gzc'@'localhost' identified by 'gzc';
Query OK, 0 rows affected (0.04 sec)
//修改用户密码
mysql> alter user 'wmsRoot'@'localhost' identified with mysql_native_password by 'wmsroot';
Query OK, 0 rows affected (0.02 sec)
//删除用户
mysql> drop user admin@'localhost';

mysql基础使用

1.show databases；
use 某个数据库database；
show tables；
show columns from 某张表table；查看表结构：

2.空值检查：where parking_no is null；where parking_no is not  null；

3.in操作符比or操作符更快

4.and比or的优先级高

5.连接函数concat("","","".....)
concat(str1,str2,str3,.......)

6.文本处理函数
left()   返回串左边的字符
length()   返回串的长度
locate()   找出串的一个子串
lower()   将串转换为小写
ltrim()   去掉串左边的空格
right()   返回串右边的字符
rtrim()   去掉串右边的空格
soundex()   返回串的soundex值
substring()   返回子串的字符
upper()   将串转换为大写

not 否定跟在它后面的条件，对IN和BETWEEN,EXISTS等取反；

like %a%，a_b;
%：所有字符包括空
_：一个字符
不要用太多like语句，对性能影响较大；

正则：regexp “正则表达式A”
A：1000；匹配含有1000字符串的；
A：.000；匹配含有000字符串的；.表示任意一个字符；
A：1000|2000；匹配含有1000或者2000的；
A：[12]ton ；类似1ton|2ton   
A：[1-8];类似[12345678]
A：\\.;匹配特殊字符"."
^开始 $结尾
A：^[0-9\\.]小数开始的
--
NOT LIKE ** AND NOT LIKE ** 可以下面的写法
SELECT * FROM table WHERE zongbu NOT REGEXP'北京|上海|深圳|天津|香港|沈阳';
--


分组：group by  前面一定是聚合
select a,count(*),min(b),max(c),avg(d),sum(e) AS AB from table group by a having min(b)>12 order by AB;
where在分组前过滤，having分组后过滤

union：多条select语句中加union，条件是select必须包含相同的列、表达式或者聚集函数。
union all不删除重复项

存储过程：call set ifelse while do @@n @n n  in out  hanshu(inout a)
游标：
触发器：
事务管理：transaction，rollback，commit，savepoint

存储过程加事件组成定时任务

eg：
USE gzc;
SHOW TABLES;
---------存储过程1
DELIMITER $
CREATE PROCEDURE pro()
BEGIN
    INSERT  INTO gzc.`test` VALUES("time",NOW());
END $
---------存储过程2
DELIMITER $
CREATE PROCEDURE pro_TEST()
BEGIN
    UPDATE gzc.`test` SET test.`value`=NOW() WHERE test.key="name";
END $
------调用存储过程
CALL pro();

------创建事件，5秒调用一次
CREATE EVENT IF NOT EXISTS test_event1
    ON SCHEDULE EVERY 5 SECOND
    ON COMPLETION PRESERVE
DO CALL pro_TEST();
------创建事件，5秒调用一次
CREATE EVENT IF NOT EXISTS test_event2
    ON SCHEDULE EVERY 5 SECOND
    ON COMPLETION PRESERVE
DO CALL pro();
---------查询事件
SELECT * FROM information_schema.events;  
---------删除事件
DROP EVENT test_event1;
DROP EVENT test_event2;
---------开启事件
ALTER EVENT test_event1 ON COMPLETION PRESERVE ENABLE;
ALTER EVENT test_event2 ON COMPLETION PRESERVE ENABLE;
---------关闭事件
ALTER EVENT test_event1 ON COMPLETION PRESERVE DISABLE;
ALTER EVENT test_event2 ON COMPLETION PRESERVE DISABLE;
-------
SELECT * FROM gzc.`test`；
SHOW VARIABLES LIKE '%sche%'; -- 查看定时器状态
SET GLOBAL test_event1= 1;  -- 启动定时器
SET GLOBAL test_event1= 0;  -- 停止定时器

eg:创建新增分区存储过程

DELIMITER $
CREATE DEFINER=`root`@`localhost` PROCEDURE `add_partition_test`()       localhost----》%
    READS SQL DATA
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
      START TRANSACTION;
  
      SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
      WHERE table_name='test' ORDER BY partition_ordinal_position DESC LIMIT 1;
      SET @Max_date= DATE_FORMAT((DATE_ADD(@P12_Name, INTERVAL 20 MINUTE)),"%Y%m%d%H%i%S");
      SET @s1=CONCAT('ALTER TABLE test ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (''',@Max_date,'''))');
     
      SELECT @s1;
      PREPARE stmt2 FROM @s1;
      EXECUTE stmt2;
      DEALLOCATE PREPARE stmt2;
      COMMIT ;
END $

DELIMITER $
CREATE DEFINER=`root`@`%` PROCEDURE `add_partition_tomor01`()
    READS SQL DATA
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
      START TRANSACTION;
  
      SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
      WHERE table_name='tomor01' ORDER BY partition_ordinal_position DESC LIMIT 1;
       SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;
       SET @Max_date_add= DATE(DATE_ADD(@P12_Name+0, INTERVAL 2 DAY))+0;
       SET @Max_date_format= DATE_FORMAT(@Max_date_add,"%Y-%m-%d");
      SET @s1=CONCAT('ALTER TABLE tomor01 ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (''',@Max_date_format,' 00:00:00''))');
     
      SELECT @s1;
      PREPARE stmt2 FROM @s1;
      EXECUTE stmt2;
      DEALLOCATE PREPARE stmt2;
      COMMIT ;
END $

DELIMITER $
CREATE DEFINER=`root`@`%` PROCEDURE `add_partition_tomor01`()
    READS SQL DATA
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
      START TRANSACTION;
  
      SELECT REPLACE(partition_name,'p','') INTO @P12_Name FROM INFORMATION_SCHEMA.PARTITIONS
      WHERE table_name='tomor01' ORDER BY partition_ordinal_position DESC LIMIT 1;
       SET @Max_date= DATE(DATE_ADD(@P12_Name+0, INTERVAL 1 DAY))+0;
      SET @s1=CONCAT('ALTER TABLE tomor01 ADD PARTITION (PARTITION p',@Max_date,' VALUES LESS THAN (''',@Max_date+1,'000000''))');
     
      SELECT @s1;
      PREPARE stmt2 FROM @s1;
      EXECUTE stmt2;
      DEALLOCATE PREPARE stmt2;
      COMMIT ;
END $

eg：事件

CREATE DEFINER=`root`@`%` EVENT `add_event_tomor01` ON SCHEDULE EVERY 1 DAY STARTS '2019-07-03 23:45:00' ON COMPLETION PRESERVE ENABLE DO CALL add_partition_tomor01()
CREATE DEFINER=`root`@`localhost` EVENT `add_event_test` ON SCHEDULE EVERY 20 MINUTE STARTS '2019-07-02 13:20:00' ON COMPLETION PRESERVE ENABLE DO CALL add_partition_test ()

事件：

CREATE EVENT [IF NOT EXISTS]  ‘事件名’

ON SCHEDULE schedule

[ON COMPLETION [NOT] PRESERVE]

[ENABLE | DISABLE]

[COMMENT 'comment']

DO sql_statement

ON SCHEDULE 计划任务，有两种设定计划任务的方式：

1. AT 时间戳，用来完成单次的计划任务。
2. EVERY 时间（单位）的数量时间单位[STARTS 时间戳] [ENDS时间戳]，用来完成重复的计划任务。
   在两种计划任务中，时间戳可以是任意的TIMESTAMP 和DATETIME 数据类型，时间戳需要大于当前时间。
   在重复的计划任务中，时间（单位）的数量可以是任意非空（Not Null）的整数式，可以选 1 second，3 minute，5 hour，9 day，1month，1 quarter（季度），1 year ,分别对应多少秒、分钟、小时、天、月、季度、年
   重复计划如：

a、从2013年12月18号0点开始，每天运行一次
 ON SCHEDULE EVERY 1 DAY STARTS  '2013-12-18 00:00:00'
b、从现在开始每隔九天定时执行
ON SCHEDULEEVERY9 DAY STARTS NOW() ；
c、每个月的一号凌晨1 点执行
on schedule every1 monthstartsdate_add(date_add(date_sub(curdate(),interval day(curdate())-1day),interval 1 month),interval 1 hour);
D、每个季度一号的凌晨1点执行
on schedule every1 quarterstartsdate_add(date_add(date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)),interval1 quarter),interval 1 hour);
F、每年1月1号凌晨1点执行
on schedule every1 yearstartsdate_add(date_add(date(concat(year(curdate()),'-',elt(quarter(curdate()),1,4,7,10),'-',1)),interval1 quarter),interval 1 hour);

触发器（日志）：

-- 触发器
CREATE TRIGGER tri_test AFTER INSERT ON test FOR EACH ROW
    INSERT INTO test_log(input_time,remark) VALUES(NOW(),"插入一条数据了！")
-- 触发器结果    
SELECT * FROM test_log;
-- 触发器结果清空    
TRUNCATE TABLE test_log;

锁

show processlist;展示所有活动进程；
SHOW FULL PROCESSLIST;  -- 所有的连接数（事务+等待+空的连接）
SELECT * FROM information_schema.innodb_trx;    -- 所有在执行中的进程数
KILL ;

mysql语句优化：

1. 能用inner join连接尽量使用inner join连接
SELECT A.id,A.name,B.id,B.name FROM A,B WHERE A.id = B.id;
SELECT A.id,A.name,B.id,B.name FROM A INNER JOIN ON A.id =B.id;

2.子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。
Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);
Select* from A inner join B using(uuid) where b.uuid>=3000;

3.执行过程会先执行ON 后面先过滤掉B表的一些行数。然而WHERE是后再过滤他们两个连接产生的记录。
不过在这里提醒一下大家：ON后面的条件只能过滤出B表的条数，但是连接返回的记录的行数还是A表的行数是一样

4.使用JOIN时候，应该用小的结果驱动打的结果（left join 左边表结果尽量小，如果有条件应该放到左边先处理，right join同理反向），同事尽量把牵涉到多表联合的查询拆分多个query(多个表查询效率低，容易锁表和阻塞)。如：
Select * from A left join B A.id=B.ref_id where B.ref_id>10;
可以优化为：select * from (select * from A wehre id >10) T1 left join B onT1.id=B.ref_id;

5.建立索引,加快查询性能.
A．在建立复合索引的时候，在where条件中用到的字段在复合索引中，则最好把这个字段放在复合索引的最左端，这样才能使用索引，才能提高查询。
B．保证连接的索引是相同的类型，意思就是A表和B表相关联的字段，必须是同类型的。这些类型都建立了索引，这样才能两个表都能使用索引，如果类型不一样，至少有一个表使用不了索引。
C．索引，不仅仅是主键和唯一键，也可以是其他的任何列。在使用like其中一个有索引的字段列的时候。
如： select *from A name like ‘xxx%’;
这个sql会使用name的索引（前提name建立了索引）；而下面的语句就使用不了索引
Select * from A name like ‘%xxx’;
因为‘%’代表任何字符，%xxx不知道怎么去索引的，所以使用不了索引。
D.复合索引
比如有一条语句这样的：select* from users where area =’beijing’ and age=22;
如果我们是在area和age上分别创建索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效 率，但是如果area，age两列上创建复合索引的话将带来更高的效率。如果我们创建了（area,age,salary）的复合索引，那么其实相当于创 建了（area,age,salary）,(area,age),(area)三个索引，这样称为最佳左前缀特性。因此我们在创建复合索引的应该将最常用 作限制条件的列放在最左边，依次递减。
E.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中（除非是唯一值的域，可以存在一个NULL），复合索引中只要有一列含有NULL值，那么这一列对于此复合索引是无效的。所以我们在数据库设计时不要让字段的默认值为NULL.
F.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在钱10个或者20字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
G．排序的索引问题
Mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6.limit千万级分页的时候优化。
Select * from A order by id limit10000000,10;
Select * from A where id>=(Select idfrom a limit 10000000,1) limit 10;
Select * from A where id between 10000000  and 10000010;

7..尽量避免Select * 命令
8. 尽量不要使用BY RAND()命令
9. 利用limit 1取得唯一行
10. 尽量少排序
11.尽量少OR
12. 尽量用union all 代替union
13. 避免类型转换

14.不要在列上进行运算
select * fromusers where YEAR(adddate)<2007;
Select * from users where adddate<’2007-01-01’;

15. 尽量不要使用NOT IN和<>操作
A. NOT IN和<>操作都不会使用索引，而是将会进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可以使用id>3 or id <3;如果NOT EXISTS是子查询，还可以尽量转化为外连接或者等值连接，要看具体sql的业务逻辑。
B．把NOT IN转化为LEFT JOIN如：
SELECT * FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo );
优化：
SELECT * FROM customerinfo LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo. CustomerID WHERE salesinfo.CustomerID IS NULL;

16.使用批量插入节省交互（最好是使用存储过程）
A. 尽量使用insert intousers(username,password) values(‘test1’,’pass1’), (‘test2’,’pass2’), (‘test3’,’pass3’);
17.锁定表
A. 尽管事务是维护数据库完整性的一个非常好的方法,但却因为它的独占性,有时会影响数据库的性能,尤其是在很多的应用系统中.由于事务执行的过程中,数据库 将会被锁定,因此其他的用户请求只能暂时等待直到该事务结算.如果一个数据库系统只有少数几个用户来使用,事务造成的影响不会成为一个太大问题;但假设有 成千上万的用户同时访问一个数据库系统,例如访问一个电子商务网站,就会产生比较严重的响应延迟.其实有些情况下我们可以通过锁定表的方法来获得更好的性 能.如:
LOCK TABLE inventory write
Select quanity from inventory whereitem=’book’;
…
Update inventory set quantity=11 whereitem=’book’;
UNLOCK TABLES;
这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到列表中。包含有write关键字的LOCK TABLE语句可以保证在UNLOCK TABLES命令被执行之前，不会有其他的访问来对inventory进行插入，更新或者删除的操作。
18.对多表关联的查询，建立视图
A．对多表的关联可能会有性能上的问题，我们可以对多表建立视图，这样操作简单话，增加数据安全性，通过视图，用户只能查询和修改指定的数据。且提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响。

19.数据库分区处理：
 ----------------------------------------------------------------------
CREATE TABLE `test` (
  `key` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `key_value` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`key`,`key_value`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(key_value)
   (
    PARTITION p201907021130 VALUES LESS THAN ('20190702113000'),    
    PARTITION p201907021240 VALUES LESS THAN ('20190702124000'),
    PARTITION p201907021250 VALUES LESS THAN ('20190702125000'),
    PARTITION p201907021300 VALUES LESS THAN ('20190702130000')     
)
-----------------------------------------------------------------------------------------------
ALTER TABLE test ADD PARTITION (PARTITION p20190702134000 VALUES LESS THAN ('20190702134000'));
ALTER TABLE test DROP PARTITION  p20190702134000;
----------------------------------------------------------------------------------------------
SELECT
  partition_name part,
  partition_expression expr,
  partition_description descr,
  table_rows
FROM
  INFORMATION_SCHEMA.partitions
WHERE
  TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME='test'
---------------------------------------------------------------------------

函数：

日期和时间处理函数

SELECT
YEAR('20200512123000'),
MONTH('20200512123000'),
DAY('20200512123000'),
HOUR('20200512123000'),
MINUTE('20200512123000'),
SECOND('20200512123000')

SELECT 
ADDDATE('20200512',INTERVAL 2 DAY),
ADDTIME('123000',2000),
CURDATE(),
DATE('20200512123000'),
DATEDIFF('20200512123000','20200512123000'),
DATE_ADD('20200512123000',INTERVAL 2 DAY),
DATE_FORMAT('20200512123000','%Y-%m-%d %H:%m:%s'),
DAYOFWEEK('20200512123000')

聚集函数

SELECT COUNT(*) AS countNumber,
MIN(ID) AS idmin,
MAX(ID) AS idmax,
AVG(ID) AS idavg
FROM employer;

建表时注意事项

主键-----id
编号-----no
名称-----name
信息录入人员------inputMan
信息录入时间------inputTime
信息修改人员------updateMan
信息修改时间------updateTime
其他属性空出至少3个拓展属性

jdbc链接时注意

com.mysql.jdbc.Driver 是 mysql-connector-java 5中的，
com.mysql.cj.jdbc.Driver 是 mysql-connector-java 6中的
1、JDBC连接Mysql5 com.mysql.jdbc.Driver:

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=

2、JDBC连接Mysql6 com.mysql.cj.jdbc.Driver， 需要指定时区serverTimezone:

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=

在设定时区的时候，如果设定serverTimezone=UTC，会比中国时间早8个小时，如果在中国，可以选择Asia/Shanghai或者Asia/Hongkong，例如：

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?serverTimezone=Shanghai&?useUnicode=true&characterEncoding=utf8&useSSL=falseusername=root
password=

mysql利用group_concat()合并多行数据到一行

利用group_concat()方法，参数为需要合并的字段，合并的字段分隔符默认为逗号，可通过参数separator指定，该方法往往配合group by 一起使用。

select a.*,group_concat(b.name separator '-') as name from a left join b on a.id=b.id group by a.id;

SELECT ms01.street_no,GROUP_CONCAT(ms01.monitor_name) AS monitor_name
FROM ipms.tomms01 ms01 WHERE ms01.street_no=#streetNo#

mysql replace函数对字段进行字符替换

比如你要将表 tb1里面的 f1字段的abc替换为def
UPDATE tb1 SET f1=REPLACE(f1, ‘abc’, ‘def’);

UPDATE students SET s_id = REPLACE(s_id, "000","0001000") WHERE s_id LIKE "%000%"

update cy_ct_game_activity set title=REPLACE(title,'B游汇','异酷游') where title like "%B游汇%"

update cy_ct_game_activity set content=REPLACE(content,'B游汇','异酷游') where content like "%B游汇%"

添加序号

select  (@i:=@i+1)  i,a.url from  base_api_resources a  ,(select   @i:=0)  t2 order by a.id desc limit 10;

保存案例

-- 除强制下单的所有打开地磁最近一条进出车记录输入时间
SELECT
  fm.`parking_no`,
  CONCAT("'", MAX(pm.`data_time`))
FROM
  ipms.trmfm01 fm
  LEFT JOIN ipms.trmpm02 pm
    ON (
      pm.`input_time` > "20190708000000"
      AND pm.`parking_id` = fm.`parking_id`
      AND pm.`record_source` <> "01"
    )
WHERE fm.`signal_switch` = "1"
GROUP BY fm.`parking_no`
ORDER BY MAX(pm.`data_time`)

-- 除强制下单的所有打开地磁没有最近一条进出车记录
  SELECT
    *
  FROM
    (SELECT
      fm.`parking_no`,
      CONCAT("'", MAX(pm.`data_time`)) AS "time1"
    FROM
      ipms.trmfm01 fm
      LEFT JOIN ipms.trmpm02 pm
        ON (
          pm.`input_time` > "20190708000000"
          AND pm.`parking_id` = fm.`parking_id`
          AND pm.`record_source` <> "01"
        )
    WHERE fm.`signal_switch` = "1"
    GROUP BY fm.`parking_no`) a
  WHERE a.time1 IS NULL

  -- 频繁进出车记录的地磁
    SELECT
      pm1.street_name,
      pm1.street_no,
      pm1.parking_no,
      pm.`facility_no`,
      MAX(pm.`input_time`),
      MAX(pm.`parking_dur`),
      COUNT(*)
    FROM
      ipms.trmpm02 pm
      LEFT JOIN ipms.trmpm01 pm1
        ON (pm.`parking_id` = pm1.parking_id)
    WHERE pm.`input_time` LIKE "201907%"
      AND pm.`parking_dur` < 3
    GROUP BY pm1.street_no,
      pm1.parking_no,
      pm.`facility_no`

SELECT SUM(om.`hasbeen_pay`) ,
SUM(om.`have_pay_money`),
(SUBSTR(om.`end_time`,7,2)) AS ti,
"0756_0858" AS time_ 
FROM ipms.`tomor01` om
WHERE SUBSTR(om.`end_time`,1,6)="201904" AND  
(SUBSTR(om.`end_time`,9,4)  BETWEEN "0756" AND "0858") AND 
SUBSTR(om.`street_no`,1,1)="Z" 
GROUP BY ti
查询每天某时间段内的应收与实收（静停or闸北）


SELECT SUM(om.`total_amount`) ,(
SUBSTR(om.`end_time`,7,2)) AS ti,
"1617" AS time_ ,
om.`street_no` 
FROM ipms.`tomor01` om
WHERE SUBSTR(om.`end_time`,1,6)="201904" AND  
(SUBSTR(om.`end_time`,9,4)  BETWEEN "1600" AND "1700") AND  
SUBSTR(om.`street_no`,1,1)="J" 
 GROUP BY om.`street_no`,ti


SELECT SUM(om.pay_money),
DATE_FORMAT(trade_time,'%d') AS ti,
"1617" AS time_ ,om.`street_no` 
FROM  ipms.`tomti01view` om
WHERE SUBSTR(om.`trade_time`,1,7)="2019-04"  AND 
(DATE_FORMAT(trade_time,'%H%i')  BETWEEN "1600" AND "1700") AND
 SUBSTR(om.`street_no`,1,1)="J"  
 GROUP BY om.`street_no`, ti

mysql数据类型

菜鸟驿站链接

语法

select * from emp;  #注释
#---------------------------
#----命令行连接MySql---------
 
#启动mysql服务器
net start mysql
 
#关闭 
net stop mysql
  
#进入
mysql -h 主机地址 -u 用户名 －p 用户密码
 
#退出
exitstatus;显示当前mysql的version的各种信息。
 
#---------------------------
#----MySql用户管理---------
 
#修改密码:首先在DOS 下进入mysql安装路径的bin目录下，然后键入以下命令:
mysqladmin -uroot -p123 password 456;
 
#增加用户
#格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by '密码'
/*
如，增加一个用户user1密码为password1，让其可以在本机上登录， 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql，然后键入以下命令：
grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";
如果希望该用户能够在任何机器上登陆mysql，则将localhost改为"%"。
如果你不想user1有密码，可以再打一个命令将密码去掉。
grant select,insert,update,delete on mydb.* to user1@localhost identified by "";
*/
 
grant all privileges on wpj1105.* to sunxiao@localhost identified by '123';   #all privileges 所有权限
 
#----------------------------
#-----MySql数据库操作基础-----
 
#显示数据库
show databases;
 
#判断是否存在数据库wpj1105,有的话先删除
drop database if exists wpj1105;
 
#创建数据库
create database wpj1105;
 
#删除数据库
drop database wpj1105;
 
#使用该数据库
use wpj1105;
 
#显示数据库中的表
show tables;
 
#先判断表是否存在,存在先删除
drop table if exists student;
 
#创建表
create table student(
id int auto_increment primary key,
name varchar(50),
sex varchar(20),
date varchar(50),
content varchar(100)
)default charset=utf8;
 
#删除表
drop table student;
 
#查看表的结构
describe student;  #可以简写为desc student;
 
#插入数据
insert into student values(null,'aa','男','1988-10-2','......');
insert into student values(null,'bb','女','1889-03-6','......');
insert into student values(null,'cc','男','1889-08-8','......');
insert into student values(null,'dd','女','1889-12-8','......');
insert into student values(null,'ee','女','1889-09-6','......');
insert into student values(null,'ff','null','1889-09-6','......');
#查询表中的数据
select * from student;
select id,name from student;
 
#修改某一条数据
update student set sex='男' where id=4;
 
#删除数据
delete from student where id=5;
 
# and 且
select * from student where date>'1988-1-2' and date<'1988-12-1';
 
# or 或
select * from student where date<'1988-11-2' or date>'1988-12-1';
  
#between
select * from student where date between '1988-1-2' and '1988-12-1';
 
#in 查询制定集合内的数据
select * from student where id in (1,3,5);
 
#排序 asc 升序  desc 降序
select * from student order by id asc;
 
#分组查询 #聚合函数
select max(id),name,sex from student group by sex;
 
select min(date) from student;
 
select avg(id) as '求平均' from student;
 
select count(*) from student;   #统计表中总数
 
select count(sex) from student;   #统计表中性别总数  若有一条数据中sex为空的话,就不予以统计~
 
select sum(id) from student;
 
#查询第i条以后j条的数据(不包括第i条)
select * from student limit 2,5;  #显示3-7条数据
 
#巩固练习
create table c(
 id int primary key auto_increment,
 name varchar(10) not null,
 sex varchar(50) ,  #DEFAULT '男' ,
 age int unsigned, #不能为负值(如为负值 则默认为0)
 sno int unique    #不可重复
);
 
drop table c;
desc c;
 
insert into c (id,name,sex,age,sno) values (null,'涛哥','男',68,1);
insert into c (id,name,sex,age,sno) values (null,'aa','男',68,2);
insert into c (id,name,sex,age,sno) values (null,'平平','男',35,3);
...
 
select * from c;
 
#修改数据
update c set age=66 where id=2;
update c set name='花花',age=21,sex='女' where id=2
delete from c where age=21;
 
#常用查询语句
select name,age ,id from c
select * from c where age>40 and age<60;  #and
select * from c where age<40 or age<60;  #or
select * from c where age between 40 and 60 #between
select * from c where age in (30,48,68,99);     #in 查询指定集合内的数据
select * from c order by age desc;      #order by （asc升序 des降序）
 
#分组查询
select name,max(age) from c group by sex;  #按性别分组查年龄最大值
#聚合函数
select min(age) from c;
select avg(age) as '平均年龄 ' from c;
select count(*) from c;  #统计表中数据总数
select sum(age) from c;
 
#修改表的名字
#格式:alter table tbl_name rename to new_name
alter table c rename to a;
  
#表结构修改
create table test
(
id int not null auto_increment primary key, #设定主键
name varchar(20) not null default 'NoName', #设定默认值
department_id int not null,
position_id int not null,
unique (department_id,position_id) #设定唯一值
);
 
#修改表的名字
#格式:alter table tbl_name rename to new_name
alter table test rename to test_rename;
 
#向表中增加一个字段(列)
#格式:alter table tablename add columnname type;/alter table tablename add(columnname type);
alter table test add  columnname varchar(20);
 
#修改表中某个字段的名字
alter table tablename change columnname newcolumnname type;  #修改一个表的字段名
alter table test change name uname varchar(50);
 
select * from test;
 
#表position 增加列test
alter table position add(test char(10));
#表position 修改列test
alter table position modify test char(20) not null;
#表position 修改列test 默认值
alter table position alter test set default 'system';
#表position 去掉test 默认值
alter table position alter test drop default;
#表position 去掉列test
alter table position drop column test;
#表depart_pos 删除主键
alter table depart_pos drop primary key;
#表depart_pos 增加主键
alter table depart_pos add primary key PK_depart_pos
(department_id,position_id);
 
#用文本方式将数据装入数据库表中（例如D:/mysql.txt）
load data local infile "D:/mysql.txt" into table MYTABLE;
 
#导入.sql文件命令（例如D:/mysql.sql）
source d:/mysql.sql;  #或者  /. d:/mysql.sql;