数据库基础

注：同样，本文尚未完全整理，纯属占坑

1.数据库操作

1.创建数据库：create database databasename default charset utf8;
2.删除数据库：drop database databasename;
3.显示已有数据库：show databases;

2.表操作

1.创建表：
create table tablename(
id int not null auto_increment primary key,
name char(20) not null,
age int(10),
gender varchar(10) not null default 男
)engine=innodb default charset=utf8;
2.删除表：drop table tablename;
3.显示表：show tables;
4.清空表：delete from tablename;/truncate table tablename;
5.重命名表：rename table odlname to newname;

注释：
not null：设置该项不能为空
auto_increment：自增
primary key：设置为主键
default：默认设置为xxx
engine=innodb：利用引擎innodb支持事务回滚(比如转账业务，如果中断的话可以将钱转回原账户)

其他：
1.在mysql中默认不支持事务回滚，如果想要的话，可以设置引擎为inodb,mysql中有两个引擎， 除了inodb还有myisam,myisam会比innodb存储速度更快，但为了支持事务，一般我们就用innodb，支持事务，原子性操作。
2.清空表的两个语句是有区别的：delete from tablename;清空表后从原来的数字之后自增，比如原来有五条数据，清空后从6开始自增。truncate table tablename;清空表后从1开始重新自增。

3.表内容操作

增
insert into tablename(name,age) values(‘诸葛亮’，42);

删
delete from tablename;
truncate table tablename;
delete from t1 where id<6;

改
updata t1 set age=18;
updata t1 set age=18 where age=17;

查
select * from tableneme;
select name from t1;
select id,name from t1;

4.数据类型

数字:int,tinyint,bigint
1.tinyint:小整数，数据类型用于保存一些范围的整数数值范围：
有符号：-128 ～ 127.
无符号：0 ～ 255

2.int:整数，数据类型用于保存一些范围的整数数值范围：
有符号：-2147483648 ～ 2147483647
无符号：0 ～ 4294967295

3.bigint:
大整数，数据类型用于保存一些范围的整数数值范围：
有符号：-9223372036854775808 ～ 9223372036854775807
无符号：0 ～ 18446744073709551615

4.decimal[(m[,d])]:
准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
对于精确数值计算时需要用此类型
decaimal能够存储精确值的原因在于其内部按照字符串存储。

5.float[(M,D)]:
单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
数值越大，越不准确

6.double[(M,D)]:
双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
数值越大，越不准确

字符串:char,varchar
1.char(m):
char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。
即使数据小于m长度，也会占用m长度

2.varchar(m):
varchar数据类型用于表示变长的字符串，可以包含最多达255个字符。

3.text
text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

4.mediumtext
范围：16 777 215 (2**24 − 1)字符。

5.longtext
范围：4 294 967 295 or 4GB (2**32 − 1)个字符。

注意：
char占用固定空间，容易浪费空间，但查询速度快;
varchar有多少占多少，不浪费，但查询速度慢;
速度快慢原因在于char占用固定空间，查询直接跳过固定空间往后查询，但varchar要慢慢遍历才行;
cahr和varchar没有说哪个更优，我们要学会搭配，将定长的如电话号码用char放在前列，不定长列用varchar放在后面(优化数据库时);

时间类型:data, time,datatime,year等
一般常用到的时间类型为datatime,其他做了解就行

集合，枚举:set,enum
1.set:
集合插入时从创建表时的指定字符中任意组合，不能是指定字符之外的组合，较少用

2.enum:
枚举插入时从创建表时的指定字符中任选其一，不能时之外的，比set常用

5.外键

外键作用：
1.节省空间，当数据量足够大时，用外键id来代替字符串会大大节省空间
2.增加约束，只能写外键表里面id

外键设置：constraint fk_user_depar foreign key (depar_id) reference department(id)

思考：当设置外键时，为什么要在外键depar_id上加()?是不是意味着我能够在括号内设置多个外键(即能否(depart_id,p_id)这样)？

主键：一个表只能有一个主键，但一个主键可以由多列组成
eg:
create table test1(id int not null auto_increment,
pid int not null,
name char(20) not null,
primary key (id,pid)
)engine=innodb default charset=utf8;

