3,mysql基本SQL语句

SQL语句中的大小写问题

a.关键字、函数名、列名和索引名不区分大小写。
b.数据库名、表名、别名及视图名区分大小写。

一.基本的操作

1.库操作

mysql> show databases;  #查看所有库
mysql> use mysql;    #切换库
mysql> create database test_ku;   #创建一个库
mysql> drop database test_ku;   #删除一个库

2.表操作

mysql> show tables;  #查看当前库中的所有列表
mysql> create table class_7(id int(11),name char(10),addr char(10));   #创建一个表
mysql> desc class_7;    #查看表的结构
mysql> show create table student_info;  #查看表的属性

mysql> show create table student_info\G

mysql> alter table class_7 rename to student_info;  #更改表的名字
mysql> drop table student_info;  #删除表

3.字段操作

mysql> alter table score add id int(2) first;  #在最前面添加字段
mysql> alter table score add record int(3);  #添加字段，默认最后添加
mysql> alter table score add sex char(3) after name;  #在制定的字段后面添加字段
mysql> alter table score change record first_record int(2) default 0;  #修改字段名和属性
mysql> alter table score modify sex enum('男','女');  #修改字段属性的另一种方法，与change不同的是不能修改字段名
+--------------+-------------------+------+-----+---------+-------+
| Field        | Type              | Null | Key | Default | Extra |
+--------------+-------------------+------+-----+---------+-------+
| id           | int(2)            | YES  |     | NULL    |       |
| name         | char(4)           | YES  |     | NULL    |       |
| sex          | enum('男','女')   | YES  |     | NULL    |       |
| first_record | int(2)            | YES  |     | 0       |       |
+--------------+-------------------+------+-----+---------+-------+
mysql> alter table score drop first_record;   #删除字段

4.记录操作（增删改）

（1）增（insert）
mysql> insert into score value(1,'科比','男');  #按照默认的顺序添加
mysql> insert into score(id,name) value('2','毛线');   #也可以指定要添加的字段
mysql> insert into score value(4,'蒋大爷','男'),(5,'秦子琪','女');  #也可以一次添加多条

（2）改（update）
mysql> update score set sex = '男' where id = 2;    #按照条件修改字段

（3）删（delete）
mysql> delete from score where id = '0';  #指定条件删除
mysql>  delete from t1 where name is null;   #删除字段为空的那一行
mysql>  delete from t1;  #没有指定条件就会删除整张表

在有些时候，我们不光想要清空表中的数据，而且还想让id从0开始

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE tableName;
SET FOREIGN_KEY_CHECKS=1;

二、列表的查询方法

select * from test_info; #源数据
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  1 | 科比   | 6号楼   | 湖北罗田     |    67 |
|  2 | 毛线   | 6号楼   | 湖北孝感     |    78 |
|  3 | 黄鱼   | 5号楼   | 湖北罗田     |    75 |
|  4 | 子豪   | 6号楼   | 江苏扬州     |    45 |
|  5 | 星爷   | 5号楼   | 湖北孝感     |     1 |
|  6 | 代鹏   | 5号楼   | 江苏盐城     |    85 |
|  7 | 子栋   | 6号楼   | 湖北洪湖     |    95 |
|  8 | 周攀   | 6号楼   | 湖北武穴     |    56 |
|  9 | 大爷   | 5号楼   | 湖北通城     |    82 |
| 10 | 小鸟   | 6号楼   | 湖北襄阳     |    38 |
+----+--------+---------+--------------+-------+

（1）where

条件判断
mysql> select * from test_info where score < 60;  #查询分数低于60分
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  4 | 子豪   | 6号楼   | 江苏扬州     |    45 |
|  5 | 星爷   | 5号楼   | 湖北孝感     |     1 |
|  8 | 周攀   | 6号楼   | 湖北武穴     |    56 |
| 10 | 小鸟   | 6号楼   | 湖北襄阳     |    38 |
+----+--------+---------+--------------+-------+

mysql> select * from test_info where score  > 70 and addr = "湖北罗田";  #分数大于70，而且还是罗田的
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  3 | 黄鱼   | 5号楼   | 湖北罗田     |    75 |
+----+--------+---------+--------------+-------+
1 row in set (0.00 sec)

mysql> select * from test_info where dorm = "5号楼" or score > 90;  #分数大于90或者在5号楼住
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  3 | 黄鱼   | 5号楼   | 湖北罗田     |    75 |
|  5 | 星爷   | 5号楼   | 湖北孝感     |     1 |
|  6 | 代鹏   | 5号楼   | 江苏盐城     |    85 |
|  7 | 子栋   | 6号楼   | 湖北洪湖     |    95 |
|  9 | 大爷   | 5号楼   | 湖北通城     |    82 |
+----+--------+---------+--------------+-------+
5 rows in set (0.00 sec)

mysql> select * from test_info where score between 60 and 80;   #分数在60到80之间
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  1 | 科比   | 6号楼   | 湖北罗田     |    67 |
|  2 | 毛线   | 6号楼   | 湖北孝感     |    78 |
|  3 | 黄鱼   | 5号楼   | 湖北罗田     |    75 |
+----+--------+---------+--------------+-------+
3 rows in set (0.00 sec)
#注意between包含两边

