增删改查

create table 表名();
insert into 表名(字段名1, 字段名2) values (‘更改值1’, ‘更改值2’);
update 表名 set 字段名1 = ‘xxx’, 字段名2 = ‘xxx’ where 限制条件(如stuid = 2);
delete from 表名 where 限制条件;
select 字段名1, 字段名2 from 表名 where 限制条件;
truncate 表名;
delete from 表名;
drop table 表名;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# 新建表格
CREATE TABLE student(
	stuid int not null auto_increment PRIMARY KEY,# 非空 自增 主键
	stuname VARCHAR(200), #200个字符
	stustatus VARCHAR(100)
);

CREATE TABLE grade(
	gradeid int not null auto_increment primary KEY,
	stuid int not null ,
	gradecourse varchar(100),
	gradescore varchar(100)
);
# 插入
insert into student(stuname,stustatus) values ('zhangsan','Normal')
# 插入多行数据
insert into student(stuname,stustatus) values ('lisi','Normal'),('wangwu','Out'),('lcz','Super')
insert into grade(stuid,gradecourse,gradescore)values(2,'English','A')
# 删除
delete from student where stuid=1;#删除表格student里stuid为1的一行
delete from student #删除student表格里面所有的数据
# 修改
update student set stuname ='lihua',stustatus='Out' where stuid=5;
# 查询
select *from student ;
select * from student where stuid =5;
select stuname,stustatus from student where stuid =5;
select count(*) from student;

select stuname ,student.stuid ,gradescore,44gradecourse from student, grade where student.stuid =5;

truncate table student;#清空数据，重置自增字段
delete table student;#清空数据，不会重置自增字段
drop table student;#删除表

查询进阶

使用notrhwind数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
#northWind
#1、查询员工中是销售经理的人
select * from employees where Title ='Sales Manager'
#2、查询1994年雇用的员工
select * from employees where HireDate>="1994-01-01"and HireDate<"1995-01-01"
#3、按工龄由大到小排列员工信息
select *from employees order by HireDate;
#从小到大
select *from employees order by HireDate DESC;
#4、查询家住London的员工
select *from employees where City='London';
#5、查询还没有标明地区的员工
select *from employees where Region is NULL;
#6、按以下格式查询家住Seattle的员工并按降序排列name字段
#	employeeId    name=firstName lastName    '他的职务是'    Title    address    city
select employeeID,concat(FirstName,' ',LastName) name,Title ,Address,City FROM employees where city ='Seattle' order by name DESC ;
#7、查询50年代出生的员工并按员工编号排序
select * from employees where BirthDate<'1960-01-01' and BirthDate>='1950-01-01'order by EmployeeID 
#8、查询50年代出生的并在1993年被雇用的员工并按雇用年限由小到大排序
select *from employees where BirthDate<'1960-01-01' and BirthDate>='1950-01-01' and  HireDate>='1993-01-01'and HireDate<'1994-01-01' order by HireDate desc ;
#9、查询employees中不在‘London’、’ Seattle’、’ Tacoma’居住的员工。（northWind）
select *from employees where City not in ('London','Seattle','Tacoma')
#10、查询员工名字中最后一个字母是t的员工
select *from employees where FirstName like '%t'
#11、查询员工名字中以A开头的员工
select *from employees where FirstName like 'A%'
#12、查询员工头衔中不以S开头的员工并按firstName排序
select *from employees where Title not  like'S%' order by FirstName 
#13、查询住在London或者Seattle的员工
select *from employees where  City='London' or  City='Seattle';
select *from employees where  City in('London' ,'Seattle');
#14、查询lastName中或者有A或者有x的员工 
select *from employees where LastName like '%A%' or LastName like'%k%'
# 查询1996-7哪个员工，和哪个公司在哪个日期 签订了哪些订单（northwind : orders,employees, customers） 
select e.EmployeeID,concat(FirstName,' ',LastName) name ,CompanyName,OrderID
from orderdetails o,employees e,customers c
where e.EmployeeID =o.EmployeeID and o.CustomerID=c.CustomerID and=order by 

# 查询订单编号为10253的订单中所定产品的名称、单价、数量（northwind : order details,products) 


# 查询订单编号为10254的订单是哪个员工签订的（northwind : orders,employees） 


# 查询订单编号为10255的订单日期、员工姓名、客户公司名称（northwind : orders,employees，customer） 


# 查询客户公司名称为“Around the Horn“在1997-11月份的订单（northwind : orders,customer）

常用函数

字符串函数

时间函数

计算函数

Regex正则

多表查询

C#对数据库操作