首页 > 数据库 > mysql教程 > MySQL之-基本操作的代码示例汇总

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

黄舟
发布: 2017-03-13 13:14:09
原创
1187 人浏览过

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

882

883

884

885

886

887

888

889

890

891

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

908

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

931

932

933

934

935

936

937

938

939

940

941

942

943

944

945

946

947

948

949

950

951

952

953

954

955

956

957

958

/*

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 >= &#39;2005-05-01 00:00:00&#39; and payment_date < &#39;2005-07-01 00:00:00&#39;;

/*下面用法报警了*/

select sum(amount) from payment where customer_id=1 and payment_date > UNIX_TIMESTAMP(&#39;2005-05-01 00:00:00&#39;) and payment_date

< UNIX_TIMESTAMP(&#39;2005-07-01 00:00:00&#39;);

 

/*查询某顾客在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)=&#39;2005&#39;; /*年份加单引号的写法也能通过,但不够简练*/ 

/*针对公司,查询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 &#39;用户ID&#39;,

name varchar(20) not null default &#39;&#39; COMMENT &#39;名称&#39;,

sex integer not null default 0 COMMENT &#39;0为男,1为女&#39;,

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&#39;s SELECT refers to a temporary table &#39;tmp_user&#39;

*/

/*创建预处理语句*/

 

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,&#39; &#39;,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): &#39;test.pay_view&#39; is not BASE TABLE*/

 

/*特别注意:如果视图和基本表一一对应,视图的更新可以达到同步修改基本表的目的;如果进行抽取,运算等操作得到视图,对视图的操作不能同步到

基本表,视图中数据和基本表中的数据不一致,视图中的数据在内存中,做临时显示使用,有必要时才将数据同步到基本表*/

 

 

/*事务,mysql中默认每个sql语句是一个事务,就自动提交一次。考虑到性能,多个语句放在一个事务块中*/

begin

drop view pay_view;

create view pay_view as

    select concat(c.first_name,&#39; &#39;,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 &#39;0&#39;,

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!=&#39;test_no_index&#39;  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,&#39;test_has_index&#39;); /*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 &#39;用户ID&#39;,

name varchar(20) not null default &#39;&#39; COMMENT &#39;名称&#39;,

sex integer not null default 0 COMMENT &#39;0为男,1为女&#39;,

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 &#39;订单ID&#39;,

u_id int(10) not null default 0 comment &#39;用户ID&#39;,

username varchar(20) not null default &#39;&#39; comment &#39;用户名&#39;,

money int(10) not null default 0 comment &#39;钱数&#39;,

datetime timestamp not null default current_timestamp comment &#39;生成时间&#39;,

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,&#39;Han&#39;,223,current_timestamp);

insert into test_order(u_id,username,money,datetime) values(2,&#39;Max&#39;,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 &#39;评论ID&#39;,

u_id int(10) not null comment &#39;用户ID&#39;,

name varchar(20) not null default &#39;&#39; comment &#39;用户名&#39;,

content varchar(1000) not null default &#39;&#39; comment &#39;评论内容&#39;,

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,&#39;test1&#39;,&#39;3445212&#39;,4,23),(2,&#39;test2&#39;,&#39;up!!&#39;,43,21),(3,&#39;test3&#39;,&#39;a3235b&#39;,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(&#39;,&#39;,name, content,datetime) from comment;

select concat(&#39;,&#39;,name, content,datetime) from comment; /*concat会将第一个,当作一个附加的字符*/

select concat(name, &#39;,&#39;,content,&#39;,&#39;,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 &#39;[A-z][0-9]+&#39;;

select * from comment where content regexp &#39;^u&#39;;

 

/*随机数使用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 &#39;&#39;,

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 &#39;&#39;,

sex integer not null default 0,

primary key(id)

)engine=myisam default charset=utf8 auto_increment=1;

 

insert into user1(name,sex) values(&#39;Alice&#39;,0),(&#39;Apple&#39;,1);

insert into user2(name,sex) values(&#39;Bob&#39;,1),(&#39;Band&#39;,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 &#39;&#39;,

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(&#39;Merry&#39;,1),(&#39;Han&#39;,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 &#39;表ID&#39;,

u_id int(10) not null comment &#39;学生ID&#39;,

name varchar(20) not null default &#39;&#39; comment &#39;学生姓名&#39;,

score varchar(4) not null default 0 comment &#39;学生成绩&#39;,

message varchar(50) not null default &#39;&#39;,

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,&#39;张三&#39;,89,&#39;helloworld&#39;),(1,&#39;张三&#39;,90,&#39;hello&#39;),(2,&#39;李四&#39;,92,&#39;helloworld&#39;),

        (3,&#39;王五&#39;,93,&#39;world&#39;);

 

/*查询,去掉重复*/

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=&#39;ON&#39;;

 

/*这样超过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(&#39;a&#39;, 2, &#39;a2(a的第二个值)&#39;),(&#39;a&#39;,1,&#39;a1--a第一个值&#39;),

(&#39;a&#39;,3,&#39;a3--a第三个值&#39;),(&#39;b&#39;,1,&#39;b1--b第一个值&#39;),(&#39;b&#39;,3,&#39;b3--b第三个值&#39;),

(&#39;b&#39;,2,&#39;b3--b2b2b2&#39;),(&#39;b&#39;,4,&#39;b4b4b4&#39;),(&#39;b&#39;,5,&#39;b5b5b5b5&#39;);

 

/*按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(&#39;MovieSeq&#39;,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(&#39;MovieSeq&#39;);

 

/*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(&#39;MovieSeq&#39;);

select nextval(&#39;MovieSeq&#39;);

 

/*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(&#39;MovieSeq&#39;,150);

select curval(&#39;MovieSeq&#39;);

select nextval(&#39;MovieSeq&#39;);

 

/*即作为主键同时又是外键的情况*/

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 &#39;0.0&#39;,

sum float not null default &#39;0.0&#39;,

);

 

/*float类型的数据存储时满足IEEE754二进制浮点数的标准,表达的范围很大,舍入方式不是四舍五入;

当存储的值在Integer和numeric类型所支持的范围内,就不必选择float类型。推荐用numeric*/

 

alter table Num add column price2 numeric(9,2) default &#39;0.0&#39;;

/*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 (&#39;Mr.&#39;,&#39;Mrs.&#39;,&#39;Ms.&#39;,&#39;Dr.&#39;))

);

 

drop table if exists PersonalContacts;

Create table PersonalContacts(

id integer auto_increment primary key,

salutation enum(&#39;Mr.&#39;,&#39;Mrs.&#39;,&#39;Ms.&#39;,&#39;Dr.&#39;)

);

/*用Enum限定列的取值范围,插入不属于其范围的值时,未报错但插入字段为空。插入范围内的值,一切正常*/

insert into PersonalContacts(salutation) values(&#39;Mz.&#39;);

select * from PersonalContacts;

 

insert into PersonalContacts(salutation) values(&#39;Ms.&#39;);

select * from PersonalContacts;

/*注:enum是mysql独有的特性*/

 

/*要想增加某列取值范围,需要更改数据表,ETL(抽取--》转换--》加载数据),但麻烦*/

alter table PersonalContacts modify column salutation enum(&#39;Mr.&#39;,&#39;Mrs.&#39;,&#39;Ms.&#39;,&#39;Dr.&#39;,&#39;Mz.&#39;);

  

insert into PersonalContacts(salutation) values(&#39;Mz.&#39;);

select * from PersonalContacts;

 

/*可选数据建立一个检查表*/

drop table if exists Salutation;

create table Salutation(

status varchar(5) primary key

);

 

insert into Salutation(status) values(&#39;Mr.&#39;,&#39;Ms.&#39;);

 

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(&#39;Mss.&#39;);

update Salutation set status=&#39;Dr.&#39; where status=&#39;Mss&#39;;

 

/*使用检查表,支持废弃数据(保持历史数据的值,对新插入的数值加限制)*/

alter table Salutation add column active enum(&#39;inactive&#39;,&#39;active&#39;) not null default &#39;active&#39;;

 

/*使用update代替delete废弃一个值*/

update Salutation set active=&#39;inactive&#39; where status=&#39;Dr.&#39;;

select status from Salutation where active=&#39;active&#39;;

 

 

/*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(&#39;f:\aaa.jpg&#39;));

/*这里插入时,必须指明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(&#39;f:\bbb.jpg&#39;));

insert into Screenshots(bug_id,screenshot_image) values(2,load_file(&#39;f:\ccc.jpg&#39;));

 

delete from Screenshots where bug_id=1 and image_id=3;

 

/*将数据库中存储的Blob图片,导出到文件系统*/

select screenshot_image into dumpfile &#39;F:\aaa111.jpg&#39;

from Screenshots

where bug_id=1 and image_id=2;

/*问题:生成了 文件名 是aaa111的文件,但是打开只显示没有预览1B???????????原图片249kb*/

 

select screenshot_image into dumpfile &#39;F:\\aaa222.jpg&#39;

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(&#39;1&#39;,&#39;crash&#39;),(&#39;2&#39;,&#39;performance&#39;),(&#39;2&#39;,&#39;printing&#39;),(&#39;2&#39;,&#39;crash&#39;),(3,&#39;printing&#39;);

select * from Tags where bug_id=2;

 

/*查询和 某标签 相关的所有bug*/

select * from Bugs join Tags using(bug_id) where tag=&#39;performance&#39;;

 

/*查询和 某2个标签 相关的所有bug记录*/

select * from Bugs

join Tags as t1 using (bug_id)

join Tags as t2 using (bug_id)

where t1.tag=&#39;printing&#39; and t2.tag=&#39;performance&#39;;

/*结果:

+--------+----------+-------------+

| bug_id | tag      | tag         |

+--------+----------+-------------+

|      2 | printing | performance |

+--------+----------+-------------+

*/

 

 

/*使用从属表 可以更方便地 添加和移除 bugs和tag标签之间的关系*/

insert into Tags(bug_id,tag) values (3,&#39;save&#39;);

delete from Tags where bug_id=2 and tag=&#39;crash&#39;;

 

/**/

 

/**/

登录后复制

以上是MySQL之-基本操作的代码示例汇总的详细内容。更多信息请关注PHP中文网其他相关文章!

相关标签:
来源:php.cn
本站声明
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
最新问题
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板