MySQL usage - hqzhang/cloudtestbed GitHub Wiki

1. download mysql image

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.20-macos10.12-x86_64.dmg

2. install by double click it in folder (get passwd)

3. running mysql server by

  cd /Library/LaunchDaemons
  sudo launchctl load -F com.oracle.oss.mysql.mysqld.plist

4. running mysql client by

   /usr/local/mysql/bin/mysql -u root -p 
   input passwd

5 change passwd

>ALTER USER 'root'@'localhost' IDENTIFIED BY 'new passwd';
  quit >\q  clear >\c  help >\h 

6. create database

   >CREATE DATABASE pets;
   >show databases; 
   >use pets;

7. create table

 >CREATE TABLE dogs(
  id              INT unsigned NOT NULL AUTO_INCREMENT, # Unique ID for the record
  name            VARCHAR(150) NOT NULL,                # Name of the cat
  owner           VARCHAR(150) NOT NULL,                # Owner of the cat
  birth           DATE NOT NULL,                        # Birthday of the cat
  PRIMARY KEY     (id)                                  # Make the id the primary key
);
 >show tables;
 >DESCRIBE cats;

8. insert information

  >INSERT INTO cats ( name, owner, birth) VALUES
  ( 'Sandy', 'Lennon', '2015-01-03' ),
  ( 'Cookie', 'Casey', '2013-11-13' ),
  ( 'Charlie', 'River', '2016-05-21' );
  >SELECT * FROM cats;

9. more commands

   >SELECT name FROM cats WHERE owner = 'Casey';
   >DELETE FROM cats WHERE name='Cookie';
   >ALTER TABLE cats ADD gender CHAR(1) AFTER name;
   >SHOW CREATE TABLE cats\G;
   >ALTER TABLE cats DROP gender;

---

#sudo apt-get install python-mysqldb
import MySQLdb
db = MySQLdb.connect(host="localhost",    # your host, usually localhost
                     user="root",         # your username
                     passwd="123456",  # your password
                     db="testdb")        # name of the data base

# you must create a Cursor object. It will let
#  you execute all the queries you need
cur = db.cursor()

# Use all the SQL you like
cur.execute("drop database testdb")
cur.execute("create database testdb")
cur.execute("use testdb")
cur.execute("CREATE TABLE dogs  \
  (\
  id              INT unsigned NOT NULL AUTO_INCREMENT, \
  name            VARCHAR(150) NOT NULL,                \
  owner           VARCHAR(150) NOT NULL,                \
  birth           DATE NOT NULL,                        \
  PRIMARY KEY     (id)                                  )")
cur.execute("INSERT INTO dogs ( name, owner, birth) VALUES \
  ( 'Sandy', 'Lennon', '2015-01-03' ),   \
  ( 'Charlie', 'River', '2016-05-21' )")
cur.execute("SELECT * FROM dogs")