MySQL必知必会

人在驴途 发表于 2024-2-25 19:25:57

一、Mysql

1、什么是数据库？

(1)数据库（database）

数据库是保存有组织的数据的容器（通常是一个文件或一组文件），数据库是DBMS(数据库管理系统或数据库软件)创建和操作的容器；
（2）表、列、行、主键、数据类型分别是什么
①表：表是由某些特定的字段组成的统一的结构化的存储数据的容器；
②列：列是组成表结构的最基本的单位；
③行：一行表示一条数据库记录，存储着记录信息；
④主键：主键是由数据库表中的一列或者多个列组成的，主键需要唯一；主键不能为空，主键
⑤数据类型：表中的每一个列，定义时都需要设定数据类型；
（3）SQL与SQL的编写规则（大概规则）

①SQL是专门与数据库实现通信的统一语言，但是不同的DBMS的SQL会有小的差异，不会完全相同；
②SQL编写规范
a）SQL语言不区分大小写，但是一般SQL开发人员，关键字使用大写，表名和列名使用小写；
b）在SQL语句中，空格都会忽略掉，所有一般开发人员为了方便读和调试会对SQL语句进行换行；
2、Mysql中一些常用的语句

（1）use:表示切换到需要的数据库

①use databasename：切换到databasename数据库；如果使用命令行连接Mysql数据库，需要使用该语句才能切换到对应的数据库
（2）show：展示数据库服务中的信息，相当于查看的功能；

①show databases:查看数据库服务中的所有数据库；
②show tables:查看数据库服务中的所有表；
③show colums from tablename:查看表结构中所有字段；
④show status：显示服务器的状态信息；
⑤show grants:显示当前登录用户的权限信息；
⑥show warnings:显示服务器警告信息；
⑦show errors：显示服务器错误信息；
⑧net start/stop mysqlname:开启或者关闭mysql服务；
⑨show global variables like 'port':查看服务开启的端口；
3、表结构的增删改查

（1）创建表:CREATE TABLE TABLENAME

