MySQL语句命令

1.客户端命令

MySQL配置文件影响

1.影响服务端的启动 [mysqld] 											-->	[server]
2.影响客户端的连接 [mysql][mysqladmin][mysqldump] 	-->	[client]


#所有客户端配置写在配置文件里立即生效
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
basedir=/app/mysql
datadir=/app/mysql/data
server_id=1
log-bin=mysql-bin
socket=/opt/mysql.sock

[client]
user=root
password=123
socket=/opt/mysql.sock

? 查看帮助
\q ： quit exit 				退出mysql终端 MySQL5.6 Ctrl + c
\c ： 终止命令 				 MySQL5.6 Ctrl + c
\s : 查看MySQL状态
\G ： 格式化输出
\. : source 导入数据
\! ： system 后面接系统命令

#导出数据   
-A 备份所有的database
-B 导出那几个数据库
[root@db01 ~]# mysqldump -A >/tmp/luay.sql
[root@db01 ~]# mysqldump -B luay >/tmp/luay.sql

#库外导入数据
[root@db01 ~]# mysql -uroot </tmp/luay.sql 

#库内导入数据（先临时关闭二进制日志记录，再导入）
mysql> set sql_log_bin=0;
mysql> source /tmp/luay.sql
mysql> set sql_log_bin=1;

#库外导入数据容易脏数据,再开启二进制日志时候，库外导入数据也会记录到二进制日志里，容易造成数据的污染，会产生重复数据

#不退出MySQL而进行mysql外的操作
mysql> system ls -l
total 31856
-rw-------.  1 root root      1515 Jul  3 11:00 anaconda-ks.cfg
-rw-rw-rw-.  1 root root       433 Jul  7 13:28 ip_host.sh
-rw-rw-rw-.  1 root root    188754 Apr 23 08:42 main.sh

mysql> system vim ip_host.sh

mysqladmin命令

# 检测MySQL是否存活
[root@db01 ~]# mysqladmin -uroot -p123 ping
mysqld is alive

# 停止MySQL服务
[root@db01 ~]# mysqladmin -uroot -p123 shutdown

# 查看MySQL默认配置
[root@db01 ~]# mysqladmin -uroot -p123 variables

# 查看MySQL状态
[root@db01 ~]# mysqladmin -uroot -p123 status

# 刷新MySQL binlog日志
[root@db01 ~]# mysqladmin -uroot -p123 flush-log

# 库外建库
[root@db01 ~]# mysqladmin -uroot -p123 create zls111

# 库外删库
[root@db01 ~]# mysqladmin -uroot -p123 drop zls111

# 重新加载数据库
[root@db01 ~]# mysqladmin -uroot -p123 reload

# 设置密码 修改密码
[root@db01 ~]# mysqladmin -uroot -p123 password '1111'

2.结构化查询语言

DDL 数据定义语言

• Database Definition Language (create,drop,alter)

开发规范：
01) 库、表名不能大写,数字开头,16个字符串
02) 表名和业务有关
03) drop 语句禁止
04) 选择合适的数据类型
05) 必须要有主键
06) 列尽量非空约束
07) 减少外键约束
08) 必须设置存储引擎和字符集
09) 列必须要有注释
10) 对于非负数设置数据类型约束--无符号

库

#进入MySQL显示当前所在库(在客户端配置里添加)
[root@db01 ~]# vim /etc/my.cnf
[client]
prompt="\\u@\\h:\\d \\r:\\m:\\s>"

#\\u代表当前用户，\\d代表当前数据库名，\\r:\\m:\\s代表当前时间。

增

## 增：创建数据库 create
mysql> help create database
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
create database 库名字;
create schema 库名字;

#如果不存在就创建
mysql> create database if not exists zls;
mysql> create schema if not exists zls11;

#设置字符集和校验规则
character set utf8;
mysql> create database zls123 charset utf8;
mysql> create database if not exists zls123 charset utf8 collate utf8_general_ci;

#查看创建的字符集
mysql> show create database lgj;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| lgj      | CREATE DATABASE `lgj` /*!40100 DEFAULT CHARACTER SET utf8 */|
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)



#使用二进制安装的MySQL字符集默认是latin
#如何更改字符集？
mysql> show variables like '%char%';
+--------------------------+-----------------------------------+
| Variable_name            | Value                             |
+--------------------------+-----------------------------------+
| character_set_client     | utf8                              |
| character_set_connection | utf8                              |
| character_set_database   | latin1                            |
| character_set_filesystem | binary                            |
| character_set_results    | utf8                              |
| character_set_server     | latin1                            |
| character_set_system     | utf8                              |
| character_sets_dir       | /app/mysql-5.6.50/share/charsets/ |
+--------------------------+-----------------------------------+

#修改字符集
[root@db02 ~]#  system vim /etc/my.cnf
[mysqld]
character_set_server=utf8

#重启

删

## 删： drop
mysql> drop database test1111111;
mysql> drop database if exists zls111;

改

## 改： alter
mysql> alter database zls charset latin5;
mysql> alter database zls charset utf8 collate ut8_bin;

表

