Mysql数据库基本sql语句

1 常用的数据类型

char
char的长度是不可变。char如果存入数据的实际长度比指定长度要小 会补空格至指定长度 如果存入的数据的实际长度大于指定长度，低版本会被截取高版本会报错。
varchar
varchar长度是可变的，默认会加一个隐藏的结束符，因此结束符会多算一个字节。

2 查看数据库

2.1 查看当前服务器中的数据库

SHOW DATABASES;         #大小写不区分，分号“;”表示结束 

2.2 查看数据库中的表

法一 ：在库中查看表
USE 数据库名;
SHOW TABLES;    
法二 ：在库外查看表
show tables from mysql;

2.3 查看表的结构（字段）

法一
USE 数据库名;
DESCRIBE 表名

法二
DESCRIBE[数据库名.]表名；
#DESCRIBE可缩写成DESC 

3 SQL语句

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

3.1 SQL语句分类

3.2 创建及删除数据库和表

3.2.1 创建新的数据库

CREATE DATABASE 数据库名;

3.2.2 创建新的表

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。 
例： 
CREATE TABLE my (id int NOT NULL,name char(10) NOT NULL,score decimal (5,2),passwd char(48) DEFAULT '',PRIMARY KEY (id));

3.2.3 删除指定的数据表

#如不用USE进入库中，则需加上数据库名
DROP TABLE [数据库名.]表名;
#在库中的话 直接DROP TABLE 表名;

3.2.4 删除指定的数据库

DROP DATABASE 数据库名;

4 管理表中的数据记录

4.1 向数据表中插入新的数据记录

法一
insert into 表名（字段1,字段2[,...]） values（字段1的值,字段2的值,....）；
法二
insert into 表名 values（字段1的值,字段2的值,....）； 
 例： 
#指定全部字段加入值
insert into my(id,name,age,sex,score) values(1,'小明',18,'男',30.5);
#不指定字段为表中字段插入所有值
insert into my values (3,'小红',17,'女',60);
#指定个别字段添加值
insert into my(name,age,sex,score) values('小强',18,'男',31.5);
#如果此处不指定name那么会报错，因为name设为not null，name字段不能为空

4.2 查询表中数据记录

#查看所有数据
select * from 表名;   *代表所有字段

#查询指定数据 
SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];

#以列表方式纵向显示
select * from 表名 \G;

#查询前两行记录
select * from 表名 limit 2; 
#表示查看n行后的m行数据记录
select * from 表名 limit n，m; #查看（n+1 ,n+m）行记录

查看所有数据
查询指定数据

根据条件查询指定数据

以列表方式纵向显示

查询前两行记录

查询3~5行记录

4.3 修改 更新数据表中的数据记录

使用update的时候一定要加where语句去匹配条件否则会修改表中所有记录

UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

4.4 在数据表中删除指定的数据记录

DELETE FROM 表名 [WHERE 条件表达式];

5 修改表名和表结构

5.1 修改表名

ALTER TABLE 旧表名 RENAME 新表名;

5.2 扩展表结构

ALTER TABLE 表名 ADD 字段名 数据类型; 
例 
ALTER TABLE tieniu ADD address varchar(50) not null default '地址不详'; 
#增加“address"字段，不允许为空值，默认值为“地址不详”。
#default '地址不详：表示此字段设置默认值地址不详；可与NOT NULL配合使用

5.3 修改字段，添加唯一键

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型 [unique key];

5.4 添加主键

#创建表时设置主键
create table 表名(字段1 XXX, 字段2 XXX, ....primary key(字段));
create table 表名(字段1 XXX primary key, . . ..);     #将主键作为字段1的属性

#现有表中添加主键
ALTER TABLE 表名 add primary key(字段名);

唯一键与主键区别
共同点：字段的值都是唯一性，不允许有重复的值
不同点：

• 一个表中只能有1个主键，但是可以有多个唯一键
• 主键字段中不允许有null值，唯一键是允许有null

