1. install

brew install mysql

mysql.server start
mysql_secure_installation
Root1234@

See details for installation.

If you get an error “The server quit without updating PID file (xxx.lock.pid)” while running the commands above, you can remove the file “/tmp/mysql.sock” and try again.

2. basics

mysql -u root -p

mysql> show databases;

mysql> create database table_name;
mysql> use table_name;

mysql> create table test(
            id int auto_increment primary key,
            name varchar(16) not null,
            age int default 18,
            salary decimal(8,2),
            birth datetime
        );
mysql> show tables;
mysql> insert into test values (1, "zhang san", 23);
mysql> insert into test (name, age) values ("li si", 25), ("wang wu", 27);
mysql> select * from test where id>3;
mysql> delete from test where id=1;
mysql> update test set name="abc" where id=2;
mysql> drop table test;

mysql> drop database table_name;

mysql> exit;

3. Pymysql

import pymysql

con = pymysql.connect(host="127.0.0.1", port=3306, user="root", passwd="Root1234@", db="table111")
cursor = con.cursor()

cursor.execute("insert into test values(1, 'a', 111.11)")
sql = "insert into test values(%s, %s, %s)"
cursor.execute(sql, [2, 'aa', 222.22])
cursor.commit()

cursor.execute("select * from test")
data = cursor.fetchall()


cursor.close()
con.close()