我的Mysql数据库入门笔记——常用基础命令及案例

视频课件：B站如鹏网MySQL基础教程

页面布局

刚开始先手工创建表，代码创建表的命令在末尾给出
新建表（后续修改时右键→设计表Design Table）

双击编辑表格数据

输入MySQL语句：查询Qurery→新建查询New

Mysql语句基础

1.案例一

***新建表t_students***
select * from t_students
Insert into t_students(Id,Gender,Name,Hobbies) values(4,1,'汪峰','摇滚')
Insert into t_students(Id,Gender,Name) values(6,1,'那英')
#先设置id自动增长
Insert into t_students(Gender,Name) values(1,'杨坤')
Insert into t_students(Gender,Hobbies) values(1,'我就不为空')

update t_students set age=30
update t_students set age=50,name='tom'
update t_students set age=age+5
update t_students set age=10 where name='tom'
update t_students set age=30 where name='tom' or age<25
#or、and、not
update t_students set age=30 where (age>20 and age<30) or(age=80)

#不加where删除所有数据但是表还在,drop table t_name删除表
delete from t_students where age>20 or age<=10

2.案例二

***#新建表t_employees***
insert into t_employees(number,name,age,salary) values('DEV001','Tom',25,8300);
insert into t_employees(number,name,age,salary) values('DEV002','Jerry',28,9300.80);
insert into t_employees(number,name,age,salary) values('SALES001','John',23,5000);
insert into t_employees(number,name,age,salary) values('SALES002','Kerry',28,6200);
insert into t_employees(number,name,age,salary) values('SALES003','Stone',22,1200);
insert into t_employees(number,name,age,salary) values('HR001','Jane',23,2200.88);
insert into t_employees(number,name,age,salary) values('HR002','Tina',25,5200.36);
insert into t_employees(number,name,age,salary) values('IT001','Smith',28,3900);
insert into t_employees(number,age,salary) values('IT002',28,3900);

select * from t_employees
select name from t_employees
select name as 姓名,age as 年龄 from t_employees
select name ,age+1,age from t_employees
select * from t_employees where age>25
select * from t_employees where age>25 or salary >5000

select 1+1
select now()

#基本函数
select max(salary) from t_imployees where age<25
select max(salary) as 最高,min(salary) as 最低工资 from t_employees where age>=25
select count(*) from t_employees 
select sum(salary),avg(salary) from t_employees 

##排序order by
#按照年龄升序排列，默认升序，desc降序排列
select * from t_employees order by age asc
#order by放最后面
select * from t_employees order by age desc,salary desc
select * from t_employees where age>25 order by age desc,salary desc
##like匹配，但是性能差效率低
#一个下划线表示一个字母
select * from t_employees where name like 't__' 
#%匹配任意字符
select * from t_employees where name like 't%'
select * from t_employees where name like '%y'
select * from t_employees where name like '%n%'

#空值处理null，空值表示‘不知道’，不等于‘不存在’，1+null=null
select * from t_employees where name is null

#limit 首行行号 行数，只适用于MySQL，放在order by之后
select * from t_employees where name is not null order by salary limit 2,3

#group by
select age,ave(salary),min(salary),max(salary) from t_employees group by age
select age,count(*) from t_employees group by age

3.案例三
##表间连接join

***新建表t_customers、t_ordertypes、t_orders***
insert into t_customers(id,name,age) values(1,'tom',21);
insert into t_customers(id,name,age) values(2,'nike',24);
insert into t_customers(id,name,age) values(3,'jack',30);
insert into t_customers(id,name,age) values(4,'tom',25);
insert into t_customers(id,name,age) values(5,'linda',null);

insert into t_ordertypes(id,name) values(1,'现货订单');
insert into t_ordertypes(id,name) values(2,'预订订单');
insert into t_ordertypes(id,name) values(3,'预购订单');
insert into t_ordertypes(id,name) values(4,'内部');


insert into t_orders(id,number,price,customerld,typeld) values(1,'kOO1',100,1,1);
insert into t_orders(id,number,price,customerld,typeld) values(2,'KOO2',200,1,1);
insert into t_orders(id,number,price,customerld,typeld) values(3,'TOO3',300,1,2);
insert into t_orders(id,number,price,customerld,typeld) values(4,'NOO2',100,2,2);
insert into t_orders(id,number,price,customerld,typeld) values(5,'NOO3',500,3,4);
insert into t_orders(id,number,price,customerld,typeld) values(6,'TOO1',300,4,3);
insert into t_orders(id,number,price,customerld,typeld) values(7,'TOO2',100,NULL,1);

select o.number,o.price,c.name,c.age 
from t_orders o left join t_customers c 
on o.customerld=c.id

select o.number,o.price,o.customerld,c.name,c.age
from t_orders o 
left join t_customers c 
on o.customerld=c.id
where o.price>=150

select o.id 订单id,c.id 客户id,o.number 订单编号,o.price 订单价格,t.name 订单类型,c.name 客户姓名
from t_orders o 
left join t_customers c on o.customerid=c.id
left join t_ordertypes t on o.typeid=t.id

#也可用循环（效率低下）
while select * from t_orders
{
	string customername=select name from t_customers where id=customerid,
  string typename=select name from t_ordertypes where id=type;
}

表间连接时设置外键约束，防止删除导致逻辑混乱：

4.案例四

#代码创建表
create table MyClass(
 id int(4) not null primary key auto_increment,
	name char(20) not null,
	sex int(4) not null default '0',
	degree double(16,2)
);
#复制表
create table 2_myclass select * from myclass
#重新创建索引
alter table 2_myclass  add index contentID_index(contentID);
#查看索引
show keys from 2_table  
#创建主键和自动增加
alter table 2_table modify id int(10) primary key  auto_increment ;