MySQL基本语法

最新推荐文章于 2024-09-12 12:42:46 发布

须佐能乎！

最新推荐文章于 2024-09-12 12:42:46 发布

阅读量249

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1.SQL简介
	结构化查询语言
		（层次模型，网状模型,关系模型）

		关系模型是目前的主流  （Oralce，mysql    mssql ）

	SQL标准：ANSI  （1992  1997   2002   ISO）

	方言与Sql 标准   
		学习者：SQL标准
		方言：  只支持自己当前的数据库

2.常用数据库
	Oracle MySQL  SQL Server  SQLite

	MySQL(重点)

	安装
	   Client---->Server------>库------>表
3.建库
	1.登录MySQL
		mysql -u root -p

	2.创建数据库
	*** create database 数据库名;
	 create database day14;

	 --创建数据库时指定字符集
	 create database day14  character set gbk;

	3.查看服务器上所有的数据库
	 ****  show databases;
	4.查看创建数据库的代码
	  show create database 数据库名;
	  show create database day13; 

	5.修改某个数据库的编码
	  alter database 数据库名  character set 字符编码  COLLATE 字符集的校验规则;
	  alter database day13  character set utf8 collate utf8_general_ci;

	6.删除数据库
	****  drop database 数据库名;
	  drop database day13;

	 7.使用某个数据库  (将来建表都放在这个库中)
	 ****  use 数据库名;
	   use day13;
4.建表
	  1.建表
	  create table 表名(
	      字段名（列名）  数据类型，
	      字段名（列名）  数据类型,
	      字段名（列名）  数据类型
	  );

	  float(5,2):代表总长度是5,其中小数位数是2位，整数位数是3位

	  示例：  create table employee(
		    id int,
		    name varchar(20),
		    gender char(4),
		    birthday datetime,
		    entry_date datetime,
		    job varchar(100),
		    salary float(7,2),
		    resume longtext
		);
	   
	  2.
	  显示当前数据下所有的数据表
	  show tables;

	  3.
	  查看创建表的代码
	  show create table 表名;
	  show create table employee;

	  4.查看表的明细，以列表的形式显示出结果
	  desc 表名;
	  desc employee;
	  describe employee;

  5.修改表的列
	1.添加一列
	alter table  表名  add 列名  类型;

	2.修改某列的数据类型
	alter table  表名 modify 列名  新的数据类型;

	3.删除某列
	alter table 表名  drop 列名;

	4.修改表名
	rename table 旧表名 to 新表名;

	5.修改表的字符集
	alter table 表名 character set 字符集编码

	6.修改列名
	alter table 表名  change column 旧列名 新列名 新的数据类型;

	7.删除表结构
	drop table 表名;


	1.加一个列image
		alter table employee add image longblob;
	2.修改job列，使其长度为60
		alter table employee modify job varchar(60);
	3.删除image列
		alter table employee drop image;
	4.表名改为user
		rename table employee to user;

	5.修改表的字符集为gbk
		alter table user character set gbk;

	6.列名name修改为username
		alter table user change column name username varchar(30);




ALTER  CREATE  DROP:   DDL语言(数据定义语言)

INSERT UPDATE DELETE :DML语言(数据操作语言)

SELECT              :DQL语言(数据查询语言)
select * from employee;

