数据库之基本命令和注意点

// 创建数据库
create database 数据库名字;
// 创建数据库并指定字符集
create database 数据库名字 character set 字符集;
// 创建数据库并指定字符集并指定校对规则//校对规则中会认为两个不同字符等价，如A和a一样大，两个不同特殊字符一样大
create database 数据库名字 character set 字符集 collate 校对规则;
// 查看所有创建的数据库
show databases;
// 查看已经创建的数据库的定义
show create database 数据库名字;
// 修改数据库的字符集
alter database 数据库名字 character set 字符集;
// 删除数据库
drop database 数据库名字;
// 切换数据库
use 数据库名字;
//查看一下当前正在使用的数据库
select database();//使用一个函数
//创建表
create table 表名(
    列名 列的类型(长度) 约束，
    ...
);
//列的类型注意
char(3)//肯定占用3个字节，没有补空格
varchar(3)//占用空间可以小于3个字节
date:YYYY-MM-DD
time:hh:mm:ss
datetime:YYYY-MM-DD hh:mm:ss//默认使用null
timestamp:YYYY-MM-DD hh:mm:ss//默认使用当前时间 
text//用来存放文本
blob//用来存放二进制

//列的约束
主键约束：primary key
唯一约束：unique
非空约束：not null

// 查看表
show tables;
// 查看表的定义语句
show create table 表名;
//查看表的结构
desc 表名

//修改表---------------------------------
alter table 表名 add 列名 列的类型 列的约束;//添加列
alter table 表名 modify 列名 修改信息;//修改列的信息
alter table 表名 change 列名 新列名 列的信息;//修改列名
alter table 表名 drop 列名;//删除列
alter table 表名 character set 字符集;//修改表的字符集
rename table 表名 to 新表名;//修改表名
drop table 表名;//删除表


//增删改查--------------------
insert into 表名(列名1，列名2) values(值1，值2),(),();//插入数据
insert into values(值1，值2),(),();//在插入全列的值时可以简写
delete from 表名 where 条件//删除数据，不加条件全部删除
--说明delete删除数据和truncate删除数据的差别
delete:DML一条一条删除数据
truncate:DDL先删除表再重建表
-哪一个执行效率高
如果数据量比较少：delete比较高效
如果数据量比较大：truncate比较高效

update 表名 set 列名1=列的值，列名2=列的值 where 条件//更新表中的数据
                            //列的值，字符串和日期加引号（单或双），数字不用
                            
select [distinct][*][列名1，列名2] from 表名 [where 条件]//distinct：去除重复数据                            
select 列名1 as 别名1，列名2 as 别名2 from 表名;//将查出来的列另起名字，易于理解

where后面的关系运算符：
    <>:不等于：标准sql语法
    !=：不等于：非标准sql语法
    两个条件用and,or,not连接
    定义区间用between...and...
    
like模糊查询(where name like '%tao%')
    _代表一个字符
    %代表多个字符

in在某个范围内查找值(where age in (20,21,23))
排序查询(where order by price asc/desc)
-------------------------------------------------------------    

show variables names like 'char%';
character_set_client：主要用来设置客户端使用的字符集。
character_set_connection：主要用来设置连接数据库时的字符集，如果程序中没有指明连接数据库使用的字符集类型则按照这个字符集设置。
character_set_results：数据库给客户端返回时使用的编码格式，如果没有指明，使用服务器默认的编码格式。
character_set_server: mysql安装时指定的默认字符集.
character_set_database: mysql中某个数据库使用的字符集，如果创建库时没有指明字符集，将使用character_set_server的值默认设置。


---------------------------------------------------------
//聚合函数
sum()
avg()
count()
max()
min()
select sum(price) from 表名;//其他类似
--注意where后面不能含有聚合函数
select * from product where price >avg(price);
//子查询(查询所有大于平均价格的商品)
select * from product where price >(select avg(price) from product);

//分组group by
1.根据type字段分组，分组后统计商品的个数
select type,count(*) from product group by type;
2.根据type分组，分组统计每组商品平均价格，并且商品平均价格>60
select type,avg(price) from product group by type having avg(price)>60(应该是既然分组了，所有函数作用在一组里)

1.having 关键字，后面可以接聚合函数，要在分组之后
2.where 关键字，后面不可以接聚合函数，要在分组之前

//为表添加外键约束
alter table 现在表名 add foreign key(外键名) references 关联表(关联表主键);
foreign key(外键名) references 关联表(关联表主键)//在create table时添加用这句
//如果表中有外键的值在关联表中没有，则这句话会添加失败
//如果关联完成后，插入外键的值再关联表中不存在，则插入失败

//若现在表使用外键约束关联关联表
如果想删除关联表中的一项，要先删除现在表中关联该项的记录







//建表原则
一对多：(一条记录对应多条记录：分类和商品)
    建表原则：在多的一方添加一个外键，指向‘一’的一方的主键

多对多：(学生和课程：一个学生对应多个课程，一个课程对应多个学生)
    建表原则：另外建一张连接表，有自己id,两个外键id
        连接表和学生是：一个学生对应多个连接表记录，一个连接表记录对应一个学生(即1对多)
        连接表和课程是：一个课程对应多个连接表记录，一个连接表记录对应一个课程(即1对多)

一对一：(公民和身份证)
    建表原则：随便一张表中加入另一个表的外键
            或者直接合并
            或者两张表对应主键一样，可以使用同样数值查询
            
//主键约束(primary key)和唯一约束(unique)的区别
主键约束：唯一，不能为空
        主键一张表只有一个
        外键都是指向另一张表的主键
唯一约束：唯一，可以为空
        唯一约束一张表可以给很多属性
        唯一约束不可以作为其他表的外键

        
//隐式内链接和显示内链接
select * from recordOne,recordTwo;//这是两个表的乘积，记录个数是两个表的个数的乘积(笛卡尔积)

--隐式内链接：
select * from recordOne as r1,recordTwo as r2 where r1.外键=r2.tid;//as可省略
select * from recordOne r1,recordTwo r2 where r1.外键=r2.tid;
--显示内链接
select * from recordOne r1 inner join recordTwo r2 on r1.外键=r2.tid;
//两种内链接的区别
--隐式内链接：在查询结果(乘积)的基础上，去做where条件过滤
--显示内链接：带着条件去查询结果，执行效率高


--左外连接：
select * from recordOne r1 left outer join recordTwo r2 on r1.外键=tid;
查询结果是左表内容肯定有，右表中如果没有对应数据，用NULL代替(左表外键为null,或者通过客户端软件插入的外键另一个表中没有--通过命令不会有这种情况)
--右外连接：
select * from recordOne r1 right outer join recordTwo r2 on r1.外键=tid;
查询结果是右表内容肯定有，如果左边没有对应内容，用NULL代替
//前面所有的inner和outer都可以省略

//分页查询
select *from 表 limit 0,3//第一次
select *from 表 limit 3,3//第二次
//第一个参数是开始索引，第二个是个数
startIndex=(页数-1)*3