MySQL-day5-SQL语句

文章目录

MySQl数据库之SQL语句

MySQl数据库之SQL语句

一、SQL语句语义种类

1.sql语句语义种类

DDL：Data Query Language 数据定义语言（CREATE）
DCL：Data control Language 数据控制语言（GRANT，ROLLBACK，COMMIT）
DML：Data Manipulation Language 数据操作语言（INSERT，UPDATE，DELETE）
DQL：Data Query Language 数据查询语言（SELECT）
DPL：事务处理语言（BEGIN TRANSACTION、COMMIT和ROLLBACK）（了解）

2.DDL语句：数据定义语言（CREATE ，DROP，USE，SHOW，ALTER，ADD）

1）针对库的操作

1.使用help查看语句
mysql> help create database;
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name

2.创建oldboy数据库
mysql> create database lhd;
mysql> create schema lhd;

3.创建已存在数据库时会报错
mysql> create database if not exists lhd;

4.查看建库语句
mysql> show create database mysql;
mysql> show create database lhd;

5.创建数据库并制定字符集和校验规则
mysql> create database if not exists lhd collate utf8_general_ci charset utf8;

6.删库:
mysql> drop database lhd;

7.修改库:
mysql> alter database bgx1 charset utf8mb4;

2）针对表的操作

1>使用help查看语句
mysql> help create table;

2>建表
mysql> create table qiudao(id int);

3> 数据类型
int： 		整数 -2^31 ~ 2^31 -1
tinyint：	最小整数 -128 ~ 127		#一般年龄使用
varchar：	字符类型 （变长）		#一般身份证使用该类型
char： 	 	字符类型 （定长）		#KVM的磁盘格式：RAW：定长  QCOW2：变长
enum：   	枚举类型			   #性别使用，指定多项选其一
datetime：   时间类型 年月日时分秒
#timestamp   以前使用
PS：注意：当tinyint类型被指定非负数时，值会变，但范围不改不变

4.1>数据类型测试
mysql> desc qiudao;		# 查看qiudao表的属性
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

#插入数据，插入数据为10位可以，11位不可以
mysql> insert into qiudao values(11111111111);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into qiudao values(1111111111);
Query OK, 1 row affected (0.01 sec)

#插入数据的最大值不能大于2的31次方
mysql> insert into qiudao values(2147483648);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into qiudao values(2147483647);
Query OK, 1 row affected (0.00 sec)

#插入数据的负的最大值不能小于-2的31次方
mysql> insert into qiudao values(-2147483649);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into qiudao values(-2147483648);
Query OK, 1 row affected (0.01 sec)

4.2>建表
mysql> create table qiudao2(id int,name varchar(10),gender enum('male','fmale','qiudao'));
Query OK, 0 rows affected (0.01 sec)

#查看qiudao2表的属性
mysql> desc qiudao2;
+--------+-------------------------------+------+-----+---------+-------+
| Field  | Type                          | Null | Key | Default | Extra |
+--------+-------------------------------+------+-----+---------+-------+
| id     | int(11)                       | YES  |     | NULL    |       |
| name   | varchar(10)                   | YES  |     | NULL    |       |
| gender | enum('male','fmale','qiudao') | YES  |     | NULL    |       |
+--------+-------------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

#添加数据，性别选项只能填他给的选项，其他的不行
mysql> insert into qiudao2 values(1,'qiudao','nan');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1
mysql> insert into qiudao2 values(1,'qiudao','male');
Query OK, 1 row affected (0.00 sec)


4.3>建一个新表
create table qiudao3(
id int,
name varchar(10),
age tinyint,
gender enum('f','m'),
cometime datetime);

#插入一个数据，now()函数表示当前时间
mysql> insert into qiudao3 values(1,'qiudao',88,'f',now());

5>建表试题
表名:student
sid
sname
sage
sgender
scometime

create table student(
id int,
name varchar(10),
age tinyint,
gender enum('f','m'),
cometime datetime);

6>建表数据属性
not null： 			非空
primary key： 		主键（唯一且非空的）  #一套数据可以反复插入，没有主键的表没有意义
auto_increment： 	自增（此列必须是：primary key或者unique key）
unique key： 		单独的唯一的
default： 			默认值
unsigned： 			无符号,非负数
comment： 			注释

#唯一键与主键的区别，主键只能有一个不能为空，唯一键可以是多个可以为空，设置多个唯一键再加上非空属性可以当做多个主键，主键查询的速度快
primary key = unique key + not null

