DESC stu;
ALTER TABLE stu CHANGE stu_class3 stu_class3 DOUBLE(4,1);
添加数据
insert into stu(列1,列2,列3…列n) values(值1,值2,值3…值n)
INSERT INTO stu(stu_id ,stu_name,stu_nation) VALUES (1,‘张三’,‘汉族’);
SELECT * FROM stu;
INSERT INTO stu VALUES(2,‘李四’,‘北京’,‘汉族’,99.0,80.0,79.0,NULL);
ALTER TABLE stu MODIFY use_time TIMESTAMP(YYYY-MM-dd hh:mm:ss);
INSERT INTO stu VALUES(3,‘王五’,‘America’,‘forige’,77,88,99,NULL);
DELETE FROM stu WHERE stu_id=1;
TRUNCATE TABLE stu; - 删除表,然后再创建一个一模一样的空表
UPDATE stu SET stu_address = ‘天津’,stu_class1=80,stu_class2=60,stu_class3=55 WHERE stu_id =1;
SHOW TABLES;
DESC sheet1
;
S
E
L
E
C
T
∗
F
R
O
M
s
h
e
e
t
1
; SELECT * FROM sheet1
;SELECT∗FROMsheet1;
ALTER TABLE sheet1$ RENAME TO stu_databases;
SELECT * FROM stu_databases;
ALTER TABLE stu_databases RENAME TO 21stu;
SELECT * FROM 21stu;
SELECT 专业名称, COUNT(班级名称)AS 人数,所属系部名称 FROM 21stu GROUP BY 专业名称;
- 按照专业,班级人数,所属系部汇总出来
SELECT 专业名称, COUNT(班级名称)AS 人数,所属系部名称 FROM 21stu GROUP BY 专业名称 HAVING COUNT(班级名称)>30;
SELECT 专业名称, COUNT(班级名称)AS 人数,所属系部名称 FROM 21stu GROUP BY 专业名称 HAVING 人数 >30;
where 和 having的区别
where在分组之前进行限定,如果不满足条件,则不参与分组,having在分组之后进行限定,如果不满足结果,则不会被查询出来
where后不可以跟聚合函数,having可以进行聚合函数的判断。
-
分页查询
/*
语法:limit 开始的索引,每页查询的条数- 每页显示三条记录
SELECT * FROM 21stu LIMIT 0,3; - 第一页
SELECT * FROM 21stu LIMIT 3,3; - 第二页
SELECT * FROM 21stu LIMIT 6,3; - 第三页
- 公式:开始的索引 = (当前的页码 - 1)* 每页的条数
limit 分页操作是一个"方言"
这句话仅仅限于mysql - 每页显示三条记录
*/
- 每页显示三条记录
SELECT * FROM 21stu LIMIT 0,3; - 第一页
SELECT * FROM 21stu LIMIT 3,3; - 第二页
SELECT * FROM 21stu LIMIT 6,3; - 第三页
-
公式:开始的索引 = (当前的页码 - 1)* 每页的条数
/*
DQL:查询表中的记录
*select * from 表名;
1.语法
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件限定
order by
排序(asc 升序(默认) desc 降序)
limit
分页
2.基础查询
1.多个字段的查询
select 字段名1,字段名2…from 表名;
*注意:
如果查询所有字段,可以使用来替代字段列表
一般不推荐
2.去除重复
*distinct 只有两个结果集的数据完全一样才会去重
3.计算列
*一般可以使用四则运算计算一些列的值(一般只会进行数值型的计算)
* ifnull(表达式1,表达式2) null参与的运算,计算结果都为null
表达式1:哪个字段需要判断是否为null
表达式2为该字段为null后的替换值
4.起别名
*as:as也可以省略
3.条件查询
1.where子句后跟条件
2.运算符
* >、<、<=、>=、=、<>
* BETWEEN…AND
* IN(集合)
* LIKE
* 占位符:
* _ :单个字符
* %:多个任意字符
* IS NULL
* and 或 &&
* or 或 ||
* not 或 !- 查询大于20岁的人 SELECT * FROM student3 WHERE age >= 20; - 查询等于20岁的人 SELECT * FROM student3 WHERE age = 20; - 查询不等于20岁的人 SELECT * FROM student3 WHERE age != 20; SELECT * FROM student3 WHERE age <> 20; - 查询年龄大于等于20小于等于30 SELECT * FROM student3 WHERE age >= 20 AND age<=30; SELECT * FROM student3 WHERE age >=20 && age <=30; - &&不建议使用,并不通用 SELECT * FROM student3 WHERE age BETWEEN 20 AND 30; - 查询年龄22岁,18岁,25岁的信息 SELECT * FROM student3 WHERE age =22 OR age =18 OR age=25; SELECT * FROM student3 WHERE age IN(22,18,25); - 查询英语成绩为null的 SELECT * FROM student3 WHERE english = NULL;- 不对的,null值不能使用=和!=来判断 SELECT * FROM student3 WHERE english IS NULL; - 查询英语成绩不为null的 SELECT * FROM student3 WHERE english IS NOT NULL;
*/
USE student;
CREATE TABLE student3 (
id INT, - 编号
NAME VARCHAR(20), - 姓名
age INT, - 年龄
sex VARCHAR(5), - 性别
address VARCHAR(100), - 地址
math INT, - 数学
english INT - 英语
);
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (1,‘马云’,55,‘男’,’
杭州’,66,78),(2,‘马化腾’,45,‘女’,‘深圳’,98,87),(3,‘马景涛’,55,‘男’,‘香港’,56,77),(4,‘柳岩’,20,‘女’,‘湖南’,76,65),
(5,‘柳青’,20,‘男’,‘湖南’,86,NULL),(6,‘刘德华’,57,‘男’,‘香港’,99,99),(7,‘马德’,22,‘女’,‘香港’,99,99),(8,‘德玛西亚’,18,‘男’,‘南京’,56,65);
SELECT * FROM student3;
DROP TABLE student3;
SELECT
NAME, - 姓名
age - 年龄
FROM
student3;
SELECT
address
FROM
student3;
-
去除重复的结果集
SELECT DISTINCT address FROM student3; -
计算math和English的分数之和
SELECT NAME,math,english,math+english FROM student3; -
如果有null参与计算,计算结果都为null
SELECT NAME,math,english,math+ IFNULL(english,0)FROM student3;
SELECT NAME 姓名,math 数学,english 英语,math+ IFNULL(english,0)AS 总分 FROM student3;
-
查询大于20岁的人
SELECT * FROM student3 WHERE age >= 20; -
查询等于20岁的人
SELECT * FROM student3 WHERE age = 20; -
查询不等于20岁的人
SELECT * FROM student3 WHERE age != 20;
SELECT * FROM student3 WHERE age <> 20; -
查询年龄大于等于20小于等于30
SELECT * FROM student3 WHERE age >= 20 AND age<=30;
SELECT * FROM student3 WHERE age >=20 && age <=30; - &&不建议使用,并不通用
SELECT * FROM student3 WHERE age BETWEEN 20 AND 30; -
查询年龄22岁,18岁,25岁的信息
SELECT * FROM student3 WHERE age =22 OR age =18 OR age=25;
SELECT * FROM student3 WHERE age IN(22,18,25); -
查询英语成绩为null的
SELECT * FROM student3 WHERE english = NULL;- 不对的,null值不能使用=和!=来判断
SELECT * FROM student3 WHERE english IS NULL; -
查询英语成绩不为null的
SELECT * FROM student3 WHERE english IS NOT NULL; -
查询姓马的有哪些?
SELECT * FROM student3 WHERE NAME LIKE ‘马%’; -
查询第二个字是化的人有哪些?
SELECT * FROM student3 WHERE NAME LIKE ‘_化%’; -
查询姓名是三个字的人
SELECT * FROM student3 WHERE NAME LIKE ‘___’; -
查询姓名中含有德的人
SELECT * FROM student3 WHERE NAME LIKE ‘%德%’;
约束:是对表中的数据进行限定,保证数据的正确性、有效性、完整性。
/*
分类:
1、主键约束: primary key
2、非空约束: not null
3、唯一约束: unique
4、外键约束: foreign key
*/
非空约束: not null
CREATE TABLE stu1(
id INT,
NAME VARCHAR(20) NOT NULL - name为非空
)
- 删除name的非空约束
ALTER TABLE stu1 MODIFY NAME VARCHAR(20);
DELETE FROM stu1 WHERE id =2; - 2、在创建表后添加约束
ALTER TABLE stu1 MODIFY NAME VARCHAR(20) NOT NULL;
唯一约束: unique
CREATE TABLE stu(
id INT,
phone_number VARCHAR(20) UNIQUE - 创建表时添加唯一的约束
);
- 注意MySQL中唯一约束的值限定列的值可以有多个null;
SELECT * FROM stu;
INSERT INTO stu VALUES (1,1234),(2,1233);
ALTER TABLE stu MODIFY phone_number VARCHAR(20); - 这样是无法删除的
ALTER TABLE stu DROP INDEX phone_number; - 正确的删除表的唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;
主键约束 :primary key :非空且唯一,一张表只能有一个字段为主键,主键就是表中记录的唯一标识
#创建表的时候,添加主键约束
CREATE TABLE stu(
id INT PRIMARY KEY, - 给id添加主键约束
NAME VARCHAR (20)
)
DROP TABLE stu; - 删除表
TRUNCATE TABLE stu; - 删除
SELECT * FROM stu;
- 错误写法 alter table stu modify id int;
ALTER TABLE stu DROP PRIMARY KEY;
创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
自动增长; 如果一列是数值类型的,使用auto_incerment 可以完成自动增长,一般是配合主键一起来使用的
-
在创建表的时候来添加
CREATE TABLE stu(
id INT PRIMARY KEY AUTO_INCREMENT, - 给id添加主键约束和自动增长
NAME VARCHAR(20)
)
INSERT INTO stu VALUES (NULL,‘ccc’); -
删除自动增长
ALTER TABLE stu MODIFY id INT; -
添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT; -
在创建表时,可以添加外键
-
语法:
/*
create table 表名(
…
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
) -
删除外键
ALTER TABLE 表的名称 DROP FOREIGN KEY 外键名称; -
创建表后,添加外键
ALTER TABLE 表名称 ADD CONSTRAINT 外键名称 FOREIGN KEY (表中列的名称) REFERENCES 要关联的表名称 (关联的表的列名称);
DROP TABLE department;
- 解决方案:分成 2 张表
- 创建部门表(id,dep_name,dep_location)
- 一方,主表
CREATE TABLE department(
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
); - 创建员工表(id,name,age,dep_id)
- 多方,从表
CREATE TABLE employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT, - 外键对应主表的主键
CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department (id)
); - 添加 2 个部门
INSERT INTO department VALUES(NULL,‘研发部’,‘广州’),(NULL,‘销售部’,‘深圳’);
SELECT * FROM department; - 添加员工,dep_id 表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES (‘张三’,20,1);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘李四’,21,1);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘王五’,20,1);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘老王’,20,2);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘大王’,22,2);
INSERT INTO employee (NAME, age, dep_id) VALUES (‘小王’,18,2);
*/
SELECT * FROM employee;
SELECT * FROM department;
- 删除外键
ALTER TABLE employee DROP FOREIGN KEY dep_id_kf; - 创建表后,添加外键
ALTER TABLE employee ADD CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES department (id);
级联操作
- 添加级联,设置级联更新
- 设置外键以后,不能轻易去更改数据,所以就要设计到级联更新
方法1
- 第一步将关联表的值设为null
UPDATE employee SET dep_id = NULL WHERE dep_id= 1; - 第二步修改表的外键关联值
UPDATE department SET id = 5 WHERE id = 1; - 第三步进行关联表值的修改
UPDATE employee SET dep_id = 5 WHERE dep_id IS NULL;
方法2 添加外键实现级联更新
ALTER TABLE employee ADD CONSTRAINT dep_id_kf FOREIGN KEY (dep_id) REFERENCES department (id) ON UPDATE CASCADE;
- 级联删除 on delete cascade 在实际操作中,要很谨慎,关联的各个表的对应数据会在全部删除