MySQL-day6-条件语句与增删改查练习题

文章目录

SQL语句

SQL语句

一、SQL语句语义种类

- DDL: Data definition Language 	数据定义语言	( create、drop、alter )
- DCL: Data control Language 		数据控制语言	( grant、revoke、commit、rollback )
- DML: Data Manipulation Language	数据操作语言	( insert、delete、update )
- DQL: Data Query Language			数据查询语言	( select )

DDL、DML、DQL、DCL之间的区别：

二、DDL语句—数据定义语言

(Data Definition Language 数据定义语言）用于操作对象及对象本身，这种对象包括数据库,表对象，及视图对象。

包含的操作语句：

create:创建数据库和数据库的一些对象
drop:删除数据表、索引、触发程序、条件约束以及数据表的权限等
alter:修改数据表定义及数据属性

1.CREATE针对库的操作

1）查看语法

mysql> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...

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

2）创建库

mysql> create database qiudao;
Query OK, 1 row affected (0.00 sec)

mysql> create schema qiudao;
ERROR 1007 (HY000): Can't create database 'qiudao'; database exists

3）创建数据库已存在会报错，使用IF NOT EXISTS

mysql> create database qiudao;
ERROR 1007 (HY000): Can't create database 'qiudao'; database exists

mysql> create database if not exists qiudao;
Query OK, 1 row affected, 1 warning (0.00 sec)

4）查看建库语句

mysql> show create database qiudao;
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| qiudao   | CREATE DATABASE `qiudao` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

5）创建数据库指定字符集和校验规则

mysql> create database if not exists qiudao charset utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

6）删库

mysql> drop database qiudao;
Query OK, 0 rows affected (0.00 sec)

7）修改库

mysql> alter database qiudao charset utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)

2.CREATE 针对表的操作

1）查看建表语法

mysql> help create table;
Name: 'CREATE TABLE'
Description:
Syntax:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

2）建表

#进入qiudao库
mysql> use qiudao
Database changed

#查看所在库
mysql> select database();
+------------+
| database() |
+------------+
| qiudao     |
+------------+
1 row in set (0.00 sec)

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

3）数据类型

int：			整数 -2^31 ~ 2^31-1    （-2147483648  ~  2147483647）
tinyint：		最小整数 -128 ~ 127		#一般年龄使用
varchar：		字符类型（变长）		 #身份证 231026161616161616161X
char：			字符类型（定长）
enum：			枚举类型			  #填写性别，指定多个选项，选择其中一个
datetime：		时间类型	年月日时分秒

4）数据类型测试

#建表测试int类型
mysql> create table qiudao(id int);

#查看数据表结构
mysql> desc qiudao;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

#插入数据库失败
mysql> insert into qiudao  values(11111111111);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> insert into qiudao  values(2222222222);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

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);
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)

mysql> insert into qiudao  values(-2147483648);
Query OK, 1 row affected (0.00 sec)

#原因：
	因为int类型的范围是-2147483648到2147483647之间的数

#建表测试varchar和enum类型
mysql> create table qiudao2(id int,name varchar(10),gender enum('m','f','qiudao'));
Query OK, 0 rows affected (0.01 sec)

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

#插入数据
#name是varchar（10）位，不能超过10位字符
mysql> insert into qiudao2 values(1,'qiudaosgdsb','nv');
ERROR 1406 (22001): Data too long for column 'name' at row 1

#gender是enum类型，必须填写指定选项
mysql> insert into qiudao2 values(1,'qiudaodsb','nv');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

mysql> insert into qiudao2 values(1,'qiudaodsb','f');
Query OK, 1 row affected (0.00 sec)

#建表测试datetime
mysql> create table qiudao3( id int, name varchar(10), age tinyint, gender enum('m','f'), Birthday datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> desc qiudao3;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| id       | int(11)       | YES  |     | NULL    |       |
| name     | varchar(10)   | YES  |     | NULL    |       |
| age      | tinyint(4)    | YES  |     | NULL    |       |
| gender   | enum('m','f') | YES  |     | NULL    |       |
| Birthday | datetime      | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

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;


#插入数据
#第一种方式
insert into student values('1','sxj',22,'man',now(),'12345678901','11期');

#第二种方式
insert into student (sname,sage,stel) values('ycy',28,'12345678902');

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 oldboy char(20);

#删除列
mysql> alter table stu drop name;

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

（Data Control Language 数据控制语句）用于操作数据库对象的权限

包含的操作语句：

greate:分配权限给用户
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 oldboy@'%' identified by '123' with grant option;

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

（Data Manipulation Language 数据操控语言) 用于操作数据库对象对象中包含的数据

