mysql数据库基础操作

2023-08-18 14:46:46

1.1 数据库发展史

2.1 关系型数据库逻辑结构

3.1 mysql增删改查

3.2 常用SQL语句

3.2.1 创建数据表student详细代码

1.1 数据库发展史

网状数据库和 层次型数据库 --> 关系型数据库 --> 非关系型数据库（NoSQL 处理数据能力更强 MongoDB）

2.1 关系型数据库逻辑结构

3.1 mysql增删改查

    show databases;                         /展示所有的数据库/

    USE 数据库名字;                         /进入指定数据库/

    SHOW TABLES;                        /展示该数据库下所有的表/

    DESC 表名称;                    /描述表中的列/

3.2 常用SQL语句

    DROP DATABASE IF EXISTS ****;    /丢弃数据库，如果存在数据库****/
    
    CREATE DATABASE ****;            /创建数据库****/
    
    USE ****;                        /进入创建的数据库/

    CREATE TABLE student();          /创建保存数据的表student/

3.2.1 创建数据表student详细代码

/*创建表*/
CREATE TABLE student(                   /创建表名为student的表/
    sid int,                            /创建编号/
    name varchar(8),                    /创建姓名/
    sex varchar(1),                     /创建性别/
    score int                           /创建分数/

);

/*插入表内容*/   
        /注意插入数据的时候严格按照创建数据表的顺序插入数据/

INSERT INTO student VALUES('1','xiaoming','0','80');  /向表名为student中插入第一组数据/
INSERT INTO student VALUES('2','xiaogang','1','95');  /向表名为student中插入第二组数据/
                    /依次类推，往后插入数据。/

/*查询表格内容*/

SELECT * FROM student;              /查询表格为student的内容/

3.2.2 修改数据

/*修改数据*/

UPDATE student SET name ='xiaojun', sex='1' WHERE sid='1';   /修改第一条数据/

3.2.3 删除数据

/*删除数据*/

DELETE FROM student WHERE sid='1';  /删除表名为student中的第一条数据/

3.3.1 解决mysql中文乱码的问题

脚本文件另存为的编码为UTF8

客户端连接服务器端的编码为UTF8

SET NAMES UTF8

3.服务器端创建数据库使用的编码为UTF8

CREAT DATABASE xz CHARSET=UTF8

3.4.1 mysql 中的列类型

数值型：可以省略引号

tinyint （微整形）占一个字节，范围：-128—127

smallint （小整形）占两个字节，范围：-32768—32767

int （整型）占四个字节，范围：-2147483648—2147483647

bigint（大整形）占八个字节，范围超级大

浮点型：可以不加引号

float（单精度浮点型）占四个字节，范围3.4e38，可能产生计算误差

double（双精度浮点型）占八个字节，范围比bigint大得多，可能产生误差

定点小数：decimal（M,D） M代表总有效位数，D代表小数点后的有效位数

布尔型 Boolean /bool 不加引号

只有两个值，分别是true（真）和false（假），这两个值不能加引号使用的时候会自动转为tinyint，true变为1，false变为0，也可以直接使用1或0.往往用于存储只有两个结果的数据，例如性别，是否在线，是否为会员

日期时间形：不可省略引号

日期型：date 2020-12-25

时间型：time 15:20:30

日期时间型： datetime 2020-12-25 15:20：30

字符串型：必须加引号

Varchar（M） 变长字符串 操作速度相对慢 M最大65535

Char（M）定长字符串 M最大255，操作速度相对快，往往存储长度固定的数据，例如身份证号码，手机号码，可能产生空间浪费

3.text（M）大型变长字符串,M最大值是2G

3.4.2 列约束

主键约束——primary key

不允许插入重复的数据，一个表里边只能有一个主键约束。一般加到编号列上，查询的时候会按照编号从小到大显示，就会加快查找速度。声明了主键约束的列上不允许插入NULL
/*主键约束*/
    
    CREATE TABLE student(                /创建表名为student的表/
    sid int PRIMARY KEY,                 /创建编号的列上加入primary key/
    name varchar(8),                    /创建姓名/
    sex varchar(1),                     /创建性别/
    score int                           /创建分数/

);

唯一约束——unique

声明了唯一约束的列上不允许插入重复的值，允许插入NULL，而且是多个NULL。

/*唯一约束*/
    CREATE TABLE student(                /创建表名为student的表/
    sid int PRIMARY KEY,                 /创建编号    列上加入primary key/
    name varchar(8) UNIQUE,              /创建姓名    列上加入unique/
    sex varchar(1),                     /创建性别/
    score int                           /创建分数/

);

非空约束——NOT NULL

声明了非空约束的列上禁止插入NULL

/*非空约束*/
    CREATE TABLE student(                /创建表名为student的表/
    sid int PRIMARY KEY,                 /创建编号    列上加入primary key/
    name varchar(8) UNIQUE,              /创建姓名    列上加入unique/
    sex varchar(1) NUT NULL,             /创建性别    列上加入nut null/
    score int                           /创建分数/

);

默认值约束——DEFAULT

可以通过DEFAULT给列设置默认值，具体方式有两种

