mysql基础知识

MySql基础知识#

1.创建数据库：

create database mydata;(你所要建的数据库名)

2.使用数据库

use mydata;(说明：在使用数据库的前一定要加这个use语句，否则会出现没有数据库被选择）

3.查看所有数据库

show databases;

4.查看该数据库的所有的表名

show tables;

5.删除数据库

 drop database mydata;(直接删除数据库，不检查是否存在）
 drop database if exists mydata;(删除数据库，如果存在的话，可以避免报错）

6.创建数据库的表

create table users(
id int auto_increment primary key,
username varchar(50) not null,
email  varchar(100) not null,
dldate  date
);

说明：id: 用户 id，整数类型，自增长，作为主键。dldate:用户登录时间，日期类型。

7.插入数据

insert into users values(1001,'kiki','3569865878@qq.com','2003-1-6');

8.查询数据

查询用户的所有的信息

 select * from  users;

从用户的表中查询特定列的所有行

 select username,email from users;

添加 ORDER BY 子句，按照某列的升序排序

select * from users order by dldate;

添加 ORDER BY 子句，按照某列的降序排序

select * from users order by dldate desc;

9.where子语句

选择月收入大于5000的职员

select * from  employee where salary>=5000;

模糊查询用like。__:匹配任意一个字符,% ：用于匹配0个或者多个字符
该语句的意思是：选择所有姓氏以k开头的顾客：

select * from customers where xing like 'k%';

In关键字
可以在查询中测试某个列的值是否等于列表中的任何一个值。
该语句的意思是查找所有来自美团的订单,其中有一个订单表 Orders 和一个公司信息表 Companies。

select * from orders where company_id in(select id from companies where company_name='美团';

between条件

select * from  orders where order_date between '2023-1-1'and '2024-5-9'

10.update语句

update 语句可用来修改表中的数据,
update 表名称 set 列名称=新值 where 更新条件;

将邮箱为12356@qq.com的姓名改成“小王”，职位改为主管

update employee set name="小王", status="主管" where email="123456@qq.com"

将所有人的工资加一千元：

update employee set salary=salary+1000;

11.delete语句

delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除。
delete from 表名称 where 删除条件;
删除 id 为 007 的员工信息:

 delete from employee where id=007;

删除所有年龄小于 21 岁的数据:

delete from employee  where age<20;

删除表中的所有数据:

delete from employee;

12.union语句

UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）：
SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称；

select country from website union select country  from  apps order by country;
SELECT country FROM website：这个查询从 website 表中选择 country 列的所有值。

UNION：将上述查询的结果与下一个查询的结果合并。

 SELECT country FROM apps：这个查询从 apps 表中选择 country 列的所有值。

ORDER BY country：对最终合并后的结果集按照 country 列的值进行升序排序。
UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）：

SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称；

13.Group语句

1.GROUP BY 语句根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。它能够提供有用的汇总信息。
2.使用 GROUP BY 语句 将数据表按名字进行分组，并统计每个人有多少条记录：

 select name,count(*) from employee group by name;

14.创建用户itcast,只能在当前主机localhost访问，密码123456

create user 'itcast'@'localhost' identified by '123456';

创建用户haha，可以在任意主机访问该数据库，密码123456

create user 'haha'@'%' identified by '123456';

修改用户haha的密码为0123；

alter user 'haha'@'%' identified with mysql_native_password by '0123'

删除用户

drop user 'haha' @'主机名';

15.权限控制
查询权限：

show grants for 'itcast'@'localhost';

授予权限：

grant all on itcast.* to 'itcast'@'localhost';

撤销权限：

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';     

16.字符串
concat:字符串拼接

select concat ('hello','mysql');

lower,upper转小写，和大写：

select lower('HELLO');

lpad,rpad左填充，右填充

select lpad('01',5,'-');(输出结果是---01）

trim的作用去掉首尾的空格

select trim ('hello word');

17.数值
ceil向上取整：

select ceil(2.2);输出结果3

floor向下取整：

select floor(2.2);

round四舍五入：

 select round(2.345,2);输出结果2.35

通过数据库的函数，生成一个六位数的随机验证码

select lpad(round(rand()*1000000,0),6,'0');

18.日期时间函数

 select curdate();(当前日期）

 select curtime();(当前时间，不包括日期）

 select now();(既有当前时间，又有日期）

 select date_add（now(),INTERVAL 70 YEAR);往后推迟七十年。

 select dateiff('2020-10-4','2020-10-5');(两个指定日期相差的天数，说明：第一个时间减去第二个时间）

查询所有员工入职天数，并根据员工入职天数倒序排序：

select name,dateiff('curdate',entrydate) as 'entrydays' from emp order by entrydays desc;

as的用法：

select name as '手机支架'~;用法旧列名as新列名

select * ,(price* discount) as '实际价格' from cellphone;(列名1运算符列名2as新列名）

19.流程函数

isfull函数(如果第一个是null值，则返回default,如果是’',则返回空值：

select isfull(null,'default')//返回default

case(当）when(是）then(返回） else end
查询emp表的员工姓名和工作地址（北京，上海一线，其他二线）

select
  name,
  (case workadress when '北京' then '一线城市' 
  when '上海' then '一线城市' else '二线城市' end) 
  as '工作地址' from emp;

20.约束
如何根据规定或者约束创建表，详细解答：

create table user{
id int primary key auto_increment comment'主键'，
name varchar(10) not null unique comment'姓名',
age int check( age>0 &&age<=120 coment'年龄',
status char(1) default '1' comment'状态',
 gender char(1) comment '性别'
 )comment '员工信息表';

如何给表添加外键：
alter table 表名 add constraint 外键名称 foreign key (外键字段名） reference 主表（主表列名）;
如何删除外键：
alter table 表名 drop foreign key 外键名称;
21.内连接外连接
隐式内连接演示：

select emp.name,dept.name from emp,dept where emp.dept_id=dept.id;
==
select e.name,d.name from emp e,dept d where e.dept_id=d.id;

显示内连接演示：
select 字段列表 from 表1（inner)join 表2 on 连接条件…；

select e.name,d.name from emp e inner join dept dept d
on  e.dept_id=d.id;  

外连接：
左外连接：
select 字段列表 from 表1 left[outer] join 表2 on 条件…;
1.查询emp表的所有数据，和对应的部门信息

select e.*,d.name from emp e left outer join dept d on e.dept_id=d.id;

右连接：
2.查询dept表的所有数据，和相对应的员工信息：

select d.*,e.* from emp e right outer join dept d on e.dept_id=d.id;

自连接：
select 字段列表 from 表A 别名A join 表A 别名B on 条件
1.查询员工及其所属领导的名字：

 select a.name ,b.name from emp a, emp b where a.id=b.manage_id;

2.查询所有员工emp及其领导的名字emp,如果员工没有领导，也需要查询出来。

 select a.name '员工',b.name '领导' from emp a left join emp b on a.manage=b.id;

22.子查询
列查询：查询比财务部所有人工资都高的员工信息
1.查询所有财务部人员工资

select id  from dept where name='财务部';
select salary from  emp  where dept_id=( select id  from dept where name='财务部')

2.比财务部所有人工资都高的员工信息

select * from emp where salary >all(select salary from  emp  where dept_id=( select id  from dept where name='财务部');

表子查询：

1.查询与"lili","haha"的职位和薪资相等的员工信息；
首先是查询叫这两个名的职位和薪资，其次查询与之相等的员工信息。

select * from emp where (job,salary)in (select job,salary from emp where name='lili' or name='haha');

2.查询入职日期是“2026-01-01”之后的员工信息，及其部门信息
首先是查员工信息，再查询部门信息：

select e.*,d.* from (select * from emp where entrydate>'2006-01-01') e left join dept d on  e.dept_id=d.id;

练习
1.查询低于本部门平均工资的员工信息
*首先是先查询指定部门的平均薪资的员工信息

select avg(e1.salary) from emp e1 where e1.dept_id=1;

*其次是查询低于本部门的员工信息。

select *,(select avg(e1.salary) from emp e1 where e1.dept_id=1) as '平均' from emp e2 where e2.salary<select avg(e1.salary) from emp e1 where e1.dept_id=1;

2.查询所有的部门信息，并统计部门的员工人数

select count(*) from emp where dept_id=1
select d.id,d.name,(select count(*) from emp  e where e.dept_id=d.id)    as '人数'from dept d;

23.事务

select @@autocommit;–自动提交

set @@autocommit = 0; – 设置为手动提交

commit;—提交事务
rollback;—回滚事务//程序执行出错则利用回滚事务。
– 方式二
– 转账操作 (张三给李四转账1000)
start transaction ;

– 1. 查询张三账户余额
select * from account where name = ‘张三’;

– 2. 将张三账户余额-1000
update account set money = money - 1000 where name = ‘张三’;

– 3. 将李四账户余额+1000
update account set money = money + 1000 where name = ‘李四’;

– 提交事务
commit;