Mysql基础1

基础1

Mysql基础概念

DB：数据库（ database ）：存储数据的“仓库”，它保存了一系列有组织的数据。
DBMS：数据库管理系统（ Database Management System ），数据库是通过 DBMS 创建和操作的容器。
SQL：结构化查询语言（ Structure Query Language ），专门用来与数据库通信的语言。

SQL 的优点：

1. 不是某个特定数据库供应商专有的语言，几乎所有DBMS都支持SQL。
2. 简单易学。
3. 虽然简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

5.5版本安装

1. 安装：


2. 安装完成，开始配置：

如果不小心关掉配置页面，可以通过D:\Program Files\MySQL\MySQL Server 5.5\bin\MySQLInstanceConfig.exe打开。

mysql配置文件

D:\Program Files\MySQL\MySQL Server 5.5\my.ini
如果修改服务端配置，需要重启mysql服务。

启动和停止

方法一

开始 —> 右击计算机，选择管理 —> 服务 —> 找到MySQL。
也可以设置是否开机自启动。

方法二

启动： net start mysql
停止： net stop mysql

连接MySQL

使用mysql自带客户端

使用mysql自带客户端方式只能登陆root用户。

输入密码进入mysql：

CMD进入

mysql -h 用户名 -P 端口号 -u 用户名 -p 密码

注意： 如果直接输入密码，-p和密码之间不能有空格。

基本命令

查看数据库列表： show databases;
选择数据库： use 库名;
查看表： show tables [from 库名];
查看当前在哪个数据库： select database();
查看数据库版本： select version(); 或命令行执行：mgsql --version
查看表结构： desc 表名;
查看字符集： SHOW VARIABLES LIKE “%char%”;

Mysql语法规范

1. 命令不区分大小写，但是建议关键字大写，表名、列名小写。
2. 每条命令以分号结尾。
3. 注释： # 单行注释； – 单行注释 ； /* 多行注释 */

图形界面SQLyog

导入测试数据：


表字段信息：

DQL（Data Query Language） 查询语言

基础查询

语法： select 查询列表 from 表名；

1. 查询单个表字段： SELECT colume FROM 表名;
2. 查询多个表字段： SELECT colume1,colume2 FROM 表名;
3. 查询表所有字段： SELECT * FROM 表名;
4. 查询常量值： SELECT 100;
5. 查询表达式： SELECT 100*99;
6. 查询函数： SELECT VERSION();
7. 起别名： SELECT 100*98 AS result; （①提高可读性 ②连接查询时如果有重名，可以通过别名区分开）
8. 去重： SELECT DISTINCT department_id FROM employees;
9. 拼接： SELECT CONCAT(last_name,first_name) FROM employees;

条件查询

语法： select 查询列表 from 表名 where 筛选条件；

按条件表达式筛选

运算符： >、<、 <>（也可以使用!=代表不等于）、 =、 >=、 <= 、

# 1. 查询员工薪资大于12000
SELECT * FROM employees WHERE salary > 12000;  
# 2. 查询员工id不为90的员工名和所属部门id       
SELECT first_name, department_id FROM employees WHERE employee_id!=90;
# 3. 查询员工id为176的员工名，部门id，年薪
#    因为commission_pct有可能为null，即没有年薪，所以通过ifnull关键字判断，如果为null就取0
SELECT first_name,department_id,salary*12*(1+IFNULL(commission_pct, 0)) AS 'yearly salary' FROM employees WHERE employee_id=176;

按逻辑表达式筛选

逻辑运算符： and、or、not 或&&、||、!

# 查询员工薪资为10000-20000之间的员工名
SELECT first_name,salary FROM employees WHERE salary>10000 AND salary <20000;  

# 查询员工薪资大于15000或部门id不在90到110之间的员工信息
SELECT first_name,salary,department_id FROM employees WHERE (department_id<90 OR department_id>110) OR salary>15000;
SELECT first_name,salary,department_id FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000;

模糊查询

like

一般与通配符搭配使用。
通配符：

1. % ： 代表任意多个字符，包含0个字符，但是不能匹配NULL。
2. _ ： 代表任意一个字符。

# 1. 员工名中包含字符a的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';
# 2. 员工名中第三个字母为n，第五个字母为l的员工信息
SELECT * FROM employees WHERE last_name LIKE '__n_l%';
# 3. 查询员工名中第二个字符为_的员工名---加\防止_转义为通配符
SELECT * FROM employees WHERE last_name LIKE '_\_%';
#    也可以通过ESCAPE关键字自定义转义字符
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

# 4. 如下两个查询语句不等价，因为commission_pct 有可能为null，而通配符只能匹配字符，不能匹配null
SELECT * FROM employees;
SELECT * FROM employees WHERE commission_pct LIKE "%";

