mysql set 命令的用法_mysql命令用法复习笔记

最新推荐文章于 2024-04-15 11:20:45 发布
最新推荐文章于 2024-04-15 11:20:45 发布
阅读量549 收藏
点赞数
文章标签： mysql set 命令的用法
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_39963341/article/details/113328212
版权

show DATABASES ;

create database nulige character set utf8;

use nulige;

show tables;#创建表

CREATE TABLE ExamResult(

id INT PRIMARY KEY auto_increment,

name VARCHAR (20),

JS DOUBLE ,

Django DOUBLE ,

flask DOUBLE

);#往表中插入数据

INSERT INTO ExamResult VALUES (1,"yuan",98,98,98),

(2,"xialv",35,98,67),

(3,"alex",59,59,62),

(4,"wusir",88,89,82),

(5,"alvin",88,98,67),

(6,"yuan",86,100,55);#查询表中所有学生的信息

select * fromExamResult;#查询表中所有学生的姓名和对应的英语成绩

SELECT name,JS FROM ExamResult;#过滤表中重复数据

select DISTINCT Js,name fromExamResult;

SELECT name,JS,Django,flask FROM ExamResult;

SELECT name,JS+10,Django+10,flask+20FROM ExamResult;

SELECT name as 姓名,JS+10 as JS成绩,Django+10,flask+20FROM ExamResult;

SELECT name JSfromExamResult;

select* from ExamResult WHERE name='yuan'SELECT name,Jsfrom ExamResult WHERE JS>90;

SELECT name,JSfrom ExamResult WHERE JS!=88;#between在什么之间 在88=100之间

SELECT name,JS FROM ExamResult WHERE JS BETWEEN 88 and 100;#in在什么之内

SELECT name,JS FROM ExamResult WHERE JS IN (88,99,77);#like 模糊查询，查询名字内有y的同学

SELECT name,JS FROM ExamResult WHERE name LIKE "y%";

SELECT name,JS FROM ExamResult WHERE name LIKE"a____";#查询js分=98同学

SELECT name,JS from ExamResult WHERE name='yuan' and JS=98;

insert into ExamResult (name) VALUE ('刘洋');

SELECT* fromExamResult;#查询值为空

SELECT name from ExamResult WHERE JS isNULL;#排序

SELECT name,JS from ExamResult WHERE JS>70ORDER BY JS;

SELECT name,JS FROM ExamResult WHERE JS>70ORDER BY Js DESC ;#as重命名

SELECT name,JS+Django+flask as 总成绩 fromExamResult ORDER BY 总成绩 DESC;#按yuan 总成绩进行降序排列(mysql语句有执行顺序：from where select group by having order by)

SELECT name,JS+Django+flask as 总成绩 from ExamResult WHERE name="yuan"ORDER BY 总成绩;#分组查询 group by

SELECT * fromExamResult;#按名字进行分组

SELECT * fromExamResult GROUP BY name;#按JS进行分组

SELECT * fromExamResult GROUP BY JS;#按第3列进行分组

SELECT * from ExamResult GROUP BY 3;#按名字进行排序

SELECT name,sum(JS) fromExamResult GROUP BY name;#对成绩表按名字分组后，显示每一类名字的Django的分数总和>150的

SELECT name,sum(Django) from ExamResult GROUP BY name having sum(Django)>150;#having和where两者都可以对查询结果进行进一步的过滤，差别有：#<1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；#<2>使用where语句的地方都可以用having进行替换#<3>having中可以用聚合函数，where中就不行。

SELECT* from ExamResult WHERE id=3;

SELECT* from ExamResult HAVING id=3;#聚合函数：先把要求的内容查出来再包上聚合函数即可。

#count(列名)：统计行的个数

SELECT count(name) from ExamResult WHERE js>70;#统计一个班级共有多少学生

select count(*) fromExamResult;

SELECT sum(JS)/count(name) fromExamResult;

SELECT AVG(JS)fromExamResult;#遇到ifnull的时候转换成0#统计总分大于280的人数有多少？

select count(name) from ExamResult where (ifnull(JS,0)+ifnull(Django,0)+ifnull(flask,0))>280;

SELECT* fromExamResult;#max最大值

SELECT max(JS) FROM ExamResult;#遇到null的时候，就转成0#min最小值

SELECT min(ifnull(JS,0)) FROM ExamResult;

SELECT max(JS+Django+flask) fromExamResult;#limit 跳过几条显示几条

