mysql 命令行下 显示 中文字符 问题 和 关于数据库 2个 笔试题( 在线等待！)

Question

1Mysql 默认字符设置为 gbk
在Mysql query browser 可以正确 显示 中文。但是命令行下：
mysql> status
--------------
C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe  Ver 14.14 Distrib 5.1.30,
 for Win32 (ia32)Connection id:          1
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.1.30-community MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    gbk
Db     characterset:    gbk
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 22 min 39 secThreads: 1  Questions: 6  Slow queries: 0  Opens: 15  Flush tables: 1  Open tabl
es: 8  Queries per second avg: 0.4
--------------mysql> select * from Course;
+-----+--------------+------+---------+
| Cno | Cname        | Cpno | Ccredit |
+-----+--------------+------+---------+
| 1   |      | 7    |       4 |
| 2   |      | NULL |       2 |
| 3   |      | 1    |       4 |
| 4   |      | 6    |       3 |
| 5   |    | 7    |       4 |
| 6   | Java | NULL |       2 |
| 7   |      | 6    |       4 |
+-----+--------------+------+---------+
7 rows in set (0.55 sec)
不能 显示 中文。正确的应该为：
 Cno | Cname        | Cpno | Ccredit |
+-----+--------------+------+---------+
| 1   | 数据库系统   | 5    |       4 |
| 2   | 高等数续     | NULL |       2 |
| 3   | 信息系统     | 1    |       4 |
| 4   | 操作系统     | 6    |       3 |
| 5   | 数据结构     | 7    |       4 |
| 6   | Java程序设计 | NULL |       2 |
| 7   | 编译原理     | 6    |       4 |
下面是我遇到的笔试题：
2Given the following table in MySQL.  The table consists of 10,000 records
Employee Table
Field Type
emp_id char(6)
emp_firstname varchar(20)
emp_lastname varchar(20)
Emp_gender char(1)
emp_address varchar(255)
emp_joindate datetime
emp_birthday datetime
emp_res varchar(255) Consider the SQL statement below and comment the size of result set in JDBC.  What is the effect of result set when the number of record in Employee table growth?SELECT * from Employee.
SELECT emp_id, emp_firstname, emp_lastname from Employee.
SELECT emp_id, emp_firstname, emp_lastname from Employee limit 1000, 25.
answer:3If you have following batch of SQL:
Insert into table A values (123, 'Student A', 'Teacher B', 'Class C')
Insert into table B values (123, 'English', 'Grade A')
Insert into table B values (123, 'Math', 'Grade B')If the third statement failed, how can you ensure the first and second statement will not be applied to the database too
answer:

Answer 1

在mysql的安装目录中找到my.ini配置设置文档添加
[mysql]default-character-set=utf8
可在命令行中显示中文字符

Answer 2

2
SELECT emp_id, emp_firstname, emp_lastname from Employee limit 1000, 25. 
3
 select @@autocommit
如果为1，则自动提交，否则要用COMMIT提交

Answer 3

1. 在命令行中 set names 'gbk':2. 
SELECT * from Employee.
10,000 records　and all columnsSELECT emp_id, emp_firstname, emp_lastname from Employee.
10,000 records　only column emp_id, emp_firstname, emp_lastname SELECT emp_id, emp_firstname, emp_lastname from Employee limit 1000, 25.
record of employee from 1001 to 1024,and column emp_id, emp_firstname, emp_lastnamethe 3rd is more effective.2. use the transaction.
set autocommit=off
and then begin the transaction, after all SQL statement executed , commit.

Answer 4

为什么 gbk不行，我的default-character-set=GBK ？
下面这个没有用： 
mysql> set names 'gbk'; 
Query OK, 0 rows affected (0.01 sec) mysql> select * from Course; 
+-----+--------------+------+---------+ 
| Cno | Cname        | Cpno | Ccredit | 
+-----+--------------+------+---------+ 
| 1  |      | 7    |      4 | 
| 2  |      | NULL |      2 | 
| 3  |      | 1    |      4 | 
| 4  |      | 6    |      3 | 
| 5  |    | 7    |      4 | 
| 6  | Java | NULL |      2 | 
| 7  |      | 6    |      4 | 
+-----+--------------+------+---------+ 
7 rows in set (0.00 sec) 

Answer 5

 SHOW CREATE TABLE Course;
看看字符集是什么

Answer 6

