MySQL

最新推荐文章于 2025-01-10 17:01:35 发布
最新推荐文章于 2025-01-10 17:01:35 发布
阅读量2.8k 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： 学习记录 文章标签： mysql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_45859425/article/details/122269439
这篇博客详细介绍了MySQL数据库的基础操作，包括创建、删除和选择数据库，以及数据类型。此外，还讲解了如何插入、查询、更新和删除数据，以及使用WHERE子句进行条件查询。文章还涵盖了事务处理，如BEGIN、COMMIT、ROLLBACK，以及如何防止SQL注入。除此之外，还介绍了各种字符串、数字和日期函数，以及如何处理重复数据。最后，讨论了SQL注入防护和如何导出与导入数据。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

MySQL学习笔记

1.创建数据库

--普通用户--
CREATE DATEBASE database_name;			

--root用户--
[root@host]# mysqladmin -u root -p create NOWCODER
Enter password:******

2. 删除数据库

--drop命令--
DROP  DATABASE database_name;

--使用mysqladmin删除--
[root@host]# mysqladmin -u root -p drop NOWCODER
Enter password:******

3.选择数据库

--use命令--
[root@host]# mysql -u root -p
Enter password:******
mysql> use NOWCODER;
Database changed

4.MySQL数据类型

  • 数值类型
