Implementing database Programming with Java-06 project: Bank ATM System

1. To create a normal User:

Grammar: CREATE USER ' user ' @   ' Host ' [identified 'password']; User: username,Host: hostname,password: password;
Eg : Create a local user Teacher, Password is 123456 , CREATE USER ' teacher ' @ ' localhost ' identified by ' 123456 ';	Eg : Local User Student , no password CREATE USER   ' student ' @ ' localhost ';
' @ ' localhost ' : local user, allow logon to other hosts can omit

2. create a user and authorize:

GRANT statement can be implemented to create a user authorization at the same time or for existing users;

Grammar: GRANT Priv_type on databasename.tablename//Priv_type : User Action permission list; //Databasename.tablename : Permission range, that is, the library name . Table name to ' user ' @  ' host ' [identified by ' password ') [ with GRANT OPTION]; granting Grant permissions

Common Operation Permissions CREATE and Drop Permissions INSERT , DELETE, select, and update permissions ALTER Permissions

Note: 1. If all permissions are granted, Priv_type can use all 2. Authorization for all databases and tables, permission ranges can be used with the *. *

Eg : Create local user xiaoming, password 123456, give MySchool database student table increase and query permissions GRANT insert,select on myschool.student to ' xiaoming ' /c11>@' localhost ' identified by ' 123456 ';

Eg : Grant the [email protected] user MySchool database View_ Query permissions for student view GRANT SELECT on Myschool.view_student to ' Student ' @ ' localhost ';

To create a user error: Mysql> Grant all on epet.*to ' qingg ' @ ' localhost ' identified by ' 1234 '; ERROR 1290 (HY000): The MySQL server is running with the--skip-grant-tables option so it cannot execute this statement Workaround: Mysql> Flush Privileges Query OK, 0 rows affected (0.01 sec) Mysql> Grant all on epet.*to ' qingg ' @ ' localhost ' identified by ' 1234 '; Query OK, 0 rows affected, 1 warning (0.01 sec)

3. To modify the root account password using the mysqladmin command:

Grammar: mysqladmin–u username –p password "NewPassword"//NewPassword : New Password

Note: mysqladmin need to be executed in DOS command line, enter the original password after carriage return;

Eg : Change the root password to 1234 mysqladmin–u root –p password "1234" Enter password:****

4. use the SET command to modify the user password: After logging in to the MySQL server, use the SET command to modify the current user password;

Grammar: SET PASSWORD [for ' username ' @ ' host ']= PASSWORD ("NewPassword")//[for ' username ' @ ' host '] : You can specify to modify other user passwords
Note: Only the Super Administrator user (such as the root user) can modify the other user password, if it is a normal user, can modify their own password;
Eg : Modify the current user password SET PASSWORD = PASSWORD ("0000");	Eg : Modify other user passwords SET PASSWORD for     ' teacher ' @ ' localhost ' = PASSWORD ("8888");

5. To Delete a user:

Grammar: DROP USER ' username1 ' @ ' host ' [, ' username2 ' @ ' host ' ...]; Eg : DROP USER ' student ' @ ' localhost ';

6. DISTINCT : Distinct is generally used to remove duplicate records from query results, and this statement can only be used in SELECT, INSERT, delete, and update;

Eg : In a table, duplicate values may be included. That's not a problem, but sometimes you might want to just list different values (distinct). Keyword distinct is used to return only different values. Table A : Table B:

1. acting on a single row Select distinct name from A The following results are performed:

2 , acting on multiple columns Eg : Select DISTINCT name, ID from A The following results are performed: is actually based on name and the ID two fields come and go heavy, this way Access , MySQL , SQL Server support at the same time. Eg : Select distinct Xing, Ming from B Returns the following results: The returned result is two lines, which indicates distinct is not the right Xing and the Ming two columns " string concatenation " and then go back to the heavy, but separate for the Xing and the Ming column. Note: From the example above, it can be found that when distinct when applied to more than one field, the scope of its application is all the fields that follow it, not just a field next to it . and distinct can only be placed in front of all fields , The following statement is incorrect: SELECT Xing, distinct Ming from B ; // The statement is wrong The following error is thrown: [ERR] 1064-you has an error in your SQL syntax; Check the manual-corresponds to your MySQL server version for the right syntax-use-near ' DISTINCT-province from B ' At line 1

3 , COUNT Statistics select COUNT (distinct name) from A;  -- table name number of Go-back, SQL Server , MySQL Support, Access does not support Count is not able to count multiple fields, the following SQL cannot run. select COUNT (distinct name, id) from A; if you want to use it, use a nested query, as follows: Select COUNT (*) from (select Distinct Xing, name from B) as M;

4 , distinct must be placed at the beginning: when distinct when applied to multiple fields, the scope of its application is all fields following it, not just one field next to it, and distinct can only be placed in front of all fields, Select ID, distinct name from A; -- The error will be indicated because the distinct must be placed at the beginning

5 , for NULL the processing: distinct the NULL is not filtered, that is, the returned result contains NULL value of the.

6 , with All cannot be used at the same time by default, all results are returned at the time of the query, using the All statement, which is associated with the distinct corresponds to the following: Select All country, province by person

7 , with Distinctrow synonymous Select Distinctrow expression[,expression ...] from tables [where conditions]; This statement is associated with distinct the function is the same.

8 , yes * Handling of the : * The represents an entire column, using the DISTINCT to * Action Select DISTINCT * from person  equivalent select DISTINCT ID, ' name ', Country, province, city from person;

9 , other distinct in the statement Select The fields displayed can only be distinct The specified field, other fields are not possible. For example, if Table a has a " memo " column, if you want to get distinc name , as well as the corresponding " Notes " field and want to go directly through distinct is impossible to achieve.

Implementing database Programming with Java-06 project: Bank ATM System