MYSQL数据库
-- sql注释
-- sql:结构化查询语言
-- 数据定义语言（ddl） 操作数据库，操作表（表结构而非数据）
-- 查看数据库 创建数据库 修改数据库 删除数据库
-- 查看字符集 6处
SHOW VARIABLES LIKE 'character%';
-- 查看当前使用的数据库
-- select database()
SELECT DATABASE();
SHOW DATABASES;-- 查看数据库服务器中的所有数据库
-- 查看数据库的创建信息
-- SHOW CREATE DATABASE 数据库名
SHOW CREATE DATABASE test4;
-- 切换数据库
-- use 数据库名
USE test5;
-- create database [IF NOT EXISTS] 数据库名 [character set 编码]
CREATE DATABASE test4;-- 创建
CREATE DATABASE test4;-- 报错
CREATE DATABASE IF NOT EXISTS test4;-- 增强健壮性 报警告
CREATE DATABASE IF NOT EXISTS test5 CHARACTER SET utf8;-- 指定数据库的字符集
-- alter database 数据库名 character set 字符编码集
ALTER DATABASE test5 CHARACTER SET gbk;
-- 删除数据库
-- DROP DATABASE [IF EXISTS] 数据库名
DROP DATABASE test4;-- 没有 报错
DROP DATABASE IF EXISTS test4;
-- -------------------------------
-- 表结构 增删改查
-- 创建表结构
/*
create table 表名（
字段名 数据类型 [约束条件 约束条件]，
字段名 数据类型 [约束条件 约束条件]，
字段名 数据类型 [约束条件 约束条件]
。。。
）character set 字符集 ；
*/
CREATE TABLE student(
id INT(10),
NAME VARCHAR(10)
)CHARACTER SET utf8;
CREATE TABLE person(
pid INT(10) PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(10) NOT NULL
)CHARACTER SET utf8;
/*
约束条件：
主键约束(非空，不重复)
primary key
非空约束
not null
自增
auto_increment
唯一性约束
unique
*/
-- 查看当前在的数据库中有那些表
-- show tables
SHOW TABLES;
-- 查看表结构
-- desc 表名
DESC person_01;
-- 修改表结构 增删改查字段
-- 修改表名
-- rename table 表名 to 新表名
RENAME TABLE person TO person_01;
-- 添加字段
-- alter table 表名 add 字段名 数据类型
ALTER TABLE person ADD psex VARCHAR(2);
-- 删除字段
-- alter table 表名 drop 字段名
ALTER TABLE person DROP pname;
-- 修改表的字符集
-- alter table 表名 character set 新字符集
ALTER TABLE person_01 CHARACTER SET gbk;
-- 修改字段名称
-- alter table 表名 change 字段名 新字段名 字段类型
ALTER TABLE person_01 CHANGE psex sex VARCHAR(4);
-- 修改字段类型
-- alter table 表名 modify 字段名 字段新类型
ALTER TABLE person_01 MODIFY sex INT(3);
-- 删除表结构
-- DROP TABLE 表名
DROP TABLE person_01;
-- ---------------------------------
CREATE DATABASE test03;
SHOW DATABASES;
SELECT DATABASE();
USE test03;
-- 创建表结构 student s_id s_name s_sex s_age
CREATE TABLE student(
s_id INT(4),
s_name VARCHAR(10),
s_sex CHAR(2),
s_age INT(2)
);
-- DML 数据操作语言 对数据进行添加 修改 删除操作
/*
INSERT INTO 表名 [(字段名 [, 字段名...])]
VALUE (值 [, 值...])
*/
-- insert into student value(001); -- 报错 原因：插入数据时没有指定字段名，默认的是给所有字段添加数据
INSERT INTO student VALUE(001,'zhangsan','男',23);
SELECT * FROM student;
INSERT INTO student(s_id,s_name) VALUE(002,'lisi');
/*
批量插入数据：
INSERT INTO 表名 [(字段名 [, 字段名...])]
VALUES
(值 [, 值...])，
(值 [, 值...])，
(值 [, 值...])，
。。。
;
*/
INSERT INTO student VALUES
(01,"zhang",'女',34),
(02,"wang",'女',21),
(03,"li",'女',12);
SELECT * FROM student;
INSERT INTO student(s_age,s_name) VALUES
(11,"张斌"),
(12,"alice"),
(13,"allen");
SELECT * FROM student;
-- 修改数据
-- update 表名 set 字段名=新值 [WHERE 条件表达式]
UPDATE student SET s_sex="女";
UPDATE student SET s_sex="男" WHERE s_age=23;
UPDATE student SET s_age=18 WHERE s_sex="女";
-- 删除数据（删记录）
-- delete from 表名 [WHERE 条件表达式];
DELETE FROM student;-- 删除所有数据 （清空表数据）
SELECT * FROM student;
DELETE FROM student WHERE s_id=1;
-- truncate table 表名;
TRUNCATE TABLE student;-- 删除所有数据 （清空表数据）
/*
delete和truncate区别：
1、delete 一条记录一条记录删除，不会删除表结构
如果表中的数据量过大时不推荐使用。效率低下。
2、truncate 先删除表结构，然后再新建表结构，如果
表中的数据量很大，推荐使用。效率高。
*/
-- DQL 数据查询语言 （重点）
/*
SELECT [DISTINCT] * 或 字段名1,
字段名2，字段名3，...
FROM 表名 [where 条件表达式]
*/
SELECT * FROM student;-- 查询表中的所有字段的所有数据
INSERT INTO student VALUES
(1,"zhangsan","女",34),
(1,"zhangsan","女",23),
(1,"zhangsan","男",34);
SELECT s_id,s_name FROM student;-- 6 -- 查询表中的指定字段的所有数据
SELECT DISTINCT s_id,s_name FROM student;-- 4 -- 查询表中的指定字段的去除重复的数据
SELECT DISTINCT s_id,s_name FROM student WHERE s_name="zhang";
-- select "zhangsan" from student;
-- 在查询的字段名后面跟as 关键字 取别名，as可以省略
SELECT s_id AS "编号",s_name AS "姓名", s_sex AS "性别",s_age AS "年龄" FROM student;
SELECT s_id "编号xxx",s_name "姓名xx", s_sex AS "性别",s_age AS "年龄" FROM student;
-- --------------------------------------------
-- 条件查询 where 条件
-- 准备表和数据
CREATE TABLE t_grade(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
birthday DATE,
chinese FLOAT,
math FLOAT,
english FLOAT
);
USE test03;
DESC t_grade;
SELECT * FROM t_grade;
-- 添加数据
INSERT INTO t_grade(NAME,birthday,chinese,math,english)
VALUES('张三','1990-01-01',85.5,75,60),
('刘德华','1990-01-01',61,75,85),
('王钊','1980-01-01',88,80,66),
('周瑞发','1990-01-01',85.5,75,60),
('张三丰','1900-01-01',88,72,55),
('张翠山','1920-01-01',86,73.5,64),
('张无忌','1950-01-01',83.5,75,32),
('张小翠','1950-01-01',83.5,75,32),
('小翠','1950-01-01',83.5,75,32),
('翠','1950-01-01',83.5,75,32);
-- 1、查询英语分数在 80－90之间的同学。
SELECT * FROM t_grade WHERE english>=80 AND english<=90;
-- 2、查询英语为85,66,91的同学
SELECT * FROM t_grade WHERE english IN(85,66,91);
-- 查询所有姓张的学生 like '张%' %：匹配0-n个字符 _：1个字符
SELECT * FROM t_grade WHERE NAME LIKE "张%";
INSERT INTO t_grade(NAME) VALUE("张");
-- 查询所有姓张的且名字只有2个字的同学
SELECT * FROM t_grade WHERE NAME LIKE "张_";
-- 查询名字中带翠的学生
SELECT * FROM t_grade WHERE NAME LIKE "%翠%";
-- 3、查询所有姓张的学生成绩。
SELECT NAME,chinese,math,english FROM t_grade WHERE NAME LIKE "张%";
-- 4.查询没有英语成绩的人
SELECT * FROM t_grade;
SELECT * FROM t_grade WHERE english=NULL;-- 没有数据的null关键字不包含,"null"也不包含
SELECT * FROM t_grade WHERE english IS NULL;-- 不包含“null”
SELECT * FROM t_grade WHERE english="null";
-- 修改字段的数据类型
ALTER TABLE t_grade MODIFY english VARCHAR(10);
DESC t_grade;
INSERT INTO t_grade(english)VALUE("null");
-- 5.查询没有英语成绩且姓王的人 且：and
SELECT * FROM t_grade WHERE english IS NULL AND NAME LIKE "王%";
INSERT INTO t_grade(NAME)VALUE("王");
-- 6.查询英语成绩为null 或 姓张的人 或：or
SELECT * FROM t_grade WHERE english IS NULL OR NAME LIKE "张%";
-- 7.查询成绩不及格的同学。 三科只要一个不集合就算不合格
SELECT * FROM t_grade WHERE english<60 OR math<60 OR chinese<60;
-- 8.查询名字叫王钊的人
SELECT * FROM t_grade WHERE NAME="王钊";
SELECT * FROM t_grade WHERE NAME LIKE "王钊";
-- 9.查询名字不叫王钊的人
SELECT * FROM t_grade WHERE NAME != "王钊";
SELECT * FROM t_grade WHERE NAME <> "王钊";
SELECT * FROM t_grade WHERE NAME NOT LIKE "王钊";
-- 10.查询英语成绩不大于60的人
-- <=60
SELECT * FROM t_grade WHERE english<=60;
-- not in(>60)
-- 查询英语成绩大于60的人 子查询
SELECT * FROM t_grade WHERE english >60; -- 所有字段表
SELECT * FROM t_grade WHERE english NOT IN (85,66,64);
SELECT * FROM t_grade WHERE english NOT IN (SELECT english FROM t_grade WHERE english>60);
SELECT * FROM t_grade WHERE NOT(english > 60);
-- ---------------------------------------
-- 排序
-- order by 排序的字段名 asc/desc
-- asc(默认 主键升序)
-- desc（降序）
USE test03;
SELECT * FROM t_grade; -- 查询所有数据 按自然顺序 主键：升序
-- 按语文成绩升序排
SELECT chinese FROM t_grade ORDER BY chinese DESC; -- null:最小
SELECT english FROM t_grade ORDER BY english DESC;-- "null":做大 null:最小
SELECT math,english FROM t_grade ORDER BY english DESC;
/*
按照英语成绩降序输出，如果英语相同，
再按照数学降序，如果数学相同，再按语文降序
*/
SELECT english,math,chinese FROM t_grade ORDER BY english DESC,math DESC,chinese DESC;
-- 聚合函数（mysql提供的函数，直接使用）
-- Count(字段名) 返回对应列的总数
-- 1.统计表中的人数
SELECT * FROM t_grade;
SELECT COUNT(id) AS "总人数" FROM t_grade;-- 13
SELECT COUNT(math) FROM t_grade;-- 10
SELECT COUNT(*) FROM t_grade;-- 13 根据个数最多的字段统计
SELECT COUNT(math,english) FROM t_grade;-- 报错
-- sum(字段名) 返回 对应列的数据之和
-- 2.求表中语文成绩的总和
SELECT SUM(english) FROM t_grade;-- 518 null ， "null"不包括
-- avg(字段名) 返回 对应列的数据的平均值
-- 3.求英语成绩的平均值
SELECT AVG(english) FROM t_grade;-- 47.090909
SELECT SUM(english)/COUNT(english) AS "平均成绩" FROM t_grade;-- 47.090909
-- 实际让null “null”也参与
SELECT AVG(IFNULL(english,0)) FROM t_grade;-- 39.846
-- max(字段名) 返回参数字段中数据最大的那个数据
-- 求语文成绩最高分
SELECT MAX(chinese) FROM t_grade;
-- min(字段名) 返回参数字段中数据最小的那个数据
SELECT MIN(chinese) '语文最低分' FROM t_grade;-- 61 不包含null,"null"
-- -------------------------------------
-- 分组
-- group by 字段名 having 条件
-- 1.统计每个班的人数
SELECT * FROM t_grade;
-- 添加字段class ddl 对表结构的修改操作
ALTER TABLE t_grade ADD class VARCHAR(10);
SELECT COUNT(*) FROM t_grade ; -- 13
SELECT class, COUNT(*) FROM t_grade GROUP BY class;-- 4组
SELECT class,COUNT(id) FROM t_grade GROUP BY class;
SELECT id,class,COUNT(NAME)FROM t_grade GROUP BY class;-- 11 1 6 4
SELECT * FROM t_grade;
/*
注意：
在group by 语句中select/having中出现的字段只能是group by 的分组字段
如果出现其他字段那么这些字段只能放在聚合函数中
使用having 子句 对分组结果进行过滤
*/
-- 3.统计每个班数学总分大于300的 班级
-- 查询每个班的数学总分和班级名称
SELECT class, SUM(math) FROM t_grade GROUP BY class; -- 4
-- where 分组前条件
-- having 分组后条件
SELECT class, SUM(math) FROM t_grade GROUP BY class HAVING SUM(math)>300;
SELECT class, SUM(math) AS "数学总分" FROM t_grade GROUP BY class HAVING "数学总分">300;-- 不行
-- 4.每个人的总分排名 --
SELECT (IFNULL(math,0)+IFNULL(english,0)+IFNULL(chinese,0)) AS "个人总分" FROM t_grade GROUP BY id ORDER BY (IFNULL(math,0)+IFNULL(english,0)+IFNULL(chinese,0)) DESC;
-- 备份
/*
cmd
mysqldump -u用户名 -p 数据库名 > 盘符：/文件名.sql
*/
DROP DATABASE test03;
mysqldump -uroot -proot test03 > d:/test04.sql
-- 还原数据 :先连接数据库
-- 注意：事先要先创建文件名(数据库名)对应的数据库，使用当前数据库
-- source 盘符：/文件名.sql
USE test03;
source d:/test03.sql
SHOW TABLES;
SELECT DATABASE();
-- -----------------------
-- 多表设计和操作
-- 外键--------foreign key
-- 关联外键的---references
-- 添加一个外键：
create table 表名(
字段名 数据类型 [约束条件 约束条件]，
字段名 数据类型 [约束条件 约束条件]
);
create table 表名(
字段名 数据类型 [约束条件 约束条件]，
字段名 数据类型 [约束条件 约束条件]
字段名 数据类型 [约束条件],foreign key（字段名）references 上一个表的表名（字段名）
);
-- 1:1 1:n n:m
-- 1:1 person card 给后出现的表加外键 需要参照先出现的表的主键
-- 外键：字段 需要参照别的表的主键
CREATE TABLE person(-- 2
p_id INT PRIMARY KEY AUTO_INCREMENT,
p_name VARCHAR(10)
);
CREATE TABLE card(-- 3+1
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_num VARCHAR(10),-- 身份证号
c_address VARCHAR(40),
c_waijian INT,-- 外键 (外键名可以和先出现表的主键名相同也可以不同，但是数据类型必须相同)
FOREIGN KEY(c_waijian) REFERENCES person(p_id)
);
DROP TABLE person;
-- 1:n 关系：外键 加在多方表上
-- class student
CREATE TABLE class( -- 2
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(10)
);
CREATE TABLE student(-- 3+1
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(10),
s_sex CHAR(2),
c_id INT,-- 外键
FOREIGN KEY(c_id) REFERENCES class(c_id)
);
-- n:m 关系：关系表：关系表的主键字段，2个外键
-- teacher students tea_stu
CREATE TABLE teachers(-- 2
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(10)
);
CREATE TABLE students(-- 2
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(10)
);
-- 关系表 （中间表）
CREATE TABLE tea_stu(-- 1
ts_id INT PRIMARY KEY AUTO_INCREMENT,
t_id INT,-- 外键
s_id INT,-- 外键
FOREIGN KEY(t_id) REFERENCES teachers(t_id),
FOREIGN KEY(s_id) REFERENCES students(s_id)
);
SHOW TABLES;
-- -----------------------------------
-- 多表的操作
-- 联合查询 union
SELECT * FROM person;
SELECT * FROM card;
INSERT INTO person(p_name)VALUES
("zhangsan"),-- 1 zhangsan
("lisi");-- 2 lisi
INSERT INTO card(c_num,c_address,c_waijian)VALUES
("0x1234","济南",1),
("0x3455","北京",2);
SELECT * FROM card;
SELECT * FROM person,card;-- 结果集：笛卡尔积 包含没有意义的数据
-- person :2 card:4
INSERT INTO teachers(t_name)VALUES
("张老师"),
("王老师");
INSERT INTO students(s_name)VALUES
("张学生"),
("王学生");
(SELECT * FROM teachers) UNION (SELECT * FROM students);
-- 连接查询 从笛卡尔积过滤有意义的数据
-- 内连接 外连接：左外连接 右外连接
-- inner join .... on ....
-- 查有意义的数据：2
SELECT * FROM card,person;-- 笛卡尔积 2 4
SELECT * FROM card,person WHERE person.`p_id`=card.`c_waijian`;
SELECT * FROM card INNER JOIN person ON person.`p_id`=card.`c_waijian`;
-- 左外连接:以左边表为主，右边表数据可以为空
-- left [outer] join .... on ....
-- 右外连接：以右边表为主，左边表数据可以为空
-- right [outer] join .... on ....
USE test003;
SELECT * FROM teacher;
SELECT * FROM student;
SELECT * FROM teacher,student;-- 笛卡尔积：16 2条有意义
SELECT * FROM teacher LEFT OUTER JOIN student ON teacher.`t_id`=student.`t_id`;-- 4 2条有意义 以teacher表为主
SELECT * FROM teacher RIGHT OUTER JOIN student ON teacher.`t_id`=student.`t_id`;-- 以student 为主 4 2条有意义
-- 子查询
-- select * from 表 -- 结果集：全表
-- select 字段1，字段2 from 表 -- 结果集：自定字段对应的表
/*
select 字段1 from
（select 字段1，字段2 from 表）；
*/
SELECT * FROM teacher;
SELECT * FROM student;
-- 查询张老师带的所有学生信息
SELECT * FROM student WHERE tid=
(SELECT tid FROM teacher WHERE tname="张老师");
-- 查张老师的tid
SELECT tid FROM teacher WHERE tname="张老师";-- 结果：表 1
-- limit关键字 取前几条数据
SELECT * FROM teacher LIMIT 2;
浙公网安备 33010602011771号