一、从命令行登录MySQL数据库服务器1、登录使用默认3306端口的MySQL/usr/local/webserver/mysql/bin/mysql -u root -p 2、通过TCP连接管理不同端口的多个MySQL（注：MySQL4.1以上版本才有此项功能）/usr/local/webserver/mysql/bin/mysql -u root -p --protocol=tcp --host=localhost --port=3306 3、通过socket套接字管理不同端口的多个MySQL/usr/local/webserver/mysql/bin/mysql -u root -p --socket=/tmp/mysql.sock 4、通过端口和IP管理不同端口的多个MySQL/usr/local/webserver/mysql/bin/mysql -u root -p -P 3306 -h 127.0.0.1 二、数据库操作SQL语句1、显示服务器上当前存在什么数据库SHOW DATABASES; 2、创建名称为maps的数据库CREATE DATABASE maps; 3、删除名称为maps的数据库DROP DATABASE maps;4、选择maps数据库USE maps;
5、查看当前主机的运行情况 SHOW FULL PROCESSLIST; 三、数据库中表操作SQL语句1、显示当前数据库中列表SHOW TABLES; 2、创建数据库表maps_user：存储引擎为MYISAM，字段uid为主键、唯一索引。CREATE TABLE `maps_user` (`uid` INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,`username` VARCHAR( 20 ) NOT NULL ,`password` CHAR( 32 ) NOT NULL ,`dateline` INT(10) NOT NULL ,PRIMARY KEY ( `uid` )) ENGINE = MYISAM ; 3、查看maps_user表结构DESCRIBE maps_user; // DESC maps_user; 4、从表中检索信息4.1、从maps_user表中检索所有记录SELECT * FROM maps_user; 4.2、从maps_user表中检索特定的行：字段username等于abc，按字段id降序排列SELECT * FROM maps_user WHERE username = 'wan' ORDER BY id DESC; 4.3、从maps_user表中检索指定的字段：username和passwordSELECT username, password FROM maps_user; 4.4、从maps_user表中检索出唯一的不重复记录：SELECT DISTINCT username FROM maps_user; 5、插入信息到maps_user表INSERT INTO maps_user (id, username, password, dateline) VALUES ('', 'wan', '123456', '1234231231'); 6、更新maps_user表中的指定信息UPDATE maps_user SET password = '123123' WHERE id = 12; 7、批量替换的SQL语句（将字符串aaa批量替换为bbb的SQL语句）UPDATE 表名 SET 字段名 = REPLACE (字段名, 'aaa', 'bbb'); UPDATE maps_user SET dateline = REPLACE (dateline, '12231231', '126657333'); 8、删除maps_user表中的指定记录DELETE FROM maps_user WHERE id = 12; 9、清空maps_user表DELETE FROM maps_user; 或 TRUNCATE maps_user; 10、删除maps_user表DROP TABLE maps_user; 11、更改表结构，将maps_user表username字段的字段类型改为CHAR(25)ALTER TABLE maps_user CHANGE username username CHAR(25); 12、导入.sql文件到mysql中 /usr/local/webserver/mysql/bin>mysql -u 用户名 -p 数据库名 < ./maps.sql (将当前目录下的mysql.sql导入数据库) 或：SOURCE ./mysql.sql;例如：/usr/local/webserver/mysql/bin>mysql -u root -p test < ./maps.sql Enter password: ****13、UPDATE多表更新：MySQL语法: UPDATE tablename SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]MySQL示例: UPDATE tablea a, table b SET a.uid= b.uid, a.username= b.username WHERE a.id=b.id
14、对SQL语句的分析(DESC/EXPLAIN )EXPLAIN SELECT * FROM maps_user WHERE username = 'wan' ORDER BY id DESC; 四、数据库权限操作SQL语句1、创建一个具有root权限，可从任何IP登录的用户ctowoo，密码为123456GRANT ALL PRIVILEGES ON *.* TO 'ctowoo'@'%' IDENTIFIED BY '123456'; 2、创建一个具有“数据操作”、“结构操作”权限，只能从192.168.122.***登录的用户ctowoo，密码为123456GRANT SELECT , INSERT , UPDATE , DELETE , FILE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES , CREATE VIEW , SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON *.* TO 'ctowoo'@'192.168.122.%' IDENTIFIED BY '123456'; 3、创建一个只拥有“数据操作”权限，只能从192.168.122.24登录，只能操作maps数据库的maps_user表的用户ctowoo，密码为123456GRANT SELECT , INSERT , UPDATE , DELETE ON maps.maps_user TO 'ctowoo'@'192.168.122.24' IDENTIFIED BY '123456'; 4、创建一个拥有“数据操作”、“结构操作”权限，可从任何IP登录，只能操作maps数据库的用户ctowoo，密码为123456GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , INDEX , ALTER , CREATE TEMPORARY TABLES , CREATE VIEW , SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE, EXECUTE ON rewin.* TO 'ctowoo'@'%' IDENTIFIED BY '123456'; 5、删除用户DROP USER 'ctowoo'@'%';
