mysql的使用15

最新推荐文章于 2022-08-09 16:24:32 发布
最新推荐文章于 2022-08-09 16:24:32 发布
阅读量518 收藏
点赞数
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/u012016940/article/details/39338745
版权
mysql> #滑动订单问题
mysql> create table MonthlyOrders
    -> (
    -> ordermonth date primary key
    -> ordernum int unsigned
    -> );
ERROR 1046 (3D000): No database selected
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table MonthlyOrders
    -> (
    -> ordermonth date primary key
    -> ordernum int unsigned
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ordernum int unsigned
)' at line 4
mysql> create table MonthlyOrders
    -> (
    -> ordermonth date primary key
    -> ordernum int
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ordernum int
)' at line 4
mysql> create table MonthlyOrders
    -> (
    -> ordermonth date primary key,
    -> ordernum int unsigned
    -> );
Query OK, 0 rows affected (0.09 sec)

mysql> #奶奶的  都好没打
mysql>
mysql> insert into MonthlyOrders select '2010-02-01',23;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2010-03-01',26;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2010-04-01',24;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2010-05-01',27;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2010-06-01',26;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2010-07-01',32;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2010-08-01',34;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2010-09-01',30;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2010-10-01',31;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2010-11-01',32;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2010-12-01',33;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2011-01-01',31;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2011-02-01',34;
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2011-03-01',34;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2011-04-01',38;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2011-05-01',39;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2011-06-01',35;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2011-07-01',49;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2011-08-01',56;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2011-09-01',55;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2011-10-01',74;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2011-11-01',75;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into MonthlyOrders select '2011-12-01',14;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> 色了才同
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '色了才同' at line 1
mysql> select * from MonthlyOrders;
+------------+----------+
| ordermonth | ordernum |
+------------+----------+
| 2010-02-01 |       23 |
| 2010-03-01 |       26 |
| 2010-04-01 |       24 |
| 2010-05-01 |       27 |
| 2010-06-01 |       26 |
| 2010-07-01 |       32 |
| 2010-08-01 |       34 |
| 2010-09-01 |       30 |
| 2010-10-01 |       31 |
| 2010-11-01 |       32 |
| 2010-12-01 |       33 |
| 2011-01-01 |       31 |
| 2011-02-01 |       34 |
| 2011-03-01 |       34 |
| 2011-04-01 |       38 |
| 2011-05-01 |       39 |
| 2011-06-01 |       35 |
| 2011-07-01 |       49 |
| 2011-08-01 |       56 |
| 2011-09-01 |       55 |
| 2011-10-01 |       74 |
| 2011-11-01 |       75 |
| 2011-12-01 |       14 |
+------------+----------+
23 rows in set (0.00 sec)

mysql> #滑动订单问题是指为每个月返回上一年度(季度或月度)的华东订单数，即为每个月份N ，返回从月份N-11到月份N的订单总数
mysql>
mysql> select date_format(a.ordermonth, '%Y%m') as frommonth,
    -> date_format(b.ordermonth, '%Y%m') as tomonth,
    -> sum(c.ordernum) as orders
    -> from MonthlyOrders a
    -> inner join MonthlyOrders b
    -> on date_add(a.ordermonth, interval 11 month)=b.ordermonth
    -> inner join MonthlyOrders c
    -> on c.ordermonth between a.ordermonth and b.ordermonth
    -> group by a.ordermonth, b.ordermonth;
+-----------+---------+--------+
| frommonth | tomonth | orders |
+-----------+---------+--------+
| 201002    | 201101  |    349 |
| 201003    | 201102  |    360 |
| 201004    | 201103  |    368 |
| 201005    | 201104  |    382 |
| 201006    | 201105  |    394 |
| 201007    | 201106  |    403 |
| 201008    | 201107  |    420 |
| 201009    | 201108  |    442 |
| 201010    | 201109  |    467 |
| 201011    | 201110  |    510 |
| 201012    | 201111  |    553 |
| 201101    | 201112  |    534 |
+-----------+---------+--------+
12 rows in set (0.00 sec)

mysql>
mysql> #基于上种情况 我们还可以找出季度的情况
mysql>
mysql> select date_format(a.ordermonth, '%Y%m') as frommonth, date_format(b.ordermonth, '%Y%m') as tomonth, sum(c.ordernum) as orders from MonthlyOrders a inner join MonthlyOrders b on date_add(a.ordermonth, interval 2 month)=b.ordermonth and month(a.ordermonth)%3=1 inner join MonthlyOrders c on c.ordermonth between a.ordermonth and b.ordermonth group by a.ordermonth, b.ordermonth;
+-----------+---------+--------+
| frommonth | tomonth | orders |
+-----------+---------+--------+
| 201004    | 201006  |     77 |
| 201007    | 201009  |     96 |
| 201010    | 201012  |     96 |
| 201101    | 201103  |     99 |
| 201104    | 201106  |    112 |
| 201107    | 201109  |    160 |
| 201110    | 201112  |    163 |
+-----------+---------+--------+
7 rows in set (0.01 sec)

