mysql的基础语法

分手后的思念是犯贱 2022-07-19 00:26 181阅读 0赞

一、SQL简介  
Structured Query Language的缩写，客户端与数据库服务器交互的通道  
SQL：ANSI（ISO核心成员）维护和管理的工业标准。  
方言：属于某个数据库独有的SQL语句  
SQL标准和方言==普通话和地方话

数据库无关（Hibernate）  
二、SQL语句的分类  
DDL：数据定义语言  
DML：数据操作语言  
DQL：数据查询语言

TPL：事务处理语言（JDBC）

DCL：数据控制语言  
CCL：指针控制语言  
三、常用的数据库  
1、Oracle：占有率很高。跨平台  
2、DB2：IBM。跨平台  
3、MySQL：C语言。  
四、MySQL数据库的安装与配置(UTF8)

Window系统下：不区分大小写  
其他系统：严格区分大小写

验证安装是否成功：  
c:/>mysql -h localhost -u root -p  
默认有三个库：  
information\_schema  
mysql  
test(空库)

约定：  
c:/>window命令  
mysql>SQL语句（进入数据库）

五**、DDL数据定义语言（Data Definition Language）**

作用：用于描述数据库中要存储的现实世界实体的语言。即创建数据库和表的结构（创建索引、视图、存储过程、触发器等）。  
关键字：CREATE ALTER DROP TRUNCATE  
5.1数据库操作  
创建一个名称为mydb1的数据库。  
mysql>`CREATE DATABASE mydb1`;(默认用服务器的字符集)  
查看数据库的创建细节（可以看到使用的字符集）  
mysql>`SHOW CREATE DATABASE mydb1;`  
创建一个使用gbk字符集的mydb2数据库。  
mysql>`CREATE DATABASE mydb2 CHARACTER SET gbk;`  
创建一个使用gbk字符集，并带校对规则的mydb3数据库。  
mysql>`CREATE DATABASE mydb3 CHARACTER SET gbk COLLATE gbk_chinese_ci;`

查看当前数据库服务器中的所有数据库  
mysql>`SHOW DATABASES;`  
删除前面创建的mydb3数据库  
mysql>`DROP DATABASE mydb3;`

查看服务器中的数据库，并把mydb2的字符集修改为utf8;  
mysql>`ALTER DATABASE mydb2 CHARACTER SET utf8;`

5.2表结构操作

前提：先使用USE 数据库名 选择数据库

选择数据库  
mysql>`USE mydb1;`  
创建一个员工表

mysql>CREATE TABLE employee( id INT, name VARCHAR(100), gender VARCHAR(10), birthday DATE, entry_date DATE, job VARCHAR(100), salary FLOAT(8,2), resume TEXT );

查看表的结构  
mysql>`DESC employee;`  
在上面员工表的基础上增加一个image列。  
mysql>`ALTER TABLE employee ADD image BLOB;`  
修改job列，使其长度为60。  
mysql>`ALTER TABLE employee MODIFY job VARCHAR(60);`  
删除image列。  
mysql>`ALTER TABLE employee DROP image;`  
查看当前库中的所有表  
mysql>`SHOW TABLES;`  
表名改为user(Oracle中是关键字)。  
mysql>`RENAME TABLE employee TO user;`  
查看表的创建语句（查看编码）  
mysql>`SHOW CREATE TABLE user;`  
修改表的字符集为gbk  
mysql>`ALTER TABLE user CHARACTER SET gbk;`  
列名name修改为username  
mysql>`ALTER TABLE user CHANGE name username VARCHAR(100);`

六、**DML数据操纵语言**  
作用：用于向数据库表中***插入、删除、修改数据***  
关键字：**INSERT UPDATE DELETE**

MySQL中：*\**字符、字符串、日期、时间、日期时间这样的数据都用单引号引起来  
特殊值：NULL\*\*\*  
查看表中的所有数据  
mysql>`SELECT * FROM user;`  
使用insert语句向表中插入三个员工的信息  
mysql>`(推荐)INSERT INTO user (id,username,gender,birthday,entry_date,job,salary,resume) VALUES (1,'qihuasen','male','1986-09-08','2014-04-17','CEO',15000,'NB');`  
mysql>`INSERT INTO user VALUES (2,'yuxiaofeng','male','1991-09-08','2014-04-17','CTO',14000,'NBA');`  
中文数据  
mysql>`INSERT INTO user VALUES (3,'王斐','female','1970-09-08','2014-04-17','CXX',18000,'beauty');`