5.5 删除字段

ALTER TABLE 表名 drop 字段名;

案例扩展

use qyd;
create table if not exists info (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null default '匿名',
cardid int(18) not null unique key,
hobby varchar(50));

• if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
• int(4) zerofill：表示若数值不满4位数，则前面用“0”填充，例0001
• auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如果添加的记录数据没有指定此字段的值或者添加失败也会自动递增一次
• unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键
• not null：表示此字段不允许为NULL
  

6 mysql6种常见约束

• 主键约束（primary key）
• 外键约束（foreign key）
• 非空约束（not null）
• 唯一性约束（unique）
• 默认值约束（default）
• 自增长约束（auto_increment）

7 创建临时表

临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。 如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。

CREATE TEMPORARY TABLE 表名（字段1 数据类型, 字段2 数据类型（，...] [,PRIMARY KEY（主键名）]）；

添加临时表test

create temporary table jianlai (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));

查看当前库中所有表

show tables;  #查看不到临时表  

在临时表中添加数据

insert into test values(0001,'zhangsan',123456789,'play'); 
#查看当前表中所有数据   
select * from test;
#退出数据库
quit                       

重新登录后进行查看

mysql -u root -p
#查看之前临时表中所有数据，发现已经被自动销毁
select * from test;

8 创建外键约束，保证数据的完整性和一致性

外键的定义：如果同一个属性字段x在表一中是主键，而在表二中不是主键，则字段x称为表二的外键。
主键表和外键表的理解：
（1）以公共关键字作主键的表为主键表（父表、主表）
（2）以公共关键字作外键的表为外键表（从表、外表）
注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。

#创建主表 class
create table class (cid int, cname char(10));

#创建从表 student
create table student (id int, name char(10), age int, classid int);

#为主表class添加一个主键约束。主键名建议以“PK"开头。
alter table class add constraint PK_CID primary key(cid);

#为从表student表添加外键，并将student表的cid字段和student表的classid字段建立外键关联。外键名建议以“FK"开头。
alter table student add constraint FK_CLASSID foreign key(classid) references class(cid);

#插入新的数据记录时要先插入主表的在插入从表的
  #在主表插入数据
insert into class values(1,'下棋');
insert into class values(2,'跳舞');
insert into class values(3,'游泳');
  #在从表插入数据
insert into student values(1,'张三',22,3);
insert into student values(2,'李四',34,2);
insert into student values(3,'王五',45,1);

#删除数据记录时要先删除从表再删主表，也就是说删除主键表必须先删除其他关联的表中记录
delete from student where name='张三';
delete from class where cid=3;
select * from xy;
select * from kc;

创建主表和从表

在主表添加主键约束

在从表添加外键约束

主表插入数据（先查主表再插从表）

从表插入数据

删除记录

9 数据库用户管理

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];

‘用户名’：指定将创建的用户名
‘来源地址’：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%
‘密码’：

• 若使用明文密码，直接输入’密码’，插入到数据库时由Mysql自动加密；
• 若使用加密密码，需要先使用SELECT PASSWORD(‘密码’); 获取密文，再在语句中添加 PASSWORD ‘密文’;
• 若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）

9.1 使用明文创建用户

#创建用户zhangsan只允许从本机登录密码为123123
create user '张三'@'localhost' identified by '123123';

9.2 使用密文创建用户

#获取密文
select password('123123');      

#创建用户
create user 'my'@'localhost'identified by password'*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1';

9.3 查看用户信息

创建后的用户保存在 mysql 数据库的 user 表里

se mysql;          #切换库

SELECT User,authentication_string,Host from user;  #查看user表中的用户信息
    #指定字段用户，登录认证字符串，登录主机，查看数据记录

9.4 重命名用户

rename user '原用户名'@'登录主机' to '新用户名'@'登录主机';

9.5 删除用户

