MySQL Flashcards
(44 cards)
What are DDL languages?
Data definition languages?
Create
Alter
Drop
Retrieve
Truncate
Comment.
These are used on tables
What are DML languages?
Data Manipulation languages
Select
Insert
Update
Delete
these are used on the table’s data
Query to see all databases on the database server
show databases;
Query to tell the database server which database to work with
use database_name;
How to delete a database?
drop database database_name;
How to create a table?
create table table_name (column1_name datatype (size) null/not null/primary key,column2 column1_name datatype (size) null/not null,…..);
to get all tables in a database
show tables;
How to see the details and pattern of a table?
desc table_name;
this is short cut for
show columns from table_name;
How to insert values into a table?
insert into table_name values (value1,value2,value3...)
How to create a table with primary key?
If the table has more than one primary key then
.….. primary key(column name1,column name2);
mysql> create table train (pnr integer not null, train_num integer not null, nps
gr integer default 1, primary key (pnr,train_num));
mysql> create table train (pnr integer not null primary key, train_num integer not null primary key, npsgr integer default 1);
How to insert multiple rows at once into the table?
insert into table_name values
(value11,value12,value13….),
(value 21,value22,value23);
how to select a particular column/s from a table?
select column_name from tablename;
mysql> select pnr,train_num from train;
What are constraints in Mysql
Not null
Unique
Primary key
Foreign key
Default
Check
How to add a column to already created table?
Alter table table_name add column_name datatype constraints;
mysql> alter table train add (air_conditioning char default 1);
Query OK, 0 rows affected (0.77 sec)
How to add acolumn after a specific column in an already created table?
mysql> alter table train add express char(10) after npsgr;
mysql> select * from train;
+—–+———–+——-+———+——————+
| pnr | train_num | npsgr | express | air_conditioning |
+—–+———–+——-+———+——————+
| 10 | 123 | 2 | NULL | 1 |
+—–+———–+——-+———+——————+
1 row in set (0.00 sec)
How to make the column just inserted to be the first?
alter table train add doj date first;
mysql> alter table train add doj date first;
mysql> desc train;
+——————+———-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——————+———-+——+—–+———+——-+
| doj | date | YES | | NULL | |
| pnr | int(11) | NO | PRI | NULL | |
| train_num | int(11) | NO | PRI | NULL | |
| npsgr | int(11) | YES | | 1 | |
| express | char(10) | YES | | NULL | |
| air_conditioning | char(1) | YES | | 1 | |
+——————+———-+——+—–+———+——-+
6 rows in set (0.02 sec)
How to change the column name of a table?
mysql> alter table train change air_conditioning AC char(10);
alter table table_name change
<em>old_column_name new _column_name datatype(),</em>
old_column_name new _column_name datatype(),
old_column_name new _column_name datatype();
how to delete a column?
mysql> alter table train drop column AC;
alter table table_name drop column column_name;
notice since this drop action is on the table entities we use ‘drop’ not delete .
Difference between modify and change in alter table command
Modify and change both are used in Alter table command.
‘change’ is used for renaming the column,changing data size etc.
‘Modify’ does everything as’ change’ except renaming the column name.
How to delete the complete data of a table?
Truncate table table_name;
it drops the table and create a new table. faster way to the data in a table unlike delete.
How to get first N records of a table?
Select * from table_name limit N;
How to get N records after the record number S?
select * from table_name limit S,N
S is like offset,if s=10 and N =3 then we get record numbers 11,12,13
Does the count of records in mysql strat from 1?
No starts from 0.
Like operator
mysql> select * from train where train_num like ‘%2%’
gives anything which has 2 and starts and ends with anything.
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
