mysql 字段权限控制_mysql 学习----->查询，权限，字段控制

1.统计列分类数目

mysql> selectcount(1)fromtest;

+----------+

| count(1) |

+----------+

|        9 |

+----------+

2.分组统计

mysql> selectename,count(1)fromtestgroupbyename;

+----------+----------+

| ename    | count(1) |

+----------+----------+

| aaaaa    |        3 |

| ccccc    |        1 |

| def      |        1 |

| newName2 |        1 |

| newName3 |        1 |

| newNane1 |        1 |

| ttttt    |        1 |

+----------+----------+

3.在分组的统计数据的基础上再进行相同的统计

mysql> selectename,count(1)fromtestgroupbyenamewithrollup;

+----------+----------+

| ename    | count(1) |

+----------+----------+

| aaaaa    |        3 |

| ccccc    |        1 |

| def      |        1 |

| newName2 |        1 |

| newName3 |        1 |

| newNane1 |        1 |

| ttttt    |        1 |

| NULL|        9 |

+----------+----------+

4.按分组统计的结果计数

mysql> selectename,count(1)fromtestgroupbyenamehavingcount(1) > 1;

+-------+----------+

| ename | count(1) |

+-------+----------+

| aaaaa |        3 |

+-------+----------+

5.统计总数量、最大值、最小值

mysql> selectsum(age),max(age),min(age)fromtest;

+----------+----------+----------+

| sum(age) |max(age) |min(age) |

+----------+----------+----------+

|      112 |       81 |        1 |

+----------+----------+----------+

6.左右连接

6.1插入数据：

mysql> createtableemp( enamevarchar(20), hiredatedate, salint(11), deptnoint(11) );

