mysql5.7的安装，参考centos7安装mysql-5.7.22
使用root账号登陆
方式一: 使用账户root,密码root登陆，注意-p后面不能有空格
mysql -u root -proot
方式二:
mysql -u root -p
输入密码
查看当前登录的账号
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
更改root密码
mysql> update mysql.user set authentication_string =password('root') where User='root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 1
刷新权限，执行命令
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查看mysql字符编码
mysql> show variables like 'character%';
+--------------------------+----------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.01 sec)
mysql-数据库的操作
增: 创建名为db1且字符编码为utf8的数据库
mysql> create database db1 charset utf8;
Query OK, 1 row affected (0.00 sec)
查: 查看mysql的所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| scrapyDB |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> show create database db1;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
改: 更改db1数据库的字符编码
mysql> alter database db1 charset gbk;
Query OK, 1 row affected (0.00 sec)
在查看db1的字符串编码,已经改成gbk,mysql5.7默认字符编码为utf8
mysql> show create database db1;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
删: 删除db1数据库
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)
在查看mysql的所有数据库，db1已经被删除了
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| scrapyDB |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql-表的操作
use: 切换数据库
mysql> use db1;
Database changed
查看当前所在数据库
mysql> select database();
+------------+
| database() |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)
增: 在db1数据库中创建一个t1表，添加两个字段id指定数据类型为int(整型),name指定数据类型为char(字符串)
mysql> create table t1(id int,name char);
Query OK, 0 rows affected (0.13 sec)
查:
查看当前的t1表
mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看所有的表
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
查看表的详细信息
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
改:
修改t1表的name字段长度为20，modify修改的意思
mysql> alter table t1 modify name char(20);
Query OK, 0 rows affected (0.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
修改t1表的name字段为NAME,并修改字段长度为10
mysql> alter table t1 change name NAME char(10);
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
查看t1表的详细信息，已经修改成功
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| NAME | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
删:
删除t1表
mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)
查看db1数据库的所有表，t1表已经被删除
mysql> show tables;
Empty set (0.00 sec)
mysql-表字段(内容,记录)操作
先创建一个t1表,字段id,name(20)
mysql> create table t1(id int,name char(20));
Query OK, 0 rows affected (0.04 sec)
增: 插入多条数据，指定id,name的数据内容
mysql> insert t1(id,name) values(1,'zhangsan'),(2,'lisi'),(3,'wangwu');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
查:
从db1数据库中的t1表，查id字段
mysql> select id from db1.t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
从db1数据库中的t1表,查id,name字段
mysql> select id,name from db1.t1;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+------+----------+
3 rows in set (0.00 sec)
从db1数据库中的t1表，查询所有信息,*代表所有
mysql> select * from db1.t1;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+------+----------+
3 rows in set (0.00 sec)
改:
更改db1数据库中的t1表id=2的name为'haha'
mysql> update db1.t1 set name='haha' where id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
查看t1表中的所有信息,id=2的name已经改成'haha'
mysql> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | haha |
| 3 | wangwu |
+------+----------+
3 rows in set (0.00 sec)
更改db1数据库中的t1表的name为'如花'，注意这是更改所有的name值为'如花'(一般不用)
mysql> update db1.t1 set name='如花';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
查看t1表的所有信息,3条记录都更改了
mysql> select * from t1;
+------+--------+
| id | name |
+------+--------+
| 1 | 如花 |
| 2 | 如花 |
| 3 | 如花 |
+------+--------+
3 rows in set (0.00 sec)
删:
方法1：删除t1表中id=2的这条记录
mysql> delete from t1 where id in(2);
Query OK, 1 row affected (0.01 sec)
方法2：删除t1表中id=3的这条记录
mysql> delete from t1 where id=3;
Query OK, 1 row affected (0.00 sec)
查看t1的所有信息,已经删除了id=2,3两条记录
mysql> select * from t1;
+------+--------+
| id | name |
+------+--------+
| 1 | 如花 |
+------+--------+
1 row in set (0.00 sec)
清空t1表的所有记录
mysql> delete from t1;
Query OK, 1 row affected (0.00 sec)
查看t1表中的所有信息，已经全部被清空了
mysql> select * from t1;
Empty set (0.00 sec)
