mysql 常用指令
一、mysql的常用指令
1:登录mysql数据库管理系统 dos命令窗口
mysql 【-h 主机名 -p 端口号】 - uroot -p**** “****”表示数据库的密码
2:查看数据库
show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| bjpowernode |
| mysql |
| performance_schema |
| test |
±-------------------+
3:创建一个数据库
create database bjpowernode;
4:使用一个数据库
use bjpowernode; 其中的bjpowernode表示要指定使用的数据库
5:查看该数据库中的表
show tables;
show tables from < database name> 查看其它库中的表
6:初始化数据库
source -----此处可以拖进sql脚本—或者直接输入脚本位置
7:删除数据库
drop database bjpowernode 删除bjpowernode数据库
8:查看表结构 desc
desc user; 查看user表结构
9:查看当前使用的是哪个数据库
select database();
10:查看数据库的版本号
select version();
11:退出mysql
exit;
12:查看创建表的语句
show create table < tabele>
13:设置数据库支持汉字
set names gbk;
二、sql语句
1:条件查询
select 字段11 , 字段2 from 表名 where 条件
不在。。。。之间: not between and
注意:
1:null 和 0 意义不同, 0 代表0值, null 代表无值
2:and 的 优先级 大于 or, 所以需要用()来控制优先级
3:in 的用法(作用上等同于or)
select sname form students where job = ‘salesman’ or job = ‘manager’
select sname form students where job in (‘salesman’, ‘manager’)
4:模糊查询(like)
% 代表任意多个字符, _代表任意1个字符
eg: where like %a% -----表示条件第含有字母 a, _a表示第二个字母是a
5:去重 distinct
select distinct sname form students;
- distinct 只能出现在所有字段的最前方,后面字段联合起来去重
6:+ 作用:拼接
- select ‘john’ + 90 (结果为90)
- select null + 90 (结果为null,有一方为null就为null)
7:连接操作符 concat(有null时候,均为null)
- select concat(firstname, lasetname) as 重命名 from 表名
- select concat( ifnull (firstname, zhang), lasetname) as 重命名 from 表名 (ifnull,如果是null默认为,后的内容)
8:转义字符,\或者 escape
- select last_name from student where last_name like '_$_% ’ escape ‘$’
9: between and (数据不能颠倒顺序)
10: 字节长度 length(字段名,汉字占3个字节,字母占一个)
2:排序(order by 放最后,limit子句除外)
- 默认表示升序, asc 表示升序, desc表示降序,
select 字段1, 字段2 from 表名 order by 字段1 asc ; (表示选字段1和字段2,并且按照字段1的升序排序。)
- 多个字段排序
select 字段1, 字段2 from 表名 order by 字段1 sal asc, 字段2 sal desc;(按字段1先排序,字段1 相等的时候按照字段2排序)
3:分组函数(多行处理行数)
- count 计数
select count(*) from emp - sum 求和
select sum(sal) from emp - avg 平均值
- max 最大值
- min 最小值
注意:
1:分组函数自动忽略null
2:分组函数不可直接使用在where中,因为分组函数 group by 在 where后执行
3:count()表示总记录条数, cout(字段1)表示字段1不为null的记录总数
4:可以组合使用
select count(), sum(sal) from emp ;
5:分组函数要用select 查询结果作为返回值
select ename from emp where sal > (select avg(sal) from emp) ;
表示查询工资在平均工资以上的员工的名字
3.2:单行处理函数
1:大小写转换uppe, LOWER
- select upper(‘join’); select lower (‘JOIN’)
2:截取substr或者substring
- select substr(‘李莫愁爱上了陆展元’,6); 结果:了陆展元
- select substr(‘李莫愁爱上了陆展元’,1,3); 结果:李莫愁
3:instr(返回字串第一次出现的索引)
4:trim(去除空格)
- select trim(‘ 张翠山 ’)结果: 张翠山
- select trim(‘a’ from ‘aaaaaaa张aaaaaa翠山aaaaaaaaa’) as out_put; 结果:张aaaaaa翠山
5:lpad 左填充,rpad 右填充
- select lpad(‘殷素素’,12,‘ab’); 结果:ababababa殷素素
6:replace(替换)
- select replace(‘张无忌爱上了周芷若’, ‘周芷若’,‘赵敏’); 结果:张无忌爱上了赵敏
7:数学函数
- 四舍五入:round
-: select round(-2.8); 结果:-3 - 向上取整:ceil
- 向下取整:floor
- 截断:truncate
- :select truncate(1.991,1); 结果:1.9 ,表示小数点后保留1位 - 取余:mod(a,b) = a - a/b*b
select ename, (sal + comm) * 12 as yearssal from emp;
你会发现在选择字段的时候可以给字段做处理、月薪 x 12就是年薪。
- 但是要注意: 只要在数学表达式中出现了 null ,则运算结果就是null, 解决办法:ifnull(字段,0)
即表示如果该字段某值是null 则将该值变为0
3.3: group by 和 having
1:select max(sal) from emp group by job; 表示(查询job相同的最大工资)
2:当一条语句中有group by 时候select 后只能跟分组函数和参与分组的字段
eg: select ename, max(sal) from emp group by job; 这是错误的
3:多个字段联合分组
找出每个部门不同工作岗位的最高薪资
select deptno、job、max(sal) from emp group by deptno,job;
4:完整的DQL语句顺序
select
…5
from
… 1
where
…2
group by
…3
having
…4 必须和group by 一起用
order by
…6
4:连接查询
连接:
ename 在emp中,dname在dept表中.
(1):select ename, dname from emp, dept;
(2):select e.ename, d.dname form emp e, dept d;--------------->取别名的可读性比较好,执行效率高。
(3)避免笛卡了集现象,加where条件、但匹配次数还是不变
内连接
两张表的地位相同,匹配不上就不会显示
- 等值连接:join on 语法(sql99 语法,表的连接条件和where 分离了)
- 不等值连接
- 自连接
select a.aname as ‘员工名’, b. bname as ‘领导’
from
emp a,
inner join
emp b
on
a.mgr = b.empno;
外连接
两张表,A表为主表,B表是副表。A表一定会显示出来,如果B表匹配则显示B。否则只显示A
- 左外连接(左边的表是主表)
- select a.aname as ‘员工名’, b. bname as ‘领导’
from
emp a,
left join
emp b
on
a.mgr = b.empno; - 右外连接(右边的表是主表)
-
- select a.aname as ‘员工名’, b. bname as ‘领导’
from
emp a,
right outer join
emp b
on
a.mgr = b.empno;
- select a.aname as ‘员工名’, b. bname as ‘领导’
union(结果集相加)
select ename,job from emp where job = ‘MANAGER’
union
select ename,job from emp where job = ‘SALESMAN’;
limit (分页查询 Mysql特有)
limit是sql语句最后执行环节,starIndex, length
starlndex 表示起始位置
length 表示取几个
- 环节顺序
select 5
…
from 1
…
where 2
…
group by 3
…
having 4
…
order by 6
…
limit
…; 7
5:日期函数
-
返回当前系统日期 + 时间
select now(); -
返回当前系统日期
select curdate();
-返回当前时间
select curtime(); -
获取指定的部分, 年、月、日、小时、分钟、秒
年:year(now());
月:month(now()); monthname(now());
日:date(now()); -
日期格式转换 str_to_date
select str_to_date(‘1998-3-2’,’ %y-%c-%d’); -
将日期转换成字符 date_format
select date_format(now(), ‘%y年%m月%d日’);
6:流程函数
- if函数
select if(10 < 5, ‘大’,‘小’); - case
用法1:
select salary 原始工资,
case departmant_id
when 30 then salary * 1.1
when 40 then salary * 1.2
when 50 then salary * 1.3
end as 新工资 from 表名
用法2:类似于多重if
select salary, case
when salary > 1000 then ‘A’
when salary > 2000 then ‘B’
else ‘C’
end as 工资级别 from 表名
’
三、创建表
1:语法
(1)格式
create table 表名{ 字段名1: 数据类型, 字段名2:数据类型 default 1 (默认值是1),
… }
(2)常见的数据类型
(3)插入
- 插入值
也可以一次插入多行value数据值,前提是value间用 逗号(,)隔开。
INSERT INTO Customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip)
VALUES('1000000006',
NULL,
NULL,
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111');
- 插入另一张表 INSERT SELECT
INSERT INTO Customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM CustNew;
日期: