Songbirds


春

Mysql命令总结(一)

  • ~13.61K 字
  1. 1. mysql
    1. 1.1. 一、基本操作
    2. 1.2. 二、数据库的操作
    3. 1.3. 三、表操作
    4. 1.4. 四、数据操作
    5. 1.5. 六、SELECT
    6. 1.6. 七、UNION(注意与join区别)
    7. 1.7. 八、子查询
    8. 1.8. 九、连接查询(join)
    9. 1.9. 十、TRUNCATE

版权声明：本文转载自一千行 MySQL 学习笔记，并在一定程度上进行了增删改。

mysql

注：所有操作基于day01数据库

注：所有sql命令中的 [字段] 意为可选字段

创建表(以下表均为虚构)：

user表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(10) NULL DEFAULT NULL,
  `money` double(10, 2) NOT NULL,
  `score` int(8) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `money`(`money`) USING BTREE,
  INDEX `normalIndex`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '张三', 24, 1000.00, 3);
INSERT INTO `user` VALUES (2, '张三', NULL, 2000.00, 5);
INSERT INTO `user` VALUES (3, '未设置', 18, 0.00, 7);
INSERT INTO `user` VALUES (4, '王五', 18, 3000.00, 6);
INSERT INTO `user` VALUES (6, '田七', 18, 3500.00, 9);
INSERT INTO `user` VALUES (7, '田七', 17, 4100.00, 9);

SET FOREIGN_KEY_CHECKS = 1;

t_user表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (1, '张三', 23);
INSERT INTO `t_user` VALUES (2, '李四', 20);
INSERT INTO `t_user` VALUES (3, '王五', 25);

SET FOREIGN_KEY_CHECKS = 1;

