数据库的操作

操作数据库

连接数据库

mysql -uroot -p

退出数据库

exit
quit

查看已创建的数据库

show databases;

查看数据库版本

select version;

创建数据库

create database 库名;
create database test;

查看创建数据库的命令

show create database 数据库名;
show create database test;

查看当前使用的数据库

select database();

使用数据库

use 数据库名;
use test;

删除数据库

drop database 数据库名；
drop database test;

操作数据表

查看当前数据库中的所有表

show tables;

创建表

create table 表名(字段 类型及约束条件);
create table students (
sid int primary key auto_increment,
sname varchar(20) not null,
gender enum("male","female"),
brith varchar(10)
)engine=INNODB default charset=utf8;

查看表的创建语句

show create table 表名;
show create table students;

查看表描述信息

desc 表名;
desc students;

添加表字段

alter table 表名 add 字段 类型及约束条件;
alter table students add high float default 155;

修改表字段

alter table 表名 modify 字段 类型及约束条件;(用于修改字段的类型和约束条件，不可修改字段)
alter table students modify high int;

alter table 表名 change 旧字段 新字段 类型及约束条件;(可修改字段及约束条件)
alter table students change high h int;

删除表字段

alter table 表名 drop 字段;
alter table students drop h;

操作数据

新增数据

1.整行插入
insert into 表名 values (数据);
insert into students values (1,"lh","male","1996-11-02")

2.指定列中插入数据
insert into 表名 (字段1，字段2) values ('数据1','数据2')

3.指定列中插入多条数据
insert into 表名  (字段1,字段2) values (数据1,数据2),（数据3,数据4）
insert into students vaules (3,"feng","male","1990-05-20"),(4,"yun","female","1990-08-16"),(5,"lei","male","1990-12-21");

修改数据

update 表名 set 字段1=数据1,字段2=数据2;
update students set sname="ida";

删除数据

删除表中全部数据
delete from 表名 ；
delete from students； 

删除表中指定数据
delete from 表名 where 条件;
delete from students where sid=5;

逻辑删除：在表中增加字段，若表示删除改数据则修改

alter table students add is_del enum("0","1") default "0";
update  students ser is_del="1";  

查询数据

查询整个表数据

select * from 表名;
select * from students;

查询指定字段数据

select 字段 from 表名
select sname from students;

查询指定字段数据，并给字段起别名

select 字段 别名 from 表名;
select sname n from students ;

查询指定字段并去重

select distinct 字段1 from 表名;
select distinct sname from students;

where子句

where子句通常结合增删改查使用，用于做筛选的条件。

select * from students where sid=1;

比较运算符

运算符
=	等于
>	大于
>=	大于等于
<	小于
<=	小于等于
!=或<>	不等于

select * from students where gender="male";

逻辑运算符

运算符
and
or
not

模糊查询

like关键字用来进行模糊查询，并且结合%以及_使用。
• % 表示任意多个任意字符
• _ 表示一个任意字符

查询姓名中l字母开头的学生信息
select * from students where sname like "l%";

查询姓名中带有a的学生信息
select * from students where sname like "%a%";

查询姓名中只有三位的学生信息
select * from students where sname like "___";

范围查询

关键字
in
between … and

查询id为1，3，5的学生信息
select * from students where sid in (1,3,5);
查询id在3-5的学生信息
select * from students where sid between 3 and 5 ;

空判断

关键字
is null	空
is not null	非空

查询没有生日的学生信息
select * from students where brith is null;

常用聚合函数

count总数

求表中的总人数
select count(*) from students ;
求男性人数
select count(*) from students where gender="male";

max()最大值

查询女性最大的id
select max(sid) from students where gender="female";

min()最小值

查询女性最小的id
select min(sid) from students where gender="female";

sum求和

求男性id和
select sum(sid) from students where gender="male";

avg平均值

求未删除男性的id的平均值,保留一位小数
select round(avg(sid),1) from students where is_del="0";

分组与分组后的筛选

分组

在数据库中，通过 group by 将查询结果按照1个或多个字段进行分组，字段值相同的为一组.

select 字段 from 表名 goup by 字段
计算男女的总数
select gender,count(*) from students group by gender;

通过group_concat(…)查看每组的详细信息

select group_concat(sname,":",brith) from students ground by gender; 

分组后筛选使用having 条件

查询男生女生总数大于2的姓名
select group_concat(sname) from students group by gender having count(*)>2;

排序

order by 字段 默认升序
order by asc 字段 指定升序
order by desc 字段 指定降序

查询身高在170-180之间的男性，并高到低降序，当身高相同时，按照id从低到高升序。
select * from students where gender="male" and high between 170 and 180 order by high desc,sid asc;

限制 limit 只能写在句尾。

limit begin,length
begin为偏移量，起始位置。
length为条数。

查询4-6个学生信息
select * from students limit 3,4;

表连接

再创建一个sc表。
create table sc (
sid tinyint not null,
cid tinyint not null,
score int
) engine=innodb default charset=utf8;
向表sc中插入数据
insert into sc values (1,1,80),(1,2,90),(1,3,99),(2,1,70),(2,2,60),(2,3,80),(3,1,80),(3,2,80),(3,3,80);

内连接

select * from 表名1 别名1 inner join 表名2 别名2 on 别名1.字段=别名2.字段;
select * from students s inner join sc c on s.sid=c.sid;

当且仅当两个表中的数据都符合on后面的条件的时候,才会被select出来.

左连接

select * from 表名1 别名1 left join 表名2 别名2 on 别名1.字段=别名2.字段;
select * from students s left join sc c on s.sid=c.sid;

在查询的时候,以left join 这个关键字左边的表为主表,会将这个表中的数据全部查询出来,如果右表中没有这条数据,则用NULL字段表示.

右连接

select * from 表名1 别名1 right join 表名2 别名2 on 别名1.字段=别名2.字段;
select * from students s right join sc c on s.sid=c.sid;

在查询的时候,会以right join 这个关键字右边的表为主,然后将符合条件的查询出来,左表中没有的字段,使用NULL进行补充。

子查询

一条select语句结果作为另一条select语法一部分（查询条件，查询结果，表等）select …查询字段 … from … 表… where … 查询条件

查询身高最高的男性的姓名和生日
select sname,brith from students where high=(select max(high) from students) and gender="male";

可以将一条查询语句作为另外一个查询语句的条件和表,再次进行查询.

自关联

可以简单的理解为自己与自己进行连接查询。

外键

MySQL 外键约束（FOREIGN KEY）是表的一个特殊字段，经常与主键约束一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
主表删除某条记录时，从表中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。

定义外键时，需要遵守下列规则：

• 1.主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
• 2.必须为主表定义主键。
• 3.主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
• 4.在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
• 5.外键中列的数目必须和主表的主键中列的数目相同。
• 6.外键中列的数据类型必须和主表主键中对应列的数据类型相同。

创建表时设置约束条件

语法:
constraint 外键约束名 foreign key (字段1,字段2) references 主表名(字段1,字段2)
举例:
create table course(
id int primary key auto_increment,
name varchar(20),
sid int,
constraint fk foreign key (sid) references students(sid)
)engine=innodb default charset=utf8;

删除约束条件

语法：
alter table 从表名 drop foreign key 外键约束名;
例子：
alter table course drop foreign key fk;

修改表时添加外键约束

语法：
alter table 从表名 add constraint 外键约束名 foreign key (字段1,字段2) references 主表名(字段1,字段2)；
举例:
alter table course add constraint fk foreign key (sid) references students(sid);