- 论坛徽章:
- 0
|
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,修改表
修改表类型
如:修改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
增加表字段
如:表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
删除表字段
如:将字段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
字段改名
如:将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
修改字段排列顺序
如:将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
更加表名
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
本文来自ChinaUnix博客,如果查看原文请点:http://blog.chinaunix.net/u2/63095/showart_507553.html |
|