[note] mysql常用命令和数据类型

mySQL支持的字段类型:

1．INT[(M)] 型： 正常大小整数类型

2．DOUBLE[(M,D)] [ZEROFILL] 型： 正常大小(双精密)浮点数字类型

3．DATE 日期类型：支持的范围是1000-01-01到9999-12-31。MySQL以YYYY-MM-DD格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列

4．CHAR(M) 型：定长字符串类型，当存储时，总是是用空格填满右边到指定的长度

5．BLOB TEXT类型，最大长度为65535(2^16-1)个字符。

6．VARCHAR型：变长字符串类型

====================================================================================

mySOL常用命令：

一、启动与退出 
1、进入MySQL：启动MySQL Command Line Client（MySQL的DOS界面），直接输入安装时的密码即可。此时的提示符是：mysql> 
2、退出MySQL：quit或exit

二、库操作 
1、、创建数据库 
命令：create database <数据库名> 
例如：建立一个名为myClass的数据库 
mysql> create database myClass;

2、显示所有的数据库 
命令：show databases （注意：最后有个s） 
mysql> show databases;

3、删除数据库 
命令：drop database <数据库名> 
例如：删除名为myClass的数据库 
mysql> drop database myClass;

4、连接数据库 
命令： use <数据库名> 

mysql> use myClass; 
屏幕提示：Database changed

5、当前选择（连接）的数据库 
mysql> select database();

6、当前数据库包含的表信息： 
mysql> show tables; （注意：最后有个s）

7. 显示当前mysql版本和当前日期 
select version(),current_date;

select version();

select current_date;

select now(); //精确到秒

 

三、表操作，操作之前应连接某个数据库 
1、建表 
命令：create table <表名> ( <字段名1> <类型1> [,..<字段名n> <类型n>]);

mysql> create table MyClass( 
> id int(4) not null primary key auto_increment, 
> name char(20) not null, 
> sex int(4) not null default '0', 
> degree double(16,2));

2、获取表结构 
命令： desc 表名，或者 show columns from 表名

mysql> desc MyClass; 

mysql> describe MyClass;
mysql> show columns from MyClass;

3、删除表 
命令：drop table <表名> 
例如：删除表名为 MyClass 的表 
mysql> drop table MyClass;

4、插入数据 
命令：insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )] 
例如，往表 MyClass中插入二条记录, 这二条记录表示：编号为1的名为Tom的成绩为96.45, 编号为2 的名为Joan 的成绩为82.99， 编号为3 的名为Wang 的成绩为96.5.
mysql> insert into MyClass values(1,'Tom',96.45),(2,'Joan',82.99), (2,'Wang', 96.59);

PS: 字段名部分可选，可以使用此方法跳过需要为null的字段，插入values，如

mysql> insert into MyClass (id,degree) values(3,96.45),(42,82.99);

5、查询表中的数据 
1)、查询所有行 
命令： select <字段1，字段2，...> from < 表名 > where < 表达式 > 
例如：查看表 MyClass 中所有数据 

mysql> select * from MyClass;

mysql>select id,degree from MyClass;

2）、查询前几行数据 
例如：查看表 MyClass 中前2行数据 
mysql> select * from MyClass order by id limit 0,2;

6、删除表中数据 
命令：delete from 表名 where 表达式 
例如：删除表 MyClass中编号为1 的记录 
mysql> delete from MyClass where id=1;

7、修改表中数据：update 表名 set 字段=新值,… where 条件 
mysql> update MyClass set name='Mary' where id=1;

8、在表中增加字段： 
命令：alter table 表名 add字段 类型 其他; 
例如：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0 
mysql> alter table MyClass add passtest int(4) default '0'

9、更改表名： 
命令：rename table 原表名 to 新表名; 或者 alter table 原名 rename 新名;

例如：在表MyClass名字更改为YouClass 
mysql> rename table MyClass to YouClass;

mysql> alter table MyClass rename YouClass;

10、更新字段内容 : update用于修改表中数据
update 表名 set 字段名 = 新内容 
1) update 表名 set 字段名 = replace(字段名,'旧内容','新内容');

mysql>update shopping_list set item=replace(item,'apple','redapple');

文章前面加入4个空格 
2) update article set content=concat('　　',content);

更新若干列

3. )update artileTable set content='new content', title='new title' where id=1;

11、将查询结果保存到文件 
mysql> select 字段名1[,..,字段名n] from 表明 into outfile '/tmp/outfile.txt';

12、查询数据库当前编码 
mysql> show variables like "character_set%";

13、清空表 
mysql>delete from mytable;

14、mysql数据库的授权

        格式：grant 操作 on 数据库.* to 用户名@登录主机 identified by “密码”;

　　mysql>grant select,insert,delete,create,drop on *.* (或test.*/user.*/..)  to 用户名@localhost identified by '密码'； 

        mysql> grant all privileges on *.* to user@localhost identified by 'something';

        mysql>GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY 'psw' WITH GRANT OPTION;
        mysql>GRANT ALL PRIVILEGES ON *.* TO monty@”%” IDENTIFIED BY 'psw' WITH GRANT OPTION;

