匿名通过本文主要向大家介绍了MySQL，查询，总结等相关知识,希望本文的分享对您有所帮助
【MySQL 01】查询--总结
/*
* 本程序专为总结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.face