MySQL笔记（一）SQL基础语句相关

最新推荐文章于 2024-11-15 22:11:31 发布

而他温热如河

最新推荐文章于 2024-11-15 22:11:31 发布

阅读量709

点赞数 14

分类专栏：数据库文章标签：数据库 sql mysql 笔记

本文链接：https://blog.csdn.net/chuci0910/article/details/138765368

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

数据库：数据存储的仓库，数据是有组织的进行存储 DataBase (DB)

数据库管理系统：操纵和管理数据库的大型软件 DataBase Management System (DBMS)

SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准 Structured Query Language (SQL)

SQL

通用语法：

（1）单行或多行书写，分号结尾；

（2）可以用空格/缩进（个数不限制）增强语句可读性；

（3）不区分大小写，关键字建议大写；

（4）单行注释： --注释或者 #注释（MySQL特有）

多行注释： /*注释内容*/

语句分类：

DDL （Data Definition Language）：数据定义语言，用来定义数据库对象（数据库、表、字段）

DML （Data Manipulation Language）：数据操作语言，用来对于数据库表中的数据进行增删改

DQL （Data Query Language）：数据查询语言，用来查询数据库中表的记录

DCL （Data Control Language）：数据控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

1.查询
  查询所有数据库
  SHOW DATABASES；
  查询当前数据库
  SELECT DATABASE();
  查询当前数据库的所有表
  SHOW TABLES;
  查询表结构
  DESC 表名;
  查询指定表的建表语句
  SHOW CREATE TABLE 表名;
2.创建
  CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLIATE 排序规则];
  CREATE TABLE 表名(
        字段1 字段1类型[ COMMENT 字段1注释 ],
        字段2 字段2类型[ COMMENT 字段2注释 ],
        字段3 字段3类型[ COMMENT 字段3注释 ],
        ......
        字段n 字段n类型[ COMMENT 字段n注释 ]
  )[ COMMENT 表注释];
3.修改
  删除
  DROP DATABASE [IF EXISTS] 数据库名;
  删除表
  DROP TABLE [IF EXISTS] 表名;
  删除指定表，并重新创建该表
  TRUNCATE TABLE 表名;
  添加字段
  ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
  eg：为emp表增加一个新的字段“昵称”为nickname，类型为varchar(20)
      alter table emp add nickname varchar(20) comment '昵称';
  修改数据类型
  ALTER TABLE 表名 MODIFY 字段名 新数据类型（长度）；
  修改字段名和字段类型
  ALTER TABLE 表名 CHANGE 旧字段名 类型（长度）[COMMENT 注释] [约束];
  eg：将emp表的nickname字段修改为username，类型为varchar（30）
      alter table emp change nickname username varchar(30) comment '昵称'；
  删除字段
  ALTER TABLE 表名 DROP 字段名;
  eg：将emp表的字段username删除
      alter table emp drop username;
  修改表名
  ALTER TABLE 表名 RENAME TO 新表名;
4.使用
  USE 数据库名;

MySQL中的数据类型：数值类型、字符串类型、日期时间类型。

char性能好 varchar性能较差

DML

DML英文全称是Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改操作。

添加数据（INSERT）

修改数据（UPDATE）

删除数据（DELETE）

1.添加
  (1)给指定字段添加数据
  INSERT INTO 表名 (字段名1,字段名2,...) VALUES(值1,值2,...);
  (2)给全部字段添加数据
  INSERT INTO 表名 VALUES(值1,值2,...);
  (3)批量添加数据
  INSERT INTO 表名 VALUES(字段名1,字段名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
  INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
  
  注：插入数据时，指定字段顺序需要与值的顺序一致；字符串和日期型数据应该包含在引号中；
插入的数据大小，应该在字段的规定范围内；
2.修改
  UPDATE 表名 SET 字段名1=值1,字段名2=值2，...[ WHERE 条件 ];
  注：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
3.删除
  DELETE FROM 表名 [ WHERE 条件 ]

DQL

DQL Data Query Language（数据查询语言），用来查询数据库中表的记录

查询关键字：SELECT

SELECT
      字段列表
FROM
      表名列表
WHERE
      条件列表
GROUP BY
      分组字段列表
HAVING
      分组后条件列表
ORDER BY
      排序字段列表
LIMIT
      分页参数

基本查询
条件查询（WHERE）
聚合函数（count、max、min、avg、sum）
分组查询（GROUP BY）
分页查询（LIMIT）

DQL-基本查询
1.查询多个字段
SELECT 字段1,字段2,字段3...FROM 表名;
SELECT * FROM 表名;
2.设置别名
SELECT 字段1 [ AS 别名1 ],字段2 [AS 别名2]...FROM 表名;
3.去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;

DQL-条件查询
1.语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;

DQL-聚合函数：将一列数据作为一个整体，进行纵向计算。(不计算null值哦)

SELECT 聚合函数(字段列表) FROM 表名;

SQL-分组查询

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];

where与having区别

执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

判断条件不同：where不能对聚合函数进行判断，而having可以。

执行顺序：where>聚合函数>having

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

SQL-排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 ,字段2 排序方式2;

排序方式：

ASC：升序（默认值）

DESC：降序

注：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

DQL-分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

注：起始索引从0开始，起始索引 = （查询页码-1）*每页显示记录数。

分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。（查询第一页的十条）

limit一般要放在sql语句最后面哦，不然有可能会报错。

