mysql小记安装homebrew 安装mysql 忘记密码重置操作数据库操作数据表建表删表增删改查 L

安装homebrew

1. /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
2. 如果homebrew显示443,尝试切换手机热点安装

切换国内阿里镜像

替换 brew.git 仓库地址
cd "$(brew --repo)" 
git remote set-url origin https://mirrors.aliyun.com/homebrew/brew.git 

替换 homebrew-core.git 仓库地址
cd "$(brew --repo)/Library/Taps/homebrew/homebrew-core" 
git remote set-url origin https://mirrors.aliyun.com/homebrew/homebrew-core.git 

替换 homebrew-bottles 访问地址
echo 'export HOMEBREW_BOTTLE_DOMAIN=https://mirrors.aliyun.com/homebrew/homebrew-bottles' >> ~/.bash_profile 
source ~/.bash_profile

安装mysql

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

启动： 
mysql.server start

加入开机自启 
ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents

运行密码配置
mysql_secure_installation 

Securing the MySQL server deployment. 

Connecting to MySQL using a blank password. 

VALIDATE PASSWORD PLUGIN 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 plugin?    //你确定要安装验证密码插件吗? 

Press y|Y for Yes, any other key for No: y      //确定安装 

There are three levels of password validation policy:   //三个等级的验证策略 

LOW    Length >= 8   //最小长度大于等于8个字符 
MEDIUM Length >= 8, numeric, mixed case, and special characters     //数字，字母，特殊字符 混合，具体的应该是至少1个数字，1个字母，1个特殊字符，长度不超过32个字符 
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file     //  最严格，加上了，字典文件 

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0       //这里我选择0最简单的， 
Please set the password for root here. 

New password:       //输入密码 

Re-enter new password:      //重复输入密码 

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,     //默认情况下，MySQL有一个匿名用户， 
allowing anyone to log into MySQL without having to have     //这个匿名用户，不必有一个用户为他们创建，匿名用户允许任何人登录到MySQL， 
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    //一般情况下，root用户只允许使用"localhost"方式登录， 
'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) : no    //不允许root远程登陆？ 

... skipping. 
By default, MySQL comes with a database named 'test' that     //默认情况下，MySQL数据库中 
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  //确认删除test数据库？ 
- 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!

忘记密码重置

停止服务和进程
brew services stop mysql
mysql.server.stop

进入mysql文件夹
cd /usr/local/opt/mysql/bin

执行越权
mysqld_safe --user=mysql --skip-grant-tables --skip-networking &

进入mysql
mysql -u root mysql

重置密码
mysql> use mysql
Database changed

mysql> flush orivileges;
Query Ok, 0 rows affected (0.01 sec)

mysql> ALTER USER 'rrot'@'localhost' IDENTIFIED BY '新密码';
Query Ok, 0 rows affected (0.01 sec)

mysql> exit;

操作数据库

启动数据库
mysql.server start

重启数据库
mysql.server restart

mysql链接服务器
mysql -u root -p

php链接服务器
$dbhost = '127.0.0.1';  // mysql服务器主机地址
$dbuser = 'root';            // mysql用户名
$dbpass = 'xxxxxx';          // mysql用户名密码
mysqli_connect($dbhost, $dbuser, $dbpass);

mysql语法创建数据库
mysql> CREATE DATABASE test;

php语法创建数据库
mysqli_query($connect, 'CREATE DATABASE test');

mysql语法删除数据库
mysql> DROP DATABASE test;

php语法删除数据库
$server = mysqli_query($connect, 'DROP DATABASE test');

mysql语法指定数据库
mysql> use test;

php语法指定数据库
mysqli_select_db($connect, 'test');

操作数据表

建表

