数据库基本操作
目录
1、数据库的连接方式
本地连接的时候使用mysql -u root -p可以连接数据库，但这只是本地的连接方式，在工作当中我们连接某一上服务器上的数据库，而不是直接本地进行连接。
-P //指定端口，默认是3306
-h //指定主机IP
-u //指定账户名称
-p //指定用户的密码
[root@mysql01 ~]# mysql -u root -p
Enter password:
2、数据库的基本操作
1、查看数据库的版本
MariaDB [(none)]> select version();
+----------------+
| version() |
+----------------+
| 5.5.64-MariaDB |
+----------------+
1 row in set (0.00 sec)
2、创建数据库DDL
MariaDB [(none)]> create database bgx_edu;
Query OK, 1 row affected (0.00 sec)
注意
数据库名称严格区分⼤⼩写
数据库名称必须是唯⼀
数据库名称不允许使⽤数字
数据库名称不能使⽤关键字命名create select
3、查看所有的数据库
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bgx_edu |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
//执行命令不区分大小写
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bgx_edu |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
4、 删除数据库和删除数据里面的表
//删除bgx_edu数据库
MariaDB [(none)]> drop database bgx_edu;
//删除bgx_edu数据库里面的t1表
MariaDB [(none)]> drop database bgx_edu.t1;
5、 查询某个库的表
//进入某个数据库
MariaDB [(none)]> use bgx_edu;
Database changed
//列出当前库里面的表；
MariaDB [bgx_edu]> show tables;
Empty set (0.00 sec)
//查询表结构
MariaDB [mysql]> desc user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
//查看创建某张表用的语句
MariaDB [mysql]> show create table mysql.slow_log\G
*************************** 1. row ***************************
Table: slow_log
Create Table: CREATE TABLE `slow_log` (
`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`user_host` mediumtext NOT NULL,
`query_time` time(6) NOT NULL,
`lock_time` time(6) NOT NULL,
`rows_sent` int(11) NOT NULL,
`rows_examined` int(11) NOT NULL,
`db` varchar(512) NOT NULL,
`last_insert_id` int(11) NOT NULL,
`insert_id` int(11) NOT NULL,
`server_id` int(10) unsigned NOT NULL,
`sql_text` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='Slow log'
1 row in set (0.00 sec)
3、数据库的增删改
在MYSQL管理软件中，可以通过SQL语句中的DML（数据操纵语言）来实现数据的操作，主要用到下面三个指令：
INSERT 数据增加
UPDATE 数据更改
DELETE 数据删除
SELECT 数据查询
1、准备操作环境的数据表
//创建后面要用到的数据库和表
MariaDB [(none)]> create database bgx;
MariaDB [(none)]> use bgx;
MariaDB [bgx]> create table t1
-> (id int,
-> name varchar(10),
-> sex enum('man','gril'),
-> age int);
//查看表结构
MariaDB [bgx]> desc t1;
+-------+--------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
| sex | enum('man','gril') | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+--------------------+------+-----+---------+-------+
2、insert
insert完整插入语法1：insert into table-name（field1,field2,field3,field4) values (values1，values2，values3，values4)；
MariaDB [bgx]> insert into t1 (id,name,sex,age) values ("1","zhanghe","man","22");
MariaDB [bgx]> select * from t1;
+------+---------+------+------+
| id | name | sex | age |
+------+---------+------+------+
| 1 | zhanghe | man | 22 |
+------+---------+------+------+
insert完整插入语法2（推荐）：insert table-name values (values1，values2，values3，values4)；
MariaDB [bgx]> insert t1 values ("2","zhangjia","gril","13");
MariaDB [bgx]> select * from t1;
+------+----------+------+------+
| id | name | sex | age |
+------+----------+------+------+
| 1 | zhanghe | man | 22 |
| 2 | zhangjia | gril | 13 |
+------+----------+------+------+
insert指定字段插入语法：insert table-name（field1,field2) values (values1，values2)；
MariaDB [bgx]> insert t1 (id,name) values ("3","zhangwei");
MariaDB [bgx]> select * from t1;
+------+----------+------+------+
| id | name | sex | age |
+------+----------+------+------+
| 1 | zhanghe | man | 22 |
| 2 | zhangjia | gril | 13 |
| 3 | zhangwei | NULL | NULL |
+------+----------+------+------+
//insert插入多条数据
MariaDB [bgx]> insert t1 (id,name) values
-> ("4","zhangsan"),
-> ("5","lisi");
MariaDB [bgx]> select * from t1;
+------+----------+------+------+
| id | name | sex | age |
+------+----------+------+------+
| 1 | zhanghe | man | 22 |
| 2 | zhangjia | gril | 13 |
| 3 | zhangwei | NULL | NULL |
| 4 | zhangsan | NULL | NULL |
| 5 | lisi | NULL | NULL |
+------+----------+------+------+
3、update
更改某个字段的套路:
先用DESC查看一下字段的名字
然后再用SELECT查看表中的内容
再然后才用UPDATE更改，注意用WHERE精神修改
最的用FLUSH进行刷新
//更改表中的某一个字段的值
MariaDB [bgx]> select * from t1;
+------+----------+------+------+
| id | name | sex | age |
+------+----------+------+------+
| 1 | zhanghe | man | 22 |
| 2 | zhangjia | gril | 13 |
| 3 | zhangwei | NULL | NULL |
| 4 | zhangsan | NULL | NULL |
| 5 | lisi | NULL | NULL |
+------+----------+------+------+
MariaDB [bgx]> update t1 set age=22 where name="zhanghe";
MariaDB [bgx]> select * from t1;
+------+----------+------+------+
| id | name | sex | age |
+------+----------+------+------+
| 1 | zhanghe | man | 22 |
| 2 | zhangjia | gril | 13 |
| 3 | zhangwei | NULL | NULL |
| 4 | zhangsan | NULL | NULL |
| 5 | lisi | NULL | NULL |
+------+----------+------+------+
//用update修改root的密码
///先找出要修改的什么字段
MariaDB [mysql]> desc mysql.user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
///然后将要修改的字段列出来
MariaDB [mysql]> select host,user,password from user;
+-----------+------+----------+
| host | user | password |
+-----------+------+----------+
| localhost | root | |
| mysql01 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| mysql01 | | |
+-----------+------+----------+
///用update进行修改密码
MariaDB [mysql]> update mysql.user set password=password("cba-123") where host='localhost' and user='root';
MariaDB [mysql]> select host,user,password from user;
+-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *D1BE934B99C7CFFE2843BDE71DB34BBB0894B2DD |
| mysql01 | root | |
| 127.0.0.1 | root | |
| ::1 | root | |
| localhost | | |
| mysql01 | |
///最后再刷新一下
MariaDB [(none)]> flush privileges;
4、delete
语法: DELETE FROM 表名 WHERE CONITION
MariaDB [bgx]> select * from t1;
+------+----------+------+------+
| id | name | sex | age |
+------+----------+------+------+
| 1 | zhanghe | man | 22 |
| 2 | zhangjia | gril | 13 |
| 3 | zhangwei | NULL | NULL |
| 4 | zhangsan | NULL | NULL |
| 5 | lisi | NULL | NULL |
+------+----------+------+------+
MariaDB [bgx]> delete from t1 where name="zhanghe";
MariaDB [bgx]> select * from t1;
+------+----------+------+------+
| id | name | sex | age |
+------+----------+------+------+
| 2 | zhangjia | gril | 13 |
| 3 | zhangwei | NULL | NULL |
| 4 | zhangsan | NULL | NULL |
| 5 | lisi | NULL | NULL |
+------+----------+------+------+
//清空表数据
MariaDB [bgx]> select * from t1;
+------+----------+------+------+
| id | name | sex | age |
+------+----------+------+------+
| 2 | zhangjia | gril | 13 |
| 3 | zhangwei | NULL | NULL |
| 4 | zhangsan | NULL | NULL |
| 5 | lisi | NULL | NULL |
+------+----------+------+------+
MariaDB [bgx]> truncate t1;
MariaDB [bgx]> select * from t1;
Empty set (0.00 sec)
