mysql操作语句(持续更新ing!!!)

##查看字符集
show character set;




1.6 找回MySQL ROOT用户密码(面试题)
mysqld_safe --skip-grant-table --user=mysql &
mysql> update mysql.user set password=password('123') where user='root' and host='localhost';
mysql> flush privileges;




2.2.1 查看用户:
select user,host from mysql.user;




SELECT * 
FROM `test` 
WHERE my_time
BETWEEN '2014-01-22 00:00:00'
AND DATE_ADD( '2014-01-22 00:00:00', INTERVAL 1 HOUR )




2.1.1 查看数据库:
show databases;
show databases like '%old%';
##这里的%和*意思相似


2.1.2 创建数据库:
CREATE DATABASE oldboy;  #<==字符集和编译指定相同.


2.1.3 指定字符集建库
CREATE DATABASE db_name CHARACTER SET  charset_name COLLATE collation_name
CREATE DATABASE oldgirl CHARACTER SET gbk COLLATE gbk_chinese_ci;
show character set;找字符集.


2.1.4 修改库的字符集:
语法格式
ALTER DATABASE [db_name] CHARACTER SET  charset_name COLLATE collation_name


2.1.5 查看建库的语句
show create database oldboy\G


2.1.6 删库
drop database oldboy;


2.1.8 查看当前所在的库
select database();


2.1.9 查看库里的表
show tables;
 
 
 
2.2.2 创建用户
CREATE USER '用户'@'主机' IDENTIFIED BY '密码';
create user 'oldboy'@'localhost' identified by 'oldboy123';  #只有连接权限


2.2.3 查看用户对应的权限
show grants for oldboy@localhost\G


2.2.4 删除用户
drop user 'user'@'主机域' 


2.2.4.1 特殊的删除方法:
mysql> delete from mysql.user where  user='bbs' and host='172.16.1.%'; 
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;


2.2.5 给用户授权
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT ALL ON *.* TO 'oldboy'@'localhost';


2.2.6 创建用户同时授权
grant all on *.* to oldgirl@'172.16.1.%' identified by 'oldgirl123';
flush privileges;


2.2.6.1 查看授权情况
mysql> show grants for oldgirl@'172.16.1.%'\G


2.2.7 收回权限:
REVOKE INSERT ON *.* FROM oldboy@localhost;             #----这里要和创建的时候对应


2.3.8 更改表名
 
rename table student to test;
alter table test rename to student;         ##--原来的表名字在前,新的在后


2.3.9 增、删、改表的字段
alter table 表名 add 字段 类型 其他; 
CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
先添加性别列，长度为4，内容非空。示例如下：
alter table test add sex char(4);


2.3.10 指定添加年龄列到name列后面的位置
alter table test add age int(4) after name; 


2.3.11 在第一列添加qq字段。
alter table test add qq varchar(15) first; 


2.3.12 删除字段
alter table test drop qq;


2.3.13 同时添加两个字段
alter table test add age tinyint(2) first,add qq varchar(15);




2.3.14 改变字段
alter table ett_ambiguity change ambiguity_state  ambiguity_state tinyint  comment '状态，默认1=正常，0=失效';
ALTER TABLE `ett_photo`
MODIFY COLUMN `PHOTO_DESCRIPTION` varchar(512) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '描述' AFTER PHOTO_TITLE`;
2.3.15 修改字段类型
mysql> alter table test modify age char(4) after name;         
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
2.3.16 修改字段名称mysql> alter table test change age oldboyage char(4) after name;