DROP USER '用户名'@'登录主机'；

9.6 修改当前登录用户密码

SET PASSWORD = PASSWORD('123123');

9.7 修改其他用户密码

SET PASSWORD FOR '用户'@'登录主机'=password（'新的密码'）

9.8 忘记root密码的解决方法

1、#修改配置文件/etc/my.cnf，不使用密码直接登录到mysql。
vim /etc/my.cnf
[mysqld]
skip-grant-tables          #添加此行，使登录mysql不使用授权表
​systemctl restart mysqld    #重启服务
mysql    #直接登录
 ​​
2、#使用update命令修改root密码，刷新数据库。
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('123123') where user='root';
flush privileges;   #刷新数据库
quit                #退出
mysql -uroot -p123123     #免交互登录"-p密码"不能有空格
 ​
3、#删除配置文件中的配置，之后重启服务。
注意：最后再把/etc/my.conf 配置文件里的skip-grant-tables 删除，并重启mysql服务。
vim /etc/my.cnf
systemctl restart mysqld    #重启服务

修改配置文件

重启服务并登录

修改root密码，刷新数据库
退出重新登录
删除配置文件中的配置 重启服务

10 数据库用户授权

10.1 授予权限

GRANT语句：专门用来设置数据库用户的访问权限。

• 当指定的用户名不存在时，GRANT语句将会创建新的用户；
• 当指定的用户名存在时，GRANT 语句用于修改用户信息。

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];

• 权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
• 数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“”。例如，使用“kgc.”表示授权操作的对象为 kgc数据库中的所有表。
• ‘用户名@来源地址’：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP 地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.my.com”、“192.168.52.%”等。
• IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分， 则用户的密码将为空。
  例：
  允许用户my在本地查询 qyd数据库中 所有表的数据记录，但禁止查询其他数据库中的表的记录

mysql -u root- p123123 #使用root用户登录

库中没有用户my

grant select on qyd.*to 'my'@'localhost' identified by '123123';
#授权*表示库下的所有表
quit        #退出数据库
mysql -u my -p123123   #用户my登录验证
show databases;#查看所有库
use qyd;切换库
show tables;查看所有表
select *from class;#查看表所有字段数据记录

未授权前用户my看不到库




允许用户qy在所有终端远程连接mysql，并拥有所有权限

#使用root用户登录
mysql -u root -p123123        
#用户qy授权允许查看所有库所有表所有主机登录并给与密码123123
grant all on *.* to 'qy'@'%' identified by '123123';
#退出    
quit
#在另一台主机上使用qy用户登陆  
mysql -u 用户 -p123123 -h目标ip/主机-P端口号
#查看当前所有库   
show databases;
#切换库
use qyd;
#查看所有表
show tables;
#查看表内所有段数据记录
select * from class;

注：如果此时用户表内有同样的用户但是登录主机为本机的话你在本机登录则使用只允许本机登录的用户密码

10.2 查看权限

SHOW GRANTS FOR 用户名@来源地址;

10.3 撤销权限

REVOKE ALL ON *.* FROM '用户'@'来源地址'; #撤销所有权限

11 数据表高级操作

11.1 克隆表

法一： 通过like复制
create table 新表名 like 旧表名;           
 
insert into 新表名 select * from 旧表名;     #将旧表数据导入新表

法二：创建表的同时导入数据

create table 新表名 (select * from 旧表名);

11.2查看表结构

获取数据表的表结构、索引等信息

show create table表名\G;

desc 表名\G;

11.3 清表–删除表内所有数据

11.3.1 delete

• DELETE清空表后，返回的结果内有删除的记录条目；
• DELETE工作时是一行一行的删除记录数据的；
• 如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录

delete from 表名；

11.3.2 truncate删除

• TRUNCATE 清空表后，没有返回被删除的条目；
• TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；
• 使用 TRUNCATE TABLE 清空表内数据后，ID 会从 1 开始重新记录