初识mysql语句

发布时间:2018-06-11 16:55:18编辑:Run阅读(3860)

    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)


关键字