Mysql数据库基本操作（尚硅谷）

一、MySQL数据库基础

保存在内存中的数据具有易失性

1.1 数据库相关概念

1.DB:数据库，保存一组有组织的数据的容器。
2.DBMS:数据库管理系统，用于管理DB中的数据。
3.SQL：结构化查询语言，用于和DBMS通信的语言。

1.2 mysql的启动与停止

net stop mysql0815（服务名）
net start mysql0815（服务名）

1.3 mysql服务端的登入与退出

通过windows自带的客户端：

登入：mysql 【-h localhost（主机名） -P 3306（端口号）】 -u root（用户名） -p（密码）
登出：exit

1.4 mysql常见命令

1.查看数据库的分库
show databases;
2.查看mysql中的表
show tables from mysql;
3.进入分库
use+库名;
4.查看当前库中的表
show tables;
5.查看自己当前所在库
select database();
6.在库中建立新表
create table 表名(
列名 列类型，
列名 列类型，
...
)
7.查看表的结构
desc+表名
8.往库中插入数据
insert into 表名(id,name)values(2,'rose');
9.更改库中的数据
update 表名 set name='lilei' where id=1;
10.删除库中的数据
delete from 表名 where id=1;
11.查看服务器版本
方式一：登入到mysql服务器
select version()
12.取别名
方式一:使用as
select last_name as 姓,first_name as 名 from employees;
方式二:使用空格
select last_name 姓,first_name 名 from employees;
13.遇到使用的名字为关键字的情况
方式一：使用双引号
方式二：使用着重号
14.去重
select distinct department_id from employees;
15.加号的作用,将两个合并为一个字段
select last_name+first_name as 姓名 from employees;
或者用
select concat(last_name,first_name) as 姓名 from employees;
16.显示出employees的全部列，各个列之间用逗号隔开，列头显示为out_put
select 
     concat("first_name",",","last_name",",",**ifnull(commission_pct,0)**)as out_put
from 
    employees;

1.5 mysql语法

#select 查询列表 from 表名
特点：
    +查询结果可以是：表中的字段、常量值、表达式、函数
    +查询结果是一个虚拟的表格
1.查询表中的单个字段
select last_name from employees;
2.查询表中的多个字段（可以用F12进行格式化）
select last_name,salary,email from employees;
3.查询表中的所有字段
select *from employees;

二、条件查询

语法：
    select 
          查询列表
    from
        表名
    where
        筛选条件;
分类：
    一、按条件表达式筛选
      条件运算符：
                >,<,=,!=,<>,>=,<=
    二、按逻辑表达式筛选
       逻辑运算符：
                 &&,||,!
                 and or not
    三、模糊查询
       like:
            特点：
                通常和特殊字符搭配使用，%代表任意个字符，_代表单个字符
            案例:查询员工名中包含字母a的员工信息
                select * from employees where last_name like '%a%';
       between and:
            案例：查询员工编号在100到120之间的员工信息
                select *from employees where employee_id between 100 and 120;
       in:
            案例：查询员工的工种编号是IF_PROG,AD_VP,AD_PRES中的一个员工名和工种编号
                select first_name,job_id from employees 
            where job_id in('if_prog','ad_vp','ad_pres');
       is null:
            案例：查询没有奖金的员工名和奖金率
                 select last_name,commission_pct from employees
            where commission_pct is null;

2.1 排序查询