5.CRUD操作
	1.插入：
	insert into 表名[(列名1，列名2)] values(值1，值2);
	  字符和日期型数据应包含在单引号中。
	 insert into employee (id,name,gender,birthday,salary,entry_date,resume) 
	 values(1,'cgx','男','1982-10-1',10000,'2000-10-1','服务业干得不错');

	  insert into employee 
	 values(2,'aj','女','1982-10-1','2000-10-1','演员',100,'服务业干得不错');

	 2.处理添加及查询数据的乱码问题
	 show variables like 'character%';
	 set character_set_client=gbk;//处理客户端向服务器提交数据的编码
	 set character_set_results=gbk;//处理服务器响应在客户端中的结果的编码

	 2.
	 删除数据
	 delete from 表名 【where条件】
	 delete from employee where name='2阿娇';

	 delete from employee;//清除表中所有记录
	 truncate table employee;//清除表中所有记录

	 区别：
		1.truncate table 删除数据效率更高(先摧毁整个表结构，再重建表)
		2.delete 删除所有记录时，一条一条往后删除
		3.truncate table只能删除所有
		4.delete可以选择性的删除部分记录

	3.更新
	update 表名 set 列名=值，列名2=值2 where 条件;
	update employee set name='2阿娇',gender='男' where name='aj';


	4.查询
	select 列名 [as] 改列名,列名 from 表名 【 where 条件 order by 排序字段 （ASC(默认值)升序 |  DESC）】
	用*代表所有列
	distinct可以消除重复数据(查询时)
	order by 如果指定了多个排序字段，优先按第一个字段排，如果第一个字段取值相同时，才考虑第二个字段排序
	

	示例：
		查询表中所有学生的信息。
			select * from student;
		查询表中所有学生的姓名和对应的英语成绩。
			select name,english from student;
		过滤表中重复数据(英语成绩（同一个成绩只显示一次）)。
			select distinct english from student;
		在所有学生数学分数上加10分特长分。(显示)
			select name,math+10 from student;
			select name as 姓名,math+10 as 数学成绩 from student;
		统计每个学生的总分。
			select name,chinese+math+english from student;
		使用别名表示学生分数。
			select name 姓名,chinese+math+english 总分 from student;

		排序：order by 排序字段 （asc (默认升序)     desc降序）
			使用别名表示学生分数，并按总分从高到低排
			select name 姓名,chinese+math+english 总分 from student order by 总分 desc;

		查询姓名为王五的学生成绩
			select * from student where name='王五';
		查询英语成绩大于90分的同学
			select * from student where english>90;
		查询总分大于200分的所有同学
			select * from student where (chinese+math+english)>200 ;

	
	运算符
		(<>  !=)
		BETWEEN  ...AND...  是一个闭区间（含头含尾）
		in(set)  指的是在一个集合中取元素都可以
		Like语句中，% 代表零个或多个任意字符，_ 代表一个字符，例first_name like ‘_a%’

		IS NULL；代表某列取值是否为null    select * from student where name is null;

		and  or not

		示例：
			查询英语分数在 80－90之间的同学。
				select * from student where english between 80 and 90;
				select * from student where english>=80 and english<=90;
			查询数学分数为89,90,91的同学。
				select * from student where math in (89,90,91);
				select * from student where math=89 or math=90 or math=91;
			查询所有姓李的学生成绩。
				select * from student where name like '李%';
			查询数学分>80，语文分>80的同学。
				select * from student where math>80 and chinese>80;



	排序：
		示例：
			对数学成绩排序后输出。
				select * from student order by math;

				select * from student order by math asc ,第二个排序字段  (asc|desc) ;
				引入第二个排序字段 是当第一个字段取值相同时，才会启用第二个排序字段(字典顺)
				select * from student order by math asc ,name desc ;
			对总分排序后输出，然后再按从高到低的顺序输出
				select name 姓名,chinese+math+english 总分 from student order by 总分 desc;
			对姓李的学生成绩排序输出
				select * from student where name like '李%' order by chinese+math+english 


6.多表操作
	实体完整性约束：
		规定表的一行（即每一条记录）在表中是唯一的实体。实体完整性通过表的主键（primary key)来实现。
		主键的特性：唯一性，非空性
		主键的分类：物理主键，逻辑主键（业务主键）实际开发中主要用的是业务主键
create table student(
	id int primary key ,
	name varchar(20) unique not null,
	chinese float,
	english float,
	math float
);

