mysql语法讲解_MySQL入门详解——基础语法

MySQL语言分为：DCL(数据库控制语言)、DDL(数据库定义语言)、DQL(数据库查询语言)、DML(数据库操作语言)

DCL：数据库控制语言，用来设置数据库用户或角色权限的语句，关键字grant revoke等

mysql -u 用户名 -p 密码 -h 服务器IP地址 -P服务器MySQL端口号 -D数据库名

默认表：MySQL安装时自动创建一个名为mysql的数据库，包含五个权限表：

当客户端连接到服务器是，MySQL访问控制有两个阶段：连接验证 对用户名密码，请求验证 对权限验证

user表包含用户账号密码主机信息 (Host：账号作用IP范围，%-为不限制)

db表包含某用户对数据库访问权限

table_priv表和columns_priv表包含表列级权限

procs_priv 表存储函数和存储过程权限

#创建新用户账户CREATE USER username@hostname IDENTIFIED BY 'password'"%.baidu.com","%"/"-"

​

#查看用户权限

SHOW GRANTSFOR username@hostname​

#删除用户账户DROP USER username@hostname[，username@hostname]​

#添加权限GRANT privilege[,privilege] ON privilege_level #指定一个或多个权限或'ALL'在层级(表)TO user [IDENTIFIED BY 'password']#给某个用户[REQUIRE tsl_option]#安全的连接[WITH [GRANT_OPTION | resource_option]] #是否可以被改掉(选可以)//GRANT SELECT,UPDATE,INSERT ON alibaba.* TOrfc;

​

#允许远程连接grant all privileges on *.* to 'root@%' identified by '123456' with grant with grant option;

flushprivileges; #刷新权限

​

#撤销权限REVOKE privilege_type [(colimn_list)] [,privilege_type [(colimn_list)]]ON [object_type]privilege_levelFROM user[,user]​

#修改密码1.用SET PASSWORD命令：

登录之后set password for username@host = '123456'

2.用UPDATE编辑user表：usemysql;update user set password=password('123456') where user='user';

flushprivileges;3.用mysqladmin：

mysqladmin-u用户名 -p旧密码 password 新密码4.在忘记root密码或初始化密码时:

windows为例:

关闭正在运行的MySQL服务

打开DOS窗口，转到mysql的bin目录

mysqld--skip-grant-tables #此DOS窗口运行着跳过权限检查启动mysql的命令,8.0版本使用mysqld --console --skip-grant-tables --shared-memory

再打开一个DOS窗口输入mysql回车，usemysql;

改密码

flushprivileges;

退出quit

注销系统

​

#数据库备份

mysqldump-u[username] -p[password] [database_name] > [dump_file.sql]mysqldump-u[username] -p[password] --no-date [database_name] > [dump_file.sql] #不包含数据只导出结构

mysqldump -u[username] -p[password] --no-create-info [database_name] > [dump_file.sql] #不包含结构只导出数据

mysqldump -u[username] -p[password] --all-database > [all_dbs_dump_file.sql] #导出全部数据库

​

#数据库维护

#分析表语句

ANALYZETABLE 表名1[,表名2]#返回表名 操作 分析状态 和信息,会尝试修复

#优化表语句

OPTIMIZETABLEtabe_name; #优化由于表操作导致的碎片问题

#检查表语句CHECK TABLEtable_name; #仅检查

#修复表语句

REPAIRTABLE table_name; #仅修复

DDL：数据库定义语言，用于描述数据库中创建库表，关键字create alter drop等

#创建数据库CREATE DATABASE [IF NOT EXISTS]database_name;

​

#删除数据库DROP DATABASE [IF EXISTS]database_name;

​

#创建表CREATE TABLE [IF NOT EXISTS]table_name(

colume_list

)engine=table_type;

#定义列

字段名 数据类型 整型宽度、字符大小极限65535 是否可空NOTNULL 是否自增auto_increment 默认值default 索引 主键primary key注释等等

