MySQL数据库查询1

数据库命令语句：

1.修改表名：
语法： ALTER TABLE<旧表名> RENAME [TO] <新表名>

2、添加字段：
语法：ALTER TABLE 表名 ADD 字段名 数据类型 [属性]

3.修改字段：
语法ALTER TABLE 表名 CHANCE 原字段名 数据类型 [属性]

4.删除字段：
语法：ALTER TABLE 表名 DROP 字段名;

5.添加主键约束：
语法：ALTER TABLE 表名 ADD CONSTRAINT 主键名 PRIMARY KEY 表名（主键字段）

6.字段添加数据：
语法：INSERTT INTO [(字段名列表)] VALUES (值列名) 
	多行数据时值用逗号隔开：
	INSERTT INTO [(字段名列表)] VALUES (值列名) (值列名),(值列名),,,,,
	
7.将查询结果添加到新表：
语法：INSERT INTO 新表(字段1),(字段2,,,,)
			SELECT 字段1,字段2,,,,,,
			FROM
		第二种方法
		CREATE TABLE 新表 (SELECT 字段1，字段2，,,,,,FROM原表)；
			两种方法的区别是方法一是在已创建表的情况下给表加数据方法二则是在没有新表的情况下创建新表再把数据添加到新表里
			
	8.修改字段数据：
	语法：UPDATE 表名 字段1=值1，字段2=值2.,,,,,WHERER 条件
	
	9：删除表中数据
	语法：DELETE FROM 表名 WHERE 条件
		第二种方法：删除表中所有数据
			TRUNCATE TABLE 表名
			*注意*TRYNCATE语句实现删除表中所有数据，删除后将重置自增列，表结构及字段，约束，索引保持不变，比DELETE语句快

常用函数

聚合函数（aggregation function）—也就是组函数

在一个行的集合（一组行）上进行操作，对每个组给一个结果。

常用的组函数：

AVG([distinct] expr)
求平均值

COUNT({*|[distinct] } expr)
统计行的数量

MAX([distinct] expr)
求最大值

MIN([distinct] expr)
求最小值

SUM([distinct] expr)
求累加和

	①每个组函数接收一个参数

②默认情况下，组函数忽略列值为null的行，不参与计算
　　③有时，会使用关键字distinct剔除字段值重复的条数
注意：
　　1）当使用组函数的select语句中没有group by子句时，中间结果集中的所有行自动形成一组，然后计算组函数；

2）组函数不允许嵌套，例如：count(max(…))；

3）组函数的参数可以是列或是函数表达式；

4）一个SELECT子句中可出现多个聚集函数。

字符串函数：

字符串函数是MySQL中使用最频繁的函数，主要用于处理数据库中字符串类型的数据。表10-2列出了MySQL中常用的字符串函数及其功能。

LENGTH(str)，CHAR_LENGTH(str)
返回字符串长度或字符个数

CONCAT(str1,str2…strn)，CONCAT_WS(x,str1,str2…strn)
合并字符串

INSERT(str,x,y,instr)，REPLACE(str,a,b)
替换字符串

LOWER(str)，UPPER(str)
字符大小写转换

LEFT(str,x)，RIGHT(str,x)，SUBSTRING(str,x,y)
获取字符串的一部分

LPAD(str1,n,str2)，RPAD(str1,n,str2)
填充字符串

LTRIM(str)，RTRIM(str)，TRIM(str)
删除字符串左侧、右侧或两侧空格

REPEAT(str,n)
返回字符串str重复n次的结果

LOCATE(str1,str)
返回子字符串的开始位置

REVERSE(str)
反转字符串

日期与时间函数：
在实际应用中，有时可能会遇到这样的需求：获取当前时间，或者下个月的今天是星期几，等等类似的问题。这些需求就需要使用日期与时间函数来实现。表10-3列出了MySQL中常用的日期与时间函数及其功能。

CURDATE()
获取当前日期

CURTIME()
获取当前时间

NOW()
获取当前的日期和时间

UNIX_TIMESTAMP(date)
获取日期date的UNIX时间戳

YEAR(d)，MONTH(d)，WEEK(d)，DAY(d)，HOUR(d)，MINUTE(d)，SECOND(d)
返回指定日期的年份、月份、星期、日、时、分和秒

DATE_FORMAT(d,format)
按format指定的格式显示日期d的值

ADDDATE(date,INTERVAL expr unit)，
SUBDATE(date,INTERVAL expr unit)
获取一个日期或时间值加上一个时间间隔的时间值

TIME_TO_SEC(d),SEC_TO_TIME(d)
获取将“HH:MM:SS”格式的时间换算为秒，或将秒数换算为“HH:MM:SS”格式的值

数学函数
数学函数主要用于处理数字，包括整型和浮点数。下面介绍一下常用的几个数学函数。

ABS()函数
ABS()可以求出某字段值的绝对值。

FLOOR()函数
FLOOR()函数用于返回小于或等于参数x的最大整数。

RAND()函数
RAND()函数用于返回0~1之间的随机数。

TRUNCATE(x, y)函数
TRUNCATE(x, y)函数返回x保留到小数点后y位的值。

SQRT(x)函数
SQRT(x)函数用于求参数x的平方根。

子查询：

子查询在 WHERE 中的语法格式如下：

WHERE <表达式> <操作符> (子查询)

其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。
1）IN | NOT IN
当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。
2）EXISTS | NOT EXISTS
用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。
例 1
使用子查询在 tb_students_info 表和 tb_course 表中查询学习 Java 课程的学生姓名，SQL 语句和运行结果如下。

