Mar 30, 2018 Re: MySQL 5.7.21 for Mac OS X 10.13 (High Sierra) Preference Pane will not load.
This article is applicable to MySQL 5.7. There are some non-compatible changes in MySQL 5.7.7 over 5.6 (read ' for installing MySQL 5.6). This practical can be completed in a 2-3 hour session, if you don't encounter major problems installing MySQL. Introduction to Relational Database and SQL Relational Databases A relational database organizes data in tables. A table has rows (or records) and columns (or fields). Tables are related based on common columns to eliminate data redundancy and ensure data integrity.
Popular Relationship Database Management System (RDBMS) includes the commercial Oracle, IBM DB2, Microsoft SQL Server and Access, SAP SyBase and Teradata; and the open-source MySQL, PostgreSQL, Embedded Apache Derby (Java DB), mSQL (mini-SQL), SQLite and Apache OpenOffice's Base. Structure Query Language (SQL) A high-level language, called Structure Query Language (SQL), is designed for interacting with the relational databases.
SQL defines a set of commands, such as SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, DROP TABLE, and etc. Codd (of IBM) proposed the Relational Database Model in 1970. SQL, one of the earlier programming language, was subsequently developed by Donald D. Chamberlin and Raymond F.
Boyce at IBM in the early 1970s. Oracle, subsequently, took it to a new height. ANSI (American National Standard Institute) established the first SQL standard in 1986 (SQL-86 or SQL-87) - adopted by ISO/IEC as 'ISO/IEC 9075' - followed in 1989 (SQL-89), 1992 (SQL-92 or SQL2), 1999 (SQL-99 or SQL3), 2003 (SQL-2003), 2006 (SQL-2006) and 2011 (SQL-2011). However, most of the database vendors have their own directs, e.g., PL/SQL (Oracle), Transact-SQL (Microsoft, SAP), PL/pgSQL (PostgreSQL).
SQL By Examples A relational database system contains many databases. A database comprises one or more tables. A table have rows (or records) and columns (or fields). Suppose we have created a table called class101, in a database called studentdb, with 3 columns: id, name and gpa. A column has a data type. We choose: INT (integer) for column id, FLOAT (floating-point number) for gpa, and VARCHAR(50) (variable-length string of up to 50 characters) for name.
There are 4 rows in the table as follows: Database: studentdb Table: class101 +-+-+-+ id (INT) name (VARCHAR(50)) gpa (FLOAT) +-+-+-+ 1001 Tan Ah Teck 4.5 1002 Mohammed Ali 4.8 1003 Kumar 4.8 1004 Kevin Jones 4.6 +-+-+-+ SQL provides an easy and intuitive way to interact with relational databases. SELECT - SYNTAX SELECT column1, column2. FROM tableName WHERE criteria SELECT. FROM tableName WHERE criteria - EXAMPLES SELECT name, gpa FROM class101 - Select columns name and gpa from table class101. +-+-+ name gpa +-+-+ Tan Ah Teck 4.5 Mohammed Ali 4.8 Kumar 4.8 Kevin Jones 4.6 +-+-+ SELECT. FROM class101 - Select ALL columns from table class101. The wildcard.
denotes all the columns. +-+-+-+ id name gpa +-+-+-+ 1001 Tan Ah Teck 4.5 1002 Mohammed Ali 4.8 1003 Kumar 4.8 1004 Kevin Jones 4.6 +-+-+-+ SELECT name, gpa FROM class101 WHERE gpa = 4.7 - Select columns name and gpa, where the rows meet the criteria. You can compare numbers using =, , =, (!=) +-+-+ name gpa +-+-+ Mohammed Ali 4.8 Kumar 4.8 +-+-+ SELECT name, gpa FROM class101 WHERE name = 'Tan Ah Teck' - Full-match (= or!=) on string. Strings are enclosed in quotes. +-+-+ name gpa +-+-+ Tan Ah Teck 4.5 +-+-+ SELECT name FROM class101 WHERE name LIKE 'k%' - Use 'LIKE' for string pattern-matching, with - wildcard% matches zero or more (any) characters; - wildcard matches one (any) character. +-+ name +-+ Kumar Kevin Jones +-+ SELECT. FROM class101 WHERE gpa 4 AND name LIKE 'k%' ORDER BY gpa DESC, name ASC - Use AND, OR, NOT to combine simple conditions.
Order the results in DESC (descending) or ASC (Ascending) +-+-+-+ id name gpa +-+-+-+ 1003 Kumar 4.8 1004 Kevin Jones 4.6 +-+-+-+ DELETE - SYNTAX DELETE FROM tableName WHERE criteria - EXAMPLES DELETE FROM class101 - Delete ALL rows from the table class101! Beware that there is NO UNDO! DELETE FROM class101 WHERE id = 33 - Delete rows that meet the criteria.
INSERT - SYNTAX INSERT INTO tableName VALUES ( firstColumnValue., lastColumnValue) - All columns INSERT INTO tableName ( column1, column2.) VALUES ( value1, value2.) - Selected Columns - Example INSERT INTO class101 VALUES (1001, 'Tan Ah Teck', 4.5) - List value of all columns. INSERT INTO class101 (name, gpa) VALUES ('Peter Jones', 4.55) - Missing fields will be set to their default values or NULL UPDATE - SYNTAX UPDATE tableName SET column = value WHERE criteria - EXAMPLES UPDATE class101 SET gpa = 5.0 - ALL rows UPDATE class101 SET gpa = gpa + 1.0 WHERE name = 'Tan Ah Teck' - Selected rows CREATE TABLE - SYNTAX CREATE TABLE tableName ( column1Name column1Type, column2Name column2Type.) - EXAMPLES CREATE TABLE class101 (id INT, name VARCHAR(50), gpa FLOAT) DROP TABLE - SYNTAX DROP TABLE tableName - EXAMPLES DROP TABLE class101 - Delete the table. Beware that there is No UNDO!!!
Notes:. Case Sensitivity: SQL keywords, names (identifiers), strings may or may not be case-sensitive, depending on the implementation.
In MySQL, the keywords are NOT case-sensitive. For clarity, I show the keywords in UPPERCASE in this article. For programmers, it is BEST to treat the names (identifiers) and strings as case-sensitive. In MySQL, column-names are always case insensitive; but table-names are case-sensitive in Unix, but case-insensitive in Windows (confused!!). Case-sensitivity in string comparison depends on the collating sequence used (?!). String: SQL strings are enclosed in single quotes, but most implementations (such as MySQL) also accept double quotes. Introduction to MySQL Relational Database Management System (RDBMS) SQL is a programming language for interacting with relational databases.
On the other hand, MySQL is a software system - a Relational Database Management System. MySQL is one of the most used, industrial-strength, open-source and free Relational Database Management System (RDBMS). MySQL was developed by Michael 'Monty' Widenius and David Axmark in 1995.
It was owned by a Swedish company called MySQL AB, which was bought over by Sun Microsystems in 2008. Sun Microsystems was acquired by Oracle in 2010.
MySQL is successful, not only because it is free and open-source (there are many free and open-source databases, such as Apache Derby (Java DB), mSQL (mini SQL), SQLite, PostgreSQL and Apache OpenOffice's Base), but also for its speed, ease of use, reliability, performance, connectivity (full networking support), portability (run on most OSes, such as Unix, Windows, Mac), security (SSL support), small size, and rich features. MySQL supports all features expected in a high-performance relational database, such as transactions, foreign key, replication, subqueries, stored procedures, views and triggers. MySQL is often deployed in a LAMP (Linux-Apache-MySQL-PHP), WAMP (Windows-Apache-MySQL-PHP), or MAMP (MacOS-Apache-MySQL-PHP) environment. All components in LAMP is free and open-source, inclusive of the Operating System. The mother site for MySQL is. The ultimate reference for MySQL is the 'MySQL Reference Manual', available at. The reference manual is huge - the PDF has over 3700 pages!!!
Mysql 5.1 Download
MySQL operates as a client-server system over TCP/IP network. The server runs on a machine with an IP address, on a chosen TCP port number. The default TCP port number for MySQL is 3306. Users can access the server via a client program, connecting to the server at the given IP address and TCP port number.
A MySQL database server contains one or more databases. A database contains one or more tables. A table consists of rows (or records) and columns (or fields). How to Install MySQL 5.7 and Get Started with SQL Programming I want you to install MySQL on your own machine, because I want you to learn how to install, customize and operate complex industrial software system. Installation could be the hardest part in this exercise. Step 0: Create a directory to keep all your works Create a directory called ' c: myWebProject' (for Windows) or ' /myWebProject' (for Mac OS X) to keep all your works.
// For Windows: Use 'C: myWebProject' // Launch a 'CMD' and issue these commands: c: cd mkdir myWebProject // For Mac OS X: Use '/myWebProject' (where ' denotes your home directory) // Launch a 'Terminal' and issue these commands: cd mkdir myWebProject Use your graphical interface, e.g., File Explorer (Windows), or Finder (Mac OS X) to verify this directory. (Of course you can use your graphical interface to create this directory!) For novices: It is important to follow this step. Otherwise, you will be out-of-sync with this article and will not be able to find your files later.
Step 1: Download and Install MySQL. For Ubuntu Refer to '. I shall assume that MySQL is installed in directory 'c: myWebProject mysql' (for Windows) or ' /usr/local/mysql' (for Mac OS X).
But you need to TAKE NOTE OF YOUR MySQL INSTALLED DIRECTORY. Hereafter, I shall denote the MySQL installed directory as in this article. Step 3: Start the 'Server' The MySQL is a client-server system. The database is run as a server application. Users access the database server via a client program, locally or remotely thru the network, as illustrated:. The server program is called ' mysqld' (with a suffix 'd', which stands for daemon - a daemon is a non-interactive process running in the background).
The client program is called ' mysql' (without the 'd'). The programs mysqld and mysql are kept in the ' bin' sub-directory of the MySQL installed directory. Startup Server. For Windows To start the database server, launch a new CMD shell: - Change the current directory to MySQL's binary directory - Assume that the MySQL installed directory is 'c: myWebProject mysql' c: cd myWebProject mysql bin - Start the MySQL Database Server mysqld -console. XXXXXX XX:XX:XX Note mysqld: ready for connections.
Version: '5.7.xx' socket: ' port: 3306 MySQL Community Server (GPL) Notes: The -console option directs the output messages to the console. Without this option, you will see a blank screen. For Mac OS X The EASY WAY: Via the graphical control.
Click 'Apple' Icon ⇒ System Preferences ⇒ MySQL ⇒ Stop. WARNING: You should properly shutdown the MySQL server. Otherwise, you might corrupt the database and might have problems restarting it. BUT, if you encounter problem shutting down the server normally, you may kill the ' mysqld' process in Task Manager (for Windows); or Activity Monitor (for Mac OS X); or System Monitor (for Ubuntu).
Step 4: Start a 'Client' Recall that the MySQL is a client-server system. Once the server is started, one or more clients can be connected to the database server. A client could be run on the same machine (local client); or from another machine over the network (remote client). To login to the MySQL server, you need to provide a username and password. During the installation, MySQL creates a superuser called ' root' with a temporary password. I hope that you have taken note of this password!
Mysql Server 5.7
Mysql : Mysql 5.7.21 For Mac Free
(Otherwise, re-install!) The MySQL installation provides a command-line client program called ' mysql'. (Recall that the server program is called ' mysqld' with a suffix 'd'; the client program does not have the suffix ' d'). Let's start a command-line client with the superuser ' root'. First, make sure that the server is running.
See previous step to re-start the server if it has been shutdown. For Windows Start Another NEW CMD shell to run the client (You need to keep the CMD that run the server): - Change the current directory to MySQL's binary directory. Assume that the MySQL is installed in 'c: myWebProject mysql'. C: cd myWebProject mysql bin - Start a client as superuser 'root' (-u), and prompt for password (-p) mysql -u root -p Enter password: // Enter the root's password set during installation. Welcome to the MySQL monitor.
Commands end with; or g. Your MySQL connection id is 1 Server version: 5.7.21 Type 'help;' or ' h' for help. Type ' c' to clear the current input statement. Mysql - Client started. The prompt changes to 'mysql'. You can now issue SQL commands such as SELECT, INSERT and DELETE.
For Mac OS Open a NEW 'Terminal' and issue these commands to start a MySQL client with superuser root: - Change the current directory to MySQL's binary directory. Cd /usr/local/mysql/bin - Start a client with superuser 'root' (-u), and prompt for password (-p)./mysql -u root -p Enter password: // Enter the root's password given during installation. You will NOT any. for maximum security Welcome to the MySQL monitor. Commands end with; or g.
Mysql - Client started. The prompt changes to 'mysql'. You can now issue SQL commands such as SELECT, INSERT and DELETE.
(Skip Unless.) Read '. Step 5: Change the Password for the Superuser 'root' As mentioned earlier, the MySQL installation creates a superuser called ' root' with a temporary random password. ' root' is a privileged user that can do anything, including deleting all the databases. You are required to change the root's password immediately after logging in. Changing the Password for 'root' Let's continue with our client session started earlier.
Change password for 'root'@'localhost'. Replace xxxx with your chosen password - (For my students: use xxxx as the password. Otherwise, you will ask me what is your password next week.) - Take note that strings are to be enclosed by a pair of single-quotes. Mysql alter user 'root'@'localhost' identified by ' xxxx'; Query OK, 0 rows affected (0.00 sec) - logout and terminate the client program mysql quit Bye Re-Start a Client as 'root' with the New Password We have just changed the password for root and exited the client. Start a client and login as root again.
Enter the password when prompted. For Mac OS X - Change directory to MySQL's binary directory cd /usr/local/mysql/bin - Start a MySQL client./mysql -u root -p Enter password: // Enter the NEW password Welcome to the MySQL monitor.
Mysql - client started, ready to issue SQL command Step 6: Create a New User The superuser 'root' is privileged, which is meant for database administration and is not meant for operational. We shall create a new user - let's call it ' myuser' - with a lesser privilege. For Mac OS X - Start a NEW 'terminal' cd /usr/local/mysql/bin./mysqldump -u myuser -p -databases studentdb /myWebProject/backupstudentdb.sql // denotes the home directory of the current login user Study the output file, which contains CREATE DATABASE, CREATE TABLE and INSERT statements to re-create the database and tables dumped earlier. Restore via ' source' command in a mysql client You can restore from the backup by running the ' source' command in a MySQL client.
For example, to restore the studentdb backup earlier.