MySQL数据库

MySQL数据库

4 数据类型
整数 int bigint
小数 float double
字符串类型 可以指定字符的个数 最多只能放10个 ‘’
char(10) 数据库会分配一个长度是10的空间 不管空间中存放的字符个数多少(小于等于10)
varchar(10) 如果存放的字符少于10个 那么分配的空间也会少于10个 更省空间
时间 date ‘1999-09-09’
time ‘17:27:30’
datetime ‘1999-09-09 17:27:30’
timestamp 时间戳
大数据类型 blob clob
文本类型 text
5 如何操作使用数据库? SQL语句不区分大小写
添加 删除 修改 查询数据可以通过SQL语句来实现
SQL Structured Query Language, 结构化查询语言
SQL分类***
DDL 数据库定义语言 create alter drop
DCL 数据库控制语言 grant revoke
DML 数据库操作语言 insert into delete update
DQL 数据库查询语言 select order by group by limit inner join left join right join
6 对MySQL中数据库的操作 cmd SQL Navicat 数据库名称不区分大小写
MySQL的注释 # – /**/
1>查看所有数据库
show databases;
2>新建数据库
create database **;
3>删除数据库
drop database ***;
4>切换数据库
use **;
5>查看当前库中的表
show tables；
7 数据库中表的数据的简单操作 表名也不区分大小写 同一个库中表名是唯一的
1>创建表格
create table 表名(
字段名1 字段类型,
字段名2 字段类型,
… …
字段名n 字段类型);
create table emp_tb(
id int,
name varchar(10),
sex char(1)
);
2>添加数据
插入一条数据 记录
insert into student_tb(id,name) values(1,“zhangsan”);
insert into student_tb(id) values(2);
insert into student_tb values(4,“zhaoliu”);
如果要插入的数据有所有属性值 可以省略指定字段的部分 表名(字段名…)
插入多条数据 记录
可以写多个SQL语句插入
也可以用一个SQL语句插入多条记录
insert into emp_tb(id,name,sex) values(1,“zhangsan”,“男”),(2,null,‘女’);
3>查看数据 查询不要用 不能使用索引 效率慢
查询并不是直接看存储的数据
而是根据需要查看的属性 把对应的值组成一个虚拟表格展示出来
select * from emp_tb; *代表所有属性
select id,name from emp_tb; 查询指定字段
8 对表结构的操作
1> 查看表结构
desc 表名; 查看表的字段 类型 约束
2> 查看建表语句
show create table 表名;
3> 修改表名
alter table 旧表名 rename 新表名;
4> 修改字段
alter table 表名 change 字段名 新的字段名 数据类型;
5> 添加字段
alter table 表名 add 字段名 数据类型;
6> 删除字段
alter table 表名 drop 字段名;
7> 删除表
drop table 表名;
9 约束条件
约束是为了保证数据库数据的准确性 完整性
约束名称的规范 约束简写_表名_字段名称
modify 为对应的字段添加约束 还可以修改字段的数据类型
主键约束 primary key
非空 not null
唯一 unique
自增长 auto_increment
默认值 default
外键 foreign key
1>主键约束 primary key 一个表中只有一个主键 主键一般是数字
主键是唯一标识某一条记录 它是非空和唯一的结合体
如何给表添加主键约束?
(1)创建表时添加
create table emp_tb(
id int primary key,
name varchar(10)
);
(2)创建表时添加
create table emp_tb(
id int,
name varchar(10),
constraint pk_emp_id primary key(id)
);
(3)对已有表的修改
alter table 表名 modify 属性名 数据类型 primary key;
alter table 表名 add CONSTRAINT 约束名 primary key(字段名);

以上的主键都是单列主键
复合主键
    create table emp_tb(
        id int,
        name varchar(10),
          constraint pk_emp_idname primary key(id,name)
    );
    alter table emp_tb add constraint pk_emp_idname primary key(id,name);

