Mysql 常用命令与函数、事务、索引、导出和导入

基础命令类别

 

标准SQL【Structured Query Language,结构化查询语言】语言，按照功能分为四类：

（1）DDL[Data Definition Language，数据定义语言]：create【创建】,drop【删除】,alter【修改】

（2）DML[Data Manipulation Language，数据操作语言]:insert【插入】,update【更新】,delete【删除】

（3）DQL[Data Query Language，数据查询语言]:select【查询】

（4）DCL[Data Control Language，数据控制语言]:grant【授权】,revoke【撤消】,commit【提交】,rollback【回滚】

常用命令：
#创建新的数据库
CREATE DATABASE IF NOT EXISTS 数据库名称;
#删除存在数据库
DROP DATABASE IF EXISTS 数据库名称;
#显示所有数据库名称
SHOW DATABASES;
#指定当前数据库
USE 数据库名称;
#显示指定表的结构
DESC [数据库名称.]数据表名称;
#显示创建表语句
SHOW CREATE TABLE [数据库名称.]数据表名称;
#查询当前正在使用的数据库
SELECT DATABASE();
--修改表名称
ALTER TABLE 旧表名 RENAME AS 新表名;
#添加表字段
ALTER TABLE 表名 ADD 字段名 字段各项属性【与建表时字段属性相同】;
#修改字段名
ALTER TABLE 表名 CHANGE 旧字段名 新字段名及其各种属性;
#修改字段属性
ALTER TABLE 表名 MODIFY 字段名 字段新属性;
#删除字段
ALTER TABLE 表名 DROP 字段名;
#删除表
DROP TABLE IF EXISTS 表名;

常见字段属性
（1）unsigned，无符号整数值
（2）zerofill，零填充整数值
（3）auto_increment，自增整数值，注必须设置主键属性primary key
（4）null和not null，是否允许为空，前者可以不写，后者表示不能不写
（5）default，为指定字段赋予默认值

注释
（1）字段注释，comment '单引号字符串'
（2）表注释，comment='单引号字符串'

表引擎类型
（1）engine=myisam，适用于只读操作
（2）engine=innodb，常用，默认

例：
#创建数据表
#comment == 注释  default==默认值 unsigned == 无符号整数值 zerofill== 零填充数值整数 not null==不能为空 AUTO_INCREMENT ==自动递增 PRIMARY KEY == 设置主键