包含的操作语句：

insert:向数据库插入一条数据
delete:删除表中的一条或多条记录
update:用于修改表中的数据

1.insert命令

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

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

扩展

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

#数据条数 类似于 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;

不讲：扩展：mysql触发器trigger

1）创建触发器

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）

(Data Query Language 数据查询语言 )用于查询数据

包含的操作语句：

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.导入数据库 world.sql
mysql>source /usr/local/packages/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	#第三页

模拟翻页

[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';

4. select设置别名

#1.为每个查询字段设置别名显示
mysql> select name as '名称',population as '人口' from city limit 60;

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

1.连表查询

#1.创建student表、kcb表、score表，分别记录学生信息、课程信息、分数信息。
mysql> create table student( stu_id int primary key not null auto_increment comment '学生编号', 
    -> stu_name varchar(20) not null comment '学生姓名',
    -> stu_gender enum('f','m') default 'f' comment '性别',
    -> stu_tel char(11) not null comment '手机号码',
    -> stu_address varchar(20) comment '住址');
    
    
mysql> create table kcb( 
	-> kcb_id int not null primary key auto_increment comment '课程编号',  
	-> kcb_name varchar(10) not null comment '课程名称',  
	-> kcb_teacher varchar(10) not null comment '讲师');
	
	
mysql> create table score(
    -> score_id int not null primary key auto_increment comment '成绩编号',
    -> stu_id int not null comment '学生编号',
    -> kcb_id int not null commnet '课程编号',
    -> score_fenshu int not null comment '分数'
    -> );
    
#2.查看表结构
mysql> desc student;
mysql> desc score;
mysql> desc kcb;
 
 
#3.插入数据
mysql> insert into student values(1,'cuij','f','11111111111','shandong'),(2,'xzx','m','11111111112','anhui'),(3,'sxj','f','11111111113','harbin'),(4,'ycy','f','11111111114','gansu');


mysql> insert into kcb values(1,'shuxue','lin'),(2,'yuwen','cui'),(3,'english','chen');


mysql> insert into score values(1,1,1,77),(2,1,2,82),(3,1,3,55),(4,2,1,91),(5,2,2,78),(6,2,3,80),(7,3,1,69),(8,3,2,88),(9,3,3,70),(10,4,1,77),(11,4,2,60),(12,4,3,76);


#4.查看分数超过80分的所有学生的姓名，课程内容，分数信息
mysql> select student.stu_name,kcb.kcb_name,score.score_fenshu from student,kcb,score where score.stu_id=student.stu_id and score.kcb_id=kcb.kcb_id and score.score_fenshu > 80;

#5.查看考试分数超过80分的所有学生的姓名，课程内容，分数信息，讲师信息
mysql> select student.stu_name,kcb.kcb_name,score.score_fenshu,kcb.kcb_teacher from student,kcb,score where score.stu_id=student.stu_idd and score.kcb_id=kcb.kcb_id and score.score_fenshu > 80;


#6.查看所有学生的姓名以及考试成绩并对所有学生的成绩按照降序排列
mysql> select a.stu_name,b.kcb_name,c.score_fenshu from student a,kcb b,score c where c.stu_id=a.stu_id and c.kcb_id=b.kcb_id order by c.score_fenshu desc;

#7.将cuij同学的数学分数更新为100分。
mysql> select c.score_fenshu from student a,kcb b,score c where c.stu_id=a.stu_id and c.kcb_id=b.kcb_id and a.stu_name='cuij' and b.kcb_name='shuxue';

update student a,kcb b,score c set c.score_fenshu=100 where c.stu_id=a.stu_id and c.kcb_id=b.kcb_id and a.stu_name='cuij' and b.kcb_nam.kcb_name='shuxue';

update score,student,kcb set score.score_fenshu=99 where score.stu_id=student.stu_id and score.kcb_id=kcb.kcb_id and student.stu_name='u_name='cuij' and kcb.kcb_name='shuxue';

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.查看分数超过80分的所有学生的姓名，课程内容，分数信息
mysql> select student.stu_name,kcb.kcb_name,score.score_fenshu from student join kcb join score on score.stu_id=student.stu_id and score.kcb_id=kcb.kcb_id and score.score_fenshu > 80;

九、select高级用法外连接

1.左外连接

mysql> select a.stu_name,a.stu_gender,b.stu_id,b.kcb_id,b.score_fenshu from student a left join score b on a.stu_id=b.stu_id and b.scorre_fenshu>80;

2.右外连接

mysql> select a.stu_name,a.stu_gender,b.stu_id,b.kcb_id,b.score_fenshu from student a right join score b on a.stu_id=b.stu_id and b.scoore_fenshu>80;

十、 mysql权限管理

MySQL 赋予用户权限命令的简单格式可概括为：

grant 权限 on 数据库对象 to 用户

1、grant 普通数据用户，查询、插入、更新、删除数据库中所有表数据的权利。

grant select on testdb.* to common_user@'%'
grant insert on testdb.* to common_user@'%'
grant update on testdb.* to common_user@'%'
grant delete on testdb.* to common_user@'%'

或者，用一条 MySQL 命令来替代：

grant select, insert, update, delete on testdb.* to common_user@'%'

2、grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限。

grant 创建、修改、删除 MySQL 数据表结构权限。

grant create on testdb.* to developer@'192.168.0.%';
grant alter  on testdb.* to developer@'192.168.0.%';
grant drop   on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 外键权限。

grant references on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 临时表权限。

grant create temporary tables on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 索引权限。

grant index on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 视图、查看视图源代码权限。

grant create view on testdb.* to developer@'192.168.0.%';
grant show   view on testdb.* to developer@'192.168.0.%';

grant 操作 MySQL 存储过程、函数权限。

grant create routine on testdb.* to developer@'192.168.0.%'; -- now, can show procedure status
grant alter  routine on testdb.* to developer@'192.168.0.%'; -- now, you can drop a procedure
grant execute        on testdb.* to developer@'192.168.0.%';

3、grant 普通 DBA 管理某个 MySQL 数据库的权限。

grant all privileges on testdb.* to dba@'localhost'

其中，关键字 “privileges” 可以省略。

4、grant 高级 DBA 管理 MySQL 中所有数据库的权限。

grant all on *.* to dba@'localhost'

5、MySQL grant 权限，分别可以作用在多个层次上。

(1) grant 作用在整个 MySQL 服务器上：

grant select on *.* to dba@localhost; -- dba 可以查询 MySQL 中所有数据库中的表。
grant all    on *.* to dba@localhost; -- dba 可以管理 MySQL 中的所有数据库

(2)grant 作用在单个数据库上：

grant select on testdb.* to dba@localhost; -- dba 可以查询 testdb 中的表。

(3). grant 作用在单个数据表上：

grant select, insert, update, delete on testdb.orders to dba@localhost;

这里在给一个用户授权多张表时，可以多次执行以上语句。例如：

grant select(user_id,username) on test.users to mo_user@'%' identified by '123345';
grant select on test.table_name to mo_user@'%' identified by '123345';

(4)grant 作用在表中的列上：

grant select(id, se, rank) on testdb.table_name to dba@localhost;

(5) grant 作用在存储过程、函数上：

grant execute on procedure testdb.pr_add to 'dba'@'localhost'
grant execute on function testdb.fn_add to 'dba'@'localhost'

6、查看 MySQL 用户权限

查看当前用户（自己）权限：

show grants;

查看其他 MySQL 用户权限：

show grants for dba@localhost;

7、撤销已经赋予给 MySQL 用户权限的权限。

revoke 跟 grant 的语法差不多，只需要把关键字 “to” 换成 “from” 即可：

grant  all on *.* to   dba@localhost;
revoke all on *.* from dba@localhost;

8、MySQL grant、revoke 用户权限注意事项

(1) grant, revoke 用户权限后，该用户只有重新连接 MySQL 数据库，权限才能生效。

(2)如果想让授权的用户，也可以将这些权限 grant 给其他用户，需要选项 “grant option“

grant select on testdb.* to dba@localhost with grant option;

十一、Mysql练习题：数据的插入与增、删、改、查

1.在10.0.0.51以root账户登入mysql

[root@web03 ~]# mysql -uroot -p
Enter password:

建表数据属性参考

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

2.创建oldboy数据库

建库要求：制定字符集和校验规则

1.建库：
mysql> create database  if not exists oldboy collate utf8_general_ci charset utf8;
Query OK, 1 row affected (0.00 sec)

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

3.库内建三个表

建表要求：尾列加入每行的注释 #如：name最后注释为：姓名

1)表一：student

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

