数据库（一）--基本SQL语句

之前学过的存储数据的方式

1.集合
好处：效率比较高
弊端：无法得到数据的持久化存储

2.IO流
好处：可以得到数据的持久化存储
弊端：频繁地进行IO操作，效率就会比较低

3.数据库
好处：
（1）得到数据持久化存储；
（2）数据管理起来更加方便；
（3）效率也比较高。

---

数据库的基本SQL语句

使用的是SQLyog客户端。
– 写sql语句，发送给服务端执行
– 在后面可以写注释

1.创建库，XSGL：

CREATE DATABASE XSGL DEFAULT CHARACTER SET utf8;

查询所有表：
USE XSGL; – 使用XSGL这个数据库
SHOW TABLES; – 查询XSGL这个库中的所有的表

2.创建学生表(id,name,age)：

CREATE TABLE student(-- 字段名称 字段类型id INT,NAME VARCHAR(20),age INT);

查询表中的数据：

SELECT * FROM student;  

查询一个表结构：

DESC student;  

3.删除表：
DROP TABLE student; – 删除student表

4.修改表：
ALTER TABLE student ADD COLUMN gender VARCHAR(2); – 添加一个地址字段(gender varchar(2))

ALTER TABLE student MODIFY COLUMN gender VARCHAR(3); – 修改gender字段类型为varchar(3)

ALTER TABLE student CHANGE COLUMN gender sex VARCHAR(2); – 将gender字段修改为sex字段 varchar(2)

ALTER TABLE student ADD COLUMN address VARCHAR(20); – 添加一个地址字段

ALTER TABLE student DROP COLUMN sex,DROP COLUMN address; – 删除sex和address字段

ALTER TABLE student RENAME TO stu; – 将student这个表的表名改为stu

5.数据增删修改操作：
（1）查看表的数据：
select 字段名称(如果查询所有字段*) from student;

SELECT * FROM student; – 查看student表数据

（2）插入数据：
– 给表中插入3条数据（插入所有字段）

INSERT INTO student VALUES(1,'张三',50,'西安');INSERT INTO student VALUES(2,'李四',40,'上海');INSERT INTO student VALUES(3,'王五',50,'广州');

– 当需要插入所有字段的时候我们可以只插入部分字段吗？插入不了

INSERT INTO student VALUES(1,'赵六',60,'中国香港');  --这种语法是不对的，插入不了数据

– 只想给表中只想插入id字段和name字段，请问怎么办？

INSERT INTO student(id,NAME) VALUES(4,'郭德纲');

（3）修改数据：
– 将所有学生的年龄改为50岁,修改所有学生的年龄，用的非常少

UPDATE student SET age=50;  

– 将id为3的学生姓名改为张七

UPDATE student SET NAME='张七' WHERE id=3; 

– 修改多个字段，修改id为2的学生姓名为张学友，年龄改为60

UPDATE student SET NAME='张学友',age=60 WHERE id=2;

（4）删除数据：
– 删除全表数据,本质上是一条一条进行删除的，效率比较慢

DELETE FROM student;  --效率较慢

– 删除id为3的数据

DELETE FROM student WHERE id=3;

– 使用truncate table 表名，删除全表数据，并不是一条条删，而是直接将全表数据删除，效率比较快

TRUNCATE TABLE student;  --效率较快

– delete from和truncate table 这两种删除全表的方式有什么区别呢？
第三：delete from一条条删除，truncate table直接将全表数据删掉；
第二：delete from可以按条件删除一条数据，truncate table只能删除全表数据，不能按照条件删除；
第三：delete from无法重置自增长主键，truncate table可以重置自增长主键。

（5） 查询数据：
5.1查询所有列

SELECT * FROM student;

5.2 查询指定列(多个列之间以逗号分隔)

SELECT NAME,gender FROM student;

5.3 查询时指定别名(AS)

SELECT NAME AS '姓名',gender AS '性别' FROM student;  -- AS可以省略SELECT NAME '姓名',gender '性别' FROM student;

5.4 合并列查询
– 添加servet,jsp成绩列

ALTER TABLE student ADD servlet INT,ADD jsp INT;SELECT * FROM student;UPDATE student SET servlet=86,jsp=75 WHERE id=1;UPDATE student SET servlet=90,jsp=65 WHERE id=2;UPDATE student SET servlet=78,jsp=50 WHERE id=3;UPDATE student SET servlet=85,jsp=80 WHERE id=4;

– 需求： 查询每个学生的servlet和jsp的总成绩
– 注意： 必须是数值类型

SELECT NAME '姓名',(servlet+jsp) '总成绩'  FROM student;

– 注意：和数值以外合并没有意义

