Select语句的基本操作
来源:互联网 发布:重庆北碚网络花店 编辑:程序博客网 时间:2024/06/05 13:24
Select语句的基本操作
掌握了基本操作之后,相信你就能熟练运用Select语句了!Let's go!操作环境:
mysql-workbench-community-6.1.7-win32mysql-installer-community-5.6.20.0
mysql-connector-odbc-5.3.4-win32
#创建数据库create database bank;grant all privileges on bank.* to 'feng'@'localhost' identified by '******';#grant usage on bank.* to 'feng'@'localhost' identified by '******';/*创建用户feng,密码为******,赋予bank数据库权限*/
使用feng用户登录MySQL:
1,进入MANAGEMENT下Users and Privileges,选择feng用户,点击Administrative Roles下Revoke ALL Privileges
2,进入Home,点击MySQL Connections右边的加号,填写Connection Name(随意)和Username(feng),再点击Test Connection,输入密码,登录成功!
use bank; #以下命令均在feng用户下操作!
使用Learning SQL书中的LearningSQLExample.sql文件生成数据:
在workbench中直接打开LearningSQLExample文件并执行.
出错!workbench不能执行多个update语句
解决方案:进入Workbench下Edit>-Preferences>-SQL Queries选项,去掉"Safe Updates"之前的勾,再次重启并执行该文件。
注意:在workbench下不能使用source命令,参见http://blog.mclaughlinsoftware.com/2012/09/01/mysql-workbench-limit/select语句:
/*select 需要选择的列名 #可用字符、表达式、内建函数、自定义函数
from 表名 #永久表(create table创建的表),临时表(子查询生成的表),虚拟表(create view创建的视图),表连接
where 选择特定的行 #可用一个条件(=,!=,<>,>,<,like),两个条件(and,between,or,not),多个条件(in,圆括号分组)
group by 根据列值分组
having 选择特定的组
order by 选择的列名排序; #升序,降序,表达式
*/
MySQL还有一些特有的子句,如limit,into outfile等
1.1 select from
SELECT * FROM department;SELECT version(),user(),database(); #内建函数SELECT emp_id,'ACTIVE', emp_id*3.14159,UPPER(lname) FROM employee; #upper、lower为大写和小写的内建函数SELECT emp_id,'ACTIVE' status, emp_id*3.14159 empid_x_pi,UPPER(lname) upperlname FROM employee; #select 列名的重命名SELECT emp_id,'ACTIVE' AS status, emp_id*3.14159 AS empid_x_pi,UPPER(lname) AS upperlname FROM employee; #完整命令SELECT cust_id FROM account;select distinct cust_id from account; #去除重复的行。由于DISTINCT涉及数据排序,#在数据集较大时,这是相当耗时的,因此,应先了解数据是否含有重复行,以减少DISTINCT的使用.select e.emp_id,e.fname,e.lname from (select emp_id,fname,lname,start_date,title from employee) e; #from 定义表别名create view employee_vw AS select emp_id,fname,lname,year(start_date) start_year from employee; #定义视图select emp_id,start_year from employee_vw;select e.emp_id,e.fname,e.lname,d.name dept_name from employee as e inner join department as d #from 定义表别名 on e.dept_id=d.dept_id; #on表示连接条件
select emp_id,fname,lname from employee where left(lname,1)='T';select emp_id,fname,lname from employee where lname like '_a%e%'; #'_'表示单个字符;'%'表示多个字符select cust_id,fed_id from customer where fed_id like '___-__-____';select emp_id,fname,lname from employee where lname like 'F%' or lname like 'G%';select emp_id,fname,lname from employee where lname regexp '^[FG]'; #使用正则表达式,等价于上一句select emp_id,fname,lname,start_date,title from employee where start_date > '2002-01-01' and start_date < '2004-01-01';select emp_id,fname,lname,start_date,title from employee where start_date between '2002-01-01' and '2004-01-01'; #范围的上下限是闭合的select account_id,product_cd,cust_id,avail_balance from account where product_cd = 'CHK' or product_cd = 'SAV' or product_cd = 'CD' or product_cd = 'MM';select account_id,product_cd,cust_id,avail_balance from account where product_cd in ('CHK','SAV','CD','MM'); #等价于上一句select account_id,product_cd,cust_id,avail_balance from account where product_cd in (select product_cd from product where product_type_cd='account'); #等价于上一句select account_id,product_cd,cust_id,avail_balance from account where product_cd not in ('CHK','SAV','CD','MM'); select emp_id,fname,lname,superior_emp_id from employee where superior_emp_id is null; #表达式不能等于null,即superior_emp_id=null是错误的!select emp_id,fname,lname,superior_emp_id from employee where superior_emp_id is not null;select emp_id,fname,lname,superior_emp_id from employee where superior_emp_id !=6 or superior_emp_id is not null; #注意:取值为null的数据不能忽略!select emp_id,fname,lname,start_date,title from employee where end_date is NULL and (title = 'Teller' or start_date > '2003-01-01');select emp_id,fname,lname,start_date,title from employee where end_date is NULL and not (title = 'Teller' or start_date > '2003-01-01');select emp_id,fname,lname,start_date,title from employee where end_date is NULL and (title != 'Teller' or start_date <= '2003-01-01'); #等价于上一句select emp_id,fname,lname,start_date,title from employee where (title = 'Head Teller' or start_date > '2002-01-01') or (title = 'Teller' or start_date > '2003-01-01');
1.3 select from group by having
select d.name,count(e.emp_id) num_employees from department as d inner join employee as e on d.dept_id=e.dept_id group by d.NAME having count(e.emp_id)>2;
1.4 select from order by
select open_emp_id,product_cd from account order by open_emp_id,product_cd; #根据雇员id和账户类型cd排序select account_id,product_cd,open_date,avail_balance from account order by avail_balance desc #desc表示descend降序,asc表示ascend升序 limit 5; #显示前5个数据select cust_id,cust_type_cd,city,state,fed_id from customer order by right(fed_id,3) #fed_id的最后三位数排序 limit 5;
Reference:
Learning SQL chapter 3,chapter 4
0 0
- Select语句的基本操作
- Select 语句的基本使用
- select语句的基本写法
- 基本的SQL SELECT语句
- oriacle基本的select语句
- 掌握SQL四条最基本的数据操作语句:Insert,Select,Update和Delete
- 掌握SQL四条最基本的数据操作语句:Insert,Select,Update和Delete
- SQL四条最基本的数据操作语句:Insert,Select,Update和Delete
- Oracle Database的基本操作——SELECT语句,TO_CHAR()函数
- 掌握SQL四条最基本的数据操作语句:Insert,Select,Update和Delete
- hive基本的操作语句(实例简单易懂,create table XX as select XX)
- SQL Server2008 学习之(九) :select语句及其子句的基本操作
- MySQL中SELECT语句的基本语法
- SQL--基本的SELECT语句 (1)
- 基本的select语句总结(转)
- 2. 基本的SQL-SELECT语句
- 002_基本的SQL-SELECT语句
- LinQ—基本查询操作符 Select/Where/Group语句
- UVA 11488 Hyper Prefix Sets (Trie)
- C++笔试题深度分析 第三波 上
- Java语言基础
- 【计算几何】 POJ 1981 Circle and Points
- STL之iterator(迭代器)
- Select语句的基本操作
- 【Unity3D教程宝典之Shader篇:基础讲学习方法技巧 】
- C#泛型详解
- 2014.10.4【大逃亡】
- OC面向对象—多态
- NYOJ868--路线计算
- 中断来10万次后不来了
- Knight Moves(第一次写的双向优先BFS)
- 执节歌者 · 箫谱1