2>非空 not null 值不能为null
如何添加?
(1)创建表添加
create table emp_tb(
id int primary key,
name varchar(10) not null
);
(2)对已有表的修改
alter table student_tb modify name varchar(12) not null;
3>唯一 unique 值只出现一次 唯一约束可以有多个
如何添加?
(1)创建表添加
create table emp_tb(
id int UNIQUE,
name varchar(10) not null
);
(2)创建表添加
create table user_tb(
id int primary key,
name varchar(10) not null,
mobile char(11) unique,
age int not null,
email varchar(20) not null,
constraint uq_user_email unique(email) --可以添加组合的唯一约束
);
(3)对已有表的修改
alter table 表名 modify 字段名 数据类型 unique;
alter table 表名 add constraint 约束名称 unique(字段名);
4>自增长 一般给数字类型的主键使用
下一个主键的值是已使用的最大主键值+1
有了自增长的数字主键 插入数据的时候可以给主键指定为null
但是主键会自动在原来使用过的最大主键值基础上+1 算出新的主键值
insert into emp_tb values(null,“zhaoliu”);
如何添加?
(1)创建表添加
create table abc(
id int primary key auto_increment,
name varchar(10),
sex char(2)
);
(2)对已有表的修改
alter table student_tb modify id int auto_increment;
5>默认值 default 这个字段有默认值
(1)创建表的时候添加
create table abc(
id int primary key auto_increment,
name varchar(10),
sex char(1) default “男”
);
(2)对已有表的修改
alter table student_tb modify sex char(1) default “男”;
6>外键约束 foreign key 表和表之间有关系 就可以添加外键
一般次表的外键是主表的主键
如何判断表和表之间的关系?
表和表之间存在依赖关系 从属关系 那么就可以添加外键
有关系的表可以分为主表和次表
a先有b后有 a是主表 b次表
a是b的一员 b是主表 a次表
外键是添加到次表上的 给次表中和主表有关系的字段添加外键
员工表有个属性 部门编号deptId
该字段的值存在于部门表中对应部门编号属性
SQL文件的导入导出

Navicat添加外键
    给次表的和主表有关系的字段添加外键
    删除时和更新时选择的是cascade  级联 
        当主表删除和更新时 次表同时删除和同时更新

SQL语句添加外键
    ALTER TABLE news_info[子表名] ADD CONSTRAINT FK_news_info_news_type[约束名]
    FOREIGN KEY (info_id)[子表列,可以是多个列] REFERENCES news_type[主表名] (id)[主表列] ;

10运算符
算术运算符

• • • / %
      赋值运算符
      =
      逻辑运算符
      and或&& or或|| not
      关系运算符(比较运算符)

= < <= = != <>
11数据的增删改
1>增加
增加一条数据
insert into student_tb(id,name) values(1,‘zhangsan’);
增加多条
insert into student_tb values(1,‘zhangsan’),(2,‘lisi’),(3,‘wangwu’);
时间的添加 插入时间也需要用""把时间包起来
时间的类型 date time datetime timestamp
函数 now(); 获取当前系统时间
year(‘时间’); 获取参数时间中的年份
2>删除 物理删除 尽量少用
navicat
delete from 表名 where 删除条件

delete from 表名; 清空对应表中的所有数据

delete from student_tb where id = 7 and name  = "wujiu";  同时满足多个条件
delete from student_tb where id = 4 or id = 5;  满足条件的都删除
id=3不能使用id is 3 只有属性值是null的时候才能用is
delete from user_tb where mobile is null;

