装包:yum install mysql-server -y
起服务: service mysqld restart
第一次登录进行安全设置除了设置密码,其他都按回车
mysql_secure_installation
这里密码设置为123456
服务端:设置默认字符集服务端:vim /etc/my.cnf
character-set-server=utf8
客户端: vim ~/.my.cnf
[mysqld]
default-character-set=utf8
service mysqld restart
登录mysql: mysql -uroot -p123456
查看库:show databases;
查看表:show tables;
查看表信息:desc (表名字);
查询: select * from (表名字) ;
或者指定查询字段:select user,host,password from mysql.user;
指定查询显示的行数:select user,host,password from mysql.user limit 1;
查询固定的信息: select user,host,password from mysql.user where user="root";
查询加密的密码字符串 :select (password("gdasj"));
查询char长度:select (password("uplooking"));
修改表字段:(增,删,查,改)
alter table xb add class char(128) first;
alter table xb drop name;
alter table xb change id id int not null auto_increment primary key;
查看编码:show variables like "%char%" %代表任意
创建class库: create database db01 character set utf8;
删除库:dorp database db01;
删除用户: drop user tom; 或者drop user tom@'%'; drop user tom @'ip';
或者: delete from mysql.user where name="要删除的用户" host="ip";
修改库的编码: alter database db01 character set utf8;
查看db01的编码: show create database db01;
创建表: create table t01 (id int primary key auto_increment, name char(128) not null, email varchar(256) not null default "tom@qq.com";
按照表t01的结构创建表t02:create table t02 select * from t01; 或者:create table t02 like t01;
也可以指定字段: create table myuser select user,host,password from mysql.user;
按照库mysql的结构创建库db02: create database db02 selevt from mysql;
往表中插入信息: insert into t01 (id, name, email) values (1, "up11", "up11@qq.com");
当插入信息的顺序和表中的字段顺序一样时,可以这样: insert into t01 values (1, "up11", "up11@qq.com");
设置数据库表询时能区分大小写查询: alter table t01 CONVERT TO CHARACTER SET utf8 collate utf8_bin;
指定查询方式: select id,name,email from t01 where name like "Up%";
select id,name,email from t01 where name like "up%";
正则表达式: select id,name,email from t01 where name rike "^up";
select id,name,email from t01 where name rike "^Up";
select id,name,email from t01 where name rlike "^(U|u)p";
统计表中id小于4的有多少行:select count(*) from t01 where id < 4;
更新表信息: update t01 set name=lower(name), email=upper(email);
查询表中name中右边2个字符:select right(name, 2) from t01;
删除: delete from t01 where right(name,2)>20 && right(name,2)<40;
查询当前时间以及年月日: select now();
查询年月日:select current_date;
update mysql.user set host='%' where user="tom" and host="192.168.30.35";
刷新表结构: flush privileges; 执行完update之后执行这个 或者重启mysql服务也会生效,生产中不建议重启服务。
改密码: set password for tom@'%' = password('abc');
在数据库外面直接改密码: mysqladmin -uroot -puplooking password abc
让用户自己能改密码必须授予super的权限:
grant super on *.* to tom@'%';
flush privileges;
用户自己改密码: mysqladmin -h 172.16.184.4 -P 3306 -utom -pabc password uplooking
查看用户有哪些权限:show grants for roy@'%';
收回用户的super权限: revoke super on *.* from roy@'%';
收回所有权限: revoke all on mysql.user from roy;
授予用户创建库的权限: grant create database on *.* to roy@'%';
授予查询权限: grant select on mysql.user to roy;
让用户能把自己拥有的权限授予给别人: grant all on *.* to roy with grant option;
收回with grant optiong的权限:revoke all, grant option from roy;
创建用户: create user tom; 默认省略@'%' 或者直接建用户授权:grant all on *.* to tom identified by password("uplooking");
或者 insert into mysql.user (name, host,password) values (tom, "%", "password(uplooking)");
忘记root密码的办法:vim /etc/my.cnf
加: skip-grant-tables
skip-networking
保存退出
/etc/init.d/mysqld restart
然后直接就可以连接进mysql了再:
update mysql.user set password=password("uplooking") where user="root";
然后退出:
vim /etc/my.cnf 将配置改回来
/etc/init.d/mysqld restart
然后就可以用现在的密码连接了