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;
存储过程;
原文链接：https://www.cnblogs.com/dabaine/p/10094485.html
作者：大白讷
随时随地看视频
