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、删除所有泰国人
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (1.52 sec) mysql> use db2
Database changed
mysql> show tables;
Empty set (0.01 sec) mysql> create table t1(
-> id int(3) zerofill,
-> name varchar(15),
-> age tinyint unsigned,
-> address char(10)
-> )default charset=utf8;
Query OK, 0 rows affected (1.12 sec) mysql> insert into t1 values
-> (1,'诸葛亮',33,'北京'),
-> (2,'司马懿',34,'上海')，
-> (3,'赵子龙',30,'北京');
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 4
mysql> insert into t1 values (1,'诸葛亮',33,'北京'), (2,'司马懿',34,'上海')， (3,'赵子龙',30,'北京');
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
mysql> insert into t1 values (1,'诸葛亮',33,'北京'), (2,'司马懿',34,'上海')， (3,'赵子龙',30,'北京');
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
mysql> insert into t1 values (1,'诸葛亮',33,'北京'), (2,'司马懿',34,'上海'), (3,'赵子龙',30,'北京');
Query OK, 3 rows affected (0.34 sec)
Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t1 where address='北京';
+------+-----------+------+---------+
| id | name | age | address |
+------+-----------+------+---------+
| 001 | 诸葛亮 | 33 | 北京 |
| 003 | 赵子龙 | 30 | 北京 |
+------+-----------+------+---------+
2 rows in set (0.08 sec) mysql> select * from t1 where id=1;
+------+-----------+------+---------+
| id | name | age | address |
+------+-----------+------+---------+
| 001 | 诸葛亮 | 33 | 北京 |
+------+-----------+------+---------+
1 row in set (0.03 sec) mysql> update t1 set address='上海'
-> where
-> name='赵子龙';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t1;
+------+-----------+------+---------+
| id | name | age | address |
+------+-----------+------+---------+
| 001 | 诸葛亮 | 33 | 北京 |
| 002 | 司马懿 | 34 | 上海 |
| 003 | 赵子龙 | 30 | 上海 |
+------+-----------+------+---------+
3 rows in set (0.02 sec) mysql> update t1 set name='张飞',age=88
-> where
-> id=1;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t1;
+------+-----------+------+---------+
| id | name | age | address |
+------+-----------+------+---------+
| 001 | 张飞 | 88 | 北京 |
| 002 | 司马懿 | 34 | 上海 |
| 003 | 赵子龙 | 30 | 上海 |
+------+-----------+------+---------+
3 rows in set (0.00 sec) mysql> update t1 set name='赵云'
-> where
-> id=3;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t1;
+------+-----------+------+---------+
| id | name | age | address |
+------+-----------+------+---------+
| 001 | 张飞 | 88 | 北京 |
| 002 | 司马懿 | 34 | 上海 |
| 003 | 赵云 | 30 | 上海 |
+------+-----------+------+---------+
3 rows in set (0.00 sec) mysql> update t1 set name='赵云';
Query OK, 2 rows affected (0.03 sec)
Rows matched: 3 Changed: 2 Warnings: 0 mysql> select * from t1;
+------+--------+------+---------+
| id | name | age | address |
+------+--------+------+---------+
| 001 | 赵云 | 88 | 北京 |
| 002 | 赵云 | 34 | 上海 |
| 003 | 赵云 | 30 | 上海 |
+------+--------+------+---------+
3 rows in set (0.00 sec) mysql> delete from t1 where id=2;
Query OK, 1 row affected (0.06 sec) mysql> select * from t1;
+------+--------+------+---------+
| id | name | age | address |
+------+--------+------+---------+
| 001 | 赵云 | 88 | 北京 |
| 003 | 赵云 | 30 | 上海 |
+------+--------+------+---------+
2 rows in set (0.00 sec) mysql> delete from t1;
Query OK, 2 rows affected (0.04 sec) mysql> select * from t1;
Empty set (0.00 sec) mysql>
第一步：创建库 SANGUO
create database SANGUO; 第二步：切换库
use SANGUO; 第三步：创建表sanguo
create table sanguo(
id int,
name char(15),
sex enum("男","女"),
country char(10)
)default charset=utf8; 第四步：插入记录
insert into sanguo values
(1,"曹操","男","魏国"),
(2,"小乔","女","吴国"),
(3,"诸葛亮","男","蜀国"),
(4,"貂蝉","女","东汉"),
(5,"赵子龙","男","蜀国"),
(6,"魏延","男","蜀国"); ######
1、创建库MoShou
create database MoShou; 2、切换库
use MoShou; 3、创建表sanguo
create table sanguo(
id int,
name char(20),
gongji int,
fangyu tinyint unsigned,
sex enum("男","女"),
country varchar(20)
)default charset=utf8; 4、在表中插入记录
insert into sanguo values
(1,'诸葛亮',120,20,'男','蜀国'),
(2,'司马懿',119,25,'男','魏国'),
(3,'关羽',188,60,'男','蜀国'),
(4,'赵云',200,66,'男','魏国'),
(5,'孙权',110,20,'男','吴国'),
(6,'貂蝉',666,10,'女','魏国'),
(7,null,1000,99,'男','蜀国'),
(8,'',1005,88,'女','蜀国'); ####
mysql> create database SANGUO;
Query OK, 1 row affected (0.20 sec) mysql> use SANGUO;
Database changed
mysql> create table sanguo(
-> id int,
-> name char(15),
-> sex enum("男","女"),
-> country char(10)
-> )default charset=utf8;
Query OK, 0 rows affected (0.16 sec) mysql> insert into sanguo values
-> (1,"曹操","男","魏国"),
-> (2,"小乔","女","吴国"),
-> (3,"诸葛亮","男","蜀国"),
-> (4,"貂蝉","女","东汉"),
-> (5,"赵子龙","男","蜀国"),
-> (6,"魏延","男","蜀国");
Query OK, 6 rows affected (0.07 sec)
Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from sanguo;
+------+-----------+------+---------+
| id | name | sex | country |
+------+-----------+------+---------+
| 1 | 曹操 | 男 | 魏国 |
| 2 | 小乔 | 女 | 吴国 |
| 3 | 诸葛亮 | 男 | 蜀国 |
| 4 | 貂蝉 | 女 | 东汉 |
| 5 | 赵子龙 | 男 | 蜀国 |
| 6 | 魏延 | 男 | 蜀国 |
+------+-----------+------+---------+
6 rows in set (0.00 sec) mysql> create database MoShou;
Query OK, 1 row affected (0.02 sec) mysql> use MoShou;
Database changed
mysql> create table sanguo(
-> id int,
-> name char(20),
-> gongji int,
-> fangyu tinyint unsigned,
-> sex enum("男","女"),
-> country varchar(20)
-> )default charset=utf8;
Query OK, 0 rows affected (0.20 sec) mysql> insert into sanguo values
-> (1,'诸葛亮',120,20,'男','蜀国'),
-> (2,'司马懿',119,25,'男','魏国'),
-> (3,'关羽',188,60,'男','蜀国'),
-> (4,'赵云',200,66,'男','魏国'),
-> (5,'孙权',110,20,'男','吴国'),
-> (6,'貂蝉',666,10,'女','魏国'),
-> (7,null,1000,99,'男','蜀国'),
-> (8,'',1005,88,'女','蜀国');
Query OK, 8 rows affected (0.09 sec)
Records: 8 Duplicates: 0 Warnings: 0 mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 200 | 66 | 男 | 魏国 |
| 5 | 孙权 | 110 | 20 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.00 sec) mysql> select database();
+------------+
| database() |
+------------+
| MoShou |
+------------+
1 row in set (0.00 sec) mysql> use SANGUO;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
mysql> show tables;
+------------------+
| Tables_in_SANGUO |
+------------------+
| sanguo |
+------------------+
1 row in set (0.00 sec) mysql> select * from sanguo;
+------+-----------+------+---------+
| id | name | sex | country |
+------+-----------+------+---------+
| 1 | 曹操 | 男 | 魏国 |
| 2 | 小乔 | 女 | 吴国 |
| 3 | 诸葛亮 | 男 | 蜀国 |
| 4 | 貂蝉 | 女 | 东汉 |
| 5 | 赵子龙 | 男 | 蜀国 |
| 6 | 魏延 | 男 | 蜀国 |
+------+-----------+------+---------+
6 rows in set (0.00 sec) mysql> use SANGUO;
Database changed
mysql> show tables;
+------------------+
| Tables_in_SANGUO |
+------------------+
| sanguo |
+------------------+
1 row in set (0.01 sec) mysql> select * from sanguo;
+------+-----------+------+---------+
| id | name | sex | country |
+------+-----------+------+---------+
| 1 | 曹操 | 男 | 魏国 |
| 2 | 小乔 | 女 | 吴国 |
| 3 | 诸葛亮 | 男 | 蜀国 |
| 4 | 貂蝉 | 女 | 东汉 |
| 5 | 赵子龙 | 男 | 蜀国 |
| 6 | 魏延 | 男 | 蜀国 |
+------+-----------+------+---------+
6 rows in set (0.00 sec) mysql> select * from sanguo where country='蜀国';
+------+-----------+------+---------+
| id | name | sex | country |
+------+-----------+------+---------+
| 3 | 诸葛亮 | 男 | 蜀国 |
| 5 | 赵子龙 | 男 | 蜀国 |
| 6 | 魏延 | 男 | 蜀国 |
+------+-----------+------+---------+
3 rows in set (0.00 sec) mysql> select name,sex,country from sanguo where sex='女';
+--------+------+---------+
| name | sex | country |
+--------+------+---------+
| 小乔 | 女 | 吴国 |
| 貂蝉 | 女 | 东汉 |
+--------+------+---------+
2 rows in set (0.01 sec) mysql> update sanguo set
-> sex='女',country='泰国'
-> where
-> name='魏延';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from sanguo;
+------+-----------+------+---------+
| id | name | sex | country |
+------+-----------+------+---------+
| 1 | 曹操 | 男 | 魏国 |
| 2 | 小乔 | 女 | 吴国 |
| 3 | 诸葛亮 | 男 | 蜀国 |
| 4 | 貂蝉 | 女 | 东汉 |
| 5 | 赵子龙 | 男 | 蜀国 |
| 6 | 魏延 | 女 | 泰国 |
+------+-----------+------+---------+
6 rows in set (0.00 sec) mysql> update sanguo set
-> name='司马懿',sex='男',country='魏国'
-> where
-> id=2;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from sanguo;
+------+-----------+------+---------+
| id | name | sex | country |
+------+-----------+------+---------+
| 1 | 曹操 | 男 | 魏国 |
| 2 | 司马懿 | 男 | 魏国 |
| 3 | 诸葛亮 | 男 | 蜀国 |
| 4 | 貂蝉 | 女 | 东汉 |
| 5 | 赵子龙 | 男 | 蜀国 |
| 6 | 魏延 | 女 | 泰国 |
+------+-----------+------+---------+
6 rows in set (0.00 sec) mysql> delect from sanguo
-> where
-> country='泰国';
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
mysql> delect from sanguo where country='泰国';
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
mysql> delete from sanguo where country='泰国';
Query OK, 1 row affected (0.02 sec) mysql> select * from sanguo;
+------+-----------+------+---------+
| id | name | sex | country |
+------+-----------+------+---------+
| 1 | 曹操 | 男 | 魏国 |
| 2 | 司马懿 | 男 | 魏国 |
| 3 | 诸葛亮 | 男 | 蜀国 |
| 4 | 貂蝉 | 女 | 东汉 |
| 5 | 赵子龙 | 男 | 蜀国 |
+------+-----------+------+---------+
5 rows in set (0.00 sec) mysql>
运算符操作(配合查询、修改、删除操作)
1、数值比较&字符比较
1、数值比较运算符：=、!=、>、>=、<、<=
2、字符比较运算符：=、!=
3、语法格式
查询：
select * from 表名 where 字段名 运算符 数字/字符;
修改：
update 表名 set 字段名=值,... where 字段名 运算符 数字/字符;
删除：
delete from 表名 where 字段名 运算符 数字/字符;
4、练习
1、找出攻击值高于150的英雄的名字和攻击值
2、将赵云的攻击值改为666，防御值改为88
mysql> use MoShou;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A Database changed
mysql> show tables;
+------------------+
| Tables_in_MoShou |
+------------------+
| sanguo |
+------------------+
1 row in set (0.04 sec) mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 200 | 66 | 男 | 魏国 |
| 5 | 孙权 | 110 | 20 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.00 sec) mysql> select name,gongji from sanguo
-> where
-> gongji > 150;
+--------+--------+
| name | gongji |
+--------+--------+
| 关羽 | 188 |
| 赵云 | 200 |
| 貂蝉 | 666 |
| NULL | 1000 |
| | 1005 |
+--------+--------+
5 rows in set (0.01 sec) mysql> update sanguo set
-> gongji=666,fangyu=88
-> where
-> name='赵云';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 5 | 孙权 | 110 | 20 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.00 sec) mysql>
2、逻辑比较
1、运算符:
and(多个条件同时满足)
or(多个条件有一个条件满足就可以)
2、练习
1、找出攻击值大于200的蜀国英雄的名字及攻击值
2、将吴国英雄中攻击值为110的英雄的攻击值设置为100，防御值设置为60
3、查找蜀国和魏国的英雄信息
mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 5 | 孙权 | 110 | 20 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.00 sec) mysql> 1、找出攻击值大于200的蜀国英雄的名字及攻击值
-> 2、将吴国英雄中攻击值为110的英雄的攻击值设置为100，防御值设置为60
-> 3、查找蜀国和魏国的英雄信息\c
mysql> select name,gongji from sanguo
-> where
-> gongji > 200 and country='蜀国';
+------+--------+
| name | gongji |
+------+--------+
| NULL | 1000 |
| | 1005 |
+------+--------+
2 rows in set (0.07 sec) mysql> update sanguo set
-> gongji=100,fangyu=60
-> where
-> gongji=110 and country='吴国';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.00 sec) mysql> select * from sanguo where contry='吴国';
ERROR 1054 (42S22): Unknown column 'contry' in 'where clause'
mysql> select * from sanguo where country='吴国';
+------+--------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+--------+--------+--------+------+---------+
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
+------+--------+--------+--------+------+---------+
1 row in set (0.00 sec) mysql> select * from sanguo
-> where
-> country='蜀国' or country='魏国';
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
7 rows in set (0.01 sec) 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的蜀国英雄 和 貂蝉的编号、姓名和国家
mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.00 sec) mysql> select * from sanguo
-> where
-> gongji between 100 and 200 and country='蜀国';
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
+------+-----------+--------+--------+------+---------+
2 rows in set (0.05 sec) mysql> select * from sanguo
-> where
-> (gongji between 100 and 200) and (country='蜀国');
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
+------+-----------+--------+--------+------+---------+
2 rows in set (0.00 sec) mysql> select * from sanguo
-> where
-> in\c
mysql> select id,name from sanguo
-> where
-> id in(1,3,5,7);
+------+-----------+
| id | name |
+------+-----------+
| 1 | 诸葛亮 |
| 3 | 关羽 |
| 5 | 孙权 |
| 7 | NULL |
+------+-----------+
4 rows in set (0.05 sec) mysql> select * from sanguo
-> where
-> country not in('蜀国','吴国') and sex='女';
+------+--------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+--------+--------+--------+------+---------+
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
+------+--------+--------+--------+------+---------+
1 row in set (0.00 sec) mysql> select id,name,country from sanguo
-> where
-> id in(1,3,5) and country='蜀国' or name='貂蝉';
+------+-----------+---------+
| id | name | country |
+------+-----------+---------+
| 1 | 诸葛亮 | 蜀国 |
| 3 | 关羽 | 蜀国 |
| 6 | 貂蝉 | 魏国 |
+------+-----------+---------+
3 rows in set (0.00 sec) mysql>
4、匹配空、非空
1、空 ：is null
2、非空 ：is not null
3、练习
1、查找姓名为NULL的蜀国女英雄信息
2、查找姓名为 "" 的英雄的id,姓名和国家
4、注意
1、null ：空值,必须用is 或者 is not 去匹配
2、"" ：空字符串,用 = 或者 != 去匹配
mysql> select * from sanguo where name is null and country='蜀国' and sex='女';
Empty set (0.00 sec) mysql> select * from sanguo where name is null and country='蜀国';
+------+------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+------+--------+--------+------+---------+
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
+------+------+--------+--------+------+---------+
1 row in set (0.01 sec) mysql>
mysql>
mysql> select * from sanguo
-> where \c
mysql> select id,name,country from sanguo
-> where
-> name='';
+------+------+---------+
| id | name | country |
+------+------+---------+
| 8 | | 蜀国 |
+------+------+---------+
1 row in set (0.00 sec) 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 "赵%";
mysql> select id,name from sanguo
-> where
-> name like '_%_';
+------+-----------+
| id | name |
+------+-----------+
| 1 | 诸葛亮 |
| 2 | 司马懿 |
| 3 | 关羽 |
| 4 | 赵云 |
| 5 | 孙权 |
| 6 | 貂蝉 |
+------+-----------+
6 rows in set (0.04 sec) mysql> select id,name from sanguo
-> where
-> name like '%';
+------+-----------+
| id | name |
+------+-----------+
| 1 | 诸葛亮 |
| 2 | 司马懿 |
| 3 | 关羽 |
| 4 | 赵云 |
| 5 | 孙权 |
| 6 | 貂蝉 |
| 8 | |
+------+-----------+
7 rows in set (0.00 sec) mysql> select id,name from sanguo
-> where
-> name like '___';
+------+-----------+
| id | name |
+------+-----------+
| 1 | 诸葛亮 |
| 2 | 司马懿 |
+------+-----------+
2 rows in set (0.00 sec) mysql> select id,name from sanguo
-> where
-> name like '赵%';
+------+--------+
| id | name |
+------+--------+
| 4 | 赵云 |
+------+--------+
1 row in set (0.01 sec) 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、将魏蜀两国男英雄中名字为三个字的英雄按防御值升序 排列
mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.17 sec) mysql> select * from sanguo order by fangyu;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.16 sec) mysql> select * from sanguo order by fangyu ASC;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.04 sec) mysql> select * from sanguo order by gongji DESC;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 8 | | 1005 | 88 | 女 | 蜀国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.05 sec) mysql> select * from sanguo
-> where
-> country='蜀国' order by gongji desc;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 8 | | 1005 | 88 | 女 | 蜀国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
+------+-----------+--------+--------+------+---------+
4 rows in set (0.00 sec) mysql> select * from sanguo
-> where
-> country in('蜀国','魏国') and sex='男' and name like '___'
-> order by fangyu asc;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
+------+-----------+--------+--------+------+---------+
2 rows in set (0.04 sec) 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、查找攻击值前三名且名字不为空值的蜀国英雄的姓名,攻 击值和国家
mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.00 sec) mysql> select * from sanguo
-> where
-> country='蜀国' order by fangyu
-> limit 1,3;
+------+--------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+--------+--------+--------+------+---------+
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
+------+--------+--------+--------+------+---------+
3 rows in set (0.00 sec) mysql> select * from sanguo order by fangyu limit 1,3;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
+------+-----------+--------+--------+------+---------+
3 rows in set (0.00 sec) mysql> select * from sanguo
-> where
-> \c
mysql> select name,gongji,country from sanguo
-> where
-> name is null and country='蜀国'
-> order by gongji desc limit 3;
+------+--------+---------+
| name | gongji | country |
+------+--------+---------+
| NULL | 1000 | 蜀国 |
+------+--------+---------+
1 row in set (0.03 sec) mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.00 sec) mysql> select name,gongji,country from sanguo where name is null and country='蜀国' order by gongji desc limit 3;
+------+--------+---------+
| name | gongji | country |
+------+--------+---------+
| NULL | 1000 | 蜀国 |
+------+--------+---------+
1 row in set (0.00 sec) mysql> select name,gongji,country from sanguo where name is null and country='蜀国' order by gongji desc limit 0,3;
+------+--------+---------+
| name | gongji | country |
+------+--------+---------+
| NULL | 1000 | 蜀国 |
+------+--------+---------+
1 row in set (0.00 sec) mysql> select name,gongji,country from sanguo where name is null and country='蜀国' order by gongji desc;
+------+--------+---------+
| name | gongji | country |
+------+--------+---------+
| NULL | 1000 | 蜀国 |
+------+--------+---------+
1 row in set (0.05 sec) mysql> select name,gongji,country from sanguo where name is not null and country='蜀国' order by gongji desc limit 0,3;
+-----------+--------+---------+
| name | gongji | country |
+-----------+--------+---------+
| | 1005 | 蜀国 |
| 关羽 | 188 | 蜀国 |
| 诸葛亮 | 120 | 蜀国 |
+-----------+--------+---------+
3 rows in set (0.00 sec) mysql>
4、聚合函数
1、分类
1、avg(字段名) : 求字段的平均值
2、sum(字段名) : 求字段的和
3、max(字段名) : 求字段的最大值
4、min(字段名) : 求字段的最小值
5、count(字段名):统计该字段记录的个数
2、练习
1、攻击力最强值是多少
2、统计一下表中id,name字段分别有多少条记录
## 空值NULL不会被count统计,""可以被统计
3、计算蜀国英雄的总攻击力
mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.00 sec) mysql> select max(gongji) from sanguo;
+-------------+
| max(gongji) |
+-------------+
| 1005 |
+-------------+
1 row in set (0.45 sec) mysql> 攻击力最强值\c
mysql>
mysql> select max(gongji) as aa from sanguo;
+------+
| aa |
+------+
| 1005 |
+------+
1 row in set (0.00 sec) mysql> 别名aa \c
mysql>
mysql> 统计下ｉｄ和ｎａｍｅ有多少条记录\c
mysql>
mysql> select count(id) as c_id,count(name) as c_name from sanguo;
+------+--------+
| c_id | c_name |
+------+--------+
| 8 | 7 |
+------+--------+
1 row in set (0.03 sec) mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.00 sec) mysql> NULL不会被统计进去 \c
mysql>
mysql> 计算蜀国英雄的总攻击力\c
mysql> select sum(gongji) from sanguo
-> where
-> country='蜀国';
+-------------+
| sum(gongji) |
+-------------+
| 2313 |
+-------------+
1 row in set (0.07 sec) mysql> 统计蜀国英雄中攻击值大于２００的英雄的数量\c
mysql> select count(name) from sanguo
-> where
-> country='蜀国' and gongji > 200;
+-------------+
| count(name) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec) mysql> select count(*) from sanguo
-> where
-> country='蜀国' and gongji > 200;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.01 sec) mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.00 sec) 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;
mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.00 sec) mysql> 几个国家
-> \c
mysql> select country from sanguo by country;
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
mysql> select country from sanguo group by country;
+---------+
| country |
+---------+
| 吴国 |
| 蜀国 |
| 魏国 |
+---------+
3 rows in set (0.09 sec) mysql> select name,country from sanguo group by country;
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
mysql> 计算所有国家的攻击力\c
mysql> 平均\c
mysql> select country,avg(gongji) from sanguo
-> group by country;
+---------+-------------+
| country | avg(gongji) |
+---------+-------------+
| 吴国 | 100.0000 |
| 蜀国 | 578.2500 |
| 魏国 | 483.6667 |
+---------+-------------+
3 rows in set (0.14 sec) mysql> select country,count(name) from sanguo
-> group by country order by desc limit 2;
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
mysql> select country,count(name) from sanguo group by country order by desc, limit 2;
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
mysql> select country,count(*) from sanguo group by country order by desc, limit 2;
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
mysql> select country,count(*) from sanguo group by country order by desc limit 2;
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
mysql> select country,count(*) from sanguo
-> group by country
-> order by count(*) desc
-> limit 2;
+---------+----------+
| country | count(*) |
+---------+----------+
| 蜀国 | 4 |
| 魏国 | 3 |
+---------+----------+
2 rows in set (0.00 sec) mysql> select country,count(*) from sanguo group by country order by count(*) desc limit 2;
+---------+----------+
| country | count(*) |
+---------+----------+
| 蜀国 | 4 |
| 魏国 | 3 |
+---------+----------+
2 rows in set (0.00 sec) mysql>
6、having
1、作用：对查询的结果进行进一步筛选；
2、练习
1、找出平均攻击力大于105的国家的前两名，显示国家名和平均攻击力
3、注意
1、having语句通常与group by语句联合使用,用来过滤由group by语句返回的记录集
2、having语句的存在弥补了where关键字不能与聚合函数联合使用的不足,having操作的是聚合函数生成的显示列
mysql> 平均攻击力> 105\c
mysql>
mysql> select country,avg(gongji) from sanguo
-> group by country
-> having avg(gongji) > 105
-> order by avg(gongji) desc
-> limit 2;
+---------+-------------+
| country | avg(gongji) |
+---------+-------------+
| 蜀国 | 578.2500 |
| 魏国 | 483.6667 |
+---------+-------------+
2 rows in set (0.01 sec) mysql>
7、distinct
1、作用：不显示字段的重复值
2、练习
1、sanguo表中一共有多少个国家
2、计算蜀国一共有多少个英雄
3、注意
1、distinct处理的是distinct和from之间的所有字段,所有字段值必须全部相同才能去重；
2、distinct不能对任何字段做聚合处理；
8、查询表记录时做数学运算
1、运算符 ：+ - * / %
2、练习
1、查询时显示所有英雄的攻击力 *10
2、查询时显示所有英雄的防御力 +5
mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.77 sec) mysql> select country,avg(gongji) from sanguo
-> group by country
-> having avg(gongji) > 105
-> order by avg(gongji) desc
-> limit 2;
+---------+-------------+
| country | avg(gongji) |
+---------+-------------+
| 蜀国 | 578.2500 |
| 魏国 | 483.6667 |
+---------+-------------+
2 rows in set (0.24 sec) mysql> 平均攻击力> 105\c
mysql>
mysql> select country,avg(gongji) from sanguo
-> group by country
-> having avg(gongji) > 105
-> order by avg(gongji) desc
-> limit 2;
+---------+-------------+
| country | avg(gongji) |
+---------+-------------+
| 蜀国 | 578.2500 |
| 魏国 | 483.6667 |
+---------+-------------+
2 rows in set (0.01 sec) mysql>
mysql> 三国表中一共多少个国家\c
mysql>
mysql> select country from sanguo group by country;
+---------+
| country |
+---------+
| 吴国 |
| 蜀国 |
| 魏国 |
+---------+
3 rows in set (0.00 sec) mysql> select distinct country from sanguo;
+---------+
| country |
+---------+
| 蜀国 |
| 魏国 |
| 吴国 |
+---------+
3 rows in set (0.00 sec) mysql> select distinct country,name from sanguo;
+---------+-----------+
| country | name |
+---------+-----------+
| 蜀国 | 诸葛亮 |
| 魏国 | 司马懿 |
| 蜀国 | 关羽 |
| 魏国 | 赵云 |
| 吴国 | 孙权 |
| 魏国 | 貂蝉 |
| 蜀国 | NULL |
| 蜀国 | |
+---------+-----------+
8 rows in set (0.00 sec) mysql> 计算蜀国有多少个英雄\c
mysql>
mysql> select count(distinct name) from sanguo
-> where
-> country='蜀国';
+----------------------+
| count(distinct name) |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.04 sec) mysql> select count(distinct name) from sanguo;
+----------------------+
| count(distinct name) |
+----------------------+
| 7 |
+----------------------+
1 row in set (0.00 sec) mysql> select country(distinct id) from sanguo where country='蜀国';
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
mysql> select count(distinct id) from sanguo where country='蜀国';
+--------------------+
| count(distinct id) |
+--------------------+
| 4 |
+--------------------+
1 row in set (0.00 sec) mysql> 显示攻击力×１０\c
mysql>
mysql> select id,name,gongji*10 as xgjl from sanguo;
+------+-----------+-------+
| id | name | xgjl |
+------+-----------+-------+
| 1 | 诸葛亮 | 1200 |
| 2 | 司马懿 | 1190 |
| 3 | 关羽 | 1880 |
| 4 | 赵云 | 6660 |
| 5 | 孙权 | 1000 |
| 6 | 貂蝉 | 6660 |
| 7 | NULL | 10000 |
| 8 | | 10050 |
+------+-----------+-------+
8 rows in set (0.03 sec) mysql> 防御力＋５\c
mysql>
mysql> select id,name,fangyu+5 as xfyl from sanguo;
+------+-----------+------+
| id | name | xfyl |
+------+-----------+------+
| 1 | 诸葛亮 | 25 |
| 2 | 司马懿 | 30 |
| 3 | 关羽 | 65 |
| 4 | 赵云 | 93 |
| 5 | 孙权 | 65 |
| 6 | 貂蝉 | 15 |
| 7 | NULL | 104 |
| 8 | | 93 |
+------+-----------+------+
8 rows in set (0.01 sec) mysql> select * from sanguo;
+------+-----------+--------+--------+------+---------+
| id | name | gongji | fangyu | sex | country |
+------+-----------+--------+--------+------+---------+
| 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |
| 2 | 司马懿 | 119 | 25 | 男 | 魏国 |
| 3 | 关羽 | 188 | 60 | 男 | 蜀国 |
| 4 | 赵云 | 666 | 88 | 男 | 魏国 |
| 5 | 孙权 | 100 | 60 | 男 | 吴国 |
| 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |
| 7 | NULL | 1000 | 99 | 男 | 蜀国 |
| 8 | | 1005 | 88 | 女 | 蜀国 |
+------+-----------+--------+--------+------+---------+
8 rows in set (0.00 sec) mysql>
约束
1、作用
为了保证数据的完整性、一致性、有效性
2、约束分类
1、默认约束(default)
1、作用
在插入记录时,如果不给该字段赋值,则使用默认值
2、格式
字段名 数据类型 default 值,
2、非空约束(not null)
1、作用 ：不允许将该字段设置为NULL
2、格式 ：字段名 数据类型 not null
mysql> create table t1(
-> id int(3) zerofill,
-> name char(20),
-> sex enum('F','M','Secret') default 'Secret',
-> age tinyint unsigned
-> )default charset=utf8;
Query OK, 0 rows affected (2.36 sec) mysql> desc t1;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| id | int(3) unsigned zerofill | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| sex | enum('F','M','Secret') | YES | | Secret | |
| age | tinyint(3) unsigned | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
4 rows in set (0.20 sec) mysql> insert into t1(id,name) values(1,'Lucy');
Query OK, 1 row affected (0.38 sec) mysql> select * from t1;
+------+------+--------+------+
| id | name | sex | age |
+------+------+--------+------+
| 001 | Lucy | Secret | NULL |
+------+------+--------+------+
1 row in set (0.00 sec) mysql> create table t2(
-> id int,
-> name varchar(15) not null default '单挑王'
-> )default charset=utf8;
Query OK, 0 rows affected (0.56 sec) mysql> desc t2;
+-------+-------------+------+-----+-----------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+-----------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(15) | NO | | 单挑王 | |
+-------+-------------+------+-----+-----------+-------+
2 rows in set (0.09 sec) mysql> insert into t2(id) values(1);
Query OK, 1 row affected (0.07 sec) mysql> select * from t2;
+------+-----------+
| id | name |
+------+-----------+
| 1 | 单挑王 |
+------+-----------+
1 row in set (0.00 sec) mysql> create table t3(
-> id int not null,
-> name varchar(15)
-> );
Query OK, 0 rows affected (0.44 sec) mysql> insert into t3(name) values('Bob');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
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 表名;
mysql> create table t4(
-> id int,
-> name char(20),
-> age tinyint unsigned,
-> index(id),
-> index(name)
-> );
Query OK, 0 rows affected (0.50 sec) mysql> desc t4;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | char(20) | YES | MUL | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.08 sec) mysql> create index age on t4(age);
Query OK, 0 rows affected (1.37 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t4;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | char(20) | YES | MUL | NULL | |
| age | tinyint(3) unsigned | YES | MUL | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.04 sec) mysql> show index from t4;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t4 | 1 | id | 1 | id | A | 0 | NULL | NULL | YES | BTREE | | |
| t4 | 1 | name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| t4 | 1 | age | 1 | age | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.04 sec) mysql> show index from t4\G;
*************************** 1. row ***************************
Table: t4
Non_unique: 1
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: t4
Non_unique: 1
Key_name: name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: t4
Non_unique: 1
Key_name: age
Seq_in_index: 1
Column_name: age
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec) ERROR:
No query specified mysql> desc t4;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(11) | YES | MUL | NULL | |
| name | char(20) | YES | MUL | NULL | |
| age | tinyint(3) unsigned | YES | MUL | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec) mysql> drop index id on t4;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t4;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(20) | YES | MUL | NULL | |
| age | tinyint(3) unsigned | YES | MUL | NULL | |
+-------+---------------------+------+-----+---------+-------+
3 rows in set (0.05 sec) mysql> drop index name on t4;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> drop index age on t4;
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from t4;
Empty set (0.00 sec) 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在删除时只能一个一个删
mysql> create table t5(
-> id int,
-> name varchar(15),
-> number char(11) unique
-> );
Query OK, 0 rows affected (0.56 sec) mysql> desc t5;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(15) | YES | | NULL | |
| number | char(11) | YES | UNI | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec) mysql> create unique index id on t5(id);
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t5;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(15) | YES | | NULL | |
| number | char(11) | YES | UNI | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec) mysql> show index from t5\G;
*************************** 1. row ***************************
Table: t5
Non_unique: 0
Key_name: number
Seq_in_index: 1
Column_name: number
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: t5
Non_unique: 0
Key_name: id
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec) ERROR:
No query specified mysql> desc t5;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(15) | YES | | NULL | |
| number | char(11) | YES | UNI | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec) mysql> drop index id on t5;
Query OK, 0 rows affected (0.39 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t5;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(15) | YES | | NULL | |
| number | char(11) | YES | UNI | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.08 sec) mysql>
练习1
有一张文章评论表comment如下
comment_id(评论) article_id(文章) user_id date
1 10000 10000 2018-01-30 09:00:00
2 10001 10001 ... ...
3 10002 10000 ... ...
4 10003 10015 ... ...
5 10004 10006 ... ...
6 10025 10006 ... ...
7 10009 10000 ... ...
以上是一个应用的comment表格的一部分，请使用SQL语句找出在本站发表的所有评论数量最多的10位用户及评论数，并按评论数从高到低排序
备注：comment_id为评论id
article_id为被评论文章的id
user_id指用户id
答案:
select user_id,count(*) as c from comment
group by user_id order by c desc limit 10;