名词介绍
1、DB(Database)
DB就是数据库,存储数据的仓库
2、DBMS(Database Management System)
数据库管理系统
管理数据库的软件,MySQL、Oracle、...
3、DBS(Database System)
数据库系统
DBS=DB(存储)+DBMS(数据库软件)+数据库应用(财务管理系统,人事管理系统)+用户
where条件字句(配合查、改、删操作)
1、语法格式
select * from 表名 where 条件;
表记录管理(续)
1、更新表记录
1、update 表名 set 字段名=值,字段名=值,...where 条件;
2、注意
update语句后如果不加where子句,表中所有记录该字段的值都会更改
2、删除表记录
1、delete from 表名 where 条件;
2、注意
delete语句后如果不加where条件子句,将会把表中所有的记录全部删除
练习：
1、查找所有蜀国人信息
2、查找女英雄信息,显示姓名、性别和国家
3、把魏延的性别改为女，国籍改为泰国
4、把id为2的记录名字改为司马懿,性别男,国家为魏国
5、删除所有泰国人
1 mysql> show databases; 2 +--------------------+ 3 | Database | 4 +--------------------+ 5 | information_schema | 6 | db1 | 7 | db2 | 8 | db3 | 9 | mysql | 10 | performance_schema | 11 | sys | 12 +--------------------+ 13 7 rows in set (1.52 sec) 14 15 mysql> use db2 16 Database changed 17 mysql> show tables; 18 Empty set (0.01 sec) 19 20 mysql> create table t1( 21 -> id int(3) zerofill, 22 -> name varchar(15), 23 -> age tinyint unsigned, 24 -> address char(10) 25 -> )default charset=utf8; 26 Query OK, 0 rows affected (1.12 sec) 27 28 mysql> insert into t1 values 29 -> (1,‘诸葛亮‘,33,‘北京‘), 30 -> (2,‘司马懿‘,34,‘上海‘)， 31 -> (3,‘赵子龙‘,30,‘北京‘); 32 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 ‘， 33 34 (3,‘赵子龙‘,30,‘北京‘)‘ at line 4 35 mysql> insert into t1 values (1,‘诸葛亮‘,33,‘北京‘), (2,‘司马懿‘,34,‘上海‘)， (3,‘赵子龙‘,30,‘北京‘); 36 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 ‘， (3,‘赵子龙‘,30,‘北京‘)‘ at line 1 37 mysql> insert into t1 values (1,‘诸葛亮‘,33,‘北京‘), (2,‘司马懿‘,34,‘上海‘)， (3,‘赵子龙‘,30,‘北京‘); 38 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 ‘， (3,‘赵子龙‘,30,‘北京‘)‘ at line 1 39 mysql> insert into t1 values (1,‘诸葛亮‘,33,‘北京‘), (2,‘司马懿‘,34,‘上海‘), (3,‘赵子龙‘,30,‘北京‘); 40 Query OK, 3 rows affected (0.34 sec) 41 Records: 3 Duplicates: 0 Warnings: 0 42 43 mysql> select * from t1 where address=‘北京‘; 44 +------+-----------+------+---------+ 45 | id | name | age | address | 46 +------+-----------+------+---------+ 47 | 001 | 诸葛亮 | 33 | 北京 | 48 | 003 | 赵子龙 | 30 | 北京 | 49 +------+-----------+------+---------+ 50 2 rows in set (0.08 sec) 51 52 mysql> select * from t1 where id=1; 53 +------+-----------+------+---------+ 54 | id | name | age | address | 55 +------+-----------+------+---------+ 56 | 001 | 诸葛亮 | 33 | 北京 | 57 +------+-----------+------+---------+ 58 1 row in set (0.03 sec) 59 60 mysql> update t1 set address=‘上海‘ 61 -> where 62 -> name=‘赵子龙‘; 63 Query OK, 1 row affected (0.06 sec) 64 Rows matched: 1 Changed: 1 Warnings: 0 65 66 mysql> select * from t1; 67 +------+-----------+------+---------+ 68 | id | name | age | address | 69 +------+-----------+------+---------+ 70 | 001 | 诸葛亮 | 33 | 北京 | 71 | 002 | 司马懿 | 34 | 上海 | 72 | 003 | 赵子龙 | 30 | 上海 | 73 +------+-----------+------+---------+ 74 3 rows in set (0.02 sec) 75 76 mysql> update t1 set name=‘张飞‘,age=88 77 -> where 78 -> id=1; 79 Query OK, 1 row affected (0.08 sec) 80 Rows matched: 1 Changed: 1 Warnings: 0 81 82 mysql> select * from t1; 83 +------+-----------+------+---------+ 84 | id | name | age | address | 85 +------+-----------+------+---------+ 86 | 001 | 张飞 | 88 | 北京 | 87 | 002 | 司马懿 | 34 | 上海 | 88 | 003 | 赵子龙 | 30 | 上海 | 89 +------+-----------+------+---------+ 90 3 rows in set (0.00 sec) 91 92 mysql> update t1 set name=‘赵云‘ 93 -> where 94 -> id=3; 95 Query OK, 1 row affected (0.07 sec) 96 Rows matched: 1 Changed: 1 Warnings: 0 97 98 mysql> select * from t1; 99 +------+-----------+------+---------+100 | id | name | age | address |101 +------+-----------+------+---------+102 | 001 | 张飞 | 88 | 北京 |103 | 002 | 司马懿 | 34 | 上海 |104 | 003 | 赵云 | 30 | 上海 |105 +------+-----------+------+---------+106 3 rows in set (0.00 sec)107 108 mysql> update t1 set name=‘赵云‘;109 Query OK, 2 rows affected (0.03 sec)110 Rows matched: 3 Changed: 2 Warnings: 0111 112 mysql> select * from t1;113 +------+--------+------+---------+114 | id | name | age | address |115 +------+--------+------+---------+116 | 001 | 赵云 | 88 | 北京 |117 | 002 | 赵云 | 34 | 上海 |118 | 003 | 赵云 | 30 | 上海 |119 +------+--------+------+---------+120 3 rows in set (0.00 sec)121 122 mysql> delete from t1 where id=2;123 Query OK, 1 row affected (0.06 sec)124 125 mysql> select * from t1;126 +------+--------+------+---------+127 | id | name | age | address |128 +------+--------+------+---------+129 | 001 | 赵云 | 88 | 北京 |130 | 003 | 赵云 | 30 | 上海 |131 +------+--------+------+---------+132 2 rows in set (0.00 sec)133 134 mysql> delete from t1;135 Query OK, 2 rows affected (0.04 sec)136 137 mysql> select * from t1;138 Empty set (0.00 sec)139 140 mysql>
View Code
1 第一步：创建库 SANGUO 2 create database SANGUO; 3 4 第二步：切换库 5 use SANGUO; 6 7 第三步：创建表sanguo 8 create table sanguo( 9 id int, 10 name char(15), 11 sex enum("男","女"), 12 country char(10) 13 )default charset=utf8; 14 15 第四步：插入记录 16 insert into sanguo values 17 (1,"曹操","男","魏国"), 18 (2,"小乔","女","吴国"), 19 (3,"诸葛亮","男","蜀国"), 20 (4,"貂蝉","女","东汉"), 21 (5,"赵子龙","男","蜀国"), 22 (6,"魏延","男","蜀国"); 23 24 ###### 25 1、创建库MoShou 26 create database MoShou; 27 28 2、切换库 29 use MoShou; 30 31 3、创建表sanguo 32 create table sanguo( 33 id int, 34 name char(20), 35 gongji int, 36 fangyu tinyint unsigned, 37 sex enum("男","女"), 38 country varchar(20) 39 )default charset=utf8; 40 41 4、在表中插入记录 42 insert into sanguo values 43 (1,‘诸葛亮‘,120,20,‘男‘,‘蜀国‘), 44 (2,‘司马懿‘,119,25,‘男‘,‘魏国‘), 45 (3,‘关羽‘,188,60,‘男‘,‘蜀国‘), 46 (4,‘赵云‘,200,66,‘男‘,‘魏国‘), 47 (5,‘孙权‘,110,20,‘男‘,‘吴国‘), 48 (6,‘貂蝉‘,666,10,‘女‘,‘魏国‘), 49 (7,null,1000,99,‘男‘,‘蜀国‘), 50 (8,‘‘,1005,88,‘女‘,‘蜀国‘); 51 52 #### 53 mysql> create database SANGUO; 54 Query OK, 1 row affected (0.20 sec) 55 56 mysql> use SANGUO; 57 Database changed 58 mysql> create table sanguo( 59 -> id int, 60 -> name char(15), 61 -> sex enum("男","女"), 62 -> country char(10) 63 -> )default charset=utf8; 64 Query OK, 0 rows affected (0.16 sec) 65 66 mysql> insert into sanguo values 67 -> (1,"曹操","男","魏国"), 68 -> (2,"小乔","女","吴国"), 69 -> (3,"诸葛亮","男","蜀国"), 70 -> (4,"貂蝉","女","东汉"), 71 -> (5,"赵子龙","男","蜀国"), 72 -> (6,"魏延","男","蜀国"); 73 Query OK, 6 rows affected (0.07 sec) 74 Records: 6 Duplicates: 0 Warnings: 0 75 76 mysql> select * from sanguo; 77 +------+-----------+------+---------+ 78 | id | name | sex | country | 79 +------+-----------+------+---------+ 80 | 1 | 曹操 | 男 | 魏国 | 81 | 2 | 小乔 | 女 | 吴国 | 82 | 3 | 诸葛亮 | 男 | 蜀国 | 83 | 4 | 貂蝉 | 女 | 东汉 | 84 | 5 | 赵子龙 | 男 | 蜀国 | 85 | 6 | 魏延 | 男 | 蜀国 | 86 +------+-----------+------+---------+ 87 6 rows in set (0.00 sec) 88 89 mysql> create database MoShou; 90 Query OK, 1 row affected (0.02 sec) 91 92 mysql> use MoShou; 93 Database changed 94 mysql> create table sanguo( 95 -> id int, 96 -> name char(20), 97 -> gongji int, 98 -> fangyu tinyint unsigned, 99 -> sex enum("男","女"),100 -> country varchar(20)101 -> )default charset=utf8;102 Query OK, 0 rows affected (0.20 sec)103 104 mysql> insert into sanguo values105 -> (1,‘诸葛亮‘,120,20,‘男‘,‘蜀国‘),106 -> (2,‘司马懿‘,119,25,‘男‘,‘魏国‘),107 -> (3,‘关羽‘,188,60,‘男‘,‘蜀国‘),108 -> (4,‘赵云‘,200,66,‘男‘,‘魏国‘),109 -> (5,‘孙权‘,110,20,‘男‘,‘吴国‘),110 -> (6,‘貂蝉‘,666,10,‘女‘,‘魏国‘),111 -> (7,null,1000,99,‘男‘,‘蜀国‘),112 -> (8,‘‘,1005,88,‘女‘,‘蜀国‘);113 Query OK, 8 rows affected (0.09 sec)114 Records: 8 Duplicates: 0 Warnings: 0115 116 mysql> select * from sanguo;117 +------+-----------+--------+--------+------+---------+118 | id | name | gongji | fangyu | sex | country |119 +------+-----------+--------+--------+------+---------+120 | 1 | 诸葛亮 | 120 | 20 | 男 | 蜀国 |121 | 2 | 司马懿 | 119 | 25 | 男 | 魏国 |122 | 3 | 关羽 | 188 | 60 | 男 | 蜀国 |123 | 4 | 赵云 | 200 | 66 | 男 | 魏国 |124 | 5 | 孙权 | 110 | 20 | 男 | 吴国 |125 | 6 | 貂蝉 | 666 | 10 | 女 | 魏国 |126 | 7 | NULL | 1000 | 99 | 男 | 蜀国 |127 | 8 | | 1005 | 88 | 女 | 蜀国 |128 +------+-----------+--------+--------+------+---------+129 8 rows in set (0.00 sec)130 131 mysql> select database();132 +------------+133 | database() |134 +------------+135 | MoShou |136 +------------+137 1 row in set (0.00 sec)138 139 mysql> use SANGUO;140 Reading table information for completion of table and column names141 You can turn off this feature to get a quicker startup with -A142 143 Database changed144 mysql> show tables;145 +------------------+146 | Tables_in_SANGUO |147 +------------------+148 | sanguo |149 +------------------+150 1 row in set (0.00 sec)151 152 mysql> select * from sanguo;153 +------+-----------+------+---------+154 | id | name | sex | country |155 +------+-----------+------+---------+156 | 1 | 曹操 | 男 | 魏国 |157 | 2 | 小乔 | 女 | 吴国 |158 | 3 | 诸葛亮 | 男 | 蜀国 |159 | 4 | 貂蝉 | 女 | 东汉 |160 | 5 | 赵子龙 | 男 | 蜀国 |161 | 6 | 魏延 | 男 | 蜀国 |162 +------+-----------+------+---------+163 6 rows in set (0.00 sec)164 165 166 mysql> use SANGUO;167 Database changed168 mysql> show tables;169 +------------------+170 | Tables_in_SANGUO |171 +------------------+172 | sanguo |173 +------------------+174 1 row in set (0.01 sec)175 176 mysql> select * from sanguo;177 +------+-----------+------+---------+178 | id | name | sex | country |179 +------+-----------+------+---------+180 | 1 | 曹操 | 男 | 魏国 |181 | 2 | 小乔 | 女 | 吴国 |182 | 3 | 诸葛亮 | 男 | 蜀国 |183 | 4 | 貂蝉 | 女 | 东汉 |184 | 5 | 赵子龙 | 男 | 蜀国 |185 | 6 | 魏延 | 男 | 蜀国 |186 +------+-----------+------+---------+187 6 rows in set (0.00 sec)188 189 mysql> select * from sanguo where country=‘蜀国‘;190 +------+-----------+------+---------+191 | id | name | sex | country |192 +------+-----------+------+---------+193 | 3 | 诸葛亮 | 男 | 蜀国 |194 | 5 | 赵子龙 | 男 | 蜀国 |195 | 6 | 魏延 | 男 | 蜀国 |196 +------+-----------+------+---------+197 3 rows in set (0.00 sec)198 199 mysql> select name,sex,country from sanguo where sex=‘女‘;200 +--------+------+---------+201 | name | sex | country |202 +--------+------+---------+203 | 小乔 | 女 | 吴国 |204 | 貂蝉 | 女 | 东汉 |205 +--------+------+---------+206 2 rows in set (0.01 sec)207 208 mysql> update sanguo set 209 -> sex=‘女‘,country=‘泰国‘210 -> where 211 -> name=‘魏延‘;212 Query OK, 1 row affected (0.04 sec)213 Rows matched: 1 Changed: 1 Warnings: 0214 215 mysql> select * from sanguo;216 +------+-----------+------+---------+217 | id | name | sex | country |218 +------+-----------+------+---------+219 | 1 | 曹操 | 男 | 魏国 |220 | 2 | 小乔 | 女 | 吴国 |221 | 3 | 诸葛亮 | 男 | 蜀国 |222 | 4 | 貂蝉 | 女 | 东汉 |223 | 5 | 赵子龙 | 男 | 蜀国 |224 | 6 | 魏延 | 女 | 泰国 |225 +------+-----------+------+---------+226 6 rows in set (0.00 sec)227 228 mysql> update sanguo set229 -> name=‘司马懿‘,sex=‘男‘,country=‘魏国‘230 -> where231 -> id=2;232 Query OK, 1 row affected (0.04 sec)233 Rows matched: 1 Changed: 1 Warnings: 0234 235 mysql> select * from sanguo;236 +------+-----------+------+---------+237 | id | name | sex | country |238 +------+-----------+------+---------+239 | 1 | 曹操 | 男 | 魏国 |240 | 2 | 司马懿 | 男 | 魏国 |241 | 3 | 诸葛亮 | 男 | 蜀国 |242 | 4 | 貂蝉 | 女 | 东汉 |243 | 5 | 赵子龙 | 男 | 蜀国 |244 | 6 | 魏延 | 女 | 泰国 |245 +------+-----------+------+---------+246 6 rows in set (0.00 sec)247 248 mysql> delect from sanguo249 -> where 250 -> country=‘泰国‘;251 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 ‘delect from sanguo252 where 253 country=‘泰国‘‘ at line 1254 mysql> delect from sanguo where country=‘泰国‘;255 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 ‘delect from sanguo where country=‘泰国‘‘ at line 1256 mysql> delete from sanguo where country=‘泰国‘;257 Query OK, 1 row affected (0.02 sec)258 259 mysql> select * from sanguo;260 +------+-----------+------+---------+261 | id | name | sex | country |262 +------+-----------+------+---------+263 | 1 | 曹操 | 男 | 魏国 |264 | 2 | 司马懿 | 男 | 魏国 |265 | 3 | 诸葛亮 | 男 | 蜀国 |266 | 4 | 貂蝉 | 女 | 东汉 |267 | 5 | 赵子龙 | 男 | 蜀国 |268 +------+-----------+------+---------+269 5 rows in set (0.00 sec)270 271 mysql>
View Code
运算符操作(配合查询、修改、删除操作)
1、数值比较&字符比较
1、数值比较运算符：=、!=、>、>=、<、<=
2、字符比较运算符：=、!=
3、语法格式
查询：
select * from 表名 where 字段名 运算符 数字/字符;
修改：
update 表名 set 字段名=值,... where 字段名 运算符 数字/字符;
删除：
delete from 表名 where 字段名 运算符 数字/字符;
4、练习
1、找出攻击值高于150的英雄的名字和攻击值
2、将赵云的攻击值改为666，防御值改为88
2、逻辑比较
1、运算符:
and(多个条件同时满足)
or(多个条件有一个条件满足就可以)
2、练习
1、找出攻击值大于200的蜀国英雄的名字及攻击值
2、将吴国英雄中攻击值为110的英雄的攻击值设置为100，防御值设置为60
3、查找蜀国和魏国的英雄信息
3、范围内比较
1、运算符 ：between and 、in 、not in
2、语法格式
字段名 between 值1 and 值2
字段名 in(值1,值2,...)
字段名 not in(值1,值2,...)
练习
1、查找攻击值在100-200之间的蜀国英雄信息
2、查找id在1,3,5,7中的英雄的id和姓名
3、找到蜀国和吴国以外的国家的女英雄
4、找到编号为1或3或5的蜀国英雄 和 貂蝉的编号、姓名和国家
4、匹配空、非空
1、空 ：is null
2、非空 ：is not null
3、练习
1、查找姓名为NULL的蜀国女英雄信息
2、查找姓名为 "" 的英雄的id,姓名和国家
4、注意
1、null ：空值,必须用is 或者 is not 去匹配
2、"" ：空字符串,用 = 或者 != 去匹配
5、模糊比较
1、语法格式
字段名 like 表达式
2、表达式
1、_ : 匹配单个字符
2、% : 匹配0到多个字符
3、练习
1、select id,name from sanguo where name like "_%_"; ## 名字中至少2个字符的
2、select id,name from sanguo where name like "%"; ## 名字不为null的所有记录
3、select id,name from sanguo where name like "___"; ##匹配名字是三个字符的记录
4、select id,name from sanguo where name like "赵%";