mysql> select * from test_info where addr in ("湖北罗田","湖北孝感");   #某个字段在某一个选择之内
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  1 | 科比   | 6号楼   | 湖北罗田     |    67 |
|  2 | 毛线   | 6号楼   | 湖北孝感     |    78 |
|  3 | 黄鱼   | 5号楼   | 湖北罗田     |    75 |
|  5 | 星爷   | 5号楼   | 湖北孝感     |     1 |
+----+--------+---------+--------------+-------+
4 rows in set (0.00 sec)

mysql> select * from test_info where addr not in ("湖北罗田","湖北孝感"); #某个字段不在某一个选择之内
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  4 | 子豪   | 6号楼   | 江苏扬州     |    45 |
|  6 | 代鹏   | 5号楼   | 江苏盐城     |    85 |
|  7 | 子栋   | 6号楼   | 湖北洪湖     |    95 |
|  8 | 周攀   | 6号楼   | 湖北武穴     |    56 |
|  9 | 大爷   | 5号楼   | 湖北通城     |    82 |
| 10 | 小鸟   | 6号楼   | 湖北襄阳     |    38 |
+----+--------+---------+--------------+-------+
6 rows in set (0.00 sec)

(2)模糊查询（like）

mysql> select name from test_info where addr like '__罗田';  #查询罗田
+--------+
| name   |
+--------+
| 科比   |
| 黄鱼   |
+--------+
2 rows in set (0.00 sec)

（3）排序（order by）

mysql> select * from test_info order by score;  #按照成绩升序
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  5 | 星爷   | 5号楼   | 湖北孝感     |     1 |
| 10 | 小鸟   | 6号楼   | 湖北襄阳     |    38 |
|  4 | 子豪   | 6号楼   | 江苏扬州     |    45 |
|  8 | 周攀   | 6号楼   | 湖北武穴     |    56 |
|  1 | 科比   | 6号楼   | 湖北罗田     |    67 |
|  3 | 黄鱼   | 5号楼   | 湖北罗田     |    75 |
|  2 | 毛线   | 6号楼   | 湖北孝感     |    78 |
|  9 | 大爷   | 5号楼   | 湖北通城     |    82 |
|  6 | 代鹏   | 5号楼   | 江苏盐城     |    85 |
|  7 | 子栋   | 6号楼   | 湖北洪湖     |    95 |
+----+--------+---------+--------------+-------+
10 rows in set (0.00 sec)

mysql> select * from test_info order by score desc;  #按照成绩降序
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  7 | 子栋   | 6号楼   | 湖北洪湖     |    95 |
|  6 | 代鹏   | 5号楼   | 江苏盐城     |    85 |
|  9 | 大爷   | 5号楼   | 湖北通城     |    82 |
|  2 | 毛线   | 6号楼   | 湖北孝感     |    78 |
|  3 | 黄鱼   | 5号楼   | 湖北罗田     |    75 |
|  1 | 科比   | 6号楼   | 湖北罗田     |    67 |
|  8 | 周攀   | 6号楼   | 湖北武穴     |    56 |
|  4 | 子豪   | 6号楼   | 江苏扬州     |    45 |
| 10 | 小鸟   | 6号楼   | 湖北襄阳     |    38 |
|  5 | 星爷   | 5号楼   | 湖北孝感     |     1 |
+----+--------+---------+--------------+-------+

mysql> select * from test_info order by score desc limit 3; #取成绩的前三名
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  7 | 子栋   | 6号楼   | 湖北洪湖     |    95 |
|  6 | 代鹏   | 5号楼   | 江苏盐城     |    85 |
|  9 | 大爷   | 5号楼   | 湖北通城     |    82 |
+----+--------+---------+--------------+-------+

（4）分组（group by）

mysql> select * from test_info group by dorm;  #按照宿舍分组
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  3 | 黄鱼   | 5号楼   | 湖北罗田     |    75 |
|  1 | 科比   | 6号楼   | 湖北罗田     |    67 |
+----+--------+---------+--------------+-------+
2 rows in set (0.00 sec)

mysql> select *,group_concat(name) from test_info group by dorm;  #查看组成员
+----+--------+---------+--------------+-------+-------------------------------------------+
| id | name   | dorm    | addr         | score | group_concat(name)                        |
+----+--------+---------+--------------+-------+-------------------------------------------+
|  6 | 代鹏   | 5号楼   | 江苏盐城     |    85 | 代鹏,大爷,黄鱼,星爷                       |
|  8 | 周攀   | 6号楼   | 湖北武穴     |    56 | 周攀,子栋,科比,子豪,毛线,小鸟             |
+----+--------+---------+--------------+-------+-------------------------------------------+
2 rows in set (0.00 sec)

（5）函数

max()：最大值

mysql> select max(score) from test_info;  #查询最高分
+------------+
| max(score) |
+------------+
|         95 |
+------------+
1 row in set (0.00 sec)