语法：
    select 查询列表
    from 表
    [where 筛选条件]
    order by 排序列表 [asc(升序）|desc(降序)];默认升序

2.2 常见函数

调用：
     select 函数名（实参列表）[from  表];
分类：
    1.单行函数
    如concat,ifnull,length等
    2.分组函数
    功能：做统计使用
    分类：
        sum 求和、avg 平均值、max 最大值、min 最小值、count 统计个数

2.3 字符函数

1.length()获取参数值的字节个数
      select length('john');（一个字母占一个字节，一个汉字三个字节）
2.concat()拼接字符串
      select concat('last_name','_','first_name') 姓名 from employees;
3.upper,lower转换大小写
      select upper('John');
      select concat(upper(last_name),lower(first_name)) 姓名 from employees;
4.substr,substring
      注意：索引从1开始
      *截取从指定索引处后面的所有字符
      select substr('李莫愁爱上了陆展元',7) out_put;//输出为陆展元
      *截取从指定索引处指定长度的字符
      select substr('李莫愁爱上了陆展元',1,3) out_put;//输出为李莫愁
5.instr返回字串第一次出现的索引，如果搜不到则返回0
      select instr('杨六侠爱上了杨不悔','杨不悔');//输出为7；
6.trim去掉前后相同的字符，默认去空格
      select trim('    张三丰    ');//输出为张三丰
      select trim('a','aaaaaaa张aa三丰aaaaa');//输出为张aa三丰；
7.lpad用指定的字符实现左填充指定长度
      select lpad('殷素素',5,'*')as out_put;//输出**殷素素 
8.rpad用指定的字符实现左填充指定长度
      select rpad('殷素素',5,'*')as out_put;//输出殷素素**
9.replace替换
      select replace('周芷若张无忌爱上了周芷若','周芷若','赵敏')as out_put;

2.4 数学函数

1.round四舍五入
      select round(1.55);//输出2
      select round(1.567,2);//输出1.57；
2.ceil向上取整，返回大于等于该参数的最小整数
      select ceil(1.002);//输出2
3.floor向下取整，返回小于等于该参数的最大整数
4.truncate截断
      select truncate(1.6777,1);//输出为1.6
5.mod取余
      select mod(10,3);//输出为1

2.5 日期函数

1.now返回当前系统日期+时间
2.curdate返回当前系统日期，不包含时间
3.curtime返回当前日期，不包含时间
4.可以获取指定的年月日小时分钟秒
  select year(now()) 年;
5.str_to_date将字符通过指定的格式转换为日期
6.date_to_format将日期转换为字符

2.6 流程控制函数

1.if函数:if else 的效果
      select if(10>5,'大','小');
2.case语句
      case 要判断的字段或表达式
    when 常量1 then 要显示的值1或表达式1;
    when 常量2 then 要显示的值2或表达式2;
    ...
    else 要显示的值n或表达式n;
    end;

2.7 分组查询

语法：
       select 分组函数、列（要求出现在group by的后面）
       from 表
       [where 筛选条件]//根据行筛选
       group by 分组的
       [having 筛选条件]//根据分组后的结果筛选
       [order by字句]
案例一：查询每个工种的最高工资
       select max(salary),job_id from employees
       group by job_id;

2.8 连接查询

含义：又称多表查询，当查询的字段来自多个表时就会用到连接查询
案例一：
        select name,boyname from beauty,boys  
  where beauty.boyfriend_id=boys.id;

三、sql99语法

语法：
      select 查询列表
      from 表1 别名 
      【连接类型】join 表2 别名
      on 连接条件
      【where 筛选条件】
      【group by 分组】
      【having 筛选条件】
      【order by 排序列表】 
分类：
      [内连接]：inner join
      [外连接]：
            左外：left【outer】join;
            右外：right【outer】join;
            全外：full【outer】join;
      [交叉连接]：cross join; 

3.1 连接

3.1.1 外连接

应用场景：查询一个表里有，另一个表里没有的情况
特    点：
          1.外连接的查询结果为主表中的所有记录
       如果从表中有和它匹配的，则显示匹配的值
       如果没有匹配的，则显示为null
       外连接查询结果=内连接查询结果+主表中有而从表中没有的记录
          2.左外连接：left join左边的是主表
       右外连接：right join右边的是主表
          3.全外连接查询结果=内连接查询结果+表1有表2没有的+表2有表1没有的
案例一：查询男朋友不在男神表里的女神的名字
        select b.name
        from beauty b
        left outer join boys bo
        on b.boyfriend_id=bo.id
        where bo.id is null;
案例二：查询哪个部门没有员工
        select d.*,e.employee_id
        from department d
        left outer join employee e
        on d.employee_id=e.employee_id
        where e.employee_id is null;
        group by d.name;

3.1.2 交叉连接

select b.*,bo.*
from beauty b
cross join boys bo;
//单独显示每个对应值

3.3 子查询

含义：
    出现在其他语句中的select语句，称为子查询或内查询
    外部的查询语句称为主查询或外查询
分类：
	按子查询出现的位置：
		select后面：
			仅仅支持标量子查询
		from后面：
			支持表子查询
		where或having后面（重点）：
			标量子查询，列子查询，行子查询
		exists后面：
			表子查询
	按结果集的行列数不同：
		标量子查询（结果集只有一行一列）
		列子查询（结果集只有一列多行）
		行子查询（结果集只有一行多列）
		表子查询（结果集为多行多列）

3.3.1 where或having后面（重点）：

[标量子查询]：
	案例一：查询工资比Abel高的员工信息 
	select *
	from employees
	where salary>(
			select salary
			from employees
			where last_name='Abel'
	);
	特点：
		1.标量子查询一般搭配着单行操作符使用，
		>,<,>=,<=,<>,=
		2.列子查询一般搭配着多行操作符使用
		in,any/some,all
列子查询
行子查询

3.3.2 select后面：

3.3.3 from后面：

案例一：查询每个部门的平均工资的工资等级
	select ag_dep.*,g.grade_level
	from(
		select avg(salary) ag,department_id
		from employees
		group by department_id
    )ag_dep
	inner join job_grades g
	on ag_dep.ag between g.lowest_sal and g.highest_sal;

3.3.4 exists后面（相关子查询）

案例一：查询有员工的部门名
	1.in 
	select department_name
	from departments d
	where d.department_id in(
		select department_id
		from empolyees 
	);
	2.exists
	select department_name
	from departments d
	where exists(
		select *
		from employees e
		where e.department_id=d.department_id
	); 

3.4 分页查询

应用场景：当要显示的数据一页显示不全，需要分页提交sql请求
语   法：
		select 查询列表			7
		from 表                 1
		【join type join 表2     2
		on 连接条件              3
		where 筛选条件           4
		group by 分组字段        5
		having by 分组后的筛选    6
		order by 排序字段】		8
		limit 【offset,】size;    9

		offset要显示条目的起始索引（起始索引从0开始）
		size要显示的条目个数
特  点：
		公式：要显示的页数page，每页的条目数size
		select 查询列表
		from 表
		limit (page-1)*size,size;

3.5 联合查询

unoin 联合 合并：将多条查询语句的结果合并为一个结果
语法：
	查询语句1
	union
	查询语句2
	union
	...
应用场景：
	要查询的结果来自多个表，且多个表没有直接的连接关系，但查询的信息一致时
特   点：
	1.要求多条查询语句的查询列数是一样的
	2.要求多条查询语句的查询的每一列的类型和顺序最好一致
	3.union关键字默认去重，如果使用union all可以包含重复项

四、DML语言

数据操作语言：
	插入：insert
	修改：update
	删除：delete

4.1 insert

语法：
	方式一：insert into 表名（列名,...)values(值1,...);
 	方式二：inset into 表名
		   set 列名=值，列名=值,...
	//方式一支持子查询，方式二不支持
	//方式一支持插入多行，方式二不支持

