Help us understand the problem. What is going on with this article?

MySQLでよく使うコマンド一覧まとめ

More than 5 years have passed since last update.

MySQLのユーザー一覧を確認

select host,user from mysql.user

バージョン確認

mysqlにログインしてselect version()

テーブルを作成

create table if not exists users(
user_id INT AUTO_INCREMENT,
password VARCHAR(30) NOT NULL,
name VARCHAR(140) NOT NULL,
regist_date TIMESTAMP NOT NULL,
PRIMARY KEY(user_id)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

テーブル削除

drop table テーブル名

データ検索

select * from tweets;
select * FROM tweets WHERE UID='nak';
select * FROM tweets WHERE MESSAGE LIKE 'he%';

IN句

select cm.club_name from club_master as cm where club_id 
in(select uj.club_id from user_master as um inner join user_join_club as uj on um.user_id = uj.user_id)

JOIN句

select um.name_kanji,cm.club_name from user_master as um
inner join user_join_club as uj on um.user_id = uj.user_id
inner join club_master as cm on uj.club_id = cm.club_id

データ挿入

insert into tweets
('TID','UID','MESSAGE','DATEOFMSG')
values('1','nak','oraora','2012-10-22');

データ更新

update tweets set
message='おらおら' where uid='nak';

データ削除

delete from tweets where uid='wo2';

テーブルを削除して、作り直す(キー番号もリセット)

truncate table テーブル名

データベースのバックアップをとる

mysqldump -u root -p データベース名 > test.sql

特定のテーブルをダンプする
mysqldump -u root -p データベース名 テーブル名 > test.sql

SQLを既存のデータベースにインポートする

mysql -u root -p データベース名 < test.sql

既存のテーブル名の変更

alter table テーブル名 rename as 新テーブル名

既存の列の構造(列名、型、制約)を変更

alter table テーブル名 change column 既存の列名 新しい列名 型名 制約

テーブルの構造を表示

describe テーブル名

データベースと接続用ユーザーの作成

create database testdb;
grant all on testdb.* to root@localhost identified by 'hogehoge'; 

データベースの削除

drop database 〜

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away