7>加上属性建表
create table student(
sid int not null primary key auto_increment comment '学号',
sname varchar(10) not null comment '姓名',
sage tinyint unsigned not null comment '年龄',
sgender enum('f','m') not null default 'm' comment '性别',
scometime datetime default now() comment '入学时间',
sbirthday datetime comment '学生生日',
class varchar(10) not null comment '学生班级');

#查看建表语句
mysql> show create table student;

8>删除表
mysql> drop table student;

9>修改表
#先创建一个表
mysql> create table students(zzy varchar(10));
Query OK, 0 rows affected (0.01 sec)

#修改表名
mysql> alter table students rename stu;
#添加列和列定义
mysql> alter table stu add zy varchar(10);
mysql> alter table stu add age int;
#添加多个列
mysql> alter table stu add qiudao varchar(20),add gcc int;
mysql> alter table stu add test varchar(20),add qq int;
#指定位置进行添加列（表首）
mysql> alter table stu add dsb varchar(20) first;
mysql> alter table stu add classid varchar(20) first;
#指定位置进行添加列（指定列）
mysql> alter table stu add ggj int after zy;
mysql> alter table stu add phone int after age;
#删除指定的列及定义
mysql> alter table stu drop zy;
mysql> alter table stu drop qq;
#修改列及定义（列属性）
mysql> alter table stu modify zzy varchar(10);
#修改列及定义（列名及属性）
mysql> alter table stu change zzy lhd char(20);
#删除列
mysql> alter table stu drop name;

二、DCL语句—数据控制语言（GRANT，revoke）

1.授权 grant

1.授权
mysql> grant all on *.* to root@'%' identified by '123';

2.查看用户权限
mysql> show grants for root@'%';

3.授权扩展
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connections_per_hour：一个用户每小时可连接到服务器的次数
max_user_connections：允许同时连接数量

mysql> grant all on *.* to test@'%' identified by '123' with max_user_connections 1;

2.回收权限 revoke

1.回收权限
mysql> revoke delete on *.* from root@'%';
Query OK, 0 rows affected (0.00 sec)

2.查看权限
mysql> show grants for root@'%';

3.授权一个超级管理员

mysql> grant all on *.* to lhd@'%' identified by '123' with grant option;

四、DML语句—数据操作语言（INSERT，UPDATE，DELETE）

1.insert命令

1.插入数据之前一定要先看表结构
mysql> desc qiudao3;

insert:插入数据，into可以省略
2.不规范写法，很容易值跟字段类型对不上
mysql> insert into oldtian5 values(1,'lhd',18,'m',now());
		
3.规范写法,插入一条数据
mysql> insert into oldtian5(name,age,gender) values('lhd',18,'f');
		
4.规范写法,插入多条数据
mysql> insert into oldtian5(name,age,gender) values('lhd',18,'m'),('qiudao',84,'f');

5.扩展
#数据库修改某一列为唯一键的时候，那一列的数据不能有重复数据
#所以可以使用函数计算那一列是否有重复数据

#数据条数 类似于 wc -l
mysql> select count(name) from qiudao3;

#数据去重 类似于 uniq -c
mysql> select distinct(name) from qiudao3;

#去重之后计数，当值与没去重条数一致时可以为该列加唯一键
mysql> select count(distinct(name)) from qiudao3;

#city表name列不能做唯一键或主键
mysql> select count(name) from city;
mysql> select distinct(name) from city;

#country表name列能做唯一键或主键
mysql> select count(Name) from country;
mysql> select distinct(Name) from country;

2.update命令

1.修改之前一定确认数据，查看数据
mysql> select * from qiudao3;
mysql> select * from qiudao.qiudao3;     #没进入库需要指定库   涉及一个绝对路径和相对路径的问题

2.修改数据，错误方法，整列全都修改成f
mysql> update student set gender='f';

3.使用update时,必须要接条件(where)，如果有主键就用主键，没有主键就多加几个条件确定数据再修改
mysql> update student set gender='f' where sid=1;

4.就是修改整列内容
mysql> update student set sgender='f' where 1=1;

3.delete命令

1.错误写法，删除整张表的数据
mysql> delete from student;

2.使用delete时,必须接条件(where)，如果有主键就用主键，没有主键就多加几个条件确定数据再修改
mysql> delete from student where id=3;

3.清空表
mysql> truncate table student;

4.使用update代替delete

1）额外添加一个状态列
mysql> alter table student add status enum(1,0) default 1;

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

3）应用查询存在的数据
#跟研发沟通好，以后查数据都要加上status条件
mysql> select * from student where status=1;

5.扩展：mysql触发器trigger（不讲）

创建触发器

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句

#可以说MySQL创建以下六种触发器：
BEFORE INSERT
BEFORE DELETE
BEFORE UPDATE
AFTER INSERT
AFTER DELETE
AFTER UPDATE

