Preliminary MySQL Study Notes

 

Some useful functions...

mysql> select version(), database(), user(), curdate(), now();
+-----------+------------+----------------+------------+---------------------+
| version() | database() | user()         | curdate()  | now()               |
+-----------+------------+----------------+------------+---------------------+
| 5.5.10    | test       | ODBC@localhost | 2012-03-07 | 2012-03-07 22:09:04 |
+-----------+------------+----------------+------------+---------------------+
1 row in set (0.00 sec)

mysql> select current_date;
+--------------+
| current_date |
+--------------+
| 2012-03-07   |
+--------------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pet            |
| shop           |
+----------------+
2 rows in set (0.05 sec)

mysql>

 

show table schema and ddl (desc, show create table) ...

mysql> desc pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.02 sec)

mysql> show create table pet \G
*************************** 1. row ***************************
Table: pet
Create Table: CREATE TABLE `pet` (
  `name` varchar(20) DEFAULT NULL,
  `owner` varchar(20) DEFAULT NULL,
  `species` varchar(20) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `death` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

 

Convinient INSERT statement...

mysql> create table shop
-> ( article int(4) unsigned zerofill default '0000' not null,
-> dealer char(20) default '' not null,
-> price double(16, 2) default '0.00' not null,
-> primary key(article, dealer));
Query OK, 0 rows affected (0.17 sec)

mysql> select * from shop;
Empty set (0.00 sec)

mysql> insert into shop values
-> (1, 'A', 3.45),
-> (1, 'B', 3.99),
-> (2, 'A', 10.99),
-> (3, 'B', 1.45),
-> (3, 'C', 1.69),
-> (3, 'D', 1.25),
-> (4, 'D', 19.95);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+
7 rows in set (0.00 sec)

load data into a table -- "LOAD DATA LOCAL INFILE xxx INTO table xxx"

mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';

(On an Apple machine running OS X, you would likely want to use LINES TERMINATED BY '\r'.)

 

 AUTO_INCREMENT (similar to Oracle Sequence)....

mysql> create table animals
-> ( id mediumint not null AUTO_INCREMENT,
->   name char(30) not null,
->   primary key(id)
-> ) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into animals(name) values
-> ('dog'), ('cat'), ('penguin');
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
+----+---------+
3 rows in set (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.03 sec)

 

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

BIT_COUNT(), BIT_OR()...

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1
GROUP BY year,month;

 

mysqld - (mysql daemon -- The MySQL Server)

When MySQL server starts, it listens for network connections from client programs and manages access to database on behalf of those clients.

 

 MySQL Storage Engine

 

InnoDB Strorage Engine

  - Overview of InnoDB Tablespace and Log Files

  Two important disk-based resources managed by the InnoDB storage engine are its tablespace data files and its log files. If you specify no InnoDB configuration options, MySQL creates an auto-extending 10MB datafile named "ibdata1" and two 5MB log files named "ib_logfile0" and "ib_logfile1" in the MySQL data directory.

 

Enabling and Disabling Multiple Tablespaces

To enable multiple tablepspaces, start the server with the "--innodb_file_per_table" option.

[mysqld]
innodb_file_per_table

 

Or, can set the variable globally and move the table from the system tablespace to its own tablespace or vice versa.

-- Move table from system tablespace to its own tablespace.
SET GLOBAL innodb_file_per_table=1;
ALTER TABLE table_name ENGINE=InnoDB;
-- Move table from its own tablespace to system tablespace.
SET GLOBAL innodb_file_per_table=0;
ALTER TABLE table_name ENGINE=InnoDB;

InnoDB always needs the shared tablepace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate.

When a table is moved out of the system tablespace into its own .ibd file, the data files that make up the system tablespace remain the same size. The space formerly occupied by the table can be reused for new InnoDB data, but is not reclaimed for use by the OS. 

 

Query Transaction/lock in innodb

There are 3 tables in INFORMATION_SCHEMA database -- INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS

There is one column in the table INNODB_TRX -- trx_weight, which reflects the number of rows locked in the transaction. If deadlock happens, the transaction with lower trx_weight will be rollbacked by innodb engine.

 

 

 

 

 

转载于:https://www.cnblogs.com/fangwenyu/archive/2012/07/08/2382677.html