MYSQL 커맨드 관련된 모든것들을 정리해 봤습니다. 가끔 자주쓰지 않는 커맨드들은 까먹기가 쉬워서 적어보았습니다.

1) mysql  로그인.
—->[mysql dir]/bin/mysql -h hostname -u root -p

***************************************************************
2) 데이터 베이스 생성하기
—-> create database [databasename];
—->eg :- create database college;

****************************************************************
3) 모든 데이터 베이스 정보 보기.
—-> show databases;

*****************************************************************
4) 해당 데이터 베이스로 이동하기
—–> use [databasename];

*****************************************************************
5) 테이블 정보 보기
——->show tables

******************************************************************
6) 데이터 베아스에 있는 테이블들의 필드 정보 보기
—–> desc [table name];

*******************************************************************
7) 데이터 베이스 삭제
—> drop database [database name];
eg:- drop database college;

******************************************************************
8) 테이블 생성.
—–>CREATE table tablename(field_one datatype(size),fiels_two datatype(size));

eg1:-mysql> CREATE TABLE student( id INT(20),name VARCHAR(100));
Query OK, 0 rows affected (0.03 sec)

eg:-2 mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups
VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));
*******************************************************************
9) 테이블에서 auto_increment 생성

—> CREATE TABLE tablename(field_one datatype(size) NOT NULL AUTO_INCREMENT PRIMARY KEY , field_two datatype(size));

eg:-mysql> CREATE TABLE student (id INT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) );
Query OK, 0 rows affected (0.01 sec)

*******************************************************************
10) 테이블 생성 시 timestamp 적용
—> CREATE TABLE student ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(100), cur_timestamp TIMESTAMP(7) );

*******************************************************************
11테이블 생성 시 TIMESTAMP DEFAULT NOW() 적용

—> CREATE TABLE student (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100), created TIMESTAMP DEFAULT NOW());

*******************************************************************
12) 테이블 명세 보기
—-> DESC tablename;

*********************************************************************
13) 테이블 삭제
—–> drop table [table name];
eg) drop table student;

*********************************************************************
14) 테이블에 INSERT
—–>INSERT INTO [tablename] (filed_one,field_two,feild_three) values(value1,value2,value3);

eg:-INSERT INTO student (id,name,branch) values(1,’Amit’,'computer science’);

*******************************************************************
15) 패스워드 변경(unix shell에서)
—->mysql dir]/bin/mysqladmin -u root -h hostname.blah.org -p password ‘new-password’

******************************************************************
16) 패스워드 변경(MySQL 내에서)
—-> SET PASSWORD FOR ‘user’@'hostname’ = PASSWORD(’passwordhere’);

******************************************************************
17) GRANT privileges
—–>grant all privileges on [databasename].* to user@’localhost’ identified by ‘passwd’;

******************************************************************
18) GRANT specific privileges
—-> GRANT SELECT ON databasename.* TO username@’localhost’ IDENTIFIED BY ‘password’;

To enable more options you would separate them with a comma. So to enable SELECT, INSERT, and DELETE your syntax would look like this;

—–>GRANT SELECT, INSERT, DELETE ON database.* TO username@’localhost’ IDENTIFIED BY ‘password’;

Once you have given the desired privileges for your user, you will need to run this command within the MySQL command prompt;

—–>FLUSH PRIVILEGES;

To see a list of the privileges that have been granted to a specific user;

—–> select * from MySQL.user where User=’user’ \G

This is a list of privileges that you can grant;

********************************************************************
19) 테이블 갱신.
—-> Update table tablename SET fieldname=newvalue where fieldname=oldvalue;
eg1:- Update table student set id=2 where id=2;
eg2:- Update table student set name=’Arun’ where name=’Amit’;

********************************************************************
20) 테이블 필드 정보 수정
—-> update table table name MODIFY fieldname(new size);
eg:- Update table tablename MODIFY id(30);

********************************************************************
21) 테이블에 한 레코드 삭제.
—> DELETE from [table name] where [field name] = ‘value’;

********************************************************************
22) 테이블의 필드(칼럼) 삭제
—->alter table [table name] drop column [column name];

*******************************************************************
23) 테이블에 새로운 칼럼 추가
—–> alter table [table name] add column [new column name] varchar (20);

***************************************************************
24) 테이블의 칼럼 이름 변경
—-> alter table [table name] change [old column name] [new column name] varchar (50);

*****************************************************************
25) 테이블의 unique 칼럼 추가
—-> alter table [table name] add unique ([column name]);

******************************************************************
26) 테이블의 칼럼 정보 변경
—-> alter table [table name] modify [column name] VARCHAR(3);

*****************************************************************
27) 테이블 조회

To see alla values of a table use

SELECT * from tablename;

To select specific values from a table use

SELECT fieldname_one,fieldname_two from tablename;

To select fields based on condition

SELECT fieldname_one,fieldname_two from tablename where [condition];

eg:-SELECT id from student where name=’Arun’;

28 ) 테이블 Dump

[mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

29)  Dump 테이블 Restore
[mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql