Ubuntu 上安装 MySQL(一码到底)

安装

apt install mysql-server

卸载

apt remove mysql-server 
1、sudo apt purge mysql-* 
2、sudo rm -rf /etc/mysql/ /var/lib/mysql 
3、sudo apt autoremove 
4、sudo apt autoreclean 

设置密码

重要：Mysql 5.7.x及以上的版本 可能没有初始密码，直接输入：mysql -u root 就直接进入mysql了。
可以这样设置初始化密码(先mysql -u root进入mysql)，其他很多方法无效：

alter user 'root'@'localhost' identified with mysql_native_password by '123456';

此时，远程客户端可以通过ssh，使用root:123456登录mysql，不需要开启3306端口
修改密码(在普通的命令行中操作，不用登录mysql)

mysqladmin -u root -p password 回车，此时系统要求输入旧密码、回车，然后输入新密码、回车 

设置初始密码(此方法无效)

mysqladmin -u root password 123456 

远程访问失败的问题(这样不安全)

1、安装mysql之后，修改配置文件：/etc/mysql/mysql.conf.d/mysqld.cnf，改为bind-address = 0.0.0.0 
2、grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option; 
3、flush privileges; 

或者通过ssh访问，也就是不用开启mysql种root账户的远程权限
启动Mysql(以下三种任一)

systemctl start mysql 
service mysqld start 
/etc/rc.d/init.d/mysqld start 

检查启动结果

netstat -tulnp \| grep ：3306 

查看运行状态

systemctl status mysql 

停止 Mysql

systemctl stop mysql 

登录 Mysql

mysql -u root -p回车后输入密码 

添加表字段

ALTER TABLE xxx ADD yyy INT DEFAULT 0; 

删除表字段

ALTER TABLE xxx DROP  yyy; 

设置表字段默认值

ALTER TABLE xxx ALTER yyy SET DEFAULT 0; 

查看数据库

show databases; 

创建数据库

create database db_name; 

选择数据

use db_name; 

查看当前库中的表

show tables; 

选择当前库中的表

 use table_name; 

查看表中所有的数据

select * frome talbe_name; 

查看表中所有的字段

show columns from talbe_name; 

使用uid来查询id和card，判断是否存在该用户，

select id,card from user where uid=unionid; 

不存在，则插入新用户

insert into user (uid) values (unionid); 

存在，则返回id和房卡数据

select id,card where uid=unionid; 

修改属性:

 update table set card = card-1 where uid = 353500 and card > 0; 

今天0点截止到当前时间的记录数

select count(*) from table where to_days( date ) = to_days( now() ); 
select count(*) from mjgamedata.t_roominfo where to_days( '时间字段' ) = to_days( now() ); 

Homebrew 安装mysql之后的提示：

==> Installing mysql 
==> Pouring mysql-9.0.1_6.arm64_sequoia.bottle.tar.gz 
==> /opt/homebrew/Cellar/mysql/9.0.1_6/bin/mysqld --initialize-insecure --user=zhuzheng --basedir=/opt/homebrew/Cellar/mysql/9.0.1_6 --datadir=/opt/homebrew/var/mysql --tm 
==> Caveats 
Upgrading from MySQL <8.4 to MySQL >9.0 requires running MySQL 8.4 first: 
- brew services stop mysql 
- brew install mysql@8.4 
- brew services start mysql@8.4 
- brew services stop mysql@8.4 
- brew services start 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 -u root 
To 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: 
/opt/homebrew/opt/mysql/bin/mysqld_safe --datadir\=/opt/homebrew/var/mysql 
 ==> Summary 
/opt/homebrew/Cellar/mysql/9.0.1_6: 324 files, 308.8MB 
==> Running `brew cleanup mysql`... 
 Disable this behaviour by setting HOMEBREW_NO_INSTALL_CLEANUP. 
Hide these hints with HOMEBREW_NO_ENV_HINTS (see `man brew`). 
==> Caveats 
==> mysql 
Upgrading from MySQL <8.4 to MySQL >9.0 requires running MySQL 8.4 first: 
- brew services stop mysql 
- brew install mysql@8.4 
- brew services start mysql@8.4 
- brew services stop mysql@8.4 
- brew services start 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 -u root 
To 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: 
/opt/homebrew/opt/mysql/bin/mysqld_safe --datadir\=/opt/homebrew/var/mysql