1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 |
-- 修改表名, TO 或AS都可以 -- ALTER TABLE 表名 RENAME [TO|AS] 新表名 ALTER TABLE tb1 RENAME TO tb2; 或者 RENAME TABLE tb1 TO tb2; create table cgb1907_user( id int primary key auto_increment, name varchar(20) not null, age int not null, sex char(5) not null, phone int(20) default '188' ); alter table cgb1907_user add addr varchar(20) default '长沙' after age; #添加字段# alter table cgb1907_user add aa int not null first, add bb int not null first, add cc int not null first; #删除字段# alter table cgb1907_user drop aa, drop cc, drop bb; #修改字段类型# alter table 表名 modify 字段名 数据类型 约束条件 #例: alter table cgb1907_user modify addr char(30); #修改结构的字段名称 alter table 表名 change 旧字段名 新字段名 数据类型 约束条件 #例: alter table cgb1907_user change addr address varchar(30) not null insert cgb1907_user value("1","老王","18","维也纳666房间","不详","133"); insert cgb1907_user(name,age,sex,address) value ("老王","18","男","维也纳666房间"), ("小赵","20","女","长沙市岳麓区"), ("小钱","21","男","长沙市雨花区"), ("老孙","28","男","长沙市开福区"), ("老李","27","男","长沙市芙蓉区"), ("老胡","20","男","长沙市天心区"), ("老程","18","男","长沙市望城区"), ("小王","18","女","长沙市雨花区"), ("小费","17","男","长沙市岳麓区"); update cgb1907_user set address="上海市" where id=18; insert cgb1907_user set name="小王",age="15",address="湖南省长沙市",sex="女"; create table Emp_lst( id int primary key ); create table emp_lst (编码 int primary key auto_increment, 姓名 varchar(255) not null, 职位 varchar(120) not null, 薪水 int not null, 奖金 int, 入职时间 date, 经理 int, 所在部门 varchar(255)); create table emp_xlz (id int primary key, name varchar(255) not null, job varchar(120) not null, salary int not null, comm int, birth date, manager int, dept varchar(255)); insert emp_xlz(id,name,job,salary,comm,birth,manager,dept) value (1001,"张无忌","Manager",10000,2000,"2018-6-15",1005,10), (1002,"刘苍松 ","Analyst",8000,1000,"2017-1-15",1001,10), (1003,"李翊","Analyst",9000,1000,"2015-5-3",1001,10), (1004,"郭芙蓉","Programmer",5000,0,"2018-6-15",1005,10), (1005,"张三丰","president",15000,0,"2008-2-16",0,20), (1006,"燕小六","Manager",5000,400,"2011-6-20",1005,20), (1007,"陆无双","Clerk",4000,500,"2017-8-7",1005,20), (1008,"黄蓉","Manager",5000,800,"2013-7-11",1005,30), (1009,"韦小宝","Salesman",4000,0,"2018-2-15",1008,30), (1010,"郭靖","Salesman",4500,2000,"2016-4-19",1008,30); (1011,"张某","Manager",10000,2000,"2019-2-15",1005,10); 查询每个部门工资最低的人 select name,salary from emp_xlz where salary=(select min(salary) from emp_xlz) ; select * from emp_xlz as a where salary in(select dept,min(salary) from emp_xlz as b group by dept) and (a.dept=b.dept); 查询张无忌的领导是谁 select id,name from emp_xlz where id=(select manager from emp_xlz where name="张无忌"); 查询张三丰一个部门的员工姓名 select id,name,dept from emp_xlz where dept=(select dept from emp_xlz where name="张三丰"); create database Dept character set utf8; create table Dept_info (depno int(2) not null, dname char(20) not null, location char(20) not null); insert Dept_info value(10,"研发部","北京"), (20,"财务部","上海"), (30,"销售部","广州"), (40,"后勤部","天津"); 查询员员工上班城市 select a.name,b.location from emp_xlz as a inner join dept_xlz as b on a.dept=b.deptno; 查询emp_xlz表中的员工所在部门和上班城市 select a.name,b.dname,b.location from emp_xlz as a inner join dept_xlz as b on a.dept=b.deptno; |
mySQL数据基础(二)
- mySQL数据基础(一)
- CMS新闻管理系统练习(一)