​

eg:create tabletname(

idint(10) auto_increment primary key, #auto_increment自增 primarykey主键

namevarchar(255),

agevarchar(10),

sexvarchar(10)) default charset=utf8;

​

#修改表ALTER TABLE table_name CHANGE column_name new_column_name(不改写原来的) varchar(200) not null;ALTER TABLE table_name ADD column_name varchar(200) not null [FIRST | AFTER 字段];ALTER TABLE DROPcolumn_name;

#删除索引ALTER TABLE 'table_name' DROP UNIQUE(index) name;

#添加索引ALTER TABLE 'table_name' ADD UNIQUE(name) #唯一索引ALTER TABLE 'table_name' ADDFULLTEXT(name) #全文索引ALTER TABLE 'table_name' ADD INDEX(name) #普通索引

#修改引擎

ALERTTABLE table_name ENGINE = InnoDB;

空值有NULL和None之分：NULL占了空间但内容是空的，None没有内容也不占空间 数据类型：

数值类型：TINYINT 1字节 范围255；SMALLINT 2字节 范围65535；MEDIUMINT 3字节 8388607；INT 4字节 2^32；BIGINT 8字节 2^64;FLOAT 4字节；DOUBLE 8字节；DECIMAL(M,D) M整体位数，D小数位数；

UNSIGHED无符号 ；zerofill剩余宽度用0补齐

字符类型：CHAR 255字节(定长字符串,无论存多少内容255字节都是)；VARCHAR 0-65535字节；TINYBLOB 0-255字节 存储二进制字符串；TINYTEXT 0-255字节 短文本；BLOB 0-65535字节 长二进制字符串;TEXT 0-65535字节 文本；MEDIUM BLOB/TEXT...；ENUM 数据长度为1则为0,1,2；SET 集合；

时间类型：DATE 3字节 YYYY-MM-DD；TIME 3字节 HH:MM:SS；YEAR 1字节 YYYY；DATETIME 8字节 YYYY-MM-DD HH:MM:SS；TIMESTAMP 4字节 时间戳 默认插入/更新数据时的值；

空间数据类型：GEOMETRY POINT 等等

数值类型(宽度) 字符类型(长度) 枚举enum("1","0")

引擎类型：

InnoDB：健壮的事务型存储引擎。表文件夹中存放.frm表结构文件 date文件夹中.ibdata1存放数据 .ibd存放索引

MyISAM：访问速度很快，但不支持外键和事务。每个MyISAM在磁盘上生成三个文件 .frm 表结构信息 .MYD 数据 .MYI 存储索引

设置引擎:默认InnoDB 显示所有支持的引擎:SHOW ENGINEs 字符集:默认latin1

索引类型：

主键索引：primary

在一个表中唯一，值不能重复，在数据查写读时能按一定顺序和排列工作

唯一键：unique

一个表中可以同时给多个字段设置unique，设置了的字段值不能重复null除外

普通索引：index

没有其他约束

文本索引：fulltext

5.7版本之后使用，加快大批量文本中的查找效率

mysql外键

副表的非主键字段与主表的主键字段关联，那么这个副表的非主键字段称为外键

在副表中添加一个主键中不存在的数据，操作会报错

​

如果在主表修改或删除，副表中有对应数据，主表会默认阻止

#创建外键[CONSTRAINT constraint_name]#约束名FOREIGN KEY [foreign _key_name](columns)REFERENCESparent_table(columns_name)ON DELETE 模式 #模式：district默认严格模式 cascade关联 setnull将管理数据设置null no action什么都不做ON UPDATE模式;

#添加外键ALTER TABLE table_name ADD constraint FK_ID foreign key(key_name) REFERENCESparent_table(columns_name);

#删除外键ALTER TABLE table_name DROP FOREIGN KEY key_name

DQL：数据库查询语言，查询，关键字select等

