MySql数据库
一．表的类型：
ISAM表
索引顺序储存方法。是旧的MySQL标准，用于3.23.0以前的版本。 MyISAM表
从3.23.0开始，MyISAM表代替了ISAM表。
MyISAM索引ISAM表小得多，相通会使用更少的资源来执行带有索引的SELECT 语句。
MERGE表
MERGE表是相通的MyISAM表的合并，从3.23.24中开始引进的。
优点:很多情况速度更快、表的尺寸小、
缺点：对于某些搜索(eq_ref)，速度很慢。
HEAP表
存储在内存里，速度最快的表。
数据可能会在出现问题后丢失，而且不能保留太多数据。
InnoDB表
是事务安全的表类型。
MyISAM表中在插入是整个表被锁定。InnoDB 只锁定该记录。所以如果执行大量的UPDATE 和INSERT，应该使用InnoDB；如果主要用来执行SELECT，MyISAM是更好的选择。
BDB表
伯克利数据库。接口不甚稳定。
二. MySQL中的数据类型
<1> 数值型
<2> 字符(串)型
<3> 日期和时间型
{
1.整型
{
(1) TINYINT 1字节
(2) SMALLINT 2字节
(3) MEDIUMINT 3字节
(4) INT 4字节
(5) BIGINT 8字节
}
2.浮点型
{
(1) FLOAT 4字节
(2) DOUBLE 8字节
(3) DECLMAL
}
3.字符串
{
(1) CHAR 1字节
(2) VARCHAR 1字节 注:可以释放空间
(3) TEXT 2字节
(4) MEDIUMBLOB 3字节
(5) LONGTEXT 4字节
(6) ENUM 1或2字节 注:取决于枚举值的数目 (复合类型)
(7) SET 1,2,3,4或8字节 取决于集合成员的数量 (复合类型) }
4.日期时间型
{
(1) DATETIME '0000-00-00 00:00:00' 占19位
(2) DATE '0000-00-00'
(3) TIMESTAMP 00000000000(长度取决于显示尺寸,并且有专有的 更新特性，默认为当前时间)
(4) TIME '00:00:00'
(5) YEAR 0000 占一个字节
}
}
Sql语言的分类：DDL（数据库定义语言）：create、drop、alter
DML(数据库操作语言)：insert、delete 、update、select
DCL(数据控制语言)：grant、revoke
事务控制语言：committ、rollback
二. 约束:
{
1. primary key 主键约束
2. foreign key 外键约束
3. default 默认约束
4. unique 唯一约束
5. check 检查约束
6. not null 空约束
}
约束的基本操作:
1.主键约束
{
定义主键:
方法1: 列名 + primary key
方法2: alter table 表名
add constraint 逻辑名 primary key
方法3: primary key(列名)
删除主键:
alter table 表名 drop primary key
} 2.外键约束 { 定义外键: 方法1： 列名 + references 主表(列名) 方法2: constraint 逻辑名 foreign key(列名) references 主表( 列名) 方法3: foreign key references 主表(列名) 删除外键:
}
3.唯一约束
{
定
删
}
4.非空约束
{
定
删
}
5.检查约束
{
定
删
}
6.默认约束
{
定
alter table 表名 drop foreign key 逻辑名 义： 方法1: 列名 + unique 方法2: constraint 逻辑名 unique(列名) 方法3: alter table 表名 add constraint 逻辑名 unique(列名) 除: alter table 表名 drop unique 义: 方法1: 列名 + not null 方法2: alter table 表名 change 旧列名 新列名 varchar(20) not null 除: alter table 表名 change 旧列名 新列名 varchar(20) null 义： eg： 列名 + check( sex = '男' or sex = '女') eg: 列名 + check( email like '%@%') 除: 手动删除 (注:代码不可以实现删除功能) 义： 方法1： 列名 + default ''
方法2: alter table 表名 change 旧列名 新列名 类型 default
删 除:
alter table 表名 change 旧列名 新列名 类型 default null
}
三.对表的操作
1.修改表名
方法一: rename table 旧表名 to 新表名
方法二: alter table 旧表名 rename to 新表名
2.修改列名
alter table 表名 change 原列名 新列名 类型
3.修改列的属性
<1> MySQL alter table 表名 change 旧列名 新列名 属性
<2> SQLServer alter table 表名 column 列名 属性
<3> Oracle alter table 表名 modify 列名 属
4.修改表的类型 alter table 表名 type=表类型
5.查看所有数据库
show databases
6.查看所有表
show tables
7.查看表的结构
desc 表名
8.自动标识
auto-increment
四.DML语言的操作
1.insert into 表名[（列名）] values(值) 或者是 select语句
2.update 表名
Set 列名=[select 的值 或者是具体的值] ，列名……
Where 条件
3.delete from 表名
Where 条件
Truncated table 表名
4.select 显示的内容 from 表名
Where 条件
Group by 列名
Having 条件
Order by 列名 desc 降序、 asc 升序
Limit 显示的行数
.查询前5条信息
<1> MySQL select * from 表名 limit 5
<2> SQLServer select top 5 * from 表名
<3> Oracle select * from 表名 where rownum<=5
高级查询：
连接查询：交叉连接、内连接（等值连接）、外连接（左连接、右连接、全连接），自
连接和自然连接
嵌套查询：查询工资大于1000的员工姓名（姓名在employ表中，工资在emp_salary表中）
Select emp_name from employ where emp_id in（select emp_id from emp_salary where salary>1000）
Select emp_name from employ where emp_id=any（select emp_id from emp_salary where salary>1000）
Select emp_name from employ where emp_id=some（select emp_id from emp_salary where salary>1000）
合并查询： unoin（并）
五.索引
1.索引的概念和作用
2.索引的类型
3.索引的创建在什么样的列上
4.创建的方法
eg:
create index ff on employ(emp_name) ------------创建索引 select * from employ where emp_name='蚩尤'
alter table employ ---------删除索引 drop index ff
或者是 drop index 索引名 on 表名
六．视图
1.视图的概念和优点
2.视图的创建
3.视图的定义和应用
4．查看视图
七. 函数:
1.字符串函数
ASCII(str) 返回字符串str最左边的那个字符的ASCII码值
CONCAT(str1,str2) 将参数连接成字符串返回
LENGTH(str) 返回一个字符串的长度
LOCATE(small,big) 返回一个small在big的位置
INSTR(big,small) 返回一个small在big的位置
SUBSTRING(str from pos) 从字符串str的起始位置pos返回一个字符串
REPLACE('asdf','sd','ff') 返回'affff'
2.数学函数
floor(x) 返回不大于x的最大整数值
ceiling(x) 返回不小于x的最小整数
round(x) 四舍五入
rand() 返回一个随机小数
3.日期函数
DATE_ADD(date,INTERVAL EXPR TYPE) 对日期进行加减运算
DATE_FORMAT(date,format) 根据format字符串格式化date值
eg:
SELECT DATE_FORMAT(CURDATE(),'%Y'); ----------提取当前时间的年份 SELECT DATE_FORMAT(CURDATE(),'%M'); ----------提取当前时间的月份 SELECT DATE_FORMAT(CURDATE(),'%S'); ----------提取当前时间的秒 SELECT DATE_FORMAT(CURDATE(),'%D'); ----------提取当前时间的日期
curdate() 显示当前日期
now() 显示当前日期时间
select datediff(curdate(),'2008-8-8');------------返回当前时间和‘2008-8-8’的时间差
八.事务：是数据库中一个重要机制，用来确保数据的完整性和并发处理能力，是将一条和一组sql语句看成一个逻辑单元，在执行过程中同时成功或同时失败。
1.原子性
2.一致性
3.隔离性
4.持久性
九.锁
LOCK TABLES 锁定表
UNLOCK TABLE 解锁
十.存储过程
特点:
优: <1> 速度快
<2> 安全
<3> 效率高
<4> 编码
缺:
服务器配置必须高
常用命令
1.show procedure status 显示数据库中所有存储的存储过程的基本信息.
2.show create procedure sp_name 显示某一个存储过程的详细信息
格式:
CREATE PROCEDURE 过程名(参数)
begin
declare //申明变量
end
call 过程名
条件语句
if 条件 then
statemen
else
statemen
end if;
循环语句
(1)while()
statemen
end while;
(2)[label:]loop
statemen
end loop;
(3)repeat
statemen
until 条件
end repeat
十一.触发器
1.功能:
1.完成复杂的约束
2. 实现事务的原子性
3. 审计日志
2.触发器与存储过程的区别
触发器 存储过程
不带参数 带参数
隐示调用 显示调用
特殊的存储过程
3.类型
DDL触发
DML触发
Instead of 触发
数据库触发
4.语法
CREATE TRIGGER
{BEFORE/AFTER}
{INSERT/UPDATE/DELETE}
FOR EACH ROW //行触发
eg:
select * from course
create trigger my_tr
before
insert on course
for each row
begin
insert into a values(4,444) ;
end;
select * from a
drop trigger my_tr //删除触发器 insert into course values(4,'MySQL');
九.备份和恢复
逻辑
{
导入
导出
}
物理
{
冷
热
}
语法:
备份:
mysqldump -u root -p 数据库名>/home/ttt.sql 还原:
create database tt
mysql -u root -p tt</home/ttt.sql
十.授权
eg:
增加一个用户test 密码为abc 让她可以在任何主机上
登录,并对所有数据库有查询，插入，修改，删除的权限.
Revoke from
grant select,insert,update,delete on *.* to test @"%"
Identified by "abc" //设置密码
ON 子句中*.*说明符的意思是"所有数据库，所有的表"
经典例题
-------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
-------------------------------------------超市销售管理系统---------------------------------------
一 . 数据库设计：
1 .公司名称(varchar(20))，店面名称(varchar(20))，收款机号(int (不能重复))，收款员姓名(varchar(10))，
收款员编号(int (不能重复))，商品编码(int (不能重复))，商品名称(varchar(50))，进货价格(numeric(精度10，小数位数4))，销售价格(numeric)，库存(int)，销售数量(int)，销售时间（精确到秒）。
2 .测试数据要求，收款机不能少于2台，收款员不能少于3人，商品数量不能少于5个，单日销售数量不能少于5个，商品的进货价格和销售价格保留2位小数。
二. 具体功能：
1. 将每日的销售数量进行汇总,生成销售日报表， 将报表的数据上传到服务器端，上传内容包含：商品编码，商品名称，商品销售单日总数量，销售总额，利润(每个商品)，公司名称，店面名称，收款机号，收款员编号(顺序不能更改)，当天日期(精确到天)。
2. 利润计算公式 利润=（销售价格-进货价格）*85%
3.把所得数据上传到远程服务器 数据库ip：192.168.1.100 数据库名：pubs 表名：abc 用户名 ：sa 密码 ：sa
create database tset
on primary
(name=test_date,
filename='e:\test1.mdf',
size=3,
filegrowth=10%)
log on
(name=test_log,
filename='e:\test2.ldf',
size=3,
filegrowth=10%)
create table 店面表
(店面编号 int primary key,
店面名称 varchar(20),
公司名称 varchar(20),
店面地址 varchar(100),
联系地址 varchar(20))
create table 收款机表
(收款机编号 int primary key,
型号 varchar(20),
店面编号 int,
constraint fp_7 foreign key(店面编号) references
create table 员工表
(员工号 int primary key,
员工名 varchar(20),
性别 char(3),
地址 varchar(30),
电话 varchar(15),
身份证 char(18))
create table 收款状况表
(id int primary key, 店面表(店面编号))
员工号 int,
收款机编号 int,
开始工作时间 datetime,
离开工作时间 datetime,
constraint fp_1 foreign key(员工号) references 员工表(员工号),
constraint fp_2 foreign key(收款机编号) references 收款机表(收款机编号) )
create table 商品表
(
商品编号 int primary key,
商品名称 varchar(50),
进货价格 numeric(10,4),
库存数量 int,
店面编号 int,
constraint fp_7 foreign key(店面编号) references 店面表(店面编号) )
create table 进货表
(id int primary key,
商品编号 int,
员工号 int,
进货价格 numeric(10,4),
进货数量 int,
constraint fp_8 foreign key(商品编号) references 商品表(商品编号) )
create table 销售表
( id int primary key,
收款机编号 int,
商品编号 int,
销售数量 int,
销售价格 numeric(10,4),
销售时间 datetime,
constraint fp_4 foreign key(商品编号) references 商品表(商品编号),
constraint fp_2 foreign key(收款机编号) references 收款机表(收款机编号) )
-----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
-------------------------------------学员的学籍管理--------------------------------------------
注册所有学生的基本信息。包括学号、姓名、性别、年龄、籍贯。专业、学制、入学时间、生源 .实现查询：
学员的查询：
学员基本信息的查询：
a) 按姓名查询
b) 按城市查询。
c) 按市场部查询。
d) 按学院查询。
e) 按班级查询。
f) 按到校日期查询。
编写一个存储过程不管你输入几个条件都能等到你需要的结果（sql server 的写法）
CREATE PROCEDURE pro_select
@sname varchar(20),
@addr varchar(20),
@baoxiao varchar(20),
@xueyuan varchar(20),
@sno varchar(20),
@comdate datetime
as
create table #mytp
(s_no varchar(20))
declare @i int
set @i=0
if @sname<>''
begin
insert into #mytp select s_number from s_info where s_name=@sname
set @i=@i+1
end
if @addr<>''
begin
insert into #mytp select s_number from s_info where addr=@addr
set @i=@i+1
end
if @baoxiao<>''
begin
insert into #mytp select s_number from s_info where bao_xiao=@baoxiao set @i=@i+1
end
if @xueyuan<>''
begin
insert into #mytp select s_number from s_info where xueyuan=@xueyuan
set @i=@i+1
end
if @sno<>''
begin
insert into #mytp select s_number from s_info where left(s_number,len(s_number)-2)=@sno
set @i=@i+1
end
if @comdate<>''
begin
insert into #mytp select s_number from s_info where come_date=@comdate set @i=@i+1
end
select * from s_info
where s_number in (
select s_no from #mytp group by s_no having count(*)=@i)
SELECT * FROM s_info
--参数依次为姓名、城市、市场部、学院、班级、到校日期
exec pro_select '方超','山西','','','',''
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------