Home >Database >Mysql Tutorial >MySQL - Summary of code examples for basic operations
MySQL - Summary of code examples for basic operations
- 黄舟Original
- 2017-03-13 13:14:091173browse
本文记录了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.php.cn/*/
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';
/**/
/**/The above is the detailed content of MySQL - Summary of code examples for basic operations. For more information, please follow other related articles on the PHP Chinese website!
Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：MySQL - Introduction to several ways to add indexesNext article：MySQL - Introduction to several ways to add indexes