MySQL篇，第二章：数据库知识2
MySQL 数据库 2
名词介绍
1、DB(Database)
DB就是数据库,存储数据的仓库
2、DBMS(Database Management System)
数据库管理系统
管理数据库的软件,MySQL、Oracle、...
3、DBS(Database System)
数据库系统
DBS=DB(存储)+DBMS(数据库软件)+数据库应用(财务管理系统,人事管理系统)+用户
where条件字句(配合查、改、删操作)
1、语法格式
select * from 表名 where 条件;
表记录管理(续)
1、更新表记录
1、update 表名 set 字段名=值,字段名=值,...where 条件;
2、注意
update语句后如果不加where子句,表中所有记录该字段的值都会更改
2、删除表记录
1、delete from 表名 where 条件;
2、注意
delete语句后如果不加where条件子句,将会把表中所有的记录全部删除
练习：
1、查找所有蜀国人信息
2、查找女英雄信息,显示姓名、性别和国家
3、把魏延的性别改为女，国籍改为泰国
4、把id为2的记录名字改为司马懿,性别男,国家为魏国
5、删除所有泰国人
1 mysql> show databases; 2 +--------------------+ 3 | Database | 4 +--------------------+ 5 | information_schema | 6 | db1 | 7 | db2 | 8 | db3 | 9 | mysql | 10 | performance_schema | 11 | sys | 12 +--------------------+ 13 7 rows in set (1.52 sec) 14 15 mysql> use db2 16 Database changed 17 mysql> show tables; 18 Empty set (0.01 sec) 19 20 mysql> create table t1( 21 -> id int(3) zerofill, 22 -> name varchar(15), 23 -> age tinyint unsigned, 24 -> address char(10) 25 -> )default charset=utf8; 26 Query OK, 0 rows affected (1.12 sec) 27 28 mysql> insert into t1 values 29 -> (1,'诸葛亮',33,'北京'), 30 -> (2,'司马懿',34,'上海')， 31 -> (3,'赵子龙',30,'北京'); 32 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '， 33 34 (3,'赵子龙',30,'北京')' at line 4 35 mysql> insert into t1 values (1,'诸葛亮',33,'北京'), (2,'司马懿',34,'上海')， (3,'赵子龙',30,'北京'); 36 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '， (3,'赵子龙',30,'北京')' at line 1 37 mysql> insert into t1 values (1,'诸葛亮',33,'北京'), (2,'司马懿',34,'上海')， (3,'赵子龙',30,'北京'); 38 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '， (3,'赵子龙',30,'北京')' at line 1 39 mysql> insert into t1 values (1,'诸葛亮',33,'北京'), (2,'司马懿',34,'上海'), (3,'赵子龙',30,'北京'); 40 Query OK, 3 rows affected (0.34 sec) 41 Records: 3 Duplicates: 0 Warnings: 0 42 43 mysql> select * from t1 where address='北京'; 44 +------+-----------+------+---------+ 45 | id | name | age | address | 46 +------+-----------+------+---------+ 47 | 001 | 诸葛亮 | 33 | 北京 | 48 | 003 | 赵子龙 | 30 | 北京 | 49 +------+-----------+------+---------+ 50 2 rows in set (0.08 sec) 51 52 mysql> select * from t1 where id=1; 53 +------+-----------+------+---------+ 54 | id | name | age | address | 55 +------+-----------+------+---------+ 56 | 001 | 诸葛亮 | 33 | 北京 | 57 +------+-----------+------+---------+ 58 1 row in set (0.03 sec) 59 60 mysql> update t1 set address='上海' 61 -> where 62 -> name='赵子龙'; 63 Query OK, 1 row affected (0.06 sec) 64 Rows matched: 1 Changed: 1 Warnings: 0 65 66 mysql> select * from t1; 67 +------+-----------+------+---------+ 68 | id | name | age | address | 69 +------+-----------+------+---------+ 70 | 001 | 诸葛亮 | 33 | 北京 | 71 | 002 | 司马懿 | 34 | 上海 | 72 | 003 | 赵子龙 | 30 | 上海 | 73 +------+-----------+------+---------+ 74 3 rows in set (0.02 sec) 75 76 mysql> update t1 set name='张飞',age=88 77 -> where 78 -> id=1; 79 Query OK, 1 row affected (0.08 sec) 80 Rows matched: 1 Changed: 1 Warnings: 0 81 82 mysql> select * from t1; 83 +------+-----------+------+---------+ 84 | id | name | age | address | 85 +------+-----------+------+---------+ 86 | 001 | 张飞 | 88 | 北京 | 87 | 002 | 司马懿 | 34 | 上海 | 88 | 003 | 赵子龙 | 30 | 上海 | 89 +------+-----------+------+---------+ 90 3 rows in set (0.00 sec) 91 92 mysql> update t1 set name='赵云' 93 -> where 94 -> id=3; 95 Query OK, 1 row affected (0.07 sec) 96 Rows matched: 1 Changed: 1 Warnings: 0 97 98 mysql> select * from t1; 99 +------+-----------+------+---------+ 100 | id | name | age | address | 101 +------+-----------+------+---------+ 102 | 001 | 张飞 | 88 | 北京 | 103 | 002 | 司马懿 | 34 | 上海 | 104 | 003 | 赵云 | 30 | 上海 | 105 +------+-----------+------+---------+ 106 3 rows in set (0.00 sec) 107 108 mysql> update t1 set name='赵云'; 109 Query OK, 2 rows affected (0.03 sec) 110 Rows matched: 3 Changed: 2 Warnings: 0 111 112 mysql> select * from t1; 113 +------+--------+------+---------+ 114 | id | name | age | address | 115 +------+--------+------+---------+ 116 | 001 | 赵云 | 88 | 北京 | 117 | 002 | 赵云 | 34 | 上海 | 118 | 003 | 赵云 | 30 | 上海 | 119 +------+--------+------+---------+ 120 3 rows in set (0.00 sec) 121 122 mysql> delete from t1 where id=2; 123 Query OK, 1 row affected (0.06 sec) 124 125 mysql> select * from t1; 126 +------+--------+------+---------+ 127 | id | name | age | address | 128 +------+--------+------+---------+ 129 | 001 | 赵云 | 88 | 北京 | 130 | 003 | 赵云 | 30 | 上海 | 131 +------+--------+------+---------+ 132 2 rows in set (0.00 sec) 133 134 mysql> delete from t1; 135 Query OK, 2 rows affected (0.04 sec) 136 137 mysql> select * from t1; 138 Empty set (0.00 sec) 139 140 mysql>
1 第一步：创建库 SANGUO 2 create database SANGUO; 3 4 第二步：切换库 5 use SANGUO; 6 7 第三步：创建表sanguo 8 create table sanguo( 9 id int, 10 name char(15), 11 sex enum("男","女"), 12 country char(10) 13 )default charset=utf8; 14 15 第四步：插入记录 16 insert into sanguo values 17 (1,"曹操","男","魏国"), 18 (2,"小乔","女","吴国"), 19 (3,"诸葛亮","男","蜀国"), 20 (4,"貂蝉","女","东汉"), 21 (5,"赵子龙","男","蜀国"), 22 (6,"魏延","男","蜀国"); 23 24 ###### 25 1、创建库MoShou 26 create database MoShou; 27 28 2、切换库 29 use MoShou; 30 31 3、创建表sanguo 32 create table sanguo( 33 id int, 34 name char(20), 35 gongji int, 36 fangyu tinyint unsigned, 37 sex enum("男","女"), 38 country varchar(20) 39 )default charset=utf8; 40 41 4、在表中插入记录 42 insert into sanguo values 43 (1,'诸葛亮',120,20,'男','蜀国'), 44 (2,'司马懿',119,25,'男','魏国'), 45 (3,'关羽',188,60,'男','蜀国'), 46 (4,'赵云',200,66,'男','魏国'), 47 (5,'孙权',110,20,'男','吴国'), 48 (6,'貂蝉',666,10,'女','魏国'), 49 (7,null,1000,99,'男','蜀国'), 50 (8,'',1005,88,'女','蜀国'); 51 52 #### 53 mysql> create database SANGUO; 54 Query OK, 1 row affected (0.20 sec) 55 56 mysql> use SANGUO; 57 Database changed 58 mysql> create table sanguo( 59 -> id int, 60 -> name char(15), 61 -> sex enum("男","女"), 62 -> country char(10) 63 -> )default charset=utf8; 64 Query OK, 0 rows affected (0.16 sec) 65 66 mysql> insert into sanguo values 67 -> (1,"曹操","男","魏国"), 68 -> (2,"小乔","女","吴国"), 69 -> (3,"诸葛亮","男","蜀国"), 70 -> (4,"貂蝉","女","东汉"), 71 -> (5,"赵子龙","男","蜀国"), 72 -> (6,"魏延","男","蜀国"); 73 Query OK, 6 rows affected (0.07 sec) 74 Records: 6 Duplicates: 0 Warnings: 0 75 76 mysql> select * from sanguo; 77 +------+-----------+------+---------+ 78 | id | name | sex | country | 79 +------+-----------+------+---------+ 80 | 1 | 曹操 | 男 | 魏国 | 81 | 2 | 小乔 | 女 | 吴国 | 82 | 3 | 诸葛亮 | 男 | 蜀国 | 83 | 4 | 貂蝉 | 女 | 东汉 | 84 | 5 | 赵子龙 | 男 | 蜀国 | 85 | 6 | 魏延 | 男 | 蜀国 | 86 +------+-----------+------+---------+ 87 6 rows in set (0.00 sec) 88 89 mysql> create database MoShou; 90 Query OK, 1 row affected (0.02 sec) 91 92 mysql> use MoShou; 93 Database changed 94 mysql> create table sanguo( 95 -> id int, 96 -> name char(20), 97 -> gongji int, 98 -> fangyu tinyint unsigned, 99 -> sex enum("男","女"), 100 -> country varchar(20) 101 -> )default charset=utf8; 102 Query OK, 0 rows affected (0.20 sec) 103 104 mysql> insert into sanguo values 105 -> (1,'诸葛亮',120,20,'男','蜀国'), 106 -> (2,'司马懿',119,25,'男','魏国'), 107 -> (3,'关羽',188,60,'男','蜀国'), 108 -> (4,'赵云',200,66,'男','魏国'), 109 -> (5,'孙权',110,20,'男','吴国'), 110 -> (6,'貂蝉',666,10,'女','魏国'), 111 -> (7,null,1000,99,'男','蜀国'), 112 -> (8,'',1005,88,'女','蜀国'); 113 Query OK, 8 rows affected (0.09 sec) 114 Records: 8 Duplicates: 0 Warnings: 0 115 116 mysql> select * from sanguo; 117 +------+-----------+--------+--------+------+---------+ 118 | id | name | gongji | fangyu | sex | country | 119 +------+-----------+--------+--------+------+---------+ 120 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 121 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 122 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 123 | 4 | 赵云 | 200 | 66 | 男 | 魏国 | 124 | 5 | 孙权 | 110 | 20 | 男 | 吴国 | 125 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 126 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 127 | 8 | | 1005 | 88 | 女 | 蜀国 | 128 +------+-----------+--------+--------+------+---------+ 129 8 rows in set (0.00 sec) 130 131 mysql> select database(); 132 +------------+ 133 | database() | 134 +------------+ 135 | MoShou | 136 +------------+ 137 1 row in set (0.00 sec) 138 139 mysql> use SANGUO; 140 Reading table information for completion of table and column names 141 You can turn off this feature to get a quicker startup with -A 142 143 Database changed 144 mysql> show tables; 145 +------------------+ 146 | Tables_in_SANGUO | 147 +------------------+ 148 | sanguo | 149 +------------------+ 150 1 row in set (0.00 sec) 151 152 mysql> select * from sanguo; 153 +------+-----------+------+---------+ 154 | id | name | sex | country | 155 +------+-----------+------+---------+ 156 | 1 | 曹操 | 男 | 魏国 | 157 | 2 | 小乔 | 女 | 吴国 | 158 | 3 | 诸葛亮 | 男 | 蜀国 | 159 | 4 | 貂蝉 | 女 | 东汉 | 160 | 5 | 赵子龙 | 男 | 蜀国 | 161 | 6 | 魏延 | 男 | 蜀国 | 162 +------+-----------+------+---------+ 163 6 rows in set (0.00 sec) 164 165 166 mysql> use SANGUO; 167 Database changed 168 mysql> show tables; 169 +------------------+ 170 | Tables_in_SANGUO | 171 +------------------+ 172 | sanguo | 173 +------------------+ 174 1 row in set (0.01 sec) 175 176 mysql> select * from sanguo; 177 +------+-----------+------+---------+ 178 | id | name | sex | country | 179 +------+-----------+------+---------+ 180 | 1 | 曹操 | 男 | 魏国 | 181 | 2 | 小乔 | 女 | 吴国 | 182 | 3 | 诸葛亮 | 男 | 蜀国 | 183 | 4 | 貂蝉 | 女 | 东汉 | 184 | 5 | 赵子龙 | 男 | 蜀国 | 185 | 6 | 魏延 | 男 | 蜀国 | 186 +------+-----------+------+---------+ 187 6 rows in set (0.00 sec) 188 189 mysql> select * from sanguo where country='蜀国'; 190 +------+-----------+------+---------+ 191 | id | name | sex | country | 192 +------+-----------+------+---------+ 193 | 3 | 诸葛亮 | 男 | 蜀国 | 194 | 5 | 赵子龙 | 男 | 蜀国 | 195 | 6 | 魏延 | 男 | 蜀国 | 196 +------+-----------+------+---------+ 197 3 rows in set (0.00 sec) 198 199 mysql> select name,sex,country from sanguo where sex='女'; 200 +--------+------+---------+ 201 | name | sex | country | 202 +--------+------+---------+ 203 | 小乔 | 女 | 吴国 | 204 | 貂蝉 | 女 | 东汉 | 205 +--------+------+---------+ 206 2 rows in set (0.01 sec) 207 208 mysql> update sanguo set 209 -> sex='女',country='泰国' 210 -> where 211 -> name='魏延'; 212 Query OK, 1 row affected (0.04 sec) 213 Rows matched: 1 Changed: 1 Warnings: 0 214 215 mysql> select * from sanguo; 216 +------+-----------+------+---------+ 217 | id | name | sex | country | 218 +------+-----------+------+---------+ 219 | 1 | 曹操 | 男 | 魏国 | 220 | 2 | 小乔 | 女 | 吴国 | 221 | 3 | 诸葛亮 | 男 | 蜀国 | 222 | 4 | 貂蝉 | 女 | 东汉 | 223 | 5 | 赵子龙 | 男 | 蜀国 | 224 | 6 | 魏延 | 女 | 泰国 | 225 +------+-----------+------+---------+ 226 6 rows in set (0.00 sec) 227 228 mysql> update sanguo set 229 -> name='司马懿',sex='男',country='魏国' 230 -> where 231 -> id=2; 232 Query OK, 1 row affected (0.04 sec) 233 Rows matched: 1 Changed: 1 Warnings: 0 234 235 mysql> select * from sanguo; 236 +------+-----------+------+---------+ 237 | id | name | sex | country | 238 +------+-----------+------+---------+ 239 | 1 | 曹操 | 男 | 魏国 | 240 | 2 | 司马懿 | 男 | 魏国 | 241 | 3 | 诸葛亮 | 男 | 蜀国 | 242 | 4 | 貂蝉 | 女 | 东汉 | 243 | 5 | 赵子龙 | 男 | 蜀国 | 244 | 6 | 魏延 | 女 | 泰国 | 245 +------+-----------+------+---------+ 246 6 rows in set (0.00 sec) 247 248 mysql> delect from sanguo 249 -> where 250 -> country='泰国'; 251 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delect from sanguo 252 where 253 country='泰国'' at line 1 254 mysql> delect from sanguo where country='泰国'; 255 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delect from sanguo where country='泰国'' at line 1 256 mysql> delete from sanguo where country='泰国'; 257 Query OK, 1 row affected (0.02 sec) 258 259 mysql> select * from sanguo; 260 +------+-----------+------+---------+ 261 | id | name | sex | country | 262 +------+-----------+------+---------+ 263 | 1 | 曹操 | 男 | 魏国 | 264 | 2 | 司马懿 | 男 | 魏国 | 265 | 3 | 诸葛亮 | 男 | 蜀国 | 266 | 4 | 貂蝉 | 女 | 东汉 | 267 | 5 | 赵子龙 | 男 | 蜀国 | 268 +------+-----------+------+---------+ 269 5 rows in set (0.00 sec) 270 271 mysql>
运算符操作(配合查询、修改、删除操作)
1、数值比较&字符比较
1、数值比较运算符：=、!=、>、>=、<、<=
2、字符比较运算符：=、!=
3、语法格式
查询：
select * from 表名 where 字段名 运算符 数字/字符;
修改：
update 表名 set 字段名=值,... where 字段名 运算符 数字/字符;
删除：
delete from 表名 where 字段名 运算符 数字/字符;
4、练习
1、找出攻击值高于150的英雄的名字和攻击值
2、将赵云的攻击值改为666，防御值改为88
1 mysql> use MoShou; 2 Reading table information for completion of table and column names 3 You can turn off this feature to get a quicker startup with -A 4 5 Database changed 6 mysql> show tables; 7 +------------------+ 8 | Tables_in_MoShou | 9 +------------------+ 10 | sanguo | 11 +------------------+ 12 1 row in set (0.04 sec) 13 14 mysql> select * from sanguo; 15 +------+-----------+--------+--------+------+---------+ 16 | id | name | gongji | fangyu | sex | country | 17 +------+-----------+--------+--------+------+---------+ 18 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 19 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 20 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 21 | 4 | 赵云 | 200 | 66 | 男 | 魏国 | 22 | 5 | 孙权 | 110 | 20 | 男 | 吴国 | 23 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 24 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 25 | 8 | | 1005 | 88 | 女 | 蜀国 | 26 +------+-----------+--------+--------+------+---------+ 27 8 rows in set (0.00 sec) 28 29 mysql> select name,gongji from sanguo 30 -> where 31 -> gongji > 150; 32 +--------+--------+ 33 | name | gongji | 34 +--------+--------+ 35 | 关羽 | 188 | 36 | 赵云 | 200 | 37 | 貂蝉 | 666 | 38 | NULL | 1000 | 39 | | 1005 | 40 +--------+--------+ 41 5 rows in set (0.01 sec) 42 43 mysql> update sanguo set 44 -> gongji=666,fangyu=88 45 -> where 46 -> name='赵云'; 47 Query OK, 1 row affected (0.06 sec) 48 Rows matched: 1 Changed: 1 Warnings: 0 49 50 mysql> select * from sanguo; 51 +------+-----------+--------+--------+------+---------+ 52 | id | name | gongji | fangyu | sex | country | 53 +------+-----------+--------+--------+------+---------+ 54 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 55 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 56 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 57 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 58 | 5 | 孙权 | 110 | 20 | 男 | 吴国 | 59 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 60 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 61 | 8 | | 1005 | 88 | 女 | 蜀国 | 62 +------+-----------+--------+--------+------+---------+ 63 8 rows in set (0.00 sec) 64 65 mysql>
2、逻辑比较
1、运算符:
and(多个条件同时满足)
or(多个条件有一个条件满足就可以)
2、练习
1、找出攻击值大于200的蜀国英雄的名字及攻击值
2、将吴国英雄中攻击值为110的英雄的攻击值设置为100，防御值设置为60
3、查找蜀国和魏国的英雄信息
1 mysql> select * from sanguo; 2 +------+-----------+--------+--------+------+---------+ 3 | id | name | gongji | fangyu | sex | country | 4 +------+-----------+--------+--------+------+---------+ 5 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 6 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 7 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 8 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 9 | 5 | 孙权 | 110 | 20 | 男 | 吴国 | 10 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 11 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 12 | 8 | | 1005 | 88 | 女 | 蜀国 | 13 +------+-----------+--------+--------+------+---------+ 14 8 rows in set (0.00 sec) 15 16 mysql> 1、找出攻击值大于200的蜀国英雄的名字及攻击值 17 -> 2、将吴国英雄中攻击值为110的英雄的攻击值设置为100，防御值设置为60 18 -> 3、查找蜀国和魏国的英雄信息\c 19 mysql> select name,gongji from sanguo 20 -> where 21 -> gongji > 200 and country='蜀国'; 22 +------+--------+ 23 | name | gongji | 24 +------+--------+ 25 | NULL | 1000 | 26 | | 1005 | 27 +------+--------+ 28 2 rows in set (0.07 sec) 29 30 mysql> update sanguo set 31 -> gongji=100,fangyu=60 32 -> where 33 -> gongji=110 and country='吴国'; 34 Query OK, 1 row affected (0.03 sec) 35 Rows matched: 1 Changed: 1 Warnings: 0 36 37 mysql> select * from sanguo; 38 +------+-----------+--------+--------+------+---------+ 39 | id | name | gongji | fangyu | sex | country | 40 +------+-----------+--------+--------+------+---------+ 41 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 42 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 43 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 44 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 45 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 46 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 47 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 48 | 8 | | 1005 | 88 | 女 | 蜀国 | 49 +------+-----------+--------+--------+------+---------+ 50 8 rows in set (0.00 sec) 51 52 mysql> select * from sanguo where contry='吴国'; 53 ERROR 1054 (42S22): Unknown column 'contry' in 'where clause' 54 mysql> select * from sanguo where country='吴国'; 55 +------+--------+--------+--------+------+---------+ 56 | id | name | gongji | fangyu | sex | country | 57 +------+--------+--------+--------+------+---------+ 58 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 59 +------+--------+--------+--------+------+---------+ 60 1 row in set (0.00 sec) 61 62 mysql> select * from sanguo 63 -> where 64 -> country='蜀国' or country='魏国'; 65 +------+-----------+--------+--------+------+---------+ 66 | id | name | gongji | fangyu | sex | country | 67 +------+-----------+--------+--------+------+---------+ 68 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 69 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 70 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 71 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 72 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 73 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 74 | 8 | | 1005 | 88 | 女 | 蜀国 | 75 +------+-----------+--------+--------+------+---------+ 76 7 rows in set (0.01 sec) 77 78 mysql>
3、范围内比较
1、运算符 ：between and 、in 、not in
2、语法格式
字段名 between 值1 and 值2
字段名 in(值1,值2,...)
字段名 not in(值1,值2,...)
3，练习
1、查找攻击值在100-200之间的蜀国英雄信息
2、查找id在1,3,5,7中的英雄的id和姓名
3、找到蜀国和吴国以外的国家的女英雄
4、找到编号为1或3或5的蜀国英雄 和 貂蝉的编号、姓名和国家
1 mysql> select * from sanguo; 2 +------+-----------+--------+--------+------+---------+ 3 | id | name | gongji | fangyu | sex | country | 4 +------+-----------+--------+--------+------+---------+ 5 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 6 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 7 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 8 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 9 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 10 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 11 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 12 | 8 | | 1005 | 88 | 女 | 蜀国 | 13 +------+-----------+--------+--------+------+---------+ 14 8 rows in set (0.00 sec) 15 16 mysql> select * from sanguo 17 -> where 18 -> gongji between 100 and 200 and country='蜀国'; 19 +------+-----------+--------+--------+------+---------+ 20 | id | name | gongji | fangyu | sex | country | 21 +------+-----------+--------+--------+------+---------+ 22 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 23 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 24 +------+-----------+--------+--------+------+---------+ 25 2 rows in set (0.05 sec) 26 27 mysql> select * from sanguo 28 -> where 29 -> (gongji between 100 and 200) and (country='蜀国'); 30 +------+-----------+--------+--------+------+---------+ 31 | id | name | gongji | fangyu | sex | country | 32 +------+-----------+--------+--------+------+---------+ 33 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 34 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 35 +------+-----------+--------+--------+------+---------+ 36 2 rows in set (0.00 sec) 37 38 mysql> select * from sanguo 39 -> where 40 -> in\c 41 mysql> select id,name from sanguo 42 -> where 43 -> id in(1,3,5,7); 44 +------+-----------+ 45 | id | name | 46 +------+-----------+ 47 | 1 | 诸葛亮 | 48 | 3 | 关羽 | 49 | 5 | 孙权 | 50 | 7 | NULL | 51 +------+-----------+ 52 4 rows in set (0.05 sec) 53 54 mysql> select * from sanguo 55 -> where 56 -> country not in('蜀国','吴国') and sex='女'; 57 +------+--------+--------+--------+------+---------+ 58 | id | name | gongji | fangyu | sex | country | 59 +------+--------+--------+--------+------+---------+ 60 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 61 +------+--------+--------+--------+------+---------+ 62 1 row in set (0.00 sec) 63 64 mysql> select id,name,country from sanguo 65 -> where 66 -> id in(1,3,5) and country='蜀国' or name='貂蝉'; 67 +------+-----------+---------+ 68 | id | name | country | 69 +------+-----------+---------+ 70 | 1 | 诸葛亮 | 蜀国 | 71 | 3 | 关羽 | 蜀国 | 72 | 6 | 貂蝉 | 魏国 | 73 +------+-----------+---------+ 74 3 rows in set (0.00 sec) 75 76 mysql>
4、匹配空、非空
1、空 ：is null
2、非空 ：is not null
3、练习
1、查找姓名为NULL的蜀国女英雄信息
2、查找姓名为 "" 的英雄的id,姓名和国家
4、注意
1、null ：空值,必须用is 或者 is not 去匹配
2、"" ：空字符串,用 = 或者 != 去匹配
1 mysql> select * from sanguo where name is null and country='蜀国' and sex='女'; 2 Empty set (0.00 sec) 3 4 mysql> select * from sanguo where name is null and country='蜀国'; 5 +------+------+--------+--------+------+---------+ 6 | id | name | gongji | fangyu | sex | country | 7 +------+------+--------+--------+------+---------+ 8 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 9 +------+------+--------+--------+------+---------+ 10 1 row in set (0.01 sec) 11 12 mysql> 13 mysql> 14 mysql> select * from sanguo 15 -> where \c 16 mysql> select id,name,country from sanguo 17 -> where 18 -> name=''; 19 +------+------+---------+ 20 | id | name | country | 21 +------+------+---------+ 22 | 8 | | 蜀国 | 23 +------+------+---------+ 24 1 row in set (0.00 sec) 25 26 mysql>
5、模糊比较
1、语法格式
字段名 like 表达式
2、表达式
1、_ : 匹配单个字符
2、% : 匹配0到多个字符
3、练习
1、select id,name from sanguo where name like "_%_"; ## 名字中至少2个字符的
2、select id,name from sanguo where name like "%"; ## 名字不为null的所有记录
3、select id,name from sanguo where name like "___"; ##匹配名字是三个字符的记录
4、select id,name from sanguo where name like "赵%";
1 mysql> select id,name from sanguo 2 -> where 3 -> name like '_%_'; 4 +------+-----------+ 5 | id | name | 6 +------+-----------+ 7 | 1 | 诸葛亮 | 8 | 2 | 司马懿 | 9 | 3 | 关羽 | 10 | 4 | 赵云 | 11 | 5 | 孙权 | 12 | 6 | 貂蝉 | 13 +------+-----------+ 14 6 rows in set (0.04 sec) 15 16 mysql> select id,name from sanguo 17 -> where 18 -> name like '%'; 19 +------+-----------+ 20 | id | name | 21 +------+-----------+ 22 | 1 | 诸葛亮 | 23 | 2 | 司马懿 | 24 | 3 | 关羽 | 25 | 4 | 赵云 | 26 | 5 | 孙权 | 27 | 6 | 貂蝉 | 28 | 8 | | 29 +------+-----------+ 30 7 rows in set (0.00 sec) 31 32 mysql> select id,name from sanguo 33 -> where 34 -> name like '___'; 35 +------+-----------+ 36 | id | name | 37 +------+-----------+ 38 | 1 | 诸葛亮 | 39 | 2 | 司马懿 | 40 +------+-----------+ 41 2 rows in set (0.00 sec) 42 43 mysql> select id,name from sanguo 44 -> where 45 -> name like '赵%'; 46 +------+--------+ 47 | id | name | 48 +------+--------+ 49 | 4 | 赵云 | 50 +------+--------+ 51 1 row in set (0.01 sec) 52 53 mysql>
SQL查询
1、总结(执行顺序)
3、 select ... 聚合函数 from ...
1、 where ...
2、 group by ...
4、 having ...
5、 order by ...
6、 limit ...;
2、order by
1、作用：对查询的结果进行排序
2、语法格式：order by 字段名 排序方式;
3、排序方式
1、ASC(默认) ： 升序
2、DESC ：降序
4、练习
1、将英雄按防御值从低到高排序
2、将蜀国英雄按攻击值从高到低排序
3、将魏蜀两国男英雄中名字为三个字的英雄按防御值升序 排列
1 mysql> select * from sanguo; 2 +------+-----------+--------+--------+------+---------+ 3 | id | name | gongji | fangyu | sex | country | 4 +------+-----------+--------+--------+------+---------+ 5 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 6 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 7 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 8 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 9 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 10 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 11 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 12 | 8 | | 1005 | 88 | 女 | 蜀国 | 13 +------+-----------+--------+--------+------+---------+ 14 8 rows in set (0.17 sec) 15 16 mysql> select * from sanguo order by fangyu; 17 +------+-----------+--------+--------+------+---------+ 18 | id | name | gongji | fangyu | sex | country | 19 +------+-----------+--------+--------+------+---------+ 20 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 21 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 22 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 23 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 24 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 25 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 26 | 8 | | 1005 | 88 | 女 | 蜀国 | 27 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 28 +------+-----------+--------+--------+------+---------+ 29 8 rows in set (0.16 sec) 30 31 mysql> select * from sanguo order by fangyu ASC; 32 +------+-----------+--------+--------+------+---------+ 33 | id | name | gongji | fangyu | sex | country | 34 +------+-----------+--------+--------+------+---------+ 35 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 36 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 37 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 38 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 39 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 40 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 41 | 8 | | 1005 | 88 | 女 | 蜀国 | 42 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 43 +------+-----------+--------+--------+------+---------+ 44 8 rows in set (0.04 sec) 45 46 mysql> select * from sanguo order by gongji DESC; 47 +------+-----------+--------+--------+------+---------+ 48 | id | name | gongji | fangyu | sex | country | 49 +------+-----------+--------+--------+------+---------+ 50 | 8 | | 1005 | 88 | 女 | 蜀国 | 51 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 52 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 53 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 54 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 55 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 56 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 57 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 58 +------+-----------+--------+--------+------+---------+ 59 8 rows in set (0.05 sec) 60 61 mysql> select * from sanguo 62 -> where 63 -> country='蜀国' order by gongji desc; 64 +------+-----------+--------+--------+------+---------+ 65 | id | name | gongji | fangyu | sex | country | 66 +------+-----------+--------+--------+------+---------+ 67 | 8 | | 1005 | 88 | 女 | 蜀国 | 68 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 69 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 70 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 71 +------+-----------+--------+--------+------+---------+ 72 4 rows in set (0.00 sec) 73 74 mysql> select * from sanguo 75 -> where 76 -> country in('蜀国','魏国') and sex='男' and name like '___' 77 -> order by fangyu asc; 78 +------+-----------+--------+--------+------+---------+ 79 | id | name | gongji | fangyu | sex | country | 80 +------+-----------+--------+--------+------+---------+ 81 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 82 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 83 +------+-----------+--------+--------+------+---------+ 84 2 rows in set (0.04 sec) 85 86 mysql>
3、limit(永远放在SQL语句的最后写)
1、作用：限制显示查询记录的条数
2、用法
1、limit n -->显示几条记录
2、limit m,n
m --> 从第几条记录开始显示,n表示显示几条
## m的值是从0开始计数的, 如果m=3则从第四条记录开始
limit 1,3 --> 显示 2、3、4 三条记录，从2开始显示3条记录；
3、练习
1、查找防御值倒数第二名至倒数第四名的蜀国英雄的记录
2、查找攻击值前三名且名字不为空值的蜀国英雄的姓名,攻 击值和国家
1 mysql> select * from sanguo; 2 +------+-----------+--------+--------+------+---------+ 3 | id | name | gongji | fangyu | sex | country | 4 +------+-----------+--------+--------+------+---------+ 5 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 6 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 7 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 8 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 9 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 10 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 11 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 12 | 8 | | 1005 | 88 | 女 | 蜀国 | 13 +------+-----------+--------+--------+------+---------+ 14 8 rows in set (0.00 sec) 15 16 mysql> select * from sanguo 17 -> where 18 -> country='蜀国' order by fangyu 19 -> limit 1,3; 20 +------+--------+--------+--------+------+---------+ 21 | id | name | gongji | fangyu | sex | country | 22 +------+--------+--------+--------+------+---------+ 23 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 24 | 8 | | 1005 | 88 | 女 | 蜀国 | 25 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 26 +------+--------+--------+--------+------+---------+ 27 3 rows in set (0.00 sec) 28 29 mysql> select * from sanguo order by fangyu limit 1,3; 30 +------+-----------+--------+--------+------+---------+ 31 | id | name | gongji | fangyu | sex | country | 32 +------+-----------+--------+--------+------+---------+ 33 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 34 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 35 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 36 +------+-----------+--------+--------+------+---------+ 37 3 rows in set (0.00 sec) 38 39 mysql> select * from sanguo 40 -> where 41 -> \c 42 mysql> select name,gongji,country from sanguo 43 -> where 44 -> name is null and country='蜀国' 45 -> order by gongji desc limit 3; 46 +------+--------+---------+ 47 | name | gongji | country | 48 +------+--------+---------+ 49 | NULL | 1000 | 蜀国 | 50 +------+--------+---------+ 51 1 row in set (0.03 sec) 52 53 mysql> select * from sanguo; 54 +------+-----------+--------+--------+------+---------+ 55 | id | name | gongji | fangyu | sex | country | 56 +------+-----------+--------+--------+------+---------+ 57 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 58 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 59 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 60 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 61 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 62 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 63 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 64 | 8 | | 1005 | 88 | 女 | 蜀国 | 65 +------+-----------+--------+--------+------+---------+ 66 8 rows in set (0.00 sec) 67 68 mysql> select name,gongji,country from sanguo where name is null and country='蜀国' order by gongji desc limit 3; 69 +------+--------+---------+ 70 | name | gongji | country | 71 +------+--------+---------+ 72 | NULL | 1000 | 蜀国 | 73 +------+--------+---------+ 74 1 row in set (0.00 sec) 75 76 mysql> select name,gongji,country from sanguo where name is null and country='蜀国' order by gongji desc limit 0,3; 77 +------+--------+---------+ 78 | name | gongji | country | 79 +------+--------+---------+ 80 | NULL | 1000 | 蜀国 | 81 +------+--------+---------+ 82 1 row in set (0.00 sec) 83 84 mysql> select name,gongji,country from sanguo where name is null and country='蜀国' order by gongji desc; 85 +------+--------+---------+ 86 | name | gongji | country | 87 +------+--------+---------+ 88 | NULL | 1000 | 蜀国 | 89 +------+--------+---------+ 90 1 row in set (0.05 sec) 91 92 mysql> select name,gongji,country from sanguo where name is not null and country='蜀国' order by gongji desc limit 0,3; 93 +-----------+--------+---------+ 94 | name | gongji | country | 95 +-----------+--------+---------+ 96 | | 1005 | 蜀国 | 97 | 关羽 | 188 | 蜀国 | 98 | 诸葛亮 | 120 | 蜀国 | 99 +-----------+--------+---------+ 100 3 rows in set (0.00 sec) 101 102 mysql>
4、聚合函数
1、分类
1、avg(字段名) : 求字段的平均值
2、sum(字段名) : 求字段的和
3、max(字段名) : 求字段的最大值
4、min(字段名) : 求字段的最小值
5、count(字段名):统计该字段记录的个数
2、练习
1、攻击力最强值是多少
2、统计一下表中id,name字段分别有多少条记录
## 空值NULL不会被count统计,""可以被统计
3、计算蜀国英雄的总攻击力
1 mysql> select * from sanguo; 2 +------+-----------+--------+--------+------+---------+ 3 | id | name | gongji | fangyu | sex | country | 4 +------+-----------+--------+--------+------+---------+ 5 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 6 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 7 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 8 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 9 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 10 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 11 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 12 | 8 | | 1005 | 88 | 女 | 蜀国 | 13 +------+-----------+--------+--------+------+---------+ 14 8 rows in set (0.00 sec) 15 16 mysql> select max(gongji) from sanguo; 17 +-------------+ 18 | max(gongji) | 19 +-------------+ 20 | 1005 | 21 +-------------+ 22 1 row in set (0.45 sec) 23 24 mysql> 攻击力最强值\c 25 mysql> 26 mysql> select max(gongji) as aa from sanguo; 27 +------+ 28 | aa | 29 +------+ 30 | 1005 | 31 +------+ 32 1 row in set (0.00 sec) 33 34 mysql> 别名aa \c 35 mysql> 36 mysql> 统计下ｉｄ和ｎａｍｅ有多少条记录\c 37 mysql> 38 mysql> select count(id) as c_id,count(name) as c_name from sanguo; 39 +------+--------+ 40 | c_id | c_name | 41 +------+--------+ 42 | 8 | 7 | 43 +------+--------+ 44 1 row in set (0.03 sec) 45 46 mysql> select * from sanguo; 47 +------+-----------+--------+--------+------+---------+ 48 | id | name | gongji | fangyu | sex | country | 49 +------+-----------+--------+--------+------+---------+ 50 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 51 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 52 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 53 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 54 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 55 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 56 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 57 | 8 | | 1005 | 88 | 女 | 蜀国 | 58 +------+-----------+--------+--------+------+---------+ 59 8 rows in set (0.00 sec) 60 61 mysql> NULL不会被统计进去 \c 62 mysql> 63 mysql> 计算蜀国英雄的总攻击力\c 64 mysql> select sum(gongji) from sanguo 65 -> where 66 -> country='蜀国'; 67 +-------------+ 68 | sum(gongji) | 69 +-------------+ 70 | 2313 | 71 +-------------+ 72 1 row in set (0.07 sec) 73 74 mysql> 统计蜀国英雄中攻击值大于２００的英雄的数量\c 75 mysql> select count(name) from sanguo 76 -> where 77 -> country='蜀国' and gongji > 200; 78 +-------------+ 79 | count(name) | 80 +-------------+ 81 | 1 | 82 +-------------+ 83 1 row in set (0.00 sec) 84 85 mysql> select count(*) from sanguo 86 -> where 87 -> country='蜀国' and gongji > 200; 88 +----------+ 89 | count(*) | 90 +----------+ 91 | 2 | 92 +----------+ 93 1 row in set (0.01 sec) 94 95 mysql> select * from sanguo; 96 +------+-----------+--------+--------+------+---------+ 97 | id | name | gongji | fangyu | sex | country | 98 +------+-----------+--------+--------+------+---------+ 99 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 100 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 101 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 102 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 103 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 104 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 105 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 106 | 8 | | 1005 | 88 | 女 | 蜀国 | 107 +------+-----------+--------+--------+------+---------+ 108 8 rows in set (0.00 sec) 109 110 mysql>
5、group by
1、作用 ：给查询的结果进行分组; (去重)
2、练习
1、查询sanguo表中一共有几个国家
2、计算所有国家的平均攻击力
3、查找所有国家中 英雄数量最多的 前2名 的国家的名称及英雄数量
3、注意
1、group by之后的字段必须要为select之后的字段；
2、如果select之后的字段和group by之后的字段不一致,则必须要对select之后的该字段值做聚合处理；
select country,avg(gongji) from sanguo
group by country;
1 mysql> select * from sanguo; 2 +------+-----------+--------+--------+------+---------+ 3 | id | name | gongji | fangyu | sex | country | 4 +------+-----------+--------+--------+------+---------+ 5 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 6 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 7 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 8 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 9 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 10 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 11 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 12 | 8 | | 1005 | 88 | 女 | 蜀国 | 13 +------+-----------+--------+--------+------+---------+ 14 8 rows in set (0.00 sec) 15 16 mysql> 几个国家 17 -> \c 18 mysql> select country from sanguo by country; 19 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'by country' at line 1 20 mysql> select country from sanguo group by country; 21 +---------+ 22 | country | 23 +---------+ 24 | 吴国 | 25 | 蜀国 | 26 | 魏国 | 27 +---------+ 28 3 rows in set (0.09 sec) 29 30 mysql> select name,country from sanguo group by country; 31 ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'MoShou.sanguo.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 32 mysql> 计算所有国家的攻击力\c 33 mysql> 平均\c 34 mysql> select country,avg(gongji) from sanguo 35 -> group by country; 36 +---------+-------------+ 37 | country | avg(gongji) | 38 +---------+-------------+ 39 | 吴国 | 100.0000 | 40 | 蜀国 | 578.2500 | 41 | 魏国 | 483.6667 | 42 +---------+-------------+ 43 3 rows in set (0.14 sec) 44 45 mysql> select country,count(name) from sanguo 46 -> group by country order by desc limit 2; 47 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc limit 2' at line 2 48 mysql> select country,count(name) from sanguo group by country order by desc, limit 2; 49 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc, limit 2' at line 1 50 mysql> select country,count(*) from sanguo group by country order by desc, limit 2; 51 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc, limit 2' at line 1 52 mysql> select country,count(*) from sanguo group by country order by desc limit 2; 53 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc limit 2' at line 1 54 mysql> select country,count(*) from sanguo 55 -> group by country 56 -> order by count(*) desc 57 -> limit 2; 58 +---------+----------+ 59 | country | count(*) | 60 +---------+----------+ 61 | 蜀国 | 4 | 62 | 魏国 | 3 | 63 +---------+----------+ 64 2 rows in set (0.00 sec) 65 66 mysql> select country,count(*) from sanguo group by country order by count(*) desc limit 2; 67 +---------+----------+ 68 | country | count(*) | 69 +---------+----------+ 70 | 蜀国 | 4 | 71 | 魏国 | 3 | 72 +---------+----------+ 73 2 rows in set (0.00 sec) 74 75 mysql>
6、having
1、作用：对查询的结果进行进一步筛选；
2、练习
1、找出平均攻击力大于105的国家的前两名，显示国家名和平均攻击力
3、注意
1、having语句通常与group by语句联合使用,用来过滤由group by语句返回的记录集
2、having语句的存在弥补了where关键字不能与聚合函数联合使用的不足,having操作的是聚合函数生成的显示列
1 mysql> 平均攻击力> 105\c 2 mysql> 3 mysql> select country,avg(gongji) from sanguo 4 -> group by country 5 -> having avg(gongji) > 105 6 -> order by avg(gongji) desc 7 -> limit 2; 8 +---------+-------------+ 9 | country | avg(gongji) | 10 +---------+-------------+ 11 | 蜀国 | 578.2500 | 12 | 魏国 | 483.6667 | 13 +---------+-------------+ 14 2 rows in set (0.01 sec) 15 16 mysql>
7、distinct
1、作用：不显示字段的重复值
2、练习
1、sanguo表中一共有多少个国家
2、计算蜀国一共有多少个英雄
3、注意
1、distinct处理的是distinct和from之间的所有字段,所有字段值必须全部相同才能去重；
2、distinct不能对任何字段做聚合处理；
8、查询表记录时做数学运算
1、运算符 ：+ - * / %
2、练习
1、查询时显示所有英雄的攻击力 *10
2、查询时显示所有英雄的防御力 +5
1 mysql> select * from sanguo; 2 +------+-----------+--------+--------+------+---------+ 3 | id | name | gongji | fangyu | sex | country | 4 +------+-----------+--------+--------+------+---------+ 5 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 6 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 7 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 8 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 9 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 10 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 11 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 12 | 8 | | 1005 | 88 | 女 | 蜀国 | 13 +------+-----------+--------+--------+------+---------+ 14 8 rows in set (0.77 sec) 15 16 mysql> select country,avg(gongji) from sanguo 17 -> group by country 18 -> having avg(gongji) > 105 19 -> order by avg(gongji) desc 20 -> limit 2; 21 +---------+-------------+ 22 | country | avg(gongji) | 23 +---------+-------------+ 24 | 蜀国 | 578.2500 | 25 | 魏国 | 483.6667 | 26 +---------+-------------+ 27 2 rows in set (0.24 sec) 28 29 mysql> 平均攻击力> 105\c 30 mysql> 31 mysql> select country,avg(gongji) from sanguo 32 -> group by country 33 -> having avg(gongji) > 105 34 -> order by avg(gongji) desc 35 -> limit 2; 36 +---------+-------------+ 37 | country | avg(gongji) | 38 +---------+-------------+ 39 | 蜀国 | 578.2500 | 40 | 魏国 | 483.6667 | 41 +---------+-------------+ 42 2 rows in set (0.01 sec) 43 44 mysql> 45 mysql> 三国表中一共多少个国家\c 46 mysql> 47 mysql> select country from sanguo group by country; 48 +---------+ 49 | country | 50 +---------+ 51 | 吴国 | 52 | 蜀国 | 53 | 魏国 | 54 +---------+ 55 3 rows in set (0.00 sec) 56 57 mysql> select distinct country from sanguo; 58 +---------+ 59 | country | 60 +---------+ 61 | 蜀国 | 62 | 魏国 | 63 | 吴国 | 64 +---------+ 65 3 rows in set (0.00 sec) 66 67 mysql> select distinct country,name from sanguo; 68 +---------+-----------+ 69 | country | name | 70 +---------+-----------+ 71 | 蜀国 | 诸葛亮 | 72 | 魏国 | 司马懿 | 73 | 蜀国 | 关羽 | 74 | 魏国 | 赵云 | 75 | 吴国 | 孙权 | 76 | 魏国 | 貂蝉 | 77 | 蜀国 | NULL | 78 | 蜀国 | | 79 +---------+-----------+ 80 8 rows in set (0.00 sec) 81 82 mysql> 计算蜀国有多少个英雄\c 83 mysql> 84 mysql> select count(distinct name) from sanguo 85 -> where 86 -> country='蜀国'; 87 +----------------------+ 88 | count(distinct name) | 89 +----------------------+ 90 | 3 | 91 +----------------------+ 92 1 row in set (0.04 sec) 93 94 mysql> select count(distinct name) from sanguo; 95 +----------------------+ 96 | count(distinct name) | 97 +----------------------+ 98 | 7 | 99 +----------------------+ 100 1 row in set (0.00 sec) 101 102 mysql> select country(distinct id) from sanguo where country='蜀国'; 103 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct id) from sanguo where country='蜀国'' at line 1 104 mysql> select count(distinct id) from sanguo where country='蜀国'; 105 +--------------------+ 106 | count(distinct id) | 107 +--------------------+ 108 | 4 | 109 +--------------------+ 110 1 row in set (0.00 sec) 111 112 mysql> 显示攻击力×１０\c 113 mysql> 114 mysql> select id,name,gongji*10 as xgjl from sanguo; 115 +------+-----------+-------+ 116 | id | name | xgjl | 117 +------+-----------+-------+ 118 | 1 | 诸葛亮 | 1200 | 119 | 2 | 司马懿 | 1190 | 120 | 3 | 关羽 | 1880 | 121 | 4 | 赵云 | 6660 | 122 | 5 | 孙权 | 1000 | 123 | 6 | 貂蝉 | 6660 | 124 | 7 | NULL | 10000 | 125 | 8 | | 10050 | 126 +------+-----------+-------+ 127 8 rows in set (0.03 sec) 128 129 mysql> 防御力＋５\c 130 mysql> 131 mysql> select id,name,fangyu+5 as xfyl from sanguo; 132 +------+-----------+------+ 133 | id | name | xfyl | 134 +------+-----------+------+ 135 | 1 | 诸葛亮 | 25 | 136 | 2 | 司马懿 | 30 | 137 | 3 | 关羽 | 65 | 138 | 4 | 赵云 | 93 | 139 | 5 | 孙权 | 65 | 140 | 6 | 貂蝉 | 15 | 141 | 7 | NULL | 104 | 142 | 8 | | 93 | 143 +------+-----------+------+ 144 8 rows in set (0.01 sec) 145 146 mysql> select * from sanguo; 147 +------+-----------+--------+--------+------+---------+ 148 | id | name | gongji | fangyu | sex | country | 149 +------+-----------+--------+--------+------+---------+ 150 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 | 151 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 | 152 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 | 153 | 4 | 赵云 | 666 | 88 | 男 | 魏国 | 154 | 5 | 孙权 | 100 | 60 | 男 | 吴国 | 155 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 | 156 | 7 | NULL | 1000 | 99 | 男 | 蜀国 | 157 | 8 | | 1005 | 88 | 女 | 蜀国 | 158 +------+-----------+--------+--------+------+---------+ 159 8 rows in set (0.00 sec) 160 161 mysql>
约束
1、作用
为了保证数据的完整性、一致性、有效性
2、约束分类
1、默认约束(default)
1、作用
在插入记录时,如果不给该字段赋值,则使用默认值
2、格式
字段名 数据类型 default 值,
2、非空约束(not null)
1、作用 ：不允许将该字段设置为NULL
2、格式 ：字段名 数据类型 not null
1 mysql> create table t1( 2 -> id int(3) zerofill, 3 -> name char(20), 4 -> sex enum('F','M','Secret') default 'Secret', 5 -> age tinyint unsigned 6 -> )default charset=utf8; 7 Query OK, 0 rows affected (2.36 sec) 8 9 mysql> desc t1; 10 +-------+--------------------------+------+-----+---------+-------+ 11 | Field | Type | Null | Key | Default | Extra | 12 +-------+--------------------------+------+-----+---------+-------+ 13 | id | int(3) unsigned zerofill | YES | | NULL | | 14 | name | char(20) | YES | | NULL | | 15 | sex | enum('F','M','Secret') | YES | | Secret | | 16 | age | tinyint(3) unsigned | YES | | NULL | | 17 +-------+--------------------------+------+-----+---------+-------+ 18 4 rows in set (0.20 sec) 19 20 mysql> insert into t1(id,name) values(1,'Lucy'); 21 Query OK, 1 row affected (0.38 sec) 22 23 mysql> select * from t1; 24 +------+------+--------+------+ 25 | id | name | sex | age | 26 +------+------+--------+------+ 27 | 001 | Lucy | Secret | NULL | 28 +------+------+--------+------+ 29 1 row in set (0.00 sec) 30 31 mysql> create table t2( 32 -> id int, 33 -> name varchar(15) not null default '单挑王' 34 -> )default charset=utf8; 35 Query OK, 0 rows affected (0.56 sec) 36 37 mysql> desc t2; 38 +-------+-------------+------+-----+-----------+-------+ 39 | Field | Type | Null | Key | Default | Extra | 40 +-------+-------------+------+-----+-----------+-------+ 41 | id | int(11) | YES | | NULL | | 42 | name | varchar(15) | NO | | 单挑王 | | 43 +-------+-------------+------+-----+-----------+-------+ 44 2 rows in set (0.09 sec) 45 46 mysql> insert into t2(id) values(1); 47 Query OK, 1 row affected (0.07 sec) 48 49 mysql> select * from t2; 50 +------+-----------+ 51 | id | name | 52 +------+-----------+ 53 | 1 | 单挑王 | 54 +------+-----------+ 55 1 row in set (0.00 sec) 56 57 mysql> create table t3( 58 -> id int not null, 59 -> name varchar(15) 60 -> ); 61 Query OK, 0 rows affected (0.44 sec) 62 63 mysql> insert into t3(name) values('Bob'); 64 ERROR 1364 (HY000): Field 'id' doesn't have a default value 65 mysql>
索引
1、定义
对数据库中表的一列或者多列的值进行排序的一种结构(MySQL中用Btree方式)
2、优点
加快数据的检索速度
3、缺点
1、当对表中的数据进行增加、删除或修改的时候,索引也到动态维护,降低了数据的维护速度
2、索引需要占用物理空间
4、索引分类
1、普通索引(index)
1、使用规则
1、一个表中可以有多个index字段
2、字段的值可以有重复,且可以为NULL值
3、经常把做查询条件的字段设置为index字段
4、index字段的key标志是MUL
2、创建index
1、创建表时创建
index(字段名1),index(字段名2)
2、在已有表中创建index
1、语法
create index 索引名 on 表名(字段名);
2、注意
索引名一般和字段名一样
3、查看普通索引
1、desc 表名; -->查看key标志
2、show index from 表名;
4、删除索引
drop index 索引名 on 表名;
1 mysql> create table t4( 2 -> id int, 3 -> name char(20), 4 -> age tinyint unsigned, 5 -> index(id), 6 -> index(name) 7 -> ); 8 Query OK, 0 rows affected (0.50 sec) 9 10 mysql> desc t4; 11 +-------+---------------------+------+-----+---------+-------+ 12 | Field | Type | Null | Key | Default | Extra | 13 +-------+---------------------+------+-----+---------+-------+ 14 | id | int(11) | YES | MUL | NULL | | 15 | name | char(20) | YES | MUL | NULL | | 16 | age | tinyint(3) unsigned | YES | | NULL | | 17 +-------+---------------------+------+-----+---------+-------+ 18 3 rows in set (0.08 sec) 19 20 mysql> create index age on t4(age); 21 Query OK, 0 rows affected (1.37 sec) 22 Records: 0 Duplicates: 0 Warnings: 0 23 24 mysql> desc t4; 25 +-------+---------------------+------+-----+---------+-------+ 26 | Field | Type | Null | Key | Default | Extra | 27 +-------+---------------------+------+-----+---------+-------+ 28 | id | int(11) | YES | MUL | NULL | | 29 | name | char(20) | YES | MUL | NULL | | 30 | age | tinyint(3) unsigned | YES | MUL | NULL | | 31 +-------+---------------------+------+-----+---------+-------+ 32 3 rows in set (0.04 sec) 33 34 mysql> show index from t4; 35 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 36 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | 37 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 38 | t4 | 1 | id | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | | 39 | t4 | 1 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | | 40 | t4 | 1 | age | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | | 41 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 42 3 rows in set (0.04 sec) 43 44 mysql> show index from t4\G; 45 *************************** 1. row *************************** 46 Table: t4 47 Non_unique: 1 48 Key_name: id 49 Seq_in_index: 1 50 Column_name: id 51 Collation: A 52 Cardinality: 0 53 Sub_part: NULL 54 Packed: NULL 55 Null: YES 56 Index_type: BTREE 57 Comment: 58 Index_comment: 59 *************************** 2. row *************************** 60 Table: t4 61 Non_unique: 1 62 Key_name: name 63 Seq_in_index: 1 64 Column_name: name 65 Collation: A 66 Cardinality: 0 67 Sub_part: NULL 68 Packed: NULL 69 Null: YES 70 Index_type: BTREE 71 Comment: 72 Index_comment: 73 *************************** 3. row *************************** 74 Table: t4 75 Non_unique: 1 76 Key_name: age 77 Seq_in_index: 1 78 Column_name: age 79 Collation: A 80 Cardinality: 0 81 Sub_part: NULL 82 Packed: NULL 83 Null: YES 84 Index_type: BTREE 85 Comment: 86 Index_comment: 87 3 rows in set (0.00 sec) 88 89 ERROR: 90 No query specified 91 92 mysql> desc t4; 93 +-------+---------------------+------+-----+---------+-------+ 94 | Field | Type | Null | Key | Default | Extra | 95 +-------+---------------------+------+-----+---------+-------+ 96 | id | int(11) | YES | MUL | NULL | | 97 | name | char(20) | YES | MUL | NULL | | 98 | age | tinyint(3) unsigned | YES | MUL | NULL | | 99 +-------+---------------------+------+-----+---------+-------+ 100 3 rows in set (0.00 sec) 101 102 mysql> drop index id on t4; 103 Query OK, 0 rows affected (0.19 sec) 104 Records: 0 Duplicates: 0 Warnings: 0 105 106 mysql> desc t4; 107 +-------+---------------------+------+-----+---------+-------+ 108 | Field | Type | Null | Key | Default | Extra | 109 +-------+---------------------+------+-----+---------+-------+ 110 | id | int(11) | YES | | NULL | | 111 | name | char(20) | YES | MUL | NULL | | 112 | age | tinyint(3) unsigned | YES | MUL | NULL | | 113 +-------+---------------------+------+-----+---------+-------+ 114 3 rows in set (0.05 sec) 115 116 mysql> drop index name on t4; 117 Query OK, 0 rows affected (0.18 sec) 118 Records: 0 Duplicates: 0 Warnings: 0 119 120 mysql> drop index age on t4; 121 Query OK, 0 rows affected (0.31 sec) 122 Records: 0 Duplicates: 0 Warnings: 0 123 124 mysql> show index from t4; 125 Empty set (0.00 sec) 126 127 mysql>
2、唯一索引(unique key)
1、使用规则
1、一个表中可以有多个unique字段
2、unique字段的值不允许重复,但可以为NULL
3、unique的key标志是UNI
2、创建唯一索引unique
1、创建表时创建
1、unique(字段名1),unique(字段名2)
2、字段名 数据类型 unique,
2、在已有表中创建
create unique index 索引名 on 表名;
3、删除唯一索引
drop index 索引名 on 表名;
注意：index、unique在删除时只能一个一个删
1 mysql> create table t5( 2 -> id int, 3 -> name varchar(15), 4 -> number char(11) unique 5 -> ); 6 Query OK, 0 rows affected (0.56 sec) 7 8 mysql> desc t5; 9 +--------+-------------+------+-----+---------+-------+ 10 | Field | Type | Null | Key | Default | Extra | 11 +--------+-------------+------+-----+---------+-------+ 12 | id | int(11) | YES | | NULL | | 13 | name | varchar(15) | YES | | NULL | | 14 | number | char(11) | YES | UNI | NULL | | 15 +--------+-------------+------+-----+---------+-------+ 16 3 rows in set (0.06 sec) 17 18 mysql> create unique index id on t5(id); 19 Query OK, 0 rows affected (0.16 sec) 20 Records: 0 Duplicates: 0 Warnings: 0 21 22 mysql> desc t5; 23 +--------+-------------+------+-----+---------+-------+ 24 | Field | Type | Null | Key | Default | Extra | 25 +--------+-------------+------+-----+---------+-------+ 26 | id | int(11) | YES | UNI | NULL | | 27 | name | varchar(15) | YES | | NULL | | 28 | number | char(11) | YES | UNI | NULL | | 29 +--------+-------------+------+-----+---------+-------+ 30 3 rows in set (0.00 sec) 31 32 mysql> show index from t5\G; 33 *************************** 1. row *************************** 34 Table: t5 35 Non_unique: 0 36 Key_name: number 37 Seq_in_index: 1 38 Column_name: number 39 Collation: A 40 Cardinality: 0 41 Sub_part: NULL 42 Packed: NULL 43 Null: YES 44 Index_type: BTREE 45 Comment: 46 Index_comment: 47 *************************** 2. row *************************** 48 Table: t5 49 Non_unique: 0 50 Key_name: id 51 Seq_in_index: 1 52 Column_name: id 53 Collation: A 54 Cardinality: 0 55 Sub_part: NULL 56 Packed: NULL 57 Null: YES 58 Index_type: BTREE 59 Comment: 60 Index_comment: 61 2 rows in set (0.00 sec) 62 63 ERROR: 64 No query specified 65 66 mysql> desc t5; 67 +--------+-------------+------+-----+---------+-------+ 68 | Field | Type | Null | Key | Default | Extra | 69 +--------+-------------+------+-----+---------+-------+ 70 | id | int(11) | YES | UNI | NULL | | 71 | name | varchar(15) | YES | | NULL | | 72 | number | char(11) | YES | UNI | NULL | | 73 +--------+-------------+------+-----+---------+-------+ 74 3 rows in set (0.00 sec) 75 76 mysql> drop index id on t5; 77 Query OK, 0 rows affected (0.39 sec) 78 Records: 0 Duplicates: 0 Warnings: 0 79 80 mysql> desc t5; 81 +--------+-------------+------+-----+---------+-------+ 82 | Field | Type | Null | Key | Default | Extra | 83 +--------+-------------+------+-----+---------+-------+ 84 | id | int(11) | YES | | NULL | | 85 | name | varchar(15) | YES | | NULL | | 86 | number | char(11) | YES | UNI | NULL | | 87 +--------+-------------+------+-----+---------+-------+ 88 3 rows in set (0.08 sec) 89 90 mysql>
练习1
1 有一张文章评论表comment如下 2 comment_id(评论) article_id(文章) user_id date 3 1 10000 10000 2018-01-30 09:00:00 4 2 10001 10001 ... ... 5 3 10002 10000 ... ... 6 4 10003 10015 ... ... 7 5 10004 10006 ... ... 8 6 10025 10006 ... ... 9 7 10009 10000 ... ... 10 以上是一个应用的comment表格的一部分，请使用SQL语句找出在本站发表的所有评论数量最多的10位用户及评论数，并按评论数从高到低排序 11 备注：comment_id为评论id 12 article_id为被评论文章的id 13 user_id指用户id 14 答案: 15 select user_id,count(*) as c from comment 16 group by user_id order by c desc limit 10;
