cmd框设置的合适尺寸 100X30 字号最大 另外在全屏情况下 按alt+空格会出现属性选项
1、查看表结构的三种方式
desc tablename;
show columns from tablename;
show create table tablename; // 查看创建表的语句
2、Alter 更改表元素 ADD / DROP / MODIFY / CHANGE
alter table tablename1 rename tablename2; // 改表名:tablename1改名为tablename2
alter table tablename change [column] myname alteredname varchar(10); // 改字段名:将tablename的myname字段更名为alteredname字段 类型为 varchar(10)
alter table tablename modify [column] myname varchar(20); //改字段类型: 将tablename的myname字段的类型改为 varchar(20)
alter table tablename add [column] myname varchar(20); // 添加字段 myname;
alter table tablename drop[column] myname; // 删除字段 myname;
alter table tablename add [column] myname varchar(20) after id; // 添加字段 myname 在字段Id之后;
alter table tablename modify [column] myname varchar(20) first; // 将字段放置到第一位 (int类型后面可以不写位数)
3、一次性插入多个纪录
insert into tablename(field1,field2...) values(field1_value,field2_value....),(field1_value,field2_value.....),(field1_value,field2_value.....),.......;
4、update和delete可以一次性操作多条纪录
update beijing b,shanghai s set b.people = b.people+1,s.speople = s.speople+1 where b.people = s.speople;
等同于 update beijing,shanghai set beijing.people = beijing.people+1,shanghai.speople = shanghai.speople+1 where beijing.people = shanghai.speople;
delete b,s from beijing b,shanghai s where b.people = s.speople;
这个必须这样搞,没有直接用数据表原名。
(注意:where条件中,有多个条件的情况,相互间要用and或or,而不要用逗号)
5、聚合
SELECT [field1,field2...] fun_name // 功能函数
FROM tablename
[WHERE conditions]
[GROUP BY field1,field2...]
[WITH ROLLUP] // 再总的汇总下
[HAVING conditons] // 结果中再加条件筛选
SELECT people ,count(1) as total FROM beijing GROUP BY people WITH ROLLUP HAVING count(1) >100;
SELECT MIN(people),MAX(people),SUM(people) FROM beijing;
联合查询 SELECT name,sname FROM beijing,shanghai WHERE beijing.name = shanghai.sname
等同于 SELECT beijing.name,shanghai.sname FROM beijing,shanghai WHERE beijing.name = shanghai.sname
6、表连接
left join 是以左表的每一项为基础,右表符合条件的上。若左表项目超过右表,则不足地方NULL填充。另外注意连接条件是ON,而不是WHERE
right join 同理反之。
例:SELECT name,sname FROM beijing LEFT JOIN shanghai ON beijing.people = shanghai.speople;
7、子查询
SELECT * FROM beijing WHERE id IN (SELECT id FROM shanghai);
等同于表连接 SELECT beijing.* FROM beijing,shanghai WHERE beijing.id = shanghai.id; 一般该情况用于优化子查询
记录只有一条情况下,可以如此写:SELECT * FROM beijing WHERE id = (SELECT id FROM shanghai LIMIT 1);
8、记录联合
【select语句】 UNION | UNION ALL 【select语句】
UNION ALL 是将所有结果合并
UNION 等同于distinct UNION ALL的结果,去除重复结果。
9、帮助使用
例如 ? create table 即是查看create table的用法
10、Mysql存储过程学习 http://www.blogjava.net/sxyx2008/archive/2009/11/24/303497.html
11、索引和外键
show index from tablename\G 查看一个表的索引
外键只适用于innodb引擎的表
alter table xiaodi add foreign key(dage_id) refences dage(dage_id) on delete cascade on update cascade;
// 增加外键dage_id , 会自动为xiaodi 表的该外键constraint 设为 xiaodi_ibfk_1
alter table xiaodi drop foreign key xiaodi_ibfk_1; // 删除刚才创建的外键,删除外键后,xiaodi表中的dage_id会变为key索引,可以用show index查看到
alter table xiaodi drop index dage_id // 删除这个索引
索引的具体内容查看 http://www.cnblogs.com/tianhuilove/archive/2011/09/05/2167795.html