DQL-执行顺序

DCL

DCL Data Control Language（数据控制语言），用来管理数据库用户、控制数据库的访问权限。

DCL-管理用户
1.查询用户
S=USE mysql;
SELECT * FROM user;
2.创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
3.修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
4.删除用户
DROP USER '用户名'@'主机名';

注：主机名可以使用%通配。

这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）使用。

DCL-权限控制
1.查询权限
SHOW GRANTS FOR '用户名'@'主机名';
2.授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
3.撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注：

多个权限之间，使用逗号分隔

授权时，数据库名和表名可以使用 * 进行通配，代表所有。

GRANT 权限列表 ON *.* TO '用户名'@'主机名';（超级管理员！）

自己相关练习代码：

-- 查询数据 --
drop table employee;

-- 数据准备 --
create table emp(
    id int comment'编号',
    workno varchar(10) comment'工号',
    name varchar(10) comment'姓名',
    gender char(1) comment'性别',
    age tinyint unsigned comment'年龄',
    idcard char(18) comment'身份证号',
    workaddress varchar(50) comment'工作地址',
    entrydate date comment'入职时间'
) comment '员工表';

insert into emp(id, workno, name, gender, age, idcard, workaddress, entrydate)
values(1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01'),
      (2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),
      (3, '3', '韦一笑', '男', 38, '123456789012345670', '上海', '2005-08-01'),
      (4, '4', '赵敏', '女', 18, '123456789012345670', '北京', '2009-12-01'),
      (5, '5', '小昭', '女', 16, '123456789012345678', '上海', '2007-07-01'),
      (6, '6', '杨逍', '男', 28, '12345678901234567X', '北京', '2006-01-01'),
      (7, '7', '范瑶', '男', 40, '123456789012345670', '北京', '2005-05-01'),
      (8, '8', '黛绮丝', '女', 38, '123456789012345670', '天津', '2015-05-01'),
      (9, '9', '范凉凉', '女', 45, '123456789012345678', '北京', '2010-04-01'),
      (10, '10', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'),
      (11, '11', '张士诚', '男', 55, '123456789012345670', '江苏', '2015-05-01'),
      (12, '12', '常遇春', '男', 32, '123456789012345670', '北京', '2004-02-01'),
      (13, '13', '张三丰', '男', 88, '123456789012345678', '江苏', '2020-11-01'),
      (14, '14', '灭绝', '女', 65, '123456789012345670', '西安', '2019-05-01'),
      (15, '15', '胡青牛', '男', 70, '12345678901234567X', '西安', '2018-04-01'),
      (16, '16', '周芷若', '女', 18, null, '北京', '2012-06-01');
-- 1.查询指定字段 name,workno,age 返回
select name,workno,age from emp;
-- 2.查询所有字段返回
select id, workno, name, gender, age, idcard, workaddress, entrydate from emp;

select * from emp;
-- 3.查询所有员工的工作地址，起别名
select workaddress as'工作地址'from emp;
select workaddress '工作地址'from emp;  -- as可以省略
-- 4.查询员工上班地址（不重复）
select  distinct workaddress '工作地址' from emp;

select * from emp where age = 88;

select * from emp where age < 20;

select * from emp where age <= 20;

select * from emp where idcard is null;

select * from emp where idcard is not null;

select * from emp where age != 88;
select * from emp where age <> 88;

select * from emp where age between 15 and 20;
select * from emp where age >=15 && age <= 20;
select * from emp where age >=15 and age <= 20;

select * from emp where gender = '女' and age < 25;

select * from emp where age = 18 or age = 20 or age =40;
select * from emp where age in(18,40,20);

select * from emp where name like '___';

select * from emp where idcard like '%x';-- 身份证最后一位是x （模糊匹配）
select * from emp where idcard like '_________________x';

-- 聚合函数
select count(*) from emp;
select count(idcard) from emp;
select avg(age) from emp;
select max(age) from emp;
select min(age) from emp;
select sum(age) from emp where workaddress='西安';

-- 分组查询
-- 1.根据性别分组，统计男性员工和女性员工的数量
select gender,count(*) from emp group by gender;
-- 2.根据性别分组，统计男性员工和女性员工的平均年龄
select gender,avg(age) from emp group by gender;
-- 3.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress,count(*) from emp where age<45 group by workaddress having count(*)>=3;

-- 排序查询
select * from emp order by age asc ;-- asc可以省略
select * from emp order by age desc ;
select * from emp order by age ;
select * from emp order by entrydate desc ;
select * from emp order by age asc ,entrydate desc ;

-- 分页查询
select * from emp limit 0,10;
select * from emp limit 10;
select * from emp limit 10,10;

select * from emp where age in(20,21,22,23) and gender='女';
select * from emp where gender='男' and age between 20 and 40 and name like '___';
select gender,count(*) from emp where age<60 group by gender ;
select name,age from emp where age<=35 order by age asc,entrydate desc;
select * from emp where gender = '男' and age between 20 and 40  order by age asc,entrydate desc limit 5;

-- DCL
create user 'itcast'@'localhost' identified by '123456';
create user 'heima'@'%' identified by '123456';
alter user 'heima'@'%' identified with mysql_native_password by '23456';
drop user 'itcast'@'localhost';

show grants for 'heima'@'%';
grant all on wangrui.* to 'heima'@'%';
revoke all on wangrui.* from 'heima'@'%';