SELECTcolumn_1,column_2,...FROMtable_1[INNER | LEFT |RIGHT] JOIN table_2 ONCONDITIONSWHEREconditionsGROUP BYcolumn_1HAVINGgroup_conditionsORDER BY columnlimit offset,length

​

#条件查询select * from 表名 [where 条件];select field1,field2,...fieldn... from 表名 [where 条件];

where后面的条件可以用>、=、<=、!=等多种比较运算符，多个条件之间可以用or、and、not等逻辑运算符、between begin_expr AND end_expr;<= >=;eg:BETWEEN CAST('2000-01-01' AS DATE) AND CAST('2001-01-01' ASDATE)、LIKE 'value' #精准查询，可加% _ 如果需要可以加 ESCAPE'\'转译,此处\也可以指定为其他符号、ineg；where in...#不确定字符串包含关键字查找 FIND_IN_SET(要包含字符串，字段)、group by 表达式，表达式... havinggroup_conditions

#GROUP BY分组 having分组后进行过滤 配合聚合函数avg()计算一组值平均值 count()计算表中行数 instr()返回子字符串在字符串中第一次出现位置 sun()计算一组数值综合 min()在一组数值中找到最小值 max()在一组数值中找到最大值、order by asc升序 desc降序，多列排序时后一列是在前一列基础上排，自定义 field(column,"A","D","B","C")

​

#去重查询select distinct 字段 from表名;

eg:select distinct name from students;//查询名字不相同的学生;select distinct name,age from students;//查询名字和年龄同时不同的学生1.distinct必须放在最开头2.distinct只能使用需要去重的字段进行操作。 ----也就是说我sidtinct了name,age两个字段，我后面想根据id进行排序，是不可以的，因为只能name,age两个字段进行操作.

3.distinct去重多个字段时，含义是：几个字段 同时重复 时才会被 过滤。

​

#排序查询select * from 表名 [where 条件] [order by field1 [desc/asc],field2 [desc/asc]... ];

​

eg:select *from student order by age desc;//查询学生表并按年龄降序排列。1.desc 降序排列，asc升序排列2.order by后面可以跟多个不同的排序字段，每个排序字段都可以有不同的排序顺序。3.如果排序字段的值一样，则相同的字段按照第二个排序字段进行排序。4.如果只有一个排序字段，则字段相同的记录将会无序排列。

​

#限制查询select ... [limit 起始偏移量,行数];

​

eg:select * from student order by mark desc limit 5;//取出成绩前五名的学生(省略了起始偏移量，此时默认为0)

默认情况下，起始偏移量为0，只写记录行数就可以。

​

#聚合查询select 字段 fun_name from 表名 [where 条件] [group by field1,field2...] [with rollup] [having 条件];1.fun_name 表示要做的聚合操作，也就是说聚合函数，常用的有 : sum(求和)、count(*)(记录数)、max(最大值)、min(最小值)。2.group by关键字 表示要进行分类聚合的字段。比如要按照部门分类统计员工数量，部门就应该写在group by后面。3.withrollup 是可选语法，表明是否对分类聚合后的结果进行再汇总4.having关键字表示对分类后的结果再进行条件过滤。

​

#表连接

表连接分为内连接和外连接。

他们之间最主要的区别：内连接仅选出两张表中互相匹配的记录，外连接会选出其他不匹配的记录。

内连接:

...from table1 inner join table2 on table1.name=table2.name;

外连接 分为左连接和右连接

左连接：包含所有左边表中的记录，甚至是右边表中没有和他匹配的记录。

...from table1 left join table2 on table1.name=table2.name;

右连接：包含所有右边表中的记录，甚至是右边表中没有和他匹配的记录。

...from table1 right join table2 on table1.name=table2.name;

交叉连接：

...from table1 cross join table2 #产生a*b条，笛卡尔乘积

​

#联合查询

把多个SELECT查询结果合并起来select column_1 from table1 union select column_2 fromtable2

