mysql数据库技术1——基本的增删查改的sql语句

1、数据库语言的分类

DDL:数据库定义语言 data Definition language

用于创建、修改、和删除数据库内的数据结构,如:

1:创建和删除数据库(CREATE DATABASE || DROP DATABASE);

2:创建、修改、重命名、删除表(CREATE TABLE || ALTER TABLE|| RENAME TABLE||DROP TABLE,TRUNCATE TABLE);

3:创建和删除索引(CREATEINDEX || DROP INDEX)

DML:数据操控语言 data Manipulation language

修改数据库中的数据,包括插入(INSERT)、更新(UPDATE)和删除(DELETE)

DCL:数据控制语言 (管理用户权限)data Control language

用于对数据库的访问,主要包括创建用户、给用户授权、对用户撤销授权、查询用户授权和删除用户等

,如:1.创建用户(create user)2:给用户授予访问权限(GRANT);3:取消用户访问权限(REMOKE),4删除用户(drop user)。

DQL:数据查询语言 data Query language

从数据库中的一个或多个表中查询数据(SELECT)

下面一一来介绍这四种类型的语言

2、DDL:数据库定义语言 data Definition language

2.1、创建数据库,并且以utf8的字符集创建

-- 如果不存在,则创建数据库,并且以utf的字符集创建
CREATE DATABASE IF NOT EXISTS westos DEFAULT CHARACTER SET = 'utf8'

2.2、如果存在,删除数据库

-- 如果存在,则删除数据库
DROP DATABASE IF EXISTS westos

2.3、使用数据库

-- 使用数据库
USE westos

2.4、查看所有的数据库

-- 查看所有的数据库
SHOW DATABASES

2.5、查看所有的数据表

-- 查看所有的数据表
SHOW TABLES

2.6、创建表

