【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_studentG; * * 查看表的结构：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）中没有匹配。如果右表中没有匹配，则结果为 NULL。 * 【RIGHT JOIN】 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。 * 【FULL OUTER JOIN】 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行. * 【FULL OUTER JOIN】 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。 * * 【INNER JOIN】 * mysql> select website.id,website.name,website.alexa,website.country,access.aid,access.site_id,access.count -> from website inner join access on website.id=access.site_id； +------+----------+-------+---------+------+---------+-------+ | id | name | alexa | country | aid | site_id | count | +------+----------+-------+---------+------+---------+-------+ | 1 | Google | 1 | USA | 1 | 1 | 45 | | 3 | cainiao | 673 | CN | 2 | 3 | 100 | | 1 | Google | 1 | USA | 3 | 1 | 230 | | 2 | taobao | 13 | CN | 4 | 2 | 10 | | 5 | Facebook | 3 | USA | 5 | 5 | 206 | | 4 | weibo | 20 | CN | 6 | 4 | 13 | | 3 | cainiao | 673 | CN | 7 | 3 | 220 | | 5 | Facebook | 3 | USA | 8 | 5 | 545 | | 3 | cainiao | 673 | CN | 9 | 3 | 201 | +------+----------+-------+---------+------+---------+-------+ * * 【LEFT JOIN】 * mysql> select website.id,website.name,website.alexa,website.country,access.aid,access.site_id,access.count -> from website left join access on website.id=access.site_id; +------+----------+-------+---------+------+---------+-------+ | id | name | alexa | country | aid | site_id | count | +------+----------+-------+---------+------+---------+-------+ | 1 | Google | 1 | USA | 1 | 1 | 45 | | 1 | Google | 1 | USA | 3 | 1 | 230 | | 2 | taobao | 13 | CN | 4 | 2 | 10 | | 3 | cainiao | 673 | CN | 2 | 3 | 100 | | 3 | cainiao | 673 | CN | 7 | 3 | 220 | | 3 | cainiao | 673 | CN | 9 | 3 | 201 | | 4 | weibo | 20 | CN | 6 | 4 | 13 | | 5 | Facebook | 3 | USA | 5 | 5 | 206 | | 5 | Facebook | 3 | USA | 8 | 5 | 545 | | 7 | ali | 32 | CN | NULL | NULL | NULL | +------+----------+-------+---------+------+---------+-------+ * * mysql> select website.id,website.name,website.alexa,website.country,access.aid,access.site_id,access.count -> from website left join access on website.alexa=access.aid; +------+----------+-------+---------+------+---------+-------+ | id | name | alexa | country | aid | site_id | count | +------+----------+-------+---------+------+---------+-------+ | 1 | Google | 1 | USA | 1 | 1 | 45 | | 2 | taobao | 13 | CN | NULL | NULL | NULL | | 3 | cainiao | 673 | CN | NULL | NULL | NULL | | 4 | weibo | 20 | CN | NULL | NULL | NULL | | 5 | Facebook | 3 | USA | 3 | 1 | 230 | | 7 | ali | 32 | CN | NULL | NULL | NULL | +------+----------+-------+---------+------+---------+-------+ * * 【RIGHT JOIN】 * mysql> select website.id,website.name,website.alexa,website.country,access.aid,access.site_id,access.count -> from website right join access on website.alexa=access.aid; +------+----------+-------+---------+------+---------+-------+ | id | name | alexa | country | aid | site_id | count | +------+----------+-------+---------+------+---------+-------+ | 1 | Google | 1 | USA | 1 | 1 | 45 | | NULL | NULL | NULL | NULL | 2 | 3 | 100 | | 5 | Facebook | 3 | USA | 3 | 1 | 230 | | NULL | NULL | NULL | NULL | 4 | 2 | 10 | | NULL | NULL | NULL | NULL | 5 | 5 | 206 | | NULL | NULL | NULL | NULL | 6 | 4 | 13 | | NULL | NULL | NULL | NULL | 7 | 3 | 220 | | NULL | NULL | NULL | NULL | 8 | 5 | 545 | | NULL | NULL | NULL | NULL | 9 | 3 | 201 | +------+----------+-------+---------+------+---------+-------+ * * *【union】操作符：合并两个或多个 SELECT 语句的结果 * 注意：UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。 * 同时，每个 SELECT 语句中的列的顺序必须相同。 * * 注意：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL * * mysql> select country from app -> union -> select country from website; +---------+ | country | +---------+ | CN | | USA | +---------+ mysql> select country from app -> union all -> select country from website; +---------+ | country | +---------+ | CN | | CN | | CN | | USA | | CN | | CN | | CN | | USA | | CN | +---------+ * * *select into:从一个表中复制数据，然后插入到一个新表中 *insert into select:从一个表复制数据，然后把数据插入到一个已存在的表中 * 区别： select into from 要求目标表不存在，因为在插入时会自动创建。 * insert into select from 要求目标表存在，直接进行插入。 * * 【select into】 * ★★★★注意：mysql不支持select into语句 * 可以替换为：create table newtable(select * from website); * mysql> create table newtable(select * from website); Query OK, 6 rows affected (0.17 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from newtable; +------+----------+-------------------------+-------+---------+ | 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 | +------+----------+-------------------------+-------+---------+ * * 【insert into select】 * mysql> create table web(id int,name varchar(20),url varchar(50),alexa int,country varchar(20)); Query OK, 0 rows affected (0.17 sec) * * mysql> insert into web -> select * from website; Query OK, 6 rows affected (0.06 sec) Records: 6 Duplicates: 0 Warnings: 0 * * mysql> select * from web; +------+----------+-------------------------+-------+---------+ | 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 | +------+----------+-------------------------+-------+---------+ * * 【drop】:撤销索引，撤销表，撤销数据库 * 【drop index】删除表中的索引 * drop index index_name on table_name;//MS Access * drop index table_name.index_name;//MS SQL * drop index index_name;//DB2/ORACLE * alter table table_name drop index index_name;//MySQL * * 【drop table】删除表 * drop table table_name; * * mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | access | | app | | newtable | | web | | website | | websites | +----------------+ mysql> drop table newtable; mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | access | | app | | web | | website | | websites | +----------------+ * * 【drop database】删除数据库 * drop database database_name; * * mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | data1 | | data2 | | my_db | | mysql | | performance_schema | | test | +--------------------+ mysql> drop database my_db; mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | data1 | | data2 | | mysql | | performance_schema | | test | +--------------------+ * * 【truncate table】删除表中的数据，但不删除表本身 * truncate table table_name; * * mysql> select * from websites; +------+----------+-------------------------+-------+---------+ | 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 | +------+----------+-------------------------+-------+---------+ mysql> truncate table websites; mysql> select * from websites; Empty set (0.00 sec) *【alter table】:在已有的表中添加、删除或修改列 * ★★★★★★★ * 【添加列】： * alter table table_name * add column_name datatype; * * 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 | +------+----------+-------------------------+-------+---------+ mysql> alter table website -> add date date; Query OK, 6 rows affected (0.35 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from website; +------+----------+-------------------------+-------+---------+------+ | id | name | url | alexa | country | date | +------+----------+-------------------------+-------+---------+------+ | 1 | Google | http://www.google.com | 1 | USA | NULL | | 2 | taobao | http://www.taobao.com | 13 | CN | NULL | | 3 | cainiao | http://www.runoob.com | 673 | CN | NULL | | 4 | weibo | http://weibo.com | 20 | CN | NULL | | 5 | Facebook | http://www.facebook.com | 3 | USA | NULL | | 7 | ali | http://www.ali.com | 32 | CN | NULL | +------+----------+-------------------------+-------+---------+------+ * * 【删除列】： * alter table table_name * drop column column_name; * * mysql> alter table website -> drop date; Query OK, 6 rows affected (0.24 sec) Records: 6 Duplicates: 0 Warnings: 0 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 | +------+----------+-------------------------+-------+---------+ * * 【修改列】： * 【SQL/MS Access】 * alter table table_name * alter column column_name datatype; * 【MySQL/Oracle】 * alter table table_name * modify column column_name datatype; * * *【SQL约束】：用于规定表中的数据规则 * 如果存在违反约束的数据行为，行为会被约束终止。 约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。 * * 在创建表的时候进行约束定义： * create table table_name( * column_name_1 data_type(size) constaint_name; * column_name_2 data-type(size) constaint_name; * column_name_3 data_type(size) constaint_name; * ); * * 约束包括以下几点： * not null:说明某列元素不能为空 * unique:保证某列的每行必须有唯一的值 * primary key: 主键，not null和unique的结合，保证实体完整性 * foreign key：外键，保证参照完整性 * check：保证列中的值符合指定的条件 * default：规定没有给列赋值时的默认值 * * 【not null】 * mysql> create table persons(id int not null,name varchar(20),address varchar(20),city varchar(20)); * * mysql> insert into persons -> (name) values ('mary'); ERROR 1364 (HY000): Field 'id' doesn't have a default value //必须给id赋值，id不能为空，约束条件为not null mysql> insert into persons -> (id,name,address,city) -> values (1,'mary','xian','changan'); mysql> select * from persons; +----+------+---------+---------+ | id | name | address | city | +----+------+---------+---------+ | 1 | mary | xian | changan | +----+------+---------+---------+ * * 【unique】 * mysql> create table person01(id int not null,name varchar(20) unique,address varchar(20),city varchar(20)); * * mysql> insert into person01 (id,name,address,city) values (1,'mary','xian','changan'); * * mysql> insert into person01 (id,name,address,city) values (2,'mary','ningxia','yinchuan'); ERROR 1062 (23000): Duplicate entry 'mary' for key 'name' mysql> insert into person01 (id,name,address,city) values (1,'mary','ningxia','yinchuan'); ERROR 1062 (23000): Duplicate entry 'mary' for key 'name' mysql> insert into person01 (id,name,address,city) values (2,'Lily','ningxia','yinchuan'); Query OK, 1 row affected (0.03 sec) * * MySQL: SQL Server / Oracle / MS Access ： * CREATE TABLE Persons CREATE TABLE Persons ( ( P_Id int NOT NULL, P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), FirstName varchar(255), Address varchar(255), Address varchar(255), City varchar(255), City varchar(255), UNIQUE (P_Id) ); ); * * 如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法: * MySQL / SQL Server / Oracle / MS Access： * CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) ); * * 撤销unique： * alter table website * drop index id; * * 【primary】主键，主键必须包含唯一的值，主键列不能包含null值，每个表都应该有一个主键，而且每个表只能有一个主键 * * MySQL: SQL Server / Oracle / MS Access： * CREATE TABLE Persons CREATE TABLE Persons ( ( P_Id int NOT NULL, P_Id int NOT NULL PRIMARY KEY, LastName varchar(255) NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), FirstName varchar(255), Address varchar(255), Address varchar(255), City varchar(255), City varchar(255), PRIMARY KEY (P_Id) ); ); * * 当表已经创建了，再继续添加主键primary key: 需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法： * MySQL / SQL Server / Oracle / MS Access： * alter table website alter table website * add primary key(id); add constraint * * mysql> desc person; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | LastName | varchar(10) | YES | | NULL | | | FirstName | varchar(10) | YES | | NULL | | | Address | varchar(50) | YES | | NULL | | | City | varchar(20) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ * * mysql> alter table person -> add primary key (id); Query OK, 0 rows affected (0.22 sec) * * mysql> desc person; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | LastName | varchar(10) | YES | | NULL | | | FirstName | varchar(10) | YES | | NULL | | | Address | varchar(50) | YES | | NULL | | | City | varchar(20) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ * * mysql> alter table person -> drop primary key; Query OK, 3 rows affected (0.27 sec) * * mysql> desc person; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | LastName | varchar(10) | YES | | NULL | | | FirstName | varchar(10) | YES | | NULL | | | Address | varchar(50) | YES | | NULL | | | City | varchar(20) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ * * 【foreign】:一个表中的foreign key 指向另一个表中的primary key * MySQL： SQL Server / Oracle / MS Access： * CREATE TABLE Orders CREATE TABLE Orders ( ( O_Id int NOT NULL, O_Id int NOT NULL primary key, OrderNo int NOT NULL, OrderNo int NOT NULL, P_Id int, P_Id int foreign key references Persons(P_Id) PRIMARY KEY (O_Id), ); FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) ); * * 当 "Orders" 表已被创建时，如需在 "P_Id" 列创建 FOREIGN KEY 约束，请使用下面的 SQL： * MySQL / SQL Server / Oracle / MS Access： MySQL / SQL Server / Oracle / MS Access： * ALTER TABLE Orders ALTER TABLE Orders ADD FOREIGN KEY (P_Id) ADD FOREIGN fk_PerOrders REFERENCES Persons(P_Id) FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) * * 撤销foreign key约束条件： * MySQL: SQL Server / Oracle / MS Access： * ALTER TABLE Orders ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders DROP constraint KEY fk_PerOrders * * 【check】:用于限制列中值的范围 * 如果对单个列定义check约束，那么该列只允许特定的值 * 如果对一个表定义check约束，那么词约束会基于行中其他列的值在特定的列中队值进行限制 * MySQL： SQL Server / Oracle / MS Access： * CREATE TABLE Persons CREATE TABLE Persons ( ( P_Id int NOT NULL, P_Id int NOT NULL CHECK (P_Id>0), LastName varchar(255) NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), FirstName varchar(255), Address varchar(255), Address varchar(255), City varchar(255), City varchar(255), CHECK (P_Id>0) ); ); * * 需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法： * MySQL / SQL Server / Oracle / MS Access： * CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') ) * * 当表已被创建时，如需在 "P_Id" 列创建 CHECK 约束，请使用下面的 SQL： * MySQL / SQL Server / Oracle / MS Access: * ALTER TABLE Persons ADD CHECK (P_Id>0) * 如需命名 CHECK 约束，并定义多个列的 CHECK 约束，请使用下面的 SQL 语法： * MySQL / SQL Server / Oracle / MS Access： * ALTER TABLE Persons ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes') * * 撤销 CHECK 约束： * SQL Server / Oracle / MS Access： MySQL： * ALTER TABLE Persons ALTER TABLE Persons DROP CONSTRAINT chk_Person DROP CHECK chk_Person * * 【default】:用于向列中插入默认值 * My SQL / SQL Server / Oracle / MS Access： 通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值： * CREATE TABLE Persons CREATE TABLE Orders ( ( P_Id int NOT NULL, O_Id int NOT NULL, LastName varchar(255) NOT NULL, OrderNo int NOT NULL, FirstName varchar(255), P_Id int, Address varchar(255), OrderDate date DEFAULT GETDATE() City varchar(255) DEFAULT 'Sandnes' ) ) * * mysql> create table person01(id int,name varchar(20),address varchar(20),city varchar(20) default 'shanghai'); * * mysql> insert into person01 (id,name,address) values (1,'Lily','xian'); * * mysql> select * from person01;//city被默认设置为shanghai +------+------+---------+----------+ | id | name | address | city | +------+------+---------+----------+ | 1 | Lily | xian | shanghai | +------+------+---------+----------+ * * 当表已被创建时，如需在 "City" 列创建 DEFAULT 约束，请使用下面的 SQL： * MySQL： SQL Server / MS Access： Oracle： * ALTER TABLE Persons ALTER TABLE Persons ALTER TABLE Persons ALTER City SET DEFAULT 'SANDNES' ALTER COLUMN City SET DEFAULT 'SANDNES'MODIFY City DEFAULT 'SANDNES' * * 撤销 DEFAULT 约束： * MySQL： SQL Server / Oracle / MS Access： * ALTER TABLE Persons ALTER TABLE Persons ALTER City DROP DEFAULT ALTER COLUMN City DROP DEFAULT * * mysql> alter table person01 -> alter date set default '2016-3-2'; mysql> select * from person01; +------+------+---------+----------+------------+ | id | name | address | city | date | +------+------+---------+----------+------------+ | 1 | Lily | xian | shanghai | NULL| | 2 | Mary | beijing | shanghai | 2016-03-02 | +------+------+---------+----------+------------+ * *【create index】:用于在表中创建索引 * 在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。 * 索引：可以在表中创建索引，以便更加快速高效的查询数据 * 用户无法看到索引，它们只能被用来加快搜索/查询 * 注意：更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。 * 因此，理想的做法是仅仅在常常被索引的列上面创建索引。 * * 在表上创建一个简单的索引。允许使用重复的值： 在表上创建一个唯一的索引。不允许使用重复的值： * CREATE INDEX index_name 唯一的索引意味着两个行不能拥有相同的索引值。 ON table_name (column_name) CREATE UNIQUE INDEX index_name * ON table_name (column_name) * * *【auto-increment】:会在新纪录插入表中时生成一个唯一的数字 * 每次插入新记录时，自动地创建主键字段的值 * * SQL 语句把 "Persons" 表中的 "ID" 列定义为 auto-increment 主键字段 * CREATE TABLE Persons ( ID int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (ID) ) * * mysql> create table person03(id int not null AUTO_INCREMENT,name varchar(20),primary key(id)); Query OK, 0 rows affected (0.10 sec) * 注意：为属性设置auto-increment时，必须是设置主键，自增是针对于主键而言的。 * mysql> insert into person03 (name) value ('lily'); * mysql> insert into person03 (name) value ('mary'); * mysql> insert into person03 (name) value ('gass'); * * mysql> select * from person03; +----+------+ | id | name | +----+------+ | 1 | lily | | 2 | mary | | 3 | gass | +----+------+ * *【views】视图：视图是可视化的表 * 创建、更新和删除视图 * 注释：视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据。 * * 创建视图： * CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition * * 更新视图： * CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition * * 撤销视图： * DROP VIEW view_name * *【date】日期函数： * NOW() 返回当前的日期和时间 * CURDATE() 返回当前的日期 * CURTIME() 返回当前的时间 * DATE() 提取日期或日期/时间表达式的日期部分 * EXTRACT() 返回日期/时间的单独部分 * DATE_ADD() 向日期添加指定的时间间隔 * DATE_SUB() 从日期减去指定的时间间隔 * DATEDIFF() 返回两个日期之间的天数 * DATE_FORMAT() 用不同的格式显示日期/时间 * * mysql> select now(); +---------------------+ | now() | +---------------------+ | 2016-07-14 14:49:07 | +---------------------+ mysql> select curdate(); +------------+ | curdate() | +------------+ | 2016-07-14 | +------------+ mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 14:49:53 | +-----------+ * *【null】: * IS NULL: * SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NULL * * IS NOT NULL: * SELECT LastName,FirstName,Address FROM Persons WHERE Address IS NOT NULL * *【SQL ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数】：都是判断表中是否为null,若为null，则返回1，若不空，则返回0 * 注意：在不同的数据库中，使用不同的函数 * SQL Server / MS Access：isnull() * Oracle：nvl() * MySQL：ifnull()/coalesce() * * mysql> select * from cal; +----+------+-------+--------+------+ | id | name | first | second | sum | +----+------+-------+--------+------+ | 1 | lily | 42 | 3 | NULL | +----+------+-------+--------+------+ * * mysql> select (first+second+isnull(sum)) as s from cal; +------+ | s | +------+ | 46 | +------+ * * mysql> select * from cal; +----+------+-------+--------+------+ | id | name | first | second | sum | +----+------+-------+--------+------+ | 1 | lily | 42 | 3 | NULL | | 2 | mary | 22 | 637 | NULL | | 3 | may | 32 | 43 | 75 | +----+------+-------+--------+------+ * * mysql> select (first+second+!isnull(sum)) as s from cal; +------+ | s | +------+ | 45 | | 659 | | 76 | +------+ * * mysql> select (first+second+isnull(sum)) as s from cal; +------+ | s | +------+ | 46 | | 660 | | 75 | +------+ * * *-------------------------------------------------MySQL函数--------------------------------------------------- * *SQL aggregate函数： * * avg():返回平均值 * 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 | +------+----------+-------------------------+-------+---------+ mysql> select avg(alexa) as avg_alexa -> from website; +-----------+ | avg_alexa | +-----------+ | 123.6667 | +-----------+ * * count():返回数目 * mysql> select count(id) as count -> from website; +-------+ | count | +-------+ | 6 | +-------+ * * first():返回第一个记录的值 //注释：只有 MS Access 支持 FIRST()函数 * * last():返回最后一个记录的值 //注释：只有 MS Access 支持 LAST() 函数 * * max():返回最大值 * mysql> select max(id) as max -> from website; +------+ | max | +------+ | 7 | +------+ mysql> select max(id) as max,name,url,alexa,country -> from website; +------+--------+-----------------------+-------+---------+ | max | name | url| alexa | country | +------+--------+-----------------------+-------+---------+ | 7 | Google | http://www.google.com |1 | USA| +------+--------+-----------------------+-------+---------+ * * min():返回最小值 * mysql> select min(id) as min -> from website; +------+ | min | +------+ | 1 | +------+ * * sum():返回总和 * mysql> select sum(alexa) as sum_alexa -> from website; +-----------+ | sum_alexa | +-----------+ | 742 | +-----------+ * * *SQL scalar函数： * ucase():大写转换 * mysql> select id,ucase(name),url,alexa,country -> from website; +------+-------------+-------------------------+-------+---------+ | id | ucase(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 | +------+-------------+-------------------------+-------+---------+ * * lcsse():小写转换 * mysql> select id,lcase(name),url,alexa,lcase(country) -> from website; +------+-------------+-------------------------+-------+----------------+| id| lcase(name)| url| alexa| lcase(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 | +------+-------------+-------------------------+-------+----------------+ * * mid():从文本中提取字符 格式：mid(column_name,start,length) * mysql> select mid(name,1,4) as mid -> from website; +------+ | mid | +------+ | Goog | | taob | | cain | | weib | | Face | | ali | +------+ * * len():返回长度 格式：length(column) * mysql> select id,name,length(url),alexa,country -> from website; +------+----------+-------------+-------+---------+ | id | name | length(url) | alexa | country | +------+----------+-------------+-------+---------+ | 1 | Google | 21 | 1 | USA | | 2 | taobao | 21 | 13 | CN | | 3 | cainiao | 21 | 673 | CN | | 4 | weibo | 16 | 20 | CN | | 5 | Facebook | 23 | 3 | USA | | 7 | ali | 18 | 32 | CN | +------+----------+-------------+-------+---------+ * * round():指定小数的四舍五入 格式：SELECT ROUND(column_name,decimals) FROM table_name; * mysql> select round(1.3234); +---------------+ | round(1.3234) | +---------------+ | 1 | +---------------+ mysql> select round(23.43434,3); +-------------------+ | round(23.43434,3) | +-------------------+ | 23.434 | +-------------------+ * * now():返回当前的系统日期和时间 格式：SELECT NOW() FROM table_name; * mysql> select now(); +---------------------+ | now() | +---------------------+ | 2016-07-13 22:56:04 | +---------------------+ * * mysql> select id,name,url,now() as date -> from website; +------+----------+-------------------------+---------------------+ | id | name | url | date | +------+----------+-------------------------+---------------------+ | 1| Google | | 2016-07-13 22:57:30 | | 2 | taobao | http://www.taobao.com | 2016-07-13 22:57:30 | | 3 | cainiao | http://www.runoob.com | 2016-07-13 22:57:30 | | 4 | weibo | http://weibo.com | 2016-07-13 22:57:30 | | 5 | Facebook | http://www.facebook.com | 2016-07-13 22:57:30 | | 7 | ali | http://www.ali.com | 2016-07-13 22:57:30 | +------+----------+-------------------------+---------------------+ * * format():格式化某个字段的显示方式 格式：SELECT FORMAT(column_name,format) FROM table_name; * * * group by:用于结合聚合函数，根据一个或多个列对结果进行分组 * SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name; * * mysql> select site_id, sum(count) as sum -> from access -> group by site_id; +---------+------+ | site_id | sum | +---------+------+ | 1 | 275 | | 2 | 10 | | 3 | 521 | | 4 | 13 | | 5 | 751 | +---------+------+ * * * having子句：在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。 HAVING 子句可以让我们筛选分组后的各组数据。 * SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value; * * mysql> select site_id,sum(count) -> from access -> group by site_id -> having sum(count)>100; +---------+------------+ | site_id | sum(count) | +---------+------------+ | 1 | 275 | | 3 | 521 | | 5 | 751 | +---------+------------+ * * */ System.out.println("10h over"); System.out.println("终于敲完了！"); } }
以上就是 【mysql 01】查询–总结的内容，更多相关内容请关注PHP中文网（www.php.cn）！
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
