Mysql 增删改查学习记录

mysql数据库的基本操作

1.如何登录到本地数据库

# 语法
mysql -uuserName -pPassword
或
mysql -uuserName -p
# 当不指定登录的主机时，默认登录的主机地址为localhost

2.如何登录到远程数据库

mysql -uUserName -p -h IP或domain

3.如何查看当前数据库软件下有什么数据库

show databases;    # 命令

4.如何选择数据库

use 库名;

5.如何查看当前数据库下有哪些表

show tables;

6.如何查询表的所有数据

select * from 表名;

7.如何查询指定字段名下的数据

select 字段名1,字段名2 from 表名;

8.如何退出数据库的登录

exit
或
quit

9.如何在数据库服务器中创建自己的数据库

create database 库名;

10.如何在某个数据库下创建一个表

其语法格式为:
create table 库名.表名(字段名1 字段类型,字段名2 字段类型,字段名3 字段类型);

举例命令为:
create table admin.student(id INT(3),username VARCHAR(16),password VARCHAR(32),reg_date DATE);

注意事项:

1:char()与varchar()的区别在于char()是定长的,哪怕存储的字符串没有达到”()”中数字的上限,char()依然会占用空格来填充空间.而varchar()则是不定长的,没有达到”()”中的上限则会自动去掉后面的空格;

2:定义最后一个字段的时候不要加”,”;

3:上面的”CHAR”,”VARCHAR”,”DATE”可以用小写.不过最好用大写来表示区分关键字,若不然也许写到后面你自己都不知道这个词是数据库中的关键字还是你自己自定义的一些数据,同时一定要用英文的标点符号也必须半角输入。

11.如何查看某个表的结构

desc tableName;
或
describe tableName;

mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
Field 表示字段的名称
Type 表示字段的类型,可以是INT 也可以CHAR 或者 VARCHAR 以及DATE
Null 表示其值是否能为NULL。
Key 表示是否是关键字 如可以定义为:  primary key 或者 unique key   ...
Default 表示若是改字段没有主动设置值的时候，他的默认值是什么。
Extra 表示额外的信息

12.如何往一个表中插入数据

语法: insert into 库名.表名 values (数据1,数据2,数据3,数据4);
这里分别对应着字段1的值为数据1，字段2的值为数据2,依次类推,按位传参

示例命令:
insert into admin.student values (1,'admin','123456',20221015);

插入数据之后查询结果如下：

当我们尝试执行如下数据：

insert into admin.student values (2,'test','abcd1234',null);

你希望注册日期哪里的数据为null，也就是为空，那么请注意，当你希望某个字段数据为Null,也就是为空的时候，请不要直接写null，这意味着这里的字段值为’null’，并不是为空。

那当我们想让某个字段的数据为空该如何去做？

其实在插入数据的时候，还可以单独指定为某个字段插入数据：

语法：insert into 库名.表名 (字段名1,字段名3,字段名4) values (数据1,数据3,数据4);

示例命令:
insert into admin.student (id,username,reg_date) values (3,'guest',20221001);
代表我只在username和id以及reg_date字段上面插入的一条,其他皆为NULL/默认值的数据

其插入之后查询的结果如下：

另外在这里，其实我们使用use命令选择了某个数据库的话，你的任何关于已选择库下的任何表的操作，都不需要写库名.表名的形式，直接写表名就行。这种库名.表名的形式适用在操作use命令选择的数据库之外的数据库。

13.如何删除表内的数据

语法: delete from 表名 where 条件;

当我们执行如下命令的时候，它会将student表下的所有数据都删除，因为我们没有给它指定约束条件。

因此我们再来查询studnet表，就已经显示空空如也。

我们再次往表内插入几行数据，用于实验：

insert into student values (1,'admin','123456',20221015),(2,'test','Admin123',20201104),(3,'guest','abcd1234',20220103);

再次查询student表：

接下来，我们来说下where条件约束的使用方法，当我们直接执行delete from student则会删除student表内的所有数据，假设此时我们只想删除id为2的那行数据，我们可以这样做：

delete from student where id=2;

可以看到，这里就只删除了id为2那行数据，当然where条件可以用在许多地方，比如删除语句delete以及查询语句selete乃至我们后面要学的update更新语句。

例如：我现在只想查看student表内id为3的哪行数据

select * from student where id=3;

14.如何修改一个表的内容

语法 update 表名 set 字段名=新值  where 条件;

