MySQL

最新推荐文章于 2022-10-08 08:00:00 发布

rongxiaojun

最新推荐文章于 2022-10-08 08:00:00 发布

阅读量1.9k

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/rongxiaojun1989/article/details/18990657

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

38.查看表信息

show create table tb;

该命令可查看表创建的完整信息，特别适合查看分区信息，十分方便

同理，查看存储历程：

show create procedure sp_name;

show create function sf_name;

37.分区

37.1 分区列

凡分区表达式中出现的列都必须被包含于该表的所有唯一键索引中，

即作为分区的列一定得是该表所有唯一性索引交集的子集。

注意：若表无唯一键和主键，则分区列可为任意列。

37.2 maxvalue对于范围列分区的限制

maxvalue可作为范围分区的极限值，表示比一切数值或字符串、时间值都大的一个值。

maxvalue限制：以范围列进行分区，对于各分区上限值列表中第一列，maxvalue最多只能出现一次，且必须位于最后一个分区。

除第一列以外的其它列无此限制，例如

37.3 列分区

列分区包括：范围列分区和列表列分区

形如：partition by range/list columns(col1, col2...coln)

参数类型：col1, col2...coln可以为int, datetime, varchar类型

限制：columns后只能接列名列表，不能是列的函数和表达式

举例：partition by range columns(year(col_datetime)), partition by list columns(concat(col1, col2))均非法

36.创建数据库

如果存在就删除

drop database if exists vmsserver;
create database vmsserver default charset utf8 collate utf8_general_ci;

如果不存在就创建

create database if not exists vmsserver default charset utf8 collate utf8_general_ci;

35.条件分支语句

表a

存储过程

结果一

结果二

34.条件选择语句

表a，sex字段类型为int

34.1 用于返回值

语句

结果

34.2 用于条件语句

语句

结果

33.字段成组查询，字段一体化，字段关联查询

tb_alarm为报警表，存储所有设备的报警；tb_device为我们公司的设备表，存储自己公司的所有设备信息

要求查询我们公司设备的所有报警信息

表tb_alarm

表tb_device

查询

32.

表a

表b

表a中在表b有对应项的返回(guid, true)，无对应项的返回(guid, false)

(select a.guid, 1 from a, b where a.guid = b.guid) union (select a.guid, 0 from a where a.guid not in (select b.guid from b));

结果

31. order by和limit

order by与limit通常配合使用：

order by对where筛选出的结果集进行排序，limit取出排好序的若干条。

例1，取4班成绩前10名的学生：

select * from table where classroom=4 order by Score limit 10;

例2，取1类货物中最便宜的4种：

select * from table where GoodsType=1 order by Price desc limit 4;

30. order by

order by 放在where后面表示对已取出结果进行排序。

例1，取4班学生，同时将其按学号升序排列：

select * from table where classroom=4 order by StudentID;

例2，取1类货物，同时将其按价格降序排列：

select * from table where GoodsType=1 order by Price desc;

多字段排序

select * from table orber by field1 desc, field2 asc, field3 desc...;

从table中选取若干记录，将结果集按字段一降序排，字段一相同的按字段二升序排，字段二相同的按字段三降序排......。

故优先级关系：field1 > field2 > field3 >.....，即order by中字段越靠前优先级越高。

例，tb_student_score为学生分数表，获取本次考试的名次排名。已知分数越高排名越靠前，分数相同年龄越小排名越靠前，年龄相同女生比男生靠前

表tb_student_score

查询

排序字段后无升降标识，则默认升序。即order by field1, field2 desc等同于order by field1 asc, field2 desc。

29. UNION

union：联合的意思，即把两次或多次查询结果合并起来。

要求：两次查询的列数必须一致

union会将相同的行合并，最终只保留一行。也可以这样理解，union会去掉重复的行。如果不想去掉重复的行，可以使用union all。

如：(select * from a) union (select * from b);

28.获取结果个数

