数据库及SQL/MySQL基础

ymingliu
235 阅读8分钟

课程参考

阿里云开发者社区——数据库学习路线

数据库及SQL/MySQL基础

1、常见数据库： Oracle：甲骨文； DB2：IBM； SQL Server：微软； Sybase：赛尔斯； MySQL：甲骨文

2、数据库全称：关系型数据库管理系统（RDBMS），即数据库服务器， Manager->databases->tables（管理员->仓库->表）

3、表结构：包含列数、列名称、列类型； 表记录：具体数据； 先给定表结构，再插入表记录

4、SQL：结构化查询语言。客户端使用其来操作服务器。

5、虽不区分大小写，但建议大写

6、数据库操作

  • DDL：表结构操作，包括表创建、修改、删除；
  • DML：表记录操作，包括表增，删，改；
  • DQL：表记录查询（重点、难点）；
  • DCL：对用户的创建和授权

DDL 数据定义语言

1、查看所有数据库

mysql> SHOW DATABASES;

切换数据库（注意先选库才能再查表）

mysql> USE TEST;

创建数据库

mysql> CREATE DATABASE [IF NOT EXISTS]  mydb [CHARSET=utf8];

删除数据库

mysql> DROP DATABASE mydb;

2、常用数据类型（列类型）:

  • Int 整型
  • double 浮点型，double(5,2)表示，最多五位，并且包含两位小数
  • decimal 浮点型，在表单钱方面使用该类型，因为不会出现精度缺失
  • char 固定长度字符串类型，最大char(255)，例如时间、身份证
  • varchar 可变长度字符串类型，最大varchar(65535)，例如姓名、备注

3、MySQL独有类型:

  • text(clob) 字符串类型，分为很小 tinytext (2^8-1B)、小 text (2^16-1B)、中 mediumtext (2^24-1B)、大 longtext (2^32-1B)
  • text(blob) 字节类型，分为tinyblob、blob、mediumblob、longblob
  • date 日期类型，格式为：yyyy-MM-dd
  • time 时间类型，格式为：hh:mm:ss
  • timestamp 时间戳类型

4、创建表

mysql> CREATE TABLE tb_stu(
-> number char(11),
-> name varchar(50),
-> age int,
-> gender varchar(10)
-> );

查看表结构

mysql> DESC tb_stu;
Field Type Null Key Default Extra
number char(11) YES NULL
name varchar(50) YES NULL
age int YES NULL
gender varchar(10) YES NULL

修改表

mysql> ALTER TABLE tb_stu
-> ADD(
-> education varchar(50)
-> );
mysql> DESC tb_stu;
Field Type Null Key Default Extra
number char(11) YES NULL
name varchar(50) YES NULL
age int YES NULL
gender varchar(10) YES NULL
education varchar(50) YES NULL 
mysql> ALTER TABLE tb_stu
-> MODIFY education varchar(100);
mysql> DESC tb_stu;
Field Type Null Key Default Extra
number char(11) YES NULL
name varchar(50) YES NULL
age int YES NULL
gender varchar(10) YES NULL
education varchar(100) YES NULL 
mysql> ALTER TABLE tb_stu
-> DROP education;
mysql> DESC tb_stu;
Field Type Null Key Default Extra
number char(11) YES NULL
name varchar(50) YES NULL
age int YES NULL
gender varchar(10) YES NULL 
mysql> ALTER TABLE tb_stu
-> RENAME TO t_stu;
mysql> SHOW TABLES;
Tables_in_test
t_stu
websites

DML 数据操作语言

1、查询表记录（属于DQL）

mysql> select * from t_stu;

2、在数据库中所有的字符串类型，必须使用单引号，不能用双引号

3、插入表记录