## 常用的数据类型
数字类型：
int：整型 -2^31 ~ 2^31 -1 			-2147483648 ~ 2147483647
tinyint：最小整型 -128 ~ 127    无符号范围： 0-255
float：浮点型 单精度
double：浮点型 双精度

字符串类型：
char：定长字符串 char(3) 张三 char(11) char(18)
varchar：变长字符串 varchar(3) 张三
enum：枚举类型 enum('f','m') enum('0','1') enum('A','B','C','D')
male   female

时间类型：
timestamp：时间戳类型 类型的值范围从1970-01-01 00:00:01 UTC到 2038-01-19 03:14:07 UTC
datetime：时间类型 类型的值范围从1000-01-01 00:00:00到 9999-12-31 23:59:59

## 常用的约束
NULL：						可以为空
NOT NULL：				 非空
default：				 默认值
primary key：		 主键 特点：唯一 且 非空
auto_increment：	 自增
unique key：			 唯一键 特点：唯一 但 可以为空 primary key = unique key + not null
unsigned：				无符号（非负数）
comment：				注释

增 & 查

## 增：创建表
学生表：id 学号，name 名字，age 年龄，gender 性别，phone 手机号，come_time 入学日期
create table student(id int,name varchar(10),age tinyint,gender enum('f','m'),phone char(11),come_time datetime);

#创建表，带有约束
create table stu(
id int primary key auto_increment comment '学生学号',
name varchar(10) comment '学生姓名',
age tinyint unsigned not null comment '学生年龄',
gender enum('f','m') not null default 'm' comment '学生性别',
phone char(11) not null unique key comment '学生手机',
come_time datetime not null default NOW() comment '入学时间');

