Ubuntu 安装 MySQL

1、安装

apt install mysql-server mysql-client

2、卸载

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 autoclean

3、设置

3.1 设置密码

重要：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

3.2 设置远程访问：远程访问失败的问题(这样不安全)

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账户的远程权限

4、运维操作

1、启动Mysql(以下三种任一)

systemctl start mysql 

service mysqld start 

/etc/rc.d/init.d/mysqld start

2、检查启动结果

netstat -tulnp | grep ：3306

3、 查看运行状态

systemctl status mysql

4、 停止 Mysql

systemctl stop mysql 

5、 登录 Mysql

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

5、常见的 SQL 操作

1、查看数据库

show databases;

2、创建数据库

create database db_name;

3、选择数据库

use db_name;

4、 查看当前库中的表

show tables;

5、查看表中所有的字段

show columns from talbe_name;

6、 查看表中所有的数据

select * frome talbe_name;

7、添加表字段

ALTER TABLE xxx ADD yyy INT DEFAULT 0;

8、删除表字段

ALTER TABLE xxx DROP  yyy;

9、 设置表字段默认值

ALTER TABLE xxx ALTER yyy SET DEFAULT 0;

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

select id,card from user where uid=unionid;

11、不存在，则插入新用户

insert into user (uid) values (unionid);

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

select id,card where uid=unionid;

13、修改属性:

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

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

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

6、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