select count(*) from
(
(select TbAlarm.Guid, TbAlarm.AlarmConfigGuid, TbAlarm.AlarmType, TbAlarm.AlarmTime, TbAlarm.DealTime, TbAlarm.DealPerson, TbAlarm.Note
from tb_vms_user as tUser, tb_vms_alarm_region as tRegion, TbVideoAlarmConfig as tVideoConfig, TbAlarm
where tUser.username="abc" and tUser.roleguid=tRegion.role_guid and tRegion.region_guid=tVideoConfig.regionId and tVideoConfig.guid=TbAlarm.AlarmConfigGuid)
union
(select TbAlarm.Guid, TbAlarm.AlarmConfigGuid, TbAlarm.AlarmType, TbAlarm.AlarmTime, TbAlarm.DealTime, TbAlarm.DealPerson, TbAlarm.Note
from tb_vms_user as tUser, tb_vms_alarm_region as tRegion, TbBoxIOAlarmConfig as tBoxConfig, TbAlarm
where tUser.username="abc" and tUser.roleguid=tRegion.role_guid and tRegion.region_guid=tBoxConfig.regionId and tBoxConfig.guid=TbAlarm.AlarmConfigGuid)
) as temp;

27.触发器

26.判断字段是否为空

取不为空的字段：select guid from tb_vms_device where DealTime is not null;

取为空的字段：select guid from tb_vms_device where DealTime is null;

DealTime字段类型为TimeStamp

25.mysql乱码

前言：

VS2010默认汉字编码GB2312、mysql被设置成UTF8，故通过程序插入数据库中的“崇祯”为乱码。

过程描述：

1.原因一直没有找到，不停更改mysql数据库编码、table编码。

2.程序设置断点，debug下汉字正常显示。故认为乱码出在mysql。

3.后经刘XX提醒，即将插入mysql、正常显示的汉字以十六进制打出，对应编码表，

结果：程序中正常显示的汉字编码为GB，结合mysql的UTF8，乱码不足为奇。

解决：

A.存mysql前不管汉字与否一律：首先默认编码转宽字符、最后宽字符转UTF8。

B.取mysql后不管汉字与否一律：首先UTF8转宽字符、最后宽字符转默认编码。

24.select笛卡尔积

select c.guid
from a, b, c
where a.roleguid=b.role_guid and b.region_guid=c.regionId;

select c.guid
from a, b, c, d
where a.roleguid=b.role_guid and b.region_guid=c.regionId;

原因：1.表d中有四条记录 2.where没有对d设筛选条件

导致：ConfigGuid1 ConfigGuid2 ConfigGuid3与d中四条记录笛卡尔积

23. C库API

http://dev.mysql.com/doc/refman/5.1/zh/apis.html#c-api-function-overview

22. UPDATE

更新表中的数据

UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值，例如：UPDATE Person SET FirstName = 'Fred', LastName = 'Rong' WHERE LastName = 'Wilson'

21.DELETE

删除表中的行

DELETE FROM 表名称 WHERE 列名称 = 值，例如：DELETE FROM Person WHERE LastName = 'Wilson'

20.存储过程

创建

delimiter //

create procedure SP_Get_PasswordByUser(hisname varchar(50), region varchar(30))

begin

select password from table where username = hisname and region_guid = region;

end

delimiter ;

删除

drop procedure SP_Get_PasswordByUser;

调用

call SP_Get_PasswordByUser("shibaizhe", 9);

注意：table中的region_guid类型varchar(30)，存储过程中的参数region类型也是varchar(30)，而实际参数为数字9：正常执行。

20.1 结果集叠加

create procedure SP_Get_VauleByKey(k varchar(30))

begin

select value from table1 where key = k;

select value from table2 where key = k;

end

结果集附加的方式还有很多种。

19. mysql结束符设定

默认结束符为分号“;”

delimiter // 设定//为结束符

delimiter ; 设定;为结束符

18.复合查询

已知device_guid = "dev"和channel_no = 9查region_guid

select region_guid from tb_vms_channel as a, tb_vms_region_channel as b where a.channel_guid = b.channel_guid and a.device_guid = "dev" and a.channel_no = 9;

1.删除字段

alter table mytable drop column age;

2.mysql_query函数

int mysql_query（MYSQL*，char*）成功返回0，失败返回非0。

3.添加字段

lter table mytable add age int;

4.创建数据库

create database mine；

5.建表

create table mytable(name varchar(20),who varchar(20));

6.如果表存在则删除

drop table if exists test_table;

7.Update语句：用于修改表中的数据，update mytable set temperature = 12.3, hour = 14 where device_guid = "123";

