mysql必会技能-基本操作
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>神呐，请赐我力量吧
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2019-03-24 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。