数据库基本概念以及常用命令

//此博客主要陈述一些数据库的基本概念以及常用的命令，便于初学者快速查询所需的数据库语句以及数据库一些知识点的概念，便于上手SQL语言，熟练的使用查询方法。

//数据库基本概念:

数据:对客观事物的符号表示,

数据库:有一批数据构成的有序集合,这些数据被分门别类地存放在一些结构化的数据表里,而数据之间又往往存在交叉引用的关系,这种关系使数据库又被称为关系型数据库
	按照数据结构来组织,存储和管理数据的仓库
数据库管理系统:DBMS(Database Management System)
	主要功能:
		1.数据定义功能:	提供数据定义语言(DDL)
				定义数据库中的数据对象
		2.数据操纵功能:	提供数据操纵语言(DML)
				操纵数据实现对数据库的基本操作(增删查改)
		
数据库系统:(DBS) 是一个实际可运行的存储,维护和应用系统提供数据的软件系统
	构成:DBMS,DB,应用软件,数据库管理员,用户

LAMP = Linux+apache+mysql+php架构

MySQL卸载流程
1.net stop mysql(以管理员身份停止mysql服务)(net start mysql)
2.控制面板卸载
3.删除残留的mysql目录:C:\ProgramFiles\MySQL
4.显示隐藏文件,删除数据目录:C:\ProgramData\MySQL

启动方式:cmd-->mysql -uroot -p123456      连接本地主机
	cmd-->mysql -h 192.168.46.254 -uroot -p123456  连接指定主机

常用命令:
查看所有的数据库实例
show databases;
查看当前用户
select user();
使用数据库
use test;

MySQL命令
退出mysql命令行
quit  \q   exit
取消正在编写的命令: \c,clear
修改结束分隔符:delimiter 分隔符
显示当前正在输入的命令:\p

mysqladmin命令
在命令行中创建数据库实例
mysqladmin -u root -p123456 create oa
在命令行中删除数据库实例
mysqladmin -u root -p123456 drop oa
修改当前用户密码
mysqladmin -uroot -ppassword 'newpassword'

mysqldump命令
备份指定数据库实例
mysqldump -uroot -p123456 oa > d:\oa.sql
备份指定数据库实例中的指定表
mysqldump -uroot -p123456 test user > c:\user.sql

恢复(导入)数据
进入到指定的数据库实例
source *.sql文件所在路径

忘记密码时如何设置密码:
	1, net stop mysql
  	 mysqld --skip-grant-table
	2,新开cmd,  mysql
		    use mysql
		    任务管理器中关闭mysql进程
	3, net start mysql

SQL:Structured Query Language结构化查询语言，一门独立于数据库的操作命令，任何关系型数据库几乎都支持sql，
在不同的数据库管理系统(DBMS)存在细微差异
SQL分类
	1.DDL数据定义语句(create,drop,alter...)
	2.DML数据操作语句(insert,update,delete,select 增删改查)
	3.DCL数据控制语句(grant,revoke)

创建一个人事管理系统的数据库:OA
1.创建数据库实例
create database oa
2.使用数据库实例
use oa
3.创建数据表(员工表:工号,姓名,性别,职位,月薪)
create table emp
(
eno int,
ename varchar(32),
sex char(8),
ob varchar(16),
sal float
);

