MySQL基础知识大总结

男娘i 2021-12-15 13:41 269阅读 0赞

> 前言：本文主要为mysql基础知识的大总结，mysql的基础知识很多，这里作简单概括性的介绍，具体的细节还是需要自行搜索。当然本文还有很多遗漏的地方，后续会慢慢补充完善。

## 数据库和数据库软件 ##

*  数据库是保存有组织数据的容器
 *  DBMS是为管理数据库而设计的软件管理系统，MYSQL、ORACLE 等是数据库管理系统

## MYSQL ##

*  MYSQL是一种数据库管理软件
 *  开放源码，免费使用

## MYSQL命令 ##

*  CREATE DATABASE NAME 创建数据库
 *  USE DATABASE 选择数据库
 *  DROP DATABASE NAME 直接删除数据库
 *  SHOW DATABASE 显示可用数据库列表
 *  SHOW TABLE 显示数据库中的表的列表
 *  SHOW COLUMNS FROM TABLE 与 DESCRIBE TABLE 等效，显示表的字段信息
 *  SHOW GRANTS 显示授予用户的安全权限
 *  SHOW ERRORS SHOW WARNINGS 显示服务器错误和警告信息

## SQL ##

*  STRUCTURED QUERY LANGUAGE 结构化查询语言，一种专门用于与数据库通信的语言
 *  不是DBMS专有的语言，很多DBMS都支持SQL，但是不同DBMS对SQL的实现不同
 *  DBMS支持的SQL语法不能完全适用于其他DBMS
 *  SQL语句不区分大小写
 *  多条语句需要分号分隔，单条语句可以不用分号
 *  通常SQL语句用大写，标识符（比如表名 列名 数据库名）用小写

## 创建表 ##

*  CREATE TABLE user (id INT NOT NULL AUTO\_INCREMENT, username VARCHAR(50) NOT NULL,password VARCHAR (50) NOT NULL DEFAULT 1,PRIMARY KEY(id)) ENGINE =INNODB

## 检索列 ##

*  SELECT username FROM user
 *  SELECT username,password FROM user
 *  SELECT \* FROM user 建议不是要所有的列，不用'\*',要不然这种操作是很耗性能的
 *  SELECT DISTINCT username FROM user 只返回不同的username，比如有两行用户名是一样的，只显示一行记录

## 限制结果 ##

*  SELECT \* FROM TABLE LIMIT 5 检索前五行
 *  SELECT \* FROM TABLE LIMIT 5,5 检索6到10
 *  SELECT \* FROM TABLE LIMIT 5,-1 检索6到最后一行

## 结果排序 ##

*  SELECT \* FROM user ORDER BY username,以username的字母顺序排列
 *  SELECT \* FROM user ORDER BY username,password ,如果username有相同的，那么username相同的再按password排列，如果username都是唯一，这个时候password不起作用
 *  SELECT \* FROM user ORDER BY username DESC ，降序排列，默认是升序ASC
 *  SELECT \* FROM user e ORDER BY username DESC,password 此时按照username降序排列，如果username相同的行按照password升序排列
 *  SELECT \* FROM user ORDER BY username LIMIT 1 混合使用ORDER 和 LIMIT
 *  SELECT \* FROM user WHERE username ='jiajun' ORDER BY password 这里ORDER要在WHERE 之后

## 过滤数据 ##

*  SELECT \* FROM user WHERE username <> 'jiajun' 不等于 和 ！= 效果相同
 *  SELECT \* FROM user WHERE id BETWEEN 1 AND 10 检索1到10的记录，包括1和10
 *  SELECT \* FROM user WHERE username IS NULL 查找列无值的行，这里的无值不等于 值为0 和 空字符串
 *  SELECT \* FROM user WHERE id=1 OR username='jiajun' AND password='666' 执行顺序是 WHERE id=1 OR (username='jiajun' AND password='666')AND的优先级更高，但是建议添加括号
 *  SELECT \* FROM user WHERE id IN (1,2) 效果和SELECT \* FROM user WHERE id=1 OR id =2 如果要实现这种效果，建议使用第一种，语法更清晰，而且一般执行更快
 *  SELECT \* FROM user WHERE id NOT IN (1,2)

## 通配符过滤 ##

