mysql 数据库基础教程(一)

目录

1.操作数据库和表(DDL)

1.数据库

2.表

2.数据类型

3.增删改查

1.select

2.update

3.delete

4.like

5.union操作符

6.order by排序

7.group by分组

8.having

9.多表查询​​​​​​​

10.null值处理

---

数据库的CRUD操作, 储存引擎,索引,事务,锁见下一篇

1.操作数据库和表(DDL)

1.数据库

创建数据库

create database 数据库名 [DEFAULT CHARSET utf8 COLLATE utf8_general_ci];

# root用户登录时,可使用这种方式创建数据库,执行完后,再登录即可
mysqladmin -u root -p create 数据库名;

方括号中为可选内容,分别用于设置数据库的默认编码格式和数据库校对规则:

collate:  中文是核对,校验的意思, 涉及到字符串比较或排序的地方都会用到collate,影响order by语句的顺序, where条件筛选结果等

                1. utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。

                2. utf8_genera_ci不区分大小写，ci为case insensitive的缩写，即大小写不敏感。

                3. utf8_general_cs区分大小写，cs为case sensitive的缩写，即大小写敏感。

删除数据库

drop database 数据库名;

mysqladmin -u -root -p drop 数据库名;

使用数据库

use 数据库名;

显示数据库

show databases;

显示数据库中所有表

show tables;

显示表字段详情

show columns from 表名;

显示表的索引信息

show index from 表名;

显示库中表状态信息

创建时间, 使用的引擎, 索引长度等

 show table status from 数据库名 [like 'xxx'];

2.表

创建表