SELECT * FROM ExamResult LIMIT 1;#跳过1,从2开始到5

SELECT * FROM ExamResult LIMIT 1,4;

增加外键

#查看数据库

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| lessens |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows inset#创建abc数据库

mysql>create database abc character set utf8;

Query OK,1row affected#进入abc表

mysql>use abc ;

Database changed#创建表

mysql>CREATE TABLE ClassCharger(

id TINYINT PRIMARY KEY auto_increment,

name VARCHAR (20),

age INT ,

is_marriged boolean

)ENGINE=INNODB;

Query OK, 0 rows affected#往表中插入数据

mysql> INSERT INTO ClassCharger (name,age,is_marriged) VALUES ("冰冰",52,0),

("丹丹",34,0),

("歪歪",32,0),

("姗姗",28,0),

("小雨",61,0);

Query OK,5rows affected

Records:5Duplicates: 0 Warnings: 0#创建表

mysql>CREATE TABLE Student(

id INT PRIMARY KEY auto_increment,

name VARCHAR (20),

charger_id TINYINT

) ENGINE=INNODB;

Query OK, 0 rows affected#往表中插入数据

mysql> INSERT INTO Student(name,charger_id) VALUES ("alvin1",2),

("alvin2",4),

("alvin3",1),

("alvin4",3),

("alvin5",5);

Query OK,5rows affected

Records:5Duplicates: 0 Warnings: 0#查看表中数据

mysql> select * fromStudent;+----+--------+------------+

| id | name | charger_id |

+----+--------+------------+

| 1 | alvin1 | 2 |

| 2 | alvin2 | 4 |

| 3 | alvin3 | 1 |

| 4 | alvin4 | 3 |

| 5 | alvin5 | 5 |

+----+--------+------------+

5 rows inset#查看表中数据

mysql> select * fromClassCharger;+----+------+-----+-------------+

| id | name | age | is_marriged |

+----+------+-----+-------------+

| 1 | 冰冰 | 52 | 0 |

| 2 | 丹丹 | 34 | 0 |

| 3 | 歪歪 | 32 | 0 |

| 4 | 姗姗 | 28 | 0 |

| 5 | 小雨 | 61 | 0 |

+----+------+-----+-------------+

5 rows inset#创建外键

mysql>ALTER TABLE Student ADD CONSTRAINT abc

FOREIGN KEY(charger_id)

REFERENCES classcharger(id);

Query OK,5rows affected

Records:5Duplicates: 0 Warnings: 0#查看表结构

mysql>desc Student;+------------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| name | varchar(20) | YES | | NULL | |

| charger_id | tinyint(4) | YES | MUL | NULL | |

+------------+-------------+------+-----+---------+----------------+

3 rows inset#查看表结构

mysql>desc ClassCharger;+-------------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------------+-------------+------+-----+---------+----------------+

| id | tinyint(4) | NO | PRI | NULL | auto_increment |

| name | varchar(20) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| is_marriged | tinyint(1) | YES | | NULL | |

+-------------+-------------+------+-----+---------+----------------+

4 rows inset#查看建表语句

mysql>show CREATE TABLE Student;+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Student |CREATE TABLE `student` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`charger_id` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `abc` (`charger_id`),

CONSTRAINT `abc` FOREIGN KEY (`charger_id`) REFERENCES `classcharger` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set

mysql> select * from student;

+----+--------+------------+

| id | name | charger_id |

+----+--------+------------+

| 1 | alvin1 | 2 |

| 2 | alvin2 | 4 |

| 3 | alvin3 | 1 |

| 4 | alvin4 | 3 |

| 5 | alvin5 | 5 |

+----+--------+------------+

5 rows in set

#插入数据

mysql> insert into student(name,charger_id) values("alvin1",2),("alvin1",4);

Query OK, 2 rows affected

Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from student;

+----+--------+------------+

| id | name | charger_id |

+----+--------+------------+

| 1 | alvin1 | 2 |

| 2 | alvin2 | 4 |

| 3 | alvin3 | 1 |

| 4 | alvin4 | 3 |

| 5 | alvin5 | 5 |

| 6 | alvin1 | 2 |

| 7 | alvin1 | 4 |

+----+--------+------------+

7 rows in set

#查看表

mysql> show tables;

+---------------+

| Tables_in_abc |

+---------------+

| classcharger |

| student |

+---------------+

2 rows in set

#查看表内容

mysql> select * from classcharger;

+----+------+-----+-------------+

| id | name | age | is_marriged |