t_money表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_money
-- ----------------------------
DROP TABLE IF EXISTS `t_money`;
CREATE TABLE `t_money`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `money` double(40, 0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of t_money
-- ----------------------------
INSERT INTO `t_money` VALUES (1, 100);
INSERT INTO `t_money` VALUES (2, 200);
INSERT INTO `t_money` VALUES (3, 300);

SET FOREIGN_KEY_CHECKS = 1;

一、基本操作

1
2
3
4
5
6
7
8
9
10
11
-- 启动MySQL
    net start mysql;
-- 创建Windows服务
    sc create mysql binPath= mysqld_bin_path;(注意：等号与值之间有空格)
/* 连接与断开服务器 */
    mysql -h 地址 -P 端口 -u 用户名 -p 密码;
    SHOW PROCESSLIST -- 显示哪些线程正在运行
    SHOW VARIABLES -- 显示系统变量信息

-- 停止MySQL
    net stop mysql;

二、数据库的操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 查看所有已存在的数据库
    show databases;
	
-- 创建数据库
    create database[if not exists] 数据库名 数据库选项
    数据库选项：
        CHARACTER SET charset_name
        COLLATE collation_name
    -- 实例：如果名为day01的数据库不存在，就创建，创建默认编码为utf8，排序规则为utf8_general_ci
    CREATE DATABASE IF NOT EXISTS day01 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
	
-- 使用该数据库
    use 数据库名;

-- 查看当前选中使用的数据库
    select database();

-- 查看指定的数据库数据库信息
    show create database 数据库名;
	
-- 删除数据库
    drop database[if exists] 数据库名;

三、表操作

  1. 记忆：操作表，(不操作表的数据)，都需要加上table参数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
-- 创建表
    CREATE [TEMPORARY] TABLE[ IF NOT EXISTS] [库名.]表名 ( 表的结构定义 )[ 表选项]
    -- TEMPORARY 表示创建临时表，会话结束后消失
    -- 表结构定义：
        字段名 数据类型 [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
        [NOT NULL | NULL]：不能为空/可以为空(默认)
        [DEFAULT default_value]：如果为空则自动设置值为default_value
        [AUTO_INCREMENT] ：自增长，一般用于主键id
        [UNIQUE [KEY]：唯一约束，同主键一样，值不能重复，但是可以设置多个
        [PRIMARY] KEY]：主键
        [COMMENT 'string']：表注释
    -- 实例：
      -- 如果不存在就创建 user表(避免存在报错)
	CREATE TABLE if not EXISTS user(
		-- 创建自增长id主键
		id int PRIMARY key auto_increment,
		-- 创建string类型姓名不能为空，默认值未'未设置'，注释：姓名
		name VARCHAR(30) not null DEFAULT '未设置' COMMENT '姓名',
		-- 创建string类型mone不能为空，并且唯一约束(如果可以为null，是允许存在多个null的)
		money DOUBLE(10,2) not null UNIQUE
	)
 
 
-- 查看所有表
    show tables 
    show tables from 数据库名
    -- 查看表中数据就是sql语句select *;


-- 修改表
    -- 修改表本身
        ALTER TABLE 表名 表的选项
        eg：ALTER TABLE 表名 ENGINE=MYISAM; (将表的引擎改为myisam)
    -- 对表进行重命名
        RENAME TABLE 原表名 TO 新表名;
        RENAME TABLE 原表名 TO 库名.表名 (可以将表移动到另一个数据库)
    -- 修改表的字段(ALTER TABLE 语法)
        ALTER TABLE 表名 操作名
        -- 操作名
            ADD [COLUMN] 字段定义			 -- 增加字段
                AFTER 字段名				 -- 在xx之后增加字段
                FIRST				  	   -- 增加在第一个位置
            -- 例：ALTER TABLE `user` ADD score INT(8);  给user表添加一个score字段
            ADD PRIMARY KEY			   	    -- 创建主键
            ADD UNIQUE [索引名] (字段名)      -- 创建唯一索引
            ADD INDEX [索引名] (字段名)        -- 创建普通索引
            DROP[ COLUMN] 字段名              -- 删除字段
            MODIFY[ COLUMN] 字段名 字段属性     -- 支持对字段属性进行修改，不能修改字段名(所有原有属性也需写上)
            CHANGE[ COLUMN] 原字段名 新字段名 字段属性      -- 支持对字段名修改
            DROP PRIMARY KEY     -- 删除主键(删除主键前需删除其AUTO_INCREMENT属性)
            DROP INDEX 索引名     -- 删除索引
            DROP FOREIGN KEY 外键    -- 删除外键
            
-- 删除表
    DROP TABLE[ IF EXISTS] 表名 ...
-- 清空表数据
    TRUNCATE [TABLE] 表名
-- 复制表结构
    CREATE TABLE 表名 LIKE 要复制的表名
-- 复制表结构和数据
    CREATE TABLE 表名 [AS] SELECT * FROM 要复制的表名
-- 检查表是否有错误
    CHECK TABLE tbl_name [, tbl_name] ... [option] ...
-- 优化表
    OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
-- 修复表
    REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED] [USE_FRM]
-- 分析表
    ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

四、数据操作

  1. 注意：删改操作一定要加条件，否则就是删改整张表(准备跑路)
1
2
3
4
5
6
7
8
9
10
11
-- 增
    INSERT INTO 表名 [(字段列表)] VALUES (值列表)[,(值列表),,,]
    INSERT INTO 表名 SET 字段名=值[,字段名=值,,,]   -- 推荐上面
-- 删
    DELETE FROM 表名 [删除条件的句子]
    -- 没有条件默认删除全部
-- 改
    UPDATE 表名 SET 字段名=新值[,字段名=新值,,,] [更新条件]
    -- 没有条件默认更改全部
-- 查
    SELECT 字段列表 FROM 表名 [查询条件]

六、SELECT

  1. select：创建一张临时的表，来存储查询到的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT
    -- 字段释义：
        ALL:展示查询到的所有数据，和不写效果一样
        DISTINCT:查询去重后的结果
            -- 注：SELECT DISTINCT * from 表名;    测试不起效果
            -- SELECT DISTINCT 字段名 FROM 表名	  创建一张只有某字段去重后的临时表(同样设置多个字段失效)
        GROUP BY 字段名[ASC|DESC]：将查询到的结果分组(实际效果相当于根据字段名去重后展示(抛弃重复字段后面的))
		         配合某些例如count()、avg(字段名)可以达到分组展示的效果; [升序|降序]
         HAVING：对 GROUP BY 后的表再进行过滤
         ORDER BY 字段名; 排序，只会根据字段名排序，不会去掉重复数据
         LIMIT a,b：分页查询，a:起始行数(从0开始计数)，分页查询页数 
             -- select * from user limit 1,3;   分页：从第2行开始，一次查询3个；
			
a. select_expr：
    -- 可以用*表示所有字段
        select * from user;
    -- 可以使用表达式（计算公式、函数调用、字段也是个表达式） 
    -- 备注：select就是创建一张临时的表，如果直接给数据(如下列的计算公式，函数调用now()等不从表中查的会直接创建到临时表中)
        select name, 29+25, now() from user;
    -- 可以为每个列使用别名。适用于简化列标识，避免多个列标识符重复。
        - 使用 as 关键字，也可省略 as.
        select user+10 as add10 from user;
 
b. FROM 子句
    用于标识查询来源。
    -- 可以为表起别名。使用as关键字。
        SELECT * FROM tb1 AS tt, tb2 AS bb;
    -- from子句后，可以同时出现多个表。
        -- 多个表会横向叠加到一起，而数据会形成一个笛卡尔积。
        SELECT * FROM tb1, tb2;
    -- 向优化符提示如何选择索引
        USE INDEX、IGNORE INDEX、FORCE INDEX :使用指定的索引，忽略一个或多个索引，强制使用指定索引
        SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
        SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;

c. WHERE 子句
    -- 从from获得的数据源中进行筛选。
    -- 整型1表示真，0表示假。
    -- 表达式由运算符和运算数组成。
        -- 运算数：变量（字段）、值、函数返回值
        -- 运算符：
            =, <=>,(等于);   !=, <>,(不等于，)    
            	-- 两靠近括号的后者，如果为true，结果为1，否则为0(可以判断是否位null的情况，建议使用is null来进行判断)
            <=,<, >=, >,  !,(取符号相反)    &&,(同and，但是用于逻辑判断) ||,
            (not)in,
            	-- (不)在集合中，例：select * from user where name in("张三","王五");查询(除)张三王五
            (not) like,
            	-- (不)匹配的模糊查询，模糊查询使用%占多位符，_占一位符
            (not) between and, 
            	-- 在A和B之间的数据
            is (not),
            	-- 一般和null一起使用，用于判断是否位null
            and, or, not, 
            xor	
            	-- 逻辑异或，如果有null则为null，如果相同为0，不相同为1(一般不用)
            is/is not 加上ture/false/unknown，检验某个值的真假
            <=>与<>功能相同，<=>可用于null比较
            
d. GROUP BY 子句, 分组子句
    GROUP BY 字段/别名 [排序方式]
    分组后会进行排序。升序：ASC，降序：DESC
    以下[合计函数]需配合 GROUP BY 使用：
        count 返回不同的非NULL值数目  count(*)、count(字段)
            -- 例：select name,count(*) from user group by name;  根据姓名分组查询总数
        sum 求和
            -- 例：select age, sum(money) from user group by age; 根据年龄分组，计算每个年龄的总金钱
        max 求最大值
        min 求最小值
        avg 求平均值
        group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接。(例如一个id对应多个分数，根据id分组后，所有分数显示拼接在一个表格中，默认用逗号拼接)(将多个数据以拼接的方式放入同一组)
            -- 例：SELECT name,GROUP_CONCAT( [DISTINCT] score) from user group by name;
            -- 解释：查询姓名，该姓名的所有 [去重] 分数(在同一个格子里显示默认逗号分割)，以姓名分组
            -- 例：SELECT name,GROUP_CONCAT(score ORDER BY score DESC   SEPARATOR ';') FROM user GROUP BY `name`;
            -- 解释：查询姓名，该姓名的所有分数,同时指定分数排序方式(在同一个格子里显示)，指定以';'分割，以姓名分组

e. HAVING 子句，条件子句(概括：having可以接聚合函数，用于groupby 后；where只能接数据库存在的值，一般用where)
    与 where 功能、用法相同，执行时机不同。
    where 在开始时执行检测数据，对原数据进行过滤。
    having 对筛选出的结果再次进行过滤。
    having 字段必须是查询出来的，where 字段必须是数据表存在的。
    where 不可以使用字段的别名，having 可以。因为执行WHERE代码时，可能尚未确定列值。
    where 不可以使用合计函数。一般需用合计函数才会用 having
    SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。
        -- 例：SELECT `name`,sum(money) FROM user GROUP BY `name` HAVING sum(money)>2000;
        -- 解释：查询姓名，总钱 通过姓名分组，并且分组之后总金额大于2000的

f. ORDER BY 子句，排序子句
    order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]...
        升序：ASC，降序：DESC

