解决访问表 数据乱码
告诉服务器,客户端使用的是GBK编码
set name gbk;
创建表
mysql> create table class(
-> id int primary key auto_increment,
-> sname varchar(10) not null default '',
-> gender char(1) not null default '',
-> company varchar(20) not null default '',
-> salary decimal(6,2) not null default 0.00,
-> fanbu smallint not null default 0
-> )engine myisam charset utf8;
查看表结构
desc 表名;
mysql> desc class;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(10) | NO | | | |
| gender | char(1) | NO | | | |
| company | varchar(20) | NO | | | |
| salary | decimal(6,2) | NO | | 0.00 | |
| fanbu | smallint(6) | NO | | 0 | |
+---------+--------------+------+-----+---------+----------------+
增
添加数据insert into 表名
#添加数据
mysql> insert into class
->
-> (id,sname,gender,company,salary,fanbu)
-> values
-> (1,'张三','男','百度',8888.67,234);
#查询表数据
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 234 |
+----+-------+--------+---------+---------+-------+
#插入部分列
mysql> insert into class
-> (id,sname,salary,fanbu)
-> values
-> (2,'夏天',6400,407);
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 234 |
| 2 | 夏天 | | | 6400.00 | 407 |
+----+-------+--------+---------+---------+-------+
- 如果插入所有列,则可以不声明待插入的列.
- 即,如果不声明插入的列,则理解为依次插入所有列
mysql> insert into class
-> values
-> ('3','想想','女','高和',3000.35,284.42);
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 234 |
| 2 | 夏天 | | | 6400.00 | 407 |
| 3 | 想想 | 女 | 高和 | 3000.35 | 284 |
+----+-------+--------+---------+---------+-------+
添加多行数据
mysql> insert into class
-> (sname,company,salary)
-> values
-> ('王浩','中石化',8422.84),
-> ('浩哥','镇明',9433.23);
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 234 |
| 2 | 夏天 | | | 6400.00 | 407 |
| 3 | 想想 | 女 | 高和 | 3000.35 | 284 |
| 4 | 王浩 | | 中石化 | 8422.84 | 0 |
| 5 | 浩哥 | | 镇明 | 9433.23 | 0 |
+----+-------+--------+---------+---------+-------+
改
更改数据update 表名 set xxx where 表达式;
mysql> update class
-> set
-> salary = 6666,
-> fanbu =111,
-> where id=6;#表达式为真才会执行
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 234 |
| 2 | 夏天 | | | 6400.00 | 407 |
| 3 | 李四 | 男 | 腾讯 | 9999.67 | 234 |
| 4 | 想想 | 女 | 高和 | 3000.35 | 284 |
| 5 | 王浩 | | 中石化 | 8422.84 | 0 |
| 6 | 浩哥 | | 镇明 | 6666.00 | 111 |
-
where 表达式的用法
表达式为真才会执行
#改性别为男,且工资>9000的用户
mysql> update class set fanbu=1314 where gender='男' and salary>9000;
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 234 |
| 2 | 夏天 | | | 6400.00 | 407 |
| 3 | 李四 | 男 | 腾讯 | 9999.67 | 1314 |
| 4 | 想想 | 女 | 高和 | 3000.35 | 284 |
| 5 | 王浩 | | 中石化 | 8422.84 | 0 |
| 6 | 浩哥 | | 镇明 | 6666.00 | 111 |
+----+-------+--------+---------+---------+-------+
#where 1,一直为真,会把所有人fanbu改成0
mysql> update class set fanbu=0 where 1;
删
删除数据 delete from 表名 where 表达式;
- 删除就是指删除整行.
#删除salary大于九千的行
mysql> delete from class where salary>9000;
Query OK, 1 row affected (0.00 sec)
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 234 |
| 2 | 夏天 | | | 6400.00 | 407 |
| 4 | 想想 | 女 | 高和 | 3000.35 | 284 |
| 5 | 王浩 | | 中石化 | 8422.84 | 0 |
| 6 | 浩哥 | | 镇明 | 6666.00 | 111 |
+----+-------+--------+---------+---------+-------+
5 rows in set (0.00 sec)
查
select * from 表名;表示查找所有行,所有列.
" * "代表所有列,表名后不加where条件,则选所有行.
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 234 |
| 2 | 夏天 | | | 6400.00 | 407 |
| 4 | 想想 | 女 | 高和 | 3000.35 | 284 |
| 5 | 王浩 | | 中石化 | 8422.84 | 0 |
| 6 | 浩哥 | | 镇明 | 6666.00 | 111 |
+----+-------+--------+---------+---------+-------+
5 rows in set (0.00 sec)
select xxx from 表名 where表达式;
mysql> select sname,company,salary from class where id=1;
+-------+---------+---------+
| sname | company | salary |
+-------+---------+---------+
| 张三 | 百度 | 8888.67 |
+-------+---------+---------+
1 row in set (0.00 sec)
部分列,所有行--查所有人的工资
mysql> select sname,salary from class;
+-------+---------+
| sname | salary |
+-------+---------+
| 张三 | 8888.67 |
| 夏天 | 6400.00 |
| 想想 | 3000.35 |
| 王浩 | 8422.84 |
| 浩哥 | 6666.00 |
+-------+---------+
5 rows in set (0.00 sec)