错误：\*\*ERROR 1406 (22001): Data too long for column ‘username’ at row 1  
原因：1、确实超出了长度 2、编码问题\*\*

告知服务器，客户端使用的编码(临时告知)  
mysql>`set character_set_client=gbk;`  
告知服务器，客户端显示结果集用的编码（临时告知）  
mysql>`set character_set_results=gbk;`

数据库的编码：  
mysql>`SHOW VARIABLES LIKE 'character%';`

(\*)client是客户端使用的字符集。  
(\*)connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。  
database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。  
(\*)results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。  
server是服务器安装时指定的默认字符集设定。  
system是数据库系统使用的字符集设定。

将所有员工薪水修改为5000元。  
msyql>`UPDATE user SET salary=5000;`  
将姓名为’qihuasen’的员工薪水修改为3000元。  
mysql>`UPDATE user SET salary=3000 WHERE username='qihuasen';`  
将姓名为’王斐’的员工薪水修改为4000元,job改为CCC。  
mysql>`UPDATE user SET salary=4000,job='CCC' WHERE username='王斐';`  
将yuxiaofeng的薪水在原有基础上增加1000元。  
mysql>`UPDATE user SET salary=salary+1000 WHERE username='yuxiaofeng';`

删除表中名称为’yuxiaofeng’的记录。  
mysql>`DELETE FROM user WHERE username='yuxiaofeng';`  
删除表中所有记录。  
mysql>`DELETE FROM user;（一条条记录）`  
使用truncate删除表中记录(DDL)。  
mysql>`TRUNCATE TABLE user;` (摧毁表结构，然后重新建立表结构)

七、**DQL数据查询语言**  
作用：查询表中的数据  
关键字：**SELECT**

单表数据的查询  
查询表中所有学生的信息。  
mysql>`SELECT * FROM student;`  
查询表中所有学生的姓名和对应的英语成绩。(投影查询)  
mysql>`SELECT name,english FROM student;`  
过滤表中英语重复数据。  
mysql>`SELECT DISTINCT english FROM student;`  
在所有学生数学分数上加10分特长分。  
mysql>`SELECT name,math+10 FROM student;`  
统计每个学生的总分。  
mysql>`SELECT name,chinese+english+math FROM student;`  
使用别名表示学生分数。  
mysql>`SELECT name AS 姓名,chinese+english+math 总分 FROM student;`  
查询姓名为王五的学生成绩  
mysql>`SELECT * FROM student WHERE name='王五';`  
查询英语成绩大于90分的同学  
mysql>`SELECT * FROM student WHERE english>90;`  
查询总分大于200分的所有同学  
mysql>`SELECT name AS 姓名,chinese+english+math 总分 FROM student WHERE (chinese+english+math)>200;`  
查询英语分数在 80－90之间的同学。  
mysql>`SELECT name,english FROM student WHERE english BETWEEN 80 AND 90;`  
查询数学分数为89,90,91的同学。  
mysql>`SELECT name,math FROM student WHERE math IN (89,90,91);`  
查询所有姓李的学生成绩。  
mysql>`SELECT * FROM student WHERE name LIKE '李%';`

查询数学分>80，语文分>80的mysql>`SELECT * FROM student WHERE math>80 AND chinese>80;`成绩排序后输出。  
mysql>SELECT name,math FROM student ORDER BY math;  
对总分排序后输出，然后再按从高到低的顺序输出  
mysql>`SELECT name 姓名,chinese+english+math 总分 FROM student ORDER BY 总分 DESC;`  
对姓李的学生成绩排序输出  
mysql>`SELECT name 姓名,chinese+english+math 总分 FROM student WHERE name LIKE '李%' ORDER BY 总分;`

八、**数据完整性**  
作用：数据完整性是为了保证插入到数据库中的数据是正确的，它防止了用户可能的输入错误  
8.1实体完整性(记录完整性)  
指能够唯一标识数据库中的一条记录  
解决办法：  
采用主键：**PRIMARY KEY** 唯一，不能为NULL

第一种声明主键的方式：