五、DQL语句—数据查询语言（SELECT）

1.select查询数据

1.查看表中所有内容(危险)，数据量过多可能会导致内存撑爆了
mysql> select * from qiudao.qiudao3;

2.查询数据之前先查看数据量
mysql> select count(*) from qiudao.qiudao3;

3.查看某几列中的内容
mysql> select gender,age from qiudao.qiudao3;

4.按条件查询
mysql> select gender,age from qiudao.qiudao3 where age=18 and gender='f';

2.1查询数据库测试

1.导入数据库 world.sql
mysql> \. /tmp/world.sql

2.查看库
mysql> use world;
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

3.查看city表中所有的内容
mysql> select * from city;

4.查询city表中的字段（表结构）
mysql> desc city;

5.查询列数据
mysql> select countrycode from city;
mysql> select countrycode,district from city;

排序可以使用 order by
6.按照人口数量排序(升序)
mysql> select * from city order by population;

7.按照人口数量排序(降序)	
mysql> select * from city order by population desc;
		
8.按照人口数量排序，前十的(limit)，行级查询
mysql> select * from city order by population limit 10;
		
	#8.1生产实例使用：按照步长60查找数据(商品页面翻页)
	mysql> select * from world.city limit 60		#第一页
	mysql> select * from world.city limit 60,60		#第二页
	mysql> select * from world.city limit 120,60	#第三页

2.2模拟翻页

[root@db02 tmp]# vim fanye.sh
#!/bin/bash
M='mysql'
read -p "请输入你要查看的页码:" page
if [ $page -eq 1 ];then
    buchang=0
    $M -e "select * from world.city limit $buchang,60"
elif [ $page -eq 2 ];then
    buchang=60
    $M -e "select * from world.city limit $buchang,60"
fi

3.按条件查询测试

1.条件查询where可以接的符号
where接条件: > < = >= <= != <> 不等于 like and or
=:精确查询
> < = >= <= != :范围查询
like:模糊查询

2.查询中国城市的人口
mysql> select name,population from city where countrycode='CHN';

3.查询中国黑龙江省的人口
mysql> select name,population from city where countrycode='CHN' and district='heilongjiang';

4.查询人口数量大于多少的城市
mysql> select * from city where population>=1410000;

5.模糊查询，跟正则不同，查询带H的使用 %H%，查询H开头的使用 H%，查询H结尾的，使用 %H
mysql> select * from world.city where countrycode like '%H%';

6.查询中国和美国的城市 or 和 in
mysql> select * from world.city where countrycode='CHN' or countrycode='USA';
mysql> select * from world.city where countrycode in ('CHN','USA');
# 排除
mysql> select * from world.city where countrycode not in ('CHN','USA');

# union all (联合查询) 讲索引的时候再说
mysql> select * from world.city where countrycode='USA' union all select * from world.city where countrycode='CHN';

六、select高级用法（连表查询,多表联查）传统连接

1.连表查询

集合
[qiudao,zhangyu,zhangyin]
[80,90,100]
# 只写两个集合，并不知道里面的值是什么，也不知道关联，所以一般会给他们一个定义

id:[1,2,3]
name:[qiudao,zhangyu,zhangyin]

id:[1,2,3]
length:[80,90,100]
mark:[80,90,100]

1.创建一个student表
mysql> create table student(id int,name varchar(10));

2.创建一个成绩表
mysql> create table score(id int,mark int)

3.查看表结构
mysql> desc student;
mysql> desc score;

4.插入数据
mysql> insert into student values(1,'qiudao'),(2,'zhangyu'),(3,'yinyin');
mysql> insert into score values(1,80),(2,90),(3,100);

5.查看数据
ysql> select * from student;
mysql> select * from score;

6.连表查询数据
	#错误写法
	mysql> select student.name,score.mark from student,score;
	mysql> select student.name,score.mark from student,score where name='qiudao';
	#必须找出两个表相关联的值
	mysql> select student.name,score.mark from student,score where id=1;
	mysql> select student.name,score.mark from student,score where student.id=score.id;
	mysql> select student.name,score.mark from student,score where student.id=2;
# 正确写法
mysql> select student.name,score.mark from student,score where student.id=score.id and name='qiudao';

2.连表查询练习

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

1.看看查询的内容有哪些
人口数量  城市名  国家名

2.查看表字段
mysql> desc city;
mysql> desc country;
mysql> desc countrylanguage;

3.找出要查询的值
人口数量  			城市名  		国家名
city.Population		city.name	  country.name

4.找三个表相关联的字段
city.CountryCode
country.code
countrylanguage.CountryCode

