SQL - CURD 示例

创建数据库 2 CREATE DATABASE guyu; 3 4 5 进入数据库 6 USE guyu; 7 8 9 创建表格名:emp 10 CREATE TABLE emp ( 11 empno INT PRIMARY KEY auto_increment, 12 ename VARCHAR (20), 13 job VARCHAR (30), 14 salary NUMERIC (7, 2), 15 bonus NUMERIC (7, 2), 16 mgr INT, 17 hiredate date, 18 deptno INT 19 ); 20 21 22 --查询该表字段信息 23 DESC emp; 24 25 26 导入数据 27 INSERT INTO emp VALUES(1001,‘rose‘,‘analyst‘,15000,5000,null,‘2010-2-2‘,10); 28 INSERT INTO emp VALUES(1002,‘tom‘,‘analyst‘,10000,3000,1001,‘2010-3-5‘,10); 29 INSERT INTO emp VALUES(1003,‘jerry‘,‘analyst‘,10000,3000,null,‘2010-3-5‘,10); 30 INSERT INTO emp VALUES(1004,‘smith‘,‘sales‘,8000,2000,null,‘2010-5-5‘,10); 31 INSERT INTO emp VALUES(1005,‘blake‘,‘programmer‘,9000,null,null,‘2010-10-7‘,30); 32 INSERT INTO emp VALUES(1006,‘zhangsan‘,‘sales‘,11000,null,null,‘2009-12-15‘,30); 33 INSERT INTO emp VALUES(1007,‘lisi‘,‘clerk‘,2000,500,null,‘2008-3-5‘,10); 34 INSERT INTO emp VALUES(1008,‘king‘,‘boss‘,20000,null,null,‘2008-1-6‘,10); 35 INSERT INTO emp VALUES(1009,‘allen‘,‘clerk‘,8500,2000,null,‘2009-3-5‘,30); 36 INSERT INTO emp VALUES(1010,‘dawson‘,‘sales‘,7400,3000,null,‘2010-5-5‘,30); 37 38 39 显示所有信息 40 SELECT * FROM emp; 41 42 43 44 45 1.查询2010年入职的员工（三种方法） 46 方法一： 47 SELECT 48 ename, 49 hiredate 50 FROM 51 emp 52 WHERE 53 hiredate LIKE ‘2010%‘; 54 55 方法二： 56 SELECT 57 ename, 58 hiredate 59 FROM 60 emp 61 WHERE 62 YEAR(hiredate)=2010; 63 64 方法三： 65 SELECT 66 ename, 67 hiredate 68 FROM 69 emp 70 WHERE 71 hiredate>=‘2010-01-01‘ AND hiredate<=‘2010-12-31‘; 72 73 74 75 2.查询薪资在10000~20000之间的员工 76 方法一： 77 SELECT 78 ename, 79 salary 80 FROM 81 emp 82 WHERE 83 salary BETWEEN 10000 AND 20000; 84 85 方法二： 86 SELECT 87 ename, 88 salary 89 FROM 90 emp 91 WHERE 92 salary >= ‘10000‘ AND salary <= ‘20000‘; 93 94 95 96 3。查询薪资不在10000~20000之间的员工 97 方法一： 98 SELECT 99 ename, 100 salary 101 FROM 102 emp 103 WHERE 104 salary NOT BETWEEN 10000 AND 20000; 105 106 方法二： 107 SELECT 108 ename, 109 salary 110 FROM 111 emp 112 WHERE 113 NOT salary >= ‘10000‘ AND salary <= ‘20000‘; 114 115 116 117 4.查询姓名中含有a的名字的员工信息 118 SELECT 119 ename, 120 empno, 121 job, 122 hiredate 123 FROM 124 emp 125 WHERE 126 ename LIKE ‘%a%‘; 127 128 129 130 5.查询姓名中第二个字母含有a的员工信息 131 SELECT 132 ename, 133 empno, 134 job, 135 hiredate 136 FROM 137 emp 138 WHERE 139 ename LIKE ‘_a%‘; 140 141 142 143 6.查询所有员工的年薪是多少 144 SELECT 145 ename, 146 salary, 147 bonus, 148 salary * 12 + ifnull(bonus, 0) year_sal 149 FROM 150 emp; 151 152 153 154 7.显示所有员工的姓名,要求首字母大写(特殊难点) 155 -- 方法一: 156 SELECT 157 CONCAT( 158 upper(substr(ename, 1, 1)), 159 substr(ename, 2) 160 ) 161 FROM 162 emp; 163 164 方法二: 165 SELECT 166 REPLACE ( 167 ename, 168 substr(ename, 1, 1), 169 upper(substr(ename, 1, 1)) 170 ) 171 FROM 172 emp; 173 174 175 176 8.修改1002, 1003, 1004的领导为1001 177 UPDATE emp 178 SET mgr = 1001 179 WHERE 180 empno IN (1002, 1003, 1004); 181 182 183 184 9.修改1001, 1005的领导设置为1008 185 UPDATE emp 186 SET mgr = 1008 187 WHERE 188 -- empno IN (1001,1005); 189 190 191 192 10.修改表格名称, 将emp改为employee 193 alter table emp rename to employee; 194 195 196 197 11.修改1001, 1002 的入职时间为2019-07-30 198 UPDATE employee 199 SET hiredate = ‘2019-7-30‘ 200 WHERE 201 empno IN (1001, 1002); 202 203 204 205 12.修改1006, 1007的入职时间为2019-4-15 206 UPDATE employee 207 SET hiredate = ‘2019-4-15‘ 208 WHERE 209 empno IN (1006, 1007); 210 211 212 213 13.修改1002, 1007的入职时间为2020-8-26 214 UPDATE employee 215 SET hiredate = ‘2020-8-26‘ 216 WHERE 217 empno IN (1002, 1007); 218 219 220 221 14.查询今年入职的员工有哪些 222 SELECT 223 ename, 224 hiredate 225 FROM 226 employee 227 WHERE 228 YEAR (hiredate) = YEAR (CURDATE()); 229 230 231 232 15.查询当月入职的员工有哪些 233 SELECT 234 ename, 235 hiredate 236 FROM 237 employee 238 WHERE 239 MONTH (hiredate) = MONTH (now()); 240 AND YEAR (hiredate) = YEAR (curdate()); 241 242 243 244 16.查询薪资大于10000且部门号为10的员工年薪 245 SELECT 246 ename, 247 salary, 248 bonus, 249 deptno, 250 salary * 12 + IFNULL(bonus, 0) year_sal 251 FROM 252 employee 253 WHERE 254 salary > 10000 255 AND deptno = 10; 256 257 258 259 17.查询薪资大于10000且奖金大于1000的员工信息 260 SELECT 261 * 262 FROM 263 employee 264 WHERE 265 salary > 10000 266 AND bonus > 1000; 267 268 269 270 18.查询当前员工中哪些员工已经超过了3年 271 SELECT 272 ename,hiredate 273 FROM 274 employee 275 WHERE 276 hiredate < DATE_SUB(CURDATE(), INTERVAL 3 YEAR); 277 278 279 280 -- 19.查询employee表所有数据 281 SELECT * from employee;

　　 -- 20.修改表格原先名称, 将employee改为emp
   alter table employee rename to emp;

 

 

                  故屿γ                   

 

 

SQL - CURD 示例

标签：技术   err   inter   数据库   tab   之间   bsp   esc   class