mysql 数据库 day_03

回顾：

mysql   部分收费mariadb 免费客户端链接mysql -uroot -p客户端退出exit 或 \q显示所有数据库show databases;show schemas;创建数据库create database db1charset utf8;删除数据库drop database db1drop 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_idalter 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.sqlmysql>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_descdrop 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_paramdrop 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_itemdrop 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

.