操作MySQL增刪改查的常用方法

下文內(nèi)容主要給大家?guī)聿僮?a title="MySQL" target="_blank" href="http://kemok4.com/mysql/">MySQL增刪改查的常用方法，所講到的知識，與書籍不同，都是億速云專業(yè)技術(shù)人員在與用戶接觸過程中，總結(jié)出來的，具有一定的經(jīng)驗分享價值，希望給廣大讀者帶來幫助。

增加記錄
  INSERT INTO 表名(字段名，字段名) VALUES (值，值)；
刪除記錄
  DELETE FROM 表名 WHERE 條件(oracal中就可以不要from)
修改記錄
  UPDATE 表名 SET 字段=值，字段=值 WHERE 條件
查詢記錄
  SELECT 字段，字段 FROM 表名 WHERE 條件

了解了一些最基本的操作命令后，我們再來學(xué)習(xí)如何創(chuàng)建一個數(shù)據(jù)庫和數(shù)據(jù)庫表。

    1、使用SHOW語句找出在云服務(wù)器上當(dāng)前存在什么數(shù)據(jù)庫：

mysql> SHOW DATABASES; 
+----------+ 
| Database | 
+----------+ 
| mysql | 
| test | 
+----------+ 
3 rows in set (0.00 sec)

    2、創(chuàng)建一個數(shù)據(jù)庫abccs 
    mysql> CREATE DATABASE abccs; 
    注意不同操作系統(tǒng)對大小寫的敏感。

    3、選擇你所創(chuàng)建的數(shù)據(jù)庫 
    mysql> USE abccs 
    Database changed 
    此時你已經(jīng)進(jìn)入你剛才所建立的數(shù)據(jù)庫abccs. 

    4、 創(chuàng)建一個數(shù)據(jù)庫表

    首先看現(xiàn)在你的數(shù)據(jù)庫中存在什么表： 
    mysql> SHOW TABLES; 
    Empty set (0.00 sec) 
    說明剛才建立的數(shù)據(jù)庫中還沒有數(shù)據(jù)庫表。下面來創(chuàng)建一個數(shù)據(jù)庫表mytable: 
    我們要建立一個你公司員工的生日表，表的內(nèi)容包含員工姓名、性別、出生日期、出生城市。

mysql> CREATE TABLE mytable (name VARCHAR(20), sex CHAR(1), 
 birth DATE, birthaddr VARCHAR(20)) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
Query OK, 0 rows affected (0.00 sec)
 

    由于name、birthadd的列值是變化的，因此選擇VARCHAR，其長度不一定是20?？梢赃x擇從1到255的任何長度，如果以后需要改變它的字長，可以使用ALTER TABLE語句。）;性別只需一個字符就可以表示："m"或"f"，因此選用CHAR(1);birth列則使用DATE數(shù)據(jù)類型。

    創(chuàng)建了一個表后，我們可以看看剛才做的結(jié)果，用SHOW TABLES顯示數(shù)據(jù)庫中有哪些表：

mysql> SHOW TABLES; 
+---------------------+ 
| Tables in menagerie | 
+---------------------+ 
| mytables | 
+---------------------+
 

    5、顯示表的結(jié)構(gòu)：

mysql> DESCRIBE mytable; 
+-------------+-------------+------+-----+---------+-------+ 
| Field | Type | Null | Key | Default | Extra | 
+-------------+-------------+------+-----+---------+-------+ 
| name | varchar(20) | YES | | NULL | | 
| sex | char(1) | YES | | NULL | | 
| birth | date | YES | | NULL | | 
| deathaddr | varchar(20) | YES | | NULL | | 
+-------------+-------------+------+-----+---------+-------+ 
4 rows in set (0.00 sec)
 

    6、 往表中加入記錄

    我們先用SELECT命令來查看表中的數(shù)據(jù)：字段帶別名查詢的時候，中文的別名要加上單引號； 
    mysql> select * from mytable; 
    Empty set (0.00 sec)
    這說明剛才創(chuàng)建的表還沒有記錄。

    加入一條新記錄：

mysql> insert into mytable 
-> value (′abccs′,′f′,′1977-07-07′,′china′); 
Query OK, 1 row affected (0.05 sec)
 

    再用上面的SELECT命令看看發(fā)生了什么變化。我們可以按此方法一條一條地將所有員工的記錄加入到表中。

    7、用文本方式將數(shù)據(jù)裝入一個數(shù)據(jù)庫表

    如果一條一條地輸入，很麻煩。我們可以用文本文件的方式將所有記錄加入你的數(shù)據(jù)庫表中。創(chuàng)建一個文本文件“mysql.txt”，每行包含一個記錄，用定位符(tab)把值分開，并且以在CREATE TABLE語句中列出的列次序給出，例如：