建表语法结构
create table 表名称
(
列名称 列类型
...
）
4.删除表中的指定列(前提是表中列没有数据)
alter table emp drop sex;
5.向表中新增一个列
alter table emp add dept varchar(16);
6.修改表中指定列的类型(前提是表中列没有数据)
alter table emp modify sal double;
7.删除表
drop table emp;
8.修改表中列名称
alter table emp change deptno dno int;
9.修改表名称
alter table emp rename to employee;

任务：
创建两张表
1.员工表(
工号:int,		主键 自动增长
姓名:varchar,		不为空
职位:varchar，		不为空 默认值'普通员工'
手机号:varchar,		不为空 check(len(phone)=11)
月薪：float，		
部门编号:int		foreign key references dept(dno)
)
2.部门表(
部门编号:int,		主键 自动增长
部门名称:varchar,	not null unique
部门分机号：varchar	not null unique
)

create table dept
(
dno int auto_increment,
dname varchar(32) not null unique,
tel varchar(16) not null unique,
primary key(dno)
)

create table emp
(
eno int primary key auto_increment,
ename varchar(32) not null,
job varchar(32) not null default '普通员工',
phone varchar(11) check(len(phone)=11),
sal float,
dno int references dept(dno)
)



DML语句使用(数据操作语句)
1.向表中添加一条记录
insert into employee values(1,'唐僧','部门经理','110',18800.08,10);
insert into employee(eno,ename,sal) values(2,'孙悟空',800);
insert into employee(eno,ename) values(3,'猪八戒'),(4,'沙和尚'),(5,'白龙马');

2.删除数据
delete from employee where eno=2

3.修改数据
update employee set job='普通员工' where job is null;
 null != ''

数据完整性的维护
约束
	主键约束  	primary key
	不为空约束	no null
	唯一约束		unique
	检查约束		check
	外键约束		foreign key	
触发器
添加主键约束
alter table employee add constraint pk_eno primary key(eno);
添加不为空约束同时设置默认值
alter table employee change ename ename varchar(32) not null default '匿名';
添加不为空以及唯一约束
alter table department change dname dname varchar(32) not null unique;
设置检查约束
alter table employee add constraint ck_sex check(sex in('男','女'));
设置外键约束(被设置外键的列必须是另一个表的主键列)
alter table employee add constraint fk_dno foreign key(dno) references department(dno);

mysql数据类型
字符串
	char
	varchar
	nchar
	nvarchar
数值
	int/integer
	float
	double
	decimal
日期类型
	date(日期格式 yyyy-MM-dd)
	time（时间格式 HH:mm:ss）
	datetime(日期时间格式:yyyy-MM-dd HH:mm:ss)
	year(年份格式 yyyy)
	timestamp(时间戳yyyyMMddHHmmss)
大文本
	text
二进制类型
	blob
	binary

char和varchar区别
char定长字符串类型，char类型存储的字符串不论长度是否达到指定长度，都会占满char设定的存储空间，但是在存储定长数据时，效率较高，所以char类型一般用于声明长度固定的数据(性别，手机号,身份证号...)
varchar可变长度的字符串类型,类型中存储的数据长度会跟随实际添加的数据长度而自动申请空间，有效的节省空间(空间利用率较高)，效率不如char，一般varchar用于长度不定的数据(家庭住址，姓名...)

nchar和char，varchar与nvarchar区别？(mysql中均以字符为单位)
nchar，nvarchar长度是以字符为单位
char，varchar长度以字节为单位
在使用中文数据时尽量以nchar，nvarchar为参考
在使用英文数据时以char,varchar为参考

主从数据库(主库存储数据，从库检索数据)
数据库表的主从关系
员工表(dno)  从表
部门(dno)    主表




************SQL查询************

查询目标表
查询的目标列
查询条件

数据库中支持隐式转换
a.声明数据类型为varchar时，可以使用数值类型，但是不能包含非数值字符
b.声明的数据类型为数值时，可以使用‘’包裹数值，但是被包裹的数据必须为数值类型字符串

1.单表查询
#显示所有员工信息
select * from emp
select eno,ename,job,phone,sal,dno from emp;
#查询所有的普通员工
select * from emp where job='普通员工';
#查询所有普通员工的姓名和月薪
select ename,sal from emp where job='普通员工';
#模糊查询，查询姓名中包含“悟”的员工
select * from emp where ename like '%悟%';
#模糊查询，查询姓名以“悟”结尾的员工
select * from emp where ename like '%悟';
#模糊查询，查询姓名长度为3个字符，中间为“悟”的员工
select * from emp where ename like '_悟_';
#模糊查询，查询姓名中不包含‘悟’的员工
select * from emp where ename not like '%悟%';
#查询1号部门中的所有员工
select * from emp where dno=1;
#查询月薪在5000~20000之间的所有员工的姓名和月薪以及部门号
select ename,sal,dno from emp where sal>=5000 and sal<=20000;
select ename,sal,dno from emp where sal between 5000 and 20000;
#查询1,2,3号员工的姓名，月薪，职位
select ename,job,sal from emp where eno in(1,2,3);
#查询除了1,2,3号员工以外的其他员工的姓名，月薪，职位,工号
select ename,job,sal from emp where eno not in(1,2,3);

聚合函数
count 统计
sum 求和
avg 求平均值
min 求最小值
max 求最大值

#显示员工表中的总员工数
select count(*) from emp;
#计算一个月发放的工资总数
select sum(sal) from emp;
#计算平均薪资
select avg(sal) from emp;
#获取所有员工中月薪最高以及最低的员工的薪资
select max(sal),min(sal) from emp;

#计算1号部门需要发放的工资总额
select sum(sal) from emp where dno=1;

#计算每个部门需要发放的工资总额(group by:分组)
select sum(sal) from emp group by dno;

#计算每个部门的员工总数，部门平均薪资，以及部门号?
select count(*),avg(sal),dno from emp group by dno;

//数据库常用命令

停止mysql服务(以管理员身份启动cmd)
net stop mysql
启动mysql服务
net start mysql
启动mysql
cmd->mysql -u root -p123456			连接本机
cmd->mysql -h 192.168.46.254 -uroot -p123456	连接指定主机
查看所有的数据库实例
show databases;
查看当前用户
select user();
使用数据库
use test

MySQL命令
退出mysql命令行：quit、\q、exit
取消正在编写的命令：\c、clear
修改结束分隔符：delimiter 分隔符号
显示当前正在输入的命令:\p

mysqladmin命令
在命令行中创建数据库实例
cmd->mysqladmin -uroot -p123456 create oa
在命令行中删除数据库实例
cmd->mysqladmin -uroot -p123456 drop oa
修改当前用户密码(前提是知道原始密码)
cmd->mysqladmin -uroot -p password 'newpassword'

mysqldump命令
备份指定数据库实例
mysqldump -uroot -p123456 hellomrchai > d:\hellomrchai.sql
备份指定数据库实例中的指定表
mysqldump -uroot -p123456 test user > c:\user.sql

恢复(导入)数据
进入到指定的数据库实例
source *.sql文件所在路径

SQL != 数据库
SQL:Structured Query Language结构化查询语言，一门独立于数据库的操作命令，任何关系型数据库几乎都支持sql，
在不同的数据库管理系统(DBMS)存在细微差异
SQL分类
	1.DDL数据定义语句(create,drop,alter...)
	2.DML数据操作语句(insert,update,delete,select 增删改查)
	3.DCL数据控制语句(grant,revoke)

创建一个人事管理系统的数据库:OA
1.创建数据库实例
create database oa
2.使用数据库实例
use oa
3.创建数据表(员工表:工号,姓名,性别,职位,月薪)
create table emp
(
eno int,
ename varchar(32),
sex char(8),
ob varchar(16),
sal float
);

建表语法结构
create table 表名称
(
列名称 列类型
...
）
4.删除表中的指定列(前提是表中列没有数据)
alter table emp drop sex;
5.向表中新增一个列
alter table emp add dept varchar(16);
6.修改表中指定列的类型(前提是表中列没有数据)
alter table emp modify sal double;
7.删除表
drop table emp;
8.修改表中列名称
alter table emp change deptno dno int;
9.修改表名称
alter table emp rename to employee;

任务：
创建两张表
1.员工表(
工号:int,		主键 自动增长
姓名:varchar,		不为空
职位:varchar，		不为空 默认值'普通员工'
手机号:varchar,		不为空 check(len(phone)=11)
月薪：float，		
部门编号:int		foreign key references dept(dno)
)
2.部门表(
部门编号:int,		主键 自动增长
部门名称:varchar,	not null unique
部门分机号：varchar	not null unique
)

create table dept
(
dno int auto_increment,
dname varchar(32) not null unique,
tel varchar(16) not null unique,
primary key(dno)
)

create table emp
(
eno int primary key auto_increment,
ename varchar(32) not null,
job varchar(32) not null default '普通员工',
phone varchar(11) check(len(phone)=11),
sal float,
dno int references dept(dno)
)



DML语句使用(数据操作语句)
1.向表中添加一条记录
insert into employee values(1,'唐僧','部门经理','110',18800.08,10);
insert into employee(eno,ename,sal) values(2,'孙悟空',800);
insert into employee(eno,ename) values(3,'猪八戒'),(4,'沙和尚'),(5,'白龙马');

2.删除数据
delete from employee where eno=2

3.修改数据
update employee set job='普通员工' where job is null;
 null != ''

数据完整性的维护
约束
	主键约束  	primary key
	不为空约束	no null
	唯一约束		unique
	检查约束		check
	外键约束		foreign key	
触发器
添加主键约束
alter table employee add constraint pk_eno primary key(eno);
添加不为空约束同时设置默认值
alter table employee change ename ename varchar(32) not null default '匿名';
添加不为空以及唯一约束
alter table department change dname dname varchar(32) not null unique;
设置检查约束
alter table employee add constraint ck_sex check(sex in('男','女'));
设置外键约束(被设置外键的列必须是另一个表的主键列)
alter table employee add constraint fk_dno foreign key(dno) references department(dno);

mysql数据类型
字符串
	char
	varchar
	nchar
	nvarchar
数值
	int/integer
	float
	double
	decimal
日期类型
	date(日期格式 yyyy-MM-dd)
	time（时间格式 HH:mm:ss）
	datetime(日期时间格式:yyyy-MM-dd HH:mm:ss)
	year(年份格式 yyyy)
	timestamp(时间戳yyyyMMddHHmmss)
大文本
	text
二进制类型
	blob
	binary

char和varchar区别
char定长字符串类型，char类型存储的字符串不论长度是否达到指定长度，都会占满char设定的存储空间，但是在存储定长数据时，效率较高，所以char类型一般用于声明长度固定的数据(性别，手机号,身份证号...)
varchar可变长度的字符串类型,类型中存储的数据长度会跟随实际添加的数据长度而自动申请空间，有效的节省空间(空间利用率较高)，效率不如char，一般varchar用于长度不定的数据(家庭住址，姓名...)

nchar和char，varchar与nvarchar区别？(mysql中均以字符为单位)
nchar，nvarchar长度是以字符为单位
char，varchar长度以字节为单位
在使用中文数据时尽量以nchar，nvarchar为参考
在使用英文数据时以char,varchar为参考

主从数据库(主库存储数据，从库检索数据)
数据库表的主从关系
员工表(dno)  从表
部门(dno)    主表