Mysql

mysql数据库

mysql数据库

概述

一 mysql概述
二 mysql数据库的搭建*
三 mysql数据库软件的使用
四 mysql数据类型
五 mysql数据库数据的操作*

一 初识数据库

数据库database DB:存储数据的仓库,数据的集合---文件
数据库管理系统 database management system DBMS ----操作和管理数据库的软件
数据库软件 database 

<1>数据库的类型
	关系型数据库:二维表形式存储的数据(见图)
	非关系型数据库:nosql---not only sql
			redis:键值对存储--k-v形式存储
			mongoDB:文档存储:将数据以文档存储,eg json/xml
			HBase:列存储,Hadoop Database
			
<2>主流关系型数据库
			oracle 甲骨文公司
			sql sever 微软
					简单 界面友好,交互和操作性更强
					开发性差 性能低
			db2:IMB
					伸缩性比较强,适用搜易的主流平台
			Myasql: mysql ab ---现也是甲骨文

2 mysql数据库概述

<1>mysql数据库
			mysql是一个关系型数据库管理系统,由瑞典的mysql AB公司开发,现属于oracle旗下
		<2>版本
			社区版--免费
			企业版--非盈利版权,收取一定的服务费
		
		<3>特点
			1.开源 自由的关系型数据库管理系统
			2.短信精悍,速度快,拥有成本低
			3.是LAMP流行网站架构的一部分(linux+Apache+mysql+php)
			4.被官方的运用在互联网公司中(中小型网站)

二 安装mysql数据库

<1>安装方式

zip压缩安装
			msi安装,windows installer微软格式的安装包安装

<2>注意事项:以前安装过,需要卸载干净再安装

1.关闭mysql服务
			2.卸载mysql软件(控制面板---程序---卸载程序--右键卸载mysql文件)
			3.删除残留文件1:原安装盘
			4.删除残留文件2:c:\programdata
			5.删除从哪里文件配置参数:
					打开注册表(cmd---regedit)-----xxxxmachine---system----001/002/service----eventlog---application---删除mysql

<3>安装

msi安装方式(操作和配置更加简单)
		注意:字符集的选择,推荐utf-8

三 使用mysql软件

<1>启动服务

cmd----- net start/stop mysql

<2>登录数据库

mysql -参数
		参数: -u 用户名
					-p 密码
					-p 端口号
					-h 数据库的服务器地址(远程连接数据库)
					-- prompt 设置提示

<3>退出数据库

ctrl+c
		exit
		\q
		quit

<4>修改密码

4.1第一种忘记密码时使用
		(1)停止mysql服务器
		(2)在dos下输入 mysqld --skip-grand-tables
		(3)再新开一个dos登录mysql数据库(不需要密码)
		(4)更改密码:
						use mysql  选择名称是mysql的数据库
						update user set password-password('密码')where user='root';
		(5)关闭dos窗口,结束mysqld进程
		(6)重启mysql服务,使用新的密码登录
		
		4.2第二种当你没有忘记密码时候
			4.2.1 mysqladmin -u 用户名 - p password 新密码(回车输入旧密码)
			4.2.1 mysqladmin -u 用户名 -p 密码 password 新密码

<5>修改mysql的提示符

方式一 prompt hehe
		方式二 mysql -uroot -p123456 --prompt 提示符
                eg mysql -uroot -p123456 --prompt 你好

<6>识别版本号

select version();

四 mysql数据类型

常用的数据类型
	1.整型
		int:整型,存储整数
		int(M):M表示预期值.与存储大小和数值的范围无关
		id int                    1000     1
		id int(3)zreofill         1000     001
    2.浮点型
    	float(M,D):浮点型,存储小数
		double:浮点型,小数
    3.字符型
        char(M)固定长度的字符串类型
		varchar(M)可变长度的字符串类型
		text:大的字符串累成,4G字符串
		blob:字节类型
    4.日期
       date:日期类型,年月日              yyyy-mm-dd日期格式
	   time:时间类型,时分秒              HH:mm:ss时间格式
	   year:年份						   YYYY格式的年份值
	   timestamp:年月日 时分秒  时间戳   YYYYMMDDhhmmss
	   datetime:年月日时分秒             YY-MM-DD hh:mm:ss
  5.整型
     tinyint  1  -128~127 含负数 或 0-255无符合 unsigned
	 smallint 2  3276765535
	 int      4  21亿
	 bigint   8  兆
	 mediumint3  八百多万/一千六百多万

	注意:选择合适的数据类型,是数据库友好的一部分,short it better原则

