mysql笔记 20140710
查询每张表 占用空间大小
select table_name, data_length,round(DATA_LENGTH/1024/1024, 2) 'size/MB', concat(round(DATA_LENGTH/1024/1024, 2),'MB') 'size(MB)' from information_schema.tables;
加上where 表示 forexpert 数据的 member表
where table_schema= 'forexpert' and table_name= 'member' ;
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/shopping?user=root&password=root");
登录：
mysql -u root -p md //md为数据库名，也可以不输入，回车后需要输入密码
exit; 退出
show create table article;显示article详细信息，包括编码
show create database bbs;显示bbs详细信息，包括编码
set character_set_results = gbk;//设置终端显示方式为gbk
注意事项：
int integer 32位整数
bigint 64位整数
serial bigint auto_increment not null primary_key
timestamp 在修改其他字段时，这个字段会自动刷新，可用于最后一次修改时间的记录
select concat(space(hier*2),catname) as category, hier from categories
concat( ) //把两个字符串连接在一起
space( ) //生成指定个数的空格字符
外键约束的四个状态
foreign key name references table2 (column2)
[on delete {cascade | set null | no action | restrict}]
restrict 默认行为，有外键关联时，table2的表的行数据不允许删除
set null table2记录删除时，该字段设为null
cascade table2记录删除时，将级联删除table1的记录
no action table2记录删除时，table1不做任何操作
删除外键
alter table drop foreign key foreign_key
临时禁用外键约束检查
set foreign_key_checks = 0
启用外键约束检查
set foreign_key_checks = 1
在此期间的外键约束错误不进行自动修正
mysql 同一张表的 index 最大为16
InnoDB的行级锁定机制发生在他们的索引上
索引的限制：
1.如果where字句的查询条件中有不等号（where column != ...),mysql 将无法使用索引
2.在where字句的查询条件中使用了函数，mysql无法使用索引
3.在join操作中，当主键和外键数据类型相同时，才能使用索引
4.如果where字句的查询条件里使用比较操作符like和regexp，mysql只有在搜索模板中的第一个字符不是通配符的情况下
才能使用索引
5.在orderby 中mysql只用在排序条件不是一个查询表达式的情况下才能使用索引
6.如果某个数据列包含太多重复值，建立索引的效果不是很好。
复合索引 index（columnA,columnB)
全文索引 (InnoDB不支持全文索引)
alter table tablename add fulltext( column1,column2)
#查询方法
select * from tablename where match(column1,column2) against('world1','world2','world3')
explain select *..... 将查看各个查询步骤里可供选择的索引清单
在视图中修改数据记录
在视图中使用insert 、update、delete取决于当初用来定义这个视图的select 命令
需要满足以下条件：
当初用来定义视图的select命令不得包含group by 、distinct、limit、union或者having子命令
如果某个视图的数据来自多个表，那 几乎是不可刷新的
视图应该包含主键索引、唯一索引、外键约束条件所涉及 的所有列。如果视图中缺少这样的数据列，将由
mysql选项updateable_views_with_limit来决定是否是允许刷新并返回一条警告（默认设置），还是不允许刷新
并触发一个错误(设置为0）
create [or replace] [algorithm= undefined | merge | temptable]
view name [(columnlist)] as select command [with [cascade |local] check option]
如果希望临时改用另外一种排序方式对查询结果进行排序，用collate
select authname from authors order by authname collate latin1_german2_ci
统计函数group_concat( )
select title, group_concat(authname order by authname separator ',') as authors
group by ...with rollup
自动添加一行，总数统计记录
将表中最新插入或者修改的记录删除
delete from authors order by ts desc limit 1
//创建数据库
C:\Users\mys>mysqladmin -u root -p create newdatabase
Enter password: ****
//删除数据库
C:\Users\mys>mysqladmin -u root -p drop newdatabase
Enter password: ****
//备份数据库
C:\Users\mys>mysqldump -u root -p newdatabase >d:\back.sql
Enter password: ****
//导入数据库
C:\Users\mys>mysql -u root -p newdatabase < d:\back.sql
Enter password: ****
create database mydata;//创建数据库
use mydata;//使用数据库
//创建一张表
create table dept
(
deptno int primary key,
dname varchar(14),
loc varchar(13);
pdate datetime
);
mysql> create table votelanguage(
-> id int not null auto_increment,
-> choice tinyint not null,
-> ts timestamp,
-> primary key(id));
Query OK, 0 rows affected (0.17 sec)
? //列出命令
. d:\mysql_script\mydata.sql //执行脚本
create table emp
(
empno int primary key,
ename varchar(10),
job varchar(10),
mgr int,
hiredate datetime,
sal double,
comm double,
deptno int,
foreign key (deptno) references dept(deptno)
);
show databases;
show tables;
desc dept;
insert into dept values (10, 'A', 'a');
insert into dept values (20, 'B', 'b );
commit;
select * from dept order by deptno desc limit 3, 2; //从第3条往后数2条（倒序）
select last_insert_id( ) //查询最后一次插入的id
create table article
(
id int primary key auto_increment,
title varchar(255)
);
insert into article values(null,'a');
insert into article values(null,'b');
select * from article;
insert into article (title) values('c');//自动递增
select * from article;
select now();//查看时间
select date_format(now(),'%Y-%m-%d %H:%i:%s');//按特定格式查看时
//oracle中 select to_char<sysdate , 'YYYY-MM-DD HH24:MI:SS'> from dual;
desc emp;
insert into emp values (9999, 'test', 'clerk', 7369, '1981-12-23 12:23:23
', 8000, 80, 10);
------^ java 编程 jdbc ^------
import java.sql.*;
public class TestMysqlConnection {
public static void main(String[] agrs) throws Exception {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
} catch (Exception ex) {
ex.printStackTrace();
}
try {
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test?user=monty&password=greatsqldb");
//conn = DriverManager.getConnection("jdbc:mysql://localhost/mydata?user=root&password=root");
} catch (SQLException ex) {
System.out.println("SQLException: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("VendorError: " + ex.getErrorCode());
}
}
}
drop database bbs;--删除原有的数据库
--创建数据库
create database bbs;
use bbs;
--创建表
--使用
create table article
(
id int primary key auto_increment,
pid int,
rootid int,
title varchar(255),
cont text,
pdate datetime,
isleaf int
);
-0 代表leaf，1代表非leaf
insert into article values(null,0,1,'蚂蚁大战大象','蚂蚁大战大象',now(),1);
insert into article values(null,1,1,'大象被打趴下','大象被打趴下',now(),1);
insert into article values(null,2,1,'蚂蚁也不好过','蚂蚁也不好过',now(),0);
insert into article values(null,2,1,'瞎说','瞎说',now(),1);
insert into article values(null,4,1,'没有瞎说','没有瞎说',now(),0);
insert into article values(null,1,1,'怎么可能','怎么可能',now(),1);
insert into article values(null,6,1,'怎么没有可能','怎么没有可能',now(),0);
insert into article values(null,6,1,'可能行很大的','可能行很大的',now(),0);
insert into article values(null,2,1,'大象进医院了','大象进医院了',now(),1);
insert into article values(null,9,1,'护士是蚂蚁','护士是蚂蚁',now(),0);
import java.sql.*;
public class ArticleTree {
public static void main(String[] args) {
new ArticleTree().show();
}
public void show() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager
.getConnection("jdbc:mysql://localhost/bbs?user=root&password=root");
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from article where pid = 0");
while(rs.next()){
System.out.println(rs.getString("cont"));
tree(conn, rs.getInt("id"), 1);
}
} catch(ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(rs != null) {
rs.close();
rs = null;
}
if(stmt != null) {
stmt.close();
stmt = null;
}
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
private void tree(Connection conn, int id, int level) {
Statement stmt = null;
ResultSet rs = null;
StringBuffer strPre = new StringBuffer("");
for(int i=0; i<level; i++) {
strPre.append(" ");
}
try {
stmt = conn.createStatement();
String sql = "select * from article where pid = " + id;
rs = stmt.executeQuery(sql);
while(rs.next()) {
System.out.println(strPre + rs.getString("cont"));
if(rs.getInt("isleaf") != 0)
tree(conn, rs.getInt("id"), level + 1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if(rs != null) {
rs.close();
rs = null;
}
if(stmt != null) {
stmt.close();
stmt = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
浙公网安备 33010602011771号