首先创建一张代中文字符的表
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,'女');
====================================
查询所有字段和数据
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 '%关键字%'
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 条件进行删除， 否则将会把表的内容全部清空。