#查看创建的表
root@student > show create table stu;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------
| stu   | CREATE TABLE `stu` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生学号',
  `name` varchar(10) DEFAULT NULL COMMENT '学生姓名',
  `age` tinyint(3) unsigned NOT NULL COMMENT '学生年龄',
  `gender` enum('f','m') NOT NULL DEFAULT 'm' COMMENT '学生性别',
  `phone` char(11) NOT NULL COMMENT '学生手机',
  `come_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8  

#查看表结构  (如果已经在student库里，直接使用   desc stu  )
root@mysql > desc student.stu;
#					 desc 	库名 .  表名
+-----------+---------------------+------+-----+-------------------+----------------+
| Field     | Type                | Null | Key | Default           | Extra          |
+-----------+---------------------+------+-----+-------------------+----------------+
| id        | int(11)             | NO   | PRI | NULL              | auto_increment |
| name      | varchar(10)         | YES  |     | NULL              |                |
| age       | tinyint(3) unsigned | NO   |     | NULL              |                |
| gender    | enum('f','m')       | NO   |     | m                 |                |
| phone     | char(11)            | NO   | UNI | NULL              |                |
| come_time | datetime            | NO   |     | CURRENT_TIMESTAMP |                |
+-----------+---------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)


#查看表（如果已经再student库里，直接使用   show tables  ）
root@mysql > show tables from student;
+-------------------+
| Tables_in_student |
+-------------------+
| stu               |
+-------------------+
1 row in set (0.00 sec)

#插入数据
insert into student.stu (id,name,age,gender,phone) values(123,'zls',18,'m',12312312366);
insert into student.stu (id,name,age,gender,phone) values(1223,'zls',18,'m',12312312365);

#查看数据
root@mysql > select * from student.stu;
+------+------+-----+--------+-------------+---------------------+
| id   | name | age | gender | phone       | come_time           |
+------+------+-----+--------+-------------+---------------------+
|  123 | zls  |  18 | m      | 12312312366 | 2024-08-12 19:26:04 |
| 1223 | zls  |  18 | m      | 12312312365 | 2024-08-12 19:28:01 |
+------+------+-----+--------+-------------+---------------------+

删 & 改

## 删表 drop
student > drop table stu;					#如果已经在student库内
mysql > drop table student.stu1

## 改表 alter
### 修改表名
student > alter table stu3 rename stu30;

### 修改字段
# 添加字段（默认添加在最后）
mysql > alter table stu10 add lzd char(2);

root@student > desc stu;
+-----------+---------------------+------+-----+-------------------+----------------+
| Field     | Type                | Null | Key | Default           | Extra          |
+-----------+---------------------+------+-----+-------------------+----------------+
| id        | int(11)             | NO   | PRI | NULL              | auto_increment |
| name      | varchar(10)         | YES  |     | NULL              |                |
| age       | tinyint(3) unsigned | NO   |     | NULL              |                |
| gender    | enum('f','m')       | NO   |     | m                 |                |
| phone     | char(11)            | NO   | UNI | NULL              |                |
| come_time | datetime            | NO   |     | CURRENT_TIMESTAMP |                |
| zls       | char(2)             | YES  |     | NULL              |                |
+-----------+---------------------+------+-----+-------------------+----------------+

# 添加字段（添加在指定字段后面）
mysql > alter table stu10 add abc char(11) after phone;
root@student > desc stu;
+-----------+---------------------+------+-----+-------------------+----------------+
| Field     | Type                | Null | Key | Default           | Extra          |
+-----------+---------------------+------+-----+-------------------+----------------+
| id        | int(11)             | NO   | PRI | NULL              | auto_increment |
| name      | varchar(10)         | YES  |     | NULL              |                |
| age       | tinyint(3) unsigned | NO   |     | NULL              |                |
| gender    | enum('f','m')       | NO   |     | m                 |                |
| phone     | char(11)            | NO   | UNI | NULL              |                |
| abc       | char(11)            | YES  |     | NULL              |                |
| come_time | datetime            | NO   |     | CURRENT_TIMESTAMP |                |
| zls       | char(2)             | YES  |     | NULL              |                |
+-----------+---------------------+------+-----+-------------------+----------------+


# 添加字段（将字段添加在最前面）
mysql > alter table stu10 add sss char(11) first;

# 删除字段
mysql > alter table stu10 drop lzd;

# 修改字段 modify change
## 只能修改字段的数据类型和约束
mysql > alter table stu modify name varchar(10);

## 可以修改字段类型和约束外还能修改字段名
mysql > alter table stu10 change lyk kangkang varchar(20);

DML 数据操作语言

• Data Manipulation Language ( insert,delete,update )

增删改操作

增

#插入之前查看字段

#insert
#规范用法，插入数据
mysql> insert into stu(classid,birth.sname,sage,sgender,comtime,telnum,qq) values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456);

#插入多条数据
mysql> insert into stu(classid,birth.sname,sage,sgender,comtime,telnum,qq) values('linux01',1,NOW(),'zhangsan',20,'m',NOW(),110,123456),('linux02',2,NOW(),'zhangsi',21,'f',NOW(),111,1234567);

删

#delete

#规范删除（危险）
mysql> delete from student where sid=3;

#DDL删除表 （危险）
mysql> truncate table student;

改

#update  

#规范update修改
mysql> update student set sgender='f' where sid=1;

#如果非要全表修改
mysql> update student set sgender='f' where 1=1;

使用update 伪删除

1）额外添加一个状态列(前端做判断，0代表没有充钱，1代表充钱)
mysql> alter table student.stu add status enum(0,1) default 1 comment '0代表没有，1代表有';

2）使用update
mysql> update student.stu set status='0' where sid=1;

3）应用查询存在的数据
mysql> select * from student where status=1;

DCL数据控制语言

（Data Control Language) ( grant )

grant

#授权root@10.0.0.51用户所有权限
mysql> grant all on *.* to root@'10.0.0.51' identified by 'oldboy123';

#授权超级管理员
mysql> grant all on *.* to root@'10.0.0.51' identified by 'oldboy123' with grant option;
with grant option：赋予用户grant权限


#其他参数（扩展）

#max_queries_per_hour：		一个用户每小时可发出的查询数量
grant insert,update,select on zls.* to dev3@'%' identified by '123' with max_queries_per_hour 3;

#max_updates_per_hour：		一个用户每小时可发出的更新数量
grant insert,update,select on zls.* to dev4@'%' identified by '123' with max_updates_per_hour 1;

#max_connetions_per_hour：一个用户每小时可连接到服务器的次数   多个限制可以使用一个with
grant insert,update,select
on zls.*
to dev5@'%'
identified by '123'
with max_queries_per_hour 3
max_updates_per_hour 1
max_connections_per_hour 1;

#max_user_connetions：		允许同时连接数量
grant insert,update,select
on zls.*
to dev6@'%'
identified by '123'
with max_user_connections 1;

revoke回收权限

#收回select权限
mysql> revoke select on *.* from root@'10.0.0.51';

#查看权限
root@(none) > show grants for root@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

DQL数据查询语言

Data Query Language

#下载world库测试 
[root@db01 ~]# wget http://test.driverzeng.com/MySQL_File/world.sql

# 2.导入数据
1)[root@db01 ~]# mysql -uroot -p123 < world.sql
2)[root@db01 ~]# cat world.sql|mysql -uroot -p123
3)mysql> source /root/world.sql

#查看有多少表
root@(none) > show tables from world;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

#查看表结构
root@(none) > desc world.city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

show

show databases;							#查看库
show tables;								#查看表--已经在world库里
show tables from world;			 #查看world库里的所有表
show create database zls;		 #查看创建库的语句
show create table zls.stu30; #查看创建表的语句
show grants for root@'%'; 	 
show variables like '%char%';
show engines;								#查看MySQL中自带的储存引擎
show charset;								# MySQL支持的字符集
show collation;							# MySQL支持的校验规则
show processlist;						#查看MySQL后台SQL语句执行状态
show full processlist;			#查看MySQL后台SQL语句执行状态

desc mysql.user;						# desc 查看表结构




root@(none) > show grants for root@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)


root@(none) > show variables like '%char%';
+--------------------------+-----------------------------------+
| Variable_name            | Value                             |
+--------------------------+-----------------------------------+
| character_set_client     | utf8                              |
| character_set_connection | utf8                              |
| character_set_database   | utf8                              |
| character_set_filesystem | binary                            |
| character_set_results    | utf8                              |
| character_set_server     | utf8                              |
| character_set_system     | utf8                              |
| character_sets_dir       | /app/mysql-5.6.50/share/charsets/ |
+--------------------------+-----------------------------------+



#查看MySQL后台SQL语句执行状态
root@(none) > show processlist;
+----+------+---------------+-------+---------+------+-------+------------------+
| Id | User | Host          | db    | Command | Time | State | Info             |
+----+------+---------------+-------+---------+------+-------+------------------+
|  3 | root | 10.0.0.1:1083 | test  | Sleep   | 9411 |       | NULL             |
|  4 | root | 10.0.0.1:1084 | mysql | Sleep   | 9414 |       | NULL             |
|  5 | root | 10.0.0.1:1095 | world | Sleep   | 9551 |       | NULL             |
|  8 | root | localhost     | NULL  | Query   |    0 | init  | show processlist |
+----+------+---------------+-------+---------+------+-------+------------------+

select

# select
use world；	进入world库里

## 全表扫描 (  慎用，要加上条件，否则所有数据都会加载到内存里)
select * from city;

## 单条件查询
select * from city where id=10;

## 多条件查询
select * from city where countrycode='CHN' or countrycode='USA';

## 成员查询
select * from city where countrycode in ('CHN','USA');

## 联合查询
select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

## 范围查询 < 、 > 、 >= 、 <= 、 <> 、 !=
select * from city where id<10;

## 模糊查询
select * from city where countrycode like '%H%';		#城市代码里含有H的
select * from city where countrycode like 'H%';			#城市代码里以H开头的
select * from city where countrycode like '%H';			#城市代码里以H结尾的

## 排序查询 order by
# 正序
select * from city where countrycode like 'H%' order by population;
# 倒叙
select * from city where countrycode like 'H%' order by population desc;

## 分页查询
select * from city limit 60;
select * from city limit 180,60;

使用函数

MySQL自带的函数：
password()
now()
distinct()		#去重函数

## 分组查询 group by
# max()
# min()
# avg()
# sum()
# count()
1.遇到统计想函数
2.形容词前groupby
3.函数中央是名词
4.列名select后添加

`练习`
# 统计世界上每个国家的总人口数
1.遇到统计想函数 sum()
2.形容词前groupby group by countrycode
3.函数中央是名词 sum(population)
4.列名select后添加 select 国家,总人口数
select countrycode,sum(population) from city group by countrycode;

# 统计中国各个省的人口数量(练习)
1.遇到统计想函数 sum()
2.形容词前groupby groupby district
3.函数中央是名词 sum(population)
4.列名select后添加 select countrycode,district,sum(population)
select countrycode,district,sum(population) from city where countrycode='CHN'
group by district;



# 统每个国家的城市数量(练习)
1.遇到统计想函数 count()
2.形容词前groupby group by countrycode
3.函数中央是名词 count(name)
4.列名select后添加 select countrycode,count(name)
select countrycode,count(name) from city group by countrycode order by count(name);


# 统每个国家的每个省的城市数量(练习)
select CountryCode,District,count(Name) from city group by District order by count(name);


# 统中国的每个省的城市数量(练习)
select CountryCode,District,count(Name) from city where CountryCode='CHN' group by District order by count(name);

#统计每个国家最多人数的省份
select CountryCode,District,max(population) from city group by population;

#统计中国每个省的人数
select CountryCode, District,sum(population) from city where CountryCode='CHN' group by District;



#传统连接
select country.name as 国家名,city.name as 城市名,countrylanguage.language as 国家语言,city.population as 城市人口
from city,country,countrylanguage
where city.CountryCode=country.code and country.code=countrylanguage.CountryCode
and city.population<100;

#select自连接（必须得有等价条件）
select country.name as 国家名,city.name as 城市名
from city natural join country
where city.population<100;
 
 

select country.name,city.name,countrylanguage.language,city.population
from city  join (country,countrylanguage)
on city.CountryCode=country.code and country.code=countrylanguage.CountryCode
where city.population<100;

select-连表查询

#简单连表查询
root@student > create table stu(id int,name varchar(10));
root@student > insert into stu values(1,'zhang3'),(2,'li4'),(3,'wang5');

root@student > create table score(id int,mark tinyint unsigned);
root@student > insert into score values(1,80),(2,90),(3,100);

root@student > select * from stu;
+------+--------+
| id   | name   |
+------+--------+
|    1 | zhang3 |
|    2 | li4    |
|    3 | wang5  |
+------+--------+
3 rows in set (0.00 sec)

root@student > select * from score;
+------+------+
| id   | mark |
+------+------+
|    1 |   80 |
|    2 |   90 |
|    3 |  100 |
+------+------+
3 rows in set (0.00 sec)

#查询zhang3同学的成绩
root@student > select stu.name,score.mark 
from stu,score
where stu.id=score.id
and stu.name='zhang3';
+--------+------+
| name   | mark |
+--------+------+
| zhang3 |   80 |
+--------+------+
1 row in set (0.00 sec)

==传统查询==

#查看表结构

root@world > desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)


root@world > desc country;
+----------------+---------------------------------+------+-----+---------+-------+
| Field          | Type                            | Null | Key | Default | Extra |
+----------------+---------------------------------+------+-----+---------+-------+
| Code           | char(3)                         | NO   | PRI |         |       |
| Name           | char(52)                        | NO   |     |         |       |
| Continent      | enum('Asia','Europe', America') | NO   |     | Asia    |       |
| Region         | char(26)                        | NO   |     |         |       |
| SurfaceArea    | float(10,2)                     | NO   |     | 0.00    |       |
| IndepYear      | smallint(6)                     | YES  |     | NULL    |       |
| Population     | int(11)                         | NO   |     | 0       |       |
| LifeExpectancy | float(3,1)                      | YES  |     | NULL    |       |
| GNP            | float(10,2)                     | YES  |     | NULL    |       |
| GNPOld         | float(10,2)                     | YES  |     | NULL    |       |
| LocalName      | char(45)                        | NO   |     |         |       |
| GovernmentForm | char(45)                        | NO   |     |         |       |
| HeadOfState    | char(60)                        | YES  |     | NULL    |       |
| Capital        | int(11)                         | YES  |     | NULL    |       |
| Code2          | char(2)                         | NO   |     |         |       |
+----------------+---------------------------------+------+-----+---------+-------+


root@world > desc countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3)       | NO   | PRI |         |       |
| Language    | char(30)      | NO   | PRI |         |       |
| IsOfficial  | enum('T','F') | NO   |     | F       |       |
| Percentage  | float(4,1)    | NO   |     | 0.0     |       |
+-------------+---------------+------+-----+---------+-------+

查询世界上小于100人口的城市是哪个国家的？

国家名		城市名		城市人口数

root@world > select country.name,city.name,city.population
from country,city 
where city.CountryCode=country.code 
and city.population<100;
+----------+-----------+------------+
| name     | name      | population |
+----------+-----------+------------+
| Pitcairn | Adamstown |         42 |
+----------+-----------+------------+
1 row in set (0.00 sec)

root@world > select country.name as 国家名,city.name as 城市名,city.population as 城市人口
from country,city 
where city.CountryCode=country.code 
and city.population<100;
+-----------+-----------+--------------+
| 国家名    | 城市名    | 城市人口       |
+-----------+-----------+--------------+
| Pitcairn  | Adamstown |           42 |
+-----------+-----------+--------------+
1 row in set (0.00 sec)

世界上小于100人口的城市说什么语言？

#三表联查
国家名，城市名，语言，人口

root@world > select country.name,city.name,countrylanguage.language,city.population 
from country,city,countrylanguage 
where city.countrycode=country.code and country.code=countrylanguage.countrycode 
and city.population<100;
+----------+-----------+-------------+------------+
| name     | name      | language    | population |
+----------+-----------+-------------+------------+
| Pitcairn | Adamstown | Pitcairnese |         42 |
+----------+-----------+-------------+------------+

==select自连接==

# 自动关联多张表的数据（自动帮我们找到等价条件）等价条件的字段名必须相同，比如student库里表的ID
#查看表结构
root@student > desc stu;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

root@student > desc score;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | int(11)             | YES  |     | NULL    |       |
| mark  | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+


#查询zhang3同学的成绩，使用自连接查询
select stu.name,score.mark
from stu natural join score
where stu.name='zhang3';
+--------+------+
| name   | mark |
+--------+------+
| zhang3 |   80 |
+--------+------+
1 row in set (0.00 sec)


# 世界上小于100人的人口城市是哪个国家的说的什么语言？
root@world > select city.countrycode,city.name,city.population,countrylanguage.language
from countrylanguage natural join city
where city.population<100;
+-------------+-----------+------------+-------------+
| countrycode | name      | population | language    |
+-------------+-----------+------------+-------------+
| PCN         | Adamstown |         42 | Pitcairnese |
+-------------+-----------+------------+-------------+
1 row in set (0.00 sec)

==内连接(inner join)==

## 语法：tb1 join tb2 on 等价条件
## 语法：tb1 join tb2 on 等价条件1 join tb3 on 等价条件2

inner join on # 内连接
# 世界上小于100人的人口城市是哪个国家的？

国家名，城市名，城市人口

root@world > select country.name,city.name,city.population
from country join city
on country.code=city.countrycode
where city.population<100;
+----------+-----------+------------+
| name     | name      | population |
+----------+-----------+------------+
| Pitcairn | Adamstown |         42 |
+----------+-----------+------------+
1 row in set (0.00 sec)


# 世界上小于100人的人口城市是哪个国家的说的什么语言？

root@world > select country.name,city.name,city.population,countrylanguage.language
from country join city
on country.code=city.countrycode
join countrylanguage
on countrylanguage.countrycode=city.countrycode
where city.population<100;

+----------+-----------+------------+-------------+
| name     | name      | population | language    |
+----------+-----------+------------+-------------+
| Pitcairn | Adamstown |         42 | Pitcairnese |
+----------+-----------+------------+-------------+
1 row in set (0.01 sec)

==select外连接==

左外连接

left outer join
root@world > select city.name,city.countrycode,country.name
from city left join country 
on city.countrycode=country.code and
city.population<100 limit 10;
+----------------+-------------+------+
| name           | countrycode | name |
+----------------+-------------+------+
| Kabul          | AFG         | NULL |
| Qandahar       | AFG         | NULL |
| Herat          | AFG         | NULL |
| Mazar-e-Sharif | AFG         | NULL |
| Amsterdam      | NLD         | NULL |
| Rotterdam      | NLD         | NULL |
| Haag           | NLD         | NULL |
| Utrecht        | NLD         | NULL |
| Eindhoven      | NLD         | NULL |
| Tilburg        | NLD         | NULL |
+----------------+-------------+------+
10 rows in set (0.00 sec)


left join
只显示左边city信息
=

右外连接

right outer join
root@world>  select city.name,city.countrycode,country.name
from city right join country on city.countrycode=country.code and
city.population<100 limit 10;


root@world > select city.name,city.countrycode,country.name
from city right join country 
on city.countrycode=country.code and
city.population<100 limit 10;
+------+-------------+----------------------+
| name | countrycode | name                 |
+------+-------------+----------------------+
| NULL | NULL        | Aruba                |
| NULL | NULL        | Afghanistan          |
| NULL | NULL        | Angola               |
| NULL | NULL        | Anguilla             |
| NULL | NULL        | Albania              |
| NULL | NULL        | Andorra              |
| NULL | NULL        | Netherlands Antilles |
| NULL | NULL        | United Arab Emirates |
| NULL | NULL        | Argentina            |
| NULL | NULL        | Armenia              |
+------+-------------+----------------------+
10 rows in set (0.00 sec)

只显示右边country信息
right join

连表查询练习

创建库 & 表

• 库Linux

• 表：student 学生表

字段	数据类型要求	是否为空	注释
sno	最多20位	否	学号
sname	可变长	否	学生姓名
sage	最小整数，非负数	否	学生年龄
ssex	0,1	否	学生性别（1是男，0是女，默认男）
sbirthday	时间类型	默认为空	学生生日
class	可变长	否	学生班级

root@world > create database linux;
root@world > use linux

#创建表
create table linux.student(
sno bigint(20) primary key auto_increment comment '学生学号',
sname varchar(10) not null comment '学生姓名',
sage tinyint unsigned not null comment '学生年龄',
ssex enum('0','1') not null default '1' comment '学生性别（1是男，0是女）默认为男）',
sbirthday datetime  comment '学生生日',
class varchar(10) not null  comment '学生班级');

#插入数据
insert student (sname,sage,ssex,class)
values('小明',18,'1','1班'),
('李明',20,'1','1班'),
('小红',20,'0','2班'),
('小王',21,'1','1班'),
('小蔡',19,'1','2班'),
('小李',21,'1','1班'),
('小丽',20,'0','2班');

#查看表
root@linux > select * from student where 1=1;
+-----+--------+------+------+-----------+-------+
| sno | sname  | sage | ssex | sbirthday | class |
+-----+--------+------+------+-----------+-------+
|   1 | 小明   |   18 | 1    | NULL      | 1班   |
|   2 | 李明   |   20 | 1    | NULL      | 1班   |
|   3 | 小红   |   20 | 0    | NULL      | 2班   |
|   4 | 小王   |   21 | 1    | NULL      | 1班   |
|   5 | 小蔡   |   19 | 1    | NULL      | 2班   |
|   6 | 小李   |   21 | 1    | NULL      | 1班   |
|   7 | 小丽   |   20 | 0    | NULL      | 2班   |
+-----+--------+------+------+-----------+-------+
7 rows in set (0.00 sec)

• course表 课程表

字段	数据类型要求	是否为空	注释
cno	最多20位	否	课程号（主键）
cname	可变长	否	课程名称
tno	最多3位	否	教师编号

#创建能课程表
create table course(
cno bigint(20) primary key auto_increment comment '课程号',
cname varchar(10) not null comment '课程名称',
tno int(3) zerofill unsigned not null comment '教师编号');

#插入数据
insert into course (cname,tno)
values('英语','1'),
('数学','2'),
('语文','3');

#查看表数据
root@linux > select * from course where 1=1;
+-----+--------+-----+
| cno | cname  | tno |
+-----+--------+-----+
|   1 | 英语   | 001 |
|   2 | 数学   | 002 |
|   3 | 语文   | 003 |
+-----+--------+-----+
3 rows in set (0.00 sec)

• score 成绩表

字段	数据类型要求	是否为空	注释
sno	最多20位	否	学号（主键）
cno	最多20位	否	课程号 （主键）
mark	浮点数（4,1）	否	成绩

#创建表
create table score(
	sno bigint(20) auto_increment comment '学生学号',
	cno bigint(20)  comment '课程号',
	mark float(4,1) unsigned not null comment '成绩',
	primary key (sno,cno)
);

#插入数据
insert into score (cno,mark) values
(1,90.2),
(2,99.9),
(3,66.6),
(1,78.5),
(2,93.0),
(3,91.5),
(2,65.5),

insert into score (sno,cno,mark) values
(1,2,82.5),
(2,1,90.0),
(3,2,85.5),
(4,2,66.6),
(5,3,72.5);


#查看表
root@linux > select * from score where 1=1;
+-----+-----+------+
| sno | cno | mark |
+-----+-----+------+
|   1 |   1 | 90.2 |
|   1 |   2 | 82.5 |
|   2 |   1 | 90.0 |
|   2 |   2 | 99.9 |
|   3 |   2 | 85.5 |
|   3 |   3 | 66.6 |
|   4 |   1 | 78.5 |
|   4 |   2 | 66.6 |
|   5 |   2 | 93.0 |
|   5 |   3 | 72.5 |
|   6 |   3 | 91.5 |
|   7 |   2 | 65.5 |
+-----+-----+------+
12 rows in set (0.00 sec)

• teacher 教师表

字段	数据类型要求	是否为空	注释
tno	最多3位	否	教师编号（主键）
tname	可变长	否	教师姓名
tage	最小整数，非负数	否	教师年龄
tsex	0,1	否	教师性别（1是男，0是女）默认为男）
prof	可变长	是	教师职称
depart	可变长	否	教师部门

#创建表

create table teacher(
tno int(3) zerofill primary key not null auto_increment comment '教师编号',
tname varchar(10) not null  comment '教师姓名',
tage tinyint unsigned not null comment '教师年龄',
tsex enum('0','1') not null default '1' comment '教师性别（1是男，0是女）默认为男）',
prof varchar(5) not null  comment '教师职称',
depart varchar(5) not null  comment '教师部门'
);

#插入数据
insert into teacher (tno,tname,tage,tsex,prof,depart) 
values('1','李导',30,'1','教学总监','语言系'),
('2','小秘',30,'1','助教','文学系'),
('3','曾导',30,'1','教学总监','科学系'),
('4','苍老师',30,'0','助教','运动系'),
('5','黄老师',28,'1','助教','文学系');

#查看数据
root@linux > select * from teacher;
+-----+--------+------+------+--------------+-----------+
| tno | tname  | tage | tsex | prof         | depart    |
+-----+--------+------+------+--------------+-----------+
|   1 | 李导   |   30 | 1    | 教学总监     | 语言系    |
|   2 | 小秘   |   30 | 1    | 助教         | 文学系    |
|   3 | 曾导   |   30 | 1    | 教学总监     | 科学系    |
+-----+--------+------+------+--------------+-----------+
3 rows in set (0.00 sec)

查询练习

1.查询student表中的所有记录的sname、ssex和class列。
root@linux > select sname,ssex,class from linux.student;
+--------+------+-------+
| sname  | ssex | class |
+--------+------+-------+
| 小明   | 1    | 1班   |
| 李明   | 1    | 1班   |
| 小红   | 0    | 2班   |
| 小王   | 1    | 1班   |
| 小蔡   | 1    | 2班   |
| 小李   | 1    | 1班   |
| 小丽   | 0    | 2班   |
+--------+------+-------+

2.查询教师所有的单位即不重复的depart列。
root@linux > select distinct(depart) from teacher;
+-----------+
| depart    |
+-----------+
| 语言系    |
| 文学系    |
| 科学系    |
| 运动系    |
+-----------+

3.查询student表的所有记录。
root@linux > select * from student where 1=1;
+-----+--------+------+------+-----------+-------+
| sno | sname  | sage | ssex | sbirthday | class |
+-----+--------+------+------+-----------+-------+
|   1 | 小明   |   18 | 1    | NULL      | 1班   |
|   2 | 李明   |   20 | 1    | NULL      | 1班   |
|   3 | 小红   |   20 | 0    | NULL      | 2班   |
|   4 | 小王   |   21 | 1    | NULL      | 1班   |
|   5 | 小蔡   |   19 | 1    | NULL      | 2班   |
|   6 | 小李   |   21 | 1    | NULL      | 1班   |
|   7 | 小丽   |   20 | 0    | NULL      | 2班   |
+-----+--------+------+------+-----------+-------+

4.查询score表中成绩在60到80之间的所有记录。
root@linux > select * from score where mark>60 and mark<80;
+-----+-----+------+
| sno | cno | mark |
+-----+-----+------+
|   3 |   3 | 66.6 |
|   4 |   1 | 78.5 |
|   4 |   2 | 66.6 |
|   5 |   3 | 72.5 |
|   7 |   2 | 65.5 |
+-----+-----+------+

5.查询score表中成绩为66.6，72.5或90.0的记录。
root@linux > select * from score where mark=66.6 or mark=72.5 or mark=90;
+-----+-----+------+
| sno | cno | mark |
+-----+-----+------+
|   2 |   1 | 90.0 |
|   3 |   3 | 66.6 |
|   4 |   2 | 66.6 |
|   5 |   3 | 72.5 |
+-----+-----+------+

6.查询student表中1班或性别为“女”的同学记录。
root@linux > select * from student where class='1班' or ssex='0';
+-----+--------+------+------+-----------+-------+
| sno | sname  | sage | ssex | sbirthday | class |
+-----+--------+------+------+-----------+-------+
|   1 | 小明   |   18 | 1    | NULL      | 1班   |
|   2 | 李明   |   20 | 1    | NULL      | 1班   |
|   3 | 小红   |   20 | 0    | NULL      | 2班   |
|   4 | 小王   |   21 | 1    | NULL      | 1班   |
|   6 | 小李   |   21 | 1    | NULL      | 1班   |
|   7 | 小丽   |   20 | 0    | NULL      | 2班   |
+-----+--------+------+------+-----------+-------+

7.以class降序查询Student表的所有记录。
root@linux > select * from student order by class desc;
+-----+--------+------+------+-----------+-------+
| sno | sname  | sage | ssex | sbirthday | class |
+-----+--------+------+------+-----------+-------+
|   3 | 小红   |   20 | 0    | NULL      | 2班   |
|   5 | 小蔡   |   19 | 1    | NULL      | 2班   |
|   7 | 小丽   |   20 | 0    | NULL      | 2班   |
|   1 | 小明   |   18 | 1    | NULL      | 1班   |
|   2 | 李明   |   20 | 1    | NULL      | 1班   |
|   4 | 小王   |   21 | 1    | NULL      | 1班   |
|   6 | 小李   |   21 | 1    | NULL      | 1班   |
+-----+--------+------+------+-----------+-------+

8.以cno升序、mark降序查询Score表的所有记录
root@linux > select * from score order by cno,mark desc;
+-----+-----+------+
| sno | cno | mark |
+-----+-----+------+
|   1 |   1 | 90.2 |
|   2 |   1 | 90.0 |
|   4 |   1 | 78.5 |
|   2 |   2 | 99.9 |
|   5 |   2 | 93.0 |
|   3 |   2 | 85.5 |
|   1 |   2 | 82.5 |
|   4 |   2 | 66.6 |
|   7 |   2 | 65.5 |
|   6 |   3 | 91.5 |
|   5 |   3 | 72.5 |
|   3 |   3 | 66.6 |
+-----+-----+------+

9.查询2班的学生人数。
root@linux > select count(*) from student where class='2班';
+----------+
| count(*) |
+----------+
|        3 |
+----------+

10.查询”曾导“教师任课的学生成绩。
#老师名字	班级	学生姓名	分数
select teacher.tname,student.class,student.sname,score.mark
from teacher,course,student,score
where student.sno=score.sno and score.cno=course.cno 
and teacher.tno=course.cno
and teacher.tname='曾导';

+--------+-------+--------+------+
| tname  | class | sname  | mark |
+--------+-------+--------+------+
| 曾导   | 2班   | 小红   | 66.6 |
| 曾导   | 2班   | 小蔡   | 72.5 |
| 曾导   | 1班   | 小李   | 91.5 |
+--------+-------+--------+------+

11.查询语文课程所有男生的成绩并且查出对应课程的教师名，职称，及所在部门。
#男生名字	成绩	课程	老师	职称	部门
select student.sname,score.mark,course.cname,teacher.tname,teacher.prof,teacher.depart
from teacher,course,student,score
where student.sno=score.sno and score.cno=course.cno 
and teacher.tno=course.cno
and student.ssex='1';

+--------+------+--------+--------+--------------+-----------+
| sname  | mark | cname  | tname  | prof         | depart    |
+--------+------+--------+--------+--------------+-----------+
| 小明   | 90.2 | 英语   | 李导   | 教学总监     | 语言系    |
| 李明   | 90.0 | 英语   | 李导   | 教学总监     | 语言系    |
| 小王   | 78.5 | 英语   | 李导   | 教学总监     | 语言系    |
| 小明   | 82.5 | 数学   | 小秘   | 助教         | 文学系    |
| 李明   | 99.9 | 数学   | 小秘   | 助教         | 文学系    |
| 小王   | 66.6 | 数学   | 小秘   | 助教         | 文学系    |
| 小蔡   | 93.0 | 数学   | 小秘   | 助教         | 文学系    |
| 小蔡   | 72.5 | 语文   | 曾导   | 教学总监     | 科学系    |
| 小李   | 91.5 | 语文   | 曾导   | 教学总监     | 科学系    |
+--------+------+--------+--------+--------------+-----------+

12.把11题查出的成绩按照降序排序。

select student.sname,score.mark,course.cname,teacher.tname,teacher.prof,teacher.depart
from teacher,course,student,score
where student.sno=score.sno and score.cno=course.cno 
and teacher.tno=course.cno
and student.ssex='1' order by score.mark desc;

+--------+------+--------+--------+--------------+-----------+
| sname  | mark | cname  | tname  | prof         | depart    |
+--------+------+--------+--------+--------------+-----------+
| 李明   | 99.9 | 数学   | 小秘   | 助教         | 文学系    |
| 小蔡   | 93.0 | 数学   | 小秘   | 助教         | 文学系    |
| 小李   | 91.5 | 语文   | 曾导   | 教学总监     | 科学系    |
| 小明   | 90.2 | 英语   | 李导   | 教学总监     | 语言系    |
| 李明   | 90.0 | 英语   | 李导   | 教学总监     | 语言系    |
| 小明   | 82.5 | 数学   | 小秘   | 助教         | 文学系    |
| 小王   | 78.5 | 英语   | 李导   | 教学总监     | 语言系    |
| 小蔡   | 72.5 | 语文   | 曾导   | 教学总监     | 科学系    |
| 小王   | 66.6 | 数学   | 小秘   | 助教         | 文学系    |
+--------+------+--------+--------+--------------+-----------+