insert into student(id,chinese,english,math) values(1,89,78,90);
insert into student(id,chinese,english,math) values(4,67,98,56);
insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);
insert into student(id,name,chinese,english,math) values(4,'李一',88,98,90);
insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45);
insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);


auto_increment:只针对这一列的类型是整数，代表自动增长（1，2，3，4，5依次增长）
	:它应该与primary key 配套使用
create table score(
  id int primary key auto_increment,
  english float(5,2)
);

insert into score(english) values(67);
insert into score(english) values(89);
insert into score(english) values(98);

delete from score where id =3;
insert into score(english) values(100);#  id从4开始  如果又要从1开始自动增长，就可以先删除表，再进行数据添加


数据冗余：数据重复

	域完整性约束:保证某个字段符合要求
		not null
		unique:唯一 (对于null值无效)

		create table orders(
			id int primary key,
			orderno varchar(100) not null,
			price float(8,1)
		);
		insert into orders(id,orderno,price) values(1,null,1000);

		drop table orders;
		create table orders(
			id int primary key,
			orderno varchar(100) unique ,
			price float(8,1)
		);
		insert into orders(id,orderno,price) values(1,'123',1000);
		insert into orders(id,orderno,price) values(2,null,1000);
		insert into orders(id,orderno,price) values(3,null,4000);#可以添加进去，对于null值无效
		解决问题？可以对这一列同时添加unique及not null
		drop table orders;
		create table orders(
			id int primary key,
			orderno varchar(100) unique not null,
			price float(8,1)
		);
		insert into orders(id,orderno,price) values(1,'123',1000);
		insert into orders(id,orderno,price) values(2,'123',1000);

	参照完整性约束
		问题？成绩表中出现了一个成绩（它的学号是在学生表没有）
		       订单（订单的客户编号   应该在客户表中能找到这样的编号）
		解决办法 ：给表添加外键
			建表时添加外键：constraint 外键名 foreign key(外键字段名) references 主键表（主键字段） 
		先建表再添加外键：
		alter table 表名 add constarint FK_employee_dept_id foreign key(dept_id) references departement(id)

		表与表之间的关系:
		一对一 （一个人只能有一个身份证号码）
		一对多（一个顾客可以下多个订单，一个订单只能一个顾客下）  （开发中用的最多）
		create table cust(
			id int primary key,
			name varchar(20) not null
		);

		insert into cust values(1,'cgx');
		insert into cust values(2,'aj');
		insert into cust values(3,'zxc');
		
		drop table orders;
		customerid不能随意编写，它应当来自于cust表中的id这一列的取值
			customerid也称为外键列（外键字段）
		create table orders(
			id int primary key,
			orderno varchar(100) ,
			totalprice float(7,2),
			customerid int ,
			constraint FK_orders_customerid foreign key(customerid) references cust(id)
		);
		insert into orders values(1,'wqewq234324',1000,4);#出错
		insert into orders values(1,'wqewq234324',1000,3);#成功

		delete from cust where id=3;#删除失败，原因是它现在有关联的子项，当把父项删除后，关联子项就
						找不到父项了，所以删除失败
		解决：
			一、先将子项清除，再删除自己
			    delete from orders where customerid=3;
			    delete from cust where id=3;

			二、解除外键
				alter table 表名  drop foreign key 外键名;
				alter table orders drop foreign key FK_orders_customerid;

				 delete from cust where id=3;


		多对多  （一个老师可以教多个学生，一个学生也可以被多个老师教）
			用中间表来描述关系：
				联合主键：primary key(字段1，字段2）



连接查询：
	交叉连接（cross join）:不带on子句，返回连接表中所有数据行的笛卡儿积。
	select * from A cross join B;(显式交叉连接)
	select * from A ,B;           (隐式交叉连接）


	内连接（inner join）：返回连接表中符合连接条件及查询条件的数据行。
	select * from A inner join B on (连接条件) where 条件
	示例：
		查询陈冠希的所有订单
		select * from customer A inner join orders B on(A.id=B.customer_id) where A.name='陈冠希';
		查询所有下过订单的人员及订单信息
		select * from customer A inner join orders B on(A.id=B.customer_id)


	外连接：分为左外连接（left out join）、右外连接（right outer join）。与内连接不同的是，外连接不仅返回连接表中符合连接条件及查询条件的数据行，也返         回左表（左外连接时）或右表（右外连接时）中仅符合查询条件但不符合连接条件的数据行。
	select * from A left join B on (连接条件） where条件
	特点：左表中能连接上右表的记录显示出来，还会将左表中连接不上的其它所有记录也显示出来
	示例：
		select * from customer left join orders on(customer.id=orders.customer_id) ;

	select * from A right join B on (连接条件） where 条件
	特点：将右表中的记录都要保留下来
	示例：
		select * from customer right join orders on(customer.id=orders.customer_id) ;
		select * from orders right join customer  on(customer.id=orders.customer_id) ;

子查询
	select * from 表名 where 字段名 in (select 字段名 from 表名  where条件);  (放在外面的查询叫父查询，放在内部的查询叫子查询)
	子查询可以出现在select ,from ,where部分。
	示例：查询陈冠希的所有订单
	select id from customer where name='陈冠希';
	select id,order_number,price,customer_id from orders 
	         where customer_id in (select id from customer where name='陈冠希');
联合查询
	select * from A 
	union
	select * from B
	将A表中的记录与B表中的记录合并在一起，并消除重复记录

	select * from orders where price>200
	union
	select * from orders where customer_id in (select id from customer where name='陈冠希');

报表查询
	select * from 表名 where 条件  group by 分组字段 having(分组后的条件)

	聚合函数：
		count():统计个数
		sum()：求和
		avg()：求平均值
		max()最大值
		min()最小值

	示例：
	统计一个班级共有多少学生？
	select count(*) from student;
	统计数学成绩大于等于90的学生有多少个？
	select count(*) from student where math>=90;
	统计总分大于250的人数有多少？
	select count(*) from student where (math+chinese+english)>250;
	求一个班级数学平均分？
	select avg(math) from student;

	求一个班级总分平均分
	select avg(math+chinese+english) from student;
	select sum(math+chinese+english)/count(*) from student;

	求班级最高分和最低分
	select max(math+chinese+english) 最高分,min(math+chinese+english) 最低分 from student;


	对订单表中商品归类后，显示每一类商品的总价
	select product ,sum(price) from orderss group by product ;
	查询购买了几类商品，并且每类总价大于100的商品
	select product ,sum(price) from orderss   group by product having (sum(price)>100);

	
7.数据备份与还原
	1.数据库备份：
	   WINDOWS命令行：
		mysqldump -h localhost -u root -p[也可以加密码] day14>d:/day14.sql

	2.还原
		1.WINDOWS命令
		mysql -u root -psorry 数据库名（要事先存在）<test.sql（sql文件所在位置）


		2.MYSQL命令
		  1.确保数据库存在
		    create database day14;
		  2.source sql文件所有位置
		    source d:/mysql.sql;

DCL
1　创建用户
语法：
CREATE USER 用户名@地址 IDENTIFIED BY '密码';
CREATE USER user1@localhost IDENTIFIED BY ‘123’; 
CREATE USER user2@’%’ IDENTIFIED BY ‘123’; 

2　给用户授权
　　语法：
GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@IP
GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost;
GRANT ALL ON mydb1.* TO user2@’%’;

3　撤销授权
　　语法：
　　REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名
REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

4　查看用户权限
语法：
SHOW GRANTS FOR 用户名
SHOW GRANTS FOR user1@localhost;

5　删除用户
语法：
DROP USER 用户名
DROP USER user1@localhost;

6　修改用户密码
语法：
Use mysql;
UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’;
FLUSH PRIVILEGES;
UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2';
FLUSH PRIVILEGES;