5.编写语句
mysql> select city.population,city.name,country.name from city,country where city.countrycode=country.code and city.population < 100;

2）查询人口数量小于100的城市在哪个国家,说的什么语言?

1.看看查询的内容有哪些
人口数量  城市名  国家名  语言

2.查看表字段
mysql> desc city;
mysql> desc country;
mysql> desc countrylanguage;

3.找出要查询的值
人口数量  			城市名  		国家名  		语言
city.Population		city.name	  country.name	  countrylanguage.language

4.找两个表相关联的字段
city.CountryCode
country.code

5.编写语句
mysql> select city.population,city.name,country.name,countrylanguage.language from city,country,countrylanguage where city.CountryCode=country.code and country.code=countrylanguage.CountryCode and city.population < 100;

七、select高级用法自连接

自连接的表要有共同的列名字和列数据

1.根据语句翻译要查询的内容

SELECT city.name,city.countrycode,countrylanguage.language,city.population
FROM city NATURAL JOIN countrylanguage 
WHERE population > 1000000
ORDER BY population;
#查询人口数量大于一百万的城市的国家代码和国家语言，并升序排序?

#刚才的题没有办法使用该连接方式，查不出来，因为没有相同字段，name字段相同，但是值不同
select city.population,city.name,country.name from city natural join country where city.population < 100;

注意:
	1.自己去查找两个表之间的关联字段(natural join) 主键:聚集索引
	2.查询的表内必须有相同的字段名和数据

八、select高级用法内连接

1.内连接格式:
select * from 表1 join 表2 on 关联条件 where 条件

# 驱动，命中率概念
重点:表1 (小表)
	表2 (大表)

1.查询人口数小于100的城市是哪个国家的，国家代码是多少
select city.name,city.countrycode,country.name 
from city join country on city.countrycode=country.code 
where city.population < 100;

1.查询人口数小于100的城市是哪个国家的，人数是多少
select city.name,city.population,country.name
from country join city on city.countrycode=country.code
where city.population < 100;

九、select高级用法外连接

1.左外连接

select city.name,city.countrycode,country.name from city left join country on city.countrycode=country.code and city.population < 100;

2.右外连接

select city.name,city.countrycode,country.name from city right join country on city.countrycode=country.code and city.population < 100;

十、概念及常用语句总结

字符编码：utf8mb4
utf8与utf8mb4区别：utf8mb4更新支持了emoji表情

1.数据库相关概念

数据：记录的事物的状态
记录：文件中的一行内容
表：文件
库：文件夹
数据库管理软件：套接字程序
数据库服务器：运行有数据库数据库管理软件服务端的计算机

2.针对库操作（文件夹）

增
create database db1 charset utf8mb4;

删
drop database db1;

改	# 更改编码格式
alter database db1 charset gbk;

查	# 查创建库时定义内容
show create database db1;

查看所有库
show databases;

进库	# 相当于cd命令
use db1;

调用函数功能查看所在当前库名称	# 相当于pwd命令
select database();

PS：库名建立后不可更改！

3.针对表操作

增	# 建表，必须指定字段
create table t1(id int,name varchar(20));

删	
drop table db1.t1;

改
alter table t1 rename t2;	# 改表名
alter table t2 modify name varchar(22);		# 改字段类型
alter table t2 change name mz varchar(22);	# 改字段名
alter table t2 drop mz;		# 删除mz字段
		
查
show tables;	# 查看当前库的所有表
show create table t1;	# 查看创建t1表时定义内容
desc t1;		# 查看表结构

复制db库下的t1表到新表t3
create table t3 select * from db.t1;

条件若为假，则只拷贝表结构（不包含索引）
create table t6 select user,host from mysql.user where 1>2

4.针对记录操作（编辑插入）

增
insert t1(id,name) values (1,"egon"),(2,"tom"),(3,"jack");

删
truncate t1;
delete from t1 where id=3;

改 
update db1.t1 set name="yyy" where id=1;
	
查
select * from t1 where id>1;

show engines;	# 查看所有引擎

PS：char、varchar
	- 大概率用varchar类型  节省硬盘存储空间  加快查询数据速度
	- 在明确知道几个子字符时采用char类型
	- 占用空间是小事，主要是为了提升查询速度

十一、综合练习题

1.在10.0.30.51以root账户登入mysql

[root@web03 ~]# mysql -uroot -p1

建表数据属性参考：

not null： 		非空
primary key： 	主键（唯一且非空的）
auto_increment： 自增（此列必须是：primary key或者unique key）
unique key： 	单独的唯一的
default： 		默认值
unsigned： 		无符号，非负数		#添加unsigned属性，会加到数据属性中，所以把这个属性写到数据属性后面
comment： 		注释
primary key = unique key + not null

