mysql学習メモ

## sql基本
###ユーザー作成＆削除、権限付与
```python 
create user "新規ユーザー名" identified by '新規パスワード'

grant "与える権限" on "データベース名"."テーブル名" to "ユーザー名"
```
### 基本操作
```python

create database "データベース名"

show databases

use "データベース名"

select database() #現在使用中のデータベース

create table cs(number int,name varchar(10),year int) 

show tables 

desc "テーブル名"

insert into "テーブル名" ("カラム名1","カラム名2"...) values("データ1","データ2"...),("データ1","データ2"...)
```

+ データベース選択された場合も、他のデータベースの中身見れる```select * from データベース.テーブル名```
+ ユーザー情報```select user from mysql.user```
+ プロンプトの文字列の変更```prompt \d-\h-\u>```.```prompt mysql>```

### カラムの操作
```python

alter table "テーブル名" modify "カラム名" "データ型" #カラムのデータ型の変更

alter table "テーブル名" add "カラム名" "データ型" #カラムの追加

alter tabel "テーブル名" add "カラム名" "データ型" first#カラム最前位置追加

alter tabel "テーブル名" add "カラム名" "データ型" after "カラム1" #カラム自由位置追加

alter table "テーブル名" modify "カラム名3" "データ型" first #カラム位置の変更

alter table "テーブル名" change "変更前カラム名" "変更後カラム名" "変更後データ型" #カラム名とデータ型の変更

alter table "テーブル名" drop "カラム名"
```

###主キー＆一意キー
```python
create table "テーブル名"("カラム名1","データ型1" primary key,....) #不能为空

create table "テーブル名"("カラム名1","データ型1" unique,....) #可以为空
```
### 自動連続番号＆カラム構造変更
```python
create table "テーブル名" ("カラム名1",int auto_increment primary key,....) #之后输入的时候就不用输入主键了

alter table "テーブル名" auto_increment=0 #全削除後の自動増加初期値のリセット

create table "テーブル名"("カラム名1","データ型1" default "初期値",....) #最初からデータが入っているカラムにする

alter table "テーブル名" modify "カラム名" "データ型" default "初期値"
```

### index設定
```python
create index "インデックス名" on "テーブル名"("カラム名") #インデックス作成

show index from "テーブル名"\G

drop index "インデックス名" on "テーブル名" #インデックスの削除  
```
### テーブルのコピーや削除
```python
create table "新規テーブル名" select * from "元となるテーブル名" #カラム構造とデータを丸ごとココピー・自動増加などはコピーされない。

create table "新規テーブル名" like "元となる新規テーブル名" #カラム構造だけコピーされる。自動増加などもコピーできる。

insert into "テーブル名" select * from "元となるテーブル名" #同じ構造のテーブルのデータを丸ごとコピーする。

insert into "テーブル名"("カラム名1") select "カラム名2" from  "元なるテーブル名" #特定のカラムを選択してコピーする

drop table (if exists)"テーブル名"

drop database "データベース名"

delete from "テーブル名" (where "条件")#レコードをまるごと削除
```

### 情報抽出
```python
select "カラム名" as "エイリアス" from "テーブル名"

select "テーブル名" as "エイリアス"

concat(a,b,c) from "テーブル名" #文字結合

#right left substring repeat reverse now

select (distinct) "カラム名" from "テーブル名" where "条件" limit "表示するレコード数"
 
  (not) like "あ" #曖昧検索 %任意文字列 _任意一文字

  where XX is NULL
 
  case when then else end #!! 

  order by "カラム名" desc #降順

  offset "表示開始レコードのシフト数"

  group by "カラム名" (having "条件") #このカラム名の下の重複した項目をグループ化

  where "カラム名" (not) in('値1','値2')
```

###データの編集
```python
update "テーブル名" set "カラム名" = "設定する値" where "条件"

insert into "存在するテーブル名" select * "テーブル名" where "条件"
```

###複数のテーブルを利用
```python
select "カラム名1" from "テーブル名1" union select "カラム名2" from "テーブル名2"
  
  union all #重複を省くことをさける

# 内部結合

select "カラム名" from "テーブル1" join "結合するテーブル2" on "テーブル1のカラム名"="テーブル2のカラム名" 
  
  using("共通のカラム名") #"テーブル1のカラム名"="テーブル2のカラム名"

#（左右）外部結合

select "カラム名" from "テーブル1" left join "結合するテーブル2" on "テーブル1のカラム名"="テーブル2のカラム名"

# 自己結合(順位づけ)

select "カラム名" from "テーブル名" as "エイリアス1" join "テーブル名" as "エイリアス2"

# サブクエリ

select "表示するカラム" from "テーブル名" where "カラム名" in ("selectんいよるサブクエリでカラムを抽出")

# exist と not existの利用
```

### ビュー
```python
select (or replace) view "ビューの名前" as select "カラム名" from "テーブル名" where "条件"

  with check option #就是使往view里插入不符合where条件的数值的时候报错

alter view "ビュー名" as select "カラム名" from "テーブル名" #ビューのカラム構造変更

drop view if exists "ビュー名"
```

### ストアドプロシージャ
```python
delimiter //
create procedure "ストアドプロシージャ名"("引数名","データ型")
begin
xxxx;
yyyy;
end
//
delimiter ;

call "ストアドプロシージャ名"

show create procedure "ストアドプロシージャ名" #内容表示

drop procedure "ストアドプロシージャ名"


delimiter //
create function "関数名"("引数名","データ型") return "返す値のデータ型"
begin
declare "変数名" "データ型";
"SQL文";
return "返し値 式";
end
//
delimiter ;

show create function "関数名";

drop function "関数名";
```
### トラザクション
```python
show create table "テーブル名" #テーブル詳細情報確認

start transaction

rollback or commit

set autocommit=0/1

select @@autocommit
```

### ファイルを使ったやりとり
```python
load data infile "ファイル名" into table "テーブル名" "オプション記述"

  "オプション記述": 
  field terminated by "区切り文字(デフォルトは'\t'タブ)"
  lines terminated by "改行文字(デフォルトは'\n'改行)"
  ignore "最初にスキップする行" lines "(デフォルトは0)"

select * into outfile "ファイル名" "オプション記述" from "テーブル名"

type "ファイル名" #ファイルの中身確認

source "テキストファイル名" #从文件里读sql语言
```

### バックアップとリストア
```python
mysqldump -u "ユーザー名" -p"password" "データベース名">"出力ファイル名"

mysqladmin -u root -p1234 create db2

mysql -u root -p1234 db2<"入力ファイル名"
```