mysql> insert into stu(
-> number, name, age, gender
-> )values(
-> 'ITCAST_0001', 'zhangSan', 28, 'male'
-> );
mysql> insert into stu(
-> number, name
-> )values(
-> 'ITCAST_0002', 'liSi'
-> );
mysql> select * from stu;
number name age gender
ITCAST_0001 zhangSan 28 male
ITCAST_0002 liSi NULL NULL 
mysql> insert into stu values(
-> 'ITCAST_0003', 'wnagHu', 82, 'female'
-> );
mysql> select * from stu;
number name age gender
ITCAST_0001 zhangSan 28 male
ITCAST_0002 liSi NULL NULL
ITCAST_0003 wnagHu 82 female

4、修改表记录

mysql> update stu set name='wangHu'
-> where number='ITCAST_0003';
mysql> select * from stu;
number name age gender
ITCAST_0001 zhangSan 28 male
ITCAST_0002 liSi NULL NULL
ITCAST_0003 wangHu 82 female 
mysql> update stu set age=18
-> where name='wangHu' or number='ITCAST_0003';
mysql> select * from stu;
number name age gender
ITCAST_0001 zhangSan 28 male
ITCAST_0002 liSi NULL NULL
ITCAST_0003 wangHu 18 female 
mysql> update stu set age=age+1
-> where age between 20 and 40;
mysql> select * from stu;
number name age gender
ITCAST_0001 zhangSan 29 male
ITCAST_0002 liSi 39 NULL
ITCAST_0003 wangHu 18 female 
mysql> update stu set age=36
-> where name in ('zhangSan', 'liSi');
mysql> select * from stu;
number name age gender
ITCAST_0001 zhangSan 36 male
ITCAST_0002 liSi 36 NULL
ITCAST_0003 wangHu 18 female 
mysql> update stu set gender='male'
-> where gender=NULL;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
mysql> update stu set gender='male'
-> where gender is null;
mysql> select * from stu;
number name age gender
ITCAST_0001 zhangSan 36 male
ITCAST_0002 liSi 36 male
ITCAST_0003 wangHu 18 female

3、删除表记录

Delete from stu where 条件；

DCL 数据控制语言

1、创建用户，通常一个项目创建一个用户，且一个项目对应一个数据库

用户lym可在指定的localhost这一IP地址上登录

mysql> create user lym@localhost identified by '123';
MacBook-Pro-2:~ liuyiming$ mysql -u lym -p ‘123’
mysql> show databases;
Database
information_schema

注意，这样写该用户只能看见系统自带数据库，若想用户可在任意IP地址上登录，可写成

Create user 用户名@‘%’ identified by ‘密码’

2、用户授权

先切换到超级用户

mysql> grant all on test.* to lym@localhost;
mysql> show databases;
Database
information_schema
test

3、撤销权限

mysql> revoke delete on test.* from lym@localhost;

4、查看权限

mysql> show grants for lym@localhost;

5、删除用户

mysql> drop user lym@localhost;

DQL 数据查询语言

1、查询所有列

mysql> select * from stu;

2、制定列查询

mysql> select ename,sal from emp;

3、完全重复的记录显示一次

mysql> select distinct deptno from emp;

4、列运算

mysql> select *,sal*1.5 from emp;
empno ename job mgr hiredate sal comm deptno sal*1.5
1002 黛绮丝 销售员 1006 2001-02-20 16000.00 3000.00 30 24000.00
1001 甘宁 文员 1013 2000-12-17 8000.00 NULL 20 12000.00
1003 殷天正 销售员 1006 2001-02-22 12500.00 5000.00 30 18750.00
1004 刘备 经理 1009 2001-04-02 29750.00 NULL 20 44625.00
1005 谢逊 销售员 1006 2001-09-28 12500.00 14000.00 30 18750.00
1006 关羽 经理 1009 2001-05-01 28500.00 NULL 30 42750.00
1007 张飞 经理 1009 2001-09-01 24500.00 NULL 10 36750.00
1008 诸葛亮 分析师 1004 2007-04-19 30000.00 NULL 20 45000.00
1009 曾阿牛 董事长 NULL 2001-11-17 50000.00 NULL 10 75000.00
1010 韦一笑 销售员 1006 2001-09-08 15000.00 0.00 30 22500.00
1011 周泰 文员 1008 2007-05-23 11000.00 NULL 20 16500.00
1012 程普 文员 1006 2001-12-03 9500.00 NULL 30 14250.00
1013 庞统 分析师 1004 2001-12-03 30000.00 NULL 20 45000.00
1014 黄盖 文员 1007 2002-01-23 13000.00 NULL 10 19500.00
1015 张三 保洁员 1001 2013-05-01 80000.00 50000.00 50 120000.00

