ちょっとメモ

ほんとにちょっとしたメモなの全く意味のない内容です。

すんません。

1.まずはログイン
c:\xampp\mysql\bin\mysql -u root

2.データベース一覧を表示
show databases;

3.使用するデータベースを指定
use sampledb;

4.show tables;
テーブル一覧を見る

—————————————-
1.まずはログイン
c:\xampp\mysql\bin\mysql -u root

2.使用するデータベースを指定
use db1

3.
—————————————-

c:\xampp\mysql\bin\mysql -u root

use sampledb;

use sampledb;

set names sjis;
set names ujis;

select population/1000 from tblpref;

select population/1000 as density from tblpref;

select concat(zone,prefecture);

select length(prefecture) from tblpref;

select char_length(prefecture) from tblpref;

create table tb(bang varchar(10),
name varchar(10),
tosi int);

insert into tb1 values(‘A101′,’佐藤’,40);
insert into tb1 values(‘A102′,’高橋’,28);
insert into tb1 values(‘A103′,’中川’,20);
insert into tb1 values(‘A104′,’渡辺’,23);
insert into tb1 values(‘A105′,’西沢’,33);

create database db4 default character set sjis collate sjis_japanese_ci;

create table tb(bang varchar(10),
uria int,
tuki int);

insert into tb values(‘A101′,’101’,4);
insert into tb values(‘A102′,’54’,5);
insert into tb values(‘A104′,’181’,4);
insert into tb values(‘A103′,’17’,5);
insert into tb values(‘A103′,’17’,5);
insert into tb values(‘A101′,’300’,5);
insert into tb values(‘A102′,’205’,6);
insert into tb values(‘A104′,’93’,5);
insert into tb values(‘A103′,’12’,6);
insert into tb values(‘A107′,’87’,6);

show fields from tb1;
select * from tb;

create table tb1(bang varchar(10),
name varchar(10),
tosi int);

insert into tb1 values(‘A101′,’佐藤’,40);
insert into tb1 values(‘A102′,’高橋’,28);
insert into tb1 values(‘A103′,’中川’,20);
insert into tb1 values(‘A104′,’渡辺’,23);
insert into tb1 values(‘A105′,’西沢’,33);

create table tb2(bang varchar(10),
name varchar(10),
tosi int);

insert into tb2 values(‘A106′,’中村’,26);
insert into tb2 values(‘A107′,’田中’,24);
insert into tb2 values(‘A108′,’鈴木’,23);
insert into tb2 values(‘A109′,’村井’,25);
insert into tb2 values(‘A110′,’吉田’,27);

create table tb3(bang varchar(10),
ken varchar(10));

insert into tb3 values(‘A101′,’東京都’);
insert into tb3 values(‘A102′,’埼玉県’);
insert into tb3 values(‘A103′,’神奈川県’);
insert into tb3 values(‘A104′,’水海道’);
insert into tb3 values(‘A105′,’静岡県’);

delete from tb where uria = 0;

select length(prefecture) from tblpref;

select char_length(prefecture) from tblpref;

select left(prefecture, 2) from tblpref;

select right(prefecture, 2) from tblpref;

select substring(prefecture, 2 , 1) from tblpref;

select ltrim(prefecture) from tblpref;

select rtrim(prefecture) from tblpref;

select trim(prefecture) froum tblpref;

update tbladdress set tel = ’03-999999′ where prefid = 13;

update tbladdress set address = null, tel = null;

update tblpref set area = area/1000;

delete from tbladdress

truncate table tbladdress

select * from tb1 union select * from tb2;

select bang from tb where uria >= 200 union select bang from tb1 where tosi >= 35;

create table tba select * from tba;

select * from テーブル名1 join テーブル名2 using(キーフィールド名);

select * from tba join tb1 using(bang);

select tbb.bang, tb1.nama, tbb.uria,

select tbb.bang, tb1.nama, tbb.uria from tbb join tb1 using(bang);

create table tbc select * from tb;

select tbc.bang, tbc.uria, tb1.nama, tb3.ken from tbc join tb1 using(bang) join tb3 using(bang);

select tbd.bang, tb1.nama from tbd left join tb1 using(bang);

select tbe.bang, tb1.nama from tbe right join tb1 using(bang);

create view v1 as select nama,tosi from tb1;

create table tbk(bang int primary key auto_increment,nama varchar(100),mess varchar(100));

insert into tbk values(‘1′,’佐藤’,’今日は日直です’);
insert into tbk values(‘3′,’雪村’,’今日はスピーチです’);
insert into tbk values(‘4′,’真田’,’今日はコメントです’);
insert into tbk values(‘5′,’吉田’,’今日は日直です’);
insert into tbk values(‘6′,’中村’,’今日はスピーチです’);
insert into tbk values(‘7′,’根津’,’今日はコメントです’);
insert into tbk values(‘8′,’中元’,’今日は日直です’);
insert into tbk values(‘9′,’飯塚’,’今日はスピーチです’);
insert into tbk values(’10’,’池田’,’今日はコメントです’);
insert into tbk values(’11’,’芳本’,’今日は日直です’);

<P.155> データベースを作る際に、文字コードを指定する
create database db1 default character set sjis collate sjis_japanese_ci;

create table tbk(bang int primary key auto_increment,nama varchar(100),mess varchar(100));

create table tbj0(guru int primary key auto_increment,sure varchar(30),niti datetime,aipi varchar(20));

create table tbj1(bang int primary key auto_increment,nama varchar(30),mess text,niti datetime,guru int,aipi varchar(20));