1.insert into laptop values（5，‘小米’，default……）；

2.insert into laptop(lid,title) values(6,’apple’)

/*默认值约束*/
    CREATE TABLE student(                /创建表名为student的表/
    sid int PRIMARY KEY,                 /创建编号    列上加入primary key/
    name varchar(8) UNIQUE,              /创建姓名    列上加入unique/
    sex varchar(1) NUT NULL,             /创建性别    列上加入nut null/
    score int DEFAULT 60                 /创建分数    列上加入default/
);

    INSERT INTO student VALUES('1','xiaoming','0',default);    /设置了默认分数为60/

外键约束

外键的取值范围必须到另外一个表的主键列中去寻找。两者的列类型要保持一致

使用方法：foreign key(外键列) references 另一个表（主键列)

/*外键约束*/

    CREATE TABLE student(                /创建表名为student的表/
    sid int PRIMARY KEY,                 /创建编号    列上加入primary key/
    name varchar(8) UNIQUE,              /创建姓名    列上加入unique/
    sex varchar(1) NUT NULL,             /创建性别    列上加入nut null/
    score int DEFAULT 60,                /创建分数    列上加入default/
    FOREIGN KEY(famliyId) references famliy(fid)  /把familyId作为外键，取值要到family中的fid中去找/
);

自增列——auto_increment

自动增长，自动获取当前的最大值，然后加1插入，自增列添加在主键列上

注意：只适用于整数型的列上，同时允许手动赋值，如果想要实现自增，需要赋值NULL，允许使用NULL
/*自增列*/
    CREATE TABLE student(                /创建表名为student的表/
    sid int PRIMARY KEY,                 /创建编号    列上加入primary key/
    name varchar(8) UNIQUE,              /创建姓名    列上加入unique/
    sex varchar(1) NUT NULL,             /创建性别    列上加入nut null/
    score int DEFAULT 60                 /创建分数    列上加入default/
);

    INSERT INTO student VALUES(NULL,'xiaoming','0','85');    /设置了自增/

3.5.1 简单查询

1.查询特定的列

/*查询特定的列*/

    	SELECT eid,ename FROM emp;     (查询所有员工的编号和姓名)
	SELECT ename,sex, salary, birthday FROM emp;    (查询所有员工的姓名，性别，工资，生日)

2.查询所有的列

/*查询所有的列*/

        select * from emp;（查询所有员工的内容）
	select eid,ename,sex,birthday,salary,deptId from emp;（查询所有员工的内容）

3.给列起别名

/*给列起别名*/

        Select eid as 编号,ename as 姓名 from emp;(查询出所有员工的编号和姓名，使用汉字别名)
	Select ename as 姓名,sex 性别,birthday 生日 from emp;（查询所有员工的姓名生日性别 注意：as关键字可以省略）
        Select ename a,salary b from emp;(查询所有员工的工资和姓名，并使用别名为字母)

4.显示不同的记录

/*显示不同的记录*/

    Select distinct deptId from emp;(查询出员工所属部门编号有哪些)
    Select distinct sex from emp;(查询出都有哪些性别的员工)

5.查询时执行计算

/*查询时进行计算*/

    Select 1+2+3-5*8.3+935*7;（计算1+2+3-5*8.3+935*7）
    Select ename 姓名,salary *12 from emp;(查询出员工的姓名及其年薪)
    Select ename 姓名,(salary+500)*12+10000 年薪 from emp;(假设每个员工的工资增长500元，年终奖10000，查询所有员工的姓名及其年薪 )

6.对结果集排序

如果不加排序规则，默认按照升序进行排列（ASC）

/*按照结果集排序*/

        Select * from dept order by did asc;（查询所有的部门，结果集按照编号的升序排列）
	Select * from dept order by did desc;（查询所有的部门，结果集按照编号的降序排列）
	Select * from emp order by salary desc; (查询所有的员工，结果集按照工资的降序排列)
	Select * from emp order by birthday asc;(查询所有的员工，结果按照年龄从大大小)

        Select * from emp order by ename asc;（查询所有的员工，结果按照姓名升序排列）
注意：上述排列按照unicode码排序
	Select * from emp order by salary desc,ename asc;（查询所有的员工，结果集按照工资的降序排列，如果工资相同，按照姓名排列）
	Select * from emp order by sex asc,birthday asc;(查询所有的员工，结果集要求女员工显示在前，如果性别相同，按照年龄从大到小排列)

7.条件查询

