数据库基础
数据库基础一、web应用架构
客户端——web服务器——数据库
二、数据库
1. 数据库定义
数据库（database）是用来组织、存储和管理数据的仓库
2. 分类：
(1）关系型数据库(SQL数据库)
基于关系模型的数据库管理系统
常见的关系型数据库：MySQL（3306）、SQL server（1433）、Oracle（1521）、PostgreSQL（5432）、MariaDB（MySQL 的分支，兼容 MySQL，默认端口相同3306）
（2）非关系型数据库（NoSQL）
不依赖传统关系模型的数据库管理系统
常见的非关系型数据库：Redis （6379）、MongoDB（27017）、Cassandra（9042）、Elasticsearch（9200）
3. sql语句
登录数据库：mysql -u -root -p
（1）对数据库操作
查看有哪些数据库：show databases;
创建数据库：create database database_name;
删除数据库：drop database database_name;
使用数据库：use database_name;
查看当前数据库：select database();
（2）对表操作
查看表：show tables;
创建表：create table 表名 列名 数据类型 约束;（常见数据类型 常见约束）
create table user(
id int not null auto_increment,
username varchar(255) not null,
password varchar(255) not null,
phone varchar(255),
primary key(id)
);删除表：drop table table_name;
查看表结构：desc table_name;
（3）对数据操作
添加数据：
insert into user(id,username,password,phone) value (1,'zhangsan','123456','188');
insert into user value(2,'lisi','123456','199');修改数据
update user set password='654321' where name='san';
update user set password='521521' where id=1;删除数据
delete from user where username = 'lisi';
show databases;
create database empdb;
use empdb;
create table employees(
id int,
name varchar(255),
job varchar(255),
hiredate date,
salary float,
bonus float,
department int,
primary key(id)
);
insert into employees (id,name,job,hiredate,salary,department) value (1,'zhao','CLERK','2020-01-01',800.00,2);
insert into employees (id,name,job,hiredate,salary,bonus,department) value (2,'qian','SALESMAN','2021-05-06',1900.00,400.00,2);
insert into employees (id,name,job,hiredate,salary,bonus,department) value (3,'sun','SALESMAN','2021-05-10',1600.00,500.00,3);
insert into employees (id,name,job,hiredate,salary,department) value (4,'li','MANAGER','2021-07-12',2850.00,2);
insert into employees (id,name,job,hiredate,salary,bonus,department) value (5,'zhou','SALESMAN','2021-07-26',1600.00,1200.00,3);
insert into employees (id,name,job,hiredate,salary,department) value (6,'wu','ANALYST','2020-07-25',3500.00,1);
insert into employees (id,name,job,hiredate,salary,department) value (7,'zheng','CLERK','2021-08-12',3000.00,1);
insert into employees (id,name,job,hiredate,salary,bonus,department) value (8,'wang','PRESIDENT','2019-12-26',6000.00,2500.00,1);
select * from employees;查询数据
select * from employee;
select id,name,job,salary from emplyee;
select name,salay from employees where salary > 2000;
select name,salary from employees where salary < 4000;
select name,salary from employees where salary > 2000 and salary < 4000;
select * from employees where job = 'manager' or job = 'salesman';
select * from employees where job in ('manager','salesman');
select * from employees where job != 'manager' and job != 'salesman';
select * from employees where job not in ('manager','salesman');
select * from employees where name like ('z%');
select * from employees where name like ('z___');
select * from employees order by salary; --默认按照升序进行排列 asc：表示按升序排序 desc：表示按降序排序
select * from employees order by salary desc;
select * from employees order by 1; --跟数字，可以确定列数
select * from employees limit 0,3; --第0行后开始显示三行
select * from teacher union select * from student; --union 联合查询，两个表查询结果列的数量必须相同，否则会进行报错
select * from teacher union select 1,2,3;4. 聚合函数
-- count 统计符合条件的行数
select count(*) from employees;
-- max 查询最大值 min查询最小值
select max(salary) as '最高工资' from employees;
-- sum 求和
select sum(salary) as '月支出' from employees;
-- avg 平均
select avg(salary) as '平均工资' from employees;5. 常见函数
-- 字符串连接
select concat('1','2');
select concat_ws('_','Hello','Wrold'); -- 指定分隔符将多个字符串连接
select group_concat(name) AS all_name from employees; -- 将表中整列内容连接，AS all_name指定别名为all_name
-- 截取字符串
select substr(database(),1,3); -- 从第一个字符起，截取三个字符
select left(database(),3); -- 从左边截取三个字符串
select right(database(),3); -- 从右边截取三个字符串
-- 查看版本
select @@version；
select version();
-- 获取当前用户
select user();
select system_user();
select current_user();
-- 获取安装目录
select @@datadir;
来源：豆瓜网用户自行投稿发布，如果侵权，请联系站长删除
页:
[1]