– –创建数据库
CREATE DATABASE class06;
– 查看所有的数据库 show
SHOW DATABASES;
– 删除数据库
DROP DATABASE class05;
– –修改数据库的字符编码集 alter set设置 get获取
ALTER DATABASE class06 DEFAULT CHARACTER set gbk;
– 选择哪个数据库
USE class06;
SHOW TABLES;
– 创建表 CREATE TABLE 表名 ();
CREATE TABLE student(
s_ID INT(3),
s_name VARCHAR(10),
s_age INT(1)
);
– 查看表结构 DESC
DESC student;
– 查看数据库中的所有表
-- SHOW TABLES
– – 修改表 ALTER
– – 添加字段 add COLUMN
alter TABLE student add COLUMN s_sex CHAR(10);
– – 修改字段类型 MODIFY COLUMN
alter TABLE student MODIFY COLUMN s_sex INT(10);
– – 修改字段名称 CHANGE COLUMN 旧字段名称 新字段名
alter TABLE teahcer CHANGE COLUMN sex s_sex CHAR(10);
– – 修改表名 rename to 新的表名
ALTER TABLE teahcer rename TO student;
DESC teahcer;
– 添加数据 INSERT INTO 表名 VALUES(数据)INSERT INTO student添加多条数据
VALUES(4,'大发发',18),
(5,'大发的发',18);
– 添加name 的类型
```
INSERT INTO STUDENT(s_name)
VALUES('大发发'),
('大发的发');
INSERT INTO student VALUES(
3,'张连',56
);
INSERT INTO student VALUES(
1,'张得分连',53
);
– 更新UPDATUPDATE STUDENT SET S_AGE = 20;
– 修改某一个字段 WHEREUPDATE STUDENT SET S_AGE =18 WHERE S_ID=2
–删除数据 DELETE
DELETE FROM STUDENT WHERE S_ID=2;
– 删除数据 不加where过滤 则会全部删除
DELETE FROM STUDENT ;
DROP student;
– 数据的约束 5种 默认值 非空 空键 外键 唯一 自增长
– 默认值
use lobp;
SELECT * FROM studentNew;
CREATE TABLE studentNew(
s_id INT COMMENT '学号',
s_name VARCHAR(10) COMMENT '名字',
s_address VARBINARY(20) COMMENT '地址' not NULL
);
INSERT INTO studentNew(s_id,s_name,s_address)
VALUES(1,"张三","shanghai");
DROP TABLE student;
DROP TABLE studentNew;
– 唯一性
– 对字段的值不能重复,只能是唯一的
– 字符串的默认值是null
CREATE TABLE studentNew(
s_id INT UNIQUE COMMENT '学号',
s_name VARCHAR(10) COMMENT '名字',
s_address VARBINARY(20) COMMENT '地址'
);
– 主键 PRIMARY KEY
– 作用 :非空+唯一
CREATE TABLE studentNew(
s_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
s_name VARCHAR(10) COMMENT '名字',
s_address VARCHAR(20) COMMENT '地址'
);
INSERT INTO studentNew(s_name,s_address)
VALUES("李四","上海");
INSERT INTO studentNew(s_name)
VALUES("李8");
– 自增长 auto_increment
– 作用:自动递增 默认值从1开始的,只能用于主键
CREATE TABLE studentNew(
s_id INT COMMENT '学号' PRIMARY KEY auto_increment,
s_name VARCHAR(10) COMMENT '名字',
s_address VARCHAR(10) COMMENT '地址' DEFAULT '重庆'
)AUTO_INCREMENT=100;
– 创建fruits表格
CREATE TABLE fruits(
f_id VARCHAR(2) PRIMARY KEY COMMENT '编号',
s_id INT COMMENT '编号',
f_name VARCHAR(2) COMMENT '名字' NOT NULL,
f_price FLOAT(1) COMMENT '价格' NOT NULL
);
DROP TABLE fruits;
INSERT INTO fruits(f_id,s_id,f_name,f_price)
VALUES
('12',102,'lemon',6.4),
('a1',101,'apple',5.2),
('a2',103,'aprecot',2.2),
('a3',103,'cot',2.3)
– 创建suppliers表格
CREATE TABLE suppliers(
s_id INT(2) PRIMARY KEY COMMENT '编号' AUTO_INCREMENT,
s_name VARCHAR(10) COMMENT '名称',
s_city VARCHAR(10) COMMENT '城市' NOT NULL,
s_zip INT(2) COMMENT 'zip编号' NOT NULL,
s_call INT(2) COMMENT '电话'
)AUTO_INCREMENT=100;
-- ALTER TABLE suppliers AUTO_INCREMENT=100;
ALTER TABLE suppliers AUTO_INCREMENT_INCREMENT=2;
DROP TABLE suppliers;
INSERT INTO suppliers(s_name,s_city,s_zip,s_call)
VALUES
('FastFruic Inc','Tianjin',30000,48075),
('LT Supplies','Chongqin',40000,484345),
('LT Supplies','Shanghai',40000,484345);
DELETE FROM suppliers WHERE s_id=3;
-- SET auto_increment_increment=10; #自增量每次增加的值改为10,
-- SET auto_increment_offset=2; #第一次加载数值时的偏移值的个位值
-- SHOW VARIABLES LIKE 'AUTO_INC%'; #查看修改后变量的值查询操作
select 字段名 FROM 表名 WHERE 筛选条件
– 查询所有字段 * 通配符(所有)
SELECT *
FROM fruits;
– 查看某一条数据 WHERE判断符
– where 条件查询 判断符
– > = >= <= != <>表示不等于 BETWEEN 位于俩者之间
SELECT * FROM fruits where f_id ='12';
sELECT f_name as ‘水果’
FROM fruits
WHERE f_id ='12';
– 聚合函数 5种 sum()、AVG( expr)、min、max、count统计
sELECT AVG(s_id)
FROM fruits;
WHERE f_id ='12';
– 分组查询 group by
SELECT s_id,GROUP_CONCAT(f_name),f_price,COUNT(s_id)
FROM FRUITS
GROUP BY S_ID
HAVING COUNT(s_id)>3;
– 删除数据 DELETE删除不了约束条件 TRUNCATE可以删除数据包括其约束条件
TRUNCATE table suppliers;