五 mysql数据库中的数据的使用

如何操作:编程语言

<1>sql概述

结构化查询语言,可以用于查询和修改关系数据库 sql语言 是数据库的核心语言,被iso采纳为数据库语言的国际标准,几乎所有的数据库都支持sql

<2>组成 ddl dml dql tcl dcl

数据定义语言-ddl--data definition language 用于管理和定义数据对象,包括数据库数据表等

create 创建数据库和表 drop 删除表对象 alter 修改表结果

数据操作语言-dml--data manipulate language 用于操作数据库对象中所包含的数据

insert 添加数据 delete 删除数据 update 更新数据

数据查询语言<dql>-- data query language
用于查询数据库对象所包含的数据 select:查询数据

事务控制语言<tcl>--transaction control language 包含提交事务,事务回滚

commit 提交事务 rollback事务回滚 savepoint事务节点

数据控制语言-dcl--data control language 用来管理数据库中管理权限以及数据更改

grant授权 revoke收回权限

1.数据定义语言DDL

数据库的操作

<1>创建数据库
		语法格式:create database [if not exists] 数据库名称 [character set [=] 编码名称];
		
		if not exists---屏蔽错误,显示警告---查看警告 show warnings;
		字符集可设置可不设置
		create database db character set gbk;
		<2>查询数据库
		语法格式:show databases;//查询所有的数据库
		                  show create database 数据库名称;//查询某一个已经创建的数据库
		<3>修改数据库
		      语法格式:alter database [if not exists]数据库名称 [character set 新的编码名称]
		<4>删除数据库
		     语法格式:drop database 数据库名称

数据表的操作

<1>创建数据表

使用一个数据库:use数据库名称;
查看当前正在使用的数据库,select database();

创建数据表语法:
create table 表名(
	字段名 字段类型 约束,
	字段名 字段类型 约束,
	......
);
eg  
create table user(
	uid int,
	username varchar(32),
	password varchar(32),
	birthday date,
	sex  enum('男','女')
);

<2>查询数据表

show tables;//显示当前数据库中所有的数据表
show columns form 表名;//查询某张表的表结构
desc 表名;//查询某张表的表结构

<3>修改数据表

3.1修改表名
(1)alter table 表名 rename [as|to] 新表名;  alter table user us;
(2)rename table 旧表名 to 新表名;  rename table us to user;

3.2添加列
   语法格式:alter table 表名 add [column] 列名 列定义 [before|after列名];
   默认在最后
   alter table user add hobby varchar(32);

3.3修改列定义
  语法格式:alter table 表名 modify 列名 列定义
3.4修改列名
				语法格式
				 alter table 表名 change  列的列名  新列名  新的列定义   [first|after列名];
3.5 删除列
				语法格式
				   alter table 表名 drop [column] 列名;

<4>删除数据表

语法格式:drop table 表名
			 eg drop table user;

2.数据操作语言 DML

<1>插入操作

insert [into] 表名 [(列名1,列名2,...)] values  |value  (数值1|表达式1,数值2|表达式2,....)

<2>删除操作

delete from 表名 [where 条件];
delete from teacher where id=1;//where指定删除
delete from teacher;//不带条件删除,删除所有的记录

扩展: truncate
 truncate table 表名;//清空表,会清空表中所有的数据
 
 面试题目:deletetruncate删除有啥区别
 			delete:一条一条删除,不清空表 auto_increment记录数
			truncate直接将表中的数据进行清空,auto_increment自增将被重置,重新开始

<3>修改操作

语法格式:update 表名 set 列名1=数据1,列名2=数据2 where 条件;