2.新建库名为oldboy的库，库内建三个表

建表附属要求：尾列加入每行的注释，如：name最后注释为：姓名）
1.创建oldboy数据库并制定字符集和校验规则:
mysql> create database  if not exists oldboy collate utf8_general_ci charset utf8;
Query OK, 1 row affected (0.00 sec)

2.查看创建的oldboy库
mysql> show create database oldboy;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| oldboy   | CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

1）表1为：student，包含以下内容

已知学生名称：yao，daji，agui，wukong，含学生的基本信息：要求带数据属性创建student表
stu_id			int			   注释为：'学生编号'	# 主键（唯一且非空的）；且自增
stu_name		varchar(10)		注释为：'姓名'
stu_age			tinyint			注释为：'年龄'
stu_gender		men，women，中性  注释为：'性别'
stu_tel			char(11)		注释为：'手机号'
stu_cometime	datetime		注释为：'入学时间'

解答步骤

mysql> create table oldboy.student(
	stu_id int not null primary key auto_increment comment'学生编号',
	stu_name varchar(10) not null comment '学生姓名', 
	stu_age tinyint unsigned not null comment '年龄', 
	stu_gender enum('man','girl') not null default 'man' comment '性别',  
	stu_tel char(11) not null comment '手机号码',  
	stu_cometime datetime  default now() comment '入学时间'
	);
	
#给student表插入相应数据     
mysql> insert into oldboy.student values('1','yao','16','girl','12345678910',now()),('2','daji','17','girl','12345678911',now()),('3','agui','18','girl','12345678912',now()),('4','wukong','19','man','12345678913',now());
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

#查看student表数据
mysql> select * from oldboy.student;
+--------+----------+---------+------------+-------------+---------------------+
| stu_id | stu_name | stu_age | stu_gender | stu_tel     | stu_cometime        |
+--------+----------+---------+------------+-------------+---------------------+
|      1 | yao      |      16 | girl       | 12345678910 | 2021-02-25 19:21:28 |
|      2 | daji     |      17 | girl       | 12345678911 | 2021-02-25 19:21:28 |
|      3 | agui     |      18 | girl       | 12345678912 | 2021-02-25 19:21:28 |
|      4 | wukong   |      19 | man        | 12345678913 | 2021-02-25 19:21:28 |
+--------+----------+---------+------------+-------------+---------------------+
4 rows in set (0.01 sec)

2）表2为kcb，包含以下内容 # 含学生的科目与教学老师信息

kcb_id		注释为：'课程编号'
kcb_name	注释为：'课程名称'
kcb_techer	注释为：'教学老师'

需对应教学科目信息：
老刘	语文
egon  数学
陈阳	英语
崔萌	化学

解答步骤

#带数据属性创建kcb表
mysql> create table oldboy.kcb(kcb_id int not null primary key auto_increment comment '课程编码',
    -> kcb_name varchar(10) not null comment '课程名称',
    -> kcb_techer varchar(10) not null comment '教学老师');
Query OK, 0 rows affected (0.02 sec)
#给kcb表插入相应数据
mysql> insert into oldboy.kcb values ('1','语文','老刘'),('2','数学','egon'),('3',' 英语','陈阳'),('4','化学','崔萌');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
#查看kcb表数据
mysql> select * from oldboy.kcb;
+--------+----------+------------+
| kcb_id | kcb_name | kcb_techer |
+--------+----------+------------+
|      1 | 语文     | 老刘       |
|      2 | 数学     | egon       |
|      3 | 英语     | 陈阳       |
|      4 | 化学     | 崔萌       |
+--------+----------+------------+
4 rows in set (0.00 sec)

3）表3为score，包含学生的分数信息

	score_id	注释为：'成绩编号'
	stu_id		注释为：'学生编号'
	kcb_id		注释为：'课程编号'
	score_fs 	注释为：'考试分数'
已知学生名称：yao，daji，agui，wukong	#成绩示例：（分数单独放在表3内）
		语文		数学		英语		化学
yao ：	40		  70	    80		 20
daji：	50		  70	    27		 20
agui：	60		  38	    80		 18
wukong：	60		  70	    98		 20

解答步骤

#带数据属性创建score表
mysql> create table oldboy.score(score_id int not null primary key auto_increment comment '成绩编号',
    -> stu_id int not null comment '学生编号',
    -> kcb_id int not null comment '课程编号',
    -> score_fenshu int not null comment '分数');
Query OK, 0 rows affected (0.01 sec)

