建表和增删改查

建表

1、建表的语法

建表属于DDL语句，包括create,drop,alter

create table 表名（字段名1 数据类型， 字段名2 数据类型，字段名3 数据类型，）

1.1快速创建表：

create table emp2 as select * from emp;

原理：

将一个查询结果当作一个表快速复制。

mysql> show tables;
+-----------------------+
| Tables_in_bjpowernode |
+-----------------------+
| dept                  |
| emp                   |
| emp2                  |
| salgrade              |
| t_student             |
| t_user                |
+-----------------------+

1.2 将查询结果插入到一张表中

create table dept_back as select * from dept;
insert into dept_back select * from dept;

mysql> select * from dept_back;
+--------+------------+----------+
| 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.00 sec)

2、数据类型

varchar：可变长度字符串，速度慢，节省空间

char：定长字符串，不管实际的数据长度是多少，分配固定长度的长度去存储数据，速度快，浪费空间

int（11）：数字中的整数型

bigint：数字中的长整型

float：单精度浮点型数据

double：双精度浮点型数据

date：短日期类型

datetime：长日期类型

clob：字符大对象，最多可以存储4G字符串，超过255字符

blob：二进制大对象 ，专门用来存图片，声音，视频等流媒体数据。往BLOB类型的字段插入数据的时候，需要使用IO流

3、 表的操作

3.1 创建表：

create table t_student(
	no int,
	name varchar(32),
	sex char(1),
	age int(3),
	email varchar(255)
);

mysql> create table t_student(
    -> no int,
    -> name varchar(32),
    -> sex char(1),
    -> age int(3),
    -> email varchar(255)
    -> );
Query OK, 0 rows affected (0.01 sec)

3.2 删除表：

drop table if exists t_student;

3.3 插入数据：

注：字段名和值要一一对应，数量要对应，数据类型要对应。

insert语句凡是执行成功了，那么必然会多一条记录。没有给其他字段指定值的话，默认值是null

mysql> insert into t_student (no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@163.com')
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> select * from  t_student;
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   20 | zhangsan@163.com |
+------+----------+------+------+------------------+
1 row in set (0.00 sec)

插入多条记录：

insert into t_student (no,name,sex,age,email) 
> create table t_student(
id int,
name varchar(32),
birth date,
create_time datetime);

insert into t_student(id,name,birth,create_time) values
(1,'zs','1990-01-01',now()),
(2,'ls','1990-01-01',now()),
(3,'ww','1990-01-02',now());

mysql> select * from t_student;
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | zs   | 1990-01-01 | 2021-09-18 12:15:30 |
|    2 | ls   | 1990-01-01 | 2021-09-18 12:15:30 |
|    3 | ww   | 1990-01-02 | 2021-09-18 12:15:30 |
+------+------+------------+---------------------+
3 rows in set (0.00 sec)

3.3.1 指定默认值

create table t_student(
	no int,
	name varchar(32),
	sex char(1) default 'm',
	age int(3),
	email varchar(255)
);

mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| sex   | char(1)      | YES  |     | m       |       |
| age   | int(3)       | YES  |     | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

insert into t_student(no) values(1);
+------+------+------+------+-------+
| no   | name | sex  | age  | email |
+------+------+------+------+-------+
|    1 | NULL | m    | NULL | NULL  |
+------+------+------+------+-------+

前面的字段名省略的话，等于都写上了！所以值也要都写上

mysql> insert into t_student values(2,'lisi','f',13,'123456');
mysql> select * from t_student;
+------+------+------+------+--------+
| no   | name | sex  | age  | email  |
+------+------+------+------+--------+
|    1 | NULL | m    | NULL | NULL   |
|    2 | lisi | f    |   13 | 123456 |
+------+------+------+------+--------+

3.3.2 插入日期

str_to_date：将字符串varchar类型转换成date类型

date_format：将date类型转换成具有一定格式的varchar字符串类型

 drop table if exists t_user;
 create table t_user(
 id int,
 name varchar(32),
 birth date,
 );
 mysql> desc t_user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

注意：数据库中所有的标识符全部都是小写，单词和单词之间使用下划线进行衔接。

3.3.2.1 str_to_date

插入数据：

str_to_date：将字符串varchar类型转换成date类型，可以把varchar转换成date类型数据，通常使用在插入insert方面

如果提供的日期字符串是’1990-01-01’，不需要此函数。

语法格式：

str_to_date（‘字符串日期’，‘日期格式’）

mysql的日期格式：

​ %Y：年

​ %m: 月

​ %d: 日

​ %h: 时

​ %i: 分

​ %s: 秒

insert into t_user(id,name,birth) values(1,'zhangsan',str_to_date('01-10-1990','%d-%m-%Y'));

mysql> select * from t_user;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 1990-10-01 |
+------+----------+------------+

3.3.2.2 date_format

date_format：通常使用在查询日期方面。设置展示的日期格式。

select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;

+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 10/01/1990 |
+------+----------+------------+

3.3.3 date和datetime

drop table if exists t_user;
 create table t_user(
 id int,
 name varchar(32),
 birth date,
 create_time datetime    
 );

mysql短日期默认格式：%Y-%m-%d

mysql长日期默认格式：%Y-%m-%d %h:%i:%s

insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-01-01','2020-03-18 15:49:50');

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-01-01 | 2020-03-18 15:49:50 |
+------+----------+------------+---------------------+

3.3.4 当前时间 now()：

用now()函数，并且获得的时间带有：时分秒信息，是datetime类型的

insert into t_user (id,name,birth,create_time) values(2,'lisi','1990-01-01',now());

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-01-01 | 2020-03-18 15:49:50 |
|    2 | lisi     | 1990-01-01 | 2021-09-18 11:01:44 |
+------+----------+------------+---------------------+

3.4 修改update

语法格式：

update 表名 set 字段名1 = 值1，字段名2 = 值2，字段名3 = 值3...where 条件
update t_user set name = 'jack',birth = '2000-10-11' where id = 2;

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-01-01 | 2020-03-18 15:49:50 |
|    2 | jack     | 2000-10-11 | 2021-09-18 11:01:44 |
+------+----------+------------+---------------------+

不加where则全部更新！

mysql> update t_user set name = 'jack',birth = '2000-10-11';
mysql> select * from t_user;
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | jack | 2000-10-11 | 2020-03-18 15:49:50 |
|    2 | jack | 2000-10-11 | 2021-09-18 11:01:44 |
+------+------+------------+---------------------+

3.5 删除数据

语法格式：

delete from 表名 where 条件
delete from t_user where id = 2;
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | jack | 2000-10-11 | 2020-03-18 15:49:50 |
+------+------+------------+---------------------+

注意：没有条件，整张表的数据会全部删除

3.5.1 快速删除数据：

delect from dept_back;

这样速度比较慢，表中的数据被删除了，但是数据在硬盘上的真是存储空间不会被释放

优点：可回滚

truncate table dept_back;

这样速度比较快，表被一次截断，物理删除；不支持回滚

优点：快速