大学学科建设网站,网站开发的职业规划,杭州市建设信用网官网,做搞机网站mysql登录远程登录方式本地登陆方式mysql#xff1a;mysql -h 主机名 -P 端口号 -u 用户名 -p密码
mysql#xff1a;mysql -uroot -p密码数据库操作命令创建数据库、删除数据库、展示所有数据库名。查看当前数据库名、查看所有表、查看其他数据库的表。查看数据库的版本、表结…mysql登录远程登录方式本地登陆方式mysqlmysql -h 主机名 -P 端口号 -u 用户名 -p密码
mysqlmysql -uroot -p密码数据库操作命令创建数据库、删除数据库、展示所有数据库名。查看当前数据库名、查看所有表、查看其他数据库的表。查看数据库的版本、表结构、以及字符集、数据库引擎。-- 如果该数据库不存在创建该数据库
create database if not exists 数据库名;-- 设置指定数据库的字符集为gbk/uft8
alter database 数据库名 character set gbk/uft8;-- 如果该数据库存在删除该数据库
drop database if exists 数据库名 ; -- 查看所有数据库
show databases; -- 使用数据库
use 数据库名; -- 在当前数据库下查看所有表格
show tables; -- 查看其他数据库的全部表格
show tables from 数据库名; -- 查看当前数据库名
select database(); -- 查看当前MySQl登陆的用户
select user(); -- 查看当前数据库的版本
select version(); -- 查看表结构
desc 表名; -- 查看数据库的字符集
show variables like %character%;
show variables like %char%;
show variables like collation%;-- 查看数据库的引擎
show engines;表操作命令创建表、修改表数据插入表数据、修改表数据、删除表数据、查询表数据。修改表结构新增字段、修改表字段、增加主键、调整字段顺序、指定位置添加字段。根据原表创建新表仅复制表结构、复制表结构和数据、复制部分表字段和表数据。-- 创建员工表先在数据库里面创建该表为了后面做测试用
create table dept(department_id int primary key auto_increment, -- 部门编号dname varchar(14) , -- 部门名字location varchar(13) -- 地址
);create table employee(employee_id int primary key auto_increment, -- 员工编号c_name varchar(20) -- 员工中文名e_name varchar(20) -- 员工英文名hiredate date, -- 雇佣日期入职日期salary int, -- 薪水comm int, -- 奖金job_id int, -- 所属工种department_id int not null, -- 部门编号manager_id int -- 直接领导编号
);-- 表中插入数据
insert into dept values(10,财务部,北京);
insert into dept values(20,研发部,上海);
insert into dept values(30,销售部,广州);
insert into dept values(40,行政部,深圳);
insert into dept values(50,人力资源,惠州);
-- 表中插入数据
insert into employee values(1,刘一,liuyi,1980-12-17,7902,800,1,10,2);
insert into employee values(2,陈二,chener,1981-02-20,7698,1600,3,30,3);
insert into employee values(3,张三,zhangsan,1981-02-22,7698,1250,5,30,4);
insert into employee values(4,李四,lisi,1981-04-02,7839,2975,2,20,5);
insert into employee values(5,王五,wangwu,1981-09-28,7698,1250,1,40,0);
insert into employee values(6,赵六,zhaoliu,1981-05-01,7839,2850,3,50,5);-- 更新数据
update employee set c_name 刘一一 where id1;-- 删除数据
delete from employee where id1;-- 查询数据
select * from employee where id in(1,4);-- 修改表名
alter table 旧表名 rename to 新表名;-- 修改表注释
alter table 表名 comment 系统信息表;-- 修改字段类型和注释
alter table 表名 modify column 字段名 varchar(20) COMMENT 新的注释;-- 设置字段允许为空
alter table 表名 modify column 字段名 varchar(255) null COMMENT 新注释;-- 增加一个字段设好数据类型且不为空添加注释
alert table 表名 add 字段名 varchar(255) not null comment 新注释; -- 增加非空、自增主键
alter table 表名 add 字段名 int(5) not null auto_increment ,add primary key (aid); -- 修改字段名字(要重新指定该字段的类型)
alter table t_app change 原字段名 新字段名 varchar(20) not null;-- 删除字段
alter table 表名 drop 字段名; -- 在某个字段后增加字段
alter table 表名 add column 新字段名 int not null default 0 after 字段名-- 调整字段顺序
alter table employee change num num int not null after departmen_id ; -- 表的删除
drop table 表名 ;--复制表的结构不复制表数据
create table 新表名 like 旧表名;--复制表的结构同时也复制表数据
create table 新表名 select * from 旧表名;--只复制部分表结构和对应的数据并且带筛选条件
create table user1 select id, name,salary from user where salary3000;--仅复制部分字段
create table user2 select id, name from user;表约束NOT NULL 非空约束DEFAULT 默认用于保证该字段有默认值。PRIMARY KEY 主键约束UNIQUE: 唯一约束CHECK 检查约束FOREIGN KEY外键约束。create table student (
id int,
name varchar(20) ,
gender char(1) ,
seat int,
age int,
class_id int, PRIMARY KEY(id), -- 主键
UNIQUE (seat), -- 唯一键
CHECK(gender男 or gender女),-- 检查
FOREIGN KEY (class_id) REFERENCES class (id) -- 外键
)注释这些约束在你创建表和修改表的时候都可以使用。表查询distinct(去重)limit(分页查询)offset(跳过多少条)UNION 和 UNION ALL(联合查询)like(模糊查询)where、between、in、or、and条件关键字order by (asc升序、desc降序排序)group by (分组查询)having 关键字case(流程控制)-- distinct(去重)
select distinct 字段名 from 表名;-- limit(初始记录行的偏移量是 0(而不是 1),第一个参数指定第一个返回记录行的偏移量第二个参数指定返回记录行的最大数目。)
select * from 表名 limit 5,10; -- 检索记录行6-15-- offset(跳过多少条)
selete * from employee limit 2 offset 1;
-----------------------------------------
| 2 | lisi | 12000 | 40 | 90 |
| 3 | wangwu | 0 | 50 | 0 |
-----------------------------------------
-- 注意
-- 1.数据库数据计算是从0开始的
-- 2.offset X是跳过X个数据limit Y是选取Y个数据
-- 3.limit X,Y 中X表示跳过X个数据读取Y个数据--union 和union allunion all是直接连接取到得是所有值记录可能有重复 union 是取唯一值记录没有重复。-- UNION 的语法如下
[SQL 语句 1]
UNION
[SQL 语句 2]-- UNION ALL 的语法如下
[SQL 语句 1]
UNION ALL
[SQL 语句 2]-- UNION全连接查询把部门表和员工表的所有数据都查出来若有两个表都有匹配数据的就显示匹配数据若其中有一个表在另一个表中没有匹配数据的输就显示为null
select e.ename,d.dnameFROM employee e left JOIN dept dON e.department_id d.department_id
UNION
select e.ename,d.dnameFROM employee e right JOIN dept dON e.department_id d.department_id;
------------------
| ename | dname |
------------------
| 刘一 | 财务部 |
| 陈二 | 销售部 |
| 张三 | NULL |
| 李四 | 研发部 |
| 王五 | 行政部 |
| 赵六 | 人力资源 |
-------------------- 模糊查询like%标识匹配任意哥字符_表示匹配一个字符
-- 查询employee表里面名字含有豪字的员工的全部信息
select * form employee where c_name like %豪%;-- 查询员工名中第三个字母为a第五个字母为b的员工信息;
select * from employee where c_name like __达_法%;-- 当查询的信息信息看里面还有_这样的特殊字符;
select * from employee where c_name like __%;-- 查询工资在5000到6000之间的员工信息;
select * from employee where salary between 5000 and 6000;-- in、or、and关键字
select * from dept where location in (北京,上海);
select * from dept where location 北京 or location 上海 ;
select * from employee where (department_id30 or department_id40) and salary 3000;-- 选择工资不在3000到5000的员工的姓名和工资按工资降序
select name, salary, department_id from employees where salary not between 3000 and 5000 order by salary desc;-- 查询每个部门的员工个数
select count (*) , department_ id from employee group by department_ id;-- 给30号部门的增加50040号部门增加100050号部门增加1500
select *, (case department_idwhen 30 thensalary500when 40 thensalary1000when 50 thensalary1500else salaryend) 涨后工资
fromemployee;-- 显示员工的薪资等级
select *, (casewhen salary 7900 then高薪资when salary 7800 then中等薪资when salary 7700 then低薪资else太难了end) 薪资等级
fromemployee
order by salary desc;-- 语法格式
select 字段1,字段2,字段3 from 表名 [where 筛选条件] [group by 分组] [having 筛选条件1] [order by 排序列表]mysql字符串函数concat()拼接字符串substr()截取字符串instr()获取子串第一次出现的索引lpad()左边以指定字符填充到指定长度rpad()右边以指定字符填充到指定长度upper()转换为大写lover()转换为小写replace()替换函数length()获取字节长度trim()去掉字符串前后空格-- 将英文名全部转换大写和全部转换为小写然后进行拼接。
select concat(upper(e_name) ,lower(e_name)) from employee;-- substr注意:索引从1开始截取从指定索引处后面所有字符
select substr(欢迎关注非科班的科班带你一起提升代码内功,7) str ;-- 从指定索引截取指定长度的字符串substr(str,num1,num2)第二个参数时索引、第三个参数是指定的长度。
select substr(欢迎关注非科班的科班带你一起提升代码内功,5,6) str;-- 姓名中首字符大写其他字符小写然后用_拼接显示出来
SELECT CONCAT (UPPER (SUBSTR(last_ name,1,1)),_ ,LOWER (SUBSTR(last_ name,2))) out_put
FROM employees;-- instr返回子串在指定字符串第一次出现的素引如果找不到返回0
select instr(欢迎关注非科班的科班带你一起提升代码内功, 科班) as str;-- length获取字符串长度、trim()去掉字符串前后的空字符串
select length(trim( 非科班的科班 )) as str; -- lpad用指定的字符实现左填充指定长度
select lpad(非科班的科班,9,*) as str;-- rpad用指定的字符实现右填充指定长度
select rpad(非科班的科班,9,*) as str;-- replace 替换
select replace(非科班的科班, 科班,javaboy) as str;mysql字符串函数now返回当前的日期和时间year返回年份month返回月份monthname以英文形式返回月份day返回日hour小时mimute分钟second秒datediff返回两个日期相差的天数date_format将时间日期转换为字符串str_to_date将字符转换成日期curdate返回当前日期不包含时间curtime返回当前时间不包含日期-- now()返回当前系統日期时间
select now();-- curdate返回当前系统日期不包含时间curtime返回当前时间不包含日期
select curdate();
select curtime();-- 可以获取指定的部分年、月、日、小时、分钟、秒
select year(now())年;
select year(2020-1-1) 年;
select year(hiredate) 年 from employee;
select month(now()) 月;
select monthname(now()) 月;-- %Y 四位的年份
-- %y 2位的年份
-- %m 2位的月份( 01,02...11,12)
-- %c 1位的月份( 1,2...11,12)
-- %d 日( 01,02,.. )
-- %H 24小时制的小时
-- %h 12小时制的小时
-- %i 分钟( 00,01...59)
-- %s 秒( 00,01...59)-- str_to_date:将字符串转换为指定格式的日期对象
select str_to_date(2-28-2020,%m-%d-%Y);-- 查询入职日期为2020-2-28的员工信息
select * from employee where hiredate 2020-2-28 ;-- date_format:将日期转换成字符
select date_format(2020/02/28,%Y年%m月 %d日)
select date_format(now(), %y年%m月%d日) as date ; -- 查询工资大于7800的员工的中文名、入职日期
select c_name 中文名, date_format(hiredate,%m月/%d日 %y年) 入职日期 from employee where salary7800;mysql数学函数ceil中文意思表示天花板表示向上取整floor中文意思表示地板表示向下取整round对数字取四舍五入rand随机取0-1之间的所及小数mod取模运算truncate截取类似于字符串的substr的用法-- ceil表示向上取整。整数返回本身。小数返回的是与该数相邻并比该数大的整数
select ceil(2) ;
select ceil(2.21) ;
select ceil(-2) ;
select ceil(-2.1) ;-- floor向下取整。整数返回本身。小数返回的是与该数相邻并比该数大的整数
select floor(2) ;
select floor(2.21) ;
select floor(-2) ;
select floor(-2.1) ;-- rand在0-1之间随机去一个随机数
select round(rand()*10) ; --取一个0-10的随机整数-- round对数字取四舍五入
select round(-1.55) ;
select round(1.567,2) ;-- mod取余运算
select mod(3,2) ;
select 3%2;-- truncate截取第一个是要截取的数字第二个是要截取的位数从小数点后开始算
select truncate(2.3345534,1) ;创建视图和索引视图view创建查询视图删除视图索引index创建索引删除索引语法格式如下
create view 视图名 [新字段名1,新字段名2,新字段名3,新字段名4,....] as select语句--创建一个员工的视图
create view v_employee (id,name,sal,department,hiredate)
as select employee_id,c_name,salary,department_id,hiredate
from employee ;--查询视图
select * from v_employee where sal7800;--修改视图
alter view v_employee as select * from employee where salary7800;--删除视图
drop view v_employee ;--基于多表创建视图
create view v_test as select e.c_name,e.hiredate,e.salary,d.dname,d.location from employee e,dept d where e.department_idd.department_id;select * from v_test
----------------------------------------------------
| c_name | hiredate | salary | dname | location |
----------------------------------------------------
| 刘一 | 1980-12-17 | 7902 | 财务部 | 北京 |
| 陈二 | 1981-02-20 | 7698 | 销售部 | 广州 |
| 张三 | 1981-02-22 | 7698 | 销售部 | 广州 |
| 李四 | 1981-04-02 | 7839 | 研发部 | 上海 |
| 王五 | 1981-09-28 | 7698 | 行政部 | 深圳 |
| 赵六 | 1981-05-01 | 7839 | 人力资源 | 惠州 |
------------------------------------------------------(1)使用alter table 语句创建索性,使用场景是在表创建完毕之后再添加索引。语法如下
alter table 表名 add 索引类型 unique,primary key,fulltext,index[索引名]字段名-- 普通索引当column_list有多个的时候使用逗号隔开
alter table table_name add index index_name (column_list) ;-- 唯一索引当column_list有多个的时候使用逗号隔开
alter table table_name add unique (column_list) ;--主键索引当column_list有多个的时候使用逗号隔开
alter table table_name add primary key (column_list) ;-- (2)使用create index语句对表增加索引,create index可用于对表增加普通索引或UNIQUE索引可用于建表时创建索引。
create index index_name on table_name(username(length)); -- create只能添加这两种索引;
create index index_name on table_name(column_list);
create UNIQUE index index_name on table_name(column_list);
create index index_employee on employee(salary,hiredate,c_name);-- (3)删除索引,删除索引可以使用ALTER TABLE或DROP INDEX语句来实现。
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;视图 是一个虚拟表非真实存在其本质是【根据SQL语句获取动态的数据集并为其命名】用户使用时只需使用【名称】即可获取结果集并可以将其当作表来使用使用视图时将其当作表进行操作即可由于视图是虚拟表所以无法使用其对真实表进行创建、更新和删除操作仅能做查询用。 索引 数据库中将数据整齐的排列在磁盘阵列中当获取数据的时候只需要逐个搜索并返回结果但是 如果开发的应用有几百上千万甚至亿级别的数据那么不深入了解索引的原理 写出来程序就根本跑不动光查一个数据库就得好几天因此就需要索引能够快速的查找的需要的数据。mysql连接查询内连接 等值连接非等值连接外连接 左外连接右外连接自连接-- 内连接
select e.c_name,d.dname,d.location from employee e inner join dept d on e.department_id d.department_id;
---------------------------
| c_name | dname | location |
---------------------------
| 刘一 | 财务部 | 北京 |
| 陈二 | 销售部 | 广州 |
| 张三 | 销售部 | 广州 |
| 李四 | 研发部 | 上海 |
| 王五 | 行政部 | 深圳 |
| 赵六 | 人力资源 | 惠州 |
----------------------------- 左外连接是指以左边的表的数据为基准去匹配右边的表的数据如果匹配到就显示匹配不到就显示为null
-- 查询employee表中的所有数据和dept表中与employee中相匹配的数据若是没有匹配的就显示null
select e.c_name,d.dname from employee e left outer join dept d on d.department_id e.department_id ;-- 修改employee中的数据
update employee set department_id60 where employee_id3;-- 重新查询由于dept表中不存在60的数据所以再dept表中没有对应的匹配数据显示为null
select e.c_name,d.dname from employee e left outer join dept d on d.department_id e.department_id ;
------------------
| ename | dname |
------------------
| 刘一 | 财务部 |
| 陈二 | 销售部 |
| 张三 | NULL |
| 李四 | 研发部 |
| 王五 | 行政部 |
| 赵六 | 人力资源 |
-------------------- 右外连接和左外连接只不过是左右表相换也能达到同样的效果
-- 这里就是查询dept部门表对应所有部门和employee表中与之对应的数据你会发现本来employee中有6条数据只显示了5条数据因为有一个人的部门60再dept中没有数据所以就没有显示出来。
select e.c_name,d.dname from employee e right outer join dept d on d.department_id e.department_id;
------------------
| ename | dname |
------------------
| 刘一 | 财务部 |
| 陈二 | 销售部 |
| 李四 | 研发部 |
| 王五 | 行政部 |
| 赵六 | 人力资源 |
-------------------- 自连接查询就是当前表与自身的连接查询关键点在于虚拟化出一张表给一个别名
-- 查询员工以及他的上司的名称由于上司也是员工所以这里虚拟化出一张上司表
select e.c_name 员工名,b.c_name 上司名 from employee e left join employee b on e.manager_id b.employee_id;
----------------
| 员工名 | 上司名 |
----------------
| 刘一 | 陈二 |
| 陈二 | 张三 |
| 张三 | 李四 |
| 李四 | 王五 |
| 王五 | NULL |
| 赵六 | 王五 |
----------------mysql子连接查询按子查询出现在主查询中的不同位置分 select后面仅仅支持标量子查询。from后面支持表子查询。where或having后面支持标量子查询单列单行、列子查询单列多行、行子查询多列多行exists后面即相关子查询表子查询多行、多列-- select后面的子查询
-- 查询每个部门员工个数
SELECT d.*,(SELECT count(*)FROM employee bWHERE b.department_id d.department_id) AS 员工个数
FROM dept d;-- 查询员工号等于3的部门名称
SELECT (SELECT a.dname FROM dept a, employee b WHERE a.department_id b.department_id AND b.employee_id 3) AS 部门名;-- from后面的子查询
-- 查询每个部门平均工资的工资等级
-- 1先查询每个部门平均工资
SELECTdepartment_id,avg(a.salary)
FROM employee a
GROUP BY a.department_id;-- 2然后是查询薪资等级表
SELECT *
FROM job_grades;-- 3将上面2个结果连接查询筛选条件:平均工资 between lowest_sal and highest_sal;
SELECTt1.department_id,avg_salary AS 平均工资,t2.grade_level
FROM (SELECTdepartment_id,avg(a.salary) avg_salaryFROM employees aGROUP BY a.department_id) t1, job_grades t2
WHEREt1.avg_salary BETWEEN t2.lowest_sal AND t2.highest_sal;-- where和having后面的子查询
-- 查询谁的工资比javaboy的高
-- 1查询lisi的工资
SELECT salary FROM employee WHERE e_name lisi;-- 2查询员工信息满足salary上面的结果
SELECT *
FROM employee a
WHERE a.salary (SELECT salaryFROM employeeWHERE e_name lisi);--having后的子查询
--查询最低工资大于40号部门最低工资的部门id和其最低工资
-- 1查询40号部门的最低工资
SELECT min(salary)
FROM employee
WHERE department_id 40;--2查询每个部门的最低工资
SELECTmin(salary),department_id
FROM employee
GROUP BY department_id;--3③在②的基础上筛选满足min(salary)①
SELECTmin(a.salary) minsalary,department_id
FROM employee a
GROUP BY a.department_id
HAVING min(a.salary) (SELECT min(salary)FROM employeeWHERE department_id 50);
