mysql总结

最新推荐文章于 2024-10-05 14:16:38 发布

chenli532

最新推荐文章于 2024-10-05 14:16:38 发布

阅读量330

点赞数

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/chenli532/article/details/67633059

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1 。验证mysql安装

mysqladmin --version

2。 mysql的连接

mysql -uroot -p

然后输入密码

3。 mysql退出

exit

4。mysql创建数据库

create database  datatest;

删除

drop database test;

5。选择数据库

use datatest;

6。 mysql数据类型：

大致可以分为三类：数值、日期/时间和字符串类型。

数值类型：

tinyint 1字节（0，255）

smallint 2字节

mediumint 3字节

int 或 integer 4字节

bigint 8字节

float 4字节

double 8字节

时间和日期类型：

date 3字节 YYYY-MM-DD

time 3字节 HH:MM:SS

year 1字节 YYYY

datetime 8字节 YYYY-MM-DD HH:MM:SS

timestamp 8字节 YYYYMMDDHHMMSS 带时间戳

字符串类型

char、 varchar、binary、varbinary、blob、text、enum和set

char和varchar类型相似，但它们保存和检索的方式不同，

7. mysql创建数据表

语法：

CREATE TABLE table_name (column_name column_type);

举例：

create table tutorials_tbl(
   tutorial_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   tutorial_title VARCHAR(100) NOT NULL,
   tutorial_author VARCHAR(40) NOT NULL,
   submission_date DATE,
);

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔
mysql命令终止符为分号

8 mysql删除数据表

MySQL中删除数据表是非常容易操作的，但是你再进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失

语法：

DROP TABLE table_name ;

9 mysql插入数据
MySQL 表中使用 INSERT INTO SQL语句来插入数据
语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

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

10 mysql查询数据库

MySQL 数据库使用SQL SELECT语句来查询数据。

语法：

SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[OFFSET M ][LIMIT N]

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
你可以使用 LIMIT 属性来设定返回的记录数。

11 mysql 的where子句

我们知道从MySQL表中使用SQL SELECT 语句来读取数据。
如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

语法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
你可以在WHERE子句中指定任何条件。
你可以使用AND或者OR指定一个或多个条件。
WHERE子句也可以运用于SQL的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的if条件，根据 MySQL 表中的字段值来读取指定的数据。

操作符

12 mysql 的 update 更新数据

如果我们需要修改或更新MySQL中的数据，我们可以使用 SQL UPDATE 命令来操作。

语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

13 mysql的delete删除语句
语法：

DELETE FROM table_name [WHERE Clause]

如果没有指定 WHERE 子句，MySQL表中的所有记录将被删除。
你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。
当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。

14 mysql的like子句
我们知道在MySQL中使用 SQL SELECT 命令来读取数据，同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。
WHERE 子句中可以使用等号 (=) 来设定获取数据的条件：如 "author= ' sandy' "

但是有时候我们需要获取 author 字段含有 "san" 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。

SQL LIKE 子句中使用百分号(%)字符来表示任意字符。
如果没有使用百分号(%), LIKE 子句与等号（=）的效果是一样的。

语法：

SELECT field1, field2,...fieldN from table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

你可以在WHERE子句中指定任何条件。
你可以在WHERE子句中使用LIKE子句。
你可以使用LIKE子句代替等号(=)。
LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
你可以使用AND或者OR指定一个或多个条件。
你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件

例如：在where子句的like后跟上 author= ' %an%' ；就会查询出所有的字段包含an的数据。

15 mysql排序

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

语法：

SELECT field1, field2,...fieldN from  table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。默认情况下，它是按升排列。
你可以添加 WHERE...LIKE 子句来设置条件

16 mysql group by语句
GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数

语法：

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

17 mysql连接使用

你可以在SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询

JOIN 按照功能大致分为如下三类：
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

inner join

例如：

