mysql学习笔记一-基础篇

最近数据库用的比较多，所以补习一下数据库相关的知识。

文章内容主要是跟黑马的老师的课程: https://www.bilibili.com/video/BV1Kr4y1i7ru/https://www.bilibili.com/video/BV1Kr4y1i7ru/

我用的都是随便建的表和数据和课程内容可能会有差别。

sql语句分类

• DDL(Data Definition Language) 数据库定义语言：用来定义数据库对象(数据库、表、字段)。
• DML(Data Manipulation Language) 数据库操作语言：用来对数据库的表进行增删改。
• DQL(Data Query Language) 数据库查询语法：用来查询数据库表的记录。
• DCL(Data Control Language) 数据库控制语言：用来创建数据库用户，控制数据库访问权限。

DDL

数据库操作

IF NOT EXISTS ==> 常用关键字，判断是否已经存在

查询

SHOW DATABASES; -- 查询所有数据库
SELECT DATABASE(); -- 查询当前数据库

创建

CREATE DATABASE IF NOT EXISTS test_database DEFAULT CHARSET utf8 COLLATE utf8_unicode_ci;
-- CREATE DATABASE IF NOT EXISTS [数据库名] DEFAULT CHARSET [字符集] COLLATE [排序规则];

删除

DROP DATABASE IF EXISTS test_database;
-- DROP DATABASE IF EXISTS [数据库名];

使用

USE test_database;
-- USE [数据库名]

数据表操作

查询

SHOW TABLES; -- 查询该数据库中的所有表 
DESC test_table; -- 查询表结构 DESC [表名]
SHOW CREATE TABLE test_table; -- 查询指定的表建表语句 SHOW CREATE TABLE [表名];

创建

CREATE TABLE [表名](字段名1 字段类型 COMMENT ["注释"], 字段名1 字段类型 COMMENT ["注释"]) COMMENT ["注释"];

CREATE TABLE test(id int COMMENT "编号",name VARCHAR(20) COMMENT "姓名") COMMENT "测试表";

修改

修改表名

ALTER TABLE [表名] RENAME TO [新表名];

ALTER TABLE test RENAME TO test_user;

添加字段

ALTER TABLE [表名] ADD [字段名] [字段类型] COMMENT "注释"

ALTER TABLE test ADD age int COMMENT "年龄";

修改字段

修改字段类型: ALTER TABLE [表名] MODIFY [字段名] [新数据类型]

ALTER TABLE test MODIFY name VARCHAR(20);-- 修改字段类型

修改字段名和字段类型：ALTER TABLE [表名] CHANGE [旧字段名] [新字段名] [类型] COMMENT "注释" [约束];

ALTER TABLE test CHANGE id userId int;

删除

删除字段

删除指定字段: ALTER TABLE [表名] DROP [字段名];

ALTER TABLE test DROP age;

删除表

删除指定表: DROP TABLE IF EXISTS [表名];

DROP TABLE IF EXISTS test_user;

删除指定表并重新创建该表: TRUNCATE TABLE [表名];

SHOW TABLES;

数据类型表（跟一些编程语言数据类型大小差不多，不做详细的说明）

数值类型	字符串类型	日期类型
TINYINT	CHAR	DATE
SMALLINT	VARCHAR	TIME
MEDIUMINT	TINYBLOB	YEAR
INT or INTEGER	BLOB	DATETIME
BIGINT	TEXT	TIMESTAMP
FIOAT	MEDIUMBLOB
DOUBLE	MEDIUMTEXT
	LONGBLOB
	LONGTEXT

DML

添加数据

上面把表删了需要重新建表;

USE test_database;
CREATE TABLE test(id int, name varchar(20), age int);

INSERT INTO [表名] ([字段名1], [字段名2]) VALUES("数据1", "数据2");如果是全部字段，字段名可以省略。

insert into test(id, name, age) values(1, "国雪", 18);

批量添加: INSERT INTO [表名]VALUES("数据1", "数据2"),("数据1", "数据2"),("数据1", "数据2");

修改数据

UPDATE [表名] SET [字段名]=[值] WHERE [条件];

update test set name="王五" where id=3;

删除数据

删除指定数据: DELETE FROM [表名] WHRER [条件]

