创建表
-
语法格式
create table 表名 ( 字段1 数据类型, 字段2 数据类型, 字段3 数据类型, ... );
-
MySQL中字段的常见数据类型
- int 整数型
- bigint 长整型
- float 浮点型
- char 定长字符串
- varchar 可变长字符串
- data 日期类型
- BLOB 二进制大对象(存储图片、视频等流媒体信息)
- CLOB 字符大对象(存储较大文本,可以存储4个G的字符串)
-
char和varchar如何选择
在实际开发中 当某个字段的实际长度为定长 例如性别 生日等都是char
当一个字段的实际长度不确定是 例如简介 姓名等都是varchar
-
建表
create table t_student( no bigint, name varchar(255), sex char(1), classno varchar(255), birth char(10) ); desc t_student; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | no | bigint(20) | YES | | NULL | | | name | varchar(255) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | classno | varchar(255) | YES | | NULL | | | birth | char(10) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+
-
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'); select * from t_student; +------+----------+------+------------+------------+ | no | name | sex | classno | birth | +------+----------+------+------------+------------+ | 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 | +------+----------+------+------------+------------+ insert into t_student(name, sex, classno, birth, no) values('zhangsan', '1', 'gaosan1ban', '1950-10-12', 2); select * from t_student; +------+----------+------+------------+------------+ | no | name | sex | classno | birth | +------+----------+------+------------+------------+ | 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 | | 2 | zhangsan | 1 | gaosan1ban | 1950-10-12 | +------+----------+------+------------+------------+ //除name字段外 别的字段都为NULL insert into t_student(name) values('wangwu'); select * from t_student; +------+----------+------+------------+------------+ | no | name | sex | classno | birth | +------+----------+------+------------+------------+ | 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 | | 2 | zhangsan | 1 | gaosan1ban | 1950-10-12 | | NULL | wangwu | NULL | NULL | NULL | +------+----------+------+------------+------------+
-
设默认值
create table t_student( no bigint, name varchar(255), sex char(1) default 1, classno varchar(255), birth char(10) ); desc t_student; +---------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+--------------+------+-----+---------+-------+ | no | bigint(20) | YES | | NULL | | | name | varchar(255) | YES | | NULL | | | sex | char(1) | YES | | 1 | | | classno | varchar(255) | YES | | NULL | | | birth | char(10) | YES | | NULL | | +---------+--------------+------+-----+---------+-------+
-
//如果前面的字段省略不写 后面values的顺序必须准确 而且必须全写 不能省略 insert into t_student values(1, 'jack', '1', 'gaosan2ban', '1986-10-23'); select * from t_student; +------+------+------+------------+------------+ | no | name | sex | classno | birth | +------+------+------+------------+------------+ | 1 | jack | 1 | gaosan2ban | 1986-10-23 | +------+------+------+------------+------------+
-
一次插入多行数据
insert into t_student(no, name, sex, classno, birth) values(2, 'ropq', '0', 'gaosan2ban', '1986-11-23'), (3, 'wdq', '1', 'gaosan2ban', '1986-10-12'); select * from t_student; +------+------+------+------------+------------+ | no | name | sex | classno | birth | +------+------+------+------------+------------+ | 1 | jack | 1 | gaosan2ban | 1986-10-23 | | 2 | ropq | 0 | gaosan2ban | 1986-11-23 | | 3 | wdq | 1 | gaosan2ban | 1986-10-12 | +------+------+------+------------+------------+
表的复制
-
语法
//将查询结果当作表创建出来 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 | +--------+------------+----------+
-
修改表的数据 update
//语法格式 update 表名 set 字段名1 = 值1, 字段名2 = 值2... where 条件; //没有条件整张表全部更新
-
将部门10的LOCAL该为上海 将部门名称改为RENSHIBU
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 | +--------+------------+----------+ update dept1 set loc = 'shanghai', dname = 'renshibu' where deptno = 10; 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 | +--------+------------+----------+
-
-
删除数据
//语法格式 delete from 表名 where 条件; ///没有条件全部删除
delete from dept1 where deptno = 10; select * from dept1; +--------+------------+---------+ | DEPTNO | DNAME | LOC | +--------+------------+---------+ | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+---------+
//删除所有 mysql> delete from dept1; mysql> select * from dept1; Empty set (0.00 sec)
-
删除大表
//表被截断 不可回滚 永久丢失 truncate table 表名;
-
对于表结构的修改 使用工具完成即可 修改表结构的语句不会出现在Java代码中 出现在Java代码中的sql语句包括 insert delete updata select(都是对表中数据进行操作)
-
增删改查的术语 CRUD
Create(增) Retrieve(检索) Updata(修改) Delete(删除)