网站合同,采集网站会员,wordpress表单支付插件下载,阿里云里面网站建设目录
三、数据库的基本操作
1.数据库中库的操作
①创建数据库
②字符集和校验规则
③操纵数据库
④备份与恢复
2.数据库中表的操作
①创建表
②查看表
1 查看表位于的数据库
2查看所有表
3查看表中的数据
4查看创建表的时候的详细信息
③修改表 …目录
三、数据库的基本操作
1.数据库中库的操作
①创建数据库
②字符集和校验规则
③操纵数据库
④备份与恢复
2.数据库中表的操作
①创建表
②查看表
1 查看表位于的数据库
2查看所有表
3查看表中的数据
4查看创建表的时候的详细信息
③修改表
1修改表的名字
2修改表的内容插入数据
3修改表的Type属性
4修改表的Field属性
5删除表中某一列
④删除表 三、数据库的基本操作
1.数据库中库的操作
①创建数据库
CREATE DATABASE database_name; -- 最基本的写法CREATE [IF NOT EXISTS] DATABASE data
CHARACTER SET utf8mb4 -- 指定字符集
COLLATE utf8mb4_unicode_ci; -- 指定校验规则[IF NOT EXISTS] -- 可选项表示如果不存在就创建
说明当我们创建数据库没有指定字符集和校验规则时
系统使用默认字符集u8
校验规则是utf8_general_ci创建一个使用utf8字符集的db2数据库
create database db2 charsetutf8;
创建一个使用utf8字符集并带校对规则的db3数据库。
create database db3 charsetutf8 collate utf8_general_ci;②字符集和校验规则 -- 查看系统默认字符集以及校验规则
show variables like character_set_database;
show variables like collation_database;-- 查看数据库支持的字符集
show charset;-- 查看数据库支持的字符集校验规则
show collation;
校验规则对数据库的影响
使用utf8_general_ci校验规则
mysql create database test1 collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)mysql use test1;
Database changed
mysql create table person(name varchar(20));
Query OK, 0 rows affected (0.01 sec)mysql insert into person values(a);
Query OK, 1 row affected (0.00 sec)mysql insert into person values(A);
Query OK, 1 row affected (0.00 sec)mysql insert into person values(b);
Query OK, 1 row affected (0.01 sec)mysql insert into person values(B);
Query OK, 1 row affected (0.01 sec)使用utf8_bin校验规则
mysql create database test2 collate utf8_bin;
Query OK, 1 row affected (0.00 sec)mysql use test2;
Database changed
mysql create table person(name varchar(20));
Query OK, 0 rows affected (0.02 sec)mysql insert into person values(a);
Query OK, 1 row affected (0.00 sec)mysql insert into person values(A);
Query OK, 1 row affected (0.00 sec)mysql insert into person values(b);
Query OK, 1 row affected (0.01 sec)mysql insert into person values(B);
Query OK, 1 row affected (0.00 sec)
查询
-- utf8_general_ci
mysql use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql select * from person where namea;
------
| name |
------
| a |
| A |
------
2 rows in set (0.00 sec)-- utf8_bin
mysql use test2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql select * from person where namea;
------
| name |
------
| a |
------
1 row in set (0.00 sec)结果排序
-- utf8_general_ci
mysql use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql select * from person order by name;
------
| name |
------
| a |
| A |
| b |
| B |
------
4 rows in set (0.00 sec)-- utf8_bin
mysql use test2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql select * from person order by name;
------
| name |
------
| A |
| B |
| a |
| b |
------
4 rows in set (0.00 sec)-- utf8_general_ci 不区分大小写 -- utf8_bin 区分大小写 不同的校验规则在进行数据库操作的时候会产生不同的结果
③操纵数据库
-- 查看数据库
mysql show databases;
--------------------
| Database |
--------------------
| information_schema |
| bookstore |
| helloworld |
| mysql |
| performance_schema |
| sys |
| test1 |
| test2 |
--------------------
8 rows in set (0.00 sec)-- 显示创建数据库语句
mysql show create database test1;
--------------------------------------------------------------------------
| Database | Create Database |
--------------------------------------------------------------------------
| test1 | CREATE DATABASE test1 /*!40100 DEFAULT CHARACTER SET utf8 */ |
--------------------------------------------------------------------------
1 row in set (0.00 sec)-- 修改数据库将 mytest 数据库字符集改成 gbk
mysql alter database test1 charsetgbk;
Query OK, 1 row affected (0.00 sec)mysql show create database test1;
-------------------------------------------------------------------------
| Database | Create Database |
-------------------------------------------------------------------------
| test1 | CREATE DATABASE test1 /*!40100 DEFAULT CHARACTER SET gbk */ |
-------------------------------------------------------------------------
1 row in set (0.00 sec)-- 删除数据库
mysql DROP database test1;
Query OK, 1 row affected (0.01 sec)④备份与恢复
1. 备份
mysqldump -P3306 -u root -p 密码 -B 数据库名 数据库备份存储的文件路径-- 示例将mytest库备份到文件退出连接
mysqldump -P3306 -u root -p123456 -B mytest D:/mytest.sql
-- 这时可以打开看看 mytest.sql 文件里的内容其实把我们整个创建数据库建表导入数据的语句
都装载这个文件中。
2. 还原
source 备份数据库的路径
mysql source D:/mysql-5.7.22/mytest.sql;
3. 备份一张表
mysqldump -u root -p 数据库名 表名1 表名2 D:/mytest.sql
4. 同时备份多个数据库
mysqldump -u root -p -B 数据库名1 数据库名2 ... 数据库存放路径
如果备份一个数据库时没有带上-B参数在恢复数据库时需要先创建空数据库然后使用数据库再使用source来还原。
5. 查看数据库连接情况
mysql show processlist;
----------------------------------------------------------------------
| Id | User | Host | db | Command | Time | State | Info |
----------------------------------------------------------------------
| 15 | root | localhost | NULL | Query | 0 | starting | show processlist |
----------------------------------------------------------------------
1 row in set (0.00 sec)2.数据库中表的操作
①创建表
mysql create database user_db;
Query OK, 1 row affected (0.00 sec)mysql use user_db;
Database changed-- 两张表使用不同的存储引擎user1使用MyIsamuser2使用InnoDB
mysql create table user1(- id int,- name varchar(20) comment 用户名,- password char(32) comment 用户密码,- birthday date comment 用户生日- )character set utf8 collate utf8_general_ci engine MyIsam;
Query OK, 0 rows affected (0.00 sec)mysql create table user2(- id int,- name varchar(20) comment 用户名,- password char(32) comment 用户密码,- birthday date comment 用户生日- )character set utf8 collate utf8_general_ci engine InnoDB;
Query OK, 0 rows affected (0.01 sec)因为使用不同的存储引擎所以表结构不同
如果直接创建而不制定存储引擎
mysql create table user3(name char(32));
Query OK, 0 rows affected (0.02 sec) 因为在配置mysql的时候配置了默认的存储引擎vim /etc/my.cnf查看配置
②查看表
1 查看表位于的数据库
-- 查看当前位于的数据库
mysql select database();
------------
| database() |
------------
| user_db |
------------
1 row in set (0.00 sec)2查看所有表
-- 查看所有表
mysql show tables;
-------------------
| Tables_in_user_db |
-------------------
| user1 |
| user2 |
| user3 |
-------------------
3 rows in set (0.00 sec)
3查看表中的数据
-- 查看表中的数据
mysql desc user1;
--------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
--------------------------------------------------
4 rows in set (0.00 sec)mysql desc user2;
--------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
--------------------------------------------------
4 rows in set (0.00 sec)mysql desc user3;
--------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------
| name | char(32) | YES | | NULL | |
--------------------------------------------
1 row in set (0.00 sec)
4查看创建表的时候的详细信息
-- 查看创建表的时候的详细信息
mysql show create table user1 \G
*************************** 1. row ***************************Table: user1
Create Table: CREATE TABLE user1 (id int(11) DEFAULT NULL,name varchar(20) DEFAULT NULL COMMENT 用户名,password char(32) DEFAULT NULL COMMENT 用户密码,birthday date DEFAULT NULL COMMENT 用户生日
) ENGINEMyISAM DEFAULT CHARSETutf8
1 row in set (0.00 sec)③修改表
1修改表的名字
-- 显示当前表信息
mysql desc user1;
--------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
--------------------------------------------------
4 rows in set (0.00 sec)-- 更改表的名字
mysql alter table user1 rename to user;
Query OK, 0 rows affected (0.00 sec)-- 显示更改后的表的名字
mysql show tables;
-------------------
| Tables_in_user_db |
-------------------
| user |
-------------------
1 row in set (0.00 sec)2修改表的内容插入数据
-- 显示表的信息
mysql desc user;
--------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
--------------------------------------------------
4 rows in set (0.00 sec)-- 向表中插入第一条数据
mysql insert into user values(1,张三,12345,2010-10-1);
Query OK, 1 row affected (0.00 sec)-- 向表中插入第二条数据
mysql insert into user values(1,李四,23456,2015-1-1);
Query OK, 1 row affected (0.00 sec)-- 查询表中所有内容
mysql select * from user;
------------------------------------
| id | name | password | birthday |
------------------------------------
| 1 | 张三 | 12345 | 2010-10-01 |
| 1 | 李四 | 23456 | 2015-01-01 |
------------------------------------
2 rows in set (0.00 sec)-- 新增一列且在birthday一列之后
mysql alter table user add image_path varchar(128) comment 用户的头像路径 after birthday;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql select * from user;
------------------------------------------------
| id | name | password | birthday | image_path |
------------------------------------------------
| 1 | 张三 | 12345 | 2010-10-01 | NULL |
| 1 | 李四 | 23456 | 2015-01-01 | NULL |
------------------------------------------------
2 rows in set (0.00 sec)
3修改表的Type属性
-- 当前表的属性
mysql desc user;
-----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| image_path | varchar(128) | YES | | NULL | |
-----------------------------------------------------
5 rows in set (0.00 sec)-- 修改某一属性如name的Type
mysql alter table user modify name varchar(60);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0-- 查看修改后的表的属性
mysql desc user;
-----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(60) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| image_path | varchar(128) | YES | | NULL | |
-----------------------------------------------------
5 rows in set (0.00 sec)4修改表的Field属性
-- 当前表的属性
mysql desc user;
-----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(60) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| image_path | varchar(128) | YES | | NULL | |
-----------------------------------------------------
4 rows in set (0.00 sec)-- 修改某一属性如name改为xingming
mysql alter table user change name xingming varchar(60) DEFAULT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0-- 查看修改后的表的属性
mysql desc user;
-----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------
| id | int(11) | YES | | NULL | |
| xingming | varchar(60) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| image_path | varchar(128) | YES | | NULL | |
-----------------------------------------------------
4 rows in set (0.00 sec)5删除表中某一列
-- 查看当前表的属性
mysql desc user;
-----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(60) | YES | | NULL | |
| password | char(32) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| image_path | varchar(128) | YES | | NULL | |
-----------------------------------------------------
5 rows in set (0.00 sec)-- 查看当前表的内容
mysql select * from user;
------------------------------------------------
| id | name | password | birthday | image_path |
------------------------------------------------
| 1 | 张三 | 12345 | 2010-10-01 | NULL |
| 1 | 李四 | 23456 | 2015-01-01 | NULL |
------------------------------------------------
2 rows in set (0.00 sec)-- 修改表删除其中的password列
mysql alter table user drop password;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0-- 查看修改后的表的属性
mysql desc user;
-----------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-----------------------------------------------------
| id | int(11) | YES | | NULL | |
| name | varchar(60) | YES | | NULL | |
| birthday | date | YES | | NULL | |
| image_path | varchar(128) | YES | | NULL | |
-----------------------------------------------------
4 rows in set (0.00 sec)-- 查看修改后的表的内容
mysql select * from user;
--------------------------------------
| id | name | birthday | image_path |
--------------------------------------
| 1 | 张三 | 2010-10-01 | NULL |
| 1 | 李四 | 2015-01-01 | NULL |
--------------------------------------
2 rows in set (0.00 sec)④删除表
-- 显示所有的表
mysql show tables;
-------------------
| Tables_in_user_db |
-------------------
| user |
-------------------
1 row in set (0.00 sec)-- 删除表
mysql drop table user;
Query OK, 0 rows affected (0.00 sec)-- 显示更改后所有的表
mysql show tables;
Empty set (0.00 sec)