①创建表基本语句
CREATE TABLE tablename(
columname type 约束条件 comment '字段名字'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='表名';

CREATE TABLE IF NOT EXISTS tablename(
columname type 约束条件 COMMENT '字段名字'
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='表名';②创建表时常用的约束条件
a）主键(PRIMARY KEY) b）外键(constraints foreign_key_idname foreign key(column) references tablename(columnname)) c）不为空(NOT NULL) d）自增（AUTO_INCREMENT）
e）默认值(DEFAULT)
f）字段备注信息（COMMENT） ③实例
CREATE TABLE IF NOT EXISTS customers
(
cust_id int NOT NULLAUTO_INCREMENT COMMENT '顾客ID',
cust_name char(50)NOT NULL comment '顾客名',
cust_address char(50)COMMENT '顾客的地址',
cust_city char(50)COMMENT '顾客的城市',
cust_state char(5) COMMENT '顾客的州',
cust_zip char(10)COMMENT '顾客的邮政编码',
cust_country char(50)COMMENT '顾客的国家',
cust_contact char(50)COMMENT '顾客的联系名',
cust_email char(255) DEFAULT '123@qq.com'COMMENT '顾客的联系email地址',
PRIMARY KEY(cust_id)
) ENGINE=InnoDB COMMENT='顾客表';④自增AUTO_INCREMENT相关
a）每个表只能有一个列上加AUTO_INCERMENT,且该字段必须被索引上；
b）如果在insert时设置了自增的值，则自增的值会被手工设置的值覆盖，且后续自增的值从手工设置的值往后增；
c）在Mysql中可以使用select last_insert_id()查看自增量的最后一个值；
⑤引擎
a）InnoDB：该引擎是可靠的事务处理引擎，但是不支持全文本搜索；
b）MEMORY：等同于MyISAM，由于是存储与内存所以速度较快，适用于临时表；
c）MyISAM：性能极高，支持全文本搜索，但是不支持事务；
（2）修改表:ALTER TABLE TABLENAME

ALTER TABLE TABLENAME 后续的操作;
ALTER TABLE TABLENAME ADD COLUMN COLUMNNAME INT;
ALTER TABLE TABLENAME DROP COLUMN COLUMNNAME;
ALTER TABLE TABLENAME MODIFY COLUMN COLUMNNAME NEWTYPE;
ALTER TABLE TABLENAME RENAME COLUMNNAME TO NEWCOLUMNNAME;（3）删除表:DROP TABLE TABLENAME

DROP TABLE TABLENAME;
RENAME TABLE TABLENAME TO NEWTABLENAME,TABLENAME1 to NEWTABLENAME1; 4、表数据的增删改查

（1）插入数据:INSERT INTO TABLENAME...

INSERT INTO TABLENAME(COLUMN1,COLUMN2,COLUMN3,...) VALUES(VALUE1,VALUE2,VALUE3,...);

INSERT INTO TABLENAME(COLUMN1,COLUMN2,COLUMN3,...) VALUES(VALUE1,VALUE2,VALUE3,...),(VALUE1,VALUE2,VALUE3,...),...;

INSERT LOW_PRIORITY ...;

INSERT INTO TABLENAME(COLUMN1,COLUMN2,COLUMN3,...) SELECT VALUE1,VALUE2,VALUE3,... FROM TABLENAME1;（2）更新数据:UPDATE TABLENAME SET...

UPDATE TABLENAME SET COLUMNNAME='NEWVALUE' ;

UPDATE TABLENAME SET COLUMNNAME='NEWVALUE' WHERE ...;

UPDATE TABLENAME SET COLUMNNAME='NEWVALUE',COLUMNNAME1='NEWVALUE1' WHERE ...;

UPDATE IGNORE TABLENAME SET COLUMNNAME='NEWVALUE' WHERE ...;如果存在错误时，忽略错误，继续往下执行（3）删除数据:DELETE FROM TABLENAME...

DELETE FROM TABLENAME WHERE...;删除表中的特定行或所有行

TRUNCATE TABLENAME;--删除表中所有的数据，且直接提交不能回滚5、查询数据库

（1）检索数据
a）DISTINCTSELECT DISTINCT COLUMNNAME FROM TABLENAME;

SELECT DISTINCT COLUMNNAME,COLUMNNAME1 FROM TABLENAME;

SELECT DISTINCT *FROM TABLENAME; --过滤掉完全相同的行，或者把所有只要存在一个内容不同的数据都查询出来；b）LIMIT
SELECT *FROM TABLENAME LIMIT 5;--查询小于等于5条数据

SELECT *FROM TABLENAME LIMIT 5,5;--查询从行5开始往后的5条或5条以内的数据

SELECT *FROM TABLENAME LIMIT 5 offset 5;--同上（2）排序数据:ORDER BY COLUMNNAME
SELECT *FROM TABLENAME ORDER BY COLUMNNAME ;--默认是按照升序排列的（ASC）

SELECT *FROM TABLENAME ORDER BY COLUMNNAME DESC ;--设置降序

SELECT *FROM TABLENAME ORDER BY COLUMNNAME DESC,COLUMNNAME DESC;--如果需要按照多个列排序，中间使用逗号隔开，多个列都需要降序的话多个列都需要加DESC（3）过滤数据：WHERE
SELECT *FROM TABLENAME WHERE COLUMNNAME...;

SELECT *FROM TABLENAME WHERE COLUMNNAME BETWEEN ... AND ...;

SELECT *FROM TABLENAME WHERE COLUMNNAME> ... AND ;

SELECT *FROM TABLENAME WHERE COLUMNNAME< ... OR ...;

SELECT *FROM TABLENAME WHERE COLUMNNAME<= ... OR ...;

SELECT *FROM TABLENAME WHERE COLUMNNAME>= ... OR ...;

SELECT *FROM TABLENAME WHERE COLUMNNAME= ... OR ...;

SELECT *FROM TABLENAME WHERE COLUMNNAME!= ... OR ...;

SELECT *FROM TABLENAME WHERE COLUMNNAME<> ... OR ...;

SELECT *FROM TABLENAME WHERE COLUMNNAME IS NULL;

SELECT *FROM TABLENAME WHERE COLUMNNAME IS NOT NULL;

SELECT *FROM TABLENAME WHERE COLUMNNAME IN(...);（4）使用通配符和正则表达式查询数据:LIKE&REGEXP
通配符：
SELECT *FROM TABLENAME WHERE COLUMNNAME LIKE '%jack';--%匹配任意多个字符
SELECT *FROM TABLENAME WHERE COLUMNNAME LIKE '_jack';--%匹配单个字符
 正则表达式：
SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP 'jack';--表示匹配COLUMNNAME列中包含jack的行； SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP 'jack|jack1';--表达式中的|表示或者； SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '.jack';--表达式中的.表示匹配任意字符； SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP 'ton';--表达式中的[]表示匹配[]中的任意字符，表示或的意思； SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP 'ton';--表达式中的表示匹配1-9,a-z中的任意数字或者字母； SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '\\.'--表达式中\\表示转义，若特殊字符. - []这些内容，可以在前面加上转义字符表示查询特殊字符内容； SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '*';--表达式中的*表示匹配0个或者多个内容; SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '+';--表达式中的+表示匹配1个或者多个内容; SELECT *FROM TABLENAME WHERE COULMNNAME REGEXP '?';--表达式中的？表示匹配0个或者1个内容; SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '{n,m}';--表达式中的{n,m}表示匹配n到m之间个内容； SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '{n,}';--表达式中的{n,}表示匹配大于等于n个内容； SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '{n}';--表达式中的{n}表示匹配n个内容； SELECT *FROM TABLENAME WHERE COLUMNNAME REGEXP '^main';--表达式中的^表示匹配开头; SELECT *FROM TBALENAME WHERE COLUMNNAME REGEXP '$main';--表达式中的$表示匹配结尾;（5）Mysql的内置函数
①计算字段
SELECT CONCAT(COLUMNNAME ,'(',COLUMNNAME,')') AS COLUMNNAMENEW FROM TABLENAME;
②去除空格字段
SELECT RTRIM(COLUMNNME) FROM TABLENAME; SELECT LTRIM(COLUMNNAME)FROM TABLENAME;
③使用别名 SELECT AVG(COULUMNAME) as COLUMNNAME1 FROM TABLENAME;
④执行算数计算
+\-\*\/ SELECT COLUMNNAME*2,FROM TABLENAME;
⑤使用数据处理函数
ABS--绝对值 PI--Π Sqrt--返回数的平方根 Sin--正弦 Cos--余弦 Tan--正切
⑥聚集函数
a）AVG：返回指定COLUMNNAME的平均值；
SELECT AVG(COLUMNNAME)FROM TABLENAME; SELECT AVG(COLUMNNAME) AS COLUMNNAME1 FROM TABLENAME; 
b）SUM:返回指定列值的总和； SELECT SUM(COLUMNNAME) FROM TABLENAME; 
c）COUNT：返回表中行数数量；
SELECT COUNT(*) FROM TABLENAME;--返回表中行数数量，包括NULL的行 SELECT COUNT(COLUMNNAME)FROM TABLENAME;--返回表中行数数量，不包括指定COLUMNNAME为NULL的； 
d）MIN和MAX:查询表中特特定列值的最大或者最小值； SELECT MIN(COLUMNNAME) FROM TABLENAME; SELECT MAX(COULUNNAME) FROM TABLENAME;（6）分组数据
SELECT COLUMNNAME FROM TABLENAME GROUP BY COLUMNNAME ORDER BY COLUMNAME1 DESC;（在ORDER BY后面的COLUMNNAME可以不是GROUP BY后面的COLUMNNAME,但是根据规范最好是,如下语句） SELECT COULMNNAME FROM TABLENAME GROUP BY COLUMNNAME ORDER BY COLUMNNAME DESC; 
GROUP BY语句使用注意事项
①GROUP BY 语句在WHERE之后，ORDER BY之前
②在SELECT语句中使用GROUP BY时，需要查询展示的列在GROUP BY中必须也要有，不然查询出来的结果会不准确；如果在SELECT 中使用表达式，在GROUP BY中也必须使用表达式，不能使用别名；聚集函数可以在SELECT中存在，但是在GROUP BY中不存在；
③如果分组中存在NULL值，则NULL分为单独一组，多个NULL值分为一组； HAVING过滤分组 WHERE过滤行而HAVING过滤分组，WHERE在分组之前过滤，HAVING在分组之后过滤； SELECT COLUMNNAME,COUNT(*) FROM TABLENAME GROUP BY COLUMNNAME HAVING COUNT(*)>8; SELECT COLUMNANME,COUNT(*) FROM TABLENAME WHERE COLUMNANEM ='' GROUP BY COLUMNNAME HAVING COUNT(*)>8; Mysql中的关键字的顺序： SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT；（7）子查询
SELECT *FROM TABLENAME WHERE COLUMNNAME IN (SELECT COLUMNNAME FROM TABLENAME1 WHERE COLUMNNAME1='') SELECT COLUMNNAME (SELECT COUNT(*) FROM TABLENAME WHERE ......s)AS COLUMNNAME FROM TABLENAME ;（8）组合查询
组合查询：执行多个SELECT 查询语句，并将结果作为单个结果集返回，使用UNION关键字将多个SELECT 查询结果集组合成一个结果集返回；

SELECT COLUMNNAME,COLUMNNAME1 FROM TABLENAME UNION SELECT COLUMNNAME2,COLUMNNAME3 FROM TABLENAME1;

UNION规则
①UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔；
②UNION中的每个查询必须包含相同的列、表达式或聚集函数；
③列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）；
④使用UNION时默认取消掉了重复行，如果需要不取消重复行需要使用UNION ALL; ⑤如果需要对UNION的结果排序，只需要在最后一个SELECT 查询语句后面加上ORDER BY即可；（9）Mysql中的连接方式
①内连接
a）内连接：自然连接(Naturaljoin)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉；
SELECT *FROM TABLENAME INNER JOIN TABLENAME1 ON TABLENAME.COLUMNNAME = TABLENAME.COLUMNNAME;(比较的分量必须是相同的属性组)
b）等值连接：等值连接是关系运算-连接运算的一种常用的连接方式。是条件连接（或称θ连接）在连接运算符为“=”号时，即θ=0时的一个特例 SELECT *FROM TABLENAME,TABLENAME1 WHERE TABLENAME.COLUMNNAME=TABLENAME1.COLUMNNAME;②外连接
a）左连接：LEFT JOIN或LEFT OUTER JOIN
SELECT *FROM TABLENAME LEFT OUTER JOIN TABLENAME1 ON TABLENAME.COLUMNNAME=TABLENAME1.COLUMNNAME;
b）右连接：RIGHT JOIN 或 RIGHT OUTER JOIN
SELECT *FROM TABLENAME AS T RIGHT OUTER JOIN TABLENAME1 AS T1 ON T.COLUMNNAME=T1.COLUMNNAME;
c）全连接：FULL JOIN 或 FULL OUTER JOIN
SELECT *FROM TABLENAME AS T FULL OUTER JOIN TABLENAME1 AS T1 ON T.COLUMNNAME=T1.COLUMNNAME;(Mysql不支持)（10）全文本搜索
①什么是全文本搜索a）Mysql中最常用的引擎就是MyISAM和InnoDB搜索引擎，前者支持全文本搜索，后者不支持；b）为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引；若在创建表是进行适当的设置，Mysql就会自动进行索引和重新索引；c）创建表时设置全文本搜索CREATE TABLE TABLENAME(COLUMNNAME TYPE,FULLTEXT(COLUMNNAME))②进行全文本搜索的基本语法：Match()和Against()a）其中Match()指定被搜索的列，Against()指定要使用的搜索表达式；（一般不区分大小写）b）传递给Match的值必须必须要与FULLTEXT中定义的一致，且如果定义了多列则次序也需要一致；c）全文本搜索会自动根据搜索的匹配优先级排序列出结果；SELECT *FROM TABLENAME MATCH(COLUMNNAME) AGAINST('需要搜索的内容');③进行全文本搜索的更高级语法a）使用扩展查询SELECT *FROM TABLENAME MATCH() AGAINST('需要搜索的内容' with query expansion);b）布尔文本搜索SELECT *FROM TABLENAME MATCH AGAINST('需要搜索的内容' IN BOOLEAN MODE)SELECT *FROM TABLENAME MATCH AGAINST('a +b' IN BOOLEAN MODE);--+表示包含，必须存在+后面的内容；SELECT *FROM TABLENAME MATCH AGAINST('a -b' IN BOOLEAN MODE)；---表示不包含，必须不存在+后面的内容；SELECT *FROM TABLENAME MATCH AGAINST('a >b' IN BOOLEAN MODE)；-->表示包含，而且增加等级；SELECT *FROM TABLENAME MATCH AGAINST('a

页: [1]

翼度科技's Archiver

MySQL必知必会