mysql基本SQL语句

1. 连接

# 客户端连接mysql,[]为可选,# 默认为本机3306端口
mysql [-h localhost] [-p 3306] -u root -p 	

2. DDL(数据定义语言)

(1) 数据库操作

查询

-- 查询所有数据库
SHOW DATABSES

--查询当前数据库
SELECT DATABASE()

创建

CREATE DATABASE [IF NOT EXISTS] 数据库名称 
[DEFAULT CHARSET 字符编码] [COLLATE 排序规则] 

删除

DROP [IF NOT EXISTS] 数据库名

使用数据库

USE 数据库名

(2) 表操作

查询

-- 查询所有表
SHOW TABLES
-- 查看表结构
DESC 表名
--表看创建表语句
SHOW CREATE TABLE 表名

创建

CREATE TABLE 表名(
	字段 字段类型 [COMMENT 注释] [约束例如not null]
	......
)[COMMENT 注释]

常见字段类型:

数值型:
TINYINT：范围为-128至127的整数。
SMALLINT：范围为-32768至32767的整数。
MEDIUMINT：范围为-8388608至8388607的整数。
INT：范围为-2147483648至2147483647的整数。
BIGINT：范围为-9223372036854775808至9223372036854775807的整数。
FLOAT：单精度浮点数，存储范围为-3.402823466E+38至3.402823466E+38。
DOUBLE：双精度浮点数，存储范围为-1.7976931348623157E+308至1.7976931348623157E+308。
DECIMAL：定点数，适用于存储精确的小数值。

字符串类型：
CHAR：固定长度的字符串，最多255个字符。
VARCHAR：可变长度的字符串，最多255个字符。
TINYTEXT：最多255个字符的非二进制字符串。
TEXT：最多65535个字符的非二进制字符串。
MEDIUMTEXT：最多16777215个字符的非二进制字符串。
LONGTEXT：最多4294967295个字符的非二进制字符串。

日期时间类型：
DATE：日期，格式为'YYYY-MM-DD'。
TIME：时间，格式为'HH:MM:SS'。
DATETIME：日期和时间，格式为'YYYY-MM-DD HH:MM:SS'。
TIMESTAMP：时间戳，保存从1970年1月1日到当前时间的秒数。
YEAR：年份，格式为'YYYY'。
其他类型：
BINARY：固定长度的二进制字符串，最多255个字节。
VARBINARY：可变长度的二进制字符串，最多255个字节。
BLOB：最多65535个字节的二进制字符串。
ENUM：枚举类型，可以从预定义的值列表中选择一个值。
SET：集合类型，可以从预定义的值列表中选择一个或多个值。

修改删除表结构

-- 添加字段
ALTER TABLE 表名 ADD 字段名 类型() [COMMENT 注释] [约束];
-- 删除字段
ALTER TABLE 表名 drop 字段名
-- 修改字段
ALTER TABLE 表名 CHANGE 字段名 新字段名 类型()[注释] [约束]
-- 修改表名
ALTER TABLE 表名 RENAME TO 新表名

-- 删除表
DROP TABLE [IF EXISTS] 表名;

常见约束

NOT NULL				-- 非空
UNIQUE					-- 唯一
PRIMARY KEY				-- 主键
DEFAULT					-- 默认
CHECK					-- 检查
FOREIGN KEY				-- 外键

3. DML(数据操作语言)

新增

INSERT INTO 表名(字段列表) values(值列表)

修改

-- 不带条件更改所有数据
UPDATE 表名 SET 字段名 = 值,.. [WHERE 条件]

删除

-- 不指定条件删除全部
delete from 表名 [where 条件]

4. DQL(数据查询语言)

SELECT 		字段	AS 别名	-- as可以省略
FROM		表名
WHERE  		条件
GROUP BY	分组字段
HAVING		分组后条件
ORDER BY	排序字段
LIMIT		分页参数

条件

- 等于 (=)：				用于判断两个值是否相等。
- 不等于 (<> 或 !=)：用于判断两个值是否不相等。
- 大于 (>)：				用于判断一个值是否大于另一个值。
- 小于 (<)：				用于判断一个值是否小于另一个值。
- 大于等于 (>=)：	用于判断一个值是否大于等于另一个值。
- 小于等于 (<=)：	用于判断一个值是否小于等于另一个值。
- BETWEEN：		用于判断一个值是否在某个范围之间。
- IN：						用于判断一个值是否在指定的多个值中。
- LIKE：					用于匹配指定的模式。
- NOT：					用于否定其他条件运算符。