abccs f 1977-07-07 china 　 
mary f 1978-12-12 usa 
tom m 1970-09-02 usa

    使用下面命令將文本文件“mytable.txt”裝載到mytable表中:mysql> LOAD DATA LOCAL INFILE "mytable.txt(加路徑)" INTO TABLE pet(表名);

    再使用如下命令看看是否已將數(shù)據(jù)輸入到數(shù)據(jù)庫表中：mysql> select * from mytable;

MySQL實用命令
Wikipedia，自由的百科全書
一) 連接MYSQL：

   格式： mysql -h主機(jī)地址 -u用戶名 －p用戶密碼

1、例1：連接到本機(jī)上的MYSQL

   首先在打開DOS窗口，然后進(jìn)入mysql安裝目錄下的bin目錄下，例如： D:\mysql\bin，再鍵入命令mysql -uroot -p，回車后提示你輸密碼，如果剛安裝好MYSQL，超級用戶root是沒有密碼的，故直接回車即可進(jìn)入到MYSQL中了，MYSQL的提示符是：mysql> 
2、例2：連接到遠(yuǎn)程主機(jī)上的MYSQL

   假設(shè)遠(yuǎn)程主機(jī)的IP為：10.0.0.1，用戶名為root,密碼為123。則鍵入以下命令：
   mysql -h30.0.0.1 -uroot -p123 
  （注：u與root可以不用加空格，其它也一樣） 
3、退出MYSQL命令

   exit （回車）

(二) 修改密碼：

   格式：mysqladmin -u用戶名 -p舊密碼 password 新密碼 
1、例1：給root加個密碼123。首先在DOS下進(jìn)入目錄C:\mysql\bin，然后鍵入以下命令：

   mysqladmin -uroot -password 123 
   注：因為開始時root沒有密碼，所以-p舊密碼一項就可以省略了。 
2、例2：再將root的密碼改為456

   mysqladmin -uroot -pab12 password 456 
(三) 增加新用戶：（注意：和上面不同，下面的因為是MYSQL環(huán)境中的命令，所以后面都帶一個分號作為命令結(jié)束符）

   格式：grant select on 數(shù)據(jù)庫.* to 用戶名@登錄主機(jī) identified by "密碼" 
   例1、增加一個用戶test1密碼為abc，讓他可以在任何主機(jī)上登錄，并對所有數(shù)據(jù)庫有查詢、插入、修改、刪除的權(quán)限。首先用以root用戶連入MYSQL，然后鍵入以下命令： 
   grant select,insert,update,delete on *.* to test1@"%" Identified by "abc";

   但例1增加的用戶是十分危險的，你想如某個人知道test1的密碼，那么他就可以在internet上的任何一臺電腦上登錄你的mysql數(shù)據(jù)庫并對你的數(shù)據(jù)可以為所欲為了，解決辦法見例2。 
   例2、增加一個用戶test2密碼為abc,讓他只可以在localhost上登錄，并可以對數(shù)據(jù)庫mydb進(jìn)行查詢、插入、修改、刪除的操作（localhost指本地主機(jī)，即MYSQL數(shù)據(jù)庫所在的那臺主機(jī)），這樣用戶即使用知道test2的密碼，他也無法從internet上直接訪問數(shù)據(jù)庫，只能通過MYSQL主機(jī)上的web頁來訪問了。 
   grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc"; 
   如果你不想test2有密碼，可以再打一個命令將密碼消掉。 
   grant select,insert,update,delete on mydb.* to test2@localhost identified by ""; 
(四) 顯示命令

1、顯示數(shù)據(jù)庫列表：

   show databases; 
   剛開始時才兩個數(shù)據(jù)庫：mysql和test。mysql庫很重要它里面有MYSQL的系統(tǒng)信息，我們改密碼和新增用戶，實際上就是用這個庫進(jìn)行操作。 
2、顯示庫中的數(shù)據(jù)表：

   use mysql； //打開庫
   show tables; 
3、顯示數(shù)據(jù)表的結(jié)構(gòu)：

   describe 表名; 
4、建庫：

   create database 庫名; 
5、建表：

   use 庫名； 
   create table 表名 (字段設(shè)定列表)； 
