数据库-mysql数据操作

一：mysql 数据的插入　

　　语法

　　以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

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

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

MariaDB [test2]> desc student;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| stu_id | int(11)  | NO   | PRI | NULL    | auto_increment |
| name   | char(10) | NO   |     | NULL    |                |
| age    | int(11)  | NO   |     | NULL    |                |
| sex    | char(2)  | NO   |     | F       |                |
+--------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [test2]> insert into student (name,age,sex) values('shisanjun',23,'F');
Query OK, 1 row affected (0.00 sec)

二：MySQL 查询数据　

　　语法

　　以下为在MySQL数据库中查询数据通用的 SELECT 语法：

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

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

MariaDB [test2]> select * from student;
+--------+------------+-----+-----+
| stu_id | name       | age | sex |
+--------+------------+-----+-----+
|      1 | shisanjun  |  23 | F   |
|      2 | shisanjun2 |  23 | F   |
|      3 | shisanjun3 |  24 | F   |
+--------+------------+-----+-----+
3 rows in set (0.00 sec)

MariaDB [test2]> select * from student limit 2 offset 1;   offset必须要和limit一起使用，而且limit在前端
+--------+------------+-----+-----+
| stu_id | name       | age | sex |
+--------+------------+-----+-----+
|      2 | shisanjun2 |  23 | F   |
|      3 | shisanjun3 |  24 | F   |
+--------+------------+-----+-----+
2 rows in set (0.00 sec)

MariaDB [test2]> select * from student offset 1; 报错了
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual 
that corresponds to your MariaDB server version for the right syntax to use near '1' at line 1
MariaDB [test2]> select * from student limit 2 ,1; 表示从第2条后面开始查询1条
+--------+------------+-----+-----+
| stu_id | name       | age | sex |
+--------+------------+-----+-----+
|      3 | shisanjun3 |  24 | F   |
+--------+------------+-----+-----+
1 row in set (0.00 sec)

MariaDB [test2]> 

三：mysql where 子句

　　

　　语法

　　以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

　　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 表中的字段值来读取指定的数据。

以下为操作符列表，可用于 WHERE 子句中

下表中实例假定 A 为 10, B 为 20

操作符	描述	实例
=	等号，检测两个值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等于，检测两个值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true	(A > B) 返回false。
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true	(A < B) 返回 true。
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true	(A >= B) 返回false。
<=	小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true	(A <= B) 返回 true。

如果我们想再 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。

使用主键来作为 WHERE 子句的条件查询是非常快速的。

如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

MariaDB [test2]> select * from student where sex='F';
+--------+------------+-----+-----+
| stu_id | name       | age | sex |
+--------+------------+-----+-----+
|      1 | shisanjun  |  23 | F   |
|      2 | shisanjun2 |  23 | F   |
|      3 | shisanjun3 |  24 | F   |
+--------+------------+-----+-----+

四：MySQL UPDATE 查询

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

　　语法

　　以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

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

• 你可以同时更新一个或多个字段。
• 你可以在 WHERE 子句中指定任何条件。
• 你可以在一个单独表中同时更新数据。

　　当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

MariaDB [test2]> update student set sex='M';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [test2]> select * from student;
+--------+------------+-----+-----+
| stu_id | name       | age | sex |
+--------+------------+-----+-----+
|      1 | shisanjun  |  23 | M   |
|      2 | shisanjun2 |  23 | M   |
|      3 | shisanjun3 |  24 | M   |
+--------+------------+-----+-----+
3 rows in set (0.00 sec)

五：MySQL DELETE 语句

你可以使用 SQL 的 DELETE FROM 命令来删除 MySQL 数据表中的记录。

　　语法

　　以下是 SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法：

　　DELETE FROM table_name [WHERE Clause]

• 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
• 你可以在 WHERE 子句中指定任何条件
• 您可以在单个表中一次性删除记录。

　　当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。

MariaDB [test2]> delete from student where name='shisanjun3';
Query OK, 1 row affected (0.00 sec) 

六：MySQL LIKE 子句