#给score表插入相应数据
mysql> insert into oldboy.score values(1,1,1,40),(2,1,2,70),(3,1,3,80),(4,1,4,20),(5,2,1,50),(6,2,2,70),(7,2,3,27),(8,2,4,20),(9,3,1,60),(10,3,2,38),(11,3,3,80),(12,3,4,18),(13,4,1,60),(14,4,2,70),(15,4,3,98),(16,4,4,20);
Query OK, 16 rows affected (0.01 sec)
Records: 16  Duplicates: 0  Warnings: 0

#查看score表的数据
mysql> select * from oldboy.score;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        1 |      1 |      1 |           40 |
|        2 |      1 |      2 |           70 |
|        3 |      1 |      3 |           80 |
|        4 |      1 |      4 |           20 |
|        5 |      2 |      1 |           50 |
|        6 |      2 |      2 |           70 |
|        7 |      2 |      3 |           27 |
|        8 |      2 |      4 |           20 |
|        9 |      3 |      1 |           60 |
|       10 |      3 |      2 |           38 |
|       11 |      3 |      3 |           80 |
|       12 |      3 |      4 |           18 |
|       13 |      4 |      1 |           60 |
|       14 |      4 |      2 |           70 |
|       15 |      4 |      3 |           98 |
|       16 |      4 |      4 |           20 |
+----------+--------+--------+--------------+
16 rows in set (0.00 sec)

3.建立wp用户并授权登陆

新建用户wp，并针对oldboy库给wp用户授权，其权限如下；授权网段为10.0.0.%，且密码为1 （select,delete,update,insert）	

#创建wp用户
mysql> create user wp@'10.0.0.%' identified by '1';
Query OK, 0 rows affected (0.02 sec)
#给wp用户授权限
mysql> grant select,delete,update,insert on oldboy.* to wp@'10.0.0.%' identified by '1';
Query OK, 0 rows affected (0.01 sec)
#查看用户权限
mysql> show grants for wp@'10.0.0.%';
+----------------------------------------------------------------------------------------------------------+
| Grants for wp@10.0.0.%                                                                                   |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wp'@'10.0.0.%' IDENTIFIED BY PASSWORD '*E6CC90B878B948C35E92B003C792C46C58C4AF40' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `oldboy`.* TO 'wp'@'10.0.0.%'                                    |
+----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

#退出mysql，用wp用户登入mysql，用wp用户来执行后续操作
[root@db01 ~]# mysql -uwp -p1

5.根据题目查看内容

1）查看agui的所有科目分数、教学老师

MySQL [oldboy]> select kcb.kcb_name,score.score_fenshu,kcb.kcb_techer from student,kcb,score where kcb.kcb_id=score.kcb_id and student.stu_id=score.stu_id and student.stu_name='agui';
+----------+--------------+------------+
| kcb_name | score_fenshu | kcb_techer |
+----------+--------------+------------+
| 语文     |           60 | 老刘       |
| 数学     |           38 | egon       |
| 英语     |           80 | 陈阳       |
| 化学     |           18 | 崔萌       |
+----------+--------------+------------+
4 rows in set (0.00 sec)

2）查看agui的所有科目分数、教学老师，且只显示agui的一个名字

MySQL [oldboy]> select student.stu_name,kcb.kcb_name,score.score_fenshu,kcb.kcb_techer from student,kcb,score where kcb.kcb_id=score.kcb_id and student.stu_id=score.stu_id and student.stu_name='agui';
+----------+----------+--------------+------------+
| stu_name | kcb_name | score_fenshu | kcb_techer |
+----------+----------+--------------+------------+
| agui     | 语文     |           60 | 老刘       |
| agui     | 数学     |           38 | egon       |
| agui     | 英语     |           80 | 陈阳       |
| agui     | 化学     |           18 | 崔萌       |
+----------+----------+--------------+------------+
4 rows in set (0.00 sec)

6.查看低于60分的名字、科目、教学老师

MySQL [oldboy]> select student.stu_name,kcb.kcb_name,kcb.kcb_techer from student,kcb,score where kcb.kcb_id=score.kcb_id and student.stu_id=score.stu_id and score.score_fenshu<60;
+----------+----------+------------+
| stu_name | kcb_name | kcb_techer |
+----------+----------+------------+
| yao      | 语文     | 老刘       |
| yao      | 化学     | 崔萌       |
| daji     | 语文     | 老刘       |
| daji     | 英语     | 陈阳       |
| daji     | 化学     | 崔萌       |
| agui     | 数学     | egon       |
| agui     | 化学     | 崔萌       |
| wukong   | 化学     | 崔萌       |
+----------+----------+------------+
8 rows in set (0.00 sec)

7.查看agui低于60分的科目，老师名字，并改为70分