不带条件修改,修改列全部
update teacher set name = "小三";//所有的人名都变小三了

3 数据查询语言 DQL

完整语法格式
select 表达式|字段,....from 表名
[where 条件]
[group by 列名]
[having 条件]
[order by 列名 [asc升序|desc降序]]
[limit 位置,数量]--分页
<1>普通查询
	select 查询表达式;
	select database();
	select version();
	select now();
<2>条件查询
	where 条件表达式,试吃运算符和函数
	
	mysql支持的运算符:=/!=/>/>=/</<=/<>
	                                  and or not
									  is null   is not null   在数据库中null永远不等于null ,需要用is null/is not null
									  between...and...   区间查询
									  in(set)
									  like 通配符和占位符: % _(模糊查询)
									 							%表示0个或者多个字符
																_表示占位1位   当查询自带_时需要\反斜杠转义
	<3>分组查询
		[group by 列名][having 条件]一般情况分组查询结合聚合函数一起使用 min max sum avg count
		select * from teacher group by dname
		
		记住,分组的正确使用方式,group by 后面没有出现的列名不能出现在selectfrom之间
		虽然不保存,但是不是分组的正确打开方式
		聚合函数中出现的列名无所谓
		
		select dname,avg(sal) from teacher group by dname --查询每个部门的平均薪资
		select dname,avg(sal) from teacher group by dname having avg(sal)>5000--查询平均薪资大约5k的部门

	<4>排序查询
		语法格式:oeder by 列名 asc|desc  默认asc升序
		eg 查询老师信息,要求根据薪资从高到低
		select * from teacher order by sal desc  --降序
		
	<5>限制结果集数量的查询(分页)
		语法格式: limit n 条数;从第一条开始取n条数据(了解)
		语法格式:limit start 开始下标索引,count条数; --从其实位置start取count条数据(起始位置从0开始)
		
					分页(每页显示2条数据)
					  第一页 select * from teacher limit 0,2;
					  第二页 select * from teacher limit 2,2;
					  第三页 select * from teacher limit 4,2;
					  第四页 select * from teacher limit 6,2;
					  
					  
					  分页公式
					  		开始下标索引(其实位置)=(分页页数-1)*每页显示数
	
拓展:别名
			给查询的字段设置别名,同时也可以给表设置别名,通过 as  ,有时候as可省略(待补脑)
		eg select name as "姓名",sal as"薪资",dname as "部门名称" from teacher

事务控制语言TCL

在mysql数据库中,只有使用innodb数据库引擎的数据表或库才支持事务

通过事务来管理insert update delete语句

事务必须满足4个条件(ACID)
<1>原子性:要么全部完成,要么全部不完成.执行过程中一旦出现异常,会被回滚rollback到事务开始前状态
<2>一致性:事务处理前后的数据保存一致
<3>隔离性:事务处理必须是独立的,彼此隔离
<4>持久性:事务对数据的修改被永久保存

为啥使用事务
	银行转账
	事务广泛运用:订单系统,银行系统
mysql事务控制
	commit提交  rollback 回滚  savepoint事务节点
实战操作
	create table student(
		id int,
		name varchar(32),
		age int,
		money double
	);
	
	insert into student value(1,'老王',18,60000);
	
	语法: set autocommit = false手动提交  |true 自动提交  
	         rollback;事务回滚
			 commit;事务提交
			 savepoint 节点名称;设置回盾的节点
			 rollback to 节点名称;回滚到具体的某个节点
			 
			 
				例如:
				set autocommit  = false;//设置事务手动提交
				
				delete from student where id=1;//删除id为1 的信息
				rollback;//事务回滚
				commit;//事务提交

				update student set money = money-30000 where id=1;
				savepoint t1;//设置事务节点

				update student set money = money-20000 where id=1;

				rollback to t1;//回滚到t1节点位置
				commit;//事务提交

约束和多表查询

<1>概念

约束是一种限制,它是对表的行和列的数据做出约束,确保表中数据的完整性和唯一性

<2>使用场景

创建表的时候,添加约束