　　 如：新建一个用户帐号以便可以访问数据库，需要进行如下操作： 
　　 mysql> grant usage  (USAGE: 只允许登录--其它什么也不允许做。)
　　 -> ON test.* 
　　 -> TO testuser@localhost; 
　　 Query OK, 0 rows affected (0.15 sec) 
　　 此后就创建了一个新用户叫：testuser，这个用户只能从localhost连接到数据库并可以连接到test 数据库。下一步，我们必须指定testuser这个用户可以执行哪些操作： 
　　 mysql> GRANT select, insert, delete,update 
　　 -> ON test.* 
　　 -> TO testuser@localhost; 
　　 Query OK, 0 rows affected (0.00 sec) 
　　 此操作使testuser能够在每一个test数据库中的表执行SELECT，INSERT和DELETE以及UPDATE查询操作。

        再如，创建一个用户custom在特定客户端it363.com登录，可访问特定数据库fangchandb 
        mysql >grant select, insert, update, delete, create,drop on fangchandb.* to custom@ it363.com identified by ‘ passwd'

        mysql>grant select on db1.table1 to kate@% identified by 'root';

15、删除授权
mysql> revoke all privileges on *.* from root@”%”;
mysql> delete from user where user=”root” and host=”%”; 
mysql> flush privileges; 

16. 删除列

命令：alter table 表名 drop column 列名;
mysql>  alter table myClass drop column name;

17. 修改列(即字段)的类型

命令: alter table 表名 modify 列名 类型

mysql> alter table myClass modify name char(100);

18. 修改列(即字段)的名字 (需重新指定类型)

命令: alter table 表明 change 旧列名 新列名 类型

mysql>alter table myClass change name firstName char(50);

19. group by分组命令

常用形式 group by 列名 having count(该列名)>1, 用于将数据以该列分组并选出每组该列数据大于1(即有重复)的数据行,group by一般与聚合函数使用: 如 avg,max,min,count

eg:查询重复name 列

mysql> select * from myClass where name in (

mysql> select name from myClass group by name having count(name)>1);   //以name列分组并挑出每组重复的name列数值大于1                                                                                                                               的 name列，返回此值给上一句 where name in 作为                                                                                                                             条件表达式的结果 

eg:  选出name和job列，并以name列分组

mysql> select name, job from myClass group by name;

eg: 以name列分组并查询每组有多少数据

myaql>select name,count(name) from myClass group by name;

20, 查询不重复的数据，可使用如上的group by having count(..)=1，或者distinct关键字（放在列名前修饰列）

mysql> select distinct 列名1,列名2 from myClass;  //distinct同时作用于此两行，除非列1和列2值完全相同，否则所有列都会检索出。即distinct作用                                                                                       于2列，查的是列1和列2值的组合是否唯一

mysql>select count(distinct 列名) as 结果列名 from myClass; //查询该列中不重复数据的count

mysql>select distinct * from myclass; //返回所有不重复的数据row

21，order by命令，可以与DESC或ASC一起使用，这两个关键字在列名后修饰列。order by子句必须是select语句的最后一个子句

mysql> select name,age from myClass order by ageDESC;

mysql> select name,age from myClass order by age ASC; 

22. 查看数据用户信息:

>select current_user; 

>select user,host,password from mysql.user;

23. 表中所有记录数

mysql->select count(*) as newColumn from myclass; //返回表中所有记录数，列名为newColumn

24. 按字母顺序选取 Persons 表中 LastName 介于 Adams 和 Carter 的所有记录

mysql->select * from persons where lastname BETWEEN 'adams' AND 'carter';

25. 建立索引: 

>create INDEX myclass_name on myClass(name); //or

>create index myclass_name_job on myClass(name,job); // index名建议用表名_列名1_列名2

26. 模糊查询使用关键字LIKE和正则表达式:

> select * from myclass where username LIKE '%李%'; //通配符%指0个或多个字符

27. 编码: 

 建表时设定编码: 

> create table table1 ( id int(4), //...) default charset=utf8;

 建DB时设定编码

> create database db1 character set='utf8' collate='utf8_general_ci';

PS: mysql 4.1 dos即便所有字符集设为utf8，在dos中显示中文仍会乱码，导出仍然正常显示中文。mysql 5.6 dos无此问题。应该是dos显示问题。

=====================================================================

汇总:

全局管理权限：
FILE: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表，刷新日志等。
SHUTDOWN: 关闭MySQL服务。
数据库/数据表/数据列权限：
Alter: 修改已存在的数据表(例如增加/删除列)和索引。
Create: 建立新的数据库或数据表。
Delete: 删除表的记录。
Drop: 删除数据表或数据库。
INDEX: 建立或删除索引。
Insert: 增加表的记录。
Select: 显示/搜索表的记录。
Update: 修改表中已存在的记录。
特别的权限：
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录--其它什么也不允许做。