SELECT NAME '姓名',(gender+jsp)  FROM student;

5.5 查询时添加常量列
– 需要： 在查询学生表时都带上一个班级列，内容为”java就业班”

SELECT NAME '姓名',gender '性别',age '年龄','java就业班' AS '班级' FROM student;

5.6 查询去除重复数据
– 需求： 统计学生都来自于哪里

SELECT DISTINCT remark2 FROM student;  -- 这种语法也可以SELECT DISTINCT(remark2) FROM student;

5.7 条件查询(where)
SELECT * FROM student;
– 1) 逻辑条件： and or
– 需求: 查询id值为3，且性别为男的学生(交集：两个条件同时满足)

SELECT * FROM student WHERE id=3 AND gender='男';

– 需求：查询id为3，或性别为男的学生（并集： 两个条件其中一个满足）

SELECT * FROM student WHERE id=3 OR gender='男';

– 2）比较条件： > < >= <= = between and
– 需求： 查询servlet分数大于80分的学生

SELECT * FROM student WHERE servlet>80;

– 需求：查询jsp分数小于或等于80分的学生

SELECT * FROM student WHERE jsp<=80;SELECT * FROM student WHERE jsp<80 OR jsp=80;

– 需求： 查询servlet成绩大于等于80，且小于等于88的学生

SELECT * FROM student WHERE servlet>=80 AND servlet<=88;

– 等价于上面

SELECT * FROM student WHERE servlet BETWEEN 80 AND 88; -- (包前包后)

– 需求：查询年龄不等于30岁的学生

SELECT * FROM student WHERE age<>30;

– 3）判空条件: =” is null <>” is not null

UPDATE student SET remark2="" WHERE id=1;UPDATE student SET remark2=NULL WHERE id=4;

– 需求： 查询remark字段为空的学生（包含null，也包含字符串）
null 和 空字符串的区别：
null： 没有数据。 判断null： is null,判断不为null： is not null
空字符： 有数据，数据就是空字符串。判断空字符： =”； 判断不为空字符串： <>”

SELECT * FROM student WHERE remark2 IS NULL;SELECT * FROM student WHERE remark2='';SELECT * FROM student WHERE remark2 IS NULL OR remark2='';

– 需求：查询remark字段不为空的学生(空串或者null都被视为空)

SELECT * FROM student WHERE remark2 IS NOT NULL AND remark2<>'';

– 4) 模糊条件： like
– like后面跟上符号
– %: 表示任意多个字符（0-多个）
– _: 表示一个字符
– 需求： 查询姓张的学生

SELECT * FROM student WHERE NAME LIKE '张%';

– 需求： 查询姓名中包含’张’字的学生

SELECT * FROM student WHERE NAME LIKE '%张%';

– 需求： 查询姓张，且姓名只有两个字的学生

SELECT * FROM student WHERE NAME LIKE '张__';

5.8聚合函数

SELECT * FROM student;

– 需求： 查询所有学生servlet的总分 (SUM: 求和函数)

SELECT SUM(servlet) FROM student;

– 需求： 查询所有学生servlet的平均分（AVG; 平均函数）

SELECT AVG(servlet) FROM student;

– 需求：查询最高的servlet分数（MAX：最大值函数）

SELECT MAX(servlet) FROM student;

– 需求：查询最低的servlet分数（MIN：最小值函数）

SELECT MIN(servlet) FROM student;

– 需求： 一共几个学生（COUNT： 统计数量函数）

SELECT COUNT(*) FROM student;SELECT COUNT(id) FROM student; -- 效率会比count(*)效率稍高

注意： 聚合函数，会排除null值的数据，根据某一个字段求取学生表中的数据条数,当一个字段数值为null的时候，是不予计算的。

SELECT COUNT(remark2) FROM student;

5.9分组查询

– 求取本班中香港和河北的学生分别有多少人
– 1.给学生使用address这个字段进行分组（group by）
– 2.求取每一组中的学生人数
– address count

SELECT address,COUNT(*) FROM student GROUP BY address;

5.10分组筛选
– 查询人数大于1的地域(group by 分组字段 having 筛选条件)

SELECT address,COUNT(*) FROM student GROUP BY address HAVING COUNT(*)>1;

5.11分页查询
limit 起始行数,查询的条数 注意：起始行数从0开始
– 假设我的表中有20条数据，分为4也显示
– 第一页：limit 0,5
– 第二页：limit 5,5
– 第三页：limit 10,5
– 第四页：limit 15,5
– 结论：查询某一页要现实的数据的时候可以利用如下公式 limit (当前页数-1)*每页显示的条数,每页现实的条数

– student表中目前有4条数据，分为2页显示，每页显示2条
– 查询第一页的显示数据：

