mysql语法基础

摘自：https://www.zhaochao.top/article/64，更多开发技术请访问 https://www.zhaochao.top

SQL的命令类别：

数据定义语言 DDL : Data Definition Language; creat(创建) alter(修改) drop(删除)

数据操纵语言 DML : Data Manipulation Language; insert(插入) select(查询) update(修改) delete(删除)

事务控制语言 TCL : Task Control Language; commit(提交) rollback(回滚)

数据控制语言 Data Control Language; grant(授权) revoke(取消授权)

详细用法及代码实现：

create table test01(id int,name varchar(255)) -- create table表名

alter table test01 ADD(sal int) -- alter table + add 添加列

alter table test01 drop [column] name -- alter table + drop [column] 列名

alter table stu add lil varchar(100) first; -- 在最前面(第一列)插入

alter table stu add llll varchar(100) after id; -- 插入在指定列名之后

alter table stu add(num int(10),str varchar(100)); -- 插入多列

drop table test01 -- drop table 删除表

alter table test1 modify column address varchar(50) after tel; -- 重新定义列并改变列顺序

alter table test1 change stunum number varchar(100) after tel; -- 修改列名称和定义并改变顺序

alter table test1 rename test01; -- alter + rename 一个数据表更名

rename table 表名1 to 新表名1[,表名2 to 新表名2]... -- rename to , to ... 多个数据表更名

 

insert into test01 values(1,9000,'Tom','US',138000) -- insert into 表名称 + values (值1, 值2,....) 向表格中添加新的行

insert into test01 (id) value (1) -- insert into + value 指定要插入数据的列

insert into test01 (id,name) values (3,'Jery') -- insert into table_name (列1, 列2,...) + values (值1, 值2,....) 指定要插入数据的列

update test01 set id=4 where id=3 -- UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值 修改表中的数据

update test01 set sal =11000 where sal is null -- 修改一个指定字段值

update test01 set address='China',tel=186000 where id=6; -- 修改多个指定字段值(注意：同时修改多个列的值的时候，满足where条件的列必须在同一行才能够修改成功)

delete from test01 where name is null -- detete from + where 删除（指定）行

 

select 查询信息

select * from emp -- 查询所有员工信息

select * from dept; -- 查询所有部门信息

select * from salgrade; -- 查询所有薪资等级信息

select empno,ename,deptno from emp; -- 仅查询员工编号、员工姓名、部门编号信息

select ename,sal*12 from emp; -- 查询员工姓名和他们的年薪

select sal*12 as 年薪 from emp; -- 查询员工年薪，并起别名为年薪 也可忽略as，写成：select sal*12 年薪 from emp;

 

select 2*4 from emp -- 简单的数学运算，但会出现冗余

select 2*4 from dual -- 简单的数学运算，可消除冗余 dual是一个虚拟的表

select distinct 2*4 from emp; -- 另一个可消除冗余的数学运算

select sysdate() from dual; -- 获得当前日期时间 2016-08-15 14:53:13

select now() from dual; -- 获得当前日期时间 2016-08-15 14:53:58

 

比较操作符:between and;in;like;is null;=;>;<;>=;<=;<>; 逻辑操作符：and;or;not;

select e.ename,e.sal from emp e where e.sal between 2000 and 3000; -- 查询工资在2000-3000内的员工姓名和工资

select e.ename,e.sal from emp e where e.sal>=2000 and e.sal<=3000 or e.sal=800; -- 查询工资在2000-3000内的员工姓名和工资

select e.ename,e.job,e.sal from emp e where e.sal in(800,1500,3000); -- 查询工资是800、1500或3000的员工姓名，职位和工资

select e.ename,e.job,e.sal from emp e where e.sal not in(800,1500,3000); -- 查询工资是不是800、1500和3000的员工姓名，职位和工资

select e.ename,e.sal from emp e where e.ename like 'A%'; -- 查询名字首字母为'A'的员工的姓名和...

select e.ename,e.job from emp e where e.ename like '_A%' -- 查询名字第二个字母为'A'的员工的姓名和...

select e.ename,e.job from emp e where e.ename like '%A%' -- 查询名字中包含字母'A'的员工的姓名和...

百分号%:0-n 下划线_:1-1 like '%\%%' 默认转义字符\ like '%$%%' escape '$' 指定转义字符

select e.ename,e.hiredate from emp e where e.comm is null; -- 查询没有奖金的员工的姓名和...

select e.ename,e.hiredate from emp e where e.comm=0; -- 查询没有奖金的员工的姓名和...

select ename from emp where ename>'FORD'; -- 查询排在“FORD”之后的员工信息

select e.ename,e.hiredate from emp e where e.hiredate>'1981-03-22'; -- 查询在1981年3月22日之后入职的员工的姓名和入职时间

 

