目录
1、创建表
1.1、建表语句的语法格式:
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
....
);
创建表的时候,表中有字段,每一个字段有:
- * 字段名
- * 字段数据类型
- * 字段长度限制
- * 字段约束
1.2、MySql常用数据类型
类型 | 描述 |
Char(长度) | 定长字符串,存储空间大小固定,适合作为主键或外键 |
Varchar(长度) | 变长字符串,存储空间等于实际数据空间 |
double(有效数字位数,小数位) | 数值型 |
Float(有效数字位数,小数位) | 数值型 |
Int( 长度) | 整型 |
bigint(长度) | 长整型 |
Date | 日期型 年月日 |
DateTime | 日期型 年月日 时分秒 毫秒 |
time | 日期型 时分秒 |
BLOB | Binary Large OBject(二进制大对象) |
CLOB | Character Large OBject(字符大对象) |
其它………………… |
|
2、增加/删除/修改表结构
采用alter table来增加/删除/修改表结构,不影响表中的数据
- 2.1、添加字段
alter table t_student add tel varchar(40);
desc t_student;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| no | bigint | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| classno | varchar(255) | YES | | NULL | |
| birth | char(10) | YES | | NULL | |
| tel | varchar(40) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
- 2.2、修改字段
alter table t_student modify name varchar(100) ;
desc t_student;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| no | bigint | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| classno | varchar(255) | YES | | NULL | |
| birth | char(10) | YES | | NULL | |
| tel | varchar(40) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
- 2.3、删除字段
alter table t_student drop tel;
desc t_student;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| no | bigint | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| classno | varchar(255) | YES | | NULL | |
| birth | char(10) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
3、表数据的增、删、改
- 3.1、insert语句插入数据
语法格式:
insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)
要求:字段的数量和值的数量相同,并且一一对应
#插入单条记录
insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban', '1950-10-12');
insert into t_student(name,sex,classno,birth,no) values('lisi','1','gaosan1ban', '1950-10-12',2);
insert into t_student(name) values('wangwu');
select * from t_student;
+------+----------+------+----------+------------+------------+
| no | name | sex | birthday | classno | birth |
+------+----------+------+----------+------------+------------+
| 1 | zhangsan | 1 | NULL | gaosan1ban | 1950-10-12 |
| 2 | lisi | 1 | NULL | gaosan1ban | 1950-10-12 |
| NULL | wangwu | NULL | NULL | NULL | NULL |
+------+----------+------+----------+------------+------------+
#插入多条记录
insert into t_student
(no,name,sex,classno,birth)
values
(3,'rose','1','gaosi2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1955-12-14');
+------+--------+------+----------+-----------+------------+
| no | name | sex | birthday | classno | birth |
+------+--------+------+----------+-----------+------------+
| 3 | rose | 1 | NULL | gaosi2ban | 1952-12-14 |
| 4 | laotie | 1 | NULL | gaosi2ban | 1955-12-14 |
+------+--------+------+----------+-----------+------------+
- 3.2、修改数据:update
语法格式:
update 表名 set 字段名1 = '值1', 字段名2 = '值2'...;(后面可加where条件)
update dept1 set loc='beijing' where deptno=10;
#where后面加限制条件,如果不写,将更改这个字段下的所有行
mysql> select * from dept1;
+--------+------------+---------+
| DEPTNO | DNAME | LOC |
+--------+------------+---------+
| 10 | ACCOUNTING | beijing |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | ACCOUNTING | beijing |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+---------+
update dept1 set loc='beijing'
+--------+------------+---------+
| DEPTNO | DNAME | LOC |
+--------+------------+---------+
| 10 | ACCOUNTING | beijing |
| 20 | RESEARCH | beijing |
| 30 | SALES | beijing |
| 40 | OPERATIONS | beijing |
| 10 | ACCOUNTING | beijing |
| 20 | RESEARCH | beijing |
| 30 | SALES | beijing |
| 40 | OPERATIONS | beijing |
+--------+------------+---------+
- 3.3、删除数据
语法格式:
delete from 表名 where 条件; //注意:没有条件全部删除。
delete from dept1
select * from dept1;
Empty set (0.00 sec)
4、表的复制
语法:
create table 表名 as select语句; //将查询结果当做表创建出来。
create table dept1 as select * from dept;
select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
练习:将查询结果插入到一张表中?
insert into dept1 select * from dept;
select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
5、删除表
语法:
drop table 表名; // 这个通用。
drop table if exists 表名; // mysql可以这样写,但oracle不支持这种写法。