<3>分类

  1. default:默认约束,域完整性
  2. not null:非空约束,域完整性
  3. unique:唯一约束,实体完整性
  4. primary key:主键约束,实体完整性
  5. foreign key:外键约束,参照完整性
  6. check:检查约束(MySQL不支持),域完整性
  7. atuo_increment:自增长约束
  8. unsigned:无符号约束
  9. zerofill:零填充约束
数据库中有单个完整性:域 实体 参照完整性
	域(列)完整性:对数据表中字段属性的约束
	实体完整性:通过主键约束和候选键约束实现
	参照完整性:是mysql的外键

1.default约束

<1>概念
		指定某列的默认值,插入数据的时候此列没有值,则用default指定的值来填充
<2>添加
		在创建表的时候添加 create ... default
		eg create table t1(
			id int default 1,
			name varchar(20) default "小明"
		);
		通过alter语句添加:alter ...modify/change....
		
		alter table t1 modify id int default 2;
		alter table t1 change name varchar(32)default "大拿";
<3>删除 alter ...modify/change
		alter table t1 modify id int;
		alter table t1 change name name varchar(32);

2.not null

<1>概念
指定某列的值不为空,在插入数据的时候必须非空 "" 不等于 null ,0不等于 null

<2>添加
	在创建表的时候添加   : create .....not null
		create table t1(
			id int not null,
			name varchar(20) not null
		);

	通过alter语句添加:alter....modify/change.........
		alter table t2 modify id int;
		alter table t2 change name name varchar(32) null;   

<3>删除: alter.....modify/change

3.unique

<1>概念
	指定列或者列组合不能重复,保证数据的唯一性
	不能不限重复的值,但是可以有多个null
	同一张表可以有多个唯一的约束

<2>添加唯一约束
	在创建表的时候添加   : create .....unique
		create table t3(
			id int unique,
			name varchar(32) not null
		);

		insert t3 value (1,'老王');
		insert t3 value (1,'老李'); -- id唯一约束,添加异常



		create table t3(
			id int,
			name varchar(32) not null,
			constraint id_unique unique(id,name)   --  添加复合约束
		);


	通过alter语句添加:alter....modify/change.........         /  alter .... add unique.....
		alter table t3 modify id int unique;
		alter table t3 add unique(name);
		alter table t3 add constraint un_id unique(id);


<3>删除唯一约束:alter .... drop...index 名称            / drop index  名称 on 表名 

		alter table t3 drop index id_unique 

<4>注意:如果删除的唯一约束列具有自增长约束,则必须要先删除自增长约束,再去删除唯一约束。

4.主键约束

<1>概念
	当前行的数据不为空并且不能重复
	相当于:唯一约束+非空约束

<2>添加主键约束
	在创建表的时候添加   : create .....primary key

		create table t4(
			id int primary key,
			name varchar(20)
		);


	create table t4(
		id int,
		name varchar(32),
		[constraint id_unique] primary key (id,name)   --  联合约束
	);

	通过alter语句添加:alter....modify/change.........         /  alter .... add  primary key.....
		alter table t4 modify id int primary key;
		alter table t4 add constraint pk_id_name  primary key(id,name);


<3>删除主键:alter ... drop primary key
		alter table t4 drop primary key



<4>注意:如果删除的主键列具有自增长约束,则必须要先删除自增长约束,再去删除主键约束。

5.自增长约束auto_increment

<1>概述
		列的数值自动增长,列的类型只能是整数类型
		通常给主键添加自增长约束。


<2>添加
	在创建表的时候添加   : create .....auto_increment
	create table t5(
		id int auto_increment, --这里会提示需要主键自增加  补充设置primary key
		name varchar(20)
	);


		通过alter语句添加:alter....modify/change.........     auto_increment
		alter table t5 change  id id int auto_increment;


<3>删除:alter ... modify/change....
		alter table t5 modify  id int;


<4>注意:
	一张表只能有一个自增长列,并且该列需要定义约束。

6.unsigned:无符号约束