select deptno from emp; -- 查询部门编号，出现冗余

select distinct deptno from emp; -- 查询部门编号，消除冗余

select distinct deptno,job from emp; -- 同时查询部门编号与岗位，消除冗余

 

排序问题: ORDER BY子句跟在SELECT 语句之后 ASC: 升序, 缺省值、DESC: 降序

select * from emp order by hiredate; -- 按照时间排列(默认升序)

select * from emp order by sal; -- 按照薪资排列(默认升序)

select * from emp order by sal asc; -- 按照薪资升序排列；

select * from emp order by sal desc; -- 按照薪资降序排列；

select * from emp order by deptno asc,sal desc; -- 先按照部门升序排序，部门相同的员工按照工资降序排序

select * from emp order by job asc,sal desc; -- 先按照职位升序排序，职位相同的员工按照工资降序排序

 

集合操作符

select * from emp where(deptno=10 and job='manager')or(deptno=20 and job='clerk')-- 查找所有10部门的经理和 20部门的办事员

union 联合两个结果集（去除掉重复记录）

select * from emp where(deptno=10 and job='manager') union select * from emp where(deptno=20 and job='clerk');

union all 联合两个结果集（不去除掉重复记录）

select * from emp where(deptno=10 and job='manager') union all select * from emp where(deptno=20 and job='clerk');

 

聚合函数 select 分组字段,聚合函数... from … where …

select sum(sal) from emp; -- 查找公司的工资总和

select count(*) from emp; -- 查找公司的总人数

select avg(sal) from emp; -- 查找公司的平均工资

select max(sal) from emp; -- 查找公司的最高工资

select min(sal) from emp; -- 查找公司的最低工资

select deptno,sum(sal) from emp group by deptno; -- 查看每个部门的总工资

select * from emp group by deptno,ename; -- 分别查看每个部门的员工的所有信息

select deptno,max(sal) from emp group by deptno; -- 求各个部门的最高薪水('子查询'中有加强版

select * from emp where ename not like '_A%' and sal>800 order by sal*12 desc; -- 查询姓名第二个字母不是”A”且薪水大于800元的员工信息，按年薪降序排列

select deptno,avg(sal) from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal) desc; -- 将员工薪水大于1200且部门平均薪水大于1500的部门编号和平均薪水列出来，按部门平均薪水降序排列

 

Having和where的区别

having用来筛选聚合之后的结果集

where用来筛选表中的记录

select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; -- 查看平均工资超过2000的部门的编号和平均工资

select sum(sal) from emp where sal>2000 group by deptno having sum(sal)>5000 order by sum(sal) desc;--查找每个部门工资高于2000的员工的总工资，要求只显示总工资高于5000的，按总工资降序排列

 

查询语法总结：select [distinct] {*| column [alias], ...} from table [where condition(s)] [group by column(s)][having condition(s)][order by {column, expr, alias} [asc|desc]]; (where-group-having-order)

 

select sal from emp where sal>2000 limit 3; -- TOP子句用于规定要返回的记录的数目 限制数据长度为定值之内

 

函数：

数学函数

ABS(n) 绝对值

CEIL(n) 取整函数(向上取整)>= 取大于等于数值n的最小整数

FLOOR(n) 取整函数(向下取整)<= 取小于等于数值n的最大整数

TRUNC(n) 截断函数

ROUND(n) 四舍五入函数 ROUND(数值,保留位数) 假设保留位数为负数,继续往前四舍五入

select round(23.652) from dual; => 24

select round(23.652,2) from dual; => 23.65

select round(23.652,1) from dual; => 23.7

select round(23.652,0) from dual; => 24

select round(23.652,-1) from dual; => 20

 

字符串函数

select concat(ename,job) from emp where sal=800; -- CONCAT(str1,str2) 连接两个字符串

select distinct instr(t.name,"i") from test01 t; -- INSTR(str,substr) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置

select length("nimeide") from dual;-- LENGTH(str)返回字符串的长度

select lower("NIMEIDE") from dual; -- LOWER(str)返回字符串,并将所有的字符小写

select upper("nimeide") from dual; -- UPPER(str)返回字符串,并将所有的字符大写

select lcase("NIMEIDE") from dual; -- -- LCASE(str)返回字符串,并将所有的字符小写

select ucase("nimeide") from dual; -- -- UCASE(str)返回字符串,并将所有的字符小写

select lpad(ename,8,"str") from emp; -- LPAD(str,length,char) 在字符串的左边粘贴字符

select rpad(ename,8,"str") from emp; -- RPAD(str,length,char) 在字符串的右边粘贴字符

select ltrim("strname","str") from dual;

select substr(ename,1,3) from emp; -- SUBSTR(str,start,count) 取子字符串,从start开始，count截取长度