+----+------+-----+-------------+

| 1 | 冰冰 | 52 | 0 |

| 2 | 丹丹 | 34 | 0 |

| 3 | 歪歪 | 32 | 0 |

| 4 | 姗姗 | 28 | 0 |

| 5 | 小雨 | 61 | 0 |

+----+------+-----+-------------+

5 rows in set

#查看表内容

mysql> select * from student;

+----+--------+------------+

| id | name | charger_id |

+----+--------+------------+

| 1 | alvin1 | 2 |

| 2 | alvin2 | 4 |

| 3 | alvin3 | 1 |

| 4 | alvin4 | 3 |

| 5 | alvin5 | 5 |

| 6 | alvin1 | 2 |

| 7 | alvin1 | 4 |

+----+--------+------------+

7 rows in set

#更新表中数据

mysql> update student set charger_id=4 where id=1 or id=6;

Query OK, 2 rows affected

Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from student;

+----+--------+------------+

| id | name | charger_id |

+----+--------+------------+

| 1 | alvin1 | 4 |

| 2 | alvin2 | 4 |

| 3 | alvin3 | 1 |

| 4 | alvin4 | 3 |

| 5 | alvin5 | 5 |

| 6 | alvin1 | 4 |

| 7 | alvin1 | 4 |

+----+--------+------------+

7 rows in set

#删除掉id=2 的 丹丹 ，再插入数据就会报错。

mysql> delete from classcharger where id=2;

Query OK, 1 row affected

mysql> select * from classcharger;

+----+------+-----+-------------+

| id | name | age | is_marriged |

+----+------+-----+-------------+

| 1 | 冰冰 | 52 | 0 |

| 3 | 歪歪 | 32 | 0 |

| 4 | 姗姗 | 28 | 0 |

| 5 | 小雨 | 61 | 0 |

+----+------+-----+-------------+

4 rows in set

再插入数据就会报错

mysql> insert into student (name,charger_id) values("alvin8",2);

1452 - Cannot add or update a child row: a foreign key constraint fails (`abc`.`student`, CONSTRAINT `abc` FOREIGN KEY (`charger_id`) REFERENCES `classcharger` (`id`))

#删除外键

mysql> alter table student drop foreign key abc;

Query OK, 0 rows affected

Records: 0 Duplicates: 0 Warnings: 0

#查看建表语句

mysql> show create table student;

