mysql数据库操作(一)
来源:互联网 发布:js invoke 方法 编辑:程序博客网 时间:2024/05/23 18:26
1、mysql常识
本文前面主要是对《mysql_5.5中文参考手册》部分内容的整理。
说明:登录mysql后,可以看到mysql> 的提示符,可以输入相关命令;输入命令后,需要以“;”号结束,否则会一直等待,少数的命令不需要“;”号,如QUIT;多个短命令,可以放在一起,中间用“;”号隔开;我们也可借助外部工具,如navicat来管理数据为库,在navicat中单个查询命令也可以以“;”号结束,但在复杂查寻的内部,则不能有“;”号;mysql中命令不区分大小写,但创建数据库的时候要注意大小写,大写的数据库名称,查询的时候用小写查不出来,同时也要与linux shell和python相区别,它们的编程是区分大小写的;
使用navicat将txt文本load进数据库出现乱码,首先查看txt文件是否是utf-8格式;然后在navicat中设置数据库属性,字符集选utf8mb4 – UTF-8 Unicode或utf8 – UTF-8 Unicode,排序规则选utf8mb4_general_ci或utf8_general_ci;
mysql状态表:
2、mysql基本操作汇总
UPDATE temp_id_area SET SF = CONCAT(split(SF,’市’,1),’市’) WHERE LOCATE(‘市’,SF);
UPDATE temp_id_area SET SF = CONCAT(split(SF,’地区’,1),’地区’) WHERE LOCATE(‘地区’,SF);
UPDATE temp_id_area SET SF = CONCAT(split(SF,’自治区’,1),’自治区’) WHERE LOCATE(‘自治区’,SF);
UPDATE temp_id_area SET SF = CONCAT(split(SF,’省’,1),’省’) WHERE LOCATE(‘省’,SF);
UPDATE temp_id_area SET SFBM = SUBSTR(SSBM,1,2);
SELECT MONTH(CURRENT_DATE);
SELECT DAY(CURRENT_DATE); CURRENT_DATE也可以换成其他日期格式的数据;也可以用取模的方式来间接获取月份,如SELECT MOD(CURDATE(),12);但注意,此时的范围是0-11,因而如果是12月份要加1
在GROUP BY中,两个null视为相同;执行ORDER BY ,null排在最前面,加上DESC则排在最后面
SELECT COUNT(*) FROM PET GROUP BY SEX ; 使用计数如果不需要检索整个表,应该用where来限定范围提高效率
mysq NULL值替换
将mysql数据库中的NULL值替换为空
UPDATE qrt_order_info
SET order_num=” WHERE order_num=”NULL”;
原本在数据库的NULL是被当成字符串了,不同的语言,表示空的方法都不一样,mysql中是”\N”;所有可以在加载数据前,对NULL作替换,这样一次性导入后就自动为空,而不需要像上面那样去数据库里UPDATE了。
mysql内NULL判断的是空,就是没有任何值,此时用navicat查看的时候可以看淡灰色的”(Null)”字样;有时会在数据库记录的某一栏看不到任务记录,此时未必是NULL,有可能是空字符,”,只不过字符串里没任何内容,此时要用where xx=”;
MySQL数据导出
数据表导出:
在navicat中直接导出已存在的数据表,导出格式很多,最常用的导出格式是.sql,.excel;
查询结果导出:
navicat中查询结果也是可以直接导出的,不用再复制粘贴了。
linux下查询结果可通过如下方式导出:
在已连接数据库情况下,select * from edu_iclass_areas into outfile test.xls #这里需要绝对路径
使用命令导出:
window下
1.导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u dbuser -p dbname > dbname.sql
2.导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u dbuser -p dbname users> dbname_users.sql
3.导出一个数据库结构
mysqldump -u dbuser -p -d --add-drop-table dbname >d:/dbname_db.sql-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table
linux下
一、导出数据库用mysqldump命令(注意mysql的安装路径,即此命令的路径):
1、导出数据和表结构:
mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql
#/usr/local/mysql/bin/ mysqldump -uroot -p abc > abc.sql
敲回车后会提示输入密码
2、只导出表结构
mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql
#/usr/local/mysql/bin/ mysqldump -uroot -p -d abc > abc.sql
注:/usr/local/mysql/bin/ —> mysql的data目录
MySQL数据导入
关于批量导入数据到mysql数据库,首先可以从数据库导出批量文件,有多种格式,如excel,txt或sql;对于txt文件后期可以利用load导入其他数据库,但出要先创建数据库,如下面的命令所示,在navicat中使用load,文件名中不能有中文;对于sql文件,它导出得到的是包含一系列insert 语句的文件,有的会包含类似下面的完整命令,此时直接在navicat中运行该.sql文件即可;如果是在mysql的shell里直接运行的话,可用source *.sql
。
DROP TABLE IF EXISTS `temp_integrated`;CREATE TABLE `temp_integrated` ( `cust_code` varchar(32) NOT NULL COMMENT 'xx', `cust_name` varchar(32) DEFAULT NULL COMMENT 'xx', `id_card_no` varchar(32) DEFAULT NULL COMMENT 'xx', `province` varchar(32) DEFAULT NULL COMMENT 'xx', `cust_add` varchar(32) DEFAULT NULL COMMENT 'xx', `county` varchar(32) DEFAULT NULL COMMENT 'xx') ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;INSERT INTO temp_integrated VALUES ('xxx', 'xx','xx', 'xx', 'xx', 'xx');
跨数据库操作
在表的前面加上数据进行限定,如select * from 库.表 where …….
关于数据乱码
在R读入mysql数据库出现中文乱码,首先确保mysql数据库中字符集编码格式为utf-8;然后再本地电脑上的数据源设置中(控制面板–管理工具–数据源–配置–detail)设置好对应mysql数据库连接的字符集编码为utf-8,然后在R中channel_temp=odbcConnect(“数据库连接的名称”,uid =”xx” ,pwd =”xx”,DBMSencoding=”UTF-8”),指定编码,则不会出现乱码。
如何判断mysql数据库中是否存在某张表
方法1、 DROP TABLE IF EXISTS tablename;
方法2、 做一个sql查询,比如:select * from tablename/select count(*) from tablename,如果返回值为空,则表不存在。
方法3、 查询:SHOW TABLES LIKE table1,判断返回值。如:
if(mysql_num_rows(mysql_query(“SHOW TABLES LIKE ‘”.$table.”’”)==1)
{
echo “Table exists”;
}
else
{
echo “Table does not exist”;
}
方法4、 CREATE TABLE IF NOT EXISTS yourdb
.yourtable
(…)
mysql判断数据库是否存在
(1) 判断数据库存在, 则删除:
drop database if exists db_name;
(2) 如果单纯显示是否存在数据库, 则可用:
show databases like ‘db_name’;
mysql中的函数
select SIGN(0); -1
0
select GREATEST(“B”,”A”,”C”); 2
”C”
mysql中自定义函数
mysql中可以通过自定义函数来完成一些功能。
新建一个函数:
语法结构:
Create function function_name(参数列表)returns返回值类型函数体
函数名,应该合法的标识符,并且不应该与已有的关键字冲突。
一个函数应该属于某个数据库,可以使用db_name.funciton_name的形式执行当前函数所属数据库,否则为当前数据库。
参数部分,由参数名和参数类型组成。
返回值类类型
函数体由多条可用的mysql语句,流程控制,变量声明等语句构成。
多条语句应该使用begin end语句块包含。在给变量赋值时一定要使用set ……
注意,一定要有return返回值语句。
#下面的程序自定义了一个分割字符串的函数,参数f_string为待分割的字符串,f_delimiter参数为定义的分割字符,参数f_order为从分割好的字符串数组中取第几个值;#mysql中默认的命令分隔符为;号,当遇到;号时就要执行命令,这里自定义了$$为命令分隔符,这样后面的不同命令之间就可以使用$$分隔,与;不同的是,它不需立即执行,可以等到所有的命令读取完毕后再由mysql解释器一次性执行DELIMITER $$ DROP function IF EXISTS `func_splitString` $$ CREATE FUNCTION `func_splitString` ( f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8 BEGIN declare result varchar(255) default ''; set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1)); return result; END$$
上面函数的使用方法,先运行该函数,这样函数就会存在mysql所在计算机的内存中,这样其他命令就可以像调用内置函数一样调用该函数,如下:
SELECT func_splitString('a,b,c',',','2')
将得到结果b
MySQL存储过程的创建
语法:
CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体DELIMITER // CREATE PROCEDURE myproc(OUT s int) BEGIN SELECT COUNT(*) INTO s FROM students; END //DELIMITER ;
分隔符:
MySQL默认以”;”为分隔符,如果没有声明分割符,则编译器会把存储过程当成SQL语句进行处理,因此编译过程会报错,所以要事先用“DELIMITER //”声明当前段分隔符,让编译器把两个”//”之间的内容当做存储过程的代码,不会执行这些代码;“DELIMITER ;”的意为把分隔符还原。
参数:
存储过程根据需要可能会有输入、输出、输入输出参数,如果有多个参数用”,”分割开。MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT:
IN参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
OUT:该值可在存储过程内部被改变,并可返回
INOUT:调用时指定,并且可被改变和返回
过程体:
过程体的开始与结束使用BEGIN与END进行标识。
条件语句
IF-THEN-ELSE语句:
#条件语句IF-THEN-ELSEDROP PROCEDURE IF EXISTS proc3;DELIMITER //CREATE PROCEDURE proc3(IN parameter int) BEGIN DECLARE var int; SET var=parameter+1; IF var=0 THEN INSERT INTO t VALUES (17); END IF ; IF parameter=0 THEN UPDATE t SET s1=s1+1; ELSE UPDATE t SET s1=s1+2; END IF ; END ; //DELIMITER ;
下面是一个往表格中插入行的例子
#条件语句IF-THEN-ELSEDROP PROCEDURE IF EXISTS proc3;DELIMITER //CREATE PROCEDURE proc3(IN date_string varchar(32)) BEGIN IF MONTH(date_string)=12 THEN INSERT INTO temp_month_interval VALUES( CONCAT(year(CURDATE()),'11','01'),CONCAT(year(CURDATE()),'11','30'),CONCAT(year(CURDATE()),'11','01'),CONCAT(year(CURDATE()),'11','30'), CONCAT(year(CURDATE()),'10','01'),CONCAT(year(CURDATE()),'10','31'),CONCAT(year(CURDATE()),'10','01'),CONCAT(year(CURDATE()),'10','31'), CONCAT(year(CURDATE()),'09','01'),CONCAT(year(CURDATE()),'09','30'),CONCAT(year(CURDATE()),'09','01'),CONCAT(year(CURDATE()),'09','30'), CONCAT(year(CURDATE()),'08','01'),CONCAT(year(CURDATE()),'08','31'),CONCAT(year(CURDATE()),'08','01'),CONCAT(year(CURDATE()),'08','31'), CONCAT(year(CURDATE()),'07','01'),CONCAT(year(CURDATE()),'07','31'),CONCAT(year(CURDATE()),'07','01'),CONCAT(year(CURDATE()),'07','31'), CONCAT(year(CURDATE()),'06','01'),CONCAT(year(CURDATE()),'06','30'),CONCAT(year(CURDATE()),'06','01'),CONCAT(year(CURDATE()),'06','30')); ELSE INSERT INTO temp_month_interval VALUES( CONCAT(year(CURDATE()),'11','01'),CONCAT(year(CURDATE()),'11','30'),CONCAT(year(CURDATE()),'11','01'),CONCAT(year(CURDATE()),'11','30'), CONCAT(year(CURDATE()),'10','01'),CONCAT(year(CURDATE()),'10','31'),CONCAT(year(CURDATE()),'10','01'),CONCAT(year(CURDATE()),'10','31'), CONCAT(year(CURDATE()),'09','01'),CONCAT(year(CURDATE()),'09','30'),CONCAT(year(CURDATE()),'09','01'),CONCAT(year(CURDATE()),'09','30'), CONCAT(year(CURDATE()),'08','01'),CONCAT(year(CURDATE()),'08','31'),CONCAT(year(CURDATE()),'08','01'),CONCAT(year(CURDATE()),'08','31'), CONCAT(year(CURDATE()),'07','01'),CONCAT(year(CURDATE()),'07','31'),CONCAT(year(CURDATE()),'07','01'),CONCAT(year(CURDATE()),'07','31'), CONCAT(year(CURDATE()),'06','01'),CONCAT(year(CURDATE()),'06','30'),CONCAT(year(CURDATE()),'06','01'),CONCAT(year(CURDATE()),'06','30')); END IF ; END ; //DELIMITER ;
调用过程:
call proc3(CURDATE())
这样表里的数据就得到更新。
存储过程的更详细信息可查阅该文
MySQL性能提升
1、能先用where 缩减查询范围先缩减
此条对运行速度影响最在
2、尽量在查询一个表的时候完成功能
多生成一些新的字段都没关系,对性能影响不大,但反复查询就很影响性能
3、少自己创建的函数
调用自己创建的函数速度很慢
- 【MySQL】数据库操作【一】
- mysql数据库操作(一)
- 一、MySQL数据库基本操作
- MySQL数据库操作(一)
- mysql命令(一)--操作数据库
- java 操作MySQL数据库(一)
- Mysql数据库基本操作(一)
- mysql数据库操作(一)
- MySQL数据库基本操作(数据库操作一)
- mysql数据库基本操作(一)
- Mysql数据库在cmd操作(一)
- mysql为例,操作数据库(一)
- MYSQL之操作数据库表(一)
- [mysql ] 数据库常用操作(一)
- MySQL(一)--数据库的基本操作
- 基础Mybatis操作数据库(MySql)(一)
- Python开发(一)操作Mysql数据库
- mysql数据库基本操作(一)
- 前端术语笔记(待更新)
- 机器学习方法在量子多体物理中的应用
- xserver相关知识汇总
- 1541 +1 *2 ²
- 一段日期内星期都是周五的客流信息
- mysql数据库操作(一)
- 给定一个n个整数的数组S,是否存在S中的4个数,使得a + b + c + d = target。 在数组中找出所有唯一的四元组,给出目标的总和。
- form表单提交中的input,button,submit
- webstorm快捷键
- Tesseract-OCR 入门
- 安装eclipse c++版本neno
- 如何在ubuntu里使用资源视图的方式打开任意目录?
- 6-11 Level-order Traversal(25 point(s))
- 20.4 缓存的重定向方法