mysql作业是什么意思_MySQL 作业七

1.InnoDB的默认事务隔离级别是什么？它和read committed隔离级别有什么区别？

InnoDB的默认事务隔离级别是repeatable read(重复读)

确保在同一个事务中的读操作返回相同的结果，不管是否另外的事务修改了数据并提交；而read committed隔离级别在第二次读之前如果有另外的事务修改了数据并提交，则能读到最新的数据

可重复读有间隔锁

2.MySQL中默认的锁等待超时时长用哪个参数控制？默认值是多少秒？

默认是 50 秒，用innodb_wait_timeout 参数控制

3.简要说明间隔锁的作用？并举一个实际的事务例子

阻止对行记录间隔内的数据的插入

update temp set name='abc' where id between 3 and 5;

另一个事务执行 insert into temp values(4,'d'); 需要等待 update 结束并提交

4.用SQL语句人为制造一个死锁的例子

事务1：update temp set name='aa' where id=1;

事务2：update temp set name='bb' where id=2;

事务1：update temp set name='aaa' where id=2;

事务2：update temp set name='bbb' where id=1;

死锁发生

5.MySQL的死锁自动检测功能开启是由哪个参数控制的

可以通过 innodb_deadlock_detect 参数关闭死锁检测功能

6.MySQL的默认字符集是什么？怎样将实例的字符集设置成UTF8？

8.0默认字符集是 ulf8mb4

在my.cnf中配置 character-set-server=utf8，并重启数据库

[root@mysql-master ~]# vim /etc/my.cnf

[mysqld]

basedir=/data/mysql/mysql

datadir=/data/mysql/mysql/data

log_error=/data/mysql/mysql/data/abc.log

secure_file_priv=/tmp/innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend:max:20G [root@mysql-master ~]# /etc/init.d/mysql.server restart

Shutting down MySQL... SUCCESS!Starting MySQL.. SUCCESS![root@mysql-master ~]# mysql -u root -pmysql> show variables like '%character%';+--------------------------+-----------------------------------+

| Variable_name | Value |

+--------------------------+-----------------------------------+

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /data/mysql/mysql/share/charsets/ |

+--------------------------+-----------------------------------+

8 rows in set (0.03sec)

mysql>exit;

Bye[root@mysql-master ~]# vim /etc/my.cnf

[mysqld]

basedir=/data/mysql/mysql

datadir=/data/mysql/mysql/data

log_error=/data/mysql/mysql/data/abc.log

secure_file_priv=/tmp/innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend:max:20G

character_set_server=utf8[root@mysql-master ~]# /etc/init.d/mysql.server restart

Shutting down MySQL.. SUCCESS!Starting MySQL.. SUCCESS![root@mysql-master ~]# mysql -u root -pmysql> show variables like '%character%';+--------------------------+-----------------------------------+

| Variable_name | Value |

+--------------------------+-----------------------------------+

| character_set_client | utf8mb4 |

| character_set_connection | utf8mb4 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8mb4 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /data/mysql/mysql/share/charsets/ |

+--------------------------+-----------------------------------+

8 rows in set (0.03 sec)

7.假设表temp中的name字段有两行值，分别是’a’和’A’，请问在默认情况下查询select * from temp where name=‘A’会查询出几条记录？如果要查询结果只出现’A’这一条记录，应该如何设置？

select * from temp where name='A'collate latin1_general_cs;select * from temp where name collate latin1_general_cs='A';

alter table temp modify name varchar(10) collate lailn1_general_cs;

mysql> select case when 'a'='A' then 1 else 0end;+-------------------------------------+

| case when 'a'='A' then 1 else 0 end |

+-------------------------------------+

| 1 |

+-------------------------------------+

1 row in set (0.00sec)

mysql> select ascii('a');+------------+

| ascii('a') |

+------------+

| 97 |

+------------+

1 row in set (0.00sec)

mysql> select ascii('A');+------------+

| ascii('A') |

+------------+

| 65 |

+------------+

1 row in set (0.00sec)

mysql> show variables like 'collation';

Emptyset (0.01sec)

mysql> show variables like '%collation%';+-------------------------------+--------------------+

| Variable_name | Value |

+-------------------------------+--------------------+

| collation_connection | utf8mb4_0900_ai_ci |

| collation_database | utf8mb4_0900_ai_ci |

| collation_server | utf8_general_ci |

| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |

+-------------------------------+--------------------+

4 rows in set (0.00sec)

mysql> set collation_connection=utf8mb4_0900_as_cs;

Query OK,0 rows affected (0.00sec)

mysql> select case when 'a'='A' then 1 else 0end;+-------------------------------------+

| case when 'a'='A' then 1 else 0 end |

+-------------------------------------+

| 0 |

+-------------------------------------+

1 row in set (0.00sec)

mysql>use course;Database changedmysql> select * fromtemp;+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | c |

+----+------+