任何东西和NULL相加等于NULL

把NULL转换成某东西

mysql> select *,sal+ifnull(comm,0) from emp;

不能用加号连接字符串

mysql> select concat(ename, job) from emp;
concat(ename, job)
黛绮丝销售员
甘宁文员
殷天正销售员
刘备经理
谢逊销售员
关羽经理
张飞经理
诸葛亮分析师
曾阿牛董事长
韦一笑销售员
周泰文员
程普文员
庞统分析师
黄盖文员
张三保洁员

给列起别名

mysql> select ename as 姓名, job as 工作 from emp;
mysql> select ename  姓名, job 工作 from emp;

SQL题：mysql> select empno, ename job, sal from emp;这条语句是正确的

5、条件控制

mysql> select * from emp where sal>20000;
mysql> select * from emp where comm is not null;
mysql> select * from emp where sal between 20000 and 30000;
mysql> select * from emp where job in ('经理', '分析师');

6、模糊查询

人名中有两个字，并且第一个字是’张’

mysql> select * from emp where ename like '张_';

人名中有三个字，并且第一个字是’张’

mysql> select * from emp where ename like '张__';

人名中有三个字

mysql> select * from emp where ename like '___';

人名以’刚’结尾，%指匹配0个或多个字符

mysql> select * from emp where ename like '%刚';

人名包含’小’

mysql> select * from emp where ename like ‘%小%';

7、排序

升序

mysql> select * from emp order by sal asc;

降序

mysql> select * from emp order by sal desc;

两条件排序

mysql> select * from emp order by sal asc, comm desc;

三条件排序

mysql> select * from emp order by sal asc, comm desc, empno asc;

8、聚合函数

总员工数，NULL不计数

mysql> select count(*) from emp;

SQL题：mysql> select count(1) from emp;这条语句与count(*)的查询结果相同，并不是错的。

总工资数，NULL记为0

mysql> select sum(sal) from emp;
mysql> select max(sal) from emp;
mysql> select min(sal) from emp;
mysql> select avg(sal) from emp;
mysql> select count(*) 人数, sum(sal) 总和, max(sal) 最高, min(sal) 最低, avg(sal) 平均 from emp;
人数 总和 最高 最低 平均
15 370250.00 80000.00 8000.00 24683.333333

9、分组查询

不同工种的人数

mysql> select job, count(*) from emp group by job;

以下是错误的，ename不是组信息或聚合函数

mysql> select job, count(*), ename from emp group by job;

不同工种的人数和最大工薪

mysql> select job, count(*), max(sal) from emp group by job;

每个部门中工薪大于15000的员工个数

mysql> select deptno, count(*) from emp where sal>15000 group by deptno;

每个部门中工薪大于15000的员工个数，并且个数大于等于2的部门

mysql> select deptno, count(*) from emp where sal>15000 group by deptno having count(*)>=2;

总结

以上查询语句的重要性排序：Select、 from、 where、 group by、 having、 order by

MySQL特有语句

limit（常用在分页查询）

查询第1-5行

mysql> select * from emp limit 0, 5;

查询第9-13行

mysql> select * from emp limit 8, 5;

查询第14-18行

mysql> select * from emp limit 13, 5;

一页记录10行，查询第3页

mysql> select * from emp limit 20, 10;

(当前页-1)*每页记录数

avatar
文章
阅读
粉丝