<1>概念
	指定当列的数值未非负数

	 age  tinyint 1   -128~127  UNSIGNED 0~255

<2>添加
	在创建表的时候添加   : create .....UNSIGNED
	create table t5(
		id int,
		age tinyint UNSIGNED
	);


		通过alter语句添加:alter....modify/change.........     
		alter table t5 change  age age int tinyint UNSIGNED;
		alter table t5 modify age tinyint UNSIGNED;


<3>删除:alter ... modify/change....
		alter table t5 modify age tinyint;
		alter table t5 change  age age int;

7.zerofill:零填充约束

<1>概述:
	指定当前列的数值的显示格式,不影响当前列显示范围

<2>
	在创建表的时候添加   : create .....zerofill

	create table t6(
		id int,
		age tinyint zerofill
	);

<3>删除:alter ... modify/change....
		alter table t5 modify age tinyint;
		alter table t5 change  age age int;

8.外键约束

通过建立外键,设置表于表之间的约束性,限制数据的录入



员工表(从表)                         部门表(主表)
员工号 员工姓名    部门名称                部门号    部门名称
1       张三      1                   1           人力
2       李四      2                   2           销售
3       王五      3


<1>概述
	建立表和表之间的关系,建立参照完整性。一个表可以有多个外键,每个外键必须参照另一个主键。
	被外键约束的列,取值必须参照其主表列中的值。

	注意:通常先创建主表,再创建从表


	create table dept(
		deptno int primary key auto_increment,
		dname varchar(32),
		loc varchar(32)
	);


	create table emp(
		empno int primary key auto_increment,
		ename varchar(32) not null,
		deptno int
	);





<2>添加外键约束
	create table emp(
		empno int primary key auto_increment,
		ename varchar(32) not null,
		deptno int
		[constraint fk_name] foreign key (deptno) references dept(deptno);  -- 添加外键约束
	);

	使用alter add constraint....
		alter table emp add  constraint  fk_name foreign key (deptno)  references dept(deptno);



<3>删除外键约束
		alter drop foreign key fk_name

		在创建表时,不去明确的指定外键约束的名称,系统辉自动的随机生成一个外键的名称,
		使用 show create table 表名。查看具体的外键名称。


	alter table emp drop foreign key fk_name;


<4>设置外键中的级联关系
	1.on delete cascade:删除主表中的数据时,从表中的数据随之删除。
	2.on update cascade:更新主表中的数据时,从表中的数据随之删除。
	3.on delete set null:删除主表中的数据时,从表中的数据置空



级联删除
	create table emp(
		empno int primary key auto_increment,
		ename varchar(32) not null,
		deptno int
		[constraint fk_name] foreign key (deptno) references dept(deptno) on delete cascade;    -- 添加外键约束   
	);


注意:
	插入数据时,先插入主表的数据,在插入从表的数据。
	删除数据时候,先删除从表的数据,在删除主表中的数据。

二、多表查询

1.表与表之间的关系

一对一:用户表和身份信息表,用户表是主表
       男人表 、女人表

    create table man(
        mid int primary key  auto_increment,
        mname varchar(32),
        wid int unique
    );


    create table woman(
        wid  int primary key  auto_increment,
        wname varchar(32)
    );

一对多:最常见的表关系,用户表和订单表
       员工表、部门表

        create table emp(
            empno int primary key  auto_increment,
            ename varchar(32),
            deptno int
        );


        create table dept(
            deptno int primary key  auto_increment,
            dname varchar(32)
        );





多对多:学生表和课程表,通常情况都是将多对多的关系拆分为一对多或者多对一的关系。

        至少需要三张表


        create table student(
            sid  int primary key  auto_increment,
            sname varchar(32)
        );

        insert into student (sname) values ('大拿');
        insert into student (sname) values ('唐嫣');
        insert into student (sname) values ('王健林');

        create table course(
            cid int primary key  auto_increment,
            cname varchar(32)
        );

    insert into course (cname) values ('语文');
    insert into course (cname) values ('数学');
    insert into course (cname) values ('英语');
    insert into course (cname) values ('化学');



    create table s_c(
        cid int,
        sid int
    );


    insert into s_c (sid,cid) values (1,1);
    insert into s_c (sid,cid) values (1,2);
    insert into s_c (sid,cid) values (1,3);
    insert into s_c (sid,cid) values (1,4);
    insert into s_c (sid,cid) values (2,2);
    insert into s_c (sid,cid) values (2,4);
    insert into s_c (sid,cid) values (3,1);
    insert into s_c (sid,cid) values (3,3);