1.带数据属性创建student表
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 '入学时间');

2.给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

3.查看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，包含以下内容	# 含学生的科目与教学老师信息

	kcb_id		注释为：'课程编号'
	kcb_name	注释为：'课程名称'
	kcb_techer	注释为：'教学老师'
	
	需对应教学科目信息：
	老刘	语文
	egon  数学
	陈阳	英语
	崔萌	化学
	
1.带数据属性创建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)

2.给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

3.查看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，包含学生的分数信息
	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

1.带数据属性创建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)

2.给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

3.查看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)

4.新建用户wp

要求：针对oldboy库给wp用户授权，授权网段为10.0.0.%，且密码为1，其权限如下：
select,delete,update,insert

1.创建wp用户
mysql> create user wp@'10.0.0.%' identified by '1';
Query OK, 0 rows affected (0.02 sec)

2.给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)

3.查看用户权限
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)

5.用wp用户登入mysql

退出mysql，用wp用户登入mysql，用wp用户来执行后续操作

注意：登录期间可能会报错，重新将其授权即可，授权时@符号两边若有引号，都需要与报错提示的一致才行！

[root@db01 ~]# mysql -uwp -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'zh'@'localhost' (using password: YES)

6.查看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)

7.查看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)

8.查看低于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)

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

1.查看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)