delete from test where id=2;

清空表数据: DELETE FROM [表名]

delete from test;

DQL

基本查询

先建一个用于测试的表

CREATE DATABASE IF NOT EXISTS test_database;
CREATE TABLE IF NOT EXISTS test_table(id int, username varchar(100), password varchar(20));
insert into test_table values(1, "root", "root"), (2, "admin", "admin"), (3, "test", "test");

查询多个字段: SELECT [字段1, 字段2] FROM [表名]; 或 全表查询：SELECT * FROM [表名]

select id,username from test_table;
select * from test_table;

设置别名: SELECT [字段1] AS [别名], [字段2] AS [别名] FROM [表名]

select username as "用户名", password as "密码" from test_table;

去重查询: SELECT DISTINCT [字段列表] FROM [表名];没有加重复数据就这样凑合用了。

select distinct username,password from test_table;

条件查询

基本语法：SELECT [字段列表] FROM [表名] WHERE [条件列表];

常用条件表

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 !=	不等于
BETWEEN...AND...	在某个范围之内
IN(...)	在in之后的列表中的值
LIKE	模糊匹配
IS NULL	是NULL

逻辑运算符	功能
AND 或 &&	并且
OR 或 |	或者
NOT 或 !	非，不是

具体使用

在之前的表中再添加两条测试数据

INSERT INTO test_table(username, password) value("admin1", "test"),("admin2", "root");

select * from test_table where id>2;select * from test_table where id>=2;

select * from test_table where id>2;select * from test_table where id>=2;

select * from test_table where id>2;select * from test_table where id>=2;

select * from test_table where id between 2 and 3;

select * from test_table where username in("admin","test");

select * from test_table where username like '____';select * from test_table where username like 'admin%';

'%'匹配多个字符; '_'匹配多个字符

select * from test_table where id is null;

select * from test_table where id is not null;

聚合函数

常见聚合函数表

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

具体使用

select count(username) from test_table;

select max(id) from test_table;select max(username) from test_table;select max(password) from test_table;

select min(id) from test_table;select min(username) from test_table;select min(password) from test_table;

select avg(id) from test_table;

select sum(id) from test_table;

分组查询

基本语法

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

WHERE 和 HAVING区别

执行时机不同：where是在分组前过滤，having是在分组后过滤。

判断条件不同：where不能使用聚合函数having可以。

具体使用

select username, count(username) from test_table group by username having username like "admin%";

排序查询

基本语法

SELECT [字段列表] FROM [表名] ORDER BY [字段名] 排序方式,[字段名] 排序方式;

排序方式：

• ASC：升序
• DESC：降序

具体使用

select * from test_table order by password desc;

select * from test_table order by username asc;

分页查询

基本语法

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

具体使用

select * from test_table limit 0,2;select * from test_table limit 2,2;

DCL

管理用户

查询用户

mysql用户基本全部存储在默认的mysql库中的user表之中

use mysql;
select Host, User from user;

创建用户

语法

CREATE USER '用户名'@'主机名' IDENT BY '密码';

使用

create user 'test_user'@'localhost' identified by '000000';

修改密码

语法

ALTER USER '用户名'@'主机名' IDENTIFIED BY '密码';

主机名为'%'则允许任意用户访问该账户

使用

alter user 'test_user'@'localhost' identified by 'admin';

删除用户

语法

DROP USER '用户名'@'主机名';

使用

drop user 'test_user'@'localhost';

权限控制

常用的权限表

权限	说明
ALL, ALL PRIVILEGES	所有权限
SELECT	查询数据
INSERT	插入数据
UPDATE	修改数据
DELETE	删除数据
ALTER	修改表
DROP	删除数据库/表
CREATE	创建数据库/表

查询权限

SHOW GRANTS FOR '用户名'@'主机名';

show grants for test;

授予权限

语法

GRANT [权限列表] ON 数据库名.表名 TO '用户名'@'主机名';

使用

需要使用root用户修改。

grant select, insert on test_database.* to test;

撤销权限

语法

REVOKE [权限列表] ON 数据库名.表名 FROM '用户名'@'主机名';

使用

revoke ALL privileges on *.* from test;

此时的test用户已经不能使用mysql库了