-- 创建学生表,如果不存在
CREATE TABLE IF NOT EXISTS student (
	`id` INT NOT NULL AUTO_INCREMENT COMMENT '学生表id',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET = 'utf8'

2.7、查看创建数据库和数据表的语句

-- 查看创建数据库的语句
SHOW CREATE DATABASE westos
-- 查看创建数据表的语句
SHOW CREATE TABLE student

2.8、查看表的结构

-- 查看表的具体结构
DESC student

3、DQL:数据查询语言 data Query language

3.1、修改表名

-- 修改表名
ALTER TABLE student RENAME AS teacher

3.2、增加表的字段

-- 增加表的字段
ALTER TABLE teacher ADD age INT 

3.3、修改表的字段

-- 修改表的字段(重命名,修改约束)
ALTER TABLE teacher MODIFY age VARCHAR(11)	-- 修改约束,不能改字段名
ALTER TABLE teacher CHANGE age is_del INT 	-- 字段重命名

3.4、删除表的字段

-- 删除表的字段
ALTER TABLE teacher DROP is_del

3.5、删除表

 -- 删除表
 DROP TABLE IF EXISTS student

3.6、插入语句

 -- 插入语句(添加)
 INSERT INTO teacher (`name`) VALUES ('xgp')

3.7、修改语句

-- 修改语句
UPDATE teacher SET `name`='xgp',sex='女' WHERE id=1

3.8、设置数据库的时间

-- 设置数据库的时间
UPDATE `teacher` SET `birthday` = CURRENT_TIME WHERE id=1

3.9、删除指定数据

-- 删除指定数据
DELETE FROM teacher WHERE id=1

3.10、清空一个数据表

-- 完全清空一个数据库表,表的结构和索引约束不变,自增会归零
TRUNCATE TABLE teacher

4、DQL:数据查询语言 data Query language

4.1、使用别名查询

-- 查询(使用别名)
SELECT `name` 姓名,pwd 密码 FROM teacher 老师表

4.2、拼接字符串

-- 函数 拼接字符串 Concat(a,b)
SELECT CONCAT('姓名:',`name`) 新名字 FROM teacher

4.3、去重

-- 去重
SELECT DISTINCT `name` FROM teacher 

4.4、查看系统版本

-- 查看系统版本
SELECT VERSION()

4.5、计算结果

-- 计算结果
SELECT 100*3-23 计算结果
SELECT pwd+'1' 密码加1 FROM teacher

4.6、模糊查询

-- 模糊查询
-- like结合 %(代表0到任意个字符) _(代表一个字符)
SELECT `name`,pwd FROM teacher
WHERE `name` LIKE '_g_'

-- in
SELECT id,`name`,pwd FROM teacher
WHERE id IN (4,7,10)

4.7、排序查询

-- 排序:升序 ASC 降序 DESC
-- order by 通过哪个字段排序,怎么排
SELECT * FROM teacher ORDER BY id DESC 
SELECT * FROM teacher ORDER BY id ASC 

4.8、分页查询

-- 分页:
SELECT * FROM teacher ORDER BY id DESC LIMIT 2,10

5、常用函数

5.1、数学函数

-- 常用函数
SELECT ABS(-78) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT SIGN(-9)	-- 判断一个数的符号

5.2、字符串函数

-- 字符串函数
SELECT CHAR_LENGTH('哈哈哈') -- 字符串长度
SELECT CONCAT('xgp','aaa')	-- 拼接字符串
SELECT INSERT('asa',1,2,'ss')		-- 替换字符串
SELECT LOWER('AAAAAAAA')	-- 转小写
SELECT UPPER('aaaaaaaa')	-- 转大写
SELECT INSTR('aaaaa','a')	-- 返回第一次出现子串的索引
SELECT REPLACE('aaaa','a','x')	-- 替换字符串
SELECT SUBSTR('asasasxsssas',4,6)	-- 截取字符串,截取的位置,截取的长度
SELECT REVERSE('aasss') -- 字符串反转

5.3、时间和日期函数

-- 时间和日期函数(获取当前日期)
SELECT CURRENT_DATE(); -- 获取当前日期
SELECT CURDATE();	-- 获取当前日期
SELECT NOW(); -- 获取当前的时间
SELECT LOCALTIME(); -- 获取本地时间
SELECT SYSDATE() -- 获取系统时间

5.4、系统函数

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

5.5、聚合函数

-- 聚合函数
SELECT COUNT(*) FROM teacher
SELECT COUNT(字段) FROM teacher	-- 会忽略null值
SELECT COUNT(1) FROM teacher
SELECT COUNT(主键) FROM teacher -- 速度更快

SELECT SUM(pwd) 求和 FROM teacher
SELECT AVG(pwd) 平均 FROM teacher
SELECT MAX(pwd) 最高 FROM teacher
SELECT MIN(pwd) 最低 FROM teacher

6、DCL:数据控制语言 (管理用户权限)data Control

6.1、创建用户

-- 创建用户
CREATE USER xgp123 IDENTIFIED BY '123456'

6.2、修改密码

-- 修改密码(修改当用户密码)
SET PASSWORD = PASSWORD('111111')
-- 修改密码(修改自定的用户的密码)
SET PASSWORD FOR xgp123 = PASSWORD('123456')

6.3、给用户重命名

-- 给用户重命名
RENAME USER xgp123 TO xgp

6.4、用户授权

-- 用户授权(授予全部的权限,库,表)
GRANT ALL PRIVILEGES ON *.* TO xgp

6.5、查看指定用户权限

-- 查看指定用户权限
SHOW GRANTS FOR xgp

6.6、查看管理员权限

-- 查看管理员权限
SHOW GRANTS FOR root@localhost

6.7、撤销权限

-- 撤销权限(撤销全部权限)
REVOKE ALL PRIVILEGES ON *_* FROM xgp

7、事务

7.1、开启/和关闭事务的自动提交,mysql默认是开启的

-- 事务(测试转账)mysql 是默认开启事务自动提交的
SET autocommit = 0;	-- 先关闭事务提交
SET autocommit = 1;	-- 开启

7.2、进行手动处理事务

-- 事务开启
START TRANSACTION -- 标记一个事务的开始

-- 提交
COMMIT

-- 回滚
ROLLBACK

-- 事务结束

-- 了解
SAVEPOINT -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名,会滚到保存点

7.3、通过转账案例手动测试事务

-- 编写转账案例
CREATE TABLE account (
id INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT CHARSET = 'utf8'

INSERT INTO account(`name`,money) VALUES ('张三',2000.00),('李四',10000.00)

-- 模拟转账
SET autocommit = 0;	-- 关闭自动提交
START TRANSACTION; -- 开启事务
UPDATE account SET money = money - 500 WHERE `name` = '张三';
UPDATE account SET money = money + 500 WHERE `name` = '李四';

COMMIT;	-- 提交事务
ROLLBACK; -- 回滚
SET autocommit = 1;	-- 恢复默认值

8、其他技术

8.1、对数据库进行备份

命令行执行导出命令 mysqldump
mysqldump -hlocalhost -uroot -p123456 westos > D:/a.sql

8.2、对数据库进行导入

-- 导入
source sql文件路径

8.3、对数据库进行md5加密

-- 数据库级别的MD5加密
UPDATE teacher SET pwd = MD5(pwd)
-- 插入时进行加密
INSERT INTO teacher(`name`,pwd) VALUES ('aaa',MD5('123456'))
-- 如何校验
SELECT * FROM teacher WHERE `name`='aaa' AND pwd = MD5('123456')

9、mysql基础语法的详细语法格式文档

mysql产品的常见命令

1,mysql服务的启动和停止

方式一:计算机——右键管理——服务
方式二:通过管理员身份运行
	net start 服务名(启动服务)
	net stop 服务名(停止服务)

2,mysql服务的登陆和退出

方式一:通过mysql自带的客户端
	只限于root用户
方式二:通过cmd命令行
	登陆:
		mysql [-h主机名 -P端口号] -u用户名 -p[密码]
	退出:
		exit	或者		ctrl+c

3,sql语句的基本使用

1.查看当前所有数据库
	show databases;
2.打开指定库
	user 库名
3.查看当前库的所有表
	show tables;
4.查看其它库的所有表
	show tables from 库名;
5.创建表
	create table 表名 (
		列名 列类型,
		列名 列类型,
		...
	);
6.查看表结构
	desc 表名;
7.查看服务器版本
	方式一:登陆到mysql服务端
		select version();
	方式二:没有登陆到mysql服务端
		mysql --version		或者 	mysql -V
8.注释
	单行:#注释文字
		-- 注释文字
	多行:/*注释文字*/

DQL语言的学习

阶段一:1,基础查询

语法:
	select 查询列表 from 表名;
查询列表:
	表中的字段,常量值,表达式,函数
1.查询表中的单个字段
	select last_name from employees;
2.查询表中的多个字段
	select last_name,salary,email from employees;
3.查询表中的所有字段
	select * from employees;

2,其他基础查询

1.查询常量值
	select 100;
	select 'join';
2.查询表达式
	select 100*98;
	select 100%98;
3.查询函数
	select version();

3,为字段起别名

1.方式一:(使用as)
	select 100%98 as 结果;
	select last_name as 姓 from employees;
2.方式二:(使用空格)
	select last_name 姓 from employees;

4,去重(distinct)

1.案例:查询员工表的所有部门的编号
	select distinct
		department_id
	from
		employees;

5,+号的作用

1.案例:查询员工名和姓连接成一个字段,并显示为 姓名
	错误做法:
		select 
			last_name + first_name as 姓名
		from
			employees;
	正确做法:(使用concat()函数)
		select 
			concat(last_name,first_name) as 姓名
		from 
			employees;

6,ifnull()函数的使用

格式:
	ifnull(判断是否为空的参数,若为空返回的值)
举例:
	select ifnull(commission_pct,0) as 奖金率;

###阶段二:1,条件查询 语法: select 查询列表 from 表名 where 筛选条件; 筛选条件: 1.按照条件表达式筛选 条件运算符: > < = .... 案例: select * from employees where salary > 12000; 2.按照逻辑运算符刷选 逻辑运算符: && || ! mysql推荐: and or not 案例: select * from employees where salary > 12000 and salary < 20000; 3.模糊查询 1.关键字:like 特点: 一般和通配符使用 % 任意多个字符,包含0个字符 _ 任意多个字符 案例:查询员工名中包含字符a的员工信息 select * from employees where last_name like '%a%'; 特殊案例(需要转义通配符 escape):查询员工名中第二个字符为_的员工信息 select * from employees where last_name like '_$_%' escape '$'; 2.关键字:between and 特点: 包含临界值 案例: select * from employees where salary between 12000 and 20000; 3.关键字:in 案例: select * from employees where job_id in ('IT_PROT','AD_VP','AD_PRES'); 4.关键字:is null 注意:=号不能判断null值 案例: select * from employees where commission_pct is null; ###2,两个特殊符号的介绍(<>,<=>) 1.<>相当于!=,不过mysql更推荐改写法 2.<=>安全等于,可以判断null值
###阶段三:1,排序查询 语法: select 查询条件 from 表 [where 筛选条件] order by 排序列表 [asc|desc] 注意: asc:升序 desc:降序 不写:默认升序 案例(从高到低 desc): select * from employee order by salary desc; 案例(从低到高 asc): select * from employee order by salary asc; 特殊案列:按姓名的字节长度显示员工的姓名和工资(按函数排序 length(last_name)) select length(last_name) 字节长度,last_name,salary from employees order by length(last_name) desc; ###2,多个字段排序 1.案例:查询员工信息,要求先按工资排序,再按员工编号排序【按多个字段排序】 select * from employees order by salary asc,employee_id desc;
###阶段四:常见函数 1.调用:select 函数名(实参列表) [from 表]; 2.分类:单行函数,聚合函数 ###1,字符函数 1.length(字符) 获取参数值的字节个数 select length('join'); select length('哈'); 2.concat(参数1,参数2,...) 拼接字符串 select concat(last_name,'_',first_name) 姓名 from employees; 3.upper(字符) lower(字符) select upper('dadada'); select lower('HJJJI'); 4.substr(字符,位置1,位置2),substring(字符,位置1,位置2) 截取字符 注意:mysql中索引从1开始 select substr('hduxshdfkjsf',2,6); 5.instr(字符,查找的字符字串) 放回子串第一次出现的索引,如果找不到放回0 select instr('dsfsfd','fs'); 6.trim(字符) 去除前后空格 select length(trim(' 长记性了 ')) as out_put 其他用法:去除前后指定字符 trim(字符 from 字符串); select trim('a' from 'aaaaaaaaa或许是第aaaaaacxhddjkhaaaaaaaaa') as out put; 7.lpad(字符,个数,填充字符) 用指定的字符实现左填充指定的长度 select lpad('xgp',2,'*') as out_put; 8.rpad(字符,个数,填充字符) 用指定的字符实现右填充指定的长度 select rpad('xgp',12,'*') as out_put; 9.replace(原字符串,要替换字符,替换字符) 替换 select replace('xgp123','xgp','123') as oup_put; ###2,数学函数 1.round(数值,保留几位小数) 四舍五入 select round(1.65); select round(1.675,2); 2.ceil(数值) 向上取整 select ceil(1.02); 3.floor(数值) 向下取整 select floor(-9.99); 4.truncate(数值,保留几位小数) 截断 select truncate(1.66,1); 5.mod(被取余数,取余子) 取余 select mod(-10,-3); ###3,日期函数 1.now() 放回当前系统的日期+时间 select now(); 2.curdate() 放回当前系统日期,不包含时间 select curdate(); 3.curtime() 放回当前系统时间,不包含日期 select curtime(); 4.可以获取指定的部分,年、月、日、小时、分钟、秒 select year(now()) 年; 5.str_to_date(格式,字符类型) 将日期格式的字符转换成指定格式的日期 select str_to_date('1998-3-2','%Y-%c-%d'); 6.date_format(格式,字符类型); 将日期转换成字符 select date_format(now(),'%y年%m月%d日'); ###4,其他函数 1.select version(); 查看mysql版本 2.select database(); 查看当前数据库 3.select user(); 查看当前用户 ###5,流程控制函数 1.if(判断条件,if结果,else结果)函数 if else 的效果 select if(10>5,'大','小'); 2.case()函数的使用一 case 要判断的字段或者表达式 when 常量1 then 要显示的值1或语句1 when 常量2 then 要显示的值2或语句2 when 常量3 then 要显示的值3或语句3 ...... else 要显示的值n或者语句n end
案例:查询员工工资,要求
	部门号=30,显示工资为1.1倍
	部门号=40,显示工资为1.2倍
	部门号=50,显示工资为1.3倍
	其他部门,显示为原工资

	SELECT 
		salary 原始工资,department_id ,
	CASE 
		department_id = 30 
	WHEN 30 THEN salary*1.1
	WHEN 40 THEN salary*1.2
	WHEN 50 THEN salary*1.3
	ELSE 
		salary
	END 
		AS 新工资
	FROM 
		employees;
3.case()函数的使用二
	case 
	when 条件1 then 要显示的值1或语句1
	when 条件2 then	要显示的值2或语句2
	......
	else 要显示的值n或语句n
	end

案例:查询员工的工资的情况
	如果工资>20000,A
	如果工资>15000,B
	如果工资>10000,C
	否则 D

	SELECT salary,
	CASE 
	WHEN salary > 20000 THEN 'A'
	WHEN salary > 15000 THEN 'B'
	WHEN salary > 10000 THEN 'C'
	ELSE 'D'
	END AS 工资级别
	FROM employees;

6,聚合函数

1.sum(字段) 求和 avg() 平均值 min() 最小值 max() 最大值 count() 计数
	select sum(salary) from employees;
2.特点:
	1.sum(),avg() 支持数值型
		min(),max(),count() 支持任何类型
	2.上述聚合函数都排除了null值
3.和distinct搭配使用
	select count(distinct salary),count(salary) from employees;
4.count() 函数的详细介绍
	select count(*) from employees;
	select count(1) from employees;
	效率:
		MYISAM存储引擎下,count(*) 的效率高
		INNODB存储引擎下,count(*)和count(1)的效率差不多,比count(字段)要高一些
5.和聚合函数使用,一般都不会带字段

###阶段五:1,分组查询 1.语法: select 分组函数,列 (要求出现在group by的后面) from 表 [where 筛选条件] group by 分组的列表 [order by 子句] 2.注意: 查询列表必须特殊,要求是分组函数和group by后出现的字段 3.案例1:查询每个工种的最高工资 select max(salary),job_id from employees group by job_id; ###2,添加分组前筛选 案例1:查询邮箱中包含a字符的,每个部门的平均工资 select avg(salary),department_id from employees where email like '%a%' group by department_id; 案例2:查询有奖金的每个领导手下员工的最高工资 select max(salary),manager_id from employees where commission_pct is not null group by manager_id; ###3,添加分组后筛选 1.案例1:查询哪个部门的员工个数>2 1.查询每个部门的员工个数 select count(*),department_id from employees group by deparment_id; 2.根据1的结果,查询哪个部门的员工数>2 select count(*),department_id from employees group by deparment_id; having count(*)>2; 2.案例2:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资 select max(salary),job_id from employees where commission_pct is not null group by job_id having max(salary) > 12000; ###4,按多个字段分组 1.案例:查询每个部门每个工种的员工的平均工资 select avg(salary),department_id,job_id from employees group by deparentment_id,job_id; ###5,添加排序 1.案例:查询每个部门各个工种的员工的平均工资,并且按平均工资的高低显示 select avg(salary) a,department_id,job_id from employees where department_id is not null group by job_id,department_id having avg(salary) > 10000 order by avg(salary) desc;
###阶段六:1,连接查询(多表查询) 笛卡尔乘积现象:没有有效的连接条件
如何避免:添加有效的连接条件
举例:
	SELECT 
		NAME,boyName 
	FROM 
		boys,beauty
	WHERE 
		beauty.boyfriend_id = boys.id;

分类:

按年代分类:
	sql92标准:仅仅只支持内连接
	sql99标准【推荐】:支持内连接+外连接(左外和右外) + 交叉连接
按功能分类:
	内连接:
		等值连接
		非等值连接
		自连接
	外连接:
		左外连接
		右外连接
		全外连接
	交叉连接

2,sql92标准

1,等值连接

1.案例1:查询员工名和对应的部门名
	SELECT 
		last_name,department_name
	FROM
		employees,departments
	WHERE
		employees.department_id = departments.department_id
2.案例2:查询每个工种的工种名和员工的个数,并且按员工个数升序
	SELECT 
		job_title,COUNT(*)
	FROM 
		employees e,jobs j
	WHERE 
		e.job_id = j.job_id
	GROUP BY
		job_title
	ORDER BY
		COUNT(*) DESC;
3.案例3:查询员工名、部门名和所在的城市(三表查询)
	SELECT
		last_name,department_name,city
	FROM 
		employees e,departments d,locations l
	WHERE
		e.department_id = d.department_id
	AND
		d.location_id = l.location_id
	AND
		city LIKE 's%'
	ORDER BY
		department_name DESC;

2,非等值连接
1.案例1:查询员工的工资和工资级别

	SELECT
		salary,grade_level
	FROM
		employees e,job_grades g
	WHERE
		salary BETWEEN g.lowest_sal AND g.highest_sal;

3,自连接
1.案例:查询 员工名和上级的名称

	SELECT 
		e.employee_id,e.last_name,m.employee_id,m.last_name,m.department_id
	FROM
		employees e,employees m
	WHERE 
		e.manager_id = m.employee_id;

3,sql99标准

1.语法:
	select
		查询列表
	from 
		表1 别名 连接类型
	join
		表2 别名
	on
		连接条件
	[where
		筛选条件]
	[group by
		分组]
	[having
		筛选条件]
	[order by
		排序列表]

2.连接类型
	内连接(*):inner
	外连接:
		左外(*):left [outer]
		右外(*):right [outer]
		全外:full [outer]
	交叉连接:cross join

1,内连接

1.语法:
	select
		查询列表
	from
		表1 别名
	inner join
		表2 别名
	on 
		连接条件

1,等值连接

1.案例1:查询哪个部门的员工个数>3的部门名和员工个数,并按照个数降序(添加排序)

	1.查询每个部门的员工个数
		SELECT 
			COUNT(*),department_name
		FROM 
			employees e
		INNER JOIN
			departments d
		ON
			e.department_id = d.department_id
		GROUP BY 
			department_name
	2.在1的结果上筛选员工个数>3的记录,并排序
		SELECT 
			COUNT(*),department_name
		FROM 
			employees e
		INNER JOIN
			departments d
		ON
			e.department_id = d.department_id
		GROUP BY 
			department_id
		HAVING 
			COUNT(*) > 3
		ORDER BY
			COUNT(*) DESC;

2,非等值连接

2.案例1:查询员工的工资级别

	SELECT 
		salary,grade_level
	FROM 
		employees e
	JOIN 
		job_grades g
	ON 
		e.salary 
	BETWEEN 
		g.lowest_sal 
	AND 
		g.highest_sal;
posted @ 2020-02-11 19:45  孤傲的咸鱼  阅读(712)  评论(0编辑  收藏  举报