MySQL

MySQL语言基本操作

1. 登录数据库

mysql -u root -p ## 以root身份登录

1. 创建新的database

create database Marina; ## 创建Marina数据库

1. 在新的database中创建新的表

use Marina;create table teacher(name varchar(10), age int(2), hiredate date, gender varchar(8));show tables;desc teacher;//详细描述表show create table teacher \G;//更全面的表定义查看命令

1. 表操作

alter table teacher rename [to] teachersalter table teacher modify [column] gender varchar(8) [after hiredate];//将gender移到hiredate字段之前，modify可以修改字段类型alter table teacher add [column] salary int(4) [after hiredate];//增加salary表字段在hiredate后alter table teacher modify [column] name varchar(20) first; //将name字段放到表的首部alter table teacher drop [column] hiredate// 删除表字段hiredatealter table teacher chage [column] gender sex varchar(8);//将gender字段改为sex。 这是change与modify的不同之处，可以修改字符按名称drop table teacher;//删除表

1. DML操作是指对数据库中表记录的操作
   主要包括表记录的插入、更新、删除和查询，是开发人员最频繁的操作.
   
   • 插入记录

insert into teacher(age, name, sex, salary) values('Yi Mingbo', 43, 'male', 4000);//向表中添加记录insert into teacher values('Yi Mingbo', 43, 'male', 4000);//可以不指定字段名称，前提是values后面的顺序与字段一致insert into teacher (age, sex) values('Yi Mingbo', 'male');//某些字段因为有默认值，所以可以指定某一部分字段//还可以一次性插入多条记录insert into teacher(name, age, sex, salary)values('Yi Mingbo', 43, 'Male', 4000),('Cheng Guangxu', 45, 'Male', 5000),('Li Jun', 46, 'Male', 10000);

• 更新记录

update t1,t2,...,tn set t1.field1=expr1, tn.fieldn=exprn [where condition]; //可以同时更新多个表中的数据update teacher set salary=5000 where name='Yi Mingbo';update emp a, dept b set a.sal = a.sal * b.deptno, b.deptname = a.name where a.deptno=b.deptno;//将emp等价为a, dept等价为b,进行相关的更新操作。

• 删除记录

DELETE FROM tablename [WHERE CONDITION]delete from emp where name='dony';//删除name为dony的记录DELETE t1,t2,...,tn FROM t1,t2,...,tn [WHERE CONDITION]delete a,b from emp a, dept b where a.deptno = b.deptno and a.deptno=3;//删除符合条件的两个表格中的记录

需要注意的是，不管是多表还是单表，不加where条件会把表的所有记录删除，所以必须小心。

• 查询记录
  SELECT * FROM tablename [WHERE CONDITION]
  SELECT [ALL FIELD] FROM tablename

select * from teacher;select name, sex, age, salary from teacher;

有的时候需要将表中的字段去除重复后显示出来，可以用distinct关键字实现。

select distinct deptno from emp;

大多数条件下，用户并不需要查询所有的记录，而只是需要根据限定条件来查询一部分数据，用where关键字可以实现这样的操作
where关键字可以使用<、>、>=、<=、!=等比较运算符； 多个条件可以使用or and 等逻辑运算符

select * from teacher where age=43

• 排序和限制
  SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC/ASC], field2[DESC/ASC],…,field3[DESC/ASC]],
  DESC表示降序排列， ASC表示升序排列，默认为升序排列。

select * from teacher order by salary;select * from emp order by deptno, sal desc;//如果对于deptno相同的记录按工资由高到低排列

对于排序后的记录，如果只需要显示一部分，而不是全部，可以使用LIMIT关键字
SELECT [LIMIT offset_start, row_count]
offset为偏移量，起始为0, row_count为前几条记录。

select * from emp order by sal limit 3;//显示emp表按照sal升序排列的前3条记录select * from emp order by sal limit 1, 3;// 显示emp表中按照sal排序后，从第二条记录开始的3条记录。

limit经常和order by 一起使用来进行记录的分页显示。

• 聚合
  SELECT [field1, field2,…,fieldn] fun_name
  FROM tablename
  [WHERE where_condition]
  [GROUP BY field1, field2, …, fieldn
  [WITH ROLLUP]]
  [HAVING where_condition]

fun_name表示要做的聚合操作，也就是聚合函数，常用的有sum(求和)、count(*)(记录数)、 max(最大值)、min(最小值)。
GROUP BY关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，部门就应该写在group by后面
WITH ROLLUP是可选语法，表明是否对分类聚合后的结果进行再汇总
HAVING关键字表示对分类后的结果再进行条件的过滤。

* 注意: * having和where的区别在于， having是对聚合后的结果进行条件的过滤，而where是在聚合前就对记录进行过滤，如果逻辑允许，我们尽可能用where先过滤记录，这样因为结果集减少，将对聚合的效率大大提高，最后在根据逻辑看是否用having进行再过滤。

select salary, count(1) from teacher group by salary with rollup having count(1)>1;//统计salary不同工资的个数，并且求出总共多少个，打印某个工资大于1的情况。select sum(salary),max(salary),min(salary) from teacher;//统计salary的和，最大值，最小值

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

select name, deptname from teacher, dept where teacher.deptno=dept.deptno;

外连接又分为左连接和右连接，具体定义如下：
左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录

select name, deptname from teacher left join dept on teacher.deptno=dept.deptno;// techer为左边表select name, deptname from dept right join teacher on dept.deptno=teacher.deptno;//teacher为右边表

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

select * from teacher where deptno in(select deptno from dept);//子记录不为1select * from teacher where deptno =(select deptno from dept limit 1);//如果条件部分的子查询结果唯一，则可以使用=

子查询可以转化成表连接，例如

select * from teacher where deptno in(select deptno from dept);//等价于表连接select * from teacher.* from teacher, dept where teacher.deptno=dept.deptno;//表连接

子查询与表连接之间的转换主要应用在两个方面
1. MySQL 4.1之前的版本不支持子查询，需要用表连接来实现子查询的功能。
2. 表连接在很多情况下用于优化子查询

• 记录联合
  我们经常会碰到这样的应用，将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示出来，这个时候就需要用到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,去除重复记录后的结果。

select deptno from teacherunion all//直接合并，可能存在重复select deptno from dept;//orselect deptno from teacherunion //去除重复select deptno from dept;