CREATE TABLE t2( id int PRIMARY KEY, name varchar(100),#姓名 idcardnum varchar(100) #身份证号码 );

第二种声明主键的方式：（推荐）

CREATE TABLE t3( id int, name varchar(100),#姓名 idcardnum varchar(100), #身份证号码 PRIMARY KEY(id) );

第二种声明主键的方式：可以声明联合主键（两个字段作为主键）

CREATE TABLE T4( a varchar(10), b varchar(10), PRIMARY KEY(a,b) #联合主键 );

-------------------------------------
    （建议）逻辑主键：没有实际的业务意义，目的就是为了唯一确定一条记录。ID
    业务主键：有一定的业务含义，也能唯一确定一条记录。身份证号码

CREATE TABLE t5( name varchar(100),#姓名 idcardnum varchar(18) PRIMARY KEY #身份证号码 );

一、数据完整性  
1、实体完整性  
2、域完整性（列完整性约束）：  
指数据库表的列（即字段）必须符合某种特定的数据类型或约束。

约束：
            类型约束：  id int 
            长度约束：   id int(3)      插入数据 520438
            非空约束：   username varchar(10) NOT NULL 必须有值
            唯一约束：   idcardnum varchar(18) UNIQUE 可以为null，有的话必须唯一
    
            用户名：一般网站用户名必须有，且唯一（不让他作为主键）
                username varchar(100) NOT NULL UNIQUE
    
    
        示例：
            CREATE TABLE user(
                id int PRIMARY KEY,
                username varchar(20) NOT NULL UNIQUE,
                idcardnum varchar(18) UNIQUE,
                gender varchar(10) NOT NULL
            );
    
        专题：整数类型的主键自动增长。由数据库自己自动插入主键的值

CREATE TABLE t1( id int PRIMARY KEY auto_increment,#主键自动增长 name varchar(100) );
                INSERT INTO t1 (id,name) VALUES(1,'A');
                INSERT INTO t1 (name) VALUES('B');  (推荐)
                INSERT INTO t1 VALUES (null,'C');

**建议：尽量不要让数据库维护主键。（不是所有的数据库都有自动增长这个功能）
        由应用来维护主键**
    
    3、参照完整性（重点：多表.外键）
        3.1多表的设计
            a、一对多（出现频率最高）

CREATE TABLE department( id int PRIMARY KEY, name varchar(100), addr varchar(100) );

CREATE TABLE employee( id int PRIMARY KEY, name varchar(100), gender varchar(10), salary float(8,2), depart_id int, CONSTRAINT depart_id FOREIGN KEY(depart_id) REFERENCES department(id) );

外键定义语法：
                **CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名称(主键字段);**
                外键名称：随便定义，当前库中必须唯一
                外键字段：当前表中那个字段是外键
    
            b、多对多（出现频率其次）

CREATE TABLE teacher( id int PRIMARY KEY, name varchar(100), salary float(8,2) );

CREATE TABLE student( id int PRIMARY KEY, name varchar(100), grade varchar(10) );

#定义关系表

CREATE TABLE teacher_student( t_id int, s_id int, PRIMARY KEY(t_id,s_id), CONSTRAINT t_id_fk FOREIGN KEY(t_id) REFERENCES teacher(id), CONSTRAINT s_id_fk FOREIGN KEY(s_id) REFERENCES student(id) );

INSERT INTO teacher VALUES(1,'WYJ',10000);
                    INSERT INTO teacher VALUES(2,'DH',10001);
                    INSERT INTO student VALUES(1,'WF','A');
                    INSERT INTO student VALUES(2,'QHS','A');
                    INSERT INTO teacher_student VALUES(1,1);
                    INSERT INTO teacher_student VALUES(1,2);
                    INSERT INTO teacher_student VALUES(2,1);
                    INSERT INTO teacher_student VALUES(2,2);

c、一对一（出现频率几乎没有）

CREATE TABLE person( id int PRIMARY KEY, name varchar(100) );
                    CREATE TABLE id_card( id int PRIMARY KEY, num varchar(18), CONSTRAINT id_fk FOREIGN KEY(id) REFERENCES person(id) );

二、**多表的查询**

1、**连接查询**
        基本语法：**select XXX from t1 连接类型 t2 [on 连接条件][where 筛选条件]**
            约定：t1在连接类型的左边，称之为左表 t2就是右表
                  连接类型：
                    **cross join:交叉连接
                    inner join:显式内连接
    
                    left outer join:左外连接
                    right outer join:右外连接**
    
        1.1交叉连接(了解)：cross join
            SELECT * FROM customer CROSS JOIN orders;
            返回的结果是：返回的是两张表结果的笛卡尔积。即表1有5条，表2有7条，返回的5*7=35条
        1.2内连接：inner join（自然连接）
            a、隐式内连接：不使用on关键字（即不明确指定连接条件），使用的是where
                查询有订单的客户的信息和订单信息

SELECT * FROM customer c,orders AS o WHERE c.id=o.customer_id;

b、显式内连接：使用on关键字
                查询有订单的客户的信息和订单信息

SELECT * FROM customer c INNER JOIN orders o ON c.id=o.customer_id;

查询订单金额在200元以上的客户的信息和订单信息

SELECT * FROM customer c INNER JOIN orders o ON c.id=o.customer_id WHERE o.price>=200;

1.3外连接：outer join
            a、左外连接:返回满足连接条件的结果，同时返回左表中剩余的其他记录
                查询客户信息，同时显示他的订单

SELECT * FROM customer c LEFT OUTER JOIN orders o ON c.id=o.customer_id;

查询所有的员工，打印所在的部门名称

SELECT * FROM employee e LEFT OUTER JOIN department d ON e.depart_id=d.id;

b、右外连接:返回满足连接条件的结果，同时返回右表中剩余的其他记录
                查询客户信息，同时显示他的订单
                SELECT * FROM orders o RIGHT OUTER JOIN customer c ON c.id=o.customer_id;
                查询所有订单，同时显示他的客户信息
                SELECT * FROM customer c RIGHT OUTER JOIN orders o ON c.id=o.customer_id;
    2、子查询（简单）：
        子查询：嵌套查询（内部语句）。一个查询语句是另外一个查询语句的条件。子查询的语句必须放在小括号之内

select * from t1 where id=(select id from t2);

查询id为2的老师教过的学生姓名
            方式一：多条语句

select s_id from teacher_student where t_id=2;
                    select * from student where id in (1,2);

方式二：连接查询

select * from teacher_student ts,student s where ts.s_id=s.id and ts.t_id=2;

方式三：子查询（一个单列结果）

select * from student where id in (select s_id from teacher_student where t_id=2);

查询名字为'陈冠希'的所有订单信息（子查询）

select * from orders where customer_id=(select id from customer where name='陈冠希');

3、联合查询：UNION
        联合查询能够合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。
    
        取多条语句的并集（没有重复记录）
        查询客户id=1并且订单金额>=200的订单信息

SELECT * from orders where price>=200 and customer_id=1;

使用联合查询：查询客户id=1或者订单金额>=200的订单信息

SELECT * FROM orders WHERE price>=200 UNION SELECT * FROM orders WHERE customer_id=1;

4、报表查询（数据库的内置函数）
        报表查询对数据行进行分组统计
        [select …] from … [where…] [ group by … [having… ]] [ order by … ]
        group by:按照那些字段进行分组
        having:对分组后的内容进行过滤（不能使用where）
    
    
        统计一个班级共有多少学生？
        mysql>`SELECT count(*) FROM student;`
        统计数学成绩大于90的学生有多少个？
        mysql>`SELECT COUNT(*) FROM student where math>90;`
        统计总分大于250的人数有多少？
        mysql>`SELECT COUNT(*) FROM student where (math+chinese+english)>250;`
        统计一个班级数学总成绩？
        mysql>`SELECT SUM(math) FROM student;`
        统计一个班级语文、英语、数学各科的总成绩
        mysql>`SELECT SUM(math),SUM(chinese),SUM(english) FROM student;`
        统计一个班级语文、英语、数学的成绩总和
        mysql>`SELECT SUM(math+chinese+english) FROM student;`
        统计一个班级语文成绩平均分
        mysql>`SELECT SUM(chinese)/COUNT(*) FROM student;`
    
        求一个班级数学平均分？
        mysql>`SELECT AVG(math) FROM student;`
        求一个班级总分平均分
        msyql>`SELECT AVG(math+chinese+english) FROM student;`
    
        求班级语文最高分和最低分
        mysql>`SELECT MAX(chinese) FROM student;`
        mysql>`SELECT MIN(chinese) FROM student;`
    
        **注意：不能使用关键字作为表名或列名，如果必须使用，请使用反引号`（ESC按键下面）引起来**
    
        对订单表中商品归类后，显示每一类商品的总价

mysql>SELECT product,sum(price) FROM orders GROUP BY product;

查询购买了几类商品，并且每类总价大于100的商品
        mysql`>SELECT product,sum(price) FROM orders GROUP BY product HAVING sum(price)>100;`

三、数据库的备份与恢复（MySQL）  
1、备份数据库（表结构和表中数据）  
c:/>`mysqldump -h localhost -u root -p mydb1>d:/mydb1.sql`

2、恢复数据库：数据库名必须手工创建，并选择
        方式一：在MySQL内部导入数据

mysql>create database mydb1;
                mysql>use mydb1;
                mysql>source d:/mydb1.sql;

方式二：不进入mysql进行数据的恢复

c:/>mysql -u root -p mydb1<d:/mydb1.sql