自己总结的Mysql一些简单操作

连接数据库命令

Mysql –u root –p //然后再输入密码进入数据库命令行界面

DDL语句     数据库定义语言

创建一个数据库的语句是：

Create database database_name;

选择数据库命令是

Use database_name;

删除数据库的操作:

Drop database database_name;

创建表操作

Create table emp(

ename varchar(20),

hiredate date,

sal decimal(10,2),

deptno int(2)

);

表创建完成以后，查看一下表的定义，可以用如下命令：

Desc table_name; //desc student;

有时候需要查看详细的表定义，这时需要的语句为：

Show create table table_name;

For example: show create table emp \G;

 

迅速清空表中的数据:

Truncate newstu；//和delete区别就是truncate相当于删除表之后再重建一张同样结构的表，操作后得到一张全新表。而delete是从删除所有的层面来操作的。 如果决定全部清空的情况下，truncate 的速度更快一些。

删除表的操作为:

Drop table table_name;

修改表

（类型修改）

Alter table  tablename modify column_definition [FIRST|AFTER col_name]

For example:

修改表emp的ename字段的定义，将varchar（10）改为varchar(20)；

Alter table emp modify ename varchar(20);//并不修改字段的位置

Alter table tangseng modify id tinyint unsigned not null auto_crement primary key;

(增加表字段)

Alter table tablenale add [column] column_definition [FIRST|AFTER col_name]

For example：在表emp中增加新的字段age，类型为int（3）；

Alter table emp add column age int(3);//如果没有指定位置，则显示在最后

Alter tabla  tablename add primary key(index_col_name);添加主键名称

Alter table tablename drop primary key;删除唯一索引

Alter table tablename drop index index_name删除索引名称；

(删除表字段)

Alter table tablename drop  [column] column_name;

For example：删除字段age

Alter table emp drop column age;//其中关键字column可有可无

(字段改名)

Alter table tablename CHANGE [column] old_name column_definition [FIRST]

Alter table tablename rename to new_tablename;

Rename table old_name to new_name;

如果在重新命名一个数据表时在它的前面加上数据库名的前缀，就可以把它从一个数据库移动到另一个数据库中。比如说：

Alter table sampdb.t rename to test.t;

Rename table  sampdb.t to test.t;

For example:将字段age改名为age2，同时修改字段类型为int（4）

Alter table emp change age age2 int(4);

Notes: change 和modify都可以修改表的定义，不同的是change后面需要写两次列名，不方便，但是change的优点是可以修改列的名称，modify则不可以。

 

DML语句   数据库操纵语言

1、 插入记录

Insert into tablename(field1,field2……) values(value1,value2……);

For example：插入一条记录

Insert into emp (ename,hiredate,sal,deptno) values (‘zzz’,’2001-09-09’,’200’,1);

也可以不指定字段名称，但是要保持排列一致 。

Insert into emp values (‘zzz’, ‘2001-2-3’,’233’,4);

Note: 含可空字段，非空但是含有默认值的字段，自增字段，可以不用在insert后的字段列表里面出现，values后面只写对应字段名称的value。这些没写的字段可以自动设置为NULL，默认值，自增的下一个数字。

插入多个记录的语句为:

Insert into tablename (field1,field2,……)values

(record1_value1,record1_value2，……),

(record2_value2,record2_value2 , ……);

更新记录

Update tablename set field1 = value1, field2 = value2, ……[where condition];

For example:

Update emp set sal = 4000 where ename = ‘lisa’;

同时更新多个表的操作，例如更新表emp中的字段和表dept中的字段；

Update emp a, dept b set a.sal = a.sal*b.deptno, b.deptname=a.ename where a.deptno = b.deptno;

删除记录

Delete from tablename [where condition];

For example: 在emp中将ename为dony的记录删除

Delete from emp where ename = ‘dony’;

删除多个记录的值为:

Delete t1,t2,…… from t1,t2,…… [where condition];

在下列删除表emp和dept中的deptno为3的记录:

Delete a,b from emp a,dept b where a.deptno = b.deptno and a.deptno = 3;//如果from后面的表明用别名，则delete后面的也要用相应的别名，否则会提示语法错误。无论是单表还是多表，不加where条件，会删除整个记录，所以操作一定要小心。

查询记录

Select * from tablename [where condition]

a.查询不重复的信息

有时候需要查询表中的不重复信息，可以用distinct关键字来表示：

Select ename, hiredate, sal, deptno from emp;

Select distinct deptno from emp;

b.条件查询

select * from emp where deptno = 1;

c.排序和限制

select * from tablename [where condition] [order by field1 (desc|asc), field2 (desc|asc),……];

//其中desc代表的是降序排列，asc代表的是升序排列，默认升序排列。

//例如查询emp表中的记录按照工资高低来显示

Select * from emp order by sal;

如果排序字段的值一样，则值相同的字段按照第二个排序字段进行排序。如果只有一个排序字段，则这些字段相同的记录将会按照无序排列。

对于排序后的记录，如果希望只显示一部分，而不是全部，这时，就需要使用LIMIT关键字来实现，语法如下：

