Summary of MySQL basic commands for IBM-ETP Training

MySQLStudy Notes

Three types of SQL statements: DDL (create, drop, Alter) -- implicit commit, dql (select), DML (insert, delete, update) -- Automatic commit, DCL (commit, rollback)

Some common MySQL statements:

1. Show databases; displays all existing databases

2. Use Database Name; select database

3. Show tables; displays the table name of the selected Database

4. DESC table name. It describes the fields of a table.

5. Insert into table name values (......); /Insert into Table Name (column name 1, column name 2 ,..., Column name N) values (......); Insert data to a table

6. alter table table name Add column name attribute; add Column

7. alter table Table Name drop [column] column name; delete column

8. Update table name set column name 2 =... [Where column name 1 =…]; Modify Table Data

9. Delete from table name [where column name =…]; Delete table data

10. Set autocommit = 0; Make MySQL not automatically submitted, available rollback; UNDO, commit; Confirm submission

11. Select * from table name where condition for update; Data lock, similar to non-automatic submission

12. Lock table name read; Table lock, only read-only operations can be performed on the table

13. Unlock tables; unlock all tables

14. alter table table name add'read' int (2); when using a keyword such as read as a column name or variable, add '', which is on the left of Number 1

15. alter table name change column name new column name attribute; rename column name

16. Alter talble table name Modify column name attribute; Modify column attribute

17. alter table table name add primary key (column name 1, column name 2); set two primary keys

18. Tee D:/test.txt; record the operation to the file in the specified path.

19. Source D:/user. SQL; or source D:/user.txt; import the script.

20. Enter the bin folder in the MySQL installation directory in cmd, enter the mysql-u root-P database name, and enter the password root to enter the MySQL environment.

21. Enter the bin folder in the MySQL installation directory in cmd and enter the mysqldump-u username-P Database Name> file name (for example, D: \ db_userbak. SQL) to export the entire database.

22. Enter the bin folder in the MySQL installation directory in cmd, and enter mysqldump-u username-P database name Table Name> file name (for example, D: \ db_userinfobak. SQL) to export a table.

23. In the MySQL environment, enter select * from table name into OUTFILE "D: \ userinfo.txt" fields terminated by '\ t' lines terminated by' \ r \ n '; output table data in the specified file. Similarly, you can change the suffix to import the table data to excel.

24. usage of the wildcard '_': Select * from table name where column name like 'x _ y'; select results starting with X, ending with Y, and any character in the middle, '_' can start or end with only one character

25. usage of the wildcard '%': Select * from table name where column name like '% xy'; select the result of any number of characters before, followed by XY, 'xy % 'is the result of selecting XY before and after any number of characters.' % XY % 'is the result of selecting any number of characters before and after XY.

26. MD5 ('abc'); MD5 encryption function

27. Upper ('abc'); and ucase ('abc'); replace lowercase letters with uppercase letters.

28. Select * from table name limit; select three from the first record

29. delimiter // change the statement Terminator. //, you can also change it to another symbol.

30. Create trigger name before | after update | Delete | insert on table name

For each row

SQL statement; create a trigger

31. Show triggers; display trigger information

32. Drop trigger name; Delete trigger

33. Create procedure process name ([[In | Out | inout] parameter type,…])

SQL statement;

SQL: Begin

Declare variable name type [default value];

End; create a stored procedure