RDBMS--Day03

子查询

select查询命令里包含select查询命令，包涵的select 命令 放在() 里

俄罗斯套娃

包含的select查询命令可以

在where 命令之后 、having命令之后 、 from命令之后、select命令之后

select  *  from salary 
where year(date)=2018 and month(date)=12 and 
basic>(select basic from salary where year(date)=2018 and 
month(date)=12 and employee_id=100);

select  dept_id ,  count(name) as total    from employees group by dept_id
having  total < (
select count(name) from employees  where dept_id=(
select dept_id from departments where dept_name='开发部')
);

select * from salary 
where  year(date)=2018 and month(date)=12  
and 
basic=(select max(basic) from salary where year(date)=2018 and month(date)=12) 
and 
bonus=(select max(bonus) from salary where year(date)=2018 and month(date)=12);

select dept_id, dept_name, employee_id, name, email  from (
select  d.dept_name, e.*  from departments as d  inner join employees as e 
on d.dept_id=e.dept_id ) as tmp_table  where dept_id=3;

库管理

库名是有命名规则 （可以创建多个库，多个库通过库名区分）

库名仅可以使用数字、字母、下划线、不能纯数字 命名

库名区分字母大小写，

库名具有唯一性

库名不可使用命令、特殊字符 命名

mysql> create database GAMEDB ;  重名报错
加if not exists 命令避免重名报错
mysql> create database  if not exists  gamedb ;  正常
drop database gamedb

表管理

表管理

表是存储数据的文件

drop table 学生库.学生信息表;

修改表

alter table 库名.表名 操作命令；

add 添加新表头 first(默认) after xxx

drop 删除表头

modify 修改表头数据类型

change 修改表头名

rename 修改表名

alter table  studb.stuinfo 
add  number  char(9) first , 
add  school char(10) after name;

alter table stuinfo modify mail varchar(20);
alter table stuinfo modify mail varchar(20) after name;

alter table stuinfo change class 班级 char(9);

alter table studb.stu rename studb.stuinfo;

复制表

拷贝已有的表 和系统命令 cp 的功能一样

表头和数据都复制命令

create table 库名.表名 select * from 库名.表名 ；

仅仅复制表头命令

create table 库.表 like 库.表；

插入记录命令

不指定表头名插入记录，必须给所有表头赋值且

值的顺序必须和表头顺序一致，数据类型要匹配

insert into 库名.表名 values();

//插入1行

INSERT INTO 库名.表名 VALUES (值列表);

//插入多行

INSERT INTO 库名.表名 VALUES (值列表),(值列表),...;

指定表头名插入记录，仅须给指定表头赋值

值和表头顺序要一致 数据类型要匹配;没有赋值的列没有数据

//插入1行

INSERT INTO 库名.表名(表头名列表) VALUES (值列表);

//插入多行

INSERT INTO 库名.表名(表头名列表) VALUES (值列表),(值列表),...;

添加记录的其他方法

insert into studb.stuinfo set number="nsd211107" ,  gender="boy";
mysql> select  * from studb.stuinfo;
insert into tarena.user(uid,name) 
(select employee_id,name from tarena.employees 
where name in ("lucy","lili","bob")) ;

更新 ：修改行中列的数据 使用update

批量修改例子

update tarena.user set password="A";

加筛选条件 仅修改与条件匹配表头的值

update studb.stuinfo set gender="boy" where gender is null ;

删除表中行 使用delete

有筛选条件 仅删除符合条件的行

delete from 库.表 where 筛选条件；

delete  from  studb.stuinfo where gender="boy";

没有筛选条件 删除表里所有行

delete from 库.表；

 delete  from  studb.stuinfo;

数据类型

字符类型 (存储汉字或英文字母)

varchar 和char varchar速度慢

不补空格 和补空格

默认不允许表头存储中文 要存储中文 建表 要指定表使用中文字符集

