001-MYSQL命令

数据库

一、命令相关

1.常见命令

mysql -uroot -p #如果刚安装好MySQL，root是没有密码的*
mysql> mysql -h192.168.206.100 -uroot -p12345678; #u与root可以不加空格

mysql> drop database db_name;    # -- 删除数据库
mysql> use db_name;              #-- 选择数据库
mysql> create table tb_name (字段名 varchar(20), 字段名 char(1));   #-- 创建数据表模板
mysql> show tables;              #-- 显示数据表
mysql> desc tb_name；            # -- 显示表结构
mysql> drop table tb_name；      #-- 删除表



#创建学生表
create table Student(
     Sno char(10) primary key,
     Sname char(20) unique,
     Ssex char(2),
     Sage smallint,
     Sdept char(20)
)；

#第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：
mysql> insert into tb_name values (value1,value2,value3,...);
#第二种形式需要指定列名及被插入的值：
mysql> insert into tb_name (column1,column2,column3,...) values (value1,value2,value3,...);


#插入数据
mysql> insert into Student values ( 20180001,张三,男,20,CS）;
mysql> insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ( 20180003,王五,男,18,MA）;
mysql> insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ( 20180004,赵六,男,20,IS）;

2.指定查询结果中的列标题

通过指定列标题（也叫列别名）可使输出结果更容易被人理解。 指定列标题时，可在列名之后使用AS子句；也可使用：列别名=<表达式>的形式指定列标题。AS子句的格式为：列名或计算表达式 [AS] 列标题
模板：

select <字符型字段> as 列标题1,<字符型字段> as 列标题2, <字符型字段> as 列标题3 from bt_name；

3. 查询经过计算的列（即表达式的值）

选择行：选择表中的部分行或全部行作为查询的结果。格式: select [all|distinct] [top n[percent]]<目标列表达式列表> from 表名

• 消除查询结果中的重复行

在select语句中使用distinct关键字可以消除结果集中的重复行,
模板：select distinct <字符型字段>[,<字符型字段>,…] from tb_name；

• 限制查询结果中的返回行数

使用top选项可限制查询结果的返回行数，即返回指定个数的记录数。其中：n是一个正整数，表示返回查询结果集的前n行；若带 percent关键字，则表示返回结果集的前n%行。

  模板：select top n from tb_name order by ..; /*查询前 n 的数据*/
  模板：select top n percent from tb_name order by ..; /*查询前 n% tb_name的数据*/

• 查询满足条件的行: 用where子句实现条件查询

通过where子句实现,该子句必须紧跟在From子句之后。格式为：select [all|distinct] [top n[percent]]<目标列表达式列表> from 表名 where <条件>；说明：在查询条件中可使用以下运算符或表达式：

 运算符                 运算符标识
 比较运算符         <=，<，=，>，>=，!=，<>，!>，!<
 范围运算符         between... and，not between... and
 列举运算符         in，not in
 模糊匹配运算符      like，not like
 空值运算符         is null，is not null
 逻辑运算符         and，or，not

• 使用比较运算符：

模板：select * from tb_name where <字符型字段> >= n ；

• 指定范围：

用于指定范围的关键字有两个：between...and和 not between...and。
格式为：
select * from tb_name where [not] between <表达式1> and <表达式2>;

其中：between关键字之后的是范围的下限（即低值）,and关键字之后的是范围的上限（即高值）。用于查找字段值在（或不在）指定范围的行。

• 使用列举:

使用in关键字可以指定一个值的集合，集合中列出所有可能的值，当表达式的值与集合中的任一元素个匹配时，即返回true，否则返回false。

模板：select * from tb_name where <字符型字段> [not] in(值1,值2,...,值n);

4.使用通配符进行模糊查询

可用like 子句进行字符串的模糊匹配查询，like子句将返回逻辑值（true或False）。
like子句的格式： select * from tb_name where <字符型字段> [not] like <匹配串>；
其含义是：查找指定字段值与匹配串相匹配的记录。匹配串中通常含有通配符%和_（下划线）。 其中: %：代表任意长度（包括0）的字符串

5.多重条件查询：使用逻辑运算符

逻辑运算符and（与：两个条件都要满足）和or（或：满足其中一个条件即可）可用来联接多个查询条件。and的优先级高于or,但若使用括号可以改变优先级。
模板：select * from tb_name where <字符型字段> = ‘volues’ and <字符型字段> > n;

6.对查询结果排序

order by子句可用于对查询结果按照一个或多个字段的值（或表达式的值）进行升序（ASC）或降序（DESC）排列，默认为升序。
格式：order by {排序表达式[ASC|DESC]}[,…n]；
其中：排序表达式既可以是单个的一个字段，也可以是由字段、函数、常量等组成的表达式，或一个正整数。
模板：

select name,age,city from staff where city = '深圳' order by age limit 10;

使用统计函数: 又称集函数，聚合函数
在对表进行检索时，经常需要对结果进行计算或统计，T-SQL提供了一些统计函数（也称集函数或聚合函数），用来增强检索功能。统计函数用于计算表中的数据，即利用这些函数对一组数据进行计算，并返回单一的值。
常用统计函数表

        函数名      功能
        AVG         求平均值
        count        求记录个数，返回int类型整数
        max          求最大值
        min           求最小值
        sum          求和

1. SUM和AVG
   功能：求指定的数值型表达式的和或平均值。
   模板：

   select avg(<字符型字段>) as 平均数,sum(<字符型字段>) as 总数 from tb_name where <字符型字段> ='字符串';
   

2. Max和Min
   功能：求指定表达式的最大值或最小值。
   模板：select max(<字符型字段>) as 最大值,min(<字符型字段>) as 最小值 from tb_name;

3. count
   该函数有两种格式：count()和count([all]|[distinct] 字段名），为避免出错，查询记录个数一般使用count()，而查询某字段有几种取值用count(distinct 字段名）。
   (1).count():
   功能：统计记录总数。
   模板：select count(<字符型字段>) as 总数 from tb_name;
   (2).count([all]|[distinct] 字段名）
   功能：统计指定字段值不为空的记录个数，字段的数据类型可以是text、image、ntext、uniqueidentifier之外的任何类型。

 模板：select count(<字符型字段>) as 总数 from tb_name;

7.对查询结果分组

​ group by子句用于将查询结果表按某一列或多列值进行分组，列值相等的为一组，每组统计出一个结果。该子句常与统计函数一起使用进行分组统计。
​ 格式为：group by 分组字段[…n] [having <条件表达式>]

1. 在使用group by子句后
   select列表中只能包含：group by子句中所指定的分组字段及统计函数。

2. having子句的用法
   having子句必须与group by 子句配合使用，用于对分组后的结果进行筛选（筛选条件中常含有统计函数）。

3. 分组查询时不含统计函数的条件
   通常使用where子句；含有统计函数的条件,则只能用having子句。
   模板：

   select <字符型字段>,count(<字符型字段>) as 列标题 from tb_name where <字符型字段>='字符串' group by <字符型字段>；
   

4. 修改数据(Update）

   Update 语句用于修改表中的数据。

     格式：update tb_name set 列名称 = 新值 where 列名称 = 某值；
   

5. 删除数据(Delete)

   删除单行
   格式：delete from tb_name where 列名称 = 某值；
   删除所有行
   可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：
   格式：

   delete * from tb_name   或  delete from tb_name；
   

8、MySQL – 应用

学生-课程数据库
学生表：Student（Sno，Sname，Ssex，Sage，Sdept）
课程表：Course（Cno，Cname，Cpno，Ccredit）
学生选课表：SC（Sno，Cno，Grade）
关系的主码加下划线表示。各个表中的数据示例如图所示：

 建立一个“学生”表Student：
create table Student(
  Sno char(9) peimary key, /*列级完整性约束条件，Sno是主码*/
  Sname char(20) unique, /* Sname取唯一值*/
  Ssex char(2),
  Sage smallint,
  Sdept char(20)
);
 建立一个“课程”表Course：
create table Course(
  Sno char(4) primary key, /*列级完整性约束条件，Cname不能取空值*/
  Sname char(40) not null, /*Cpno的含义是先修课*/
  Cpno char(4)
  Ccredit smallint,
  foreign key (Cpnoo) references Course(Cno) /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
);
 建立学生选课表SC：
create table SC(
  Sno char(9),
  Cno char(4),
  Grade smallint,
  primary key (Sno,Cno), /*主码由两个属性构成，必须作为表级完整性进行定义*/
  foreign key (Sno) references Student(Sno), /*表级完整性约束条件，Sno是外码，被参照表是Student*/
  foreign key (Cno) references Course(Cno)   /*表级完整性约束条件，Cno是外码，被参照表是Course */
);

增 插入数据
语法：insert into [table_name] ([column],…) values(“name”,…);
例子：

insert into student(name,age,NCEE_grade,register_date) values("刘备",20,400,"2018-9-1")；
insert into student(name,age,NCEE_grade,register_date) values("关羽",19,380,"2018-8-31");
insert into student(name,age,NCEE_grade,register_date) values("张飞",18,360,"2018-8-30");

删 删除行数据
语法 ：delete from [table_name] where [column_name] = " "
例子：

1.删除叫刘备的小朋友 delete from student where name = "刘备"；
2.删除age大于等于19的小朋友 delete from student where age>=19;
3.删除登记日期在9月之后的 delete from student where date like "2018-09%";

改 更改数据值
格式：

update [table_name] set [columns] = " ",[columns] = " " where /

例子：将刘备小朋友的高考成绩改成390

update student set NCEE_grade = 390 where name = "刘备";

原文链接：https://blog.csdn.net/caipengbenren/article/details/98388046

insert into tb_name values (values1,values2...)
insert into tb_name (column1,column2,column3...) values (value1,value2,value3...)

select * from tb_name;
select column1,column2,column3 from tb_name;
select count(*) from tb_name;  返回行数
select distinct column1,column2 from tb_name;
select column1,column2 from tb_name order by column2 DESC;
select id as ID号 from tb_name;
select distinct id from tb_name;
select * from student order by age desc, math asc;
查询所有数据,在年龄降序排序的基础上，如果年龄相同再以数学成绩升序排序
SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录



实验如下:
1.left join(左联接)

sql语句如下: 
SELECT * FROM a
LEFT JOIN  b 
ON a.aID =b.bID

结果如下:
aID        aNum                   bID           bName
1            a20050111         1               2006032401
2            a20050112         2              2006032402
3            a20050113         3              2006032403
4            a20050114         4              2006032404
5            a20050115         NULL       NULL
（所影响的行数为 5 行）

结果说明:
        left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.

2.right join(右联接)

sql语句如下: 
SELECT  * FROM a
RIGHT JOING b 
ON a.aID = b.bID

结果如下:
aID        aNum                   bID           bName
1            a20050111         1               2006032401
2            a20050112         2              2006032402
3            a20050113         3              2006032403
4            a20050114         4              2006032404
NULL    NULL                   8              2006032408
（所影响的行数为 5 行）

结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

3.inner join(相等联接或内联接)

sql语句如下: 
SELECT * FROM  a
INNER JOIN  b
ON a.aID =b.bID

等同于以下SQL句:
SELECT * 
FROM a,b
WHERE a.aID = b.bID

结果如下:
aID        aNum                   bID           bName
1            a20050111         1               2006032401
2            a20050112         2              2006032402
3            a20050113         3              2006032403
4            a20050114         4              2006032404

结果说明:
        很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
LEFT JOIN操作用于在任何的 FROM 子句中，

select a.*, b.* from a left join b on a.id = b.id and a.type = 1;