数据库基础的库、表管理
- 数据库基础
- 库管理
- MySQL中加上system,可以执行Linux命令
- 一行写多个命令
- 表管理
- 建表 例:
- 查表架构
- 创建表记录
- 查表的创建命令和属性
- 加\G,可分行显示
- 修改表记录:
- 删除表记录:
- 下面是自定义练习
- 改表的字符格式:`mysql> alter table db1.st1 DEFAULT CHARSET=utf8;`
- alter table 库.表 DEFAULT CHARSET=字符格式;
数据库基础
数据库基础
1)连接MySQL服务
连接方式:客户端连接MySQL服务的方法
命令行
web页面
安装图形软件
编写脚本(PHP、Java、Python…)
使用mysql 命令:
mysql -h服务器IP -u用户名 -p密码 [数据库名]
quit 或 exit 退出
2)数据存储流程
客户端把数据存储到数据库服务器上的步骤
连接数据库服务器
建库 ##相当于创建文件夹
建表 ##相当于创建文本文件
插入记录 ##相当于在文件文件中写入内容
断开连接
3)MySQL管理环境
SQL命令使用规则
SQL命令不区分字母大小写(密码,变量值除外)
每条SQL命令以;结束
默认命令不支持Tab键自动补齐
\c 终止sql命令
4)SQL命令分类
管理数据库使用SQL(结构化查询语言)
DDL 数据定义语言:如 create、alter、drop
DML 数据操作语言:如 insert、update、delete
DCL 数据控制语言:如 grant、revoke
DTL 数据事务语言:如 commit、rollback、savepoint
库管理
库类似于文件夹,用来存储表
…
show databases; ##显示已有的库
select user(); ##显示连接用户
use 库名; ##切换库
select database(); ##显示当前所在的库
create database 库名; ##创建新库
show tables; ##显示已有的表
drop database 库名; ##删除库
库名的命名规则:
仅可以使用数字、字母、下划线、不能纯数字
区分字母大小写,具有唯一性
不可使用指令关键字、特殊字符
create database DB1; ##创建数据库DB1
create database db1; ##创建数据库db1
MySQL中加上system,可以执行Linux命令
mysql> system ls /var/log/mysql/mysqld.log
/var/log/mysql/mysqld.log
mysql>
一行写多个命令
mysql> create database db1; create database db2;
ERROR 1007 (HY000): Can't create database 'db1'; database exists
Query OK, 1 row affected (0.11 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| dbaaa |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
7 rows in set (0.15 sec)
表管理
表管理命令:
建表:表用于存储数据文件
create table 库名.表名(
字段名1 类型(宽度),
字段名2 类型(宽度),
… …
) DEFAULT CHARSET=utf8; ##指定中文字符,让表支持插入中文
建表 例:
mysql> create table db1.st1(name char(10),sex char(4),address char(60));
Query OK, 0 rows affected (2.06 sec)
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
验证:
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| st1 |
+---------------+
1 row in set (0.04 sec)
desc 库名.表名; ##查看表结构
drop table 库名.表名; ##删除表
select * from 库名.表名; ##查看表记录,表的内容
insert into 库名.表名 values(值列表); ##插入表记录
update 库名.表名 set 字段=值; ##修改表记录
delete from 库名.表名; ##删除表记录
show create table 表名; ##查看创建表的命令和相关属性
查表架构
例:
mysql> desc db1.st1;
+---------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name | char(10) | YES | | NULL | |
| sex | char(4) | YES | | NULL | |
| address | char(60) | YES | | NULL | |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.02 sec)
创建表记录
创建用户信息
mysql> insert into db1.st1 values ("ohno","boy","guangdong"),("ninomiya","boy","shanxi"),("xiaoxu","girl","shenzhen");
Query OK, 3 rows affected (0.30 sec)
Records: 3 Duplicates: 0 Warnings: 0
验证,查表记录
mysql> select * from db1.st1; +----------+------+-----------+
| name | sex | address |
+----------+------+-----------+
| ohno | boy | guangdong |
| ninomiya | boy | shanxi |
| xiaoxu | girl | shenzhen |
+----------+------+-----------+
3 rows in set (0.01 sec)
mysql>
查表的创建命令和属性
mysql> show create table st1;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| st1 | CREATE TABLE `st1` (
`name` char(10) DEFAULT NULL,
`sex` char(4) DEFAULT NULL,
`address` char(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
加\G,可分行显示
mysql> show create table db1.st1 \G;
*************************** 1. row ***************************
Table: st1
Create Table: CREATE TABLE `st1` (
`name` char(10) DEFAULT NULL,
`sex` char(4) DEFAULT NULL,
`address` char(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
ERROR:
No query specified
修改表记录:
mysql> select * from db1.st1;
+----------+------+-----------+
| name | sex | address |
+----------+------+-----------+
| ohno | boy | guangdong |
| ninomiya | boy | shanxi |
| xiaoxu | girl | shenzhen |
| jun | boy | dongjing |
| sakurai | girl | qingying |
| aiba | girl | qianye |
+----------+------+-----------+
6 rows in set (0.01 sec)
例:改性别列为女生:
mysql> update db1.st1 set sex="girl";
Query OK, 3 rows affected (0.17 sec)
Rows matched: 6 Changed: 3 Warnings: 0
mysql> select * from db1.st1;
+----------+------+-----------+
| name | sex | address |
+----------+------+-----------+
| ohno | girl | guangdong |
| ninomiya | girl | shanxi |
| xiaoxu | girl | shenzhen |
| jun | girl | dongjing |
| sakurai | girl | qingying |
| aiba | girl | qianye |
+----------+------+-----------+
6 rows in set (0.00 sec)
删除表记录:
mysql> delete from
库名.表名;
下面是自定义练习
1、建表结构:
mysql> create table db1.st2 (name char(20), sex char(5), age char(10) ) DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.57 sec)
2、查表结构:
mysql> desc db1.st2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(20) | YES | | NULL | |
| sex | char(5) | YES | | NULL | |
| age | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.02 sec)
3、查建表语句:
mysql> show create table db1.st2 \G;
*************************** 1. row ***************************
Table: st2
Create Table: CREATE TABLE `st2` (
`name` char(20) DEFAULT NULL,
`sex` char(5) DEFAULT NULL,
`age` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
4、添加表记录:
mysql> insert into db1.st2 values ("zhangsan","boy","32"),("lisi","boy",23),("wangwu","boy","32");
Query OK, 3 rows affected (0.11 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from db1.st2;
+----------+------+------+
| name | sex | age |
+----------+------+------+
| zhangsan | boy | 32 |
| lisi | boy | 23 |
| wangwu | boy | 32 |
+----------+------+------+
3 rows in set (0.00 sec)
mysql>
5、更新表记录,年龄行为26岁:
mysql> update db1.st2 set age="26";
Query OK, 3 rows affected (0.14 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from db1.st2;
+----------+------+------+
| name | sex | age |
+----------+------+------+
| zhangsan | boy | 26 |
| lisi | boy | 26 |
| wangwu | boy | 26 |
+----------+------+------+
3 rows in set (0.00 sec)
6、删表记录
mysql> delete from db1.st2;
Query OK, 3 rows affected (0.09 sec)
mysql> select * from db1.st2;
Empty set (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| st1 |
| st2 |
+---------------+
2 rows in set (0.08 sec)
7、删表:
mysql> drop table db1.st2;
Query OK, 0 rows affected (2.08 sec)
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| st1 |
+---------------+
1 row in set (0.01 sec)
mysql>