创建数据库
用CREATE DATABASE关键字(也可以小写但建议关键字用大写方便区分)创建一个名为“mydatabase”的数据库。
CREATE DATABASE mydatabase;
如果名称和关键字相撞,可以用Esc键下面的反引号括起来(关键字会显示蓝色)。
选择要运行的语句再点击“闪电”(也可以根据鼠标点下这一语句然后再点击第二个“闪电”即带“I的闪电”)即可创建数据库“database”(已经存在的数据库再创建会报错)。
显示数据库
用SHOW DATABASE显示已有的数据库,其中红框里的是Mysql内建的数据库。
SHOW DATABASES;
删除数据库
用DROP DATABASE删除名为"database"的数据库(不冲突的名字可以不用反引号括起来)。
DROP DATABASE `database`;
表格
创建表格
USE表示使用哪个数据库,CREATE TABLE表示在该数据库下创建表格,在这里都用反引号括起来是为了防止冲突,比如name(单行注释--后记得加一个空格才能被识别为注释)。
USE mydatabase;
CREATE TABLE `student`(
`student_id` INT PRIMARY KEY,-- INT表示student_id的数据类型是int整型,PRIMARY KEY表示student_id是作为student区分的唯一标识
`name` VARCHAR(20),-- VARCHAR(20)表示是个不定长的字符串,最大可以存20个字符
`major` VARCHAR(20)
);
显示表格样式
用DESCRIBE显示表格。
DESCRIBE student;
删除表格
用DROP TABLE删除表格。
DROP TABLE student;
更改表格的某行
用ALTER TABLE `表格` ADD 增加表格中的属性(行)。
ALTER TABLE student ADD gpa DECIMAL(3,2);-- DECIMAL(3,2)表示十进制的浮点数,其中位数3位,小数位占2位
用ALTER TABLE `表格` DROP COLUMN `属性` 删除表格中的某属性。
ALTER TABLE student DROP COLUMN gpa;
填写表格
用INSERT INTO `表格` VALUES()按照创建的表格中的属性顺序填写表格(如果某个属性不填就默认为NULL或者也可以直接填NULL)。
INSERT INTO student VALUES(1,'小黑','物理');
显示表格内容
用SELECT * FROM `表格`显示表格中填写的内容(*代表全部)。
SELECT * FROM student;
表格的属性限制
新增诸如NOT NULL、UNIQUE等限制属性的值。
CREATE TABLE `student`(
`student_id` INT PRIMARY KEY AUTO_INCREMENT,-- AUTO_INCREMENT表示自动编号,这样填表就不用手动填student_id的值了
`name` VARCHAR(20) NOT NULL,-- 表示该属性不能为NULL
`major` VARCHAR(20) UNIQUE -- 表示该属性的值不能重复,比如已经有‘物理’就不能再新增major为‘物理’的字段了
);
一般在PRIMARY KEY加上AUTO_INCREMENT自动编号,但是填表时需要(`name`,`major`)这样指定填的是`name`,`major`。
修改表格属性
修改
比如初始的表格是这样的。
现在要将“英语”改为“英语文学”。
UPDATE student
SET major = '英语文学'
WHERE major = '英语';
我们也可以在WHERE里填其他属性来找到该条表格信息。
我们也可以用OR同时把“英语文学”和“物理”都改为“数学”。
我们可以在SET同时修改多个属性值。
如果不写WHERE就会修改全部。
删除
用DELETE FROM删除第几条数据。
DELETE FROM student
WHERE student_id = 3;
我们也可以在WHERE匹配多个条件。
除了用逻辑运算符,我们也可以用比较运算符,注意不等于是<>。
不写WHERE就是全删。
搜寻表格数据
用SELECT "属性" FROM "表格" 获取某属性,如果多个属性则用逗号隔开(*是全部属性)。
SELECT `name` FROM `student`;
现在新增一个"score"分数用来演示排序。
用ORDER BY可以从小到大排序,也可以写ORDER BY `score`,`student_id`,这样表示如果"score"一样再按"student_id"从小到大排。
从大到小则加上DESC,默认是从小到大ASC。
LIMIT表示获取前几条数据。
还可以与排序结合起来,比如获取前几高分数的数据。
还可以与WHERE结合起来,比如获取分数为100的数据。
用IN类似于用OR连接起来,不过会更简洁。
多表格操作
案例:创建一个公司数据库,这里的多张表是相互联系的。
创建公司数据库
-- 创建公司数据库表格
CREATE DATABASE `sql_tutorial`;
SHOW DATABASES;
USE `sql_tutorial`;
DROP TABLE `student`;
SET SQL_SAFE_UPDATES = 0; #关闭预设自动更新
#创建表格
CREATE TABLE `employee`(
`emp_id` INT PRIMARY KEY,
`name` VARCHAR(20),
`brith_date` DATE,
`sex` VARCHAR(1),
`salary` INT,
`branch_id` INT,
`sup_id` INT
);
DROP TABLE `employee`;
describe TABLE employee;
#创建部门表格
# on delete 删除某一行
# on delete set NULL:如果删掉某个资料1,其他表格的资料2用外键对应的该资料1,在其他表格中把资料2设为null,当资料2为primary key时,不能设置为null,只能为on delete cascade
#on delete cascade:如果删掉某个资料1,其他表格的资料2用外键对应的该资料1,在其他表格中把资料2也删掉
CREATE TABLE `branch`(
`branch_id` INT PRIMARY KEY,
`branch_name` VARCHAR(20),
`manager_id` INT,
FOREIGN KEY (`manager_id`) REFERENCES `employee`(`emp_id`) ON DELETE SET NULL #FOREIGN KEY表示外键;REFERENCES 表示外键接的表格名,再加外接的表格的primary key
);
#对公司表格补上外键foreign key
ALTER TABLE `employee`
ADD FOREIGN KEY(`branch_id`) #加外键 branch_id属性
REFERENCES `branch`(`branch_id`) #对应到branch表格的branch_id的属性
ON DELETE SET NULL;
ALTER TABLE `employee`
ADD FOREIGN KEY(`sup_id`) #加外键sup_id
REFERENCES `employee`(`emp_id`) #对应到branch表格的branch_id的属性
ON DELETE SET NULL;
#创建客户表格
CREATE TABLE `client`(
`client_id` INT PRIMARY KEY,
`client_name` VARCHAR(20),
`phone` VARCHAR(20)
);
#创建worh_with表格
CREATE TABLE `work_with`(
`emp_id` INT,
`client_id` INT,
`total_sales` INT,
PRIMARY KEY ( `emp_id`, `client_id`),
FOREIGN KEY (`emp_id`) REFERENCES `employee`(`emp_id`) ON DELETE CASCADE,
FOREIGN KEY (`client_id`) REFERENCES `client`(`client_id`) ON DELETE CASCADE
);
#填写表格数据
#有foreign key时,对应的表没有相应资料时,直接输入会报错,要用NULL代替
INSERT INTO `branch` VALUES(1,'研发',NULL);
INSERT INTO `branch` VALUES(2,'行政',NULL);
INSERT INTO `branch` VALUES(3,'查询',NULL);
INSERT INTO `branch` VALUES(4,'查询',NULL);
INSERT INTO `employee` VALUES(206,'小黄','1998-10-08','F',50000,1,NULL);
INSERT INTO `employee` VALUES(207,'小绿','1995-10-09','M',51000,2,206);
INSERT INTO `employee` VALUES(208,'小灰','1993-11-24','M',23000,3,207);
INSERT INTO `employee` VALUES(209,'小黑','1996-10-08','M',36000,3,208);
INSERT INTO `employee` VALUES(210,'小红','1997-07-05','F',50000,1,209);
#修改
UPDATE `employee`
SET `branch_id` = '3'
WHERE `emp_id` = 209;
select * from `employee`;
desc `employee`;
DROP TABLE `employee`;
#把原本branch的manager_id=NULL改回来
UPDATE `branch`
SET `manager_id`=206
where `branch_id`=1;
UPDATE `branch`
SET `manager_id`=207
where `branch_id`=2;
UPDATE `branch`
SET `manager_id`=208
where `branch_id`=3;
INSERT INTO `client` VALUES(400,'阿狗','1234567');
INSERT INTO `client` VALUES(401,'阿猫','1673692');
INSERT INTO `client` VALUES(402,'来福','1894738');
INSERT INTO `client` VALUES(403,'路西','1746952');
INSERT INTO `client` VALUES(405,'杰克','9947538');
INSERT INTO `work_with` VALUES(206,400,70000);
INSERT INTO `work_with` VALUES(207,400,'56000');
INSERT INTO `work_with` VALUES(208,402,'35000');
INSERT INTO `work_with` VALUES(209,403,'54000');
INSERT INTO `work_with` VALUES(210,404,'64000');
DROP TABLE `worh_with`;
desc work_with;
select * from `work_with`;
聚合函数
##聚合函数 aggregate functions
#取得员工数目
select count(*) from `employee`; #employee表格里有几条数据
select count(sup_id) from `employee`; #count()里对应的属性的数据有几条
#取得所有出生在1996-10-01之后出生的女性员工
select count(*) from `employee`
where `brith_date` > '1996-10-01' and `sex`='F';
#取得所有员工的平均薪水
select avg(`salary`) from `employee`;
#取得所有员工的总和薪水 sum
select sum(`salary`) from `employee`;
#取得最高/低员工的薪水 max,min
select max(`salary`) from `employee`;
万用字元
##wildcards 万用字元 %表示多个字元,_表示一个字元,可以用一个,也可以前后都用
#取得电话号码尾数是567的客户
select * from `client`
where `phone` like '%567';
#取得电话号码开头是189的客户
select * from `client`
where `phone` like '189%';
#取得姓阿的员工
select * from `client`
where `client_name` like '阿%';
#取得生日在10月的员工
select * from `employee`
where `brith_date` like '_____10%';
合并表格
union
##union 连集 可以合并搜寻的结果,合并的属性资料形态要一样,不同表格的属性数目要一样,不能用表格1的2个属性去和表格2的1个属性合并
#员工名字和客户名和部门名字合并
select (`name`) from `employee`
union
select (`client_name`) from `client`
union
select (`branch_name`) from `branch`;
#员工和客户各自的id和名字合并
select `name`,`emp_id` from `employee`
union
select `client_name`,`client_id` from `client`;
#员工和客户各自的id和名字合并,as相当于C++的引用,改个名字
select `name` as `total_name`,`emp_id` as `total_id` #改变回传的属性名称
from `employee`
union
select `client_name`,`client_id` from `client`;
join
##join 连接
#取得所有部门经理的名字,把employee和branch两个表格连接起来,条件是`emp_id` = `manager_id`,比如emp_id和manager_id都有208这条数据且employee和branch分别有n和m个属性,那么就把两个表格的这条数据的各种属性合并起来,属性有n+m个。
select * from employee
#select `emp_id`, `name`, `branch_name` from `employee`
join `branch`
on `emp_id` = `manager_id`;
#如果不同表格有相同属性,用表格.属性名称进行区分,说明表格属性来源
select `employee`.`emp_id`, `employee`.`name`, `branch`.`branch_name` from `employee`
join `branch`
on `employee`.`emp_id` = `branch`.`manager_id`;
#left join/right join把左/右边的表格都回传,右/左边表格符合条件才回传,否则右/左边表格不符合条件的属性项回传NULL
select *
#from `employee` left join `branch`
from `employee` right join `branch`
on `emp_id` = `manager_id`;
子查询
##subquery 子查询,在一个查询语句里面查询另外一个结果,就是在一个查询语句里面插入另外一个查询结果
#找出研发部门的经理名字:先找研发部门的manager_id(放在括号里面的,就是子查询的部分),再用id进行查找名字
select `name` from `employee`
where `emp_id` = (
select `manager_id` from `branch`
where `branch_name` = '研发'
);
#找出一位客户销售金额超过50000的员工名字:当子查询回传结果不止一个,要用in不用=
select `name` from `employee`
where `emp_id`in (
select `emp_id` from `work_with`
where `total_sales` > 50000
);
举例:
有关系数据库如下,一位读者可以借阅多本书,同一书号的书可以被多位读者借阅。
书(书号、书名,作者,价格,库存量)读者(证件号,姓名,单位,地址)
借阅(书号,证件号,借书时间,还书时间,备注)
其中:某位读者的借阅记录中的“还书时间”为空白表示未还书。
1.用SQL 语言实现,查询“证件号”为R1的读者所借图书的“书名”和“作者”。
#写法一
SELECT 书.书名, 书.作者
FROM 书
JOIN 借阅 ON 书.书号 = 借阅.书号
WHERE 借阅.证件号 = 'R1';
#写法二
SELECT 书名, 作者
FROM 借阅, 书
WHERE 借阅.证件号 = 'R1' AND 借阅.书号 = 书.书号;
2.用SQL 语言实现,查询比《数据库原理》价格高的“书号”和“书名”(请写出两种查询语句)。
#子查询
SELECT 书号, 书名
FROM 书
WHERE 价格 > (SELECT 价格 FROM 书 WHERE 书名 = '数据库原理');
#自连接
SELECT X.书号, X.书名
FROM 书 AS X, 书 AS Y
WHERE X.价格 > Y.价格 AND Y.书名 = '数据库原理';
3.用 SQL 语言实现,查询借阅人数在 3 个以上的“书号”和借阅人数。
Select 书号, count(*) as r_sum
From 借阅
Group by 书号
Having(count(*)>=3);
4.用 SQL 语言实现,建立“书名”中包含“计算机”的图书视图。
CREATE VIEW Book_Computer
AS
SELECT *
FROM 书
Where 书名 like '%计算机%';