CREATE 创建表、数据库
CREATE DATABASE company;
USE在同一个localhost上选择不同的 database
# USE 这个database的名字
USE onlytest2
在database中创建一个table
第一行写你要创建的表的名字是什么
从第二行开始,第一个元素是列名,第二个元素是这一列的数据的类型,两个元素之间用空格分割,每一列的描述之间用逗号分割
USE onlytest2;
CREATE TABLE stest8(
id INT, 规定的类型是整数,你写小数、字符串进去都录入不进去
name VARCHAR(20), # 名字是字符串变量,长度不许超过20个字节
sex ENUM('男','女'), # 只能从男女中选
ruxueriqi DATE, # 类型是时间,年份
birthday DATETIME); # 类型是时间
在MySQL5.0以上VARCHAR(20)表示可以存储20个汉字。
下面是你打算存21个汉字,保存的时候会报错
21个数字,报错
21个字母,报错
ENUM让你只能在后面两个中选一个,除了这两个选项之外,“男”和“女”之外的其他字符串,你想录入都录入不进去 。
DATE和DATETIME的区别如下
DATE就是 年-月-日
DATETIME是在DATE的 年-月-月 的基础上,多了 小时:分钟:秒
将一列设为主键、key自增、一列非空、默认为0
CREATE TABLE stest9(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
grade FLOAT DEFAULT 0,
sex ENUM('男','女'),
rexueriqi DATE,
birthday DATETIME # 切记,最后一列这个 结尾不要加逗号,否则会报错
);
只要选定了某一列自增,这一列即使你不设定,也是默认设定为非空
外键CONSTRAINT ... FOREIGN KEY
创建一个叫mathgrade的TABLE,这个TABLE的PRIMARY KEY, id2 ,是 引用自 stest9的PRIMARY KEY id
CREATE TABLE mathgrade(
id2 INT PRIMARY KEY,
grade INT,
# constraint 给约束起的变量名字(自己随便起,爱叫啥叫啥) foreign key(引用别人的表的 ID) reference 被引用表(被 引用表中字段)
CONSTRAINT cons1 FOREIGN KEY(id2) REFERENCES stest9(id)
);
注意点:
1 被引用字段必须有唯一特性(primary key unique)的 建议就是主键
2 被引用字段和引用字段 他们名字可以一样也可以不一样,但是 类型 必须一模
一样
3 注意外键的名字必须要唯一
4 删除表的时候 ,注意表示被引用状态 表示删除不了的?
解决: 第一种:先删除 引用表(成绩表)再删除 被引用表(学生表)
第二种: 解除外键关系 任意删除了
①查看外键的名字 ②根据外键的名字进行解除
代码:
ALTER TABLE mathgrade DROP foreign key aa_foreign11; #删除外键
DESC,SHOW查看表
查看TABLE的每一列的列名,数据类型,是否默认非空,默认值有没有设定,有没有其他特性如设定为自增
DESC stest3;
查看一个表创建时候用的SQL语句
SHOW CREATE TABLE stest9;
列出这个DATABASE下面所有的TABLE的名字
SHOW TABLES;
ALTER修改表的结构
修改表的名字
# alter table 旧名字 rename to 新名字
ALTER TABLE stest2 RENAME TO s2;
改变某个TABLE的某一列的 列名和数据类型
语法:alter table 表名 change 旧字段名 新字段名 新字段的数据类型;
ALTER TABLE s2 CHANGE name fullname VARCHAR(4);
给某个TABLE增加一列,设定列名和这一列数据的数据类型
# alter table 表名 add 字段名字 字段类型 (可选字段的约束)
ALTER TABLE s2 ADD grade FLOAT;
删除某个TABLE中的某一列
#alter table 表名 drop 字段名字;
ALTER TABLE s2 DROP grade;
直接删除一个TABLE
DROP TABLE s2;
INSERT 向表格中插入数据
# insert into 表 (字段 1,字段 2,字段 3.。。) values (数据 1,数据 2,数据 3...)
INSERT INTO stest3(id, name, sex) VALUES(1, '令狐冲', '直');
自增的那一列(那个字段)可以在插入数据的时候不写,id会自动增加数字
INSERT INTO stest9(name,grade) VALUES('孙悟空',99);
一次插入多条记录。括号,逗号分割即可。
INSERT INTO stest9(name,grade) VALUES
('唐三藏',1),
('猪刚鬣',40),
('沙悟净',20),
('龙王三太子', 10);
不写列名也可以插入,但是顺序要严格按照列名来写。顺序不能错,任何一列都不能缺。不可以省略任何一列
INSERT INTO stest9 VALUES (10,'银角大王',80,'男','2023-11-11','2023-11-11');
UPDATE修改表的数据
update 表的名字 set 修改内容 where 字段 in (select 语句)
不设定条件,整个表,每一行,全部更新
# sc这个表里面所有的行的score这一列全部减2
UPDATE sc SET score=score-2;
用WHERE,就只有满足这个条件的才修改
# 让sc这个TABLE中score这一列中,sid=101的 score加10
UPDATE sc SET score=score+10 WHERE sid=101;
DELETE,DROP删除表
创建好要被删的数据
# 创建一个能被删的数据
# 创建表结构
CREATE TABLE student333(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
age INT,
gender ENUM('男','女')
);
# 向表内新增数据
INSERT INTO student333(name, age, gender) VALUES
('武大郎',40,'男'),
('西门庆',42,'男'),
('潘金莲',18,'女');
既要删除表内数据,也要删除表的结构
DROP TABLE student333;
删除表内的数据,但是保留表结构(表的每一列的列名和数据类型限定还有,其他东西没有了)
DELETE FROM student333;
有条件的删除表内某些行
DELETE FROM student333 WHERE id=2;
但是这个DELETE删除后,再往里面新增数据,PRIMARY KEY的序号从删除的那些行以后继续
删除之前的三行的id分别是0 1 2,删除以后再新增的三行id是在删除前的基础上自增,变成4 5 6
如果你不想删除三行以后,新增数据的 id在删除前的基础上自增,删除应该用TRUNCATE
TRUNCATE TABLE student333;
结果真的从1 开始了,不是从4开始了
作业
1)根据以下信息写出建表语句: 有一张学生表 stu,包含以下信息:
学号 int 主键约束
姓名 varchar(8) 非空约束
年龄 int
性别 varchar(4)
家庭住址 varchar(50)
联系电话 int 唯一约束
首先你要注意,在SQL里面列名可以是英文,也可以是中文
限制条件要求某一列必须不重复,要用CONSTRAINT来实现
CREATE TABLE stu(
`学号` INT PRIMARY KEY,
`姓名` VARCHAR(8) NOT NULL,
`年龄` INT,
`性别` VARCHAR(4),
`家庭住址` VARCHAR(50),
`联系电话` INT,
CONSTRAINT c1 UNIQUE(`联系电话`)
);
2)修改学生表的结构,添加一列信息,学历 varchar(6)
ALTER TABLE stu ADD `学历` VARCHAR(6);
3)修改学生表的结构,删除一列信息,家庭住址
ALTER TABLE stu DROP `家庭住址`;
SELECT查询数据
SELECT 你想要查询调用的列的列名 FROM 这一列所在的TABLE的名字
SELECT sname FROM student;
检索过个列,就在SELECT后面多写几个列的名字
SELECT sname,sage FROM student;
调用取出所有列,用 星号 * 表示所有列
SELECT * FROM student;
DISTINCT取出某一列中不重复的行
加个DISTINCT即可
SELECT DISTINCT sid FROM sc;
如何你想要两列的组合,这个组合是不重复的,在两列的列名前加DISTINCT
SELECT DISTINCT sid,cid FROM sc;
LIMIT限制只返回前n行
想从头返回之后的6条
SELECT * FROM student LIMIT 6;
想从索引为3的那一条(实际是第4条),返回之后的3条
SELECT * FROM student LIMIT 3,3;
跳过第一行,再取1行
SELECT * FROM student LIMIT 1 OFFSET 1;
上面那条LIMIT3,3等价于 用OFFSET下面这样写
SELECT * FROM student LIMIT 3 OFFSET 3;
ORDER BY DESC对于取出的数据进行排序
排序按照一个条件的时候。默认是升序。
SELECT * FROM student ORDER BY sage;
上面这句话是省略了ASC
SELECT * FROM student ORDER BY sage ASC;
希望是降序加个DESC
SELECT * FROM student ORDER BY sage DESC;
一列升序,一列降序排列
两列的列名写上 后面跟ASC或DESC
SELECT * FROM sc ORDER BY sid DESC, cid ASC;
先按照sid降序排列,然后在不改变sid这一列号码顺序的基础上,对cid进行排序
也就是先按照前面那个条件排,排完了,再在后面那个基础上排序。
不信的话,两个排序条件一对调,就会发现排序结果不同了
SELECT * FROM sc ORDER BY cid ASC, sid DESC;
用WHERE来施加过滤条件
结尾写WHERE,加条件,等于
SELECT * FROM student WHERE ssex='女';
小于,取年龄小于20岁的
SELECT * FROM student WHERE sage<20;
大于等于19,小于等于20
SELECT * FROM student WHERE sage>=19 AND sage<=20;
BETWEEN AND是 大于等于+小于等于
SELECT * FROM student WHERE sage BETWEEN 19 AND 20;
三个条件同时满足,用OR来连接
SELECT * FROM student WHERE sname='李华' OR sname='张三' OR sname='王五';
上面这种写法 可以用sname IN 一个集合的方式写,更简便
SELECT * FROM student WHERE sname IN ('李华','张三','王五');
把数据中某一行的某一列为空的筛选出来
WHERE结尾加NULL,用IS连接
SELECT * FROM student WHERE sage IS NULL;
LIKE,通配符,查姓张的、姓李的学生
% 代表 0 ~多个字符
%张% 名字中含张。 比张翼德,比如开张大吉,张 前面有0到多个字符都可以
张%,“张飞”,“张狗蛋”,“张灯结彩”,别管张后面几个字全能匹配出来
_ 下划线代表 1 个字符
想查询出,“张翼德”,用 张__
想查询出,“张飞”,用 张_
匹配姓李的,两个字名字的学员
SELECT * FROM student WHERE sname LIKE '李_';
三个字
SELECT * FROM student WHERE sname LIKE '李__';
四个字
SELECT * FROM student WHERE sname LIKE '李___';
AS 将一列选出来然后重命名
SELECT score AS '成绩修正' FROM sc;
其实AS可以不写,可以省略。只是看这句命令的人,会觉得有点懵
SELECT score '成绩修正' FROM sc;
这一列也可以做各种计算,比如同给成绩打八折
SELECT score*0.8 '成绩打八折' FROM sc;
聚合函数
AVG()求学生的平均成绩
在调取的那一列前面加AVG
SELECT AVG(score) AS '平均分' FROM sc;
求sid学号为102的学生的各科成绩的平均分
SELECT AVG(score) AS '102各科平均分' FROM sc WHERE sid=102;
COUNT()数个数
想数出表中,3003这门课上,学生的人数
SELECT COUNT(sid) '学生人数' FROM sc WHERE cid=3003;
MAX()最大值
求3003这门课上,考试最高分
SELECT MAX(score) '3003最高分' FROM sc WHERE cid=3003;
MIN()最小值
求3003这门课上,考试最低分
SELECT MIN(score) '3003最低分' FROM sc WHERE cid=3003;
SUM 求和
将学生103的各个科目所有的分数求和,算出他的总分
SELECT SUM(score) '1003的总分' FROM sc WHERE sid=103;
同时取出103这个学生的 平均分、总分、考了几科、最高分、最低分
SELECT
AVG(score) '平均分',
SUM(score)'总分',
COUNT(score)'科目数量',
MAX(score) '最高分',
MIN(score) '最低分'
FROM sc WHERE sid=103;
作业
题目:
有一张学生表 stutest,包含以下字段信息:
学号 int
姓名 varchar(8)
年龄 int
性别 varchar(4)
家庭住址 varchar(50)
联系电话 int
CREATE TABLE stutest(
学号 INT,
姓名 VARCHAR(8),
年龄 INT,
性别 VARCHAR(4),
家庭住址 VARCHAR(50),
练习电话 INT);
记得列名即使是中文的,也不能用引号引起来
1)向学生表添加如下信息:
学号 姓名 年龄 性别 家庭住址 练习电话
1 A 22 男 '北京市' 110
2 B 21 男 '上海市' 120
INSERT INTO stutest VALUES
(1,'李白',22,'男','四川', 110),
(2,'武则天',18,'女','河南',120),
(3,'朱元璋',28,'男','安徽',119),
(4, '戴笠',26,'男','浙江',NULL);
2)修改学生表的数据,将电话号码为 11 开头的学员的家庭住址改为”京城”
修改表的数据用UPDATE
UPDATE stutest SET 家庭住址='京城' WHERE 练习电话 LIKE '11%';
注意,列名即使是中文也别加引号
3)删除学生表的数据,姓名以 李 开头,性别为男的记录删除
删除某些行数据,用DELETE
DELETE FROM stutest WHERE 姓名 LIKE '李%' AND 性别='男';
李白这个李姓男子被删掉了
4)查询学生表的数据,将所有年龄小于 27 岁的,家庭住址为“河南”的,学生的姓名和学号查询出来;
SELECT 姓名,学号 FROM stutest WHERE 年龄<27 AND 家庭住址='河南';
5)查询年龄在 25 到 30 岁之间的学生
SELECT * FROM stutest WHERE 年龄 BETWEEN 25 AND 30;
6)查询联系电话不为空的学生信息
SELECT * FROM stutest WHERE 练习电话 IS NOT NULL;
7)把 练习电话 这一列的错别字 改为 联系电话
ALTER TABLE stutest CHANGE 练习电话 联系电话 INT;
GROUP BY 根据某一列来分组,计算其他数据
求每一门课上的平均分
记住顺序,先写FROM ,之后再写GROUP BY
SELECT cid 课程, AVG(score) 平均分 FROM sc GROUP BY cid;
查一下每个科目及格的人数
计数用COUNT()
SELECT cid 科目,COUNT(score) 选课人数 FROM sc WHERE score>=60 GROUP BY cid;
下面这样写 是不对的GROUP BY 必须放在WHERE之后,否则会报错
SELECT cid,COUNT(score) FROM sc GROUP BY cid WHERE score>=60;
HAVING对聚合的结果再进行筛选
HAVING 和 WHERE 的差别 这里有另一种理解方法,WHERE 在数据分组前进行过滤,HAVING在数据分组后进行过滤。
在聚合结果的基础上,筛选出聚合结果大于93分的结果
筛选出每个科目分数大于80分的平均分,然后在此基础上筛选出聚合结果大于93分的结果
HAVING二次筛选前
SELECT
cid 科目,
AVG(score) 80分以上平均分
FROM sc
WHERE
score>=80
GROUP BY cid
HAVING 80分以上平均分>93;
HAVING二次筛选后
筛选出挂科在2门以上的学生
首先用WHERE筛出挂科的学生
然后用GROUP BY把sid相同的归类为同一个GROUP,
在这个GROUP内数出挂科的次数,展示学号
最后用HAVING把挂科次数大于等于2的筛出来
# 筛选出挂科在2门以上的学生
SELECT
sid 学号,
COUNT(*) 挂科次数
FROM sc
WHERE score<=60
GROUP BY sid
HAVING 挂科次数>=2;
跨表查询-笨办法
我们目前有两张表student和sc
student表有学号、姓名、年龄、性别
sc这个表 有学号 课程名 分数
我希望查询名字为张三的学生的成绩。
这里就需要关联两张表。
首先,用student表拿到sname为 张三 的用户的 sid
SELECT sid FROM student WHERE sname='张三';