类型大小范围（有符号）范围（无符号）用途
TINYINT1 字节(-128，127)(0，255)小整数值
SMALLINT2 字节(-32 768，32 767)(0，65 535)大整数值
MEDIUMINT3 字节(-8 388 608，8 388 607)(0，16 777 215)大整数值
INT或INTEGER4 字节(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值
BIGINT8 字节(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值
FLOAT4 字节(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度 浮点数值
DOUBLE8 字节(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度 浮点数值
DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值
  • 日期和时间类型
类型大小 (字节)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值，时间戳
  • 字符串类型
类型大小用途
CHAR0-255字节定长字符串
VARCHAR0-65535 字节变长字符串
TINYBLOB0-255字节不超过 255 个字符的二进制字符串
TINYTEXT0-255字节短文本字符串
BLOB0-65 535字节二进制形式的长文本数据
TEXT0-65 535字节长文本数据
MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215字节中等长度文本数据
LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据
LONGTEXT0-4 294 967 295字节极大文本数据

5. 创建数据表

创建数据表通用语法：

CREATE TABLE table_name (column_name column_type);

实例：

root@host# mysql -u root -p
Enter password:*******
mysql> use NOWCODER;
Database changed
mysql> CREATE TABLE nowcoder_tbl(
   -> nowcoder_id INT NOT NULL AUTO_INCREMENT,
   -> nowcoder_title VARCHAR(100) NOT NULL,
   -> nowcoder_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( nowcoder_id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)

6.删除数据表

语法：

DROP TABLE table_name ;

7. 插入数据

语法：

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

8. 查询数据

语法：

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

9. WHERE子句

语法：

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

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

mysql> select * from nowcoder_tbl where binary nowcoder_author='nowcoder.com';
Empty set (0.00 sec)

mysql> select * from nowcoder_tbl where binary nowcoder_author='NOWCODER.COM';
+-------------+----------------+-----------------+-----------------+
| nowcoder_id | nowcoder_title | nowcoder_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           3 | JAVA 教程       | NOWCODER.COM    | 2019-10-06      |
|           4 | 学习 Python     | NOWCODER.COM    | 2019-10-06      |
+-------------+----------------+-----------------+-----------------+
2 rows in set (0.00 sec)

10. UPDATE更新

语法：

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

实例：

mysql> update nowcoder_tbl set nowcoder_title='***习 C++' where nowcoder_id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from nowcoder_tbl where nowcoder_id = 3;
+-------------+----------------+-----------------+-----------------+
| nowcoder_id | nowcoder_title | nowcoder_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           3 | 学习 C++        | NOWCODER.COM    | 2019-10-06      |
+-------------+----------------+-----------------+-----------------+
1 row in set (0.00 sec)

11. DELETE语句

语法：

DELETE FROM table_name [WHERE Clause]

实例：

mysql> delete from nowcoder_tbl where nowcoder_id = 3;
Query OK, 1 row affected (0.01 sec)

mysql> select * from nowcoder_tbl where nowcoder_id = 3;
Empty set (0.00 sec)

12.LIKE语句

SQL LIKE 子句中使用百分号 **%**字符来表示任意字符，类似于UNIX或正则表达式中的星号 *****。

如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

语法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

实例：

mysql> select * from nowcoder_tbl where nowcoder_author like '%COM';
+-------------+----------------+-----------------+-----------------+
| nowcoder_id | nowcoder_title | nowcoder_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           3 | JAVA 教程       | NOWCODER.COM    | 2019-10-06      |
|           4 | 学习 Python     | NOWCODER.COM    | 2019-10-06      |
+-------------+----------------+-----------------+-----------------+
2 rows in set (0.00 sec)

13. UNION操作符

描述：

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

语法：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

参数：

  • expression1, expression2, … expression_n：要检索的列
  • tables：要检索的数据表
  • WHERE conditions：可选，检索条件
  • DISTINCT：可选，去重
  • ALL：可选，返回所有结果集，包括重复数据

实例：

演示数据库：

Websites表

mysql> select * from websites;
+----+---------------+-------------------------------+-------+---------+
| id | name          | url                           | alexa | country |
+----+---------------+-------------------------------+-------+---------+
|  1 | Google        | https://www.google.com        |     1 | USA     |
|  2 | 淘宝          | https://www.taobao.com        |    13 | CN      |
|  3 | 牛客网        | https://www.nowcoder.com      |  6524 | CN      |
|  4 | 微博          | https://www.weibo.com         |    20 | CN      |
|  5 | Facebook      | https://www.facebook.com      |     3 | USA     |
|  7 | Stackoverflow | https://www.stackoverflow.com |     0 | IND     |
+----+---------------+-------------------------------+-------+---------+
6 rows in set (0.00 sec)

apps表

mysql> select * from apps;
+----+------------+-------------------+---------+
| id | app_name   | url               | country |
+----+------------+-------------------+---------+
|  1 | QQ APP     | http://im.qq.com  | CN      |
|  2 | 微博 APP   | http://weibo.com  | CN      |
|  3 | 淘宝 APP   | http://taobao.com | CN      |
+----+------------+-------------------+---------+
3 rows in set (0.00 sec)

下面的 SQL 语句从 “Websites” 和 “apps” 表中选取所有不同的country（只有不同的值）：

mysql> SELECT country FROM Websites
    -> UNION
    -> SELECT country FROM apps
    -> ORDER BY country;
+---------+
| country |
+---------+
| CN      |
| IND     |
| USA     |
+---------+
3 rows in set (0.00 sec)

mysql> SELECT country FROM Websites
    -> UNION ALL
    -> SELECT country FROM apps
    -> ORDER BY country;
+---------+
| country |
+---------+
| CN      |
| CN      |
| CN      |
| CN      |
| CN      |
| CN      |
| IND     |
| USA     |
| USA     |
+---------+
9 rows in set (0.00 sec)

mysql> SELECT country, name FROM Websites
    -> WHERE country='CN'
    -> UNION ALL
    -> SELECT country, app_name FROM apps
    -> WHERE country='CN'
    -> ORDER BY country;
+---------+------------+
| country | name       |
+---------+------------+
| CN      | 淘宝       |
| CN      | 牛客网     |
| CN      | 微博       |
| CN      | QQ APP     |
| CN      | 微博 APP   |
| CN      | 淘宝 APP   |
+---------+------------+
6 rows in set (0.00 sec)

14. 排序

语法：

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]

15. GROUP BY语句

语法：

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

实例：

mysql> SELECT * FROM employee_tbl;
+----+--------+---------------------+--------+
| id | name   | date                | singin |
+----+--------+---------------------+--------+
|  1 | 小明   | 2016-04-22 15:25:33 |      1 |
|  2 | 小王   | 2016-04-20 15:25:47 |      3 |
|  3 | 小丽   | 2016-04-19 15:26:02 |      2 |
|  4 | 小王   | 2016-04-07 15:26:14 |      4 |
|  5 | 小明   | 2016-04-11 15:26:40 |      4 |
|  6 | 小明   | 2016-04-04 15:26:54 |      2 |
+----+--------+---------------------+--------+
6 rows in set (0.00 sec)


mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name   | COUNT(*) |
+--------+----------+
| 小明   |        3 |
| 小王   |        2 |
| 小丽   |        1 |
+--------+----------+
3 rows in set (0.01 sec)

使用WITH ROLLUO:WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。

实例：

mysql> SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name   | singin_count |
+--------+--------------+
| 小丽   |            2 |
| 小明   |            7 |
| 小王   |            7 |
| NULL   |           16 |
+--------+--------------+
4 rows in set (0.01 sec)

我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：

SELECT coalesce(a, b, c);

参数说明：如果a == null,则选择 b；如果b == null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。

mysql> SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name, '总数')   | singin_count |
+--------------------------+--------------+
| 小丽                     |            2 |
| 小明                     |            7 |
| 小王                     |            7 |
| 总数                     |           16 |
+--------------------------+--------------+
4 rows in set (0.00 sec)

16. JOIN的使用

JOIN分类：

  • INNER JOIN:获取两个表中字段匹配关系的记录。
  • LEFT JOIN:获取左表所有记录，即使右表没有对应匹配的记录。
  • RIGHT JOIN: 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

数据表记录：

mysql> select * from tcount_tbl;
+-----------------+----------------+
| nowcoder_author | nowcoder_count |
+-----------------+----------------+
| 牛客教程        |             10 |
| NOWCODER.COM    |             20 |
| Google          |             22 |
+-----------------+----------------+
3 rows in set (0.00 sec)

mysql> select * from nowcoder_tbl;
+-------------+----------------+-----------------+-----------------+
| nowcoder_id | nowcoder_title | nowcoder_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           1 | 学习 PHP       | 牛客教程        | 2017-04-12      |
|           2 | 学习 MySQL     | 牛客教程        | 2017-04-12      |
|           3 | 学习 Java      | NOWCODER.COM    | 2015-05-01      |
|           4 | 学习 Python    | NOWCODER.COM    | 2016-03-06      |
|           5 | 学习 C         | FK              | 2017-04-05      |
+-------------+----------------+-----------------+-----------------+
5 rows in set (0.00 sec)

INNER JOIN实例：

mysql> SELECT a.nowcoder_id, a.nowcoder_author, b.nowcoder_count FROM nowcoder_tbl a INNER JOIN tcount_tbl b ON a.nowcoder_author = b.nowcoder_author;
+-------------+-----------------+----------------+
| nowcoder_id | nowcoder_author | nowcoder_count |
+-------------+-----------------+----------------+
|           1 | 牛客教程        |             10 |
|           2 | 牛客教程        |             10 |
|           3 | NOWCODER.COM    |             20 |
|           4 | NOWCODER.COM    |             20 |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)


--上述命令等价于--
mysql> SELECT a.nowcoder_id, a.nowcoder_author, b.nowcoder_count FROM nowcoder_tbl a,tcount_tbl b where a.nowcoder_author=b.nowcoder_author;
+-------------+-----------------+----------------+
| nowcoder_id | nowcoder_author | nowcoder_count |
+-------------+-----------------+----------------+
|           1 | 牛客教程        |             10 |
|           2 | 牛客教程        |             10 |
|           3 | NOWCODER.COM    |             20 |
|           4 | NOWCODER.COM    |             20 |
+-------------+-----------------+----------------+
4 rows in set (0.00 sec)

LEFT JOIN实例：

mysql> SELECT a.nowcoder_id, a.nowcoder_author, b.nowcoder_count FROM nowcoder_tbl a LEFT JOIN tcount_tbl b ON a.nowcoder_author = b.nowcoder_author;
+-------------+-----------------+----------------+
| nowcoder_id | nowcoder_author | nowcoder_count |
+-------------+-----------------+----------------+
|           1 | 牛客教程        |             10 |
|           2 | 牛客教程        |             10 |
|           3 | NOWCODER.COM    |             20 |
|           4 | NOWCODER.COM    |             20 |
|           5 | FK              |           NULL |
+-------------+-----------------+----------------+
5 rows in set (0.00 sec)

RIGHT JOIN 实例：

mysql> SELECT a.nowcoder_id, a.nowcoder_author, b.nowcoder_count FROM nowcoder_tbl a RIGHT JOIN tcount_tbl b ON a.nowcoder_author = b.nowcoder_author;
+-------------+-----------------+----------------+
| nowcoder_id | nowcoder_author | nowcoder_count |
+-------------+-----------------+----------------+
|           1 | 牛客教程        |             10 |
|           2 | 牛客教程        |             10 |
|           3 | NOWCODER.COM    |             20 |
|           4 | NOWCODER.COM    |             20 |
|        NULL | NULL            |             22 |
+-------------+-----------------+----------------+
5 rows in set (0.00 sec)

17. NULL值处理

三大运算符：

  • IS NULL
  • IS NOT NULL
  • <=>

IFNULL:将NULL值转为0；

select * , columnName1+ifnull(columnName2,0) from tableName;

实例：

mysql> select * from nowcoder_test_tbl where nowcoder_count is null;
+-----------------+----------------+
| nowcoder_author | nowcoder_count |
+-----------------+----------------+
| 牛客教程        |           NULL |
| Google          |           NULL |
+-----------------+----------------+
2 rows in set (0.00 sec)

mysql> select * from nowcoder_test_tbl where nowcoder_count is not null;
+-----------------+----------------+
| nowcoder_author | nowcoder_count |
+-----------------+----------------+
| NOWCODER        |             20 |
| FK              |             20 |
+-----------------+----------------+
2 rows in set (0.00 sec)

18. 正则表达式

模式描述
^匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。
$匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。
.匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用象 '[.\n]' 的模式。
[...]字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。
[^...]负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。
p1|p2|p3匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。
*匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n}n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
{n,m}m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

实例：

--查找name字段中以'st'为开头的所有数据--
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

--查找name字段中以'ok'为结尾的所有数据--
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

--查找name字段中包含'mar'字符串的所有数据--
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

--查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据--
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

19. 事务

四个条件

  • 原子性Atomicity：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
  • 一致性Consistency：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。
  • 隔离性Isolation：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
  • 持久性Durability：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务控制语句

  • BEGIN, START TRANSACTION:显式的开启一个事务；
  • COMMIT:提交事务，并使已对数据库进行的所有修改成为永久性的；
  • ROLLBACK:结束用户的事务，并撤销正在进行的所以未提交的修改；
  • SAVEPOINT indetifier:允许在事务中创建一个保存点，一个事务中可以有多个保存点；
  • RELEASE SAVEPOINT indentifier:删除一个保存点，如果没有保存点，则会抛出异常；
  • ROLLBACK TO indentifier:把事务回滚到保存点；
  • SET TRANSACTION：设置事务隔离级别；InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

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

  1. 用BEGIN,ROLLBACK,COMMIT实现；
  2. 直接用SET来改变MySQL的自动提交模式
    • SET AUTOCOMMIT = 0:禁止自动提交
    • SET AUTOCOMMIT = 1 :允许自动提交

20. ALTER命令

删除

mysql> ALTER TABLE testalter_tbl  DROP i;

添加

mysql> ALTER TABLE testalter_tbl ADD i INT;

修改

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

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

修改字段默认值

--修改默认值--
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;


--删除默认值--
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

修改表名

ALTER TABLE testalter_tbl RENAME TO alter_tbl;

20. 索引

普通索引

  • 创建索引

    CREATE INDEX indexName ON mytable(username(length));
--如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。--

  • 修改表结构（添加索引）

    ALTER table tableName ADD INDEX indexName(columnName)

  • 创建表的时候直接指定

    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 table mytable ADD UNIQUE [indexName] (username(length))

  • 创建表的时候直接指定

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

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

  • ALTER TABLE tabel_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 ，用于全文索引。

    实例：

    mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
mysql> ALTER TABLE testalter_tbl DROP INDEX c;

使用ALTER命令添加和删除主键

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

实例：

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

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

显示索引信息

实例：

mysql> SHOW INDEX FROM table_name; \G

21.临时表

实例：

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)