列名总显示第一个查询语句列名，默认清除重复项，unionall不去重

​

#子查询select * from table1 where id =(select column from table2 where...)select * from table1 where id = and exisit (select column from table2 where ...) #加上and exists有就查，没有不会报错

​

#查询用户信息select user()； #查看当前用户select user,host,db,command FROMinformation_schema.processlist; #查询连接的用户信息

​

#查看查询语句运行效率

explain select语句

DML：数据库操作语言，插入更新删除，关键字insert update delete等

#插入insert into tname(id,name) values ("1","xm") [ON DUPLICATE KEY UPDATE id=id+1];

​

#拷贝表，具有SELECT字句的INSERTcreate table new_table_name liketable_name;insert into new_table_name select * fromtable_name;

​

#替换replace into tname(id,name) values ("1","xm");

​

#更新UPDATE [LOW_PRIORITY]#优先查询再更新 [IGNORE]#忽略错误 table_name SET column='value'

UPDATE table_name SET column=(select * fromtable_name)

​

#关联更新

updata table1,table2set table1.arr=val,table2.arr=val where table1.column=value and table2.column=value

updata table1join table2 set table1.column='val' , table2.column='val' where table1.col='value' and table2.col='value'​

#删除delete * from table_name [order by id desc] [limit row_count] [where];truncate * fromtable_name ; #这种方式效率很快，且重置自增

​

#关联删除delete table1,table2 from table1 inner join table2 on...;

​

#设置写内存配置

max_allowed_packet=1M #限制接收的数据包大小，打的插入和更新会被限制导致失败

net_buffer_length=2K #insert语句缓存值2K-16M

bulk_insert_buffer_size=8M #一次性insert语句插入大小

常用内置函数

1.聚合函数avg() #返回平均值，计算中忽略NULLcount() #返回行数，不忽略NULLsum() #求和min()max()2.字符串函数

group_concat() #select group_cancat(column) from table group by.. 将各项连接

concat() #select cancat(column1,column2) from table将内容连接起来

concat_ws() #select cancat_ws("-",column1,column2) from table 将内容以-连接起来left() #从左边取x个字符 left("abcde","3") ->"abc"replace() #替换 replace(整体字符串对象,被替换字符串，新字符串) update products set productsDescription = REPLACE(productsDescription,"old","new")substring() #截取 substring(string,start[,length])start是从1开始计数的

trim() #trim([{both|leading|trailing} [removed_str]],obj ) 参数：从两边去，前面去，后面去，去掉什么(不传为空格)

format() #保留位数 format(N,D[,locale]) N数字，D格式化小数位数,表示方式默认en_US3.日期和时间函数

curdate() #返回当前日期2000-01-01now() #返回当前时间2000-01-01 00:00:00此时间只算程序执行时的时间

sysdate() #返回当前系统时间2000-01-01 00:00:00此时间时刻运算

sleep()day() month() year() week()今年第几周 weekday()周一到周日对应0-6 dayname() #返回指定日期函数，设置dayname显示中文:SET @@lc_time_names='zh_CN'

datediff() #计算两时间之差天数 datediff('2000-01-01','2000-01-02')

timediff() #计算两时间之差时间返回00:00:00参数可以是日期也可以是时间

timestampdiff() #timestampdiff(单位,start,end) 计算两时间之差返回单位可以是 day week monthhour quarter second minute...

date_add()/datetime_add() #将时间间隔添加到date或datetime date_add(start,INTERVAL 数字 单位) 也可以例date_add(2000-01-01,INTERVAL '1:1' day_month)

视图

1.创建视图：create view viewname as select...2.修改视图：alter view viewname as select...

或create or replace view as select...3.删除视图:drop viewviewname;4.查看视图：

showfulltables;

​

临时表与视图区别：视图一直存在，而临时表存在于内存中当客户端会话结束时自动删除

创建临时表create temporary table tempname select...