为你整理了一份 Mysql 的学习笔记,建议收藏学习!

为你整理了一份 Mysql 的学习笔记,建议收藏学习!

为你整理了一份 Mysql 的学习笔记,建议收藏学习!

作者 | 陈熹

责编 | Carol

来源 | 早起Python

SQL是一个存活近半个世纪的语言,如今仍有大量人在使用。它语法简单,对培养数据整理和提取的思维有很大帮助。我将我过去的笔记分享给大家,希望能为大家的学习提供参考,更希望有人因此能迈出学习SQL的第一步~

既然是笔记,就省去了很多介绍性定义,一切都在代码在注释中!这一万字符的笔记中包含了SQL中的全部常用操作,可以 边看边敲 也可以收藏当成 速查手册 随用随查!

1、连接Mysql

mysql -h$ip -P$port -u$user -p net start mysql # 或 mysql.server start mysql -u root -p  mysql -u user -p db_name # 直接进入指定数据库

2、导入导出

导出现有数据库数据: mysqldump -u用户名 -p密码 数据库名称 >导出文件路径           # 结构+数据 mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径       # 结构  导入现有数据库数据: mysqldump -uroot -p密码  数据库名称 <导入文件路径

3、数据库操作

SELECT version(), current_date, now(); # 查看mysql版本号和日期、时间 SELECT version(); select now(); select current_date; # 分开写则表格分开  SOURCE c://test.sql # 用txt文件保存命令该后缀名 可直接执行文件内的命令
/c # 撤销先前输入  /G # 按行输出 SHOW DATABASES; # 查看所有数据库 SELECT DATABASE(); # 进入数据库后查看当前数据库 # 数据库名称组成除了三大项还可以含$,但不能是纯数字 CREATE DATABASE db_name; # 创建spiders数据库 CREATE DATABASE db_name DEFAULT CHARSET utf8 COLLATE utf8_general_ci;  # 安装utf8规则排序 # utf8mb4支持墨迹表情 USE db_name; # 进入数据库 ALTER DATABASE db_name DEFAULT CHARSET SET utf8;  # 修改数据库  DROP DATABASE db_name; # 删除数据库  DROP VARIABLES LIKE 'datadir'; # 查看数据库所在位置
SHOW TABLES; # 查看所有表 DESCRIBE db_name; # 查看表的描述,也可以用 DESC table; SELECT * FROM db_name; # 查看表中所有数据 SELECT host,user FROM db_name; # 大小写不敏感 INSERT INTO person(name,birth) VALUES('A',1994-01-01); # VALUE也可

4、表操作:创建表