当你使用 SHOW TABLES命令显示数据表列表时，你将无法看到 SalesSummary表。

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

删除临时表

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'NOWCODER.SalesSummary' doesn't exist

22.复制表

  1. 获取数据表的完整结构：

    mysql> show create table nowcoder_tbl \G;
*************************** 1. row ***************************
       Table: nowcoder_tbl
Create Table: CREATE TABLE `nowcoder_tbl` (
  `nowcoder_id` int(11) NOT NULL AUTO_INCREMENT,
  `nowcoder_title` varchar(100) NOT NULL,
  `nowcoder_author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL,
  PRIMARY KEY (`nowcoder_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

ERROR:
No query specified

  2. 修改表名并执行数据

    mysql> create table clone_tbl (
    -> nowcoder_id int(11) not null auto_increment,
    -> nowcoder_title varchar(100) not null default '',
    -> nowcoder_author varchar(40) not null default '',
    -> submission_date date default null,
    -> primary key (nowcoder_id)
    -> ) engine=innodb;
Query OK, 0 rows affected (0.05 sec)

  3.  mysql> insert into clone_tbl
     -> (nowcoder_id, nowcoder_title, nowcoder_author, submission_date)
     -> select
     -> nowcoder_id, nowcoder_title, nowcoder_author, submission_date
     -> from nowcoder_tbl;
 Query OK, 5 rows affected (0.01 sec)
 Records: 5  Duplicates: 0  Warnings: 0

23. 元数据

数据库和数据表列表

SHOW TABLES;
SHOW DATABASES;

获取服务器元数据

命令描述
SELECT VERSION( )服务器版本信息
SELECT DATABASE( )当前数据库名 (或者返回空)
SELECT USER( )当前用户名
SHOW STATUS服务器状态
SHOW VARIABLES服务器配置变量

24. 序列使用

使用AUTO_INCREMENT

实例：

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
    -> (NULL,'housefly','2001-09-10','kitchen'),
    -> (NULL,'millipede','2001-09-10','driveway'),
    -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

获取AUTO_INCREMENT值

在MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。

重置序列

mysql> ALTER TABLE insect DROP id;
mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);

设置序列开始值

mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;


mysql> ALTER TABLE t AUTO_INCREMENT = 100;

25. 处理重复数据

防止表中出现重复数据

你可以在 MySQL 数据表中设置指定的字段为 **PRIMARY KEY（主键）**或者 **UNIQUE（唯一）**索引来保证数据的唯一性。

INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。

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

统计重复数据数

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

过滤重复数据

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

删除重复元素

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


mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);

26. SQL注入

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

防止SQL注入，我们需要注意以下几个要点：

  1. 永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双"-"进行转换等。
  2. 永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
  3. 永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
  4. 不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
  5. 应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
  6. sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。

27.导出数据

使用 SELECT … INTO OUTFILE 语句导出数据

mysql> SELECT * FROM nowcoder_tbl  
    -> INTO OUTFILE '/tmp/nowcoder.txt';
    
--CSV格式--
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/nowcoder.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';
    
    
--逗号隔开--
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

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 -u root -p --no-create-info \
                --tab=/tmp NOWCODER nowcoder_tbl
password ******

导出SQL格式的数据

$ mysqldump -u root -p NOWCODER nowcoder_tbl > dump.txt
password ******


$ mysqldump -u root -p NOWCODER > database_dump.txt
password ******

$ mysqldump -u root -p --all-databases > database_dump.txt
password ******

导入SQL格式的数据

$ mysql -u root -p database_name < dump.txt
password *****

28. 导入数据

  1. mysql命令导入

    --mysql -u用户名    -p密码    <  要导入的数据库数据--
mysql -uroot -p123456 < nowcoder.sql

  2. source命令导入

    mysql> create database abc;      # 创建数据库
mysql> use abc;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source /home/abc/abc.sql  # 导入备份数据库

  3. 使用LOAD DATA导入

    mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;


mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';
  
  
mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
    -> INTO TABLE mytbl (b, c, a);

  4. 使用mysqlinport导入

    --从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：--
$ mysqlimport -u root -p --local mytbl dump.txt
password *****

--mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：--
$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  mytbl dump.txt
password *****

--mysqlimport 语句中使用 --columns 选项来设置列的顺序：--
$ mysqlimport -u root -p --local --columns=b,c,a \
    mytbl dump.txt
password *****

mysqlinport常用选项

选项功能
-d or --delete新数据导入数据表中之前删除数据数据表中的所有信息
-f or --force不管是否遇到错误，mysqlimport将强制继续插入数据
-i or --ignoremysqlimport跳过或者忽略那些有相同唯一 关键字的行， 导入文件中的数据将被忽略。
-l or -lock-tables数据被插入之前锁住表，这样就防止了， 你在更新数据库时，用户的查询和更新受到影响。
-r or -replace这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录。
--fields-enclosed- by= char指定文本文件中数据的记录时以什么括起的， 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。
--fields-terminated- by=char指定各个数据的值之间的分隔符，在句号分隔的文件中， 分隔符是句号。您可以用此选项指定数据之间的分隔符。 默认的分隔符是跳格符（Tab）
--lines-terminated- by=str此选项指定文本文件中行与行之间数据的分隔字符串 或者字符。 默认的情况下mysqlimport以newline为行分隔符。 您可以选择用一个字符串来替代一个单个的字符： 一个新行或者一个回车。

29. 函数

字符串函数