SELECT a.w3cschool_id, a.w3cschool_author, b.w3cschool_count FROM w3cschool_tbl a INNER JOIN tcount_tbl b ON a.w3cschool_author = b.w3cschool_author;

上边语句等价于：

SELECT a.w3cschool_id, a.w3cschool_author, b.w3cschool_count FROM w3cschool_tbl a, tcount_tbl b WHERE a.w3cschool_author = b.w3cschool_author;

left join

SELECT a.w3cschool_id, a.w3cschool_author, b.w3cschool_count FROM w3cschool_tbl a LEFT JOIN tcount_tbl b ON a.w3cschool_author = b.w3cschool_author;

right join

SELECT b.w3cschool_id, b.w3cschool_author, a.w3cschool_count FROM tcount_tbl a RIGHT JOIN w3cschool_tbl b ON a.w3cschool_author = b.w3cschool_author;

18 mysql nul值处理

我们已经知道MySQL使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
为了处理这种情况，MySQL提供了三大运算符:

IS NULL: 当列的值是NULL,此运算符返回true。
IS NOT NULL: 当列的值不为NULL, 运算符返回true。
<=>: 比较操作符（不同于=运算符），当比较的的两个值为NULL时返回true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值。

在MySQL中，NULL值与任何其它值的比较（即使是NULL）永远返回false，即 NULL = NULL 返回false

MySQL中处理NULL使用IS NULL和IS NOT NULL运算符

例如：

这样查询是无效的

SELECT * FROM tcount_tbl WHERE w3cschool_count = NULL;

查找数据表中 w3cschool_count 列是否为 NULL，必须使用IS NULL和IS NOT NULL，如下实例

SELECT * FROM tcount_tbl 
WHERE w3cschool_count IS NULL;

19 mysql 正则表达式

在前面我们已经了解到MySQL可以通过 LIKE ...% 来进行模糊匹配。
MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。
下表中的正则模式可应用于 REGEXP 操作符中

实例：

查找name字段中以'st'为开头的所有数据。

SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以'ok'为结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含'mar'字符串的所有数据

SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头且以'ok'字符串结尾的所有数据：

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]ok$';

20 mysql事物

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

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行
事务用来管理insert,update,delete语句

一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（稳定性）、Isolation（隔离性）、Durability（可靠性）

1、事务的原子性：一组事务，要么成功；要么撤回。
2、稳定性：有非法数据（外键约束之类），事务撤回。
3、隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的100%隔离，需要牺牲速度。
4、可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit选项决定什么时候吧事务保存到日志里。

21 mysql alter 命令

当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令
1. 删除表字段

ALTER TABLE testalter_tbl  DROP i;

2. MySQL 中使用 ADD 子句来想数据表中添加列，如下实例在表 testalter_tbl 中添加 i 字段，并定义数据类型

ALTER TABLE testalter_tbl ADD i INT;

3. 修改字段的长度

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句

ALTER TABLE testalter_tbl MODIFY c CHAR(10)

4. change修改字段名

使用 CHANGE 子句, 语法有很大的不同。在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段的类型及名称

ALTER TABLE testalter_tbl CHANGE i j BIGINT

5 修改默认值

ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;

如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。
修改默认值

ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000

删除默认值

ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

6 修改表名
如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

22 mysql 索引

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引：

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

组合索引，即一个索包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。

普通索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length
修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

创建表的时候直接指定

CREATE TABLE mytable(  
	ID INT NOT NULL,   
	username VARCHAR(16) NOT NULL,  
	INDEX [indexName] (username(length))  
);

删除索引的语法

DROP INDEX [indexName] ON mytable;

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length)) ;

修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length))

创建表的时候直接指定

CREATE TABLE mytable(  
	ID INT NOT NULL,   
	username VARCHAR(16) NOT NULL,  
	UNIQUE [indexName] (username(length))  
);

使用alter 命令添加和删除索引

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name (column_list)

添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

该语句指定了索引为 FULLTEXT ，用于全文索引。

添加索引

