MySQL03_22.7.28

最新推荐文章于 2024-07-24 16:18:52 发布

cdmmc3

最新推荐文章于 2024-07-24 16:18:52 发布

阅读量158

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/cicadaaas/article/details/126032029

版权

MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

1、表的创建 DDL语句

1、建表的语法格式：

create table 表明(

字段名1 数据类型，

字段名2 数据类型，

字段名3 数据类型);

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

2、关于mysql中的数据类型

varchar（最长255）可变长度的字符串根据实际的数据动态分配空间

优点：节省空间缺点：需要动态分配空间速度慢

char （最长255）定长字符串不管实际数据长度是多少分配固定的长度空间去存储数据

优点：不需要动态分配空间速度快缺点：使用不当会导致空间的浪费

1、因此灵活根据实际存储的数据选择数据类型 (比如说性别选择char 姓名选择varchar)

int（最长11）整数型

bigint 长整型

    float     单精度浮点型数据

double 双精度浮点型数据

date 短日期类型

    datetime  长日期类型

clob 字符大对象可存储4G的字符串超过255个字符的都要采用 clob

blob 二进制对象存储图片声音视频等流媒体数据

3、insert语句

1、insert语句执行成功后必然会多一条语句

mysql> insert into t_tt(no,name,sex,age,email) values(1,'zhangsan','m',23,'zhansgan@123.com');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_tt;
+------+----------+------+------+------------------+
| no   | name     | sex  | age  | email            |
+------+----------+------+------+------------------+
|    1 | zhangsan | m    |   23 | zhansgan@123.com |
+------+----------+------+------+------------------+
1 row in set (0.00 sec)

        2、insert插入日期

mysql> insert into t_oo(id,name,birth) values(2,'daming','2020-10-03');
Query OK, 1 row affected (0.01 sec)

        3、str_to_date : 将字符串转换成date

mysql> insert into t_oo(id,name,birth) values(1,'zhangsan',str_to_date('01-10-2010','%d-%m-%Y'));
Query OK, 1 row affected (0.07 sec)

mysql> select * from t_oo;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 2010-10-01 |
+------+----------+------------+
1 row in set (0.00 sec)

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

%Y 年

%m 月

%d 日

%h 时

%i 分

%s 秒

4、date_format 将日期转换成字符串

mysql> select id,name,date_format(birth,'%m/%d/%Y') as birth from t_oo;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 10/01/2010 |
|    2 | daming   | 10/03/2020 |
+------+----------+------------+
2 rows in set (0.01 sec)

5、insert 插入多条语句

mysql> insert into t_oo(id,name,birth) values
    -> (5,'ww','1999-10-10'),
    -> (6,'ww','1999-10-10');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_oo;
+------+--------+------------+
| id   | name   | birth      |
+------+--------+------------+
|    2 | daming | 2020-10-03 |
|    5 | ww     | 1999-10-10 |
|    6 | ww     | 1999-10-10 |
+------+--------+------------+
3 rows in set (0.00 sec)

6、datetime 长日期

mysql> insert into t_ii(id,name,birth,create_time)
    -> values(1,'dada','1990-10-10','2020-10-10 10:10:10');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_ii;
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | dada | 1990-10-10 | 2020-10-10 10:10:10 |
+------+------+------------+---------------------+
1 row in set (0.00 sec)

7、now()获取当前系统日期

mysql> insert into t_ii(id,name,birth,create_time)
    -> values(1,'dada','1990-10-10',now());
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_ii;
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | dada | 1990-10-10 | 2020-10-10 10:10:10 |
|    1 | dada | 1990-10-10 | 2022-07-28 09:50:16 |
+------+------+------------+---------------------+
2 rows in set (0.00 sec)

4、drop 删除表

mysql> drop table t_ss;
Query OK, 0 rows affected (0.03 sec)

mysql> drop table if exists t_pp;
Query OK, 0 rows affected (0.02 sec)

5、创建表的同时制定默认值

mysql> create table t_yy(
    -> no int,
    -> name varchar(32),
    -> sex char(1) default 'm',
    -> age int(3)
    -> );
Query OK, 0 rows affected, 1 warning (0.03 sec)

6、update

mysql> update t_ii set name ='jack',birth = '2000-10-10' where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_ii;
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | dada | 1990-10-10 | 2020-10-10 10:10:10 |
|    1 | dada | 1990-10-10 | 2022-07-28 09:50:16 |
|    2 | jack | 2000-10-10 | 2022-07-28 10:01:42 |
+------+------+------------+---------------------+
3 rows in set (0.00 sec)

7、delete (DML) 删除表中数据

delete from 表名 where 条件

mysql> delete from t_oo where id =1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_oo;
+------+--------+------------+
| id   | name   | birth      |
+------+--------+------------+
|    2 | daming | 2020-10-03 |
+------+--------+------------+
1 row in set (0.00 sec)

delete 语句删除数据原理

1、表中数据被删除了但数据在硬盘上的真实存储空间不会被释放

优点支持回滚缺点效率低

mysql> start transaction;
Query OK, 0 rows affected (0.07 sec)

mysql> delete from t_oo;
Query OK, 3 rows affected (0.00 sec)

mysql> select * from t_oo;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_oo;
+------+--------+------------+
| id   | name   | birth      |
+------+--------+------------+
|    2 | daming | 2020-10-03 |
|    5 | ww     | 1999-10-10 |
|    6 | ww     | 1999-10-10 |
+------+--------+------------+
3 rows in set (0.00 sec)

8、truncate 语句删除数据原理 (DDL) 删除表中数据

效率高表被一次截断物理删除

优点快速缺点不支持回滚

mysql> truncate table t_oo;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from t_oo;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t_oo;
Empty set (0.00 sec)

9、快速创建表（复制）

1、将查询结果当作一张新表

mysql> create table t_uu as select * from t_oo;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_uu;
+------+--------+------------+
| id   | name   | birth      |
+------+--------+------------+
|    2 | daming | 2020-10-03 |
|    5 | ww     | 1999-10-10 |
|    6 | ww     | 1999-10-10 |
+------+--------+------------+
3 rows in set (0.00 sec)