函数描述实例
ASCII(s)返回字符串 s 的第一个字符的 ASCII 码。返回 CustomerName 字段第一个字母的 ASCII 码：
SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;
CHAR_LENGTH(s)返回字符串 s 的字符数返回字符串 NOWCODER 的字符数
SELECT CHAR_LENGTH("NOWCODER") AS LengthOfString;
CHARACTER_LENGTH(s)返回字符串 s 的字符数返回字符串 NOWCODER 的字符数
SELECT CHARACTER_LENGTH("NOWCODER") AS LengthOfString;
CONCAT(s1,s2...sn)字符串 s1,s2 等多个字符串合并为一个字符串合并多个字符串
SELECT CONCAT("SQL ", "Nowcoder ", "Gooogle ", "Facebook") AS ConcatenatedString;
CONCAT_WS(x, s1,s2...sn)同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符合并多个字符串，并添加分隔符：
SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
FIELD(s,s1,s2...)返回第一个字符串 s 在字符串列表(s1,s2...)中的位置返回字符串 c 在列表值中的位置：
SELECT FIELD("c", "a", "b", "c", "d", "e");
FIND_IN_SET(s1,s2)返回在字符串s2中与s1匹配的字符串的位置返回字符串 c 在指定字符串中的位置：
SELECT FIND_IN_SET("c", "a,b,c,d,e");
FORMAT(x,n)函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。格式化数字 "#,###.##" 形式：
SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56
INSERT(s1,x,len,s2)字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串从字符串第一个位置开始的 8 个字符替换为 nowcoder：
SELECT INSERT("facebook.com", 1, 8, "nowcoder"); -- 输出：nowcoder.com
LOCATE(s1,s)从字符串 s 中获取 s1 的开始位置获取 b 在字符串 abc 中的位置：
SELECT LOCATE('st','myteststring'); -- 5
返回字符串 abc 中 b 的位置：
SELECT LOCATE('b', 'abc') -- 2
LCASE(s)将字符串 s 的所有字母变成小写字母字符串 NOWCODER 转换为小写：
SELECT LCASE('NOWCODER') -- nowcoder
LEFT(s,n)返回字符串 s 的前 n 个字符返回字符串 nowcoder 中的前两个字符：
SELECT LEFT('nowcoder',2) -- no
LOWER(s)将字符串 s 的所有字母变成小写字母字符串 NOWCODER 转换为小写：
SELECT LOWER('NOWCODER') -- nowcoder
LPAD(s1,len,s2)在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len将字符串 xx 填充到 abc 字符串的开始处：
SELECT LPAD('abc',5,'xx') -- xxabc
LTRIM(s)去掉字符串 s 开始处的空格去掉字符串 NOWCODER 开始处的空格：
SELECT LTRIM(" NOWCODER") AS LeftTrimmedString;-- NOWCODER
MID(s,n,len)从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len)从字符串 NOWCODER 中的第 2 个位置截取 3个 字符：
SELECT MID("NOWCODER", 2, 3) AS ExtractString; -- OWC
POSITION(s1 IN s)从字符串 s 中获取 s1 的开始位置返回字符串 abc 中 b 的位置：
SELECT POSITION('b' in 'abc') -- 2
REPEAT(s,n)将字符串 s 重复 n 次将字符串 nowcoder 重复三次：
SELECT REPEAT('nowcoder',3) -- nowcodernowcodernowcoder
REPLACE(s,s1,s2)将字符串 s2 替代字符串 s 中的字符串 s1将字符串 abc 中的字符 a 替换为字符 x：
SELECT REPLACE('abc','a','x') --xbc
REVERSE(s)将字符串s的顺序反过来将字符串 abc 的顺序反过来：
SELECT REVERSE('abc') -- cba
RIGHT(s,n)返回字符串 s 的后 n 个字符返回字符串 nowcoder 的后两个字符：
SELECT RIGHT('nowcoder',2) -- er
RPAD(s1,len,s2)在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len将字符串 xx 填充到 abc 字符串的结尾处：
SELECT RPAD('abc',5,'xx') -- abcxx
RTRIM(s)去掉字符串 s 结尾处的空格去掉字符串 NOWCODER 的末尾空格：
SELECT RTRIM("NOWCODER ") AS RightTrimmedString; -- NOWCODER
SPACE(n)返回 n 个空格返回 10 个空格：
SELECT SPACE(10);
STRCMP(s1,s2)比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1比较字符串：
SELECT STRCMP("nowcoder", "nowcoder"); -- 0
SUBSTR(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串从字符串 NOWCODER 中的第 2 个位置截取 3个 字符：
SELECT SUBSTR("NOWCODER", 2, 3) AS ExtractString; -- OWC
SUBSTRING(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串从字符串 NOWCODER 中的第 2 个位置截取 3个 字符：
SELECT SUBSTRING("NOWCODER", 2, 3) AS ExtractString; -- OWC
SUBSTRING_INDEX(s, delimiter, number)返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。SELECT SUBSTRING_INDEX('a*b','*',1) -- a
SELECT SUBSTRING_INDEX('a*b','*',-1) -- b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1) -- c
TRIM(s)去掉字符串 s 开始和结尾处的空格去掉字符串 NOWCODER 的首尾空格：
SELECT TRIM(' NOWCODER ') AS TrimmedString;
UCASE(s)将字符串转换为大写将字符串 nowcoder 转换为大写：
SELECT UCASE("nowcoder"); -- NOWCODER
UPPER(s)将字符串转换为大写将字符串 nowcoder 转换为大写：
SELECT UPPER("nowcoder"); -- NOWCODER

数字函数

函数名描述实例
ABS(x)返回 x 的绝对值返回 -1 的绝对值：
SELECT ABS(-1) -- 返回1
ACOS(x)求 x 的反余弦值(参数是弧度)SELECT ACOS(0.25);
ASIN(x)求反正弦值(参数是弧度)SELECT ASIN(0.25);
ATAN(x)求反正切值(参数是弧度)SELECT ATAN(2.5);
ATAN2(n, m)求反正切值(参数是弧度)SELECT ATAN2(-0.8, 2);
AVG(expression)返回一个表达式的平均值，expression 是一个字段返回 Products 表中Price 字段的平均值：
SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x)返回大于或等于 x 的最小整数SELECT CEIL(1.5) -- 返回2
CEILING(x)返回大于或等于 x 的最小整数SELECT CEIL(1.5) -- 返回2
COS(x)求余弦值(参数是弧度)SELECT COS(2);
COT(x)求余切值(参数是弧度)SELECT COT(6);
COUNT(expression)返回查询的记录总数，expression 参数是一个字段或者 * 号返回 Products 表中 products 字段总共有多少条记录：
SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x)将弧度转换为角度SELECT DEGREES(3.1415926535898) -- 180
n DIV m整除，n 为被除数，m 为除数计算 10 除于 5：
SELECT 10 DIV 5; -- 2
EXP(x)返回 e 的 x 次方计算 e 的三次方：
SELECT EXP(3) -- 20.085536923188
FLOOR(x)返回小于或等于 x 的最大整数小于或等于 1.5 的整数：
SELECT FLOOR(1.5) -- 返回1
GREATEST(expr1, expr2, expr3, ...)返回列表中的最大值返回以下数字列表中的最大值：
SELECT GREATEST(3, 12, 34, 8, 25); -- 34
返回以下字符串列表中的最大值：
SELECT GREATEST("Google", "Nowcoder", "Apple"); -- Nowcoder
LEAST(expr1, expr2, expr3, ...)返回列表中的最小值返回以下数字列表中的最小值：
SELECT LEAST(3, 12, 34, 8, 25); -- 3
返回以下字符串列表中的最小值：SELECT LEAST("Google", "Nowcoder", "Apple"); -- Apple
LN返回数字的自然对数返回 2 的自然对数：
SELECT LN(2); -- 0.6931471805599453
LOG(x)返回自然对数(以 e 为底的对数)SELECT LOG(20.085536923188) -- 3
LOG10(x)返回以 10 为底的对数SELECT LOG10(100) -- 2
LOG2(x)返回以 2 为底的对数返回以 2 为底 6 的对数：
SELECT LOG2(6); -- 2.584962500721156
MAX(expression)返回字段 expression 中的最大值返回数据表 Products 中字段 Price 的最大值：
SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression)返回字段 expression 中的最小值返回数据表 Products 中字段 Price 的最小值：
SELECT MIN(Price) AS LargestPrice FROM Products;
MOD(x,y)返回 x 除以 y 以后的余数5 除于 2 的余数：
SELECT MOD(5,2) -- 1
PI()返回圆周率(3.141593）SELECT PI() --3.141593
POW(x,y)返回 x 的 y 次方2 的 3 次方：
SELECT POW(2,3) -- 8
POWER(x,y)返回 x 的 y 次方2 的 3 次方：
SELECT POWER(2,3) -- 8
RADIANS(x)将角度转换为弧度180 度转换为弧度：
SELECT RADIANS(180) -- 3.1415926535898
RAND()返回 0 到 1 的随机数SELECT RAND() --0.93099315644334
ROUND(x)返回离 x 最近的整数SELECT ROUND(1.23456) --1
SIGN(x)返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1SELECT SIGN(-10) -- (-1)
SIN(x)求正弦值(参数是弧度)SELECT SIN(RADIANS(30)) -- 0.5
SQRT(x)返回x的平方根25 的平方根：
SELECT SQRT(25) -- 5
SUM(expression)返回指定字段的总和计算 OrderDetails 表中字段 Quantity 的总和：
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x)求正切值(参数是弧度)SELECT TAN(1.75); -- -5.52037992250933
TRUNCATE(x,y)返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）SELECT TRUNCATE(1.23456,3) -- 1.234

