MySql数据库

来源：互联网发布：手机淘宝保证金怎么交编辑：程序博客网时间：2024/06/02 02:21

学习来源于：http://www.runoob.com/mysql

注意：

（1）MySql的sql语句以分号（;）作为结束标识。

（2）MySql语法对大小写不敏感，但是大写更容易看出。

（3）MySql支持的数据类型大致可以分为三类：数值、日期/时间和字符串（字符）类型。

1、MySql连接

（1）从命令行中以root用户链接mysql服务器：mysql -u root -p

（2）退出MySql>命令提示窗口：exit

2、创建数据库

（1）使用mysqladmin在终端创建数据库：mysqladmin -u root -p creat database-name

（2）使用mysqladmin在终端删除数据库：mysqladmin -u root -p drop database-name

（3）在MySql>命令提示窗口中创建数据库：create DATABASE database-name;

（4）在MySql>命令提示窗口中删除数据库：drop DATABASE database-name;

3、选择数据库

（1）显示所有数据库：SHOW DATABASES;

（2）选择指定的数据库：USE database-name;

（3）选择好指定的数据库后，列出该数据库的所有表：SHOW TABLES;

（4）显示数据表的属性、属性类型，主键信息等：SHOW COLUMNS FROM table-name;

（5）显示数据表的详细索引信息，包括主键：SHOW INDEX FROM table-name;

（6）输出mysql数据库管理系统的性能及统计信息：SHOW TABLE STATUS from database-name LIKE 'runoob%'; #表名以runoob开头的表的信息

SHOW TABLE STATUS from database-name LIKE ‘runoob%’\G #加上 \G ，查询结果按列打印

4、创建数据表

创建数据表需一下信息：表名(table-name)、表字段名(column-name)和定义每个表字段

注意：通常为了区分mysql关键字与普通字符而引入反引号（键盘esc键下面的那个～键），一般的，表名与字段名都使用反引号。

（1）选择指定的数据库：USE database-name;

（2）创建mysql数据表的SQL通用语法：CREATE TABLE table-name (column-name column-type);

（3）删除数据表：DROP TABLE table-name;

5、插入数据

注意：数据表中插入的field要和创建的表字段名一一对应。

（1）选择指定的数据库：USE database-name;

（2）创建mysql数据表的SQL通用语法：CREATE TABLE table-name (column-name column-type);

（3）向数据表table-name中插入数据：INSERT INTO table-name (field1, field2, ... fieldN) VALUES (value1, value2, ...valueN);

#注意：如果数据是字符型，必须使用单引号或者双引号，如：‘value’ 或 "value"。

（4）查看数据表中的数据：select * from table-name;

6、查询数据

（1）在MySQL数据库中查询数据通用的SELECT语法：SELECT column_name1, column_name2 from table-name [WHERE Clause] [OFFSET M] [LIMIT M]

注释：（1）查询语句中可以使用一个或多个表，表之间使用逗号(,)分割，并可以使用WHERE语句设定查询条件。

（2）SELECT命令可以读取一条或者多条记录。

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

（4）可以使用WHERE语句来包含任何条件。

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

（6）可以使用LIMIT属性来设定返回的记录。

7、MySQL WHERE子句

注意：如果需要有条件地从表中选取数据，可将WHERE子句添加到SELECT语句中。

（1）在SQL SELECT语句中使用WHERE子句从数据表中读取数据的通用语法：SELECT field1, field2, ...fieldN FROM table_name1, table_name2...