ALTER TABLE testalter_tbl ADD INDEX (c);

使用drop子句来删除索引

ALTER TABLE testalter_tbl DROP INDEX (c);

使用alter命令添加和删除主键
主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。

ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键

ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除指定时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名.

显示索引信息

SHOW INDEX FROM table_name\G

23 mysql 临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，MySQL会自动删除表并释放所有空间。

如果你使用了其他MySQL客户端程序连接MySQL数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

CREATE TEMPORARY TABLE

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

删除mysql临时表

默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你也可以在当前MySQL会话使用 DROP TABLE 命令来手动删除临时表

24 mysql复制表

如果我们需要完全的复制MySQL的数据表，包括表的结构，索引，默认值等。如果仅仅使用CREATE TABLE ... SELECT 命令，是无法实现的

使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令将完全的复制数据表结构。
如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现

INSERT INTO clone_tbl (w3cschool_id,
                        w3cschool_title,
                        w3cschool_author,
                        submission_date)
SELECT w3cschool_id,tutorial_title,
        w3cschool_author,submission_date
FROM w3cschool_tbl;

25 mysql处理重复数据

防止表中出现重复数据

你可以在MySQL数据表中设置指定的字段为 PRIMARY KEY（主键）或者 UNIQUE（唯一）索引来保证数据的唯一性。
让我们尝试一个实例：下表中无索引及主键，所以该表允许出现多条重复记录。

CREATE TABLE person_tbl
(
    first_name CHAR(20),
    last_name CHAR(20),
    sex CHAR(10)
);

如果你想设置表中字段first_name，last_name数据不能重复，你可以设置双主键模式来设置数据的唯一性，如果你设置了双主键，那么那个键的默认值不能为NULL，可设置为NOT NULL。如下所示：

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);

如果我们设置了唯一索引，那么在插入重复数据时，SQL语句将无法执行成功,并抛出错。
INSERT IGNORE INTO与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)

INSERT IGNORE INTO当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。而REPLACE INTO 如果存在primary 或 unique相同的记录，则先删除掉。再插入新记录。

另一种设置数据的唯一性方法是添加一个UNIQUE索引，如下所示

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10)
   UNIQUE (last_name, first_name)
);

统计重复数据

以下我们将统计表中 first_name 和 last_name的重复记录数：

mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

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

确定哪一列包含的值可能会重复。
在列选择列表使用COUNT(*)列出的那些列。
在GROUP BY子句中列出的列。
HAVING子句设置重复数大于1。

过滤重复数据

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

mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl
    -> ORDER BY last_name;

你也可以使用 GROUP BY 来读取数据表中不重复的数据：

mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);

删除重复数据

如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex
       FROM person_tbl;
       GROUP BY (last_name, first_name);
mysql> DROP TABLE person_tbl;
mysql> ALTER TABLE tmp RENAME TO person_tbl;

26 mysql 及sql注入

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

所谓SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。
我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
{
   $result = mysql_query("SELECT * FROM users 
                          WHERE username=$matches[0]");
}
 else 
{
   echo "username 输入异常";
}

让我们看下在没有过滤特殊字符时，出现的SQL情况：

// 设定$name 中插入了我们不需要的SQL语句
$name = "Qadir'; DELETE FROM users;";
mysql_query("SELECT * FROM users WHERE name='{$name}'");

27 mysql导出数据

MySQL中你可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上。

以下实例中我们将数据表 w3cschool_tbl 数据导出到 /tmp/tutorials.txt 文件中:

mysql> SELECT * FROM tutorials_tbl 
    -> INTO OUTFILE '/tmp/tutorials.txt'

使用select 。。。 into outfile

LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。
SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。
输出不能是一个已存在的文件。防止文件数据被篡改。
你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。
在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。

导出表作为原始数据

mysqldump是MySQL用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。
使用mysqldump导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。
以下实例将数据表 tutorials_tbl 导出到 /tmp 目录中：