日期函数

函数名描述实例
ADDDATE(d,n)计算起始日期 d 加上 n 天的日期SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
->2017-06-25
ADDTIME(t,n)时间 t 加上 n 秒的时间SELECT ADDTIME('2011-11-11 11:11:11', 5);
->2011-11-11 11:11:16 (秒)
CURDATE()返回当前日期SELECT CURDATE();
-> 2018-09-19
CURRENT_DATE()返回当前日期SELECT CURRENT_DATE();
-> 2018-09-19
CURRENT_TIME返回当前时间SELECT CURRENT_TIME();
-> 19:59:02
CURRENT_TIMESTAMP()返回当前日期和时间SELECT CURRENT_TIMESTAMP();
-> 2018-09-19 20:57:43
CURTIME()返回当前时间SELECT CURTIME();
-> 19:59:02
DATE()从日期或日期时间表达式中提取日期值SELECT DATE("2017-06-15");
-> 2017-06-15
DATEDIFF(d1,d2)计算日期 d1->d2 之间相隔的天数SELECT DATEDIFF('2001-01-01','2001-02-02');
-> -32
DATE_ADD(d，INTERVAL expr type)计算起始日期 d 加上一个时间段后的日期SELECT ADDDATE('2011-11-11 11:11:11',1);
-> 2011-11-12 11:11:11 (默认是天)
SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE);
-> 2011-11-11 11:16:11 (TYPE的取值与上面那个列出来的函数类似)
DATE_FORMAT(d,f)按表达式 f的要求显示日期 dSELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r');
-> 2011-11-11 11:11:11 AM
DATE_SUB(date,INTERVAL expr type)函数从日期减去指定的时间间隔。Orders 表中 OrderDate 字段减去 2 天：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders
DAY(d)返回日期值 d 的日期部分SELECT DAY("2017-06-15");
-> 15
DAYNAME(d)返回日期 d 是星期几，如 Monday,TuesdaySELECT DAYNAME('2011-11-11 11:11:11');
->Friday
DAYOFMONTH(d)计算日期 d 是本月的第几天SELECT DAYOFMONTH('2011-11-11 11:11:11');
->11
DAYOFWEEK(d)日期 d 今天是星期几，1 星期日，2 星期一，以此类推SELECT DAYOFWEEK('2011-11-11 11:11:11');
->6
DAYOFYEAR(d)计算日期 d 是本年的第几天SELECT DAYOFYEAR('2011-11-11 11:11:11');
->315
EXTRACT(type FROM d)从日期 d 中获取指定的值，type 指定返回的值。 type可取值为： MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTHSELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11');
-> 11
FROM_DAYS(n)计算从 0000 年 1 月 1 日开始 n 天后的日期SELECT FROM_DAYS(1111);
-> 0003-01-16
HOUR(t)返回 t 中的小时值SELECT HOUR('1:2:3');
-> 1
LAST_DAY(d)返回给给定日期的那一月份的最后一天SELECT LAST_DAY("2017-06-20");
-> 2017-06-30
LOCALTIME()返回当前日期和时间SELECT LOCALTIME();
-> 2018-09-19 20:57:43
LOCALTIMESTAMP()返回当前日期和时间SELECT LOCALTIMESTAMP();
-> 2018-09-19 20:57:43
MAKEDATE(year, day-of-year)基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期SELECT MAKEDATE(2017, 3);
-> 2017-01-03
MAKETIME(hour, minute, second)组合时间，参数分别为小时、分钟、秒SELECT MAKETIME(11, 35, 4);
-> 11:35:04
MICROSECOND(date)返回日期参数所对应的微秒数SELECT MICROSECOND("2017-06-20 09:34:00.000023");
-> 23
MINUTE(t)返回 t 中的分钟值SELECT MINUTE('1:2:3');
-> 2
MONTHNAME(d)返回日期当中的月份名称，如 JanyarySELECT MONTHNAME('2011-11-11 11:11:11');
-> November
MONTH(d)返回日期d中的月份值，1 到 12SELECT MONTH('2011-11-11 11:11:11');
->11
NOW()返回当前日期和时间SELECT NOW();
-> 2018-09-19 20:57:43
PERIOD_ADD(period, number)为 年-月 组合日期添加一个时段SELECT PERIOD_ADD(201703, 5);
-> 201708
PERIOD_DIFF(period1, period2)返回两个时段之间的月份差值SELECT PERIOD_DIFF(201710, 201703);
-> 7
QUARTER(d)返回日期d是第几季节，返回 1 到 4SELECT QUARTER('2011-11-11 11:11:11');
-> 4
SECOND(t)返回 t 中的秒钟值SELECT SECOND('1:2:3');
-> 3
SEC_TO_TIME(s)将以秒为单位的时间 s 转换为时分秒的格式SELECT SEC_TO_TIME(4320);
-> 01:12:00
STR_TO_DATE(string, format_mask)将字符串转变为日期SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
-> 2017-08-10
SUBDATE(d,n)日期 d 减去 n 天后的日期SELECT SUBDATE('2011-11-11 11:11:11', 1);
->2011-11-10 11:11:11 (默认是天)
SUBTIME(t,n)时间 t 减去 n 秒的时间SELECT SUBTIME('2011-11-11 11:11:11', 5);
->2011-11-11 11:11:06 (秒)
SYSDATE()返回当前日期和时间SELECT SYSDATE();
-> 2018-09-19 20:57:43
TIME(expression)提取传入表达式的时间部分SELECT TIME("19:30:10");
-> 19:30:10
TIME_FORMAT(t,f)按表达式 f 的要求显示时间 tSELECT TIME_FORMAT('11:11:11','%r') 11:11:11 AM
TIME_TO_SEC(t)将时间 t 转换为秒SELECT TIME_TO_SEC('1:12:00');
-> 4320
TIMEDIFF(time1, time2)计算时间差值SELECT TIMEDIFF("13:10:11", "13:10:10");
-> 00:00:01
TIMESTAMP(expression, interval)单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和SELECT TIMESTAMP("2017-07-23", "13:10:11");
-> 2017-07-23 13:10:11
TO_DAYS(d)计算日期 d 距离 0000 年 1 月 1 日的天数SELECT TO_DAYS('0001-01-01 01:01:01');
-> 366
WEEK(d)计算日期 d 是本年的第几个星期，范围是 0 到 53SELECT WEEK('2011-11-11 11:11:11');
-> 45
WEEKDAY(d)日期 d 是星期几，0 表示星期一，1 表示星期二SELECT WEEKDAY("2017-06-15");
-> 3
WEEKOFYEAR(d)计算日期 d 是本年的第几个星期，范围是 0 到 53SELECT WEEKOFYEAR('2011-11-11 11:11:11');
-> 45
YEAR(d)返回年份SELECT YEAR("2017-06-15");
-> 2017
YEARWEEK(date, mode)返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推SELECT YEARWEEK("2017-06-15");
-> 201724

