MySQL命令

创建数据库

CREATE DATABASE eesy;

使用某个数据库

USE eesy;

创建表

CREATE TABLE account (

    id INT PRIMARY KEY AUTO_INCREMENT,

    NAME VARCHAR(40),

    money FLOAT

)CHARACTER SET utf8 COLLATE utf8_general_ci;

增删改查数据

增加数据 insert into

insert into table_name values(…,…);

删除数据 delete from

delete from table_name where ……

更新数据 update……set……

update table_name set 属性名='...',…… where ……

删除数据库

drop database <数据库名>;

删除数据表

DROP TABLE worker;

查看表结构

describe

DESC table_name;

查看创建表的命令

SHOW CREATE TABLE table_name;

ALTER

当我们需要修改数据表名或者修改数据表字段时,就需要使用到 ALTER命令。

增删改表字段

使用了 ALTER 命令及 DROP 子句来删除字段

# 将 teacherInfo_table 表的 wages 字段删除

ALTER TABLE teacherInfo_table DROP wages;

使用 ADD 子句来向数据表中添加列,如下实例在表 中添加字段,并定义数据类型,字段会自动添加到数据表字段的末尾

# 在 teacherInfo 表中增加名为 wages 的字段,数据类型为 FLOAT。

ALTER TABLE teacherInfo ADD wages FLOAT;

指定新增字段的位置,可以使用MySQL提供的关键字 FIRST (设定位第一列), AFTER 字段名(设定位于某个字段之后)

ALTER TABLE testalter_tbl ADD i INT FIRST;

ALTER TABLE testalter_tbl ADD i INT AFTER c;

FIRSTAFTER 关键字可用于 ADD 与 MODIFY 子句,来修改某个字段的位置

在移动时,是必须要设置字段类型的

# 将 birthday 字段的位置改到 sex 字段的前面。

ALTER TABLE teacherInfo MODIFY birthday DATETIME AFTER NAME;

修改表字段类型及名称(modify)

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY

# 将 teacherInfo 表的 name 字段的数据类型改为 VARCHAR(30)

ALTER TABLE teacherInfo MODIFY NAME VARCHAR(30) CHARACTER SET utf8;

modify只能改字段数据类型完整约束,不能改字段名,但是change可以

# 将 num 字段改名为 t_id

ALTER TABLE teacherInfo CHANGE num t_id INT(10) NOT NULL;

修改表名

如果需要修改数据表的名称,可以在 ALTER TABLE 语句中使用 RENAME 子句来实现

# 将 teacherInfo 表改名为 teacherInfo_table

ALTER TABLE teacherInfo RENAME teacherInfo_table;

修改存储引擎

# 将 teacherInfo_table 表的存储引擎更改为 MyISAM 类型

ALTER TABLE teacherInfo_table ENGINE=MYISAM;

查看字符集

-- 创建数据库时指定字符集

create schema schema_name character set utf8 collate utf8_general_ci;


-- 创建表时指定字符集

create table table_name engine=InnoDB default charset=utf8;


-- 查看表的字符集信息

show create table table_name;


-- 修改表的默认字符集

alter table table_name default character set utf8 collate utf8_general_ci;


-- 修改字段的字符集

alter table table_name change column_name column_name varchar(255) character set utf8;

数据库

show create database eesy;

数据表

show create table t_account;

字段

show full columns from t_account;

修改字符集

数据库

ALTER DATABASE `test_db` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

# COLLATE指字符的排序规则

数据表

ALTER TABLE `test_db`.`user` CHARACTER SET = utf8mb4, COLLATE = utf8mb4_bin;

字段

alter table t_account modify username varchar(11) character set utf8;

将一个表的所有字段修改为指定字符集

alter table `table_name` convert to character set utf8 COLLATE utf8_bin;

分组 group by

聚合是从行的角度看

分组是从表的角度看