SELECT * FROM student LIMIT 0,2;

– 查询第二页现实的数据

SELECT * FROM student LIMIT 2,2;

5.12查询后排序
– 按照id字段的升序进行排序
– asc,升序，数字从小到大，字母a-z
– desc,降序，数字从大到小，字母z-a

SELECT * FROM student ORDER BY id DESC;

– 按照servlet的成绩的降序进行排序

SELECT * FROM student ORDER BY servlet DESC;

– 当有多个排序条件的时候，先按照第一个条件排序，如果第一个条件相同则按照第二个条件进行排序
– 先按照学生的年龄升序进行排序，年龄相同按照学生的servelt成绩的升序进行排序

SELECT * FROM student ORDER BY age ASC,servlet ASC;

6.字段类型：
char(20) vs varchar(20)
– char(20): 固定长度的字符串。不管实际存储的数据的大小，一定占用20个字符空间
– varchar(20): 可变长度的字符串。占用的空间大小就是实际存储的数据大小。

int vs int(4)
– int: 默认最多11位，长度根据实际存储的数值的长度
– int(4): 固定的数值长度

---

数据约束

1.数据约束：给表添加一些数据约束从而可以达到约束用户操作数据的效果。

2.默认值约束（default）
当给这个字段没有添加值的时候，会给一个默认值,如果给默认值约束的字段添加的值为null的时候，那么他的字段值就为null
– 创建一个stu表

CREATE TABLE stu(id INT,NAME VARCHAR(20),

– 给性别gender这个字段添加一个默认值约束

gender VARCHAR(2) DEFAULT '男');

– 给stu表中添加几个数据

INSERT INTO stu VALUES(1,'张八','男');INSERT INTO stu(id,NAME) VALUES(2,'赵九');

– 给stu表中插入数据，性别为null

INSERT INTO stu VALUES(3,'刘十',NULL);

3.非空约束(not null)：插入的字段不为null，而且必须插入数据。

CREATE TABLE stu(-- 给stu表中的id字段添加一个非空约束id INT NOT NULL,NAME VARCHAR(20),gender VARCHAR(2));

– 给这张表中添加一个元素，不插入id字段的值

INSERT INTO stu(NAME,gender) VALUES('郭德纲','男');

– 给这张表添加一条数据，id字段的值直接给成null,这样的话是插入不进去的。

4.唯一约束(unique)
– 给stu表添加一个唯一约束

CREATE TABLE stu(-- 给stu表中的id字段添加唯一约束id INT UNIQUE,NAME VARCHAR(20),gender VARCHAR(2));

– 给表中插入两条id相同的数据

INSERT INTO stu VALUES(1,'刘德华','男');INSERT INTO stu VALUES(1,'张学友','男');-- Duplicate entry '1' for key 'id',无法插入重复的id值

– 给表中插入一条id为null的数据，当给id添加了唯一约束之后，依然可以给他插入多条null值，不会出现重复

INSERT INTO stu VALUES(NULL,'吴奇隆','男');INSERT INTO stu VALUES(NULL,'刘诗诗','女');

5.主键约束(primary key)
经过我们的分析，我们认定我们的这个id字段(唯一+非空)–主键(primary key)
注意：
（1）一般来说我们需要给每一张表都设定一个主键字段(非空+唯一)，用来标示一条信息的唯一性；
（2）我们一般不会将业务字段设定为主键字段，比如name字段，一般我们会给每一张表添加一个id字段作为主键字段；
（3）建议给每张表添加一个主键字段，用来标示每一条数据的唯一性。

CREATE TABLE stu(-- 给stu表中的id字段设置为主键（唯一+非空）id INT PRIMARY KEY,NAME VARCHAR(20),gender VARCHAR(2));

– 给表中插入两条id为1的学生信息

INSERT INTO stu VALUES(1,'华仔','男');INSERT INTO stu VALUES(1,'华建','男');-- Duplicate entry '1' for key 'PRIMARY'

– 给表中插入id为null的元素

INSERT INTO stu VALUES(NULL,'杰伦','男');-- Column 'id' cannot be null

上面的实验我们可以得出结论，当我们给id字段设置了主键约束后，这个id字段就非空+唯一了。

6.自增长约束(auto_increment)
– 给stu表中的id字段添加一个主键自增长约束

CREATE TABLE stu(-- 给stu表中的id字段设置主键自增长约束，我们其实就将id这个字段交给了数据库自己去维护，我们自己不需要去动他id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),gender VARCHAR(2));

– 给stu表中添加两条数据