*  SELECT \* FROM user WHERE username LIKE 'jia%' ，匹配jia开头的username，不管后面有多少字符，jia和jiajun都是匹配，可以配置MySQL是否区份大小写，如果区份大小写，也就是jiajun是不匹配的 。注意LIKE '%'是不能匹配值为NULL的
 *  SELECT \* FROM user WHERE username LIKE 'jia\_' , \_只能匹配一个字符，也就是匹配jiaj不匹配jiajun
 *  通配符匹配是效率不高，如果其他方式能有相同的效果，建议用其他效果，并且最好不要将通配符放在开始处，因为这样是很慢的

## 正则表达式过滤 ##

*  SELECT \* FROM user WHERE username REGEXP '.ia' ,正则表达式， . 匹配任意一个字符
 *  SELECT \* FROM user WHERE username REGEXP 'jun' 匹配包含jun的username，'jiajun'和'jun'都匹配
 *  SELECT \* FROM user WHERE username REGEXP 'jiajun|jiaj' ,'|'和'OR' 效果一样
 *  SELECT \* FROM user WHERE username REGEXP ‘\[123\]jun’ ，匹配包含'1jun'或者'2jun'或者'3jun'
 *  \[^123\]123除外
 *  \[1-9\],\[A-Z\],用'-'表示范围
 *  匹配特殊字符（比如'.'和'\_'）可以采用'\\'转义，比如匹配有'.'的可以这样表示'\\.'

## 拼接字段 ##

*  SELECT CONCAT(username,'(',password,')') FROM user,将两个字段拼接到一起，查询结果是一列，行值 jiajun(666)
 *  SELECT CONCAT(username,')',password,')') AS up FROM user,为拼接后的一列设置字段名

## 算数运算 ##

*  SELECT price\*count AS all FROM TABLE 将单价和数量相乘，列名为all

## 函数 ##

*  函数可移植性不高，也就是一个函数支持一个DBMS，但不一定支持另一个DBMS，比如支持MYSQL而不支持ORACLE。所以使用函数的要写好注释。

## 文本处理函数 ##

*  LENGTH() 返回串长度
 *  LOWER()将串转换为小写
 *  UPPER()将串转换为大写
 *  LTRIM()去掉串左边的空格
 *  RTRIM()去掉串右边的空格

## 日期和时间处理函数 ##

*  CURDATE() 2017-07-22
 *  CURTIME()16:20:19
 *  NOW() 2017-07-22 16:20:19
 *  DATE() 返回日期的日期部分 SELECT DATE(NOW()) 2017-07-22
 *  DATE\_FORMAT() 返回格式化的日期和时间串
 *  HOUR() 返回一个时间的小时部分
 *  MINUTE() 返回一个时间的分钟部分
 *  MONTH() 返回一个日期的月份部分
 *  NOW() 返回当前日期和时间
 *  SECOND() 返回一个时间的秒部分
 *  TIME() 返回一个日期时间的时间部分
 *  YEAR() 返回一个日期的年份部分

## 日期函数注意点 ##

*  SELECT \* FROM TABLE WHERE date='2017-7-22' ,如果DATE的类型是DATATIME类型，那么表里一条记录存放的格式应该是2017-07-22 16:20:19，此时这条记录不会被筛选出来，筛选的是2017-07-22 00:00:00
 *  可以采用DATE函数 SELECT \* FROM TABLE WHERE DATE(date)='2017-7-22'
 *  时间范围 可以采用DATE函数 SELECT \* FROM TABLE WHERE DATE(date) BETWEEN '2017-7-1' AND ''2017-7-31

## 数组处理函数 ##

*  ABS() 返回一个数的绝对值 COS()
 *  返回一个角度的余弦 EXP()
 *  返回一个数的指数值 MOD()
 *  返回除操作的余数 PI()
 *  返回圆周率 RAND()
 *  返回一个随机数 SIN()
 *  返回一个角度的正弦
 *  SQRT() 返回一个数的平方根
 *  TAN() 返回一个角度的正切

## 聚集函数 ##

*  SELECT AVG(student\_score) AS AVG\_ PRIVE FROM score 求平均值
 *  SELECT COUNT(\*) FROM TABLE 表里记录数
 *  SELECT COLUMN(name) FROM TABLE 表里列有值的记录数 ，值为NULL不计数
 *  SELECT MAX(score) FROM TABLE 查找最大值
 *  SELECT MIN(score) FROM TABLE 查找最小值
 *  SELECT SUM(count) FROM TABLE 返回指定列的和 SUM(price\*count)同样适用