mysql> #连接算法
mysql> #Nested-Loops Join and Classic Hash Join and then future will be support hash join
mysql>
mysql> #首先介绍Simple Nested-Loops Join
mysql> #for each row r in R do
mysql> # for each row s in S do
mysql> #   if r and s satisfy the join condition
mysql> #        then output the tuple<r,s>
mysql> #其中R为外部表  S为内部表      算法效率O(Rn + Rn * Sn)
mysql> #算法这里就不深入了  比较晦涩难懂  嘿嘿  自己想看的可以看看
mysql>
mysql> #集合操作    一般是将这些操作视为垂直操作
mysql> #MYSQL数据库支持两种集合操作  union all  and union distinct
mysql>
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| MonthlyOrders  |
| Nums           |
| a              |
| animals        |
| charTest       |
| customers      |
| dept_manager   |
| emp            |
| employees      |
| g              |
| mintable       |
| new_emp        |
| orders         |
| sales          |
| sessions       |
| t              |
| test01         |
| timetest       |
| tt             |
| ttt            |
| updatetime     |
| x              |
| yeartest       |
| z              |
+----------------+
24 rows in set (0.00 sec)

mysql> drop table x;
Query OK, 0 rows affected (0.06 sec)

mysql> drop table y;
ERROR 1051 (42S02): Unknown table 'y'
mysql> create table x (a, char(1)) engine = innodb;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' char(1)) engine = innodb' at line 1
mysql> create table x (a, char(1)) engine=innodb;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' char(1)) engine=innodb' at line 1
mysql> create table x (a, char(1))engine=innodb;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' char(1))engine=innodb' at line 1
mysql>
mysql>
mysql> create table y(a char(1))engine=innodb;
Query OK, 0 rows affected (0.10 sec)

mysql> create table x(a char(1))engine=innodb;
Query OK, 0 rows affected (0.09 sec)

mysql> insert into x select 'a';
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into x select 'b';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into x select 'c';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into y select 'a';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into y select 'b';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select a as m from x
    -> union
    -> select a as n from y;
+------+
| m    |
+------+
| a    |
| b    |
| c    |
+------+
3 rows in set (0.00 sec)

mysql> insert into y select 'w';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into y select 'q';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select a as m from x union select a as n from y;
+------+
| m    |
+------+
| a    |
| b    |
| c    |
| w    |
| q    |
+------+
5 rows in set (0.00 sec)

mysql> #select查询的不同  
mysql> #只有最后一个select空调一应用into outfile，但是整个集合的操作将被输出到文件中
mysql> #不能在select语句中使用high_priority关键字
mysql>
mysql> select a from x union select a into outfile '/home/MySQL/record.txt' from y;
ERROR 1 (HY000): Can't create/write to file '/home/MySQL/record.txt' (Errcode: 13)
mysql> select a from x union select a into outfile '/home/MySQL/record.txt' from y;
ERROR 1086 (HY000): File '/home/MySQL/record.txt' already exists
mysql> select a from x union select a into outfile '/home/MySQL/record.txt' from y;
ERROR 1 (HY000): Can't create/write to file '/home/MySQL/record.txt' (Errcode: 13)
mysql> #有点问题。。。。。
mysql>
mysql>
mysql> #如果导入成功  可以使用下面的命令查看  system cat path
mysql>
mysql> (select a from x limit 2) union (select a from y);
+------+
| a    |
+------+
| a    |
| b    |
| w    |
| q    |
+------+
4 rows in set (0.00 sec)

