mysql必会技能-基本操作

数据库 专栏收录该内容
21 篇文章 0 订阅

mysql基本操作

只有简单的增删改查,大神请绕道,小白可以看看!
有不足之处,或者想要交流可以使用下面的方式联系我:
联系QQ:8042965
邮箱:8042965@qq.com

一、操作数据库

1、查

mysql> show databases; #查看mysql中存在哪些数据库

注意:格式是固定的,是show databases; 不是show database;

最后还要以;号结尾

mysql> show databases;  #查看mysql中存在哪些数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| address_book       |
| day01              |
| dedecmsv57utf8sp2  |
| demo               |
| dmxt               |
| mgeids             |
| mysql              |
| performance_schema |
| pms                |
| sqltestdb          |
| test               |
| test1              |
| testqkl            |
| workflow           |
| yuanlaizheyang     |
| zup                |
+--------------------+
17 rows in set (0.00 sec)

mysql>

2、增

增就是添加一个新的数据库呗

语法: create database [想创建的数据库名称]

mysql> create database  data1;
Query OK, 1 row affected (0.00 sec)

mysql>

3、删

语法:drop datbase [要删除的数据库的名称]

mysql> drop database data1;
Query OK, 0 rows affected (0.00 sec)

mysql>

二、操作数据表

1、使用数据库

因为只有使用了数据库才能操作到数据表,他俩的关系是:表在数据库里面,下面以默认存在的数据库mysql为例,查询里面的表

mysql> use mysql;
Database changed
mysql>
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)

2、查询数据表内容

语法:select 必须为要查询的表单的表头名可以一个也可以多个 from 要查询的表单

因为user表里面存在很多数据,所以好演示,就以user表为例

1)查询user表里面所有的内容

语法:select * from 表名;

示范语句:select * from user;

解释:

  • *号代表查询所有
  • user代表要查询的表名

合起来的意思是:我要查询user表下的所有东西

mysql>select * from user;  #查询user表里面所有的内容
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host      | User | Password                                  | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| %         | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| 127.0.0.1 | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
| 1         | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y               | Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y                   | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |               0 |                    0 |        |                       |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
3 rows in set (0.00 sec)

mysql>

解释:

像查询出来的| Host | User | Password| 都表头。

2)查询user表中的User列

语法:select User from user

示范语句:select User from user;

解释:

​ 1、User是user表里存在的表头,既自己想要查询的到的结果列

​ 2、user是要查询的表

合起来:我想要查询user表下User表头列的内容

mysql> select User from user;
+------+
| User |
+------+
| root |
| root |
| root |
+------+
3 rows in set (0.00 sec)

mysql>

3)同时查询user表中User和Password和Host列

示范语句:select User,Password,Host from user;

解释:

​ 1、 User,Password,Host是user表里存在的表头,多个要用‘’,‘’号隔开。

​ 2、user是要查询的表

合起来:我想要查询user表下User,Password,Host表头列的内容

mysql> select User,Password,Host from user;  #同时查询user表中User和Password和Host列
+------+-------------------------------------------+-----------+
| User | Password                                  | Host      |
+------+-------------------------------------------+-----------+
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | %         |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 127.0.0.1 |
| root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1         |
+------+-------------------------------------------+-----------+
3 rows in set (0.00 sec)

mysql>

3、删除表

删除表:

drop table 表名

4、查询表结构

mysql> desc user;  #查询表结构
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.02 sec)

mysql>

三、操作数据

例子:

创建学校数据库School,有学生表Student

–学生表结构:
Student(SId,Sname,Sage,Ssex)
–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

步骤:

1、创建数据库

mysql> create database School;
Query OK, 1 row affected (0.06 sec)

2、进入数据库

mysql> use School
Database changed
mysql>

3、创建表

没创建之前:

mysql> show tables;
Empty set (0.00 sec)

mysql>

创建表:

mysql> create table Student(SId varchar(10),Sname varchar(10),Sage varchar(10),Ssex varchar(10));
Query OK, 0 rows affected (0.13 sec)

mysql>

现在已经有了:

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

mysql>

查看表结构

mysql> desc Student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| SId   | varchar(10) | YES  |     | NULL    |       |
| Sname | varchar(10) | YES  |     | NULL    |       |
| Sage  | varchar(10) | YES  |     | NULL    |       |
| Ssex  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql>

查询表内容:无