6、刪庫和刪表:

   drop database 庫名; 
   drop table 表名； 
7、將表中記錄清空：

   delete from 表名; 
8、顯示表中的記錄：

   select * from 表名;

#################################

查看mysql的字符集：
mysql> show variables like "character\_set\_%";
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| character_set_client     | gbk    |
| character_set_connection | gbk    |
| character_set_database   | gbk    |
| character_set_filesystem | binary |
| character_set_results    | gbk    |
| character_set_server     | gbk    |
| character_set_system     | utf8   |
+--------------------------+--------+ 
character_set_system 總是utf-8

這5個最好總是保持一致，要不就和亂碼玩爽吧:
character_set_client    
character_set_connection
character_set_database  
character_set_results   
character_set_server  

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%<???>
2008-09-12
如何啟動/停止/重啟MySQL
一、啟動方式

D:\MySQL\MySQL Server 5.0\bin>net stop mysql
MySQL 服務(wù)正在停止.
MySQL 服務(wù)已成功停止。

1、使用 service 啟動：service mysqld start

2、使用 mysqld 腳本啟動：/etc/inint.d/mysqld start

3、使用 safe_mysqld 啟動：safe_mysqld&

二、停止 
停止：
D:\MySQL\MySQL Server 5.0\bin>net stop mysql
MySQL 服務(wù)正在停止.
MySQL 服務(wù)已成功停止。

1、使用 service 啟動：service mysqld stop

2、使用 mysqld 腳本啟動：/etc/inint.d/mysqld stop

3、mysqladmin shutdown

三、重啟

1、使用 service 啟動：service mysqld restart

2、使用 mysqld  腳本啟動：/etc/inint.d/mysqld restart

一 . 安裝與配置MYSQL
二 . 常用mysql命令行命令
1 .mysql的啟動與停止
啟動MYSQL服務(wù) net start mysql
停止MYSQL服務(wù) net stop mysql
2 . netstat –na | findstr 3306 查看被監(jiān)聽的端口 , findstr用于查找后面的端口是否存在
3 . 在命令行中登陸MYSQL控制臺 , 即使用 MYSQL COMMEND LINE TOOL
     語法格式 mysql –user=root –password=123456 db_name
       或 mysql –uroot –p123456 db_name
4 . 進(jìn)入MYSQL命令行工具后 , 使用status; 或\s 查看運(yùn)行環(huán)境信息
5 . 切換連接數(shù)據(jù)庫的語法 : use new_dbname;

  
6 . 顯示所有數(shù)據(jù)庫 : show databases;

7 . 顯示數(shù)據(jù)庫中的所有表 : show tables;

8 . 顯示某個表創(chuàng)建時的全部信息 : show create table table_name;

9 . 查看表的具體屬性信息及表中各字段的描述
   Describe table_name; 縮寫形式 : desc table_name;
三 。 MySql中的SQL語句
1 . 數(shù)據(jù)庫創(chuàng)建 : Create database db_name;
數(shù)據(jù)庫刪除 : Drop database db_name; 刪除時可先判斷是否存在，寫成 : drop database if exits db_name
  
2 . 建表 : 創(chuàng)建數(shù)據(jù)表的語法 : create table table_name (字段1 數(shù)據(jù)類型 , 字段2 數(shù)據(jù)類型);
   例 : create table mytable (id int , username char(20));
   刪表 : drop table table_name; 例 : drop table mytable;

8 . 添加數(shù)據(jù) : Insert into 表名 [(字段1 , 字段2 , ….)] values (值1 , 值2 , …..); 
如果向表中的每個字段都插入一個值,那么前面 [ ] 括號內(nèi)字段名可寫也可不寫
   例 : insert into mytable (id,username) values (1,’zhangsan’);

9 . 查詢 : 查詢所有數(shù)據(jù) : select * from table_name; 
查詢指定字段的數(shù)據(jù) : select 字段1 , 字段2 from table_name;
例 : select id,username from mytable where id=1 order by desc;多表查詢語句------------參照第17條實例

10 . 更新指定數(shù)據(jù) , 更新某一個字段的數(shù)據(jù)（注意，不是更新字段的名字）
Update table_name set 字段名=’新值’ [, 字段2 =’新值’ , …..][where id=id_num] [order by 字段 順序]
例 : update mytable set username=’lisi’ where id=1;
Order語句是查詢的順序 , 如 : order by id desc(或asc) , 順序有兩種 : desc倒序(100—1,即從最新數(shù)據(jù)往后查詢),asc(從1-100)，Where和order語句也可用于查詢select 與刪除delete