2.为什么要使用多张表

避免出现大量的数据的冗余。
并不是表拆的越多就越好,根据实际情况进行拆分。

3.概念

同时查询多张表

4.分类

<1>合并查询

unionunion all

合并结果集,就是把两个select语句的查询结果合并到一起。(相当于并集)
合并的两个结果,列数和列的顺序,类需要一致


create table emp(
    empno int primary key  auto_increment,
    ename varchar(32)
);

create table dept(
    deptno int primary key  auto_increment,
    dname varchar(32)
);

select * from emp  union select * from dept;
select * from emp  union all select * from dept;

<2>连接查询

员工表
    create table emp(
        empno int primary key auto_increment, # 员工编号
        ename varchar(32),  #员工姓名
        job varchar(32),        #员工职位
        mgr  int,                       #上级编号
        hiredate date,          #入职时间
        sal double,                 #薪水
        comm double,                #奖金
        deptno int                  #所属部门
    );

部门表
    create table dept(
        deptno int primary key auto_increment,  #部门编号
        dname varchar(32),      #部门名称
        loc varchar(32)             #部门地址
    );

内连接: inner join....on  、 join 、 ,
    inner join 是比较运算符,只返回符合条件的行

例如:
    select * from emp inner join  dept  on emp.deptno=dept.deptno;
    select * from emp e ,dept d where e.deptno = d.deptno;
    select * from emp e join dept d where e.deptno = d.deptno;

外连接:
    左外连接:LEFT OUTER JOIN | left join ... on
        代表查询,左边行的全部,右边没有则null
        select * from emp e LEFT OUTER JOIN  dept d ON e.deptno = d.deptno;

    右外连接: right join ... on 或者  right outer join .... on
        右连接包含right join 右表所有的行,如果左表中某行在右表没有匹配,则结果中对应的左表的部门全部为空(null)        
        select * from emp e right OUTER JOIN  dept d ON e.deptno = d.deptno;


自连接:
    自连接就是说,在同一个数据表中,看作是两个表,表示查找每个人的领导,如果没有领导,则显示无领导

    把一张表看作成两张表,一张员工表,一张领导表,都是emp表
    select e.ename,el.ename from emp e left join emp el on e.mgr = el.empno;



自然连接:natural join (join)|   natural  left join(同 left join) | natural right join (同 right join)

    自然连接会自动判断,以两个表中相同的字段为连接条件,返回查询结果。


    select * from emp natural join dept;

    select * from emp NATURAL left join dept;

    select * from emp NATURAL right join dept;





    注意:内连接不写连接条件会出现笛卡尔积的结果,应该避免这种情况,而外连接不写连接条件会报错。

<3>子查询(ANY子查询、IN子查询、SOME子查询、ALL子查询)

子查询解决的问题:
    谁的薪资比丛浩高???
        select * from emp where sal >(select sal from emp where ename='从浩');


    定义:子查询允许把一个查询嵌套在另一个查询当中
        子查询又叫做内部查询,相当于内部查询。包含内部查询的就称为外部查询。子查询的结果被主查询所使用。


注意的问题:
    1.括号
    2.可以在主查询的where select having from 后面,都可以使用子查询
    3.不可以再group by 后面使用子查询
    4.主查询和子查询可以不是同一张表;只有子查询返回的值,主查询可以使用。

        需求:查询部门名称是人力的员工信息

         第一种方式:利用子查询
            select * from emp where deptno=(select deptno from dept where dname='人力部');

        第二种方式:利用关联查询
            select * from emp e,dept d where e.deptno = d.deptno and d.dname='人力部';


        SQL优化:尽量使用多表查询
            绝大部分的子查询再最终执行的时候他都是转换成一个多表查询来执行的。  通过SQL执行计划可以看出来。
            通过SQL执行计划会发现两种方式执行的是一样的。


    5.from后面的子查询

            需求:
                查询员工号   姓名      月薪

                select empno,ename,sal from emp;


    6.一般不在子查询中排序

    7.一般先执行子查询,再去执行主查询

