mysql学习笔记（四）

DDL包括 create drop alter
create table 表名（字段名1 数据类型, 字段名2 数据类型,字段名3 数据类型）;
表名建议以t_或者tbl_下划线开始，可读性很强
varchar 可变长度 char 定长度 float 单精度 double 双精度
varchar会根据实际的长度动态来分配空间，需要动态分配空间，时间慢
char直接给你规定了长度，不去管实际的长度，分配固定的长度，时间慢
性别字段选用char（固定的长度） 姓名长度（varchar)
bigint 长整型 等同于java当中的long
clob可以存储4G的字符串 character
blob可以存储图片 binary 需要使用io流

表的创建

(76条消息) Navicat使用快速入门教程_苜苜的烂笔头的博客-CSDN博客_navicat使用教程详解

(76条消息) [新人向]MySQL和Navicat下载、安装及使用详细教程_Pipi酱~的博客-CSDN博客_mysql和navicat安装教程

#删除表
drop table if exits t_student;
#创建表
create table t_student(no int, name varchar(32), sex char(1),age int(3),email varchar(255));

 DML

insert into 表名（列1，列2，列3，列4） values('','','','')
mysql> insert into t_student(no,name,sex,age,email) values(1,'LiMing','男',18,'LiMing.com');
Query OK, 1 row affected (0.22 sec)

mysql> select * from t_student;
+------+--------+------+------+------------+
| no   | name   | sex  | age  | email      |
+------+--------+------+------+------------+
|    1 | LiMing | 男   |   18 | LiMing.com |
+------+--------+------+------+------------+
1 row in set (0.01 sec)

#插入的时候顺序可以乱，但是需要一一去对应
mysql> insert into t_student(age,sex,name,no) values(24,'男','wwss',2);
#insert执行之后必定会多一条记录
default ''可以去指定默认值

insert 插入日期

str_to_date('日期','格式）
mysql> create table t_student2(id int(11),name varchar(32),birth date);
Query OK, 0 rows affected, 1 warning (1.48 sec)

mysql> desc t_student2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.13 sec)

mysql> insert into t_student2(id,name,birth) values(1,'liming',str_to_date('01-10-1999','%d-%m-%Y'));
Query OK, 1 row affected (0.13 sec)

