#檢視資料庫版本号
mysql> select @@version;
+------------+
| @@version |
+------------+
| 5.5.16-log |
+------------+
1 row in set (0.00 sec)
mysql> select * from information_schema.schemata; # 儲存了系統的全部的資料庫名 ，關鍵的字段是schema_name
# 2 rows in set (0.04 sec)表示僅僅有2個資料庫
+--------------+--------------------+----------------------------+------------------------+----------+
| catalog_name | schema_name | default_character_set_name | default_collation_name | sql_path |
+--------------+--------------------+----------------------------+------------------------+----------+
| def | information_schema | utf8 | utf8_general_ci | null |
| def | test | gb2312 | gb2312_chinese_ci | null |
+--------------+--------------------+----------------------------+------------------------+----------+
mysql> select * from information_schema.columns; #
# 關鍵的字段是table_name & column_name 411 rows in set (0.05 sec)
+---------------+--------------------+---------------------------------------+-------------------------------+------------------
| table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type |
character_maximum_length | character_octet_length | numeric_precision | numeric_scale | character_set_name | collation_name | column_type | column_key | extra
| privileges | column_comment |
+---------------+--------------------+---------------------------------------+-------------------------------+------------------
mysql> select * from information_schema.tables; # 包括全部的表名 。38 rows in set (0.09 sec) 表示有38張表
mysql> select count(*) from information_schema.tables; # count(*)傳回一共同擁有多少行(就是多少條記錄)
+----------+
| count(*) |
+----------+
| 38 |
+----------+
1 row in set (0.00 sec)
#關鍵的字段是table_column & table_name
+---------------+--------------------+---------------------------------------+-------------+--------+---------+------------+--
| table_catalog | table_schema | table_name | table_type | engine | version | row_format | table_rows | avg_row_length | data_length |
max_data_length | index_length | data_free | auto_increment | create_time | update_time | check_time | table_collation | checksum | create_options |
table_comment |
+---------------+--------------------+---------------------------------------+-------------+--------+---------+------------+--
mysql> select * from information_schema.tables where table_schema="test";
# keyword是table_name和table_schema (資料庫名)
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-----
| table_catalog | table_schema | table_name | table_type | engine | version | row_format | table_rows | avg_row_length | data_length | max_data_length | index_length |
data_free | auto_increment | create_time | update_time | check_time | table_collation | checksum | create_options | table_comment |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-----
| def | test | t_users | base table | innodb | 10 | compact | 0 | 0 | 16384 | 0 | 16384 | 9437184 | 1 | 2012-10
-06 12:21:23 | null | null | gb2312_chinese_ci | null | | |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-----
1 row in set (0.00 sec)
mysql> select * from information_schema.columns where table_name="t_users";
# 關鍵是得到 column_name
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+----
| table_catalog | table_schema | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length |
character_octet_length | numeric_precision | numeric_scale | character_set_name | collation_name | column_type | column_key | extra | privileges |
column_comment |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+----
| def | test | t_users | id | 1 | null | no | int | null | null | 10 | 0 | null |
null | int(11) | pri | auto_increment | select,insert,update,references | |
| def | test | t_users | name | 2 | null | no | text | 65535 | 65535 | null | null | gb2312
| gb2312_chinese_ci | text | | | select,insert,update,references | |
| def | test | t_users | password | 3 | null | no | text | 65535 | 65535 | null | null | gb2312
| gb2312_chinese_ci | text | | | select,insert,update,references | |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+----
3 rows in set (0.01 sec)
mysql> select "id","password" from information_schema.columns where table_name="t_users";
# 注意當要查詢的變量是常數的時候就是空查詢。傳回的一定就是你的查詢常量，通常是在union的查詢裡确定
顯示位置而用的
+----+----------+
| id | password |
+----+----------+
| id | password |
| id | password |
| id | password |
+----+----------+
3 rows in set (0.02 sec)
mysql> use test; #使用該資料庫
database changed
mysql> select * from test;
error 1146 (42s02): table 'test.test' doesn't exist
mysql> select * from t_users;
empty set (0.00 sec)
這樣就不須要再猜username與password啦
insert into `t_users`(`id`, `name`, `password`) values (001,'張三瘋','123456');
#插入一條記錄之後
mysql> select * from t_users;
+----+--------+----------+
| id | name | password |
+----+--------+----------+
| 1 | 張三瘋 | 123456 |
+----+--------+----------+
1 row in set (0.00 sec)
#假設沒有權限加入，就僅僅有逐位猜值啦
mysql> select count(*) from t_users where len(password)=12;
error 1305 (42000): function test.len does not exist
mysql>
# 二分查找法
#這裡報錯啦，該函數不存在。在mysql是length()在access裡是len();
mysql> select count(*) from t_users where length(password)=12;
error 1305 (42000): function test.len does not exist
#首先确定了密碼的長度
mysql> select password from t_users where length(password)<6;
empty set (0.00 sec)
mysql> select password from t_users where length(password)>6;
empty set (0.00 sec)
mysql> select password from t_users where length(password)=6;
+----------+
| password |
+----------+
| 123456 |
+----------+
1 row in set (0.00 sec)
#再進行逐位猜值
select * from t_users where asc(left(password,1))>0;
mysql> select password from t_users where left(password,1)<1;
empty set (0.00 sec)
mysql> select password from t_users where left(password,1)<2;
+----------+
| password |
+----------+
| 123456 |
+----------+
#函數運作并成功傳回，說明第一位的值就是1
#或者直接查詢密碼:
mysql> select password from t_users where length('password')>0;
+----------+
| password |
+----------+
| 123456 |
+----------+
1 row in set (0.00 sec)
mysql> select password from t_users where ascii(left(password,1))<2;
empty set (0.00 sec)
#在mysql裡面什麼函數都要寫全啦，在acess裡直接就是asc();
mysql> select password from t_users where ascii(left(password,1))=49;
+----------+
| password |
+----------+
| 123456 |
#能夠直接擦每一位的值。也能夠查acs值。可是直接查值是快些
#這樣直到猜完length(password)位為止
#可是中文的名字不好猜啊，1個字，2個位元組
>>> int("張")
traceback (most recent call last):
file "", line 1, in
valueerror: invalid literal for int() with base 10: '\xd6\xec'
>>>
>>> chr(66)
'b'
>>>
#事實上還是能夠查的
mysql> select password from t_users where left(name,1)="張";
+----------+
| password |
+----------+
| 123456 |
+----------+
1 row in set (0.00 sec)
mysql> select password from t_users where left(name,2)="張";
empty set (0.00 sec)
#記住left是傳回的全部的左邊的值哈
mysql> select password from t_users where left(name,2)="張三";
+----------+
| password |
+----------+
| 123456 |
+----------+
#mid(比對的字段，從第幾個開始，取幾個);能夠完畢逐位比較
mysql> select password from t_users where mid(name,2,1)="三";
+----------+
| password |
+----------+
| 123456 |
+----------+
1 row in set (0.00 sec)
![mysql資料庫有schema嗎_mysql資料庫schema是什麼[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)