解答：若是常规的设置是不能的,但如果是如上面这样创建的主键是两列的话，那么设置外键时，就可以把那两列设置成外键，即：
create table test2(tid int not null auto_increment primary key,
tpid int not null,
name char(20) not null,
constraint fk_1_2 foreign key (tid,tpid) references test1(id,pid)
)engine=innodb default charset=utf8;

注意：foreign key的名字是不能重复的

一对一
一百个人中只有两个人是主管有权限登录管理系统，这个时候就不应该在用户表中设置一列只有那两个人有的登录密码了，而是新建一张管理员表，设置id自增列，设置user_id的外键，再设置一个密码列。
create table userinfo1(
id int auto_increment primary key,
name char(10),
gender char(10),
email varchar(64)
)engine=innodb default charset=utf8;

create table admin(
    id int not null auto_increment primary key,
    username varchar(64) not null,
    password VARCHAR(64) not null,
    user_id int not null,
    unique uq_u1 (user_id),
    CONSTRAINT fk_admin_u1 FOREIGN key (user_id) REFERENCES userinfo1(id)
)engine=innodb default charset=utf8;

一对多
在员工部门表中，一个部门对应很多个员工，而每个员工只有一个部门

多对多
相亲网站，一个人可以和多个人约会，双向的多对多就是多对多了
公司服务器，一个人管理多台机器，几个人共用一台机器
当多对多时，一般引出第三张表

在多对多中不一定要设置唯一索引，主要看需求

create table userinfo2(
    id int auto_increment primary key,
    name char(10),
    gender char(10),
    email varchar(64)
)engine=innodb default charset=utf8;

create table host(
    id int auto_increment primary key,
    hostname char(64)
)engine=innodb default charset=utf8;


create table user2host(
    id int auto_increment primary key,
    userid int not null,
    hostid int not null,
    unique uq_user_host (userid,hostid),
    CONSTRAINT fk_u2h_user FOREIGN key (userid) REFERENCES userinfo2(id),
    CONSTRAINT fk_u2h_host FOREIGN key (hostid) REFERENCES host(id)
)engine=innodb default charset=utf8;

自增

查看表创建语句
查看表的创建语句：desc test1;/show create table test1;/show create table test1 \G;

设置自增值
前面我们有提到，如果用truncate from test1;的话，会清空表内的数据，假设原来有五条数据，那么下次数据的自增会从6开始，那么我们可不可以手动设置自增的值比如下次从10开始呢？当然可以！方法如下：
设置下次自增的值:alter table test1 auto_increment=10;
当修改自增为10之后下次自增id为10，但下次再添加数据时自增id仍然按11开始。

查看设置步长
如果我们想要一个表的id每次自增的间隔为2，即(2,4,6,8,…)这样，怎么办？这里，我们就要用到步长了，不过很遗憾的是，mysql并不能像其他类似于sqlServer这样的给某个表单独的设置步长，不过mysql也提供了相应的会话级别和全局级别的步长，其中，会话级别的步长你可以理解为某个单独的cmd窗口，即只能在这个窗口上的表的步长才是你设置的步长。
查看步长：show session variables like ‘auto_inc%’;
设置会话步长：set session auto_increment_increment=2;

下面的了解即可：
查看全局步长：show global variables like ‘auto_inc%’;
设置全局步长：set global auto_increment_increment=2;
设置会话自增起始值：set session auto_increment_offset=10;
设置全局自增起始值：set global auto_increment_offset=10;

唯一索引

唯一索引具有不可重复性（1,2,3,4…），若为联合唯一索引，则两列共同组合不可重复(1 1,1 2,2 1,…)

作用：
1.约束不能重复
2.加速查找
主键也能起到相同的作用，主键和索引的区别为：主键不能为空，唯一索引可以为空

唯一索引：
create table t1(id int not null …,
num int,
unique uql (num)
)…

联合唯一索引：
create table t1(id int not null …,
num int,
xxx int,
unique uql (num,xxx)
)…

数据库-增

增加一行数据：
insert into test1(name,age) values(‘杨玉环’，25);