mysql> select * from test_info where score = (select max(score) from test_info);  #最高分的学员信息
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  7 | 子栋   | 6号楼   | 湖北洪湖     |    95 |
+----+--------+---------+--------------+-------+
1 row in set (0.00 sec)

min()：最小值

mysql> select min(score) from test_info;  #查询最低分
+------------+
| min(score) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)


sum()：求和

mysql> select sum(score) from test_info;  #成绩总和
+------------+
| sum(score) |
+------------+
|        622 |
+------------+
1 row in set (0.00 sec)

avg()：平均值

mysql> select sum(score) from test_info;  #求平均值
+------------+
| sum(score) |
+------------+
|        622 |
+------------+
1 row in set (0.00 sec)

count()：计数

mysql> select count(dorm) from test_info;  #统计行数
+-------------+
| count(dorm) |
+-------------+
|          10 |
+-------------+
1 row in set (0.00 sec)

(6).去重

在MySQL中去重有两种方法DISTINCT和group by

mysql> select * from test_info;  #源数据
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  1 | 科比   | 6号楼   | 湖北罗田     |    67 |
|  2 | 毛线   | 6号楼   | 湖北孝感     |    78 |
|  3 | 黄鱼   | 5号楼   | 湖北罗田     |    75 |
|  4 | 子豪   | 6号楼   | 江苏扬州     |    45 |
|  5 | 星爷   | 5号楼   | 湖北孝感     |     1 |
|  6 | 代鹏   | 5号楼   | 江苏盐城     |    85 |
|  7 | 子栋   | 6号楼   | 湖北洪湖     |    95 |
|  8 | 周攀   | 6号楼   | 湖北武穴     |    56 |
|  9 | 大爷   | 5号楼   | 湖北通城     |    82 |
| 10 | 小鸟   | 6号楼   | 湖北襄阳     |    38 |
+----+--------+---------+--------------+-------+
10 rows in set (0.00 sec)

将地址栏这个字段去重
mysql> select addr from test_info group by addr;   #group by去重
+--------------+
| addr         |
+--------------+
| 江苏扬州     |
| 江苏盐城     |
| 湖北孝感     |
| 湖北武穴     |
| 湖北洪湖     |
| 湖北罗田     |
| 湖北襄阳     |
| 湖北通城     |
+--------------+
8 rows in set (0.00 sec)

mysql> select addr from test_info group by addr; #DISTINCT去重
+--------------+
| addr         |
+--------------+
| 江苏扬州     |
| 江苏盐城     |
| 湖北孝感     |
| 湖北武穴     |
| 湖北洪湖     |
| 湖北罗田     |
| 湖北襄阳     |
| 湖北通城     |
+--------------+
8 rows in set (0.00 sec)

（7）分页：limit

mysql> select * from test_info;
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  1 | 科比   | 6号楼   | 湖北罗田     |    67 |
|  2 | 毛线   | 6号楼   | 湖北孝感     |    78 |
|  3 | 黄鱼   | 5号楼   | 湖北罗田     |    75 |
|  4 | 子豪   | 6号楼   | 江苏扬州     |    45 |
|  5 | 星爷   | 5号楼   | 湖北孝感     |     1 |
|  6 | 代鹏   | 5号楼   | 江苏盐城     |    85 |
|  7 | 子栋   | 6号楼   | 湖北洪湖     |    95 |
|  8 | 周攀   | 6号楼   | 湖北武穴     |    56 |
|  9 | 大爷   | 5号楼   | 湖北通城     |    82 |
| 10 | 小鸟   | 6号楼   | 湖北襄阳     |    38 |
+----+--------+---------+--------------+-------+
10 rows in set (0.00 sec)

mysql> select * from test_info limit 0,3;
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  1 | 科比   | 6号楼   | 湖北罗田     |    67 |
|  2 | 毛线   | 6号楼   | 湖北孝感     |    78 |
|  3 | 黄鱼   | 5号楼   | 湖北罗田     |    75 |
+----+--------+---------+--------------+-------+
3 rows in set (0.00 sec)

mysql> select * from test_info limit 3,3;
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  4 | 子豪   | 6号楼   | 江苏扬州     |    45 |
|  5 | 星爷   | 5号楼   | 湖北孝感     |     1 |
|  6 | 代鹏   | 5号楼   | 江苏盐城     |    85 |
+----+--------+---------+--------------+-------+
3 rows in set (0.00 sec)

mysql> select * from test_info limit 6,4;
+----+--------+---------+--------------+-------+
| id | name   | dorm    | addr         | score |
+----+--------+---------+--------------+-------+
|  7 | 子栋   | 6号楼   | 湖北洪湖     |    95 |
|  8 | 周攀   | 6号楼   | 湖北武穴     |    56 |
|  9 | 大爷   | 5号楼   | 湖北通城     |    82 |
| 10 | 小鸟   | 6号楼   | 湖北襄阳     |    38 |
+----+--------+---------+--------------+-------+
4 rows in set (0.00 sec)

limit需要有两个参数，第一个是开始的行数，第一行是从0开始，第二个参数是显示的行数