分类： Java
2008-03-10 11:13:22
配置MySQL数据库
1,把root用户口令设置为”123”, SQL命令如下:
Update user set password=password(‘123’) where user=’root’;
Flush privileges;
2,退出mysql客户程序, SQL命令如下:
Exit
3,再以root用户身份连接mysql客户程序, SQL命令如下:
Mysql –u root –p
提示输入密码时,输入123
4,进入mysql数据库,新建” pl”用户,密码为” 123”,SQL命令如下:
Use mysql;
Grant all privileges on *.* to pl@localhost identified by ‘123’
With grant option;
5,创建STOREDB数据库,CUSTOMERS表和ORDERS表,并且插入相关数据,此外还创建支持数据库事务的ACCOUNTS表
<1>如果存在同名数据库,则删除:
Drop database if exists STOREDB;
<2>创建STOREDB数据库:
Create database STOREDB;
<3>使用STOREDB数据库
Use STOREDB;
<4>创建CUSTOMERS表, ID作为主键,
Create table CUSTOMERS(
ID bigint not null auto_increment primary key,
NAME varchar(16) not null,
AGE int,
ADDRESS varchar(255)
);
创建后,显示CUSTOMERS表描述:
mysql> desc CUSTOMERS;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| ID | bigint(20) | NO | PRI | NULL | auto_increment |
| NAME | varchar(16) | NO | | NULL | |
| AGE | int(11) | YES | | NULL | |
| ADDRESS | varchar(255) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
<5>创建ORDERS表, ID作为主键,CUSTOMER_ID作为CUSTOMERS表主键ID的外键
Create table ORDERS(
ID bigint not null auto_increment primary key,
ORDER_NUMBER varchar(16) not null,
PRICE float,
CUSTOMER_ID bigint,
Foreign key(CUSTOMER_ID) references CUSTOMERS(ID)
);
创建后,显示ORDERS表描述:
mysql> desc ORDERS;
+--------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+----------------+
| ID | bigint(20) | NO | PRI | NULL | auto_increment |
| ORDER_NUMBER | varchar(16) | NO | | NULL | |
| PRICE | float | YES | | NULL | |
| CUSTOMER_ID | bigint(20) | YES | MUL | NULL | |
+--------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
<6>创建ACCOUNTS表,ID作为主键
Create table ACCOUNTS(
ID bigint not null,
NAME varchar(15),
BALANCE decimal(10,2),
Primary key(ID)
)type=innodb;
创建后,显示ACCOUNTS表描述:
mysql> desc ACCOUNTS;
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| ID | bigint(20) | NO | PRI | NULL | |
| NAME | varchar(15) | YES | | NULL | |
| BALANCE | decimal(10,2) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
<7>向CUSTOMERS表,ORDERS表插入相关数据
Insert into CUSTOMERS(ID,NAME,AGE,ADDRESS)
value(1,’小张’,23,’北京’);
insert into CUSTOMERS(ID,NAME,AGE,ADDRESS)
value(2,’小红’,29,’天津’);
insert into CUSTOMERS(ID,NAME,AGE,ADDRESS)
value(3,’小白’,33,’香港’);
insert into CUSTOMERS(ID,NAME,AGE,ADDRESS)
value(4,’小彭’,46,’深圳’);
插入数据后,显示CUSTOMERS表内容:
mysql> select * from CUSTOMERS;
+----+------+------+---------+
| ID | NAME | AGE | ADDRESS |
+----+------+------+---------+
| 1 | 小张 | 23 | 北京 |
| 2 | 小红 | 29 | 天津 |
| 3 | 小白 | 33 | 香港 |
| 4 | 小彭 | 28 | 深圳 |
+----+------+------+---------+
4 rows in set (0.00 sec)
insert table ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID)
value(1,’小张_001’,100.12,1);
insert table ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID)
value(2,’小张_002’,233.56,1);
insert table ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID)
value(3,’小红_001’,87.64,2);
插入数据后,显示ORDERS表内容:
mysql> select * from ORDERS;
+----+--------------+--------+-------------+
| ID | ORDER_NUMBER | PRICE | CUSTOMER_ID |
+----+--------------+--------+-------------+
| 1 | 小张_001 | 100.12 | 1 |
| 2 | 小张_002 | 233.56 | 1 |
| 3 | 小红_001 | 87.64 | 2 |
+----+--------------+--------+-------------+
3 rows in set (0.00 sec)