str_to_date('日期','格式）
%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒

#如果提供的是年月日这样的格式（2000-10-20）就不需要前面再去写日期了

date_format(，)可以把日期类型转换为mysql> select * from t_student2
    -> ;
+------+--------+------------+
| id   | name   | birth      |
+------+--------+------------+
|    1 | liming | 1999-10-01 |
+------+--------+------------+
1 row in set (0.00 sec)特定的格式


mysql> select id,date_format(birth,'%d-%m-%Y') from t_student2;
+------+-------------------------------+
| id   | date_format(birth,'%d-%m-%Y') |
+------+-------------------------------+
|    1 | 01-10-1999                    |
+------+-------------------------------+
1 row in set (0.12 sec)

 data和datatime区别 data是短时期datatime是长时期

date的默认格式是 %Y-%m-%d 
datetime的默认格式是 %Y-%m-%d %h:%i:%s
mysql> create table t_user(name varchar(32),no int(32),birth date,showtime datetime);
Query OK, 0 rows affected, 1 warning (0.48 sec)
mysql> desc t_user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(32) | YES  |     | NULL    |       |
| no       | int         | YES  |     | NULL    |       |
| birth    | date        | YES  |     | NULL    |       |
| showtime | datetime    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> insert into t_user(name,no,birth,showtime) values('liming',15,'2022-10-20','2022-10-20 10:20:20');
Query OK, 1 row affected (0.19 sec)
mysql> select * from t_user;
+--------+------+------------+---------------------+
| name   | no   | birth      | showtime            |
+--------+------+------------+---------------------+
| liming |   15 | 2022-10-20 | 2022-10-20 10:20:20 |
+--------+------+------------+---------------------+
1 row in set (0.00 sec)


#获取现在的时间now() 获取的时间是datetime类型的 带有时分秒的信息
mysql> insert into t_user(name,no,birth,showtime) values('lisssssming',15,'2022-10-20',now());
Query OK, 1 row affected (0.20 sec)

mysql> select * from t_user;
+-------------+------+------------+---------------------+
| name        | no   | birth      | showtime            |
+-------------+------+------------+---------------------+
| liming      |   15 | 2022-10-20 | 2022-10-20 10:20:20 |
| lisssssming |   15 | 2022-10-20 | 2022-03-31 15:31:30 |
+-------------+------+------------+---------------------+
2 rows in set (0.00 sec)

mysql遇到的报错

mysql error1064
错误原因：select后面少加一个逗号
RROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(birth,'%d-%m-%Y') from t_student2' at line 1
mysql> select id,date_format(birth,'%d-%m-%Y') from t_student2;
+------+-------------------------------+
| id   | date_format(birth,'%d-%m-%Y') |
+------+-------------------------------+
|    1 | 01-10-1999                    |
+------+-------------------------------+
1 row in set (0.12 sec)


mysql error 1064
错误原因：exists拼写错误 drop后面没有加table
mysql> drop if exits t_student2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if exits t_student2' at line 1
mysql> drop table if exits t_student2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'exits t_student2' at line 1
mysql> drop table if exists t_student2;
Query OK, 0 rows affected (0.79 sec)


mysql error 1064
错误原因：t_student 拼写错误 t-student
mysql> drop table if exists t-student;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-student' at line 1
mysql> drop table if exists t_student;
Query OK, 0 rows affected (0.44 sec)


mysql error 1064
错误原因没有加前面的 也没有加values
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''liming',15,'2022-10-20','2022-10-20 10:20:20')' at line 1
mysql> now();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'now()' at line 1
mysql> desc t_user;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(32) | YES  |     | NULL    |       |
| no       | int         | YES  |     | NULL    |       |
| birth    | date        | YES  |     | NULL    |       |
| showtime | datetime    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> insert into t_user(name,no,birth,showtime) values('liming',15,'2022-10-20','2022-10-20 10:20:20');
Query OK, 1 row affected (0.19 sec)

update 改

#默认的格式
update '表名' set colum1='',colum2='';
#注意的是需要一一去对应，否则会默认都修改了
mysql> create table t_student(no int,name varchar(32),sex char(1));
Query OK, 0 rows affected (1.33 sec)

mysql> insert into t_student(no,name,sex) values(18,'liming','男');
Query OK, 1 row affected (0.08 sec)

mysql> update t_student set no=10,name='lllll',sex='h';
Query OK, 1 row affected (0.82 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t_student;
+------+-------+------+
| no   | name  | sex  |
+------+-------+------+
|   10 | lllll | h    |
+------+-------+------+
1 row in set (0.02 sec)



mysql> update t_student set no=2;
Query OK, 2 rows affected (0.15 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from t_student;
+------+-------+------+
| no   | name  | sex  |
+------+-------+------+
|    2 | lllll | h    |
|    2 | das   | d    |
+------+-------+------+
2 rows in set (0.00 sec)

delete 删除 

#一定也是要添加条件否则就会删除全部的
mysql> select * from t_student;
+------+-------+------+
| no   | name  | sex  |
+------+-------+------+
|    2 | lllll | h    |
|    2 | das   | d    |
+------+-------+------+
2 rows in set (0.00 sec)

mysql> delete from t_student;
Query OK, 2 rows affected (0.08 sec)

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


mysql> select * from t_student;
+------+------+------+
| no   | name | sex  |
+------+------+------+
|   10 | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)

mysql> insert into t_student(no) values(19);
Query OK, 1 row affected (0.06 sec)

mysql> select * from t_student;
+------+------+------+
| no   | name | sex  |
+------+------+------+
|   10 | NULL | NULL |
|   19 | NULL | NULL |
+------+------+------+
2 rows in set (0.00 sec)

mysql> delete from t_student where no=10;
Query OK, 1 row affected (0.15 sec)

mysql> select * from t_student;
+------+------+------+
| no   | name | sex  |
+------+------+------+
|   19 | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)