MYSQL杂记

数据库范式：1234
表中不能有重复字段，且字段不能拆分
DDL数据定义语言 DML DCL

INNODB，mysam：mysql常用的表类型

mysql默认端口3306
mysql分为客户端和服务器，只有服务器端开启以后，客户端才可以使用服务。安装时选择自动启动服务时，每次开机都会自动启用mysql服务。手动启动方式：控制面板--性能与维护--管理工具--服务--MySQL--启动（或右击设置）
服务启动后可以通过客户端登陆。
（1）DOS界面客户端登陆
mysql -h localhost -u root -p
;或\g来结束命令
exit退出
（2）运行中直接输入mysql -h localhost -u root -p或者mysql -h 127.0.0.1 -u root -p
（3）开始菜单中找到mysql command line client



mysql需要配置path变量（bin文件夹的路径）
mysql配置：（1）开始菜单中MYSQL选项中有配置选项，选重新配置
           （2）修改配置文件：安装目录下

bin文件下为常用命令可执行文件；lib为库文件；share为字符集信息；my.ini为配置文件；其他.ini为配置文件模板。datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/

mysqld？？
MYSQL图形管理工具：4个：GUI PHPMYSQL navicat sqlyog

免安装安装方法：解压软件包--创建MY.INI --修改MY.INI（datadir workdir server）--设置MYSQL服务--修改path变量--启动和关闭服务;免安装的默认没有密码


MYSQL开源、跨平台、价格、方便
1、数据是如何存储的？

SHOW WARNINGS显示警告
mysql中数据类型：1、整型：TIMYINT
当插入长度超过设置长度，没有超过默认长度时，可以插入。tinyint255,int,bigint
2、浮点数 FLOAT（6,2）表示总共六位，2位小数。插入时会四舍五入处理。float double型没有警告，decimal（定点数）型有警告。定点数以字符串形式存储，无误差。float型有误差。
3、year类型，可以用四位数字或字符赋值，范围为1901-2155，超过范围会赋值为0000.当不足4位时，会自动加20或19前缀。
4、time类型，3字节
5、date类型，4字节


操作数据库：创建、删除、存储引擎及其选择
show databases;//看现有数据库
create database 数据库名；//创建数据库
use 数据库名；//选择数据库
drop database 数据库名;//删除数据库
mysql中表可以用不同存储方式存储（不同存储引擎）：MYISAM CSV MRG_MYISAM INNODBMEMORY
show engines /G//显示数据库支持的存储引擎
show variables like 'hava%';//
show variables like 'storage_engine';//看当前存储引擎
INNODB支持AUTO_INCREMENT，支持外键、表空间；效率差，占空间大；为mysql默认引擎
MYISAM的表存储为三个文件：文件名字与表明相同，扩展名为frm myd myi其中frm的文件存储表结构,myd 存储；空间小，但是并发性不好
MEMORY使用内存中的内容来创建表，所有数据存在内存中，容易丢失，关机或重启则不存在了
存储引擎选择：事物安全、外键选INNODB；常存储出入选MYISAM；要求快，且表不大选MEMORY


show create table 表名 /G 查看表结构
创建、修改删除表

create table 表名（属性名 数据类型【完整性约束条件】，属性名 数据类型，属性名，数据类型）；//创建表
完整性约束条件：primary key:该属性为主键；foreign key：该属性为外键；not null:该属性不空；auto_increment：自动增长；unique：该属性值唯一；
default：该属性为默认值。
主键：唯一标识该表每条信息。如人和身份证：逐渐必须唯一，任何两个不得相同；非空；主键可以是单一字段，也可以是多个字段组合
单字段主键：属性名 数据类型[primary key]
设置多字段主键：primary key(stu_id,course_id)
外键：必须依赖于已存在的父表的主键，且其可为空值，用来把此表与父表相关联
constraint 外键名字 foreign key(字段名1，字段名2) references 父表名（关联字段）
discribe 表名；//查看表结构
属性名 数据类型 AUTO_INCREMENT
属性名 数据类型 UNIQUE
属性名 数据类型 default 默认值


查看表结构describe
describe 表名；//查看表结构
修改表alter table
alter table 旧表名 rename 新表名；//修改表名
修改字段数据类型
later table 表名modify 属性名 数据类型；//修改字段数据类型
修改字段名
alter table 表名 change 旧属性名 新属性名 数据类型；//修改字段名
增加字段
alter table 表名 add 属性名1 数据类型[完整性约束条件][first|after 属性名2]；//增加字段
删除字段
alter table 表名 drop 属性名；//删除字段
修改字段排列位置
alter table 表名 modify 属性名1 数据类型 first|after 属性名2；//
其中first是把属性名1修改到第一个位置；after是把属性名1修改到属性名2之后
更改表的存储引擎
alter table 表名engine=存储引擎名；//修改存储引擎
删除外键
alter table 表名 drop foreign key 外键别名；//删除外键
删除表
drop table 表名；
删除被其他表关联的父表（先alter table删除关联的外键）