Select …… [ LIMIT offset_start, row_count ] //其中offset_start表示记录的起始偏移量，默认为0，这时只需要写行数就行了，实际显示的就是前N条记录。row_count表示显示的行数。

For example:显示emp表中的前3条记录，同时按照sal排序.

Select * from emp order by sal limit 3;

如果要显示emp表中按照sal排序后，从第二条记录开始的3条记录，可以使用如下命令：

Select * from emp order by sal limit 1,3;

Note:limit经常和order by一起配合，来进行记录的分页显示。

聚合

条件：很多情况下，用户都需要进行一些汇总操作，比如统计整个公司的人数或者统计每个部门的人数，这时就要用到SQL的聚合操作。

聚合操作语法如下：

Select [field1,field2,……,fieldn] fun_name

From tablename

[where where_contition]

[group by field1,field2,……,fieldn [ with rollup]]

[having where_contition]

其中fun_name表示要做的聚合操作，也就是聚合函数，常用的有sum,count(*),max,min

Group by 关键字表示要进行分类聚合的字段，比如要按部分分类统计员工数量，部门就应该写在group by 后面。

With rollup 是可选语法，表明是否对分类聚合后的结果进行再汇总。

Having 关键字表示分类后的结果再进行条件的过滤。

Having和where区别在于having是对聚合后的结果进行条件的过滤，而where是在聚合前就对记录进行过滤。

For example:对emp表中的统计公司的总人数.

Select count(1) from emp;

在此基础上统计各个部门的人数:

Select deptno, count(1) from emp group by deptno;

更细致一些，统计各部门的人数，又要统计总人数。

Select deptno, count(1) from emp group by deptno with rollup;

统计人数大于1的部门：

Select deptno, count(1) from emp group by deptno having count(1)>1;

最后统计公司所有员工的薪水总额，最高和最低薪水。

Select sum(sal),max(sal),min(sal) from emp;

表连接

条件：当需要同时显示多个表中的字段时，就可以用表连接来实现这样的功能。分为外连接和内连接，区别在于内连接仅选出两张表中互相匹配的记录，而外连接会选出和其他不匹配的记录，我们最常用的是内连接。

For example：查询所有雇员的名字和所在部门名称，因为雇员名称和部门分别存放在表emp和表dept中，因此需要使用表连接来进行查询。

Select ename, deptname from emp, dept where emp.deptno=dept.deptno;

外连接又分为：

左连接：包含所有的左边表中的记录，甚至是右边表中没有和它匹配的记录。

右连接：包含所有的右边表中的记录，甚至是左边表中没有和它匹配的记录。

Select ename，deptname from emp left join dept on emp.deptno=dept.deptno;

比较这个查询，两者的区别就是在本例中列出了所有的用户名，即使有的用户名并不存在合法的部门名称。而上例中仅仅列出了存在合法部门的用户名和部门名称。

左连接和右连接类似，两者可以相互转化。

 

子查询

条件：某些情况下，当进行查询的时候，需要的条件是另一个select语句的结果，这个时候就要用到子查询。用于子查询的关键字主要包括in，not in，=，！=，exists，not exists等。

例如：从emp中查询出所有部门在dept表中的所有记录：

Select * from emp where deptno in(select deptno from dept);

如果子查询记录数唯一，还可以用=代替in。

某些情况下，子查询可以转化为表连接，例如：

Select * from emp where deptno in(select deptno from dept);//转化为表连接后：

Select emp.* from emp, dept where emp.deptno =dept.deptno;

子查询和表连接，主要用在优化子查询。

 

记录联合

条件：我们经常会碰到这样，将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示出来， 这个时候，就需要用union 和union all关键字来实现这样的功能，具体语法如下：

Select * from t1

Union | union all

Select * from t2

……

Union | union all

Select * from tn;

Union 和union all的区别是union all是把结果集直接合并在一起，而union是将union all后的结果进行一起distinct，去除重复后的结果。

 

DCL语句 主要是用来管理系统中对象权限的使用。例如创建一个用户z1，具有对sakila数据库中所有表的select/insert权限。

Grant select, insert on sakila.* to ‘z1’@’localhost’ identified by ‘123’;

由于权限的变更，需要将z1的权限变更，收回insert，只能对数据进行select操作。

Mysql  -u root –p root

Revoke insert on sakila.* from ‘z1’@’localhost’;

 

可以使用命令改变存储引擎：

Alter table table_name engine = InnoDB;

Alter table table_name engine = MyIsam;

 

 

 

Mysql可以创建好几种索引：

Alter table table_name add index index_name(index_columns);

Alter table table_name add unique index_name (index_columns);

Alter table table_name add primary key (index_columns);

Alter table table_name add fulltext index_name (index_columns);

Alter table table_name add spatial index_name (index_colunms);

其中index_columns是想添加的索引的一个或者多个数据列。如果索引有多个数据列构成，要用逗号在它们的名字中间隔开。索引名称index_name是可选的，如果不指定，系统会自动挑选名字。

删除索引语句：

Drop Index index_name ON table_name;