CREATE TABLE students(     nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,  # 自增列必须是索引,最好是主键     name VARCHAR(20),     num INT NOT NULL DEFAULT 2)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

5、主键

一张表只能有一个主键,值是唯一的(或多列组合是唯一的),不能重复不能为空,一般情况下自增列设置为主键。 常用 nid INT NOT NULL AUTO_INCREMENT PRIMARY KEY 一张表可以多个唯一列

CREATE TABLE tb1(                 nid INT NOT NULL,                 num INT NOT NULL,                 PRIMARY KEY(nid,num) # 两列组成一个主键             )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

主键的作用:

  • 约束

  • 索引,加速查找

6、外键

# 创建表时创建 CREATE TABLE color(     INT NOT NULL PRIMARY KEY,     name CHAR(16) NOT NULL     )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;;  CREATE TABLE fruit(     INT NOT NULL PRIMARY KEY,     smt CHAR(32) NOT NULL ,     color_id INT NOT NULL,     CONSTRAINT fk_fruit_color FOREIGN KEY (color_id) REFERENCES color(nid)     )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;; # 表外创建 ALTER TABLE students ADD CONSTRAINT fk_tb2_tb1 FOREIGN KEY tb2(info) REFERENCES tb1(nid);

7、修改表属性

# 添加列: ALTER TABLE 表名 ADD 列名 类型 # 删除列: ALTER TABLE 表名 DROP COLUMN 列名  # 修改列: ALTER TABLE 表名 MODIFY COLUMN 列名 类型;  一般只改类型 ALTER TABLE 表名 CHANGE 原列名 新列名 类型; 可改列名+类型    # 添加主键: ALTER TABLE 表名 ADD PRIMARY KEY(列名); # 删除主键: ALTER TABLE 表名 DROP PRIMARY KEY; ALTER TABLE 表名  MODIFY 列名 INT, DROP PRIMARY KEY;    # 添加外键: ALTER TABLE 从表 ADD CONSTRAINT 外键名称(形如:FK_从表_主表)FOREIGN KEY 从表(外键字段) REFERENCES 主表(主键字段); # 删除外键: ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;    # 修改默认值: ALTER TABLE tbl ALTER列名 SET DEFAULT 1000; # 删除默认值: ALTER TABLE tbl ALTER列名 DROP DEFAULT; 

8、数据类型

  • bit(M) : 

二进制位,M为1~64,默认M=1

  • int :

nid int unsigned最大4GB (2**32 − 1)

整数类型中的m仅用于显示,对存储范围无限制

tinyint: 有符号-128127,无符号0255,默认是signed

MySQL中无布尔值,使用tinyint(1)构造

bigint

smallint

  • decimal: 

精确的小数,能够存储精确值的原因在于其内部按照字符串存储

num decimal(6,2)   6个有效数字(总长度),2位小数,最大是65和30

float,double: 不精确

  • char :

唯一定长,其他都是变长,数据不到该长度其他空间会闲置,查找速度快,浪费空间

varchar: 变长,相对于char效率低。查找下一列时不确定跳多少存储空间

字符最大是255

text:字符最大是65535 2**16-1

mediumtext:2**24-1

longtext:2**32-1

  • 二进制数据 :

TinyBlob、Blob、MediumBlob、LongBlob

上传文件,blob强制二进制方式。现在多用varchar保存路径,上传文件保存在硬盘

  • 时间

DATE YYYY-MM-DD(1000-01-01/9999-12-31) TIME HH:MM:SS('-838:59:59'/'838:59:59') YEAR YYYY(1901/2155) DATETIME YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59    Y) TIMESTAMP YYYYMMDD HHMMSS(1970-01-01 00:00:00/2037 年某时)
  • 枚举 enum

支持65535个元素枚举

CREATE TABLE shirts (  name VARCHAR(40),  size ENUM('x-small', 'small', 'medium', 'large', 'x-large'));   INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');
  • 集合 set

CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

9、表内容操作

# 写的顺序 SELECT... FROM... WHERE... GROUP BY... HAVING... ORDER  BY...  LIMIT...  # 执行顺序 FROM... WHERE... GROUP BY... SELECT... HAVING... ORDER BY... LIMIT...

10、表内容操作—增

INSERT INTO 表 (列名,列名...) VALUES(值,值,值...); INSERT INTO  表 (列名,列名...) VALUES(值,值,值...),(值,值,值...); # 增加多条数据  INSERT INTO  表 (列名,列名...) SELECT 列名,列名 FROM 表;  # 后面选择列不需要空格 # 如果数据可以转则允许互转 insert into students(name,age) SELECT caption,nid FROM tb2 WHERE nid>2;

11、表内容操作—删

DELETE FROM 表; DELETE FROM 表 WHERE id=1 AND name='alex'; DELETE FROM 表 WHERE id > 1 OR name='alex';  # 删除和清空 DROP TABLE student; # 删除表 DELETE FROM student; # 清空表,有自增列则清空后自增记忆存在 TRUNCATE (TABLE) student;# 快速清空表,有自增列则清空后自增从1重新开始

12、表内容操作—改

UPDATE students SET name = 'alex' WHERE id>1;  UPDATE salary SET sex = IF(sex = 'm', 'f', 'm')  # 条件判断更改 UPDATE salary SET sex = char(ascii('m') + ascii('f') - ascii(sex)); UPDATE salary  SET     sex = CASE sex          WHEN "m" THEN "f"          ELSE "m" END;          # UPDATE和JOIN UPDATE A JOIN B ON A.URL = B.URL SET member_id = '00012138'  WHERE LOGIN_time BETWEEN '2019' AND '2020'  AND B.class_id = 'TNT'; # BETWEEN 后可以再跟AND

13、表内容操作—查

SELECT * FROM 表; # 要全部列可以把列名写一遍,效率比*高 SELECT * FROM 表 WHERE id <> 1; # 即!= SELECT * FROM 表 WHERE id BETWEEN 3 AND 5;  # 即 id>=3 AND id<=5 SELECT nid,name,gender FROM 表 WHERE id%2 = 1;  SELECT DISTINCE name FROM score WHERE num < 60; # distinct 去重只保留各组一项 也可以使用group by  WHERE (class, name) NOT IN (SELECT class, name FROM B) # 多个字段限制

14、表内容操作—条件选择

SELECT * FROM 表 WHERE id BETWEEN 5 AND 16; # 也支持多个并列 SELECT * FROM 表 WHERE id IN(11,22,33) SELECT * FROM 表 WHERE id NOT IN(11,22,33) SELECT * FROM 表 WHERE id IN(select nid from 表)

15、表内容操作—通配符模糊匹配

SELECT * FROM 表 WHERE name LIKE 'ale%'  # - ale开头的所有(多个字符串) where ... like '' SELECT * FROM 表 WHERE name LIKE 'ale_'  # - ale开头的所有(1个字符) SELECT * FROM 表 WHERE name LIKE '_le%'

16、表内容操作—限制/分页

SELECT * FROM 表 LIMIT 5;            # 前5行 SELECT * FROM 表 LIMIT 4,5;          # 从第4行下一行开始的5行 SELECT * FROM 表 LIMIT 5 OFFSET 4    # 从第4行开始的5行,与上一条功能一样

17、表内容操作—排序

SELECT * FROM 表 ORDER BY 列 ASC             # 根据 “列” 从小到大排列 SELECT * FROM 表 ORDER BY 列 DESC            # 根据 “列” 从大到小排列 SELECT * FROM 表 ORDER BY 列1 DESC,列2 ASC   # 根据 “列1” 从大到小排列,如果相同则按列2从小到大排序,否则一列相同时其他列默认升序排序

18、表内容操作—分组+聚合

# group by获取各组第一行作为标识,其余行丢弃 SELECT num FROM 表 GROUP BY num  # 分组的原理默认是升序排序,也可以降序 SELECT num FROM 表 GROUP BY num DESC  SELECT count(1) FROM 表 GROUP BY num # 也可使用 SELECT count(name) FROM students GROUP BY classid # count(1) 和 count(name)区别 # 如果name中有null则count(name)不记录,其余时候二者完全一样 SELECT count(distinct name) FROM students GROUP BY classid # 去重  # sum只能对int类型计算 否则是0  # 多列分组 多个均相同的分到一组 SELECT num,nid FROM 表 GROUP BY num,nid SELECT num,nid FROM 表  WHERE nid > 10 GROUP BY num,nid ORDER BY nid DESC SELECT num,nid,count(*) AS count,sum(score) AS sum,avg(score),max(score),min(score) FROM 表 GROUP BY num,nid  # as 是自定义命名   # 如果要对聚合函数进行筛选需引入having 顺序在group by后 SELECT num FROM 表 GROUP BY num HAVING max(id) > 10 # 聚集函数 count   sum   max   min   avg   group_concat(字符串拼接) # 都会去除null # sum(1) 等于 count(1) 只能针对int类型  # group_concat SELECT id,GROUP_CONCAT(name) FROM aa GROUP BY id; SELECT id,GROUP_CONCAT(name SEPARETOR ';') FROM aa GROUP BY id;   SELECT id,GROUP_CONCAT(DISTINCT name) FROM aa GROUP BY id;   SELECT id,GROUP_CONCAT(name ORDER BY name DESC) FROM aa GROUP BY id; # 语法如下 DISTINCT name ORDER BY id DESC SEPARETOR '-'

19、表内容操作—组合

# 纵向组合,并以第一个表字段为准 # 组合,自动处理重合 SELECT nickname FROM A UNION SELECT name FROM B;   # 组合,不处理重合 SELECT nickname FROM A UNION ALL SELECT name FROM B;

20、表内容操作—连表

# 需要多行之间满足一个需求就需要join  # 笛卡尔积 SELECT * FROM students,disc_info;  # 根据对应关系连表,实际等同于inner join SELECT * FROM students,disc_info WHERE students.discipline = disc_info.nid; SELECT students.name,disc_info.discipline FROM students,disc_info WHERE students.discipline = disc_info.nid;  # join 左右连接如果无对应关系显示NULL,join效率高  # 取交集,inner join,也可以理解成过滤掉含NULL数据行的左右连接 SELECT A.num, A.name, B.name FROM  A INNER JOIN B  ON A.nid = B.nid;  # 右连接,right join SELECT A.num, A.name, B.name FROM  A RIGHT JOIN B  ON A.nid = B.nid  # 左连接,right join SELECT A.num, A.name, B.name FROM  A LEFT JOIN B  ON A.nid = B.nid

21、表内容操作—CASE WHEN 判断

CASE WHEN 表达式     THEN 输出 WHEN 表达式 # 多少个WHEN都可以     THEN 输出 ELSE     输出 END  SELECT id,name, (     CASE WHEN classid = 1 THEN 2     WHEN classid =2 THEN 1     ELSE classid END ) AS clid FROM stu;   # 把tidydata转为正常数据 SELECT name, MAX(CASE WHEN project = '基础' THEN score ELSE NULL END) as '基础', MAX(CASE WHEN project = '爬虫' THEN score ELSE NULL END) as '爬虫', MAX(CASE WHEN project = 'SQL' THEN score ELSE NULL END) as 'SQL' FROM score;   # 查询两门及以上不及格同学信息 SELECT st.Name, AVG(score) as Score_n FROM SC JOIN student st ON SC.SId = st.SId GROUP BY SId HAVING COUNT(CASE WHEN Score < 80 THEN 1 ELSE NULL END) >=2;

22、表内容操作—半连接

SELECT     id,NAME,classid FROM     student tf WHERE     id = (         SELECT             max(id)         FROM             student ts         WHERE             ts.classid = tf.classid     );      # 也可以用常规方法 SELECT     id,NAME,classid FROM (         SELECT             max(id)         FROM             student         GROUP BY  classid     );

23、表内容操作—索引

# 基于B+树 # 数据分的越开的列则建索引效果越好 # OR情况不能用索引 # 如果是联合索引 前部过滤条件可以做为索引 # 在SQL语句前加EXPLAIN就可以明确是否走索引  # 创建索引 CREATE INDEX name_index ON student(name);  # 聚簇索引叶子节点跟着数据,非聚簇索引叶子节点跟着主键(聚簇索引) # 非聚簇走完多数会再走聚簇,除非SELECT内容均被包含于索引(全覆盖索引) # MySQL中主键是聚簇索引,其他均为非聚簇索引 # 如果没有主键,内部会虚拟一个AUTO_INCREMENT的主键

24、表内容操作—视图

CREATE VIEW V1 AS  # 反复利用某个临时表则可以创建视图  SELECT * FROM stu WHERE id > 10; # 视图是一个动态表,会从物理表动态读出来。但无法直接对虚拟表即视图修改 # 修改视图 ALTER VIEW V1 AS SELECT * FROM stu WHERE id > 20; # 删除视图 DROP VIEW V1;

25、表内容操作—触发器

DELIMITER // # 修改终止符 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT INTO tb1 FOR EACH ROW BEGIN     INSERT INTO tb2(name) VALUES('chenx');      INSERT INTO tb2(name) VALUES(NEW.sname);  # 指代新插入的一行,OLD可以用在DELETE和UPDATE END // DELIMITER ; # 修改回原终止符  # BEFORE可以换成AFTER,INSERT可以换成DELETE或者UPDATE  # 删除触发器 DROP TRIGGER tri_before_insert_tb1;

26、表内容操作—函数

# 内置函数 SELECT CURDATE(); # 执行函数的用法 SELECT DATE_FORMAT(ctime, "%Y-%m") FROM stu GROUP BY DATE_FORMAT(ctime, "%Y-%m") # 时间格式化  # 自定义函数 DELIMITER // CREATE FUNCTION f1(     i1 INT,     i2 INT) RETURNS INT# 强类型语言 BEGIN     DECLARE num INT DEFAULT 0; # 声明变量     SET num = i1 + i2;     RETURN(num); END // DELIMITER ;

为你整理了一份 Mysql 的学习笔记,建议收藏学习!

推荐阅读

  • 阿里技术专家告诉你,如何画出优秀的架构图?

  • 一文带你认识keepalived,再带你通关LVS+Keepalived!

  • 那个分分钟处理 10 亿节点图计算的 Plato,现在怎么样了?

  • “谷歌杀手”发明者,科学天才 Wolfram

  • 数据库激荡 40 年,深入解析 PostgreSQL、NewSQL 演进历程

  • 超详细!一文告诉你 SparkStreaming 如何整合 Kafka !附代码可实践

  • 5分钟!就能学会以太坊 JSON API 基础知识!

    真香,朕在看了!

更多互联网行业动态,请大家继续关注营销星球,每天更新互联网相关的各类信息,让你掌握最新动态。

未经允许不得转载:营销星球 » 为你整理了一份 Mysql 的学习笔记,建议收藏学习!

赞 (0)

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址