MySQL的折腾之旅
MAMP的 MySQL, 不完全（之前的有提到）,无法安装 mysqldb. MAMP 中安装 mysqldb 不能 - 解决方法未尝试。
后面看到「再见 MAMP，你好 Ampps」,直接换 Ampps 用了。
Ampps MySQL 的 root 用户默认密码是 mysql
找到 Ampps 中的 mysql 路径 /Applications/AMPPS/mysql/bin 并将其加入环境变量
$ echo 'export PATH=/Applications/AMPPS/mysql/bin:$PATH' >> ~/.bash_profile
新开一个 terminal 窗口，
$ echo $PATH
看看有没加成功。然后就可以直接在 terminal 中使用 mysql 的命令了
which mysql
/Applications/AMPPS/mysql/bin/mysql
最后还是选择之前安装 MySQL，绕了一圈又回到了原点。
以下是命令行操作 MySQL 的基础
连接 MySQL
$ mysql -u[MySQL用户名] -p[MySQL密码] -h[ip 或 域名] -P[端口] -D[数据库名]
$ mysql -uroot -pmysql
Warning: Using a password on the command line interface can be insecure.
Welcome to the mysql monitor. Commands end with ; or \g.
Your mysql connection id is 4697
Server version: 5.6.15 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
下面的命令用 ‘\h’ 或者 RTFM(Read the Fucking Menu) 都能找到，权当做个记录。
查看当前所有用户
mysql> select host,user from mysql.user;
+-----------------+------+
| host | user |
+-----------------+------+
| 127.0.0.1 | root |
| ::1 | root |
| JigarsMac.local | root |
| localhost | root |
+-----------------+------+
4 rows in set (0.00 sec)
创建新的用户
mysql> create user [新的用户名]@[登陆主机名] identified by '[新用户密码]';
创建名为 appadmin 密码为 admin 的新用户
mysql> create user appadmin identified by 'admin';
Query OK, 0 rows affected (0.00 sec)
很奇怪 0 rows affected, 再看下当前所有用户
mysql> select host,user from mysql.user;
+-----------------+----------+
| host | user |
+-----------------+----------+
| % | appadmin |
| 127.0.0.1 | root |
| ::1 | root |
| JigarsMac.local | root |
| localhost | root |
+-----------------+----------+
5 rows in set (0.00 sec)
若要限制在固定地址登陆，比如 localhost 登陆
mysql> create user appadmin@localhost identified by 'admin';
已经增加了 appadmin。
提升用户权限
mysql> grant [需要提升的权限] on [数据库名].* to [需要被提升权限的用户名]@[登陆主机名] identified by '[需要被提升权限的用户密码]';
「需要提升的权限」:
- all privileges 所有权限
- select
- insert
- update
- delete select,insert,update,delete可自由组合
mysql> grant all privileges on test.* to appadmin identified by 'admin';
或者
mysql> grant all privileges on test.* to appadmin@localhost identified by 'admin';
查看数据库们
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
操作数据库
创建
mysql> create database [数据库名];
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)
删除
mysql> drop database [数据库名];
mysql> drop database test;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
指定为当前数据库
mysql> use [数据库名]
mysql> use test
Database changed
备份数据库（dump）
!!注意!! 这不是 mysql 中执行的命令，而是 Shell 中执行的，看 “$” 符号
$ mysqldump -u[MySQL用户名] -p [数据库名] > [导出sql文件路径 path/to/xx.sql]
Enter password:[输入数据库的密码]
数据库导入
$ mysql -u[MySQL用户名] -p [数据库名] < [导入sql文件路径 path/to/xx.sql]
Enter password: [输入数据库的密码]
查看表结构
mysql> desc plugin;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name | varchar(64) | NO | PRI | | |
| dl | varchar(128) | NO | | | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show create table plugin;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| plugin | CREATE TABLE `plugin` (
`name` varchar(64) NOT NULL DEFAULT '',
`dl` varchar(128) NOT NULL DEFAULT '',
PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL plugins' |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
其他常用命令
查看状态
mysql> show status;
输出好长
+-----------------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 13090 |
...
| Threads_running | 1 |
| Uptime | 5783 |
| Uptime_since_flush_status | 5783 |
+-----------------------------------------------+-------------+
341 rows in set (0.00 sec)
查看进程
mysql> show processlist;
+-------+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-----------+------+---------+------+-------+------------------+
| 13086 | root | localhost | NULL | Query | 0 | init | show processlist |
+-------+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
Shell 命令
- mysqlshow 显示用户选择的数据库和表
$ mysqlshow -uroot -p test
Enter password:
Database: test
+--------+
| Tables |
+--------+
+--------+
- mysqladmin 创建和维护 MySQL 数据库的命令
MySQL SQL 语句
Top
SELECT * FROM user
WHERE department = "IT"
LIMIT 1;
事务
BEGIN;
SELECT * FROM user
WHERE department = "IT"
FOR UPDATE
LIMIT 1;
UPDATE user SET user_name = "Jobs" WHERE user_id = ? ;
COMMIT;
MySQL 中的锁
-以上-