mysql 数据库 day_03

回顾：

mysql   部分收费
mariadb 免费
客户端链接
mysql -uroot -p
客户端退出
exit 或 \q
显示所有数据库
show databases;
show schemas;
创建数据库
create database db1
charset utf8;
删除数据库
drop database db1
drop database if exists db1
查看表
show tables;
desc tb1; --查看表结构
show create table tb1\G --查看建表语句
表的增删改查
create table tb1(
    id int primary key auto_increment,
    name varchar(20) not null,
    num int not null unique,
    xid int,
    foreign key(xid) references tb2(id)
)engine=innodb charset=utf8;
修改表
*)添加字段
    alter table tb1 add gender char(1) 
after name;
*)去掉非空
    alter table tb1 modify num int null;
*)去掉主键自增
    alter table tb1 modify id int;
*)加主键自增
    alter table tb1 primary key auto_increment id int;
*)删除约束  
    alter table tb1 drop primary key;       
    alter table tb1 drop foreign key tb1;       
    alter table tb1 drop index 约束名；
*）删除表
    drop table if exists tb1;

数据类型
    数字
        tinyint int bigint decimal(10,2)
    字符串
        char(6) varchar(6) text 
    日期
        date time datetime timestemp
    表总字节量最大
        65535
约束
    主键、非空、唯一、外键、检查  
    默认值
        num int default 1;

mysql的 sql_mode变量
    linux 中安装mysql, sql_mode变量默认是空值，
    表示运行在“不严格”模式，非空字段会插入。字符             串超长会截断插入。
    --查看sql_mode变量的值        
    show variables like 'sql_mode';
    可以修改这个变量使mysql运行在严格模式
    --修改
    set global sql_mode='STRICT_TRANS_TABLES';
    --退出，重新进入mysql，再查看变量

show variables like ‘sql_mode’;

sql

*structured query language
    结构化查询语言
*sql 标准语法
*数据库厂商都有自己的扩展语法
    *）mysql 扩展
    *）oracle    plsql
    *)sql server    t-sql
*)sql语句分类
    *）DDL       --定义语言，建库建表修改表...
    *）DML       --数据操作语言，增删改...

*）DQL –数据查询语言,select

插入数据 insert

*insert into tb1 values(5,'abc');
    全部字段按字段顺序插入值；

*insert into tb1(name,gender) values('M','张三');
    向指定字段插入值；

*insert into tb1(name,gender) values('M','张三')，
                      ('F','张四'),
                      ('H','张五')；
    向指定字段插入多个值，非标准sql
*insert into tb1 select*from tb2
 insert into tb1(name,gender) select name,gender from tb2;
    向 tb1 插入 tb2 表中的数据

案例：

*)、新建学生数据库
    create database stu charset utf8;
    use stu;
*）、新建学生表 students
    drop table if exists students;
    create table students(
        id int primary key auto_increment,
        name varchar(20),
        gender char(1),
        birth date
    )engine=innodb charset=utf8;
*）、向学生表插入数据
    insert into students(name,gender,birth) values 
                ('张三那','M','2017-10-10'),
                ('张四那','w','2017-10-1');
    insert into students(name,gender,birth) values 
                ('张五那','h','2017-10-20'),
                ('张六那','D','2017-1-1');
    insert into students(name) values('李四');
    select * from students; --查看学生表信息

*）、同表复制数据
    2条——4条——8条——16条
    insert into students(name,gender,birth) select      name,gender,birth from students;

*）、从 students 表查询的结果，创建成一张新的表
    create table xueshengs as select            id,gender,name,birth from students;

    alter table xueshengs add primary key(id); --设置主键
    alter table xueshengs modify id int auto_increment;--自增主键

    select * from xueshengs limit 5; --只查询前5条
    alter table xueshengs add primary key(id);--添加id主键
    desc xueshengs;--查看表中字段类型约束

*）、联系方式表，与学生表一对一关系
    一对一关系，不重复的非空外键

    drop table if exists lianxi;
    --创建联系表
    create table lianxi(
        xs_id int primary key,
        tel varchar(100),
        qq varchar(20),
        email varchar(50),
        foreign key(xs_id) references xueshengs(id)
    );
    --插入数据
    insert into lianxi(xs_id, tel) values(1,'15454454');
    insert into lianxi(xs_id, tel) values(2,'25454454');
    insert into lianxi(xs_id, tel) values(3,'35454454');
    insert into lianxi(xs_id, tel) values(4,'45454454');
    insert into lianxi(xs_id, tel) values(5,'55454454');
    --查看数据