4.2 update

4.2.1 修改单表的记录【重点】

语法：
	update 表名
	set 列=新值，列=新值,...
	where 筛选条件;

4.2.2 修改多表的记录【补充】

sql92语法：
	update 表1 别名，表2 别名
	set 列=值,...
	where 连接条件
	and 筛选条件
案例一：修改没有男朋友的女神的男朋友编号都为2号
	update boys bo
	right join beauty b on bo.id=b.boyfriend_id
	set b.boyfriend_id=2
	where bo.id is null;

4.3 删除语句

方式一：delete 
语法：
	1.单表的删除【重点】
	delete from 表名 where 筛选条件
	2.多表的删除
	sql92语法：
		delete 表1的别名，表2的别名
		from 表1 别名，表2 别名
		where 连接条件
		and 筛选条件
	sql99语法：
		delete 表1的别名，表2的别名
		from 表1 别名
		inner|left|right join 表2 别名 on 连接条件
		where 筛选条件；
方式二：truncate
语法：truncate table 表名

五、DDL

数据定义语言

库和表的管理

5.1 库的管理

创建、修改、删除

5.1.1 库的创建

语法：
	create database 库名；
案例：创建库Books
	create database if not exists books;

5.1.2 库的删除

drop database if exists books；

5.2 表的管理

