Mysql數(shù)據(jù)庫(kù)學(xué)習(xí)、應(yīng)用筆記(Java應(yīng)用).doc

Mysql數(shù)據(jù)庫(kù)1、JDBC設(shè)置/登記JDBC驅(qū)動(dòng)程序 Class.forName(org.gjt.mm.mysql.Driver).newInstance(); /連接數(shù)據(jù)庫(kù) sqlConn = java.sql.DriverManager.getConnection(jdbc:mysql:/localhost:3306/xyw,xyw,1234); /創(chuàng)建語(yǔ)句對(duì)象 sqlStmt = sqlConn.createStatement(); /獲取來(lái)自客戶端的數(shù)據(jù) String ls_sql=select * from tbl_name; rs = sqlStmt.executeQuery(ls_sql);2、授予服務(wù)器小程序訪問(wèn)權(quán)mysqlgrant all on xyw.tbl_name to xywlocalhost identified by 1234;3、創(chuàng)建數(shù)據(jù)庫(kù)mysqlcreate database xyw;4、打開數(shù)據(jù)庫(kù)mysqluse xyw;5、建表6、Mysql/Mysqlc的用法 可以用Help獲得61、Execute a SQL script file. Takes a file name as an argument 使用Source命令，例如：mysql source g:project.sqlgrant all on xyw.tbl_project to xywlocalhost identified by 1234;關(guān)閉數(shù)據(jù)庫(kù)C:mysqlbinmysqladmin -u root shutdown要設(shè)置MySQL中的root口令，在你安裝MySQL的目錄的bin子目錄下輸入下面的命令（包括引號(hào)）：mysqladmin -u root password your new passwordC:mysqlbinmysqladmin -u root password your_password使用MYSQLmysql -h localhost -u root pmysql -user=root mydata -p（mydata是數(shù)據(jù)庫(kù)名稱）1、增加了密碼后的登錄格式如下： mysql -u root -p 2、修改登錄密碼 1) mysqladmin -u用戶名 -p舊密碼 password 新密碼 例：mysqladmin -u root password 21century 注：因?yàn)殚_始時(shí)root沒(méi)有密碼，所以-p舊密碼一項(xiàng)就可以省略了。 2)直接修改user表的root用戶口令： mysql user mysql; mysql update user set pasword=password(21century) where user=root; mysql flush privileges; 注：flush privileges的意思是強(qiáng)制刷新內(nèi)存授權(quán)表，否則用的還是緩沖中的口令。3、測(cè)試密碼是否修改成功 1）不用密碼登錄roottest1 local# mysqlERROR 1045: Access denied for user: rootlocalhost (Using password: NO)顯示錯(cuò)誤，說(shuō)明密碼已經(jīng)修改。2）用修改后的密碼登錄roottest1 local# mysql -u root -p Enter password: (輸入修改后的密碼21century)Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 177 to server version: 3.23.48 Type help; or h for help. Type c to clear the buffer.mysql成功！這是通過(guò)mysqladmin命令修改口令，也可通過(guò)修改庫(kù)來(lái)更改口令 4、啟動(dòng)和停止 啟動(dòng)：Mysql從3.23.15版本開始作了改動(dòng)，默認(rèn)安裝后服務(wù)要用mysql用戶來(lái)啟動(dòng)，不允許root用戶啟動(dòng)。 如果非要用root用戶來(lái)啟動(dòng)，必須加上-user=root參數(shù) (./safe_mysqld -user=root &) 停止：mysqladmin -u root -p shutdown 5、導(dǎo)出meeting數(shù)據(jù)庫(kù)： mysqldump -uroot -p21century meeting db_meeting.sql 導(dǎo)人dbname數(shù)據(jù)庫(kù) mysqldump -uroot -p21century dbname xxx.sql 導(dǎo)入數(shù)據(jù)庫(kù)還可用類似于oracle中my_script.sql的方式一次執(zhí)行大量sql語(yǔ)句，這在使用mysqldump不起作用時(shí)非常有用。 例：#./mysql -uroot -p mysqld -installTo remove a server that is installed as a service, first stop it if it is running. Then use the -remove option to remove it: shell mysqld -removeAdding New Users to MySQLFirst, use the mysql program to connect to the server as the MySQL root user: shell mysql -user=root mysqlThen you can add new users by issuing GRANT statements: mysql GRANT ALL PRIVILEGES ON *.* TO montylocalhost - IDENTIFIED BY some_pass WITH GRANT OPTION;mysql GRANT ALL PRIVILEGES ON *.* TO monty% - IDENTIFIED BY some_pass WITH GRANT OPTION;mysql GRANT RELOAD,PROCESS ON *.* TO adminlocalhost;mysql GRANT USAGE ON *.* TO dummylocalhost;These GRANT statements set up three new users: monty A full superuser who can connect to the server from anywhere, but who must use a password some_pass to do so. Note that we must issue GRANT statements for both montylocalhost and monty%. If we dont add the entry with localhost, the anonymous user entry for localhost that is created by mysql_install_db takes precedence when we connect from the local host, because it has a more specific Host field value and thus comes earlier in the user table sort order. admin A user who can connect from localhost without a password and who is granted the RELOAD and PROCESS administrative privileges. This allows the user to execute the mysqladmin reload, mysqladmin refresh, and mysqladmin flush-* commands, as well as mysqladmin processlist . No database-level privileges are granted. (They can be granted later by issuing additional GRANT statements.) dummy A user who can connect without a password, but only from the local host. No privileges are granted-the USAGE privilege type allows you to create a user with no privileges. It has the effect of setting all the global privileges to N. It is assumed that you will grant specific privileges to the account later. 或者mysql INSERT INTO user - VALUES(localhost,monty,PASSWORD(some_pass), - Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y,Y);You can stop the MySQL server by executing this command: shell C:mysqlbinmysqladmin -u root shutdown6、數(shù)據(jù)庫(kù)的備份：退回到DOS：mysqlquitd:mysqlbin使用如下命令對(duì)數(shù)據(jù)庫(kù)abccs進(jìn)行備份：mysqldump-optabccsabccs.dbbabccs.dbb就是你的數(shù)據(jù)庫(kù)abccs的備份文件。7、用批處理方式使用MySQL:首先建立一個(gè)批處理文件mytest.sql,內(nèi)容如下：useabccs;select*frommytable;selectname,sexfrommytablewherename=abccs;在DOS下運(yùn)行如下命令：d:mysqlbinmysqlmytest.sql在屏幕上會(huì)顯示執(zhí)行結(jié)果。如果想看結(jié)果，而輸出結(jié)果很多，則可以用這樣的命令：mysqlmytest.sql|more