sql basics refresh

play_basic.sql

show databases;

create database play;

use play;

create table users (username VARCHAR(20), create_date date);

drop table users;

create table users (user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20), create_date date);

show tables;

explain users;

alter table users add email VARCHAR(100) after username;

alter table users drop email;

alter table users add email varchar(100) after username;

alter table users change username user_name varchar(30);

insert into users values ( NULL, 'johndoe', 'john@doe.com', '2011-07-18' );

insert into users set user_name = 'przemek', email = 'foobar@example.com', create_date = '2011-07-18';

insert into users (email, user_name, create_date) values('foo@bar.com', 'foobar', '2012-01-01');

select last_insert_id();

insert into users set create_date = now(), user_name = 'ania' , email = 'some@mail.com';

select now();

select * from users;

select user_name, email from users;

select email from users where user_name = 'foobar';

select * from users where user_id <= 2;

select * from users where create_date != '2011-07-18';

select * from users where user_id = 1 or user_name = 'przemek';

select * from users where create_date in('2000-01-01', '2012-01-01');

select * from users where email like '%com%';

select * from users order by create_date;

select * from users limit 2;

select * from users limit 2 offset 1;

update users set email = 'new_email@gmail.com', user_name = 'new_name' where user_name = 'przemek';

delete from users where user_name = 'ania';

truncate table users;

drop database play;