My current version:
ji@ji-cada:~$ mysql --version;
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2
The path MySQL is installed：
/usr/bin/
display how many columns and rows：
ji@ji-cada:/usr/bin$ mysqlshow -u root -p -vv mysql
More info to display:
ji@ji-cada:/usr/bin$ mysqlshow -u root -p --status factory
add a database:
ji@ji-cada:/usr/bin$ mysqladmin -u root -p create mobileweb
or
login, then
mysql> CREATE DATABASE webdb
see how many database:
ji@ji-cada:/usr/bin$ mysqlshow -u root -p
change database:
mysql> use factory;
mysql> connect sakai;
show how table created
mysql> SHOW CREATE TABLE factory.Customer;
or
mysql> DESCRIBE factory.Customer;
See how many tables and databases:
SHOW TABLES;
SHOW DATABASES;
Alter a table property:
mysql> DESCRIBE Customer;
mysql> ALTER TABLE Customer MODIFY COLUMN Name VARCHAR(50) NOT NULL default '';
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> DESCRIBE Customer;
mysql> ALTER TABLE Customer CHANGE COLUMN Zip Postcode CHAR(10);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> DESCRIBE Customer;
mysql> ALTER TABLE Customer ADD INDEX Postcode(Postcode);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> DESCRIBE Customer;
Another way of insertion:
mysql> insert into Customer set Customer_Number = 3167, Name='Good Guys', Address='21 Garnet St', City='Lane Cove', State='NSW', Postcode = '3253';
Very fast way to copy data accross:
mysql> CREATE TABLE City ( City_Name CHAR(20) PRIMARY KEY);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO City SELECT City FROM Customer;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
Other commands:
TRUNCATE table1;
ALTER TABLE table1 AUTO_INCREMENT=1;
Manage user:
mysql> SHOW PRIVILEGES;
For other user:
mysql> SHOW GRANTS FOR webuser;
To see what user info, in mysql database:
SELECT user from user where host = 'localhost';
To create a user:
mysql> CREATE USER webuser IDENTIFIED BY 'webuser';
Query OK, 0 rows affected (0.00 sec)
Change password:
mysql> SET PASSWORD FOR 'webuser' = PASSWORD('newpass');
Query OK, 0 rows affected (0.00 sec)
Other commands:
GRANT: give permission to user, on particular table, action(SELECT, UPDATE, INSERT etc.), logging in from which domain.
REVOKE: remove permission.
A proper formatted version with table showing is here:
No comments:
Post a Comment