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

Z时代
2024-01-10
分类：综合

database

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 teacherWHERE 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 teacherSELECT 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

sql语句的基本使用">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

连接条件