## 数据分组 ##

*  SELECT AVG(score) FROM score GROUP BY class\_id 检索不同课的平均分
 *  SELECT AVG(score) FROM score GROUP BY class\_id, dep\_id ,GROUP BY 后面可以多个字段
 *  SELECT AVG(score) FROM score GROUP BY class\_id ，SELECT 的字段必须是GROUP BY 后面的字段或者是聚合函数，在这里根据class\_id分最后，class\_id相同合并成一行，但是这里面的合并的这些score不同，如何能合并成一行
 *  如果分组列有NULL值的，将列值为NULL的分为一组
 *  GROUP BY 要在WHERE之后，ORDER BY 之前，也就是先过滤再分组再排序
 *  SELECT COUNT(\*) FROM TABLE GROUP BY id HAVING COUNT(\*)>3 ,分组后，对分组再进行过滤。

## 子查询 ##

*  SELECT \* FROM student WHERE class\_id IN (SELECT class\_id FROM teacher WHERE teacher\_id=6) ,子查询过滤，WHERE里面嵌套子查询
 *  SELECT name ,(SELECT score FROM score WHERE student\_id=class\_id) AS score FROM student 字段作为子查询
 *  不建议使用太多的子查询，会影响性能

## 主键 ##

*  唯一标识自己的一列或一组列，唯一区别表中的一行
 *  主键不能为空
 *  任意两行的主键值必须不同
 *  主键不是必须的，但是建议每个表中有一个主键，这样操作管理更方便
 *  主键值最好不更新
 *  一个表的主键只能有一个

## 外键 ##

*  一个表的主键指向另一个表的外键，比如说student表的主键student\_id，在score表中也有，并且是score表的外键
 *  外键保持了数据完整性和一致。，比如你在student表里面修改了student\_id后，则score的student\_id也会联动更新。并且score表中插入的student\_id必须是student表里有的

## 联结 ##

*  SELECT NAME,score FROM score，student 两个表进行联结，此时进行的是笛卡尔积，就是说结果的行数score表行数\*student表的行数
 *  SELECT NAME,score FROM score，student WHERE student.student\_id=score.student\_id，在上面的基础上，加上WHERE进行过滤
 *  SELECT NAME,score FROM score INNER JOIN student ON student.student\_id=SOCRE.student\_id 内部联结
 *  外部联结，LEFT OUTER JOIN 和 RIGHT OUTER JOIN ，有时候内部联结的时候会出现联结条件不匹配的行，而LEFT JOIN 保证了保证左边表的所有行，右联结同理
 *  FULL JOIN效果LEFT JOIN + RIGHT JOIN一致，左表不匹配的右表不匹配的都会列出
 *  不要联结太多的表，这样会降低性能
 *  联结的时候可能因为两个表有相同的列，因为没有做好限制导致相同的列的出现两次，所以这是需要的地方，这也是所谓的自然联结

## 别名 ##

*  SELECT CONCAT(username,'(',password,')') AS MES FROM user,这里采用CONCAT将两个字段拼接在一起，并且给拼接后的字段起一个别名MES
 *  同样表也可以起别名SELECT name,score FROM score AS A，student AS B WHERE A.student\_id=B.student\_id

## 组合查询 ##

*  利用UNION将多个SELECT语句的结果组合起来，可以理解成同一个表头的表垂直拼接在一起
 *  每个查询必须包含相同的列，表达式和聚集函数，而且字段类型要兼容。
 *  SELECT score FROM score WHERE NAME='jiajun' OR score BETWEEN 95 AND 100 和 SELECT score FROM score WHERE NAME='jiajun' UNION SELECT score FROM score WHERE score BETWEEN 95 AND 100 作用等效。
 *  上面的是单表查询，用了UNION感觉复杂了，但是如果用于不同表的查询的连接会更简单。
 *  如果A查询查到5行，B查询查到4行，由于有重复的，会去掉相同的行，最后剩下8行，如果需要的话可以用UNION ALL

## 插入数据 ##

