Mysql数据库手册

数据库基本概念

1.数据库 :就是数据的仓库,由表,关系,操作对象组成

2.表 :由行和列组成,数据都存放在表中,由于mysql是关系数据库,所以表又被称为关系

3.字段:就是属性

4.记录;一行数据就是一条记录,也是一条实体(需要设置主键)

5.约束:对数据的限制,比如 列值要求-身份证必须是18位,年龄必须是数字; 整行要求- 是否重复输入

6.主键和外键 :表中有一列或几列组合的值能够用来标识每一行,就称之为表的主键

7.子表的字段 对应 主表的字段,在子表中被称为外键,或者引用键;一个表中可以有多个外键

8.非关系数据库不是以表的形式保存数据,而是以键值的形式保存

9.SQL结构化查询语言,尽管这些客户端用的不一样,ASPNET,java,Visual Basic c#,还是别的语言,sql是所有这些前台与关系型数据库通讯的语言

10.不同职位的人对数据学习的重点不同

11.使用数据库,可以降低存储数据的冗余度，提高更高的数据统一性，数据的安全性和完整性

SQL语法

1, 数据查询语句	select数据选择查询
2,数据操作语句	insert插入; update更新; delete删除
3,数据定义结构	create 新建; Alter;修改结构; Drop删除; Rename重命名; Truncate 删除;
4.数据控制语言	Grant 授权 , Revoke 解除授权

💯执行sql语句

1. 通过命令行 执行 net start mysql57 , mysql - u root -p
2. 点击navicat软件,选择功能区->查询->标签页(查询编辑器)

💯规则

• sql语句以分号(;)结尾
• sql语句不区分大小写
• 字符串和日期需要用引号扩起来 ‘abc’.‘3456’
• sql语句的单词用空格分隔
• 注释(快捷键ctrl+/): -- 注释的内容

💯数据类型

int(整数值) , float(单精度浮点数) , char(定长字符) , varchar(不定长字符) , text(文本型) , blob(二进制文件),时间类型

定长字符char :比如char(10),存储字符是hello,如果实际字符长度不够,存储时则补相对应的空格,而不定长varchar则直接存储hello

文本型text: 存储大容量文本,比如评论

二进制文件 blob :比如存储音乐,视频

类型	格式	范围
year	yyyy	1991 - 2155
date	yyyy-mm-dd	1000-01-01 -9999-12–31
time	hh:mm:ss	-835:59:59 - 835:59:59
timestamp	yyyy-mm-dd hh:mm:ss	1970-01-01 00:00:01 - 2038
datetime	yyyy-mm-dd hh:mm:ss	1000-01-01 00:00:01 - 9999-12–31 23:59:59

💯运算符

运算符	含义
between … and …	在两值之间(比如工资,id)
in(匹配值)	匹配列出的值
like ( %表示0个/多个字符 , _表示一个字符)	匹配一个字符
is null	空值
or	或者

运算符顺序 : ()> not > and > or

关键词顺序: select > where > group by > having > order by

执行顺序: from, where, group by, select, having, order by

模块

💯增

//创建增加个数据库 demo,指定编码格式
create database 数据库名 character set=utf8;

//指定数据库demo增加表s1,并设置主键id
//设置name内容约束不为空,设置内容约束性别为指定'男&女'
use database demo;//使用demo数据库
create table 表名(id int primary key ,name varchar(10) not null,sex set('男','女'));

//修改表s1增加salary属性,int型,不为空,放在第一列
alter table 表名 add column salary int not null  first;

//给表中增加两条数据
insert into 表名 values(1,'王侍郎','男',12000),(2,'张韵诗','女',8000);

💯删

//删除数据库
 drop database 数据库名;

 //删除表
drop table 表名

//删除表s1,若有约束,则取消约束,记得恢复
set foreign_key_checks = 0;关闭Foreign Key检查
drop table s1;
set foreign_key_checks = 1;恢复Foreign Key检查

//删除列
alter table 表名 drop column 列名;

💯查

查询时,会向服务器发送查询请求,而服务器会给客户端发送查询结果,查询只是产生一个虚拟表而言

• 查询结构:select 列名 from 表名 [where 查询条件] [order by 排序的列名 asc/desc]
• 去重属性distinct
• 指定从第x+1行取,取y行select 列名 from 表名 limit x,y ,代码x第一行下标为0

查询表所有行                                                                                    
select * from 表名

查询表 id,name,sex 字段
select id, name,sex from 表名;

查询时使用别名
select id as 会员号,name as 用户名, sex as 性别 from 表名;

查询指定第0行开始查,查5行
select  * from s1 limit 5;