#查看agui低于60分的科目，老师名字
MySQL [oldboy]> select kcb.kcb_name,kcb.kcb_techer from student,kcb,score where kcb.kcb_id=score.kcb_id and student.stu_name='agui' and student.stu_id=score.stu_id and score.score_fenshu<60 ;
+----------+------------+
| kcb_name | kcb_techer |
+----------+------------+
| 数学     | egon       |
| 化学     | 崔萌       |
+----------+------------+
2 rows in set (0.00 sec)

#改成70分
MySQL [oldboy]> update score,student,kcb set score.score_fenshu=70 where kcb.kcb_id=score.kcb_id and student.stu_name='agui' and student.stu_id=score.stu_id and score.score_fenshu<60;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

#查看改后表
MySQL [oldboy]> select * from score;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        1 |      1 |      1 |           40 |
|        2 |      1 |      2 |           70 |
|        3 |      1 |      3 |           80 |
|        4 |      1 |      4 |           20 |
|        5 |      2 |      1 |           50 |
|        6 |      2 |      2 |           70 |
|        7 |      2 |      3 |           27 |
|        8 |      2 |      4 |           20 |
|        9 |      3 |      1 |           60 |
|       10 |      3 |      2 |           70 |
|       11 |      3 |      3 |           80 |
|       12 |      3 |      4 |           70 |
|       13 |      4 |      1 |           60 |
|       14 |      4 |      2 |           70 |
|       15 |      4 |      3 |           98 |
|       16 |      4 |      4 |           20 |
+----------+--------+--------+--------------+
16 rows in set (0.00 sec)

8.查看所有低于50分同学的名字、科目、教学老师，并将分数置零

#查看所有低于50分同学的名字、科目、教学老师
MySQL [oldboy]> select  student.stu_name,kcb.kcb_name,kcb.kcb_techer from student,kcb,score where kcb.kcb_id=score.kcb_id and student.stu_id=score.stu_id and score.score_fenshu<50 ;
+----------+----------+------------+
| stu_name | kcb_name | kcb_techer |
+----------+----------+------------+
| yao      | 语文     | 老刘       |
| yao      | 化学     | 崔萌       |
| daji     | 英语     | 陈阳       |
| daji     | 化学     | 崔萌       |
| wukong   | 化学     | 崔萌       |
+----------+----------+------------+
5 rows in set (0.00 sec)

#将分数置零
MySQL [oldboy]> update score,student,kcb set score.score_fenshu=0 where kcb.kcb_id=score.kcb_id and student.stu_id=score.stu_id and score.score_fenshu<50;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5  Changed: 5  Warnings: 0

#查看改后分数
MySQL [oldboy]> select * from score;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        1 |      1 |      1 |            0 |
|        2 |      1 |      2 |           70 |
|        3 |      1 |      3 |           80 |
|        4 |      1 |      4 |            0 |
|        5 |      2 |      1 |           50 |
|        6 |      2 |      2 |           70 |
|        7 |      2 |      3 |            0 |
|        8 |      2 |      4 |            0 |
|        9 |      3 |      1 |           60 |
|       10 |      3 |      2 |           70 |
|       11 |      3 |      3 |           80 |
|       12 |      3 |      4 |           70 |
|       13 |      4 |      1 |           60 |
|       14 |      4 |      2 |           70 |
|       15 |      4 |      3 |           98 |
|       16 |      4 |      4 |            0 |
+----------+--------+--------+--------------+
16 rows in set (0.00 sec)

9.查看所有分数为0的同学名字、科目，并改为60分

#查看所有分数为0的同学名字、科目，
MySQL [oldboy]> select  student.stu_name,kcb.kcb_name from student,kcb,score where kcb.kcb_id=score.kcb_id and student.stu_id=score.stu_id and score.score_fenshu=0 ;
+----------+----------+
| stu_name | kcb_name |
+----------+----------+
| yao      | 语文     |
| yao      | 化学     |
| daji     | 英语     |
| daji     | 化学     |
| wukong   | 化学     |
+----------+----------+
5 rows in set (0.01 sec)

#改为60
MySQL [oldboy]> update score,student,kcb set score.score_fenshu=60 where kcb.kcb_id=score.kcb_id and student.stu_id=score.stu_id and score.score_fenshu=0;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