增加多行数据：
insert into test1(name,age) values(‘李白’，30)，（’奕星’，15);

从其他表中导入数据：
insert into test1(name,age) select name,age from test2; #从表test2中插入部分数据到表test1中

数据库-删

delete from test1;
delete from test1 where id=2;
delete from test1 where id!=2;
delete from test1 where id<=2;
delete from test1 where id>=2;
delete from test1 where id>=2 and age=18;
delete from test1 where id>=2 or age=18;

数据库-改

update test1 set name='花木兰' where id>3 and age=20;
update test1 set name='百里守约',age=22 where id>6 and age=25;

数据库-查

因为数据库的查较重要，故单开一节重点讲数据库的查询
select * from test1;

select id,name from test1;

select id,name from test1 where id>3 and age=18;

select id,name as cname from test1 where id>3 or age=18;

select id,name,11 from tset1;

其他：

1.条件

select * from test1 where id!=2;  <==>  select * from test1 where id <> 2;

select * from test1 where id in (1,5,10);

select * from test1 where id not in (1,5,10);

select * from test1 where id between 2 and 6; #闭区间

select * from test1 where name in (select id from test2);

2.通配符(模糊匹配)

select * from test1 where name like '李%'; #显示表test1中所有名字以'李'开头的人的数据

select * from test1 where name like '_星';

select * from test1 where name like '%木%'；

注释：%代表可以有任意个字符，代表只能有一个字符；如’李%’可以是李白，李云龙，李大本事；而’李‘只能是李白，李黑，李逵。

3.分页

select * from test1 limit 10; #取前十条数据

select * from test1 limit 2,4; #从2开始，往后取4条数据，即(3,4,5,6)；

select * from test1 limit 20,10; <==> 
select * from test1 limit 10 offset 20;

注释：(limit a,b;)中，是从a开始，往后取b条数据；(limit a offset b;)中，limit后面跟着的限制条数a意思为只取a条数据，offset后面跟着的起始条数b意思为从b后面一条开始取数据；

不能直接取后10条数据，只能先将顺序颠倒，再取前10条数据即为取后10条数据；

4.排序

select * from test1;

顺序排序：select * from test1 order by id asc;

逆序排序：select * from test1 order by id desc;

select * from test1 order by id desc,age asc; #根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

取后10条数据：select * from test1 order by id desc limit 10;

5.分组

聚合函数：count,max,min,sum,avg
select count(id),part_id from userinfo group by part_id;

select max(id),part_id from userinfo group by part_id;

对于聚合函数结果进行二次筛选时,必须使用having
select count(id),part_id from userinfo group by part_id having count(id)>10;

6.连表查询

select from admin,user where admin.user_id=user.id;
<==>
select from admin left join user on admin.user_id=user.id;

select * from user left join admin on admin.user_id=user.id; # user左边全部显示

select * from user left join admin on admin.user_id=user.id; # user右边全部显示

select * from user inner join admin on admin.user_id=user.id; # 将出现null时一行隐藏

select 
    score.sid,
    student.sid 
    from 
score

left join student on score.student_id = student.sid

left join course on score.course_id = course.cid

left join class on student.class_id = class.cid

left join teacher on course.teacher_id=teacher.tid

连表时where和left join的区别

在其他数据库中是有区别的，但在现版本的mysql中where和on基本一样。在测试过程中发现还是有些许区别的。如下：
创建一个部门表，创建一个员工表，在用where连表查询时发现，并不会按照employee1的id进行排序，而是按照department1的部门顺序(id)进行排序，看起来不习惯；而我们用left join查询时会发现，它是按照employee1的id进行排序的，更符合我们的观看习惯。

所以，在连表查询时，建议用left join 进行查询，如果非要用where并且还想要符合观看习惯的话，可以用

select * from employee1 left join department1 on employee1.part_id=department1.id order by employee1.id asc;

达到与

select *\ from employee1 left join department1 on employee1.part_id=department1.id;

相同的效果

注意

1.更新数据时是update 而不是updata;
2.设置外键时是references 而不是reference;
3.创建数据库时为default charset utf8;创建表时为default charset=utf8;