[SQL] 23.04.17

인덱스

alter table usertbl drop primary key;  //테이블의 구조가 바뀌는것.

 

create schema testdb;
drop schema testdb;
create table tbl1(
	a int primary key,
    b int,
    c int);
show index from tbl1;
create table tbl2(
	a int primary key,
    b int unique,
    c int unique,
    d int);
show index from tbl2;
create table tbl3(
	a int unique,
    b int unique,
    c int unique,
    d int);
show index from tbl3;
create table tbl4(
	a int unique not null,
    b int unique,
    c int unique,
    d int);
show index from tbl4;
create table tbl5(
	a int unique not null,
    b int unique,
    c int unique,
    d int primary key);
show index from tbl5;
 
create database if not exists testdb;
use testdb;
create table usertbl(
	userID char(8) not null primary key,
    name   varchar(10) not null,
    birthYear int not null,
    address char(2) not null);
insert into usertbl values ('L5G', '이승기', 1987, '서울');
insert into usertbl values ('KBS', '김범수', 1979, '경남');
select * from usertbl;
alter table usertbl drop primary key;  --테이블의 구조가 바뀌는것.
alter table usertbl
	add constraint pk_name primary key(name);
show index from usertbl;
 
use shopdb;
create view view_membertbl as 
select membername, memberaddress from membertbl;
select * from view_membertbl;
show variables like 'innodb_file_per_table';
create tablespace ts_a add datafile 'ts_a.ibd';
create tablespace ts_b add datafile 'ts_b.ibd';
create tablespace ts_c add datafile 'ts_c.ibd';
 
use testdb;
create table table_a(id int) tablespace ts_a;
create table table_b(id int) tablespace ts_b;
create table table_c(select * from employees.salaries);
select * from table_c;
alter table table_c tablespace ts_c;
drop table table_c;
select * from table_c;

 

뷰

use shopdb; //shopdb 사용할거야

 

 

모델링하는 도구