mysql基本操作指令

database :数据空间(文件夹)table :数据表(文件)登录数据库 :mysql -u root -p 输入密码显示数据空间(文件夹) :show databases;创建数据空间 :create database 名称;删除数据空间 :drop database 名称;选择数据空间 :use 名称;显示数据表(文件) :show tables;创建数据表 :create table 名称(列名 类型,列名1 类型1，列名2 类型2,...,列名N 类型N);创建一个student表 :create table student(no int,name varchar(20),sex char,score double);删除数据表 :drop table 名称;查看表结构 : desc 表名;插入数据到数据表insert into 表名 values(列值1,列值2,...列值N);注：列值必须与列的类型对应另外一种插入方式insert into 表名(列名1,列名2,...列名N) values(列值1,列值2,...列值N);查看数据表内容select 列名1,列名2,...列名N from 表名;练习时常用 :select * from 表名; *通配符 用于表示全部注：工作时一般不使用，* 会查询列的内容占用额外效率删除表内数据注：delete from 表名; 清空所有数据修改表内数据update 表名 set 列名 = 列值;条件查询语句查询语句 where 条件（比较运算/逻辑运算）逻辑运算符and(&&) or(||) 比较运算符 : ><=!=>=<=between...and...in(列值1,列值2,...列值N)查询出编号为1011的人的姓名select empno,ename from emp where empno = 1011;条件修改语句修改1011编号的人的工作为talkshowupdate emp set job = 'talkshow' where empno = 1011;条件删除语句删除掉编号为1011的员工delete from emp where empno = 1011;函数 ：ifnull(列名,转换值) :如果该列中含有null值，则转换为转换值别名机制 as：select 列名 (as) 新列名 from 表名;大部分时间中，as可略查询出公司员工的编号，姓名，基本工资，月薪，年薪select empno,ename,salary,salary + ifnull(bonus,0) month_sal,(salary + ifnull(bonus,0)) * 12 year_salfrom emp;排序语句 ：正常语句 order by 列名;升序 ：asc (默认，可略)降序 ：desc查询出公司员工的编号，姓名，奖金 并按照从高到低的顺序排序select empno,ename,bonus from emp order by bonus desc;复制表create table 新表名 select * from 源表名;组函数 :count(列名) :显示该列中的值的数量sum(列名) :显示该列中值的和avg(列名) :显示该列中值的平均数max(列名) :显示该列中最大的值min(列名) :显示该列中最小的值可以通过help contents查询数据库中的函数库组函数可以通过help functions and modifiers for use with group by的方式查询出函数名查询出公司有多少员工？select count(empno) emp_count from emp;查询出公司员工的总工资select sum(salary) sum_sal from emp;查询出公司员工的最大工资和最小工资select max(salary) max_sal,min(salary) min_sal from emp;查询出公司的平均工资select avg(salary) avg_sal from emp;查询出所有没有奖金的员工编号、姓名select empno,ename from emp where bonus = NULL;在数据库中null值特性 :1.任何与null做运算的结果均为null;2.在mysql中 null值默认最小 为-∞ 在oracle中 null值默认最大 为∞3.NULL值默认被组函数忽略4.NULL值无法使用比较运算符判断  判断某一列的值是否为空，通过is null判断为空，is not null判断不为空分组：group by 列名当一行查询语句需要普通列查询和组函数查询时，需要分组查询注：通常情况下group by 后面的列名 均为查询时的普通列查询出公司的职位和该职位的人数select job,count(empno) job_count from emp group by job;查询出公司的各个部门的平均工资select deptno,avg(salary) avg_sal from emp group by deptno;分组之后的条件查询 不可以使用where使用分组后的关键字 having注：非分组情况建议不要使用having关键字 会造成隐性分组查询出平均工资过5000的部门编号和其平均工资select deptno,avg(salary) avg_sal from emp group by deptnohaving avg_sal > 5000;查询出平均工资过5000的部门编号和其平均工资并按照从小到大的顺序排序select deptno,avg(salary) avg_sal from empgroup by deptno having avg_sal > 5000 order by avg_sal asc;注：排序一般情况下放在末尾处查询出工资大于5000小于10000的员工编号、姓名和工资select empno,ename,salary from emp where salary > 5000 && salary < 10000;select empno,ename,salary from emp where salary > 5000 and salary < 10000;查询出工资小于5000和工资大于8000的员工编号、姓名和工资select empno,ename,salary from empwhere salary < 5000 || salary > 8000;select empno,ename,salary from empwhere salary < 5000 or salary > 8000;查询出工资在5000到10000之间的员工编号、姓名和工资select empno,ename,salary from emp where salary >= 5000 and salary <= 10000;select empno,ename,salary from empwhere salary between 5000 and 10000;查询出编号为1001、1003和1005的员工编号、姓名和工资select empno,ename,salary from emp where empno = 1001 or empno = 1003 or empno = 1005;select empno,ename,salary from empwhere empno in(1001,1003,1005);查询出公司的职位都有哪些？重复的不显示select job from emp group by job;注：分组可是实现但效率不高去重 ：distinctselect distinct job from emp;注：distinct必须写在最初，如果是多列去重，则必须写在所有列之前    判断重复是按照多列的数据判断的，而不是按照最近的数据判断    例：select distinct 列1,列2 from 表名;是对列1,列2的值进行去重操作模糊查询 like :配合通配符使用_ : 每一个_代表一个字符% : 在字符前 代表前部分数据是模糊的 匹配最后的字符    在字符后 代表后部分数据时模糊的 匹配开始的字符    前后均有% 则只要数据中存在值 就会查询出该数据查询出名字中带'e'的员工编号、姓名select empno,ename from emp where ename like '%e%';查询名字是Lacus的员工编号、姓名和工资select empno,ename,salary from emp where ename = 'Lacus';注：mysql中会默认将字符转换成小写字符，不需要考虑大小写问题    oracle不会转换，所以必须注意值的大小写问题lower(列名)：将该列值转换成小写值upper(列名)将该列值转换成大写值子查询 :查询出比Lacus工资高的员工编号、姓名和工资select empno,ename,salary from emp where salary > (select salary from emp where ename = 'Lacus');注：不关心Lacus的工资值，因为只需要求出比Lacus工资高的员工查询出哪个工作岗位的人数比salesMan多select job,count(empno) emp_count from emp group by job having emp_count >(select count(empno) sales_count from emp where job = 'salesMan');查询出哪个部门比部门20的平均工资高select deptno,avg(salary) avg_sal from emp group by deptno having avg_sal >(select avg(salary) avg_sal_temp from emp where deptno = 20);在数据库中插入一条数据insert into emp(empno,ename,job,salary,mgr,deptno) values(1012,'Teemo','xicesuo',10000,1001,10);子查询返回多列的情况：查询出比Teemo工资高的员工编号、姓名和工资比所有的Teemo工资高：select empno,ename,salary from emp where salary >all(select salary from emp where ename = 'Teemo');比任意一个的Teemo工资高：select empno,ename,salary from emp where salary >any(select salary from emp where ename = 'Teemo');谁和Teemo同部门，显示Teemo同部门的员工编号、姓名select empno,ename from emp where deptno = any(select deptno from emp where ename = 'Teemo');select empno,ename from emp where deptno in(select deptno from emp where ename = 'Teemo');谁是Teemo的上司，显示Teemo的上司的员工编号、姓名select empno,ename from emp where empno =any(select mgr from emp where ename = 'Teemo');select empno,ename from emp where empno in(select mgr from emp where ename = 'Teemo');关联子查询 :查询出比自身部门平均工资低的员工编号和姓名select empno,ename,salary,deptno from emp e1 where salary <(select avg(salary) from emp e2 where e1.deptno = e2.deptno);查询出每个职位对应的最高工资的员工编号、姓名、职位和工资select empno,ename,job,salary from emp e1 where salary =(select max(salary) from emp e2 where e1.job = e2.job);exists :与运算符和in不同，不在判断类型，只返回bool类型判断该值是否存在于子查询中 如果存在，则返回true如果不存在则返回false因为返回bool类型的原因，子查询的select部分没有实际意义为了保持语法通过，一般情况下用select 1 查询一个常量代替列名谁和Teemo同部门，显示Teemo同部门的员工编号、姓名select empno,ename from emp e1 where exists(select 1 from emp e2 where e1.deptno = e2.deptno and e2.ename = 'Teemo'); select empno,ename from emp where deptno = any(select deptno from emp where ename = 'Teemo');select empno,ename from emp where deptno in(select deptno from emp where ename = 'Teemo');谁是Teemo的上司，显示Teemo的上司的员工编号、姓名select empno,ename from emp e1 where exists(select 1 from emp e2 where e1.empno = e2.mgr and e2.ename = 'Teemo');select empno,ename from emp where empno =any(select mgr from emp where ename = 'Teemo');select empno,ename from emp where empno in(select mgr from emp where ename = 'Teemo');哪些人是别人的上司？显示这些人的编号、姓名select empno,ename from emp e1 where exists(select 1 from emp e2 where e1.empno = e2.mgr);select empno,ename from emp where empno =any(select mgr from emp);select empno,ename from emp where empno in(select mgr from emp);哪些人不是别人的上司？显示这些人的编号、姓名select empno,ename from emp e1 where not exists(select 1 from emp e2 where e1.empno = e2.mgr);select empno,ename from emp where empno !=all(select mgr from emp where mgr is not null);select empno,ename from emp where empno not in(select mgr from emp where mgr is not null);哪个部门没有员工？显示该部门的部门号、部门名和工作地点select deptno,dname,location from dept where not exists(select 1 from emp where dept.deptno = emp.deptno);select deptno,dname,location from dept where deptno !=all(select deptno from emp where deptno is not null);select deptno,dname,location from dept where deptno not in(select deptno from emp where deptno is not null);虚表 dual :在数据库中用于存储常量和一般函数的虚拟表当前日期 curdate():显示当前的日期 按照YYYY-MM-DD的形式显示当前时间 curtime():显示当前的时间 按照HH:MM:SS的形式显示当前的精确时间 now():显示当前的日期和时间 按照YYYY-MM-DD HH:MM:SS的形式显示to_days(时间值) :将参数转换为天数datediff(时间值1,时间值2) :返回两个之间值的天数差查询公司所有员工的编号，姓名，入职时间，工作天数select empno,ename,hiredate,(to_days(curdate())-to_days(hiredate))/365 work_year from emp;select empno,ename,hiredate,datediff(curdate(),hiredate)/365 work_year from emp;多表关联查询 :表1 join 表2 on 条件 ... join 表N on 条件N查询出公司的员工编号、姓名、部门名和工作地点内连接查询 ：select empno,ename,dname,locationfrom emp e inner join dept d on e.deptno = d.deptno;注：默认为内连接查询 inner可略外连接查询:左外连接：查询出公司的员工编号、姓名、部门名和工作地点 把没有部门的员工也显示出来select empno,ename,dname,locationfrom emp e left outer join dept d on e.deptno = d.deptno;select empno,ename,dname,locationfrom dept d right outer join emp e on e.deptno = d.deptno;右外连接:查询出公司的员工编号、姓名、部门名和工作地点 把没有员工的部门也显示出来select empno,ename,dname,locationfrom emp e right outer join dept d on e.deptno = d.deptno;select empno,ename,dname,locationfrom dept d left outer join emp e on e.deptno = d.deptno;全外连接:查询出公司的员工编号、姓名、部门名和工作地点 把所有相关数据都显示出来select empno,ename,dname,locationfrom emp e full outer join dept d on e.deptno = d.deptno;注：mysql不支持此写法在外连接中，以join为分割线 视外连接方式而定如果是左外连接 则join左边的表 称之为驱动表join右边的表 称之为匹配表如果是右外连接 则join右边的表 称之为驱动表join左边的表 称之为匹配表可以通过修改表顺序的方式 用左外连接实现右外连接的功能右外连接也可以实现左外连接的功能union 联合 :不会显示重复的数据查询列的数量必须一致select empno,ename,dname,locationfrom emp e left outer join dept d on e.deptno = d.deptnounion select empno,ename,dname,locationfrom emp e right outer join dept d on e.deptno = d.deptno;注：在mysql中 所有的outer均可以略写自连接 :查询出公司里各个下属的名字与其对应上司的名字select e1.ename,e2.ename from emp e1join emp e2 on e1.mgr = e2.empno;limit 下标，个数取一段数据的一定量的数据下标从0开始，个数代表取几个constraint 约束 :not null 非空约束 : 该列不可以为NULLunique 唯一约束 : 该列不可重复primary key 主键约束 : 非空约束和唯一约束的集合default 默认约束 : 规定该列的默认值auto_increment 自增约束 : 在不插入该值时，该列值自动+1的方式自增foreign key 外键约束 : 关联另外一个表中的值，使其相互作用index 索引 :主键索引 组合索引 聚簇索引创建一个索引 :create index 索引名 on 表名(列名)删除一个索引 :drop index 索引名 on 表名类似于目录式另外的一个数据文件 当条件查询时可以加快> < >= <= in not in = <> between...and... not between...and...但是提高查询效率的同时，会降低插入、修改和删除的效率，因为索引文件会与表本身同步更新索引文件会根据表中数据的增大而增大，占用硬盘空间，容易造成服务器硬盘溢出事务 :begin :开始一个事务rollback : 撤销commit : 确认视图 :查询语句的结果依然可以被视为是一个表不可修改内容 必须起一个别名才可以使用select * from (select * from emp) e;创建联合主键：alter table mytable add PRIMARY key(name, address);如果想在一个已经建好的表中添加一列，可以用诸如：alter table t1 add column addr varchar(20) not null;这条语句会向已有的表t1中加入一列addr，这一列在表的最后一列位置。如果我们希望添加在指定的一列，可以用：alter table t1 add column addr varchar(20) not null after user1;注意，上面这个命令的意思是说添加addr列到user1这一列后面。如果想添加到第一列的话，可以用：alter table t1 add column addr varchar(20) not null first;将表web1.new_table change中，列名def改为unitalter table web1.new_table change  def unit char;将表web1.new_table change中，列名def的列删除alter table web1.new_table drop column def ;