[tomopugh@localhost /]#

SQL 　　« mysql - data input | HOME | Wii 当たっちゃった！！！ »

Database

Create database
> create database sample;

Show database
> show databases;

Set database to use
> use sample

Show current working database
> select database();

You can indicate database also when you login to mysql like below.
# mysql sample -u root -p1234

Table

Create table
> create table table1 (column1 varchar(10), column2 varchar(5), column3 int);

You can create tables with indicating charset like below.
> crate table table1 (name varchar(10)) charset=sjis;

Types of data
varchar : all characters until 255 characters. You need to indicate a length of the data quoted ().
text : all characters until 65535 characters.
longtext : same as text, but until 4294967295 characters.
int : only digit. (integral number)
double : only deigit. (allow dicimal number)
datetime : date and time like YYYY-MM-DD HH:MM:SS
date : only date like YYYY-MM-DD
time : only time like HH:MM:SS
timestamp : date and time are automatically input when inserting or updating data.

If you would like to learn the type of table more, visit here.
http://www.rfs.jp/sb/sql/02/03.html

Also you can setup options here.
> create table table_name (column1 varchar(10) option,.....);

primary key : no duplicate, no null data
unique : no duplicate, but ok for null data
auto_increment : use with int and primary key. the number is counted up automatically. (when inserting data, just input 0 or null for this column, then automatically counted up. If input 100, then it is counted up from 100.)
> create table table_name (column1 int auto_increment primary key) auto_increment=100;

Initialize auto increment, because even if you deleted the all records, new data will be counted up from the last number. (of you can setup initial value with this.)
> alter table table_name auto_increment=0;

default 'default_value' : this value will be the default if data is not input.

Show table
> show tables;

Show details of the table (Show columns of table)
> desc table1;
or
> describe table1;

Copy table
> create table table2 select * from table1;

Create table with the column from the other table
> create table table_name like existing_table;

Modify table

Change type of column
> alter table table_name modify column_name data_type;

Add new column in the existing table (added as a last column)
> alter table table_name add new_column_name data_type;

If you want to add new column as a first column,
> alter table table_name add new_column_name data_type first;

or you can add new column wherever you want,
> alter table table_name add new_column_name data_type after column_name;

Change the order of the existing column
> alter table table_name modify column_name data_type first;
this "first" can be "last" or "after column_name"

Change column name, data type and the order
> alter table table_name change current_column_name new_column_name data_type location;

Delete existing column
> alter table table_name drop column_name;

Index

Create Index on table
To search data faster, especially the column for primary key and unique.
> create index index_name on table_name (column_name);

Show index setup
> show index from table_name;
If it is hard to read, then try below command to view easily.
> show index from table_name \G

Delete Index
> drop index index_name on table_name;

Delete

Delete database
> drop database database_name;

Delete table
> drop table table_name;

If the specified table exists, it will be deleted. If not, nothing happens.
> drop table if exists table_name;

Delete only data from table
> delete from table_name;

Comment

Please comment !