【MySQL】Mysql必会语句

--使用mysql root -p  提示权限不够： Access denied for user 'ODBC'@'localhost' (using password: YES)
msyql -u root -p

use goodsadmin; -- 使用数据库

alter table person add column card_id int(12); --增加列card_id
create table IDCard(card_id int(12) primary key, card_code varchar(25)); --创建IDcard 表



-- 添加外键约束：alter table 从表 add constraint 外键（形如：FK_从表_主表） foreign key (从表外键字段) references 主表(主键字段);
alter table person add constraint fk_person_IDcard_card_id foreign key (card_id) references IDCard(card_id);    --不加单引号

-- SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
select p.* ,c.* from  person p inner join idcard c on p.card_id = c.card_id where p.id = 1; --内连接查询

使用mysql root -p 提示权限不够： Access denied for user ‘ODBC’@‘localhost’ (using password: YES)

msyql -u root -p

use goodsadmin; – 使用数据库

alter table person add column card_id int(12); --增加列card_id
create table IDCard(card_id int(12) primary key, card_code varchar(25)); --创建IDcard 表

– 添加外键约束：alter table 从表 add constraint 外键（形如：FK_从表_主表） foreign key (从表外键字段) references 主表(主键字段);
alter table person add constraint fk_person_IDcard_card_id foreign key (card_id) references IDCard(card_id); --不加单引号

– SELECT <字段名> FROM <表1> INNER JOIN <表2> [ON子句]
select p.* ,c.* from person p inner join idcard c on p.card_id = c.card_id where p.id = 1; --内连接查询

– select <字段名> FROM<表1> LEFT JOIN <表2> [ON 子句]

– 左连接 如果数据不存在，左表记录会出现，而右表为null填充

alter table person add column nation_id int(12);
create table nation(nation_id int(12) primary key, nation_name varchar(12));
alter table person add constraint fk_person_nation_nation_id foreign key (nation_id) references nation(nation_id);

—增加某列
ALTER TABLE skill ADD COLUMN createdTime TIMESTAMP not
null DEFAULT now();

CREATE database myDB;
use myDB;

CREATE table course(
cid int(3) comment ‘课程编号’,
cname varchar(20) comment ‘课程名字’,
tid int(3) comment ‘授课老师编号’
);

CREATE table teacher(
tid int(3) comment ‘老师编号’,
tname varchar(20) comment ‘老师名字’,
tcid int(3) comment ‘老师卡编号’
);

CREATE table teacherCard(
tcid int(3) comment ‘老师卡编号’,
tcdesc varchar(200) comment ‘老师描述’
);

insert into course values(1,‘java’,1);
insert into course values(2,‘html’,1);
insert into course values(3,‘sql’,2);
insert into course values(4,‘web’,3);

insert into teacher values(1,‘tz’,1);
insert into teacher values(2,‘tw’,2);
insert into teacher values(3,‘tl’,3);

insert into teacherCard values(1,‘tzdesc’) ;
insert into teacherCard values(2,‘twdesc’) ;
insert into teacherCard values(3,‘tldesc’) ;

多表查询

– 查询教授SQL课程的老师的描述
EXPLAIN SELECT tc.tcdesc from teacherCard tc, course c, teacher t
where tc.tcid = t.tcid and c.tid = t.tid and cname=‘SQL’;

EXPLAIN SELECT tc.tcdesc from teacherCard tc where tc.tcid =(
SELECT t.tcid from teacher t where t.tid = (SELECT c.tid from course c where c.cname =‘SQL’)
);

create table test01
(
tid int(3),
tname varchar(20)
);

insert into test01 values(1,‘a’) ;

— 增加主键索引
alter table test01 add CONSTRAINT pk_id PRIMARY KEY (tid);

– system
explain select * from (select * from test01) as t where tid=1;

– const
explain select * from test01 where tid=1;

–eq_ref

– 增加主键索引
alter table teacherCard add CONSTRAINT pk_tcid PRIMARY KEY (tcid);
– 唯一索引
alter table teacher add CONSTRAINT uk_tcid UNIQUE INDEX (tcid);

–eq_ref
explain SELECT t.tcid from teacher t, teacherCard tc where t.tcid = tc.tcid;

insert into teacher values(4, ‘tz’, 4);
insert into teacherCard values(4, ‘tz222’);

– ref
alter table teacher add index name_index(tname);
explain select * from teacher where tname=‘tz’;

– range
alter table teacher add index tid_index(tid);
explain select * from teacher where t.tid < 3;

– all
show indexes from course;
explain select cid from course;

create table test_kl(
name char(20) not null DEFAULT ‘’
);
alter table test_kl add index name_index(name);
explain select * from test_kl where name =‘’;

alter table test_kl add index name_name1_index (name, name1);
explain select * from test_kl where name=‘’;
explain select * from test_kl where name1=‘’;

alter table test_kl add column name2 varchar(20) ;
alter table test_kl add index name2_index (name2) ;
explain select * from test_kl where name2 = ‘’ ;

— extra

create table test02
(
a1 char(3),
a2 char(3),
a3 char(3),
index idx_a1(a1),
index idx_a2(a2),
index idx_a3(a3)
);

explain select * from test02 where a1=‘’ order by a1;
explain select * from test02 where a1=‘’ order by a2; – using filesort

drop index idx_a1 on test02;
drop index idx_a2 on test02;
drop index idx_a3 on test02;

alter table test02 add index idx_a1_a2_a3(a1, a2, a3);
explain select * from test02 where a1=‘’ order by a3;
explain select * from test02 where a2=‘’ order by a3;
explain select * from test02 where a1=‘’ order by a2;
explain select * from test02 where a2=‘’ order by a3;

explain select al from test02 where a1 in (‘1’,‘2’,‘3’) group by a1;
explain select al from test02 where a1 in (‘1’,‘2’,‘3’) group by a2;

– using index
explain select a1,a2 from test02 where a1=‘’ or a2=‘’;
explain select a1,a2 from test02 where a1=‘’ or a3=‘’;

drop index idx_a1_a2_a3 on test02;
alter table test02 add index idx_a1_a2(a1,a2);
explain select a1, a3 from test02 where a1=‘’ or a3=‘’;

3