3 rows in set (0.00sec)mysql> insert into temp values(10,'a'),(11,'A');

Query OK,2 rows affected (0.09sec)

Records:2 Duplicates: 0 Warnings: 0mysql>commit;

Query OK,0 rows affected (0.00sec)

mysql>show create table temp;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| temp |CREATE TABLE `temp` (

`id`int(11) NOT NULL,

`name` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00sec)

mysql> select * fromtemp;+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | c |

| 10 | a |

| 11 | A |

+----+------+

5 rows in set (0.00sec)

mysql> select * from temp where name='a';+----+------+

| id | name |

+----+------+

| 10 | a |

| 11 | A |

+----+------+

2 rows in set (0.00sec)

mysql> select * from temp where name='A';+----+------+

| id | name |

+----+------+

| 10 | a |

| 11 | A |

+----+------+

2 rows in set (0.00sec)

mysql> select * from temp where name='A'collate utf8mb4_0900_as_cs;+----+------+

| id | name |

+----+------+

| 11 | A |

+----+------+

1 row in set (0.00sec)

mysql> select * from temp where name='a'collate utf8mb4_0900_as_cs;+----+------+

| id | name |

+----+------+

| 10 | a |

+----+------+

1 row in set (0.01 sec)

8. 为什么latin1字符集可以存储中文？

Latin1是一种很常见的字符集，这种字符集是单字节编码，向下兼容ASCII，其编码范围是0x00-0xFF，0x00-0x7F之间完全和ASCII一致，0x80-0x9F之间是控制字符，0xA0-0xFF之间是文字符号。很明显，Latin1覆盖了所有的单字节，因此，可以将任意字符串保存在latin1字符集中，而不用担心有内容不符合latin1的编码规范而被抛弃。——gbk和utf8是多字节编码，没有这种特性。

mysql使用者经常利用Latin1的这种全覆盖特性，将其它类型的字符串，gbk,utf8,big5等，保存在latin1列中。保存的过程中没有数据丢失，只要原样取出来，便又是合法的gbk/utf8/big字符串。

mysql>use course;mysql>show tables;+------------------+

| Tables_in_course |

+------------------+

| course |

| dept |

| myset |

| score |

| students |

| teacher |

| temp |

| temp1 |

+------------------+

22 rows in set (0.01sec)

mysql>drop table temp1;

Query OK,0 rows affected (0.12sec)

mysql> create table temp1(id int,name varchar(10)) character setlatin1;

Query OK,0 rows affected (0.07sec)mysql> setnames latin1;

Query OK,0 rows affected (0.00sec)

mysql> insert into temp1 values(1,'abbd');

Query OK,1 row affected (0.07sec)

mysql> select * fromtemp1;+------+------+

| id | name |

+------+------+

| 1 | abbd |

+------+------+

1 row in set (0.00sec)

mysql> insert into temp1 values(1,'中国');

Query OK,1 row affected (0.05sec)

mysql> select * fromtemp1;+------+--------+

| id | name |

+------+--------+

| 1 | abbd |

| 1 | 中国 |

+------+--------+

2 rows in set (0.01sec)

mysql>commit;

Query OK,0 rows affected (0.00sec)

mysql> select hex(convert('中国' usingutf8));+-----------------------------------+

| hex(convert('中国' using utf8)) |

+-----------------------------------+

| C3A4C2B8C2ADC3A5E280BAC2BD |

+-----------------------------------+

1 row in set, 1 warning (0.00sec)

mysql> select hex(convert('你' usingutf8));+--------------------------------+

| hex(convert('你' using utf8)) |

+--------------------------------+

| C3A4C2BDC2A0 |

+--------------------------------+

1 row in set, 1 warning (0.00sec)

mysql> select hex(convert('你' usinggbk));+-------------------------------+

| hex(convert('你' using gbk)) |

+-------------------------------+

| 3F3F3F |

+-------------------------------+

1 row in set (0.00sec)

mysql> select hex(convert('你' usinggb2312));+----------------------------------+

| hex(convert('你' using gb2312)) |

+----------------------------------+

| 3F3F3F |

+----------------------------------+

1 row in set (0.00sec)

mysql> select convert(0xC3A4C2BDC2A0 usinggbk);+-----------------------------------+

| convert(0xC3A4C2BDC2A0 using gbk) |

+-----------------------------------+

| ??? |

+-----------------------------------+

1 row in set (0.00sec)

mysql> select convert(0xC3A4C2BDC2A0 usinggb2312);+--------------------------------------+

| convert(0xC3A4C2BDC2A0 using gb2312) |

+--------------------------------------+

| NULL |

+--------------------------------------+

1 row in set, 1 warning (0.01sec)

mysql> select convert(0xC3A4C2BDC2A0 usingutf8);+------------------------------------+

| convert(0xC3A4C2BDC2A0 using utf8) |

+------------------------------------+

| 你 |

+------------------------------------+

1 row in set, 1 warning (0.00sec)