mysql> select * from student;
Empty set (0.00 sec)

mysql>

1、添加表内容

语法:insert into 表 (列名,列名…) values (值,值,…)

mysql> insert into student(SId,Sname,Sage,Ssex)  values("1","zhangsan","1","2");
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+----------+------+------+
| SId  | Sname    | Sage | Ssex |
+------+----------+------+------+
| 1    | zhangsan | 1    | 2    |
+------+----------+------+------+
1 row in set (0.00 sec)

mysql>

多添加几条:

mysql> insert into student(SId,Sname,Sage,Ssex)  values("1","zhangsan","1","2");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(SId,Sname,Sage,Ssex)  values("2","lisi","2","1");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql>
mysql> insert into student(SId,Sname,Sage,Ssex)  values("3","wagwu","2","1");
Query OK, 1 row affected (0.00 sec)


mysql> select * from student;  #添加多条的结果
+------+----------+------+------+
| SId  | Sname    | Sage | Ssex |
+------+----------+------+------+
| 1    | zhangsan | 1    | 2    |
| 2    | lisi     | 2    | 1    |
| 3    | wagwu    | 2    | 1    |
+------+----------+------+------+
3 rows in set (0.00 sec)

mysql>

2、多条件查询

例1:查询Sage为2的所有数据

mysql> select * from student where Sage=2;
+------+-------+------+------+
| SId  | Sname | Sage | Ssex |
+------+-------+------+------+
| 2    | lisi  | 2    | 1    |
| 3    | wagwu | 2    | 1    |
+------+-------+------+------+
2 rows in set (0.00 sec)

mysql>

例2:查询Sname为lisi,Sage为2的所有数据

mysql> select * from student where Sname='lisi' and Sage=2 ;
+------+-------+------+------+
| SId  | Sname | Sage | Ssex |
+------+-------+------+------+
| 2    | lisi  | 2    | 1    |
+------+-------+------+------+
1 row in set (0.00 sec)

mysql>

例3:查询SId大于1的所有数据

mysql> select * from student where SId>1;
+------+-------+------+------+
| SId  | Sname | Sage | Ssex |
+------+-------+------+------+
| 2    | lisi  | 2    | 1    |
| 3    | wagwu | 2    | 1    |
+------+-------+------+------+
2 rows in set (0.00 sec)

mysql>

例4:查询SId包含1和3的所有数据

mysql> select * from student where Sid in(1,3);
+------+----------+------+------+
| SId  | Sname    | Sage | Ssex |
+------+----------+------+------+
| 1    | zhangsan | 1    | 2    |
| 3    | wagwu    | 2    | 1    |
+------+----------+------+------+
2 rows in set (0.00 sec)

mysql>

例5:查询SId不包含1和3的所有数据

mysql> select * from student where Sid not in(1,3);
+------+-------+------+------+
| SId  | Sname | Sage | Ssex |
+------+-------+------+------+
| 2    | lisi  | 2    | 1    |
+------+-------+------+------+
1 row in set (0.00 sec)

mysql>

3、修改数据

update 表 set 表头名= ‘修改的值’ where 条件

一定要where跟条件,不然会全部修改

例1:修改SId为1的Sname值为xiaolizi

mysql> update student set Sname='xiaolizi' where SId=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select * from student;
+------+----------+------+------+
| SId  | Sname    | Sage | Ssex |
+------+----------+------+------+
| 1    | xiaolizi | 1    | 2    |
| 2    | lisi     | 2    | 1    |
| 3    | wagwu    | 2    | 1    |
+------+----------+------+------+
3 rows in set (0.00 sec)

mysql>

4、删除

删除语法:

delete from 表   # 删除表里全部数据
delete from 表 where id=1 and name='zhangyanlin' # 删除ID =1 和name='zhangyanlin' 那一行数据

例子:删除SId为1的数据

注意:删除的时候也要跟条件,不然也会全部删除的。

mysql> delete from student where SId=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+-------+------+------+
| SId  | Sname | Sage | Ssex |
+------+-------+------+------+
| 2    | lisi  | 2    | 1    |
| 3    | wagwu | 2    | 1    |
+------+-------+------+------+
2 rows in set (0.00 sec)

mysql>

神呐,请赐我力量吧

  • 4
    点赞
  • 2
    评论
  • 7
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
©️2020 CSDN 皮肤主题: 程序猿惹谁了 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值