示例命令:
update student set username='x1ong' where username='admin'
将username字段名原有的admin值修改为x1ong

再次查询的时候，就由原来的admin改为了x1ong

15.如何删除一个表或者删除一个库

# 删除一个库
drop database 库名;

# 删除一个表
drop table 表名;

16.如何清空一个表的内容

truncate table 表名
# truncate清除之后会初始自增值

17.mysql中建表的约束

• 主键约束

它能够唯一确定一张表中的一条记录，增加主键约束之后，就可以使得该字段不能重复而且其他字段不能为空。

如何创建一个表中存在主键约束的字段：

create table info(
	id INT(3) PRIMARY KEY,
	username VARCHAR(16),
	password VARCHAR(32)
);

创建一个info表,分别有三个字段id和username以及password,其中将id字段被设置为主键字段，也就是说id不能为空且其他字段不能重复。

我们使用desc命令查看info表的结构：

从以上图中我们可以看到，当id字段被设置为主键约束之后，该字段的属性Null自动变为了NO，该字段属性的key值自动变成了PRI（PRIMARY），这也就意味着id这个字段是不可以为NULL的，且是不能重复滴。

• 复合主键

  复合主键的作用是不允许某个字段为NULL，且不允许他们重复。它与主键约束的区别是主键约束不允许整行数据有和重复值，而复合主键只要他们所有的字段都不是相同的情况下，是可以允许其中的字段重复的。

create table user(
id INT(3),
username VARCHAR(15),
password VARCHAR(15),
PRIMARY KEY(id,username)
);

以下命令是可以正常执行的：

insert into user values (1,'admin','Admin123'),(2,'admin','Admin123');

因为复合主键允许只要他们所有的字段不重复，是可以被接受的，而主键约束是不允许其他字段重复的。

• 自增约束

自增约束则是允许被定义自增约束的字段依次自动递增的。

create table user1(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(15)
);

由于id字段被设置为了自增约束，那么我们在插入的时候，则可以不知道id字段，让它自动的递增，如下：

我们并没有插入id那一列，但是他们就自动的递增了。这就是自增约束的作用。

• 唯一约束

顾名思义，这里表示该字段是唯一的，不可重复的。

首先先创建一个user2数据表

create table user2(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(15)
);

接着为user2表中的name字段添加唯一约束

alter table user2 add unique(name);

我们为user2表中插入一个name为admin的值

insert into user2 (name) values ('admin');

当我们再次插入name为admin的值时提示重复，这个就是唯一约束。

• 非空约束

非空约束，顾名思义，就是不能某个字段不能为空(Null)。

下面我们创建一个user3表进行演示：

create table user3(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(15) NOT NULL,
	password VARCHAR(32)
);

当我们向password字段插入数据时，而不向name字段插入数据，则会抛出如下错误：

• 默认约束

  我们创建user4数据表用于演示：

create table user4(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(15),
	password VARCHAR(15),
	gender int(1) DEFAULT 0 COMMENT '0表示mela,1表示female'
);

那么这样，我们就为gender设置了默认值为0，如果没有指定gender字段的值，则默认为0，接下来，我们插入数据：

insert into user4 (name,password) values ('admin','Admin123');

• 外键约束

外键约束涉及到两个表，一个是主表(父表)，另外一个是副表(子表)，外键约束主要是将父表的某一个字段与子表的某个字段做一个绑定操作(映射)。

为了实验方便，我们这里就以班级表和学生表为例：

首先创建班级表：

create table classes(
	id INT PRIMARY KEY,
	name varchar(30)
);

之后在该报中插入四个班的记录：

insert into classes values (1,'一班'),(2,'二班'),(3,'三班'),('4','四班');

接下来我们创建student表，将student的class_id字段与classes表中的id做一个绑定。

create table student(
	id INT PRIMARY KEY,
	name VARCHAR(20),
	class_id INT(11),
	FOREIGN KEY(class_id) references classes(id)
);

接下来，为student插入学生数据：

insert into student values (1,'张三',1),(2,'李四',2),(3,'王五',3),(4,'赵七',4);

此时student表的class_id字段就与classes表中的id字段做了关联。

注意：被别的表关联的外键不能直接被删除

数据库alter语法的使用

1.如何往一个已经创建好的表追加新的字段

create table users(
id INT,
username VARCHAR(15),
password VARCHAR(32)
);

语法: alter table 表名 add 字段名 字段类型;