对数据进行分组统计的时候需要用到 GROUP BY 进行分组,GROUP BY 子句就像切蛋糕那样将表进行了分组。在GROUP BY 子句中指定的列称为聚合键或者分组列。group by子句根据聚合键,对相同的数据进行汇总,得到不同的分组

1. 通用语法

会先根据WHERE 子句指定的条件进行过滤,然后再进行汇总处理

同时使用GROUP BY 和 WHERE 的情况下句子的执行顺序为:FROMWHEREGROUP BYSELECT

SELECT <列名1>, <列名2>, <列名3>, ……

FROM <表名>

where ……

GROUP BY <列名1>, <列名2>, <列名3>, ……;

2. 聚合键中包含null

都归为一组中,显示为空

常见错误

1. 使用聚合函数时 SELECT 中书写多余列

使用聚合函数时,SELECT 子句中只能存在以下三种元素。

  • 常数
  • 聚合函数
  • GROUP BY 中指定的列(也指聚合键)常数就是指常量值,数字或者字符串等。聚合函数就是count、sum之类的。最容易出问题的是写上了聚合键以外的列名。

MySQL除外,写上其它列名也可以执行

2. 在GROUP BY子句中使用列的别名

SELECT 子句中的项目可以通过AS 关键字来指定别名。但是,在GROUP BY 子句中是不能使用别名的。

SELECT product_type AS pt, COUNT(*)

FROM Product

GROUP BY pt;

这个句子出错的原因是因为SQL执行顺序的原因。之前有介绍过DBMS是先执行GROUP BY 再执行 SELECT,所以在 SELECT 中设置的别名不生效

需要注意的是,虽然这样的写法在PostgreSQL和MySQL都不会发生执行错误,但是这并不是通常的使用方法

3. 在WHERE子句中错误使用聚合函数

只有SELECT子句和HAVING子句(以及ORDER BY子句)中能够使用聚合函数,因为只有group by操作之后才有分组,才能去使用聚合函数,where操作是在group by操作之前的

实例

查询每个部门有多少员工。先按部门号进行分组,然后用 COUNT()函数来计算每组的人数(id数量)。

select d_id,count(id) from yuangong group by d_id;

# 给count(id)取名为sum

select d_id,count(id) as sum from yuangong group by d_id;

查询每个部门的最高工资。先按部门号进行分组,然后用 MAX()函数来计算最大值。

select d_id,MAX(salary) from yuangong group by d_id;

计算每个部门的总工资。先按部门号进行分组,然后用 SUM()函数来求和

SELECT d_id,SUM(salary) FROM yuangong GROUP BY d_id;

having子句

如果想要从 GROUP BY 分组中进行筛选的话,不是用 WHERE 而是使用 HAVING 来进行聚合函数的筛选。

通用语法

SELECT <列名1>, <列名2>, <列名3>, ……

FROM <表名>

where ……

GROUP BY <列名1>, <列名2>, <列名3>, ……

HAVING <分组结果对应的条件>

HAVING 子句必须写在GROUP BY 子句之后,其在DBMS 内部的执行顺序也排在GROUP BY 子句之后。

HAVING和GROUP BY 类似,可以使用在HAVING里面的表达式:

  • 常数
  • 聚合函数
  • GROUP BY 指定的列名(即聚合键)

排序 ORDER BY

descend:下降;降序排列

查询 yuangong 表,按照工资从高到低的顺序排列

SELECT * FROM yuangong ORDER BY salary DESC;

合并 union

从 bumen 表和 yuangong 表中查询出部门号,然后使用 UNION 合并查询结果

SELECT d_id FROM yuangong UNION SELECT d_id FROM bumen;

like

查询家是北京市员工的姓名、年龄、家庭住址。这里使用 LIKE 关键字

SELECT name,birthday,address FROM yuangong WHERE address LIKE '北京%';

唯一性

取消唯一性

ALTER TABLE student DROP INDEX sex;


上一篇:MySQL:ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)


下一篇:数据结构与算法(1)支线任务8——Find Median from Data Stream