基础语句
CREATE DATABASE day6;
USE day6;
CREATE TABLE user1(
uid INT(3) PRIMARY KEY AUTO_INCREMENT,
uname VARCHAR(20),
uaddress VARCHAR(50)
);
SHOW TABLES;
--1.修改字段类型
ALTER TABLE user1 MODIFY uname VARCHAR(10);
DESC user1;
--2.修改字段名
ALTER TABLE user1 CHANGE uname unames VARCHAR(20);
ALTER TABLE user1 CHANGE uaddress uaddr VARCHAR(20);
DESC user1;
--3.修表名
RENAME TABLE user1 TO user2;
SHOW TABLES;
RENAME TABLE user2 TO user1;
--4.添加数据
INSERT INTO user1(uid,unames,uaddr) VALUES(1,'杨蒙蒙','商丘'),
(NULL,'韩高峰','周口');
INSERT INTO user1 VALUES (NULL,'胡歌','北京');
SELECT *FROM user1;
--5.删除数据 不能是主键
DELETE FROM user1 WHERE unames ='胡歌';
--6.添加字段
ALTER TABLE user1 ADD age VARCHAR(100);
--7.删除字段
ALTER TABLE user1 DROP age;
ALTER TABLE user1 ADD uage VARCHAR(100);
--8.修改字段类型
ALTER TABLE user1 MODIFY uage INT(100);
SELECT *FROM user1;
DESC user1;
INSERT INTO user1(uage) VALUES(18),(23);
DELETE FROM user1 WHERE uage=18 OR uage=23 OR uage=35;
SELECT *FROM user1;
DELETE FROM user1 WHERE uage=18 OR uage=23;
--9.更新数据
UPDATE user1 SET uage =18 WHERE uid=1;
UPDATE user1 SET uage =23 WHERE uid=2;
SELECT*FROM user1;
INSERT INTO user1(uid,unames,uaddr,uage) VALUES
(3,'唐三','唐门',20),
(NULL,'大师','史莱克',40),
(5,'小舞','学校',15);
UPDATE user1 SET uid = 4 WHERE unames='大师';
--10.查询...之间
SELECT*FROM user1 WHERE uage>=20;
SELECT*FROM user1 WHERE uage>=18 AND uage<=38;
--11.in关键字 在...里
SELECT*FROM user1 WHERE uid IN (1,3);
--12.LIKE查询五个的
SELECT*FROM user1 WHERE unames LIKE('%大%');
--13.排序
SELECT*FROM user1 ORDER BY uage ASC;
SELECT*FROM user1 ORDER BY uage DESC;
--14.聚合函数
--15.查询表的总行数
SELECT COUNT(*) AS '总行数' FROM user1;
--16.求某个字段的值得总和
SELECT SUM(uage) AS '年龄总和' FROM user1;
--17.like求和
SELECT SUM(uage) FROM user1 WHERE uage='%2%';
--18.获取最值 usnames 与AS unames相同
select oname as '姓名',max(oage) as'最大值' from ones where oage=(select max(oage) from ones);
select oname as '姓名',min(oage) as'最小值' from ones where oage=(select min(oage) from ones);
SELECT *FROM user1;
--19.计算一个字段的平均值
SELECT AVG(uage) FROM user1;
--20.分组
SELECT SUM(uage),unames FROM user1 GROUP BY unames;
SELECT unames,uid,SUM(uage) FROM user1 GROUP BY unames;
--21.Like分组
SELECT SUM(uage) AS '总年龄',unames FROM user1 WHERE unames LIKE '%爸%' GROUP BY unames ORDER BY '总年龄' ASC;
--21.having
SELECT SUM(uage) AS '总年龄', unames FROM user1 WHERE unames LIKE '%爸%' GROUP BY unames HAVING 总年龄>50;
having vs where
having是在分组后对数据进行过滤
where是在分组前对数据进行过滤
having 后面可以使用分组函数
where后面不允许使用分组函数
======================================================================================
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY, -- 员工id
ename VARCHAR(50), -- 员工姓名
job_id INT, -- 职务id
mgr INT , -- 上级领导
joindate DATE, -- 入职日期
salary DECIMAL(7,2), -- 工资
bonus DECIMAL(7,2), -- 奖金
dept_id INT, -- 所在部门编号
CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES
(1001,'孙悟空',4,1004,'2018-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2011-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2019-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2018-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2019-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2018-5-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2017-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2012-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2016-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2018-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2009-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2007-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2005-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2018-01-23','13000.00',NULL,10);
-- 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);
-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);