select replace("nametolearn","name","stud") from dual; -- replace(str,from_str,to_str) str源字符串,from_str待替换的字符串,to_str用于替换的新字符串

select reverse("nimeide") from dual; -- REVERSE(str)反转字符串中的每个字符

 

转换函数

select TO_DAYS(hiredate) from emp;

 

其他函数

select mid(ename,3,2) from emp; -- MID(column_name,start[,length]) 从文本字段中提取字符

 

高级查询：

索引的创建和查看

create index indexname on tablename(列名); -- 为表的某一列名添加名为indexname的索引

show index from tablename; -- 查看某表的索引

drop index indexname on tablename; -- 删除某表的索引

普通两表查询

select * from emp e,dept d where e.deptno=d.deptno;

内联接

select * from emp e inner join dept d on e.deptno=d.deptno;

select * from emp e join dept d on e.deptno=d.deptno;

左联(完全显示左侧内容)

select * from emp e left join dept d on e.deptno=d.deptno;

右联(完全显示右侧内容)

select * from emp e right join dept d on e.deptno = d.deptno;

设置主键(创建时设置)

create table test1(

id int(10) primary key,tname varchar(50),address varchar(100)

);

设置自增(创建时设置)

create table test2(

id int(10) key auto_increment,tname varchar(100),tel int(10)

);

设置外键 (tid是外键的列名，-- prov是要关联的表名，id是主键列名)

alter table cit add(

foreign key(tid) references prov(id)

);

创建视图和其他操作(与操作表很相似)

create view newview as select ename from emp;

create view nimei as select id from test1;

insert into nimei (id) value (3); -- 对视图进行操作就相当于在直接操作表，除了删除表之外

update nimei set sname='新寨高中' where sname='inini'; -- 修改数据

desc nimei; -- 查看视图表结构

drop view nimei; -- 删除视图

case语句

结构：select 列名,case when 条件 then '说明' ... else '最后说明' end '字段说明(列名)' from 表名;

select degree,case when degree>=90 then '优秀' when degree>=75 and degree <90 then '良好' when degree>60 and degree<75 then '及格' else '不及格' end '评级' from score;

子查询

select * from emp where sal=(select max(sal) from emp); -- 将已经查询出来的数据作为已知数据使用来查询新的数据

举例:

输出非办事员的员工姓名，所在部门名称及薪水等级

select e.ename '员工姓名', d.dname '部门名称',s.grade '薪水等级' from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between losal and hisal and e.job <> 'clerk';

求各个部门薪水最高的员工信息

select e.* from emp e,(select max(sal) maxsal,deptno from emp group by deptno) a where e.deptno=a.deptno and e.sal=a.maxsal;

(解释：(select max(sal) maxsal,deptno from emp group by deptno) a 相当于创建了一个新的表，命名为a，在把e和a关联)

查询总工资大于10000的部门的总工资和部门名

select a.sumsal,d.dname from dept d,(select sum(sal) sumsal,deptno from emp group by deptno having sumsal>10000)a where d.deptno=a.deptno;

求各个部门获得最高薪水的员工姓名，部门编号和薪水

select distinct e.deptno,e.ename,e.sal from emp e,(select deptno,max(sal) maxsal from emp group by deptno)a where e.deptno=a.deptno and e.sal=a.maxsal;

选择所有a=1或者b=2的记录，使a=1且b=2的记录排在最前面，并且a=1且b=2的记录按id降序排列

select * from table_name where a=1 or b=2 order by case when a=1 and b=2 then 0 else 1 end,id desc;

 

数据控制语言： grant revoke (注意是否添加引号)

创建、删除新用户和授予、回收权限

创建用户：create user 'zc' identified by '123456';

删除用户：drop user zc;

授予部分权限：grant select,update on emp to zc;

授予全部权限：grant all privileges on emp to zc;

创建用户并授予所有权限：grant all privileges on emp to  'zc' identified by '123456';

创建用户并授予部分权限：grant select，insert on emp to  'zc' identified by '123456';

回收部分权限：revoke insert on emp from zc;

回收所有权限：revoke all privileges on emp from zc;

 

Dos窗口操作mysql常用指令(注意是否添加分号和引号)：

启动mysql服务：net start mysql

关闭mysql服务：net stop mysql

连接数据库(用户登录)：mysql -u username -p 按Enter之后输入password(密码)

退出数据库：exit

查看所有数据库：show databases;

选择(使用)数据库：use databasename;

查看该数据库下的所有表格：show tables;(分号可以不写)

查看某表的区段：desc或describe tablename;

结束本条语句编写并转到下一行：\c 一般会在输入错误的时候使用

其余指令参考上面mysql指令；

 

更多资源请访问 http://www.zhaochao.top