mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql |
| test |
| tmp |
+----------+
The mysql database describes user access privileges. The test database often is available as a workspace for users to try things out.
The list of databases displayed by the statement may be different on your machine; SHOW DATABASES does not show databases that you have no privileges for if you do not have the SHOW DATABASES privilege. See Section 13.7.7.14, “SHOW DATABASES Statement”.
If the test database exists, try to access it:
mysql> USE test
Database changed
USE, like QUIT, does not require a semicolon. (You can terminate such statements with a semicolon if you like; it does no harm.) The USE statement is special in another way, too: it must be given on a single line.
You can use the test database (if you have access to it) for the examples that follow, but anything you create in that database can be removed by anyone else with access to it. For this reason, you should probably ask your MySQL administrator for permission to use a database of your own. Suppose that you want to call yours menagerie. The administrator needs to execute a statement like this:
mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
where your_mysql_name is the MySQL user name assigned to you and your_client_host is the host from which you connect to the server.
$> mysql -h host -u user -p menagerie
Enter password: ********
mysql> USE menagerie
Database changed
mysql> CREATE DATABASE menagerie;
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.36-community-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| department |
| mysql |
| org |
| temp |
+--------------------+
5 rows in set (0.00 sec)
mysql> use temp;
Database changed
mysql> show tabels;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tabels' at line 1
mysql> show tables;
+----------------+
| Tables_in_temp |
+----------------+
| student |
+----------------+
1 row in set (0.01 sec)
mysql> insert into student value(1,'ankit');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(1,'ankit');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into student values(2,'lakshay');
Query OK, 1 row affected (0.00 sec)
mysql> drop database if exists temp;
Query OK, 1 row affected (0.04 sec)
mysql> use org ;
Database changed
mysql> create table worker( worker_id int null primary key auto-increment,
-> first_name varchar(255),last_name char (25),salary int(15),joining _date datetime, department char(25));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'auto-increment,
first_name varchar(255),last_name char (25),salary int(15),joini' at line 1
mysql> create table worker( worker_id int null primary key,
-> first_name varchar(255),last_name char (25),salary int(15),joining _date datetime, department char(25));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_date datetime, department char(25))' at line 2
mysql> create table worker( worker_id int null primary key,first_name char(25),last_name char(25),salary int(15),joiningdate datetime,department char(25));
ERROR 1050 (42S01): Table 'worker' already exists
mysql> desc worker;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| worker_id | int(11) | NO | PRI | NULL | auto_increment |
| first_name | char(25) | YES | | NULL | |
| last_name | char(25) | YES | | NULL | |
| salary | int(15) | YES | | NULL | |
| joining_date | datetime | YES | | NULL | |
| department | char(25) | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
6 rows in set (0.04 sec)
mysql> insert into worker(worker_id,first_name,last_name,salary,joining_date ,department) values(001,'monika','arora',100000,'14-02-20 90.00.00','hr');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select *from worker;
+-----------+------------+-----------+---------+---------------------+------------+
| worker_id | first_name | last_name | salary | joining_date | department |
+-----------+------------+-----------+---------+---------------------+------------+
| 1 | monika | arora | 100000 | 2014-02-20 09:00:00 | hr |
| 2 | niharika | verma | 800000 | 2014-06-12 09:00:00 | admin |
| 3 | vishal | singhal | 300000 | 2014-02-20 09:00:00 | hr |
| 4 | amitab | singh | 500000 | 2014-02-20 09:00:00 | admin |
| 5 | viveka | bhatia | 500000 | 2014-06-12 09:00:00 | admin |
| 6 | vipul | diwan | 200000 | 2014-06-11 09:00:00 | account |
| 7 | satish | kumar | 75000 | 2014-01-20 09:00:00 | account |
| 8 | geetika | chauhan | 9999990 | 2014-04-11 09:00:00 | admin |
+-----------+------------+-----------+---------+---------------------+------------+
8 rows in set (0.00 sec)
mysql> select salary from worker;
+---------+
| salary |
+---------+
| 100000 |
| 800000 |
| 300000 |
| 500000 |
| 500000 |
| 200000 |
| 75000 |
| 9999990 |
+---------+
8 rows in set (0.00 sec)
mysql> select first_name,salary from worker;
+------------+---------+
| first_name | salary |
+------------+---------+
| monika | 100000 |
| niharika | 800000 |
| vishal | 300000 |
| amitab | 500000 |
| viveka | 500000 |
| vipul | 200000 |
| satish | 75000 |
| geetika | 9999990 |
+------------+---------+
8 rows in set (0.00 sec)
mysql> select 44+11;
+-------+
| 44+11 |
+-------+
| 55 |
+-------+
1 row in set (0.04 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-04-27 23:58:00 |
+---------------------+
1 row in set (0.04 sec)
mysql> select ucase(first_name) from worker;
+-------------------+
| ucase(first_name) |
+-------------------+
| MONIKA |
| NIHARIKA |
| VISHAL |
| AMITAB |
| VIVEKA |
| VIPUL |
| SATISH |
| GEETIKA |
+-------------------+
8 rows in set (0.04 sec)
mysql> select ucase(last_name) from worker;
+------------------+
| ucase(last_name) |
+------------------+
| ARORA |
| VERMA |
| SINGHAL |
| SINGH |
| BHATIA |
| DIWAN |
| KUMAR |
| CHAUHAN |
+------------------+
8 rows in set (0.00 sec)
mysql> select *from worker where salary < 10000;
Empty set (0.04 sec)
mysql> select *from worker where salary > 10000;
+-----------+------------+-----------+---------+---------------------+------------+
| worker_id | first_name | last_name | salary | joining_date | department |
+-----------+------------+-----------+---------+---------------------+------------+
| 1 | monika | arora | 100000 | 2014-02-20 09:00:00 | hr |
| 2 | niharika | verma | 800000 | 2014-06-12 09:00:00 | admin |
| 3 | vishal | singhal | 300000 | 2014-02-20 09:00:00 | hr |
| 4 | amitab | singh | 500000 | 2014-02-20 09:00:00 | admin |
| 5 | viveka | bhatia | 500000 | 2014-06-12 09:00:00 | admin |
| 6 | vipul | diwan | 200000 | 2014-06-11 09:00:00 | account |
| 7 | satish | kumar | 75000 | 2014-01-20 09:00:00 | account |
| 8 | geetika | chauhan | 9999990 | 2014-04-11 09:00:00 | admin |
+-----------+------------+-----------+---------+---------------------+------------+
8 rows in set (0.00 sec)
mysql> select *from worker where salary = 10000;
Empty set (0.00 sec)
mysql> select *from worker where salary == 10000;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '== 10000' at line 1
mysql> select *from worker where department = 'hr';
+-----------+------------+-----------+--------+---------------------+------------+
| worker_id | first_name | last_name | salary | joining_date | department |
+-----------+------------+-----------+--------+---------------------+------------+
| 1 | monika | arora | 100000 | 2014-02-20 09:00:00 | hr |
| 3 | vishal | singhal | 300000 | 2014-02-20 09:00:00 | hr |
+-----------+------------+-----------+--------+---------------------+------------+
2 rows in set (0.04 sec)
mysql> select *from worker where department = 'account';
+-----------+------------+-----------+--------+---------------------+------------+
| worker_id | first_name | last_name | salary | joining_date | department |
+-----------+------------+-----------+--------+---------------------+------------+
| 6 | vipul | diwan | 200000 | 2014-06-11 09:00:00 | account |
| 7 | satish | kumar | 75000 | 2014-01-20 09:00:00 | account |
+-----------+------------+-----------+--------+---------------------+------------+
2 rows in set (0.01 sec)
mysql> select *from worker where salary between 70000 and 100000;
+-----------+------------+-----------+--------+---------------------+------------+
| worker_id | first_name | last_name | salary | joining_date | department |
+-----------+------------+-----------+--------+---------------------+------------+
| 1 | monika | arora | 100000 | 2014-02-20 09:00:00 | hr |
| 7 | satish | kumar | 75000 | 2014-01-20 09:00:00 | account |
+-----------+------------+-----------+--------+---------------------+------------+
2 rows in set (0.05 sec)
mysql> select *from worker where salary between 700 and 1000000;
+-----------+------------+-----------+--------+---------------------+------------+
| worker_id | first_name | last_name | salary | joining_date | department |
+-----------+------------+-----------+--------+---------------------+------------+
| 1 | monika | arora | 100000 | 2014-02-20 09:00:00 | hr |
| 2 | niharika | verma | 800000 | 2014-06-12 09:00:00 | admin |
| 3 | vishal | singhal | 300000 | 2014-02-20 09:00:00 | hr |
| 4 | amitab | singh | 500000 | 2014-02-20 09:00:00 | admin |
| 5 | viveka | bhatia | 500000 | 2014-06-12 09:00:00 | admin |
| 6 | vipul | diwan | 200000 | 2014-06-11 09:00:00 | account |
| 7 | satish | kumar | 75000 | 2014-01-20 09:00:00 | account |
+-----------+------------+-----------+--------+---------------------+------------+
7 rows in set (0.00 sec)
mysql> select *from worker where department ='hr' or department ='admin' or department ='account';
+-----------+------------+-----------+---------+---------------------+------------+
| worker_id | first_name | last_name | salary | joining_date | department |
+-----------+------------+-----------+---------+---------------------+------------+
| 1 | monika | arora | 100000 | 2014-02-20 09:00:00 | hr |
| 2 | niharika | verma | 800000 | 2014-06-12 09:00:00 | admin |
| 3 | vishal | singhal | 300000 | 2014-02-20 09:00:00 | hr |
| 4 | amitab | singh | 500000 | 2014-02-20 09:00:00 | admin |
| 5 | viveka | bhatia | 500000 | 2014-06-12 09:00:00 | admin |
| 6 | vipul | diwan | 200000 | 2014-06-11 09:00:00 | account |
| 7 | satish | kumar | 75000 | 2014-01-20 09:00:00 | account |
| 8 | geetika | chauhan | 9999990 | 2014-04-11 09:00:00 | admin |
+-----------+------------+-----------+---------+---------------------+------------+
8 rows in set (0.05 sec)
mysql> select *from worker where department in ('admin','account');
+-----------+------------+-----------+---------+---------------------+------------+
| worker_id | first_name | last_name | salary | joining_date | department |
+-----------+------------+-----------+---------+---------------------+------------+
| 2 | niharika | verma | 800000 | 2014-06-12 09:00:00 | admin |
| 4 | amitab | singh | 500000 | 2014-02-20 09:00:00 | admin |
| 5 | viveka | bhatia | 500000 | 2014-06-12 09:00:00 | admin |
| 6 | vipul | diwan | 200000 | 2014-06-11 09:00:00 | account |
| 7 | satish | kumar | 75000 | 2014-01-20 09:00:00 | account |
| 8 | geetika | chauhan | 9999990 | 2014-04-11 09:00:00 | admin |
+-----------+------------+-----------+---------+---------------------+------------+
6 rows in set (0.00 sec)
mysql> select *from worker where department not in ('admin','account');
+-----------+------------+-----------+--------+---------------------+------------+
| worker_id | first_name | last_name | salary | joining_date | department |
+-----------+------------+-----------+--------+---------------------+------------+
| 1 | monika | arora | 100000 | 2014-02-20 09:00:00 | hr |
| 3 | vishal | singhal | 300000 | 2014-02-20 09:00:00 | hr |
+-----------+------------+-----------+--------+---------------------+------------+
2 rows in set (0.00 sec)
mysql> select *from worker order by salary;
+-----------+------------+-----------+---------+---------------------+------------+
| worker_id | first_name | last_name | salary | joining_date | department |
+-----------+------------+-----------+---------+---------------------+------------+
| 7 | satish | kumar | 75000 | 2014-01-20 09:00:00 | account |
| 1 | monika | arora | 100000 | 2014-02-20 09:00:00 | hr |
| 6 | vipul | diwan | 200000 | 2014-06-11 09:00:00 | account |
| 3 | vishal | singhal | 300000 | 2014-02-20 09:00:00 | hr |
| 4 | amitab | singh | 500000 | 2014-02-20 09:00:00 | admin |
| 5 | viveka | bhatia | 500000 | 2014-06-12 09:00:00 | admin |
| 2 | niharika | verma | 800000 | 2014-06-12 09:00:00 | admin |
| 8 | geetika | chauhan | 9999990 | 2014-04-11 09:00:00 | admin |
+-----------+------------+-----------+---------+---------------------+------------+
8 rows in set (0.04 sec)
mysql> select distinct from worker;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from worker' at line 1
mysql> select distinct department from worker;
+------------+
| department |
+------------+
| hr |
| admin |
| account |
+------------+
3 rows in set (0.05 sec)
mysql> -- grp by
mysql> select department from worker group by department;
+------------+
| department |
+------------+
| account |
| admin |
| hr |
+------------+
3 rows in set (0.00 sec)
mysql> select department, count(*) from worker group by department;
+------------+----------+
| department | count(*) |
+------------+----------+
| account | 2 |
| admin | 4 |
| hr | 2 |
+------------+----------+
3 rows in set (0.04 sec)
mysql> select department, count(department) from worker group by department;
+------------+-------------------+
| department | count(department) |
+------------+-------------------+
| account | 2 |
| admin | 4 |
| hr | 2 |
+------------+-------------------+
3 rows in set (0.00 sec)
mysql> select department, avg(salary) from worker group by department;
+------------+--------------+
| department | avg(salary) |
+------------+--------------+
| account | 137500.0000 |
| admin | 2949997.5000 |
| hr | 200000.0000 |
+------------+--------------+
3 rows in set (0.04 sec)
mysql> select department, min(salary) from worker group by department;
+------------+-------------+
| department | min(salary) |
+------------+-------------+
| account | 75000 |
| admin | 500000 |
| hr | 100000 |
+------------+-------------+
3 rows in set (0.00 sec)
mysql> select department, max(salary) from worker group by department;
+------------+-------------+
| department | max(salary) |
+------------+-------------+
| account | 200000 |
| admin | 9999990 |
| hr | 300000 |
+------------+-------------+
3 rows in set (0.00 sec)
mysql> select department, sum(salary) from worker group by department;
+------------+-------------+
| department | sum(salary) |
+------------+-------------+
| account | 275000 |
| admin | 11799990 |
| hr | 400000 |
+------------+-------------+
3 rows in set (0.04 sec)
mysql> select department, count(department) from worker group by department having count(department) ;
+------------+-------------------+
| department | count(department) |
+------------+-------------------+
| account | 2 |
| admin | 4 |
| hr | 2 |
+------------+-------------------+
3 rows in set (0.04 sec)
mysql> select department, count(department) from worker group by department having count(department)>2 ;
+------------+-------------------+
| department | count(department) |
+------------+-------------------+
| admin | 4 |
+------------+-------------------+
1 row in set (0.00 sec)
mysql> create table order_details(order_id int primary key,deliv_date date,cust_id int, foreign key(cust_id) references custmoer(id));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into order_details values(1,'2019-03-11',245);
Query OK, 1 row affected (0.00 sec)
mysql> creat table account (id int primary key, name varchar(255) unique, balance acc_balance_chk check(balance>1000));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creat table account (id int primary key, name varchar(255) unique, balance acc_b' at line 1
mysql> create table account (id int primary key, name varchar(255) unique, balance acc_balance_chk check(balance>1000));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'acc_balance_chk check(balance>1000))' at line 1
mysql> create table account (id int primary key, name varchar(255) unique, balance int,constraint acc_balance_chk check(balance>1000));
Query OK, 0 rows affected (0.06 sec)
mysql> desc account;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(255) | YES | UNI | NULL | |
| balance | int(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.05 sec)
mysql> insert into account values(1,'a',10000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into account values(2,'a',10000);
ERROR 1062 (23000): Duplicate entry 'a' for key 'name'
mysql> insert into account values(2,'4b',10000);
Query OK, 1 row affected (0.00 sec)
mysql> drop table account;
Query OK, 0 rows affected (0.00 sec)
mysql> create table account (id int primary key, name varchar(255) unique, balance int not null default 0);
Query OK, 0 rows affected (0.05 sec)
mysql> insert into account values(2,'a');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into account (id,name) values(2,'a');
Query OK, 1 row affected (0.00 sec)
mysql> select *from account;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 2 | a | 0 |
+----+------+---------+
1 row in set (0.00 sec)
mysql>
 |
Comments
Post a Comment