高级函数

函数名描述实例
BIN(x)返回 x 的二进制编码15 的 2 进制编码:
SELECT BIN(15); -- 1111
BINARY(s)将字符串 s 转换为二进制字符串SELECT BINARY "NOWCODER";
-> NOWCODER
CASE expression
WHEN condition1
THEN result1
WHEN condition2
THEN result2
...
WHEN conditionN
THEN resultN
ELSE result
END		CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。SELECT CASE
WHEN 1 > 0
THEN '1 > 0'
WHEN 2 > 0
THEN '2 > 0'
ELSE '3 > 0' 　　END
->1 > 0
CAST(x AS type)转换数据类型字符串日期转换为日期：
SELECT CAST("2017-08-29" AS DATE);
-> 2017-08-29
COALESCE(expr1, expr2, ...., expr_n)返回参数中的第一个非空表达式（从左向右）SELECT COALESCE(NULL, NULL, NULL, 'nowcoder.com', NULL, 'google.com');
-> nowcoder.com
CONNECTION_ID()返回服务器的连接数SELECT CONNECTION_ID();
-> 4292835
CONV(x,f1,f2)返回 f1 进制数变成 f2 进制数SELECT CONV(15, 10, 2);
-> 1111
CONVERT(s USING cs)函数将字符串 s 的字符集变成 csSELECT CHARSET('ABC') ->utf-8
SELECT CHARSET(CONVERT('ABC' USING gbk))
->gbk
CURRENT_USER()返回当前用户SELECT CURRENT_USER();
-> guest@%
DATABASE()返回当前数据库名SELECT DATABASE();
-> nowcoder
IF(expr,v1,v2)如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。SELECT IF(1 > 0,'正确','错误')
->正确
IFNULL(v1,v2)如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。SELECT IFNULL(null,'Hello Word')
->Hello Word
ISNULL(expression)判断表达式是否为 NULLSELECT ISNULL(NULL);
->1
LAST_INSERT_ID()返回最近生成的 AUTO_INCREMENT 值SELECT LAST_INSERT_ID();
->6
NULLIF(expr1, expr2)比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1SELECT NULLIF(25, 25);
->
SESSION_USER()返回当前用户SELECT SESSION_USER();
-> guest@%
SYSTEM_USER()返回当前用户SELECT SYSTEM_USER();
-> guest@%
USER()返回当前用户SELECT USER();
-> guest@%
VERSION()返回数据库的版本号SELECT VERSION()
-> 5.6.34

