windows上的操作
1.从官网下载mysql
下载navicat,用来连接mysql的
2.打开运行启动mysql
3.在navicat上的连接打开新建连接
然后输入链接名,连接名就是用户名,自己随意命名
输入密码,点击确定,此时已经建好了用户并和mysql连接成功
4.在左栏新建好的用户名中,右击新建数据库,命名确定
5.打开新建数据库,右击表,运行SQL文件,
6.从电脑中选取要导入的文件。
7.点中表,右击刷新便已导入成功。
DML: 数据库操作语言(添加,删除,更新,查询)
DDL:数据定义语言(creat ,drop,alter,truncate)
DCL:数据控制语言(用户管理 权限管理 数据库维护)
1. DML(Data Manipulation Language)
数据操纵语言,SQL中处理数据等操作统称为数据操纵语言 ;
INSERT---数据的插入
DELETE---数据的删除
UPDATE---数据的修改
SELECT---数据的查询
2. DDL(Data Definition Language)
数据定义语言,用于定义和管理 SQL 数据库中的所有对象的语言 ;
CREATE---创建表
ALTER---修改表
DROP---删除表
3.DCL(Data Control Language)
数据控制语言,用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视等;
GRANT---授权。
ROLLBACK---回滚
COMMIT---提交。
经典SQL查询语句大全地址:http://www.cnblogs.com/gates/p/3936974.html
mysql官方网址 : https://dev.mysql.com/doc/
mysql select 参数: http://www.jb51.net/article/54321.htm
按类别列出的mysql函数: https://www.techonthenet.com/mysql/functions/index.php
单表查询:
select查询语法
where: 是限制性查询
concat: 字符串拼接 实例: select concat(last_name,', ',job_title) employee_title
from employees
distinct: 去重复
and: 并且
between....and....:[闭区间]
%,_ where product_name like '%apple%' %代表任意字符,大小写不敏感
‘—Apple’ 下划线代表任意单个字符
in: where job_title in ('Sales Manager','Shipping Clerk')在列表中的匹配表达,字符串用单引号标注
not in where category_id not in (1000,1001,1003)不在列表中的匹配表达
is null where report—to is null/is not null 空值处理操作
exists where exists (subquery) subquery就是select查询语句
常用if exists
order by :order by ....[asc|desc] 按照什么进行升序或者降序排列
实例:ORDER BY salary desc,commission_pct desc
分组操作实例:
select category_id,count(*) quality count后不能再跟别的列,注意别名前没有符号
from prduct 此实例顺序是不能变的,如果有where则添加到第三行
group by category_id
having count(*)>5 统治结果的限制性查询
order by quality 只有在order by中可以引用select的别名
select max(price) 价格最高的
from product
select *
from product 价格最高的详细信息
where price=(select max(price) from product)
多表查询:union:纵向链接,会合并重复行,注意:列数要相等,类型在mysql上可以不一致
union all :不会合并重复行
字符函数:initcap:首字母大写 (mysql中不能用,)
ascii:返回字符的ascii码
char—length:返回字符串长度 (慎用length) select char_length('hello中国')
length:汉字在不同的数据语言中代表不同的字符 select length('hello中国') from dual
concat:链接字符串
format:格式化数值
instr:在字符串中第几个位置 select instr('hello china','ch') from dual;
lcast和lower:转小写字母 select lcase('hELLo'),lower('hELLo') from dual;
upper:转大写字母
left:从左向右提取字符串
right:从右向左提取字符串
lpad:左填充(`hello`“8”“#”) select lpad('hello',3,'#') from dual
trim:去字符 select trim(leading '$' from '$$$123$$45$$$') from dual;
select trim(trailing '$' from '$$$123$$45$$$') from dual;
select trim(both '$' from '$$$123$$45$$$') from dual;
ltrim:去掉串左边的空格
rtrim:去掉串右边的空格
mid:
position:
strcmp:
repeat:重复次数 select repeat('tty',3) from dual; select repeat('*',round(salary/1000))
reverse:取反向
space:在前面加空格 select length(space(5)) from dual
replace:替换 select replace('$$$123$$45$$$','$','') from dual
substr: select substr('hello world.',5,3) from dual; where substr(last_name,3,1)='a'
substring: select substring('hello world.',5,3) from dual; 同substr用法相同。
substring_index:以空格为分隔符的几个字符
比如: select substring_index('i from china. i say hello world to everybody.',' ',-2) from dual
soundex:返回发音类似的字符 比如: soundex(cuse_name) = soundex('y lie')返回y lee
数值函数:ABS;取绝对值 select abs(-123) from dual;
ceil:小数点后向上取正 select ceil(1234.34) from dual
floor小数点后向下取正 select floor(1234.89) from dual
mod:求余 select mod(10,3) from dual;
pi:取π(pai)值 select pi() from dual;
pow:求幂值,几次方求值 select power(10,3) from dual
rand:随机数 select dept_id,rand(1),rand(2),rand(3) from department;
round:四舍五入 select round(123.556,-1) from dual
sqrt:开平方 select sqrt(16) from dual;
truncate:把小数点去掉 select truncate(199.956,-2) from dual;
统计函数:sum min max avg count
日期函数:adddate,addtime,date_forcat,datediff,last_day,mouth,year,date,sysdate,str_to_date,weekday,
date_forcat : http://www.w3school.com.cn/sql/func_date_format.asp
weekday:select weekday(DATE_FORMAT(sysdate(),'%Y-%m-%d')) from dual weekday:日期的周几
str_to_date:select str_to_date ('17-6月 -03','%e-%m月 -%Y') from dual;str_to_date:日期格式化
datediff:日期相差天数 select round(datediff('2008-08-08', '2007-07-07')/30) days
from dual datediff:两个日期相差天数或时间
select datediff('2017-06-15 12:00:00','2017-05-24 03:00:00') from dual;
select sysdate() from dual; sysdate:系统现在时间
select adddate(sysdate(),3) from dual; adddate: 添加天数,单位是天
select adddate(sysdate(),interval 3 month) from dual;
select subdate(sysdate(),3) from dual; subdate: 减去天数,单位是天
select subdate(sysdate(),interval 3 month) from dual;
select sysdate(),addtime(sysdate(),'10') from dual; addtime: 添加时间,单位是秒
select sysdate(),addtime(sysdate(),'3:2:10') from dual; -- hh:mm:ss
-- mm-dd yyyy hh:MM:ss date_forcat:时间格式化
select sysdate(),date_format(sysdate(),'%m-%e %Y %h:%i:%s') from dual;
select date_format('sysdate()','%Y-%m-%e') 结果是:2017-5-18 date_format
select cast('2017-5-18' as DATE) 结果是:2017-5-18 cast
select last_day(sysdate()) from dual; last_day(sysdate()):当月的最后一天
select year(sysdate()),month(sysdate()),day(sysdate()) from dual;
高级函数:case,isnull, nullif, ifnull, cast
select *,case is_payed
when 1 then '已付款'
when 0 then '未付款'
else '未知状态'
end 支付状态
from `order`;
-- 把订单按照订单金额区分成大额订单,一般订单,小额订单
-- 大额订单:>= 100w
-- 一般订单:50w <= money < 100w
-- 小额订单:< 50w
select *,case
when total_money/10000 >= 100 then '大额订单'
when total_money/10000 >= 50 and total_money/10000 < 100 then '一般订单'
else '小额订单'
end 订单类型
from `order`;
select '2017-5-18' from dual;
select cast('2017-5-18' as DATE) from dual;
select date_format(cast('2017-5-18' as DATE) + 10,'%Y-%m-%e') from dual;
select 100 + ifnull(null,0) from dual;
加密函数:passwd select password('hello') from dual;
mysql查询语句练习题网址:
http://www.cnblogs.com/pingliangren/p/5586918.html
实例:studymysql表
select * from employee;
select * from department;
select * from office;
select * from customer;
select * from `order`;
select * from order_detail;
select * from product;
1.员工姓名,所服务的客户信息
select employee_name,cust.*
from employee emp
right join customer cust on emp.employee_id = cust.sale_employee_id
order by employee_name;
2.订单编号,客户姓名,产品名称,产品数量,产品价格,小计(其中金额项使用$999,999.99格式)
select `order`.order_number,customer.customer_name,product.product_name,buy_number,concat('$',format(order_detail.price,2)),concat('$',format(order_detail.total_money,2))
from `order`
left join order_detail on `order`.order_id = order_detail.order_id
left join customer on `order`.customer_id = customer.customer_id
left join product on order_detail.product_id = product.product_id;
select lpad('3,499.99',length('3,499.99')+1,'$');
3.订单编号,客户姓名,产品名称,产品数量,产品价格,小计,每个客户添加一行订单总额
select order_number,customer_name,product_name,buy_number,fmt_price,fmt_total
from (
select `order`.order_id id,`order`.order_number,customer.customer_name,product.product_name,buy_number,concat('$',format(order_detail.price,2)) fmt_price,concat('$',format(order_detail.total_money,2)) fmt_total
from `order`
left join order_detail on `order`.order_id = order_detail.order_id
left join customer on `order`.customer_id = customer.customer_id
left join product on order_detail.product_id = product.product_id
union
select `order`.order_id,'','','','','总计:',total_money
from `order`
order by id,order_number desc) new_order;
4.员工姓名,员工的职位,所在部门,员工的上级领导,员工的上级领导的职位
select emp.employee_name,emp.job_title,dept_name,leader.employee_name,leader.job_title
from employee emp
left join department on emp.dept_id = department.dept_id
left join employee leader on emp.report_to = leader.employee_id;
5.按照产品种类统计每一类产品的数量,显示产品类别名称,产品信息
select category_name,numbers,prod.*
from
(select name category_name,count(*) numbers
from category
left join product on category.category_id = product.category_id
group by name) cate
left join