首先創(chuàng)建一張代中文字符的表
mysql> create database xue_xiao character set utf8 collate utf8_general_ci;
use xue_xiao;
mysql> create table xue_sheng(id int, xing_ming varchar(50), fen_shu int, xing_bie char(2));
insert into xue_sheng values(1,'張三',90,'男');
insert into xue_sheng values(2,'李四',70,'男');
insert into xue_sheng values(3,'李小紅',80,'女');
====================================
查詢所有字段和數(shù)據(jù)
mysql> select * from xue_sheng;
+------+-----------+---------+----------+
| id | xing_ming | fen_shu | xing_bie |
+------+-----------+---------+----------+
| 1 | 張三 | 90 | 男 |
| 2 | 李四 | 70 | 男 |
| 3 | 李小紅 | 80 | 女 |
+------+-----------+---------+----------+
3 rows in set (0.00 sec)
指定顯示某一字段
select xing_ming from xue_sheng;
+-----------+
| xing_ming |
+-----------+
| 張三 |
| 李四 |
| 李小紅 |
+-----------+
3 rows in set (0.00 sec)
模糊查詢 like '%關(guān)鍵字%'
select * from xue_sheng where xing_ming like '李%';
+------+-----------+---------+----------+
| id | xing_ming | fen_shu | xing_bie |
+------+-----------+---------+----------+
| 2 | 李四 | 70 | 男 |
| 3 | 李小紅 | 80 | 女 |
+------+-----------+---------+----------+
2 rows in set (0.01 sec)
滿足多條件查詢
select * from xue_sheng where xing_ming like '李%' and xing_bie='女';
+------+-----------+---------+----------+
| id | xing_ming | fen_shu | xing_bie |
+------+-----------+---------+----------+
| 3 | 李小紅 | 80 | 女 |
+------+-----------+---------+----------+
1 row in set (0.00 sec)
select * from xue_sheng where xing_ming = '李四';
+------+-----------+---------+----------+
| id | xing_ming | fen_shu | xing_bie |
+------+-----------+---------+----------+
| 2 | 李四 | 70 | 男 |
+------+-----------+---------+----------+
進行排序查詢
order by 字段名 desc(降序) 或者 asc(默認升序)
mysql> select * from xue_sheng order by fen_shu desc;
+------+-----------+---------+----------+
| id | xing_ming | fen_shu | xing_bie |
+------+-----------+---------+----------+
| 1 | 張三 | 90 | 男 |
| 3 | 李小紅 | 80 | 女 |
| 2 | 李四 | 70 | 男 |
+------+-----------+---------+----------+
分頁顯示
從第1條記錄（不包括第一條記錄）后開始递沪，顯示2條記錄
limit 1,2
select * from xue_sheng limit 2, 2;
===============================================
指定id等于3的記錄進行更新
update xue_sheng set xing_bie='男' where id=3;
如果沒有 where 指定條件豺鼻，則全部進行更新
update xue_sheng set xing_bie='女';
======================================
刪除
delete from 表名
mysql> delete from xue_sheng where id=2;
不過我們一般加入 where 條件進行刪除， 否則將會把表的內(nèi)容全部清空款慨。