CREATE table 表名(
   `id` INT UNSIGNED AUTO_INCREMENT,
   `title` VARCHAR(100) NOT NULL, 
   `author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `id` )
)ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

1. NOT NULL: 该字段插入的数据不能为NULL

2. AUTO_INCREMENT: 定义列自增,一般用于主键,数值会自动加1

3. PRIMARY KEY: 定义列为主键,也可以定义多个列为主键

4. ENGINE: 设置储存引擎

5. DEFAULT CHARSET: 设置编码

6. AUTO_INCREMENT=8: 设置主键从8开始自增

删除表

drop table 表名; 

插入数据

INSERT INTO 表名 ( 字段1, 字段2,...字段n )
                       VALUES
                       ( 值1, 值2,...值n ); #基本结构
                       
                       
INSERT INTO user VALUES(null,'cc','123456',2); #需插入表中全部字段,插入主键时可以插入null或者0,他会自动添加

INSERT INTO user VALUES(null,'ee','123456',2),(null,'ff','123456',1); #批量插入

INSERT INTO user VALUES(null,'ee','123456',2,NOW()); #NOW()是一个mysql函数,该函数返回日期和时间

2.数据类型

1.数值类型

int(n)中n表示数据显示宽度,已弃用

2.日期和时间类型

 

注意: TIMESTAMP类型的字段会自动更新

3.字符串类型

注意:

1. char和varchar的区别:

        char(n) :  n是字符数,范围0-255(额外需要1-2个字节来存长度),char是定长,最多存255个字符,不足的部分会用隐藏空格填充,查找效率高; char(11)表示固定储存11个字符,超过就报错

        varchar(n):  n是字符数,但是最大的值需要通过编码计算,最多65535字节(从中还需要拿出1-2个字节来存长度),同样是65535字节,不同编码一个字符占用的字节不同,所以n能写的最大值就会有不同,varchar是不定长的实际存的多少就是多少,但查询效率低

        使用场景:   一般定长的数据选用char类型，比如身份证号，手机号，电话等，长度变化很大的可以使用varchar类型

2. BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

3. BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

3.增删改查

1.select

select 字段名1,字段名2,.... from 表名 [where 条件] [limit 返回的条数] [offset 偏移量]:

注意:

        1. limit语句表示返回的记录数

        2. offset语句设置开始查询的数据偏移量,默认情况下偏移量为0

例子:

user表数据:

select * from user limit 3; 查询结果如下:

默认偏移量为0,表示从第0行开始,向后查询3条记录

select * from user limit 3 offset 2;查询结果如下:

设置偏移量为2,表示从第2行开始,向后查询3条记录

select * from user limit 2,4; 查询结果如下:

从第2行开始,向后查询4条记录

where子句

        用于指定条件,可以使用and或者or指定一个或多个条件

select * from user where tid <> 3; #查询tid不等于3的所有数据

select * from user where gh in (200,300); #查询gh = 200或者300的所有数据

注意:

        1. 数据库中常用的是where关键字，用于在初始表中筛选查询。它是一个约束声明，用于约束数据，在返回结果集之前起作用。

        2. group by: 对select查询出来的结果集按照某个字段或者表达式进行分组，获得一组组的集合，然后从每组中取出一个指定字段或者表达式的值。

        3. having：用于对where和group by查询出来的分组经行过滤，查出满足条件的分组结果。它是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作。

        4. 执行顺序: select –>where –> group by–> having–>order by

        group by, having, order by后面会说明

2.update

UPDATE 表名 SET 字段1=新值, 字段2=新值 [WHERE条件];

 注意:

update students set age = age + 1; #将所有人的年龄加1

#使用函数,把表中user_name字段值中'aa'替换为'bb',条件是id = 1
UPDATE user SET user_name  = REPLACE(user_name , 'aa', 'bb') where id = 1;

3.delete

DELETE FROM 表名 [WHERE条件];

注意: delete,drop,truncate都有删除表的作用,区别在于:

       1. delete 和 truncate 都是删除表数据,但truncate可用于清空当前表主键增长值信息,drop 连表数据和表结构一起删除

       2. delete 是 DML 语句，操作完以后可以通过事务回滚，truncate 和 drop 是 DDL  语句，操作完马上生效，不能回滚

       3. 执行的速度上，drop>truncate>delete

4.like

SELECT 字段1, 字段2,...字段n FROM 表名
	WHERE 字段1 LIKE '%ab_' [and/or 字段2='a'];

使用百分号 %字符来表示任意字符,如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

使用下划线_表示一个字符

SELECT * FROM object WHERE name LIKE 'java%'; #查询name字段以java开头的所有数据
SELECT * FROM object WHERE name LIKE '_a_'; #查询name字段是3位,并且中间是a的所有数据

5.union操作符

连接两个或以上select结果集,然后将他们组合成一个结果集,默认会删除重复的数据

select ... 表1 [where条件]
	union [all | distinct]
select ... 表2 [where条件]; #可选all

aa表数据:

bb表数据:

select * from aa union select * from bb 结果如下:

注意:

        1. 如果两个select查询结果列数不一样,使用union操作符会报错

        2. union的结果集列名与第一个select保持一致

select username,gh from aa union select * from bb; #直接报错,因为前后查询结果列数不一致
select username,gh from aa union select username ,phone from bb; #这样才正确

         如果两次查询中有相同的数据,则只会保留一条,如果想保留相同的数据,使用union all即可

6.order by排序

select * from user order by 字段名1 [降序DESC | 默认ASC升序],.......可以设置多个排序字段;

ascend: 上升, 升职

descend: 下降

注意: 如何实现根据拼音排序:

        1. 如果字符集是gbk,直接排序即可,就是拼音排序的

        2. 如果字符集是utf-8,需要先将字符集转换为gbk,再进行排序,如下:

SELECT * 
FROM aa
ORDER BY CONVERT(username using gbk);

7.group by分组

group by语句根据一个或多个列对结果集进行分组,在分组的列上我们可以使用count,sun,avg等函数

select 字段名1,字段名2,....,函数名 from 表名 [where条件] group by 字段名;

aa表数据:

select * from aa group by username 结果如下, 显示了每一组中第一行数据

select  username, gh, count(*)  from  aa  group  by  username 结果如下:

count(*) 统计分组中多少行数据, count(字段名)表示分组中该字段名不为null的行数

select  username, sum(gh)  from  aa  group  by  username 结果如下:

这里sum(gh)函数用于统计每一个分组中gh这一列的和

select  username, avg(gh)  from  aa  group  by  username 结果如下:

这里avg(gh)函数用于计算每一个分组中gh列的平均值

在分组统计的基础上再进行统计

 select  username, gh, count(*)  as  'cou'  from  aa  group  by  username  with  rollup 结果如下:

可以看出对每一个分组的count(*)再进行了count()统计

 select  username, gh, sum(gh)  as  'ghsum'  from  aa  group  by  username  with  rollup 结果如下:

可通过下面方式将查询结果中的NULL设置名字

select  coalesce(username, '总数'), gh, sum(gh)  as  'ghsum'  from  aa  group  by username with rollup;

coalesce(a,b,c): 如果a=null选择b,b=null选择c,如果a != null,就选择a,就这样

8.having

在分组的基础上再进行筛选

aa表数据:

 

select  *, sum(gh)  from  aa  group  by  username 结果如下:

查询分组后sum(gh) > 200的数据: 

select  *, sum(gh)  as  'ss'  from  aa  group  by  username  having  ss > 200;这样就可以了

having后的判断字段必须是聚合函数返回的结果

9.多表查询

你可以在select,update和delete语句中使用mysql的join来来联合多表查询, join按照功能大致分为如下三类:

        1. inner join(内连接或等值连接):  获取两个表中字段匹配关系的记录

        2. left join(左连接):  左表优先,获取左表所有记录,即便右表没有对应匹配的记录

        3. right join(右连接):  右表优先,获取右表所有记录,即使左表没有对应匹配的记录

aa表数据:

 bb表数据: 

(1) inner join

select  a.*,  b.phone  from  aa a  inner  join  bb b  on  a.username  =  b.username 结果如下:

inner join也可以直接使用join,效果一样,这里主要是根据on后面的匹配关系查询两表中都有的匹配记录

上面查询也可以等价于:

select   a.*,  b.phone  from  aa a, bb b  where  a.username  =  b.username ;

(2) left join

select  a.*,  b.phone  from  aa a  left join  bb b  on  a.username  =  b.username 结果如下:

其中aa 为左表,在左连接中会查询左表中所有记录,再根据on后面的匹配关系查询右表中与之匹配的记录

 

(3) right join

select  b.username , b.phone,  a.username  from  aa a  right join  bb b  on a.username =   b.username 结果如下:

其中aa为左表,bb为右表,在右连接中会查询右表所有记录,,再根据on后面的匹配关系查询左表中与之匹配的记录

 

10.null值处理

aa表数据:

select * from aa where username = null; 这样查询不到id=15那条记录

关于null的比较是特殊的,你不能使用=null或者!=null来查询,因为null值与其他任何值的比较永远返回null,即null = null返回null

为了处理这种情况,mysql提供了三种运算符:

        1. is null: 当列的值是null时,返回true

        2. is not null: 当列的值不是null时,返回true

        3. <=>:比较操作符(不同于 = 运算符),当比较的两个值相等时(包含都为null),返回true

#这两种都可以
select * from aa where username is null;
select * from aa where username <=> null;