First knowledge of MySQL, first knowledge of demon spells
System Information: macOS siider Version 10.12.1 Hombrew 1.1.7
1. Install MySQL
# Enter the command in Terminal and wait for the download and installation to complete. For a long time, $ brew install mysql # If the installation is complete, the following Warning is displayed. The installation operation stops Warning: mysql 5.7.18 _ 1 is already installed.
2. uninstall MySQL
Run brew uninstall mysql or brew remove mysql in Terminal.
Remember to delete the my. cnf file in/usr/local/etc/(to prevent problems during reinstallation ).
Download and install the uninstall method on the official website:
$ sudo rm /usr/local/mysql$ sudo rm -rf /usr/local/var/mysql$ sudo rm -rf /usr/local/mysql*$ sudo rm -rf /Library/Startupltens/MySQLCOM$ sudo rm -rf /Library/PreferencePanes/My*$ vim /etc/hostconfig and removed the line MYSQLCOM=-YES-rm -rf ~/Library/PreferencePanes/My*$ sudo rm -rf /Library/Receipts/mysql*$ sudo rm -rf /Library/Receipts/MySQL*$ sudo rm -rf /var/db/receipts/com.mysql.*
3. Start MySQL to create a database and a simple example of a list
# Start MySQL
$ Mysql. server startStarting MySQL. SUCCESS!
# Create a local connection and log on to $ mysql-u root-pEnter password: # the password has not been changed. the default value is null. the following information is displayed after logon. Welcome to the MySQL monitor. commands end with; or \ g. your MySQL connection id is 4 Server version: 5.7.18 HomebrewCopyright (c) 2000,201 7, Oracle and/or its affiliates. all rights reserved. oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners. type 'help; 'or' \ H' for help. type '\ C' to clear the current input statement. # viewing basic MySQL Information
Mysql> \ s -------------- mysql Ver 14.14 Distrib 5.7.18, for osx10.12 (x86_64) using EditLine wrapperConnection id: 4 Current database: Current user: root @ localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''using delimiter:; Server version: 5.7.18 HomebrewProtocol version: 10 Connection: Localhost via UNIX socketServer characterset: utf8Db charactersiet: utf8Client characterset: utf8Conn. characterset: utf8UNIX socket:/tmp/mysql. sockUptime: 32 secThreads: 1 Questions: 6 Slow queries: 0 Opens: 105 Flush tables: 1 Open tables: 98 Queries per second avg: 0.187 --------------
# The command is case sensitive, but the format is not required.
# Display Database mysql> show databases; + -------------------- + | Database | + -------------------- + | information_schema | mysql | performance_schema | sys | + ------------------ + 4 rows in set (0.00 sec)
# Create database mytestmysql> create database mytest; Query OK, 1 row affected (0.01 sec)
# Check whether the database is successfully created. mysql> show databases; + -------------------- + | Database | + ------------------ + | information_schema | mysql | mytest | performance_schema | sys | + ------------------ + 5 rows in set (0.00 sec)
# No database is selected. mysql> create table employees (-> empid int not null,-> lastname varchar (30),-> firstname varchar (30 ), -> salary float,-> primary key (empid); ERROR 1046 (3D000): No database selected # select mysql> USE mytest; Database changedmysql> SHOW tables; empty set (0.00 sec) # create employees table mysql> create table employees (-> empid int not null,-> lastname varchar (30),-> firstna Me varchar (30),-> salary float,-> primary key (empid); Query OK, 0 rows affected (0.03 sec) # The empid data field also specifies a data constraint ). Data constraints restrict the types of data input to create a valid record.
# Not null data indicates that each record must have a specified empid value. # Finally, the primary key defines the data fields that can uniquely identify each record. This means that each record must have a unique empid value in the table. # Check whether mysql> show tables; + ------------------ + | Tables_in_mytest | + ------------------ + | employees | + ---------------- + 1 row in set (0.00 sec) # insert record, format: insert into table VALUES (....) mysql> insert into employees VALUES (1, 'blum ', 'rich', 2500.00); Query OK, 1 row affected (0.01 sec) # When you INSERT the same record, the following ERROR occurs: mysql> insert into employees VALUES (1, 'blum ', 'rich', 2500.00); ERROR 1062 (23000): Duplicate entry '1 'For key' primary' # DELETE a specified record. Format: delete from employees WHERE enpid = 2; mysql> delete from employees WHERE empid = 1; Query OK, 1 row affected (0.00 sec) mysql> insert into employees VALUES (2, 'blum ', 'Barbara', 45000.00); Query OK, 1 row affected (0.00 sec) mysql> insert into employees VALUES (1, 'blum ', 'rich', 25000.00); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO employees VALUES (3, 'blum ', 'Katie Jane ', 34500.00); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO employees VALUES (4, 'blum', 'jessica ', 52340.00 ); query OK, 1 row affected (0.00 sec) # Query data. Format: SELECT datafields FROM table; the datafields parameter is a list of data field names separated by commas, # specify the fields to be returned. If you want to extract all data field values, you can use an asterisk as a wildcard. # Query all records in mysql> SELECT * FROM employees; + ------- + ---------- + ------------ + -------- + | empid | lastname | firstname | salary | + ------- + ---------- + ------------ + -------- + | 1 | Blum | Rich | 25000 | 2 | Blum | Barbara | 45000 | 3 | Blum | Katie Jane | 34500 | 4 | Blum | Jessica | 52340 | + ------- + ---------- + ------------ + -------- + 4 rows in set (0.00 sec) # common modifier. # WHERE: displays a subset of data rows that meet specific conditions. # Order by: displays data rows in the specified ORDER. # LIMIT: Only displays a subset of data rows. Mysql> SELECT * FROM employees WHERE salary> 40000; + ------- + ---------- + ----------- + -------- + | empid | lastname | firstname | salary | + ------- + ---------- + ----------- + -------- + | 2 | Blum | Barbara | 45000 | 4 | Blum | Jessica | 52340 | + ------- + ---------- + ----------- + -------- + 2 rows in set (0.00 sec) # exit mysql> exitBye # Close the database connection $ mysql. server stopShutting down MySQL .. SUCCESS!