Mysql数据库学习、应用笔记(Java应用).doc

Mysql数据库1、JDBC设置/登记JDBC驱动程序 Class.forName(org.gjt.mm.mysql.Driver).newInstance(); /连接数据库 sqlConn = java.sql.DriverManager.getConnection(jdbc:mysql:/localhost:3306/xyw,xyw,1234); /创建语句对象 sqlStmt = sqlConn.createStatement(); /获取来自客户端的数据 String ls_sql=select * from tbl_name; rs = sqlStmt.executeQuery(ls_sql);2、授予服务器小程序访问权mysqlgrant all on xyw.tbl_name to xywlocalhost identified by 1234;3、创建数据库mysqlcreate database xyw;4、打开数据库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;关闭数据库C:mysqlbinmysqladmin -u root shutdown要设置MySQL中的root口令，在你安装MySQL的目录的bin子目录下输入下面的命令（包括引号）：mysqladmin -u root password your new passwordC:mysqlbinmysqladmin -u root password your_password使用MYSQLmysql -h localhost -u root pmysql -user=root mydata -p（mydata是数据库名称）1、增加了密码后的登录格式如下： mysql -u root -p 2、修改登录密码 1) mysqladmin -u用户名 -p旧密码 password 新密码 例：mysqladmin -u root password 21century 注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。 2)直接修改user表的root用户口令： mysql user mysql; mysql update user set pasword=password(21century) where user=root; mysql flush privileges; 注：flush privileges的意思是强制刷新内存授权表，否则用的还是缓冲中的口令。3、测试密码是否修改成功 1）不用密码登录roottest1 local# mysqlERROR 1045: Access denied for user: rootlocalhost (Using password: NO)显示错误，说明密码已经修改。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成功！这是通过mysqladmin命令修改口令，也可通过修改库来更改口令 4、启动和停止 启动：Mysql从3.23.15版本开始作了改动，默认安装后服务要用mysql用户来启动，不允许root用户启动。 如果非要用root用户来启动，必须加上-user=root参数 (./safe_mysqld -user=root &) 停止：mysqladmin -u root -p shutdown 5、导出meeting数据库： mysqldump -uroot -p21century meeting db_meeting.sql 导人dbname数据库 mysqldump -uroot -p21century dbname xxx.sql 导入数据库还可用类似于oracle中my_script.sql的方式一次执行大量sql语句，这在使用mysqldump不起作用时非常有用。 例：#./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、数据库的备份：退回到DOS：mysqlquitd:mysqlbin使用如下命令对数据库abccs进行备份：mysqldump-optabccsabccs.dbbabccs.dbb就是你的数据库abccs的备份文件。7、用批处理方式使用MySQL:首先建立一个批处理文件mytest.sql,内容如下：useabccs;select*frommytable;selectname,sexfrommytablewherename=abccs;在DOS下运行如下命令：d:mysqlbinmysqlmytest.sql在屏幕上会显示执行结果。如果想看结果，而输出结果很多，则可以用这样的命令：mysqlmytest.sql|more