SQL语法基础之SELECT
SQL语法基础之SELECT
作者：尹正杰
版权声明：原创作品，谢绝转载！否则将追究法律责任。
一.SELECT查看帮助信息
1>.查看SELECT命令的帮助信息
mysql> ? SELECT Name: 'SELECT' Description: Syntax: SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] SQL_NO_CACHE [SQL_CALC_FOUND_ROWS] select_expr [, select_expr ...] [FROM table_references [PARTITION partition_list] [WHERE where_condition] [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]] [HAVING where_condition] [WINDOW window_name AS (window_spec) [, window_name AS (window_spec)] ...] [ORDER BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name]] [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED] | LOCK IN SHARE MODE]] SELECT is used to retrieve rows selected from one or more tables, and can include UNION statements and subqueries. See [HELP UNION], and http://dev.mysql.com/doc/refman/8.0/en/subqueries.html. A SELECT statement can start with a WITH clause to define common table expressions accessible within the SELECT. See http://dev.mysql.com/doc/refman/8.0/en/with.html. The most commonly used clauses of SELECT statements are these: o Each select_expr indicates a column that you want to retrieve. There must be at least one select_expr. o table_references indicates the table or tables from which to retrieve rows. Its syntax is described in [HELP JOIN]. o SELECT supports explicit partition selection using the PARTITION with a list of partitions or subpartitions (or both) following the name of the table in a table_reference (see [HELP JOIN]). In this case, rows are selected only from the partitions listed, and any other partitions of the table are ignored. For more information and examples, see http://dev.mysql.com/doc/refman/8.0/en/partitioning-selection.html. SELECT ... PARTITION from tables using storage engines such as MyISAM that perform table-level locks (and thus partition locks) lock only the partitions or subpartitions named by the PARTITION option. For more information, see Partitioning and Locking (http://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-lock ing.html). o The WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause. In the WHERE expression, you can use any of the functions and operators that MySQL supports, except for aggregate (summary) functions. See http://dev.mysql.com/doc/refman/8.0/en/expressions.html, and http://dev.mysql.com/doc/refman/8.0/en/functions.html. SELECT can also be used to retrieve rows computed without reference to any table. URL: http://dev.mysql.com/doc/refman/8.0/en/select.html mysql>
2>.测试数据准备
建表语句如下：
mysql> CREATE DATABASE yinzhengjie DEFAULT CHARACTER SET = utf8; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> mysql> USE yinzhengjie Database changed mysql> mysql> CREATE TABLE course(id INT(11) PRIMARY KEY AUTO_INCREMENT,name VARCHAR(30)); Query OK, 0 rows affected (0.01 sec) mysql> mysql> mysql> CREATE TABLE teacher( -> id INT(11) PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(30), -> course_id INT(11) NOT NULL, -> CONSTRAINT teacher_course FOREIGN KEY(course_id) REFERENCES course(id) -> ); Query OK, 0 rows affected (0.02 sec) mysql> mysql> CREATE TABLE student( -> id INT(11) PRIMARY KEY AUTO_INCREMENT, -> name VARCHAR(30), -> teacher_id INT(11) NOT NULL, -> CONSTRAINT student_teacher FOREIGN KEY(teacher_id) REFERENCES teacher(id) -> ); Query OK, 0 rows affected (0.02 sec) mysql> mysql>
创建完表后，接下里我们就应该往表中插入数据了：
mysql> INSERT INTO course(name) VALUE('语文'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO course(name) VALUE('数学'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO course(name) VALUE('英语'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO course(name) VALUE('体育'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO course(name) VALUE('美术'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO course(name) VALUE('音乐'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO course(name) VALUE('物理'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO course(name) VALUE('化学'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO course(name) VALUE('生物'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO course(name) VALUE('历史'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO course(name) VALUE('政治'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO course(name) VALUE('地理'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO course(name) VALUE('计算机'); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM course; +----+-----------+ | id | name | +----+-----------+ | 1 | 语文 | | 2 | 数学 | | 3 | 英语 | | 4 | 体育 | | 5 | 美术 | | 6 | 音乐 | | 7 | 物理 | | 8 | 化学 | | 9 | 生物 | | 10 | 历史 | | 11 | 政治 | | 12 | 地理 | | 13 | 计算机 | +----+-----------+ 13 rows in set (0.00 sec) mysql>
mysql> INSERT INTO teacher(name,course_id) VALUES('谢霆锋',11); Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO teacher(name,course_id) VALUES('周杰伦',1); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO teacher(name,course_id) VALUES('蔡依林',13); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO teacher(name,course_id) VALUES('杨幂',2); Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO teacher(name,course_id) VALUES('胡歌',12); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO teacher(name,course_id) VALUES('刘德华',3); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO teacher(name,course_id) VALUES('张学友',10); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO teacher(name,course_id) VALUES('郭德纲',4); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO teacher(name,course_id) VALUES('张杰',9); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO teacher(name,course_id) VALUES('苍老师',5); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO teacher(name,course_id) VALUES('谢娜',8); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO teacher(name,course_id) VALUES('赵薇',6); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO teacher(name,course_id) VALUES('张卫健',7); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+-----------+ 13 rows in set (0.00 sec) mysql> mysql>
mysql> INSERT INTO student(name,teacher_id) VALUES ('尹正杰',1); Query OK, 1 row affected (0.00 sec) mysql> mysql> INSERT INTO student(name,teacher_id) VALUES ('耿宇星,2); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO student(name,teacher_id) VALUES ('陈飞',3); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO student(name,teacher_id) VALUES ('彭兴旭',4); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO student(name,teacher_id) VALUES ('李慧鹏',5); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO student(name,teacher_id) VALUES ('孟欣',6); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO student(name,teacher_id) VALUES ('鲜惠珊',7); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO student(name,teacher_id) VALUES ('陈劲',8); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO student(name,teacher_id) VALUES ('居彭阳',9); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO student(name,teacher_id) VALUES ('李嘉韵',10); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO student(name,teacher_id) VALUES ('石闹闹',11); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO student(name,teacher_id) VALUES ('肖风',12); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO student(name,teacher_id) VALUES ('刘晓江',13); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT * FROM student; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql>
3>.SELECT语句的常规语法
mysql> SELECT * FROM student; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql>
mysql> SELECT id,name FROM student; +----+-----------+ | id | name | +----+-----------+ | 1 | 尹正杰 | | 2 | 耿宇星 | | 3 | 陈飞 | | 4 | 彭兴旭 | | 5 | 李慧鹏 | | 6 | 孟欣 | | 7 | 鲜惠珊 | | 8 | 陈劲 | | 9 | 居彭阳 | | 10 | 李嘉韵 | | 11 | 石闹闹 | | 12 | 肖风 | | 13 | 刘晓江 | +----+-----------+ 13 rows in set (0.00 sec) mysql>
mysql> SELECT id,name FROM student WHERE id%2 != 0 ; +----+-----------+ | id | name | +----+-----------+ | 1 | 尹正杰 | | 3 | 陈飞 | | 5 | 李慧鹏 | | 7 | 鲜惠珊 | | 9 | 居彭阳 | | 11 | 石闹闹 | | 13 | 刘晓江 | +----+-----------+ 7 rows in set (0.00 sec) mysql> mysql>
mysql> SELECT id,name FROM student ORDER BY id DESC; +----+-----------+ | id | name | +----+-----------+ | 13 | 刘晓江 | | 12 | 肖风 | | 11 | 石闹闹 | | 10 | 李嘉韵 | | 9 | 居彭阳 | | 8 | 陈劲 | | 7 | 鲜惠珊 | | 6 | 孟欣 | | 5 | 李慧鹏 | | 4 | 彭兴旭 | | 3 | 陈飞 | | 2 | 耿宇星 | | 1 | 尹正杰 | +----+-----------+ 13 rows in set (0.00 sec) mysql>
mysql> SELECT name,count(*) FROM student GROUP BY name HAVING count(*) = 1 ; +-----------+----------+ | name | count(*) | +-----------+----------+ | 尹正杰 | 1 | | 耿宇星 | 1 | | 陈飞 | 1 | | 彭兴旭 | 1 | | 李慧鹏 | 1 | | 孟欣 | 1 | | 鲜惠珊 | 1 | | 陈劲 | 1 | | 居彭阳 | 1 | | 李嘉韵 | 1 | | 石闹闹 | 1 | | 肖风 | 1 | | 刘晓江 | 1 | +-----------+----------+ 13 rows in set (0.00 sec) mysql>
mysql> SELECT * FROM student AS t1 INNER JOIN teacher AS t2 ON (t1.id/5) = t2.id; +----+-----------+------------+----+-----------+-----------+ | id | name | teacher_id | id | name | course_id | +----+-----------+------------+----+-----------+-----------+ | 5 | 李慧鹏 | 5 | 1 | 谢霆锋 | 11 | | 10 | 李嘉韵 | 10 | 2 | 周杰伦 | 1 | +----+-----------+------------+----+-----------+-----------+ 2 rows in set (0.00 sec) mysql>
mysql> SELECT id AS '学员编号', name AS '学生姓名' FROM student; +--------------+--------------+ | 学员编号 | 学生姓名 | +--------------+--------------+ | 1 | 尹正杰 | | 2 | 耿宇星 | | 3 | 陈飞 | | 4 | 彭兴旭 | | 5 | 李慧鹏 | | 6 | 孟欣 | | 7 | 鲜惠珊 | | 8 | 陈劲 | | 9 | 居彭阳 | | 10 | 李嘉韵 | | 11 | 石闹闹 | | 12 | 肖风 | | 13 | 刘晓江 | +--------------+--------------+ rows in set (0.00 sec) mysql>
mysql> SELECT id '学员编号', name '学生姓名' FROM student; +--------------+--------------+ | 学员编号 | 学生姓名 | +--------------+--------------+ | 1 | 尹正杰 | | 2 | 耿宇星 | | 3 | 陈飞 | | 4 | 彭兴旭 | | 5 | 李慧鹏 | | 6 | 孟欣 | | 7 | 鲜惠珊 | | 8 | 陈劲 | | 9 | 居彭阳 | | 10 | 李嘉韵 | | 11 | 石闹闹 | | 12 | 肖风 | | 13 | 刘晓江 | +--------------+--------------+ 13 rows in set (0.00 sec) mysql>
二.SELECT关键点剖析
1>.SELECT_EXPR 关键词
代表查询的字段，至少要有一个SELECT_EXPR，或者如果是查询所有的字段，则用"*"好代替；
2>.Table_references 关键词
代表查询数据来自的一个或多个表。
3>.WHERE子句
代表只查询满足条件的表数据，如果没有WHERE子句则代表查询表中所有的数据。
4>.查询两张表的所有字段方法有如下两种
mysql> SELECT * FROM student INNER JOIN teacher; +----+-----------+------------+----+-----------+-----------+ | id | name | teacher_id | id | name | course_id | +----+-----------+------------+----+-----------+-----------+ | 1 | 尹正杰 | 1 | 1 | 谢霆锋 | 11 | | 2 | 耿宇星 | 2 | 1 | 谢霆锋 | 11 | | 3 | 陈飞 | 3 | 1 | 谢霆锋 | 11 | | 4 | 彭兴旭 | 4 | 1 | 谢霆锋 | 11 | | 5 | 李慧鹏 | 5 | 1 | 谢霆锋 | 11 | | 6 | 孟欣 | 6 | 1 | 谢霆锋 | 11 | | 7 | 鲜惠珊 | 7 | 1 | 谢霆锋 | 11 | | 8 | 陈劲 | 8 | 1 | 谢霆锋 | 11 | | 9 | 居彭阳 | 9 | 1 | 谢霆锋 | 11 | | 10 | 李嘉韵 | 10 | 1 | 谢霆锋 | 11 | | 11 | 石闹闹 | 11 | 1 | 谢霆锋 | 11 | | 12 | 肖风 | 12 | 1 | 谢霆锋 | 11 | | 13 | 刘晓江 | 13 | 1 | 谢霆锋 | 11 | | 1 | 尹正杰 | 1 | 2 | 周杰伦 | 1 | | 2 | 耿宇星 | 2 | 2 | 周杰伦 | 1 | | 3 | 陈飞 | 3 | 2 | 周杰伦 | 1 | | 4 | 彭兴旭 | 4 | 2 | 周杰伦 | 1 | | 5 | 李慧鹏 | 5 | 2 | 周杰伦 | 1 | | 6 | 孟欣 | 6 | 2 | 周杰伦 | 1 | | 7 | 鲜惠珊 | 7 | 2 | 周杰伦 | 1 | | 8 | 陈劲 | 8 | 2 | 周杰伦 | 1 | | 9 | 居彭阳 | 9 | 2 | 周杰伦 | 1 | | 10 | 李嘉韵 | 10 | 2 | 周杰伦 | 1 | | 11 | 石闹闹 | 11 | 2 | 周杰伦 | 1 | | 12 | 肖风 | 12 | 2 | 周杰伦 | 1 | | 13 | 刘晓江 | 13 | 2 | 周杰伦 | 1 | | 1 | 尹正杰 | 1 | 3 | 蔡依林 | 13 | | 2 | 耿宇星 | 2 | 3 | 蔡依林 | 13 | | 3 | 陈飞 | 3 | 3 | 蔡依林 | 13 | | 4 | 彭兴旭 | 4 | 3 | 蔡依林 | 13 | | 5 | 李慧鹏 | 5 | 3 | 蔡依林 | 13 | | 6 | 孟欣 | 6 | 3 | 蔡依林 | 13 | | 7 | 鲜惠珊 | 7 | 3 | 蔡依林 | 13 | | 8 | 陈劲 | 8 | 3 | 蔡依林 | 13 | | 9 | 居彭阳 | 9 | 3 | 蔡依林 | 13 | | 10 | 李嘉韵 | 10 | 3 | 蔡依林 | 13 | | 11 | 石闹闹 | 11 | 3 | 蔡依林 | 13 | | 12 | 肖风 | 12 | 3 | 蔡依林 | 13 | | 13 | 刘晓江 | 13 | 3 | 蔡依林 | 13 | | 1 | 尹正杰 | 1 | 4 | 杨幂 | 2 | | 2 | 耿宇星 | 2 | 4 | 杨幂 | 2 | | 3 | 陈飞 | 3 | 4 | 杨幂 | 2 | | 4 | 彭兴旭 | 4 | 4 | 杨幂 | 2 | | 5 | 李慧鹏 | 5 | 4 | 杨幂 | 2 | | 6 | 孟欣 | 6 | 4 | 杨幂 | 2 | | 7 | 鲜惠珊 | 7 | 4 | 杨幂 | 2 | | 8 | 陈劲 | 8 | 4 | 杨幂 | 2 | | 9 | 居彭阳 | 9 | 4 | 杨幂 | 2 | | 10 | 李嘉韵 | 10 | 4 | 杨幂 | 2 | | 11 | 石闹闹 | 11 | 4 | 杨幂 | 2 | | 12 | 肖风 | 12 | 4 | 杨幂 | 2 | | 13 | 刘晓江 | 13 | 4 | 杨幂 | 2 | | 1 | 尹正杰 | 1 | 5 | 胡歌 | 12 | | 2 | 耿宇星 | 2 | 5 | 胡歌 | 12 | | 3 | 陈飞 | 3 | 5 | 胡歌 | 12 | | 4 | 彭兴旭 | 4 | 5 | 胡歌 | 12 | | 5 | 李慧鹏 | 5 | 5 | 胡歌 | 12 | | 6 | 孟欣 | 6 | 5 | 胡歌 | 12 | | 7 | 鲜惠珊 | 7 | 5 | 胡歌 | 12 | | 8 | 陈劲 | 8 | 5 | 胡歌 | 12 | | 9 | 居彭阳 | 9 | 5 | 胡歌 | 12 | | 10 | 李嘉韵 | 10 | 5 | 胡歌 | 12 | | 11 | 石闹闹 | 11 | 5 | 胡歌 | 12 | | 12 | 肖风 | 12 | 5 | 胡歌 | 12 | | 13 | 刘晓江 | 13 | 5 | 胡歌 | 12 | | 1 | 尹正杰 | 1 | 6 | 刘德华 | 3 | | 2 | 耿宇星 | 2 | 6 | 刘德华 | 3 | | 3 | 陈飞 | 3 | 6 | 刘德华 | 3 | | 4 | 彭兴旭 | 4 | 6 | 刘德华 | 3 | | 5 | 李慧鹏 | 5 | 6 | 刘德华 | 3 | | 6 | 孟欣 | 6 | 6 | 刘德华 | 3 | | 7 | 鲜惠珊 | 7 | 6 | 刘德华 | 3 | | 8 | 陈劲 | 8 | 6 | 刘德华 | 3 | | 9 | 居彭阳 | 9 | 6 | 刘德华 | 3 | | 10 | 李嘉韵 | 10 | 6 | 刘德华 | 3 | | 11 | 石闹闹 | 11 | 6 | 刘德华 | 3 | | 12 | 肖风 | 12 | 6 | 刘德华 | 3 | | 13 | 刘晓江 | 13 | 6 | 刘德华 | 3 | | 1 | 尹正杰 | 1 | 7 | 张学友 | 10 | | 2 | 耿宇星 | 2 | 7 | 张学友 | 10 | | 3 | 陈飞 | 3 | 7 | 张学友 | 10 | | 4 | 彭兴旭 | 4 | 7 | 张学友 | 10 | | 5 | 李慧鹏 | 5 | 7 | 张学友 | 10 | | 6 | 孟欣 | 6 | 7 | 张学友 | 10 | | 7 | 鲜惠珊 | 7 | 7 | 张学友 | 10 | | 8 | 陈劲 | 8 | 7 | 张学友 | 10 | | 9 | 居彭阳 | 9 | 7 | 张学友 | 10 | | 10 | 李嘉韵 | 10 | 7 | 张学友 | 10 | | 11 | 石闹闹 | 11 | 7 | 张学友 | 10 | | 12 | 肖风 | 12 | 7 | 张学友 | 10 | | 13 | 刘晓江 | 13 | 7 | 张学友 | 10 | | 1 | 尹正杰 | 1 | 8 | 郭德纲 | 4 | | 2 | 耿宇星 | 2 | 8 | 郭德纲 | 4 | | 3 | 陈飞 | 3 | 8 | 郭德纲 | 4 | | 4 | 彭兴旭 | 4 | 8 | 郭德纲 | 4 | | 5 | 李慧鹏 | 5 | 8 | 郭德纲 | 4 | | 6 | 孟欣 | 6 | 8 | 郭德纲 | 4 | | 7 | 鲜惠珊 | 7 | 8 | 郭德纲 | 4 | | 8 | 陈劲 | 8 | 8 | 郭德纲 | 4 | | 9 | 居彭阳 | 9 | 8 | 郭德纲 | 4 | | 10 | 李嘉韵 | 10 | 8 | 郭德纲 | 4 | | 11 | 石闹闹 | 11 | 8 | 郭德纲 | 4 | | 12 | 肖风 | 12 | 8 | 郭德纲 | 4 | | 13 | 刘晓江 | 13 | 8 | 郭德纲 | 4 | | 1 | 尹正杰 | 1 | 9 | 张杰 | 9 | | 2 | 耿宇星 | 2 | 9 | 张杰 | 9 | | 3 | 陈飞 | 3 | 9 | 张杰 | 9 | | 4 | 彭兴旭 | 4 | 9 | 张杰 | 9 | | 5 | 李慧鹏 | 5 | 9 | 张杰 | 9 | | 6 | 孟欣 | 6 | 9 | 张杰 | 9 | | 7 | 鲜惠珊 | 7 | 9 | 张杰 | 9 | | 8 | 陈劲 | 8 | 9 | 张杰 | 9 | | 9 | 居彭阳 | 9 | 9 | 张杰 | 9 | | 10 | 李嘉韵 | 10 | 9 | 张杰 | 9 | | 11 | 石闹闹 | 11 | 9 | 张杰 | 9 | | 12 | 肖风 | 12 | 9 | 张杰 | 9 | | 13 | 刘晓江 | 13 | 9 | 张杰 | 9 | | 1 | 尹正杰 | 1 | 10 | 苍老师 | 5 | | 2 | 耿宇星 | 2 | 10 | 苍老师 | 5 | | 3 | 陈飞 | 3 | 10 | 苍老师 | 5 | | 4 | 彭兴旭 | 4 | 10 | 苍老师 | 5 | | 5 | 李慧鹏 | 5 | 10 | 苍老师 | 5 | | 6 | 孟欣 | 6 | 10 | 苍老师 | 5 | | 7 | 鲜惠珊 | 7 | 10 | 苍老师 | 5 | | 8 | 陈劲 | 8 | 10 | 苍老师 | 5 | | 9 | 居彭阳 | 9 | 10 | 苍老师 | 5 | | 10 | 李嘉韵 | 10 | 10 | 苍老师 | 5 | | 11 | 石闹闹 | 11 | 10 | 苍老师 | 5 | | 12 | 肖风 | 12 | 10 | 苍老师 | 5 | | 13 | 刘晓江 | 13 | 10 | 苍老师 | 5 | | 1 | 尹正杰 | 1 | 11 | 谢娜 | 8 | | 2 | 耿宇星 | 2 | 11 | 谢娜 | 8 | | 3 | 陈飞 | 3 | 11 | 谢娜 | 8 | | 4 | 彭兴旭 | 4 | 11 | 谢娜 | 8 | | 5 | 李慧鹏 | 5 | 11 | 谢娜 | 8 | | 6 | 孟欣 | 6 | 11 | 谢娜 | 8 | | 7 | 鲜惠珊 | 7 | 11 | 谢娜 | 8 | | 8 | 陈劲 | 8 | 11 | 谢娜 | 8 | | 9 | 居彭阳 | 9 | 11 | 谢娜 | 8 | | 10 | 李嘉韵 | 10 | 11 | 谢娜 | 8 | | 11 | 石闹闹 | 11 | 11 | 谢娜 | 8 | | 12 | 肖风 | 12 | 11 | 谢娜 | 8 | | 13 | 刘晓江 | 13 | 11 | 谢娜 | 8 | | 1 | 尹正杰 | 1 | 12 | 赵薇 | 6 | | 2 | 耿宇星 | 2 | 12 | 赵薇 | 6 | | 3 | 陈飞 | 3 | 12 | 赵薇 | 6 | | 4 | 彭兴旭 | 4 | 12 | 赵薇 | 6 | | 5 | 李慧鹏 | 5 | 12 | 赵薇 | 6 | | 6 | 孟欣 | 6 | 12 | 赵薇 | 6 | | 7 | 鲜惠珊 | 7 | 12 | 赵薇 | 6 | | 8 | 陈劲 | 8 | 12 | 赵薇 | 6 | | 9 | 居彭阳 | 9 | 12 | 赵薇 | 6 | | 10 | 李嘉韵 | 10 | 12 | 赵薇 | 6 | | 11 | 石闹闹 | 11 | 12 | 赵薇 | 6 | | 12 | 肖风 | 12 | 12 | 赵薇 | 6 | | 13 | 刘晓江 | 13 | 12 | 赵薇 | 6 | | 1 | 尹正杰 | 1 | 13 | 张卫健 | 7 | | 2 | 耿宇星 | 2 | 13 | 张卫健 | 7 | | 3 | 陈飞 | 3 | 13 | 张卫健 | 7 | | 4 | 彭兴旭 | 4 | 13 | 张卫健 | 7 | | 5 | 李慧鹏 | 5 | 13 | 张卫健 | 7 | | 6 | 孟欣 | 6 | 13 | 张卫健 | 7 | | 7 | 鲜惠珊 | 7 | 13 | 张卫健 | 7 | | 8 | 陈劲 | 8 | 13 | 张卫健 | 7 | | 9 | 居彭阳 | 9 | 13 | 张卫健 | 7 | | 10 | 李嘉韵 | 10 | 13 | 张卫健 | 7 | | 11 | 石闹闹 | 11 | 13 | 张卫健 | 7 | | 12 | 肖风 | 12 | 13 | 张卫健 | 7 | | 13 | 刘晓江 | 13 | 13 | 张卫健 | 7 | +----+-----------+------------+----+-----------+-----------+ 169 rows in set (0.00 sec) mysql>
mysql> SELECT student.*,teacher.* FROM student INNER JOIN teacher; +----+-----------+------------+----+-----------+-----------+ | id | name | teacher_id | id | name | course_id | +----+-----------+------------+----+-----------+-----------+ | 1 | 尹正杰 | 1 | 1 | 谢霆锋 | 11 | | 2 | 耿宇星 | 2 | 1 | 谢霆锋 | 11 | | 3 | 陈飞 | 3 | 1 | 谢霆锋 | 11 | | 4 | 彭兴旭 | 4 | 1 | 谢霆锋 | 11 | | 5 | 李慧鹏 | 5 | 1 | 谢霆锋 | 11 | | 6 | 孟欣 | 6 | 1 | 谢霆锋 | 11 | | 7 | 鲜惠珊 | 7 | 1 | 谢霆锋 | 11 | | 8 | 陈劲 | 8 | 1 | 谢霆锋 | 11 | | 9 | 居彭阳 | 9 | 1 | 谢霆锋 | 11 | | 10 | 李嘉韵 | 10 | 1 | 谢霆锋 | 11 | | 11 | 石闹闹 | 11 | 1 | 谢霆锋 | 11 | | 12 | 肖风 | 12 | 1 | 谢霆锋 | 11 | | 13 | 刘晓江 | 13 | 1 | 谢霆锋 | 11 | | 1 | 尹正杰 | 1 | 2 | 周杰伦 | 1 | | 2 | 耿宇星 | 2 | 2 | 周杰伦 | 1 | | 3 | 陈飞 | 3 | 2 | 周杰伦 | 1 | | 4 | 彭兴旭 | 4 | 2 | 周杰伦 | 1 | | 5 | 李慧鹏 | 5 | 2 | 周杰伦 | 1 | | 6 | 孟欣 | 6 | 2 | 周杰伦 | 1 | | 7 | 鲜惠珊 | 7 | 2 | 周杰伦 | 1 | | 8 | 陈劲 | 8 | 2 | 周杰伦 | 1 | | 9 | 居彭阳 | 9 | 2 | 周杰伦 | 1 | | 10 | 李嘉韵 | 10 | 2 | 周杰伦 | 1 | | 11 | 石闹闹 | 11 | 2 | 周杰伦 | 1 | | 12 | 肖风 | 12 | 2 | 周杰伦 | 1 | | 13 | 刘晓江 | 13 | 2 | 周杰伦 | 1 | | 1 | 尹正杰 | 1 | 3 | 蔡依林 | 13 | | 2 | 耿宇星 | 2 | 3 | 蔡依林 | 13 | | 3 | 陈飞 | 3 | 3 | 蔡依林 | 13 | | 4 | 彭兴旭 | 4 | 3 | 蔡依林 | 13 | | 5 | 李慧鹏 | 5 | 3 | 蔡依林 | 13 | | 6 | 孟欣 | 6 | 3 | 蔡依林 | 13 | | 7 | 鲜惠珊 | 7 | 3 | 蔡依林 | 13 | | 8 | 陈劲 | 8 | 3 | 蔡依林 | 13 | | 9 | 居彭阳 | 9 | 3 | 蔡依林 | 13 | | 10 | 李嘉韵 | 10 | 3 | 蔡依林 | 13 | | 11 | 石闹闹 | 11 | 3 | 蔡依林 | 13 | | 12 | 肖风 | 12 | 3 | 蔡依林 | 13 | | 13 | 刘晓江 | 13 | 3 | 蔡依林 | 13 | | 1 | 尹正杰 | 1 | 4 | 杨幂 | 2 | | 2 | 耿宇星 | 2 | 4 | 杨幂 | 2 | | 3 | 陈飞 | 3 | 4 | 杨幂 | 2 | | 4 | 彭兴旭 | 4 | 4 | 杨幂 | 2 | | 5 | 李慧鹏 | 5 | 4 | 杨幂 | 2 | | 6 | 孟欣 | 6 | 4 | 杨幂 | 2 | | 7 | 鲜惠珊 | 7 | 4 | 杨幂 | 2 | | 8 | 陈劲 | 8 | 4 | 杨幂 | 2 | | 9 | 居彭阳 | 9 | 4 | 杨幂 | 2 | | 10 | 李嘉韵 | 10 | 4 | 杨幂 | 2 | | 11 | 石闹闹 | 11 | 4 | 杨幂 | 2 | | 12 | 肖风 | 12 | 4 | 杨幂 | 2 | | 13 | 刘晓江 | 13 | 4 | 杨幂 | 2 | | 1 | 尹正杰 | 1 | 5 | 胡歌 | 12 | | 2 | 耿宇星 | 2 | 5 | 胡歌 | 12 | | 3 | 陈飞 | 3 | 5 | 胡歌 | 12 | | 4 | 彭兴旭 | 4 | 5 | 胡歌 | 12 | | 5 | 李慧鹏 | 5 | 5 | 胡歌 | 12 | | 6 | 孟欣 | 6 | 5 | 胡歌 | 12 | | 7 | 鲜惠珊 | 7 | 5 | 胡歌 | 12 | | 8 | 陈劲 | 8 | 5 | 胡歌 | 12 | | 9 | 居彭阳 | 9 | 5 | 胡歌 | 12 | | 10 | 李嘉韵 | 10 | 5 | 胡歌 | 12 | | 11 | 石闹闹 | 11 | 5 | 胡歌 | 12 | | 12 | 肖风 | 12 | 5 | 胡歌 | 12 | | 13 | 刘晓江 | 13 | 5 | 胡歌 | 12 | | 1 | 尹正杰 | 1 | 6 | 刘德华 | 3 | | 2 | 耿宇星 | 2 | 6 | 刘德华 | 3 | | 3 | 陈飞 | 3 | 6 | 刘德华 | 3 | | 4 | 彭兴旭 | 4 | 6 | 刘德华 | 3 | | 5 | 李慧鹏 | 5 | 6 | 刘德华 | 3 | | 6 | 孟欣 | 6 | 6 | 刘德华 | 3 | | 7 | 鲜惠珊 | 7 | 6 | 刘德华 | 3 | | 8 | 陈劲 | 8 | 6 | 刘德华 | 3 | | 9 | 居彭阳 | 9 | 6 | 刘德华 | 3 | | 10 | 李嘉韵 | 10 | 6 | 刘德华 | 3 | | 11 | 石闹闹 | 11 | 6 | 刘德华 | 3 | | 12 | 肖风 | 12 | 6 | 刘德华 | 3 | | 13 | 刘晓江 | 13 | 6 | 刘德华 | 3 | | 1 | 尹正杰 | 1 | 7 | 张学友 | 10 | | 2 | 耿宇星 | 2 | 7 | 张学友 | 10 | | 3 | 陈飞 | 3 | 7 | 张学友 | 10 | | 4 | 彭兴旭 | 4 | 7 | 张学友 | 10 | | 5 | 李慧鹏 | 5 | 7 | 张学友 | 10 | | 6 | 孟欣 | 6 | 7 | 张学友 | 10 | | 7 | 鲜惠珊 | 7 | 7 | 张学友 | 10 | | 8 | 陈劲 | 8 | 7 | 张学友 | 10 | | 9 | 居彭阳 | 9 | 7 | 张学友 | 10 | | 10 | 李嘉韵 | 10 | 7 | 张学友 | 10 | | 11 | 石闹闹 | 11 | 7 | 张学友 | 10 | | 12 | 肖风 | 12 | 7 | 张学友 | 10 | | 13 | 刘晓江 | 13 | 7 | 张学友 | 10 | | 1 | 尹正杰 | 1 | 8 | 郭德纲 | 4 | | 2 | 耿宇星 | 2 | 8 | 郭德纲 | 4 | | 3 | 陈飞 | 3 | 8 | 郭德纲 | 4 | | 4 | 彭兴旭 | 4 | 8 | 郭德纲 | 4 | | 5 | 李慧鹏 | 5 | 8 | 郭德纲 | 4 | | 6 | 孟欣 | 6 | 8 | 郭德纲 | 4 | | 7 | 鲜惠珊 | 7 | 8 | 郭德纲 | 4 | | 8 | 陈劲 | 8 | 8 | 郭德纲 | 4 | | 9 | 居彭阳 | 9 | 8 | 郭德纲 | 4 | | 10 | 李嘉韵 | 10 | 8 | 郭德纲 | 4 | | 11 | 石闹闹 | 11 | 8 | 郭德纲 | 4 | | 12 | 肖风 | 12 | 8 | 郭德纲 | 4 | | 13 | 刘晓江 | 13 | 8 | 郭德纲 | 4 | | 1 | 尹正杰 | 1 | 9 | 张杰 | 9 | | 2 | 耿宇星 | 2 | 9 | 张杰 | 9 | | 3 | 陈飞 | 3 | 9 | 张杰 | 9 | | 4 | 彭兴旭 | 4 | 9 | 张杰 | 9 | | 5 | 李慧鹏 | 5 | 9 | 张杰 | 9 | | 6 | 孟欣 | 6 | 9 | 张杰 | 9 | | 7 | 鲜惠珊 | 7 | 9 | 张杰 | 9 | | 8 | 陈劲 | 8 | 9 | 张杰 | 9 | | 9 | 居彭阳 | 9 | 9 | 张杰 | 9 | | 10 | 李嘉韵 | 10 | 9 | 张杰 | 9 | | 11 | 石闹闹 | 11 | 9 | 张杰 | 9 | | 12 | 肖风 | 12 | 9 | 张杰 | 9 | | 13 | 刘晓江 | 13 | 9 | 张杰 | 9 | | 1 | 尹正杰 | 1 | 10 | 苍老师 | 5 | | 2 | 耿宇星 | 2 | 10 | 苍老师 | 5 | | 3 | 陈飞 | 3 | 10 | 苍老师 | 5 | | 4 | 彭兴旭 | 4 | 10 | 苍老师 | 5 | | 5 | 李慧鹏 | 5 | 10 | 苍老师 | 5 | | 6 | 孟欣 | 6 | 10 | 苍老师 | 5 | | 7 | 鲜惠珊 | 7 | 10 | 苍老师 | 5 | | 8 | 陈劲 | 8 | 10 | 苍老师 | 5 | | 9 | 居彭阳 | 9 | 10 | 苍老师 | 5 | | 10 | 李嘉韵 | 10 | 10 | 苍老师 | 5 | | 11 | 石闹闹 | 11 | 10 | 苍老师 | 5 | | 12 | 肖风 | 12 | 10 | 苍老师 | 5 | | 13 | 刘晓江 | 13 | 10 | 苍老师 | 5 | | 1 | 尹正杰 | 1 | 11 | 谢娜 | 8 | | 2 | 耿宇星 | 2 | 11 | 谢娜 | 8 | | 3 | 陈飞 | 3 | 11 | 谢娜 | 8 | | 4 | 彭兴旭 | 4 | 11 | 谢娜 | 8 | | 5 | 李慧鹏 | 5 | 11 | 谢娜 | 8 | | 6 | 孟欣 | 6 | 11 | 谢娜 | 8 | | 7 | 鲜惠珊 | 7 | 11 | 谢娜 | 8 | | 8 | 陈劲 | 8 | 11 | 谢娜 | 8 | | 9 | 居彭阳 | 9 | 11 | 谢娜 | 8 | | 10 | 李嘉韵 | 10 | 11 | 谢娜 | 8 | | 11 | 石闹闹 | 11 | 11 | 谢娜 | 8 | | 12 | 肖风 | 12 | 11 | 谢娜 | 8 | | 13 | 刘晓江 | 13 | 11 | 谢娜 | 8 | | 1 | 尹正杰 | 1 | 12 | 赵薇 | 6 | | 2 | 耿宇星 | 2 | 12 | 赵薇 | 6 | | 3 | 陈飞 | 3 | 12 | 赵薇 | 6 | | 4 | 彭兴旭 | 4 | 12 | 赵薇 | 6 | | 5 | 李慧鹏 | 5 | 12 | 赵薇 | 6 | | 6 | 孟欣 | 6 | 12 | 赵薇 | 6 | | 7 | 鲜惠珊 | 7 | 12 | 赵薇 | 6 | | 8 | 陈劲 | 8 | 12 | 赵薇 | 6 | | 9 | 居彭阳 | 9 | 12 | 赵薇 | 6 | | 10 | 李嘉韵 | 10 | 12 | 赵薇 | 6 | | 11 | 石闹闹 | 11 | 12 | 赵薇 | 6 | | 12 | 肖风 | 12 | 12 | 赵薇 | 6 | | 13 | 刘晓江 | 13 | 12 | 赵薇 | 6 | | 1 | 尹正杰 | 1 | 13 | 张卫健 | 7 | | 2 | 耿宇星 | 2 | 13 | 张卫健 | 7 | | 3 | 陈飞 | 3 | 13 | 张卫健 | 7 | | 4 | 彭兴旭 | 4 | 13 | 张卫健 | 7 | | 5 | 李慧鹏 | 5 | 13 | 张卫健 | 7 | | 6 | 孟欣 | 6 | 13 | 张卫健 | 7 | | 7 | 鲜惠珊 | 7 | 13 | 张卫健 | 7 | | 8 | 陈劲 | 8 | 13 | 张卫健 | 7 | | 9 | 居彭阳 | 9 | 13 | 张卫健 | 7 | | 10 | 李嘉韵 | 10 | 13 | 张卫健 | 7 | | 11 | 石闹闹 | 11 | 13 | 张卫健 | 7 | | 12 | 肖风 | 12 | 13 | 张卫健 | 7 | | 13 | 刘晓江 | 13 | 13 | 张卫健 | 7 | +----+-----------+------------+----+-----------+-----------+ 169 rows in set (0.00 sec) mysql>
mysql> SELECT * FROM student,teacher; +----+-----------+------------+----+-----------+-----------+ | id | name | teacher_id | id | name | course_id | +----+-----------+------------+----+-----------+-----------+ | 1 | 尹正杰 | 1 | 1 | 谢霆锋 | 11 | | 2 | 耿宇星 | 2 | 1 | 谢霆锋 | 11 | | 3 | 陈飞 | 3 | 1 | 谢霆锋 | 11 | | 4 | 彭兴旭 | 4 | 1 | 谢霆锋 | 11 | | 5 | 李慧鹏 | 5 | 1 | 谢霆锋 | 11 | | 6 | 孟欣 | 6 | 1 | 谢霆锋 | 11 | | 7 | 鲜惠珊 | 7 | 1 | 谢霆锋 | 11 | | 8 | 陈劲 | 8 | 1 | 谢霆锋 | 11 | | 9 | 居彭阳 | 9 | 1 | 谢霆锋 | 11 | | 10 | 李嘉韵 | 10 | 1 | 谢霆锋 | 11 | | 11 | 石闹闹 | 11 | 1 | 谢霆锋 | 11 | | 12 | 肖风 | 12 | 1 | 谢霆锋 | 11 | | 13 | 刘晓江 | 13 | 1 | 谢霆锋 | 11 | | 1 | 尹正杰 | 1 | 2 | 周杰伦 | 1 | | 2 | 耿宇星 | 2 | 2 | 周杰伦 | 1 | | 3 | 陈飞 | 3 | 2 | 周杰伦 | 1 | | 4 | 彭兴旭 | 4 | 2 | 周杰伦 | 1 | | 5 | 李慧鹏 | 5 | 2 | 周杰伦 | 1 | | 6 | 孟欣 | 6 | 2 | 周杰伦 | 1 | | 7 | 鲜惠珊 | 7 | 2 | 周杰伦 | 1 | | 8 | 陈劲 | 8 | 2 | 周杰伦 | 1 | | 9 | 居彭阳 | 9 | 2 | 周杰伦 | 1 | | 10 | 李嘉韵 | 10 | 2 | 周杰伦 | 1 | | 11 | 石闹闹 | 11 | 2 | 周杰伦 | 1 | | 12 | 肖风 | 12 | 2 | 周杰伦 | 1 | | 13 | 刘晓江 | 13 | 2 | 周杰伦 | 1 | | 1 | 尹正杰 | 1 | 3 | 蔡依林 | 13 | | 2 | 耿宇星 | 2 | 3 | 蔡依林 | 13 | | 3 | 陈飞 | 3 | 3 | 蔡依林 | 13 | | 4 | 彭兴旭 | 4 | 3 | 蔡依林 | 13 | | 5 | 李慧鹏 | 5 | 3 | 蔡依林 | 13 | | 6 | 孟欣 | 6 | 3 | 蔡依林 | 13 | | 7 | 鲜惠珊 | 7 | 3 | 蔡依林 | 13 | | 8 | 陈劲 | 8 | 3 | 蔡依林 | 13 | | 9 | 居彭阳 | 9 | 3 | 蔡依林 | 13 | | 10 | 李嘉韵 | 10 | 3 | 蔡依林 | 13 | | 11 | 石闹闹 | 11 | 3 | 蔡依林 | 13 | | 12 | 肖风 | 12 | 3 | 蔡依林 | 13 | | 13 | 刘晓江 | 13 | 3 | 蔡依林 | 13 | | 1 | 尹正杰 | 1 | 4 | 杨幂 | 2 | | 2 | 耿宇星 | 2 | 4 | 杨幂 | 2 | | 3 | 陈飞 | 3 | 4 | 杨幂 | 2 | | 4 | 彭兴旭 | 4 | 4 | 杨幂 | 2 | | 5 | 李慧鹏 | 5 | 4 | 杨幂 | 2 | | 6 | 孟欣 | 6 | 4 | 杨幂 | 2 | | 7 | 鲜惠珊 | 7 | 4 | 杨幂 | 2 | | 8 | 陈劲 | 8 | 4 | 杨幂 | 2 | | 9 | 居彭阳 | 9 | 4 | 杨幂 | 2 | | 10 | 李嘉韵 | 10 | 4 | 杨幂 | 2 | | 11 | 石闹闹 | 11 | 4 | 杨幂 | 2 | | 12 | 肖风 | 12 | 4 | 杨幂 | 2 | | 13 | 刘晓江 | 13 | 4 | 杨幂 | 2 | | 1 | 尹正杰 | 1 | 5 | 胡歌 | 12 | | 2 | 耿宇星 | 2 | 5 | 胡歌 | 12 | | 3 | 陈飞 | 3 | 5 | 胡歌 | 12 | | 4 | 彭兴旭 | 4 | 5 | 胡歌 | 12 | | 5 | 李慧鹏 | 5 | 5 | 胡歌 | 12 | | 6 | 孟欣 | 6 | 5 | 胡歌 | 12 | | 7 | 鲜惠珊 | 7 | 5 | 胡歌 | 12 | | 8 | 陈劲 | 8 | 5 | 胡歌 | 12 | | 9 | 居彭阳 | 9 | 5 | 胡歌 | 12 | | 10 | 李嘉韵 | 10 | 5 | 胡歌 | 12 | | 11 | 石闹闹 | 11 | 5 | 胡歌 | 12 | | 12 | 肖风 | 12 | 5 | 胡歌 | 12 | | 13 | 刘晓江 | 13 | 5 | 胡歌 | 12 | | 1 | 尹正杰 | 1 | 6 | 刘德华 | 3 | | 2 | 耿宇星 | 2 | 6 | 刘德华 | 3 | | 3 | 陈飞 | 3 | 6 | 刘德华 | 3 | | 4 | 彭兴旭 | 4 | 6 | 刘德华 | 3 | | 5 | 李慧鹏 | 5 | 6 | 刘德华 | 3 | | 6 | 孟欣 | 6 | 6 | 刘德华 | 3 | | 7 | 鲜惠珊 | 7 | 6 | 刘德华 | 3 | | 8 | 陈劲 | 8 | 6 | 刘德华 | 3 | | 9 | 居彭阳 | 9 | 6 | 刘德华 | 3 | | 10 | 李嘉韵 | 10 | 6 | 刘德华 | 3 | | 11 | 石闹闹 | 11 | 6 | 刘德华 | 3 | | 12 | 肖风 | 12 | 6 | 刘德华 | 3 | | 13 | 刘晓江 | 13 | 6 | 刘德华 | 3 | | 1 | 尹正杰 | 1 | 7 | 张学友 | 10 | | 2 | 耿宇星 | 2 | 7 | 张学友 | 10 | | 3 | 陈飞 | 3 | 7 | 张学友 | 10 | | 4 | 彭兴旭 | 4 | 7 | 张学友 | 10 | | 5 | 李慧鹏 | 5 | 7 | 张学友 | 10 | | 6 | 孟欣 | 6 | 7 | 张学友 | 10 | | 7 | 鲜惠珊 | 7 | 7 | 张学友 | 10 | | 8 | 陈劲 | 8 | 7 | 张学友 | 10 | | 9 | 居彭阳 | 9 | 7 | 张学友 | 10 | | 10 | 李嘉韵 | 10 | 7 | 张学友 | 10 | | 11 | 石闹闹 | 11 | 7 | 张学友 | 10 | | 12 | 肖风 | 12 | 7 | 张学友 | 10 | | 13 | 刘晓江 | 13 | 7 | 张学友 | 10 | | 1 | 尹正杰 | 1 | 8 | 郭德纲 | 4 | | 2 | 耿宇星 | 2 | 8 | 郭德纲 | 4 | | 3 | 陈飞 | 3 | 8 | 郭德纲 | 4 | | 4 | 彭兴旭 | 4 | 8 | 郭德纲 | 4 | | 5 | 李慧鹏 | 5 | 8 | 郭德纲 | 4 | | 6 | 孟欣 | 6 | 8 | 郭德纲 | 4 | | 7 | 鲜惠珊 | 7 | 8 | 郭德纲 | 4 | | 8 | 陈劲 | 8 | 8 | 郭德纲 | 4 | | 9 | 居彭阳 | 9 | 8 | 郭德纲 | 4 | | 10 | 李嘉韵 | 10 | 8 | 郭德纲 | 4 | | 11 | 石闹闹 | 11 | 8 | 郭德纲 | 4 | | 12 | 肖风 | 12 | 8 | 郭德纲 | 4 | | 13 | 刘晓江 | 13 | 8 | 郭德纲 | 4 | | 1 | 尹正杰 | 1 | 9 | 张杰 | 9 | | 2 | 耿宇星 | 2 | 9 | 张杰 | 9 | | 3 | 陈飞 | 3 | 9 | 张杰 | 9 | | 4 | 彭兴旭 | 4 | 9 | 张杰 | 9 | | 5 | 李慧鹏 | 5 | 9 | 张杰 | 9 | | 6 | 孟欣 | 6 | 9 | 张杰 | 9 | | 7 | 鲜惠珊 | 7 | 9 | 张杰 | 9 | | 8 | 陈劲 | 8 | 9 | 张杰 | 9 | | 9 | 居彭阳 | 9 | 9 | 张杰 | 9 | | 10 | 李嘉韵 | 10 | 9 | 张杰 | 9 | | 11 | 石闹闹 | 11 | 9 | 张杰 | 9 | | 12 | 肖风 | 12 | 9 | 张杰 | 9 | | 13 | 刘晓江 | 13 | 9 | 张杰 | 9 | | 1 | 尹正杰 | 1 | 10 | 苍老师 | 5 | | 2 | 耿宇星 | 2 | 10 | 苍老师 | 5 | | 3 | 陈飞 | 3 | 10 | 苍老师 | 5 | | 4 | 彭兴旭 | 4 | 10 | 苍老师 | 5 | | 5 | 李慧鹏 | 5 | 10 | 苍老师 | 5 | | 6 | 孟欣 | 6 | 10 | 苍老师 | 5 | | 7 | 鲜惠珊 | 7 | 10 | 苍老师 | 5 | | 8 | 陈劲 | 8 | 10 | 苍老师 | 5 | | 9 | 居彭阳 | 9 | 10 | 苍老师 | 5 | | 10 | 李嘉韵 | 10 | 10 | 苍老师 | 5 | | 11 | 石闹闹 | 11 | 10 | 苍老师 | 5 | | 12 | 肖风 | 12 | 10 | 苍老师 | 5 | | 13 | 刘晓江 | 13 | 10 | 苍老师 | 5 | | 1 | 尹正杰 | 1 | 11 | 谢娜 | 8 | | 2 | 耿宇星 | 2 | 11 | 谢娜 | 8 | | 3 | 陈飞 | 3 | 11 | 谢娜 | 8 | | 4 | 彭兴旭 | 4 | 11 | 谢娜 | 8 | | 5 | 李慧鹏 | 5 | 11 | 谢娜 | 8 | | 6 | 孟欣 | 6 | 11 | 谢娜 | 8 | | 7 | 鲜惠珊 | 7 | 11 | 谢娜 | 8 | | 8 | 陈劲 | 8 | 11 | 谢娜 | 8 | | 9 | 居彭阳 | 9 | 11 | 谢娜 | 8 | | 10 | 李嘉韵 | 10 | 11 | 谢娜 | 8 | | 11 | 石闹闹 | 11 | 11 | 谢娜 | 8 | | 12 | 肖风 | 12 | 11 | 谢娜 | 8 | | 13 | 刘晓江 | 13 | 11 | 谢娜 | 8 | | 1 | 尹正杰 | 1 | 12 | 赵薇 | 6 | | 2 | 耿宇星 | 2 | 12 | 赵薇 | 6 | | 3 | 陈飞 | 3 | 12 | 赵薇 | 6 | | 4 | 彭兴旭 | 4 | 12 | 赵薇 | 6 | | 5 | 李慧鹏 | 5 | 12 | 赵薇 | 6 | | 6 | 孟欣 | 6 | 12 | 赵薇 | 6 | | 7 | 鲜惠珊 | 7 | 12 | 赵薇 | 6 | | 8 | 陈劲 | 8 | 12 | 赵薇 | 6 | | 9 | 居彭阳 | 9 | 12 | 赵薇 | 6 | | 10 | 李嘉韵 | 10 | 12 | 赵薇 | 6 | | 11 | 石闹闹 | 11 | 12 | 赵薇 | 6 | | 12 | 肖风 | 12 | 12 | 赵薇 | 6 | | 13 | 刘晓江 | 13 | 12 | 赵薇 | 6 | | 1 | 尹正杰 | 1 | 13 | 张卫健 | 7 | | 2 | 耿宇星 | 2 | 13 | 张卫健 | 7 | | 3 | 陈飞 | 3 | 13 | 张卫健 | 7 | | 4 | 彭兴旭 | 4 | 13 | 张卫健 | 7 | | 5 | 李慧鹏 | 5 | 13 | 张卫健 | 7 | | 6 | 孟欣 | 6 | 13 | 张卫健 | 7 | | 7 | 鲜惠珊 | 7 | 13 | 张卫健 | 7 | | 8 | 陈劲 | 8 | 13 | 张卫健 | 7 | | 9 | 居彭阳 | 9 | 13 | 张卫健 | 7 | | 10 | 李嘉韵 | 10 | 13 | 张卫健 | 7 | | 11 | 石闹闹 | 11 | 13 | 张卫健 | 7 | | 12 | 肖风 | 12 | 13 | 张卫健 | 7 | | 13 | 刘晓江 | 13 | 13 | 张卫健 | 7 | +----+-----------+------------+----+-----------+-----------+ 169 rows in set (0.00 sec) mysql> mysql>
5>.SELECT_EXPR也可以使用MySQL内部的函数，另外字段也可以使用别名
mysql> SELECT CONCAT(id,'-',name) AS '编号-姓名' FROM student ; +---------------+ | 编号-姓名 | +---------------+ | 1-尹正杰 | | 2-耿宇星 | | 3-陈飞 | | 4-彭兴旭 | | 5-李慧鹏 | | 6-孟欣 | | 7-鲜惠珊 | | 8-陈劲 | | 9-居彭阳 | | 10-李嘉韵 | | 11-石闹闹 | | 12-肖风 | | 13-刘晓江 | +---------------+ 13 rows in set (0.00 sec) mysql> mysql>
mysql> SELECT CONCAT(id,'-',name) '编号-姓名' FROM student ; +---------------+ | 编号-姓名 | +---------------+ | 1-尹正杰 | | 2-耿宇星 | | 3-陈飞 | | 4-彭兴旭 | | 5-李慧鹏 | | 6-孟欣 | | 7-鲜惠珊 | | 8-陈劲 | | 9-居彭阳 | | 10-李嘉韵 | | 11-石闹闹 | | 12-肖风 | | 13-刘晓江 | +---------------+ 13 rows in set (0.00 sec) mysql>
6>.WHERE条件中不能使用SELECT_EXPR中定义的字段别名，因为语句执行数据线是WHERE在SELECT之前，所以WHERE在执行时字段别名未知
mysql> SELECT id AS f1 ,name AS f2 FROM student WHERE f1/2 != 0 ; #由于WHERE执行顺序在SELECT之前，因此我们在SELECT中定义的变量在WHERE中是获取不到的！正如报错所示～ ERROR 1054 (42S22): Unknown column 'f1' in 'where clause' mysql> mysql> SELECT id AS f1 ,name AS f2 FROM student WHERE id <=3 ; +----+-----------+ | f1 | f2 | +----+-----------+ | 1 | 尹正杰 | | 2 | 耿宇星 | | 3 | 陈飞 | +----+-----------+ 3 rows in set (0.00 sec) mysql> mysql>
7>.FROM TABLE_REFERENCES子句指定表面，tbl_name也可以指定别名，当设计当表不再当前的数据库时，需要使用db_name.table_name来指定表和所在的数据库
mysql> SELECT t1.name AS '姓名',t1.teacher_id '老师ID' FROM student AS t1 WHERE id % 2 = 1 LIMIT 3; +-----------+----------+ | 姓名 | 老师ID | +-----------+----------+ | 尹正杰 | 1 | | 陈飞 | 3 | | 李慧鹏 | 5 | +-----------+----------+ 3 rows in set (0.00 sec) mysql> mysql>
8>.当多个表中有相同的字段名，且需要查询出来时，需要在SELECT_EXPR中使用tbl_name.column_name 来显视指定要查询哪个表的字段
9>.查看另外的数据库的表数据
mysql> SELECT * FROM yinzhengjie.student; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql> mysql>
10>.两个数据库里的表关联查询
mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | course | +------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT * FROM students; +-----+-----------+--------+---------+ | sid | sname | gender | dept_id | +-----+-----------+--------+---------+ | 1 | 尹正杰 | boy | 1 | | 2 | 耿雨星 | boy | 1 | | 3 | 鲜惠珊 | girl | 2 | | 4 | 白羽 | boy | 3 | | 5 | 肖凤 | girl | 4 | +-----+-----------+--------+---------+ 5 rows in set (0.00 sec) mysql> mysql> SELECT * FROM yinzhengjie.student; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql> mysql> SELECT * FROM students AS t1 INNER JOIN yinzhengjie.student t2 ON t1.sid = t2.id; +-----+-----------+--------+---------+----+-----------+------------+ | sid | sname | gender | dept_id | id | name | teacher_id | +-----+-----------+--------+---------+----+-----------+------------+ | 1 | 尹正杰 | boy | 1 | 1 | 尹正杰 | 1 | | 2 | 耿雨星 | boy | 1 | 2 | 耿宇星 | 2 | | 3 | 鲜惠珊 | girl | 2 | 3 | 陈飞 | 3 | | 4 | 白羽 | boy | 3 | 4 | 彭兴旭 | 4 | | 5 | 肖凤 | girl | 4 | 5 | 李慧鹏 | 5 | +-----+-----------+--------+---------+----+-----------+------------+ 5 rows in set (0.00 sec) mysql> mysql>
11>.在想用字段名时要指定表名，可以用表的别名（注意执行顺序）
mysql> SELECT * FROM student; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql> mysql> SELECT * FROM teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+-----------+ 13 rows in set (0.00 sec) mysql> mysql> SELECT t1.name FROM student AS t1 INNER JOIN teacher AS t2 ON t1.id = t2.id/2; +-----------+ | name | +-----------+ | 尹正杰 | | 耿宇星 | | 陈飞 | | 彭兴旭 | | 李慧鹏 | | 孟欣 | +-----------+ 6 rows in set (0.00 sec) mysql> mysql>
12>.GROUP BY 子句
代表分组，通常和聚合函数配合使用，如max（最大值），min（最小值），avg（平均值），count（个数），sum（求和）
mysql> CREATE TABLE scores ( id INT(11), subject VARCHAR(30), scores INT(11)); Query OK, 0 rows affected (0.01 sec) mysql> insert into scores values(1,'english',88),(1,'chinese',86),(1,'math',90),(2,'english',95),(2,'chinese', 84); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT sum(scores) FROM scores; +-------------+ | sum(scores) | +-------------+ | 443 | +-------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT id,count(*),max(scores),min(scores),avg(scores),sum(scores) FROM scores GROUP BY id; +------+----------+-------------+-------------+-------------+-------------+ | id | count(*) | max(scores) | min(scores) | avg(scores) | sum(scores) | +------+----------+-------------+-------------+-------------+-------------+ | 1 | 3 | 90 | 86 | 88.0000 | 264 | | 2 | 2 | 95 | 84 | 89.5000 | 179 | +------+----------+-------------+-------------+-------------+-------------+ 2 rows in set (0.00 sec) mysql> mysql>
13>.ORDER BY 与GROUP BY子句可以引用 SELECT_EXPR中的列，通过以下三种方式：
mysql> SELECT id,name FROM course ORDER BY id DESC; +----+-----------+ | id | name | +----+-----------+ | 13 | 计算机 | | 12 | 地理 | | 11 | 政治 | | 10 | 历史 | | 9 | 生物 | | 8 | 化学 | | 7 | 物理 | | 6 | 音乐 | | 5 | 美术 | | 4 | 体育 | | 3 | 英语 | | 2 | 数学 | | 1 | 语文 | +----+-----------+ 13 rows in set (0.00 sec) mysql>
mysql> SELECT id AS f1,name AS f2 FROM course ORDER BY f1 DESC; +----+-----------+ | f1 | f2 | +----+-----------+ | 13 | 计算机 | | 12 | 地理 | | 11 | 政治 | | 10 | 历史 | | 9 | 生物 | | 8 | 化学 | | 7 | 物理 | | 6 | 音乐 | | 5 | 美术 | | 4 | 体育 | | 3 | 英语 | | 2 | 数学 | | 1 | 语文 | +----+-----------+ 13 rows in set (0.00 sec) mysql> mysql>
mysql> SELECT id,name FROM course ORDER BY 1 DESC; +----+-----------+ | id | name | +----+-----------+ | 13 | 计算机 | | 12 | 地理 | | 11 | 政治 | | 10 | 历史 | | 9 | 生物 | | 8 | 化学 | | 7 | 物理 | | 6 | 音乐 | | 5 | 美术 | | 4 | 体育 | | 3 | 英语 | | 2 | 数学 | | 1 | 语文 | +----+-----------+ 13 rows in set (0.00 sec) mysql> mysql>
14>.ORDER BY子句
表示查询结果按照顺序排列，默认是升序排列，可以指定DESC表面按照降序排列。
15>.HAVING 子句
一般是跟在GROUP BY子句中，代表限制分组之后的结果。
mysql> SELECT id,name FROM student GROUP BY id HAVING id < 5; +----+-----------+ | id | name | +----+-----------+ | 1 | 尹正杰 | | 2 | 耿宇星 | | 3 | 陈飞 | | 4 | 彭兴旭 | +----+-----------+ 4 rows in set (0.00 sec) mysql>
16>.LIMIT子句
用来限制查询结果的条数，其后可以带两位>0的整数，第一位代表offset，第二位代表取多少行。
mysql> SELECT id,name FROM student LIMIT 5; +----+-----------+ | id | name | +----+-----------+ | 1 | 尹正杰 | | 2 | 耿宇星 | | 3 | 陈飞 | | 4 | 彭兴旭 | | 5 | 李慧鹏 | +----+-----------+ 5 rows in set (0.00 sec) mysql> mysql> SELECT id,name FROM student LIMIT 0,5; +----+-----------+ | id | name | +----+-----------+ | 1 | 尹正杰 | | 2 | 耿宇星 | | 3 | 陈飞 | | 4 | 彭兴旭 | | 5 | 李慧鹏 | +----+-----------+ 5 rows in set (0.00 sec) mysql> mysql> SELECT id,name FROM student LIMIT 5,7; +----+-----------+ | id | name | +----+-----------+ | 6 | 孟欣 | | 7 | 鲜惠珊 | | 8 | 陈劲 | | 9 | 居彭阳 | | 10 | 李嘉韵 | | 11 | 石闹闹 | | 12 | 肖风 | +----+-----------+ 7 rows in set (0.00 sec) mysql>
17>.SELECT ... INTO语句
代表将查询结果写入文件中或者定义的参数变量中。换句话说，就是将查询的结果存入定义的变量或者文件中。
• SELECT ... INTO var_list
将查询结果存入定义的变量
• SELECT ... INTO OUTFILE 将查询结果按照一定的格式写入到文件中
• SELECT ... INTO DUMPFILE 将查询结果以一行的格式写入到文件中，且只能写入一行
当使用存入变量方法是，需要保证查询结果返回一行，如果不返 回数据则报no data错误，如果返回多行则报Result consisted of more than one row错误，当返回行数不确定时，可以用limit 1强制 只返回一行，具体SQL案例如下所示：
mysql> SELECT * FROM student; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql> mysql> SELECT @x; +------+ | @x | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> SELECT @y; +------+ | @y | +------+ | NULL | +------+ 1 row in set (0.00 sec) mysql> mysql> SELECT id,name INTO @x,@y FROM student LIMIT 1; Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT @x; +------+ | @x | +------+ | 1 | +------+ 1 row in set (0.00 sec) mysql> mysql> SELECT @y; +-----------+ | @y | +-----------+ | 尹正杰 | +-----------+ 1 row in set (0.00 sec) mysql>
使用Select ... into outfile ‘file_name’时，文件会创建在本地服务器 上，所以要确保你的用户能创建文件，而且此file_name不能已经 存在在服务器上以免覆盖其他文件：
mysql> SELECT * FROM student; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql> mysql> SELECT id,name INTO OUTFILE '/yinzhengjie/backup/student.bak' FIELDS TERMINATED BY '-' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM student; Query OK, 13 rows affected, 1 warning (0.00 sec) mysql> mysql> quit Bye [root@node110 ~]# [root@node110 ~]# cat /yinzhengjie/backup/student.bak 1-"尹正杰" 2-"耿宇星" 3-"陈飞" 4-"彭兴旭" 5-"李慧鹏" 6-"孟欣" 7-"鲜惠珊" 8-"陈劲" 9-"居彭阳" 10-"李嘉韵" 11-"石闹闹" 12-"肖风" 13-"刘晓江" [root@node110 ~]# [root@node110 ~]#
mysql> select * from teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+-----------+ 13 rows in set (0.00 sec) mysql> mysql> mysql> SELECT * INTO DUMPFILE '/yinzhengjie/backup/teacher.bak' FROM teacher WHERE course_id = 1 LIMIT 1; Query OK, 1 row affected (0.00 sec) mysql> quit Bye [root@node110 ~]# [root@node110 ~]# cat /yinzhengjie/backup/teacher.bak 2周杰伦1[root@node110 ~]# [root@node110 ~]#
18>.FOR UPDATE关键词
代表将查询的数据行加上写锁，直到本事物提交为止。
19>.LOCK IN SHARE MODE关键词
代表将查询的数据行加上读锁，则其他的链接可以读相同的数据但无法修改加锁但数据。
20>.ALL/DISTINCT关键词
代表是否将查询结果中完全重复的行都查询出来，ALL默认值代表都查询出来，指定DISTINCT代表重复行只显示一次。
mysql> SELECT * FROM scores; +------+---------+--------+ | id | subject | scores | +------+---------+--------+ | 1 | english | 88 | | 1 | chinese | 86 | | 1 | math | 90 | | 2 | english | 95 | | 2 | chinese | 84 | +------+---------+--------+ 5 rows in set (0.00 sec) mysql> mysql> SELECT COUNT(*),COUNT(ALL id),COUNT(DISTINCT id) FROM scores; +----------+---------------+--------------------+ | COUNT(*) | COUNT(ALL id) | COUNT(DISTINCT id) | +----------+---------------+--------------------+ | 5 | 5 | 2 | +----------+---------------+--------------------+ 1 row in set (0.00 sec) mysql>
21>.HIGH_PRIORITY
代表赋予独裁做较高的操作优先级
22>.MAX_STATEMENT_TIME = N子句
代表设置语句执行超过时间（ms，毫秒级别）。
23>.STRAIGHT_JOIN关键词
代表强制优化器在表链接操作是按照语句中FROM子句中的表顺序执行。
24>.SQL_BIG_RESULT/SQL_SMALL_RESULT通常是和GROUP BY/DISTINCT一起使用
其作用是事先告诉优化器查询结果是大还是小，以便优化器事先准备好将查询结果存放在磁盘临时表或者快速临时表中以便后续操作。
25>.SQL_BUFFER_RESULT强制将查询结果存入临时表中
26>.SQL_CALC_FOUND_ROWS关键词
代表要求查询结果的同时计算结果的行数，以便后续通过SELECT FOUND_ROWS()直接获取行数。
27>.SQL_CACHE/SQL_NO_CACHE
代表是否直接从QUERY CACHE中获取查询结果。
三.SELECT语句中表链接
当SELECT语句中涉及到多表查询时，就会用到表连接操作
1>.在MySQL中，JOIN/INNER JOIN/CROSS JOIN三者到意思是一样的
mysql> SELECT * FROM student INNER JOIN teacher ON student.id = teacher.id; +----+-----------+------------+----+-----------+-----------+ | id | name | teacher_id | id | name | course_id | +----+-----------+------------+----+-----------+-----------+ | 1 | 尹正杰 | 1 | 1 | 谢霆锋 | 11 | | 2 | 耿宇星 | 2 | 2 | 周杰伦 | 1 | | 3 | 陈飞 | 3 | 3 | 蔡依林 | 13 | | 4 | 彭兴旭 | 4 | 4 | 杨幂 | 2 | | 5 | 李慧鹏 | 5 | 5 | 胡歌 | 12 | | 6 | 孟欣 | 6 | 6 | 刘德华 | 3 | | 7 | 鲜惠珊 | 7 | 7 | 张学友 | 10 | | 8 | 陈劲 | 8 | 8 | 郭德纲 | 4 | | 9 | 居彭阳 | 9 | 9 | 张杰 | 9 | | 10 | 李嘉韵 | 10 | 10 | 苍老师 | 5 | | 11 | 石闹闹 | 11 | 11 | 谢娜 | 8 | | 12 | 肖风 | 12 | 12 | 赵薇 | 6 | | 13 | 刘晓江 | 13 | 13 | 张卫健 | 7 | +----+-----------+------------+----+-----------+-----------+ 13 rows in set (0.00 sec) mysql>
mysql> SELECT * FROM student LEFT JOIN teacher ON student.id = teacher.id; +----+-----------+------------+------+-----------+-----------+ | id | name | teacher_id | id | name | course_id | +----+-----------+------------+------+-----------+-----------+ | 1 | 尹正杰 | 1 | 1 | 谢霆锋 | 11 | | 2 | 耿宇星 | 2 | 2 | 周杰伦 | 1 | | 3 | 陈飞 | 3 | 3 | 蔡依林 | 13 | | 4 | 彭兴旭 | 4 | 4 | 杨幂 | 2 | | 5 | 李慧鹏 | 5 | 5 | 胡歌 | 12 | | 6 | 孟欣 | 6 | 6 | 刘德华 | 3 | | 7 | 鲜惠珊 | 7 | 7 | 张学友 | 10 | | 8 | 陈劲 | 8 | 8 | 郭德纲 | 4 | | 9 | 居彭阳 | 9 | 9 | 张杰 | 9 | | 10 | 李嘉韵 | 10 | 10 | 苍老师 | 5 | | 11 | 石闹闹 | 11 | 11 | 谢娜 | 8 | | 12 | 肖风 | 12 | 12 | 赵薇 | 6 | | 13 | 刘晓江 | 13 | 13 | 张卫健 | 7 | +----+-----------+------------+------+-----------+-----------+ 13 rows in set (0.00 sec) mysql>
mysql> SELECT * FROM student LEFT JOIN teacher USING(id); +----+-----------+------------+-----------+-----------+ | id | name | teacher_id | name | course_id | +----+-----------+------------+-----------+-----------+ | 1 | 尹正杰 | 1 | 谢霆锋 | 11 | | 2 | 耿宇星 | 2 | 周杰伦 | 1 | | 3 | 陈飞 | 3 | 蔡依林 | 13 | | 4 | 彭兴旭 | 4 | 杨幂 | 2 | | 5 | 李慧鹏 | 5 | 胡歌 | 12 | | 6 | 孟欣 | 6 | 刘德华 | 3 | | 7 | 鲜惠珊 | 7 | 张学友 | 10 | | 8 | 陈劲 | 8 | 郭德纲 | 4 | | 9 | 居彭阳 | 9 | 张杰 | 9 | | 10 | 李嘉韵 | 10 | 苍老师 | 5 | | 11 | 石闹闹 | 11 | 谢娜 | 8 | | 12 | 肖风 | 12 | 赵薇 | 6 | | 13 | 刘晓江 | 13 | 张卫健 | 7 | +----+-----------+------------+-----------+-----------+ 13 rows in set (0.00 sec) mysql>
mysql> SELECT * FROM student LEFT JOIN teacher ON student.id = teacher.id LEFT JOIN course ON student.id = course.id; +----+-----------+------------+------+-----------+-----------+------+-----------+ | id | name | teacher_id | id | name | course_id | id | name | +----+-----------+------------+------+-----------+-----------+------+-----------+ | 1 | 尹正杰 | 1 | 1 | 谢霆锋 | 11 | 1 | 语文 | | 2 | 耿宇星 | 2 | 2 | 周杰伦 | 1 | 2 | 数学 | | 3 | 陈飞 | 3 | 3 | 蔡依林 | 13 | 3 | 英语 | | 4 | 彭兴旭 | 4 | 4 | 杨幂 | 2 | 4 | 体育 | | 5 | 李慧鹏 | 5 | 5 | 胡歌 | 12 | 5 | 美术 | | 6 | 孟欣 | 6 | 6 | 刘德华 | 3 | 6 | 音乐 | | 7 | 鲜惠珊 | 7 | 7 | 张学友 | 10 | 7 | 物理 | | 8 | 陈劲 | 8 | 8 | 郭德纲 | 4 | 8 | 化学 | | 9 | 居彭阳 | 9 | 9 | 张杰 | 9 | 9 | 生物 | | 10 | 李嘉韵 | 10 | 10 | 苍老师 | 5 | 10 | 历史 | | 11 | 石闹闹 | 11 | 11 | 谢娜 | 8 | 11 | 政治 | | 12 | 肖风 | 12 | 12 | 赵薇 | 6 | 12 | 地理 | | 13 | 刘晓江 | 13 | 13 | 张卫健 | 7 | 13 | 计算机 | +----+-----------+------------+------+-----------+-----------+------+-----------+ 13 rows in set (0.00 sec) mysql> mysql>
2>.JOIN语句中表别名的用法
mysql> SELECT t1.name,t2.name FROM student AS t1 INNER JOIN teacher AS t2 ON t1.id = t2.id; +-----------+-----------+ | name | name | +-----------+-----------+ | 尹正杰 | 谢霆锋 | | 耿宇星 | 周杰伦 | | 陈飞 | 蔡依林 | | 彭兴旭 | 杨幂 | | 李慧鹏 | 胡歌 | | 孟欣 | 刘德华 | | 鲜惠珊 | 张学友 | | 陈劲 | 郭德纲 | | 居彭阳 | 张杰 | | 李嘉韵 | 苍老师 | | 石闹闹 | 谢娜 | | 肖风 | 赵薇 | | 刘晓江 | 张卫健 | +-----------+-----------+ 13 rows in set (0.00 sec) mysql>
mysql> SELECT t1.name,t2.name FROM student t1 INNER JOIN teacher t2 ON t1.id = t2.id; +-----------+-----------+ | name | name | +-----------+-----------+ | 尹正杰 | 谢霆锋 | | 耿宇星 | 周杰伦 | | 陈飞 | 蔡依林 | | 彭兴旭 | 杨幂 | | 李慧鹏 | 胡歌 | | 孟欣 | 刘德华 | | 鲜惠珊 | 张学友 | | 陈劲 | 郭德纲 | | 居彭阳 | 张杰 | | 李嘉韵 | 苍老师 | | 石闹闹 | 谢娜 | | 肖风 | 赵薇 | | 刘晓江 | 张卫健 | +-----------+-----------+ 13 rows in set (0.00 sec) mysql>
3>.FROM子句后面还可以跟子查询，但子查询必须带别名
mysql> SELECT * FROM(SELECT id,name FROM student) AS t1; +----+-----------+ | id | name | +----+-----------+ | 1 | 尹正杰 | | 2 | 耿宇星 | | 3 | 陈飞 | | 4 | 彭兴旭 | | 5 | 李慧鹏 | | 6 | 孟欣 | | 7 | 鲜惠珊 | | 8 | 陈劲 | | 9 | 居彭阳 | | 10 | 李嘉韵 | | 11 | 石闹闹 | | 12 | 肖风 | | 13 | 刘晓江 | +----+-----------+ 13 rows in set (0.00 sec) mysql> mysql>
4>.当INNER JOIN或者表之间用逗号隔开，且没有表之间的关联字段，则代表结果是两者的笛卡尔积。
mysql> SELECT * FROM teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+-----------+ 13 rows in set (0.00 sec) mysql> mysql> SELECT * FROM student; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql> mysql> SELECT * FROM teacher,student; +----+-----------+-----------+----+-----------+------------+ | id | name | course_id | id | name | teacher_id | +----+-----------+-----------+----+-----------+------------+ | 1 | 谢霆锋 | 11 | 1 | 尹正杰 | 1 | | 2 | 周杰伦 | 1 | 1 | 尹正杰 | 1 | | 3 | 蔡依林 | 13 | 1 | 尹正杰 | 1 | | 4 | 杨幂 | 2 | 1 | 尹正杰 | 1 | | 5 | 胡歌 | 12 | 1 | 尹正杰 | 1 | | 6 | 刘德华 | 3 | 1 | 尹正杰 | 1 | | 7 | 张学友 | 10 | 1 | 尹正杰 | 1 | | 8 | 郭德纲 | 4 | 1 | 尹正杰 | 1 | | 9 | 张杰 | 9 | 1 | 尹正杰 | 1 | | 10 | 苍老师 | 5 | 1 | 尹正杰 | 1 | | 11 | 谢娜 | 8 | 1 | 尹正杰 | 1 | | 12 | 赵薇 | 6 | 1 | 尹正杰 | 1 | | 13 | 张卫健 | 7 | 1 | 尹正杰 | 1 | | 1 | 谢霆锋 | 11 | 2 | 耿宇星 | 2 | | 2 | 周杰伦 | 1 | 2 | 耿宇星 | 2 | | 3 | 蔡依林 | 13 | 2 | 耿宇星 | 2 | | 4 | 杨幂 | 2 | 2 | 耿宇星 | 2 | | 5 | 胡歌 | 12 | 2 | 耿宇星 | 2 | | 6 | 刘德华 | 3 | 2 | 耿宇星 | 2 | | 7 | 张学友 | 10 | 2 | 耿宇星 | 2 | | 8 | 郭德纲 | 4 | 2 | 耿宇星 | 2 | | 9 | 张杰 | 9 | 2 | 耿宇星 | 2 | | 10 | 苍老师 | 5 | 2 | 耿宇星 | 2 | | 11 | 谢娜 | 8 | 2 | 耿宇星 | 2 | | 12 | 赵薇 | 6 | 2 | 耿宇星 | 2 | | 13 | 张卫健 | 7 | 2 | 耿宇星 | 2 | | 1 | 谢霆锋 | 11 | 3 | 陈飞 | 3 | | 2 | 周杰伦 | 1 | 3 | 陈飞 | 3 | | 3 | 蔡依林 | 13 | 3 | 陈飞 | 3 | | 4 | 杨幂 | 2 | 3 | 陈飞 | 3 | | 5 | 胡歌 | 12 | 3 | 陈飞 | 3 | | 6 | 刘德华 | 3 | 3 | 陈飞 | 3 | | 7 | 张学友 | 10 | 3 | 陈飞 | 3 | | 8 | 郭德纲 | 4 | 3 | 陈飞 | 3 | | 9 | 张杰 | 9 | 3 | 陈飞 | 3 | | 10 | 苍老师 | 5 | 3 | 陈飞 | 3 | | 11 | 谢娜 | 8 | 3 | 陈飞 | 3 | | 12 | 赵薇 | 6 | 3 | 陈飞 | 3 | | 13 | 张卫健 | 7 | 3 | 陈飞 | 3 | | 1 | 谢霆锋 | 11 | 4 | 彭兴旭 | 4 | | 2 | 周杰伦 | 1 | 4 | 彭兴旭 | 4 | | 3 | 蔡依林 | 13 | 4 | 彭兴旭 | 4 | | 4 | 杨幂 | 2 | 4 | 彭兴旭 | 4 | | 5 | 胡歌 | 12 | 4 | 彭兴旭 | 4 | | 6 | 刘德华 | 3 | 4 | 彭兴旭 | 4 | | 7 | 张学友 | 10 | 4 | 彭兴旭 | 4 | | 8 | 郭德纲 | 4 | 4 | 彭兴旭 | 4 | | 9 | 张杰 | 9 | 4 | 彭兴旭 | 4 | | 10 | 苍老师 | 5 | 4 | 彭兴旭 | 4 | | 11 | 谢娜 | 8 | 4 | 彭兴旭 | 4 | | 12 | 赵薇 | 6 | 4 | 彭兴旭 | 4 | | 13 | 张卫健 | 7 | 4 | 彭兴旭 | 4 | | 1 | 谢霆锋 | 11 | 5 | 李慧鹏 | 5 | | 2 | 周杰伦 | 1 | 5 | 李慧鹏 | 5 | | 3 | 蔡依林 | 13 | 5 | 李慧鹏 | 5 | | 4 | 杨幂 | 2 | 5 | 李慧鹏 | 5 | | 5 | 胡歌 | 12 | 5 | 李慧鹏 | 5 | | 6 | 刘德华 | 3 | 5 | 李慧鹏 | 5 | | 7 | 张学友 | 10 | 5 | 李慧鹏 | 5 | | 8 | 郭德纲 | 4 | 5 | 李慧鹏 | 5 | | 9 | 张杰 | 9 | 5 | 李慧鹏 | 5 | | 10 | 苍老师 | 5 | 5 | 李慧鹏 | 5 | | 11 | 谢娜 | 8 | 5 | 李慧鹏 | 5 | | 12 | 赵薇 | 6 | 5 | 李慧鹏 | 5 | | 13 | 张卫健 | 7 | 5 | 李慧鹏 | 5 | | 1 | 谢霆锋 | 11 | 6 | 孟欣 | 6 | | 2 | 周杰伦 | 1 | 6 | 孟欣 | 6 | | 3 | 蔡依林 | 13 | 6 | 孟欣 | 6 | | 4 | 杨幂 | 2 | 6 | 孟欣 | 6 | | 5 | 胡歌 | 12 | 6 | 孟欣 | 6 | | 6 | 刘德华 | 3 | 6 | 孟欣 | 6 | | 7 | 张学友 | 10 | 6 | 孟欣 | 6 | | 8 | 郭德纲 | 4 | 6 | 孟欣 | 6 | | 9 | 张杰 | 9 | 6 | 孟欣 | 6 | | 10 | 苍老师 | 5 | 6 | 孟欣 | 6 | | 11 | 谢娜 | 8 | 6 | 孟欣 | 6 | | 12 | 赵薇 | 6 | 6 | 孟欣 | 6 | | 13 | 张卫健 | 7 | 6 | 孟欣 | 6 | | 1 | 谢霆锋 | 11 | 7 | 鲜惠珊 | 7 | | 2 | 周杰伦 | 1 | 7 | 鲜惠珊 | 7 | | 3 | 蔡依林 | 13 | 7 | 鲜惠珊 | 7 | | 4 | 杨幂 | 2 | 7 | 鲜惠珊 | 7 | | 5 | 胡歌 | 12 | 7 | 鲜惠珊 | 7 | | 6 | 刘德华 | 3 | 7 | 鲜惠珊 | 7 | | 7 | 张学友 | 10 | 7 | 鲜惠珊 | 7 | | 8 | 郭德纲 | 4 | 7 | 鲜惠珊 | 7 | | 9 | 张杰 | 9 | 7 | 鲜惠珊 | 7 | | 10 | 苍老师 | 5 | 7 | 鲜惠珊 | 7 | | 11 | 谢娜 | 8 | 7 | 鲜惠珊 | 7 | | 12 | 赵薇 | 6 | 7 | 鲜惠珊 | 7 | | 13 | 张卫健 | 7 | 7 | 鲜惠珊 | 7 | | 1 | 谢霆锋 | 11 | 8 | 陈劲 | 8 | | 2 | 周杰伦 | 1 | 8 | 陈劲 | 8 | | 3 | 蔡依林 | 13 | 8 | 陈劲 | 8 | | 4 | 杨幂 | 2 | 8 | 陈劲 | 8 | | 5 | 胡歌 | 12 | 8 | 陈劲 | 8 | | 6 | 刘德华 | 3 | 8 | 陈劲 | 8 | | 7 | 张学友 | 10 | 8 | 陈劲 | 8 | | 8 | 郭德纲 | 4 | 8 | 陈劲 | 8 | | 9 | 张杰 | 9 | 8 | 陈劲 | 8 | | 10 | 苍老师 | 5 | 8 | 陈劲 | 8 | | 11 | 谢娜 | 8 | 8 | 陈劲 | 8 | | 12 | 赵薇 | 6 | 8 | 陈劲 | 8 | | 13 | 张卫健 | 7 | 8 | 陈劲 | 8 | | 1 | 谢霆锋 | 11 | 9 | 居彭阳 | 9 | | 2 | 周杰伦 | 1 | 9 | 居彭阳 | 9 | | 3 | 蔡依林 | 13 | 9 | 居彭阳 | 9 | | 4 | 杨幂 | 2 | 9 | 居彭阳 | 9 | | 5 | 胡歌 | 12 | 9 | 居彭阳 | 9 | | 6 | 刘德华 | 3 | 9 | 居彭阳 | 9 | | 7 | 张学友 | 10 | 9 | 居彭阳 | 9 | | 8 | 郭德纲 | 4 | 9 | 居彭阳 | 9 | | 9 | 张杰 | 9 | 9 | 居彭阳 | 9 | | 10 | 苍老师 | 5 | 9 | 居彭阳 | 9 | | 11 | 谢娜 | 8 | 9 | 居彭阳 | 9 | | 12 | 赵薇 | 6 | 9 | 居彭阳 | 9 | | 13 | 张卫健 | 7 | 9 | 居彭阳 | 9 | | 1 | 谢霆锋 | 11 | 10 | 李嘉韵 | 10 | | 2 | 周杰伦 | 1 | 10 | 李嘉韵 | 10 | | 3 | 蔡依林 | 13 | 10 | 李嘉韵 | 10 | | 4 | 杨幂 | 2 | 10 | 李嘉韵 | 10 | | 5 | 胡歌 | 12 | 10 | 李嘉韵 | 10 | | 6 | 刘德华 | 3 | 10 | 李嘉韵 | 10 | | 7 | 张学友 | 10 | 10 | 李嘉韵 | 10 | | 8 | 郭德纲 | 4 | 10 | 李嘉韵 | 10 | | 9 | 张杰 | 9 | 10 | 李嘉韵 | 10 | | 10 | 苍老师 | 5 | 10 | 李嘉韵 | 10 | | 11 | 谢娜 | 8 | 10 | 李嘉韵 | 10 | | 12 | 赵薇 | 6 | 10 | 李嘉韵 | 10 | | 13 | 张卫健 | 7 | 10 | 李嘉韵 | 10 | | 1 | 谢霆锋 | 11 | 11 | 石闹闹 | 11 | | 2 | 周杰伦 | 1 | 11 | 石闹闹 | 11 | | 3 | 蔡依林 | 13 | 11 | 石闹闹 | 11 | | 4 | 杨幂 | 2 | 11 | 石闹闹 | 11 | | 5 | 胡歌 | 12 | 11 | 石闹闹 | 11 | | 6 | 刘德华 | 3 | 11 | 石闹闹 | 11 | | 7 | 张学友 | 10 | 11 | 石闹闹 | 11 | | 8 | 郭德纲 | 4 | 11 | 石闹闹 | 11 | | 9 | 张杰 | 9 | 11 | 石闹闹 | 11 | | 10 | 苍老师 | 5 | 11 | 石闹闹 | 11 | | 11 | 谢娜 | 8 | 11 | 石闹闹 | 11 | | 12 | 赵薇 | 6 | 11 | 石闹闹 | 11 | | 13 | 张卫健 | 7 | 11 | 石闹闹 | 11 | | 1 | 谢霆锋 | 11 | 12 | 肖风 | 12 | | 2 | 周杰伦 | 1 | 12 | 肖风 | 12 | | 3 | 蔡依林 | 13 | 12 | 肖风 | 12 | | 4 | 杨幂 | 2 | 12 | 肖风 | 12 | | 5 | 胡歌 | 12 | 12 | 肖风 | 12 | | 6 | 刘德华 | 3 | 12 | 肖风 | 12 | | 7 | 张学友 | 10 | 12 | 肖风 | 12 | | 8 | 郭德纲 | 4 | 12 | 肖风 | 12 | | 9 | 张杰 | 9 | 12 | 肖风 | 12 | | 10 | 苍老师 | 5 | 12 | 肖风 | 12 | | 11 | 谢娜 | 8 | 12 | 肖风 | 12 | | 12 | 赵薇 | 6 | 12 | 肖风 | 12 | | 13 | 张卫健 | 7 | 12 | 肖风 | 12 | | 1 | 谢霆锋 | 11 | 13 | 刘晓江 | 13 | | 2 | 周杰伦 | 1 | 13 | 刘晓江 | 13 | | 3 | 蔡依林 | 13 | 13 | 刘晓江 | 13 | | 4 | 杨幂 | 2 | 13 | 刘晓江 | 13 | | 5 | 胡歌 | 12 | 13 | 刘晓江 | 13 | | 6 | 刘德华 | 3 | 13 | 刘晓江 | 13 | | 7 | 张学友 | 10 | 13 | 刘晓江 | 13 | | 8 | 郭德纲 | 4 | 13 | 刘晓江 | 13 | | 9 | 张杰 | 9 | 13 | 刘晓江 | 13 | | 10 | 苍老师 | 5 | 13 | 刘晓江 | 13 | | 11 | 谢娜 | 8 | 13 | 刘晓江 | 13 | | 12 | 赵薇 | 6 | 13 | 刘晓江 | 13 | | 13 | 张卫健 | 7 | 13 | 刘晓江 | 13 | +----+-----------+-----------+----+-----------+------------+ 169 rows in set (0.00 sec) mysql>
5>.CONDITIONAL_EXPR 子句一般代表指定两个表之间的关联条件，而WHERE条件中指定查询结果的筛选条件。
6>.STRAIGHT_JOIN和JOIN的用法大致相同，唯一不同是确保左表是先被读取的，以保证优化器的读取顺序。
四.SELECT语句中的UNION
UNION用来将多个SELECT语句的执行结果合并成一个结果。
1>.第一个SELECT语句的COLUMN_NAME会被当做最后查询结果的列名，接下来的每一个SELECT语句所一一对应的列应该和第一个语句的列类型最好保持一致。
2>.默认情况下UNION语句会把最终结果中的重复行去掉，这和增加DISTINCT这个关键词的作用一样，如果使用UNION ALL则代表最终结果中的重复行保留。
mysql> SELECT * FROM student; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql> mysql> SELECT * FROM teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+-----------+ 13 rows in set (0.00 sec) mysql> mysql> SELECT * FROM student UNION SELECT * FROM teacher; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+------------+ 26 rows in set (0.01 sec) mysql>
mysql> SELECT * FROM student UNION ALL SELECT * FROM teacher; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+------------+ 26 rows in set (0.00 sec) mysql> mysql>
3>.如果相对UNION语句的最后结果做排序或者LIMIT限制，则需要将每个SELECT语句用括号括起来，把ORDER BY或LIMIT语句放在最后
mysql> SELECT * FROM student UNION ALL SELECT * FROM teacher; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+------------+ 26 rows in set (0.00 sec) mysql> SELECT * FROM student UNION ALL SELECT * FROM teacher ORDER BY id LIMIT 10; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 谢霆锋 | 11 | | 1 | 尹正杰 | 1 | | 2 | 周杰伦 | 1 | | 2 | 耿宇星 | 2 | | 3 | 蔡依林 | 13 | | 3 | 陈飞 | 3 | | 4 | 杨幂 | 2 | | 4 | 彭兴旭 | 4 | | 5 | 胡歌 | 12 | | 5 | 李慧鹏 | 5 | +----+-----------+------------+ 10 rows in set (0.00 sec) mysql>
五.CREATE VIEW语句
CREATE VIEW语句是指将某个查询数据的定义保留下来，以便随时调用。换句话说，就是视图保留的是SQL而不是具体的数据哟～
1>.VIEW本身不存储查询结果，它只是一个定义
mysql> ? CREATE VIEW Name: 'CREATE VIEW' Description: Syntax: CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] The CREATE VIEW statement creates a new view, or replaces an existing view if the OR REPLACE clause is given. If the view does not exist, CREATE OR REPLACE VIEW is the same as CREATE VIEW. If the view does exist, CREATE OR REPLACE VIEW replaces it. For information about restrictions on view use, see http://dev.mysql.com/doc/refman/8.0/en/view-restrictions.html. The select_statement is a SELECT statement that provides the definition of the view. (Selecting from the view selects, in effect, using the SELECT statement.) The select_statement can select from base tables or other views. The view definition is "frozen" at creation time and is not affected by subsequent changes to the definitions of the underlying tables. For example, if a view is defined as SELECT * on a table, new columns added to the table later do not become part of the view, and columns dropped from the table will result in an error when selecting from the view. The ALGORITHM clause affects how MySQL processes the view. The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at view invocation time. The WITH CHECK OPTION clause can be given to constrain inserts or updates to rows in tables referenced by the view. These clauses are described later in this section. The CREATE VIEW statement requires the CREATE VIEW privilege for the view, and some privilege for each column selected by the SELECT statement. For columns used elsewhere in the SELECT statement, you must have the SELECT privilege. If the OR REPLACE clause is present, you must also have the DROP privilege for the view. CREATE VIEW might also require the SET_USER_ID or SUPER privilege, depending on the DEFINER value, as described later in this section. When a view is referenced, privilege checking occurs as described later in this section. A view belongs to a database. By default, a new view is created in the default database. To create the view explicitly in a given database, use db_name.view_name syntax to qualify the view name with the database name: CREATE VIEW test.v AS SELECT * FROM t; Unqualified table or view names in the SELECT statement are also interpreted with respect to the default database. A view can refer to tables or views in other databases by qualifying the table or view name with the appropriate database name. Within a database, base tables and views share the same namespace, so a base table and a view cannot have the same name. Columns retrieved by the SELECT statement can be simple references to table columns, or expressions that use functions, constant values, operators, and so forth. A view must have unique column names with no duplicates, just like a base table. By default, the names of the columns retrieved by the SELECT statement are used for the view column names. To define explicit names for the view columns, specify the optional column_list clause as a list of comma-separated identifiers. The number of names in column_list must be the same as the number of columns retrieved by the SELECT statement. A view can be created from many kinds of SELECT statements. It can refer to base tables or other views. It can use joins, UNION, and subqueries. The SELECT need not even refer to any tables: CREATE VIEW v_today (today) AS SELECT CURRENT_DATE; The following example defines a view that selects two columns from another table as well as an expression calculated from those columns: mysql> CREATE TABLE t (qty INT, price INT); mysql> INSERT INTO t VALUES(3, 50); mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t; mysql> SELECT * FROM v; +------+-------+-------+ | qty | price | value | +------+-------+-------+ | 3 | 50 | 150 | +------+-------+-------+ A view definition is subject to the following restrictions: o The SELECT statement cannot refer to system variables or user-defined variables. o Within a stored program, the SELECT statement cannot refer to program parameters or local variables. o The SELECT statement cannot refer to prepared statement parameters. o Any table or view referred to in the definition must exist. If, after the view has been created, a table or view that the definition refers to is dropped, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement. o The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view. o You cannot associate a trigger with a view. o Aliases for column names in the SELECT statement are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters). ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY. For other options or clauses in the definition, they are added to the options or clauses of the statement that references the view, but the effect is undefined. For example, if a view definition includes a LIMIT clause, and you select from the view using a statement that has its own LIMIT clause, it is undefined which limit applies. This same principle applies to options such as ALL, DISTINCT, or SQL_SMALL_RESULT that follow the SELECT keyword, and to clauses such as INTO, FOR UPDATE, FOR SHARE, LOCK IN SHARE MODE, and PROCEDURE. The results obtained from a view may be affected if you change the query processing environment by changing system variables: mysql> CREATE VIEW v (mycol) AS SELECT 'abc'; Query OK, 0 rows affected (0.01 sec) mysql> SET sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> SELECT "mycol" FROM v; +-------+ | mycol | +-------+ | mycol | +-------+ 1 row in set (0.01 sec) mysql> SET sql_mode = 'ANSI_QUOTES'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT "mycol" FROM v; +-------+ | mycol | +-------+ | abc | +-------+ 1 row in set (0.00 sec) The DEFINER and SQL SECURITY clauses determine which MySQL account to use when checking access privileges for the view when a statement is executed that references the view. The valid SQL SECURITY characteristic values are DEFINER (the default) and INVOKER. These indicate that the required privileges must be held by the user who defined or invoked the view, respectively. If a user value is given for the DEFINER clause, it should be a MySQL account specified as 'user_name'@'host_name', CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE VIEW statement. This is the same as specifying DEFINER = CURRENT_USER explicitly. If the DEFINER clause is present, these rules determine the valid DEFINER user values: o If you do not have the SET_USER_ID or SUPER privilege, the only valid user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account. o If you have the SET_USER_ID or SUPER privilege, you can specify any syntactically valid account name. If the account does not exist, a warning is generated. o Although it is possible to create a view with a nonexistent DEFINER account, an error occurs when the view is referenced if the SQL SECURITY value is DEFINER but the definer account does not exist. For more information about view security, see http://dev.mysql.com/doc/refman/8.0/en/stored-programs-security.html. Within a view definition, CURRENT_USER returns the view's DEFINER value by default. For views defined with the SQL SECURITY INVOKER characteristic, CURRENT_USER returns the account for the view's invoker. For information about user auditing within views, see http://dev.mysql.com/doc/refman/8.0/en/account-activity-auditing.html. Within a stored routine that is defined with the SQL SECURITY DEFINER characteristic, CURRENT_USER returns the routine's DEFINER value. This also affects a view defined within such a routine, if the view definition contains a DEFINER value of CURRENT_USER. MySQL checks view privileges like this: o At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. For example, if the view definition refers to table columns, the creator must have some privilege for each column in the select list of the definition, and the SELECT privilege for each column used elsewhere in the definition. If the definition refers to a stored function, only the privileges needed to invoke the function can be checked. The privileges required at function invocation time can be checked only as it executes: For different invocations, different execution paths within the function might be taken. o The user who references a view must have appropriate privileges to access it (SELECT to select from it, INSERT to insert into it, and so forth.) o When a view has been referenced, privileges for objects accessed by the view are checked against the privileges held by the view DEFINER account or invoker, depending on whether the SQL SECURITY characteristic is DEFINER or INVOKER, respectively. o If reference to a view causes execution of a stored function, privilege checking for statements executed within the function depend on whether the function SQL SECURITY characteristic is DEFINER or INVOKER. If the security characteristic is DEFINER, the function runs with the privileges of the DEFINER account. If the characteristic is INVOKER, the function runs with the privileges determined by the view's SQL SECURITY characteristic. Example: A view might depend on a stored function, and that function might invoke other stored routines. For example, the following view invokes a stored function f(): CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name); Suppose that f() contains a statement such as this: IF name IS NULL then CALL p1(); ELSE CALL p2(); END IF; The privileges required for executing statements within f() need to be checked when f() executes. This might mean that privileges are needed for p1() or p2(), depending on the execution path within f(). Those privileges must be checked at runtime, and the user who must possess the privileges is determined by the SQL SECURITY values of the view v and the function f(). The DEFINER and SQL SECURITY clauses for views are extensions to standard SQL. In standard SQL, views are handled using the rules for SQL SECURITY DEFINER. The standard says that the definer of the view, which is the same as the owner of the view's schema, gets applicable privileges on the view (for example, SELECT) and may grant them. MySQL has no concept of a schema "owner", so MySQL adds a clause to identify the definer. The DEFINER clause is an extension where the intent is to have what the standard has; that is, a permanent record of who defined the view. This is why the default DEFINER value is the account of the view creator. The optional ALGORITHM clause is a MySQL extension to standard SQL. It affects how MySQL processes the view. ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED. For more information, see http://dev.mysql.com/doc/refman/8.0/en/view-algorithms.html, as well as http://dev.mysql.com/doc/refman/8.0/en/derived-table-optimization.html. Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable. A generated column in a view is considered updatable because it is possible to assign to it. However, if such a column is updated explicitly, the only permitted value is DEFAULT. For information about generated columns, see http://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.h tml. The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true. In a WITH CHECK OPTION clause for an updatable view, the LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. The LOCAL keyword restricts the CHECK OPTION only to the view being defined. CASCADED causes the checks for underlying views to be evaluated as well. When neither keyword is given, the default is CASCADED. For more information about updatable views and the WITH CHECK OPTION clause, see http://dev.mysql.com/doc/refman/8.0/en/view-updatability.html, and http://dev.mysql.com/doc/refman/8.0/en/view-check-option.html. URL: http://dev.mysql.com/doc/refman/8.0/en/create-view.html mysql>
2>.OR REPLACE关键词表示当创建当视图已经存在时，执行替换命令。换句话说，就是已经存在的视图内容可能会被替换！
mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | course | | scores | | student | | student_name_view | | student_view | | teacher | +-----------------------+ 6 rows in set (0.00 sec) mysql> mysql> SELECT * FROM student_name_view; +-----------+ | view_name | +-----------+ | 尹正杰 | | 耿宇星 | | 陈飞 | | 彭兴旭 | | 李慧鹏 | | 孟欣 | | 鲜惠珊 | | 陈劲 | | 居彭阳 | | 李嘉韵 | | 石闹闹 | | 肖风 | | 刘晓江 | +-----------+ 13 rows in set (0.00 sec) mysql> mysql> CREATE OR REPLACE VIEW student_name_view AS SELECT * FROM student; Query OK, 0 rows affected (0.01 sec) mysql> mysql> SELECT * FROM student_name_view; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql>
3>.SELECT_STATEMENT子句则是创建视图当SELECT语句，可以是从表中查询数据，也可以从其他视图中查询数据。
mysql> SELECT * FROM student; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql> mysql> CREATE VIEW student_name_view(view_name) AS SELECT name FROM student; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT * FROM student_name_view; +-----------+ | view_name | +-----------+ | 尹正杰 | | 耿宇星 | | 陈飞 | | 彭兴旭 | | 李慧鹏 | | 孟欣 | | 鲜惠珊 | | 陈劲 | | 居彭阳 | | 李嘉韵 | | 石闹闹 | | 肖风 | | 刘晓江 | +-----------+ 13 rows in set (0.00 sec) mysql>
4>.当视图被创建之后，则其定义就已经固定不会在改变，比如一个视图是由“SELECT *” 创建的，则后续对表增加对字段不会成为视图对一部分，而后续对表删除对字段则会导致查询视图失败。
mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | course | | scores | | student | | student_name_view | | student_view | | teacher | +-----------------------+ 6 rows in set (0.00 sec) mysql> mysql> CREATE VIEW view_student AS SELECT * FROM student; #这里创建一个视图 Query OK, 0 rows affected (0.01 sec) mysql> mysql> ALTER TABLE student ADD remarks VARCHAR(100); #此处我们添加一个remarks字段。 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT * FROM student; #字段添加成功，可以在原表中看到 +----+-----------+------------+---------+ | id | name | teacher_id | remarks | +----+-----------+------------+---------+ | 1 | 尹正杰 | 1 | NULL | | 2 | 耿宇星 | 2 | NULL | | 3 | 陈飞 | 3 | NULL | | 4 | 彭兴旭 | 4 | NULL | | 5 | 李慧鹏 | 5 | NULL | | 6 | 孟欣 | 6 | NULL | | 7 | 鲜惠珊 | 7 | NULL | | 8 | 陈劲 | 8 | NULL | | 9 | 居彭阳 | 9 | NULL | | 10 | 李嘉韵 | 10 | NULL | | 11 | 石闹闹 | 11 | NULL | | 12 | 肖风 | 12 | NULL | | 13 | 刘晓江 | 13 | NULL | +----+-----------+------------+---------+ 13 rows in set (0.00 sec) mysql> mysql> SELECT * FROM view_student; #很显然，在视图中看不到我们插入的字段 +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql> mysql>
mysql> SELECT * FROM student; +----+-----------+------------+---------+ | id | name | teacher_id | remarks | +----+-----------+------------+---------+ | 1 | 尹正杰 | 1 | NULL | | 2 | 耿宇星 | 2 | NULL | | 3 | 陈飞 | 3 | NULL | | 4 | 彭兴旭 | 4 | NULL | | 5 | 李慧鹏 | 5 | NULL | | 6 | 孟欣 | 6 | NULL | | 7 | 鲜惠珊 | 7 | NULL | | 8 | 陈劲 | 8 | NULL | | 9 | 居彭阳 | 9 | NULL | | 10 | 李嘉韵 | 10 | NULL | | 11 | 石闹闹 | 11 | NULL | | 12 | 肖风 | 12 | NULL | | 13 | 刘晓江 | 13 | NULL | +----+-----------+------------+---------+ 13 rows in set (0.00 sec) mysql> mysql> SELECT * FROM view_student; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql> mysql> ALTER TABLE student DROP COLUMN name; #将原表中删除一个字段～ Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT * FROM student; +----+------------+---------+ | id | teacher_id | remarks | +----+------------+---------+ | 1 | 1 | NULL | | 2 | 2 | NULL | | 3 | 3 | NULL | | 4 | 4 | NULL | | 5 | 5 | NULL | | 6 | 6 | NULL | | 7 | 7 | NULL | | 8 | 8 | NULL | | 9 | 9 | NULL | | 10 | 10 | NULL | | 11 | 11 | NULL | | 12 | 12 | NULL | | 13 | 13 | NULL | +----+------------+---------+ 13 rows in set (0.00 sec) mysql> mysql> SELECT * FROM view_student; #当我们删除一个字段时，发现我们无法从定义的视图中查阅数据～ ERROR 1356 (HY000): View 'yinzhengjie.view_student' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them mysql> mysql> SHOW CREATE TABLE view_student; #如果非要想知道为什么会出现上述的报错情况，我们可以查看一下创建视图过程～从中你会找到对应的答案～ +--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | view_student | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_student` AS select `student`.`id` AS `id`,`student`.`name` AS `name`,`student`.`teacher_id` AS `teacher_id` from `student` | utf8mb4 | utf8mb4_0900_ai_ci | +--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set, 1 warning (0.00 sec) mysql>
5>.创建对视图默认情况下是属于当前数据库的，当要创建到另外的数据库时则需要在视图前面加上数据库名。
mysql> CREATE VIEW yinzhengjie.student_view AS SELECT * FROM yinzhengjie.student; Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT DATABASE(); +-------------+ | DATABASE() | +-------------+ | yinzhengjie | +-------------+ 1 row in set (0.00 sec) mysql> mysql> SHOW TABLES; +-----------------------+ | Tables_in_yinzhengjie | +-----------------------+ | course | | scores | | student | | student_view | | teacher | +-----------------------+ 5 rows in set (0.01 sec) mysql> mysql> SELECT * FROM student_view; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.01 sec) mysql> mysql> SELECT * FROM student; +----+-----------+------------+ | id | name | teacher_id | +----+-----------+------------+ | 1 | 尹正杰 | 1 | | 2 | 耿宇星 | 2 | | 3 | 陈飞 | 3 | | 4 | 彭兴旭 | 4 | | 5 | 李慧鹏 | 5 | | 6 | 孟欣 | 6 | | 7 | 鲜惠珊 | 7 | | 8 | 陈劲 | 8 | | 9 | 居彭阳 | 9 | | 10 | 李嘉韵 | 10 | | 11 | 石闹闹 | 11 | | 12 | 肖风 | 12 | | 13 | 刘晓江 | 13 | +----+-----------+------------+ 13 rows in set (0.00 sec) mysql>
6>.ORDER BY 子句在创建视图过程中是允许的，但当后续当查询视图当语句中有子句当ORDER BY子句是则会被忽略掉；
7>.视图在满足特定的条件时是可以执行 INSERT/UPDATE/DELETE 语句的，条件就是视图中的每一行和视图对应的表中的每行数据都能意义对应起来。
mysql> SELECT * FROM teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+-----------+ 13 rows in set (0.00 sec) mysql> mysql> CREATE VIEW view_teacher AS SELECT id,name,course_id FROM teacher; Query OK, 0 rows affected (0.01 sec) mysql> mysql> SELECT * FROM view_teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | +----+-----------+-----------+ 13 rows in set (0.00 sec) mysql> mysql> INSERT INTO view_teacher(id,name,course_id) VALUES(14,'周润发',3),(15,'周星驰',7); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> mysql> SELECT * FROM view_teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | | 14 | 周润发 | 3 | | 15 | 周星驰 | 7 | +----+-----------+-----------+ 15 rows in set (0.00 sec) mysql> mysql> SELECT * FROM teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | | 14 | 周润发 | 3 | | 15 | 周星驰 | 7 | +----+-----------+-----------+ 15 rows in set (0.00 sec) mysql>
mysql> SELECT * FROM view_teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | | 10 | 苍老师 | 5 | | 11 | 谢娜 | 8 | | 12 | 赵薇 | 6 | | 13 | 张卫健 | 7 | | 14 | 周润发 | 3 | | 15 | 周星驰 | 7 | +----+-----------+-----------+ 15 rows in set (0.00 sec) mysql> mysql> DELETE FROM view_teacher WHERE id >= 10; Query OK, 6 rows affected (0.00 sec) mysql> SELECT * FROM view_teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | +----+-----------+-----------+ 9 rows in set (0.00 sec) mysql> mysql> SELECT * FROM teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | +----+-----------+-----------+ 9 rows in set (0.00 sec) mysql>
mysql> SELECT * FROM view_teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 张杰 | 9 | +----+-----------+-----------+ 9 rows in set (0.00 sec) mysql> mysql> UPDATE view_teacher SET name = '林俊杰' WHERE id = 9 ; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> SELECT * FROM view_teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 林俊杰 | 9 | +----+-----------+-----------+ 9 rows in set (0.01 sec) mysql> mysql> SELECT * FROM teacher; +----+-----------+-----------+ | id | name | course_id | +----+-----------+-----------+ | 1 | 谢霆锋 | 11 | | 2 | 周杰伦 | 1 | | 3 | 蔡依林 | 13 | | 4 | 杨幂 | 2 | | 5 | 胡歌 | 12 | | 6 | 刘德华 | 3 | | 7 | 张学友 | 10 | | 8 | 郭德纲 | 4 | | 9 | 林俊杰 | 9 | +----+-----------+-----------+ 9 rows in set (0.00 sec) mysql>
六.CREATE INDEX语句
CREATE INDEX语句用来创建索引。
1>.查看CREATE INDEX语句的帮助信息
mysql> ? CREATE INDEX Name: 'CREATE INDEX' Description: Syntax: CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [index_type] ON tbl_name (key_part,...) [index_option] [algorithm_option | lock_option] ... key_part: {col_name [(length)] | (expr)} [ASC | DESC] index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' | {VISIBLE | INVISIBLE} index_type: USING {BTREE | HASH} algorithm_option: ALGORITHM [=] {DEFAULT | INPLACE | COPY} lock_option: LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE} Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See [HELP CREATE TABLE]. This guideline is especially important for InnoDB tables, where the primary key determines the physical layout of rows in the data file. CREATE INDEX enables you to add indexes to existing tables. CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See [HELP ALTER TABLE]. CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead. For more information about indexes, see http://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html. URL: http://dev.mysql.com/doc/refman/8.0/en/create-index.html mysql>
2>.INDEX_COL_NAME 可以包含一个字段，也可以包含多个字段（逗号隔开），如果包含多个字段，则表面此索引是复合索引
mysql> SHOW CREATE TABLE teacher\G *************************** 1. row *************************** Table: teacher Create Table: CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `course_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `teacher_course` (`course_id`), CONSTRAINT `teacher_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> mysql> mysql> CREATE INDEX index_teacher_id ON teacher(id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> CREATE INDEX index_teacher_id_and_name ON teacher(id,name); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> SHOW CREATE TABLE teacher\G *************************** 1. row *************************** Table: teacher Create Table: CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `course_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `teacher_course` (`course_id`), KEY `index_teacher_id` (`id`), KEY `index_teacher_id_and_name` (`id`,`name`), CONSTRAINT `teacher_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> mysql>
3>.UNIQUE INDEX代表索引中的只不能有重复
mysql> SHOW CREATE TABLE teacher\G *************************** 1. row *************************** Table: teacher Create Table: CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `course_id` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `teacher_course` (`course_id`), KEY `index_teacher_id` (`id`), KEY `index_teacher_id_and_name` (`id`,`name`), CONSTRAINT `teacher_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> mysql> CREATE UNIQUE INDEX index_teacher_id_unique ON teacher(id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> SHOW CREATE TABLE teacher\G *************************** 1. row *************************** Table: teacher Create Table: CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `course_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_teacher_id_unique` (`id`), KEY `teacher_course` (`course_id`), KEY `index_teacher_id` (`id`), KEY `index_teacher_id_and_name` (`id`,`name`), CONSTRAINT `teacher_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 1 row in set (0.01 sec) mysql>
4>.FULLTEXT INDEX只能创建在innodb和myisam存储引擎的CHAR，VARCHAR和TEXT字段上
5>.INDEX可以创建在包含NULL值的字段上
6>.KEY_BLOCK_SIZE=VALUE是在myisam存储引擎的表上指定索引键block大小
7>.COMMENT 'string'代表可以为索引天啊急最长1024的注释
mysql> SHOW CREATE TABLE teacher\G *************************** 1. row *************************** Table: teacher Create Table: CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `course_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_teacher_id_unique` (`id`), KEY `teacher_course` (`course_id`), KEY `index_teacher_id` (`id`), KEY `index_teacher_id_and_name` (`id`,`name`), CONSTRAINT `teacher_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> mysql> CREATE UNIQUE INDEX index_teacher_id_comment ON teacher(id) COMMENT '这就是创建一个唯一索引而已!'; Query OK, 0 rows affected, 1 warning (0.01 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> SHOW CREATE TABLE teacher\G *************************** 1. row *************************** Table: teacher Create Table: CREATE TABLE `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) DEFAULT NULL, `course_id` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_teacher_id_unique` (`id`), UNIQUE KEY `index_teacher_id_comment` (`id`) COMMENT '这就是创建一个唯一索引而已!', KEY `teacher_course` (`course_id`), KEY `index_teacher_id` (`id`), KEY `index_teacher_id_and_name` (`id`,`name`), CONSTRAINT `teacher_course` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql>
当你的才华还撑不起你的野心的时候，你就应该静下心来学习。当你的能力还驾驭不了你的目标的时候，你就应该沉下心来历练。问问自己，想要怎样的人生。
欢迎交流学习技术交流，个人微信: "JasonYin2020"（添加时请备注来源及意图备注）
作者: 尹正杰, 博客: https://www.cnblogs.com/yinzhengjie/p/10317655.html
