MySQL基本语法

1.SQL简介结构化查询语言（层次模型，网状模型,关系模型）关系模型是目前的主流  （Oralce，mysql    mssql ）SQL标准：ANSI  （1992  1997   2002   ISO）方言与Sql 标准   学习者：SQL标准方言：  只支持自己当前的数据库2.常用数据库Oracle MySQL  SQL Server  SQLiteMySQL(重点)安装   Client---->Server------>库------>表3.建库1.登录MySQLmysql -u root -p2.创建数据库*** 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.加一个列imagealter table employee add image longblob;2.修改job列，使其长度为60alter table employee modify job varchar(60);3.删除image列alter table employee drop image;4.表名改为userrename table employee to user;5.修改表的字符集为gbkalter table user character set gbk;6.列名name修改为usernamealter 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 nullunique:唯一 (对于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 nulldrop 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 unionselect * from B将A表中的记录与B表中的记录合并在一起，并消除重复记录select * from orders where price>200unionselect * 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.sql2.还原1.WINDOWS命令mysql -u root -psorry 数据库名（要事先存在）<test.sql（sql文件所在位置）2.MYSQL命令  1.确保数据库存在    create database day14;  2.source sql文件所有位置    source d:/mysql.sql;

DCL1　创建用户语法：CREATE USER 用户名@地址 IDENTIFIED BY '密码';CREATE USER user1@localhost IDENTIFIED BY ‘123’; CREATE USER user2@’%’ IDENTIFIED BY ‘123’; 2　给用户授权　　语法：GRANT 权限1, … , 权限n ON 数据库.* TO 用户名@IPGRANT 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;