INSERT INTO stu(NAME,gender) VALUES('华仔','男');INSERT INTO stu(NAME,gender) VALUES('周杰伦','男');INSERT INTO stu(NAME,gender) VALUES('周杰伦','男');INSERT INTO stu(NAME,gender) VALUES('周杰伦','男');

– 删除id为4的数据

DELETE FROM stu WHERE id=4;

– 给表中添加一条数据

INSERT INTO stu(NAME,gender) VALUES('张学友','男');

– delete from 这种删除数据的方式，无法重置自增长的主键

– 删除stu的全表数据

DELETE FROM stu;

– 添加一条数据

INSERT INTO stu(NAME,gender) VALUES('张学友','男');

– 删除全表数据的truncate table 表名 ，删除全表数据，这种删除全表数据的方式可以重置主键

TRUNCATE TABLE stu;-- 给表中添加一条数据INSERT INTO stu(NAME,gender) VALUES('华仔','男');

7.外键约束
（1）外键约束： 约束两种表的情况，例如：员工表中出现部门名称的冗余字段。

（2）如何解决部门名称冗余问题?
答案：独立设计一张部门表，把部门名称放到部门表中，这是员工表只需要关联部门的id即可。

（3） 当插入员工表的部门id的时候，可能会出现不存在的部门id，这是非法数据。

（4）如何防止非法数据的插入？
答案： 这是可以把员工表的部门id设置为外键约束。

（5）当有了外键约束之后，操作数据的顺序如下：
插入数据： 先插入主表的数据，再插入副表数据
修改数据： 先修改主表数据，再修改副表数据
删除数据： 先删除副表数据，再删除主表数据

– 员工表

CREATE TABLE employee(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20),    deptName VARCHAR(20))INSERT INTO employee(NAME,deptName) VALUES('张三','软件开发部');INSERT INTO employee(NAME,deptName) VALUES('李四','软件维护部');INSERT INTO employee(NAME,deptName) VALUES('王五','软件开发部');INSERT INTO employee(NAME,deptName) VALUES('陈六','软件开发部');

– 问题：每次插入员工数据时，部门名称数据会出现重复（冗余）,如果数据出现冗余，那么会浪费数据库存储空间。
– 如何解决部门名称数据冗余的问题? 这时就可以设计一张独立的 部门表，把部门名称放到部门表中。

– 员工表

CREATE TABLE employee(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20),    deptId INT)

– 部门表

CREATE TABLE dept(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20))SELECT * FROM employee;SELECT * FROM dept;INSERT INTO dept(NAME) VALUES('软件开发部');INSERT INTO dept(NAME) VALUES('软件维护部');INSERT INTO employee(NAME,deptId) VALUES('陈六',1);INSERT INTO employee(NAME,deptId) VALUES('王五',1);INSERT INTO employee(NAME,deptId) VALUES('张三',3); 

– 问题：在插入员工表的部门id的时候，插入了不存在的部门id，如何防止这种非法数据的插入，这时就添加外键约束了。

– 添加外键约束（foreign key）
– 需求：deptId字段值来自于dept表的id字段的值，这时可以给deptId字段添加外键约束。
– 员工表（副表： 被别的表约束。外键设置在副表）

CREATE TABLE employee(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20),    deptId INT,    CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)    --          外键名称                     外键字段  参考          )

– 部门表（主表：约束别人的表）

CREATE TABLE dept(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20))SELECT * FROM employee;SELECT * FROM dept;INSERT INTO employee(NAME,deptId) VALUES('陈六',1);INSERT INTO employee(NAME,deptId) VALUES('王五',1);

– 插入数据

INSERT INTO employee(NAME,deptId) VALUES('张三',3); -- 违反外键约束：a foreign key constraint fails 

– 修改数据

UPDATE employee SET deptId=5 WHERE id=2; -- a foreign key constraint fails

– 删除数据

DELETE FROM dept WHERE id=1; -- a foreign key constraint fails

（6）当有了外键之后，应该如何管理数据呢？
– 插入数据： 先插入主表的数据，再插入副表数据
– 修改数据： 先修改主表数据，再修改副表数据

UPDATE employee SET deptId=3 WHERE id=5; UPDATE dept SET id=3 WHERE id=2;

– 删除数据：先删除副表数据，再删除主表数据

DELETE FROM dept WHERE id=3;DELETE FROM employee WHERE deptId=3;

8.级联技术
级联： 当有了外键的时候，我们希望修改或删除数据的时候，修改或删除了主表的数据，同时能够影响副表的数据，这时就可以使用级联。

CREATE TABLE employee(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20),    deptId INT,    -- 添加级联修改： ON UPDATE CASCADE    -- 添加级联删除： ON DELETE CASCADE     CONSTRAINT employee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE     --          外键名称                     外键字段  参考          )