Mock Lecture in MySQL

1. Mock Lecture in MySQL

2. MySQL • Is a relational database management system (RDBMS) – a system that organizes data into tables that are related to each other. – is an open-source database management system, which is available on a variety of platforms, including Windows, Mac OS X, and various distributions of Linux and Unix. It is centered around MySQL Server, which is the component that manages data and executes SQL statements that are issued to it. MySQL Server provides services to other programs, such as stand-alone applications with graphical user interfaces, web servers, and application servers.

3. MySQL • The MySQL software consists of the MySQL server, several utility programs, and some supporting software. • The MySQL server is the database manager. It runs all the time, in the background, waiting for instructions.

4. Database • The database is a set of files that contain the data. • You can create new databases, add data to databases, and retrieve data from a database by communicating with the server – sending messages that the server executes.

5. Communicating with the MySQL Server • You can manage your database, retrieve data from it, or add data to it by sending messages to the MySQL server. • You can communicate using SQL (structured query language), a language developed specifically to interact with the databases. • You build and send a SQL message, called query, to the MySQL server

6. Communicating with the MySQL Server • The server responds by performing the action defined by the query or, if the server is unable to perform the requested operation, it returns an error message with information about the problem.

7. The Database Structure • MySQL is an RDBMS, which means that the data is organized into tables. Each database can have many tables. Database tables are organized like other tables that you are familiar with – in rows and columns. Columns are called fields, and rows are called records.

8. The Database Structure • The focus of each table is an object, also called an entity, that you want to store information about, such as customer, a product or an order. • Each entity has attributes. In the table, each row represents an entity, and the columns contain the attributes for each entity. • Example: in a table of customers, each row contains information for a single customer, such as name, address and phone number.

9. The SQL Language • SQL is a simple, English-like language that you can learn quickly. • Example: a SQL query that retrieves all the data from a database table is SELECT * FROM Customer • The first word of each query is its name, which specifies the action to perform. Some commonly used queries are CREATE, SELECT, DELETE, INSERT, UPDATE, and ALTER. • The query name is followed by words and phrases that defines the action, such as the table name to be updated or the name of the database to be created.

10. The SQL Language NOTE: • SELECT is the same as select or Select to SQL. • However, the parameters, such as table names, must use the correct case if you are on a Linux system. To Linux, Customer is not the same as customer. • On the other-hand, Windows, is not case sensitive.

11. Send a Query with the mysql Client • When the MySQL is installed, a simple, text-based client program is also installed, called mysql. • The program accepts and sends SQL queries to the MySQL server for execution. • The response is returned to the client and displayed onscreen.

12. Send a Query with the mysql Client • The mysql client must be started from the command line. That is you must be in a command prompt window on Windows or a terminal window on Linux and Mac.

13. DEMO • Open a command prompt window • Change directory from drive Z:\ to drive C:\ • Type: Z:\>C: <enter> • From C:\ • Type: C:\>cd\mysql\bin <enter>

14. DEMO • C:\mysql\bin> • To connect to the mysql server type: C:\mysql\bin>mysqld-nt --console • Then, open another command prompt window do step 1-5 again. • To change to mysql prompt type C:\mysql\bin>mysql –u root

15. DEMO • mysql prompt • Note: • The prompt tells you that mysql is ready for you to enter commands.

16. Prompt Meaning mysql> Ready for new command. -> Waiting for next line of multiple-line command. '> Waiting for next line, collecting a string that begins with a single quote (‘). "> Waiting for next line, collecting a string that begins with a double quote (“). • Note: • To send a query, type the entire query at the mysql client prompt. • Each query should end with a semicolon • If you do not end the query with a semi-colon, the mysql client displays new • prompt and waits for more inputs. It does not send query until you type a • semicolon • Type \c to clear the buffer

17. SQL COMMANDS • SELECT – retrieve information from a database. Syntax: SELECT sql command Example #1: SELECT version(); #2: SELECT current_date; #3: SELECT user(), curdate(); Syntax: SELECT column1, column2,…. FROM tablename Example #1: SELECT * FROM myTB; #2: SELECT num, name, bdate FROM myTB;

18. SQL COMMANDS • SHOW – retrieve information about the structure of a database or table. • CREATE – create a table or database. • USE – to select/use existing database. • DESCRIBE – reveal the structure of a table. • INSERT – add a row to a table. • UPDATE – modify a database entry. • DELETE – delete rows from a table. • DROP – delete entire tables or databases.

19. SAMPLE TABLE The users table with generic data types The users table with more specific data types

20. SAMPLE TABLE CHAR vs. VARCHAR • Both of these types store strings and can be • set with a fixed maximum length. One • primary difference between the two is that • anything stored as a CHAR will always be • stored as string the length of the column • (using spaces to pad it). • Conversely, VARCHAR strings will be only • as long as the stored string itself. The users table with set length attributes

21. CREATING A DATABASE mysql> create database myDB;

22. CREATING A TABLE

23. ALTERING TABLES • The ALTER SQL term is primarily used to modify the structure of a table in your database. • The ALTER statement can even be used for renaming the table as a whole. Syntax: • ALTER TABLE <table name> CLAUSE

24. ALTERING TABLES CLAUSES