人気ブログランキング | 話題のタグを見る

テーブル>コピー

create table コピー先テーブル名
as
select * from コピー元テーブル名;
(where句等で普通に条件を付けられる)
# by sql8726 | 2006-02-23 21:14 | テーブル操作

trunc()  切り捨て

■切り捨て

・trunc() 関数は、切り捨て結果を返す。
この例では、受注数の小数 3 桁目以降を切り捨て、小数 2 桁の結果が問い
合わされる。

select 品番, trunc(受注数, 2) from sak.受注m
order by 品番
;
# by sql8726 | 2006-02-20 20:13 | 関数

テーブルの作成

◆テーブルの作成

> 実環境例

mysql> use hellodb;
Database changed

mysql> CREATE TABLE HELLO_WORLD_TABLE (
-> NO INTEGER NOT NULL,
-> LANGUAGE VARCHAR(50),
-> MESSAGE VARCHAR(100),
-> PRIMARY KEY(NO)
-> );
Query OK, 0 rows affected (0.23 sec)

mysql>
# by sql8726 | 2006-02-05 02:39 | テーブル操作

テーブル定義の確認

show create table テーブル名;


>実環境例

mysql> show create table mysql.db\G;
*************************** 1. row ***************************
Table: db
Create Table: CREATE TABLE `db` (
`Host` char(60) collate utf8_bin NOT NULL default '',
`Db` char(64) collate utf8_bin NOT NULL default '',
`User` char(16) collate utf8_bin NOT NULL default '',
`Select_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Update_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Delete_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Create_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Drop_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Grant_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`References_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Index_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Alter_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Create_tmp_table_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

`Lock_tables_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Create_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Show_view_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Create_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Alter_routine_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
`Execute_priv` enum('N','Y') character set utf8 NOT NULL default 'N',
PRIMARY KEY (`Host`,`Db`,`User`),
KEY `User` (`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Database privileg
es'
1 row in set (0.00 sec)

mysql>
# by sql8726 | 2006-02-05 02:28 | ■MySQL

テーブル一覧表示

テーブル一覧表示

show tables from データベース名;



> 実環境例

mysql> drop database test_db;
Query OK, 1 row affected (0.19 sec)

mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
17 rows in set (0.00 sec)
# by sql8726 | 2006-02-05 02:18 | ■MySQL