（JAVA自学笔记六）数据库基本语法

配置环境变量
PATH C:\Program Files\MySQL\MySQL Server 5.7\bin
登录mysql
mysql -u root -p
建立数据库
create database sqltest;
打开数据库
use sqltest;
设置编码
set names gbk;

建表语句
例一
DROP TABLE IF EXISTS `music`;
CREATE TABLE `music` (
  `mu_id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `singer` varchar(50) DEFAULT NULL,
  `size` varchar(50) DEFAULT NULL,
  `duration` time DEFAULT NULL,
  `sound` varchar(50) DEFAULT NULL,
  `picture` varchar(50) DEFAULT NULL,
  `recommend` int(11) DEFAULT NULL,
  `playcount` int(20) DEFAULT '0',
  PRIMARY KEY (`mu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;
例二
CREATE TABLE Vendors (
	-- 供应商ID：主键列，自增长
	vend_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '供应商ID',
	-- 供应商名：可变字符，非空
	vend_name VARCHAR (30) NOT NULL COMMENT '供应商名',
	-- 供应商地址
	vend_address VARCHAR (100) NOT NULL COMMENT '地址',
	-- 供应商城市
	vend_city VARCHAR (20) NOT NULL COMMENT '城市',
	-- 供应商州
	vend_state VARCHAR (20) NOT NULL COMMENT '州',
	-- 供应商邮编
	vend_zip VARCHAR (20) NOT NULL COMMENT '邮编',
	-- 供应商国家
	vend_country VARCHAR (20) NOT NULL COMMENT '国家'
);

查询单表所有信息
select * from product;
删除
delete from product where pid='p001';
修改
update product set 字段='',...... where 条件;
添加
inset into product values('',''......);按顺序
inset into product(字段) values('',''......);可不按顺序

查看表结构
desc product;
添加外键
alter table product add foreign key(category_id) references category(cid);
带名字的外键（删除用）
alter table orderitem add constraint orderitem_orders_fk foreign key(oid) references orders(oid);
删除外键
alter table 从表 drop foreign key 描述外键名 ，这个删除是要基于你的从表有对外键起名字的前提下，你在建从表的时候外键就应该这样 constraint 描述外键名 foreign key (xxx_id)references 主表(id)，这样这个删除才有用


关系
一对多
把一方主键放到多方里做外键
多对多
产生一张新表，把双方的主键放到新表中做外键和联合主键
一对一
合成一张表，或用外键，或主键对应（了解）

案例

多表查询

例
榜单表和歌曲表多对多，产生一张对应表，通过给出歌曲名来查询在哪个榜单里
此方法为连接
SELECT r.rname from music m,rank r,ranktomusic rtm where r.ra_id=rtm.ra_id and m.mu_id=rtm.mu_id and m.name='光年之外';
此方法为子查询
select rname from rank where ra_id in(select ra_id from ranktomusic where mu_id in(select mu_id from music where name='光年之外'));


交叉连接查询语法
select * from product,category;
做笛卡儿积，显示所有情况

内连接

显式
select * from category inner join product on cid=category_id;

隐式
select * from category，product where cid=category_id;

外连接

左外
select * from category left join product on cid=category_id;
右外
select * from category right join product on cid=category_id;

内外连接区别

子查询
将一条sql语句结果作为另一条的一部分（查询条件，查询结果，表等）

连接和子查询区别：

总结：连接用的多
表连接都可以用子查询，但不是所有子查询都能用表连接替换
子查询比较灵活，方便，形式多样，适合用于作为查询的筛选条件
而表连接更适合与查看多表的数据
子查询不一定需要两个表有关联字段，而连接查询必须有字段关联（所谓的主外键关系）

其他

分页查询
分页查询使用的是limit关键字进行查询。它后面有两个参数。第一个参数是起始的位置，第二个参数是每页需要显示的条目数。

举例：商品表中有10条记录，现在需要进行分页显示，每页显示3条数据。现在需要查看第二页的数据。那么应该使用的sql语句是：
	select * from product limit 3,3;