0 目录
在mysql上演示各常用sql语句。
1 安装与内置命令
先安装mysql,CentOS 6安装mysql-server,CentOS 7安装mariadb-server。之后就可通过mysql指令连接了。
1.1 安全安装
mysql中默认有一个叫做mysql的库用于存储mysql元数据。mysql库中有一个叫做user的表,记录了用户信息。在user表中可查询用户名、密码、主机:
mysql> USE mysql
……
mysql> SELECT user,host,password FROM user;
+------+-----------+----------+
| user | host | password |
+------+-----------+----------+
| root | localhost | |
| root | node1 | |
| root | 127.0.0.1 | |
| | localhost | |
| | node1 | |
+------+-----------+----------+
5 rows in set (0.00 sec)
可以看到默认情况下root没有设置密码,且存在用户名为空的用户,不安全。
可运行指令mysql_secure_installation(安装mysql自带的):
[root@node1 ~]% mysql_secure_installation
Set root password? [Y/n]
Remove anonymous users? [Y/n]
Disallow root login remotely?
……
它会依次询问是否设置root密码、是否删除空名用户、是否禁止管理员从远程主机登录等。
这样再次登录时就需要填写用户(-u指定)、从哪个主机登录(-h指定)、密码(-p指定)了。
1.2 内置命令
就是mysql客户端自带的命令,而非sql语句,只是用于便捷操作。可直接使用help查看。
常用的有:
命令 | 意义 |
---|---|
\s | 查看mysql服务端状态 |
\G | 竖排显示操作结果。因为有的表格竖排显示更清晰 |
\c | 取消当前行的命令。相当于shell的”ctrl c” |
很简单不赘述了。
下面说明sql语句:
2 表、索引、视图的定义语句
指令很简单,关键在于内容。
- | 创建 | 删除 | 修改 |
---|---|---|---|
表 | CREATE TABLE | DROP TABLE | ALTER TABLE |
索引 | CREATE INDEX | DROP INDEX | - |
视图 | CREATE VIEW | DROP VIEW | - |
索引和视图的修改,只能删除然后重新创建。
2.1 表的创建、修改、删除
2.1.1 数据类型
表的创建主要是定义其中的字段,各字段要指定其内容为何种数据类型,如有需要还可使用修饰符额外限定一些条件。
常用数据类型:
数值型
类型 意义 INT 整型(4字节) SMALLINT 短整型(2字节) FLOAT 单精度实型 DOUBLE 双精度实型 NUMERIC(p,d) 定点数。表示共p位数字,小数点后有d位 字符型
类型 意义 CHAR(n) 最大长度为n的定长字符型,不区分大小写 VARCHAR(n) 最大长度为n的变长字符型,不区分大小写 BINARY(n) 最大长度为n的定长字符型,区分大小写 VARBINARY(n) 最大长度为n的变长字符型,区分大小写 日期型
类型 意义 DATE 日期YYYY-MM-DD TIME 时间HH.MM.SS DATETIME 既有日期又有时间 枚举型
类型 意义 ENUM(a,b,c……) 取值必须是枚举元素中的一个
2.1.2 数据约束条件
常用约束:
关键字 | 意义 |
---|---|
UNSIGNED | 指定字段使用的的数值类型为非负 |
NOT NULL | 指定字段不能为空 |
DEFAULT value | 指定字段的默认值为value。表示不定义该字段内容时,就取值value |
PRIMARY KEY | 指定字段为主键 |
UNIQUE | 指定字段值唯一 |
CHECK | 指定字段需满足指定条件。比如”CHECK (指定字段 IN (a,b))” |
FOEREIGN KEY | 外键。参考其他表的主键使用关键字REFERENCES |
这些约束条件可直接跟在定义的字段后(列级约束),也可定义在表内容的最后(表级约束)。比如主键是多个列时、定义外键时,就只能定义在表的最后
2.1.3 创建表
自建库study,并切换至该库:
mysql> CREATE DATABASE study;
Query OK, 1 row affected (0.00 sec)
mysql> USE study;
Database changed
1、创建表course。字段:课程号Cno(主键)、课程名Cname、学分Cpno(不能为空)
mysql> CREATE TABLE course
-> ( Cno SMALLINT PRIMARY KEY,
-> Cname CHAR(16),
-> Cpno SMALLINT NOT NULL
-> );
Query OK, 0 rows affected (0.01 sec)
DESC查看表结构:
mysql> DESC course;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Cno | smallint(6) | NO | PRI | NULL | |
| Cname | char(16) | YES | | NULL | |
| Cpno | smallint(6) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
2、创建表student。字段:学号Sno(主键);名字Sname(唯一);年龄Age;课程号Cno(外键,参考course的Cno)
mysql> CREATE TABLE student
-> ( Sno INT PRIMARY KEY,
-> Sname CHAR(30) UNIQUE,
-> Age SMALLINT,
-> Cno SMALLINT ,
-> FOREIGN KEY (Cno) REFERENCES course(Cno)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> DESC student ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno | int(11) | NO | PRI | NULL | |
| Sname | char(30) | YES | UNI | NULL | |
| Age | smallint(6) | YES | | NULL | |
| Cno | smallint(6) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
2.1.4 修改表
修改表可以是添加列、修改指定列名和数据类型、删除指定列的约束条件等。
以上述的student表为例。
2.1.4.1 添加字段
在Age字段后添加列课程名:
mysql> ALTER TABLE student
-> ADD Cname CHAR(16) AFTER Age;
mysql> DESC student
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno | int(11) | NO | PRI | NULL | |
| Sname | char(30) | YES | UNI | NULL | |
| Age | smallint(6) | YES | | NULL | |
| Cname | char(16) | YES | | NULL | |
| Cno | smallint(6) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2.1.4.2 修改字段
修改字段Sname名称为Name,数据类型为CHAR(32),且不得为空:
mysql> ALTER TABLE student CHANGE Sname Name CHAR(32) NOT NULL;
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno | int(11) | NO | PRI | NULL | |
| Name | char(32) | NO | UNI | NULL | |
| Age | smallint(6) | YES | | NULL | |
| Cname | char(16) | YES | | NULL | |
| Cno | smallint(6) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
如果只是修改属性而不改字段名的话,使用MODIFY即可。
2.1.4.3 修改字段的约束
删除指定列的约束条件。比如删除Name的非空约束、unique约束:
mysql> ALTER TABLE student
-> MODIFY Name CHAR(32); # 相当于重新定义该字段
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno | int(11) | NO | PRI | NULL | |
| Name | char(32) | YES | UNI | NULL | |
| Age | smallint(6) | YES | | NULL | |
| Cname | char(16) | YES | | NULL | |
| Cno | smallint(6) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
在删除主键、外键、唯一键等的约束时,直接删除后会发现约束仍存在。这是因为在给指定字段添加键约束时,mysql会为之自动创建对应的索引。删除这些约束时,只要索引没有删除,会视为约束仍存在。所以还要先查看对应的INDEX,将其删除才有效。
比如,要删除unique等关于键的约束,可先查看此表的索引信息获得Key_name:
mysql> SHOW INDEX FROM student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| student | 0 | PRIMARY | 1 | Sno | A | 0 | NULL | NULL | | BTREE | |
| student | 0 | Name | 1 | Name | A | NULL | NULL | NULL | YES | BTREE | |
| student | 1 | Cno | 1 | Cno | A | NULL | NULL | NULL | YES | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
可看到Name对应的Key_name就是Name。删除Name的UNIQUE约束:
mysql> ALTER TABLE student
-> DROP KEY Name;
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno | int(11) | NO | PRI | NULL | |
| Name | char(32) | YES | | NULL | |
| Age | smallint(6) | YES | | NULL | |
| Cname | char(16) | YES | | NULL | |
| Cno | smallint(6) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
添加和删除主键:
ALTER TALBLE table_name
ADD PRIMARY KEY(column)
ALTER TABLE table_name
DROP PRIMARY KEY
添加、删除外键:
ALTER TABLE table_name
ADD FOREIGN KEY(column) REFERENCES object_table_name(column)
ALTER TABLE table_name
DROP FOREIGN KEY column
注意,这样删除后还要删除对应的索引,否则还是有外键约束。
2.1.5 删除表
格式简单,不赘述:
DROP TABLE table_name
注意,如果存在其他表的外键约束,引用的是当前表的主键,则必须先删除有外键约束的表(或删除其外键约束),才能删除当前表。
直观上这也容易理解,被其他表依赖的表当然不能随意删除。
2.2 创建、删除索引
数据库中的索引,记录的是指定表中各值和它们对应的存储位置。
所以创建索引有助于提高查询速度,但会降低写入速度(因为要更新对应的索引)。
索引创建后,无需用户管理,由系统自行维护。
为上述的student表的Sno和Name字段(可以为一个或多个字段创建索引),创建索引stu_index:
mysql> CREATE INDEX stu_index
-> ON student(Sno,Name);
mysql> SHOW INDEX FROM student
-> ;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| student | 0 | PRIMARY | 1 | Sno | A | 0 | NULL | NULL | | BTREE | |
| student | 1 | Cno | 1 | Cno | A | NULL | NULL | NULL | YES | BTREE | |
| student | 1 | stu_index | 1 | Sno | A | NULL | NULL | NULL | | BTREE | |
| student | 1 | stu_index | 2 | Name | A | NULL | NULL | NULL | YES | BTREE | |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
删除索引stu_index:
mysql> DROP INDEX stu_index ON student;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW INDEX FROM student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| student | 0 | PRIMARY | 1 | Sno | A | 0 | NULL | NULL | | BTREE | |
| student | 1 | Cno | 1 | Cno | A | NULL | NULL | NULL | YES | BTREE | |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2.3 创建、更新、删除视图
视图是虚表,本身不存储任何数据。作用:1、简化查询操作;2、使通过视图查询,某些敏感数据不被显示等。
创建视图:
CREATE VIEW view_name (列名1,列名2……)
AS <SELECT 子查询>
[WITH CHECK OPTION]
所以视图就是在内部已经有了一个查询,将查询结果作为数据内容显示给用户。这样可以把某些复杂的查询定义在视图中,从而简化操作。
选项WITH CHECK OPTION表示,在向视图中插入数据时,必须满足SELECT子查询中WHERE定义的条件1。
更新操作同基本表的数据的更新,删除也是。不赘述了。
不过注意,如果视图的定义中子查询使用了聚集函数(比如求平均值等),则插入数据就不能使用视图插入了(因为不可能插入一个平均值)。
下面看下视图的效果:
mysql> SELECT * FROM student;
+-----+---------+------+----------+------+-------+
| Sno | Name | Age | Cname | Cno | Score |
+-----+---------+------+----------+------+-------+
| 1 | Jerry | 10 | Chinese | 1 | 100 |
| 2 | Peter | 11 | English | 2 | 90 |
| 3 | Linda | 11 | English | 2 | 95 |
| 4 | Dick | 13 | Art | 5 | 98 |
| 5 | Rose | 12 | Music | 4 | 89 |
| 6 | Alice | 28 | Computer | 8 | 88 |
| 7 | Jim | 27 | Computer | 8 | 78 |
| 8 | Tim | 29 | Computer | 8 | 76 |
| 9 | Horry | 40 | Computer | 8 | 76 |
| 15 | Kobe | 40 | NULL | NULL | NULL |
| 16 | Iverson | 40 | NULL | NULL | NULL |
+-----+---------+------+----------+------+-------+
定义视图,使内容只有年龄小于等于29,且选了计算机课的学生的名字、年龄和课程:
mysql> CREATE VIEW test_view (Name,Age,Class)
-> AS SELECT Name,Age,Cname
-> FROM student
-> WHERE Age<=29 AND Cname='Computer'
-> WITH CHECK OPTION;
之后就可以对该视图进行查询等操作,但实际它的内容还是存在表student中:
mysql> SELECT *
-> FROM test_view;
+-------+------+----------+
| Name | Age | Class |
+-------+------+----------+
| Alice | 28 | Computer |
| Jim | 27 | Computer |
| Tim | 29 | Computer |
+-------+------+----------+
3 数据操作语句(增删改查)
3.1 插入数据
向表中插入一条记录,一般格式为:
INSERT INTO <table_name> (column1,column2...)
VALUES (value1,value2...)
比如,向上述的表student中,插入”学号为1,名字Tom,年龄10,课程名Chinese,课程号1”的记录:
mysql> INSERT student # 不写字段表示插入完整记录,如果至写部分字段,则没有写的表示插入空值
-> VALUES (1,'Tom',10,'Chinese',1);
mysql> SELECT * FROM student;
+-----+------+------+---------+------+
| Sno | Name | Age | Cname | Cno |
+-----+------+------+---------+------+
| 1 | Tom | 10 | Chinese | 1 |
+-----+------+------+---------+------+
可一次插入多行数据,逗号隔开。
3.2 修改数据
一般格式:
UPDATE table_name
SET column1=...,column2=...
WHERE condition
表示对表table_name中,满足条件condition的记录(如不指定条件则表示对所有记录)的指定字段做修改。
比如,把表student中Name字段由Tom改成Jerry:
mysql> UPDATE student
-> SET Name='Jerry'
-> WHERE Name='Tom';
mysql> SELECT * FROM student;
+-----+-------+------+---------+------+
| Sno | Name | Age | Cname | Cno |
+-----+-------+------+---------+------+
| 1 | Jerry | 10 | Chinese | 1 |
+-----+-------+------+---------+------+
不过因为此时student表仅有一个记录,所以加不加WHERE来指定都无所谓。
WHERE能够指定的条件很灵活,在标题3.4查询语句中详述。
3.3 删除数据
一般格式:
DELETE FROM table_name
WHERE condition
表示按指定条件删除记录。
如不指定条件,则删除所有记录,慎重。
3.4 查询数据
一般查询格式:
SELECT [ ALL | DISTINCT ] 字段1,字段2...
FROM table_name
[ WHERE condition ]
[ GROUP BY 指定字段 ] [ HAVING condition ]
[ ORDER BY 指定字段 [ ASC | DESC ] ]
其中:
DISTINCT,表示不显示重复的行,默认显示所有的;
GROUP BY,表示根据指定字段,对查询结果分组;
HAVING,表示如果按指定字段对查询结果分组,仅显示满足HAVING指定条件的组;
ORDER BY,表示对查询结果进行排序,ASC升序,DESC降序
下面说明常见用法:
3.4.1 查询指定字段
为验证查询效果,多插入一些字段至student和course表中(下同):
mysql> SELECT * FROM course;
+-----+---------+------+
| Cno | Cname | Cpno |
+-----+---------+------+
| 1 | Chinese | 6 |
| 2 | English | 5 |
| 3 | PE | 4 |
| 4 | Music | 3 |
| 5 | Art | 3 |
+-----+---------+------+
mysql> SELECT * FROM student;
+-----+-------+------+---------+------+
| Sno | Name | Age | Cname | Cno |
+-----+-------+------+---------+------+
| 1 | Jerry | 10 | Chinese | 1 |
| 2 | Peter | 11 | English | 2 |
| 3 | Linda | 11 | English | 2 |
| 4 | Dick | 13 | Art | 5 |
| 5 | Rose | 12 | Music | 4 |
+-----+-------+------+---------+------+
查询course表中,Cname和Cpno字段:
mysql> SELECT Cname,Cpno FROM course;
+---------+------+
| Cname | Cpno |
+---------+------+
| Chinese | 6 |
| English | 5 |
| PE | 4 |
| Music | 3 |
| Art | 3 |
+---------+------+
查询student表中,Name和Age字段,且Name做别名Sname:
mysql> SELECT Name AS Sname,Age FROM student;
+-------+------+
| Sname | Age | # 这里令Name字段显示为别名Sname
+-------+------+
| Jerry | 10 |
| Peter | 11 |
| Linda | 11 |
| Dick | 13 |
| Rose | 12 |
+-------+------+
别名只是为了增加输出结果的可读性,原表并没有任何改变。
也可做些简单运算。比如计算学生的出生年份(表student中记录的是年龄,用当前年份相减即可):
mysql> SELECT Name,2017-Age AS BirthYear FROM student;
+-------+-----------+
| Name | BirthYear |
+-------+-----------+
| Jerry | 2007 |
| Peter | 2006 |
| Linda | 2006 |
| Dick | 2004 |
| Rose | 2005 |
+-------+-----------+
3.4.2 按条件查询记录
查询条件由WHERE子句指定,常用查询条件:
查询条件 | 符号 |
---|---|
比较 | 就是数值比较。如<,>,<=,>=,=,!=等等 |
在(或不在)指定范围 | BETWEEN AND,NOT BETWEEN AND |
在(或不在)指定集合中 | IN,NOT IN |
字符匹配(支持通配) | LIKE,NOT LIKE。一般会用到通配符:”%”表示任意长度任意字符串;”_”表示任意单个字符 |
模式匹配 | RLIKE,NOT RLIKE |
空值(或非空值) | IS NULL,IS NOT NULL |
逻辑运算(与、或、非,可组合上述各条件) | AND,OR,NOT |
3.4.2.1 按比较条件
比如,查询student表中,修English课的学生的学号和姓名:
mysql> SELECT Sno,Name
-> FROM student
-> WHERE Cname='English';
+-----+-------+
| Sno | Name |
+-----+-------+
| 2 | Peter |
| 3 | Linda |
+-----+-------+
在student表中,查询年龄大于11岁的学生姓名和年龄:
mysql> SELECT Name,Age
-> FROM student
-> WHERE Age>11;
+------+------+
| Name | Age |
+------+------+
| Dick | 13 |
| Rose | 12 |
+------+------+
3.4.2.2 按范围
在student表中,查询学号在2到4之间的学生姓名和学号:
mysql> SELECT Name,Sno
-> FROM student
-> WHERE Sno BETWEEN 2 AND 4;
+-------+-----+
| Name | Sno |
+-------+-----+
| Peter | 2 |
| Linda | 3 |
| Dick | 4 |
+-------+-----+
3.4.2.3 按指定字段是否在指定集合中
在student表中,查询修English、Art、Music课程的学生姓名和对应课程名:
mysql> SELECT Name,Cname
-> FROM student
-> WHERE Cname IN ('English','Art','Music');
+-------+---------+
| Name | Cname |
+-------+---------+
| Peter | English |
| Linda | English |
| Dick | Art |
| Rose | Music |
+-------+---------+
3.4.2.4 按字符串匹配查询
按字符串查询,如果比较字符串中不含通配符,则可使用’=’或’!=’。
在student表中,查询以名字P开头的学生姓名和学号:
mysql> SELECT Name,Sno FROM student WHERE Name LIKE 'P%';
+-------+-----+
| Name | Sno |
+-------+-----+
| Peter | 2 |
+-------+-----+
注意,”%”和”_”如果要表示为符号本来的含义而非令其表示通配,则要使用转义符号。转义符号使用关键字ESCAPE定义(一般定义为”\”),在匹配条件最后指明转义符即可。
也可用模式匹配,查询名字中有字母”i”的学生的名字和学号:
mysql> SELECT Name,Sno FROM student WHERE Name RLIKE '.*i.*';
+-------+-----+
| Name | Sno |
+-------+-----+
| Linda | 3 |
| Dick | 4 |
+-------+-----+
3.4.2.5 组合各条件
可使用逻辑运算符组合各条件。
在student表中,查询没选English的,且年龄小于12的学生的名字、年龄和课程:
mysql> SELECT *
-> FROM student
-> WHERE Cname!='English' AND Age<12;
+-----+-------+------+---------+------+
| Sno | Name | Age | Cname | Cno |
+-----+-------+------+---------+------+
| 1 | Jerry | 10 | Chinese | 1 |
+-----+-------+------+---------+------+
类似这种,不赘述了。
3.4.3 查询结果排序
查询student表中所有内容,按年龄降序排序:
mysql> SELECT *
-> FROM student
-> ORDER BY Age DESC;
+-----+-------+------+---------+------+
| Sno | Name | Age | Cname | Cno |
+-----+-------+------+---------+------+
| 4 | Dick | 13 | Art | 5 |
| 5 | Rose | 12 | Music | 4 |
| 2 | Peter | 11 | English | 2 |
| 3 | Linda | 11 | English | 2 |
| 1 | Jerry | 10 | Chinese | 1 |
+-----+-------+------+---------+------+
排序依据字段也可以是多个,先根据第一字段,如果该字段有结果相同的,则再依据后面的字段排序。
比如根据年龄、学号降序排序:
mysql> SELECT * FROM student ORDER BY Age DESC,Sno DESC;
+-----+-------+------+---------+------+
| Sno | Name | Age | Cname | Cno |
+-----+-------+------+---------+------+
| 4 | Dick | 13 | Art | 5 |
| 5 | Rose | 12 | Music | 4 |
| 3 | Linda | 11 | English | 2 |
| 2 | Peter | 11 | English | 2 |
| 1 | Jerry | 10 | Chinese | 1 |
+-----+-------+------+---------+------+
3.4.4 聚集函数
可对查询结果中的数据进行统计计算。
常用函数:
函数 | 意义 |
---|---|
AVG(…) | 计算指定列的平均值 |
MIN(…) | 查询指定列的最小值 |
MAX(…) | 查询指定列的最大值 |
SUM(…) | 查询指定列各元素之和 |
COUNT(…) | 显示所查记录的个数,而非各记录内容 |
比如,在student表中计算各学生年龄的平均值:
mysql> SELECT AVG(Age) AS 'Average Age'
-> FROM student;
+-------------+
| Average Age |
+-------------+
| 11.4000 |
+-------------+
在student表,查询年龄最小的学生姓名和年龄:
mysql> SELECT Name,MIN(Age) AS 'MIN Age'
-> FROM student;
+-------+---------+
| Name | MIN Age |
+-------+---------+
| Jerry | 10 |
+-------+---------+
在course表,查询学分(Cpno)最高的课程名及学分:
mysql> SELECT Cname,MAX(Cpno)
-> FROM course;
+---------+-----------+
| Cname | MAX(Cpno) |
+---------+-----------+
| Chinese | 6 |
+---------+-----------+
在course表,查询所有学分之和:
mysql> SELECT SUM(Cpno)
-> FROM course;
+-----------+
| SUM(Cpno) |
+-----------+
| 21 |
+-----------+
在student表,查询修English的学生数目:
mysql> SELECT COUNT(*)
-> FROM student
-> WHERE Cname='English';
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
3.4.5 分组查询和HAVING
为更好验证效果,向student表中再插入一些记录:
mysql> SELECT *
-> FROM student;
+-----+-------+------+----------+------+
| Sno | Name | Age | Cname | Cno |
+-----+-------+------+----------+------+
| 1 | Jerry | 10 | Chinese | 1 |
| 2 | Peter | 11 | English | 2 |
| 3 | Linda | 11 | English | 2 |
| 4 | Dick | 13 | Art | 5 |
| 5 | Rose | 12 | Music | 4 |
| 6 | Alice | 28 | Computer | 8 |
| 7 | Jim | 27 | Computer | 8 |
| 8 | Tim | 29 | Computer | 8 |
| 9 | Horry | 40 | Computer | 8 |
+-----+-------+------+----------+------+
标准的sql语句,GROUP BY是用于分组查询(指定字段的值相同的为一组)。
而在mysql中,变得非常别扭。GROUP BY仅输出分组后的每组的第一个记录,毫无意义:
比如,在表student,按课程分组:
mysql> SELECT *
-> FROM student
-> GROUP BY Cname;
+-----+-------+------+----------+------+
| Sno | Name | Age | Cname | Cno |
+-----+-------+------+----------+------+
| 4 | Dick | 13 | Art | 5 |
| 1 | Jerry | 10 | Chinese | 1 |
| 6 | Alice | 28 | Computer | 8 |
| 2 | Peter | 11 | English | 2 |
| 5 | Rose | 12 | Music | 4 |
+-----+-------+------+----------+------+
使用GROUP_CONCAT()可显示所有记录,但只有指定字段2。
比如仍按Cname分组,但显示所有记录,指定字段为Cname和Age和平均年龄:
mysql> SELECT AVG(Age),GROUP_CONCAT(Age) FROM student GROUP BY Cname;
+----------+-------------------+
| AVG(Age) | GROUP_CONCAT(Age) |
+----------+-------------------+
| 13.0000 | 13 |
| 10.0000 | 10 |
| 31.0000 | 28,27,29,40 |
| 11.0000 | 11,11 |
| 12.0000 | 12 |
+----------+-------------------+
这里填写Name字段也是可以输出的,不过没什么意义,只能输出每组的第一个记录的Name。
还可使用HAVING,按一定条件筛选分组。
比如,上述结果中,输出一组中数目大于等于2的:
mysql> SELECT Cname,AVG(Age),GROUP_CONCAT(Age) FROM student GROUP BY Cname HAVING COUNT(*)>=2;
+----------+----------+-------------------+
| Cname | AVG(Age) | GROUP_CONCAT(Age) |
+----------+----------+-------------------+
| Computer | 31.0000 | 28,27,29,40 |
| English | 11.0000 | 11,11 |
+----------+----------+-------------------+
HAVING和WHERE都是筛选满足条件的记录。不过WHERE用于筛选查询结果,HAVING用于筛选分组后每个组的结果(WHERE先生效);且HAVING中可使用聚合函数。
3.4.6 连接查询
3.4.6.1 等值连接
一次查询往往需要多张表的数据,所以更多的是连接查询。
为方便演示,把上述的表student和course结构改为:
mysql> DESC student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno | int(11) | NO | PRI | NULL | |
| Name | char(32) | YES | | NULL | |
| Age | smallint(6) | YES | | NULL | |
| Cname | char(16) | YES | MUL | NULL | |
| Cno | smallint(6) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> DESC course;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Cno | smallint(6) | NO | | NULL | |
| Cname | char(16) | NO | PRI | | |
| Cpno | smallint(6) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
如果要查询学生选择课程对应的课程号,就需要把这两表按课程名连接起来:
mysql> SELECT student.Name,course.Cname,course.Cno
-> FROM student,course
-> WHERE student.Cname=course.Cname;
+-------+----------+-----+
| Name | Cname | Cno |
+-------+----------+-----+
| Jerry | Chinese | 1 |
| Peter | English | 2 |
| Linda | English | 2 |
| Dick | Art | 5 |
| Rose | Music | 4 |
| Alice | Computer | 8 |
| Jim | Computer | 8 |
| Tim | Computer | 8 |
| Horry | Computer | 8 |
+-------+----------+-----+
这是根据字段Cname进行的等值连接查询,连接过程是:
根据student表中的Cname的第一个值,与course表中所有的Cname值对比,把course表中Cname字段与student表中Cname字段相等的记录,连接在student表后。而后继续用student表Cname字段的第二个值进行对比,直到结束。
所以查询所有字段,它们本来连接起来的表是这样:
mysql> SELECT * FROM student,course WHERE student.Cname=course.Cname;
+-----+-------+------+----------+------+-----+----------+------+
| Sno | Name | Age | Cname | Cno | Cno | Cname | Cpno |
+-----+-------+------+----------+------+-----+----------+------+
| 1 | Jerry | 10 | Chinese | 1 | 1 | Chinese | 6 |
| 2 | Peter | 11 | English | 2 | 2 | English | 5 |
| 3 | Linda | 11 | English | 2 | 2 | English | 5 |
| 4 | Dick | 13 | Art | 5 | 5 | Art | 3 |
| 5 | Rose | 12 | Music | 4 | 4 | Music | 3 |
| 6 | Alice | 28 | Computer | 8 | 8 | Computer | 10 |
| 7 | Jim | 27 | Computer | 8 | 8 | Computer | 10 |
| 8 | Tim | 29 | Computer | 8 | 8 | Computer | 10 |
| 9 | Horry | 40 | Computer | 8 | 8 | Computer | 10 |
+-----+-------+------+----------+------+-----+----------+------+
因为两张表中都有字段Cno,所以重复输出了。如果仅指定输出不重复的字段(就是用SELECT一一指定要输出的字段,人为地不让输出重复的),则称为自然连接。
3.4.6.2 自身连接
在course表中添加先修课字段,内容变为:
mysql> SELECT * FROM course;
+-----+----------+------+--------------+
| Cno | Cname | Cpno | Prerequisite |
+-----+----------+------+--------------+
| 1 | Chinese | 6 | NULL |
| 2 | English | 5 | Chinese |
| 3 | PE | 4 | NULL |
| 4 | Music | 3 | PE |
| 5 | Art | 3 | Music |
| 8 | Computer | 10 | English |
+-----+----------+------+--------------+
如果要查询先修课的先修课,那么可以把两张course表连接起来(并不是真的有两course表,而是逻辑上连接起来),就可以查询了:
mysql> SELECT first.Cname,first.Prerequisite,second.Prerequisite
-> FROM course first,course second
-> WHERE first.Prerequisite=second.Cname;
+----------+--------------+--------------+
| Cname | Prerequisite | Prerequisite |
+----------+--------------+--------------+
| English | Chinese | NULL |
| Music | PE | NULL |
| Art | Music | PE |
| Computer | English | Chinese |
+----------+--------------+--------------+
注意,这里在引用表course时分别为其起了两个别名first和second,否则引用会混淆。
3.4.6.3 外连接
为验证外连接效果,在表student、course插入新记录,变为:
mysql> select * from student;
+-----+---------+------+----------+------+
| Sno | Name | Age | Cname | Cno |
+-----+---------+------+----------+------+
| 1 | Jerry | 10 | Chinese | 1 |
| 2 | Peter | 11 | English | 2 |
| 3 | Linda | 11 | English | 2 |
| 4 | Dick | 13 | Art | 5 |
| 5 | Rose | 12 | Music | 4 |
| 6 | Alice | 28 | Computer | 8 |
| 7 | Jim | 27 | Computer | 8 |
| 8 | Tim | 29 | Computer | 8 |
| 9 | Horry | 40 | Computer | 8 |
| 15 | Kobe | 40 | NULL | NULL |
| 16 | Iverson | 40 | NULL | NULL |
+-----+---------+------+----------+------+
mysql> SELECT *
-> FROM course;
+-----+----------+------+--------------+
| Cno | Cname | Cpno | Prerequisite |
+-----+----------+------+--------------+
| 1 | Chinese | 6 | NULL |
| 2 | English | 5 | Chinese |
| 3 | PE | 4 | NULL |
| 4 | Music | 3 | PE |
| 5 | Art | 3 | Music |
| 8 | Computer | 10 | English |
| 9 | Math | 18 | NULL |
+-----+----------+------+--------------+
查询各学生选修课程的课程名和学分:
mysql> SELECT student.Name,student.Cname,course.Cpno
-> FROM student,course
-> WHERE student.Cname=course.Cname;
+-------+----------+------+
| Name | Cname | Cpno |
+-------+----------+------+
| Jerry | Chinese | 6 |
| Peter | English | 5 |
| Linda | English | 5 |
| Dick | Art | 3 |
| Rose | Music | 3 |
| Alice | Computer | 10 |
| Jim | Computer | 10 |
| Tim | Computer | 10 |
| Horry | Computer | 10 |
+-------+----------+------+
可以看到,查询结果自动略去了在course表中没有匹配到Cname字段的学生记录。
如要把为匹配到字段的记录也显示出来,可使用外连接(分为左、右、全外连接),格式:
SELECT column1,column2...
FROM table_name1 [ LEFT | RIGHT | FULL [OUTER] ] JOIN table_name2
ON table_name1.colunm=table_name2.column
上述查询,如果使用左外连接,就可把未选课的学生也列出来:
mysql> SELECT student.Name,student.Cname,course.Cpno
-> FROM student LEFT OUTER JOIN course
-> ON student.Cname=course.Cname;
+---------+----------+------+
| Name | Cname | Cpno |
+---------+----------+------+
| Jerry | Chinese | 6 |
| Peter | English | 5 |
| Linda | English | 5 |
| Dick | Art | 3 |
| Rose | Music | 3 |
| Alice | Computer | 10 |
| Jim | Computer | 10 |
| Tim | Computer | 10 |
| Horry | Computer | 10 |
| Kobe | NULL | NULL |
| Iverson | NULL | NULL |
+---------+----------+------+
如果是右外连接,则会把未被选到的课程(即后者表course未匹配到的Cname)也列出:
mysql> SELECT student.Name,course.Cname,course.Cpno FROM student RIGHT OUTER JOIN course ON student.Cname=course.Cname;
+-------+----------+------+
| Name | Cname | Cpno |
+-------+----------+------+
| Jerry | Chinese | 6 |
| Peter | English | 5 |
| Linda | English | 5 |
| NULL | PE | 4 |
| Rose | Music | 3 |
| Dick | Art | 3 |
| Alice | Computer | 10 |
| Jim | Computer | 10 |
| Tim | Computer | 10 |
| Horry | Computer | 10 |
| NULL | Math | 18 |
+-------+----------+------+
还可以用逻辑运算符,在WHERE语句中定义条件,使查询涉及两个以上的表也连接起来。方法类似上述各种连接(只是用逻辑运算连起来了),不赘述了。
3.4.7 嵌套查询
分为不相关嵌套查询和相关嵌套查询。
3.4.7.1 不相关子查询
把子查询的结果作为父查询的查询范围(子查询不能进行排序操作)。
比如,查询选修了8号课程的学生名和课程名:
mysql> SELECT Name,Cname
-> FROM student
-> WHERE Cname IN (
-> SELECT Cname
-> FROM course
-> WHERE Cno=8);
+-------+----------+
| Name | Cname |
+-------+----------+
| Alice | Computer |
| Jim | Computer |
| Tim | Computer |
| Horry | Computer |
+-------+----------+
当然把两个表连接起来查询也能实现,但使用子查询更易理解。
3.4.7.2 相关子查询
相关子查询:在子查询中,引用了父查询的内容。子查询逐个处理父查询中的各记录。
为验证效果,在student表中,添加字段Score。内容变为:
mysql> SELECT *
-> FROM student;
+-----+---------+------+----------+------+-------+
| Sno | Name | Age | Cname | Cno | Score |
+-----+---------+------+----------+------+-------+
| 1 | Jerry | 10 | Chinese | 1 | 100 |
| 2 | Peter | 11 | English | 2 | 90 |
| 3 | Linda | 11 | English | 2 | 95 |
| 4 | Dick | 13 | Art | 5 | 98 |
| 5 | Rose | 12 | Music | 4 | 89 |
| 6 | Alice | 28 | Computer | 8 | 88 |
| 7 | Jim | 27 | Computer | 8 | 78 |
| 8 | Tim | 29 | Computer | 8 | 76 |
| 9 | Horry | 40 | Computer | 8 | 76 |
| 15 | Kobe | 40 | NULL | NULL | NULL |
| 16 | Iverson | 40 | NULL | NULL | NULL |
+-----+---------+------+----------+------+-------+
查询所有选了课的学生中,成绩大于等于平均成绩的学生姓名、课程名和成绩:
mysql> SELECT Name,Cname,Score
-> FROM student x
-> WHERE Score>=(SELECT AVG(Score)
-> FROM student y
-> WHERE y.Cname=x.Cname);
+-------+----------+-------+
| Name | Cname | Score |
+-------+----------+-------+
| Jerry | Chinese | 100 |
| Linda | English | 95 |
| Dick | Art | 98 |
| Rose | Music | 89 |
| Alice | Computer | 88 |
+-------+----------+-------+
父查询和子查询中都用到了student表,所以使用别名加以区分;
每一个父查询的记录,都会到子查询中“过”一遍。这正是相关子查询的流程。
3.4.7.3 带EXISTS的子查询
谓词EXISTS表示存在,NOT EXISTS表示不存在。以它们作为WHERE的条件的子查询,返回值为真或假。由于返回结果不是具体数据而只是真、假,所以效率往往更高。
比如,查询所有选修了8号课程的学生的姓名和对应课程名:
mysql> SELECT Name,Cname
-> FROM student
-> WHERE EXISTS
-> (SELECT *
-> FROM course
-> WHERE Cname=student.Cname AND Cno=8);
+-------+----------+
| Name | Cname |
+-------+----------+
| Alice | Computer |
| Jim | Computer |
| Tim | Computer |
| Horry | Computer |
+-------+----------+
由于子查询中引用了父查询中的表,所以父查询的记录会逐个到子查询中进行匹配。如匹配,则结果为真,就是要查询的结果。
NOT EXISTS不赘述了。
EXISTS子查询用法很灵活,可根据谓词演算,使用EXISTS代替很多条件(比如比较运算等)。
3.4.8 联合查询结果
使用关键字UNION可联合查询结果。显然查询结果中字段数和数据类型一致的结果才能被联合。
比如,查询选修Computer和English的学生信息:
mysql> SELECT *
-> FROM student
-> WHERE Cname='Computer'
-> UNION
-> SELECT *
-> FROM student
-> WHERE Cname='English';
+-----+-------+------+----------+------+-------+
| Sno | Name | Age | Cname | Cno | Score |
+-----+-------+------+----------+------+-------+
| 6 | Alice | 28 | Computer | 8 | 88 |
| 7 | Jim | 27 | Computer | 8 | 78 |
| 8 | Tim | 29 | Computer | 8 | 76 |
| 9 | Horry | 40 | Computer | 8 | 76 |
| 2 | Peter | 11 | English | 2 | 90 |
| 3 | Linda | 11 | English | 2 | 95 |
+-----+-------+------+----------+------+-------+
注意:
1、如需对结果排序,要在最后一个SELECT语句中使用ORDER BY,前面的不允许使用;
2、查询结果中,默认会删除重复记录。如不删除,需在UNION后添加ALL。
4 用户授权
创建、删除用户格式:
CREATE USER username@host [identifie by password]
DROP USER username
向指定用户授权格式:
GRANT 权限1,权限2……
ON 对象类型 对象名……
TO 用户1,用户2……
[WITH GRANT OPTION]
对象类型:DATABASE、TABLE、VIEW、TABLE(指定column)等。
权限:SELECT、UPDATE等,如果授予全部权限则使用ALL PRIVILEGES
WITH GRANT OPTION表示,允许用户级联授权。即授予某用户权限,令该用户也有权把该权限授予其他用户。不过如果级联授权,当这个用户的权限被收回时,他授权的那些用户权限也会被一并收回。
收回权限格式:
REVOKE 权限1,权限2……
ON 对象类型 对象名……
FROM 用户1,用户2……
很简单不举例了。
查看用户权限:
SHOW GRANTS FOR username@host # 查看指定用户名
注意默认情况host是主机名,不能是IP地址。因为mysql会反解IP。除非在配置文件/etc/my.cnf中设置不反解IP。
(完)