MYSQL使用(1)

学程序的羊

已于 2024-07-24 11:35:40 修改

阅读量220

点赞数 9

文章标签： mysql 学习数据库

于 2024-04-30 15:15:36 首次发布

本文链接：https://blog.csdn.net/weixin_72426758/article/details/138338645

版权

MYSQL使用

各期链接

数据库的创建

直接创建数据库

create database db;

创建字符集默认为utf8的数据库

create database db character set=utf8;

创建字符集默认为utf8且带有校验规则的数据库

create database db character set=utf8 collate=utf8_general_ci;

说明：当我们创建数据库没有指定字符集和校验规则时，系统默认使用字符集为utf8,校验规则为utf8_general_ci

字符集和校验规则

查看系统默认字符集和校验和

show variables like 'character_set_database';
show variables like 'collation_database';

查看数据库支持的字符集

show charset;

查看数据库支持的字符集校验规则

show collate;

观察校验和不同对数据库有什么影响

不区分大小写

	create database text1 collate=utf8_general_ci;
	use text1;
	create table student(name varchar(20));
	insert into student values('A');
	insert into student values('a');
	insert into student values('B');
	insert into student values('c');

区分大小写

	create database text1 collate=utf8_bin;
	use text1;
	create table student(name varchar(20));
	insert into student values('A');
	insert into student values('a');
	insert into student values('B');
	insert into student values('c');

进行查询和排序

不区分大小写

查找结果

mysql> use text1;
Database changed
mysql> select * from student where name='A';
+------+
| name |
+------+
| A    |
| a    |
+------+
2 rows in set (0.00 sec)

排序结果

mysql> select * from student order by name;
+------+
| name |
+------+
| A    |
| a    |
| B    |
| c    |
+------+
4 rows in set (0.00 sec)

区分大小写
查找结果

mysql> use text2;
Database changed
mysql> select * from student where name='A';
+------+
| name |
+------+
| A    |
+------+
1 row in set (0.00 sec)

排序结果

mysql> select * from student order by name;
+------+
| name |
+------+
| A    |
| B    |
| a    |
| c    |
+------+
4 rows in set (0.01 sec)

操作数据库

查看数据库

show databases;

显示创建数据库语句

show create database 数据库名;

修改数据库

alter database 数据库名 [character set=要修改的数据库字符集] [collate=要修改的字符集校验和];

示例

mysql> show create database text1;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| text1    | CREATE DATABASE `text1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database text1 character set=gbk;
Query OK, 1 row affected (0.00 sec)
mysql> show create database text1;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| text1    | CREATE DATABASE `text1` /*!40100 DEFAULT CHARACTER SET gbk */ |<<原来时utf8
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

删除数据库

drop database 数据库名;

备份与还原

//备份语法
# mysqldump -P3306 -u root -p 密码 -B 数据库名 > 数据库备份存储的文件路径 
//示例
[lhb@localhost MYSQL]$ mysqldump -P3306 -uroot -p -B text1 > ./text1.sql

//还原语法
source 备份文件路径；

只备份数据库中的其中几个表

#mysqldump -P3306 -uroot -p 密码 -B 数据库名 表1 表2 > ./text1.sql

备份时-B选项没有带的，还原时要先创建空的数据库

查看数据库连接情况

show processlist;

MYSQL表的操作（主要对表属性的操作）

创建表

语法

create table 表名(
列名1 列类型,
列名2 列类型，
列名3 列类型
)character set 字符集 collate 校验规则 engine 存储引擎;

示例

create table student(
name varchar(20),
id varchar(10),
password char(32)
)character set utf8 engine MyISAM;

查看表

查看所在数据库里有哪些表

show tables;

查看指定表结构

desc 表名;

查看指定表的所有内容（属于查看表内部的内容以后细讲，先做了解）

select * from student;

修改表

插入数据（这个属于修改表里的内容以后详细讲，先做了解）

insert into student values('张三','01','12345'),
('李四','02','54321');

mysql> select * from student;
+--------+------+----------+
| name   | id   | password |
+--------+------+----------+
| 张三   | 01   | 12345    |
| 李四   | 02   | 54321    |
+--------+------+----------+
2 rows in set (0.01 sec)

添加字段
语法

ALTER TABLE 表名 ADD 列名 列类型;

示例

mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(20) | YES  |     | NULL    |       |
| id       | varchar(10) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table student add image char(32);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(20) | YES  |     | NULL    |       |
| id       | varchar(10) | YES  |     | NULL    |       |
| password | char(32)    | YES  |     | NULL    |       |
| image    | char(32)    | YES  |     | NULL    |       | <<新添加的列
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

添加后不影响原来表的数据

mysql> select * from student;
+--------+------+----------+-------+
| name   | id   | password | image |
+--------+------+----------+-------+
| 张三   | 01   | 12345    | NULL  | << 新加的数据为空
| 李四   | 02   | 54321    | NULL  |
+--------+------+----------+-------+
2 rows in set (0.00 sec)

修改原来字段的类型
语法

ALTER TABLE 表名 MODIFY 列名 新的列类型

示例

mysql> ALTER TABLE student MODIFY name varchar(60);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc student;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| name     | varchar(60) | YES  |     | NULL    |       |  <<长度变为60
| id       | varchar(10) | YES  |     | NULL    |       | 
| password | char(32)    | YES  |     | NULL    |       |
| image    | char(32)    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

删除字段
语法

ALTER TABLE 表名 DROP 列名;

示例

mysql> ALTER TABLE student DROP password;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(60) | YES  |     | NULL    |       |  >>passwork字段被删除
| id    | varchar(10) | YES  |     | NULL    |       |
| image | char(32)    | YES  |     | NULL    |       |  
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from student;
+--------+------+-------+
| name   | id   | image |
+--------+------+-------+
| 张三   | 01   | NULL  | >> 表中数据也会失去
| 李四   | 02   | NULL  |
+--------+------+-------+
2 rows in set (0.00 sec)

修改表名

-- to可省略
alter table 表名 rename to 新表名;

删除表

语法

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

示例

drop table 表名;