数据库
MySQL
一、卸载
-
- 停止mysql服务:net stop mysql
- 控制面板卸载
- 删除残留mysql安装目录:c:\ProgramData\MySQL
- 删除数据目录:C:\ProgramData\MySQL
二、安装
1、
三、配置
详细配置: 开发:
多用途数据库: innoDB表空间位置:
并发连接数:默认20 是否开启TCP/IP网络的支持,
使用GBK编码: MySQL环境变量,服务名:
配置密码,是否远程连接:
四、找回密码
管理员身份进入cmd
1.进入mysql的bin目录(如果已配置mysql环境变量可忽略该步骤)
2.停止mysql服务net stop mysql
3.跳过权限验证:mysqld --skip-grant-tables
4.新建一个命令行窗口同样进入到bin目录,启动mysql
5.使用mysql数据库,并修改root用户密码 7版本字段名不是password
update mysql.user set authentication_string=password('*******') where user='*******';
6.关闭进程中的mysqld.exe
五、常用命令:mysql+mysqldump
mysql(登录、查)
mysqladmin(建删库)
mysqldump(导入导出)
Mysql命令:
- mysql –u Root –pPassword;登录,密码和-u不能加空格
- show databases; 显示数据库
- use test 使用数据库
- desc tableName; 显示表结构---只能在use选中库后,显示某表结构
- create database test; 创建数据库
- Select user(); 查看当前登录用户
- \q; quit; exit; 退出当前数据库
- mysql –h 192.1646.254 –uroot –p123456;连远程主机
- sadfsdaf\c || clear取消正在编写的命令
- \s 查看当前mysql的状态
- delimiter $ 修改结束分隔符 select user()$
- \h || help 获取帮助
- \p || print 显示当前正在输入的命令
- net stop mysql;关闭mysql服务,必须管理员身份
- net start mysql;启动mysql服务,必须管理员身份
Mysqladmin:
- Mysqladmin –uroot –p password ‘root’ 修改密码(知道原始密码的前提下)
- mysqladmin –u root –p123456 create oa 命令行中创建oa数据库实例
- mysqladmin –u root –p12345 drop oa 删除oa数据库
六、库备份命令:Mysqldump
- 备份oa数据库到指定目录(只有表结构):mysqldump –uroot –p123456 oa > d:\mysql.sql
- 备份oa中的某张表user表到指定目录(有数据):mysqldump –uroot –p123456 oa user > d:\user_table.sql
- 导入数据:mysql>source d:\user.sql
七、SQL语句分类
SQL != 数据库
SQL:Structured Query Language结构化查询语言,一门独立于数据库的操作命令,任何关系型数据库几乎都支持sql,在不同的数据库管理系统(DBMS)存在细微差异
SQL分类
1.DDL数据定义语句(create,drop,alter...)
2.DML数据定义语句(insert,delete,update,select增删改查)
3.DCL数据定义语句(grant,revoke)
1.DDL语句
创建数据库
Create database oa;
使用数据库实例
Use oa
建表
CREATE TABLE tableName(
user int,
dno int;
name varchar(32),
sex char(8),
sal double,
birthday );
修改表
ALTER TABLE tableName DROP sex; 删列
ALTER TABLE tableName ADD dept varchar(32); 加列
ALTER TABLE tableName MODIFY sal float; 修改列类型(前提,列没有数据)
ALTER TABLE tableName CHANGE user_id user INT; 修改表中列名称
ALTER TABLE tableName rename employee; 修改表名称
删除表
DROP TABLE EMP;
2.DML语句
查询:
#查询三个字符,中间为悟字
Select * from emp where ename like ‘_悟_’
#不包含悟
Select * from emp where ename not like ‘%悟%’
#月薪在5000~7000之间
Select * from emp where sal betweed 5000 and 7000;
聚合函数
Count() 统计
#员工总数
Select count(user_id) from emp;
Sum() 求和
#所有人工资总和
Select sum(sal) from emp;
Avg() 平均
Select Avg (sal) from emp;
Min() 最小
MAX() 最大
枚举:enum
#限制数据类型,只能是这三个值
Create table tbstu(sex enum(‘男’,’女’,’中性’));
Group by having 分组
#获取每个部门需要发放的工资总额
Select sum(sal) from emp group by dno;
#查询所有大于1的部门,平均薪资,并降序排列
Limit n,m 分页
第一个参数:起始位置为n+1 第二个参数:一共m条
#查询前5名员工信息
SELECT * FROM employee limit 0,5 --序号:1-5 的员工
分页的实现:
当前页码数: currentPage
每页记录条数: pageSize
Limit (currentPage-1)*pageSize, pageSize
Order by birthday 排序
Asc升序 desc降序
Distinct 去重
SELECT DISTINCT depno FROM employee;
Length() 字节数-mysql #UTF8 一个字符占3个字节
MySQL存储函数-基本常用函数
1、字符串类
1)mysql储存过程函数-字符串函数:
PASSWORD(STRING)//加密,不可解密
MD5(string)//加密,不可解密
SHA(string)//加密,不可解密
encode(string1,string2)//加密 string2加密种子
decode(string1,string2)//解密
CHARSET(str) //返回字串字符集
CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格
注:mysql中处理字符串时,默认第一个字符下标为1,即参数position必须大于等于1
2、数学类
1)mysql储存函数-数学函数:
ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注:HEX()中可传入字符串,则返回其ASC-11码,如HEX('DEF')返回4142143
也可以传入十进制整数,返回其十六进制编码,如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
注:返回类型并非均为整数,如下文:
SIGN (number2 ) //
SQRT(number2) //开平方
3、日期时间类
ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE ( ) //当前日期
CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW () //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分返回符号,正负或0
八、约束
1.主键约束 primary key
ALTER TABLE employee ADD CONSTRAINT pk_eno PRIMARY KEY(eno);
2.不为空约束 not null
ALTER TABLE employee CHANGE user_name name INT not null; --无默认值
ALTER TABLE employee CHANGE user_name name INT not null default ‘匿名’;
3.唯一约束 unique
ALTER TABLE employee CHANGE depart_name dept varchar(32) not null unique;
4.检查约束 check(mysql不支持,但可以兼容,只是不生效,mysql可以用枚举来约束)
ALTER TABLE employee ADD sex varchar(4) check(sex in(‘男’, ’女’));
ALTER TABLE employee change sex sex varchar(4) check(sex in(‘男’, ’女’));
ALTER TABLE employee ADD CONSTRAINT pk_eno CHECK(sex in(‘男’,’女’));
5.外键约束 foreign key
ALTER TABLE employee add constraint fk_dno foreign key(dno) references department(dno);
- 常用数值类型
字符串
char 字节,定长字符串类型,占满。在存储定长数据时效率较高。定长数据用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 小说,2-4G
二进制类型
blob 图片,视频
十、索引+视图
索引:类似字典目录,对表中指定列建立索引能提高效率。存储默认索引的空间为表的1.2倍。所以在对表进行更新操作时,同时索引也会自动更新(消耗一定时间)。索引数据必须唯一,复合索引只有在多列同时查询时才会更快。主键默认加了索引
加索引语法:create index ind_name on table_name(column1_name, column2_name);
删除索引:drop index 索引名 on 表名称
适用于:1、不经常修改的列
2、不会出现大量重复的列
3、数据达到一定数量级时,数据量较少时避免建立索引
视图:使用前只是存储在数据库服务器上的一条指令,使用时该视图时才对物理数据生成一个视图。对于视图的操作会影响到物理数据表。
创建视图语法:create view 视图名称(v$temp) as select no,name,sex from tableName;
十一、存储过程
存储过程:是存储在数据库服务器中,已经经过数据库预编译(java的正则表达式)操作,在通过应用程序对数据库发起相关的操作时,只需要发送调用指令即可(耗时操作分担数据服务器中解决)。在数据库中执行普通的sql语句的效率远远低于存储过程(sql语句需要经过编译)
缺点:不兼容(不同数据库管理系统的可编程性语法不尽相同)
创建视图语法:create procedure pro_01(参数)
Begin
Sql语句...SELECT * FROM TABLE
End;
存储过程的调用:call pro_01();
存储过程分类:
类似java中的方法,在一个过程中包含了一些用于处理某些复杂逻辑的代码片段
- 无输入无输出参数
- 有输入无输出参数
Carete procedure pro_03(in id int)
Begin
Select * from user where user_id = id
End;
调用 :call pro_03(1);
---------------------------------------------------------------------------
#声明临时变量
Create procedure pro_04(in name varchar(16))
Begin
Declare depart_code int default 0;#声明临时变量
Select dno into depart_code from user where user_name = name;
Select * from depart where no = depart_code;
End;
调用 :call pro_04(‘万曾辉’);
---------------------------------------------------------------------------
Carete procedure pro_05(in a int, in b int)
Begin
Declare c int default 0;
Set c = a + b;#为变量赋值时需要set关键字,oracle: c := a+b
Select c;
End;
调用 :call pro_05(‘万曾辉’);
---------------------------------------------------------------------------
#输出参数
---------------------------------------------------------------------------
---------------------------------------------------------------------------
---------------------------------------------------------------------------
3.无输入有输出参数
Carete procedure pro_04(out id int)
Begin
Select * from user where user_id = id
End;
调用 :pro_03();
4.有输入有输出参数
条件分支
Switch:case when
SELECT case depno
When 1 then ‘核心部门’
When 2 then ‘普通部门’
Else ‘逗比部门
End depno
From department;
循环
While:
Loop:
Repeat:
十二、自定义函数(必须给函数返回值)
调用:select fun_max(1,2)
查看系统的存储过程和函数:
通用存储过程分页
create procedure sp_paging
(
currentpage int,
pagesize int,
tname varchar(64),
selections varchar(2000),
conditions varchar(2000),
sortcolumn varchar(64),
sortOrder char(4),
out totalnum int,
out totalpage int
)
BEGIN
#临时变量,标记开始查询位置
declare vstart int default 0;
#设置查询的起始位置
set vstart = (currentpage - 1) * pagesize;
#判断是否填写查询列,若未填写则默认使用*
if selections is null or selections = '' THEN
set selections = '*';
end if;
#判断是否填写查询条件
if conditions is null or conditions = '' THEN
set conditions = '1=1';
end if;
#判断是否填写排序列,若未填写则使用默认的排序列‘_id’(需要事先约定好)
if sortcolumn is null or sortcolumn = '' THEN
set sortcolumn = '_id';
end if;
#判断是否给定正确的排序方式,如未给定或者不符合要求,则默认使用ASC(升序)
if lower(sortOrder) != 'asc' and lower(sortOrder) != 'desc' THEN
set sortorder = 'asc';
end if;
#select * from employee where depno>1 order by birth desc limit 0,5;
set @vsql = concat('select ',
selections,
' from ',
tname,
' where ',
conditions,
' order by ',sortcolumn,' ',sortorder,
' limit ',vstart,',',pagesize);
#对sql语句预编译
prepare stmt from @vsql;
execute stmt;
DEALLOCATE PREPARE stmt;
#总记录数
set @vsql = concat('select count(*) into @tnum from ',tname,' where ',conditions);
prepare stmt from @vsql;
execute stmt;
set totalnum = @tnum; #将临时变量中的数据赋值给输出参数
set totalpage = CEILING(totalnum/pagesize);
DEALLOCATE PREPARE stmt;
end;
call sp_paging(1,2,'department',null,null,'depno','desc',@tn,@tp);
select @tp,@tn;
十三、触发器
数据库完整性约束
- 约束(列级约束)
- 主键
- 外键
- 不为空
- 唯一
- 检查(mysql不支持check,用default)
- 触发器trigger(行级约束)
在对某些表执行更新操作(insert, update, delete)时同时出发一些其他动作
注:不能写select,mysql一个操作(更新)只能有一个触发器
查看系统中的触发器:
查看有哪些触发器:
创建触发器:
删除触发器:
十四、数据库设计
数据库设计范式:
意义:设计出来的表满足一些需求规范。(简洁,结构明晰)
第一范式1NF:
列不可再分,原子列。要求表中每一列只能包含一种数据类型。就目前关系型数据库来说不可能设计出不符合第一大范式的表
第二范式2NF:(非主键列完全依赖于所有主键列,不能单独依赖某一列)例如:姓名和年龄只依赖学号,学分只依赖课程名称(不同于联合主键)
在满足第一大范式的前提下,联合主键中,非主键列仅依赖于某一个或一组主键,不完全依赖所有主键。数据冗余
第三范式3NF:(不存在传递依赖关系)
第三范式要求建立在第二大范式的基础之上。
设计数据库表的原则:
- 多对一的关系中,维护关系交给多的一方
- 多对多的关系中,维护关系由第三方表维护(订单表)
- 一对一的关系,维护关系可以使用主键关联,也可以使用唯一外键关联
1NF:列不可再分
2NF:列必须与主键直接相关
3NF:列之间不存在传递依赖