数据相关操作

数据库(database)的操作

1. 创建数据库

`create database [if not existst] 数据库名;` 创建一个名称为testdb的数据库create database testdb;创建一个使用gdk字符集的testdb数据库create database testdb character set gbk;

2. 查看数据库

显示所有数据库 `show databases;`显示某数据库创建信息:`show create database 数据库名;`显示当前数据库 `select database();`

3. 修改数据库

把已存在的数据库的字符集修改为utf8

alter database 数据库名 character set utf8;

4. 删除数据库

drop database testdb;

5. 使用数据库

`use 数据库名`

表(table)的操作

表的相关约束

创建表的相关完整性约束

1. 非空约束
语法：属性名 类型 not null

1. 唯一约束
   语法：属性名 类型 unique

2. 自增长
   语法：属性名 类型 auto_increment
   (要求：类型必须是整型)（注意：如果插入到最后一个标识为7，删除掉后这个”7”后，后续则从8开始）

3. 默认值
   语法：属性名 类型 default 默认值

1. 创建表 (field:指定列名,datatype:指定数据类型)

>
create table 表名 (
field1 field,
field2 datatype,
field3 datatype
) character set 字符集 collate 校对规则

2. 查看表

查看所有表 `show tables` 查看某个表结构`desc 表名`查看表创建语句 `show create table 表名`

3. 修改表

修改表的名称: `rename table 表名 to 新表名;`添加一列: `alter table 表名 add field datatype;`修改列名: `alter table 表名 change field newfield datatype;`修改列类型 `alter table 表名 modify field newdatatype;`删除列: `alter table 表名 drop 列名;`

4. 删除表

`drop table 表名;`

记录的操作 (一)

假设表

`create table students (    id int not null auto_increment primary key,    name varchar(20),    score int not );`

1. 添加一条数据(insert)

一次插入一条记录 `insert into students (id,name,score) values (null,'张三',90);`一次插入多条记录`insert into students values (null,'李莉',99),(null,'赵六',60);`

2. 修改(update)

将所有的学生分数修改为100 `update students set score=100;`将姓名为 李莉的成绩改为 90 `update students set score=90 where name='李莉';`将没有及格的学生设为60分 `update students set score=60 where score<60;`

3. 删除数据(delete)

删除表中名称为'张三'的记录 `delete from students where name='张三';`删除所有记录 `delete from students;`使用truncate删除表中记录 `truncate students;`

记录的操作 (二)

假设表

`create table students (    id int not null auto_increment primary key,    name varchar(20),    chinese int,    english int,    math int );`

1. 普通查询 select

查询所有的学生所有字段  `select * from students;`查询所有学生对应的数学和英语 `select name,math,english from students;`过滤表中的重复数据 `select distinct math from students;`所有学生的分数+10分显示 `select name, english+10,chinese+10,math+10 from students;`查询学生总分 `select name,english+chinese+math from students;`查询使用别名(as可省略) `select name as 姓名, english+chinese+math as 总分 from students;``select name 姓名,english+chinese+math 数学 from students;`

2. 条件查询

查询英语成绩大于90分的同学`select name,english from students where english>90;`查询总分大于270的同学 `select name,english+chinese+math 总分 from students where english+chinese+math>270;`数学成绩在80~100之间的同学 `select name, math from students where math>80 and math<100;``select name, math from students where math between 80 and 100;` 查询成绩在数学57,58,59,60的同学 `select name,math from students where math in (57,58,59,60);`查询数学成绩>90,英语<60的同学 `select * from students where math>90 and english<60;`

3. 模糊查询

查询名字中带有君的 `select * from students where name like '%君%';`查找名字叫张x的学生 `select * from students where name like '张_';`查找名字叫张xx的同学 `select * from students where name like '张__';`

4. 排序查询(order by column asc|desc)

按数学成绩高低排序 `select * from students order by math desc;` 按总分成绩由低到高 `select math+chinese+english 总成绩 from students order by math+chinese+english asc;`对姓张的学生排序输出 `select name 姓名, math+english+chinese 总成绩 from students where name like '张%' order by math+english+chinese;`

5. 分组查询 (group by):

_where 在分组之前进行过滤，不能使用聚合函数__having 在分组之后进行过滤，可以使用聚合函数,having中用到的列，必须在前面使用过才可以用_select id,name from students group by math;

7. 使用聚合函数

count :统计符合条件的记录个数

>
统计一个班级的共有多少学生
select count(*) from students;
统计数学及格的学生个数
select count(*) from students where math>=60;


sum : 返回满足条件的,列对应数据的总和
>
统计一个班级的数学总成绩
select sum(math) from students;
统计班级各科总成绩:
select sum(math),sum(english),sum(chinese) from students;


avg : 返回一列的平均值
>
求一个班级数学平均分
select avg(math) from students;
求一个班级总分的平均分
select avg(math+english+chinese) from students;
select avg(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from students;


max/min : 函数返回满足where条件的列最大值/最小值

    `班级数学最高分    select max(math) from students;    班级成绩的最高分    select max(math)+max(chinese)+max(english) from students;    `

数据库备份

• 备份数据库:

  `mysql dump -u root -p 数据库名>D:/file/test.sql`

• 恢复数据库:

  mysql -u root -p 数据库名<D:/file/test.sql
使用当前数据下,source D:/file/test.sql

多表设计

• 外键约束:明确表与表中,列的关系,从而使数据库维护这种关系,保证数据数据的正确性和完整性;
  

/*创建数据库*/
create database if not exists customDB;
use customDB;
/*创建顾客表*/
create table if not existst customer(
c_id char(6) primary key,
name varchar(30) not null,
location varchar(30),
salary decimal(8,2)
);
/*创建银行表*/
create table if not exists bank(
b_id char(5) primary key,
bank_name char(30) not null
);
/*创建存款表*/
create table if not exists deposite(
d_id int(10) auto_increment primary key,
c_id char(6),
b_id char(5),
dep_date date,
amount decimal(8,2),
constraint FK_c_id foreign key(c_id) references customer(c_id)
);
alter table deposite add constraint FK_b_id foreign key(b_id) references bank(b_id);


多表查询

内连查询左外连接右外连接全连接

子查询

索引