mysql 学习记录（二）--查询，权限，字段控制

本文链接：https://blog.csdn.net/bwshqh/article/details/48847475
1.统计列分类数目
mysql> select count(1) from test;
+----------+
| count(1) |
+----------+
|        9 |
+----------+
1 row in set (0.02 sec)

2.分组统计
mysql> select ename,count(1) from test group by ename;
+----------+----------+
| ename    | count(1) |
+----------+----------+
| aaaaa    |        3 |
| ccccc    |        1 |
| def      |        1 |
| newName2 |        1 |
| newName3 |        1 |
| newNane1 |        1 |
| ttttt    |        1 |
+----------+----------+
7 rows in set (0.00 sec)

3.在分组的统计数据的基础上再进行相同的统计
mysql> select ename,count(1) from test group by ename with rollup;
+----------+----------+
| ename    | count(1) |
+----------+----------+
| aaaaa    |        3 |
| ccccc    |        1 |
| def      |        1 |
| newName2 |        1 |
| newName3 |        1 |
| newNane1 |        1 |
| ttttt    |        1 |
| NULL     |        9 |
+----------+----------+
8 rows in set (0.00 sec)

4.按分组统计的结果计数
mysql> select ename,count(1) from test group by ename having count(1) > 1;
+-------+----------+
| ename | count(1) |
+-------+----------+
| aaaaa |        3 |
+-------+----------+
1 row in set (0.00 sec)

5.统计总数量、最大值、最小值
mysql> select sum(age),max(age),min(age) from test;
+----------+----------+----------+
| sum(age) | max(age) | min(age) |
+----------+----------+----------+
|      112 |       81 |        1 |
+----------+----------+----------+
1 row in set (0.00 sec)

6.左右连接
mysql> create table emp( ename varchar(20), hiredate date, sal int(11), deptno int(11) );
Query OK, 0 rows affected (0.04 sec)

mysql> insert into emp(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);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from emp;
+--------+------------+------+--------+
| 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 rows in set (0.00 sec)

mysql> create table  dept ( deptno int(11), deptname varchar(20) );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into dept(deptno,deptname) values (1,'tech'), (2,'sale'), (3,'hr');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

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 (0.00 sec)

mysql> select ename,deptname from emp
    -> left join dept on emp.deptno = dept.deptno;
+--------+----------+
| ename  | deptname |
+--------+----------+
| zzx    | tech     |
| lisa   | sale     |
| bjguan | tech     |
| bzshen | hr       |
+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from emp;
+--------+------------+------+--------+
| 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 rows in set (0.00 sec)

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

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 (0.00 sec)

mysql> select * from emp;
+--------+------------+------+--------+
| 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 |
+--------+------------+------+--------+
5 rows in set (0.00 sec)

mysql> select ename,deptname from emp right join dept on emp.deptno = dept.deptno;
+--------+----------+
| ename  | deptname |
+--------+----------+
| zzx    | tech     |
| bjguan | tech     |
| lisa   | sale     |
| bzshen | hr       |
+--------+----------+
4 rows in set (0.00 sec)

mysql> select ename,deptname from emp right join dept on dept.deptno=emp.deptno;
+--------+----------+
| ename  | deptname |
+--------+----------+
| zzx    | tech     |
| bjguan | tech     |
| lisa   | sale     |
| bzshen | hr       |
+--------+----------+
4 rows in set (0.00 sec)

mysql> select * from emp where deptno in ( select deptno from dept);
+--------+------------+------+--------+
| 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 rows in set (0.00 sec)

7.几种嵌套查询
mysql> select * from emp where deptno = ( select deptno from dept limit 1);
+--------+------------+------+--------+
| ename  | hiredate   | sal  | deptno |
+--------+------------+------+--------+
| zzx    | 2000-01-01 | 2000 |      1 |
| bjguan | 2004-04-02 | 5000 |      1 |
+--------+------------+------+--------+
2 rows in set (0.00 sec)

mysql> select * from emp where deptno in ( select deptno from dept );
+--------+------------+------+--------+
| 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 rows in set (0.00 sec)

mysql> select emp.* from emp ,dept where emp.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 |
+--------+------------+------+--------+
4 rows in set (0.00 sec)

8.组合查询
mysql> select deptno from emp union  select deptno from dept;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
|      3 |
|   NULL |
+--------+
4 rows in set (0.00 sec)

mysql> select deptno from emp union all   select deptno from dept;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
|      1 |
|      3 |
|   NULL |
|      1 |
|      2 |
|      3 |
+--------+
8 rows in set (0.00 sec)

mysql> select deptno from emp union  select deptno from dept;
+--------+
| deptno |
+--------+
|      1 |
|      2 |
|      3 |
|   NULL |
+--------+
4 rows in set (0.00 sec)

9.权限设置举例
mysql> grant select,update on test1.* to z1@localhost identified by 'test';
Query OK, 0 rows affected (0.00 sec)

[root@ ~]# mysql -uz1 -ptest
mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> insert into emp(sal,deptno) values('2000',3);
ERROR 1142 (42000): INSERT command denied to user 'z1'@'localhost' for table 'emp'.

10.内置帮助的使用
mysql> ? contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

mysql> ? data types
You asked for help about help category: "Data Types"
For more information, type 'help <item>', where <item> is one of the following
topics:
   AUTO_INCREMENT
   BIGINT
   BINARY
   BIT
   BLOB
   BLOB DATA TYPE
   BOOLEAN
   CHAR
   CHAR BYTE
   DATE
   DATETIME
   DEC
   DECIMAL
   DOUBLE
   DOUBLE PRECISION
   ENUM
   FLOAT
   INT
   INTEGER
   LONGBLOB
   LONGTEXT
   MEDIUMBLOB
   MEDIUMINT
   MEDIUMTEXT
   SET DATA TYPE
   SMALLINT
   TEXT
   TIME
   TIMESTAMP
   TINYBLOB
   TINYINT
   TINYTEXT
   VARBINARY
   VARCHAR
   YEAR DATA TYPE

11.通过操作此表可以实现迅速修改表名等操作。
mysql> select * from information_schema.tables;

12.字段的长度测试
mysql> create table t1(id1 int,id2 int(5));
Query OK, 0 rows affected (0.06 sec)

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1   | int(11) | YES  |     | NULL    |       |
| id2   | int(5)  | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+

mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+------+
| id1  | id2  |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> alter table t1 modify id1 int zerofill;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table t1 modify id2 int(5) zerofill;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t1 values(1,1111111);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------------+---------+
| id1        | id2     |
+------------+---------+
| 0000000001 |   00001 |
| 0000000001 | 1111111 |
+------------+---------+
2 rows in set (0.00 sec)