MySQL学习总结

知者不言，言者不知。

1、SQL命令总览

可以把 SQL 分为两个部分：数据操作语言(DML)和数据定义语言(DDL)。

（1）数据操作语言（DML）主要是针对表的操作：

  • INSERT INTO - 向数据库表中插入数据（增）

  • DELETE - 从数据库表中删除数据（删）

  • SELECT - 从数据库表中获取数据（查）

  • UPDATE - 更新数据库表中的数据（改）

（2）数据定义语言 (DDL) 主要是针对库及表自身的增删查或属性改的操作：

  • CREATE DATABASE - 创建数据库

  • DROP DATABASE - 删除数据库

  • SHOW DATABASES - 列出数据库

  • ALTER DATABASE - 修改数据库属性

  • SELECT DATABASE()/status - 查看当前使用的数据库

  • CREATE TABLE - 创建表

  • DROP TABLE - 删除表

  • SHOW TABLES FROM database_name - 列出库中的所有表

  • SHOW COLUMNS FROM tbl_name - 列出表中的所有列

  • DESCRIBE tbl_name - 列出表中所有列的类型及属性

  • SHOW CREATE TABLE tbl_name - 列出创建表时的完整结构

  • ALTER TABLE - 修改表属性

  • ALTER TABLE `database_name` CREATE INDEX `index_name`(`column_name`) - 创建索引（搜索键）

  • DROP INDEX - 删除索引

  • ALTER TABLE `database_name` ADD COLUMN `column_name` ... - 添加列及属性

  • ALTER TABLE `database_name` DROP COLUMN `column_name` ...- 撤销列及属性

  • ALTER TABLE `database_name` MODIFE/ALTER `column_name` ... - 修改列及属性

  • CREATE USER `name@`%` IDENTIFIED BY 'pass' - 创建用户（此处的127.0.0.1和localhost分别代表不同的登录方式，需通过-h指定，默认是以localhost的登录方式）

  • GRANT Alter ON `database_name`.* TO `user`@`%`; - 授予user用户在database_name库中拥有的Alter权限。

  • GRANT ALL/NO ON *.* TO `user`@`%` - 授予user用户所有/无的服务器权限，包含对所有库的所有表进行操作的权限（服务器ALL权限：包含Alter, Create, Delete, Drop等，但是不包括Grant Option权限，需GRANT ALL/NO ON *.* TO `user`@`%` WITH Grant Option才能授予所有权限）。【注意：用户的服务器权限和权限有所不同，服务器权限作用于所有的库表，而权限仅作用于指定的库表。这两种权限的搭配类似于ACL中规则和默认规则的关系。】

2、库与表语法格式

【2.1】数据表自身的增删查改

（1）create table tab_name (col_name col_type [其它属性1], ...)[其它属性2](创建数据表)

其它属性1：

  • 如果你不想字段为NULL可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL，就会报错。

  • AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。

  • PRIMARY KEY关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。

其它属性2：

  • ENGINE 设置存储引擎，CHARSET 设置编码。

image

（2）drop table tab_name(删除数据表)

（3）show columns from tab_name(查看表结构)  show table status(查看数据表类型)

image

（4）alter table tab_name operate(修改数据表)

operate的操作有：

  • drop col_name(删除表的col_name列)

  • add col_name col_type [first|after col_name](添加表的col_name列)  如：ALTER TABLE testalter_tbl ADD i INT AFTER c;

  • modify col_name col_type [其它属性1](修改列的类型)  如：ALTER TABLE testalter_tbl MODIFY c CHAR(10);

  • change col_name_old col_name_new col_type(修改列的名称以及类型)  如：ALTER TABLE testalter_tbl CHANGE j j INT;

  • alter col_name set default 1000(修改列的默认值)（列的默认值在不设置的情况下，默认为NULL）

  • alter col_name drop default(删除列的默认值)

  • ENGINE = MYISAM(修改表的类型)  如：ALTER TABLE testalter_tbl ENGINE = MYISAM;

  • rename to tab_name(修改表名)

【2.2】数据表内数据的增删查改

（1）insert into tab_name(col1,col2,col3...)values(val1,val2,val3...) (插入表项)

  • 未列出的列使用默认值，若是拥有自增属性的列，则自动加一。values里的值可以是函数如val3可替换为NOW()。

（2）delete from tab_name [where clause](删除表项)

  • 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除

（3）select col_name,col_name from tab_name [where clause][limit n][offset m](查询表项)

  • 查询语句中你可以使用一个或者多个表，表之间使用逗号分割，并使用WHERE语句来设定查询条件

  • 你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据

  • 你可以使用 LIMIT 属性来设定返回的记录数

  • 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0

（4）update tab_name set col1=val1_new,col2=val2_new [where clause](修改表项)

3、从/子句

（1）WHERE从句（准确匹配）（优先级1）

SQL格式：select col1, col2,... from tab_name1,tab_name2...[where condition1 [and|or] condition2 ...]

WHERE 子句也可以运用于DELETE或者UPDATE命令，它包含的操作符有>、<、=、!=、>=、<=。

（2）like WHERE子句（模糊匹配，该句可在DELETE或UPDATE命令中使用）

SQL格式：select col1, col2,...from tab_name where col1 like condition1 [AND|OR] col2 = 'somevalue'

可以使用LIKE子句代替等号 =，MYSQL为LIKE 提供了四种匹配方式，如下：

  • %：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。

  • _：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。

  • []：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

  • [^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。

  • 查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。

（3）regexp WHERE子句，与like用法相似，只不过它支持正则表达式进行条件匹配。

SQL格式：select name from tabl where name regexp ‘^st’与上用法类似

（4）GROUP BY从句（优先级2）

group by分组语句根据一个或多个列对结果集进行分组（如下图对商品种类进行的分组），在分组的列上可以使用 COUNT, SUM, AVG,等聚合函数。

SQL格式：select col_name,function(col_name) from tab_name where col_name operator value group by col_name

image
参考链接

（5）HAVING从句（优先级3）

HAVING从句与WHERE从句都属于条件过滤，在省略GROUP BY从句的情况下，则HAVING从句的行为与WHERE从句类似。它们之间的主要区别如下：

  • WHERE从句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据。where条件中不能包含聚组函数，使用where条件过滤出特定的行。

  • HAVING从句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数。使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。例，select 类别, SUM(数量) from A where 数量 > 8 group by 类别 having SUM(数量) > 10 ，having后面可以使用聚合函数进行条件过滤。

指定任何的字段做为排序的条件，从而返回排序后的查询结果。默认情况下，它是按asc升序来排序。

SQL格式：select col1_name,col2_name...from tab_name order by col1 [asc|desc], col2 [asc|desc] ...

（7）LIMIT从句（优先级5）

限制通过SELECT查询返回的结果数量。

SQL格式1：SELECT column_list FROM table1 LIMIT row_count（实际返回数量） OFFSET offset（偏移数量）

SQL格式2：SELECT column_list FROM table1 LIMIT offset,count

4、表连接

注：表连接似乎仅在SELECT语句中使用

  • INNER JOIN：如果表中有至少一个匹配，则返回行（以左右表为参考，只有左右两表之间的连接条件都满足的列才会被返回，否则不显示数据）

  • LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行（以左表为参考，这说明左表的数据会全部被显示，即使并未满足连接条件，此时右表中不满足的位置会被置为NULL。）

  • RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行（以右表为参考，这说明右表的数据会全部被显示，即使并未满足连接条件，此时左表中不满足的位置会被置为NULL。）

  • FULL JOIN：只要其中一个表中存在匹配，则返回行（以左右表为从参考，即使两表之间的连接条件并不满足，但也会返回数据，只不过左右对应不上的数据以NULL填充。）

SQL格式1：SELECT col_name FROM tbZUO INNER JOIN tblYOU ON  tblZUO.col_name=tblYOU.col_name;（2表连接）

SQL格式2：SELECT article.aid,article.title,user.username,type.typename FROM article INNER JOIN user ON article.uid=user.uid INNER JOIN type ON article.tid=type.tid；（3表连接，article<->user<->type）

数据库检索是从参考表某列的每一项开始，依次向被参考表进行条件的比对。若被参考表中无对应值，则默认为NULL或不返回。

其它：UION操作符

UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。默认情况下，多个 SELECT 语句会删除重复的数据，即DISTINCT。（如果说JOIN是将多张有关联的表进行了一次列的连接，那么UION就是将多张相同字段列（或不相干的列）的行进行了一次拼接。）

SQL格式：SELECT name1, name2 FROM table1 [WHERE conditions] UNION [ALL | DISTINCT] SELECT name1, name2 FROM tables [WHERE conditions];

5、约束

MYSQL 约束用于规定表中的数据规则，如果存在违反约束的数据行为，行为会被约束终止。

  • NOT NULL：指示某列不能存储 NULL 值。

  • UNIQUE：保证某列的每行必须有唯一的值。

  • PRIMARY KEY：NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。（即唯一性）语法如：CREATE TABLE Persons(P_Id int NOT NULL PRIMARY KEY,LastName varchar(255) NOT NULL)

  • FOREIGN KEY：保证一个表中的数据匹配另一个表中的值的参照完整性。（1.FOREIGN KEY 约束用于预防破坏表之间连接的行为，可以保证两表之间完美衔接。2.FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。）语法如：CREATE TABLE Orders(P_Id int FOREIGN KEY REFERENCES Persons(P_Id))

  • CHECK：保证列中的值符合指定的条件。语法如：CREATE TABLE Persons(P_Id int NOT NULL CHECK (P_Id>0),Address varchar(255) )

  • DEFAULT：规定没有给列赋值时的默认值。语法如：CREATE TABLE Persons(P_Id int NOT NULL,City varchar(255) DEFAULT 'Sandnes')

6、事务

MySQL事务主要用于处理操作量大，复杂度高的数据。（如，在人员管理系统中删除一个人员，既需要删除人员的基本资料，也要删除和该人员相关的信息，如此这些数据库操作语句就构成一个事务。）

  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

  • 事务用来管理 insert,update,delete 语句

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句：

  • BEGIN（START TRANSACTION）显式地开启一个事务；

  • COMMIT（COMMIT WORK）会提交事务，并使已对数据库进行的所有修改成为永久性的；

  • ROLLBACK （ ROLLBACK WORK）回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

  • SAVEPOINT identifier，允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；【避免执行多语句时，只因其中的某一句错误之后而通过ROLLBACK直接回滚到事务执行之前的状态，相当于是分段回滚。适合在事务中执行大量的SQL语句时使用】

  • RELEASE SAVEPOINT identifier 删除一个事务的保存点；

  • ROLLBACK TO identifier 把事务回滚到标记点；

  • SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

参考链接

7、视图

注：视图仅适用于SELECT语句

（1）视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。

（2）视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限，从而加强了安全性，使用户只能看到视图所显示的数据。）

（3）视图还可以被嵌套，一个视图中可以嵌套另一个视图。

（4）视图通常被用于将某些频繁使用且语法复杂的sql查询语句进行了一个打包，使用视图就相当于是在一列复杂语法执行的结果表中进行的查询。（navicat中直接双击已定义好的视图，那么将直接呈现该视图定义中指定的所有数据，相当于执行了一遍视图中定义的select语句。可以直接将视图看做一个查询结果表）用法如下

SQL格式：CREATE VIEW view_name AS  SELECT column_name(s)  FROM table_name  WHERE condition

使用视图：SELECT * FROM `database_name`.`view_name` LIMIT 0,1000

8、索引

  • 索引并不能显式使用，它是在sql引擎进行条件查询时，首先主动根据条件列是否存在对应的索引，而后才确定是否能够使用索引

  • 索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引；组合索引，即一个索引包含多个列。

  • 创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

  • 索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

  • 索引也有缺点：虽然索引大大提高了查询速度，但同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引 ALTER table tableName ADD INDEX indexName(columnName)

删除索引 DROP INDEX [indexName] ON mytable;
参考链接

9、复制表

（1）复制表结构到新表

  • create table tbl_new like tbl_old;（推荐）

  • SHOW CREATE TABLE tbl_old；（复制粘贴创建表时的结构）

  • create table tbl_new select * from tbl_old where 1=2；（似乎不太可靠，不确定列类型是否完全一致）

（2）复制表结构及数据到新表

  • create table tbl_new select * from tbl_old；（推荐）

  • INSERT INTO tbl_new SELECT * FROM tbl_old；（在只有表结构的新表中插入旧表中的数据）

（3）复制表的部分列字段

  • CREATE TABLE tbl_new AS( SELECT id, username AS uname, password AS pass FROM tbl_old)（复制表中的部分列字段并重命名，然后导入数据）

  • CREATE TABLE tbl_new AS( SELECT * FROM tbl_oldWHERE LEFT(username,1) = 's')（复制表中的全部列字段，然后导入符合条件的数据）

参考链接

*、杂项

（1）mysql语法的解析似乎是以关键字进行的，关键字顺序也不是完全不分先后。例：

SQL1：SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

SQL2：SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;

（2）数据库的每张表只能有一个主键，不可能有多个主键。所谓的一张表多个主键，我们称之为联合主键（就是用多个字段一起作为一张表的主键）。

（3）父表中的主键是被子表中的外键所参考引用的，先有主键然后才有外键，有主无外可，有外必有主。

posted @ 2023-08-11 15:45  扛枪的书生  阅读(53)  评论(0)  编辑  收藏  举报