初识mysql语句

发布时间：2018-06-11 16:55:18编辑：Run阅读（4910）

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` /*!40100 DEFAULT CHARACTER SET utf8 */|

+----------+-------------------------------------------------------------+

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` /*!40100 DEFAULT CHARACTER SET gbk */ |

+----------+-------------------------------------------------------------+

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)

关键字：