ANY关键字 假设any内部的查询返回结果个数是三个,如:result1,result2,result3,那么

select .... from .. where a > any(...);

->

select ..... from ... where a > result1 or a >result2 or a >result3;

需求: 查询工资比1号部门中任意一个员工高的信息 select * from emp where sal > any(select sal from emp where deptno = 1); ALL关键字 ALL关键字与any关键字类似,只不过上面的or改成and : select .... from .. where a > all(...);

-> select ..... from ... where a > result1 and a >result2 and a >result3;

需求: 查询工资比1号部门中所有员工号的员工信息

select * from emp where sal > all(select sal from emp where deptno = 1);

SOME关键字 some 关键字和any关键字是一样的功能。所以:

select .... from .. where a > any(...);

->

select ..... from ... where a > result1 or a >result2 or a >result3; IN关键字 IN运算符用于where表达式中,以列表向的形式支持多个选择。语法如下:

where column in (v1,v2,v3,.....);
    where column not in (v1,v2,v3,.....);
    当in前面加上not运算符时候,表示与in相反的意思,既不在这写列表项中选择。

案例: 查询部门名称是人力和研发的员工 select * from emp where deptno in (select deptno from dept where dname='人力部' or dname='研发部')

1. MySQL中的函数

<1>加密函数

password(str)
    该函数可以对字符串str进行加密,一般情况下,此函数给用户密码加密。

    select PASSWORD('tlxy666'); 

md5(str)
    对字符串str进行散列加密,可用户对于一些普通的不需要进行解密的数据进行加密。

<2>聚合函数

avg()
count()
min()
max()
sum()

    例如:
        select AVG(sal) from emp;   平均薪资
        select COUNT(*) from emp;   员工数量
        select MIN(sal) from emp;   最低工资
        select MAX(sal) from emp;   最高工资
        select SUM(sal) from emp;   工资求和

<3>数学相关的函数

rand()
    返回0~1的随机数
    select RAND();

abs()
    获取num的绝对值
    select ABS(-2);
mod(num1,num2)
    取余数
    select MOD(10,3)

ceiling(num)
    向上取整
    select CEILING(10.1);

floor(num)
    向下取整    
    select FLOOR(10.1);


round(num)
    返回离num最近的数----四舍五入
    select round(10.5)

round(num,n)
    保留num小数点后的n位
    select ROUND(10.12345,3);

least(num,num2,.....)
    求最小值
    select LEAST(10,5,18,3,20)

<4>字符相关的函数

char_length(str)
    返回字符串str的长度,单位是字符
    select CHAR_LENGTH('nihaoya');

length(str) 
    返回字符串str的长度
    select length('nihaoya')

instr(str,substr)
    返回字符串str中子字符串substr的位置
    select INSTR('123456','34');

insert(str1,index,len,str2);
    将字符串str2替换str1的index位置开始长度为len的数据
    select INSERT('123456',1,3,'abc');

replace(str,oldstr,newstr);
    字符串替换
    select replace('hello','ll','oo')

left(str,len)/right(str,len)
    返回字符串str的前len的字符串
    select LEFT('helloword',5)

<5>日期函数

now()
    获取当前日期和时间
    select now();

curdate()/current_date();
    获取当前日期
    select curdate();

curtime()/current_time();
    select current_time();

year(date)
    获取年份
    select year('1995-01-02');

<6>流程控制函数

if(expr,v1,v2)
    如果表达式expr成立,则返回v1的值,否则返回v2的值
    select if(5>3,'大于','小于')


ifnull(v1,v2)
    如果v1的值不为null,则返回v1的值,否则返回v2的值
    select IFNULL(4,0);


