数据库授权登录
[root@localhost ~]# mysql -uroot -p123456mysql> grant select,lock tables on auth.* to 'admin'@'192.168.30.24'identified by '123456';mysql> grant select,lock tables on client.* to 'admin'@'192.168.30.24' identified by '123456';mysql> flush privileges;[root@www ~]# mysql -uadmin -p -h192.168.30.25查看数据库服务的基本信息:mysql > status退出mysql操作环境 mysql > exit,或者mysql > \q或者ctil C查看当前服务器中有哪些数据库mysql > show databases;查看数据库中的数据表信息mysql > use mysql 切换数据库显示当前操作的数据库mysql > select database();查看有哪些表mysql > show tables;mysql数据库的数据文件放在/usr/local/mysql/data ,每个子目录对应一个数据库,在MyISAM存储引擎时每个表对应三个文件user.frm 表的结构定义user.MYD 表的数据user.MYI 表的索引显示数据表的结构(字段(列))DESCRIBE [数据库名] 表名=descmysql > DESCRIBE user;mysql > DESCRIBE mysql.user;创建新的数据库mysql > create database cloud;mysql > show databases;创建新的数据表mysql > use cloudmysql > CREATE TABLE users (user_name CHAR(16) NOT NULL,user_password CHAR(48)DEFAULT ",PRIMARY KEY (user_name));mysql > show tables;删除指定的数据表DROP TABLE [数据库名] 表名mysql > DROP TABLE cloud.users;删除指定的数据库DROP DATABASE 数据库名mysql > DROP DATABASE cloud;向数据表中插入新的数据记录mysql > use cloud;mysql > INSERT INTO users(user_name,user_passwd) VALUES('zhangsan', PASSWOED('123456'));mysql > INSERT INTO users VALUTE('lisi',PASSWOED('654321'));mysql > select * from users;mysql > insert into users values('aa',123456'),('bb','123456');从数据库中查找符合条件的数据记录mysql > SELECT user_name,user_password FROM user where user_name= 'zhangsan';修改,更新数据表中的数据记录mysql >UPDATE users SET user_passwd=PASSWORD('密码为空')WHERE user_name='lisi';mysql > select * from users;在数据库中修改root用户密码mysql > select user,authentication_string,host from mysql.user;mysql > update mysql.user set authentication_string=PASSWORD('123456') where user='root';mysql > flush privileges;在数据表中删除指定的数据记录mysql > DELETE FROM users WHERE user_name='lisi';msyql> SELECT * FROM users;设置用户权限(用户不存在时,则新建用户)mysql > GRANT select ON cloud.* TO 'teacher'@'localhost; IDENTIFIED BY '123456';[root@localhost ~]#mysql -u teacher -pmysql > show databases;mysql > select * from cloud.users;mysql > SELECT * FROM mysql.user;查看用户的权限mysql > SHOW GRANTS FOR 'teacher'@'localhost';撤销用户的权限mysql > REVOKE all ON cloud.* FROM 'teacher'@'localhost';mysql > SHOW GRANTS FOR 'teacher'@'localhost';显示授权用户的安全权限mysql > show grants;显示服务器错误或警告信息show errors;show warnings;显示当前时间mysql > select now();显示当前用户及时间mysql > select CURRENT_USER(),CURRENT_TIMESTAMP;mysql > seletc user(),now();授权windows 客户机地址拥有访问权限mysql > grant all on *.* to 'root'@192.168.30.21' IDENTIFIED BY '123456';安装Navicat 客户端工具