怎么做qq刷会员的网站,网站建设及维护专业,新北网站建设,镇江网站定制1.创建数据库#xff1a; 使用CREATE DATABASE语句
CREATE DATABASE school;show databases; 列出MySQL数据库管理系统的数据库列表
2.切换数据库#xff1a; 使用USE语句选择要操作的数据库
USE school#xff1b;select database (); 当前所在库mysql select…
1.创建数据库 使用CREATE DATABASE语句
CREATE DATABASE school;show databases; 列出MySQL数据库管理系统的数据库列表
2.切换数据库 使用USE语句选择要操作的数据库
USE schoolselect database (); 当前所在库mysql select version (); 查看版本号
------------
| version () |
------------
| 8.0.20 |
------------
1 row in set (0.00 sec)show variables like datadir; 在数据库中查看数据库目录存储位置mysql show variables like datadir;
-----------------------------
| Variable_name | Value |
-----------------------------
| datadir | /data/mysql/ |
-----------------------------
1 row in set (0.01 sec)如果使用的是InnoDB存储引擎.frm表结构文件.ibd表内数据表数据和索引文件一起存放在data目录下。
MySQL 8.0开始.frm文件不再单独存在而是被整合到了.ibd文件中。
3.创建表 使用CREATE TABLE语句创建表
1-create table 表名 (id int, name char(10));2-CREATE TABLE student (id INT PRIMARY KEY,name VARCHAR(10),gender VARCHAR(10),age INT
);3-create table student2 (id int primary KEY AUTO_INCREMENT,name varchar(50) not null,gender varchar(5) not null,age int not null
);drop table 表名 删除所在库中的数据表delete from 表名 清除表内容不删除结构show tables; 查看所在库的表有哪些select * from student 查看指定表 4.查看当前表结构
DESCRIBE 表名; 查看当前表结构
desc student2; 显示数据表的结构mysql DESCRIBE student2;
---------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
---------------------------------------------------------
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| gender | varchar(10) | NO | | NULL | |
| age | int | NO | | NULL | |
| phone | varchar(20) | YES | | 未知 | |
---------------------------------------------------------
5 rows in set (0.00 sec)
5.表结构 增加删除指定列
在表中增加电话列及数据类型 默认为null
alter table student2 add column phone varchar(20); mysql select * from student;
------------------------------
| id | name | gender | age | phone |
------------------------------
| 1 | jack | male | 22 | NULL |
| 2 | mak | male | 17 | NULL |
| 4 | lily | male | 18 | NULL |
------------------------------
3 rows in set (0.00 sec)在表中增加电话列及数据类型 列的默认值为‘未知’
ALTER TABLE student ADD COLUMN phone VARCHAR(20) DEFAULT 未知;mysql select * from student2;
-------------------------------
| id | name | gender | age | phone |
-------------------------------
| 1 | tom | male | 21 | 未知 |
| 2 | lily | Female | 21 | 未知 |
| 3 | lucy | Female | 17 | 未知 |
| 4 | bibo | male | 17 | 未知 |
-------------------------------
4 rows in set (0.00 sec)alter table student2 add column address varchar(255); 增加student2表地址列列 地址
alter table student2 drop column address; 删除student2表地址列
6.表结构 修改
mysql select * from student;
------------------------------
| id | name | gender | age | phone |
------------------------------ 电话列修改为地址
| 1 | jack | male | 22 | NULL |
| 4 | lily | male | 18 | NULL |
------------------------------
2 rows in set (0.01 sec)mysql alter table student change phone address varchar(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql select * from student;
--------------------------------
| id | name | gender | age | address |
--------------------------------
| 1 | jack | male | 22 | NULL |
| 4 | lily | male | 18 | NULL |
--------------------------------
2 rows in set (0.00 sec)mysql alter table student modify address char(50); 修改字段数据类型
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql desc student;
----------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------------------
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| gender | varchar(5) | NO | | NULL | |
| age | int | NO | | NULL | |
| address | char(50) | YES | | NULL | |
----------------------------------------------------------
5 rows in set (0.00 sec)https://www.runoob.com/mysql/mysql-data-types.html 菜鸟教程7.修改数据表名
rename table 表名 to 新表名show tables; 查看修改结果
8.插入数据 使用INSERT INTO语句向表中插入数据
insert into 表名 set id1, namelee, age21;INSERT INTO student (id, name, gender, age) VALUES (1, John, male, 30);INSERT INTO student (id, name, gender, age) VALUES (1, lily, Female, 31);update 表名 set id8 where namelee; 修改lee的id编号为8
9.查询数据表 使用SELECT语句从表中检索数据
SELECT * FROM student;select id, name from student where gender Female; 查询所有女生的学号和姓名select id,name from student2 where age 20; 查询年龄大于20的id和姓名
10.更改数据 使用UPDATE语句更新表中的数据
UPDATE student SET age 35 WHERE id 1; 修改id为1的年龄为35
11.删除数据 使用DELETE FROM语句从表中删除数据
DELETE FROM student WHERE id 1;
delete from 表名 where id1; 删除id1行的数据delete from student where age 18; 删除年龄小于 18 岁的学生
12. 删除表
drop table 表名 删除所在库中的数据表delete from 表名 清除表内容不删除结构
13.查询过滤条件 使用WHERE子句指定查询的过滤条件
SELECT * FROM student WHERE age 25; 查询年龄大于25的
14.排序 使用ORDER BY子句对查询结果进行排序
SELECT * FROM student ORDER BY age DESC; 按年龄从大到小
15.聚合函数 使用聚合函数如SUM、AVG、COUNT等对数据进行统计
SELECT COUNT(*) FROM student; 统计student表人数SUM 求和
AVG 平均值
COUNT 统计
16.分组 使用GROUP BY子句对数据进行分组
SELECT department, AVG(salary) FROM employees GROUP BY department;employees 员工 表
department 部门 列
AVG() 函数计算每个部门的平均薪资
salary 工资
GROUP BY 按部门分组数据从employees表中选择department列。
对salary列使用AVG()聚合函数计算每个部门的平均薪资。
使用GROUP BY department对结果进行分组确保每个部门只计算一次平均薪资。
执行这个查询后您将得到一个结果集其中包含每个部门的名称和对应的平均薪资。
17.查看表结构SQL语句
mysql show create table student2;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| student2 | CREATE TABLE student2 (id int NOT NULL AUTO_INCREMENT,name varchar(10) NOT NULL,gender varchar(10) NOT NULL,age int NOT NULL,phone varchar(20) DEFAULT 未知,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)练习用表
CREATE database companymysql CREATE TABLE company.employee(id int primary key AUTO_INCREMENT not null,name varchar(30) not null,age intsex enum(male,female) default male not null,hire_date date not null,post varchar(50) not null,job_description varchar(100),salary double(15,2) not null,office int,dep_id int);INSERT INTO company.employee(name, age, sex, hire_date, post, job_description, salary, office, dep_id) VALUES
(jack, 31, male, 20180202, instructor, teach, 5000, 501, 100),
(tom, 32, male, 20180203, instructor, teach, 5500, 501, 100),
(robin, 33, male, 20180202, instructor, teach, 8000, 501, 100),
(alice, 34, female, 20180202, instructor, teach, 7200, 501, 100),
(wing, 35, male, 20180202, hr, hrcc, 600, 502, 101),
(harry, 36, male, 20180202, hr, NULL, 6000, 502, 101),
(emma, 37, female, 20180206, sale, salecc, 20000, 503, 102),
(christine, 38, female, 20180205, sale, salecc, 2200, 503, 102),
(zhuzhu, 39, male, 20180205, sale, NULL, 2200, 503, 102),
(gougou, 40, male, 20180205, sale, , 2200, 503, 102);
查看表结构
mysql desc employee;
----------------------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
----------------------------------------------------------------------------
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(30) | NO | | NULL | |
| age | int | YES | | NULL | |
| sex | enum(male,female) | NO | | male | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | NO | | NULL | |
| job_description | varchar(100) | YES | | NULL | |
| salary | double(15,2) | NO | | NULL | |
| office | int | YES | | NULL | |
| dep_id | int | YES | | NULL | |
----------------------------------------------------------------------------
10 rows in set (0.00 sec)查看表
mysql mysql select * from employee;
------------------------------------------------------------------------------------------------
| id | name | age | sex | hire_date | post | job_description | salary | office | dep_id |
------------------------------------------------------------------------------------------------
| 1 | jack | 31 | male | 2018-02-02 | instructor | teach | 5000.00 | 501 | 100 |
| 2 | tom | 32 | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 |
| 3 | robin | 33 | male | 2018-02-02 | instructor | teach | 8000.00 | 501 | 100 |
| 4 | alice | 34 | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
| 5 | wing | 35 | male | 2018-02-02 | hr | hrcc | 600.00 | 502 | 101 |
| 6 | harry | 36 | male | 2018-02-02 | hr | NULL | 6000.00 | 502 | 101 |
| 7 | emma | 37 | female | 2018-02-06 | sale | salecc | 20000.00 | 503 | 102 |
| 8 | christine | 38 | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 102 |
| 9 | zhuzhu | 39 | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 |
| 10 | gougou | 40 | male | 2018-02-05 | sale | | 2200.00 | 503 | 102 |
------------------------------------------------------------------------------------------------
10 rows in set (0.00 sec)mysql select job_description, avg(salary) from employee group by job_description;
-------------------------------
| job_description | avg(salary) |
-------------------------------
| teach | 6425.000000 |
| hrcc | 600.000000 | 不同职位的平均薪资
| NULL | 4100.000000 |
| salecc | 11100.000000 |
| | 2200.000000 |
-------------------------------
5 rows in set (0.01 sec)mysql select job_description, sum(age) from employee8 group by job_description;
---------------------------
| job_description | sum(age) |
---------------------------
| teach | 130 |
| hrcc | 35 | 不同部门人员年龄的和
| NULL | 75 |
| salecc | 75 |
| | 40 |
---------------------------
5 rows in set (0.01 sec)mysql select post, count(*) from employee group by post;
----------------------
| post | count(*) |
----------------------
| instructor | 4 |
| hr | 2 | 统计员工表不同职位的人数
| sale | 4 |
----------------------
3 rows in set (0.00 sec)