这里有个特务藏得很深，将上述语句改成 update mytable set temperature =12.3 and hour = 14 where..... 这个语句不会报错，但是行为诡异：直接执行，则阳奉阴违，do nothing;使用mysql connector 在程序语句中执行，则更行的字段值均为1。毒吧。

8.浮点数类型

mysql字段为float，程序存入该字段的浮点数会转化为整数。故想正确的保存浮点数，字段应设置为double。

浮点数存储，定义为double则小数点后位数过多，使用double(10,3)则数字总长十位小数点后三位。

9.时间类型

三种时间类型的字段：

date格式：2014-02-24

time格式：15:34:31

timestamp 格式：2014-02-24 15:34:31

datetime 格式：2014-02-24 15:34:31

9.1 NOW()函数

update mytable set date = NOW() where device_guid = "acd";NOW()自动读取当前日期时间。date是date类型，则acd的date字段赋值为2014-02-24；date是time类型，则赋值为15:34:31；date是timestamp或datetime类型，则赋值为2014-02-24 15:34:31。

9.2 时间类型可比较性

date、int类型的字段在筛选中是可以比较的：select hour, temperature, humidity from tb_device_humiture where

device_guid = "acd" and date >"2014.02.24" and date <="2014.02.25" and hour <=21;

tb_device_basic表中的register_time字段是datetime类型，为2014-05-16 13:57:26和2014-05-17 14:27:34格式。

统计2014年5月17日当天注册的设备个数：

select count(*) from tb_device_basic where register_time > '2014-05-17' and register_time < '2014-05-18';

9.3 时间类型处理方式类似于字符串

set date = "2014-02-25" 和 where date < "20140225"等价

10.修改字段类型

alter table tb_device_humiture modify column temperature double(4,2);

11.连接符

修改多个字段值用逗号：update mytable set temperature = 21.5, hour = 14 where.....;

选择多个字段值用逗号：select hour, temperature, humidity from.....;

筛选条件有多个用and：where device_guid = "acd" and age > 26;

12.把数据库中某一字段值全改为小写

update mytable set name = lower(name);

13.登陆mysql

mysql -uroot -p

14. executeUpdate函数

prep_stmt->executeUpdate()返回值是一个整数，指示受影响的行数（即更新计数）

15.limit

limit pos，length 返回第pos行开始共length行，pos从0计数

select * from table limit 5        //取前5行。
select * from table limit 4，10    //从第5行开始取10行
select * from table limit 4，-1    //从第5行开始取到最后

16. 改表名

rename table 老名 to 新名

17. mysql C++标准库

C++ Driver for MySQL (Connector/C++) 官网有下载，解压进入目录后，直接make和make install，在程序中引入头文件即可使用：

#include "mysql_connection.h"

#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>

准备工作：

try {
  sql::Driver *driver;
  sql::Connection *con;
  sql::Statement *stmt;
  sql::ResultSet *res;
  sql::PreparedStatement *pstmt;

  /* Create a connection */
  driver = get_driver_instance();
  con = driver->connect("tcp://127.0.0.1:3306", "root", "root");
  /* Connect to the MySQL test database */
  con->setSchema("test");

  stmt = con->createStatement();
  stmt->execute("DROP TABLE IF EXISTS test");
  stmt->execute("CREATE TABLE test(id INT)");
  delete stmt;

  pstmt = con->prepareStatement("INSERT INTO test(id) VALUES (?)");
  for (int i = 1; i <= 10; i++) {
    pstmt->setInt(1, i);
    pstmt->executeUpdate(); 
  }
  delete pstmt;

  pstmt = con->prepareStatement("SELECT id FROM test ORDER BY id ASC");
  res = pstmt->executeQuery();

  /* Fetch in reverse = descending order! */
  res->afterLast();
  while (res->previous())
    cout << "\t... MySQL counts: " << res->getInt("id") << endl;
  delete res;

  delete pstmt;
  delete con;

} catch (sql::SQLException &e) {
  cout << "ERR: " << e.what();
}

执行查询语句使用execute(),executeQuery() 和executeUpdate()都可以。通查select用executeQuery()，update和insert用execute()就行了。

官方文档地址：http://dev.mysql.com/doc/connector-cpp/en/connector-cpp-getting-started-examples.html

CentOS 6.4

Mysql下载地址：

