mysql入门

最新推荐文章于 2025-05-12 22:10:13 发布

深夜程猿

最新推荐文章于 2025-05-12 22:10:13 发布

阅读量254

点赞数

分类专栏： MySql 文章标签： mysql

MySql 专栏收录该内容

4 篇文章

订阅专栏

一、数据库
1.创建数据库：CREATE DATABASE [IF NOT EXISTS] db_name [create_specification [, create_specification] …]
create_specification: [DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name
*创建一个名称为mydb1的数据库。
create database mydb1;
*创建一个使用utf8字符集的mydb2数据库。
create database mydb2 character set utf8;
*创建一个使用utf8字符集，并带校对规则的mydb3数据库。
create database mydb3 character set utf8 collate utf8_bin;
解决乱码：show variables like “character%”
mysql中文乱码问题：
mysql有六处使用了字符集，分别为：client 、connection、database、results、server 、system。

client是客户端使用的字符集。

connection是连接数据库的字符集设置类型，如果程序没有指明连接数据库使用的字符集类型就按照服务器端默认的字符集设置。

database是数据库服务器中某个库使用的字符集设定，如果建库时没有指明，将使用服务器安装时指定的字符集设置。

results是数据库给客户端返回时使用的字符集设定，如果没有指明，使用服务器默认的字符集。

server是服务器安装时指定的默认字符集设定。

system是数据库系统使用的字符集设定。（utf-8不可修改）

show variables like’character%’;

set names gbk;指定当前窗口所使用的编码集
通过修改my.ini 修改字符集编码

2.查看数据库
    查看当前具有的所有数据库：
    SHOW DATABASES
    查看数据库的建库语句
    SHOW CREATE DATABASE bd_name

3.删除数据库
    DROP DATABSE  [IF EXISTS]  db_name 

    *查看当前数据库服务器中的所有数据库 
            show databases;
    *查看前面创建的mydb2数据库的定义信息
            show create database mydb2;
    *删除前面创建的mydb1数据库 drop database mydb1;
4.修改数据库
    ALTER  DATABASE  [IF NOT EXISTS] db_name [alter_specification [, alter_specification] ...]

alter_specification:
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

*查看服务器中的数据库，并把其中某一个库的字符集修改为gbk;
ALTER DATABASE mydb2 character set gbk;

5.选择数据库、查看选择的数据
*选择数据库
USE db_name;
*查看当前选择数据库
select database();

二、表
1.创建表(基本语句)
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype,
)character set 字符集 collate 校对规则
field：指定列名　datatype：指定列类型
*创建一个员工表employee
CREATE TABLE employee(
id int primary key auto_increment,
name varchar(20) unique ,
gender bit not null,
birthday date,
entry_date date,
job varchar(50),
salary double,
resume text
);

2.查看表信息
desc employee;–查看表结构
show tables; –列出当前数据库中的所有代表
show create table tab_names; –查看创建表的语句。

3.修改表
ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]…);
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[, column datatype]…);
ALTER TABLE table
DROP (column);
ALTER TABLE table
change [column] old_col_name column_definition;
修改表的名称：rename table 表名 to 新表名;
修改表的字符集编码alter table student character set utf8;

*在上面员工表的基本上增加一个image列。
    alter table employee add [column] image blob;
*修改job列，使其长度为60。
    alter table employee modify job varchar(60);
*删除gender列。
    alter table employee drop gender;
*表名改为user。
    rename table employee to user;
*修改表的字符集为utf8
  alter table user character set utf8;
*列名name修改为username
  alter table user change name username varchar(20);

4.删除表
DROP TABLE table;

三、数据记录
1.insert 增加表记录
INSERT INTO table [(column [, column…])] VALUES (value [, value…]);

*使用insert语句向表中插入三个员工的信息。
insert into employee (id,name,gender,birthday,entry_date,job,salary,resume) values (null,'张飞',1,'1990-09-09','1991-01-01','打手',998.00,'真的很能打，是老总的弟弟，关系户');
insert into employee values (null,'关羽',1,'1980-08-08','2000-01-01','财神',9998.00,'公司挣钱全指望他了');
insert into employee values (null,'刘备',1,'1970-07-07','1970-01-01','CEO老总',999999999.00,'公司老总，富二代'),(null,'赵云',1,'1999-07-07','1999-10-01','保安队队长',99.00,'保安队长');

2.Update 修改表记录
UPDATE tbl_name SET col_name1=expr1 [, col_name2=expr2 …] [WHERE where_definition]
*将所有员工薪水修改为5000元。
update employee set salary=5000;
*将姓名为’张飞’的员工薪水修改为3000元。
update employee set salary=3000 where name=’张飞’;
*将姓名为’关羽’的员工薪水修改为4000元,job改为‘门神’。
update employee set salary=4000 ,job=’门神’ where name=’关羽’;
*将刘备的薪水在原有基础上增加1000元。
update employee set salary=salary+1000 where name=’刘备’;