select * from lianxi;

修改数据 update

--修改一条数据
*update tb1 set name='abc',age=23,gender=null where id=43;

案例：修改学生5的年龄

update xueshengs set birth='2020-2-2' where id=5; --修改出生日期
select * from xueshengs where id=5; --查看

--修改为多条相同数据
update xueshengs set name='刘德华',gender='男',birth='2050-1-5'

where id in(2,4,6,8,10);

删除语句 delete

*delete from tb1; 删空表
*delete from tb1 where ...

案例：删除名字叫张三那的数据
--用所有张三的id过滤，删除联系
delete from lianxi where xs_id in(
    select id from xueshengs where name='刘德华'
);

delete from xueshengs where name=’张三那’;

表与表的关系

stu数据库中 添加 banji 表
*)创建表
drop table if exists banji;
create table banji(
id int primary key auto_increment,
name varchar(20)
);

*)修改xueshengs表，添加外键字段 banji_id

alter table xueshengs add banji_id int; --添加xueshengs表字段
alter table xueshengs add foreign key(banji_id) references      banji(id);

*）添加班级“男班”，“女班”
    insert into banji(name) values('男班');
    insert into banji(name) values('女班');
    insert into banji(name) values('混合班');
*）男女分班
    update xueshengs set banji_id=1 where gender='w';
    update xueshengs set banji_id=2 where gender='h';
    update xueshengs set banji_id=3 where gender='D';

    select * from xueshengs where banji_id=1; --查询
*)表连接查询
    select 
        x.id,x.name,
        b.id banid,b.name banname
    from 
        xueshengs x,banji b
    where 
        x.banji_id=b.id

limit 40;

三张表的关系（xueshengs表 kecheng表 中间表xs_kc_link）

*）创建kecheng表
    drop table if exists kecheng;
    create table kecheng(
        id int primary key auto_increment,
        name varchar(20)
    );
*）创建    xs_kc_link 中间表
    -- 多对多关系中间表
    drop table if exists xs_kc_link;
    create table xs_kc_link(
        xs_id int not null,--非空
        kc_id int not null,
        foreign key(xs_id) references xueshengs(id),--外键
        foreign key(kc_id) references kecheng(id),--外键
        unique key(xs_id,kc_id)--唯一
    );
*）添加课程，学生选课
    insert into kecheng(name) values('语文'),('数学'),
                    ('英语'),('地理'),('化学');

    insert into xs_kc_link values(1,2),(2,3),(3,4),(4,5);

    --连接起来查询
    select 
        x.name,
        k.name kecheng
    from 
        xueshengs x,kecheng k,xs_kc_link l
    where 
        x.id=l.xs_id and
        k.id=l.kc_id

limit 40;

查询数据 select (重点)

准备数据，公司员工管理系统数据  hr_mysql.sql

mysql>source /home/soft01/hr_mysql.sql

*）查看几张表：
    show tables;
    desc employees;
    desc departments;
    select * from employees;
    select * from departments;

*)where 字句过滤条件
    =   等于
    <>  不等
    >   大于
    <   小于
    <=  小于等于
    >=  大于等于
    between 小值 and 大值 范围
    in  指定固定的取值
    like    模糊查询 通常只查字符串
    is null 
    not between and
    not in
    is not null
    and
    or
    \ : 指定转移运算符
    \_  普通下划线字符


例子：

1)查询 employees 表中employee_id，first_name,salary
    select employee_id,first_name,salary from employees;

2)查询 employees 表中薪水大于等于10000的员工
    select employee_id,first_name,salary from employees where salary>=10000;

3)查询工种代码（job_id)是IT_PROG的员工
    select employee_id,first_name,salary,job_id from employees where job_id='IT_PROG';

4)查询上司的工号(manager_id)是100的员工
select employee_id,first_name,salary,manager_id from employees where manager_id=100;

5)查询部门编号（department_id）是30的员工
select employee_id,first_name,salary,department_id from employees where department_id=30;

6)部门编号（department_id）不在 50 部门的员工
select employee_id,first_name,salary,department_id from employees where department_id<>50;