mysql> SELECT name FROM tb_students_info 
    -> WHERE course_id IN (SELECT id FROM tb_course WHERE course_name = 'Java');
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.01 sec)

结果显示，学习 Java 课程的只有 Dany 和 Henry。上述查询过程也可以分为以下 2 步执行，实现效果是相同的。

1）首先单独执行内查询，查询出 tb_course 表中课程为 Java 的 id，SQL 语句和运行结果如下。

mysql> SELECT id FROM tb_course 
    -> WHERE course_name = 'Java';
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

可以看到，符合条件的 id 字段的值为 1。

2）然后执行外层查询，在 tb_students_info 表中查询 course_id 等于 1 的学生姓名。SQL 语句和运行结果如下。

mysql> SELECT name FROM tb_students_info 
    -> WHERE course_id IN (1);
+-------+
| name  |
+-------+
| Dany  |
| Henry |
+-------+
2 rows in set (0.00 sec)

习惯上，外层的 SELECT 查询称为父查询，圆括号中嵌入的查询称为子查询（子查询必须放在圆括号内）。MySQL 在处理上例的 SELECT 语句时，执行流程为：先执行子查询，再执行父查询。
例 2
与例 1 类似，在 SELECT 语句中使用 NOT IN 关键字，查询没有学习 Java 课程的学生姓名，SQL 语句和运行结果如下。

mysql> SELECT name FROM tb_students_info 
    -> WHERE course_id NOT IN (SELECT id FROM tb_course WHERE course_name = 'Java');
+--------+
| name   |
+--------+
| Green  |
| Jane   |
| Jim    |
| John   |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
| LiMing |
+--------+
9 rows in set (0.01 sec)

可以看出，运行结果与例 1 刚好相反，没有学习 Java 课程的是除了 Dany 和 Henry 之外的学生。

例 3
使用=运算符，在 tb_course 表和 tb_students_info 表中查询出所有学习 Python 课程的学生姓名，SQL 语句和运行结果如下。

mysql> SELECT name FROM tb_students_info
    -> WHERE course_id = (SELECT id FROM tb_course WHERE course_name = 'Python');
+------+
| name |
+------+
| Jane |
+------+
1 row in set (0.00 sec)

结果显示，学习 Python 课程的学生只有 Jane。
例 4
使用<>运算符，在 tb_course 表和 tb_students_info 表中查询出没有学习 Python 课程的学生姓名，SQL 语句和运行结果如下。

mysql> SELECT name FROM tb_students_info
    -> WHERE course_id <> (SELECT id FROM tb_course WHERE course_name = 'Python');
+--------+
| name   |
+--------+
| Dany   |
| Green  |
| Henry  |
| Jim    |
| John   |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
| LiMing |
+--------+
10 rows in set (0.00 sec)

可以看出，运行结果与例 3 刚好相反，没有学习 Python 课程的是除了 Jane 之外的学生。
例 5
查询 tb_course 表中是否存在 id=1 的课程，如果存在，就查询出 tb_students_info 表中的记录，SQL 语句和运行结果如下。

mysql> SELECT * FROM tb_students_info
    -> WHERE EXISTS(SELECT course_name FROM tb_course WHERE id=1);
+----+--------+------+------+--------+-----------+
| id | name   | age  | sex  | height | course_id |
+----+--------+------+------+--------+-----------+
|  1 | Dany   |   25 | 男   |    160 |         1 |
|  2 | Green  |   23 | 男   |    158 |         2 |
|  3 | Henry  |   23 | 女   |    185 |         1 |
|  4 | Jane   |   22 | 男   |    162 |         3 |
|  5 | Jim    |   24 | 女   |    175 |         2 |
|  6 | John   |   21 | 女   |    172 |         4 |
|  7 | Lily   |   22 | 男   |    165 |         4 |
|  8 | Susan  |   23 | 男   |    170 |         5 |
|  9 | Thomas |   22 | 女   |    178 |         5 |
| 10 | Tom    |   23 | 女   |    165 |         5 |
| 11 | LiMing |   22 | 男   |    180 |         7 |
+----+--------+------+------+--------+-----------+
11 rows in set (0.01 sec)

由结果可以看到，tb_course 表中存在 id=1 的记录，因此 EXISTS 表达式返回 TRUE，外层查询语句接收 TRUE 之后对表 tb_students_info 进行查询，返回所有的记录。

EXISTS 关键字可以和其它查询条件一起使用，条件表达式与 EXISTS 关键字之间用 AND 和 OR 连接。
例 6
查询 tb_course 表中是否存在 id=1 的课程，如果存在，就查询出 tb_students_info 表中 age 字段大于 24 的记录，SQL 语句和运行结果如下。

mysql> SELECT * FROM tb_students_info
    -> WHERE age>24 AND EXISTS(SELECT course_name FROM tb_course WHERE id=1);
+----+------+------+------+--------+-----------+
| id | name | age  | sex  | height | course_id |
+----+------+------+------+--------+-----------+
|  1 | Dany |   25 | 男   |    160 |         1 |
+----+------+------+------+--------+-----------+
1 row in set (0.01 sec)

结果显示，从 tb_students_info 表中查询出了一条记录，这条记录的 age 字段取值为 25。内层查询语句从 tb_course 表中查询到记录，返回 TRUE。外层查询语句开始进行查询。根据查询条件，从 tb_students_info 表中查询 age 大于 24 的记录。
拓展
子查询的功能也可以通过表连接完成，但是子查询会使 SQL 语句更容易阅读和编写。