DML数据操作语言
三、检索数据
select
单表查询
联结查询（join）
联合查询（union）
检索表数据，关键点是至少要给出两条信息————选择什么，从哪里选择。
1、检索多个列，需要使用逗号分隔，最后一个列名不用逗号。*表示所有列。
mysql> select user_name,password,password_salt from user where user_name='admin';
+-----------+----------------------------------+---------------+
| user_name | password | password_salt |
+-----------+----------------------------------+---------------+
| admin | 4da09b8063d00843d65069e711c8ae54 | 64675211 |
+-----------+----------------------------------+---------------+
1 row in set (0.00 sec)
2、显示某一列，重复内容只显示一次。
mysql> select distinct roles from user;
+----------------+
| roles |
+----------------+
| teacher| |
| |
| admin| |
| administrator| |
+----------------+
4 rows in set (0.02 sec)
3、指定显示行的范围，limit。limit 3、limit 2,3表示从第二行开始取两行显示。
mysql> select user_name from user limit 2,3;
+-----------+
| user_name |
+-----------+
| szxy1403 |
| szxy125 |
| szxy509 |
+-----------+
3 rows in set (0.00 sec)
4、完全限定检索
mysql> select user.user_name from ec_school.user;
四、排序检索语句
4.1将检索的结果按照某一个列的字母顺序进行排列显示。在使用多重排序的时候，列名之间用逗号分开，并且只有在第一个列名中存在多个相同值得时候才会启动后面的排序规则。
mysql> select user.user_name from ec_school.user order by user_name;
4.2先按照value的降序排列，然后对相同的数值在按照user_setting_id的升序排列。
mysql> select * from user_setting order by value desc,user_setting_id;
+-----------------+-------+-----------+------------------+
| user_setting_id | name | value | user |
+-----------------+-------+-----------+------------------+
| 1 | theme | school | 3????L??"_?o9_@ |
| 5 | theme | school | p??B?O?;Z?? |
| 11 | theme | school | E[?T??A???e?3 |
| 12 | theme | school | h?? ?B^???V?ā? |
4.3 对每个班级有多少名学生进行求和汇总。
mysql> select classID,count(name) from students group by classid;
+---------+-------------+
| classID | count(name) |
+---------+-------------+
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
+---------+-------------+
3 rows in set (0.00 sec)
4.4对每个班级的学生平均年龄进行汇总。
mysql> select classID,avg(age) from students group by classid;
+---------+----------+
| classID | avg(age) |
+---------+----------+
| 1 | 23.0000 |
| 2 | 22.0000 |
| 3 | 45.3333 |
+---------+----------+
3 rows in set (0.00 sec)
4.5对平均年龄大于23岁的班级进行统计，这里要使用having而不是where。
mysql> select classID,avg(age) from students group by classid having avg(age)>=23;
+---------+----------+
| classID | avg(age) |
+---------+----------+
| 1 | 23.0000 |
| 3 | 45.3333 |
+---------+----------+
2 rows in set (0.00 sec)
总结：
1、WHERE 子句用来筛选 FROM 子句中指定的操作所产生的行。
2、GROUP BY 子句用来分组 WHERE 子句的输出。
3、HAVING 子句用来从分组的结果中筛选行。
4、在查询过程中聚合语句(sum,min,max,avg,count)要比having子句优先执行.而where子句在查询过程中执行优先级别优先于聚合语句(sum,min,max,avg,count)
4.6以班级为组，显示各组年龄中最小的值，并只显示最小年龄低于23岁的人。
mysql> select name,min(age) from students group by classID having min（age）<=23;
+-------------+----------+
| name | min(age) |
+-------------+----------+
| shi potian | 23 |
| shi zhongyu | 22 |
+-------------+----------+
2 rows in set (0.00 sec)
4.7 以性别分组，显示各组重的年龄之和，要求年龄之和逆序排列。
mysql> select gender,sum(age) from students group by gender order by sum(age) desc;
+--------+----------+
| gender | sum(age) |
+--------+----------+
| m | 202 |
| f | 69 |
+--------+----------+
2 rows in set (0.00 sec)
五、过滤数据
关键字：where
| = | 等于 |
| <>或者!= | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| between | 在指定的两个值之间 |
注意：
单引号的用法：
当指定的过滤条件是列的字符类型时使用单引号，当指定的列是数值类型时可以不用单引号。
在同时使用where和order by的时候，order by在后面。
5.1不显示value的值是blue的行，并按照指定排序。
mysql> select * from user_setting where value!='blue' order by value desc,user;
5.2检索user_setting表中id在19-22之间的行。
mysql> select * from user_setting where user_setting_id between 19 and 22 order by value desc,user;
+-----------------+-------+-----------+------------------+
| user_setting_id | name | value | user |
+-----------------+-------+-----------+------------------+
| 21 | theme | rainbow | ou6q??B??Mr?!t? |
| 20 | theme | rainbow | s?iNY?Do?Q?%?e |
| 19 | theme | grassland | \*??M????????? |
| 22 | theme | blue | +??v?vM>????5??? |
+-----------------+-------+-----------+------------------+
4 rows in set (0.00 sec)
5.3 检索表中指定列为空的行。
mysql> select * from school where phone is null;
六、数据过滤
组合where子句，通过and（与运算）和or（或运算）来组合。
6.1检索角色是admin，或者状态是actice的，并且用户名是szxy1173的纪录。
mysql> select user_id,user_name,roles,password,created_by from user where (roles='admin|' or status='active') and user_name='szxy1173';
+------------------+-----------+----------+----------------------------------+------------------+
| user_id | user_name | roles | password | created_by |
+------------------+-----------+----------+----------------------------------+------------------+
| ???d?
@?9L??0 | szxy1173 | teacher| | 25f7f45a9dd723a5d02f175835dfa19e | U,?@g?r?*???} |
| ???Z?Gw??
?w,?\ | szxy1173 | teacher| | 2cd6a65a0506d9441a955c259379dc1f | U,?@g?r?*???} |
+------------------+-----------+----------+----------------------------------+------------------+
2 rows in set (0.00 sec)
6.2IN操作符，用来指定一个范围，作用类似于or。
mysql> select user_id,user_name,roles,password,created_by from user where roles IN ('admin|','administrator|');
+------------------+-----------+----------------+----------------------------------+------------------+
| user_id | user_name | roles | password | created_by |
+------------------+-----------+----------------+----------------------------------+-----------------
| ??J??D?"n? | school01 | admin| | d2fda19a662c6f043decfdbbfc92dc92 | ???k#-M??2f??_?z |
| ???k#-M??2f??_?z | admin | administrator| | 4da09b8063d00843d65069e711c8ae54 | = ?[2IB?zv????? |
+------------------+-----------+----------------+----------------------------------+------------------+
5 rows in set (0.00 sec)
6.3 NOT用途只有一个就是否定后面的条件，取非得含义。
mysql> select user_name from user where roles not in ('teacher|');
+-----------+
| user_name |
+-----------+
| szxydt01 |
| szxyjl07 |
| dtedu001 |
| szxyjl01 |
+-----------+
4 rows in set (0.01 sec)
七、用通配符进行过滤
%：表示任意长度的任意字符。
_：表示任意一个且必须是一个字符。
八、正则表达式搜索
关键字：regexp
区别：regexp匹配包含指定字符的所有内容，而like是精确匹配，要求和匹配的内容完全相同。
mysql> select user_name from user where user_name regexp 'dmin';
+-----------+
| user_name |
+-----------+
| sadmin |
| admin |
+-----------+
2 rows in set (0.01 sec)
mysql> select user_name from user where user_name like 'dmin';
Empty set (0.00 sec)
8.1区分过滤字符串的大小写，使用binary关键字。
mysql> select user_name from user where user_name regexp binary 'dmin';
+-----------+
| user_name |
+-----------+
| sadmin |
| admin |
+-----------+
2 rows in set (0.00 sec)
mysql> select user_name from user where user_name regexp binary 'DMIN';
Empty set (0.00 sec)
8.2正则表达式中的或运算。|
mysql> select user_name from user where user_name regexp 'admin|sadmin';
+-----------+
| user_name |
+-----------+
| sadmin |
| admin |
+-----------+
2 rows in set (0.01 sec)
8.3匹配几个字符中的任意一个。
mysql> select user_name,password,status from user where user_name regexp 'szxy[34]';
+-----------+----------------------------------+--------+
| user_name | password | status |
+-----------+----------------------------------+--------+
| szxy374 | 8497f477e8168a9e4527795d43fa31e2 | active |
| szxy476 | c9a646e3ccc66bb6394191aaf7551e27 | active |
| szxy422 | e26c02bb9b0c1ef109ba2aa742427c1a | active |
| szxy370 | cbae7ec9775a946b4a626cfcf29699e4 | active |
| szxy416 | a8216a528f351949dbf29aba28dd163b | active |
+-----------+----------------------------------+--------+
5 rows in set (0.01 sec)
8.4mysql查询语句中的转义字符是\\
| 转义 | 说明 |
| \\f | 换页 |
| \\n | 换行 |
| \\r | 回车 |
| \\t | 制表 |
| \\v | 纵向制表符 |
| \\. \\\ \\_ | 显示符号的本身字符含义 |
| 为什么是两个\\ | 一个用来给mysql使用，另一个给正则表达式使用 |
正则表达式中字符类的含义说明
| 类 | 说明 |
| [:alnum:] | 任意字母和数字[A-Za-z1-9] |
| [:alpha:] | 任意字符[a-zA-Z] |
| [:blank:] | 空格和制表符[\\t] |
| [:cntrl:] | ASCII控制字符 |
| [:digit:] | 任意数字 |
| [:lower:] | 任意小写字母 |
| [:print:] | 任意可打印字符 |
| [:punct:] | 既不在[:alnum:]又不在[:cntrl:]的任意字符 |
| [:space:] | 包括空格在内的任意空白字符同[\\f\\t\\v\\r\\n] |
| [:upper:] | 任意大写字母 |
| [:xdigit:] | 任意十六进制数字 |
mysql中的重复元字符
| 元字符 | 说明 |
| * | 任意一个或多个字符 |
| + | 1个或多个匹配，等于{1，} |
| {n} | 指定数目的匹配 |
| ？ | 0个或1个匹配 |
| {n，} | 不少于指定数目的匹配 |
| {n，m} | 匹配数目的范围 |
| 此元字符匹配的都是他前面的字符的指定次数。 |
常用检索关键字
| 字符 |
说明 |
| in （name1，name2） |
表示一个元素的集合 |
| between name1 and name2 |
介于两个数值之间 |
| like |
通配符匹配，通配符是%，_ |
| regexp | 正则表达式匹配 |
| and，or，not |
组合条件，将多个添加匹配在一起 |
匹配表中用户名包含szxy并且后面的数字只有2位的用户有哪些，这里如果没有加$符号的话，会认为至少包含2个数字字符的情况，因为正则表达式是对匹配内容的模糊查找。
mysql> select user_name,password,status from user where user_name regexp 'szxy[[:digit:]]{2}$';
+-----------+----------------------------------+--------+
| user_name | password | status |
+-----------+----------------------------------+--------+
| szxy02 | bd39c90c63d01704c4f64bd004c96e57 | active |
| szxy03 | 7ae5c6996b3976c033827320df1453ee | active |
| szxy01 | 800391acea29173f5784782cbbdb0eb2 | active |
+-----------+----------------------------------+--------+
3 rows in set (0.01 sec)
mysql的定位元字符
| 元字符 | 说明 |
| ^ | 文本的开头，他的另一个含义是在集合中[],表示对集合内容的否定，即取反。 |
| $ | 文本的结尾 |
| [[:<:]] | 单词的开始 |
| [[:>:]] | 单词的结尾 |
常用函数
| 函数 | 说明 |
| max（） |
求指定字段的最大值，字段写在括号内 |
| avg（） |
求平均值 |
| count（） |
统计指定字段的行数 |
| min（） |
求最小值 |
九、mysql的数据类型
1、查看当前mysql可以使用的数据类型有哪些？
mysql> help create table;
2、设置sql语句的兼容性，对于熟悉某一个数据库携带的sql语句的用户比较试用。默认情况下，mysql使用的是最宽泛的sql语句，兼容性最好。
查看目前状况
mysql> show global variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
设置sql_mode的类型，通常包括全局和会话两种
mysql> set session sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)
mysql> show session variables like 'sql_mode';
+---------------+-------------------+
| Variable_name | Value |
+---------------+-------------------+
| sql_mode | STRICT_ALL_TABLES |
+---------------+-------------------+
1 row in set (0.00 sec)
十、isert、update、delete，replace
数值数据：不需要带引号
字符数据：需要引号
空值：null，而不是‘’。
1、添加一条记录到表中。
方法一：mysql> insert into user set user_name='gongbing';
Query OK, 1 row affected, 5 warnings (0.12 sec)
方法二：mysql> insert into user (user_id,user_name,password) values (123141315123131123,'bing',123123),(12453241,'zhang',111111);
Query OK, 2 rows affected, 4 warnings (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 4
2、修改一条记录，一定要有where，否则修改全部记录，后果严重。
mysql> update user set user_name='liuhaixiang' where user_name='gongbing';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
3、删除一条记录。
mysql> delete from user where user_name='zhang';
Query OK, 1 row affected (0.04 sec)
注意：
1、一条记录被删除后他的主键值不会被重复使用，所以在清除表的时候建议使用truncate table table_name。
4、查看最后一次插入的记录的id号是多少？
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
十一、DDL数据定义语言
1、创建数据库，并制定默认字符集和排序规则。
mysql> create database if not exists gongbing;
Query OK, 1 row affected, 1 warning (0.00 sec)
2、删除数据库
当不再需要该表的时候，使用drop
当要保留表的结构，只是删除表的记录时使用truncate
当要删除部分记录时用delete
3、修改数据库参数、默认字符集、排序规则、更新数据字典。
insert databse db_name
4、创建数据库表
常见参数：
engine=engine_name：用来定义存储引擎
comment：定义注释
auto_increment：用来定义id递增起始值。
data_directory：定义数据字典保存位置。
delay_key_write：是否延时键值写入。
max_rows=最大存储行数。
row_format=行的数据格式
tablespace=定义表空间。
4.1创建数据库的表
方法一：mysql> use gongbing
Database changed
mysql> create table xiaoban (name varchar(10) not null,age tinyint unsigned);
Query OK, 0 rows affected (0.00 sec)
方法二：
mysql> create table zhongban select name,age from xiaoban;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
此方法创建的表未必和原表的数据类型完全相同，并且会将原表的数据同时复制过来。
方法三：
mysql> create table gaoban like xiaoban;
Query OK, 0 rows affected (0.00 sec)
此方法仅仅会将原表的结构（数据类型）进行复制创建，而不会将里面的数据复制过来。
4.2、重命名表名称
mysql> rename table gongbing.xiaoban to gongbing.chuji;
Query OK, 0 rows affected (0.00 sec)
4.3在原有的表中添加字段（列名称）
mysql> alter table chuji add (address char(2),huji char (10));
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
4.4将表中的字段移动到其他位置，需要将字段的属性值写全。
mysql> alter table chuji modify class char(5) after huji;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
4.5修改表中字段的属性值。
mysql> alter table chuji modify class char(5);
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 1
4.6修改表中字段的名称，同样需要将属性加上。
mysql> alter table chuji change class new_class char(5);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
查看表的存储引擎，默认不加参数的时候是myISAM引擎。
myISAM引擎：
tbname.MYD 存储的是数据库数据
tbname.MYI 存储的是数据库索引
tbname.FRM 存储的是数据库属性、定义。
以上文件保存在数据库的数据目录中，通常通过datadir来指定。
innodb引擎：
ibdata1：存放数据的表空间。默认情况是多个表公用一个表空间。但是建议每个表一个表空间，可以实现innodb的高级功能，比如单表备份。
实现方法：
查看参数
mysql> show global variables like 'innodb%';
+-----------------------------------------+------------------------+
| Variable_name | Value |
+-----------------------------------------+------------------------+
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_legacy_cardinality_algorithm | ON |
+-----------------------------------------+------------------------+
36 rows in set (0.00 sec)
设置每个表使用一个存储空间，修改my.cnf文件，并添加innodb_file_per_table=1在[mysqld]区域中。
mysql> show table status\G;
*************************** 1. row ***************************
Name: xiaoban
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 281474976710655
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2016-06-15 18:33:10
Update_time: 2016-06-15 18:33:10
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
ERROR:
No query specified
注意及建议：
1、同一个库中的表文件建议使用相同的存储引擎。
表分区
就是当企业的数据库的数据巨大的时候，往往需要将一个表的数据按照某种方式进行划分成多个更小的单位，方便数据检索，此方法称为表分区。
表的关联
1、交叉连接，这种表很少使用。
比如有两个表，产生交叉连接的结果就是第一个表的第一行与第二个表的每一行组成一个新的行记录，以此类推直到第一个表的每一行与第二个表全部产生关系。
mysql> select * from students,class;
+-------+-------------+-----+--------+---------+---------+-------------+----------+
| stuid | name | age | gender | classid | classid | class | numofstu |
+-------+-------------+-----+--------+---------+---------+-------------+----------+
| 1 | shi zhongyu | 22 | m | 2 | 1 | 1nianji2ban | 20 |
| 1 | shi zhongyu | 22 | m | 2 | 2 | 1nianji1ban | 10 |
| 1 | shi zhongyu | 22 | m | 2 | 3 | 2nianji1ban | 12 |
| 1 | shi zhongyu | 22 | m | 2 | 4 | 2nianji2ban | 23 |
| 2 | shi potian | 23 | f | 1 | 1 | 1nianji2ban | 20 |
| 2 | shi potian | 23 | f | 1 | 2 | 1nianji1ban | 10 |
| 2 | shi potian | 23 | f | 1 | 3 | 2nianji1ban | 12 |
| 2 | shi potian | 23 | f | 1 | 4 | 2nianji2ban | 23 |
| 3 | zhouxin | 42 | m | 3 | 1 | 1nianji2ban | 20 |
| 3 | zhouxin | 42 | m | 3 | 2 | 1nianji1ban | 10 |
| 3 | zhouxin | 42 | m | 3 | 3 | 2nianji1ban | 12 |
| 3 | zhouxin | 42 | m | 3 | 4 | 2nianji2ban | 23 |
| 4 | shi zhongyu | 22 | m | 2 | 1 | 1nianji2ban | 20 |
| 4 | shi zhongyu | 22 | m | 2 | 2 | 1nianji1ban | 10 |
| 4 | shi zhongyu | 22 | m | 2 | 3 | 2nianji1ban | 12 |
| 4 | shi zhongyu | 22 | m | 2 | 4 | 2nianji2ban | 23 |
| 5 | shi potian | 23 | f | 1 | 1 | 1nianji2ban | 20 |
| 5 | shi potian | 23 | f | 1 | 2 | 1nianji1ban | 10 |
| 5 | shi potian | 23 | f | 1 | 3 | 2nianji1ban | 12 |
| 5 | shi potian | 23 | f | 1 | 4 | 2nianji2ban | 23 |
| 6 | zhouxi | 42 | m | 3 | 1 | 1nianji2ban | 20 |
| 6 | zhouxi | 42 | m | 3 | 2 | 1nianji1ban | 10 |
| 6 | zhouxi | 42 | m | 3 | 3 | 2nianji1ban | 12 |
| 6 | zhouxi | 42 | m | 3 | 4 | 2nianji2ban | 23 |
| 7 | lili | 22 | m | 2 | 1 | 1nianji2ban | 20 |
| 7 | lili | 22 | m | 2 | 2 | 1nianji1ban | 10 |
| 7 | lili | 22 | m | 2 | 3 | 2nianji1ban | 12 |
| 7 | lili | 22 | m | 2 | 4 | 2nianji2ban | 23 |
| 8 | liuhaixiang | 23 | f | 1 | 1 | 1nianji2ban | 20 |
| 8 | liuhaixiang | 23 | f | 1 | 2 | 1nianji1ban | 10 |
| 8 | liuhaixiang | 23 | f | 1 | 3 | 2nianji1ban | 12 |
| 8 | liuhaixiang | 23 | f | 1 | 4 | 2nianji2ban | 23 |
| 9 | gongbing | 52 | m | 3 | 1 | 1nianji2ban | 20 |
| 9 | gongbing | 52 | m | 3 | 2 | 1nianji1ban | 10 |
| 9 | gongbing | 52 | m | 3 | 3 | 2nianji1ban | 12 |
| 9 | gongbing | 52 | m | 3 | 4 | 2nianji2ban | 23 |
+-------+-------------+-----+--------+---------+---------+-------------+----------+
36 rows in set (0.00 sec)
2、内连接，自然联接。
将第一个表中的某个字段同第二个表中的某个字段建立等值联接关系。（将不同表中共同的字段名创建关联）
mysql> select * from students,class where students.classid=class.classid;
+-------+-------------+-----+--------+---------+---------+-------------+----------+
| stuid | name | age | gender | classid | classid | class | numofstu |
+-------+-------------+-----+--------+---------+---------+-------------+----------+
| 2 | shi potian | 23 | f | 1 | 1 | 1nianji2ban | 20 |
| 5 | shi potian | 23 | f | 1 | 1 | 1nianji2ban | 20 |
| 8 | liuhaixiang | 23 | f | 1 | 1 | 1nianji2ban | 20 |
| 1 | shi zhongyu | 22 | m | 2 | 2 | 1nianji1ban | 10 |
| 4 | shi zhongyu | 22 | m | 2 | 2 | 1nianji1ban | 10 |
| 7 | lili | 22 | m | 2 | 2 | 1nianji1ban | 10 |
| 3 | zhouxin | 42 | m | 3 | 3 | 2nianji1ban | 12 |
| 6 | zhouxi | 42 | m | 3 | 3 | 2nianji1ban | 12 |
| 9 | gongbing | 52 | m | 3 | 3 | 2nianji1ban | 12 |
+-------+-------------+-----+--------+---------+---------+-------------+----------+
9 rows in set (0.00 sec)
显示学生所属班级，（结合了student，class两个表）
mysql> select name,class from students,class where students.classid=class.classid;
+-------------+-------------+
| name | class |
+-------------+-------------+
| shi potian | 1nianji2ban |
| shi potian | 1nianji2ban |
| liuhaixiang | 1nianji2ban |
| shi zhongyu | 1nianji1ban |
| shi zhongyu | 1nianji1ban |
| lili | 1nianji1ban |
| zhouxin | 2nianji1ban |
| zhouxi | 2nianji1ban |
| gongbing | 2nianji1ban |
+-------------+-------------+
9 rows in set (0.00 sec)
3、外联接
默认情况下，如果某个学生没有班级这样的属性，在前面的内连接中，就不会显示出来，如何让他也显示出来，这个就是外连接的工作。
在查询时，写在左边的叫做左外连接，写在右边的叫做右外连接。
左外联接：
left_table left join...right_table...to...condition....
mysql> select name,class from students left join class on students.classid=class.classid;
+-------------+-------------+
| name | class |
+-------------+-------------+
| shi zhongyu | 1nianji1ban |
| shi potian | 1nianji2ban |
| zhouxin | 2nianji1ban |
| shi zhongyu | 1nianji1ban |
| shi potian | 1nianji2ban |
| zhouxi | 2nianji1ban |
| lili | 1nianji1ban |
| liuhaixiang | 1nianji2ban |
| gongbing | 2nianji1ban |
| qianlong | NULL |
| meichaofeng | NULL |
+-------------+-------------+
11 rows in set (0.00 sec)
右外联接：
right jone....... to......
mysql> select name,class from class join students on students.classid=class.classid;
+-------------+-------------+
| name | class |
+-------------+-------------+
| shi potian | 1nianji2ban |
| shi potian | 1nianji2ban |
| liuhaixiang | 1nianji2ban |
| shi zhongyu | 1nianji1ban |
| shi zhongyu | 1nianji1ban |
| lili | 1nianji1ban |
| zhouxin | 2nianji1ban |
| zhouxi | 2nianji1ban |
| gongbing | 2nianji1ban |
+-------------+-------------+
9 rows in set (0.00 sec)
4、自联接
sometable as alias1 inner join sometable as aliase2 on alias1.filed=alias2.filed;
5、将两个表的查询内容合并到一个表里显示，使用union，称为联合查询。
mysql> select name,age from teacher where age>40 union select name,age from students where age>40;
+-------------+-----+
| name | age |
+-------------+-----+
| zhang | 65 |
| li | 76 |
| liu | 45 |
| zhouxin | 42 |
| zhouxi | 42 |
| gongbing | 52 |
| qianlong | 76 |
| meichaofeng | 90 |
+-------------+-----+
8 rows in set (0.00 sec)
6、显示学生的课程
mysql> select name,course from students,class,coc,cources where students.classid=class.classid and class.classid=coc.classID ;
+-------------+---------+
| name | course |
+-------------+---------+
| shi zhongyu | yuwen |
| shi zhongyu | shuxue |
| shi zhongyu | yingyu |
| shi zhongyu | huaxue |
| shi zhongyu | yinyue |
| shi zhongyu | laodong |
| shi potian | yuwen |
| shi potian | shuxue |
| shi potian | yingyu |
| shi potian | huaxue |
| shi potian | yinyue |
| shi potian | laodong |
| zhouxin | yuwen |
| zhouxin | shuxue |
| zhouxin | yingyu |
| zhouxin | huaxue |
| zhouxin | yinyue |
| zhouxin | laodong |
| shi zhongyu | yuwen |
| shi zhongyu | shuxue |
| shi zhongyu | yingyu |
| shi zhongyu | huaxue |
| shi zhongyu | yinyue |
| shi zhongyu | laodong |
| shi potian | yuwen |
| shi potian | shuxue |
| shi potian | yingyu |
| shi potian | huaxue |
| shi potian | yinyue |
| shi potian | laodong |
| zhouxi | yuwen |
| zhouxi | shuxue |
| zhouxi | yingyu |
| zhouxi | huaxue |
| zhouxi | yinyue |
| zhouxi | laodong |
| lili | yuwen |
| lili | shuxue |
| lili | yingyu |
| lili | huaxue |
| lili | yinyue |
| lili | laodong |
| liuhaixiang | yuwen |
| liuhaixiang | shuxue |
| liuhaixiang | yingyu |
| liuhaixiang | huaxue |
| liuhaixiang | yinyue |
| liuhaixiang | laodong |
| gongbing | yuwen |
| gongbing | shuxue |
| gongbing | yingyu |
| gongbing | huaxue |
| gongbing | yinyue |
| gongbing | laodong |
+-------------+---------+
54 rows in set (0.00 sec)
子查询：
就是在查询语句中嵌套着另一个查询语句。基本存在两种方式，即where中的子查询语句和from中的子查询语句。
嵌套于where中的子查询。
使用方法：
1、用于比较判断的查询中
2、用于判断是否存在exists的查询中。
3、用于in中的子查询
查看students表中，大于平均年龄的学生信息。
mysql> select name,age from students where age > (select avg(age) from students);
+-------------+-----+
| name | age |
+-------------+-----+
| zhouxin | 42 |
| zhouxi | 42 |
| gongbing | 52 |
| qianlong | 76 |
| meichaofeng | 90 |
+-------------+-----+
5 rows in set (0.00 sec)
查看coc表中的courseID在cource表中没有的，并显示classID信息。
mysql> select classID from coc where courseID not in (select courseID from cources);
+---------+
| classID |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
mysql> select * from cources;
+----------+---------+
| courseID | course |
+----------+---------+
| 1 | yuwen |
| 2 | shuxue |
| 3 | yingyu |
| 4 | huaxue |
| 5 | yinyue |
| 6 | laodong |
+----------+---------+
6 rows in set (0.00 sec)
mysql> select * from coc;
+----+---------+----------+
| ID | classID | courseID |
+----+---------+----------+
| 1 | 1 | 3 |
| 2 | 2 | 5 |
| 3 | 3 | 8 |
| 4 | 4 | 2 |
| 5 | 5 | 5 |
+----+---------+----------+
5 rows in set (0.00 sec)
嵌套于from中的子查询
查询年龄大于30岁并且是男性的学生信息。
mysql> select * from (select name,age from students where gender='f') as a where age>30;
+----------+-----+
| name | age |
+----------+-----+
| qianlong | 76 |
+----------+-----+
1 row in set (0.00 sec)
本文转自 blackstome 51CTO博客，原文链接：http://blog.51cto.com/137783/1968777，如需转载请自行联系原作者