mysql> show create table Course;
+--------+---------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
---------------+
| Table  | Create Table               |
+--------+---------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
---------------+
| Course | CREATE TABLE `course` (
  `Cno` varchar(10) NOT NULL,
  `Cname` varchar(50) DEFAULT NULL,
  `Cpno` varchar(10) DEFAULT NULL,
  `Ccredit` smallint(10) DEFAULT NULL,
  PRIMARY KEY (`Cno`),
  KEY `Cpno` (`Cpno`),
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`Cpno`) REFERENCES `course` (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+--------+---------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
---------------+
1 row in set (1.97 sec)

Answer 7

SHOW full COLUMNS FROM course;
贴结果

Answer 8

mysql> show full columns from Course;
+---------+--------------+----------------+------+-----+---------+-------+------
---------------------------+---------+
| Field   | Type         | Collation      | Null | Key | Default | Extra | Privi
leges                      | Comment |
+---------+--------------+----------------+------+-----+---------+-------+------
---------------------------+---------+
| Cno     | varchar(10)  | gbk_chinese_ci | NO   | PRI | NULL    |       | selec
t,insert,update,references |         |
| Cname   | varchar(50)  | gbk_chinese_ci | YES  |     | NULL    |       | selec
t,insert,update,references |         |
| Cpno    | varchar(10)  | gbk_chinese_ci | YES  | MUL | NULL    |       | selec
t,insert,update,references |         |
| Ccredit | smallint(10) | NULL           | YES  |     | NULL    |       | selec
t,insert,update,references |         |
+---------+--------------+----------------+------+-----+---------+-------+------
---------------------------+---------+

Answer 9

奇怪，设置都是正确的，你用
set names 'latin1'
试试

Answer 10

检查你的windows中的区域设置中的语言设置。

Answer 11

mysql> set names 'latin1';
mysql> select * from Course;
+-----+----------+------+---------+
| Cno | Cname    | Cpno | Ccredit |
+-----+----------+------+---------+
| 1   | ????     | 7    |       4 |
| 2   | ????     | NULL |       2 |
| 3   | ????     | 1    |       4 |
| 4   | ????     | 6    |       3 |
| 5   | ?????    | 7    |       4 |
| 6   | Java???? | NULL |       2 |
| 7   | ????     | 6    |       4 |
+-----+----------+------+---------+
7 rows in set (0.20 sec)
控制面板——区域和语言选项——都是中文 
没问题啊！

Answer 12

因为你的DOS窗口的默认属性是英文。做如下操作。１。 在你的DOS窗中的左上角标题栏片左键，属性，
２。在字体中，选择“宋体”，确认
３。mysql中 set names 'gbk';应该就可以了。

Answer 13

我在Mysql Command Line Client 左上角 属性，字体 选择 新宋体 
mysql> set names 'gbk';
Query OK, 0 rows affected (0.00 sec)mysql> select * from Course;
+-----+--------------+------+---------+
| Cno | Cname        | Cpno | Ccredit |
+-----+--------------+------+---------+
| 1   |      | 7    |       4 |
| 2   |      | NULL |       2 |
| 3   |      | 1    |       4 |
| 4   |      | 6    |       3 |
| 5   |    | 7    |       4 |
| 6   | Java | NULL |       2 |
| 7   |      | 6    |       4 |
+-----+--------------+------+---------+
7 rows in set (0.00 sec)
可是还是一样。
很感谢你们的回答！期待！

Answer 14

按照下面检查[code=BatchFile]mysql> show full columns from t1;
+-------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type        | Collation      | Null | Key | Default | Extra | Privileges                      | Comment |
+-------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+
| id    | int(11)     | NULL           | NO   | PRI | NULL    |       | select,insert,update,references |         |
| c1    | varchar(30) | gbk_chinese_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------+-------------+----------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.01 sec)
mysql> show variables like 'char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.1\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)mysql>[/code]然后看一下 DOS 窗口属性中，第一页上的当前代码页(current code page) 是不是936 ANSI/OEM - Siplified Chinese GBK

Answer 15

mysql> show full columns from Course;
+---------+--------------+----------------+------+-----+---------+-------+------
---------------------------+---------+
| Field   | Type         | Collation      | Null | Key | Default | Extra | Privi
leges                      | Comment |
+---------+--------------+----------------+------+-----+---------+-------+------
---------------------------+---------+
| Cno     | varchar(10)  | gbk_chinese_ci | NO   | PRI | NULL    |       | selec
t,insert,update,references |         |
| Cname   | varchar(50)  | gbk_chinese_ci | YES  |     | NULL    |       | selec
t,insert,update,references |         |
| Cpno    | varchar(10)  | gbk_chinese_ci | YES  | MUL | NULL    |       | selec
t,insert,update,references |         |
| Ccredit | smallint(10) | NULL           | YES  |     | NULL    |       | selec
t,insert,update,references |         |
+---------+--------------+----------------+------+-----+---------+-------+------
---------------------------+---------+
4 rows in set (0.11 sec)mysql> show variables like 'char%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name            | Value
     |
+--------------------------+----------------------------------------------------
-----+
| character_set_client     | gbk
     |
| character_set_connection | gbk
     |
| character_set_database   | gbk
     |
| character_set_filesystem | binary
     |
| character_set_results    | gbk
     |
| character_set_server     | gbk
     |
| character_set_system     | utf8
     |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.1\share\chars
ets\ |
+--------------------------+----------------------------------------------------
-----+
8 rows in set (0.08 sec)当前代码页 是936 （ANSI/OEM - 简体中文 GBK）

Answer 16

那你现在insert 一条记录看看。

Answer 17

mysql> insert into Course
    -> values
    -> ('8','专业英语','5',2);
Query OK, 1 row affected (0.23 sec)mysql> select * from Course;
+-----+--------------+------+---------+
| Cno | Cname        | Cpno | Ccredit |
+-----+--------------+------+---------+
| 1   |      | 7    |       4 |
| 2   |      | NULL |       2 |
| 3   |      | 1    |       4 |
| 4   |      | 6    |       3 |
| 5   |    | 7    |       4 |
| 6   | Java | NULL |       2 |
| 7   |      | 6    |       4 |
| 8   |      | 5    |       2 |
+-----+--------------+------+---------+

Answer 18

在附件中，选择命令行窗口，再右键属性，选择字体为“宋体”
打开命令行窗口输入mysql>set names"gbk";
再查询你要的东西，就能正确显示中文。我这里可以的。

Answer 19

echo mysql_client_encoding($connect);