MySQL語法

1. Login MySQL with password
$mysql -u -root -p
Enter password:

2. How to get a list of user accounts
mysql> SELECT User FROM mysql.user;

3. How to delete user
mysql> DROP USER 'user_name'@'localhost';

4. Create user
mysql>CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'user_name';

5. Change user password
mysql> UPDATE user SET Password=PASSWORD("your_password") WHERE User='user_name';

6List all database
mysql>SHOW DATABASES;

7. Show current use database
mysql>SELECT DATABASE();

8. Change database
mysql>use database_name;

9. Create database
mysql>CREATE DATABASE db_name;

10. Delete database
mysql>DROP DATABASE db_name;

11. allow user to connect to the server from localhost with password
mysql>grant usage on *.* user_name@localhost identified by'user_password';

12. grant all privileges on database to this user
mysql>grant all privileges on db_name.* to user_nae@localhost;

13. Show tables
mysql>SHOW TABLES FROM db_name;

14 Show all tables
mysql>SHOW TABLES;

15. Show data from table
mysql>select * from table_name;

16. update column
update 資料表名稱 set 欄位1='值1',欄位2='值2',欄位3='值3',... 欄位N='值N'
where 條件式 (例如 sn='5' 或 name='塔司尼' );