1、创建表
建表语句的语法格式:
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
…
);
关于MySQL中字段的数据类型?常见的
数据类型 | 说明 | 对应java中类型 |
---|---|---|
int | 整数型 | int |
bigint | 长整型 | long |
float | 浮点型 | flota double |
char | 定长字符串 | string |
varchar | 可变长字符串(最多255个字符) | char string |
date | 日期类型 | java.sql.Data |
BLOB | 二进制大对象(存储图片,视频等流媒体信息) | Binary Large OBject |
CLOB | 字符大对象(存储较大文本,可以存储4g字符串) | Character Large OBject |
char和varchar的选择
char:定长,不灵活,但规整,效率高 (适合数据长度不发生改变的时候)
varchar:可变长,灵活,智能,但效率低(因为要执行判断)
表名在数据库中一般建议以:t_或者tbl _开始
/*创建学生表
学生信息 学号,姓名,性别,班级编号,生日
*/
create table t_student(
no bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10)
);
Query OK, 0 rows affected (1.72 sec)
mysql> show tables;
+----------------+
| Tables_in_csdb |
+----------------+
| dept |
| emp |
| salgrade |
| t_student |
+----------------+
4 rows in set (0.00 sec)
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| no | bigint | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| classno | varchar(255) | YES | | NULL | |
| birth | char(10) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.10 sec)
2、insert插入数据
语法格式:
insert into 表名(字段1,字段2,字段3,…) values(值1,值2,值3,…);
要求:字段的数量和值的数量相同,并且数据类型要对应相同。
insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1','1980-01-02');
mysql> select * from t_student;
+------+----------+------+---------+------------+
| no | name | sex | classno | birth |
+------+----------+------+---------+------------+
| 1 | zhangsan | 1 | gaosan1 | 1980-01-02 |
+------+----------+------+---------+------------+
1 row in set (0.00 sec)
/*当只写一个字段的时候,其他字段会自动补齐,补齐的值为建表时的默认选项*/
insert into t_student(name) values('lisi');
mysql> select * from t_student;
+------+----------+------+---------+------------+
| no | name | sex | classno | birth |
+------+----------+------+---------+------------+
| 1 | zhangsan | 1 | gaosan1 | 1980-01-02 |
| NULL | lisi | NULL | NULL | NULL |
+------+----------+------+---------+------------+
2 rows in set (0.00 sec)
insert语句只要执行成功,数据库中必定会多一行数据。
所以插入进去之后是不可以再使用insert来修改。
删除一个表
drop table if exists t_student; //当这个表存在,则删除这张表。
drop table if exists t_student;
mysql> drop table if exists t_student;
Query OK, 0 rows affected (1.30 sec)
/*当给字段在建表时添加默认值选项 default */
create table t_student(
no bigint,
name varchar(255),
sex char(1) default 1,
classno varchar(255),
birth char(10)
);
mysql> desc t_student;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| no | bigint | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
| sex | char(1) | YES | | 1 | |
| classno | varchar(255) | YES | | NULL | |
| birth | char(10) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.28 sec)
mysql> select * from t_student;
+------+----------+------+---------+------------+
| no | name | sex | classno | birth |
+------+----------+------+---------+------------+
| 1 | zhangsan | 1 | gaosan1 | 1980-01-02 |
| NULL | lisi | 1 | NULL | NULL |
+------+----------+------+---------+------------+
2 rows in set (0.00 sec)
需要注意的地方:
当一条insert语句执行成功后,表格中必然会多一行数据。
即使多的这一行记录当中某些字段是NULL,后期也没有办法在执行insert语句插入数据了,
只能使用update进行更新。
insert省略字段写法
insert into t_student values(2,‘wangwu’,‘1’,‘gaosan1’,‘1980-03-02’);
省略字段写法,必须要求后面的values与表中字段的顺序一一对应,数量也一定要是相同的;
insert一次插入多行数据
insert into t_student(no,name,sex,classno,birth) values
(3,'xiaobai','0','gaosan2','1983-01-25'),
(4,'xiaohong','0','gaosan2','1980-03-21');
mysql> select * from t_student;
+------+----------+------+---------+------------+
| no | name | sex | classno | birth |
+------+----------+------+---------+------------+
| 1 | zhangsan | 1 | gaosan1 | 1980-01-02 |
| NULL | lisi | 1 | NULL | NULL |
| 2 | wangwu | 1 | gaosan1 | 1980-03-02 |
| 3 | xiaobai | 0 | gaosan2 | 1983-01-25 |
| 4 | xiaohong | 0 | gaosan2 | 1980-03-21 |
+------+----------+------+---------+------------+
5 rows in set (0.00 sec)
3、表的复制
语法:
create table 表名 as select语句;
将查询结果当做表创建出来。
4、将查询结果插入到一张表中
语法:
insert into dept1 select * from dept;
将查询到的结果,作为值,插入到表dept1中
create table dept1 as select * from dept;/*复制dept表给dept1*/
mysql> create table dept1 as select * from dept;
Query OK, 4 rows affected (2.32 sec)
Records: 4 Duplicates: 0 Warnings: 0
insert into dept1 select * from dept; /*将查询结果做为值插入dept表中*/
+--------+------------+----------+
| 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 |
+--------+------------+----------+
8 rows in set (0.03 sec)
5、修改数据:updata
语法格式:
update 表名 set 字段1=值1,字段2=值2,… where条件;
注意:没有条件整张表数据全部更新。
/*将dept1表中的部门标号为10的loc修改为SHANGHAI ,将部门名称修改为RENSHIBU*/
update dept1 set loc='SHANGHAI',DNAME='RENSHIBU' where deptno = 10;
mysql> update dept1 set loc='SHANGHAI',DNAME='RENSHIBU' where deptno = 10;
Query OK, 2 rows affected (0.09 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from dept1;
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | RENSHIBU | SHANGHAI |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | RENSHIBU | SHANGHAI |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
8 rows in set (0.04 sec)
6、删除数据
语法格式:
delete from 表名 where 条件;
注意: 没有条件全部删除。
/*删除10部门的数据。*/
delete from dept1 where deptno = 10;
mysql> delete from dept1 where deptno = 10;
Query OK, 2 rows affected (0.16 sec)
/*删除所有记录*/
delete from dept1;
怎么删除大表(数据量庞大的表)(重点)
//无法恢复
表截断,不可回滚,永久丢失。
语句:truncate table 表名;
CRUD
create(增加)
Retrieve(查询)
update(更新)
Delete(删除)