分类： Mysql/postgreSQL
2008-03-25 13:21:15
DDL(数据定义语言)语句
1,创建数据库 create database 数据库名;
|
mysql> create database javaworld; Query OK, 1 row affected |
2,查看已存在数据库 show databases;
|
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | javaworld | | mysql | | test | +--------------------+ 4 rows in set |
3,选择要操作的数据库 use 数据库名;
|
mysql> use javaworld; Database changed |
4,查看javaworld数据库中所有的表
show tables;
|
mysql> show tables; Empty set |
5,删除数据库 drop database 数据库名;
|
mysql> drop database javaworld; Query OK, 0 rows affected mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set |
6,创建表 create table 表名(字段名(字段类型) 约束);
|
mysql> create table emp(ename varchar(10), hiredate date, sal decimal(10,2), deptno int(2)); Query OK, 0 rows affected |
7,查看表的描述 desc 表名;
|
mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set |
8,查看创建表的SQL语句(引擎和字符集也可看到)
show create table 表名;
|
mysql> show create table emp; +-------+----------------------------------------------------------------- | Table | Create Table +-------+----------------------------------------------------------------- | emp | CREATE TABLE `emp` ( `ename` varchar(10) default NULL, `hiredate` date default NULL, `sal` decimal(10,2) default NULL, `deptno` int(2) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 | +-------+----------------------------------------------------------------- 1 row in set |
9,删除表 drop table 表名;
|
mysql> drop table emp; Query OK, 0 rows affected |
10,修改表
<1>修改表类型
如:修改emp表的ename字段定义,将varchar(10)改为varchar(20)
|
mysql> alter table emp modify ename varchar(20); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set |
<2>增加表字段
如:表emp上新增字段age,类型为int(3)
|
mysql> alter table emp add column age int(3); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | | age | int(3) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set |
<3>删除表字段
如:将字段age删除
|
mysql> alter table emp drop column age; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(20) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set |
<4>字段改名
如:将ename改名为name,同时修改字段类型为varchar(10)
|
mysql> alter table emp change ename name varchar(10); Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | name | varchar(10) | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 4 rows in set |
<5>修改字段排列顺序
如:将birth加在ename后
|
mysql> alter table emp add birth date after ename; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | ename | varchar(10) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 5 rows in set |
如:修改字段age,将它放在最前面
|
mysql> alter table emp modify age int(3) first; Query OK, 0 rows affected Records: 0 Duplicates: 0 Warnings: 0 mysql> desc emp; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | age | int(3) | YES | | NULL | | | ename | varchar(10) | YES | | NULL | | | birth | date | YES | | NULL | | | hiredate | date | YES | | NULL | | | sal | decimal(10,2) | YES | | NULL | | | deptno | int(2) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ 6 rows in set |
<6>更加表名
|
mysql> alter table emp rename emp1; Query OK, 0 rows affected mysql> desc emp; ERROR 1146 : Table 'javaworld.emp' doesn't exist mysql> show tables; +---------------------+ | Tables_in_javaworld | +---------------------+ | emp1 | +---------------------+ 1 row in set |
DML(数据操纵语言)语句
1,插入记录
|
insert into emp(ename,hiredate,sal,deptno) values('zzzx1','2000-01-01','2000',1); Query OK, 1 row affected |
也可以不指定字段名,但values中值的顺序需和字段名排列顺序一致
|
mysql> insert into emp values('lisa','2003-02-01','3000',2); Query OK, 1 row affected |
或者只对部分字段插入值
|
insert into emp(ename,sal) values('dony','1000'); Query OK, 1 row affected |
查看表
|
mysql> select * from emp; +-------+------------+---------+--------+ | ename | hiredate | sal | deptno | +-------+------------+---------+--------+ | zzzx1 | 2000-01-01 | 2000.00 | 1 | | lisa | 2003-02-01 | 3000.00 | 2 | | dony | NULL | 1000.00 | NULL | +-------+------------+---------+--------+ 3 rows in set |
也可一次插入多条记录,每条记录之间用逗号分隔
|
mysql> insert into dept values(5,'dept5'),(6,'dept6'); Query OK, 2 rows affected Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 5 | dept5 | | 6 | dept6 | +--------+----------+ 2 rows in set |
2,更新记录
将emp表ename为”lisa”的sal值从3000改为4000
|
mysql> update emp set sal=4000 where ename='lisa'; Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0 |
也可以同时更新多个表中的数据,
如:同时更新emp表的字段sal和dept表的字段deptname
|
mysql> select * from emp; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 100.00 | 1 | | lisa | 2003-02-01 | 200.00 | 2 | | bjguan | 2004-04-02 | 100.00 | 1 | | dony | 2005-02-05 | 2000.00 | 4 | +--------+------------+---------+--------+ 4 rows in set mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | tech | | 2 | sale | | 5 | fin | +--------+----------+ 3 rows in set mysql> update emp a, dept b set a.sal=a.sal*b.deptno,b.deptname=a.ename where a.deptno=b.deptno; Query OK, 3 rows affected Rows matched: 5 Changed: 3 Warnings: 0 mysql> select * from emp; +--------+------------+---------+--------+ | ename | hiredate | sal | deptno | +--------+------------+---------+--------+ | zzx | 2000-01-01 | 100.00 | 1 | | lisa | 2003-02-01 | 400.00 | 2 | | bjguan | 2004-04-02 | 100.00 | 1 | | dony | 2005-02-05 | 2000.00 | 4 | +--------+------------+---------+--------+ 4 rows in set mysql> select * from dept; +--------+----------+ | deptno | deptname | +--------+----------+ | 1 | zzx | | 2 | lisa | | 5 | fin | +--------+----------+ 3 rows in set |
