# 本地
1、启动 mysql-->进入D:\mysql-8.0.19-winx64\bin目录下
D:\mysql-8.0.19-winx64\bin>net start mysql # 如果启动报错，管理员身份启动cmd
2、登录 mysql
D:\mysql-8.0.19-winx64\bin>:mysql -u root -p
Enter password:********
D:\mysql-8.0.19-winx64\bin>:mysql -h localhost -u root -p
3、退出 mysql输入界面
mysql>exit;
4、关闭 mysql
D:\mysql-8.0.19-winx64\bin>:net stop mysql
5、显示所有数据库
mysql>SHOW DATABASES:
6、创建数据库
mysql>CREATE DATABASE five; 创建数据库
mysql>CREATE DATABASE dbtest;
6.1、删除数据库
mysql>drop database dbtest; 删除数据库(全部删除，不可撤销)
7、查看正在使用的数据库
mysql>select database();
8、使用指定数据库
mysql>use five; 使用指定数据库
mysql>show tables; 查看指定数据库的所有表
mysql>desc user; 查看表的信息
mysql>show create table user \g 查看创建表的sql，命令最后不用加";"
mysql>show fields from user; 查看表格所有信息
9、创建 table
mysql>create table user(
id int primary key,
name varchar(50),
age int,
address varchar(100)
);
9.1、创建自动增加编号
mysql> create table tbtest(
id int not null primary key auto_increment,
name varchar(20),
city varchar(20),
hobby varchar(20)
);
9.2、修改表格
mysql>alter table user modify id int auto_increment primary key; 修改id为自增
mysql>alter table user add (column) data date not null; 添加列
mysql>alter table user drop data; 删除列
mysql>alter table user rename userinfo; 修改表名
10、删除 table或者数据
mysql>drop table user; (表结构和数据全部删除，不可撤销)
mysql>truncate table user; (只能操作表，将表中数据全部删除)
mysql>delete from user; (删除整张表中数据)
mysql>delete from user where name='sunshine';; （删除符合条件的数据)
11、备份数据库
mysql>INSERT INTO newuser SELECT * FROM user; 备份数据库
12、查看表中信息
mysql>SELECT * FROM user; 查看表格信息
11、向表中中插入数据
mysql> insert into user(name,age,address) values ('xxxx',20,'chongqing');
mysql>insert into user(name,age,address) values ('xxxxx',20,'chongqing');
# 选择性查找
mysql>select * from user;
mysql>select * from user where name='sunshine1';
# 反向查找
mysql>select * from user where age != 18;
# 附条件删除 row(行)
mysql>DELETE FROM tbCustomerInfo_back_up WHERE custInfoPhone = '10000000000';
mysql>DELETE FROM tbCustomerInfo_back_up WHERE custInfoFirstName = 'William2' ADN custInfoLastName = 'Jiamin2';
# 贴 index 的方法
mysql>SHOW FIELDS FROM tbCustomerIDInfo;
mysql>create index indexcustidinfo on tbcustomeridinfo (custid); 贴标签
mysql>create index indexcustnameinfo on tbcustomeridinfo (custinfofirstname,custinfolastname);
# 自动增加编号
mysql>show databases;
mysql>use dbcustomerinfo;
mysql>show tables;
mysql>create table tbcustomerid_auto(id int primary key auto_increment,name varchar(20));
mysql>show fields from tbcustomerid_auto;
mysql>insert into tbcustomerid_auto(name) values ('William');
mysql>insert into tbcustomerid_auto(name) values ('Tony');
mysql> insert into tbcustomerid_auto(name) values ('Jery');
mysql>insert into tbcustomerid_auto(name) values ('Mary');
mysql>select * from tbcustomerid_auto;
# 调整开始的数
mysql>alter table tbcustomerid_auto auto_increment=10000;
mysql>insert into tbcustomerid_auto (name) values ('I_am_soooo_IMP1');
mysql>insert into tbcustomerid_auto (name) values ('I_am_soooo_IMP2');
# sql 自带 count 函数
mysql>select count(*) from tbcustomerinfo;
mysql>select count(distinct custinfofirstname) from tbcustomerinfo; # distinct 去重
mysql>select count(custinfolastname) from tbcustomerinfo where custinfolastname = 'lexueoude';
# 创建员工薪酬表
mysql> create table tbstaffsalary(staffid int primary key auto_increment,name,
name varchar(30),
staffage int,
staffsalary int);
mysql>show fields from tbstaffsalary;
mysql>insert into tbstaffsalary(name,staffage,staffsalary) values('William',18,1);
mysql>select * form tbstaffsalary;
mysql>insert into tbstaffsalary(name,staffage,staffsalary) values('Wangcongcong',25,900000000);
mysql>insert into tbstaffsalary(name,staffage,staffsalary) values('Normal People',27,8000);
mysql>insert into tbstaffsalary(name,staffage,staffsalary) values('Normal People2',24,9000);
mysql>insert into tbstaffsalary(name,staffage,staffsalary) values('Normal People3',23,5000);
mysql>insert into tbstaffsalary(name,staffage,staffsalary) values('Normal People4',30,10000);
mysql>insert into tbstaffsalary(name,staffage,staffsalary) values('Normal People5',45,18000);
# 员工薪酬表运算
mysql>select count(*) from tbstaffsalary;
mysql>select avg(staffage) from tbstaffsalary;
mysql>select count(*),avg(staffage) from tbstaffsalary;
mysql>select count(*),avg(staffage),avg(staffsalary) from tbstaffsalary;
# like 模糊查找
mysql>select count(*),sum(staffsalary),avg(staffsalary) from tbstaffsalary where name like 'normal%';
mysql>select count(*),sum(staffsalary),avg(staffsalary) from tbstaffsalary where name like '%mal%';
mysql>select count(*),sum(staffsalary),avg(staffsalary) from tbstaffsalary where name like '%g';
# 创建 view
mysql>create view williamview as select count(*),avg(staffsalary),sum(staffsalary) from tbstaffsalary where staffsalary > 1;
mysql>select * from williamview;
mysql>show tables;
mysql>create view williamview2 as select count(*),avg(staffsalary),sum(staffsalary) from tbstaffsalary where staffsalary < 50000;
mysql>select * from williamview2;
# join 的准备工作移动编号
mysql>alter table tbcustomerinfo change custinfoid custinfoid int auto_increment not null after custinfoaddr2; # custinfoid 跟在custinfoaddr2 后面
mysql>alter table tbcustomerinfo change custinfoid custinfoid int auto_increment not null first; # 将 custinfoid 放在第一位
# join 的准备工作订单信息
mysql>create table tbcustomerorder (orderid int primary key auto_increment,
custid int,
orderdate date);
mysql>show tables;
mysql>show fields from tbcustomerorder;
mysql>insert into tbcustomerorder (custid,orderdate) values ('1',20191111);
mysql>insert into tbcustomerorder (custid,orderdate) values ('3',20201111);
mysql>insert into tbcustomerorder (custid,orderdate) values ('2',20211111);
mysql>insert into tbcustomerorder (custid,orderdate) values ('4',20251111);
# innerjoin 满足老板的要求（一一对应）
mysql>select tbcustomerorder.orderid,tbcustomerinfo.custinfolastname,tbcustomerorder.orderdate from tbcustomerorder inner join tbcustomerinfo on tbcustomerorder.custid=tbcustomerinfo.custinfoid;
# leftjoin 满足老板要求 （以左边的表格为主，右边跟其一一对应）
mysql>insert into tbcustomerinfo (custinfolastname) values ('Bill');
mysql>select * from tbcustomerinfo;
mysql>select tbcustomerorder.orderid, tbcustomerinfo.custinfolastname, tbcustomerorder.orderdate from tbcustomerinfo left join tbcustomerorder on tbcustomerinfo.custinfoid=tbcustomerorder.custid order by tbcustomerinfo.custinfolastname;
# rightjoin 满足老板要求（以右边的表格为主，左边跟其一一对应）
mysql>select tbcustomerorder.orderid, tbcustomerinfo.custinfolastname, tbcustomerorder.orderdate from tbcustomerinfo right join tbcustomerorder on tbcustomerinfo.custinfoid=tbcustomerorder.custid order by tbcustomerinfo.custinfolastname;
# fulljoin 满足老板要求 (报错)
mysql>select tbcustomerorder.orderid, tbcustomerinfo.custinfolastname, tbcustomerorder.orderdate from tbcustomerinfo full outter join tbcustomerorder on tbcustomerinfo.custinfoid=tbcustomerorder.custid order by tbcustomerinfo.custinfolastname;
# union 准备工作
mysql>insert into tbcustomerorder(custid, orderdate) values ('5',20201111);
# union 介绍
mysql>select * from tbcustomerorder union select custinfoid, custinfofirstname,custinfolastname from tbcustomerinfo;
# union 实现 fulljoin
mysql>select tbcustomerinfo.custinfolastname, tbcustomerorder.custid from tbcustomerinfo left join tbcustomerorder on tbcustomerinfo.custinfoid=tbcustomerorder.custid union
select tbcustomerinfo.custinfolastname, tbcustomerorder.custid from tbcustomerinfo right join tbcustomerorder on tbcustomerorder.custid=tbcustomerinfo.custinfoid;
# order 实现排序
mysql>select * from tbcustomerinfo order by custinfolastname desc; # 降序排列
mysql>select * from tbcustomerinfo order by custinfolastname asc; # 升序排列
mysql>select * from tbcustomerinfo order by custinfolastname desc limit 10; # 限制显示前10行的
# 找到最大值和最小值
mysql>select max(custinfoid) from tbcustomerinfo;
mysql>select custinfoid, custinfolastname from tbcustomerinfo where custinfoid =(select max(custinfoid) from tbcustomerinfo);
# select 大小写
mysql>select ucase(custinfofirstname) , lcase(custinfolastname) from tbcustomerinfo;
