в 13:44, 26 мая 2010

2010-05-26T13:44:56Z

Admin: 1 версия

2010-05-26T09:49:07Z

1 версия

Новая страница

== Display all table names==



<source lang="sql">
mysql> use test
Database changed
mysql>
mysql> SHOW TABLES;
Empty set (0.02 sec)</source>

== Show a detailed listing of the columns of your database.==



<source lang="sql">
mysql>
mysql>
mysql> use mysql;
Database changed
mysql> SHOW COLUMNS FROM user;
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum("N","Y") | NO | | N | |
| Insert_priv | enum("N","Y") | NO | | N | |
| Update_priv | enum("N","Y") | NO | | N | |
| Delete_priv | enum("N","Y") | NO | | N | |
| Create_priv | enum("N","Y") | NO | | N | |
| Drop_priv | enum("N","Y") | NO | | N | |
| Reload_priv | enum("N","Y") | NO | | N | |
| Shutdown_priv | enum("N","Y") | NO | | N | |
| Process_priv | enum("N","Y") | NO | | N | |
| File_priv | enum("N","Y") | NO | | N | |
| Grant_priv | enum("N","Y") | NO | | N | |
| References_priv | enum("N","Y") | NO | | N | |
| Index_priv | enum("N","Y") | NO | | N | |
| Alter_priv | enum("N","Y") | NO | | N | |
| Show_db_priv | enum("N","Y") | NO | | N | |
| Super_priv | enum("N","Y") | NO | | N | |
| Create_tmp_table_priv | enum("N","Y") | NO | | N | |
| Lock_tables_priv | enum("N","Y") | NO | | N | |
| Execute_priv | enum("N","Y") | NO | | N | |
| Repl_slave_priv | enum("N","Y") | NO | | N | |
| Repl_client_priv | enum("N","Y") | NO | | N | |
| Create_view_priv | enum("N","Y") | NO | | N | |
| Show_view_priv | enum("N","Y") | NO | | N | |
| Create_routine_priv | enum("N","Y") | NO | | N | |
| Alter_routine_priv | enum("N","Y") | NO | | N | |
| Create_user_priv | enum("N","Y") | NO | | N | |
| ssl_type | enum("","ANY","X509","SPECIFIED") | NO | | | |
| ssl_cipher | blob | NO | | | |
| x509_issuer | blob | NO | | | |
| x509_subject | blob | NO | | | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
+-----------------------+-----------------------------------+------+-----+---------+-------+
37 rows in set (0.02 sec)
mysql> use test;
Database changed</source>

== SHOW COLUMNS and DESCRIBE can display information about the columns in individual INFORMATION_SCHEMA tables.==

Several SHOW statements have been extended to allow a WHERE clause:
<OL><LI>SHOW CHARACTER SET</LI><LI>SHOW COLLATION</LI><LI>SHOW COLUMNS</LI><LI>SHOW DATABASES</LI><LI>SHOW FUNCTION STATUS</LI><LI>SHOW KEYS</LI><LI>SHOW OPEN TABLES</LI><LI>SHOW PROCEDURE STATUS</LI><LI>SHOW STATUS</LI><LI>SHOW TABLE STATUS</LI><LI>SHOW TABLES</LI><LI>SHOW VARIABLES</LI></OL>
The WHERE clause is evaluated against the column names displayed by the SHOW statement.
SHOW CHARACTER SET;
SHOW CHARACTER SET WHERE `Default collation` LIKE "%japanese%";
This statement displays the multi-byte character sets:
SHOW CHARACTER SET WHERE Maxlen > 1;
26. 10. SHOW
26. 10. 1.
SHOW COLUMNS and DESCRIBE can display information about the columns in individual INFORMATION_SCHEMA tables.
26. 10. 2.
<A href="/Tutorial/MySQL/0520__MySQL-Utilities/UsetheSHOWstatementtofindoutwhatdatabasescurrentlyexistontheserver.htm">Use the SHOW statement to find out what databases currently exist on the server:</a>
26. 10. 3.
<A href="/Tutorial/MySQL/0520__MySQL-Utilities/Displayalltablenames.htm">Display all table names</a>
26. 10. 4.
<A href="/Tutorial/MySQL/0520__MySQL-Utilities/SHOWCREATETABLEstatement.htm">SHOW CREATE TABLE statement.</a>
26. 10. 5.
<A href="/Tutorial/MySQL/0520__MySQL-Utilities/SHOWINDEXFROMtblname.htm">SHOW INDEX FROM tbl_name</a>
26. 10. 6.
<A href="/Tutorial/MySQL/0520__MySQL-Utilities/Toseethestructureorschemaofadatabase.htm">To see the structure or schema of a database</a>
26. 10. 7.
<A href="/Tutorial/MySQL/0520__MySQL-Utilities/Showadetailedlistingofthecolumnsofyourdatabase.htm">Show a detailed listing of the columns of your database.</a>

== SHOW CREATE TABLE statement.==



<source lang="sql">
mysql>
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>
mysql> show create table Employee;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Employee | CREATE TABLE `employee` (
`id` int(11) default NULL,
`first_name` varchar(15) default NULL,
`last_name` varchar(15) default NULL,
`start_date` date default NULL,
`end_date` date default NULL,
`salary` float(8,2) default NULL,
`city` varchar(10) default NULL,
`description` varchar(15) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)</source>

== SHOW INDEX FROM tbl_name==

CREATE INDEX indexname ON tablename(columnnamelist);



<source lang="sql">
mysql>
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> CREATE INDEX id_index ON employee(id);
Query OK, 8 rows affected (0.02 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql>
mysql> show index from employee;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| employee | 1 | id_index | 1 | id | A | NULL | NULL | NULL | YES | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql></source>

== To see the structure or schema of a database==



<source lang="sql">
mysql>
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)
mysql>
mysql> use test;
Database changed
mysql>
mysql></source>

== Use the SHOW statement to find out what databases currently exist on the server:==



<source lang="sql">
mysql>
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql>
mysql></source>

The mysql database describes user access privileges.
The test database is for users to try things out.

← Предыдущая	Версия 13:44, 26 мая 2010
(нет различий)

MySQL Tutorial/MySQL Utilities/SHOW - История изменений

в 13:44, 26 мая 2010

Admin: 1 версия