Table of Contents

SQL


Table of Contents


Web Sites


Command

DDL(Data Definition Language)

create

create table if not exists test.idea_ideabord(
   id int not null primary key auto_increment,
   title varchar(32) not null,
   message text not null,
   name varchar(32) not null,
   created datetime not null,
   modified datetime not null
   );

Tips

create new table from old table.

create table table2 as select * from table1;

Is column blank?

select * from TABLE where str = '';

Is column NULL? You use not “= NULL” but “is NULL”. 

select * from strtest where str is NULL;


DML(Data Manipulation Language)

select

select * from TABLE;
select user_id, name form where user_id = '001';
select * form TABLE where aaa like 'string';


select * from TABLE where col1 LIKE '%AAA%' or col2 LIKE '%AAA%' or col3 LIKE '%AAA%';

select * from tb where 
  ( column1 like '%word1%' or column2 like '%word1%' or column3 like '%word1%' )
and ( column1 like '%word2%' or column2 like '%word2%' or column3 like '%word2%' )
and ( column1 like '%word3%' or column2 like '%word3%' or column3 like '%word3%' );

insert

insert into TABLENAME (vendor, group) values('HP', 'LA');
insert into TABLENAME values('HP', 'LA');

update

update TABLE set level = 1;
update TABLE set server = 'ibm' where id = 1;

delete

delete from customers where id = 6;
delete from device where id < 500;
delete from customers where companyname = 'IBM';