mysql.5.5.21_MySQL5.5.21学习教程之一

最近做项目需要MySQL，顺便捡起好久没弄的数据库了！可能要写一个系列关于MYSQL的学习笔记，那不来与大家共享！

说到安装数据库，可真的够让人着急的！自己的系统是Windows8，安装了一圈server却没有找到最好的，功夫不负有心人。找到了mysql-5.5.21-winx64.msi。终于成功了！

show databases;

show engines;

show engines \G

show variables like 'have%';

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

| Variable_name | Value |

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

| have_compress | YES |

| have_crypt | NO |

| have_csv | YES |

| have_dynamic_loading | YES |

| have_geometry | YES |

| have_innodb | YES |

| have_ndbcluster | NO |

| have_openssl | DISABLED |

| have_partitioning | YES |

| have_profiling | YES |

| have_query_cache | YES |

| have_rtree_keys | YES |

| have_ssl | DISABLED |

| have_symlink | YES |

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

show variables like 'storgae_engine%';

[mysqld]

# The TCP/IP Port the MySQL Server will listen on

port=3306

#Path to installation directory. All paths are usually resolved relative to this.

basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

#Path to the database root

datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

# The default character set that will be used when a new schema or table is

# created and no character set is defined

character-set-server=utf8

# The default storage engine that will be used when create new tables when

default-storage-engine=INNODB

help contents;

help Data Types;

e.g.

help TIME;

Name: 'TIME'

Description:

TIME

A time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME

values in 'HH:MM:SS' format, but permits assignment of values to TIME

columns using either strings or numbers.

URL: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-type-overview.html

use test;

create TABLE f_test(a FLOAT(38,30),b DECIMAL(38,30));

INSERT INTO f_test VALUES (12443.43534534543,343243.2343546534636401);

select * from f_test \G

*************************** 1. row ***************************

a: 12443.435546875000000000000000000000

b: 343243.234354653463640100000000000000

1 row in set (0.00 sec)

create TABLE int_test(num INTEGER);

INSERT INTo int_test VALUES (0),(-1),(-1.1),(1.1),(1009888);

select * from int_test \G

*************************** 1. row ***************************

num: 0

*************************** 2. row ***************************

num: -1

*************************** 3. row ***************************

num: -1

*************************** 4. row ***************************

num: 1

*************************** 5. row ***************************

num: 1009888

5 rows in set (0.00 sec)

create TABLE bit_test(id BIT(8));

#添加的第一个数字为十进制的数字，第二个数字为二进制的数据

insert into bit_test values (11),(b'11');

select id+0 from bit_test \G

*************************** 1. row ***************************

id+0: 11

*************************** 2. row ***************************

id+0: 3

2 rows in set (0.00 sec)

select BIN(id+0) from bit_test \G

*************************** 1. row ***************************

BIN(id+0): 1011

*************************** 2. row ***************************

BIN(id+0): 11

2 rows in set (0.28 sec)

#时间日期的插入学习

create table date_test(f_date DATE,f_datetime DATETIME,f_timestamp TIMESTAMP,f_time TIME,f_year YEAR);

select CURDATE();

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

| CURDATE() |

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

| 2015-01-14 |

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

select CURDATE(),NOW(),NOW(),time(NOW()),year(NOW()) \G

*************************** 1. row ***************************

CURDATE(): 2015-01-14

NOW(): 2015-01-14 19:39:56

NOW(): 2015-01-14 19:39:56

time(NOW()): 19:39:56

year(NOW()): 2015

1 row in set (0.00 sec)

insert into date_test values (CURDATE(),NOW(),NOW(),time(NOW()),year(NOW()));

select * from date_test \G

*************************** 1. row ***************************

f_date: 2015-01-14

f_datetime: 2015-01-14 19:41:07

f_timestamp: 2015-01-14 19:41:07

f_time: 19:41:07

f_year: 2015

1 row in set (0.00 sec)

create TABLE user(id INTEGER,name VARCHAR(20));

insert into user values (1,'bob'),(2,'petter');

select * from user;

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

| id | name |

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

| 1 | bob |

| 2 | petter |

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

show tables;

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

| Tables_in_test |

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

| bit_test |

| date_test |

| f_test |

| int_table |

| int_test |

| user |

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