聚合函数

-- 使用直接使用在字段列表
SELECT COUNT(*) FROM user

- COUNT：用于计算某一列的行数。
- SUM：用于计算某一列的总和。
- AVG：用于计算某一列的平均值。
- MAX：用于获取某一列的最大值。
- MIN：用于获取某一列的最小值。
- GROUP_CONCAT：用于将某一列的值连接成一个字符串。
- DISTINCT：用于返回不重复的值。
- HAVING：用于过滤聚合结果。
- CONCAT：用于将多个字符串连接在一起。
- SUBSTRING：用于截取字符串的一部分。

排序查询

-- 排序 -按照age 升序排序,多个排序使用,分割
SELECT * FROM student ORDER BY age ASC	

DESC 		--降序
ASC			-- 升序(默认)

分页查询

SELECT * FROM student LIMIT 开始索引  查询条数

多表查询

表关系:
一对一
多对多
一对多

笛卡尔积: 在多表查询时,每一条记录都和其他表的所有记录组合(需要消除无效笛卡尔积)

内连接

查询两张表交集部分:

select 字段列表
from 表1,表2
where 条件...

外连接

左外: (完全包含左表,和两表的交集)

select 字段
from 表1 
left join 表2
on 条件

右外:(相反)

select 字段
from 表1 
right join 表2
on 条件

子查询

将查询结果作为条件

select 字段
from 表
where col1 =(
	select 字段
	from 表
)

DQL 语句执行顺序

FROM  >
  WHERE  >
   GROUP BY >
     SELECT  >
       ORDER BY > 
       		LIMIT

5. DCL(数据控制语言)

管理用户

-- 查询用户
USE MYSQL;
SELECT * FROM user

-- 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'

-- 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFLED WITH mysql_native_password BY '新密码'

-- 删除用户
DROP USER '用户名'@'主机名'

权限控制

-- 查询权限
SHOW GRANTS FOR '用户名'@'表名'

-- 授权
GRANT 权限列表 ON 数据库.表 TO '用户'@'主机'
-- 撤权
REMOKE 权限列表 ON 数据库.表 TO '用户'@'主机'

常用权限:

ALL: 所有权限
SELECT：允许用户查询数据库中的数据。
INSERT：允许用户在数据库中插入新的数据。
UPDATE：允许用户修改数据库中的数据。
DELETE：允许用户删除数据库中的数据。
CREATE：允许用户创建新的数据库、表或索引。
DROP：允许用户删除数据库、表或索引。
ALTER：允许用户修改数据库中已存在的表结构。
GRANT：允许用户授予或撤销其他用户的权限。
RELOAD：允许用户重新加载服务器配置文件。
SHUTDOWN：允许用户关闭MySQL服务器。
PROCESS：允许用户查看其他用户的进程。
FILE：允许用户读取或写入服务器上的文件。
SUPER：允许用户执行一些特殊的操作，如修改服务器的配置参数。
REPLICATION CLIENT、REPLICATION SLAVE：允许用户进行主从复制相关的操作。
CREATE TEMPORARY TABLES：允许用户创建临时表。
SHOW DATABASES、SHOW TABLES：允许用户查看数据库和表的列表。

6. 事务

事务让一组操作同时成功同时失败

select @@autocommit		-- 查看事务提交方式
select @@autocommit = 0		-- 关闭自动提交事务

start transaction/begin		--开启事务

commit 						-- 提交事务
	
rollback					-- 回滚事务

事务隔离级别:

读未提交（Read Uncommitted）：一个事务未提交的修改可以被其他事务读取，可能导致脏读、不可重复读和幻读的问题。
读已提交（Read Committed）：一个事务只能读取到已提交的修改，解决了脏读的问题，但可能会导致不可重复读和幻读的问题。
可重复读（Repeatable Read）：一个事务在执行期间看到的数据都是一致的，其他事务对数据的修改只有在当前事务提交后才能读取到，解决了脏读和不可重复读的问题，但可能会导致幻读的问题。
串行化（Serializable）：所有事务按顺序执行，不会出现并发问题，解决了所有的并发问题，但由于锁的影响可能导致性能下降。

查看隔离级别:

select @@TRANSACTION_ISOLATION

-- 设置隔离级别
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL 隔离级别
-- SESSION 表示当前会话
-- GLOBAL表示全局

数据备份

mysqldump -uroot -p密码 数据库 > 文件名.sql