mysql> insertintoemp(ename,hiredate,sal,deptno)values('zzx','2000-01-01',2000,1), ('lisa','2003-02-01',4000,2), ('bjguan','

2004-04-02',5000,1), ('bzshen','2005-04-01',4000,3);

mysql> select*fromemp;

+--------+------------+------+--------+

| ename  | hiredate   | sal  | deptno |

+--------+------------+------+--------+

| zzx    | 2000-01-01 | 2000 |      1 |

| lisa   | 2003-02-01 | 4000 |      2 |

| bjguan | 2004-04-02 | 5000 |      1 |

| bzshen | 2005-04-01 | 4000 |      3 |

+--------+------------+------+--------+

mysql> createtabledept ( deptnoint(11), deptnamevarchar(20) );

mysql> insertintodept(deptno,deptname)values(1,'tech'), (2,'sale'), (3,'hr');

mysql> select*fromdept;

+--------+----------+

| deptno | deptname |

+--------+----------+

|      1 | tech     |

|      2 | sale     |

|      3 | hr       |

+--------+----------+

6.2编写左右链接SQl语句：

mysql> selectename,deptnamefromemp,deptwhereemp.deptno = dept.deptno;

+--------+----------+

| ename  | deptname |

+--------+----------+

| zzx    | tech     |

| lisa   | sale     |

| bjguan | tech     |

| bzshen | hr       |

+--------+----------+

mysql> selectename,deptnamefromempleftjoindeptonemp.deptno = dept.deptno;

+--------+----------+

| ename  | deptname |

+--------+----------+

| zzx    | tech     |

| lisa   | sale     |

| bjguan | tech     |

| bzshen | hr       |

+--------+----------+

mysql> select*fromemp;

+--------+------------+------+--------+

| ename  | hiredate   | sal  | deptno |

+--------+------------+------+--------+

| zzx    | 2000-01-01 | 2000 |      1 |

| lisa   | 2003-02-01 | 4000 |      2 |

| bjguan | 2004-04-02 | 5000 |      1 |

| bzshen | 2005-04-01 | 4000 |      3 |

+--------+------------+------+--------+

mysql> insertintoemp (ename)values('dony');

mysql> selectename,deptnamefromempleftjoindeptonemp.deptno = dept.deptno;

+--------+----------+

| ename  | deptname |

+--------+----------+

| zzx    | tech     |

| lisa   | sale     |

| bjguan | tech     |

| bzshen | hr       |

| dony   | NULL|

+--------+----------+

mysql> select*fromemp;

+--------+------------+------+--------+

| ename  | hiredate   | sal  | deptno |

+--------+------------+------+--------+

| zzx    | 2000-01-01 | 2000 |      1 |

| lisa   | 2003-02-01 | 4000 |      2 |

| bjguan | 2004-04-02 | 5000 |      1 |

| bzshen | 2005-04-01 | 4000 |      3 |

| dony   | NULL|NULL|NULL|

+--------+------------+------+--------+

mysql> selectename,deptnamefromemprightjoindeptonemp.deptno = dept.deptno;

+--------+----------+

| ename  | deptname |

+--------+----------+

| zzx    | tech     |

| bjguan | tech     |

| lisa   | sale     |

| bzshen | hr       |

+--------+----------+

mysql> selectename,deptnamefromemprightjoindeptondept.deptno=emp.deptno;

+--------+----------+

| ename  | deptname |

+--------+----------+

| zzx    | tech     |

| bjguan | tech     |

| lisa   | sale     |

| bzshen | hr       |

+--------+----------+

mysql> select*fromempwheredeptnoin(selectdeptnofromdept);

+--------+------------+------+--------+

| ename  | hiredate   | sal  | deptno |

+--------+------------+------+--------+

| zzx    | 2000-01-01 | 2000 |      1 |

| lisa   | 2003-02-01 | 4000 |      2 |

| bjguan | 2004-04-02 | 5000 |      1 |

| bzshen | 2005-04-01 | 4000 |      3 |

+--------+------------+------+--------+

7.几种嵌套查询

mysql> select*fromempwheredeptno = (selectdeptnofromdept limit 1);

+--------+------------+------+--------+

| ename  | hiredate   | sal  | deptno |

+--------+------------+------+--------+

| zzx    | 2000-01-01 | 2000 |      1 |

| bjguan | 2004-04-02 | 5000 |      1 |

+--------+------------+------+--------+

mysql> select*fromempwheredeptnoin(selectdeptnofromdept );

+--------+------------+------+--------+

| ename  | hiredate   | sal  | deptno |

+--------+------------+------+--------+

| zzx    | 2000-01-01 | 2000 |      1 |

| lisa   | 2003-02-01 | 4000 |      2 |

| bjguan | 2004-04-02 | 5000 |      1 |

| bzshen | 2005-04-01 | 4000 |      3 |

+--------+------------+------+--------+

4 rowsinset(0.00 sec)

mysql> selectemp.*fromemp ,deptwhereemp.deptno = dept.deptno;

+--------+------------+------+--------+

| ename  | hiredate   | sal  | deptno |

+--------+------------+------+--------+

| zzx    | 2000-01-01 | 2000 |      1 |

| lisa   | 2003-02-01 | 4000 |      2 |

| bjguan | 2004-04-02 | 5000 |      1 |

| bzshen | 2005-04-01 | 4000 |      3 |

+--------+------------+------+--------+

8.组合查询

mysql> selectdeptnofromempunionselectdeptnofromdept;

+--------+

| deptno |

+--------+

|      1 |

|      2 |

|      3 |

|   NULL|

+--------+

mysql> selectdeptnofromempunionallselectdeptnofromdept;

+--------+

| deptno |

+--------+

|      1 |

|      2 |

|      1 |

|      3 |

|   NULL|

|      1 |

|      2 |

|      3 |

+--------+

mysql> selectdeptnofromempunionselectdeptnofromdept;

+--------+

| deptno |

+--------+

|      1 |

|      2 |

|      3 |

|   NULL|

+--------+

9.权限设置举例

mysql> grantselect,updateontest1.*toz1@localhost identifiedby'test';

mysql> use test1;

Reading tableinformationforcompletionoftableandcolumnnames

You can turn offthis featuretoget a quicker startupwith-A

mysql> insertintoemp(sal,deptno)values('2000',3);

ERROR 1142 (42000): INSERTcommand deniedtouser'z1'@'localhost'fortable'emp'.

10.内置帮助的使用

mysql> ? contents

You asked forhelp about help category:"Contents"

Formore information, type'help ',whereisoneofthe following

categories:

Account Management

Administration

Compound Statements

Data Definition

Data Manipulation

Data Types

Functions

Functions andModifiersforUsewithGROUPBY

Geographic Features

Help Metadata

Language Structure

Plugins

Procedures

Storage Engines

TableMaintenance

Transactions

User-Defined Functions

Utility

mysql> ? data types

You asked forhelp about help category:"Data Types"

Formore information, type'help ',whereisoneofthe following

topics:

AUTO_INCREMENT

BIGINT

BINARY

BIT

BLOB

BLOB DATA TYPE

BOOLEAN

CHAR

CHARBYTE

DATE

DATETIME

DEC

DECIMAL

DOUBLE

DOUBLEPRECISION

ENUM

FLOAT

INT

INTEGER

LONGBLOB

LONGTEXT

MEDIUMBLOB

MEDIUMINT

MEDIUMTEXT

SETDATA TYPE

SMALLINT

TEXT

TIME

TIMESTAMP

TINYBLOB

TINYINT

TINYTEXT

VARBINARY

VARCHAR

YEARDATA TYPE

11.通过操作此表可以实现迅速修改表名等操作。

mysql> select*frominformation_schema.tables;

12.字段的长度测试

mysql> createtablet1(id1int,id2int(5));

mysql> desct1;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null|Key|Default| Extra |

+-------+---------+------+-----+---------+-------+

| id1   | int(11) | YES  |     |NULL|       |

| id2   | int(5)  | YES  |     |NULL|       |

+-------+---------+------+-----+---------+-------+

mysql> insertintot1values(1,1);

mysql> select*fromt1;

+------+------+

| id1  | id2  |

+------+------+

|    1 |    1 |

+------+------+

mysql> altertablet1modifyid1intzerofill;

mysql> altertablet1modifyid2int(5) zerofill;

mysql> insertintot1values(1,1111111);

mysql> select*fromt1;

+------------+---------+

| id1        | id2     |

+------------+---------+

| 0000000001 |   00001 |

| 0000000001 | 1111111 |

+------------+---------+