多表设计与基本sql语法

经验查询:
在日后的开发之中，很多人都肯定要接触到许多新的数据库和数据表，那么在这种时候有两种做法；
做法一：新手做法，上来就直接输入以下的命令
select * from 表名称;
如果此时数据量较大的话，以上无法浏览数据，二有可能造成系统的死机；
做法二：老手做法，先看一下有多少条记录
select count（*） from 表名称；

SQL分类      

       数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等

       数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等

       数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。如grant，revoke，if…else，                       while，begin transaction

       数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等

 

库的操作

创建库：create database 库名 character set 编码表;

删除库：drop database 库名;

查询库：show databases;

查看库的编码表：show create database 库名;

更改库：use 库名;

查看当前正在使用的库：select database();

修改库的编码表：alter database 库名 character set 编码表;

表本身的操作

创建表：create table 表名( 列名 列的类型(长度)  类的约束 ,列名 列的类型(长度)  类的约束...... );

删除表：drop table 表名;

查询表：show tables;

查看表的结构：desc 表名;

查看表的编码表：show create table 表名;

修改表：alter table 表名 增/删/改 列名 列的类型(长度) 约束;

add/drop/change/modify

修改表名：rename table 旧表名 to 新表名；

表中数据的操作

增：insert into 表名(列名) values(值);

删：delete from 表名 where 条件;  truncate

改：update 表名 set 列名=值 ，列名=值 where 条件 ；

查：select 列名 as 别名 ,列名 as 别名… from 表名  where 条件 group by 列名 having 条件 order by 排序.

查询排重：select distinct 列名 from 表名 where 条件;

聚合函数：

count 统计个数、sum求和、avg 平均值、max、min

在使用这几个函数进行数据的统计分析时，有时需要对数据表中的列进行数据的分组处理。group by

分组 group by :

排序：order  by 列名  asc | desc；

1. 多表设计

表与表之间可以总结出如下关系：

 

1. 1. 一对多

【案例】

表设计：记录部门中的员工信息

需求分析：

员工表和部门表

分析实体类的属性

分析实体类之间的关系

分析外键如何设置

步骤一：建表

1. 创建数据库，并切换到该数据库

1. 在创建的数据库中建表

create table dept(

       id int primary key auto_increment,

       name varchar(20)

);

create table employee(

       id int primary key auto_increment,

       name varchar(20),

       age int ,

       salary double

);

步骤二：添加数据

/*给部门表添加数据*/

insert into dept values(null,'人事部');

insert into dept values(null,'财务部');

insert into dept values(null,'公关部');

insert into dept values(null,'总经理办公室');

/*给雇员表添加数据*/

insert into employee values(null,'小乔',18,10000);

insert into employee values(null,'大乔',19,10000);

insert into employee values(null,'曹操',20,12000);

insert into employee values(null,'周瑜',21,13000);

insert into employee values(null,'刘备',22,14000);

步骤三：添加关系

分析：部门表和员工表目前都是独立的存在，无法知道这些员工是属于哪个部门，因此员工表内的信息还存在着一些缺陷。

解决方案：可以在员工表中添加一列部门编号用来标识设置该员工所属的部门，如下：

通过上图分析，可以在多方表中添加一列来设置一对多的关系。

/*employee表中添加一个dept_id列*/

alter table employee add dept_id int;

 

update employee set dept_id =1 where id = 1;

update employee set dept_id =1 where id = 2;

update employee set dept_id =2 where id = 3;

update employee set dept_id =3 where id = 4;

update employee set dept_id =4 where id = 5;

结果：

1. 1. 外键约束

需求：人事部解散，将人事部删除。

【示例】

在部门表中删除关于人事部的记录

删除人事部的记录

分析：如果一个部门被删除，那么该部门中的工作人员就应该另有安排，不应该还挂在人事部下。所以，在删除一个部门之前，应该先对原人事部员工进行安排，而不应该随便就能删除一个部门。因此需要给删除一个部门添加一些限制，比如，在没有安排原人事部员工之前，无法删除部门。

以上给删除部门添加的限制，就可以称之为 `外键约束`。

部门表和员工表之间的关系

主表： 被引用字段的那个表---部门表（一方）

从表： 引入字段的表 --- 员工表(多方)

添加外键需要注意的问题

1. 如果从表要去添加一个外键约束。要求主表被引用的字段是主键或者唯一的。通常使用主键。
2. 如果要删除主表中的数据。要求在从表中这个数据，要没有被引用，才可以去删除。
3. 如果要向从表中去添加数据。要求在主表中，要有对应的数据。才可以去添加。
4. 如果要删除表。要先删除从表。然后去删除主表。
5. 新建表的时候。需要先去创建主表，然后去创建从表。

作用：保持数据的完整性，和有效性。