操作mysql（三）
/**
燕十八 公益PHP培训
课堂地址:YY频道88354001
学习社区:www.zixue.it
**/
mysql> #tee 这句话是把我敲的sql 及结果都输出到一个sql文件里
mysql> #便于同学们来复习
mysql> #接下来我们学习增删 改查基本语法, 得先有一张表,才行.
mysql> #先创建一张班级薪水登记表
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;
ERROR 1046 (3D000): No database selected
mysql> use gy
ERROR 1049 (42000): Unknown database 'gy'
mysql> use gy1
Database changed
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;
Query OK, 0 rows affected (0.20 sec)
mysql> show tables;
+---------------+
| Tables_in_gy1 |
+---------------+
| class |
| newstu |
+---------------+
2 rows in set (0.05 sec)
mysql> # 察看一下class表的结构
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 | |
+---------+--------------+------+-----+---------+----------------+
6 rows in set (0.19 sec)
mysql> set names gbk;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into class
-> (id,sname,gender,company,salary,fanbu)
-> values
-> (1,'张三','男','百度',8888.67,234);
Query OK, 1 row affected (0.06 sec)
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 234 |
+----+-------+--------+---------+---------+-------+
1 row in set (0.00 sec)
mysql> insert into class
-> (sname,gender,salary)
-> values
-> ('刀锋','男',8765.43);
Query OK, 1 row affected (0.00 sec)
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 234 |
| 2 | 刀锋 | 男 | | 8765.43 | 0 |
+----+-------+--------+---------+---------+-------+
2 rows in set (0.00 sec)
mysql> #id在上例中虽然没有插入,但id是自增型,因此值为2
mysql> #回头再来看,插入所有列的情况
mysql> #如果插入所有列,则可以不声明待插入的列.
mysql> #即,如果不声明插入的列,则理解为依次插入所有列
mysql> insert into
-> values
-> (3,'李四','女','新浪',5678.99,125);
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 'values
(3,'李四','女','新浪',5678.99,125)' at line 2
mysql> insert into class
-> values
-> (3,'李四','女','新浪',5678.99,125);
Query OK, 1 row affected (0.00 sec)
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 234 |
| 2 | 刀锋 | 男 | | 8765.43 | 0 |
| 3 | 李四 | 女 | 新浪 | 5678.99 | 125 |
+----+-------+--------+---------+---------+-------+
3 rows in set (0.00 sec)
mysql> # 提醒不要犯如下错误,
mysql> # 有同学认为,id是自增型的,插入时不必为其赋值
mysql> insert into class
-> values
-> ('wangwu','女','sohu',3456.78,34);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> # 这是一种常见的错误,列与值必须按顺序一一对应.
mysql> #增加多行一数据
mysql> insert into class
-> (sname,company,salary)
-> values
-> ('刘备','皇室成员',15.28),
-> ('孙策','江东集团',56.34),
-> ('曹操','宦官后裔',88,56);
ERROR 1136 (21S01): Column count doesn't match value count at row 3
mysql> select * from class;
+----+-------+--------+---------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+---------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 234 |
| 2 | 刀锋 | 男 | | 8765.43 | 0 |
| 3 | 李四 | 女 | 新浪 | 5678.99 | 125 |
+----+-------+--------+---------+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into class
-> (sname,company,salary)
-> values
-> ('刘备','皇室成员',15.28),
-> ('孙策','江东集团',56.34),
-> ('曹操','宦官后裔',88.56);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from class;
+----+-------+--------+----------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+----------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 234 |
| 2 | 刀锋 | 男 | | 8765.43 | 0 |
| 3 | 李四 | 女 | 新浪 | 5678.99 | 125 |
| 4 | 刘备 | | 皇室成员 | 15.28 | 0 |
| 5 | 孙策 | | 江东集团 | 56.34 | 0 |
| 6 | 曹操 | | 宦官后裔 | 88.56 | 0 |
+----+-------+--------+----------+---------+-------+
6 rows in set (0.00 sec)
mysql> # 练习改 update
mysql> update class
-> set fanbu = fanbu+20000;
Query OK, 6 rows affected (0.08 sec)
Rows matched: 6 Changed: 6 Warnings: 0
mysql> select * from fanbu;
ERROR 1146 (42S02): Table 'gy1.fanbu' doesn't exist
mysql> select * from class;
+----+-------+--------+----------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+----------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 20234 |
| 2 | 刀锋 | 男 | | 8765.43 | 20000 |
| 3 | 李四 | 女 | 新浪 | 5678.99 | 20125 |
| 4 | 刘备 | | 皇室成员 | 15.28 | 20000 |
| 5 | 孙策 | | 江东集团 | 56.34 | 20000 |
| 6 | 曹操 | | 宦官后裔 | 88.56 | 20000 |
+----+-------+--------+----------+---------+-------+
6 rows in set (0.00 sec)
mysql> update class
-> set fanbu=20000;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 6 Changed: 2 Warnings: 0
mysql> select * from class;
+----+-------+--------+----------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+----------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 20000 |
| 2 | 刀锋 | 男 | | 8765.43 | 20000 |
| 3 | 李四 | 女 | 新浪 | 5678.99 | 20000 |
| 4 | 刘备 | | 皇室成员 | 15.28 | 20000 |
| 5 | 孙策 | | 江东集团 | 56.34 | 20000 |
| 6 | 曹操 | | 宦官后裔 | 88.56 | 20000 |
+----+-------+--------+----------+---------+-------+
6 rows in set (0.00 sec)
mysql> update class
-> set fanbu=123
-> where id=6;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from class;
+----+-------+--------+----------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+----------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 20000 |
| 2 | 刀锋 | 男 | | 8765.43 | 20000 |
| 3 | 李四 | 女 | 新浪 | 5678.99 | 20000 |
| 4 | 刘备 | | 皇室成员 | 15.28 | 20000 |
| 5 | 孙策 | | 江东集团 | 56.34 | 20000 |
| 6 | 曹操 | | 宦官后裔 | 88.56 | 123 |
+----+-------+--------+----------+---------+-------+
6 rows in set (0.00 sec)
mysql> # where 就只能用id=N这种形式
mysql> # 错误的理解
mysql> # where expression,表达式.
mysql> # 只要where 表达式为真,则该行就发挥作用
mysql> update class set gender='男',fanbu='212' where sname='孙策';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from class;
+----+-------+--------+----------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+----------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 20000 |
| 2 | 刀锋 | 男 | | 8765.43 | 20000 |
| 3 | 李四 | 女 | 新浪 | 5678.99 | 20000 |
| 4 | 刘备 | | 皇室成员 | 15.28 | 20000 |
| 5 | 孙策 | 男 | 江东集团 | 56.34 | 212 |
| 6 | 曹操 | | 宦官后裔 | 88.56 | 123 |
+----+-------+--------+----------+---------+-------+
6 rows in set (0.03 sec)
mysql> # 改性别为男,且工资>8000的用户
mysql> update class set fanbu=159 where gender='男' and salary>8000;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2 Changed: 2 Warnings: 0
mysql> select * from class;
+----+-------+--------+----------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+----------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 159 |
| 2 | 刀锋 | 男 | | 8765.43 | 159 |
| 3 | 李四 | 女 | 新浪 | 5678.99 | 20000 |
| 4 | 刘备 | | 皇室成员 | 15.28 | 20000 |
| 5 | 孙策 | 男 | 江东集团 | 56.34 | 212 |
| 6 | 曹操 | | 宦官后裔 | 88.56 | 123 |
+----+-------+--------+----------+---------+-------+
6 rows in set (0.00 sec)
mysql> # 这句话是什么意思
mysql> select * from class where 1;
+----+-------+--------+----------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+----------+---------+-------+
| 1 | 张三 | 男 | 百度 | 8888.67 | 159 |
| 2 | 刀锋 | 男 | | 8765.43 | 159 |
| 3 | 李四 | 女 | 新浪 | 5678.99 | 20000 |
| 4 | 刘备 | | 皇室成员 | 15.28 | 20000 |
| 5 | 孙策 | 男 | 江东集团 | 56.34 | 212 |
| 6 | 曹操 | | 宦官后裔 | 88.56 | 123 |
+----+-------+--------+----------+---------+-------+
6 rows in set (0.00 sec)
mysql> #where 1中的1,恒为真,所以取了所有行.
mysql> #删除的学习
mysql> # 删除 就是指删除整行,不存在删除一行中的某几列
mysql> # 删除salary大于8800的用户
mysql> delete from class where salary>8800;
Query OK, 1 row affected (0.03 sec)
mysql> select * from class;
+----+-------+--------+----------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+----------+---------+-------+
| 2 | 刀锋 | 男 | | 8765.43 | 159 |
| 3 | 李四 | 女 | 新浪 | 5678.99 | 20000 |
| 4 | 刘备 | | 皇室成员 | 15.28 | 20000 |
| 5 | 孙策 | 男 | 江东集团 | 56.34 | 212 |
| 6 | 曹操 | | 宦官后裔 | 88.56 | 123 |
+----+-------+--------+----------+---------+-------+
5 rows in set (0.00 sec)
mysql> # 删salary大于8000,且性别为女的用户
mysql> delete from class where salary>8000 and gender='女';
Query OK, 0 rows affected (0.00 sec)
mysql> # query ok,说明执行成功了. 0 rows afftected,删除了0行.
mysql> select sname,company,salary where id=6;
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 'where id=6' at line 1
mysql> select sname,company,salary from class where id=6;
+-------+----------+--------+
| sname | company | salary |
+-------+----------+--------+
| 曹操 | 宦官后裔 | 88.56 |
+-------+----------+--------+
1 row in set (0.00 sec)
mysql> # 最暴力的查询
mysql> select * from class;
+----+-------+--------+----------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+----------+---------+-------+
| 2 | 刀锋 | 男 | | 8765.43 | 159 |
| 3 | 李四 | 女 | 新浪 | 5678.99 | 20000 |
| 4 | 刘备 | | 皇室成员 | 15.28 | 20000 |
| 5 | 孙策 | 男 | 江东集团 | 56.34 | 212 |
| 6 | 曹操 | | 宦官后裔 | 88.56 | 123 |
+----+-------+--------+----------+---------+-------+
5 rows in set (0.00 sec)
mysql> # * 代表所有列, 表名后不加where条件,则选所有行
mysql> # 因此取出所有行,所有列.
mysql> # 部分列,所有行. 取所有的人姓名和工资
mysql> select sname,salary from class;
+-------+---------+
| sname | salary |
+-------+---------+
| 刀锋 | 8765.43 |
| 李四 | 5678.99 |
| 刘备 | 15.28 |
| 孙策 | 56.34 |
| 曹操 | 88.56 |
+-------+---------+
5 rows in set (0.00 sec)
mysql> # 查id>3的人的所有列
mysql> select * from class where id>3;
+----+-------+--------+----------+--------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+----------+--------+-------+
| 4 | 刘备 | | 皇室成员 | 15.28 | 20000 |
| 5 | 孙策 | 男 | 江东集团 | 56.34 | 212 |
| 6 | 曹操 | | 宦官后裔 | 88.56 | 123 |
+----+-------+--------+----------+--------+-------+
3 rows in set (0.00 sec)
mysql> # 取部分行部分列
mysql> # 取id<5的人,取其姓名和饭补
mysql> select sname,fanbu from class where id < 5;
+-------+-------+
| sname | fanbu |
+-------+-------+
| 刀锋 | 159 |
| 李四 | 20000 |
| 刘备 | 20000 |
+-------+-------+
3 rows in set (0.00 sec)
mysql> select id,sname,gender ,company,salary,fanbu from class;
+----+-------+--------+----------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+----------+---------+-------+
| 2 | 刀锋 | 男 | | 8765.43 | 159 |
| 3 | 李四 | 女 | 新浪 | 5678.99 | 20000 |
| 4 | 刘备 | | 皇室成员 | 15.28 | 20000 |
| 5 | 孙策 | 男 | 江东集团 | 56.34 | 212 |
| 6 | 曹操 | | 宦官后裔 | 88.56 | 123 |
+----+-------+--------+----------+---------+-------+
5 rows in set (0.03 sec)
mysql> select * from class;
+----+-------+--------+----------+---------+-------+
| id | sname | gender | company | salary | fanbu |
+----+-------+--------+----------+---------+-------+
| 2 | 刀锋 | 男 | | 8765.43 | 159 |
| 3 | 李四 | 女 | 新浪 | 5678.99 | 20000 |
| 4 | 刘备 | | 皇室成员 | 15.28 | 20000 |
| 5 | 孙策 | 男 | 江东集团 | 56.34 | 212 |
| 6 | 曹操 | | 宦官后裔 | 88.56 | 123 |
+----+-------+--------+----------+---------+-------+
5 rows in set (0.00 sec)
mysql> exit
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;
往哪张表 添加行 class
添哪几列 (id,sname,gender,company,salary,fanbu)
分别添加什么值(1,'张三','男','百度',8888.67,234)
insert into class
(id,sname,gender,company,salary,fanbu)
values
(1,'张三','男','百度',8888.67,234);
insert into class
(sname,company,salary)
values
('刘备','皇室成员',15.28),
('孙策','江东集团',56.34),
('曹操','宦官后裔',88.56);
=-==update 改的要素
改哪张表 : class
改哪几列 : gender,company
改成什么值: '女','千度'
update class
set
gender='女',
company='千度';
====删除要素===
删哪张表的数据: class
删哪几行: where expression
#把salary大于8800的用户删掉
delete from class where salary>8800
====查询3要素===
查哪张表的数据? class
查哪些列? sname,salary,company
===这是最基本的增删改查===
针对单个表来说.
后面还要学什么?
如何自己建表?
如何修改表(增加减少列等)
多表联查
子查询
触发器
事务
存储过程
备份恢复
浙公网安备 33010602011771号