常用的SQL语句 · 开发笔记

**一、操作技巧** 　　1、如果你打命令时，回车后发现忘记加分号，你无须重打一遍命令，只要打个分号回车就可以了。　　也就是说你可以把一个完整的命令分成几行来打，完后用分号作结束标志就OK。　　2、你可以使用光标上下键调出以前的命令。 **　　二、常用命令** 　　1、显示当前数据库服务器中的数据库列表：　　mysql> SHOW DATABASES; 　　2、建立数据库：　　mysql> CREATE DATABASE 库名; 　　mysql> CREATE DATABASE IF NOT EXISTS my\_db default charset utf8 COLLATE utf8\_general\_ci; 　　3、建立数据表：　　mysql> USE 库名; 　　mysql> CREATE TABLE 表名 (字段名 VARCHAR(20), 字段名 CHAR(1)); 　　4、删除数据库：　　mysql> DROP DATABASE 库名; 　　5、删除数据表：　　mysql> DROP TABLE 表名; 　　6、将表中记录清空：　　mysql> DELETE FROM 表名; 　　7、往表中插入记录：　　mysql> INSERT INTO 表名 VALUES ("hyq","M"); 　　8、更新表中数据：　　mysql-> UPDATE 表名 SET 字段名1='a',字段名2='b' WHERE 字段名3='c'; 　　9、用文本方式将数据装入数据表中：　　mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" INTO TABLE 表名; 　　10、导入.sql文件命令：　　mysql> USE 数据库名; 　　mysql> SOURCE d:/mysql.sql; 　　11、命令行修改root密码：　　mysql> UPDATE mysql.user SET password=PASSWORD('新密码') WHERE User='root'; 　　mysql> FLUSH PRIVILEGES; **　　三、一个建库和建表以及插入数据的实例** 　　drop database if exists school; //如果存在sudu则删除　　create database sudu; //建立库sudu 　　use school; //打开库sudu 　　create table teacher //建立表TEACHER 　　( 　　id int(3) auto\_increment not null primary key, 　　name char(10) not null, 　　address varchar(50) default '深圳', 　　year date 　　); //建表结束　　//以下为插入字段　　insert into teacher values('','allen','飞数科技1','2005-10-10'); 　　insert into teacher values('','jack','飞数科技2','2005-12-23');如果你在mysql提示符键入上面的命令也可以，但不方便调试。　　(1)你可以将以上命令原样写入一个文本文件中，假设为sudu.sql，然后复制到c:\\\\下，并在DOS状态进入目录\\mysql\\bin，然后键入以下命令：　　mysql -uroot -p密码 < c:\\sudu.sql 　　如果成功，空出一行无任何显示;如有错误，会有提示。(以上命令已经调试，你只要将//的注释去掉即可使用)。　　(2)或者进入命令行后使用 mysql> source c:\\sudu.sql; 也可以将sudu.sql文件导入数据库中。 **　　四、将文本数据转到数据库中** 　　1、文本数据应符合的格式：字段数据之间用tab键隔开，null值用\\n来代替.例：　　3 rose 飞数科技1 1976-10-10 　　4 mike 飞数科技2 1975-12-23 　　假设你把这两组数据存为速度sudu.txt文件，放在c盘根目录下。　　2、数据传入命令 load data local infile "c:\\sudu.txt" into table 表名; 　　注意：你最好将文件复制到\\mysql\\bin目录下，并且要先用use命令打表所在的库。 **　　五、备份数据库：(命令在DOS的\\mysql\\bin目录下执行)** 　　1.导出整个数据库　　导出文件默认是存在mysql\\bin目录下　　mysqldump -u 用户名 -p 数据库名 > 导出的文件名　　mysqldump -u user\_name -p123456 database\_name > outfile\_name.sql 　　2.导出一个表　　mysqldump -u 用户名 -p 数据库名表名> 导出的文件名　　mysqldump -u user\_name -p database\_name table\_name > outfile\_name.sql 　　3.导出一个数据库结构　　mysqldump -u user\_name -p -d --add-drop-table database\_name > outfile\_name.sql 　　-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table 　　4.带语言参数导出　　mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt database\_name > outfile\_name.sql # [mysql导入导出sql文件](https://www.cnblogs.com/yuwensong/p/3955834.html) 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 4.导入数据库常用source 命令进入mysql数据库控制台，如 mysql -u root -p mysql>use 数据库然后使用source命令，后面参数为脚本文件(如这里用到的.sql) mysql>source d:/dbname.sql **1.** 导入数据到数据库 mysql -uroot -D数据库名 **1.** 导入数据到数据库中得某个表 mysql -uroot -D数据库名表名 **D:\\APMServ5.2.6\\MySQL5.1\\bin>mysqldump -u root -p erp lightinthebox\_tags > ligh tinthebox.sql** **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目录二、导入数据库 1、首先建空数据库 mysql>create database abc; 2、导入数据库方法一：（1）选择数据库 mysql>use abc; （2）设置数据库编码 mysql>set names utf8; （3）导入数据（注意sql文件的路径） mysql>source /home/abc/abc.sql; 方法二： mysql -u用户名 -p密码数据库名 < 数据库名.sql #mysql -uabc\_f -p abc < abc.sql #### -- 查询字段名和字段注释 ~~~ SELECT `column_name`, `column_comment` FROM `information_schema`.`columns` WHERE `table_name` = '表名' AND `table_schema` = '数据库名' ~~~ #### -- 创建索引 ~~~ // 1.添加PRIMARY KEY(主键索引) mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) // 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) // 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) // 4.添加FULLTEXT(全文索引) mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`) // 5.添加多列索引 mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` ) ~~~ #### -- 查询输出数据库中所有的索引 ~~~ SELECT CONCAT('ALTER TABLE `',TABLE_NAME,'` ', 'ADD ', IF(NON_UNIQUE = 1, CASE UPPER(INDEX_TYPE) WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX' WHEN 'SPATIAL' THEN 'SPATIAL INDEX' ELSE CONCAT('INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE ) END, IF(UPPER(INDEX_NAME) = 'PRIMARY', CONCAT('PRIMARY KEY USING ', INDEX_TYPE ), CONCAT('UNIQUE INDEX `', INDEX_NAME, '` USING ', INDEX_TYPE ) ) ),'(', GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`') ORDER BY SEQ_IN_INDEX ASC SEPARATOR ', '), ');') AS 'Show_Add_Indexes' FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = '数据库名' GROUP BY TABLE_NAME, INDEX_NAME ORDER BY TABLE_NAME ASC, INDEX_NAME ASC; ~~~ #### -- 创建存储过程 -- 更新超时订单相关押金数额的存储过程 ~~~ DELIMITER ;; CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_dopfren_plusminus`() BEGIN UPDATE `order` SET status='-1' WHERE DATE(SYSDATE())=DATE_ADD(overtime_date, INTERVAL 1 DAY) AND status BETWEEN 1 AND 2; UPDATE order_detail d SET d.deposit=d.deposit-(d.price*0.05) WHERE d.order_id IN (SELECT id FROM `order` WHERE status = '-1'); UPDATE `order` SET freeze = freeze + `totalprice`*0.05 WHERE status='-1'; UPDATE `order` SET status='-2' WHERE freeze=totalprice; END;; DELIMITER ; ~~~ #### -- 查看库中有哪些存储过程 `SHOW PROCEDURE STATUS -- WHERE DB='xxx';` #### -- 查看event事件是否开启 `SHOW VARIABLES LIKE '%event_scheduler%';` #### -- 开启event事件 `SET GLOBAL event_scheduler = ON; ` #### -- 创建定时任务 ~~~ CREATE DEFINER=`root`@`localhost` EVENT `auto_mipu_depofren_day` ON SCHEDULE EVERY 1 DAY STARTS date_add(date(curdate() + 1),interval 0 hour) ON COMPLETION PRESERVE ENABLE DO call `proc_dopfren_plusminus`;-- 每天0点 CREATE DEFINER=`root`@`localhost` EVENT `auto_mipu_depofren_day` ON SCHEDULE EVERY 1 HOUR STARTS concat(date_format(now(),'%Y-%m-%d %H'),':00:00') ON COMPLETION PRESERVE ENABLE DO call `proc_dopfren_plusminus`;##每小时0分 CREATE DEFINER=`root`@`localhost` EVENT `auto_mipu_depofren_day` ON SCHEDULE EVERY 5 MINUTE STARTS concat(date_format(now(),'%Y-%m-%d %H'),':00:00') ON COMPLETION PRESERVE ENABLE DO call `proc_dopfren_plusminus`;##每5分钟0秒 ~~~ #### -- 启动事件任务 ~~~ ALTER EVENT `auto_mipu_depofren_day` ON COMPLETION PRESERVE ENABLE; ~~~ #### -- 关闭事件任务 ~~~ ALTER EVENT `auto_mipu_depofren_day` ON COMPLETION PRESERVE DISABLE; ~~~ #### -- 查询数据库中的事件任务 `SELECT * FROM INFORMATION_SCHEMA.EVENTS;` #### -- 系统版本和系统时间的一些查看方式 ~~~ SHOW VARIABLES like '%version%'; SELECT SYSDATE(); SELECT DATEDIFF(date('2016-8-9'), utc_date); SELECT DATE_ADD(date(now()), INTERVAL 30 DAY); SELECT DATE_ADD(date('2016-8-2'), INTERVAL 30 DAY); SELECT DATE('2016-8-30 17:31:59'); SELECT DATE_ADD(DATE(CURDATE() + 1),INTERVAL 1 HOUR); SELECT CONCAT(DATE_FORMAT(NOW(),'%Y-%m-%d %H'),':01:00'); SELECT TIMESTAMP(CURDATE() + INTERVAL 1 DAY + INTERVAL 1 HOUR); SELECT TIMESTAMP(CURRENT_DATE() + INTERVAL 1 DAY + INTERVAL 1 HOUR); SELECT DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR); SELECT (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP('2016-09-23 09:35:50')); -- 两个时间差(分钟) /60为时间差(秒); SELECT * FROM `table1` WHERE SYSDATE()>overtime AND status BETWEEN 1 AND 2; ALTER TABLE `addon_common_weiyuyue_storage` ADD COLUMN `appointment_id` INT(10) UNSIGNED NOT NULL COMMENT '预约服务ID'; SELECT `column_name`, `column_comment` FROM `information_schema`.columns WHERE `table_schema`='wemall7' and `table_name` = 'addon_common_weiyuyue_storage'; ALTER TABLE `addon_common_weiyuyue_storage` ADD UNIQUE KEY (`appointment_id`); ~~~ #### -- LOAD DATA INFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为一个文字字符串使用方法可参考[http://www.jb51.net/article/58093.htm](http://www.jb51.net/article/58093.htm)