MySql使用入门 (二)

DML 语句

DML 操作是对数据库中表记录的操作.

1. 插入记录
   INSERT INTO tablename(filed1.filed2,filed3,…,…filedn) VALUES (value1,value2,value3,…,valuen);

两种方式如下:

mysql> insert into emp(ename, hiredate, sal,deptno) values ('zzx1','2000-01-01','2000',1);
Query OK, 1 row affected (0.07 sec)

mysql> insert into emp values('lisa','2003-02-01','3000',2);
Query OK, 1 row affected (0.03 sec)

mysql> insert into emp (ename, sal) values ('dony',1000);
Query OK, 1 row affected (0.06 sec)

mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| zzx1  | 2000-01-01 | 2000.00 |      1 |
| lisa  | 2003-02-01 | 3000.00 |      2 |
| dony  | NULL       | 1000.00 |   NULL |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)

2. 更新记录
   UPDATE tablename SET filed1=value1,filed2=value2,…filedn=valuen [WHERE CONDITION]

mysql> update emp set sal=4000  where ename='lisa';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

update 命令同时更新多个表中数据,语法如下:
UPDATE t1,t2,…tn set t1,filed1=expr,tn,filedn=exprn [WHERE CONDITION]

mysql> select * from  emp;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| zzx1  | 2000-01-01 | 2000.00 |      1 |
| lisa  | 2003-02-01 | 4000.00 |      2 |
| dony  | 2005-02-05 | 1000.00 |      4 |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)

mysql> select * from  dept;                                                             +--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | tech     |
|      2 | sale     |
|      5 | fin      |
+--------+----------+
3 rows in set (0.01 sec)

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 (0.03 sec)
Rows matched: 4  Changed: 3  Warnings: 0

mysql> select * from  emp;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| zzx1  | 2000-01-01 | 2000.00 |      1 |
| lisa  | 2003-02-01 | 8000.00 |      2 |
| dony  | 2005-02-05 | 1000.00 |      4 |
+-------+------------+---------+--------+
3 rows in set (0.01 sec)

3. 删除记录

DELETE FROM tablename [WHERE CONDITION]

mysql> delete from emp where ename='dony';
Query OK,1 row affected(0.0sec)

也可以删除多个表的数据
DELETE t1,t2,…,tn FROM t1,t2,…,tn [WHERE CONDITION]

mysql> select * from emp;
+--------+------------+---------+--------+
| ename  | hiredate   | sal     | deptno |
+--------+------------+---------+--------+
| zzx1   | 2000-01-01 | 2000.00 |      1 |
| lisa   | 2003-02-01 | 8000.00 |      2 |
| dony   | 2005-02-05 | 1000.00 |      4 |
| bzshen | 2005-04-01 |  300.00 |      3 |
+--------+------------+---------+--------+
4 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | zzx1     |
|      2 | lisa     |
|      5 | fin      |
|      3 | hr       |
+--------+----------+
4 rows in set (0.00 sec)

mysql> delete a,b from emp a,dept b where a.deptno=b.deptno and a.deptno=3;
Query OK, 2 rows affected (0.05 sec)

mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| zzx1  | 2000-01-01 | 2000.00 |      1 |
| lisa  | 2003-02-01 | 8000.00 |      2 |
| dony  | 2005-02-05 | 1000.00 |      4 |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)

mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
|      1 | zzx1     |
|      2 | lisa     |
|      5 | fin      |
+--------+----------+
3 rows in set (0.00 sec)

4. 查询记录

SELECT FROM tablename [WHERE CONDITION]

其中 “*” 是将所有记录都显示出来
(1) 查询不重复记录
使用 distinct 关键字来实现

mysql> select distinct deptno from emp;

(2) 排序和限制

使用order by来实现

mysql> select * from emp order by sal;
mysql> select * from emp order by sal,deptno desc;

desc 是降序排列

limit的用法
SELECT … [LIMIT offset_start,row_count]
其中 offset_start表示记录的起始偏移量 ,row_count 表示显示的行数.默认情况下,起始偏移量为0
例如,显示emp表中按照sal排序后的前3条记录:

mysql> select * from emp order by sal limit 2;
+-------+------------+---------+--------+
| ename | hiredate   | sal     | deptno |
+-------+------------+---------+--------+
| dony  | 2005-02-05 | 1000.00 |      4 |
| zzx1  | 2000-01-01 | 2000.00 |      1 |
+-------+------------+---------+--------+
2 rows in set (0.00 sec)

(4) 聚合

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。
WITH ROLLUP是可选语法，表明是否对分类聚合后的结果进行再汇总。
HAVING 关键字表示对分类后的结果再进行条件过滤。

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

 select deptno,count(1) from emp group by depnto having count(1)>1

DCL 语句

DCL 语句主要是DBA用来管理系统中的对象权限时使用，一般的开发人员很少使用。

mysql> grant select ,insert on sakila.* to 'z1'@'localhost' identified by '123';
Query OK, 0 rows affected(0.00 sec)

如果将z1的权限变更，收回INSERT，只对数据进行SELECT操作：

mysql> revoke insert on sakila.* from 'z1'@'localhost';
Query OK, 0 rows affected(0.00 sec)

grant和revoke分别授出和收回权限

帮助的使用

mysql> ? contents             //"? contents"命令用来显示所有可查询的分类
mysql> ？ data types  			//看看MySQL中都支持哪些数据类型
mysql> ？ int 					//进一步查看int类型的具体介绍

快熟查阅帮助

mysql> ? show
mysql> ？ create table //查看CREATE TABLE语法