mysql中命令前加号_MySql总结（基础）

MySql总结(基础)

前言：笔记所需数据库在我的博客资源里面

cmd命令

常见命令：登录命令：mysql -h主机名 -p端口号 -u用户名 -p密码

如果：本机的mysql的话主机名和端口号都可以省

连接数据库服务器：cmd下输入net stop/start mysql

打开数据库：use 数据库名;

查看数据库的表：show table;

在这个数据库看别的数据库：show tables from 数据库名字；

查看自己在哪个数据库：select database();

创建一个表：create table 名字(加回车

加要点)；

查看表的结构：desc 表名；

查看表的数据：select * from 表名；

添加一个表名：insert into stuinfo(id,name) value(1,‘junbin’);

查看服务器版本：select version();

#进阶一：基础查询

/*

语法：

select 查询列表 from 表名

特点： 1·可以查询表中的字段，常量，表达式，函数

2·查询结果是一个虚拟表格

*/

#查询表中的单个字段

SELECT last_name FROM employees;

#查询表中多个字段

SELECT last_name,salary,email FROM employees;

#查询表中的所有字段

SELECT

employee_id,

first_name,

last_name,

phone_number,

job_id,

salary,

commission_pct,

manager_id,

department_id,

hiredate

FROM

employees ;

SELECT

*

FROM

employees ;

#查询常量值

SELECT 100;

SELECT ‘john’;

SELECT 10*2;

#查询函数

SELECT VERSION();

#查看变量的语法

SHOW VARIABLES LIKE ‘autocommit’;

#查看存储引擎

SHOW ENGINES;

一些细节语法

#使用concat拼接

SELECT CONCAT (‘a’,‘n’,‘d’) AS 结果;

SELECT CONCAT(last_name,first_name) AS 名字 FROM employees;

null和其他任何值拼接都为null

可以用ifnull

#+号的作用:在sql中加号只有一个功能：运算符

#案列：查询员工名和员工姓连接成一个字段并显示成姓名

SELECT ‘123’+90;#其中有一个是字符一个是数字这将字符转换成数字成功的话，再进行相加。

#如果转换失败则将字符变成0；如下：

SELECT ‘john’+123;

SELECT last_name

Sql查询语句：

#一·按条件表达式查询

#案例1：查询员工工资大于12000的员工信息

SELECT * FROM employees WHERE salary>12000;

#案例二：查询部门编号不等于90号的员工名和部门编号

SELECT

first_name,last_name,department_id

FROM employees

WHERE department_id<>90;

#二·按逻辑表达式筛选

#案例一：查询工资在一万到两万之间的员工名，工资和奖金

SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary<=20000;

#案列二：查询部门编号不在90到110之间的,或者工资高于15000的

SELECT * FROM employees WHERE department_id<90 OR department_id>110 OR salary>15000;

SELECT* FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;

#三·模糊查询

/*

like

between and

in

is null|is not null

通配符：

% 任意多个字符

_ 单个字符

*/

#1`like

#案例一：查询员工名中含有字符a的员工信息

SELECT * FROM employees WHERE last_name LIKE ‘%a%’;

#案例二： 查询员工名中第二个字符是h第五个字符是l的所有员工信息

SELECT * FROM employees WHERE last_name LIKE ‘__c_h%’;

#案例三： 查询员工名中有下滑线的所有信息

SELECT* FROM employees WHERE last_name LIKE ‘_%’;# ’\‘是转义符

SELECT* FROM employees WHERE last_name LIKE '#_%’ ESCAPE ‘#’; #escape 为解释转义的函数

#2 between and 的使用

#注意事项：使用between and的时候是包含临界值，而且值不可以颠倒顺序

#案例一：查询员工编号在100到120之间的员工所有信息

#可以用这样

USE myemployees;

SELECT * FROM employees WHERE employee_id>=100 AND employee_id <=120;

#但是用between and 的话我可以更加方便使用

SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

#两者都是一模一样的效果

#3 in 的用法

/*

含义：判断某字段的值是否属于in列表中的某一项

特点：

1，使用in比or提高语句简洁度

2，in列表的值类型必须一致或兼容

*/