+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| student | CREATE TABLE `student` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`charger_id` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `abc` (`charger_id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |

+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set

#增加外键

mysql> ALTER TABLE student ADD CONSTRAINT abc

FOREIGN KEY(charger_id)

REFERENCES classcharger(id);

Query OK, 7 rows affected

Records: 7 Duplicates: 0 Warnings: 0

#查看增加的外键

mysql> show create table student;

+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| student | CREATE TABLE `student` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`charger_id` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `abc` (`charger_id`),

CONSTRAINT `abc` FOREIGN KEY (`charger_id`) REFERENCES `classcharger` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |

+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set

INNODB支持的ON语句

#查看数据库

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| abc |

| mysql |

| performance_schema |

| sys |

+--------------------+

5 rows inset#创建数据库并设置字符集

mysql>create database s1 character set utf8;

Query OK,1row affected

mysql>use s1;

Database changed#创建表cc

mysql>CREATE TABLE CC(

id TINYINT PRIMARY KEY auto_increment,

name VARCHAR (20),

age INT ,

is_marriged boolean

);

Query OK, 0 rows affected#创建表s3，并设置外键为 delete cascade 方式

mysql>create table s3(

id int primary key auto_increment,

name varchar(20),

charger_id tinyint,

foreign key (charger_id) references CC(id) on delete cascade

) engine=innodb;

Query OK, 0 rows affected#往表中插入数据

mysql> INSERT INTO CC (name,age,is_marriged) VALUES ("冰冰",52,0),

("丹丹",34,0),

("歪歪",32,0),

("姗姗",28,0),

("小雨",61,0);

Query OK,5rows affected

Records:5Duplicates: 0 Warnings: 0#往表中插入数据

mysql> INSERT INTO S3(name,charger_id) VALUES ("alvin1",2),

("alvin2",4),

("alvin3",1),

("alvin4",3),

("alvin5",5);

Query OK,5rows affected

Records:5Duplicates: 0 Warnings: 0#查看表内容

mysql> select * fromcc;+----+------+-----+-------------+

| id | name | age | is_marriged |

+----+------+-----+-------------+

| 1 | 冰冰 | 52 | 0 |

| 2 | 丹丹 | 34 | 0 |

| 3 | 歪歪 | 32 | 0 |

| 4 | 姗姗 | 28 | 0 |

| 5 | 小雨 | 61 | 0 |

+----+------+-----+-------------+

5 rows inset#查看表内容

mysql> select * froms3;+----+--------+------------+

| id | name | charger_id |

+----+--------+------------+

| 1 | alvin1 | 2 |

| 2 | alvin2 | 4 |

| 3 | alvin3 | 1 |

| 4 | alvin4 | 3 |

| 5 | alvin5 | 5 |

+----+--------+------------+

5 rows inset#删除表中数据

mysql> delete from cc where id=4;

Query OK,1row affected#再查看表内容，4的数据被删除了。

mysql> select * froms3;+----+--------+------------+

| id | name | charger_id |

+----+--------+------------+

| 1 | alvin1 | 2 |

| 3 | alvin3 | 1 |

| 4 | alvin4 | 3 |

| 5 | alvin5 | 5 |

+----+--------+------------+

4 rows inset#查看建表语句

mysql>show create table s3;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| s3 |CREATE TABLE `s3` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`charger_id` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `charger_id` (`charger_id`),

CONSTRAINT `s3_ibfk_1` FOREIGN KEY (`charger_id`) REFERENCES `cc` (`id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row inset#########################设置外键为 set null 方式#######################

#查看建表语句的外键信息

mysql>show create table s3;+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| s3 |CREATE TABLE `s3` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`charger_id` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `charger_id` (`charger_id`),

CONSTRAINT `s3_ibfk_1` FOREIGN KEY (`charger_id`) REFERENCES `cc` (`id`) ON DELETE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row inset#删除外键

mysql>alter table s3 drop foreign key s3_ibfk_1;

Query OK, 0 rows affected

Records: 0 Duplicates: 0 Warnings: 0#查看删除结果

mysql>show create table s3;+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| s3 |CREATE TABLE `s3` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`charger_id` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `charger_id` (`charger_id`)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 |

+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row inset#增加外键为set null 方式

mysql>alter table s3 add constraint s3_fk_cc foreign key (charger_id)->references cc(id) on delete set null;

Query OK,4rows affected

Records:4Duplicates: 0 Warnings: 0

mysql> select * fromcc;+----+------+-----+-------------+

| id | name | age | is_marriged |

+----+------+-----+-------------+

| 1 | 冰冰 | 52 | 0 |

| 2 | 丹丹 | 34 | 0 |

| 3 | 歪歪 | 32 | 0 |

| 5 | 小雨 | 61 | 0 |

+----+------+-----+-------------+

4 rows inset

mysql> delete from CC where id=3;

Query OK,1row affected

mysql> select * froms3;+----+--------+------------+

| id | name | charger_id |

+----+--------+------------+

| 1 | alvin1 | 2 |

| 3 | alvin3 | 1 |

| 4 | alvin4 | NULL |

| 5 | alvin5 | 5 |

+----+--------+------------+

4 rows inset

mysql>

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| abc |

| crm |

| mysql |

| performance_schema |

| s1 |

| sys |

+--------------------+

7 rows in set

#创建数据库

mysql> create database t2 character set utf8;

Query OK, 1 row affected

mysql> use t2;

Database changed

#创建表

mysql> create table tableA(id int primary key,name varchar(20));

Query OK, 0 rows affected

#创建表

mysql> create table tableB(id int primary key,name varchar(20),tableA_id int);

Query OK, 0 rows affected

#往表中插入数据

mysql> insert into tableA values(1,'alvin'),(2,'xialv'),

(3,'yuan');

Query OK, 3 rows affected

Records: 3 Duplicates: 0 Warnings: 0

#往表中插入数据

mysql> insert into tableB values(1,'小雨',1),(2,'冰冰',2),(3,'周周',4);

Query OK, 3 rows affected

Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from tableA;

+----+-------+

| id | name |

+----+-------+

| 1 | alvin |

| 2 | xialv |

| 3 | yuan |

+----+-------+

3 rows in set

mysql> select * from tableB;

+----+------+-----------+

| id | name | tableA_id |

+----+------+-----------+

| 1 | 小雨 | 1 |

| 2 | 冰冰 | 2 |

| 3 | 周周 | 4 |

+----+------+-----------+

3 rows in set

#笛卡尔积查询

mysql> select * from tableA,tableB;

+----+-------+----+------+-----------+

| id | name | id | name | tableA_id |

+----+-------+----+------+-----------+

| 1 | alvin | 1 | 小雨 | 1 |

| 2 | xialv | 1 | 小雨 | 1 |

| 3 | yuan | 1 | 小雨 | 1 |

| 1 | alvin | 2 | 冰冰 | 2 |

| 2 | xialv | 2 | 冰冰 | 2 |

| 3 | yuan | 2 | 冰冰 | 2 |

| 1 | alvin | 3 | 周周 | 4 |

| 2 | xialv | 3 | 周周 | 4 |

| 3 | yuan | 3 | 周周 | 4 |

+----+-------+----+------+-----------+

9 rows in set

#内连接查询

mysql> select * from tableA,tableB where tableA.id=tableB.id;

+----+-------+----+------+-----------+

| id | name | id | name | tableA_id |

+----+-------+----+------+-----------+

| 1 | alvin | 1 | 小雨 | 1 |

| 2 | xialv | 2 | 冰冰 | 2 |

| 3 | yuan | 3 | 周周 | 4 |

+----+-------+----+------+-----------+

3 rows in set

#只显示关联人的id,姓名，

mysql> select tableA.id,tableA.name,tableB.name from tableA,tableB where tableA.id=tableB.id;

+----+-------+------+

| id | name | name |

+----+-------+------+

| 1 | alvin | 小雨 |

| 2 | xialv | 冰冰 |

| 3 | yuan | 周周 |

+----+-------+------+

3 rows in set

mysql> select * from tableB inner join tableA on tableB.tableA_id =tableA.id;

+----+------+-----------+----+-------+

| id | name | tableA_id | id | name |

+----+------+-----------+----+-------+

| 1 | 小雨 | 1 | 1 | alvin |

| 2 | 冰冰 | 2 | 2 | xialv |

+----+------+-----------+----+-------+

2 rows in set

#多表查询之连接查询

#创建表

mysql>create table employee(

emp_id int auto_increment primary key not null,

emp_name varchar(50),

age int,

dept_id int

);

Query OK, 0 rows affected

#往表中插入数据

mysql> insert into employee(emp_name,age,dept_id) values

('A',19,200),

('B',26,201),

('C',30,201),

('D',24,202),

('E',20,200),

('F',38,204);

Query OK, 6 rows affected

Records: 6 Duplicates: 0 Warnings: 0

#创建表

mysql>create table department(

dept_id int,

dept_name varchar(100)

);

Query OK, 0 rows affected

#往表中插入数据

mysql>insert into department values

(200,'人事部'),

(201,'技术部'),

(202,'销售部'),

(203,'财政部');

Query OK, 4 rows affected

Records: 4 Duplicates: 0 Warnings: 0

#查看表

mysql> show tables;

+--------------+

| Tables_in_t2 |

+--------------+

| department |

| employee |

| tablea |

| tableb |

+--------------+

4 rows in set

#笛卡尔积查询

mysql> select * from employee,department;

+--------+----------+-----+---------+---------+-----------+

| emp_id | emp_name | age | dept_id | dept_id | dept_name |

+--------+----------+-----+---------+---------+-----------+

| 1 | A | 19 | 200 | 200 | 人事部 |

| 1 | A | 19 | 200 | 201 | 技术部 |

| 1 | A | 19 | 200 | 202 | 销售部 |

| 1 | A | 19 | 200 | 203 | 财政部 |

| 2 | B | 26 | 201 | 200 | 人事部 |

| 2 | B | 26 | 201 | 201 | 技术部 |

| 2 | B | 26 | 201 | 202 | 销售部 |

| 2 | B | 26 | 201 | 203 | 财政部 |

| 3 | C | 30 | 201 | 200 | 人事部 |

| 3 | C | 30 | 201 | 201 | 技术部 |

| 3 | C | 30 | 201 | 202 | 销售部 |

| 3 | C | 30 | 201 | 203 | 财政部 |

| 4 | D | 24 | 202 | 200 | 人事部 |

| 4 | D | 24 | 202 | 201 | 技术部 |

| 4 | D | 24 | 202 | 202 | 销售部 |

| 4 | D | 24 | 202 | 203 | 财政部 |

| 5 | E | 20 | 200 | 200 | 人事部 |

| 5 | E | 20 | 200 | 201 | 技术部 |

| 5 | E | 20 | 200 | 202 | 销售部 |

| 5 | E | 20 | 200 | 203 | 财政部 |

| 6 | F | 38 | 204 | 200 | 人事部 |

| 6 | F | 38 | 204 | 201 | 技术部 |

| 6 | F | 38 | 204 | 202 | 销售部 |

| 6 | F | 38 | 204 | 203 | 财政部 |

+--------+----------+-----+---------+---------+-----------+

24 rows in set

外连接之左外连接

mysql> select * from employee left join department on employee.dept_id = department.dept_id;

+--------+----------+-----+---------+---------+-----------+

| emp_id | emp_name | age | dept_id | dept_id | dept_name |

+--------+----------+-----+---------+---------+-----------+

| 1 | A | 19 | 200 | 200 | 人事部 |

| 5 | E | 20 | 200 | 200 | 人事部 |

| 2 | B | 26 | 201 | 201 | 技术部 |

| 3 | C | 30 | 201 | 201 | 技术部 |

| 4 | D | 24 | 202 | 202 | 销售部 |

| 6 | F | 38 | 204 | NULL | NULL |

+--------+----------+-----+---------+---------+-----------+

6 rows in set

mysql> select employee.emp_name,department.dept_name from employee,department where employee.dept_id = department.dept_id and employee.emp_name="A";

+----------+-----------+

| emp_name | dept_name |

+----------+-----------+

| A | 人事部 |

+----------+-----------+

1 row in set

#外连接之右外连接

mysql> select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id;

+--------+----------+------+---------+---------+-----------+

| emp_id | emp_name | age | dept_id | dept_id | dept_name |

+--------+----------+------+---------+---------+-----------+

| 1 | A | 19 | 200 | 200 | 人事部 |

| 2 | B | 26 | 201 | 201 | 技术部 |

| 3 | C | 30 | 201 | 201 | 技术部 |

| 4 | D | 24 | 202 | 202 | 销售部 |

| 5 | E | 20 | 200 | 200 | 人事部 |

| NULL | NULL | NULL | NULL | 203 | 财政部 |

+--------+----------+------+---------+---------+-----------+

6 rows in set

#外连接之全外连接

全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果

-- mysql不支持全外连接 full JOIN

-- mysql可以使用此种方式间接实现全外连接

mysql> select * from tableB full join tableA on tableB.tableA_id = table.id;

1054 - Unknown column 'tableB.tableA_id' in 'on clause'

mysql> select employee.emp_name,department.dept_name from employee,department where employee

-> .dept_id=department.dept_id and department.dept_name="技术部";

+----------+-----------+

| emp_name | dept_name |

+----------+-----------+

| B | 技术部 |

| C | 技术部 |

+----------+-----------+

2 rows in set

#显示大于25岁所有的部门

mysql> select distinct department.dept_name from employee,department where employee.dept_id=department.dept_id and employee.age>25;

+-----------+

| dept_name |

+-----------+

| 技术部 |

+-----------+

1 row in set

多表查询之子查询

#带IN关键字的子查询

mysql> select * from employee where dept_id in(200,201,202,203);

+--------+----------+-----+---------+

| emp_id | emp_name | age | dept_id |

+--------+----------+-----+---------+

| 1 | A | 19 | 200 |

| 2 | B | 26 | 201 |

| 3 | C | 30 | 201 |

| 4 | D | 24 | 202 |

| 5 | E | 20 | 200 |

+--------+----------+-----+---------+

5 rows in set

mysql> select dept_id from department

;

+---------+

| dept_id |

+---------+

| 200 |

| 201 |

| 202 |

| 203 |

+---------+

4 rows in set

#带IN关键字的子查询

mysql> select * from employee where dept_id in(select dept_id from department);

+--------+----------+-----+---------+

| emp_id | emp_name | age | dept_id |

+--------+----------+-----+---------+

| 1 | A | 19 | 200 |

| 2 | B | 26 | 201 |

| 3 | C | 30 | 201 |

| 4 | D | 24 | 202 |

| 5 | E | 20 | 200 |

+--------+----------+-----+---------+

5 rows in set

#用select查询语句，建表，会丢失主键信息

mysql> create table aa(select * from employee);

Query OK, 6 rows affected

Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from aa;

+--------+----------+-----+---------+

| emp_id | emp_name | age | dept_id |

+--------+----------+-----+---------+

| 1 | A | 19 | 200 |

| 2 | B | 26 | 201 |

| 3 | C | 30 | 201 |

| 4 | D | 24 | 202 |

| 5 | E | 20 | 200 |

| 6 | F | 38 | 204 |

+--------+----------+-----+---------+

6 rows in set

mysql> desc aa;

+----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| emp_id | int(11) | NO | | 0 | |

| emp_name | varchar(50) | YES | | NULL | |

| age | int(11) | YES | | NULL | |

| dept_id | int(11) | YES | | NULL | |

+----------+-------------+------+-----+---------+-------+

4 rows in set

#带EXISTS关键字的子查询(结果存在就返回true,不存在就返回false)

mysql> select * from employee where exists (select dept_name from department where dept_id=203);

+--------+----------+-----+---------+

| emp_id | emp_name | age | dept_id |

+--------+----------+-----+---------+

| 1 | A | 19 | 200 |

| 2 | B | 26 | 201 |

| 3 | C | 30 | 201 |

| 4 | D | 24 | 202 |

| 5 | E | 20 | 200 |

| 6 | F | 38 | 204 |

+--------+----------+-----+---------+

6 rows in set

#不存在就返回false

mysql> select * from employee where exists (select dept_name from department where dept_id=205);

Empty set

weixin_39963341
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql set 命令的用法_MySQL命令行的几个用法
weixin_42168230的博客
02-01 1841
1.使用\G按行垂直显示结果如果一行很长，需要这行显示的话，看起结果来就非常的难受。在SQL语句或者命令后使用\G而不是分号结尾，可以将每一行的值垂直输出。这个可能也是大家对于MySQL最熟悉的区别于其他数据库工具的一个特性了。mysql> select * from db_archivelog\G*************************** 1. row ************...
Mysql SET语法
热门推荐
小庄往左走
11-18 5万+
<br />13.5.3. SET语法SET variable_assignment [, variable_assignment] ... variable_assignment:      user_var_name = expr    | [GLOBAL | SESSION] system_var_name = expr    | @@[global. | session.]system_var_name = expr<br />SET用于设置不同类型的变量。这些变量会影响服务器或客户端的操作。SET
MySQL中set类型的使用
08-07
我自己做的一个小项目 那出来和大家分享一下具体内容请看里面的文档
mysql中set类型的用法
qq_32253969的博客
12-06 1万+
set是可以具有0到64个值的字符串对象类型(可当集合使用)， set列中的值都必须在定义时指定的值列表中选取。 set中的每个字符串成员值之间用逗号(,)分隔， 所以成员值中不能包含逗号(,)字符串(实质即Mysql把成员值通过逗号拼接成一个完整的字符串)。 用例：一道题有ABCD四个选项，是一个不定项选择题，现在将他的答案存入一个字段中 CREATE TABLE `set_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `hobby` s
mysql set命令,MySql常用命令
weixin_29069575的博客
03-17 293
MySql常用命令1. 数据库切换数据库： use mydb2. 事件查看事件： show events启动调度器: set global event_scheduer = 1;查看是否启动： show variables like 'event_scheduler';3. 备份在administator中的backup中无法备份数据库的事件。需要手动将创建事件的脚本添加到sql备份文件中...
sql_node-master.zip_MYSQL_node笔记_sql
09-25
在本压缩包“sql_node-master.zip_MYSQL_node笔记_sql”中，主要涵盖了使用Node.js进行MySQL数据库操作的相关知识。Node.js是一种基于Chrome V8引擎的JavaScript运行环境，它以其高效、非阻塞I/O和事件驱动的特点，...
mysql中的leading用法_MySQL 函数笔记
weixin_31315007的博客
02-02 1601
统计相关函数COUNT和SUM函数使用小技巧在一个 SQL 中统计多个指标的个数:SELECT COUNT(DISTINCT col1), COUNT(DISTINCT CASE WHEN col2 > 10 THEN col2 ELSE 0 END) - 1, SUM(CASE WHEN col3 > 0 THEN 1 ELSE 0 END)FROM tableName;Date a...
Prometheus 的云上 MySQL 监控实践_mysql_global_status_slow_queries
最新发布
2401_84254451的博客
04-15 1077
Created_tmp_disk_tables：表示为创建磁盘临时表数量，对应输出监控指标为：mysql_global_status_created_tmp_disk_tables。max_used_connections：表示当前使用过的最大连接数，对应输出的监控指标为：mysql_global_status_max_used_connections。Table_locks_waited 表示为表锁数量，对应输出监控指标为：mysql_global_status_table_locks_waited。
学习mysql基本命令笔记....zip
02-24
本笔记将深入浅出地介绍MySQL的基本命令，帮助初学者快速掌握数据库操作。 一、安装与启动MySQL 在安装MySQL后，通常可以通过系统服务或命令行来启动和停止数据库服务。在Windows上，可以使用`net start MySQL`启动...
joe_MySQL笔记
12-30
#### 二、基本MySQL操作命令 - **查看数据库列表**: `show databases;` - **选择数据库**: `use 数据库名;` - **列出当前数据库中的所有表**: `show tables;` 或者 `show tables from 数据库名;` - **查看当前所在...
mysql set语句_从强网杯随便注浅析mysql存储过程
weixin_39856589的博客
12-03 115
从强网杯随便注浅析mysql存储过程Author: Smity去年的强网杯，出了一道mysql堆叠注入叫随便注，这道题被好多比赛玩了一整年，直到现在还是有各种新姿势，但是今天我忽然想到似乎没有对这个题目有一个很认真的分析，因此这里总结一下这个题目的出题用意和原本的预期做法：堆叠注入Stacked injections:堆叠注入。从名词的含义就可以看到应该是一堆sql语句（多条）一起执行。而在真实的...
mysql中set 用法_mysql中set和enum使用(简单介绍)
weixin_30359737的博客
01-19 3650
简单介绍SET类型在创建表时，就指定SET类型的取值范围。属性名 SET('值1','值2','值3'...,'值n')其中，“属性名”参数指字段的名称；“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除。其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是有64个元素构成的组合，根据...
mysql 中set函数的用法
SmileTimLi的博客
09-19 9078
mysql 中set函数的用法 ：当值为空时不进行赋值 update user_user &lt;set&gt; &lt;if test="nickname!=null and nickname!='' "&gt; user_user.nickname=#{nikename} &lt;/if&gt; &lt;/set&gt; where u...
mysql set语句_MySQL深层次的总结
weixin_39870150的博客
11-25 765
客户端与服务器连接 TCP/IP 命名管道和共享内存 Unix域套接字文件处理请求包括：连接管理，解析与优化，存储引擎SHOW 启动选项 配置文件路径 配置文件 defaults-file defaults-extra-file使用系统变量SHOW 作用范围分为这两种：GLOBAL：全局变量，影响服务器的整体操作。SESSION(LOCAL) ：会话变量，影响某个客户端连接的操作。 设置语句一：...
mysql set 常用参数_mysql命令常用参数实例讲解
weixin_28347369的博客
01-18 793
mysql命令常用参数实例讲解以下是mysql命令常用的参数，配合实例进行简单讲解1，auto-rehash自动补全(表名及表中字段)－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－[mysql]#no-auto-rehashauto-rehash－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－mysql -u root --auto-r...
mysql set 命令的用法_介绍mysql数据库下show命令的主要用法
weixin_34824634的博客
02-01 316
介绍mysql数据库下show命令的主要用法本文主要介绍mysql数据库下show命令的主要用法：a. show tables或show tables from database_name; -- 显示当前数据库中所有表的名称。b. show databases; -- 显示mysql中所有数据库的名称。c. show columns from table_name from database_n...
mysql set
Arthur的随笔
06-03 5116
from: http://www.itzk.com/thread-582269-53-1.shtml 本篇文章主要来介绍一下set语句。SET 语句的作用是指定各种选项。最好是省去词OPTION，因为它在MySQL 的未来版本中将被删除。下面为set的语法： SET [OPTION] option_setting,.. option_setting值可为下列清单中的任
mysql set 语法_MYSQL SET 和 SHOW 语法
weixin_33322743的博客
01-19 1400
SET可以用于设置各种变量或者选项。SHOW可以以各种形式描述数据库系统的信息，诸如数据表，字段，服务器状态等等。大致有以下这些方法：SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']SHOW CREATE DATABASE db_nameSHOW CREATE TABLE tbl_nameSHOW DATABASES...
mysql set类型使用_MySQL的SET字段类型使用方法
weixin_31369469的博客
01-19 1万+
MySQL的SET字段类型使用方法SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。例如，指定为SET('one', 'two') NOT NULL的列可以有下面的任何值：'''one''two''one,two'SET最多可以有64个不同的成员。当创建表时，SET成...
"MySQL基础操作与常用命令总结笔记
MYSQL（全称为My Structured Query Language）是一种使用最广泛的关系型数据库管理系统。本文将总结一些关于MYSQL的重要知识点和常用命令。 首先，MYSQL的基本操作主要包含SQL语法和一些常用的MYSQL命令。SQL是结构...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值