MySQL基础操作
一、SQL的基本概念
1.什么是SQL?
Structured Query Language:结构化查询语言
其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,称为“方言”。
2.SQL通用语法
- SQL 语句可以单行或多行书写,以分号结尾。
- 可使用空格和缩进来增强语句的可读性。
- MySQL 数据库的 SQL 语句不区分大小写,关键字建议使用大写。
- 三种注释
-- 1. SQL通用注释语法 # 2. mysql 特有注释语法 /* 3. 多行注释 */
3. SQL分类
- DDL(Data Definition Language)数据定义语言。用来定义数据库对象:数据库,表,列等。关键字:create, drop, alter 等
- DML(Data Manipulation Language)数据操作语言。用来对数据库中表的数据进行增删改。关键字:insert, delete, update 等
- DQL(Data Query Language)数据查询语言。用来查询数据库中表的记录(数据)。关键字:select, where 等
- DCL(Data Control Language)数据控制语言。用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT, REVOKE 等
二、DDL:操作数据库、表
1. 操作数据库:CRUD
-
C(create)创建
-- 创建数据库 CREATE DATABASE 数据库名; -- 创建数据库,辅以存在性判定 CREATE DATABASE IF NOT EXISTS 数据库名; -- 创建数据库,辅以指定编码字符集 CREATE DATABASE 数据库名 CHARACTER SET 字符集名; -- 示例:创建名为db的数据库,判断是否存在,并制定字符集为gbk CREATE DABABASE IF NOT EXISTS db CHARACTER SET gbk;
-
R(Retrieve)查询
-- 查询所有数据库的名称 SHOW DATABASES; -- 查询某个数据库的字符集/查询某个数据库的创建语句 SHOW CREATE DATABASE 数据库名;
-
U(Update)修改
-- 修改数据库的字符集 ALTER DATABASE 数据库名 CHARACTER SET 字符集名;
-
D(Delete)删除
-- 删除数据库 DROP DATABASE 数据库名; -- 删除数据库,辅以存在性判断 DROP DATABASE IF EXISTS 数据库名;
-
使用数据库
-- 查询当前正在使用的数据库名 SELECT DATABASE(); -- 使用数据库 USE 数据库名;
2. 操作表
-
C(Create)创建
-- 语法 CREATE TABLE 表名 ( 字段名1 数据类型1, 字段名2 数据类型2, ... 字段名n 数据类型n -- 注意:最后一个字段不要加逗号(,) )
数据类型 描述 INT 整数类型 DOUBLE 小数类型 VARCHAR 字符串类型,初始化时需要指定字符串长度,例如 name VARCHAR(20); DATE 日期,年月日(yyyy-MM-dd) DATETIME 日期,年月日时分秒(yyyy-MM-dd HH:mm:SS) TIMESTAMP 时间戳(yyyy-MM-dd HH:mm:SS)如果不为时间戳赋值或赋NULL值,则自动使用当前系统时间赋值 -- 创建表 CREATE TABLE student ( id INT, -- 学号 name VARCHAR(32), -- 姓名 age INT, -- 年龄 score double(4, 1), -- 分数(0.0~999.9) birthday DATE, -- 生日 insert_time TIMESTAMP, -- 添加时间戳 ); -- 复制表 CREATE TABLE 表名 LIKE 被复制的表名
-
R(Retrieve)查询
-- 查询某个数据库中所有的名称 SHOW TABLES; -- 查询表结构 DESC 表名;
-
U(Update)修改
-- 修改表名 ALTER TABLE 表名 RENAME TO 新表名; -- 修改表的字符集 ALTER TABLE 表名 CHARACTER SET 字符集名; -- 添加字段 ALTER TABLE 表名 ADD 字段名 数据类型; -- 修改字段名/数据类型 ALTER TABLE 表名 CHANGE 字段名 新字段名 新数据类型; ALTER TABLE 表名 MODIFY 字段名 新数据类型; -- 删除字段 ALTER TABLE 表名 DROP 字段名;
二、DML:增删改表中数据
1. 添加数据
-- 向指定字段添加值
INSERT INTO 表名(字段名1, 字段名2,...,字段名n) VALUES (值1, 值2,..., 值n);
-- 向所有字段添加值
INSERT INTO 表名 VALUES (值1, 值2,..., 值n);
注意:
1. 字段名和值必须一一对应
2. 除了数字类型,其他类型需要使用引号(单双都可以)引起来
2. 删除数据
-- 删除符合条件的数据
DELETE FROM 表名 WHERE 条件;
-- 删除所有记录1:有多少条记录就会执行多少次删除操作,效率较低
DELETE FROM 表名;
-- 删除所有记录2:先删除表,然后再创建一张一样的表,效率较高
TRUNCATE TABLE 表名;
3. 修改数据
-- 修改符合条件的数据
UPDATE
表名
SET
字段名1 = 值1,
字段名2 = 值2,
...
WHERE
条件
;
若不加任何条件,则会将表中所有记录全部修改。
三、DQL:查询表中的记录
1. 查询语法
(1)查询整张表
SELECT * FROM 表名;
(2)完整查询语法
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组列表
HAVING
分组之后的条件
ORDER BY
排序
LIMIT
分页限定
2. 基础查询
(1)多字段查询
SELECT
字段名1,
字段名2,
FROM
表名
;
-- 若需要查询所有字段,则可以使用 * 来代替字段列表,正式开发时不建议使用
(2)去除重复
SELECT
DISTINCT 字段名
FROM
表名
;
(3)计算列
通常可以使用四则运算计算一些字段的值(大部分都是数值运算)
-- 查找表中的字段1和字段2并相加得到一个新字段
SELECT
字段名1 + 字段名2
FROM
表名
;
IFNULL(exp1, exp2):NULL参与的运算,计算结果都为NULL
- exp1:哪些字段需要判断是否为NULL
- exp2:如果该字段为NULL后的替换值
-- 若字段中存在NULL值,则替换为0值
SELECT
IFNULL(字段名1, 0) + IFNULL(字段名2, 0)
FROM
表名
;
(4)起别名
可以使用 AS 为字段或表格起别名,AS 可省略。
-- 查找表中的字段1和字段2并相加得到一个新字段,并为新字段起一个别名
SELECT
字段名1 + 字段名2 (AS) 字段别名
FROM
表名
;
3. 条件查询
- WHERE子句后跟条件
- 条件运算符
1. >, <, <=, >=, =, <>(!=) 2. BETWEEN ... AND ... 3. IN(集合) 4. LIKE: 模糊查询 占位符: `_`:单个占位符 `%`:多个任意字符 5. IS NULL 6. AND 或 && 7. OR 或 || 8. NOT 或 !
条件查询示例
-- 查询年龄大于/等于/不等于20岁的学生信息
SELECT * FROM student WHERE age > 20;
SELECT * FROM student WHERE age >= 20;
SELECT * FROM student WHERE age = 20;
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;
-- 查询年龄大于等于20且小于等于30的学生信息
SELECT * FROM student WHERE age >= 20 && age <= 30;
SELECT * FROM student WHERE age >= 20 AND age <= 30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;
-- 查询年龄22岁,18岁,25岁的学生信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25;
SELECT * FROM student WHERE age IN (22, 18, 25);
-- 查询english字段为NULL/不为NULL的学生信息
SELECT * FROM student WHERE english IS NULL; # 注意不能用 `=` 判断是否为NULL,要用`IS`
SELECT * FROM student WHERE english IS NOT NULL;
-- 查询姓‘马’(name字段首字为‘马’)的学生信息
SELECT * FROM student WHERE name LIKE '马%';
-- 查询name字段第二个字是‘化’的学生信息
SELECT * FROM student WHERE NAME LIKE '_化%'; # 使用`_`做第一个字的占位符
-- 查询name字段是3个字符的学生信息
SELECT * FROM student WHERE name LIKE '___'; # 3个`_`做占位符
-- 查询name字段中包含‘德’的人
SELECT * FROM student WHERE name LIKE '%德%';
四、DCL:用户管理与权限控制
1. 管理用户
-
添加用户:
-- 语法 CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; -- 创建一个名为zhangsan的用户,只能在本地主机登陆 CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123'; -- 创建一个名为lisi的用户,可以在任意主机登陆(%) CREATE USER 'lisi'@'%' IDENTIFIED BY '456';
-
删除用户:
DROP USER '用户名'@'主机名';
-
修改用户密码:
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名'; UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi'; SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码'); SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
mysql中忘记了root用户的密码怎么办?
1. 以管理员身份运行cmd -- > net stop mysql 停止mysql服务
2. 使用无验证方式启动mysql服务: mysqld --skip-grant-tables
3. 打开新的cmd窗口,直接输入mysql命令,敲回车。就可以登录成功
4. use mysql;
5. update user set password = password('你的新密码') where user = 'root';
6. 关闭两个窗口
7. 打开任务管理器,手动结束mysqld.exe 的进程
8. 启动mysql服务
9. 使用新密码登录。
- 查询用户:
-- 1. 切换到mysql数据库 USE myql; -- 2. 查询user表 SELECT * FROM USER;
- 通配符:`%`表示可以在任意主机使用用户登录数据库
2. 权限管理:
-
查询权限:
-- 语法 SHOW GRANTS FOR '用户名'@'主机名' ; -- 查询lisi用户在当前主机上的权限 SHOW GRANTS FOR 'lisi'@'%'; -- 查询root用户在当前主机上的权限 SHOW GRANTS FOR 'root'@'%';
-
授予权限:
-- 语法 GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名' ; -- 给lisi授予在任意主机上查询和修改db3数据库中的account表的权限 GRANT SELECT, UPDATE ON db3.account TO 'lisi'@'%' -- 给zhangsan用户授予所有权限,在任意数据库任意表上 GRANT ALL ON *.* TO 'zhangsan'@'localhost';
-
撤销权限:
-- 语法 REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名' ; -- 撤销李四在任意主机上对db3数据库中account表的更新权限 REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';