查看引擎:引擎(engine)
show engines;
增删改(重点)
添加数据:
第一种:
mysql> insert into user1(id,name,money,province,age,sex) values(
-> 8,'叶梦迪',888,'北京',21,1);
Query OK, 1 row affected (0.00 sec)
第二种:
mysql> insert into user1 values(17,'张良',565,'沧州',34,1),
-> (18,'刘邦',364,'配线',32,1),
-> (20,'秦始皇',456,'西安',34,1);
Query OK, 3 rows affected (0.01 sec)
第三种:插入多条数据
mysql> insert into user1(id,name,money,province,age,sex) values(
-> 9,'张三',456,'上海',32,1),
-> (15,'李四',678,'杭州',23,2),
-> (16,'网二',866,'台湾',32,1);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
注意:通常使用第一种插入方式,切记字段和值一定要保持一致
在可视化界面插入数据应注意:
1.如果是自增的,不需要写
2.如果你的字段设置的不为空,你插入数据必须有值
3.有默认值的字段可以不用写
删除数据:
注意:在删除数据的时候一定要加上where,不然会把整个表删除
语法:delete from 表名 where 字段=值
mysql> delete from user where id=10;
Query OK, 1 row affected (0.00 sec)
mysql> delete from hahaha where age=4 or age=7;
Query OK, 2 rows affected (0.00 sec)
mysql> select *from hahaha;
+----+------+------+------+
| id | name | age | sex |
+----+------+------+------+
| 1 | er | 21 | 1 |
| 2 | ty | 32 | 1 |
| 3 | er | 21 | 1 |
| 4 | esdg | 45 | 1 |
| 8 | fdf | 43 | 1 |
| 10 | NULL | NULL | 1 |
+----+------+------+------+
mysql> delete from hahaha where name='er' and id=3;
Query OK, 1 row affected (0.00 sec)
mysql> select *from hahaha;
+----+------+------+------+
| id | name | age | sex |
+----+------+------+------+
| 1 | er | 21 | 1 |
| 2 | ty | 32 | 1 |
| 4 | esdg | 45 | 1 |
| 8 | fdf | 43 | 1 |
| 10 | NULL | NULL | 1 |
+----+------+------+------+
修改数据:
语法:update 表名 set 字段1=值1,字段2=值2,字段3=值3 where 字段=值
注意:在做修改的时候 一定要加上where
mysql> update user set name='tt' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
修改多个字段:切记字段之间一定要用逗号隔开
mysql> update user set name='hhh',age=34,sex=2 where id=10;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
段值可进行计算:
mysql> update user set age=age+12 where name='esdg';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
查询(重点)
第一种 :
select * from 表名
第二种:
语法:select 字段1,字段2,字段3,...from 表名
mysql> select name,age from hahaha;
+------+------+
| name | age |
+------+------+
| er | 21 |
| ty | 32 |
| esdg | 45 |
| fdf | 43 |
| NULL | NULL |
+------+------+
5 rows in set (0.00 sec)
第三种:去除重复值
语法:select distinct 字段 from 表名
mysql> select distinct name from user;
第四种:配合where来使用
mysql> select *from user where age<24;
+----+------+-------+----------+-----+-----+
| id | name | money | province | age | sex |
+----+------+-------+----------+-----+-----+
| 1 | 胡歌 | 666 | 上海 | 19 | 1 |
| 12 | 胡歌 | 666 | 上海 | 19 | 1 |
+----+------+-------+----------+-----+-----+
第五种:配合between and 来使用
mysql> select *from user where age between 19 and 20;
+----+------+-------+----------+-----+-----+
| id | name | money | province | age | sex |
+----+------+-------+----------+-----+-----+
| 1 | 胡歌 | 666 | 上海 | 19 | 1 |
| 12 | 胡歌 | 666 | 上海 | 19 | 1 |
第六种:配合or来使用
mysql> select *from user where age=19 or age=31;
+----+----------+-------+----------+-----+-----+
| id | name | money | province | age | sex |
+----+----------+-------+----------+-----+-----+
| 1 | 胡歌 | 666 | 上海 | 19 | 1 |
| 6 | 迪丽热巴 | 322 | 香港 | 31 | 2 |
| 12 | 胡歌 | 666 | 上海 | 19 | 1 |
+----+----------+-------+----------+-----+-----+
3 rows in set (0.00 sec)
第七种:
mysql> select *from hahaha where age!=32;
+----+------+------+------+
| id | name | age | sex |
+----+------+------+------+
| 1 | er | 21 | 1 |
| 4 | esdg | 45 | 1 |
| 8 | fdf | 43 | 1 |
+----+------+------+------+
3 rows in set (0.00 sec)
第八种:
mysql> select *from hahaha where age<>32;
+----+------+------+------+
| id | name | age | sex |
+----+------+------+------+
| 1 | er | 21 | 1 |
| 4 | esdg | 45 | 1 |
| 8 | fdf | 43 | 1 |
+----+------+------+------+
3 rows in set (0.00 sec)
第九种:
mysql> select *from hahaha where age in(21,43);
+----+------+------+------+
| id | name | age | sex |
+----+------+------+------+
| 1 | er | 21 | 1 |
| 8 | fdf | 43 | 1 |
+----+------+------+------+
2 rows in set (0.00 sec)
第十种:模糊查询
以什么开头
mysql> select *from user where name like '李%';
+----+--------+-------+----------+-----+-----+
| id | name | money | province | age | sex |
+----+--------+-------+----------+-----+-----+
| 5 | 李易峰 | 254 | 杭州 | 32 | 1 |
+----+--------+-------+----------+-----+-----+
1 row in set (0.00 sec)
以什么结尾:
mysql> select *from user where name like '%峰';
+----+--------+-------+----------+-----+-----+
| id | name | money | province | age | sex |
+----+--------+-------+----------+-----+-----+
| 5 | 李易峰 | 254 | 杭州 | 32 | 1 |
+----+--------+-------+----------+-----+-----+
1 row in set (0.00 sec)
mysql> select *from user where name like '%杰';
+----+------+-------+----------+-----+-----+
| id | name | money | province | age | sex |
+----+------+-------+----------+-----+-----+
| 13 | 张杰 | 435 | 深圳 | 24 | 1 |
+----+------+-------+----------+-----+-----+
中间有什么:
mysql> select *from user where name like '%易%';
+----+--------+-------+----------+-----+-----+
| id | name | money | province | age | sex |
+----+--------+-------+----------+-----+-----+
| 5 | 李易峰 | 254 | 杭州 | 32 | 1 |
+----+--------+-------+----------+-----+-----+
mysql> select *from user where name like '_易_';
+----+--------+-------+----------+-----+-----+
| id | name | money | province | age | sex |
+----+--------+-------+----------+-----+-----+
| 5 | 李易峰 | 254 | 杭州 | 32 | 1 |
+----+--------+-------+----------+-----+-----+
1 row in set (0.00 sec)
mysql> select *from user where name like '_易';
Empty set (0.00 sec)
mysql> select *from user where name like '_杰';
+----+------+-------+----------+-----+-----+
| id | name | money | province | age | sex |
+----+------+-------+----------+-----+-----+
| 13 | 张杰 | 435 | 深圳 | 24 | 1 |
+----+------+-------+----------+-----+-----+
1 row in set (0.00 sec)
mysql> select *from user where name like '__峰';
+----+--------+-------+----------+-----+-----+
| id | name | money | province | age | sex |
+----+--------+-------+----------+-----+-----+
| 5 | 李易峰 | 254 | 杭州 | 32 | 1 |
+----+--------+-------+----------+-----+-----+
1 row in set (0.00 sec)
第十一种:
mysql> select *from user where age>=19 and age<=24;
+----+------+-------+----------+-----+-----+
| id | name | money | province | age | sex |
+----+------+-------+----------+-----+-----+
| 1 | 胡歌 | 666 | 上海 | 19 | 1 |
| 2 | tt | 435 | 深圳 | 24 | 1 |
| 12 | 胡歌 | 666 | 上海 | 19 | 1 |
| 13 | 张杰 | 435 | 深圳 | 24 | 1 |
+----+------+-------+----------+-----+-----+
第十二种:排序
降序:
mysql> select *from user where age>=19 and age<=24 order by age desc;
+----+------+-------+----------+-----+-----+
| id | name | money | province | age | sex |
+----+------+-------+----------+-----+-----+
| 2 | tt | 435 | 深圳 | 24 | 1 |
| 13 | 张杰 | 435 | 深圳 | 24 | 1 |
| 1 | 胡歌 | 666 | 上海 | 19 | 1 |
| 12 | 胡歌 | 666 | 上海 | 19 | 1 |
+----+------+-------+----------+-----+-----+
mysql> select *from user where age>=19 and age<=24 order by age asc;
+----+------+-------+----------+-----+-----+
| id | name | money | province | age | sex |
+----+------+-------+----------+-----+-----+
| 1 | 胡歌 | 666 | 上海 | 19 | 1 |
| 12 | 胡歌 | 666 | 上海 | 19 | 1 |
| 2 | tt | 435 | 深圳 | 24 | 1 |
| 13 | 张杰 | 435 | 深圳 | 24 | 1 |
+----+------+-------+----------+-----+-----+
4 rows in set (0.00 sec)
第十三种:limit 2,4
注意:其中2代表从第3条数据开始,4代表4条数据一页
mysql> select *from hahaha user limit 2,3;
+----+------+------+------+
| id | name | age | sex |
+----+------+------+------+
| 4 | esdg | 45 | 1 |
| 8 | fdf | 43 | 1 |
| 10 | NULL | NULL | 1 |
+----+------+------+------+
3 rows in set (0.00 sec)
mysql> select *from hahaha user order by age asc limit 2,2;
+----+------+------+------+
| id | name | age | sex |
+----+------+------+------+
| 2 | ty | 32 | 1 |
| 8 | fdf | 43 | 1 |
+----+------+------+------+
2 rows in set (0.00 sec)
mysql> select *from hahaha user where age>21 order by age asc limit 0,2;
+----+------+------+------+
| id | name | age | sex |
+----+------+------+------+
| 2 | ty | 32 | 1 |
| 8 | fdf | 43 | 1 |
+----+------+------+------+
第十四:
分页
第一页:0,5
第二页:5,5
第三页:5,10
第n页:(n-1)*5,5
总页数:ceil(总条数/每页显示数) ceil() 向上取整数
第十五:
分组:
mysql> select count(*),name from user group by name;
+----------+----------+
| count(*) | name |
+----------+----------+
| 1 | tt |
| 1 | 叶梦迪 |
| 1 | 张一山 |
| 1 | 张杰 |
| 1 | 李易峰 |
| 2 | 胡歌 |
| 1 | 迪丽热巴 |
| 1 | 陈伟霆 |
| 1 | 靳东 |
+----------+----------+
9 rows in set (0.00 sec)
第十六:
起别名:
mysql> select count(*) as total ,name from user group by name;
+-------+----------+
| total | name |
+-------+----------+
| 1 | tt |
| 1 | 叶梦迪 |
| 1 | 张一山 |
| 1 | 张杰 |
| 1 | 李易峰 |
| 2 | 胡歌 |
| 1 | 迪丽热巴 |
| 1 | 陈伟霆 |
| 1 | 靳东 |
+-------+----------+
9 rows in set (0.00 sec)
第十七:
mysql> select count(*) as total ,name from user group by name having total>1;
+-------+------+
| total | name |
+-------+------+
| 2 | 胡歌 |
+-------+------+
1 row in set (0.00 sec)