30. 运算符

  1. 算数运算符

    运算符作用
    +加法
    -减法
    *乘法
    / 或 DIV除法
    % 或 MOD取余

  2. 比较运算符

    符号描述备注
    =等于
    <>, !=不等于
    >大于
    <小于
    <=小于等于
    >=大于等于
    BETWEEN在两值之间>=min && <=max
    NOT BETWEEN不在两值之间
    IN在集合中
    NOT IN不在集合中
    <=>严格比较两个NULL值是否相等两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0
    LIKE模糊匹配
    REGEXP 或 RLIKE正则式匹配
    IS NULL为空
    IS NOT NULL不为空

  3. 逻辑运算符

    运算符号作用
    NOT 或 !逻辑非
    AND逻辑与
    OR逻辑或
    XOR逻辑异或

  4. 位运算符

    运算符号作用
    &按位与
    |按位或
    ^按位异或
    !取反
    <<左移
    >>右移
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
【Prometheus】MySQL主从搭建，以及如何通过prometheus监控MySQL运行状态
最新发布
景天科技苑
02-11 7万+
要使用Prometheus 监控 MySQL，则需要 MySQL 服务器上部署对应的mysqld_exporter。 如果需要监控多个MySQL实例，则需要在每个MySQL实例上都部署一个mysqld_exporter， 因为每个 mysqld_exporter 只能连接并抓取一个MySQL 的指标数据。
mysql 带密码的登陆
weixin_42594419的博客
01-18 963
MySQL 的带密码登录方式为： 在命令行窗口中输入： mysql -u 用户名 -p 连接到 MySQL 后，系统会提示输入密码。输入密码后，就可以登录到 MySQL 了。 例如： mysql -u root -p 连接到 MySQL 后，系统会提示输入密码。 输入密码为：123456 注意: 密码是不可见的，在输入密码的时候，不会显示输入的字符。 ...
【mysql】mysql连接数据库命令（命令中包含密码）
WQN的博客
12-25 1619
mysql -u root -p rootroot 该命令敲入执行之后会要求输入密码，因为会把“rootroot”理解为数据库名，然后抛出错误 正确的（直接数据密码）连接数据库的命令为 mysql -u root -prootroot 1.-p和密码之间没有空格 2. -u和用户名之间的空格可有可无 在命令行中直接输入密码会出现一个warning,并不影响使用 ...
Mysql密码中有特殊字符&或$
小仙~
03-28 6400
现象 mysq登录报错 原因 密码中有特殊字符：& 或 $ 解决 方法1：密码加单引号 mysql -udatatech -p'O8dr2ENyRd^7v*&z' -P3306 方法2：特殊字符前面加上’’ mysql -udatatech -pO8dr2ENyRd^7v*\&z -P3306
Sex, crimes, and common sense: framing femininity from sensation to sexology【翻译】
hongfu951的专栏
03-05 2万+
ABSTRACT My dissertation tracks the production of "common sense" about female sexuality and psychology in nineteenth-century sensational British literature. I move from the sensation novel’s heyday, r...
SEO地址收藏
江湖·郎中·路
09-06 3万+
http://whois.chinaz.com/v5v6s.com
MySQL安装教程（详细版）
m0_71422677的博客
02-20 40万+
MySQL免费安装教程、如何验证MySQL是否安装成功？怎么关掉MySQL服务？MySQL 端口问题如何解决？MySQL如何开机自启OR手动自启？安装MySQL时Starting the server 安装失败怎么解决？
如何解决MySQL 的深度分页问题？
2401_85373732的博客
01-10 32万+
本文分析了 MySQL 的深度分页问题及其解决方案。对于 MySQL 中的深度分页，我们可以通过合理的优化策略来提高查询效率。具体选用什么方案，需要根据具体场景进行分析，但核心在于理解数据库的工作原理，利用索引、优化查询策略、使用标记分页、分区表、缓存结果等优化技术。通过这些方法，可以显著提升分页查询的性能，改善用户体验。
mysql省市区表完整版文件
10-07
MySQL数据库在许多应用中用于存储地理信息，如省市区数据，尤其在中国的网站和应用程序中很常见。"mysql省市区表完整版文件"提供了一个全面的数据库表结构，包含了中国所有省份、城市和区县的详细信息，这些信息通常...
2018年Java面试题
weixin_42304845的博客
06-09 2万+
参考答案：https://blog.youkuaiyun.com/matry521/article/details/522101391. String类为什么是final的。答：主要是为了“效率” 和 “安全性” 的缘故。若 String允许被继承, 由于它的高度被使用率, 可能会降低程序的性能，所以String被定义成final。1、 从设计安全)上讲， 1)、确保它们不会在子类中改变语义。String类是...
前端必会面试题
weixin_41767962的博客
05-01 6505
1.类的创建和继承 (1)原型链的继承 function Animal(){ this.name="猫"; this.sex="雌性"; } Animal.prototype.say="您好"; function Cat(){ this.a="123"; th...
【收藏】The top 200 sites on the web
weixin_34357267的博客
04-10 32万+
The top 200 sites on the web【整理】 SkySeraph Apr 10th 2011  HQU Email：zgzhaobo@gmail.com    QQ：452728574 Latest Modified Date：Apr 10th 2011 HQU   1Googlegoogle.com Enables users to search the We...
n05_Reinforcement Q-Learnining_MDP_getattr_Monte Carlo_Blackjack_Driving office_ε-greedy_SARSA_Bellm
Linli522362242的专栏
10-20 1751
Reinforcement learning (RL) is the third major section of machine learning after supervised and unsupervised learning. These techniques have gained a lot of traction in recent years in the application of artificial intelligence. In reinforcement learn...
signature=1a4f4cc2a4cb36682d90c0d4c84ca5da,dweb-mirror/yarn.lock at master · internetarchive/dweb-mi...
weixin_34615710的博客
05-29 12万+
# THIS IS AN AUTOGENERATED FILE. DO NOT EDIT THIS FILE DIRECTLY.# yarn lockfile v1"@babel/code-frame@^7.0.0", "@babel/code-frame@^7.8.3":version "7.8.3"resolved "https://registry.yarnpkg.com/@babel/co...
通讯录--静态版本
mddddd545的博客
08-13 3万+
用c语言实现静态版本的通讯录 本代码包含3个部分，自定义了一个头文件contact.h，测试文件test.c以及contact.c实现各个功能 contact.h #ifndef _CONTACT_H #define _CONTACT_H #include #include #include #include //姓名 性别 年龄 住址 电话 #define MAX_NEME 10 #def
亚洲域名.asia10月9日登场
秦梦瑶的专栏
10-27 7680
2007年10月07日 14:48:00 欧洲的.eu互联网域名注册机构EURid日前表示,当亚洲域名.Asia下个星期推出的时候,www.sex.asia互联网地址可能成为需求量最大的域名.EURid在声明中说,谁也不知道哪一个域名将是最流行的.但是,EURid的经验提供了一些线索.在.eu域名刚刚推出的头两天,EURid收到了227个sex.eu的域名申请,118个hotel.eu域名
www.zhsws.com/admin.php,WebShell/DJ团队.asp at master · xl7dev/WebShell · GitHub
热门推荐
weixin_36238982的博客
04-01 67万+
#@~^d24BAA==]/2Kxk+R~E6W+MPxYMEn@#@&rx,3DMWD,]n/!:PH+aO,@#@&/D.AzfxE@!/m.raY~VmUTECT+{\4kmMrwDP.ElOxk+D7nD@*J@#@&dOD~bG'dY.$zf'J&WP"+5;/YcErJ'm^rxOhlk/hKD9'JrJb@!@*JEErPK4nx,?+kdrW`rJ:...
快速跳转到Google SSL 中文版的网址：ggssl.com
HANK-M的专栏
05-31 9823
<br />Google 最近提供了 SSL（HTTPS） 搜索功能（https://www.google.com），可以保护用户的搜索关键字和搜索结果不被ISP等第三方机构拦截、窃取，不被墙连接重置。<br />　　国内用户访问 https://www.google.com 默认情况下会跳转到谷歌香港 http://www.google.com.hk，解决方法为先点击“Google.com in English“访问 http://www.google.com/ncr （仅需要点击一次），该网址会种一个C
Java+MySQL开发的学生信息管理系统
描述部分“简单的java学生信息管理系统，用了Java+mysql等技术，可以通过页面进行简单的增删改查”提供了此系统的技术栈和核心功能。 知识点可以从以下几个方面来展开： 1. **Java技术栈**： - **Java基础**：...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

黑暗守护者

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值