http://dev.mysql.com/downloads/mysql/

MySQL-5.6.16-1.el6.x86_64.rpm-bundle.tar

解压后：

1.安装server：

rpm -ivh MySQL-server-5.6.16-1.el6.x86_64.rpm 安装MySQL服务器。

rpm文件是Red Hat公司开发的软件安装包，rpm可让Linux在安装软件包时免除许多复杂的手续。该命令在安装时常用的参数是 –ivh ,其中i表示将安装指定的rmp软件包，V表示安装时的详细信息，h表示在安装期间出现“#”符号来显示目前的安装过程。这个符号将持续到安装完成后才停止。

出现问题：

file /usr/share/mysql/charsets/README from install of MySQL-server-5.1.7-0.i386.rpm conflicts with file from package mysql-libs-5.1.61-4.el6.i686，报了与原先版本冲突的错误。

解决方法：

首先，执行rpm -qa|grep -i mysql命令，会显示已安装的Mysql版本,我的显示mysql-libs-5.1.61-4.el6.i68。

然后，强制删除：rpm -e --nodeps mysql-libs-5.1.61-4.el6.i686（因为有包依赖，所以要强制删除）

这时候再执行rpm -ivh MySQL-server-5.6.16-1.el6.x86_64.rpm安装即可安装成功。

我这里安装成功了，但是mysql服务无法启动，总是启动失败，后来百度了一下，是因为selinux的原因，只要将selinux关闭就OK了。
关闭selinux：vi /etc/selinux/config 将SELINUX=enforcing 改成SELINUX=disabled 然后重启。

现在执行service mysql start终于可以启动mysql服务了。

2.安装client：

rpm -ivh MySQL-client-5.6.16-1.el6.x86_64.rpm

现在可以操作mysql-server了

第一步登陆mysql，输入命令：mysql，就出现问题：

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

就是说登陆时密码不对，可是我没设置过密码，查了半天反正问题就是密码对不上，

find / -name '*mysql*' //找mysql配置文件，看看默认密码是多少

倒数第二个文件是mysql_secret，打开：

# The random password set for the root user at Sat Feb 8 15:16:58 2014 (local time): mcxSvXoK

密码：mcxSvXoK

重设密码：

mysqladmin -u root -p password 123 //控制台输入

Enter password:（mcxSvXoK）

例如你的 root用户现在没有密码，你希望的密码修改为abc，那么命令是：
mysqladmin -u root password abc

如果你的root现在有密码了，那么修改密码为123的命令是：
mysqladmin -u root -p password 123

注意，命令回车后会问你旧密码，输入旧密码之后命令完成，密码修改成功。

登陆mysql：

mysql -p

Enter password:（123）

3.安装devel

rpm -ivh MySQL-client-5.6.16-1.el6.x86_64.rpm

安装devel是为了引入头文件，随着devel的安装，头文件已经存在于/usr/include/mysql。

编写的mysql.cpp编译时总是报没有/mysql/mysql.h、C++操作mysql的动态库为libmysqlclient.so。（2014.2.12 17:33添加）

4.安装shared

安装shared是引入使用C++操作mysql的库。产生libmysqlclient.so，同时放入/usr/lib64。

小例子：

#include <iostream>
#include <mysql/mysql.h>
#include <string>
int main(){
        MYSQL mysql;
        mysql_init(&mysql);
        if(!mysql_real_connect(&mysql,"localhost","root","123","mine",3306,NULL,0)){
                std::cout<<"connect failed"<<std::endl;
                return 1;
        }
        std::string sql = "insert into mytable(name,who)values('rongxiaodong','bastard');";
        mysql_query(&mysql,sql.c_str()); //成功返回0，失败返回非0。
        
        sql = "update mytable set who='a little bastard' where name='rongxiaodong';";
        if(mysql_query(&mysql,sql.c_str())){
                std::cout<<"update failed"<<std::endl;
                return 1;
        }
        sql = "select name,who from mytable;";
        mysql_query(&mysql, sql.c_str());
        MYSQL_RES *result = NULL;
        MYSQL_FIELD *field = NULL;
        result = mysql_store_result(&mysql);
        int rowcount = mysql_num_rows(result);
        std::cout << rowcount << std::endl;
        int fieldcount = mysql_num_fields(result);
        std::cout << fieldcount << std::endl;
        for(int i = 0; i < fieldcount; i++)
        {
                field = mysql_fetch_field_direct(result,i);
                std::cout << field->name << "\t\t";
        }
        std::cout << std::endl;
        MYSQL_ROW row = NULL;
        row = mysql_fetch_row(result);
        while(NULL != row)
        {
                for(int i=0; i<fieldcount; i++)
                {
                        std::cout << row[i] << "\t\t";
                }
                std::cout << std::endl;
                row = mysql_fetch_row(result);
        }
mysql_close(&mysql);
        return 0;
}