CREATE TABLE IF NOT EXISTS `test` (

  `IDNo` INT (4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '编号',

  `LoginPwd` VARCHAR (20) DEFAULT NULL COMMENT '登录密码',

  `UserName` VARCHAR (20) DEFAULT NULL COMMENT '用户姓名',

  `Gender` TINYINT (1) DEFAULT 1 COMMENT '性别，取值0或1',

  `UserId` INT (11) DEFAULT NULL COMMENT '用户编号',

  `Address` VARCHAR (255) NOT NULL COMMENT '地址，允许为空，即可选输入',

  `BornDate` DATETIME DEFAULT NULL COMMENT '出生时间',

  `Email` VARCHAR (50) NOT NULL COMMENT '邮箱账号，允许为空，即可选输入',

  `IdentityCard` VARCHAR (18) DEFAULT NULL COMMENT '身份证号'

) COMMENT = '用户信息表' ;

表字符编码：[default] charset=utf8，设置表中数据的字符编码

外键约束

外键作用：限制一个表中的指定字段的取值必须在另一个表的字段中出现的值
创建外键【建表时】
CREATE TABLE IF NOT EXISTS `表名`
(
  ......,
  FOREIGN KEY (`字段`) REFERENCES `引用表` (`主键字段`)
);
创建外键【建表后】
ALTER TABLE `表名`
ADD CONSTRAINT `外键约束名` FOREIGN KEY (`字段名`) REFERENCES `引用表` (`主键字段`);
删除外键
#删除外键约束
ALTER TABLE `表名` DROP FOREIGN KEY `外键约束名`;
例：“A表”引用了“B表”
（1）“A表”称为“引用表”
（2）“B表”称为“被引用表”
（3）先删除引用表再删除被引用表

插入数据
INSERT INTO `表名` (`字段1`,`字段2`,...`字段n`) VALUES (值1,值2,...值n);

更新数据
UPDATE `表名` SET `字段1`=值1,`字段2`=值2,...`字段n`=值n WHERE 条件[字段=值];

在where条件中可使用的运算符包括：
（1）=【等于】
（2）<>或!=【不等于】
（3）>【大于】
（4）>=【大于或等于】
（5）<【小于】
（6）<=【小于或等于】
（7）between x and y【介于x与y之间，注包括x和y】
（8）and【与，同时需要满足多个条件时】
（9）or【或，即只需满足多个条件至少一个时】

删除数据
DELETE FROM `表名` WHERE 条件;

清空数据
TRUNCATE TABLE `表名`;

delete from 与 truncate table的区别
（1）对于auto_increment而言前者采用继续编号（注无论最大值是否已删除），后者采用重新编号
（2）前者可应用事务，而后者不会

基础SQL与函数详解

CREATE DATABASE IF NOT EXISTS `TestDB数据库名称`; 
USE `TestDB数据库名称`;

#查询表中的所有数据（是指所有行及每行中所有列的数据，*表示所有字段）
SELECT * FROM `表名`;

#仅查询表的部分字段，要使用逗号分隔字段列表中各个字段
SELECT `字段1`,`字段2`,`字段3` FROM `表名`;

#在字段名前可加表名进行严格的限定
SELECT `表名`.`字段1`,`表名.`字段2`,`表名`.`字段3` 
FROM `表名`;

#查询字段（字段别名）
SELECT `字段1` AS 姓名 FROM `表名`;
SELECT `字段1` AS `姓名` FROM `表名`;
SELECT `字段1` AS '姓名' FROM `表名`;
SELECT `字段1` AS "姓名" FROM `表名`;
SELECT `字段1` AS '姓 名' FROM `表名`;

#查询表别名，只能用反引号
SELECT `别名`.`字段1` AS 学号 FROM `表名` AS `别名`;

#查询表中的原和加分后（as是可选的）
SELECT `别名`.`字段1` AS '原',`别名`.`字段1`+1 AS '修改后' FROM `表名` AS `别名`;
SELECT `别名`.`字段1` '原',`别名`.`字段1`+1 '修改后' FROM `表名` `别名`;

#查询表中字段（去重记录[行]）
SELECT `字段1` FROM `表名`;
#all表示所有记录，默认值
SELECT ALL `字段1` FROM `表名`;
#distinct表示行的去重
SELECT DISTINCT `字段1` FROM `表名`;

#表达式是指包括常量，变量，各种运算式，null
#函数version()获取mysql版本信息
SELECT VERSION() ;

#查询当前数据库
SELECT DATABASE();
 

#查询数据在60至100之间的所有记录[执行顺序from->where->select]
SELECT ALL * 
FROM `表名` r 
WHERE r.`字段1`>=60 AND r.`字段1`<=100;

SELECT ALL * 
FROM `表名` r 
WHERE r.`字段1` BETWEEN 60 AND 100;

#查询表中2000年出生的男生信息
SELECT s.`StudentName` 姓名,s.`Sex` `性别（1=男，2=女）`,s.`BornDate` 出生日期
FROM `表名` s
WHERE s.`Sex`=1 AND s.`BornDate` BETWEEN '2000-01-01' AND '2000-12-31';

#模糊查询：%表示任意个任意字符，_表示仅一个任意字符，仅适用于字符串查询
#查询所有包含“房”的姓名
SELECT *
FROM `表名` s
WHERE s.`字段1` LIKE '%房%';
#查询姓名为“房**”的三个字的用户信息
SELECT s.`字段1` 学号,s.`字段2` 姓名
FROM `表名` s
WHERE s.`字段2` LIKE '房__';

#查字段内为110,120,130的信息
SELECT *
FROM `表名` s
WHERE s.`字段1` IN (110,120,130);

SELECT *
FROM `表名` s
WHERE s.`字段1`=110 OR s.`字段1`=120 OR s.`字段1`=130;

#查询字段2为空的数据
SELECT s.`字段1`,s.`字段2`
FROM `表名` s
WHERE s.`字段2` IS NULL;

#查询字段2不为空的数据
SELECT s.`字段1`,s.`字段2`
FROM `表名` s
WHERE s.`字段2` IS NOT NULL;

SELECT s.`字段1`,s.`字段2`
FROM `表名` s
WHERE NOT s.`字段2` IS NULL;

SELECT s.`字段1`,s.`字段2`
FROM `表名` s
WHERE !(s.`字段2` IS NULL);

#当需要从多张（至少两张）表中查询数据时需要使用连接查，分为以下情况：
#交叉连接
#内连接，包括等值连接，非等值连接，自连接
#外连接，包括左连接，右连接
 

SELECT 字段1 编号,字段2 姓名
FROM 表名1 g,`表名2` s
WHERE g.字段1=s.字段1;
#语法改成内连接，默认inner join
#内连接是指按照指定条件选出的匹配记录
SELECT 字段1 编号,字段2 姓名
FROM 表名1 g INNER JOIN `表名2` s ON g.字段1=s.字段1;

#外连接
#左外连接，包括左表中所有记录以及与左表记录相匹配的右表记录，若不匹配则写null
SELECT 字段1 编号,字段2 姓名
FROM 表名1  g LEFT OUTER JOIN `表名2` s ON g.字段1=s.字段1;
#右外连接，包括右表中所有记录以及与右表记录相匹配的左表记录，若不匹配则写null
SELECT 字段1 编号,字段2 姓名
FROM `表名2` s RIGHT OUTER JOIN 表名1 g ON g.字段1=s.字段1;

USE test;
CREATE TABLE grade(id INT,`name` VARCHAR(20));
CREATE TABLE student(id INT,`name` VARCHAR(20),gid INT);
INSERT INTO grade VALUES(1,'zhangsan113'),(2,'zhangsan115');
INSERT INTO student VALUES(1,'lisi',1),(2,'wangwu',NULL);

SELECT s.`name` 姓名,g.`name` 班级
FROM student s INNER JOIN grade g ON s.gid=g.id;

SELECT s.`name` 姓名,g.`name` 班级
FROM student s LEFT JOIN grade g ON s.gid=g.id;

SELECT s.`name` 姓名,g.`name` 班级
FROM student s RIGHT JOIN grade g ON s.gid=g.id;

SELECT s.`name` 姓名,g.`name` 班级
FROM grade g LEFT JOIN student s ON s.gid=g.id;

#自连接
CREATE TABLE IF NOT EXISTS test (
  categoryId INT (10),
  categoryName VARCHAR (32) NOT NULL,
  pid INT (10) NOT NULL,  
  PRIMARY KEY (categoryId)
);
INSERT INTO  test(categoryId,pid,categoryName) VALUES
(1,1,"英语"),
(2,1,"语文"),
(3,3,"体育"),
(4,2,"生物"),
(5,2,"化学"),
 

SELECT c2.`categoryName` 父栏目名称,c1.`categoryName` 子栏目名称
FROM category c1 INNER JOIN category c2 ON c1.pid=c2.`categoryId`;

#排序，通过关键了order by 字段名 [asc|desc]，asc表示升序（默认），desc表示降序
#查询《语文》的所有考试结果，并按成绩由高到低排列
SELECT r.`StudentNo`,r.`StudentResult` 
FROM result r INNER JOIN `subject` s ON r.`SubjectNo`=s.`SubjectNo` 
WHERE s.`SubjectName`='语文'
ORDER BY r.`StudentResult` DESC;

#限制行，通过关键字limit
SELECT r.`StudentNo`,r.`StudentResult` rs
FROM result r INNER JOIN `subject` s ON r.`SubjectNo`=s.`SubjectNo` 
WHERE s.`SubjectName`='语文'
ORDER BY rs DESC
-- limit 5;
-- limit 0,5;
LIMIT 5 OFFSET 0;

#查询所有《英语》的考试成绩，并按照由高到低显示，
#同时把该成绩对应的学生的学号、姓名打印出来
# 第一步：把相关表找出来
SELECT * FROM `subject`;
SELECT * FROM `result`;
SELECT * FROM `student`;
#第二步：表边接
SELECT t.`StudentNo` 学号,t.`StudentName` 姓名,s.`SubjectName` 课程,r.`StudentResult` 成绩
FROM `subject` s INNER JOIN `result` r ON s.`SubjectNo`=r.`SubjectNo`
         INNER JOIN `student` t ON r.`StudentNo`=t.`StudentNo`
WHERE s.`SubjectName` LIKE '英语'
ORDER BY 成绩 DESC
-- limit 0,5;
LIMIT 5,5;

#子查询
#查询课程为《体育》且分数不小于30分的学生的学号和姓名
SELECT r.`StudentNo`
FROM `subject` s INNER JOIN `result` r ON s.`SubjectNo`=r.`SubjectNo`
WHERE s.`SubjectName`='体育' AND r.`StudentResult`>=30

SELECT t.`StudentNo`,t.`StudentName` 
FROM `student` t
WHERE t.`StudentNo` IN (
    SELECT r.`StudentNo`
    FROM `subject` s INNER JOIN `result` r ON s.`SubjectNo`=r.`SubjectNo`
    WHERE s.`SubjectName`='体育' AND r.`StudentResult`>=30
);

#查询《化学》的前5名学生成绩信息
SELECT t.`StudentNo` 学号,t.`StudentName` 姓名,s.`SubjectName` 课程,r.`StudentResult` 成绩
FROM `subject` s INNER JOIN `result` r ON s.`SubjectNo`=r.`SubjectNo`
         INNER JOIN `student` t ON r.`StudentNo`=t.`StudentNo`
WHERE s.`SubjectName` LIKE '化学'
ORDER BY 成绩 DESC
LIMIT 5;

#分组
#统计函数：count(1)【行数】,sum(字段)【总和】,avg(字段)【平均】,max(字段)【最大】,min(字段)【最小】
SELECT COUNT(*) FROM `表名`;
SELECT COUNT(1) FROM `表名`;
SELECT SUM(r.`字段`) FROM `表名`;
SELECT AVG(r.`字段`) FROM `表名`;
SELECT SUM(r.`字段`)/COUNT(1) FROM `表名`;
SELECT MAX(r.`字段`) FROM `表名`;
SELECT MIN(r.`字段`) FROM `表名`;
 

#通过group by关键字分组，此时select后只能写分组字段和每组的统计函数
SELECT r.`字段1` rs,COUNT(1) cnt,AVG(r.`字段2`) `avg`
FROM `表名` r
GROUP BY rs

#按照不同的数据分组，分别算出其平均分、最高分和最低分，
#对于低于80分平均分的不予显示
SELECT r.`字段1` rs,AVG(r.`字段2`) `avg`,MAX(r.`字段2`),MIN(r.`字段2`)
FROM `表名` r
GROUP BY rs
HAVING `avg`>=80;

SELECT r.`字段1` rs,AVG(r.`字段2`) `avg`,MAX(r.`字段2`),MIN(r.`字段2`)
FROM `表名` r
WHERE r.`字段2`>60
GROUP BY rs
HAVING `avg`>=80
ORDER BY `avg` DESC
LIMIT 3;
-- 执行顺序：from->where->select->group by->having->order by->limit

#函数
SELECT MOD(10,3);        //取余数
SELECT CEIL(5.56),FLOOR(5.56);  //CEIL向上取整,FLOOR向下取整
SELECT ROUND(3.1415926),ROUND(3.1415926,2);  //前者取整四舍五入,后者附取小数点后2位
SELECT RAND();                //随机数

SELECT LENGTH('abcdc');                //字符长度
SELECT CONCAT('ab','cd','ex');        //字符拼接
SELECT CONCAT_WS(',','ab','cd','ex'); //使用指定的分隔符 separator 连接多个字符串
SELECT INSERT('abcdcfg',1,5,'12xxxxx'); //将字符串abcdcfg从第1位置开始，5个字符长的子串替换为字符串xxxxxxx
SELECT LOCATE('ab','123abcde');        //计算字符串‘ab’ 在 ‘123abcde’里面什么位置，没有则返回0
SELECT LOWER('ABC'),UPPER('abc');       //LOWER转小写，UPPER转大写
SELECT LEFT('abcdefg',5),RIGHT('abcdefg',5);  //LEFT从左开始5个字符位，RIGHT从右开始5个字符位
SELECT CONCAT('xxx',TRIM('   abc   '),'yyy');        //拼接多个列
SELECT REPLACE('abcdeabc','ab','xxx');        //替换字符串 'abcdeabc' 里面有‘ab’ 的 替换成‘xxx’
SELECT SUBSTRING('abcdefg',3),SUBSTRING('abcdefg',3,3); //字符串截取前者从第3位开始截取，后者从第3位开始截取截取3位
SELECT SUBSTR('abcdefg',3),SUBSTR('abcdefg',3,3); //也是用于截取的

substring与substr

相同点

1.两者均是截取字符串使用的函数
2.如果只是写一个参数,两者的作用都是一样的:就是截取字符串当前下标以后直到字符串最后的字符串片段

不相同点

1.两者的第二个参数有完全不同的含义；
substr（a,b）
第二个参数是截取字符串的长度
substring（a,b）
第二个参数是截取字符串最终的下标

2.substr()是基于Oracle的，substring()是基于SQL Server的，substr()与substring()两个都可以应用MySQL

SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE()+0;//两个函数的作用相同，返回了相同的系统当前日期，“CURDATE()+0”将当前日期值转换为数值型的。
SELECT CURTIME(),CURRENT_TIME();        //作用相同返回当前时间
SELECT NOW(),SYSDATE();                //作用相同返回当前日期与时间
SELECT UNIX_TIMESTAMP()/3600/24/365; -- 单位：秒 //作用是返回一个确切的时间点的UNIX时间戳，这个Unix时间戳是一个无符号整数。返回自1970-1-1 8:00:00开始到当前系统时间为止的秒数。
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y年%m月%d日 %H:%i:%s'); //返回精确日期与时间
SELECT DAYOFWEEK('2022-10-23'),WEEKDAY('2022-10-23'); //DAYOFWEEK 和 WEEKDAY获取星期。

1.DAYOFWEEK(date) 
返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。
2.WEEKDAY(date) 
返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。

=========================================================================

事务与索引

#查询自动事务的工作状态【0表示关闭，1表示开启】
SELECT @@autocommit;

#设置自动事务的工作状态
SET autocommit=0;
SET autocommit=1;

DROP DATABASE IF EXISTS `库名`;        //判断是否存在丢弃库
CREATE DATABASE IF NOT EXISTS `库名`; //判断是否存在创建库
USE `库名`;                //切换库

CREATE TABLE IF NOT EXISTS  `表名` (      
  `id` INT(12) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(36) NOT NULL,
  `cash` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;
 
INSERT INTO  `表名`   (`name`,`cash`) VALUES ('zhangsan',2000.00);
INSERT INTO  `表名`   (`name`,`cash`) VALUES ('lisi',10000.00);

SELECT * FROM 表名;

#创建“变动”事务
SET autocommit=0;
START TRANSACTION; -- begin
UPDATE 表名 SET cash=cash-500 WHERE id=1;
UPDATE 表名 SET cash=cash+500 WHERE id=2;
-- commit;
ROLLBACK;
SET autocommit=1;

-- commit表示成功提交
-- rollback表示失败回滚

USE 库名;
#创建主键【标识每条记录的唯一性，非空，仅一个】
CREATE TABLE test1
(
  a1 INT PRIMARY KEY,
  a2 VARCHAR(20)
);

CREATE TABLE test2
(
  a1 INT,
  a2 VARCHAR(20),
  PRIMARY KEY (a1)
);

#唯一索引【标识每条记录的唯一性，可空，可多个】
DROP TABLE test3;
CREATE TABLE test3
(
  a1 INT PRIMARY KEY,
  a2 VARCHAR(20) UNIQUE KEY,
  a3 VARCHAR(20) UNIQUE,
  a4 VARCHAR(20),
  a5 VARCHAR(20),
  UNIQUE KEY (a4),
  UNIQUE (a5)
);
INSERT INTO test3 VALUES(1,NULL,'a','a','a');
INSERT INTO test3 VALUES(2,NULL,'b','b','b');
INSERT INTO test3 VALUES(3,NULL,'b','b','b');

SELECT * FROM test3;

CREATE TABLE test4
(
  a1 INT,
  a2 INT,
  a3 INT,
  a4 INT,
  PRIMARY KEY (a1,a2), -- 联合主键
  UNIQUE KEY (a3,a4) -- 联合唯一
);

#常规索引
CREATE TABLE test5
(
  a1 INT,
  a2 INT,
  a3 INT,
  INDEX ind01(a1),
  KEY ind02(a2),
  INDEX ind03(a1,a2)
);

#建表后创建各种索引
DROP TABLE test6;
CREATE TABLE test6
(
  a1 INT,
  a2 INT,
  a3 INT
);
ALTER TABLE test6 ADD PRIMARY KEY (a1);
ALTER TABLE test6 ADD UNIQUE KEY uq_c2(a2);
ALTER TABLE test6 ADD INDEX in_c3(a3);
SHOW INDEX FROM test6;
SHOW KEYS FROM test6;
ALTER TABLE test6 DROP PRIMARY KEY;
ALTER TABLEtest6 DROP INDEX uq_c2;
ALTER TABLE test6 DROP KEY in_c3;

DESC test6;
ALTER TABLE test6 DROP COLUMN c3;

TRUNCATE TABLE grade;

Mysql 命令行导出、导入数据

(1)导出指定数据库中的所有表【包括结构和数据】
mysqldump -h主机 -u用户名 -p密码 数据库名 > 文件路径(.sql)

(2)导出指定数据库中的指定表【包括结构和数据】
mysqldump -h主机 -u用户名 -p密码 数据库名 表1 表2 ...> 文件路径(.sql)

(3)导出指定数据库【包括结构和数据】
mysqldump -h主机 -u用户名 -p密码 --database 数据库1 数据库2 ...> 文件路径(.sql)

-d，只有结构不要数据
-t，只有数据不要结构
-c，在备份数据时补充字段名列表

(4)恢复数据库，进入mysql客户端后
source sql文件路径
\. sql文件路径

(5)恢复数据库，无需进入mysql客户端
mysql -u用户名 -p密码 < sql文件路径
*/

#导出数据
SELECT * INTO OUTFILE 'x:/xx/xxx.sql' FROM 表名;

#创建新表与旧表具有相同结构
CREATE TABLE 表名2 LIKE 表名1;
DESC 表名;

#导入数据
LOAD DATA INFILE 'x:/xx/xxx.sql' INTO TABLE 表名;
SELECT * FROM 表名;