分类：
2008-05-15 20:41:39
DDL(数据定义语言)语句
1,创建数据库 create database 数据库名;
|
mysql> create database javaworld; Query OK, 1 row affected |
2,查看已存在数据库 show databases;
|
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | javaworld | | mysql | | test | +--------------------+ 4 rows in set |
3,选择要操作的数据库 use 数据库名;
|
mysql> use javaworld; Database changed |
4,查看javaworld数据库中所有的表
show tables;
|
mysql> show tables; Empty set |
5,删除数据库 drop database 数据库名;
|
mysql> drop database javaworld; Query OK, 0 rows affected mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set |
6,创建表 create table 表名(字段名(字段类型) 约束);
|
mysql> create table emp(ename varchar(10), hiredate date, sal decimal(10,2), deptno int(2)); Query OK, 0 rows affected |
7,查看表的描述 desc 表名;
|
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set |
8,查看创建表的SQL语句(引擎和字符集也可看到)
show create table 表名;
|
mysql> show create table emp; +-------+----------------------------------------------------------------- | Table | Create Table +-------+----------------------------------------------------------------- | emp | CREATE TABLE `emp` ( `ename` varchar(10) default NULL, `hiredate` date default NULL, `sal` decimal(10,2) default NULL, `deptno` int(2) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 | +-------+----------------------------------------------------------------- 1 row in set |
9,删除表 drop table 表名;
|
mysql> drop table emp; Query OK, 0 rows affected |
10,修改表
<1>修改表类型
如:修改emp表的ename字段定义,将varchar(10)改为varchar(20)
|
mysql> alter table emp modify ename varchar(20); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set |
<2>增加表字段
如:表emp上新增字段age,类型为int(3)
|
mysql> alter table emp add column age int(3); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | | age | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set |
<3>删除表字段
如:将字段age删除
|
mysql> alter table emp drop column age; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set |
<4>字段改名
如:将ename改名为name,同时修改字段类型为varchar(10)
|
mysql> alter table emp change ename name varchar(10); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set |
<5>修改字段排列顺序
如:将birth加在ename后
|
mysql> alter table emp add birth date after ename; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set |
如:修改字段age,将它放在最前面
|
mysql> alter table emp modify age int(3) first; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | age | int(3) | YES | | NULL | | | ename | varchar(10) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 6 rows in set |
<6>更加表名
|
mysql> alter table emp rename emp1; Query OK, 0 rows affected mysql> desc emp; ERROR 1146 : Table 'javaworld.emp' doesn't exist mysql> show tables; +---------------------+ | Tables_in_javaworld | +---------------------+ | emp1 | +---------------------+ 1 row in set |
DML(数据操纵语言)语句
1,插入记录
|
insert into emp(ename,hiredate,sal,deptno) values('zzzx1','2000-01-01','2000',1); Query OK, 1 row affected |
也可以不指定字段名,但values中值的顺序需和字段名排列顺序一致
|
mysql> insert into emp values('lisa','2003-02-01','3000',2); Query OK, 1 row affected |
或者只对部分字段插入值
|
insert into emp(ename,sal) values('dony','1000'); Query OK, 1 row affected |
查看表
|
mysql> select * from emp; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzzx1 | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 3000.00 | 2 | | dony | NULL | 1000.00 | NULL | +-------+------------+---------+--------+ 3 rows in set |
也可一次插入多条记录,每条记录之间用逗号分隔
|
mysql> insert into dept values(5,'dept5'),(6,'dept6'); Query OK, 2 rows affected Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 5 | dept5 | | 6 | dept6 | +--------+----------+ 2 rows in set |
2,更新记录
将emp表ename为”lisa”的sal值从3000改为4000
|
mysql> update emp set sal=4000 where ename='lisa'; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 |
也可以同时更新多个表中的数据,
如:同时更新emp表的字段sal和dept表的字段deptname
mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| lisa | 2003-02-01 | 200.00 | 2 |
| bjguan | 2004-04-02 | 100.00 | 1 |
| dony | 2005-02-05 | 2000.00 | 4 |
+--------+------------+---------+--------+
4 rows in set
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | tech |
| 2 | sale |
| 5 | fin |
+--------+----------+
3 rows in set
mysql> update emp a, dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno;
Query OK, 3 rows affected
Rows matched: 5 Changed: 3 Warnings: 0
mysql> select * from emp;
+--------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+--------+------------+---------+--------+
| zzx | 2000-01-01 | 100.00 | 1 |
| lisa | 2003-02-01 | 400.00 | 2 |
| bjguan | 2004-04-02 | 100.00 | 1 |
| dony | 2005-02-05 | 2000.00 | 4 |
+--------+------------+---------+--------+
4 rows in set
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 1 | zzx |
| 2 | lisa |
| 5 | fin |
+--------+----------+
3,删除记录
不加where条件会将表中所有记录删除!
|
mysql> delete from emp where ename='dony'; Query OK, 1 row affected |
也可一次删除多条记录,如果from后面的表名用别名,则delete后面也要用相应的别名
同时将emp表和dept表中deptno为3的记录删除
|
mysql> select * from emp; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 100.00 | 1 | | lisa | 2003-02-01 | 400.00 | 2 | | bjguan | 2004-04-02 | 100.00 | 1 | | bzshen | 2005-04-01 | 300.00 | 3 | | dony | 2005-02-05 | 2000.00 | 4 | +--------+------------+---------+--------+ 5 rows in set mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | zzx | | 2 | lisa | | 5 | fin | | 3 | hr | +--------+----------+ 4 rows in set mysql> delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3; Query OK, 2 rows affected mysql> select * from emp; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 100.00 | 1 | | lisa | 2003-02-01 | 400.00 | 2 | | bjguan | 2004-04-02 | 100.00 | 1 | | dony | 2005-02-05 | 2000.00 | 4 | +--------+------------+---------+--------+ 4 rows in set mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | zzx | | 2 | lisa | | 5 | fin | +--------+----------+ 3 rows in set |
4,查询记录
查询指定字段记录(所有记录字段名用*)
|
mysql> select ename,hiredate,sal,deptno from emp; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 100.00 | 1 | | lisa | 2003-02-01 | 400.00 | 2 | | bjguan | 2004-04-02 | 100.00 | 1 | | dony | 2005-02-05 | 2000.00 | 4 | +--------+------------+---------+--------+ 4 rows in set |
<1>查询不重复记录
|
mysql> select distinct deptno from emp; +--------+ | deptno | +--------+ | 1 | | 2 | | 4 | +--------+ 3 rows in set |
<2>条件查询,where后面还可以使用>,<,>=,<=,!=等比较运算符,使用or,and多条件查询
如:查询deptno为1的记录
|
mysql> select * from emp where deptno=1; +--------+------------+--------+--------+ | ename | hiredate | sal | deptno | +--------+------------+--------+--------+ | zzx | 2000-01-01 | 100.00 | 1 | | bjguan | 2004-04-02 | 300.00 | 1 | +--------+------------+--------+--------+ 2 rows in set mysql> select * from emp where deptno=1 and sal<300 ; +-------+------------+--------+--------+ | ename | hiredate | sal | deptno | +-------+------------+--------+--------+ | zzx | 2000-01-01 | 100.00 | 1 | +-------+------------+--------+--------+ 1 row in set |
<3>排序和限制
desc降序,asc升序,默认为升序,order by后面可以有多个不同的排序字段,如果排序字段值一样,则按照第二个排序字段进行排序...
|
mysql> select * from emp order by sal; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 2000.00 | 1 | | bzshen | 2005-02-05 | 3000.00 | 3 | | lisa | 2003-02-01 | 4000.00 | 2 | | bjguan | 2004-04-02 | 5000.00 | 1 | +--------+------------+---------+--------+ 4 rows in set |
如果只有一个排序字段,则字段相同的记录将会无序排列
|
mysql> select * from emp order by deptno; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 2000.00 | 1 | | bjguan | 2004-04-02 | 5000.00 | 1 | | lisa | 2003-02-01 | 4000.00 | 2 | | bzshen | 2005-02-05 | 3000.00 | 3 | +--------+------------+---------+--------+ 4 rows in set |
先将deptno升序排列,再将sal降序排列
|
mysql> select * from emp order by deptno,sal desc; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | bjguan | 2004-04-02 | 5000.00 | 1 | | zzx | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 4000.00 | 2 | | bzshen | 2005-02-05 | 3000.00 | 3 | +--------+------------+---------+--------+ 4 rows in set |
对于排序后的字段,可使用limit显示一部分(limit在其它数据库不能用)
显示前3条记录
|
mysql> select * from emp order by sal limit 3; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 2000.00 | 1 | | bzshen | 2005-02-05 | 3000.00 | 3 | | lisa | 2003-02-01 | 4000.00 | 2 | +--------+------------+---------+--------+ 3 rows in set |
从第2条记录开始,显示3条记录(起始偏移量为0)
|
mysql> select * from emp order by sal limit 1,3; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | bzshen | 2005-02-05 | 3000.00 | 3 | | lisa | 2003-02-01 | 4000.00 | 2 | | bjguan | 2004-04-02 | 5000.00 | 1 | +--------+------------+---------+--------+ 3 rows in set |
<4>聚合
常用的聚合函数有sum求和,count(*)记录数,max最大值,min最小值
从emp表统计人数
|
mysql> select count(1) from emp; +----------+ | count(1) | +----------+ | 4 | +----------+ 1 row in set |
统计各部门(deptno)人数,group by表示要进行分类聚合的字段
|
mysql> select deptno,count(1) from emp group by deptno; +--------+----------+ | deptno | count(1) | +--------+----------+ | 1 | 2 | | 2 | 1 | | 3 | 1 | +--------+----------+ 3 rows in set |
统计各部门(deptno)人数,并统计总人数,with rollup表示对分类聚合后的结果进行再汇总
|
mysql> select deptno,count(1) from emp group by deptno with rollup; +--------+----------+ | deptno | count(1) | +--------+----------+ | 1 | 2 | | 2 | 1 | | 3 | 1 | | NULL | 4 | +--------+----------+ 4 rows in set |
统计人数大于1的部门,having表示对分类后的结果再进行条件过滤
|
mysql> select deptno,count(1) from emp group by deptno having count(1)>1; +--------+----------+ | deptno | count(1) | +--------+----------+ | 1 | 2 | +--------+----------+ 1 row in set |
统计员工薪水sal总额,最高,最低薪水
|
mysql> select * from emp; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 4000.00 | 2 | | bjguan | 2004-04-02 | 5000.00 | 1 | | bzshen | 2005-02-05 | 3000.00 | 3 | +--------+------------+---------+--------+ 4 rows in set mysql> select sum(sal),max(sal),min(sal) from emp; +----------+----------+----------+ | sum(sal) | max(sal) | min(sal) | +----------+----------+----------+ | 14000.00 | 5000.00 | 2000.00 | +----------+----------+----------+ 1 row in set |
<5>表连接
当同时需要显示多个表中的字段时,就需要用到表连接,
表连接分内连接和外连接,主要区别是,内连接仅选出两张表中互相匹配的记录,而外连接会选车其它不匹配的记录
如:查询雇员名字和所在部门,
|
mysql> select * from emp; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 4000.00 | 2 | | bjguan | 2004-04-02 | 5000.00 | 1 | | bzshen | 2005-02-05 | 3000.00 | 3 | +--------+------------+---------+--------+ 4 rows in set mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | tech | | 2 | sale | | 3 | hr | +--------+----------+ 3 rows in set mysql> select ename,deptname from emp,dept where emp.deptno=dept.deptno; +--------+----------+ | ename | deptname | +--------+----------+ | zzx | tech | | lisa | sale | | bjguan | tech | | bzshen | hr | +--------+----------+ 4 rows in set |
外连接分为左连接和右连接
左连接:包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
右连接:包含所有的右边表中的记录甚至是左右边中没有和它匹配的记录
如:查看emp中所有用户名和所在部门名称(左连接)
|
mysql> select * from emp; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 4000.00 | 2 | | bjguan | 2004-04-02 | 5000.00 | 1 | | bzshen | 2005-02-05 | 3000.00 | 3 | | dony | 2005-04-01 | 4000.00 | 4 | +--------+------------+---------+--------+ 5 rows in set mysql> select * from dept ; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | tech | | 2 | sale | | 3 | hr | +--------+----------+ 3 rows in set mysql> select ename,deptname from emp left join dept on emp.deptno=dept.deptno; +--------+----------+ | ename | deptname | +--------+----------+ | zzx | tech | | lisa | sale | | bjguan | tech | | bzshen | hr | | dony | NULL | +--------+----------+ 5 rows in set |
右连接:
|
mysql> select ename,deptname from dept right join emp on dept.deptno=emp.deptno; +--------+----------+ | ename | deptname | +--------+----------+ | zzx | tech | | lisa | sale | | bjguan | tech | | bzshen | hr | | dony | NULL | +--------+----------+ 5 rows in set |
<6>子查询
某些情况下,当进行查询的时候,需要的条件是另外一个select语句的结果,这就需要子查询,
关键字有in,not in,=,!=,exists,not exists等
如:从emp表中查询出所有部门在dept表中的所有记录
|
mysql> select * from emp; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 4000.00 | 2 | | bjguan | 2004-04-02 | 5000.00 | 1 | | bzshen | 2005-02-05 | 3000.00 | 3 | | dony | 2005-04-01 | 4000.00 | 4 | +--------+------------+---------+--------+ 5 rows in set mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | tech | | 2 | sale | | 3 | hr | +--------+----------+ 3 rows in set mysql> select * from emp where deptno in(select deptno from dept); +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 4000.00 | 2 | | bjguan | 2004-04-02 | 5000.00 | 1 | | bzshen | 2005-02-05 | 3000.00 | 3 | +--------+------------+---------+--------+ 4 rows in set |
<7>记录联合
将两个表的数据按一个查询条件查询出来后,将结果合并到一起,用union和union all实现
两者区别是,union all是把结果集直接合并在一起,而union是将union all后的结果进行一次distinct,去重复记录.
如:将emp表和dept表中的部门编号的集合显示出来
|
mysql> select * from emp;select * from dept; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 4000.00 | 2 | | bjguan | 2004-04-02 | 5000.00 | 1 | | bzshen | 2005-02-05 | 3000.00 | 3 | | dony | 2005-04-01 | 4000.00 | 4 | +--------+------------+---------+--------+ 5 rows in set +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | tech | | 2 | sale | | 3 | hr | +--------+----------+ 3 rows in set mysql> select deptno from emp -> union all -> select deptno from dept; +--------+ | deptno | +--------+ | 1 | | 2 | | 1 | | 3 | | 4 | | 1 | | 2 | | 3 | +--------+ 8 rows in set |
union去重复记录
|
mysql> select deptno from emp -> union -> select deptno from dept; +--------+ | deptno | +--------+ | 1 | | 2 | | 3 | | 4 | +--------+ 4 rows in set |
