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;

存储过程;

巴特西

MySql的基操勿六

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

最新文章

热门文章