# mysql -uroot -p123456 命令行登陆数据库
SHOW DATABASES; #显示所有数据库
USE mysql; # 打开数据库
SHOW TABLES; # 显示数据库表
CREATE DATABASE IF NOT EXISTS rxzxedu; #创建数据库 IF NOT EXISTS 不存在则创建数据库
DROP DATABASE IF EXISTS rxzxedu; #删除数据库
CREATE DATABASE IF NOT EXISTS rxzx ;
ALTER DATABASE rxzx CHARSET=utf8; # 修改数据库的字符集为utf8格式 --通用字符集
USE mysql; #切换数据库
SELECT * FROM USER; #查询user表的所有信息
SELECT HOST,USER FROM USER; #查询主机和用户信息
#修改root用户的主机地址为% 作用允许局域网的其它用户登陆数据库 需要重新启动mysql服务器
UPDATE USER SET HOST='%' WHERE USER='root'
#远程连接mysql服务器
# new connection 连接名称：自定义，主机名：192.168.0.XX 用户名：root 密码：123456
SHOW DATABASE mysql;
SHOW CHARACTER SET;
SHOW VARIABLES LIKE 'character_set_%'; #查看数据库字符集
SHOW VARIABLES LIKE '%char%'; #查看数据库字符集
# 创建表 语法结构
CREATE TABLE stu(
列名1 数据类型,
列名2 数据类型,
列名3 数据类型,
.....
列名n 数据类型,
)
CREATE TABLE stu(
sid INT PRIMARY KEY AUTO_INCREMENT, #主键约束，规则值唯一，不为空 AUTO_INCREMENT自动加1
sname VARCHAR(30) NOT NULL, # not null 约束规则不为空
age INT(5) UNSIGNED ZEROFILL NOT NULL DEFAULT '00018', #UNSIGNED非负数ZEROFILL用0补齐 DEFAULT默认值
birthday DATE,
gender VARCHAR(5),
cid CHAR(18) UNIQUE #唯一约束
)
SELECT * FROM new_stu;
SHOW TABLES; #显示当前数据库下的所有表
SHOW CREATE TABLE stu; #查看指定表的创建语句
DESC stu; #显示表结构
DROP TABLE IF EXISTS stu; # 删除表结构
ALTER TABLE stu ADD(classname VARCHAR(300)) #修改表结构 增加一列
ALTER TABLE stu ADD(address VARCHAR(200),shenggao DECIMAL(5,2))#修改表结构 增加多列
ALTER TABLE stu DROP cid; #修改表结构，删除字段
ALTER TABLE stu CHANGE gender sex VARCHAR(3) #修改表结构，修改列名和数据类型
ALTER TABLE stu MODIFY sex CHAR(3) #修改列的类型
DESC stu; #显示表结构
ALTER TABLE stu RENAME new_stu ; #修改数据库表名
DESC new_stu;
SELECT * FROM new_stu;
DESC new_stu;
ALTER TABLE new_stu DROP cid;
###插入数据部分
#语法结构
# insert into tablename 4 values(,,,)
# insert into tablename(sid,sname,age) values(2,'aa',33)
SELECT * FROM new_stu;
INSERT INTO new_stu VALUE(1,'张三',20,'1998-2-1','男','c001','beijing');
INSERT INTO new_stu (sname,age,birthday,sex,classname,address)
VALUES('小月',17,'1987-4-1','女','c001','shanghai');
SET auto_increment_increment=2; #设置增量部分
INSERT INTO new_stu(sname,age,sex)VALUES('aa',40,'男');
#插入多条数据
#语法结构
INSERT INTO new_stu(sid,sname,age,birthday,sex,classname,address)
VALUES(4,'赵六',24,'1994-6-1','女','c002','beijing'),
(6,'小小白',22,'1996-7-1','女','c002','beijing')
# 数据更新
# 语法结构： update tablename set 字段名=new value ,字段名2=new value where 条件表达式
UPDATE new_stu SET sname = CONCAT('Hello',sname),birthday='2012-12-12'
WHERE sid = 9; # 注意，如果不加条件，则默认修改表中所有记录
SELECT sname,CONCAT('hello -- ',sname) FROM new_stu;
SELECT * FROM new_stu;
#删除
#语法结构： delete from tablename where 条件
DELETE FROM new_stu WHERE sid=9; # 如果不加条件则默认删除表中所有记录
# 截断表，把表中数据清空，只保留表结构
# 语法结构 truncate table tablename
# 清空表数据之前先备份表
CREATE TABLE new_stu_bak AS SELECT * FROM new_stu
SELECT * FROM new_stu_bak
# 执行截断操作
TRUNCATE TABLE new_stu;
SELECT * FROM new_stu
#数据恢复
INSERT INTO new_stu SELECT * FROM new_stu_bak
# 增加外键约束
CONSTRAINT emp_fk FOREIGN KEY (in_dpt) REFERENCES department(dpt_name)
#创建表的五类约束
# 主键约束： 值唯一，不能为空 primary key
# 唯一约束： 值唯一不能重复 UNIQUE
# 非空约束： 不能为空 not null
# 默认值： 默认数据 default
# 外键约束： 参照父表的主键值 FOREIGN KEY
CREATE TABLE book (
book_id INT PRIMARY KEY ,
book_name VARCHAR(30) NOT NULL,
price INT ,
sid INT,
CONSTRAINT book_sid FOREIGN KEY (sid) REFERENCES new_stu(sid) #定义外键约束
)
SELECT * FROM new_stu
SELECT * FROM book
INSERT INTO book VALUES(1001,'Mysql',40,3),
(1002,'Java',20,2),
(1003,'Python',50,4),
(1004,'Informatica',42,3),
(1005,'Linux',18,1)
SELECT * FROM new_stu,book WHERE new_stu.sid = book.sid
SELECT * FROM book;
ALTER TABLE book MODIFY book_sid RENAME aasid
SHOW CREATE TABLE book;
# 数据查询部分 DQL
# 查询所有字段 * 语法： select * from table_name
SELECT * FROM book;
# 查询指定字段 字段名 语法：select 字段名1，字段名2 .... from table_name
SELECT sid,sname,age FROM new_stu;
# 查询计算，int类型的数据可以做计算，字符串不可以
# 给所有学生的年龄+5 字段是数字类型
SELECT sid,sname,age,age+5 FROM new_stu;
# 字符串的拼接concat
SELECT sid,CONCAT('$ -- ',sname),age FROM new_stu;
SELECT * FROM new_stu;
INSERT INTO new_stu(sname,age,sex)VALUES('小小红',NULL,'女')
DESC new_stu;
# null值和任何数计算其结果都为null 这时，需要将null转换为 0 来计算
SELECT sid,sname,age,IFNULL(age,0)+5 FROM new_stu;
# 去掉重复数据 用 distinct
SELECT DISTINCT sex FROM new_stu;
# 给字段增加别名，重命名 as 可加可不加
SELECT sid AS "学号",sname "姓名",age "年龄",IFNULL(age,0)+5 AS "年龄加5"
FROM new_stu;
# 聚合函数的用法
# SUM(expression) 求和
# AVG(expression) 求平均值
# MAX(expression) 求最大值
# MIN(expression) 求最小值
# COUNT(expression) 统计记录数
# COUNT(DISDINCT COLNAME) 统计去除重复记录数
SELECT * FROM new_stu;
SELECT SUM(age) "年龄总和",
AVG(IFNULL(age,0)) "平均年龄",
MAX(age) "最大年龄",
MIN(age) "最小年龄",
COUNT(sid) "统计记录数",
COUNT(DISTINCT sex) "统计"
FROM new_stu;
SELECT 150-8 FROM DUAL;
# 函数部分
# 字符函数 UPPER 转换为大写
# LOWER 转换为小写
# CONCAT 连接，字符串拼接
# SUBSTR 截取子串
# LENGTH 求长度
# REPLACE 替换
# 数值函数 ROUND 四舍五入 round(456,1) round(456,-2)、MOD 取余数，
# 日期函数 SYSDATE 取当前系统日期时间
SELECT UPPER('hello')up,
LOWER('HELLO')lo,
CONCAT('aa','Hello')co,
SUBSTR('abcdefghijk',2,4)su,
LENGTH('abcdefghi')le,
REPLACE('Hello world','wo','sunzhuan')re,
ROUND(456.63,-2)ro,
MOD(20,6),
SYSDATE()
FROM DUAL;
SELECT SUBSTR('abcdefghijk',2,4)su,
# 条件查询
# 语法结构：select * from table_name where 条件表达式
SELECT * FROM new_stu WHERE sex='女'
# 运算符
# 运算符 含义
# = 等于
# <>或 != 不等于
# > 大于
# < 小于
# >= 大于等于
# <= 小于等于
# BETWEEN … AND … 在某个范围之间
# IS NULL 是否为空
# IS NOT NULL 是否为不空
# LIKE 模式匹配
# IN 等于某个值则为真
SELECT * FROM new_stu WHERE sex<>'女'
SELECT * FROM new_stu WHERE sex!='女'
SELECT * FROM new_stu WHERE age>=22;
SELECT * FROM new_stu WHERE age<=22;
SELECT * FROM new_stu WHERE age BETWEEN 22 AND 24; # age>=22 and age<=24
SELECT * FROM new_stu WHERE age IS NULL;
SELECT * FROM new_stu WHERE age IS NOT NULL;
SELECT * FROM new_stu WHERE sname LIKE '%小%'
SELECT * FROM new_stu WHERE age IN (20,21)
#运算符 含义
# not 取反
# and 并且
# or 或者
SELECT * FROM new_stu WHERE age NOT IN (20,21)
SELECT * FROM new_stu WHERE age=24 AND sex='女' # 同时满足两个条件
SELECT * FROM new_stu WHERE age=24 OR sex='女' # 只需要满足一个条件即可
# 分组操作
# 按性别分组分别统计人数
SELECT * FROM new_stu;
SELECT sex,COUNT(sid),SUM(age) FROM new_stu GROUP BY sex
# 分组之后的条件查询，
# 语法：select 分组字段，统计函数 from table_name group by 字段 havging 条件
# 按性别分组分别统计人数,取总人数大于4人的数据
SELECT sex,COUNT(sid) FROM new_stu GROUP BY sex HAVING COUNT(sid)>4
SELECT * FROM new_stu WHERE COUNT(sid)>4; # 错误
SELECT * FROM new_stu GROUP BY age HAVING COUNT(sid)>4
SELECT age,COUNT(*) FROM new_stu GROUP BY sex HAVING COUNT(*)>2
SELECT * FROM new_stu;
SELECT COUNT(*),COUNT(age),COUNT(1) FROM new_stu;
COUNT(*)--> 4
COUNT(b)--> 2
COUNT(1)--> 4
a b c
1 2 3
2 NULL 4
3 4 4
4 NULL NULL
# 排序 asc 升序，desc 降序 默认为升序
SELECT * FROM new_stu ORDER BY age DESC,sid DESC; # 先执行age排序，age有相同的，则按照第二个字段排序
SELECT sid 学号,sname 姓名,age 年龄,sex 性别,address 地址 FROM new_stu
ORDER BY 年龄 DESC,学号 DESC # 给字段取别名之后排序
SELECT * FROM new_stu ORDER BY sex ASC, birthday DESC;
# 指定行数
# 使用 LIMIT 用来限定查询结果的起始行，以及总行数。
# 语法：limit 起始行（第一行从0开始）, 总行数
SELECT * FROM new_stu LIMIT 2,3
# MySQL转换函数,标量函数
# 日期类型转换成字符类型语法：date_format(date,'%Y-%m-%d')
SELECT SYSDATE(); #查询当前系统日期和时间
SELECT DATE_FORMAT(SYSDATE(),'%Y-%m-%d') AS "yyyy-mm-dd" # %Y 表示4位年份 %m 01-12
SELECT DATE_FORMAT(SYSDATE(),'%y-%M-%d') AS "yyyy-mm-dd" # %y 表示2位年份
SELECT DATE_FORMAT(SYSDATE(),'%y-%M-%d') AS "yyyy-mm-dd" # %M 表示英文月份
SELECT DATE_FORMAT(SYSDATE(),'%y-%m-%D') AS "yyyy-mm-dd" # %D 表示英文天
SELECT DATE_FORMAT(SYSDATE(),'%y-%c-%d') AS "yyyy-mm-dd" # %c 表示1-12
SELECT DATE_FORMAT(SYSDATE(),'%y-%c-%e') AS "yyyy-mm-dd" # %e 表示0-31 %d 00-31
SELECT DATE_FORMAT(SYSDATE(),'%H:%i:%s') AS "HH:mi:ss" # %H 24小时格式 %h 12小时格式 %i： 代表分钟, 格式为(00……59)
# %s：代表秒,格式为(00……59)
SELECT DATE_FORMAT(SYSDATE(),'%k:%i:%s') AS "HH:mi:ss" # %k 代表小时,格式为(0……23)
SELECT DATE_FORMAT(SYSDATE(),'%I:%i:%s') AS "HH:mi:ss" # %I 代表小时,格式为(01……12)
SELECT DATE_FORMAT(SYSDATE(),'%l:%i:%s') AS "HH:mi:ss" # %l 代表小时,格式为(1……12)(小写L)
SELECT DATE_FORMAT(SYSDATE(),'%r') AS "HH:mi:ss" # 代表时间,格式为12 小时(hh:mm:ss [AP]M)
SELECT DATE_FORMAT(SYSDATE(),'%T') AS "HH:mi:ss" # %T：代表时间,格式为24 小时(hh:mm:ss)
SELECT DATE_FORMAT(SYSDATE(),'%Y-%m-%d %H:%i:%s')# 年月日小时分秒
# 将字符串转换为日期
# 语法： str_to_date
SELECT STR_TO_DATE('17-12-12 23:12:12','%y-%m-%d %H:%i:%s')
SELECT STR_TO_DATE('2017-12-12','%Y-%m-%d');
SELECT STR_TO_DATE('2017-12-12 12:12:12','%Y-%m-%d %H:%i:%s');
# 指定日期相减函数：datediff（date1,date2）date1-date2
# 两个日期相减得到天数
SELECT DATEDIFF(NOW(),hiredate) FROM emp;
SELECT NOW(),birthday,DATEDIFF(NOW(),birthday) FROM new_stu;
2018-1998 = 20*365
SELECT 20*365
SELECT * FROM new_stu;
# 指定日期上加减天数、月份、季度、年份
# 年：year 季度：quarter 月：month 周：week 天：day
# hour 小时 minute 分钟 second
# DATE_ADD(date,INTERVAL 1 MONTH)
SELECT DATE_ADD(SYSDATE(),INTERVAL -2 YEAR);
SELECT DATE_ADD(SYSDATE(),INTERVAL 2 QUARTER);
SELECT DATE_ADD(SYSDATE(),INTERVAL -2 MONTH);
SELECT DATE_ADD(SYSDATE(),INTERVAL 1 WEEK);
SELECT DATE_ADD(SYSDATE(),INTERVAL 1 DAY);
SELECT DATE_ADD(SYSDATE(),INTERVAL 1 HOUR);
SELECT DATE_ADD(SYSDATE(),INTERVAL 1 MINUTE);
# 字符转数字语法:CAST (expression AS data_type)
SELECT CAST('123' AS INT); # 转换为整型数据
SELECT CAST('123.23' AS DECIMAL); # 转换为浮点型
# 数字转换成字符串
SELECT CAST(123 AS CHAR(3));
SELECT CAST(123.12 AS CHAR(3));
SELECT LENGTH('23.34')
#rand 取随机数
SELECT ROUND(5+5*RAND()) # 5-10之间产生一个随机数
# 取整:floor 直接取整数,小数位不要
SELECT FLOOR(5+5*RAND());
SELECT FLOOR(234.64)
# 格式化：format
SELECT FORMAT(123456.626,1); #逗号后面，是指定保留小数位 如负数保留整数位
# 当前时间：now()
SELECT NOW();
SELECT SYSDATE();
# 转换大小写：upper、lower
SELECT UPPER('aa')
SELECT LOWER('AAA')
# 字符串：concat、length、char_length
SELECT CHAR_LENGTH('你好中国')
# 提取字符串左右的数据：left、rigth
SELECT LEFT('abcdefghi',3)
SELECT RIGHT('abcdefghi',3)
# 截取字符串：substr、substring
SELECT SUBSTRING('abcdefghi',2,3) # 下标从1开始
# 从后定位开始字符往后截取
SELECT SUBSTR('abcdefghi',-4,3)
SELECT SUBSTRING('中华人民共和国',1,5);
#字符串替换：
SELECT REPLACE('heoll word','word','I love you');
# 四舍五入（Round）
SELECT ROUND(789.536)
# 四舍五入（Round）：指定保留小数位数
SELECT ROUND(789.536,2)
# 四舍五入（Round）：对整数四舍五入
SELECT ROUND(789.536,-2)
# Sutdent表的定义
#字段名 字段描述 数据类型 主键 外键 非空 唯一 自增
#Id 学号 INT(10) 是 否 是 是 是
#Name 姓名 VARCHAR(20) 否 否 是 否 否
#Sex 性别 VARCHAR(4) 否 否 否 否 否
#Birth 出生年份 YEAR 否 否 否 否 否
#Department 院系 VARCHAR(20) 否 否 是 否 否
#Address 家庭住址 VARCHAR(50) 否 否 否 否 否
CREATE TABLE Student(
Id INT(10) PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
Sex VARCHAR(4),
Birth YEAR,
Department VARCHAR(20) NOT NULL,
Address VARCHAR(50)
)
SELECT * FROM Student
INSERT INTO Student VALUES(901,'张老大','男','1985','计算机系','北京市海淀区');
INSERT INTO Student VALUES(902,'张老二','男',1986,'中文系','北京市昌平区');
INSERT INTO Student VALUES(903,'张三','女',1990,'中文系','湖南省永州市');
INSERT INTO Student VALUES(904,'李四','男',1990,'英语系','辽宁省阜新市');
INSERT INTO Student VALUES(905,'王五','女',1991,'英语系','福建省厦门市');
INSERT INTO Student VALUES(906,'王六','男',1988,'计算机系','湖南省衡阳市');
# Score表的定义
#字段名 字段描述 数据类型 主键 外键 非空 唯一 自增
#Id 编号 INT(10) 是 否 是 是 是
#Stu_id 学号 INT(10) 否 是 是 否 否
#C_name 课程名 VARCHAR(20) 否 否 否 否 否
#Grade 分数 INT(10) 否 否 否 否 否
CREATE TABLE Score(
Id INT(10) PRIMARY KEY AUTO_INCREMENT,
Stu_id INT(10),
C_name VARCHAR(20),
Grade INT(10),
CONSTRAINT aa FOREIGN KEY (Stu_id) REFERENCES Student(Id) #定义外键约束
)
ALTER TABLE Score DROP FOREIGN KEY aa
ALTER TABLE Score ADD CONSTRAINT aa FOREIGN KEY (Stu_id) REFERENCES Student(Id)
DESC Score;
INSERT INTO Score VALUES(1,901,'计算机',98);
INSERT INTO Score VALUES(2,901,'英语',80 );
INSERT INTO Score VALUES(3,902,'计算机',65);
INSERT INTO Score VALUES(4,902,'中文',88 );
INSERT INTO Score VALUES(5,903,'中文',95 );
INSERT INTO Score VALUES(6,904,'计算机',70);
INSERT INTO Score VALUES(7,904,'英语',92 );
INSERT INTO Score VALUES(8,905,'英语',94 );
INSERT INTO Score VALUES(9,906,'计算机',90);
INSERT INTO Score VALUES(10,906,'英语',85);
SELECT * FROM Score;
# 3.查询student表的所有记录
SELECT * FROM student;
# 4.查询student表的第2条到4条记录
SELECT * FROM student LIMIT 1,3 #下标从0开始
# 5.从student表查询所有学生的学号（id）、姓名（name）和院系（department）的信息?
SELECT id,NAME,department FROM student;
# 6.从student表中查询计算机系和英语系的学生的信息
SELECT * FROM student WHERE department="计算机系" OR department="英语系"
SELECT * FROM student WHERE department IN ("计算机系","英语系")
# 7.从student表中查询年龄18~22岁的学生信息
DESC student;
SELECT id,NAME,sex,department,address,
DATE_FORMAT(NOW(),'%Y') nowyear,
birth,
DATE_FORMAT(NOW(),'%Y')-birth nowy_birth
FROM student WHERE DATE_FORMAT(NOW(),'%Y')-birth BETWEEN 27 AND 28
# 8.从student表中查询每个院系有多少人?
SELECT department,COUNT(*) FROM student GROUP BY department
# 9.从score表中查询每个科目的最高分
SELECT c_name,MAX(grade) FROM score GROUP BY c_name
# 10.查询李四的考试科目（c_name）和考试成绩（grade）
SELECT c_name,grade FROM student,score
WHERE student.id = score.Stu_id
AND student.name='李四'
SELECT * FROM student,score WHERE student.id=score.Stu_id;
SELECT * FROM score;
# 11.用连接的方式查询所有学生的信息和考试信息
SELECT * FROM student s,score c WHERE s.id = c.stu_id;# 给表取别名student s
# 12.计算每个学生的总成绩
SELECT NAME,SUM(grade) FROM student s,score c
WHERE s.id = c.stu_id
GROUP BY NAME
SELECT * FROM student;
SELECT * FROM score;
# 13.计算每个考试科目的平均成绩
SELECT c_name,AVG(grade) FROM score GROUP BY c_name
SELECT * FROM score ORDER BY c_name
91.5
SELECT (88+95)-2
# 14.查询计算机成绩低于95的学生信息
SELECT * FROM student s,score c WHERE s.id = c.stu_id
AND c_name='计算机' AND grade<95
# 15.查询同时参加计算机和英语考试的学生的信息
SELECT * FROM
(SELECT stu_id,c_name FROM score WHERE c_name='计算机') a,
(SELECT stu_id,c_name FROM score WHERE c_name='英语') b ,student s
WHERE a.stu_id = b.stu_id AND a.stu_id = s.id
901,904,906
SELECT * FROM score;
# 16.将计算机考试成绩按从高到低进行排序
SELECT * FROM score WHERE c_name = '计算机' ORDER BY grade DESC
# 17.从student表和score表中查询出学生的学号，然后合并查询结果
SELECT * FROM student s ,score c WHERE s.id = c.stu_id;
# 18.查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
SELECT s.name,s.Department,c.C_name,c.Grade FROM student s,score c
WHERE s.Id=c.Stu_id
AND NAME LIKE '张%' OR NAME LIKE '王%'
# 19.查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
SELECT NAME,DATE_FORMAT(NOW(),'%Y')-Birth,
department,c.C_name,c.Grade
FROM student s,score c
WHERE s.Id = c.Stu_id
AND address LIKE '%湖南%'
# 连接查询
# 纵向连接：#注意：两个查询语句中，select 查询字段保持一致
# 作用：合并结果集就是把两个select语句的查询结果合并到一起！
# union、union all
# 取年龄大于20岁的
SELECT sid,sname,age FROM new_stu WHERE age>20; # 6条记录
# 取年龄大于21岁
SELECT sid,sname,age FROM new_stu WHERE age>21; # 2条记录
#数据合并结果集用union、union all
SELECT sid,sname,age FROM new_stu WHERE age>20
UNION ALL #UNION ALL 不会去掉重复数据
SELECT sid,sname,age FROM new_stu WHERE age>21;
SELECT sid,sname,age,birthday FROM new_stu WHERE age>20
UNION #UNION 会去掉重复数据
SELECT sid,sname,age,birthday FROM new_stu WHERE age>21;
# 先创建部门表，-----父表
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname VARCHAR(20)
)
INSERT INTO dept VALUES(10,'销售部'),(20,'财务部'),(30,'人事部'),(40,'公关部'),(50,'保洁部')
SELECT * FROM dept;
# 创建员工表 -----子表
CREATE TABLE emp(
empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
job VARCHAR(20),
mgr INT,
hiredate DATE,
sal INT,
comm INT,
deptno INT
)
ALTER TABLE emp ADD CONSTRAINT emp_deptno #给员工表增加外键约束
FOREIGN KEY (deptno) REFERENCES dept(deptno) #创建外键目的，就是检查部门在部门中是否存在，
INSERT INTO emp VALUES (1007,'张飞', '经理', 1010, '2001-09-01', 24500,NULL,10);
INSERT INTO emp VALUES (1009,'曾啊', '董事', NULL, '2001-11-17', 50000, NULL,10);
INSERT INTO emp VALUES (1014,'黄盖', '文员', 1009, '2002-01-23', 13000, NULL,10);
INSERT INTO emp VALUES (1001,'黄飞', '文员', 1007, '2013-01-23', 11000, NULL,10);
INSERT INTO emp VALUES (1002,'周泰', '文员', 1007, '2002-01-23', 5000, NULL,20);
INSERT INTO emp VALUES (1003,'周俞', '大将', 1009, '2002-01-23', 1000, NULL,20);
INSERT INTO emp VALUES (1004,'关羽', '上将', 1010, '2012-01-23', 1400, NULL,20);
INSERT INTO emp VALUES (1005,'马超', '文员', 1010, '2022-01-23', 1900, NULL,20);
INSERT INTO emp VALUES (1006,'吕布', '上将', 1007, '2009-01-23', 2200, NULL,20);
INSERT INTO emp VALUES (1008,'曹操', '文员', 1009, '2002-01-23', 1900, NULL,30);
INSERT INTO emp VALUES (1010,'刘备', '经理', 1009, '2002-01-23', 2200, NULL,30);
DESC emp;
SELECT * FROM emp;
# 连接查询取所需要的字段，deptno两个表中都有，所以加表名区分
SELECT ename,job,sal,dname,emp.deptno
FROM emp ,dept WHERE emp.deptno = dept.deptno;
# 连接查询给表取别名 emp e,dept d
# 方言：SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx=别名2.xx
![在这里插入图片描述]()
SELECT ename,job,sal,dname,e.deptno
FROM emp e,dept d WHERE e.deptno = d.deptno
AND e.ename = '关羽'
# 标准：SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx 内连接
SELECT empno,ename,job,sal,e.deptno,dname
FROM emp e INNER JOIN dept d ON e.deptno = d.deptno #内连接条件用on
# 自然：SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2
SELECT empno,ename,job,sal,e.deptno,dname
FROM emp e NATURAL JOIN dept d #不用加on条件 自动关联两表中相同字段
DROP TABLE table_name #删除表 drop 是删除表结构
DELETE FROM table_name #删除数据，表还存在
DELETE FROM emp;
SELECT * FROM emp;
#自连接 ,同一张表，使用两次，其中有一个字段是关联字段
# 取员工编号，员工姓名，上级领导编号，及上级领导姓名
SELECT e.empno,e.ename,m.empno,m.ename FROM emp e,emp m
WHERE e.mgr = m.empno;
# 不等值连接
# 左连接
selct * FROM R LEFT JOIN S ON R.c=S.c # 左连接
A B R.C S.C D E
1 1 1 1 5 8
2 2 2 2 6 9
3 3 3 NULL NULL NULL
# 右连接
selct * FROM R RIGHT JOIN S ON R.c=S.c
A B R.C S.C D E
1 1 1 1 5 8
2 2 2 2 6 9
NULL NULL NULL 4 7 9
# 取员工表的员工编号，姓名，职位，工资，部门编号，部门名称，
# 将没有员工的部门也显示出来
SELECT * FROM dept;
SELECT empno,ename,job,sal,d.deptno,dname # 左连接
FROM dept d LEFT JOIN emp e ON d.deptno=e.deptno
SELECT empno,ename,job,sal,d.deptno,dname # 左连接
FROM emp e RIGHT JOIN dept d ON d.deptno=e.deptno
#等值连接 两种不同写法
# inner join 内连接
SELECT * FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;
SELECT * FROM emp e JOIN dept d ON e.deptno = d.deptno;
# where
SELECT * FROM emp e,dept d WHERE e.deptno = d.deptno
# 查询刘备的下属 # 自连接
SELECT e.empno,e.ename,m.empno,m.ename FROM emp e,emp m
WHERE e.mgr = m.empno
AND m.ename='刘备'
SELECT a.deptno,empno,ename,dname,sal
FROM emp a,dept b WHERE b.deptno=a.deptno
AND a.ename='张飞'
SELECT * FROM emp ORDER BY sal;
#工资等级
#1000-2000 1
#2000-10000 2
#10000-20000 3
#20000-100000 4
CREATE TABLE SALGRADE # 工资等级表
(
grade INT,
losal INT,
hisal INT
)
INSERT INTO SALGRADE VALUES (1,1000,2000),(2,2000,10000),(3,10000,20000),(4,20000,100000)
SELECT * FROM SALGRADE
# 查询每个员工的工资等级
SELECT empno,ename,job,sal,s.grade FROM emp e,SALGRADE s
WHERE e.sal BETWEEN s.losal AND s.hisal
# case when 条件判断语句两种写法：
# 第一种：case 条件（字段） when 条件的值 then ‘要输出的值’
# when 条件的值 then ‘要输出的值’
# end
# (1,1000,2000), 较差
# (2,2000,10000), 一般
# (3,10000,20000), 优秀
# (4,20000,100000) 高收入
SELECT empno,ename,sal,
CASE WHEN sal>1000 AND sal<2000 THEN '较差'
WHEN sal>2000 AND sal<10000 THEN '一般'
WHEN sal>10000 AND sal<20000 THEN '优秀'
WHEN sal>20000 AND sal<100000 THEN '高收入'
ELSE '差'
END "收入等级"
FROM emp;
SELECT ename AS '姓名',sal AS '工资',
CASE WHEN sal=800 THEN '低'
WHEN sal=3000 THEN '良好'
WHEN sal=5000 THEN '好'
ELSE '一般'
END AS '工资等级'
FROM emp;
# 导入员工表数据
CREATE TABLE EMP_T
(
empno INT NOT NULL,
ename VARCHAR(50),
job VARCHAR(9),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT
)
INSERT INTO emp_t VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20);
INSERT INTO emp_t VALUES (7499,'ALLEN', 'SALESMAN',7698,'1981-02-20',1600.00,330.00,30 );
INSERT INTO emp_t VALUES (7521,'WARD', 'SALESMAN',7698,'1981-02-22',1250.00,500.00,30 );
INSERT INTO emp_t VALUES (7566,'JONES', 'MANAGER',7839,'1981-04-02',2975.00,NULL,20 );
INSERT INTO emp_t VALUES (7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1540.00,30);
INSERT INTO emp_t VALUES (7698,'BLAKE', 'MANAGER',7839,'1981-05-01',2850.00,1140.00,30);
INSERT INTO emp_t VALUES (7782,'CLARK', 'MANAGER',7839,'1981-06-09',2450.00,980.00,10 );
INSERT INTO emp_t VALUES (7788,'SCOTT', 'ANALYST',7566,'1987-04-19',3000.00,NULL,20 );
INSERT INTO emp_t VALUES (7839,'KING', 'PRESIDENT',NULL,'1981-11-17',5000.00,2000.00,10);
INSERT INTO emp_t VALUES (7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,600.00,30 );
INSERT INTO emp_t VALUES (7876,'ADAMS', 'CLERK',7788,'1987-05-23',1100.00,NULL,20 );
INSERT INTO emp_t VALUES (7900,'JAMES', 'CLERK',7698,'1981-12-03',950.00,380.00,30 );
INSERT INTO emp_t VALUES (7902,'FORD', 'ANALYST',7566,'1981-12-03',3000.00,NULL,20 );
INSERT INTO emp_t VALUES (7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,520.00,10 );
SELECT * FROM emp_t
DELETE FROM emp_t
#CLERK 输出： 职员
#SALESMAN 输出： 推销员
#MANAGER 输出： 经理
#ANALYST 输出： 分析师
#PRESIDENT输出： 懂事长
SELECT empno,ename,job,
CASE WHEN job='CLERK' THEN '职员'
WHEN job='SALESMAN' THEN '推销员'
WHEN job='MANAGER' THEN '经理'
ELSE 'aa'
END "职位"
FROM emp_t;
#习题集，创建学生表
CREATE TABLE S
(
Sno CHAR(3) PRIMARY KEY NOT NULL, #学号
Sname CHAR(8) NOT NULL, #学生姓名
Ssex CHAR(2) NOT NULL, #学生性别
Sbirthday DATETIME, #学生出生年月
Class CHAR(5) #学生所在班级
);
# 给学生表插入数据
INSERT INTO S VALUES('108','曾华','男','1997-09-01','95033');
INSERT INTO S VALUES('105','匡明','男','1975-10-02','95031');
INSERT INTO S VALUES('107','王丽','女','1976-01-23','95033');
INSERT INTO S VALUES('101','李军','男','1976-02-20','95033');
INSERT INTO S VALUES('109','王芳','女','1975-02-10','95031');
INSERT INTO S VALUES('103','陆君','男','1974-06-03','95031');
SELECT * FROM s;
# 创建课程表
CREATE TABLE C
(
Cno CHAR(5) PRIMARY KEY NOT NULL, #课程号
Cname VARCHAR(10) NOT NULL, #课程名称
Tno CHAR(3) NOT NULL #教工编号
);
#给课程表插入数据
INSERT INTO C VALUES('3-105','计算机导论','825');
INSERT INTO C VALUES('3-245','操作系统','804');
INSERT INTO C VALUES('6-166','数字电路','856');
INSERT INTO C VALUES('9-888','高等数学','831');
#创建成绩表
CREATE TABLE Sco
(
Sno CHAR(3) NOT NULL REFERENCES Student(Sno), #学号
Cno CHAR(5) NOT NULL REFERENCES Course(Cno), #课程号
Dgree DECIMAL(4,1) #成绩
);
#给成绩插入数据
INSERT INTO Sco VALUES('103','3-245','86');
INSERT INTO Sco VALUES('105','3-245','75');
INSERT INTO Sco VALUES('109','3-245','68');
INSERT INTO Sco VALUES('103','3-105','92');
INSERT INTO Sco VALUES('105','3-105','88');
INSERT INTO Sco VALUES('109','3-105','76');
INSERT INTO Sco VALUES('101','3-105','64');
INSERT INTO Sco VALUES('107','3-105','91');
INSERT INTO Sco VALUES('108','3-105','78');
INSERT INTO Sco VALUES('101','6-166','85');
INSERT INTO Sco VALUES('107','6-166','79');
INSERT INTO Sco VALUES('108','6-166','81');
#创建教师表
CREATE TABLE T
(
Tno CHAR(3) PRIMARY KEY NOT NULL, #教工编号
Tname CHAR(4) NOT NULL, #教工姓名
Tsex CHAR(2) NOT NULL, #教工性别
Tbirthday DATETIME, #教工出生年月
Prof CHAR(6), #职称
Depart CHAR(10) NOT NULL #教工所在部门
);
#给教师表插入数据
INSERT INTO T VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO T VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO T VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO T VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');
SELECT * FROM s; #学生表
SELECT * FROM t; #教师表
SELECT * FROM sco; #成绩表
SELECT * FROM c; #课程表
# 1、查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT sname,ssex,class FROM s;
# 2、查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT depart FROM t;
# 3、查询Student表的所有记录。
SELECT * FROM s;
# 4、查询Score表中成绩在60到80之间的所有记录。
SELECT * FROM sco WHERE dgree BETWEEN 60 AND 80;
# 5、查询Score表中成绩为85，86或88的记录。
SELECT * FROM sco WHERE dgree IN (85,86,88);
SELECT * FROM sco WHERE dgree=85 OR dgree=86 OR dgree=88;
# 6、查询Student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM s WHERE class='95031' OR ssex='女'
# 7、以Class降序查询Student表的所有记录。
SELECT * FROM s ORDER BY class DESC;
# 8、以Cno升序、Degree降序查询Score表的所有记录。
SELECT * FROM sco ORDER BY cno ASC ,dgree DESC;
# 9、查询“95031”班的学生人数。
SELECT COUNT(*) FROM s WHERE class='95031'
SELECT * FROM s;
# 10、查询Score表中的最高分的学生学号和课程号。
SELECT b.sno,b.cno,b.dgree FROM # from 子查询
(SELECT MAX(Dgree)max_dgree FROM sco)a,
(SELECT * FROM sco)b
WHERE a.max_dgree = b.dgree
# 11、查询每门课的平均成绩。
SELECT cno,AVG(dgree) FROM sco GROUP BY cno
# 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
SELECT cno,AVG(dgree) FROM sco WHERE cno LIKE '3%' GROUP BY cno HAVING COUNT(*)>5
SELECT * FROM sco;
# 13、查询分数大于70，小于90的Sno列。
SELECT DISTINCT sno FROM sco WHERE dgree>70 AND dgree<90; # 只要有一门课程符合条件则查询出来
#select * from sco where sno=103 #学生所学的所有课程都符合这个条件
# 14、查询所有学生的Sname、Cno和Degree列。
SELECT s.Sname,sco.Cno,sco.Dgree FROM s ,sco WHERE s.Sno=sco.Sno
# 15、查询所有学生的Sno、Cname和Degree列。
SELECT sco.Sno,c.Cname,sco.Dgree FROM sco,c WHERE sco.Cno=c.Cno
# 16、查询所有学生的Sname、Cname和Degree列。
SELECT s.Sname,c.Cname,sco.Dgree FROM sco ,s,c WHERE sco.Sno=s.Sno AND sco.Cno=c.cno
# 17、查询"95033"班学生的平均分。
SELECT AVG(dgree) FROM sco,s WHERE sco.Sno=s.Sno AND s.Class='95033'
#95033 -- 79.66667
SELECT * FROM s WHERE class='95033' #101,107,108
SELECT SUM(dgree)/6 FROM sco WHERE sno IN (101,107,108)
#创建成绩等级表
CREATE TABLE grade(low INT(3),upp INT(3),rank CHAR(1))
#给成绩等级表插入数据
INSERT INTO grade VALUES(90,100,'A');
INSERT INTO grade VALUES(80,89,'B');
INSERT INTO grade VALUES(70,79,'C');
INSERT INTO grade VALUES(60,69,'D');
INSERT INTO grade VALUES(0,59,'E');
SELECT * FROM grade
# 现查询所有同学的Sno、Cno和rank列。
SELECT sco.Sno,sco.Cno,sco.Dgree,grade.rank FROM sco,grade
WHERE sco.Dgree BETWEEN grade.low AND grade.upp
# 19、查询选修"3-105"课程的成绩高于"109"号同学成绩的所有同学的记录。
SELECT a.*,b.* FROM
(SELECT sno ,cno,dgree FROM sco WHERE cno='3-105')a,
(SELECT sno,cno,dgree FROM sco WHERE cno='3-105' AND sno=109)b #76.0
WHERE a.dgree>b.dgree
![在这里插入图片描述]()
# 20、查询score中选学多门课程的同学中 分数为非最高分成绩的记录。
SELECT b.* FROM
(SELECT sno,MAX(dgree)aa FROM sco GROUP BY sno)a,
(SELECT sno,cno,dgree FROM sco)b
WHERE a.sno = b.sno AND b.dgree NOT IN (a.aa) ORDER BY 1;
# 101 85
# 103 92
# 105 88
# 107 91
# 108 81
# 109 76
INSERT INTO sco VALUES(101,'3-245',79.0)
# 21、查询成绩高于学号为"109"、课程号为"3-105"的成绩的所有记录。
SELECT * FROM
(SELECT * FROM sco WHERE sno=109 AND cno='3-105')a,sco b #76.0
WHERE b.dgree>a.dgree
# 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
SELECT b.Sno,b.Sname,b.Sbirthday FROM
(SELECT DATE_FORMAT(sbirthday,'%Y')yyyy FROM s WHERE sno=101)a,
(SELECT DATE_FORMAT(sbirthday,'%Y')yyyy,sno,sname,sbirthday FROM s)b
WHERE a.yyyy=b.yyyy
# 23、查询"张旭"教师任课的学生成绩。
SELECT t.tno,c.cno,cname,sco.Sno,sco.Dgree
FROM t,c,sco
WHERE t.Tno=c.Tno
AND c.cno = sco.Cno
AND t.tname='张旭'
# 24、查询选修某课程的同学人数多于5人的教师姓名。
SELECT t.Tname FROM sco,c,t
WHERE sco.Cno = c.cno
AND c.Tno=t.Tno
GROUP BY sco.cno HAVING COUNT(sco.Sno)>5
# 25、查询95033班和95031班全体学生的记录。
SELECT * FROM s WHERE class='95033' OR class='95031'
# 26、查询存在有85分以上成绩的课程Cno.
SELECT DISTINCT cno FROM sco WHERE dgree>85
SELECT * FROM sco
# 27、查询出"计算机系"教师所教课程的成绩表。
SELECT * FROM
(SELECT c.* FROM t,c WHERE t.tno=c.tno AND t.depart='计算机系')a,
(SELECT sco.*,c.Tno FROM sco,c WHERE sco.cno=c.cno)b
WHERE a.tno=b.tno
# 28、查询"计算机系"与"电子工程系"不同职称的教师的Tname和Prof。
SELECT DISTINCT tname,prof
FROM t WHERE depart IN ('计算机系','电子工程系')
# 29、查询选修编号为"3-105"课程且成绩至少高于选修编号为"3-245"
# 的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
SELECT a.cno,a.sno,a.dgree FROM
(SELECT * FROM sco WHERE cno='3-105')a,
(SELECT * FROM sco WHERE cno='3-245')b
WHERE a.sno = b.sno
AND a.dgree>b.dgree ORDER BY a.dgree DESC
# 30、查询选修编号为"3-105"且成绩高于选修编号为"3-245"
# 课程的同学的Cno、Sno和Degree.
SELECT a.cno,a.sno,a.dgree FROM
(SELECT * FROM sco WHERE cno='3-105')a,
(SELECT * FROM sco WHERE cno='3-245')b
WHERE a.sno = b.sno
AND a.dgree>b.dgree
# 31、查询所有教师和同学的name、sex和birthday.
SELECT sname,ssex,sbirthday FROM s
UNION ALL #合并数据 UNION ALL 不去重
SELECT tname,tsex,tbirthday FROM t;
# 32、查询所有“女”教师和“女”同学的name、sex和birthday.
SELECT sname,ssex,sbirthday FROM s WHERE ssex = '女'
UNION ALL #合并数据 UNION ALL 不去重
SELECT tname,tsex,tbirthday FROM t WHERE tsex = '女';
# 33、查询成绩比该课程平均成绩低的同学的成绩表。
SELECT b.* FROM
(SELECT cno,AVG(dgree)avg_dgree FROM sco GROUP BY cno)a,sco b
WHERE a.cno = b.cno
AND b.dgree<a.avg_dgree
# 34、查询所有任课教师的Tname和Depart.
SELECT tname,depart FROM t
# 35、查询所有未讲课的教师的Tname和Depart.
SELECT t.tno aa,t.Depart,t.Tname,a.bb FROM
t LEFT JOIN
(SELECT DISTINCT tno bb FROM sco,c WHERE sco.Cno=c.Cno)a
ON t.Tno = a.bb
WHERE a.bb IS NULL
# 36、查询至少有2名男生的班号。
SELECT class FROM s WHERE ssex='男' GROUP BY class
HAVING COUNT(*)>=2
# 37、查询Student表中不姓“王”的同学记录。
SELECT * FROM s WHERE sname NOT LIKE '王%'
# 38、查询Student表中每个学生的姓名和年龄。
SELECT sname,sbirthday ,
DATE_FORMAT(SYSDATE(),'%Y')-DATE_FORMAT(sbirthday,'%Y')age
FROM s;
# 39、查询Student表中最大和最小的Sbirthday日期值。
SELECT MAX(sbirthday),MIN(sbirthday) FROM s;
# 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * FROM s ORDER BY class DESC,sbirthday
# 41、查询“男”教师及其所上的课程。
SELECT c.*,t.Tname FROM t,c
WHERE t.Tno = c.Tno
AND tsex='男'
# 42、查询最高分同学的Sno、Cno和Degree列。
SELECT b.sno,b.cno,b.dgree FROM
(SELECT MAX(Dgree)d FROM sco) a,sco b
WHERE a.d = b.dgree
# 43、查询和“李军”同性别的所有同学的Sname.
SELECT s.Sname,s.Ssex FROM
(SELECT ssex FROM s WHERE sname='李军')a,s
WHERE a.ssex = s.Ssex
# 44、查询和“李军”同性别并同班的同学Sname.
SELECT s.Sname,s.Ssex,s.Class FROM
(SELECT ssex,class FROM s WHERE sname='李军')a,s
WHERE a.ssex = s.Ssex AND a.class = s.Class
# 45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT sco.*,s.Ssex FROM
(SELECT cno FROM c WHERE cname='计算机导论')a,
sco ,s
WHERE a.cno = sco.Cno AND sco.Sno = s.Sno
AND s.Ssex='男'
# 将员工表数据，从oracle 数据库中，取到Mysql数据库
CREATE TABLE EMP
(
EMPNO INT, #员工编号
ENAME VARCHAR(30), #员工姓名
JOB VARCHAR(9), #职位
MGR DECIMAL(4), #上级领导编号
HIREDATE DATE, #入职日期
SAL DECIMAL(7,2), #工资，薪水
COMM DECIMAL(7,2), #奖金
DEPTNO DECIMAL(2) #部门编号
)
SELECT * FROM EMP;
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980/12/17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981/2/20',2200,300,30);
INSERT INTO emp VALUES(7521,'WarD','SALESMAN',7698,'1981/2/22',1250,NULL,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981/4/2',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981/9/28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981/5/1',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981/6/9',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987/4/19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING' ,'PRESIDENT',NULL,'1981/11/17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981/9/8',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987/5/23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981/12/3',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD' ,'ANALYST',7566,'1981/12/3',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982/1/23',1300,NULL,10);