创建、修改、删除

创建：create

修改：alter

删除：drop

5.2.1 表的创建

语法：
	create table 表名(
		列名 列的类型【（长度） 约束，
		列名 列的类型【（长度） 约束，
		列名 列的类型【（长度） 约束，
		...
		列名 列的类型【（长度） 约束，
	)
案例：创建表book
	create table book(
		id int,
		bName varchar(20),
		price double,
		authorId int,
		publishDate datetime
	);

5.2.2 表的修改

alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;

5.2.2.1 修改列名

alter table book change column publishdate pubDate datetime;

5.2.2.2 修改类的类型或约束

alter table book modify column pubdate timestamp;

5.2.2.3 添加新列

alter table author add column annual double;

5.2.2.4 删除列

alter table author drop column annual;

5.2.2.5 修改表名

alter table author rename to book_author;

5.2.3 表的删除

drop table if exists book_author;

4.表的复制

4.1 仅复制表的结构

create table copy like author;
create table copy (列名+类型);

4.2 复制表的结构+数据

create table copy2
select*from author;

4.3 复制表的部分数据

create table copy2
select id,au_name
from author
where nation='中国';

5.常见的数据类型

数值型：
整数
小数：
定点型
浮点型
字符型：
较短的文本：char,varchar
较长的文本：text，blob（较长的二进制数据）

5.1整形

分类：
	tinyint,smallint,mediumint,int,bigint
特点：
	如果不设置有符号还是无符号，默认是有符号。如果不设置无符号，需要在后面加unsigned。
	如果插入的数值超出了范围，会报异常，并且插入的是临界值（新版本会报错）

5.1.1 如何设置无符号

drop table if exists tab_int;
create table tab_int(
	t1 int,
	t2 int unsigned
);
create table tab_int(
	t1 int(7)zerofill,
);

5.2 小数

分类：
	浮点型
		float(M,D)
		double(M,D)
	定点型
		dec(M,D)
		decimal(M,D)
特点：
	M:整数部分+小数部分
	D:小数位数
	如果超出范围，则插入临界值
	M,D都可以省略，如果是decimal，M默认为10，D默认为0。float根据输入的数值决定
	定点型的精度高

5.3 字符型

			写法			M的意思		特点				空间的耗费	效率
char		char(M)		最大字符数	固定长度的字符	比较耗费		高
varchar		VARCHAR(M)  最大字符数	可变长度的字符	比较节省		低

5.4 枚举类型

create table test(
	c1 enum('a',b','c')
);

5.5 集合类型

set

5.6日期函数

分类：
	date只保存日期
	time只保存时间
	year只保存年
	datetime保存日期+时间
	timestamp保存日期+时间
	
				字节		范围			时区的影响
	datetime	8		 1000-9999	   不受
	timestamp	4		 1970-2038	   受

5.7常见约束

含义：一种限制，用于限制表中的数据，为了保证表中数据的准确和可靠性

分类：
六大约束：
	not null：非空，用于保证该字段的数值不为空
	比如姓名、学号等

	default：用于保证该字段有默认值
	比如性别

	primary key：主键，用于保证该字段的值具有唯一性，且非空
	比如学号、员工编号等

	unique：唯一，用于保证该字段的值有唯一性，可以为空
	比如座位号

	check：检查约束【MySQL不支持】

	foreign key：外键约束，用于限制两表的关系，用于保证该字段的值必须来自主表关联列的值。
	在从表中添加外键约束，用于引用主表中某列的值
	比如学生表的专业编号，员工表的部门编号