show create table studb.t3 \G   //查看数据库的字符集
 CREATE TABLE `t3` (
  `name` char(3) DEFAULT NULL,
  `address` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

枚举类型 表头值必须在类型规定的范围内选择

单选 enum(值1，值2，值3 ....) 只能选项范围内的任意一个

多选 set(值1，值2，值3 ....) 选项范围内的一个或多个

create  table studb.t8(
姓名 char(10),
性别  enum("男","女","保密"), 
爱好 set("帅哥","金钱","吃","睡") 
) DEFAULT CHARSET utf8;

 insert into studb.t8 values ("小包总","男","帅哥,睡,金钱");
 insert into studb.t8 values ("丫丫","女","帅哥,吃");
 select * from studb.t8;

create  table studb.t8(
姓名 char(10),
性别  enum("男","女","保密"), 
爱好 set("帅哥","金钱","吃","睡") 
) DEFAULT CHARSET utf8;

日期时间类型 存储日期时间格式的数据

类 型	名称	范围	赋值格式
year	年	1901~2155	例如 2022
date	日期	0001-01-01 9999-12-31	例如 20220318
time	时间	00:00:00 23:59:59	例如 091858
datetime	日期时间	1000-01-01 00:00:00~ 9999-12-31 23:59:59	例如 20220819153819
timestamp		1970-01-01 00:00:00 2038-01-19 00:00:00

create table studb.t6( 
姓名  char(10), 
生日  date , 
出生年份 year , 
家庭聚会  datetime , 
聚会地点  varchar(15), 
上班时间 time
) default charset utf8;

insert into  studb.t6 
values ("翠花",20211120,1990,20220101183000,"小学校",090000);

使用2位数给year的表头赋值也是可以的 但会自动补全4位数

01-69 之间的数字使用20补全4位数的年 2001~2069

70-99 之间的数字使用19补全4位数的年 1970~1999

datetime 与 timestamp 的区别

第一个区别是存储范围不一样 （datetime存储范围大 timestamp范围小）

datetime 1000~9999

timestamp 1970-1-1

第二个区别：

datetime类型 不赋值 使用null 赋值

timestamp类型 不赋值 使用系统时间自动赋值

使用 system 可以在mysql 提示符下执行 操作系统命令

system date
system ls
system cat

其他字符类型：用来存储视频、音频、图片、较大的文本

类 型	名称	范围
tinytext	短文本	0-255字节
tinyblob	二进制形式短文本字	0-255字节
text	长文本数据	0-65535字节
blob	二进制形式的长文本	0-65535字节
mediumblob	二进制形式的中等长度文本	0-16777215字节
mediumtext	中等长度文本数据	0-16777215字节
longblob	二进制形式极大文本	0-4284867295字节
longtext	极大文本	0-4284867295字节

数值类型 ：表头存储数字，分为 整数类型 和 浮点类型 2种

整数类型（能存储正整数和负整数的类型） 例如 23（正整数） -23（负整数）
每种整数类型分为无符号存储范围和有符号存储范围
无符号存储范围起始数字从 数字零开始
有符号存储范围 可以存储负数
使用unsigned 命令定义使用数值类型的无符号存储范围
整数类型不存储小数，如果数字有小数的话会把小数部分四舍五入后只保存整数部分

类 型	名称	有符号范围	无符号范围
tinyint	微小整数	-128~127	0 ~ 255
smallint	小整数	-32768~32767	0 ~ 65535
mediumint	中整型	-223 ~ 223-1	0 ~ 224-1
int	大整型	-231 ~ 231-1	0 ~ 232-1
bigint	极大整型	-263 ~ 263-1	0 ~ 264-1
unsigned	使用无符号存储范围

浮点类型(存储小数) 例如 23.22

类 型	名称	范围
float	单精度	8位精度(4字节)
double	双精度	16位精度(8字节)

你需要知道

1 简述MySQL数据库中插入、更新、查询、删除表记录的指令格式。

1. /一次插入一条记录 给记录的所有字段赋值

2. insert into 库.表 values(值列表);

3. //一次插入多条记录 给记录的所有字段赋值

4. insert into 库.表 values(值列表),(值列表);

5. //一次插入1条记录 给记录的指定字段赋值

6. insert into 库.表(字段名列表)values(值列表);

7. //一次插入多条记录 给记录的指定字段赋值

8. insert into 库.表(字段名列表)values(值列表)，(值列表);

9. //批量修改

10. update 表名 set 字段名=值，字段名="值";

11. //修改符合条件的记录字段的值

12. update 表名 set 字段名=值，字段名="值" where 条件;

13. //查询所有记录表头名

14. select 字段列表 from 表名;

15. //查询与条件匹配记录表头名

16. select 字段列表 from 表名 where 条件表达式列表；

17. delete from 表名； //删除所有表记录。

18. delete from 表名 where 条件； //仅删除符合条件的表记录

2 查询综合练习题，按要求写出对应查询语句。

1. 显示uid 是四位数的用户的用户名和uid号。

select name,uid from userdb.userlist where uid >=1000 and uid<=9999;

2. 显示名字是以字母r 开头 且是以字母d结尾的用户名和uid号。

3. select name,uid from userdb.userlist where name regexp '^r.*d$';

4. 查看gid 小于10的用户使用shell的种类。

5. Select distinct shell from userdb.userlist where uid<10；

6. 查看shell不是/bin/bash用户中uid号最大用户名及uid号。

7. select name,uid from userdb.userlist where shell!=”/bin/bash” order by uid

8. desc limit 1;

9. 统计uid是3位数的用户的个数。

10. select count(name) from userdb.userlist where uid >=100 and uid<=999;

3 请列出MySQL常用的数据类型，并写出定义这些数据类型所使用的命令。

1. MySQL常用的数据类型有：字符类型、数值类型、日期时间类型、枚举类型

2. 字符类型：　char　、　varchar　、　blob、　text

3. 数值类型：　tinyint 、smallint　、int　、bigint 、float 、 double

4. 日期时间类型： year 、 date 、 time 、 datetime 、 timestamp

5. 枚举类型： enum 、 set