欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySql的基操勿六

程序员文章站 2022-06-20 09:30:39
2018/12/6 星期四 19:34:07 authot by dabaine 数据库注释; 这就是注释 / ..... / 这也是注释 创建库; create databse [if not exists] dabaine [character set "utf8"]; 查看所有数据库; sho ......

2018/12/6 星期四 19:34:07

authot by dabaine

数据库注释;

 -- 这就是注释
/*.....*/ 这也是注释

创建库;

create databse [if not exists] dabaine [character set "utf8"];

查看所有数据库;

show databses;

查看数据库结构:

show create database dabaine;

查看当前数据库;

select database();

修改数据库;

alter database dabaine [character set "gbk"];

删除数据库;

drop database [if exists] dabaine;

使用数据库;

use database;

创建表;

create table dabaine(
    id smallint(10) primary key not null auto_increment,          
    name varchar(25) not null,
    gender boolean not null
);

删除表;

drop table dabaine;

查看表结构;

eg1:show create table dabaine;
eg2:show columns from dabaine;

查看表的全部信息;

desc dabaine;

修改表结构;

增加字段:
alter table dabaine add [column],add [column]......;
修改类型:
alter table dabaine modify colum_name attribute [first|after column_name] colum_name;
修改列名:
alter table dabaine change column_name new_column_name type [约束条件];
删除字段:
alter table dabaine drop [column]; 
重命名:
rename table table_name to new_table_name;

修改表内容;

插入:
eg1:insert into dabaine (id, name) values(1,"dabaine");
eg2:insert into dabaine set id = 2,name="dabaine";
更新:
update dabaine set name="cody" where name="dabaine";    
删除:
eg1:delete from dabaine where name = "cody";
eg2:truncate table dabaine; --把表摧毁,重新创建一张新表;

查询顺序;

select [distinct] *|field ... from dabaine
    where (不分组筛选)
    group by field
    having (分组后筛选)
    order by field
    limit 

查询别名;

selct distinct id + 10 as id from dabaine;

执行顺序;

from,where,select,group by,having, order by

聚合函数;

select name, sum(grade) from dabaine group by name;
ifnull(grade,0) --如果grade为空,则给它定为0;

外键约束;

创建主表:
create table class(
    id int(10) primary key auto_increment,
    name varchar(20),
    age int(5)
);
主表添加数据(多条):
insert into class(name,age) values
        ("cody",18),
        ("solider",19),
        ("guan",21),
        ("lee",22),
        ("strong",28),
        ("pig",38);
创建子表:
create table student(
    id int(10) primary key auto_increment,
    name varchar(20),
    age int(5),
    teacher_id int(10), --绑定外键的字段要和主表中的字段类型保持一致;
    constraint dabaine --给外键命名大白讷
    foreign key (teacher_id) --给子表的属性选择外键绑定 
    references class(id) --映射主表的属性(追随主表的id字段)
);
子表添加数据:
insert into student(name,age,teacher_id) values
    ("cody",18,1),
    ("solider",19,2),
    ("guan",21,3),
    ("lee",22,4),
    ("strong",28,5),
    ("pig",38,6);
这时,主表和子表已经有关联了,不可以随便删除主表的记录;
增加外键:
alter table son_table_name add constraint cody
        foreign key(son_table_field)
        references primary_table(field);
删除外键:
alter table son_table_name drop foreign key cody;

级联删除(cascade);

create table studentnew(
    id int(10) primary key auto_increment,
    name varchar(20),
    age int(5),
    teacher_id int(10),
    constraint cody foreign key (teacher_id) 
    references class(id) 
    on delete cascade --级联删除
);
    constraint cody foreign key (teacher_id) 
    references class(id) 
    on delete set null --主表删除后,子表记录设置为空值,且子表的字段属性不能设置为not null;
            on delete restrict --拒绝对主表进行更新删除操作;
    on delete no action --类似于restrict

多表查询;

笛卡尔积连接:
        a表中的全部数据m条 * b表中的全部数据n条;
连接查询~内连接:
    inner join
    eg1:select tablea.id,tablea.name,tableb.name from
            tablea,tableb where tablea.id = tableb.tablea_id
    eg2:select tablea.id,tablea.name,tableb.name from tablea 
            inner join tableb on tablea.id = tableb.tablea_id
            +---------+----+---------+
            | name    | id | name    |
            +---------+----+---------+
            | cody    |  1 | cody    |
            | solider |  2 | solider |
            | guan    |  3 | guan    |
            | cody    |  4 | lee     |
            | strong  |  5 | strong  |
            | lee     |  6 | pig     |
            +---------+----+---------+
连接查询~左外连接(左连接):
    left join
    select tablea.id,tablea.name,tableb.name from tablea 
            left join tableb on tablea.id = tableb.tablea_id
    --左连接以左表为主,select所选择的字段,左表中的记录会全部显示,而右表会去匹配左表里的记录,没有的则显示空值;
            +----+---------+---------+
            | id | name    | name    |
            +----+---------+---------+
            |  1 | cody    | cody    |
            |  2 | solider | solider |
            |  3 | guan    | guan    |
            |  4 | lee     | cody    |
            |  5 | strong  | strong  |
            |  6 | pig     | lee     |
            +----+---------+---------+
连接查询~右外连接(右连接):
    right join
    类似左连接,以右表为主;
            +------+---------+---------+
            | id   | name    | name    |
            +------+---------+---------+
            |    1 | cody    | cody    |
            |    4 | lee     | cody    |
            |    2 | solider | solider |
            |    3 | guan    | guan    |
            |    6 | pig     | lee     |
            |    5 | strong  | strong  |
            | null | null    | pig     |
            +------+---------+---------+

嵌套;

查询嵌套:
    select * from table_name where  field in (select field from table_name);
复制表:
    create table new_table(select * from old_table); --原表中的约束不会复制过来,需要重新添加
    selcet * from table_name where exists 
        (selcet field from table_name where....)
    --exists 后面的语句会返回一个布尔值,true则执行前面的select语句,
                flase 则返回空值;

索引;

    unique(唯一索引),fulltext(全局索引),spatial(空间索引),index|key(普通索引)
添加索引:
    eg1:create 
        [unique|fulltext|spatial] index|key 
        index_name on table_name (字段名[(长度)] [asc|desc]);
    eg2:alter table table_name 
        add [unique|fulltext|spatial] index|key index_name (字段名[(长度)] [asc|desc]);
删除索引:
    drop index index_name on table_name;
    unique:唯一索引的字段不能重复;
    多列索引:给多个字段添加索引 (field1,field2...)

事务;

     start transaction; --开启事务
 rollback; --回滚事务(撤销)
 commit;  --提交事务;
 savepoint; 保留点,事务处理中的临时占位符;

 savepoint name;
 rollback to svaepoint_name;

存储过程;