当前位置:Gxlcms > mysql > MySQL之-基本操作的代码示例汇总

MySQL之-基本操作的代码示例汇总

时间:2021-07-01 10:21:17 帮助过:47人阅读

本文记录了MySQL的一些常用操作,不多说了,直接一一列出:

/*
Author: liuyazhuang
Date: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 as
select 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=merge The 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.id 2、select * from table1 left join table2 on table1.id = table2.id 3. 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.id 5. select * from table1 use index(key1,key2) where key1=1 and key2=2 and key3=3 6. 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.gxlcms.com/*/ 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 integer contains sql begin 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 integer contains sql begin 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 integer contains sql begin 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. 不支持delete 2. 不支持事物隔离 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 Screenshots where bug_id=1 and image_id=2; /*问题:生成了 文件名 是aaa111的文件,但是打开只显示没有预览1B???????????原图片249kb*/ select screenshot_image into dumpfile 'F:\\aaa222.jpg' from Screenshots where 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'; /**/ /**/

以上就是MySQL之-基本操作的代码示例汇总的详细内容,更多请关注Gxl网其它相关文章!

人气教程排行