11 . 刪除表中的信息 : 
     刪除整個表中的信息 : delete from table_name;
   刪除表中指定條件的語句 : delete from table_name where 條件語句 ; 條件語句如 : id=3;
  
12 . 創(chuàng)建數(shù)據(jù)庫用戶
一次可以創(chuàng)建多個數(shù)據(jù)庫用戶如：
CREATE USER username1 identified BY ‘password’ , username2 IDENTIFIED BY ‘password’….

13 . 用戶的權(quán)限控制：grant
   庫，表級的權(quán)限控制 : 將某個庫中的某個表的控制權(quán)賦予某個用戶
   Grant all ON db_name.table_name TO user_name [ indentified by ‘password’ ];

14 . 表結(jié)構(gòu)的修改
（1）增加一個字段格式：
alter table table_name add column (字段名 字段類型); ----此方法帶括號
（2）指定字段插入的位置：
alter table table_name add column 字段名 字段類型 after 某字段；
刪除一個字段：
alter table table_name drop字段名; 
（3）修改字段名稱/類型
alter table table_name change 舊字段名 新字段名 新字段的類型;
（4）改表的名字
alter table table_name rename to new_table_name; 
（5）一次性清空表中的所有數(shù)據(jù) 
truncate table table_name; 此方法也會使表中的取號器(ID)從1開始

15 . 增加主鍵，外鍵，約束，索引。。。。(使用方法見17實例)
① 約束（主鍵Primary key、唯一性Unique、非空Not Null）
② 自動增張 auto_increment
③外鍵Foreign key-----與reference table_name(col_name列名)配合使用，建表時單獨(dú)使用
④ 刪除多個表中有關(guān)聯(lián)的數(shù)據(jù)----設(shè)置foreign key 為set null ---具體設(shè)置參考幫助文檔

16 . 查看數(shù)據(jù)庫當(dāng)前引擎
   SHOW CREATE TABLE table_name;
   修改數(shù)據(jù)庫引擎
   ALTER TABLE table_name ENGINE=MyISAM | InnoDB;

17 . SQL語句運(yùn)用實例:
--1 建users表
create table users (id int primary key auto_increment,nikename varchar(20) not null unique,password varchar(100) not null,address varchar(200), reg_date timestamp not null default CURRENT_TIMESTAMP);

--2 建articles表,在建表時設(shè)置外鍵
create table articles (id int primary key auto_increment,content longtext not null,userid int,constraint foreign key (userid) references users(id) on delete set null);

-----------------------------------------------------------------------
--2.1 建articles表,建表時不設(shè)置外鍵
   create table articles (id int primary key auto_increment,content longtext not null,userid int);
--2.2 給articles表設(shè)置外鍵
   alter table articles add constraint foreign key (userid) references users(id) on delete set null;
------------------------------------------------------------------------

--3. 向users表中插入數(shù)據(jù),同時插入多條
insert into users (id,nikename,password,address) values (1,'lyh3','1234',null),(10,'lyh42','4321','湖北武漢'),(null,'lyh533','5678','北京海淀');

--4. 向article中插入三條數(shù)據(jù)
insert into articles (id,content,userid) values (2,'hahahahahaha',11),(null,'xixixixixix',10),(13,'aiaiaiaiaiaiaiaiaiaiaiaia',1),(14,'hohoahaoaoooooooooo',10);

--5. 進(jìn)行多表查詢，選擇users表中ID=10的用戶發(fā)布的所有留言及該用戶的所有信息
select articles.id,articles.content,users.* from users,articles where users.id=10 and articles.userid=users.id order by articles.id desc;

--6. 查看數(shù)據(jù)庫引擎類型
show create table users;

--7. 修改數(shù)據(jù)庫引擎類型
alter table users engine=MyISAM; ---因為users表中ID被設(shè)置成外鍵，執(zhí)行此句會出錯

--8. 同表查詢,已知一個條件的情況下.查詢ID號大于用戶lyh3的ID號的所有用戶
select a.id,a.nikename,a.address from users a,users b where b.nikename='lyh3' and a.id>b.id;
------也可寫成
select id,nikename,address from users where id>(select id from users where nikename='lyh3');

9. 顯示年齡比領(lǐng)導(dǎo)還大的員工：
select a.name from users a,users b where a.managerid=b.id and a.age>b.age;