在表中查询rongxiaojun是谁：

        sql = “select who from mytable where name=’rongxiaojun’;”;
        mysql_query(&mysql, sql.c_str());
        MYSQL_RES *result = mysql_store_result(&mysql);
        MYSQL_ROW row = mysql_fetch_row(result);
        std::cout<<row[0]<<std::endl;

表中添加年龄字段并查询rongxiaojun年龄：

        sql = "alter table mytable add age int;";
        if(mysql_query(&mysql, sql.c_str())){
                std::cout<<"add field failed"<<std::endl;
                return 1;
        }
        sql = "update mytable set age=26 where name='rongxiaojun';";
        if(mysql_query(&mysql, sql.c_str())){
                std::cout<<"set age field failed"<<std::endl;
                return 1;
        }
        sql = "select age from mytable where name='rongxiaojun';";
        if(mysql_query(&mysql, sql.c_str())){
                std::cout<<"select age field failed"<<std::endl;
                return 1;
        }
        result = mysql_store_result(&mysql);
        row = mysql_fetch_row(result);
        int age = atoi(row[0]);
        std::cout<<++age<<std::endl;

合并上述三条sql语句的执行：

MySQL 5.1支持在单个查询字符串中指定的多语句的执行。要想与给定的连接一起使用该功能，打开连接时，必须将标志参数中的CLIENT_MULTI_STATEMENTS选项指定给mysql_real_connect()。

也可以通过用mysql_set_server_option(MYSQL*，MYSQL_OPTION_MULTI_STATEMENTS_ON)，为已有的连接设置它，成功返回0，失败返回非0。在默认情况下，mysql_query()和mysql_real_query()仅返回第1个查询的状态，并能使用mysql_more_results()和mysql_next_result()对后续查询的状态进行处理。

if(mysql_set_server_option(MYSQL*，MYSQL_OPTION_MULTI_STATEMENTS_ON)){
std::cout<<"set flag fail"<<std::endl;
        return 1;
}
        sql = "alter table mytable add age int;update mytable set age=26 where name='rongxiaojun';select age from mytable where name='rongxiaojun';";
        if(mysql_query(&mysql, sql.c_str())){
                std::cout<<"test failed"<<std::endl;
                return 1;
        }
        std::cout<<mysql_next_result(&mysql)<<std::endl; 
        std::cout<<mysql_next_result(&mysql)<<std::endl;
        result = mysql_store_result(&mysql);
        row = mysql_fetch_row(result);
        int age = atoi(row[0]);
        std::cout<<age<<std::endl;
mysql_free_result(result);

注意：

一、int mysql_next_result(&mysql)会返回下条sql语句结果集：返回0，成功并有多个结果；返回-1，成功但没有多个结果；返回>0，出错。

二、MYSQL_RES * mysql_store_result(&mysql)：通过检查mysql_store_result()是否返回0，可检测查询是否没有结果集。如果查询未返回结果集，mysql_store_result()将返回Null指针（例如，如果查询是INSERT语句）。

三、每个result都必须要mysql_free_result(result);

mysql_fetch_row（result）取出的是字符串，参考age。

编译：g++ -o mysql mysql.cpp -lmysqlclient

运行：

namewho

rongxiaojunforever loser

rongxiaodonga little bastard

CentOS卸载软件：sudo yum -y remove loser

rongxiaojun

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL

Linux：CentOS6.4Mysql下载地址：http://dev.mysql.com/downloads/mysql/MySQL-5.6.16-1.el6.x86_64.rpm-bundle.tar解压后：1.安装server：rpm -ivh MySQL-server-5.6.16-1.el6.x86_64.rpm 安装MySQL服务器。rp
复制链接

扫一扫