mysql> exit
Jack_Lpz
关注
专栏目录
Navicat for MySQL 15安装及使用
qq_43760243的博客
04-03 6356
文章目录安装教程：使用方法：1.新建数据库连接2.打开连接3.查看数据库中的表4.双击表名可看到表中数据5.操作数据库6.导出数据库7.导入数据库 安装教程： 激活教程：这里 安装包及注册机：（64位是x64 32位是x86 注册机通用） 链接：https://pan.baidu.com/s/1v3ZRhYuYdDRS2YG0xq1KYg 提取码：85vh 使用方法： 1.新建数据库连接 左上角连接->mysql->输入连接名和mysql密码->确定 2.打开连接 右击新建的连接名-&g
Mysql Workbench使用教程
zs1342084776的博客
03-20 18万+
<1> MySQL Workbench MySQL Workbench 为数据库管理员、程序开发者和系统规划师提供可视化的Sql开发、数据库建模、以及数据库管理功能。 <2>.MySQL Workbench 的下载和安装 （1）安装最新MySql时，有是否安装MySql Workbench的选项，可选择安装。 （2）可以独立安装MySql Workbench...
mysql 15 命令_mysql常用命令
weixin_42411799的博客
02-07 156
1.查询库有哪些数据库 show databases;mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test ...
MySQL基本使用
十五十六
05-19 1047
第一章：MySQL基础架构 1.1 Server层 包括连接器，查询缓存，分析器，优化器，执行器等，涵盖了MySQL大多数核心服务功能，以及所有内置函数，所有跨存储引擎功能都在这一层实现，比如存储过程，视图，触发器等 1.1.1连接器 管理连接，权限验证 在用户连接成功后，连接器会到权限表查出拥有的权限，之后在这个连接中的权限判断，都依赖于此时读到的权限。 如果连接太长时间没动静会自动断开，由...
unsigned int - int情况
qq_45949701的博客
11-23 420
问题来源:PAT1094 问题错误表现: 无法正确跳出循环,即延长了循环次数,从而造成结果错误 //测试点4: //num是string类型的，而num.size()返回的是unsigned int类型的数， //当L < K时,数学上差值为负数，但是无符号的num.size() - K得到的是极大的正数，所以会继续循环造成错误 #include<iostream> #include<string> #include<cmath> using na...
MySql 安装与使用（非常详细）
余识-
05-11 9万+
文章目录前言一、安装MySql二、认识MySql数据库软件的文件结构三、MySql Shell的使用四、建立对MySql数据库的认知五、常见MySql指令1、增2、删3、改4、查六、指令实战七、VS连接MySql 前言 所谓数据库，不过是一个别人写好的存储数据的软件，并向我们这些使用者提供对数据的操作接口，如提供与语言无关性的命令语句 而对数据我们一般需要做哪些操作呢？不过增、删、改、查而已 所以不要将数据库想的太难了 一、安装MySql 点击这里前往官网下载 下载完成后直接双点击安装 等待一段时间后
MySQL使用简单教程
萌兔兔MMQ！！
08-09 1万+
本文通过演示如何使用MySQL客户机程序创建和使用一个简单的数据库，允许连接到MySQL服务器、运行查询和查看结果。mysql也可以在批处理模式下使用：预先将查询放在文件中，然后告诉mysql执行文件的内容。要查看mysql提供的选项列表，请使用–help选项调用它：本文假设mysql已安装在您的计算机上，并且可以连接到mysql服务器本文描述了建立和使用数据库的整个过程。如果您只对访问现有数据库感兴趣，可以跳过本文。因为本文本质上是教程，所以许多细节都必须省略。...
mysql安装教程【安装版】
热门推荐
CY的博客
02-21 38万+
mysql安装教程【安装版】
【MySQL基本使用】
木鱼的博客
06-15 1万+
MySQL基本使用
MySQL15_47360.zip
04-22
Navicat for MySQL 15是为MySQL量身打造的一款数据库管理软件，软件界面简洁美观，支持数据传输和导入导出等功能，可以用于...Navicat for MySQL 15采用树状结构，让你透过弹出菜单快捷及方便地使用数据库和它们的对象
MySQL和Navicat Premium 15
12-15
此为我个人使用的MySQL数据库以及Navicat，总的来说比较好用，在此处做一个备份同时，有需要的同志和粉丝也可以取用，免费
navicat15，可以连接MySQL、Oracle等主流数据库
02-20
navicat15，可以连接MySQL、Oracle等主流数据库 连接Oracle需要配置OCI
mysql使用10
Jack_Li
09-15 2009
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +--------
mysql的使用25
Jack_Li
09-17 1622
mysql> create table listPart(     -> a int,b int)engine=innodb     -> partition p0values in (1,3,5,7,9),     -> partition p0values in (0,2,4,6,8)); ERROR 1064 (42000): You have an error in your SQ
mysql的使用19
Jack_Li
09-17 921
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> #rollup mysql> #rollup
mysql使用07
Jack_Li
09-15 778
mysql> #昨天的函数使用发生了一点点错误哈 mysql> show tables; ERROR 1046 (3D000): No database selected mysql> use test; Reading table information for completion of table and column names You can turn off this fea
mysql的使用24
Jack_Li
09-17 597
mysql> #自适应哈希索引 mysql> #自适应哈西索引采用之前讨论的三列表的方式实现 mysql> #查看当前自适应哈西索引的使用情况 mysql> show engine innodb status; +--------+------+-------------------------------------------------------------------------
mysql的使用14
Jack_Li
09-16 584
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +--------
mysql使用08
Jack_Li
09-15 575
mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> #blob 数据和 Text数据 mysql
MySQL 数学函数使用指南
15. 三角函数：SIN(), ASIN(), COS(), ACOS(), TAN(), ATAN() 这些函数用于计算三角函数值。例如，SIN(0.5235987755982989) 将返回 0.5，ASIN(0.5) 将返回 0.5235987755982989。 示例：SELECT SIN(0....
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值