查询指定第2行开始查,查5行
select  * from s1 limit 1,5;

查询前三名工资的人员信息
select * from s1 order by salary desc limit 3;

查询id包含1,2,3的人员信息
select * from s1 where  id in(1,2,3);

查询多种筛选条件的人员信息
select * from s1 where  id=1 or name='jack' or salary>10000;

查询姓王的人员信息
select * from s1 where name like '王%';

查询姓名中间是希的人员信息
select * from s1 where name like '_希%';

数据库操作

• 创建一个数据库 create database if not exists 数据库名; , if not exists表示不存在

• 创建指定编码集的数据库 alter database 数据库名 character set=编码格式

• 查看所有数据库 show databases;

• 进入数据库 use 数据库名 ,注意有的数据库名字得用``包起来

• 删除特指数据库 drop database 数据库名;

表操作

• 创建表

//engine=引擎类型,mysql 5.4版本一般使用 innodb
create table 表名 ( 列名 数据类型 约束, 列名 数据类型 约束) [default] character set=编码格式];

1.create table b1 (name varchar(50),age  varchar(3));
2. create table b2(id  int ,age  int )  engine=innodb  default character set utf8;

• 查看所有表show tables;
• 查看表定义 show create table 表名;
• 删除表 drop table 表名
• 关闭Foreign Key检查 set foreign_key_checks = 0;
• 复制表 create table 新表名 as select * from 被复制表名;
• 只复制表结构,不复制数据create table 新表名 like 被复制表名
• 修改表名 alter table 表名 rename 新的表名
• 添加列 alter table 表名 add column 列定义
• 更改列名alter table 表名 change column 原列名 新列名 列定义
• 更改列结构 alter table 表名 modify column 列名 数据类型
• 删除列 alter table 表名 drop column 列名
• 插入数据 insert into 表名 values(2,'小黑','男',15000);
• 指定字段插入数据 insert into 表名(id,name,sex) values(2,'小黑','男');
• 更新数据 update 表名 set 列名=更改值 where 更新条件

 还可以这样写 ,update s2 set salary + 1000 where salary<10000; 

• 删除行 delect from 表名 where 删除条件
• 删除表 truncate table s1等于 delect table s1 ,前者更快

主键

1. 主键必须是非空且唯一的.
2. 一个表只能有一个主键,主键约束确保了表中的是唯一的,
3. 表中可以没有主键,但是通常情况下应当为表设置一个主键

💯主键注意

1. 尽量在表中选择最少的字段作为主键
2. 尽量选择不易更改的字段作为主键

💯设置主键

//第一种方式单个主键
create table 表名(属性1 数据类型 primary key,属性2 数据类型)