示例命令:
alter table users add reg_date DATE;

2.如何更改一个表的字段名

mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| username | varchar(15) | YES  |     | NULL    |       |
| password | varchar(32) | YES  |     | NULL    |       |
| reg_date | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

# 语法: alter table 表名 change 旧字段名 新字段名 新字段类型;

# 假设我们将username字段名,修改为name,示例命令:

alter table users change username name varchar(16);

3.如何修改一个表的字段类型

mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(16) | YES  |     | NULL    |       |
| password | varchar(32) | YES  |     | NULL    |       |
| reg_date | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+

# 语法: alter table 表名 modify 字段名 新的字段类型;
# 假设将name的类型修改为char(30),示例命令:

alter table users modify name char(30);

mysql> desc users;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | char(30)    | YES  |     | NULL    |       |
| password | varchar(32) | YES  |     | NULL    |       |
| reg_date | date        | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4.如何重命名一个表的名字

# 语法: alter table 表名 rename 新表名;
# 示例命令:
alter table classes rename class

5.如何删除一个表中存在的字段

# 语法: alter table 表名 drop column 列名;

# 示例命令:
alter table users drop column reg_date;

6.如何在一个已经创建好的表中为字段添加主键

# 语法: alter table 表名 add PRIMARY KEY(字段名)
# 示例命令:

alter table users add PRIMARY KEY(id);

示例:  alter table user1 change name name varchar(50) NOT NULL;

示例: alter table user1 change class_id classid int PRIMARY KEY AUTO_INCREMENT;

7.如何删除一个主键约束

语法: alter table 表名 DROP 主键名称

示例: alter table user1 DROP PRIMARY KEY;

8.如何查看当前mysql的连接信息

show processlist;

mysql的常用函数

系统函数:
version() —— 数据库的版本
user() —— 当前数据库登录的用户
database() —— 当前使用的数据库名
@@datadir —— 系统保存数据库的路径
@@basedir—— mysql的安装路径
@@version_compile_os —— 安装mysql的系统

创建mysql的登录用户

在mysql中，我们可以查看mysql数据库下的users表中Host、User、Authentication_String字段来获取当前mysql的登录用户的主机以及用户名和密码。

mysql> select host,user,authentication_string from mysql.user;
+-----------+---------------+-------------------------------------------+
| host      | user          | authentication_string                     |
+-----------+---------------+-------------------------------------------+
| localhost | root          | *8002BEB3BAFAA2F1DF2C1266B15BAC5C1E4782D3 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| %         | x1ong         | *3ECAABBDBED377709C6F31AECB2456A2C5C159B7 |
| 127.0.0.1 | test          | *3ECAABBDBED377709C6F31AECB2456A2C5C159B7 |
+-----------+---------------+-------------------------------------------+

mysql为不同的登录地址提供了不同的登录用户，当使用root用户登录进行本地mysql登录的时候，使用的是用户名root，host值为localhost的用户进行校验。

当使用x1ong用户进行远程登录的时候，使用的用户名为x1ong，host值%的用户进行校验。当一个用户的host值为%表示该用户可以进行远程登录。是一个远程登录用户。

当使用test用户，使用-h指定的服务器地址为127.0.0.1时，使用的是用户名为test，host值为127.0.0.1进行校验。

创建mysql登录用户的语法：

create user '用户名'@'主机名' identified by "用户的密码";

示例：

create user 'x1ong'@'%' identified by "Qwer1234!@#$";
# 创建一个用户名为x1ong密码为Qwer1234!@#$的远程登录用户。

更新mysql的登录用户密码

# 语法
alter user '用户名'@'主机名' identified by "新密码";

# 示例
alter user 'root'@'localhost' identified by "Qwer1234";
# 将主机名为localhost的root用户密码修改为Qwer1234

为mysql的用户赋予权限

首先我们创建一个test用户，对其赋予有对mysql库下的user表的查询权限。

# 创建test用户
create user 'test'@'127.0.0.1' identified by "Qwer1234";
# 为其赋予对mysql库下的user表有查询的权限
grant select on mysql.user to 'test'@'127.0.0.1'
# 查询当前用户的权限
show grants for '用户名'@'主机名';

将普通用户添加为管理员

# 创建test用户
create user 'x1ong'@'localhost' identified by "Qwer1234";
# 将其添加为管理员
grant all privileges on *.* to 'x1ong'@'localhost';