g. LIMIT 子句，限制结果数量子句(即分页查询)
    仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合，按照记录出现的顺序，索引从0开始。
    limit 起始位置, 获取条数(第一行索引为0)
        省略第一个参数，表示从索引0开始。limit 获取条数
            -- 例：SELECT * FROM user LIMIT 0,3; 
            -- 解释：从索引0(第一个)开始，总共查3个
h. DISTINCT, ALL 选项
    distinct 去除重复记录
        默认为 all, 全部记录

七、UNION(注意与join区别)

1
2
3
4
5
6
7
8
9
/* UNION */ ------------------
    将多个select查询的结果组合成一个结果集合。(两张表的两个列合并为同一个列)
    SELECT ... UNION [ALL|DISTINCT] SELECT ...
        默认 DISTINCT 方式，即所有返回的行都是唯一的(去重)
        -- UNION 语句：用于将 不同表 中相同列中查询的数据展示出来；（不包括重复数据）
        -- UNION ALL 语句：用于将 不同表 中相同列中查询的数据展示出来；（包括重复数据）
            -- 例：SELECT name FROM user UNION ALL (SELECT username FROM t_user);
            -- 解释：两张表的name列与username列放到临时表的名为name(名字以前面的为准)的列中
         每个select查询的字段列表(数量、类型)应一致，因为结果中的字段名以第一条select语句为准。