GEOMETRY类型只能建立在MYISAM存储引擎中
索引：表中一列或多列
提高检索数据的速度，对于字表和父表查询提高速度，使用分组和排序子句查询提高速度
创建和维护要耗费时间，索引要占用存储空间；增加删除和修改数据时要动态维护索引

索引分类：普通索引、唯一性索引、全文索引、单列索引、多列索引、空间索引
为了使索引使用效率高，要考虑在哪些字段上创建什么类型的索引
1）选唯一性索引：当字段值是唯一性的，可以为该字段建立唯一性索引，很快确定对应记录信息
2）经常需要排序、分组、联合操作的字段：要建立索引，使查询速度快
3）常作为查询条件的字段要建立索引：可以加快查询速度
4）限制索引数目：所占空间及修改表时的更新代价
5）尽量使用数据量少得索引
6）尽量用前缀来索引：如对于TEXT 和BLOG类型，质监所前面若干字符
7）删除不再使用的索引或很少使用的索引

创建索引：在表的一列或多列上创建（可在创建时、已存在的、alter table）
create table 表名(属性名 数据类型[完整性约束]，属性名 数据类型[完整约束],index（属性名）)；//普通索引
create table 表名(属性名 数据类型[完整性约束]，属性名 数据类型[完整约束],unique index索引名（属性名 排序方式）)；//唯一索引
create table 表名(属性名 数据类型，属性名 数据类型，fulltext index 索引名（属性名）) engine=myisam;//创建全文索引（只有myisam存储引擎支持）
create table 表名(id int,subject varchar(30),index index_st(subject(10));//用前缀创建单列索引
create table 表名(属性名 数据类型[完整性约束]，属性名 数据类型[完整性约束]，属性名 数据类型[完整性约束]，index 索引名（属性名1，属性名2）)；//创建多列索引
空间索引必须用MYISAM存储引擎


使用索引查询：explain select *from index5 where name='a' and sex='man'/G
当用多列索引查询时，若查询条件中使用到了索引中的第一个字段，那么该索引会被使用，否则，该索引不会被使用



在已经存在的表上创建索引：
create [unique fulltext ]index 索引名 on 表名（属性名）；//在特定属性上创建索引
删除索引
drop index 索引名on 表名


视图是一个虚拟表，从数据库中一个或多个表中导出，数据库中只存放视图定义，而不是数据，数据从数据库中的表中获得并随之改变。
视图使操作简单化，操作表简单性、安全性逻辑独立性。
所见即所需，可为经常用到的查询定义一个视图；通过视图只能操作指定数据；

创建视图：可建立在一张或多张表上
create [algorithm={undefined、merge、temptable}]view 视图名【属性单】as select[with [cascaded、local] check option];
查询创建视图权限：select select_priv,create_view_priv from mysql.user where user='root';
单表创建视图：
create view depatment_view1 as select * from department;//创建department表中所有属性的视图。
desc department_view1;//看视图结构
create view depatment_view2(name,function,location) as selected d_name,function,address from department;//创建基于表中特定列的视图
create algorithm=merge view worker_view(name,department,age) as select d_name,department.d_name,2009-birthday from worker,department where worker.d_id=department.d_id with local check option;//创建基于两个表的视图
用户权限在mysql下得user表中保存
查看视图：desc、 show create status、 show create view、 information_schema数据库下views表
修改视图
create or replace [] view 视图名 as select语句
alert [] view 视图名 as select语句

更新视图
update department_view3 set name='科研部',function='科研',address='1号楼';//更新视图，同时更新对应表中数据
视图更新特例：视图中包含sum count max min等函数时，视图不能更新；视图中包含union、union_all、distinct、、group by 、having、等关键字时不可以更新；常量视图不可更新；视图中select包含子查询时不可更新；由不可更新的视图导出的视图不可更新；视图的algorithm为temptable时不可更新；试图对应的表中存在没有默认值且不能为空的字段，且此字段没有包含在视图中，此时视图不可更新

删除视图：删除视图定义，不能删除数据
drop view [if exists] 视图名列表 [restrict|cascade]
查看是否有删除视图权限：select drop_priv from mysql.user where user='root';



触发器：insert update delete触发操作
create trigger 触发器名 before|after 触发事件 on 表名 for each row 执行语句；
create trigger trigger1 begore  insert on department for each row insert into trigger_time values(now());//创建单执行语句触发器
create trigger trigger1 begore  insert on department for each row begin 执行语句列表end ;//创建单执行语句触发器(之前要设置语句结束符不为分号)
delimiter &&//改变结束符为&&
delimiter ;//改变结束符为;

显示触发器信息 show triggers \G
触发器详细信息都再information_schema数据库下的triggers 表中 select * from information_schema.triggers \G
删除触发器
drop trigger 触发器名；


插入数据
insert 可以单多字段，单多记录，从一个表中查插入另一个表中
insert into 表名（属性名列表） values(记录值)，（记录值），（记录值）；
insert into 表名1（属性列表）select 属性列表2 from 表名2 where 条件；//把查询结果插入表中
更新数据（可同时一条或多条记录）
update 表名 set 属性名1=取值1，属性名2=取值2 where 条件；
删除数据
delete from 表名 [where 条件];//删除所有或部分记录


查询数据
基本语法、单表查询、聚合函数查询、多表联合查询、子查询、合并查询结果、为表和字段取别名、正则表达式查询
select 属性列表 from 表名/视图列表 [where 条件] [group by属性名  [having 条件]][order by属性[ASC|DESC]];
按group by 属性对结果进行分组，他常与count sum等一起用
select * from 表名 where 属性名[not]in（，）；
查询条件：比较、指定范围（between and not between and）、in not in、like not like、is [not]null、and or
select *from employee where homeaddress like 'beijing%';//其中%为通配符
select * from employee order by age desc，id asc ;//结果按照age降序,id升序排列

分组查询group by:将查询结果按某个或多个字段进行分组，字段值相当的为一组group by [having ][with rollup]
select * from employee group by sex;//只显示这一组里第一个记录
select sex,group_concat(name) from employee group by sex;//将所有符合的NAME值在一行显示
select sex,count(sex) from employee group by sex;//将符合条件记录数目显示出来
select sex,count(sex) from employee group by sex having count(sex)>=3;//满足条件的记录才会显示
select * from employee group by sex，id;//同时用两个字段进行分组
select sex,count(sex) from employee group by sex with rollup;//最后显示求和
limit限制查询结果最多显示数量（是否指定初始位置）
select *from employee limit 2;//只显示两个记录
select *from employee limit 1，2;//从第二条记录开始显示，显示两条记录
集合函数(常与group by一起用，先分组后针对子集合运算)：count  sum avg max min
count(*)为计数记录数
select num, sum(score) from grade group by num;
select course, avg(score) from grade group by course;
select num, avg(score) from grade group by num;
select num,course,max(score) from grade group by course;//先分组后查询
连接查询（内连接和外连接、复合查询）：查询多个表，当多个表中存在有同样意义的字段，可以将两个表连接起来
内连接查询(多个表中某字段值相同时)：
select num, name, employee.d_id, department.d_id from employee, department where employ.d_id=department.d_id;
外连接查询（左右连接查询）：
select num,name,employee.d_id,age from employee left join department on employee.d_id=department.d_id;//employee表中数据全显示，而department表中只有符合条件的记录才显示
select num,name,employee.d_id,age from employee right join department on employee.d_id=department.d_id;//department表中数据全显示，而employee表中只有符合条件的记录才显示
复合查询：连接查询再加order by或and等来对查询结果排序
子查询：将一个查询结果嵌入另一个查询条件中
select * from employee where d_id in (select d_id from department);
select d_id, d_name from department where d_id =(select d_id from employee where age=24);
<>和!=是一个意思
select * from employee where not exists (select d_name from department where d_id=1003);
any、all关键字通常与比较运算符一起使用
select *from competer where score>=any (select score from scholoship );
合并查询结果
union去掉相同记录union all简单合并到一起
select d_id from employee union select d_id from department;
select d_id from employee union all select d_id from department;
为表和字段取别名
表名 表的别名
字段别名 as 字段原名
select d.d_id as department_id,d.d_name as department_name,d.function from department d where d.d_id=1001;
正则表达式查询：某种模式去匹配某类字符串，比通配符强大
属性名regexp‘匹配方式’
select * from info where name regexp '^L';//以L开头的
select * from info where name regexp '^aaa';//以aaa开头的
select * from info where name regexp 'c$';//以c结尾的
select * from info where name regexp 'aaa$';//以aaa结尾的
select * from info where name regexp '^L..y$';
select * from info where name regexp '[ceo]';//包含ceo三个字母中任何一个
select * from info where name regexp '[0-9]';//包含0-9十个数字中任何一个
select * from info where name regexp '[0-9 a-c]';//包含0-9或a-c
select * from info where name regexp '[^0-9 a-c]';//不包含这个范围内
select * from info where name regexp "cl|ub|lab";//包含任何一个字符串
select * from info where name regexp 'a*c';//包含a前c后
select * from info where name regexp 'a{3}';//包含3个a
select * from info where name regexp 'ab{1,3}';//ab出现1-3次





19:45 2011-12-6