转载请注明出处:http://blog.csdn.net/l1028386804/article/details/51133169
本文记录了MySQL的一些常用操作，不多说了，直接一一列出：
/*Author: liuyazhuangDate:2016-04-12*/use test;DROP table if exists equipment;create table equipment(assetTag INTEGER primary key,description varchar(20),acquired Date);/*数据表插入语句*/INSERT INTO equipment values (50431,'21寸监视器', DATE '2013-01-23');INSERT INTO equipment values (50532,'pad', DATE '2013-01-26');/*数据表增加新的列，并初始化*/ALTER Table equipment ADD COLUMN office VARCHAR(20);UPDATE equipment SET office='Headquarters';/*向修改后的表中插入记录*/INSERT INTO equipment(assetTag,description,acquired) values (50432,'IBM-PC',DATE '2013-01-01');/*创建代替表的新表（复制）*/DROP TABLE if EXISTS equipmentMultiSite;CREATE TABLE equipmentMultiSite(assetTag INTEGER PRIMARY KEY,office varchar(20) DEFAULT 'Headquarters',description varchar(20),acquired DATE);INSERT INTO equipmentMultiSite SELECT assetTag,'Headquarters',description,acquired FROM equipment;/*删除现有equipment用视图代替*/DROP TABLE equipment;CREATE VIEW equipment AS SELECT assetTag,description,acquired 'dateAcquired', FROM equipment WHERE office='Headquarters'; /*基础的查询*/select sum(amount) from payment where customer_id=1; /*某顾客从开户以来的消费总额*/select count(*) from payment where customer_id=1; /*某顾客从开户以来的消费次数*//*查询某顾客在2005年5月份和6月份消费总额,用"between”或者"<"，">"来建立条件*/select sum(amount) from payment where customer_id=1 and extract(year from payment_date)=2005 and extract(month from payment_date) between 05 and 06;select sum(amount) from payment where customer_id=1 and payment_date >= '2005-05-01 00:00:00' and payment_date < '2005-07-01 00:00:00';/*下面用法报警了*/select sum(amount) from payment where customer_id=1 and payment_date > UNIX_TIMESTAMP('2005-05-01 00:00:00') and payment_date < UNIX_TIMESTAMP('2005-07-01 00:00:00');/*查询某顾客在2005年一年的总消费*/select sum(amount) from payment where customer_id=1 and extract(year from payment_date)=2005;select sum(amount) from payment where customer_id=1 and extract(year from payment_date)='2005'; /*年份加单引号的写法也能通过，但不够简练*/ /*针对公司，查询2005年总的销售额*/select sum(amount) from payment where extract(year from payment_date)=2005;/*针对公司，查询2005年总的销售额，payment_copy没有建立索引*/select sum(amount) from payment_copy where extract(year from payment_date)=2005;explain select sum(amount) from payment where extract(year from payment_date)=2005;/*为payment_copy建立索引*/create index cust_id on payment_copy(customer_id);/*利用索引提高查询效率*/select sum(amount) from payment_copy where customer_id=1 and extract(year from payment_date)=2005;/*删除索引*/drop index cust_id on payment_copy;create index pay_date on payment(payment_date);drop index pay_date on payment;/*建立视图*//*利用视图查询*/select title from film_list;/*建立临时表*/create temporary table if not exists tmp_user(id integer not null auto_increment COMMENT '用户ID',name varchar(20) not null default '' COMMENT '名称',sex integer not null default 0 COMMENT '0为男，1为女',primary key(id))engine=MyISAM default charset=utf8 auto_increment=1;/*显示临时表的细节,show table显示包括table和view但不包括临时表*/desc tmp_user;/*mysql不支持在临时表上建立视图，会报错*//*create view v_tmp_user as select * from tmp_user;*//*mysql> create view v_tmp_user as select * from tmp_user;ERROR 1352 (HY000): View's SELECT refers to a temporary table 'tmp_user'*//*创建预处理语句*/create view pay_view asselect sum(amount) from payment where extract(year from payment_date)=2005;/*mysql中连接字符串用concat函数，||仅作逻辑运算用*/create view pay_view as select concat(c.first_name,' ',c.last_name) as name, sum(p.amount) as amount from payment_copy As p,customer As c where extract(year from p.payment_date)=2005 and p.customer_id=c.customer_id group by p.customer_id; select * from pay_view;/*输出前10条数据*/select * from pay_view limit 10;/*输出第11条到20条数据*/select * from pay_view limit 11,20;/*随机抽取5条数据,order by rand()*/select * from pay_view order by rand() limit 5;drop view pay_view;/*不能给视图添加索引，只能在基本表上添加索引*//*create index pay_view_index on pay_view(amount);*//*ERROR 1347 (HY000): 'test.pay_view' is not BASE TABLE*//*特别注意：如果视图和基本表一一对应，视图的更新可以达到同步修改基本表的目的；如果进行抽取，运算等操作得到视图，对视图的操作不能同步到基本表，视图中数据和基本表中的数据不一致，视图中的数据在内存中，做临时显示使用，有必要时才将数据同步到基本表*//*事务，mysql中默认每个sql语句是一个事务，就自动提交一次。考虑到性能，多个语句放在一个事务块中*/begin drop view pay_view;create view pay_view as select concat(c.first_name,' ',c.last_name) as name, sum(p.amount) as amount from payment_copy As p,customer As c where extract(year from p.payment_date)=2005 and p.customer_id=c.customer_id group by p.customer_id; select * from pay_view limit 10;end/*更改表的存储引擎*/alert table payment_copy engine=InnoDB;/*创建mysql定时执行的事件*/set global event_scheduler=1;create table testduty(time varchar(20) default null)engine=myisam default charset=latin1;create event if not exists test_event on schedule every 10 second do insert into testduty(time) values(now());/*删除定时任务*/drop event test_event;/*优化数据表*/optimize table payment;/*测试加入索引的性能*//*建立两张表：一个建立索引，另一个不建立*/create table if not exists test_has_index(id integer not null auto_increment,num integer not null default 0,d_num varchar(30) not null default '0',primary key(id))engine=MyISAM default charset=utf8 auto_increment=1;create table if not exists test_no_index(id integer not null auto_increment,num integer not null default 0,primary key(id))engine=MyISAM default charset=utf8 auto_increment=1;/*创建存储过程，用于初始化数据表*/delimiter |create procedure i_test(pa integer,tab varchar(30))begin declare max_num integer default 10000; declare i integer default 0; declare rand_num integer; declare double_num char; if tab!='test_no_index' then select count(id) into max_num from test_has_index; while i < pa do if max_num < 10000 then select cast(rand()*100 as unsigned) into rand_num; select concat(rand_num,rand_num) into double_num; insert into test_has_index(num,d_num) values(rand_num,double_num); end if; set i=i+1; end while; else select count(id) into max_num from test_no_index; while i < pa do if max_num < 10000 then select cast(rand()*100 as unsigned) into rand_num; insert into test_no_index(num) values(rand_num); end if; set i=i+1; end while; end if;end | delimiter ;/*数据表中记录较少时，索引反而会影响性能*/call i_test(10,'test_has_index'); /*call调用存储过程，并传入参数*/select num from test_has_index where num!=0;explain select num from test_has_index where num!=0;/*Tips: where后的条件，order by ,group by 等这样过滤时，后面的字段最好加上索引。根据实际情况，选择PRIMARY KEY、UNIQUE、INDEX等索引，但是不是越多越好，要适度。*/select a.num as num1, b.num as num2 from test_no_index as a left join test_has_index as b on a.num=b.num;explain select a.num as num1, b.num as num2 from test_no_index as a left join test_has_index as b on a.num=b.num;/*Tips:数据量特别大的时候，最好不要用联合查询，即使你做了索引??*//*mysql中使用外键，必须选用innodb存储引擎，myisam不支持外键*//*建立user、order表,二者相关联,以下也是两个表关联的典型实例*/create table test_user(id int(10) not null auto_increment COMMENT '用户ID',name varchar(20) not null default '' COMMENT '名称',sex integer not null default 0 COMMENT '0为男，1为女',primary key(id))engine=innodb default charset=utf8 auto_increment=1;insert into test_user(name,sex) values("Han",1),("Max",2);/*建立表test_order,并同步设置了主键、索引、外键、存储引擎innodb*/create table test_order(order_id int(10) not null auto_increment comment '订单ID',u_id int(10) not null default 0 comment '用户ID',username varchar(20) not null default '' comment '用户名',money int(10) not null default 0 comment '钱数',datetime timestamp not null default current_timestamp comment '生成时间',primary key(order_id),index(u_id),foreign key order_f_key(u_id) references test_user(id))engine=innodb default charset=utf8 auto_increment=1;/*向order中插入数据*/insert into test_order(u_id,username,money,datetime) values(1,'Han',223,current_timestamp);insert into test_order(u_id,username,money,datetime) values(2,'Max',423,current_timestamp);delete fromm user where id=1;insert into test_order(u_id,username,money,datetime) values(5,Sophe,223,current_timestamp);/*外键维护数据完整性的方式有5种cascade 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。set null 从父表删除或更新行，并设置子表中的外键列为NULL。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。no action InnoDB拒绝对父表的删除或更新操作。restrict 拒绝对父表的删除或更新操作。NO ACTION和RESTRICT都一样，删除ON DELETE或ON UPDATE子句。set default默认情况下，外键模式是*//*查询test_order表中的外键名称*/show create test_order;/*删除外键*/alter table test_order drop foreign key test_order_ibfk_1;/*新增外键,增加了on delete cascade on update cascade*/alter table test_order add foreign key(u_id) references test_user(id) on delete cascade on update cascade;/*此时更新主表，从表的u_id字段会自动更改*/update test_user set id=11 where id=1;/*下面学习几个比较使用的mysql函数*//*建立新的练习表*/create table comment(c_id int(10) not null auto_increment comment '评论ID',u_id int(10) not null comment '用户ID',name varchar(20) not null default '' comment '用户名',content varchar(1000) not null default '' comment '评论内容',datetime timestamp not null default current_timestamp,num1 int(10) default null,num2 int(10) default null,primary key(c_id))engine=myisam default charset=utf8 auto_increment=1;/*插入几条数据*/insert into comment(u_id,name,content,num1,num2) values (1,'test1','3445212',4,23),(2,'test2','up!!',43,21),(3,'test3','a3235b',23,23);/*greatest(),least()求最值*/select c_id, greatest(num1,num2) as max, least(num1,num2) as min, num1,num2 from comment where num1!="" and num2!="";/*concat(), concat_ws()用于连接多个字符串，CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式,第一个参数是其它参数的分隔符。间隔符可以自己指定*/select concat_ws(',',name, content,datetime) from comment;select concat(',',name, content,datetime) from comment; /*concat会将第一个，当作一个附加的字符*/select concat(name, ',',content,',',datetime) from comment; /*concat默认不加分隔符，可以手动加入，但不如concat_ws()简练*//*interval（）查询10小时之前的评论*/select * from comment where datetime <= (select now()-interval 10 hour as time_start);/*last_insert_id()查询最后插入记录的id*/select last_insert_id();/*mysql中可以加入正则匹配查询*/select * from comment where content regexp '[A-z][0-9]+';select * from comment where content regexp '^u';/*随机数使用rand()产生，cast（）用于转换类型*/select cast(rand()*1000 as unsigned) as rand_num;/*常用的时间处理函数extract(year from payment_date)=2005或者year(),month(),day(), hour(),minute(),second(),week(), */select day(now()) as day;/*上面day（）完全可以代替下面的substring，更简练*/select substring(now(),9,2) as day;/*mysql中分表，大表分成多个小表，提高查询性能*//*利用merge进行分表*/drop table if exists user1;create table if not exists user1(id int(10) not null auto_increment,name varchar(20) not null default '',sex integer not null default 0,primary key(id))engine=myisam default charset=utf8 auto_increment=1;drop table if exists user2;create table if not exists user2(id int(10) not null auto_increment,name varchar(20) not null default '',sex integer not null default 0,primary key(id))engine=myisam default charset=utf8 auto_increment=1;insert into user1(name,sex) values('Alice',0),('Apple',1);insert into user2(name,sex) values('Bob',1),('Band',0);/*数据分析笔试题*/drop table if exists t1;create table if not exists t1(user_id int(10) not null,blog_id int(10) not null);drop table if exists t2;create table if not exists t2(blog_id int(10) not null,comment_id int(10) not null);insert into t1 values(1,1),(1,2),(1,3),(2,4),(2,5),(2,6),(2,7),(3,8);insert into t2 values(2,1),(2,2),(2,3),(2,4),(3,5),(4,6),(4,7),(4,8),(5,9),(5,10);alter t2 change user_id blog_id int(10);alter t2 change blog_id comment_id int(10); insert into t1 values(1,9),(1,10);insert into t2 values(9,11),(9,12),(10,13);alter table t2 change user_id blog_id int(10);select t1.user_id,t1.blog_id,t2.comment_id from t1 inner join t2 on t1.blog_id=t2.blog_id;select t1.user_id,t1.blog_id,count(t2.comment_id) from t1 inner join t2 on t1.blog_id=t2.blog_id group by t1.blog_id;select t1.user_id,t1.blog_id,count(t2.comment_id) as counts from t1 inner join t2 on t1.blog_id=t2.blog_id group by t1.blog_id order by counts desc limit 1; /*将上述select的结果插入一个表中*/drop table if exists t1_t2;create table if not exists t1_t2(user_id int(10),blog_id int(10),comment_counts int(10));insert into t1_t2 values(2,5,2),(1,9,2),(1,3,1),(1,10,1),(1,2,4),(2,4,3);/*注意insert into t1_t2表后不加values关键词*/insert into t1_t2 (select t1.user_id,t1.blog_id,count(t2.comment_id) as counts from t1 inner join t2 on t1.blog_id=t2.blog_id group by t1.blog_id order by counts desc);/*求按user_id分组取最大的2个comment_id对应的blog_id*下面经过验证*/select t.user_id,t.blog_id from t1_t2 t where 2>(select count(*) from t1_t2 where user_id=t.user_id and comment_counts>t.comment_counts order by t.user_id,t.comment_counts) order by t.user_id; select t1.user_id,t1.blog_id from t1 inner join t2 on t1.blog_id=t2.blog_id order by counts desc; group by t1.blog_id ; select t1.user_id,t1.blog_id,count(t2.comment_id) as counts from t1 inner join t2 on t1.blog_id=t2.blog_id group by t1.blog_id having count(t2.comment_id)<=2; order by counts desc; limit 1; /*按照blog_id分组取最大的值所在的行*//*将2个表merge成一个表，注意最后一行是engine=merge，而不是type=mergeThe older term TYPE is supported as a synonym for ENGINE for backward compatibility,but ENGINE is the preferred term and TYPE is deprecated. *//*但是，如下方式直接建立alluser还是报错了1168：unable to open underlying table which is differently defined or of non-myiasm*//*drop table if exists alluser;create table if not exists alluser(id int(10) not null auto_increment,name varchar(20) default '',sex integer not null default 0,index(id))engine=MERGE union=(user1,user2) INSERT_METHOD=last AUTO_INCREMENT=1;*//*最后决定采用曲线方式*/create table alluser like user1;alter table alluser engine=merge union(user1,user2);alter table alluser insert_method=last;desc alluser;/*从合成的表中查询*/select * from alluser;/*总表中插入记录*/insert into alluser(name,sex) values('Merry',1),('Han',0);select * from user1;select * from user2;/*更新总表中记录*/update alluser set sex=replace(sex,1,0) where id=2;/*特别要明白各种关联查询,注重查询效率*//*在一个 INNER JOIN 之中，可以嵌套 LEFT JOIN 或 RIGHT JOIN，但是在 LEFT JOIN 或 RIGHT JOIN 中不能嵌套 INNER JOIN。*//*INNER JOIN 运算 组合两个表中的记录，只要在公共字段之中有相符的值*/select a.actor_id,b.film_id from actor as a inner join film_actor as b on a.actor_id = b.actor_id limit 10;/*两个表普通连接*/select a.actor_id,film_id from actor as a, film_actor as b where a.actor_id = b.actor_id limit 10; /*各种查询关键字的顺序:join子句 联结条件 > where子句 联结条件和查询条件 > group by子句分组 > having子句搜索 >order by子句结果排序 > limit显示某些记录*//*连接查询常用模式*//*1、select * from table1, table2 where table1.id=table2.id2、select * from table1 left join table2 on table1.id = table2.id3. select * from table1 left join table2 using(id)4. select * from table1 left join table2 on table1.id=table2.id left join table3 on table2.id=table3.id5. select * from table1 use index(key1,key2) where key1=1 and key2=2 and key3=36. slect * from table1 ignore index(key1) where key1=1 and key2=2 and key3=3*//*验证各种连接结果,以film, film_actor, actor为例*/select f.title,fa.actor from film f left join film_actor fa on f.film_id = fa.film_id limit 10;select film.*,film_actor.* from film left join film_actor on film.film_id = film_actor.film_id limit 10;select film.title,film_actor.actor_id from film left join film_actor on film.film_id = film_actor.film_id limit 10;select film.title,film_actor.actor_id from film left join film_actor using(film_id) limit 10;select film.title,film_actor.actor_id from film left join film_actor using(film_id) group by film.film_id limit 10;select film.title,film_actor.actor_id from film inner join film_actor on film.film_id = film_actor.film_id limit 10;/**//**//**/ /*查询中去掉重复字段*//*建立测试数据表school_report*/drop table if exists school_report;create table school_report(id int(10) not null auto_increment comment '表ID',u_id int(10) not null comment '学生ID',name varchar(20) not null default '' comment '学生姓名',score varchar(4) not null default 0 comment '学生成绩',message varchar(50) not null default '',dateline timestamp not null default current_timestamp,primary key(id))engine=innodb default charset=utf8 auto_increment=1;/*插入测试数据*/insert into school_report(u_id,name,score,message) values(1,'张三',89,'helloworld'),(1,'张三',90,'hello'),(2,'李四',92,'helloworld'), (3,'王五',93,'world');/*查询，去掉重复*/select distinct name,score from school_report;select *, count(distinct name) from school_report group by name;/**/select * from school_report a inner join( select max(dateline) as dateline from school_report group by u_id) b on a.dateline = b.dateline group by id order by a.dateline desc;/*记录和分析 花费时间较多的select*//*首先进行如下设置，设置long_query_time时间限*/show variables like "%long%";set global long_query_time=2;/*检查并开启慢查询,会显示mysql-slow.log文件的路径*/show variables like "%slow%";set global slow_query_log='ON';/*这样超过long_query_time的查询会记录到mysql日志中*//*分组后前n条数据: http://www.jb51.net/article/31590.htm*/drop table if exists tb;create table tb (name varchar(10),val int,memo varchar(20));insert into tb values('a', 2, 'a2(a的第二个值)'),('a',1,'a1--a第一个值'),('a',3,'a3--a第三个值'),('b',1,'b1--b第一个值'),('b',3,'b3--b第三个值'),('b',2,'b3--b2b2b2'),('b',4,'b4b4b4'),('b',5,'b5b5b5b5');/*按name分组取value 最大的值的记录*/--方法1：select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name --方法2： select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val); --方法3： select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name; --方法4： select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name ;--方法5 select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name ;/* name val memo ---------- ----------- -------------------- a 3 a3:a的第三个值 b 5 b5b5b5b5b5 方法三、四效率比较高*/ /*按name分组取val最小的值的记录*/--方法1：select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name --方法2： select a.* from tb a where not exists(select 1 from tb where name = a.name and val < a.val); --方法3： select a.* from tb a,(select name,min(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name ;--方法4： select a.* from tb a inner join (select name , min(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name ;--方法5 select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val < a.val) order by a.name ;/* name val memo ---------- ----------- -------------------- a 1 a1--a的第一个值 b 1 b1--b的第一个值 */ /*按name分组取出第一次出现的记录*/select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name /* name val memo ---------- ----------- -------------------- a 2 a2(a的第二个值) b 1 b1--b的第一个值 */ /*按name分组随机取一条数据*/select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name/* name val memo ---------- ----------- -------------------- a 1 a1--a的第一个值 b 5 b5b5b5b5b5 */ /*按name分组取最小的2个（N个）val所在记录*/select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.valselect a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) order by a.name,a.val select a.* from tb a where exists (select count(*) from tb where name = a.name and val < a.val having Count(*) < 2) order by a.name /* name val memo ---------- ----------- -------------------- a 1 a1--a的第一个值 a 2 a2(a的第二个值) b 1 b1--b的第一个值 b 2 b2b2b2b2 */ /*按name分组取最大的2个（N个）val所在记录*/select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*) < 2) order by a.name /* name val memo ---------- ----------- -------------------- a 2 a2(a的第二个值) a 3 a3:a的第三个值 b 4 b4b4 b 5 b5b5b5b5b5 */ /*MySQL中序列有auto_increment，Oracle中使用sequence序列和**.NextVal*//*在Oracle中可以添加触发器，实现mysql形式的自增长；mysql也可以通过定义过程实现Oracle的序列语法*//*mysql实现currval()*/drop table if exists sequence;create table sequence(name varchar(50) not null,current_value int not null,increment int not null default 1,primary key(name))engine=InnoDB;insert into sequence values('MovieSeq',3,5);Drop function if exists currval;delimiter $create function currval(seq_name varchar(50))returns integercontains sqlbegin declare value integer; set value=0; select current_value into value from sequence where name=seq_name; return value;end$delimiter ;/*测试定义的currval*/select currval('MovieSeq');/*mysql实现nextval()*/drop function if exists nextval;delimiter $create function nextval(seq_name varchar(50))returns integercontains sqlbegin update sequence set current_value=current_value+increment where name=seq_name; return currval(seq_name);end$delimiter ;/*测试定义的nextval()*/select nextval('MovieSeq');select nextval('MovieSeq');/*mysql实现setval(). 报错了！！*/drop function if exists setval;delimiter $create function setval(seq_name varchar(50),val integer)returns integercontains sqlbegin update sequence set current_value=val; where name = seq_name; return currval(seq_name);end$delimiter ;/*测试定义的setval()*/select setval('MovieSeq',150);select curval('MovieSeq');select nextval('MovieSeq');/*即作为主键同时又是外键的情况*/drop table if exists Issues;create table Issues (issue_id integer auto_increment primary key);drop table if exists Bugs;create table Bugs(issue_id integer primary key,foreign key(issue_id) references Issues(issue_id));insert into Issues values();insert into Issues values();select * from Issues; /*1,2*/select * from Bugs; /*null*/insert into Bugs values(4); /*报外键引用错误*/insert into Bugs values(2); /*正常插入，只要插入Issues中存在的数据就OK*/select * from Bugs; /*2*//*浮点数的表示*//*浮点数不能比较，要用近似相等*/drop table if exists Num;create table Num(id integer auto_increment primary key,price float not null default '0.0',sum float not null default '0.0',);/*float类型的数据存储时满足IEEE754二进制浮点数的标准，表达的范围很大，舍入方式不是四舍五入；当存储的值在Integer和numeric类型所支持的范围内，就不必选择float类型。推荐用numeric*/alter table Num add column price2 numeric(9,2) default '0.0';/*numeric(9,2)定义的price2列存储的就有2位小数位，可以进行=精确比较，即使插入了3个小数位，会四舍五入*//*限定列的有效值：如果，可选范围固定，使用Enum和Check约束,check约束使用范围更广，如检查start永远小于end；否则，将可选数据建立一个检查表*/drop table if exists PersonalContacts;Create table PersonalContacts(id integer auto_increment primary key,salutation varchar(5) check (salutation in ('Mr.','Mrs.','Ms.','Dr.')));drop table if exists PersonalContacts;Create table PersonalContacts(id integer auto_increment primary key,salutation enum('Mr.','Mrs.','Ms.','Dr.'));/*用Enum限定列的取值范围，插入不属于其范围的值时，未报错但插入字段为空。插入范围内的值，一切正常*/insert into PersonalContacts(salutation) values('Mz.');select * from PersonalContacts;insert into PersonalContacts(salutation) values('Ms.');select * from PersonalContacts;/*注：enum是mysql独有的特性*//*要想增加某列取值范围，需要更改数据表，ETL（抽取--》转换--》加载数据）,但麻烦*/alter table PersonalContacts modify column salutation enum('Mr.','Mrs.','Ms.','Dr.','Mz.'); insert into PersonalContacts(salutation) values('Mz.');select * from PersonalContacts;/*可选数据建立一个检查表*/drop table if exists Salutation;create table Salutation(status varchar(5) primary key);insert into Salutation(status) values('Mr.','Ms.');drop table if exists PersonalContacts2;/*定义外键时，加上on update cascade，重命名一个值就比较方便*/create table PersonalContact2(id integer auto_increment primary key,status varchar(5),foreign key(status) references Salutation(status) on update cascade );/*方便查询、插入和更改*/select status from Salutation order by status;insert into Salutation(status) values('Mss.');update Salutation set status='Dr.' where status='Mss'; /*使用检查表，支持废弃数据(保持历史数据的值，对新插入的数值加限制)*/alter table Salutation add column active enum('inactive','active') not null default 'active';/*使用update代替delete废弃一个值*/update Salutation set active='inactive' where status='Dr.';select status from Salutation where active='active';/*mysql数据类型：1.数值类型MYSQL支持所有标准SQL,这其中包括: 精确数值数据类型:INTERGER/INT,SMALLINT,DECIMAL/DEC,NUMERIC 近似数值数据类型:FLOAT,REAL,DOCULE PRECISION BIT数据类型作为对标准SQL的扩展,MySQL还支持TINYINT,MEDIUMINT及BIGINT 如果ZEROFILL指定给数值列,则MYSQL会自动添加UNSIGNED属性 整数或浮点类型均可指定AUTO_INCREMENT属性,当被赋值NULL或0时会自动设置成下一个序列值,AUTO_INCREMENT序列从1开始 MYSQL将DOUBLE与DOUBLE PRECISION等同,将REAL也与DOUBLE PRECISION等同 DECIMAL与NUMERIC用来存储精确数值数据,NUMERIC是DECIMAL的一种实现,MYSQL5.5将DECIMAL与NUMERIC存储为二进制格式 BIT数据类型用来存储比特数值,BIT(M)中M允许从1到64,位数不足时会自动左侧补0 SERIAL 等同于BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 2.日期时间类型 当使用DATE与TIME类型时通常需要注意的事项： MYSQL会尝试从各种格式的输入中解析日期与时间，但还是有格式限制的 只有两位数的YEAR，会让MYSQL感到迷惑，所以MYSQL会尝试作下面的解析：在70～99之间的年会被解析成：1970～1999，在00～69之间的年会被解析成2000～2069 MYSQL在解析日期时，日期的输入顺序必须为：year-month-day,否则无法正确解析 默认情况下，MYSQL会将超出范围或不合法的日期与时间，转换成0，但对于超出范围的TIME，会将其归整到一个恰当的时间点上。 3.字符串类型 字符串类型包括:CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM和SET. *//*图片等多媒体信息存储在数据库内，还是存储在数据库外（文件系统中）*//*存储在数据库外（仅用varchar存放图片文件路径）缺点：1. 不支持delete2. 不支持事物隔离3. 不支持回滚4. 文件不支持数据库备份工具5. 不支持访问权限限制6. 文件不是SQL数据类型*//*存储在数据库内（使用Blob类型）优点：解决 存储在数据外 的6个缺点Blob 初始化 可以从文件中导入；Blob 内容 也可以导入文件中。缺点：需要数据表占用空间更大，备份更大*/drop table if exists Bugs;create table Bugs(bug_id integer auto_increment primary key);/*image_id integer auto_increment not null,*/drop table if exists Screenshots;/*SERIAL 等同于BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE*/create table Screenshots(bug_id integer not null,image_id serial not null,screenshot_image BLOB,caption varchar(100),primary key (bug_id,image_id),foreign key (bug_id) references Bugs(bug_id));/*如果采用 记录路径名的 方式，存储在数据库之外的文件系统中*//*create table Screenshots(bug_id integer not null,image_id serial not null,screenshot_path varchar(100),caption varchar(100),primary key (bug_id,image_id),foreign key (bug_id) references Bugs(bug_id));*//*插入数据*/insert into Bugs values();insert into Bugs values();insert into Bugs values();insert into Bugs values();insert into Bugs values();insert into Bugs values();/*初始化Blob数据类型*/insert into Screenshots(bug_id,screenshot_image) values(1,load_file('f:\aaa.jpg')); /*这里插入时，必须指明bug_id*/select bug_id,image_id from Screenshots;/*验证发现image_id是自增的*/select screen_image from Screenshots;/*会显示出一大片的图片二进制字符*/insert into Screenshots(bug_id,screenshot_image) values(1,load_file('f:\bbb.jpg'));insert into Screenshots(bug_id,screenshot_image) values(2,load_file('f:\ccc.jpg'));delete from Screenshots where bug_id=1 and image_id=3;/*将数据库中存储的Blob图片，导出到文件系统*/select screenshot_image into dumpfile 'F:\aaa111.jpg'from Screenshotswhere bug_id=1 and image_id=2;/*问题：生成了 文件名 是aaa111的文件，但是打开只显示没有预览1B???????????原图片249kb*/select screenshot_image into dumpfile 'F:\\aaa222.jpg'from Screenshotswhere bug_id=1 and image_id=2;/*索引：在SQL标准中没有很多的说明，不同数据库实现有较大的自由度和区别*//*合理地使用索引!!!!错误的观点：1. 不使用索引或索引不足2. 使用太多的索引3. 执行一些让索引无能为力的查询1. 要根据具体情况，分析需要建立哪些索引，在维护索引的开销 和 索引带来的加速之间进行比较2. 通过mentor方法： measure(慢查询)，解释（explain),挑选，性能测试，优化，重建（定期维护：analyze table or optimize table）注意：1. insert update delete都要维护索引2. 索引可以快速找到要delete或update的记录3. 主键会自动建立索引，没有必要手工加入4. 对于过长的varchar不建议建立索引，不太可能进行全匹配查找5. 可以根据实际需求建立组合索引*/drop table if exists Bugs1;create table Bugs1(bug_id serial primary key,date_reported date not null,summary varchar(80) not null,status varchar(10) not null,hours numeric(9,2),index(bug_id,date_reported,status));/*考虑：bugs与多个标签的关系（多个标签不互斥）*//*一个 bugs对应的最多标签数确定时，可以采用建立多个tage列,但在查询、添加和删除tag都很方便*//*一个 bugs对应的最多标签数确定时，强烈建议使用 从属表，仅使用一列存储【多值属性】，多个值存储在多行，而不是多列！从表中定义外键和主记录关联*/drop table if exists Tags;create table Tags(bug_id integer not null,tag varchar(20),primary key(bug_id,tag),foreign key(bug_id) references Bugs(bug_id));insert into Tags(bug_id,tag) values('1','crash'),('2','performance'),('2','printing'),('2','crash'),(3,'printing');select * from Tags where bug_id=2;/*查询和 某标签 相关的所有bug*/select * from Bugs join Tags using(bug_id) where tag='performance';/*查询和 某2个标签 相关的所有bug记录*/select * from Bugs join Tags as t1 using (bug_id)join Tags as t2 using (bug_id)where t1.tag='printing' and t2.tag='performance';/*结果：+--------+----------+-------------+| bug_id | tag | tag |+--------+----------+-------------+| 2 | printing | performance |+--------+----------+-------------+*//*使用从属表 可以更方便地 添加和移除 bugs和tag标签之间的关系*/insert into Tags(bug_id,tag) values (3,'save');delete from Tags where bug_id=2 and tag='crash';/**//**/