between and

1. between and是闭区间；
2. employee_id BETWEEN 100 AND 120 等价于 employee_id>=100 AND employee_id<=120

# 1. 查询员工编号再100到120之间的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

in

用于判断某个字段的值在给定的列表内。
注意： in列表中的值必须类型统一，并且不可以使用通配符。

# 1. 查询员工工种是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
SELECT first_name,job_id FROM employees WHERE job_id IN('IT_PROG','AD_VP','AD_PRES');

is null

=或!=(或<>)不能判断 null，需要使用is null或is not null关键字。

# 判断没有奖金的员工名和奖金率
SELECT first_name,commission_pct  FROM employees WHERE commission_pct IS NULL;
# 判断有奖金的员工名和奖金率
SELECT first_name,commission_pct  FROM employees WHERE commission_pct IS NOT NULL;

安全等于 <=>

既可以判断数值类型，也可以判断null。

# 判断没有奖金的员工名和奖金率
SELECT first_name,commission_pct FROM employees WHERE commission_pct <=> NULL;
# 判断工资等于12000
SELECT first_name,salary FROM employees WHERE salary <=> 12000;

排序查询

语法： select 查询列表 from 表名 [where 查询条件] order by 排序列表 [asc|desc]; order by子句一般是放在查询语句最后面，limit语句除外。
注意： asc 升序，desc降序，默认升序。

# 1. 按照员工薪资降序
SELECT * FROM employees ORDER BY salary DESC;
# 2. 查询部分编号>=90的员工信息，按入职时间先后排序
SELECT * FROM employees WHERE department_id>=90 ORDER BY hiredate;
# 3. 按年薪高低显示员工信息和年薪【表达式排序】
SELECT *,salary*12*(1+IFNULL(commission_pct, 0)) AS yearly_salary FROM employees ORDER BY yearly_salary DESC;
# 4. 按姓名长度显示员工的姓名和工资【按函数排序】
SELECT LENGTH(first_name) AS name_lenght, last_name, salary FROM employees ORDER BY name_lenght;
# 5. 查询员工信息，要求先按工资升序排序，再按员工编号降序排序【多个排序条件】
SELECT * FROM employees ORDER BY salary ASC,employee_id DESC;

常见函数

将一组逻辑语句封装在方法体重，对外暴露方法名，提高代码复用性。
语法： select 函数名() [from 表]；

单行函数

字符函数

# 1. length 查询字符有几个字节---英文每个字母占1个字节；utf8编码下中文占3个字节，gbk编码下中文占2个字节。
SELECT LENGTH("john");   # 4个字节
SELECT LENGTH("张三丰");  # 9个字节

# 2. concat 拼接字段，如下拼接为last_name_first_name
SELECT CONCAT(last_name, "_", first_name) FROM employees;

# 3. upper、lower，转换字符大小写
SELECT CONCAT(UPPER(last_name), "_", LOWER(first_name)) FROM employees;

# 4. substr
SELECT SUBSTR('李莫愁爱上了陆展元', 7);     # 从第7个字符开始截取，SQL中下标是从1开始的。
SELECT SUBSTR('李莫愁爱上了陆展元', 1,3);   # 截取1-3个字符，闭区间
	# last_name 首字母大写，其他字母小写
SELECT CONCAT(UPPER(SUBSTR(last_name, 1, 1)), "_", LOWER(SUBSTR(last_name, 2))) FROM employees;

# 5. instr， 返回子串第一次出现的下标，找不到的话返回0
SELECT INSTR('李莫愁爱上了陆展元', '陆展元');   # 返回7

# 6. trim 去掉前后指定，默认去掉空格
SELECT TRIM("    aaa    ") AS out_str;
SELECT TRIM('a' FROM "aaa哈哈哈aaa哈哈哈aaaaaa") AS out_str;

# 7. lpad 指定长度左填充， rlap类似
SELECT LPAD("殷素素", 10, "*") AS out_str;     # *******殷素素
SELECT LPAD("殷素素", 2, "*") AS out_str;      # 殷素

# 8. replace  替换所有字符
SELECT REPLACE("周芷若爱上了张无忌，周芷若", "周芷若", "赵敏") AS out_str;      # 赵敏爱上了张无忌，赵敏

数学函数

# 1.  round 四舍五入
SELECT ROUND(-4.65) AS out_data;         #  -5
SELECT ROUND(-4.658, 2) AS out_data;     #  -4.66  小数点后保留两位，并四舍五入

# 2. ceil 向上取整
SELECT CEIL(1.01) AS out_data;           #  2

# 3. floor 向下取整
SELECT FLOOR(1.91) AS out_data;          #  1
SELECT FLOOR