3>修改 更新 update 对已有数据的修改
update 表名 set 列名=列值 where 更新条件
(1)更新一行一列
update student_tb set age = 26 where id = 5;
(2)修改一行多列
update student_tb set age = 27,sex = “男” where name = “wangwu”;
(3)修改多行一列
update student_tb set age = 28 where id >= 3 and id <=5;
(4)修改多行多列
update student_tb set age = 25, sex = “女” where id = 2 or id = 3;
(5)多条件修改一行
update student_tb set age = 26,sex = “男” where id = 2 and name = “lisi”;
12查询
把复合要求的所有记录组成虚拟表展示
1>简单查询 不要用*影响查询效率 把所有记录都会查出来
select * from student_tb;
select id from student_tb;
select id,name from student_tb;
2>条件查询 只会查询出符合条件的记录
select id,name from student_tb where id = 5 or id= 6;
select id,name from student_tb where sex = “男”;
3>别名 列 表 as可以省略
(1)列
select empid as 工号 ,basesalary as 基本工资,titlesalary 职称工资,deduction 扣钱 from salary;
select empid as 工号 ,basesalary as 基本工资,titlesalary 职称工资,deduction 扣钱 from salary
where empid = 1003;
select 3
from 1
where 2 不能使用select中的别名 可以使用from中的别名
(2)表
select s.empid as 工号 ,s.basesalary 基本工资
from salary s where s.empid = 1002 or s.empid = 1003;
4>查询空值 null “”
select id,name from student_tb where sex is null;
select id,name from student_tb where sex = “”;
5>常量列 把常量或者计算结果作为新的一列 新的属性是对虚拟表的操作
select … ,列值 as 列名 from 表名
select id,name,“中国” as 国籍 from student_tb;
select empid 工号,basesalary+titlesalary-deduction 实际工资 from salary;
6>排序 一般是给数字排序 给字符串排序是按照字典顺序 order by
单列排序 单条件排序
升序 asc或者不写
select empid,basesalary
from salary
order by basesalary asc;
降序 desc
select empid,basesalary
from salary
order by basesalary desc;
条件筛选后排序
select empid,basesalary 基本工资
from salary
where empid != 1003
order by basesalary/基本工资 desc;
from --> where --> select --> order by(可以使用别名)
多列排序 多条件排序
select empid,basesalary,titlesalary
from salary
order by titlesalary,basesalary desc;
有主次条件之分
先根据order by后的第一个条件进行排序
然后在第一个条件有值相等的情况下 把对应的记录根据第二个条件排序…
7>分页 只需要指定个数,位置的记录 limit MySQL的分页
把查询结果分为几页 MySQL分页时 记录从编号0开始 0 1 2 3 4…
SELECT 字段 1，字段 2…FROM 表名 LIMIT 初始位置，记录数
select id ,name from student_tb limit 0,4;

分页的公式
    limit  初始位置 ,  记录数(每页显示的记录数量)
    每页记录数 pageSize
    页码       pageNum
    limit  (pageNum-1)*pageSize,pageSize;

真分页和假分页
真分页  直接把查询结果给分页  limit
假分页  把所有结果都查询出来 在页面展示时进行分页

条件筛选后排序分页
select id,NAME
from student_tb
where id != 5
order by id DESC
limit 0,3;
先找到对应的表  然后找对应的记录  再查找对应的属性
再给结果排序  给排序结果分页

8>去重 去除重复 去掉重复的属性值 一般是和聚合函数一起使用
select distinct depid from employee; 可以去除多列一起
9>模糊查询 根据模糊的条件查询跟这个条件相同或者相关的记录
通配符 _ 相当于一个字符
% 相当于任意个字符
(1)like
select id,name from student_tb where name like “z_______”; z开头name共8个字符
select id,name from student_tb where name like “i"; i结尾name共4个字符
select id,name from student_tb where name like "i”; 第二位是i共6位
select id,name from student_tb where name like “z%”; 以z开头的name 位数不限
select id,name from student_tb where name like “%i”; name以i结尾
select id,name from student_tb where name like “%i%”; name中带有i的 不管开头 结尾还是中间
(2)between…and 在…和…之间 一般也是针对数字还有时间
between之后的值比and之后的小或者相等 查询结果中包含between和and之后的值
select id,name,age from student_tb where age>=24 and age <=26;
select id,name,age from student_tb where age between 24 and 26;
select empid,name,birthday
from employee
where birthday
between “1975” and “1979-01-01”;
(3)in 在…范围内
select id,name from student_tb where id = 2 or id = 6 or id = 4;
select id,name from student_tb where id in (2,4,6);
10>聚合函数 max min sum avg count
(1)sum 求和 给属性值
select sum(basesalary) as 基本工资总和 from salary;
(2)avg 求平均值
select avg(basesalary) as 基本工资平均值 from salary;
(3)max 求最大值
select max(basesalary) as 基本工资最大值 from salary;
(4)min 求最小值
select min(basesalary) as 基本工资最小值 from salary;
(5)count 统计符合条件的记录数量
select count(id) 人数 from student_tb where sex = “男”;
select count(distinct depid) from employee;
11>分组查询 group by
SELECT …… FROM <表名>
WHERE ……
GROUP BY ……

