数据库-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 运算符。
