一,关于对表和字段操作
1、给表中增加列:
alter table 表名 add colum 字段名 类型(长度);
alter table t_campus_devicemgr_device add colum style varchar(20);
2、删除表中数据:
delete from emp;
3、删除表:
drop table emp;
4、给用户授权
alter table t_campus_devicemgr_device owner to hwac;
二、关于表的索引
1、从理论上说,完全可以为数据库表里的每个字段分别建一个索引,但mysql把同一个数据表里的索引总数限制为16个。
2、索引的长度设置为10~15个字符已经足以把搜索范围缩小到很少的几条数据记录了。
三、sql优化
1)查询时,能不用* 就不用,尽量写全字段名。
2)索引不是越多越好,每个表控制在6个索引以内。范围where条件的情况下,索引不起作用,比如where value<100
3)大部分情况连接效率远大于子查询,但是有例外。
4)有时候可以1条大的SQL可以分成几个小SQL顺序执行,分了吧,速度会快很多。
5)每隔一段时间用alter table table_name engine=innodb;优化表
6)连接时注意:小表 jion 大表的原则
四、使用dos命令对数据库操作
1)用户名:root
密码:java
2)连接mysql数据库
直接使用命令的方式进行连接登录
mysql -u root -p java
查看数据库
show databases;
使用数据库
use 数据库名;
查看数据库表
show tables;
查看数据库表的结构
desc 表名;
创建数据库
create database 数据库名;
3)修改密码
格式: MySQLadmin -u 用户名 -p 旧密码 password 新密码
如1):个root加个密码ab12.首先在dos下进入目录mysqlbin,然后键入一下命令 mysqladmin -uroot -password ab12
注:因为开始时root没有密码,所以-p旧密码一项就可以省略了。
如:2)再讲roo的密码改为djg345 mysqladmin -uroot -pab12 password djg345
五、sql优化
1、统一SQL语句
select * from dual; 和select * FROM dual;
虽然只是大小写不同,查询分析器就认为是两句不同的SQL语句,必须进行两次解析;应该保证相同的查询语句在任何地 方都一致,多一个空格都不行;
2、不要把SQL语句写得太长,太过冗余
一层嵌套语句还是比较常见的,超过3层嵌套,查询优化器就很容易给出错误的执行计划;
3、考虑使用“临时表”暂存中间结果
将临时结果暂存在临时表,后面的查询就在tempdb中了,这可以避免程序中多次扫描主表,也大大减少了程序执行中“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。
4、SQL语句必须采用绑定变量
select * from orderheader where changetime >’2010-10-20 00:00:01′
select * from orderheader where changetime >’2010-09-22 00:00:01′
select*from orderheader where changetime > @chgtime
一次解析,多次重用,是提高数据库效率的原则;
5、绑定变量窥测
指的是,当第一次查询时,使用某个条件查询,数据量较大,数据库执行计划会选择扫描表查询;
当第二次查询时,同样使用这个条件查询,但是数据量不大,本可以按照索引进行查询,但是,由于重用了第一次解析的那个执行计划,那么第二次也将采用表扫描方式;
6、合理使用like模糊查询
select * from contact where username like ‘%yue%’
关键词 %yue%,由于yue前面用到了“%”,因此该查询必然走全表扫描,除非必要,否则不要在关键词前加%;
7、sql语句,尽量较少对数据库表的访问次数;
8、适当的使用内部函数(PLSQL中的自定义函数) ,以及存储过程(数据库端的业务逻辑比较复杂的)。
9、用exists替代 in或者not exists 替代 not in:
10、使用索引:
(1)、经常被访问的列,并且这个列是非主键(或者外键),查询列数据范围跨度比较大,在行记录中出现次数比率低,这行记录中空值多;
(2)、使用索引的列尽量不要使用not(条件),也尽量不要使用is null或者 is not null。原因:使用not后,索引失效,数据库会将这列数据全扫描。
(3)、针对多个索引列出现在where子句的or条件下时:使用union比or效率高:(使用or时,这些列忽略了索引);
(4)、一个索引,加在一个表中多列上, 此时如果where子句出现了这几列,此时起作用的索引列是靠前的列。
(5)、在对索引列使用to_char 或者to_number函数时, 此时索引失效。
(6)、distinct 去重复效率低下: 可以通过exists实现:
(7)、从java角度出发:避免过多的使用string字段来 “+”号连接,拼接sql语句。可以考虑通过StringBuffer 中append方法追加sql。
(8)、如果使用union或者union all了,此时如果不需要考虑去掉重复的数据,尽量不要使用union ,因为union默认是去重复的在去重复过程中,也会影响效率。
(9)、使用视图: (数据量非常大的情况下)经常被查询的列数据,并且这些数据不被经常的修改,删除。
11、trunc是截断表,将表所有数据删除,释放表空间,不可以回滚。 delete:删除表数据,不释放表空间,可以回滚。
12、数据库表语句的类型有哪些:
(make)DML:数据库操纵语言: 主要对数据库数据进行操作的。delete,update,select等。
(controller)DCL:数据库控制语言:主要有:commit,rollback,授权,用户等操作。
(definded)DDL:数据库定义语言:主要有:drop,alter (对表结构操作的 )。
13、数据库设计三大范式
第一:数据库表中列,拆到不能拆为止。(如:电话列存储固定电话和移动电话,应该拆成固定电话列和移动电话列)
第二:(满足第一范式前提下) 较少非主键列之间的依赖关系,适当的使用外键(拆成多个表)。(如:学生信息,课程,成绩都设计在一个表中,应该拆成三个表)
第三:在满足一范式,二范式基础上,在表中不能含有另一张表的非主键列。