Transcription
MySQL Shell TutorialRon Makhttp://www.cs.sjsu.edu/ mak/Department of Computer EngineeringDepartment of Computer ScienceDepartment of Applied Data ScienceSan Jose State UniversityFebruary 1, 2020IntroductionThis tutorial introduces you to the use of the MySQL Shell, a command-line based way tocommunicate with a MySQL database. You will use Shell to query the Schools database namedaproposl school located on a MySQL database management server (DBMS) running on aremote web hosting site.InstallationGo to https://dev.mysql.com/downloads/ to download and install MySQL Shell. If you wouldrather run the MySQL DBMS locally, then also download and install the MySQL CommunityServer, which is the free version of the DBMS.Start the MySQL ShellTo start the MySQL Shell on MacOS, open a Terminal window and enter the commandmysqlsh. You will then get a few startup messages, including the Shell version, and theprompt for your initial command (Figure 1).Figure 1. MySQL Shell startup and the initial prompt for a command on MacOS.
MySQL Shell TutorialTo start the MySQL Shell in Windows 10, first click the Windows icon in the lower left corner ofyour screen, scroll to MySQL, drop down the menu, and click on MySQL Shell. A Shell windowwill open and prompt for your initial command (Figure 2).Figure 2. MySQL Shell window and the initial prompt for a command in Windows 10.Connect to the Remote DatabaseConnect to the remote database named aproposl school located at apropos-logic.com, port3306 (the default port that MySQL listens to) with the Shell command\connect aproposl school@apropos-logic.com:3306Use the password school :Enter SQL commandsSwitch to SQL command mode with the command \sql :2
MySQL Shell TutorialVerify the presence of the remote aproposl school database with the show databases;command:Use the aproposl school database for subsequent commands:Verify the existence of the database tables:See the data in the tables:3
MySQL Shell Tutorial4Now you can do arbitrary queries and table joins, such as, “Who are John Lane’s students?”To quit the MySQL Shell, enter \quit :MySQL Shell l/8.0/en/
MySQL Shell Tutorial5SQL and Database Design Exercises1. Can you recreate the SQL queries in the PowerPoint slides from their plain Englishquestions?2. What other queries can you come up with based on the existing tables?3. There is another table in the database, named contact info. What’s in that table? Howis it related to the other tables? Can you do joins of contact info with the other tables?4. What is the relationship between the teacher table and the class table? How manyteachers can teach a class? What design changes to the database are necessary to allowmultiple teachers to teach a class? Can you draw an ER diagram of your new design andmap it to tables? (Unfortunately, since the database aproposl school is being shared,you won’t be able to make changes to the tables on the remote server.)Use the tool ERDPlus at http://erdplus.com to draw an ER diagram. Scrolldown and watch the videos on the home page for quick tutorials. You caneither map and draw the tables yourself, or you can ask the tool toautomatically map your ER diagram to tables for you.Google “SQL tutorial” for good tutorials(text and videos) on the web!
MySQL Shell Tutorial 2 To start the MySQL Shell in Windows 10, first click the Windows icon in the lower left corner of your screen, scroll to MySQL, drop down the
