数据库操作

创建数据库，创建表

## DROP DATABASE IF EXISTS db_d1;

## CREATE DATABASE db_d1;

USE db_d1;

-- create table 表名(字段名 类型 长度 <约束，默认值，注释>)
CREATE TABLE tb_Alm1
(
	D1_id INTEGER(10) auto_increment PRIMARY KEY COMMENT "测试ID",
	D1_name VARCHAR(20) not null COMMENT "测试名称",
	D1_pmd VARCHAR(30) not NULL DEFAULT "123456" COMMENT "测试密码"
)

常用数据类型

-- 常用类型
CREATE TABLE tb_User
(
	user_id int(10) PRIMARY KEY COMMENT"用户ID",
	user_name VARCHAR(30) NOT NULL,
	user_birthday DATE,
	user_gneder CHAR(3),
	user_state TINYINT(1) NOT NULL,
	user_height DECIMAL(4,1) NOT NULL,
	user_decribe text
);

查询当前数据中的所有表

## 查询当前数据中的所有表
SHOW TABLES;

## 查询创建表时候的SQL语句
##  语法：SHOW CREATE TABLE tb_name;
SHOW CREATE TABLE tb_alm1;

增

## 新增数据语法
## 语法：INSERT INTO 表名称(字段名称,字段名1,字段名2,字段名3。。。字段名n) VALUES(值1,值2,值3。。。值n)
INSERT INTO tb_alm1 (D1_id,D1_name,D1_pmd,user_Tel) VALUES(2354,"654321","8888","1");
INSERT INTO tb_alm1 VALUES(1,"zs","8888","136"),(2,"ls","1111","137"),(3,"ww","2222","138"),(4,"wm","3333","139"),(5,"sr","4444","140");

改

## 修改表中数据语法
## 语法：UPDATE 表名 SET 字段1=修改的值1,字段2=修改的值2。。。字段n=修改的值n WHERE 修改条件

UPDATE tb_alm1 SET D1_name="zs" WHERE D1_name="456";

## 修改多个字段时用","隔开
UPDATE tb_alm1 SET user_Tel="0000",D1_pmd="56790" WHERE D1_name="zs";

查

## 语法:
## 不带条件查询：SELECT * FROM 表名
##   带条件查询：SELECT * from 表名 WHERE 条件;(比较运算符 > < >= <= != <> =)

SELECT user_Tel FROM tb_alm1;

SELECT user_Tel FROM tb_alm1 WHERE D1_id=1;

SELECT * FROM tb_alm1 WHERE D1_id<3;## 模糊查询：【LIKE "_"占位 "%"代表通配符】
INSERT INTO tb_alm1 VALUES(1,"zs","8888","136"),(2,"ls","1111","137"),(3,"ww","2222","138"),(4,"wm","3333","139"),(5,"sr","4444","140");
SELECT * FROM tb_alm1 WHERE user_Tel LIKE"13%";

SELECT * FROM tb_alm1 WHERE user_Tel LIKE"_3_%";

SELECT * FROM tb_alm1 WHERE user_Tel LIKE"%13%";

## IN 查询

SELECT * FROM tb_alm1 WHERE D1_id IN(1,2,3);

## BETWEEN AND 查询

SELECT * FROM tb_alm1 WHERE D1_id BETWEEN 1 AND 3;

删

## 删
## 语法: DELETE FROM 表名 WHERE 删除的条件

DELETE FROM tb_alm1 WHERE D1_id>3;

DELETE FROM tb_alm1 WHERE D1_name="123" OR D1_id=2;