3.Delete 删除表记录
delete from tbl_name [WHERE where_definition];
*删除表中名称为’赵云’的记录。
delete from employee where name=’赵云’;
*删除表中所有记录。
delete from employee;
*使用truncate删除表中记录。
truncate employee;

4.Select 查询数据
（1）SELECT [DISTINCT] *|{column1, column2. column3..} FROM table;
*查询表中所有学生的信息。
select * from exam;
*查询表中所有学生的姓名和对应的英语成绩。
select name,chinese from exam;
*过滤表中重复数据。—————————-把名字也显示上?怎么写!
select distinct english from exam;
(2)在select语句中可使用表达式对查询的列进行运算
SELECT *|{column1｜expression, column2｜expression，..} FROM table;
*在所有学生分数上加10分特长分显示。
select name,english+10,chinese+10,math+10 from exam;
*统计每个学生的总分。
select name,english+chinese+math from exam;
*使用别名表示学生总分。
select name as 姓名,english+chinese+math as 总成绩 from exam;
select name 姓名,english+chinese+math 总成绩 from exam;
！！select name english from exam;
(3)使用where子句，进行过滤查询。
*查询姓名为张飞的学生成绩
select * from exam where name=’张飞’;
*查询英语成绩大于90分的同学
select name,english from exam where english>90;
*查询总分大于230分的所有同学
！！错误的写法：select name,english+chinese+math as 总成绩 from exam where 总成绩>230;
select name,english+chinese+math as 总成绩 from exam where english+chinese+math>230;
*查询英语分数在 80－100之间的同学。
select name,english from exam where english between 80 and 100;
*查询数学分数为75,76,77的同学。
select name,math from exam where math in(75,76,77);
*查询所有姓张的学生成绩。
select * from exam where name like ‘张%’;
select * from exam where name like ‘张_’;
select * from exam where name like ‘张__’;
*查询数学分>70，语文分>80的同学。
select * from exam where math>70 and chinese>80;
(4)使用order by 子句排序查询结果
默认使用升序
order by子句只能出现在整个sql语句的最后的位置上。
SELECT column1, column2. column3.. FROM table order by column [asc|desc]

            *对数学成绩排序后输出。
                select * from exam order by math;
            *对总分排序按从高到低的顺序输出
                select name 姓名,math+english+chinese 总成绩 from exam order by 总成绩 desc;
            *对姓张的学生成绩排序输出
                select  name 姓名,math+english+chinese 总成绩 from exam where name like '张%' order by 总成绩 desc; 

        （5）聚合函数
            !Count(列名)返回某一列，行的总数
                *统计一个班级共有多少学生？
                    select count(*) from exam;
                *统计数学成绩大于70的学生有多少个？
                    select count(name) from exam where math>70;
                *统计总分大于230的人数有多少？
                    select count(name) from exam where math+english+chinese>230;
            !Sum()函数返回满足where条件的行的和     
                *统计一个班级数学总成绩？
                    select sum(math) from exam;
                *统计一个班级语文、英语、数学各科的总成绩
                    select sum(math),sum(chinese),sum(english) from exam;
                *统计一个班级语文、英语、数学的成绩总和
                    select sum(math+english+chinese) from exam;
                *统计一个班级语文成绩平均分
                    select sum(chinese)/count(*) from exam;
                *统计一个班级的平均分
                    ！！问题：select sum(math+english+chinese)/count(*) from exam;
                        select sum(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0))/count(*) from exam;
            !AVG()函数返回满足where条件的一列的平均值
                *求一个班级数学平均分？
                    select avg(math) from exam;
                *求一个班级总分平均分？
                    select avg(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;
        !   Max/min函数返回满足where条件的一列的最大/最小值
            *求班级最高分和最低分（数值范围在统计中特别有用）
                select max(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;
                select min(ifnull(math,0)+ifnull(english,0)+ifnull(chinese,0)) from exam;

        (6)分组操作
            *对订单表中商品归类后，显示每一类商品的总价
                select product,sum(price) from orders group by product;
            *查询购买了几类商品，并且每类总价大于100的商品
                !!select product,sum(price) from orders group by product where sum(price)>100;
                select product,sum(price) from orders group by product having sum(price)>100;
            *查询单价小于100而总价大于150的商品的名称
                select product  from orders where price<100 group by product having sum(price)>150;

            ！！！！常考点：    
            ~where语句用来对分组之前的数据进行筛选,having用来对分组之后的数据进行筛选。
            ~使用where语句的地方，都可以用having替代。
            ~where语句中不能使用聚合函数，having子句中可以使用聚合函数。
            ~having中使用到的字段必须在having子句执行之前使用过

!!!!!!关键字出现顺序：select from where groupby having orderby
执行顺序：from where select group by having order by
select math+english+chinese as 总成绩 from exam where 总成绩 >250; —- 不成功
select math+english+chinese as 总成绩 from exam having 总成绩 >250; — 成功
select math+english+chinese as 总成绩 from exam group by 总成绩 having 总成绩 >250; —-成功
select math+english+chinese as 总成绩 from exam order by 总成绩;—-成功
select * from exam as 成绩 where 成绩.math>85; —- 成功

外键：
create table dept(
id int primary key auto_increment,
name varchar(40)
);
insert into dept values (null,’人事部’),(null,’财务部’),(null,’销售部’),(null,’科技部’);

create table emp(
    id int primary key auto_increment,
    name varchar(40),
    dept_id int,
    foreign key(dept_id) references dept(id)
);
insert into emp values  (null,'张飞',1),(null,'关羽',2),(null,'赵云',3),(null,'刘备',4);

多表查询

create table dept(
    id int primary key auto_increment,
    name varchar(40)
);
insert into dept values (null,'人事部'),(null,'财务部'),(null,'销售部'),(null,'科技部');

create table emp(
    id int primary key auto_increment,
    name varchar(40),
    dept_id int
);
insert into emp values  (null,'张飞',1),(null,'关羽',2),(null,'赵云',3),(null,'刘备',4);

mysql> select * from dept;
+—-+——–+
| id | name |
+—-+——–+
| 1 | 人事部 |
| 2 | 财务部 |
| 3 | 销售部 |
| 4 | 科技部 |
+—-+——–+
4 rows in set (0.00 sec)

mysql> select * from emp;
+—-+——+———+
| id | name | dept_id |
+—-+——+———+
| 1 | 张飞 | 1 |
| 2 | 关羽 | 2 |
| 3 | 赵云 | 3 |
| 4 | 刘备 | 4 |
+—-+——+———+
4 rows in set (0.00 sec)

1.笛卡尔积查询：一张表中的记录对应另一张表里的所有记录查询出来的结果，如果左表有m条记录，右表有n条记录，则查询结果m*n条记录。笛卡尔积查询的结果中包含着大量错误的数据，这种查询在真实情况中不会使用。
mysql> select * from dept,emp;
+—-+——–+—-+——+———+
| id | name | id | name | dept_id |
+—-+——–+—-+——+———+
| 1 | 人事部 | 1 | 张飞 | 1 |
| 2 | 财务部 | 1 | 张飞 | 1 |
| 3 | 销售部 | 1 | 张飞 | 1 |
| 4 | 科技部 | 1 | 张飞 | 1 |
| 1 | 人事部 | 2 | 关羽 | 2 |
| 2 | 财务部 | 2 | 关羽 | 2 |
| 3 | 销售部 | 2 | 关羽 | 2 |
| 4 | 科技部 | 2 | 关羽 | 2 |
| 1 | 人事部 | 3 | 赵云 | 3 |
| 2 | 财务部 | 3 | 赵云 | 3 |
| 3 | 销售部 | 3 | 赵云 | 3 |
| 4 | 科技部 | 3 | 赵云 | 3 |
| 1 | 人事部 | 4 | 刘备 | 4 |
| 2 | 财务部 | 4 | 刘备 | 4 |
| 3 | 销售部 | 4 | 刘备 | 4 |
| 4 | 科技部 | 4 | 刘备 | 4 |
+—-+——–+—-+——+———+
16 rows in set (0.00 sec)
*insert into emp values (null,’张飞’,1),(null,’关羽’,2),(null,’赵云’,3),(null,’刘备’,4);
*insert into dept values (null,’企划部’);(加入一个’企划部’)
*insert into emp values (null,’赵薇’,null);(加入一个’赵薇’,但是id设置为null)

2.内连接查询：将两边表能找到对应记录的记录显示出来
select * from dept,emp where dept.id = emp.dept_id;
select * from dept inner join emp on dept.id=emp.dept_id;

3.左[外]连接查询：在内连接的基础上增加左表表有而右边表没有的记录
select * from dept left join emp on dept.id=emp.dept_id;

4.右[外]连接查询：在内连接的基础上增加右边表有而左边表没有的记录
select * from dept right join emp on dept.id=emp.dept_id;

5.全[外]连接查询：在内连接的基础上增加左边表有而右边表没有的记录和右边表有而左边表没有的记录.
select * from dept full join emp on dept.id=emp.dept_id; –mysql默认不支持全连接

select * from dept left join emp on dept.id=emp.dept_id
union
select * from dept right join emp on dept.id=emp.dept_id;