mysql语法创建表
CREATE TABLE IF NOT EXISTS `users`(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `name` VARCHAR(100) NOT NULL,
   `age` INT,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

php语法创建表
$sql = "CREATE TABLE users( " .
    "id INT NOT NULL AUTO_INCREMENT, " .
    "name VARCHAR(100) NOT NULL, " .
    "age INT, " .
    "PRIMARY KEY ( id ))ENGINE=InnoDB DEFAULT CHARSET=utf8; ";
mysqli_query($connect, $sql);

删表

mysql语法删除表
DRAP TABLE users;

php语法删除表
mysqli_query($connect, 'DRAP TABLE users');

增

mysql语法插入数据
mysql> INSERT INTO users (name, age) VALUES ("郑先生", 18);

php语法插入数据
$sql = "INSERT INTO tests ".
    "(name, age) ".
    "VALUES ".
    "('郑先生', 18);";
mysqli_query($connect, $sql);

删

mysql语法删除数据
DELETE FROM users WHERE id=1;

php语法删除数据
$sql = "DELETE FROM tests WHERE id=1";
mysqli_query($connect, $sql);

改

mysql语法更新数据
UPDATE users SET name='郑先生' WHERE id=1;

php语法更新数据
$sql = 'UPDATE users SET name="郑先生" WHERE id=1';
mysqli_query($connect, $sql);

查

SELECT column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]

WHERE 查询条件
LIMIT 返回条数
OFFSET 偏移量,查询的间隔

mysql语法查询
SELECT * from users

php语法查询
$sql = 'SELECT * FROM users';
mysqli_query($connect, $sql);

LIKE 子句

mysql语法查询
SELECT * FROM users WHERE name LIKE "%郑%";

php语法查询
$sql = 'SELECT * FROM users WHERE name LIKE "%郑%";'
mysqli_query($connect, $sql);

%在前面表示查询以后面字符结尾的数据
%在后面表示查询以前面字符开头的数据
%在一前一后表示查询包含中间字符的数据

UNION 操作符

连接两个以以上查询语句

SELECT expression1 FROM tables [WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1 FROM tables [WHERE conditions];

UNION ALL 不去查  DISTINCT 去重 默认为DISTINCT

查询的两个表需要查询沟通的咧才可以;

mysql语句查询
SELECT name FROM users WHERE id=1
UNION
SELECT name FROM posts WHERE user_id=1;

php语法查询
$sql = 'SELECT name FROM users WHERE id=1
UNION
SELECT name FROM posts WHERE user_id=1';
mysqli_query($connect, $sql);

ALTER命令

添加tests表的age字段为int类型
ALTER TABLE tests ADD age INT;

删除tests表的字段age
ALTER TABLE tests DROP age;

修改字段类型
ALTER TABLE tests MODIFY age CHAR(10);

修改字段名称和类型
ALTER TABLE tests CHANGE age sex INT;

修改字段默认值
ALTER TABLE tests ALTER age SET DEFAULT 20;

删除默认值
ALTER TABLE tests ALTER age DROP DEFAULT;

修改表名
ALTER TABLE tests RENAME TO alters;

导入数据

mysql语法导入数据

1、source 命令导入sql格式的文件
source /Users/zhenghuihuang/Downloads/test.sql

2、使用 LOAD DATA 导入txt xls等格式的文件
LOAD DATA LOCAL INFILE '/Users/zhenghuihuang/Downloads/tests.xls' INTO TABLE tests;

3、使用 mysqlimport 导入数据
mysqlimport -u root -p --local local /Users/zhenghuihuang/Downloads/tests.xls

如果出现3948 - Loading local data is disabled; this must be enabled on both the clie
修改本地加载功能：mysql> set global local_infile = 1;

导出数据

mysql语法导出
SELECT * FROM tests INTO OUTFILE '/Users/zhenghuihuang/Downloads/tests.xls';

php语法导出
$sql = 'SELECT * FROM tests INTO OUTFILE "/Users/zhenghuihuang/Downloads/tests.xls"';
mysqli_query($connect, $sql);

如果出现The MySQL server is running with the --secure-file-priv option so it cannot execute this statement,可坑是mysql没有设置导出路径,或者导出路径与mysql设置的路径不一致

设置secure-file-priv
vi /usr/local/etc/my.ini
添加 secure-file-priv = '/Users/zhenghuihuang/Downloads/'
保存后重启mysql mysql.server restart

导出 SQL 格式的数据 需要在mysql控制台外执行,不然会报语法错误
mysqldump -u root -p database_name table_name > out_name.txt