#案例：查询员工的工种编号是 IT_PROG ,AD_VP, AD_PRES中的一个员工名和工种编号

SELECT

​ last_name,

​ job_id

​ FROM

​ employees

​ WHERE

​ job_id=‘IT_PRH’ OR job_id =‘AD_VP’ OR JOB_ID =‘AD_PRES’;

#还可以用in直接省略很多东西

SELECT last_name,job_id FROM employees WHERE job_id IN (‘IT_PRH’,‘AD_VP’,‘ad_pres’);

4#，is null 和is not null

#案例1；查询没有奖金的员工名和奖金率

SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;

#相反的如果我要查询有奖金的员工名的话只要加个not如下

SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

5#安全等于 <=>

#不仅可以判断not还可以判断是否等于数字

#但是可读性比较差

#进阶3：排序查询

引入

/*

语法：

select查询列表

from 表

{where 筛选条件}

order by 排序列表{asc|desc}(升序|降序)不写的话默认为升序

*/

#案例查询员工信息，要求工资从高到低

SELECT * FROM employees ORDER BY salary DESC;

SELECT *FROM employees ORDER BY salary ;

#案例二：

#查询部门编号>=90的员工信息，按入职时间的先后进行排序

SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate ;

#案例三：

#按年薪的高低显示员工的信息和年薪{按表达式排序)

SELECT ,salary12*(1+IFNULL(commission_pct,0)) 年薪 FROM employees ORDER BY salary12(1+IFNULL(commission_pct,0)) DESC;

#案例四：直接用别名代替就可以了

SELECT ,salary12*(1+IFNULL(commission_pct,0)) 年薪

FROM employees ORDER BY 年薪 DESC;

#案例五：按姓名的长度来显示员工的工资

SELECT LENGTH(last_name) 字节长度,last_name,salary

FROM employees

ORDER BY 字节长度;

#案例六： 查询员工信息，要求先按工资排序，再按员工编号排序{多个字段排序}

SELECT * FROM employees ORDER BY salary,employee_id DESC;

函数

字符函数

#length

SELECT LENGTH(‘john’);

SELECT LENGTH(‘as安家费’);

#concat 拼接字符串

SELECT CONCAT(last_name,’_’,first_name) 姓名 FROM employees;

#upper,lower

SELECT UPPER (‘john’);

SELECT CONCAT(UPPER(‘chane’),LOWER(‘asdfAFJ’)) 心 FROM employees;

#上例可以证明sql函数可以嵌套

#substr|sunsrting(截取函数)有不同的截取方式可以选择

#案例： 姓名中首字母大写其他字符小写然后用_拼接，显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),LOWER(SUBSTR(last_name,2))) FROM employees;

#insrt

作用：返回字串第一次出现的索引没有的话返回0

SELECT INSTR(‘abcdadfasfa’,‘fa’);

#trim

作用：去掉特定字符(前后去掉)；