/*条件查询*/

        Select * from emp where eid=5;（查询编号为5的员工的所有列）

	Select * from emp where ename=’King’;(查询出姓名为king的员工所有列)

	Select * from emp where deptId=20;（查询出20号部门下的员工有哪些）

	Select * from emp where salary >5000;(查询出工资在5000以上的所有列)

	Select * from emp where birthday>=’1991-1-1’; (查询出1991年后出生的员工有哪些)

 	Select * from emp where deptId !=10;（查询出不在10号部分的员工有哪些）

	Select * from emp where deptId  is NULL;(查询出没有明确部门的员工有哪些)

	Select * from emp where deptId  is  not NULL;（查询出有明确部门的员工有哪些）

	Select * from emp where sex=1 and salary>5000;(查询出工资在5000以上的男员工)

	Select * from emp where salary>=5000 and salary<=7000;(查询出工资在5000-7000的员工有哪些)

	Select * from emp where salary between 5000 and 7000; (查询出工资在5000-7000的员工有哪些)

	Select * from emp where salary>7000 or salary <5000;(查询出工资不在5000-7000的员工有哪些)

	Select * from emp where salary not between 5000 and 7000; (查询出工资不在5000-7000的员工有哪些)

	Select * from emp where birthday between‘1993-1-1’and‘1993-12-31’;(查询出1993年出生的员工有哪些)

        Select * from emp where birthday >=‘1993-1-1’and  birthday<=‘1993-12-31’; (查询出1993年出生的员工有哪些)

        Select * from emp where deptId=20 or deptId = 30;(查询出20号部分和30号部分的员工有哪些 满足其一 or)

	Select * from emp where deptId in (20,30); (查询出20号部分和30号部分的员工有哪些 满足其一 in)

	Select * from emp where deptId not in(20,30) ;(要求查询出不在20号部门也不再30号部门的员工有哪些)

8.模糊条件查询：

/*模糊条件查询*/

    Select * from emp where ename like ‘%e%’;    查询员工姓名中含有字母e的样有哪些

    Select * from emp where ename like ‘%e’;     查询姓名中以e结尾的员工

    select * from emp where ename like ‘%e_’;    查询姓名中倒数第二个字符为e的员工有哪些

9.分页查询

/*分页查询*/

                                            /假设每页显示五条数据：/
                                    第一页：select * from emp limit 0,5;
				    第二页：select * from emp limit 5,5;
				    第三页：select * from emp limit 10,5;

10.聚合查询和分组查询：

/*聚合查询和分组查询*/

    Select count(eid) from emp;        查询出所有员工的数量

    Select count(deptId) from emp;     通过部门编号这一列查询员工的数量

    Select count(ename) from emp where sex=1;     通过姓名查询所有男员工的数量

    Select sum(salary) from emp;    查询出所有员工工资总和

    Select avg(salary) from emp;    查询所有员工的平均工资

    Select  min(salary) from emp where sex=1;    查询出男员工的最低工资

    Select max(birthday) from emp;    查询出年龄最小的员工的生日
    
    Select max(salary),min(salary),avg(salary),deptId from emp group by deptId;    每个部门员工的最高工资，最低工资，平均工资
    
    Select count(sex),sum(salary),avg(salary),sex from emp group by sex;    查询出男女员工的数量，工资总和，平均工资

11.子查询

/*子查询*/
    i.	示例：查询出研发部的员工有哪些
步骤1：查询出研发部的部门编号是多少
	Select did from dept where dname=’研发部’;

步骤2：查询出部门编号为10的员工有哪些
	Select * from emp where deptId = 10;
	综合：select * from emp where deptId=( 	Select did from dept where dname=’研发部’);

    ii.	练习：查询出比tom工资高的员工有哪些
步骤1：查询tom的工资是多少
 select salary from emp where ename='Tom';
步骤2：查询比tom工资高的员工
select * from emp where salary >	6000;
综合：select * from emp where salary >(select salary from emp where ename='Tom');
    iii.	练习：查询出和tom同一年出生的员工有哪些
Year（）获取日期中的年份
查询所有员工出生的年份：select year(birthday) from emp;

步骤1：查询tom出生的年份
	select year(birthday) from emp where ename=’tom’;
步骤2：查询出和tom一样出生年份的员工
	Select year(birthday) from emp where year(birthday)=1990;
综合：select *from emp where ename year(birthday)=(select year(birthday)  from emp where ename='tom')and ename !=’tom’;

12.多表查询

/*多表查询*/
    Select ename,dname from emp,dept where deptId=did;
     （存在问题：无法查询出没有部门的员工，也无法查出没有员工的部门。）

    1.	内连接：
a)	语法：select ename,dname from emp inner join dept on deptId=did;
    2.  左外连接：先写哪个表，哪个表就是左，会显示所有的记录，即便右侧表没有对应的数据
				a)	语法：select ename,dname from emp left outer join	 dept on deptId=did;
    3.  右外连接：后写哪个表，哪个表就是右，会显示所有的记录，即便左侧表没有对应的数据
				a)	 语法：select ename,dname from emp right outer join dept on deptId=did;
左外和右外连接中的Outer 关键字可以省略不写
    4.	全连接
					Full join （mysql不支持这种连接）
		
左外和右外的结果组合起来
	Union
	Union all （不合并相同的记录）
	(select  ename,dname from emp left outer join	 		dept on deptId=did)
		Union all /union
	(select ename,dname from emp right outer join dept on deptId=did);

码农公寓

1.1 数据库发展史

2.1 关系型数据库逻辑结构

3.1 mysql增删改查

3.2 常用SQL语句

3.2.1 创建数据表student详细代码

3.2.2 修改数据

3.2.3 删除数据

3.3.1 解决mysql中文乱码的问题

3.4.1 mysql 中的列类型

3.4.2 列约束

3.5.1 简单查询

相关文章