MySQL works in
client-server model. You can use mysql client to communicate with mysql server.
You can communicate to mysql client using SQL (Structured Query Language).
You can run mysql in
both interactive mode (or) in batch mode like cron jobs. This tutorial is for
beginners, after this you can able to explore by yourself.
- Introduction to MySQL
- Install and setup MySQL on MAC
- Setting up MySQL user account
- What is database connection?
- Create database
- print all the databases
- Create table in a database
- Specify the database you want to work
- Get table names of a particular database
- Insert data into table
- Get the data from table
- Drop database
- Drop table
- Exit from mysql prompt
Introduction to MySQL
- Data types
- character data type
- Text data types
- Character sets
- Numeric Data types
- Temporal Data types
- enum type
- Constraints
Data types, constraints
- Create tables from other tables
- Inserting data into one table using other table data
- Get all tables in a database
- Get specific fields from table
Working with tables
- Operators in SQL
- Logical Operators
Operators
- Exploring SELECT statement
- Query clauses in SELECT statement
- WHERE clause
- Column aliases
- HAVING clause
- UPDATE Query
- DELETE Query
- ALTER command
- TRUNCATE: Truncate data from table
- LIKE operators and wildcards
- TOP and LIMIT clauses
- ORDER BY clause
- GROUP BY Clause
- DISTINCT keyword
- AS clause
- Auto increment a field
- Primary key
- Foreign key constraint
- Get all the constraints on a table
- Add new auto increment column to existing table
- Modify existing column as auto increment
- Get the results in xml format
Queries on tables
- Table Aliases
- Joins
- LEFT JOIN
- RIGHT JOIN
- VIEWS
Joins, views
- ABS() function
- AVG function
- ROUND function
- DIV: Perform integer division
- MOD: Get the reminder
- CEIL & FLOOR: Get ceil and floor of anumber
- EXP: Get Exponent of a number
- LOG, LOG10: Get logarithm of a number
- POW(x, y): Return x to the power y
- GREATEST: Get greatest element from list of elements
- LEAST: Get leastelement from list of elements
- RADIANS: Convert degrees to radians
- SQRT: get Square root of a number
- TRUNCATE: Truncate decimal number
- RAND: Generate random number
Numeric functions
- CONCAT: Concatenate strings
- UPPER and LOWER functions
- TRIM(): Remove leading and trailing spaces
- SUBSTR(): Get the substring
- Get left and right most characters
- LENGTH: Estimates the space that occupied in memory
- CHAR_LENGTH: Return number of characters in string
- REPEAT: Repeat string in given number of times
- REPLACE: Replace a string with other string
- REVERSE() : Reverse the string
- STRCMP: Compare two strings
Working with strings
- ADDDATE: Increment date
- SUBDATE: Subtract values from given date
- Get current system date
- Get current system time
- DAYNAME: get day name of the date
- NOW() : current system date and time.
- MAKEDATE(): Return date
- MONTHNAME: Get the month name of the date
- TIMEDIFF() : Get difference between two date times
- TIME_TO_SEC: Convert time to seconds
- unix_timestamp() : Return timestamp in seconds since '1970-01-01 00:00:00'
Working with dates
- COUNT() : Get count of records
- MAX: Get largest value of column
- MIN():Get least value of column
- SUM(): Sum all the values of numeric column
- AVG(): Get average of values in numeric column
Aggregate functions
- database(): Get current database name
- user(): Get currently logged in user details
- version(): Get current server version
- Sub queries
- Derived tables
- Temporary table
- Find 2nd max salary
- Find nth highest salary
- Get all the options supported by MySQL
- ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
- Install and setup MySQL on MAC
No comments:
Post a Comment