nullif(expr1,expr2)
    如果expr1=expr2,则返回null值,否则返回expr1的值
    select NULLIF(2,2)

2. 用户的创建和授权(DCL)

<1>授权的语法格式

GRANT 权限 ON 数据库.表 TO 用户名@登录的主机  IDENTIFY BY 密码;


1. 新建数据库testdb
    create database testdb;


2. 新建用户 laowang并赋予 testdb数据库的相应权限

    grant all privileges on testdb.* to laowang@localhost identified by '123';


3.如果想指定部分的权限给用户

    grant select,update on testdb.* to laowang@localhost identified by '123';


4.赋予用户 laowang所有数据库的某些权限

    grant select,update,insert,create,drop on *.* to laowang@"%" identified by '123';


注意: all privileges ,  *.*  , "%"

常用的权限:
    select  对所有表进行查询操作
    insert  对所有表进行插入操作
    update  对所有表进行更新操作
    delete  对所有表进行删除操作
    create  数据库、表、索引
    drop    数据库和表的删除操作
    alter   对所有表进行更改

<2>取消权限的语法格式

revoke 权限 on 数据库.表 from 用户名@登录的主机;

revoke select on testdb.* from laowang@localhost;

<3>显示授予的权限

show grants;

<4>删除用户

use mysql;

delete from user where user='laowang';

flush privileges; -- 刷新权限

05_MySQL中的索引

<1>概念

是一种高效获取数据的数据结构(树),以文本的形式存在。以某种数据结构存储特定的数据(地址)。
好比是一本书的目录,能够加快数据库查询的数据。

<2>作用

提高查询的效率
在多表连接的时候,作为条件加速连接的速度
在分组和排序检索数据的时候,减少分组和排序所消耗的时间。

<3>类型

1.普通索引(最基本的索引)
    1.1 创建索引
        格式: create index index_name on table_name(column);

        例如:
            create table index_tb1(
                id int primary key auto_increment,
                name varchar(32)
            );

            create index index_name on index_tb1(name);

            格式:alter table table_name add index index_name(column);

    1.2 查询索引 
            格式:show index from table_name;

    1.3 删除索引
            格式:drop index index_name on table_name;

2.唯一索引
3.主键索引
    注意:使用整型优于字符型,额为维护一个与其他字段不相关、与业务不相关的列,一般设置为整数类型并且自增长。

06_MySQL数据库的备份和恢复

<1>数据库的备份

使用mysqldump 命令可以生成指定的数据库脚本文件。

语法:
    mysqldump -uroot -p密码 数据库名称 > 生成的脚本文件存放的路径

例如:
    mysqldump -uroot -proot tlxy > G:\tlxy.sql (sql脚本文件) 不需要登录到mysql数据库中

<2>数据库的恢复

语法格式:
    source 脚本文件存放的路径        (先选择恢复到目的数据库,需要登录到mysql数据库中)

    mysql -uroot -p密码   新数据库 < 脚本文件存放的路径 (不需要登录到mysql数据中)

    注意:指定数据存放的数据库
         表名称不要重复。

07_数据库的优化

1.遵循设计范式

第一范式:对属性的原子性的约束,属性不能再分解
第二范式:对记录的唯一性约束,要求每一条记录都有唯一标识,实体完整性。
第三范式:对字段的冗余约束,字段没有冗余。 允许字段适当的冗余,提高数据操作的效率。

2.选择合适的存储引擎

Innodb      支持事务        行级别的锁   外键          推荐使用
MyISAM      不支持     表级别的锁   不支持外键   查询效率更高

3.选择合适的数据类型

优先考虑整型、日期、字符
推荐使用枚举enum('女','男')

4.适当的建立索引

where       order by    group by 后面经常出现的字符
多表的连接的字段

5.避免使用 select *

6.为每一张表设置id并且自增长

7.只需要查询一条数据, limit 1

(0)

本文由 SilenceLee的学习笔记 作者:silencelee 发表,转载请注明来源!

关键词:

热评文章

发表评论

解决 : *
13 − 2 =