7)薪水范围[5000,8000](范围:between .. and ..)
select employee_id,first_name,salary from employees where salary between 5000 and 8000;

8)部门编号（department_id）为 10,20,60,90 四个部门的员工
select employee_id,first_name,salary,department_id from employees where department_id in(10,20,60,90);

9)查询 first_name 中包含 ar 的员工
select employee_id,first_name,salary from employees where first_name like '%ar%';

10)查询 first_name 第三个字符时 e 的员工 _通配一个
select employee_id,first_name,salary from employees where first_name like '__e%';

11)查询工种（job_id） 以 SA 开头的员工
select employee_id,first_name,salary,job_id from employees where job_id like 'SA%';

练习：

1、商品描述表 tb_item_desc
drop table if exists tb_item_desc;

create table tb_item_desc(
    item_id bigint(20) primary key comment '商品编号',
    item_desc text comment '商品描述，Json格式',
    created datetime comment '创建时间',
    updated datetime comment '更新时间',
    foreign key(item_id) references tb_item(id)
)engine=innodb charset=utf8;


2、规格参数模板表tb_item_param
drop table if exists tb_item_param;

create table tb_item_param(
    id bigint(20) primary key auto_increment comment '自增编号',
    Item_cat_id bigint(20) not null comment '商品分类id',
    param_data text comment '商品参数，Json格式',
    created datetime comment '创建时间',
    updated datetime comment '更新时间',
    foreign key(Item_cat_id) references tb_item_cat(id)
)engine=innodb charset=utf8;


3、具体商品规格参数表tb_item_param_item

drop table if exists tb_item_param_item;

create table tb_item_param_item(
    id bigint(20) primary key auto_increment comment '规格参数编号',
    item_id bigint(20) not null comment '商品编号',
    param_date text comment '参数数据Json格式',
    updated datetime comment '更新时间',
    created datetime comment '创建时间',
    foreign key(item_id) references tb_item(id)
)engine=innodb charset=utf8;


4、用户表

create table tb_user(
    id bigint(20)  primary key auto_increment comment'用户编号',
    username varchar(50) unique not null comment'用户名',
    password varchar(32) not null comment'密码加密存储',
    phone varchar(20) unique comment'注册手机号',
    email varchar(50) unique comment'注册邮箱',
    created datetime comment'创建时间'          

)engine=innodb charset=utf8;

作业：

4.2.6 订单表（tb_order)

create table tb_order(
    order_id varchar(50) primary key comment '订单编号',
    user_id bigint(20) not null comment '用户编号',
    add_id bigint(20) not null comment '地址编号',
    payment decimal comment '实付金额。单位:元。精确到2位',
    payment_type int(2) comment '支付类型，1-在线支付、2-货到付款',
    post_fee decimal comment '邮费，单位元精确2位',
    status int(4) comment '状态：1、未付款，2、已付款，3、未发货，4、已发货，5、待收货，6、待评价，7、交易成功，8、交易关闭，9、删除',
    payment_time datetime comment '付款时间',
    consign_time datetime comment '发货时间',
    end_time datetime comment '交易完成时间',
    close_time datetime comment '交易关闭时间',
    shipping_name varchar(20) comment '物流名称',
    shipping_code varchar(20) comment '物流单号',
    buyer_message varchar(100) comment '买家留言',
    buyer_nick varchar(50) comment '买家昵称',
    buyer_rate int(2) comment '买家是否已评价',
    create_time datetime comment '订单创建时间',
    update_time datetime comment '订单更新时间',
    foreign key(user_id) references tb_user(id) 
)engine=innodb charset=utf8;

4.2.4 订单详情表(tb_order_item)

create table tb_order_item(
    id varchar(20) primary key comment '流水编号',
    item_id bigint not null comment '商品编号',
    order_id varchar(50) not null comment '订单编号',
    num int(10) comment '商品购买数量',
    title varchar(200) comment '商品标题',
    price decimal comment '商品单价',
    total_fee decimal comment '商品总价',
    pic_path varchar(200) comment '商品图片地址',
    cereated datetime comment '创建时间',
    updated datetime comment '更新时间',
    foreign key(item_id) references tb_item(id),
    foreign key(order_id) references tb_order_item(order_id)
)engine=innodb charset=utf8;

    --注意 此表中的item_id字段类型时bigint 不是varchar

.