解決訪問表 數(shù)據(jù)亂碼
告訴服務器,客戶端使用的是GBK編碼
set name gbk;
創(chuàng)建表
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;
查看表結(jié)構(gòu)
? 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 | |
+---------+--------------+------+-----+---------+----------------+
增
添加數(shù)據(jù)insert into 表名
#添加數(shù)據(jù)
mysql> insert into class
->
-> (id,sname,gender,company,salary,fanbu)
-> values
-> (1,'張三','男','百度',8888.67,234);
#查詢表數(shù)據(jù)
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 |
+----+-------+--------+---------+---------+-------+
添加多行數(shù)據(jù)
mysql> insert into class
-> (sname,company,salary)
-> values
-> ('王浩','中石化',8422.84),
-> ('浩哥','鎮(zhèn)明',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 | 浩哥 | | 鎮(zhèn)明 | 9433.23 | 0 |
+----+-------+--------+---------+---------+-------+
改
更改數(shù)據(jù)update 表名 set xxx where 表達式;
mysql> update class
-> set
-> salary = 6666,
-> fanbu =111,
-> where id=6;#表達式為真才會執(zhí)行
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 | 浩哥 | | 鎮(zhèn)明 | 6666.00 | 111 |
-
where 表達式的用法
表達式為真才會執(zhí)行
#改性別為男,且工資>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 | 浩哥 | | 鎮(zhèn)明 | 6666.00 | 111 |
+----+-------+--------+---------+---------+-------+
#where 1,一直為真,會把所有人fanbu改成0
mysql> update class set fanbu=0 where 1;
刪
刪除數(shù)據(jù) 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 | 浩哥 | | 鎮(zhèn)明 | 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 | 浩哥 | | 鎮(zhèn)明 | 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)