*  INSERT INTO user VALUES ('jiajun','666') 和 INSERT INTO user (username,password) VALUES ('jiajun','666'),在表里只有username和password两个字段是等效，前一种方式必须值的个数和顺序必须和字段的个数顺序一致，而后者，因为给出列和值，只要一一对应就好
 *  INSERT INTO user VALUES ('jiajun','666'),('jiajia','666') 插入多行
 *  INSERT INTO user VALUES (SELECT username ,password FROM olduser),检索出olduser的行然后插入到user表，这里注意的还是列的问题，后面的SELECT语句后的字段名并不重要，不需要和user表对应，因为只是将检索的列值按顺序插入到user表，并不在意olduser的字段名。同时列的数目和顺序也是需要注意的

## 更新数据 ##

*  UPDATE user SET password ='666',money='6666' WHERE username='jiajun',需要注意的是WHERE一定不要漏，要不然会更新表中的所有行
 *  在更新多行的时候如果中途出现错误，会将更新的恢复回原来的值，如果要做到即使中途发生错误也要继续更新可以采用 IGNORE关键字，UPDATE IGNORE user

## 删除数据 ##

*  如果想删除一个列的值，SET username=NULL就行了
 *  如果想要删除一行，DELETE FROM user WHERE username='jiajun'
 *  如果想要删除整个表的行 DETELTE FROM user，注意这个表不会删除，只是所有记录清空。TRUNCATE user，也有相同的效果，不同的是他是先删除表，然后重新建立一个表

## 更新表 ##

*  ALTER TABLE user ADD phone CHAR(20) 添加一列
 *  ALTER TABLE user DROP COLUMN phone 删除一列
 *  ALTER TABLE user CONSTRAINT wai\_jian FOREGIN KEY (class\_id) REFERENCES class (class\_id) 定义外键
 *  ALTER TABLE user ADD PRIMARY KEY (id)添加主键
 *  修改前做好备份，表的更改不能撤销

## 删除表 ##

*  DROP TABLE user

## 重命名表 ##

*  RENAME TABLE user TO users

## 视图 ##

*  当我们查询后出现一个结果，我们可以包装成一个虚拟表，也就是视图，我们可以把他当成表使用
 *  视图本身不包含数据，数据是从其他表检索出来
 *  使用视图可以重用SQL，并且可以保护数据，可以授予用户部分数据权限而不是全部数据
 *  如视图中存在分组(GROUP BY)、联结、子查询、并(UNOIN)、聚合函数(SUM/COUNT等)、计算字段、DISTINCT等都不能对视图进行更新操作
 *  CREATE VIEW my\_view AS SELECT name，score FROM student ，创建视图
 *  DROP VIEW MY\_VIEW 删除视图

## 存储过程 ##

*  有时候SQL也需要有IF ELSE，我们可以把多条SQL语句封装在一起形成存储过程，这样不仅简单安全而且性能也会更高
 *  存储过程并不显示结果，只是将结果返回给你指定的变量。
 *  过程是这样的，创建一个存储过程，使用存储过程，将参数传入，SELECT参数输出结果
 *  参数类型 IN 传递给存储过程，OUT从存储过程传出，INOUT对存储过程传入传出。结果将返回给OUT变量
 *  CREATE PROCEDURE pro(IN PARAM INT,OUT PARAM2 INT)BEGIN SELECT COUNT(\*) FROM user WHERE id=PARAM INTO PARAM2 END; 创建存储过程
 *  CALL PRO (666，@PARAM2)；SELECT @PARAM2；调用并且会输出PARAM2
 *  SHOW CREATE PROCEDURE PRO显示存储过程的CREATE语句
 *  SHOW PROCEDURE STATUS列出所有存储过程

## 触发器 ##

*  事件发生时自动执行某些语句，在INSERT UPDATE DELETE之前之后需要做一些操作，这时候可以使用触发器
 *  一个表最多6个触发器，插入删除更新的前后。
 *  CREATE TRIGGER my\_trigger AFTER INSERT ON user FOR EACH ROW BEGIN SELECT NEW.id END，创建名为my\_trigger的触发器，在对user表，每插入一行，将id显示出来
 *  在触发器中可以引用NEW新的虚拟表，访问插入的行。可以引用OLD虚拟表，访问被删除的行。
 *  DROP TRIGGER my\_trigger 删除触发器
 *  只有表支持触发器，视图不支持
 *  触发器中不能调用存储过程

转载于:https://www.cnblogs.com/wangyage/p/7229938.html