八、子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 子查询需要用括号包括
-- from型
    from后要求是一张表或是子查询产生的临时表，
        - 如果是生成的临时表，则必须给子查询产生的临时表取别名
        -- 例：select * from (select * from user where id>1) as another_table where id>2;
        -- 解释：user表中id>1的组成一张临时表，取别名another_table，再查询这张临时表中id>2的。
			
-- where型
    - where后的子查询一个或多个值
    - 可以不用给子查询起别名
    - where子查询的表不能用于更新删除
        -- 例：SELECT * FROM user WHERE money = (SELECT max(money) FROM `user`);
        -- 解释： 查询金额最多用户的所有信息
        
        -- 列子查询
            字查询的结果是一列
            使用in或not in完成查询
                 -- 例：SELECT * FROM user WHERE name in (SELECT `username` FROM t_user);
                 -- 解释：查询名字在t_user表中的人的信息
             或使用exists 和 not exists 条件，返回1或0。常用于判断条件。
                 -- 例：SELECT * FROM user WHERE EXISTS (SELECT * FROM t_user WHERE id>3);
                 -- 解释：子查询：如果id>3的查询结果有值，则EXISTS返回1(true),相当于只有主查询
	    -- 行子查询
            子查询的的条件(结果)是一个行
            -- 例：select * from user where (name, age) in (select name, age from t_user);
            -- 解释：子查询的结果要匹配的条件有两个(name,age),一行
        -- 特殊运算符
        != all()    相当于 not in
        = some()    相当于 in。any 是 some 的别名
        != some()   不等同于 not in，不等于其中某一个
        all, some 可以配合其他运算符一起使用。

九、连接查询(join)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 将多个表的字段拼接成一张表
-- 内连接(inner join)
    - 内连接只展示所有on后面条件成立的列，不以哪一张表为准
    - 默认即是内连接，可以省略inner。
    - 只有数据存在时才能发送连接。即连接结果不能出现空行。
    on 表示连接条件。其条件表达式与where类似。也可以省略条件（表示条件永远为真）
    where 也可用where表示连接条件。
    还有 using, 但需字段名相同。 using(字段名)
        -- 例：SELECT * FROM user JOIN t_user on user.`name`=t_user.username;
        -- 解释：将user、t_user中name与username相同的列拼接到一张临时表(不以其中一张表为准)，不同的列都不展示
    -- 交叉连接 cross join
        即，没有条件的内连接。
        select * from tb1 cross join tb2

-- 外连接(outer join)
    - 如果数据不存在，也会出现在结果中
    -- 左外连接 left join
        以左边的表为准，左边的表的数据全部展示，如果右边表的数据如果不存在，用null填充。
            -- 例：SELECT * FROM user left JOIN t_user on user.`name`=t_user.username;
            -- 解释：将左表user的数据全部展示，右表t_user中name与username相等的列拼接展示，如果不等处的值填null。
    -- 右外连接 right join
        以右边的表为准，右边的表的数据全部展示，如果左边表的数据如果不存在，用null填充。

-- 自然连接(natural join)
    自动判断连接条件完成连接。
    相当于省略了using，会自动查找相同字段名。
    natural join
    natural left join
    natural right join
select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id;

十、TRUNCATE

1
2
3
4
5
6
7
8
9
10
11
/* TRUNCATE */ ------------------
TRUNCATE [TABLE] tbl_name
    -- 例：TRUNCATE TABLE test ;
    -- 解释：删除test表中的所有数据(但保留表结构)，类似如下两个步骤
a.清空数据
b.删除重建表
区别：
1，truncate 是删除表再创建，delete 是逐条删除
2，truncate 重置auto_increment的值。而delete不会
3，truncate 不知道删除了几条，而delete知道。
4，当被用于带分区的表时，truncate 会保留分区
本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可