DBMS(SQL) COMMANDS
These are the very useful for sql beginners.
1. SELECT * FROM tablename WHERE name = 'Bowser'; (select for single column)
2. SELECT * FROM tablename WHERE birth >= '1998-1-1'; ( " " " " )
3. SELECT * FROM tablename WHERE species = 'snake' OR species = 'bird'; ( " " " " )
4. SELECT name, birth FROM tablename ; ( select for specific columns)
5. SELECT owner FROM tablename ; (for single column)
6. SELECT name, birth FROM tablename ORDER BY birth; (for asc or desc like that purposes)
7. SELECT * FROM tablename WHERE name LIKE 'b%'; (for starting letter begin from b)
8. SELECT * FROM tablename WHERE name LIKE '%fy'; (this name ending with fy)
9. SELECT COUNT (*) FROM tablename ; (for counting table files)
10. LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;
11. Alter table tablename rename=(new tablename); (for rename purpose)
12. Mysqldump –u root –p dbase name > filename.sql (for dump file)
13. Update tablename set column name=’newname’ where cname=’old name’;
(for column update)
14. Loaddata local infile “path with back slashes” into table filename(dbms file) fields terminated by ‘,’ lines terminated by ‘r’ (fields);
( for load data in to system)
15. Grant select on filename(dbname) to newfilename@localhost identified by ‘new password’;
(giving privileges or permissions to some one else)
16. Update tablename add columnname char(*******); (for add column)
17. Update set user password = password(new password) where user ‘root’;
( forchange password)
18. Select curdate(); (for date)
19. Select curtime(); (for time)
20. Select columnname from tablename order by columnname asc;( for ascending order)
21. Select count(*) from svu_tirupati where dist=’number’; (for single column)
22. Delete from tablename where columnname like ‘row number’;
(for single row delete)
23. Drop table tablename; (for delete table)
24. Alter table tablename drop columnname; (for delete single column)
25. Show create table tablename; ( if u want check query)
26. ALTER TABLE tablename AUTO_INCREMENT = 100; (for Specific Increment)
27. Select max(columnname) from tablename; (for maximum rows)
28. Create table tablename(values, index(id), foreignkey(id) references(first tablename) tablename(id) on delete cascade on update cascade);
(for create Foreign key to the table)
29. Select * from tablename into outfile “path” fields terminated by ‘,’ lines terminated by ‘\r’;
(for out put file from the system)
30. Alter table tablename change columnname(old) newtablename varchar;
(for rename of table)
31. Revoke all on *.* from name@localhost identified by ‘,’;
(for cancel permissions from some one)
32. Grant all on *.* to name@localhost identified by’password’;
(for giving all permissions to some one)
33. Load local infile into “path” into table sql tablename(inner table) fields terminated by ‘,’ lines terminated by ‘\r’;
34. SELECT *FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;
(for tables inner join and outer join)
