• MySql的基操勿六


    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;
    

    存储过程;

  • 相关阅读:
    【算法】273-每周一练 之 数据结构与算法(Tree)
    【算法】272-每周一练 之 数据结构与算法(Dictionary 和 HashTable)
    【CSS】271- RGB、HSL、Hex网页色彩,看完这篇全懂了
    【Redis】270- 你需要知道的那些 redis 数据结构
    Hybird App 应用开发中5个必备知识点复习
    【Vuejs】269- 提升90%加载速度——vuecli下的首屏性能优化
    【富文本】268- 富文本原理了解一下?
    HTML5 CSS3 经典案例:无插件拖拽上传图片 (支持预览与批量) (二)
    HTML5 CSS3 经典案例:无插件拖拽上传图片 (支持预览与批量) (一)
    HTML5 CSS3 专题 : 拖放 (Drag and Drop)
  • 原文地址:https://www.cnblogs.com/dabaine/p/10094485.html
Copyright © 2020-2023  润新知