SELECT TRIM(’ 沉舟 ') AS out_put;

SELECT TRIM(‘a’ FROM ‘aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaakkk’);

SELECT TRIM(‘a’,‘k’ FROM ‘aaaaaaaaaaaaaaaaaaaassdaaaaaaaaaaaaaaaaaaaaaaakkfk’);

#lpad

作用：用指定的字符实现左填充到指定长度

SELECT LPAD(‘asdfa’,12,‘4’);

#rpad

作用：用指定的字符实现右填充到指定长度

SELECT RPAD(‘adsfa’,20,‘a’);

#数学函数

#round 四舍五入

SELECT ROUND(152.36514);

SELECT ROUND(12.2564,2);#小数点后两位

#ceil 向上去整

返回>=该参数的最小整数(ceil 天花板)

SELECT CEIL(1.02);

#floor 向下取整，和上同理

SELECT FLOOR(1.02)

#truncate 截断

SELECT TRUNCATE(1.688789,1);

#mod 取余

SELECT MOD(10,4);

#三，日期函数

#now 当前系统日期+时间

SELECT NOW();

#curdate 当前系统日期，但不包含时间

SELECT CURDATE();

#curtime 当前系统时间，比包含日期

SELECT CURTIME();

#可以获得指定的日期 如：年月日小时分钟秒

SELECT YEAR(NOW());

SELECT MONTH(NOW());

SELECT DAY(NOW());

SELECT HOUR(NOW());

#str_to_date 将字符串通过指定的格式转换成日期

SELECT STR_TO_DATE(‘2000,12,26’,’%Y,%c,%d’);

#查询入职日期为1992-4-3的员工信息

SELECT * FROM employees WHERE hiredate = ‘1992-4-3’;

SELECT * FROM employees WHERE hiredate = STR_TO_DATE(‘1992.4.3’,’%Y.%m.%d’);

#date_format 将日期转换成字符

SELECT DATE_FORMAT(NOW(),’%Y年%m月%d日’);

#查询有奖金的员工名和入职日期(xx年xx月xx日)

SELECT last_name,DATE_FORMAT(hiredate,’%Y年%m月%d日’) 入职日期 FROM employees WHERE commission_pct IS NOT NULL;

#其他函数

SELECT VERSION();

SELECT DATABASE();

SELECT USER();

#分组函数

#案例2：

#查询员工中电子邮件有a字符的，每个部门的平均工资

SELECT AVG(salary),email,department_id FROM employees WHERE email LIKE ‘%a%’ GROUP BY department_id;

#案例3：查询有奖金的每个领导手下员工的最高工资

SELECT MAX(salary),manager_id FROM employees WHERE commission_pct IS NOT NULL

GROUP BY manager_id;

#升升级级版

#案例3：查询哪个部门的员工个数大于2

#1 查询每个部门的员工数

SELECT COUNT(),department_id FROM employees

GROUP BY department_id;

#2 查询员工数大于2的部门

SELECT COUNT(),department_id FROM employees

GROUP BY department_id

HAVING COUNT(*)>2;

#案例：查询每个工种有奖金的员工最高工资大于12000的工种编号和最高工资

#1查询有奖金的员工

SELECT last_name FROM employees WHERE commission_pct IS NOT NULL;

#2查询最高工资大于12000 的工种编号和最高工资

SELECT MAX(salary),job_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id

HAVING( MAX(salary)>12000);

#案例：查询领导编号大于102的每个领导手下的最低工资大于5000的领导编号是哪个，以及其最低工资

#我膨胀了，直接上，不分步了！

SELECT MIN(salary),manager_id

FROM employees

WHERE manager_id>102

GROUP BY manager_id

HAVING MIN(salary)>5000;

#流程控制函数

#if 函数： ifelse的效果

USE myemployees;

SELECT IF(10>5,‘big’,‘small’) da;

SELECT last_name,commission_pct,IF(commission_pct IS NULL,‘aa’,‘bb’)

FROM employees;

#case函数的使用一： switch case 的效果

/*

mysql中

case 要判断的字段或表达式

when 常量一： then 要显示的值1或语句；

。

。

。

。

else 值或语句

end

同理以此类推

/

#案例：查询员工的工资和要求

/

部门号=30，显示的工资为1.1倍

部门号=40，显示的工资为1.2倍

部门号=50，显示的工资为1.3倍

其他部门为原工资

*/

SELECT salary 工资,department_id,

CASE department_id

WHEN 30 THEN salary1.1

WHEN 40 THEN salary1.2

WHEN 50 THEN salary*1.3

ELSE salary

END AS 工资变化表

FROM employees;

SELECT employee_id,salary,

CASE employee_id

WHEN 110 THEN salary*1.2

ELSE salary

END AS 测试一下

FROM employees;

#case 函数的使用二：

#类似于 多重if

/*语法：

case

when 条件一 then 要显示的值或语句

when 条件二 then 。。。。。。。

else 要显示的东西

end

*/

#案例：员工编号小于110工资加五百，员工编号大于110小于120其工资加一千

SELECT * FROM employees;

SELECT employee_id,last_name,salary,

CASE

WHEN employee_id<110 THEN salary+500

WHEN employee_id>110 AND employee_id<120 THEN salary+1000

ELSE salary END AS 新工资

FROM employees;