macos 01 - install mysql

01.在终端中运行 brew install mysql, 执行结束后按照提示操作 

We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
==> Summary
?  /usr/local/Cellar/mysql/8.0.17: 284 files, 272.5MB

 

02.在终端中运行 mysql_secure_installation, 这是控制台形式的初始化向导, 按照提示进行操作: 

Weizhens-Mac-mini:~ weizhen$ mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.

New password: 741852963

Re-enter new password: 741852963

Estimated strength of the password: 50 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 
Weizhens-Mac-mini:~ weizhen$ 

 

03.使用Navicat尝试进行连接时, 会提示错误"caching_sha2_password", 解决办法可以参考这么搞, 这么搞, 这么搞。

 

04.出现"The user specified as a definer ('root'@'%') does not exist"错误时, 解决办法可以参考这么搞。

 

05.常见的数据库命令:

# 查看相关信息, 查看所有指令
help

# 选择数据库
# Use another database. Takes database name as argument.
use mysql

# 退出mysql命令行
# Quit mysql
quit

 

06.常见的SQL语句:

-- 创建表
CREATE TABLE user_info (userId INTEGER PRIMARY KEY AUTOINCREMENT, userName TEXT, userAge INTEGER);
CREATE TABLE IF NOT EXISTS user_info (userId INTEGER PRIMARY KEY AUTOINCREMENT, userName TEXT, userAge INTEGER);
CREATE TABLE user_info (userId INTEGER, userName TEXT, userAge INTEGER, PRIMARY KEY(userId, userName));

-- 销毁表
DROP TABLE user_info;

-- 修改表--增加一列
ALTER TABLE tab_name ADD COLUMN col_name TEXT;

-- 修改表--修改表名, 注意:新的表名会被带上双引号
ALTER TABLE tab_name RENAME TO new_tab_name;

-- 删除项
DELETE FROM user_info WHERE userName='saber';

-- 添加项
INSERT INTO user_info(userName, userAge) VALUES('saber', 18);
INSERT INTO user_info(userName, userAge) VALUES('saber', 18), ('baserker', 22);
INSERT INTO mo_personmap (personid, belongid) SELECT b.id, b.departid FROM mo_person b;

-- 替换或新增
REPLACE INTO user_info(userName, userAge) VALUES('saber', 18);

-- 修改项
UPDATE user_info SET userAge=16 WHERE userName='saber';

-- 查询项
SELECT * FROM user_info;

 

转载于:https://www.cnblogs.com/vision2015/p/11504474.html