创建数据库:create database if not exists firefly default charset utf8 collate utf8_general_ci;
(CREATE DATABASE IF NOT EXISTS FIREFLY DEFAULT CHARSET utf8 COLLATE utf8_general_ci;)创建的时候都是小写的firefly
删除数据库:drop database firefly;(这个一定要是小写)
选择数据库:use firefly;
创建数据表:create table if not exists `testone`(`id` int unsigned auto_increment,
`contents` text not null,
primary key(`id`)
)engine=myisam default charset=utf8;
CREATE TABLE IF NOT EXISTS `testone` (`id` INT UNSIGNED AUTO_INCREMENT,
`contents` TEXT NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
删除数据表:drop table testone;
插入内容:INSERT INTO testone(id,contents)
VALUES
(NULL,"测试一下");
更新语句:update testone set id=4,contents="今天是阴天" where id=1;
field like %condition%
union 操作符
select * from table1 where condition1
union all|distinct
select * from table2 where condition2
默认就是把重复的语句删除,所以distinct写不写无影响
order by asc(升序) desc(降序)
group by 在分组的列上我们可以使用count sum avg 等函数
inner join select * from table1 inner join table2 on table1.id=table2.infoid;
left join
right join
IS NULL
IS NOT NULL
select name from table where name REGEXP '^st';
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
只有字innodb数据库引擎下才支持
alter table testone add i int;(添加字段)
alter table testone drop id;(删除字段)
alter table testone modify id char(10);(改变字段类型)
alter table testone change id ig int;(改变字段以及字段类型)
alter table testone engine=mysiam;
alter table testone modify name1 type1 first|after name2;
create index indexName on mytable(username(length));
SELECT VERSION( ) 服务器版本信息
SELECT DATABASE( ) 当前数据库名 (或者返回空)
SELECT USER( ) 当前用户名
SHOW STATUS 服务器状态
SHOW VARIABLES 服务器配置变量
SELECT * FROM runoob_tbl
-> INTO OUTFILE '/tmp/tutorials.txt';导出数据
OAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;导入数据
(CREATE DATABASE IF NOT EXISTS FIREFLY DEFAULT CHARSET utf8 COLLATE utf8_general_ci;)创建的时候都是小写的firefly
删除数据库:drop database firefly;(这个一定要是小写)
选择数据库:use firefly;
创建数据表:create table if not exists `testone`(`id` int unsigned auto_increment,
`contents` text not null,
primary key(`id`)
)engine=myisam default charset=utf8;
CREATE TABLE IF NOT EXISTS `testone` (`id` INT UNSIGNED AUTO_INCREMENT,
`contents` TEXT NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
删除数据表:drop table testone;
插入内容:INSERT INTO testone(id,contents)
VALUES
(NULL,"测试一下");
更新语句:update testone set id=4,contents="今天是阴天" where id=1;
field like %condition%
union 操作符
select * from table1 where condition1
union all|distinct
select * from table2 where condition2
默认就是把重复的语句删除,所以distinct写不写无影响
order by asc(升序) desc(降序)
group by 在分组的列上我们可以使用count sum avg 等函数
inner join select * from table1 inner join table2 on table1.id=table2.infoid;
left join
right join
IS NULL
IS NOT NULL
select name from table where name REGEXP '^st';
BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
只有字innodb数据库引擎下才支持
alter table testone add i int;(添加字段)
alter table testone drop id;(删除字段)
alter table testone modify id char(10);(改变字段类型)
alter table testone change id ig int;(改变字段以及字段类型)
alter table testone engine=mysiam;
alter table testone modify name1 type1 first|after name2;
create index indexName on mytable(username(length));
SELECT VERSION( ) 服务器版本信息
SELECT DATABASE( ) 当前数据库名 (或者返回空)
SELECT USER( ) 当前用户名
SHOW STATUS 服务器状态
SHOW VARIABLES 服务器配置变量
SELECT * FROM runoob_tbl
-> INTO OUTFILE '/tmp/tutorials.txt';导出数据
OAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;导入数据