[WHERE condition1 [AND [OR]] condition2 ...

注释：（1）查询语句中可以使用一个或多个表，表之间使用逗号(,)分割，并可以使用WHERE语句设定查询条件。

（2）可以在WHERE子句中指定任何条件。

（3）可以使用AND 或者OR指定一个或多个条件。

（4）WHERE子句也可以运用于SQL的DELETE或者UPDATE命令。

（5）WHERE子句类似于程序语言中的if条件，根据MySQL表中的字段值来读取指定的数据。

（6）MySQL的WHERE子句的字符串比较是不区分大小写的。可以使用BINARY关键字来设定WHERE子句的字符串比较是区分大小写。

（WHERE BINARY condition1）

8、MySQL UPDATE查询

（1）修改或更新MySQL数据表数据的通用SQL语法：UPDATE table-name SET field1=new-value1, field2=new-value2 [WHERE Clause];

注释：（1）可以同时更新一个或多个字段。

（2）可以在WHERE子句中指定任何条件。

（3）可以在一个单独表中同时更新数据。

（2）UPDATE替换某个字段中的某个字符，即将字段中的特定字符串批量修改为其他字符串：UPDATE table-name SET

field=REPLACE(field,"old string","new string") [WHERE Clause]

9、MySQL DELETE语句

（1）SQL DELETE语句从MySQL数据表中删除数据的通用语法：DELETE FROM table-name [WHERE Clause];

注释：（1）若没有指定WHERE子句，MySQL表中的所有记录将被删除。

（2）可以在WHERE子句中指定任何条件。

（3）可以在单个表中一次性删除记录。

10、MySQL LIKE子句

注意：WHERE子句可以使用 = 来设定获取数据的条件，如“ runoob_author = ' RUNOOB.COM' ”；但是有时候只需获取runoob_author字段含有 'COM' 字符的所有记录，

这时就需要在WHERE子句中使用SQL LIKE子句。SQL LIKE子句中使用百分号 % 字符来表示任意字符。

（1）SQL SELECT语句使用LIKE子句从数据表中读取数据的通用语法：SELECT field1, field2,...fieldN FROM table-name

WHERE field1 LIKE condition1 [AND [OR] ] field2 = 'somevalue'

注释：（1）可以在WHERE子句中指定任何条件。

（2）可以在WHERE子句中使用LIKE子句。

（3）可以使用LIKE子句代替等号 = 。

（4）LIKE通常与%一同使用，类似于一个元字符的搜索。

（5）可以使用AND或者OR指定一个或多个条件。

（6）可以在DELETE或UPDATE命令中使用WHERE...LIKE子句来指定条件。

11、MySQL UNION 操作符

UNION操作符用于连接两个以上的SELECT语句的结果组合到一个结果集合中。多个SELECT会删除重复的数据。

（1）语法：SELECT expression1, ... expressionN FROM tables [WHERE conditions] UNION [ALL | DISTINCT]

SELECT expression1, ... expressionN FROM tables [WHERE conditions];

注释：（1）expression1, ... expressionN：要检索的列。

（2）tables：要检索的数据表。

（3）WHERE conditions：可选，检索条件。

（4）DISTINCT：可选，删除结果集中重复的数据。默认情况下UNION操作符已经删除了重复数据，所以DISTINCT修饰符对结果没影响。

（5）ALL：可选，返回所有结果集，包含重复数据。

12、MySQL排序

如果需要对读取的数据进行排序，可以使用MySQL的ORDER BY 子句来设定想按哪个字段哪种方式来进行排序，再返回搜索结果。

（1）将查询数据排序后再返回数据：SELECT field1, field2, ...fieldN FROM table-name1, table-name2, ...table-nameN ORDER BY field1, [field2...] [ASC [DESC]]

注释：（1）可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。

（2）可以设定多个字段来排序。

（3）可以使用ASC或DESC关键字来设置查询结果是按升序或降序来排序。默认情况下，它是按升序排列。

（4）可以添加WHERE ... LIKE子句来设置条件。

13、MySQL GROUP BY语句

根据一个列或多个列对结果进行分组。在分组的列上可以使用COUNT、SUM、AVG等函数。

（1）语法：SELECT column_name, function(column_name) FROM table-name WHERE column_name operator value GROUP BY column_name;

例如：将数据表按名字进行分组，并统计每个人有多少条记录：SELECT name,COUNT(*) FROM table-name GROUP BY name;

（2）WITH ROLLUP：可以实现在分组统计数据基础上再进行相同的统计（SUM，AVG，COUNT......）。

例如：将数据表中按名字进行分组，再统计每个人登录的次数。（下图中NULL表示所有人的登录次数）

可以使用coalesce来设置一个可以取代NULL的名称：SELECT coalesce(a,b,c)。如果a==NULL，则选择b；如果b==NULL，则选择c；如果a != NULL，则选择a；如果a b c都为NULL，则返回NULL。

14、MySQL连接的使用

在SELECT、UPDATE、DELETE语句中使用JOIN来联合多表查询。按照功能分为如下三类：

（1）INNER JOIN（内连接，或等值连接）：获取两个表中字段匹配关系的记录。

（2）LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。

（3）RIGHT JOIN（右连接）：与LEFT JOIN相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

例如：数据表tcount_tbl和runoob_tbl：

（1）内连接：

（2）左连接：

（3）右连接：

15、MySQL NULL值处理

当提供的查询条件字段为NULL时，如何处理？MySQL提供了三大运算符：

（1）IS NULL：当列的值是NULL，此运算符返回TRUE。

（2）IS NOT NULL：当列的值不为NULL，运算符返回TRUE。

（3）<=>：比较操作符（不同于 = 运算符），当比较的两个值为NULL时返回TRUE。

例如：

16、MySQL正则表达式

MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

例如：

（1）查找 runoob_author 字段中以 “RUN” 为开头的所有数据：

（2）查找 runoob_title字段中包含 “学习” 字符串的所有数据：

17、MySQL 事务

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

（1）在MySQL中只有使用了 InnoDB数据库引擎的数据库或表才能支持事务。

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

（3）事务用来管理 insert，update，delete语句。

事务必须满足4个条件（ACID）：

1、原子性：一组事务，要么成功，要么撤回。

2、稳定性：有非法数据（外键约束之类），事务撤回。

3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。

4、可靠性：软硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得。

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

事务控制语句：

（1）BEGIN 或 START TRANSACTION ：显式的开启一个事务。

（2）COMMIT：也可以使用COMMIT WORK，二者是等价的。COMMIT会提交事务，并使得已经对数据库进行的所有修改称为永久性的。

（3）ROLLBACK：可以使用ROLLBACK WORK，二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。

（4）SAVEPOINT identifier：SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT。

（5）RELEASE SAVEPOINT identifier：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常。

（6）ROLLBACK TO identifier：把事务回滚到标记点。

（7）SET TRANSACTION：用来设置事务的隔离级别。

MySQL事务处理主要有两种方法：

1、（a）BEGIN开始一个事务；

（b）ROLLBACK事务回滚；

（c）COMMIT事务确认。

2、直接用SET来改变MySQL的自动提交模式：

（a）SET AUTOCOMMIT=0 禁止自动提交

（b）SET AUTOCOMMIT=1 开启自动提交

事务回滚：

18、MySQL ALTER命令

用于修改数据表名或者修改数据表字段。

（1）删除，添加或修改表字段：ALTER TABLE table-name DROP field; #如果数据表中只剩余一个字段，则无啊使用DROP来删除字段

ALTER TABLE table-name ADD field TYPE

注意：如果需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST（设定位第一列）、AFTER 字段名（设定位于某个字段之后）。FIRST 和 AFTER关键字只占用于ADD子句，所以如果想重置数据表字段的位置就需要先使用DROP删除字段然后使用ADD添加字段并设置位置。

（2）修改字段类型及名称：ALTER TABLE table-name MODIFY field type;

ALTER TABLE table-name CHANGE field new_field new_type;

（3）ALTER TABLE 对 NULL值和默认值的影响

当修改字段时，可以指定是否包含值或者是否设置默认值。如果不设置默认值，则会自动设置该字段默认为NULL。

修改字段默认值：使用 ALTER 来修改默认值

使用 ALTER 及 DROP 子句来删除字段的默认值：

（4）修改数据表表名：在ALTER TABLE语句中使用 RENAME 子句来实现。

修改数据表类型：可以使用 ALTER 命令及 TYPE 子句来完成。（将下面例子的ENGINE（存储引擎）改为TYPE）

修改后：

（5）删除外键约束：ALTER TABLE table-name DROP FOREIGN KEY key-name; #key-name为外键别名

19、MySQL索引

单列索引：一个索引只包含单个列，一个表可以有多个单列索引。

组合索引：一个索引包含多个列。

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

（1）普通索引

（a）创建索引：CREATE INDEX index-name ON table-name( user-name(length) ); #如果是CHAR, VARCHAR类型，length可以小于字段实际长度；

如果是BLOB或TET类型，必须指定length。

（b）修改表结构（添加索引）：ALTER TABLE table-name ADD INDEX index-name( column-name );

（c）创建表的时候直接指定：CREATE TABLE `table-name` (`ID` INT NOT NULL, user-name VARCHAR(16) NOT NULL,

INDEX [index-name] (user-name(length)));

（d）删除索引：DROP INDEX [index-name] ON table-name;

（2）唯一索引

与普通索引类似，区别在于：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

（a）创建索引：CREATE UNIQUE INDEX index-name ON table-name( user-name(length) );

（b）修改表结构（添加索引）：ALTER TABLE table-name ADD UNIQUE [ index-name ] ( user-name(length) );

（c）创建表的时候直接指定：CREATE TABLE `table-name` (`ID` INT NOT NULL, user-name VARCHAR(16) NOT NULL,

INDEX [index-name] (user-name(length)));

（3）使用 ALTER 命令添加和删除索引

四种方式：（a）ALTER TABLE table-name ADD PRIMARY KEY (column-list)：该语句添加一个主键，意味着索引值必须唯一，且不能为NULL；

（b）ALTER TABLE table-name ADD UNIQUE index-name (column-list)：这条语句创建索引的值必须唯一（除NULL外，NULL可能出现多次）；

（c）ALTER TABLE table-name ADD INDEX index-name (column-list)：添加普通索引，索引值可以出现多次；

（d）ALTER TABLE table-name ADD FULLTEXT index-name (column-list)：该语句指定了索引为FULLTEXT，用于全文索引。

注意：删除主键时只需指定PRIMARY KEY，但是删除索引时，必须知道索引名。

20、MySQL 临时表

临时表只在当前连接可见，当关闭连接时，MySQL会自动删除并释放所有空间。

（1）创建临时表：CREATE TEMPORARY TABLE table-name(...)

注意：使用SHOW TABLES命令显示数据表列表时，将无法看到该创建的数据表。若退出当前MySQL会话，再使用SELECT命令来读取原先创建的临时表数据，会发现数据库中没有该表的存在，因为在退出时，该临时表已经被销毁了。

（2）手动删除临时表：DROP TABLE table-name; #当断开与数据库的连接后，临时表就会自动销毁。

21、MySQL复制表

完整的复制MySQL数据表，步骤如下：

（1）使用SHOW CREATE TABLE命令获取创建数据表（CREATE TABLE）语句，该语句包含来原数据表的结构、索引等。

（2）复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令将完全的复制数据表结构。

（3）如果想复制表的内容，可以使用INSERT INTO ..... SELECT 语句实现。

例：

（a）获取数据表的完整结构：

（b）修改SQL语句的数据表名（创建新的克隆表，增加了唯一键），并执行SQL语句：

（c）使用INSERT INTO ...... SELECT语句实现拷贝数据表中的数据：

（d）另一种完整复制表的方法：CREATE TABLE target-table LIKE source-table;

INSERT INTO target-table SELECT * FROM source-table;

拷贝一个表中其中的一些字段：CREATE TABLE 新表 AS ( SELECT field1, filed2,...fieldN FROM 旧表);

将新建的表的字段改名：CREATE TABLE 新表 AS (SELECT field1, field2 AS new-field2, fieldN AS new-fieldN FROM 旧表);

拷贝一部分数据：CREATE TABLE 新表 AS (SELECT * FROM 旧表 WHERE condition1 );

可以在创建表的同时定义表中的字段信息：CREATE TABLE 新表 ( field1 type ... ) AS (SELECT * FROM 旧表)

（e）只复制表结构到新表：CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2 或者 CREATE TABLE 新表 LIKE 旧表

复制表结构及数据到新表：CREATE TABLE 新表 SELECT * FROM 旧表;

22、MySQL元数据

（1）查询结果信息：SELECT，UPDATE或DELETE语句影响的记录数。

（2）数据库和数据表的信息：包含了数据库及数据表的结构信息。代码：SHOW TABLES或SHOW DATABASES；

（3）MySQL 服务器信息：包含了数据库服务器的当前状态，版本号等。

23、MySQL 序列使用

MySQL 序列是一组整数：1，2，3，...，由于一张数据表只能有一个字段自增主键，如果想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。

（1）使用AUTO_INCREMENT来定义列：

（2）获取最后的插入表中的自增列的值：SELECT LAST_INSERT_ID(); 注意：LAST_INSERT_ID()与table无关，如果向表a插入数据，再向表b插入数据，

该函数只返回表b中的ID值。同时插入多行时，该函数只返回插入的第一行数据时产生的值。

（3）重置序列，如果删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么可以通过删除自增的列，然后重新添加来实现。

（4）设置序列的开始值（从100开始）：

创建表时：

表创建成功后：

24、MySQL处理重复数据

（1）防止表中出现重复数据，可以设置指定的字段为PRIMARY KEY (主键) 或者 UNIQUE (唯一)索引来保证数据的唯一性。如果设置来主键，那么这个键的默认值不能为NULL，可设置为NOT NULL；如果设置了唯一索引，那么在插入重复数据时，SQL语句将无法执行成功，并抛出错。

（2）INSERT IGNORE INTO：会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据；如果有数据的话，就跳过这条数据。

另一种设置数据的唯一性方法是添加一个UNIQUE索引：

（3）统计重复数据：

以上查询语句将返回person_tbl数据表中重复的记录数，一般情况下，查询重复值，请执行一下操作：

（a）确定哪一列包含的值可能会重复；

（b）在列选择列表使用COUNT(*)列出的那些列；

（c）在GROUP BY 子句中列出的列；

（d）HAVING 子句设置重复数大于1。

（4）过滤重复数据

读取不重复的数据可以在SELECT语句中使用DISTINCT关键字来过滤重复数据：

或者使用GROUP BY 来读取数据表中不重复的数据：

（5）删除重复数据：先将旧表复制到新表中，然后删除旧表，将新表的表名进行重命名。

还可以在数据表中添加 INDEX（索引）和 PRIMARY KEY（主键）来删除表中的重复记录：

25、MySQL 及 SQL 注入

如果通过网页获取用户输入的数据并将其插入一个MySQL数据库，那么就可能出现SQL注入安全的问题。

SQL注入：通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

详情请查看：http://www.runoob.com/mysql/mysql-sql-injection.html

26、MySQL导出数据

（1）将数据表导出到文本文件中：SELECT * FROM table-name INTO OUTFILE 文件路径+文件名;

注意：由于MySQL存在权限问题，会报错：

这个说明必须导出到指定的路径，可以通过 SHOW VARIABLES LIKE 'secure%' 查看该路径，也可以去MySQL配置文件中修改路径位置。

通过命令选项来设置数据输出的指定格式，例如：导出CSV格式：

SELECT ...... INTO OUTFILE语句有一下属性：

（a）LOAD DATA INFILE 是SELECT ...... INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，

使用SELECT ...... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。

（b）SELECT ...... INTO OUTFILE 'filename' 形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，

因此必须拥有FILE权限，才能使用此语法。

（c）输出不能是一个已存在的文件。防止文件数据被篡改。

（d）需要有一个登录服务器的帐号来检索文件。否则SELECT ...... INTO OUTFILE 不会起任何作用。

（e）在unix中，该文件被创建之后是可读的，权限由MySQL服务器所拥有。这意味着，虽然可以读取该文件，但是可能无法将其删除。

（2）导出表作为原始程序

mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必须的命令CREATE TABLE INSERT等。

使用mysqldump导出数据需要使用--tab选项来指定导出文件指定的目录，该目标是可写的。

例：（a）将数据表runoob_tbl导出到/tmp目录中：

（b）导出SQL格式的数据到指定文件：

（c）导出整个数据库的数据表：

（d）备份所有数据库：

（3）将数据表及数据库拷贝至其他主机

如果需要将数据拷贝至其他的MySQL服务器上，可以在mysqldump 命令中指定数据库名及数据表。

或者

27、MySQL导入数据

（1）使用LOAD DATA INFILE导入数据：将从当前目录中读取文件dump.txt，将该文件中的数据插入到当前数据库的mytbl表中。

如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果未指定，则文件在服务器上按路径读取文件。能够明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。

两个命令的FIELDS 和LINES子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS子句必须出现在LINES子句之前。如果用户指定一个FIELD子句，它的子句（TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY 也是可选的，不过，用户必须至少指定它们中的一个。）