把查询结果进行分组
select depid,count(empid) as 人数 from employee group by depid;
select后跟的一般是分组条件或者聚合函数
    聚合函数一般是在分组之后才使用

例子 统计不是222部门的员工数量
select depid,count(empid)
from employee
where depid != 222
group by depid;
给查询结果进行分组

12>分组排序
select depid,count(empid) as 人数
from employee
where depid != 222
group by depid
order by 人数;
先给符合条件的数据进行分组
然后使用聚合函数
在给聚合函数的结果进行排序
13>分组筛选
对分组以后的数据进行筛选 一般是对聚合函数的结果进行筛选
select depid,count(empid) 人数
from employee
where depid != 222
group by depid
having 人数>1;
where和having的区别
where是分组前筛选 一般是对属性 字段的筛选
having分组后筛选 一般给聚合函数的结果进行筛选
14>单表查询
select 分组条件或者聚合函数
from 表名
where 条件1
group by 分组条件
having 条件2
order by 排序条件
limit 起始位置,记录数
15>多表查询 什么时候用多表?
笛卡尔积现象
单纯的把多个表中的数据进搭对 不符合实际情况的 所以要避免笛卡尔积现象
select e.empid,e.name,e.depid,d.depid,d.depname
from employee e,department d
where e.depid = d.depid; 多个表中有关系的字段
查看工号是1003的员工的姓名 所属部门名称 基本工资.
select e.name,d.depname,s.basesalary
from employee e,department d,salary s
where e.depid = d.depid and e.empid = s.empid and e.empid = 1003;

内连接 inner join
    内连接  多个表中有关系的字段值相等的会自动匹配 然后能够被查出来
            如果有多余的属性值 并没有找到能够匹配的数据 那么就不会被查询出来
    两表
        select e.empid,e.name,e.depid,d.depid,d.depname
        from employee e
        inner join department d
        on e.depid = d.depid
        where e.empid != 1004;
        相当于
            select e.empid,e.name,e.depid,d.depid,d.depname
            from employee e,department d
            where e.depid  = d.depid and e.empid != 1004;
    三表
        select e.name,d.depname,s.basesalary
        from employee e
        inner join department d
        inner join salary s
        on e.depid = d.depid and e.empid = s.empid
        where e.empid = 1003
外连接 outer是可以省略的   左外连接   left outer join
                           右外连接   right outer join   
    外连接有主次表之分
        left join之前是主表
        right join之后的是主表   
        查询结果会找到主表的所有内容     次表和主表一一匹配             
    两表
        select  e.empid,e.name,d.depid,d.depname
        from department d
        left join  employee e
        on e.depid = d.depid   

        select  e.empid,e.name,d.depid,d.depname
        from employee e
        RIGHT JOIN department d
        on e.depid = d.depid

16>子查询
把查询结果当成条件来使用
– 查看lisi的基本工资
select e.name,s.basesalary
from employee e,salary s
where e.empid = s.empid and e.name = “lisi”;

select e.name,s.basesalary
from employee e
inner join salary s
on e.empid = s.empid
where e.name = "lisi";

select basesalary from salary where empid = (
select empid  from employee where name = "lisi");