2.改成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

3.查看改后表
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)

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

1.查看所有低于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)

2.将分数置零
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

3.查看改后分数
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)

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

1.查看所有分数为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)

2.改为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

3.查看改后表
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)

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

1.语文成绩改为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';

2.查看改后成绩
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)

3.查看改后语文老师
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 秋风お亦冷阅读(55) 评论(0) 编辑收藏举报

刷新页面返回顶部

秋风お亦冷

MySQL-day6-条件语句与增删改查练习题

文章目录

SQL语句

一、SQL语句语义种类

二、DDL语句—数据定义语言

包含的操作语句：

1.CREATE针对库的操作

1）查看语法

2）创建库

3）创建数据库已存在会报错，使用IF NOT EXISTS

4）查看建库语句

5）创建数据库指定字符集和校验规则

6）删库

7）修改库

2.CREATE 针对表的操作

1）查看建表语法

2）建表

3）数据类型

4）数据类型测试

5）建表试题

6）建表数据属性

7）加上属性建表

8）删除表

9) 修改表

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

包含的操作语句：

1.授权 grant

2.回收权限 revoke

3.授权一个超级管理员

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

包含的操作语句：

1.insert命令

扩展

2.update命令

3.delete命令

不讲：扩展：mysql触发器trigger

1）创建触发器

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

包含的操作语句：

1.select查询数据

2.查询数据库测试

模拟翻页

3.按条件查询测试

4. select设置别名

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

1.连表查询

2.连表查询练习

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

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

七、select高级用法 自连接

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

八、select高级用法 内连接

九、select高级用法 外连接

1.左外连接

2.右外连接

十、 mysql权限管理

十一、Mysql练习题：数据的插入与增、删、改、查

1.在10.0.0.51以root账户登入mysql

建表数据属性参考

2.创建oldboy数据库

3.库内建三个表

1)表一：student

2)表2：kcb

3）表3：score

4.新建用户wp

5.用wp用户登入mysql

6.查看agui的所有科目分数、教学老师

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

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

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

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

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

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

公告

七、select高级用法自连接

八、select高级用法内连接

九、select高级用法外连接