【MySQL01】查询--总结

/*
 * 本程序专为总结MySQL而写！
 * @author kevinelstri
 * @time 2016/7/14
 * */
package Database;

public class Database01 {

 public static void main(String[] args){
 System.out.println("本程序专为总结MySQL而写！");

 /* 
 * --------------------------------------------MySQL基本操作-------------------------------------------------
 * 
 * mysql的启动：mysql -u root -p
 * mysql服务开启：net start mysql
 * mysql服务关闭：net stop mysql
 * 
 * 创建数据库：create database db_name;
 * 
 * 查看所有数据库：show databases;
 * 
 * 查看某一个数据库：show create database db_name;
 * 
 * 删除数据库：drop database db_name;
 * 
 * 修改数据库：alter database db_name[修改指令];
 * 
 * 创建表：create table db_name;
 * 在表的创建过程中，至少包括一个属性：
 * create table test.class( //使用test.class表示在数据库test中创建一个class表
 * class_no varchar(20); //创建表的过程中，至少要创建一个属性
 * data_time date
 * );
 * 
 * 使用数据库：use db_name;//表示在接下来的过程中都会使用这个数据库
 * 
 * 查看数据库中的表：show tables;//查看使用的数据库中的所有的表
 * 
 * 获取具有某种规则的表：show table [like '%'];
 * 
 * 查看某个表的创建信息：show create table exam_student;
 * show create table exam_student\G;
 * 
 * 查看表的结构：describe table_name;
 * desc table_name;
 * 
 * 删除表：drop table table_name;
 * 
 * 修改表名：rename table old_table_name to new_table_name;
 * 
 * crud:增删改查（create、retrieve、update、delete）
 * 
 * 创建数据/插入数据：insert into 表名 (字段列表) values (值列表)
 * insert into exam_table (name,stu_no) values ('Mary',001);
 * 
 * 查询数据：select (字段列表) from 表名 查询条件
 * select (name,stu_no) from exam_student where stu_no > 002;
 * select * from exam_student;
 * 
 * 
 * 删除数据：delete from exam_student where stu_no=1; 
 * 
 * 修改数据：update 表名 set 字段=新值....条件
 * update exam_student set score=90 where stu_no='002';
 * update exam_student set name='Lily' where stu_no='001';
 * 
 * 查看变量：show variables;
 * 
 * -------------------------------------------MySQL高级操作-----------------------------------------
 * 
 * 【distinct】查询不重复数据：select distinct country from website;
 * //注意 distinct的用法，使重复元素只显示一次
 * 
 * 【where】子句：select * from website where id=1;
 * //where子句查询特定条件的数据
 * select name from website where country='CN';
 * 
 * 【and or】子句：select * from website where country='CN';
 * +------+---------+-----------------------+-------+---------+
 * | id | name | url | alexa | country |
 * +------+---------+-----------------------+-------+---------+
 * | 2 | taobao | http://www.taobao.com | 13 | CN |
 * | 3 | cainiao | http://www.runoob.com | 673 | CN |
 * | 4 | weibo | http://weibo.com | 20 | CN |
 * +------+---------+-----------------------+-------+---------+
 * 
 * select * from website where country='CN' or country='USA'; //or是或者关系 
 * +------+----------+-------------------------+-------+---------+
 * | id | name | url | alexa | country |
 * +------+----------+-------------------------+-------+---------+
 * | 1 | Google | http://www.google.com | 1 | USA |
 * | 2 | taobao | http://www.taobao.com | 13 | CN |
 * | 3 | cainiao | http://www.runoob.com | 673 | CN |
 * | 4 | weibo | http://weibo.com | 20 | CN |
 * | 5 | Facebook | http://www.facebook.com | 20 | USA |
 * +------+----------+-------------------------+-------+---------+
 * select * from website where country='CN' and country='USA';//and并集关系
 * Empty set (0.15 sec)
 * 
 * select * from website where alexa>15 and (country='CN' or country='USA');
 +------+----------+-------------------------+-------+---------+ | id | name | url | alexa | country |
 +------+----------+-------------------------+-------+---------+ | 3 | cainiao | http://www.runoob.com | 673 | CN |
 | 4 | weibo | http://weibo.com | 20 | CN |
 | 5 | Facebook | http://www.facebook.com | 20 | USA |
 +------+----------+-------------------------+-------+---------+
 * 
 * 【order by】排序：desc逆序排列,asc正序排列
 * select * from website order by name desc;//按姓名逆序排列
 +------+----------+-------------------------+-------+---------+ | id | name | url | alexa | country |
 +------+----------+-------------------------+-------+---------+ | 4 | weibo | http://weibo.com | 20 | CN |
 | 2 | taobao | http://www.taobao.com | 13 | CN |
 | 1 | Google | http://www.google.com | 1 | USA |
 | 5 | Facebook | http://www.facebook.com | 20 | USA |
 | 3 | cainiao | http://www.runoob.com | 673 | CN |
 +------+----------+-------------------------+-------+---------+ 5 rows in set (0.18 sec)

 select * from website order by name asc;//按姓名正序排列
 +------+----------+-------------------------+-------+---------+ | id | name | url | alexa | country |
 +------+----------+-------------------------+-------+---------+ | 3 | cainiao | http://www.runoob.com | 673 | CN |
 | 5 | Facebook | http://www.facebook.com | 20 | USA |
 | 1 | Google | http://www.google.com | 1 | USA |
 | 2 | taobao | http://www.taobao.com | 13 | CN |
 | 4 | weibo | http://weibo.com | 20 | CN |
 +------+----------+-------------------------+-------+---------+
 * 
 * 
 * 【update】更新表中的数据：mysql> update website
 * -> set alexa=32,country='CN'
 * -> where id=7;
 * 
 * mysql> select * from website;
 +------+----------+-------------------------+-------+---------+ | id | name | url | alexa | country |
 +------+----------+-------------------------+-------+---------+ | 1 | Google | http://www.google.com | 1 | USA |
 | 2 | taobao | http://www.taobao.com | 13 | CN |
 | 3 | cainiao | http://www.runoob.com | 673 | CN |
 | 4 | weibo | http://weibo.com | 20 | CN |
 | 5 | Facebook | http://www.facebook.com | 3 | USA |
 | 7 | ali | http://www.ali.com | 32 | CN |
 +------+----------+-------------------------+-------+---------+
 * 
 * 
 * 
 * 【delete】删除行：
 * delete from website where name='baidu';
 * 
 * select * from website;
 * +------+----------+-------------------------+-------+---------+ | id | name | url | alexa | country |
 +------+----------+-------------------------+-------+---------+ | 1 | Google | http://www.google.com | 1 | USA |
 | 2 | taobao | http://www.taobao.com | 13 | CN |
 | 3 | cainiao | http://www.runoob.com | 673 | CN |
 | 4 | weibo | http://weibo.com | 20 | CN |
 | 5 | Facebook | http://www.facebook.com | 3 | USA |
 | 7 | ali | http://www.ali.com | NULL | NULL |
 +------+----------+-------------------------+-------+---------+
 * 
 * 
 * 【like】操作符用于在where子句中搜索列中的指定模式：
 * select * from website where url like 'http://www%';
 * +------+----------+-------------------------+-------+---------+ | id | name | url | alexa | country |
 +------+----------+-------------------------+-------+---------+ | 1 | Google | http://www.google.com | 1 | USA |
 | 2 | taobao | http://www.taobao.com | 13 | CN |
 | 3 | cainiao | http://www.runoob.com | 673 | CN |
 | 5 | Facebook | http://www.facebook.com | 3 | USA |
 | 7 | ali | http://www.ali.com | 32 | CN |
 +------+----------+-------------------------+-------+---------+
 * 
 * select name,url from website where name like '%o';
 * +---------+-----------------------+ | name | url |
 +---------+-----------------------+ | taobao | http://www.taobao.com |
 | cainiao | http://www.runoob.com |
 | weibo | http://weibo.com |
 +---------+-----------------------+
 * 
 * 
 * mysql> select name,url from website where name not like '%o';
 +----------+-------------------------+ | name | url |
 +----------+-------------------------+ | Google | http://www.google.com |
 | Facebook | http://www.facebook.com |
 | ali | http://www.ali.com |
 +----------+-------------------------+
 * 
 * 【通配符】： 
 * +----------+---------------------------+ | % | 替代 0 个或多个字符 |
 +----------+---------------------------+ | _ | 替代一个字符 |
 +----------+---------------------------+ | [a-z] | a-z中的任何单一字符 |
 +----------+---------------------------+ |[^a-z]/[!a-z] | 不在a-z中的任何单一字符 |
 +----------+---------------------------+
 * 
 * mysql> select * from website where name like '_o%';
 +------+--------+-----------------------+-------+---------+ | id | name | url | alexa | country |
 +------+--------+-----------------------+-------+---------+ | 1 | Google | http://www.google.com | 1 | USA |
 +------+--------+-----------------------+-------+---------+
 * 
 * mysql> select name from website where name like '__i%';//这里是两个下划线
 +---------+ | name |
 +---------+ | cainiao |
 | weibo |
 | ali |
 +---------+
 * 
 * 【in】操作符：允许在where子句中规定多个值，where条件在某个范围内
 * 
 * mysql> select * from website
 -> where alexa in (1,2,3,4,5,6,7,8,9,0);
 +------+----------+-------------------------+-------+---------+ | id | name | url | alexa | country |
 +------+----------+-------------------------+-------+---------+ | 1 | Google | http://www.google.com | 1 | USA |
 | 5 | Facebook | http://www.facebook.com | 3 | USA |
 +------+----------+-------------------------+-------+---------+
 * 
 * mysql> select name from website where name in ('google','facebook','baidu','ali');
 +----------+ | name | 注意：mysql中对大小写不敏感，所以在命名的时候要注意大写和小写是一样的
 +----------+ | Google |
 | Facebook |
 | ali |
 +----------+
 * 
 * 【between】操作符：选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。
 * mysql> select * from website where alexa between 1 and 50;//数值
 +------+----------+-------------------------+-------+---------+ | id | name | url | alexa | country |
 +------+----------+-------------------------+-------+---------+ | 1 | Google | http://www.google.com | 1 | USA |
 | 2 | taobao | http://www.taobao.com | 13 | CN |
 | 4 | weibo | http://weibo.com | 20 | CN |
 | 5 | Facebook | http://www.facebook.com | 3 | USA |
 | 7 | ali | http://www.ali.com | 32 | CN |
 +------+----------+-------------------------+-------+---------+
 * 
 * mysql> select * from website where name between 'a' and 'f';//文本，name的第一个字母的范围
 +------+---------+-----------------------+-------+---------+ | id | name | url | alexa | country |
 +------+---------+-----------------------+-------+---------+ | 3 | cainiao | http://www.runoob.com | 673 | CN |
 | 7 | ali | http://www.ali.com | 32 | CN |
 +------+---------+-----------------------+-------+---------+
 * 
 * mysql> select * from website where alexa not between 1 and 20;
 +------+---------+-----------------------+-------+---------+ | id | name | url | alexa | country |
 +------+---------+-----------------------+-------+---------+ | 3 | cainiao | http://www.runoob.com | 673 | CN |
 | 7 | ali | http://www.ali.com | 32 | CN |
 +------+---------+-----------------------+-------+---------+
 *
 * mysql> select * from website where (alexa between 1 and 20) and country not in ('CN');
 +------+----------+-------------------------+-------+---------+ | id | name | url | alexa | country |
 +------+----------+-------------------------+-------+---------+ | 1 | Google | http://www.google.com | 1 | USA |
 | 5 | Facebook | http://www.facebook.com | 3 | USA |
 +------+----------+-------------------------+-------+---------+
 *
 * mysql> select * from access_log where date between '2016-05-15' and '2016-05-17';
 +------+---------+-------+------------+ | aid | site_id | count | date |
 +------+---------+-------+------------+ | 6 | 4 | 13 | 2016-05-15 |
 | 7 | 3 | 220 | 2016-05-16 |
 | 8 | 5 | 545 | 2016-05-16 |
 | 9 | 3 | 201 | 2016-05-17 |
 +------+---------+-------+------------+
 * 
 *
 * mysql> select * from access_log;
 +------+---------+-------+------------+ | aid | site_id | count | date |
 +------+---------+-------+------------+ | 1 | 1 | 45 | 2016-05-10 |
 | 2 | 3 | 100 | 2016-05-13 |
 | 3 | 1 | 230 | 2016-05-14 |
 | 4 | 2 | 10 | 2016-05-14 |
 | 5 | 5 | 206 | 2016-05-14 |
 | 6 | 4 | 13 | 2016-05-15 |
 | 7 | 3 | 220 | 2016-05-16 |
 | 8 | 5 | 545 | 2016-05-16 |
 | 9 | 3 | 201 | 2016-05-17 |
 +------+---------+-------+------------+
 *
 *
 *
 * 请注意!!!，在不同的数据库中，BETWEEN 操作符会产生不同的结果！
 在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
 在某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。
 在某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。
 因此，请检查您的数据库是如何处理 BETWEEN 操作符！
 *
 * 【AS】:别名，可以为表名称或列名称指定别名。
 *
 * 列的别名：
 * mysql> select aid,site_id as id,count,date //别名是在查询时，指定的别名
 -> from access_log;
 +------+------+-------+------------+ | aid | id | count | date |
 +------+------+-------+------------+ | 1 | 1 | 45 | 2016-05-10 |
 | 2 | 3 | 100 | 2016-05-13 |
 | 3 | 1 | 230 | 2016-05-14 |
 | 4 | 2 | 10 | 2016-05-14 |
 | 5 | 5 | 206 | 2016-05-14 |
 | 6 | 4 | 13 | 2016-05-15 |
 | 7 | 3 | 220 | 2016-05-16 |
 | 8 | 5 | 545 | 2016-05-16 |
 | 9 | 3 | 201 | 2016-05-17 |
 +------+------+-------+------------+
 *
 * 表的别名：
 * mysql> select w.name,w.url,a.count,a.date
 -> from website as w,access_log as a //对表进行别名定义
 -> where a.site_id=w.id;
 +----------+-------------------------+-------+------------+ | name | url | count | date |
 +----------+-------------------------+-------+------------+ | Google | http://www.google.com | 45 | 2016-05-10 |
 | cainiao | http://www.runoob.com | 100 | 2016-05-13 |
 | Google | http://www.google.com | 230 | 2016-05-14 |
 | taobao | http://www.taobao.com | 10 | 2016-05-14 |
 | Facebook | http://www.facebook.com | 206 | 2016-05-14 |
 | weibo | http://weibo.com | 13 | 2016-05-15 |
 | cainiao | http://www.runoob.com | 220 | 2016-05-16 |
 | Facebook | http://www.facebook.com | 545 | 2016-05-16 |
 | cainiao | http://www.runoob.com | 201 | 2016-05-17 |
 +----------+-------------------------+-------+------------+
 *
 * 【join】连接：用于把来自两个或多个表的行结合起来 （笛卡儿积）
 * 【INNER JOIN】 关键字在表中存在至少一个匹配时返回行
 * 【LEFT JOIN】 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则