数据库操作语句2

CREATE DATABASE wzg_db02

#创建表
CREATE TABLE `goods`(
	id INT,
	goods_name VARCHAR(32),
	price DOUBLE);
INSERT INTO goods VALUES(01,'多兰剑',450);
INSERT INTO goods VALUES(02,'多兰盾',450);

SELECT * FROM goods

#修改表,如果后面没有where就是修改整个列
UPDATE goods SET price = 500
UPDATE goods SET price = 450 WHERE id = 01
#增加(其他操作只需要改运算符就行了)，set可以修改多个字段，逗号隔开
UPDATE goods SET  price = price + 50, goods_name = '多兰戒' WHERE id = 01

#删除行
DELETE FROM goods WHERE id = 01

#****创建新的表(student)********
CREATE TABLE student(
	id INT NOT NULL DEFAULT 1,
	`name` VARCHAR(20) NOT NULL DEFAULT '',
	chinese FLOAT NOT NULL DEFAULT 0.0,
	english FLOAT NOT NULL DEFAULT 0.0,
	math FLOAT NOT NULL DEFAULT 0.0
);

INSERT INTO student(id,NAME,chinese,english,math) VALUES(1,'韩顺平',89,78,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(2,'张飞',67,98,56);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(3,'宋江',87,78,77);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(4,'关羽',88,98,90);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(5,'赵云',82,84,67);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(6,'欧阳锋',55,85,45);
INSERT INTO student(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30);

SELECT *FROM student

#查询指定的信息
SELECT `name`,english FROM student;

#去重,带*作用于全部
SELECT DISTINCT * FROM student

#统计总分
SELECT `name`,(chinese+english+math) FROM student

#使用别名表示列
SELECT `name` AS '名字',(chinese+english+math) AS score FROM student

#查询指定学生
SELECT * FROM student WHERE `name`='赵云'

#查询英语成绩大于语文成绩并且属性大于60的人，姓“关”的人,like是模糊查询，关%是指以关开头
SELECT * FROM student WHERE english>chinese AND math>60 AND `name` LIKE '关%'

#查询英语成绩80-90之间的人,或者数学等于90的人,或者姓李的人
SELECT * FROM student WHERE english>80 AND english<90 OR math=90 OR `name` LIKE '宋%'

#对数学成绩升序输出
SELECT *FROM student ORDER BY math

#对总分进行降序输出
SELECT `name`, (chinese+english+math) AS score FROM student ORDER BY score DESC

#对姓李的同学升序输出
SELECT *FROM student WHERE `name` LIKE '关%' ORDER BY math

#统计该表中有多少条记录
SELECT COUNT(*) FROM student

#统计数学成绩大于70的学生
SELECT COUNT(*) FROM student WHERE math>70

#count(*)和count(列)的区别
#count(*)返回满足条件的记录的行数，包括null但count(列)会排除null

#统计一个班级的数学和英语总成绩
SELECT SUM(math),SUM(english) FROM student

#统计一个班的语文平均分
SELECT SUM(chinese)/COUNT(chinese) FROM student
SELECT AVG(chinese) FROM student

#查询数学最高分和最低分
SELECT MAX(math),MIN(math) FROM student

#group by+having
CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, 
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
);
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'), 
                (20, 'RESEARCH', 'DALLAS'), (30, 'SALES', 'CHICAGO'), (40, 'OPERATIONS', 'BOSTON');
SELECT *FROM dept


#创建表EMP雇员
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED ,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) ,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
);
 
 INSERT INTO emp VALUES(7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 800.00,NULL , 20), 
(7499, 'ALLEN', 'SALESMAN', 7698, '1991-2-20', 1600.00, 300.00, 30),  
(7521, 'WARD', 'SALESMAN', 7698, '1991-2-22', 1250.00, 500.00, 30),  
(7566, 'JONES', 'MANAGER', 7839, '1991-4-2', 2975.00,NULL,20),  
(7654, 'MARTIN', 'SALESMAN', 7698, '1991-9-28',1250.00,1400.00,30),  
(7698, 'BLAKE','MANAGER', 7839,'1991-5-1', 2850.00,NULL,30),  
(7782, 'CLARK','MANAGER', 7839, '1991-6-9',2450.00,NULL,10),  
(7788, 'SCOTT','ANALYST',7566, '1997-4-19',3000.00,NULL,20),  
(7839, 'KING','PRESIDENT',NULL,'1991-11-17',5000.00,NULL,10),  
(7844, 'TURNER', 'SALESMAN',7698, '1991-9-8', 1500.00, NULL,30),  
(7900, 'JAMES','CLERK',7698, '1991-12-3',950.00,NULL,30),  
(7902, 'FORD', 'ANALYST',7566,'1991-12-3',3000.00, NULL,20),  
(7934,'MILLER','CLERK',7782,'1992-1-23', 1300.00, NULL,10);
SELECT *FROM emp

#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*工资级别*/
losal DECIMAL(17,2)  NOT NULL,/*该级别最低工资*/
hisal DECIMAL(17,2)  NOT NULL/*该级别最高工资*/
);

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
SELECT *FROM salgrade

#分组查询各个部门的平均薪水和最大薪水
SELECT AVG(sal),MAX(sal) deptno FROM emp GROUP BY deptno

#分组查询每个部门每个岗位的平均工资和最低工资
SELECT AVG(sal),MIN(sal),deptno,job FROM emp GROUP BY deptno,job

#分组查询平均工资低于2000的部门号和平均工资,having相当于在GROUP BY后面又附加了一个条件
SELECT AVG(sal) AS a_vg,deptno FROM emp GROUP BY deptno HAVING a_vg<2000

#返回字符串的字符集
SELECT CHARSET (ename) FROM emp

#连接子串将多列和为一列
SELECT CONCAT(ename,' 工作是 ', job ) FROM emp

#返回后字符串在前字符串中出现的位置,DUAL是系统自带的表(亚元表)用于测试
SELECT INSTR('wzg','z') FROM DUAL

#转换大写
SELECT UCASE (ename) FROM emp

#转换小写
SELECT LCASE (ename) FROM emp

#从左边或右边取出指定长度的字符
SELECT LEFT(ename,2)FROM emp

#获取指定字符串的长度(按字节返回)
SELECT LENGTH (ename) FROM emp

#替换字符串
SELECT ename,REPLACE(job,'MANAGER','经理') FROM emp

#比较字符串长度，按照字节
SELECT STRCMP('w','z') FROM DUAL

#取出某个字符中指定长度的字符,从1开始
SELECT SUBSTRING(ename , 1 , 2) FROM emp

#去除空格,ltrim 左边 rtrim 右边 trim 两边
SELECT LTRIM('  wzg') FROM DUAL

#首字母小写显示所有员工的名字，函数嵌套
SELECT CONCAT(LCASE(SUBSTRING(ename , 1 , 1)),SUBSTRING(ename , 2)) FROM emp

#数学相关函数

#ABS(绝对值），BIN(十进制转二进制)，CELLING(向上取整)，FLOOR(向下取整)
#CONV(进制转换 8是10进制转成2进制),FORMAT(保留小数位)四舍五入,
SELECT CONV(8,10,2)FROM DUAL
#LEAST(求最小值),MOD(求余)
SELECT LEAST(0,1,-2,3) FROM DUAL
#rand()产生随机数，如果()未设置种子他就会随机产生0-1.0之间的数,设置种子后就不会变了，种子变才会变
SELECT RAND()FROM DUAL