#查看改后表
MySQL [oldboy]> select * from score;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        1 |      1 |      1 |           60 |
|        2 |      1 |      2 |           70 |
|        3 |      1 |      3 |           80 |
|        4 |      1 |      4 |           60 |
|        5 |      2 |      1 |           50 |
|        6 |      2 |      2 |           70 |
|        7 |      2 |      3 |           60 |
|        8 |      2 |      4 |           60 |
|        9 |      3 |      1 |           60 |
|       10 |      3 |      2 |           70 |
|       11 |      3 |      3 |           80 |
|       12 |      3 |      4 |           70 |
|       13 |      4 |      1 |           60 |
|       14 |      4 |      2 |           70 |
|       15 |      4 |      3 |           98 |
|       16 |      4 |      4 |           60 |
+----------+--------+--------+--------------+
16 rows in set (0.00 sec)

10.将agui的语文分数改为59分，并将教学老师改为cuimeng

#语文成绩改为59，语文老师改成崔萌
MySQL [oldboy]> update score,student,kcb set score.score_fenshu=59,kcb.kcb_techer='崔萌' where kcb.kcb_id=score.kcb_id and student.stu_id=score.stu_id and kcb.kcb_name='语文'and student.stu_name='agui';

#查看改后成绩
MySQL [oldboy]> select * from score;
+----------+--------+--------+--------------+
| score_id | stu_id | kcb_id | score_fenshu |
+----------+--------+--------+--------------+
|        1 |      1 |      1 |           40 |
|        2 |      1 |      2 |           70 |
|        3 |      1 |      3 |           80 |
|        4 |      1 |      4 |           60 |
|        5 |      2 |      1 |           50 |
|        6 |      2 |      2 |           70 |
|        7 |      2 |      3 |           60 |
|        8 |      2 |      4 |           60 |
|        9 |      3 |      1 |           59 |
|       10 |      3 |      2 |           70 |
|       11 |      3 |      3 |           80 |
|       12 |      3 |      4 |           70 |
|       13 |      4 |      1 |           60 |
|       14 |      4 |      2 |           70 |
|       15 |      4 |      3 |           98 |
|       16 |      4 |      4 |           60 |
+----------+--------+--------+--------------+
16 rows in set (0.01 sec)

#查看改后语文老师
MySQL [oldboy]> select student.stu_name,kcb.kcb_techer from student,kcb where student.stu_name='agui' and kcb.kcb_techer='崔萌' and kcb.kcb_name='语文';
+----------+------------+
| stu_name | kcb_techer |
+----------+------------+
| agui     | 崔萌       |
+----------+------------+
1 row in set (0.00 sec)

posted @ 2022-06-09 18:19 秋风お亦冷阅读(25) 评论(0) 编辑收藏举报

刷新页面返回顶部

秋风お亦冷

MySQL-day5-SQL语句

文章目录

MySQl数据库之SQL语句

一、SQL语句语义种类

1.sql语句语义种类

2.DDL语句：数据定义语言（CREATE ，DROP，USE，SHOW，ALTER，ADD）

1）针对库的操作

2）针对表的操作

二、DCL语句—数据控制语言（GRANT，revoke）

1.授权 grant

2.回收权限 revoke

3.授权一个超级管理员

四、DML语句—数据操作语言（INSERT，UPDATE，DELETE）

1.insert命令

2.update命令

3.delete命令

4.使用update代替delete

5.扩展：mysql触发器trigger（不讲）

创建触发器

五、DQL语句—数据查询语言（SELECT）

1.select查询数据

2.1查询数据库测试

2.2模拟翻页

3.按条件查询测试

六、select高级用法（连表查询,多表联查）传统连接

1.连表查询

2.连表查询练习

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

2）查询人口数量小于100的城市在哪个国家,说的什么语言?

七、select高级用法 自连接

1.根据语句翻译要查询的内容

八、select高级用法 内连接

九、select高级用法 外连接

1.左外连接

2.右外连接

十、概念及常用语句总结

1.数据库相关概念

2.针对库操作（文件夹）

3.针对表操作

4.针对记录操作（编辑插入）

十一、综合练习题

1.在10.0.30.51以root账户登入mysql

建表数据属性参考：

2.新建库名为oldboy的库，库内建三个表

1）表1为：student，包含以下内容

解答步骤

2）表2为kcb，包含以下内容 # 含学生的科目与教学老师信息

解答步骤

3）表3为score，包含学生的分数信息

解答步骤

3.建立wp用户并授权登陆

5.根据题目查看内容

1）查看agui的所有科目分数、教学老师

2）查看agui的所有科目分数、教学老师，且只显示agui的一个名字

6.查看低于60分的名字、科目、教学老师

7.查看agui低于60分的科目，老师名字，并改为70分

8.查看所有低于50分同学的名字、科目、教学老师，并将分数置零

9.查看所有分数为0的同学名字、科目，并改为60分

10.将agui的语文分数改为59分，并将教学老师改为cuimeng

公告

七、select高级用法自连接

八、select高级用法内连接

九、select高级用法外连接