//俩个主键,联合主键
create table 表名(属性1 数据类型 ,属性2 数据类型 ,primary key(属性1,属性2)

//第二种方式,括号里也可以有俩个主键
create table 表名(属性1 数据类型 ,属性2 数据类型 ,constrsint pk_表名 primary key(属性1) )

• 增加主键

  alter table 表名 add column 属性 数据类型 primary key ;

• 删除主键
  alter table 表名 drop primary key;

​

💯注意指定外键更新/删除的行为

• 如果子表试图创建一个父表中不存在的外键值,innodb会拒绝任何insert或update操作
• 如果创建表,没指定 on delect/on update ,则默认restrict
• 如果父类update/delete子表的外键,而结果取决于外键的约束[on update,on delete],默认的动作为restrict
• cascade:从父表中删除/更新对应的行,同时自动的删除/更新子表中匹配的行
• set null :若从父表更新/删除对应的行,则将子表的外键列设为空,若外键设置了not bull 就不行
• no action: innodb拒接删除/更新父表
• restrict :拒接删除/更新父表,指定restrict和忽略 on delete/on update选项的效果一样
• 在原有表增加列名
  alter table 表名 add column 新增的列名 varchar(20) 约束状态 after 已存在的列名;
• set foreign_key_checks = 0;关闭Foreign Key检查(删除表,必须关闭)
• set foreign_key_checks = 1;恢复Foreign Key检查

create table if not exists demo(
id int primary key,  //id是demo的主键
name varchar(10),
sex char(2)
)

create table if not exists demo_2(
id int,   //id是demo_2的外键
name varchar(10)  primary key,  // name是demo_2的主键
foreign key(id) references demo(id) on update cascade  on delete cascade//主键更新/删除,子表也相应的改变
)

设定自增列

语法: id int(10) auto_increment primary key

//创建一个demo表,内有int型 id, varchar型 name,id是主键,将id列设置为自增列 
注意:自增的列必须设置为主键,并且一张表只能有一个自增列
create table demo( 
id int  auto_increment primary key,
name varchar(10)
)
insert into demo values(1,'Tom'); //向demo表中插入数据 1,tom
insert into demo values(default,'Tom');//此时会向demo插入(上一个最大数+1,'Tom')

属性约束

• 创建约束 唯一

//给属性vip设置唯一性,不能重复
create table demo(vip varchar(50) unique);

• 创建约束 非空

//给属性vip设置非空,不能为空
create table demo(vip varchar(50) not null);

• 创建检查约束

//创建固定选择值
create table (sex set('男','女'),id enum('001','002'));

• 创建默认值
  create table(id int default '001')

聚合函数&组过滤

普通select语句 :对每行进行处理并返回结果

聚合函数:会根据分组的情况处理数据,每组返回一个结果

分组函数 : group by 分组字段; 分了几组就是几条记录

聚合函数: count(返回行的数量), sum,avg,max,min

count(字段名) 统计非空字段的个数

avg(字段名) 统计非空字段的平均值,其他函数同理

if null函数 强制分组函数包括空值

Having 子句 分组结果过滤

//查询最高的工资和最低的工资
select  min(salary), max(salary) from s1;

//查出多少人工资大于等于一万的员工
select count(*) from s1 where salary >= 10000;

//查询工资等于10000的id字段,且id不为空
select count(id)  from s1 where salar=10000  

//算出s1表工资的平均值,不包括空格
select avg(salary) from  s1;

//求出整体平均销量,包括空值
select avg(if null(sales_volume,0)) from s1;//sales_volume销量

//按照部门进行分组求平均工资
select job, avg(salary) from s1 group by job;

//查询哪些销售哪个团队最高工资超过15000
select Tim,max(salary) from s1  group by Tim having  max(salary) > 2900; 

select 商品种类  销售总价 进货总价 from s1 
group by 商品种类
having  sum( 销售单价)> 1.5*sum(进货单价)

子查询

• 子查询要用括号括起来
• 将子查询放在比较运算符的右边
• 子查询不要加order by子句
• 对单行子查询使用单行运算符
• 对多行子查询使用多行运算符(in,any,all)

找出工资高于jones的演员,这时候得先查到jones工资,再查询高于他工资的演员,这时候就得用子查询

语法 :  select 字段 from  表名 where 条件 运算符
                 (select 字段 from  表名 where 条件);  //子查询得返回一个结果   
in: 等于列表中的任何值
any(|):比较子查询返回的每一个值,只要其中一个值满足条件就返回true
all(&):比较子查询返回的每一个值,若其中每一个都满足条件及返回ture
         
         select name,job from s1  
         where job=
                      (select  job from s1 where name='jack')
         and  salary>
                  		 (select salary from s1 where name='tom');            

多表查询

两种类型:内连接(inner join ... on ),外连接(左连接left join,右连接 right join)

查询表1中 工资,表2中的工资是否一样(where 连接)
select s1.salary,s2.salary from s1,s2 where s1.salary1 = s2.salary2;

通过内外键连接两表
select s1.salary,s2.salary
from s1 inner join s2  
on  s1.salary1 = s2.salary2;

//inner join on,符合条件才显示两表的列
//left join on ,左边不管有没有满足条件,显示所有行,right join on 同理

存储过程

存储过程:存储在数据库的一段声明sql语句,方便其他编程需要调用,相当于对sql语句进行封装到函数内

优点1: 存储过程有利于提高应用程序的性能

优点2: 存储过程有助于减少应用程序和数据库服务器之间的流量,

不必发送冗长的sql语句到数据库,只需要传递存储过程名和参数

优点3: 存储的程序对任何应用都是可重用的和透明的

优点4:存储的程序是安全的,支持权限控制

• 格式

定义存储过程 //相当于定义方法
create procedure 存储过程名字()
begin 
     代码
end

delimiter
create procedure get_name()
begin 
     select name from s1;
end

• 调用存储过程 call get_name(); //相当于调用方法

• 删除存储过程 drop procedure if exists 存储名;

• 在存储过程中声明变量declare 变量名 数据类型(约束) [default 默认值 定义默认值];

declare name char(10);

• 在存储过程中赋值变量

  set 变量名 = 值 / select xxx into 变量名 from 表名

• 变量赋值(变量可以是数值,字符串类型)

第一种方式 set @变量名=值

set @temp=123;
//查看变量temp的值 set @temp;

第二张方式 select @temp:=value; / select xxx into @temp

select @temp := max(salary) from s1;//通过查询给temp赋值
select   max(salary) into @temp  from s1;//通过查询插入给temp赋值         

• 使用变量

  select @temp;