最后,小狼再介绍一些安装mysql后常用的设置
1、mysql允许远程连接
mysql安装后,很多服务器都是远程操作,因此该步骤是必不可少的
1>
MySQL>update user set host = '%' where user = 'root';
执行这个可能报下面错误
ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'
这时候再执行下面,发现已经修改了,那是因为没有FLUSH PRIVILEGES,接着下面步骤就可以了
MySQL>select host, user from user;
2> MySQL>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;
给予任何主机访问数据的权限
3>MySQL>FLUSH PRIVILEGES;
修改生效
2、mysql设置密码方法
鉴于安全考虑,修改密码也是比不可少的步骤
方法一:
(适用于管理员或者有全局权限的用户重设其它用户的密码)
进入命令行模式
mysql -u root mysql
mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='name';
mysql> FLUSH PRIVILEGES;
mysql> QUIT
方法二:
(应用同上,只是方法不同)
mysql -u root mysql
mysql> SET PASSWORD FOR name=PASSWORD('new password');
mysql> QUIT
(以上两种方法我不常用,如果是管理员,我会用其它如phpmyadmin或者MYSQL-front 等工具来管理用户权限,比较直观又方便)
3、查看用户权限
show grants for 你的用户
grant 权限 on 数据库对象 to 用户
增加新用户的好方法
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'pass1';
GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'user1'@'localhost';
GRANT ALL ON *.* TO 'user1'@'localhost';
grant select,insert,update,delete on *.* to user1@localhost Identified by "password1";
4、mysql基础语句
创建一个四列的表
mysql>CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1), birth DATE, birthaddr VARCHAR(20));
1> 插入
往表中插入数据
mysql> insert into mytable values (’abccs’,’f’,’1977-07-07’,’china’);
往表中导入一个文本,每行包含一个记录,用定位符(tab)把值分开,并且以在CREATE TABLE语句中列出的列次序给出
mysql> LOAD DATA LOCAL INFILE "mytable.txt" INTO TABLE mytable;
2> 修改
修改一行记录
mysql> update mytable set birth = "1973-09-02" where name = "tom";
增加一列
mysql> alter table mytable add column single char(1);
删除一整列
mysql> alter table mytable drop column jishu;
mytable为表名,技术为列名
删除一行
mysql> delete from mytable where name=’abc’;
3> 查询
组合查询
mysql> select * from mytable where name='tang' and birth='1983-04-16';
mysql> select name,birth from mytable;
对行排序
mysql> select name,birth from mytable order by birth;
逆向排序
mysql> select name,birth from mytable order by birth desc;
行计数
mysql> select count(*) from mytable;
统计某一列里相同值的行数
select sex,count(*) from mytable GROUP by sex;
多个表组合查询
select name,sex,title from mytable,title where name=jishu and name='wangxin';