语法

以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

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

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

MariaDB [test2]> select * from student where name like 'shisanjun%';
+--------+------------+-----+-----+
| stu_id | name       | age | sex |
+--------+------------+-----+-----+
|      1 | shisanjun  |  23 | M   |
|      2 | shisanjun2 |  23 | M   |
+--------+------------+-----+-----+
2 rows in set (0.00 sec)

MySQL居然不区分大小写，可以使用binary将字符串先转换为二进制字符串

MariaDB [test2]> select name,count(name) from student group by name;
+------------+-------------+
| name       | count(name) |
+------------+-------------+
| shisanjun  |           4 |
| shisanjun2 |           1 |
| shisanjun3 |           2 |
+------------+-------------+
3 rows in set (0.00 sec)
可以使用binary将字符串先转换为二进制字符串，在进行比较
MariaDB [test2]> select name,count(name) from student group by binary name;
+------------+-------------+
| name       | count(name) |
+------------+-------------+
| Shisanjun  |           1 |
| shisanjun  |           3 |
| shisanjun2 |           1 |
| shisanjun3 |           2 |
+------------+-------------+
4 rows in set (0.00 sec)

参考资料：http://www.linuxidc.com/Linux/2014-06/102870.htm

七：MySQL 排序

语法

以下是 SQL SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

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

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

MariaDB [test2]> select * from student order by name;
+--------+------------+-----+-----+
| stu_id | name       | age | sex |
+--------+------------+-----+-----+
|      1 | shisanjun  |  23 | M   |
|      6 | shisanjun  |  25 | F   |
|      7 | shisanjun  |  26 | F   |
|      2 | shisanjun2 |  23 | M   |
|      4 | shisanjun3 |  24 | F   |
|      5 | shisanjun3 |  25 | F   |
+--------+------------+-----+-----+
6 rows in set (0.00 sec)

MariaDB [test2]> select * from student order by name desc;
+--------+------------+-----+-----+
| stu_id | name       | age | sex |
+--------+------------+-----+-----+
|      4 | shisanjun3 |  24 | F   |
|      5 | shisanjun3 |  25 | F   |
|      2 | shisanjun2 |  23 | M   |
|      1 | shisanjun  |  23 | M   |
|      6 | shisanjun  |  25 | F   |
|      7 | shisanjun  |  26 | F   |
+--------+------------+-----+-----+
6 rows in set (0.00 sec)

八：MySQL GROUP BY 语句

　　GROUP BY 语句根据一个或多个列对结果集进行分组。

　　在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

　　GROUP BY 语法

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

　　使用 WITH ROLLUP

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

　　例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：

　可以使用 coalesce 来设置一个可以取代 NUll 的名称

MariaDB [test2]> select name,count(name) from student group by binary name with rollup;
+------------+-------------+
| name       | count(name) |
+------------+-------------+
| Shisanjun  |           1 |
| shisanjun  |           3 |
| shisanjun2 |           1 |
| shisanjun3 |           2 |
| shisanjun3 |           7 |
+------------+-------------+
5 rows in set (0.01 sec)

MariaDB [test2]> select name,count(name) from student group by name with rollup;
+------------+-------------+
| name       | count(name) |
+------------+-------------+
| shisanjun  |           4 |
| shisanjun2 |           1 |
| shisanjun3 |           2 |
| NULL       |           7 |
+------------+-------------+
4 rows in set (0.00 sec)

MariaDB [test2]> select coalesce(name,total),count(name) from student group by name with rollup;
ERROR 1054 (42S22): Unknown column 'total' in 'field list'
MariaDB [test2]> select coalesce(name,'total'),count(name) from student group by name with rollup;
+------------------------+-------------+
| coalesce(name,'total') | count(name) |
+------------------------+-------------+
| shisanjun              |           4 |
| shisanjun2             |           1 |
| shisanjun3             |           2 |
| total                  |           7 |
+------------------------+-------------+
4 rows in set (0.00 sec)

九：MySQL NULL 值处理

　　我们已经知道 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 运算符。