查詢編號為2的發(fā)帖人: 先查articles表,得到發(fā)帖人的編號,再根據(jù)編號查users得到的用戶名。
接著用關(guān)聯(lián)查詢.
select * from articles,users得到笛卡兒積,再加order by articles.id以便觀察
  
使用select * from articles,users where articles.id=2 篩選出2號帖子與每個用戶的組合記錄

再使用select * from articles,users where articles.id=2 and articles.userid=users.id選出users.id等于2號帖的發(fā)帖人id的記錄.
  
只取用戶名:select user where user.id=(select userid from articles where article.id =2)

找出年齡比小王還大的人:假設(shè)小王是28歲,先想找出年齡大于28的人 
select * from users where age>(select age from users where name='xiaowang');
*****要查詢的記錄需要參照表里面的其他記錄:
select a.name from users a,users b where b.name='xiaowang' and a.age>b.age

表里的每個用戶都想pk一下.select a.nickname,b.nickname from users a,users b where a.id>b.id ;

更保險的語句:select a.nickname,b.nickname from (select * from users order by id) a,(se
lect * from users order by id) b where a.id>b.id ;

再查詢某個人發(fā)的所有帖子.
select b.* from articles a , articles b where a.id=2 and a.userid=b.userid

說明: 表之間存在著關(guān)系，ER概念的解釋，用access中的示例數(shù)據(jù)庫演示表之間的關(guān)系.只有innodb引擎才支持foreign key，mysql的任何引擎目前都不支持check約束。
四、字符集出現(xiàn)錯誤解決辦法
出現(xiàn)的問題：
mysql> update users
-> set username='關(guān)羽'
-> where userid=2;
ERROR 1366 (HY000): Incorrect string value: '\xB9\xD8\xD3\xF0' for column 'usern
ame' at row 1
向表中插入中文字符時，出現(xiàn)錯誤。

mysql> select * from users;
+--------+----------+
| userid | username |
+--------+----------+
| 2 | ???? |
| 3 | ???? |
| 4 | ?í?ù |
+--------+----------+
3 rows in set (0.00 sec)
表中的中文字符位亂碼。
解決辦法：
使用命令：
mysql> status;
--------------
mysql Ver 14.12 Distrib 5.0.45, for Win32 (ia32)

Connection id: 8
Current database: test
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 7 hours 39 min 19 sec
Threads: 2 Questions: 174 Slow queries: 0 Opens: 57 Flush tables: 1 Open ta
bles: 1 Queries per second avg: 0.006
--------------
查看mysql發(fā)現(xiàn)Server characterset，Db characterset的字符集設(shè)成了latin1，所以出現(xiàn)中文亂碼。

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

更改表的字符集。
mysql> alter table users character set GBK;
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0

查看表的結(jié)構(gòu)：
mysql> show create users;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'users
' at line 1
mysql> show create table users;
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`userid` int(11) default NULL,
`username` char(20) character set latin1 default NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> desc users;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| userid | int(11) | YES | | NULL | |
| username | char(20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)

這時向表中插入中文然后有錯誤。
mysql> insert into users values(88,'中文');
ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column 'usern
ame' at row 1
mysql> insert into users values(88,'中文');
ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column 'usern
ame' at row 1

還要更改users表的username的字符集。
mysql> alter table users modify username char(20) character set gbk;
ERROR 1366 (HY000): Incorrect string value: '\xC0\xEE\xCB\xC4' for column 'usern
ame' at row 1
mysql> alter table users modify username char(20) character set gbk;
ERROR 1366 (HY000): Incorrect string value: '\xC0\xEE\xCB\xC4' for column 'usern
ame' at row 1

因為表中已經(jīng)有數(shù)據(jù)，所以更改username字符集的操作沒有成***
清空users表中的數(shù)據(jù)
mysql> truncate table users;
Query OK, 3 rows affected (0.01 sec)

從新更改user表中username的字符集
mysql> alter table users modify username char(20) character set gbk;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

這時再插入中文字符，插入成***。
mysql> insert into users values(88,'中文');
Query OK, 1 row affected (0.01 sec)

mysql> select * from users;
+--------+----------+
| userid | username |
+--------+----------+
| 88 | 中文 |
+--------+----------+
1 row in set (0.00 sec)
mysql>

對于以上關(guān)于操作MySQL增刪改查的常用方法，如果大家還有更多需要了解的可以持續(xù)關(guān)注我們億速云的行業(yè)推新，如需獲取專業(yè)解答，可在官網(wǎng)聯(lián)系售前售后的，希望該文章可給大家?guī)硪欢ǖ闹R更新。