MySQL fundamentals(one)

MySQL fundamentals(one)

0.install MySQL

#拉取镜像
docker pull mysql:5.7
#创建容器
docker run -di --name=mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:5.7
3.连接容器
docker exec -it mysql /bin/bash
4.登录mysql
mysql -uroot -p123456 --default-character-set=utf8
5.show databases;
6.通过Navicate访问容器
主机：宿主机地址：192.168.32.102
端口：宿主机端口：3306

1.基础命令

1.Connecting to and Disconnecting from the Server

#host and user represent the host name where your MySQL server is running and the user name of your MySQL account.
mysql -h 192.168.32.150 -u user -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 8.0.27-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
#######################################
#If you are logging in on the same machine that MySQL is running on, you can omit the host, and simply use the following
mysql -u user -p
########################################
#exit
QUIT

2.Entering Queries

#Here is a simple query that asks the server to tell you its version number and the current date
SELECT VERSION(), CURRENT_DATE;

#you can use mysql as a simple calculator
SELECT SIN(PI()/4),(4+1)*5;

#You can even enter multiple statements on a single line. Just end each one with a semicolon
SELECT VERSION();SELECT NOW();

#mysql accepts free-format input: it collects input lines but does not execute them until it sees the semicolon
SELECT
USER()
,
CURRENT_DATE;

The following table shows each of the prompts you may see and summarizes what they mean about the state that mysql is in.

Prompt	Meaning
mysql>	Ready for new query
->	Waiting for next line of multiple-line query
'>	Waiting for next line, waiting for completion of a string that began with a single quote (')
">	Waiting for next line, waiting for completion of a string that began with a double quote (")
``>`	Waiting for next line, waiting for completion of an identifier that began with a backtick (```)
/*>	Waiting for next line, waiting for completion of a comment that began with /*

3.Creating and Using a Database

#Use the SHOW statement to find out what databases currently exist on the server
SHOW DATABASES;

#If the test database exists, try to access it
USE test;

#You can use the test database (if you have access to it) for the examples that follow, but anything you create in that database can be removed by anyone else with access to it. For this reason, you should probably ask your MySQL administrator for permission to use a database of your own. Suppose that you want to call yours menagerie. The administrator needs to execute a statement like this
#授权用户使用属于自己的数据库；其他用户不能修改
GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';
#where your_mysql_name is the MySQL user name assigned to you and your_client_host is the host from which you connect to the server.
#########################################################
#示例
1.新建用户
#登录MySQL  
mysql -u root -p
#创建数据库；创建表
create database peanut character set utf8;
use peanut;
create table student(
id int primary key,
student_name varchar(255),
student_gender varchar(255),
student_college varchar(255),
age int, book varchar(255)
);
#插入内容
insert into student values(1,'peanut','male','information science',100,'information')；
#删除字段
ALTER TABLE student DROP COLUMN book;
#添加字段
ALTER TABLE student ADD book varchar(255) DEFAULT 'LIBRARY' COMMENT '书籍信息';
#修改字段
UPDATE student set book='INFORMATION' WHERE ID=1;
#删除记录
DELETE FROM student where id=1;
#查看内容
select * from student;
#查看用户
use mysql;
select host,user from user；
#创建用户
CREATE USER 'peanut'@'%' IDENTIFIED BY '1234';
#授权
GRANT ALL ON peanut.student TO 'peanut'@'%';
GRANT ALL ON peanut.student_copy TO 'peanut'@'%';
#刷新权限
FLUSH PRIVILEGES;
#查看字符集的设置；
show variables like 'character_set_%';
#修改数据库级别字符集
ALTER database peanut character set utf8;
#修改表字符编码格式
ALTER table student convert to character set utf8;
#创建表设置默认字符集
create table student_copy(
id int primary key,
student_name varchar(255),
student_gender varchar(255),
student_college varchar(255),
age int, book varchar(255)
)DEFAULT CHARSET=utf8;
insert into student_copy values(1,'peanut','male','information science',100,'情报学')；
select * from student_copy;
GRANT ALL ON peanut.student_copy TO 'peanut'@'%';
#刷新权限
FLUSH PRIVILEGES;
ALTER table student_copy convert to character set utf8;

#Once you have created a table, SHOW TABLES should produce some output:
SHOW TABLES;

#To verify that your table was created the way you expected, use a DESCRIBE statement:
DESCRIBE student;

#创建表student2
create table student2(
id int primary key,
name varchar(255),
age int
);
#student.txt文件内容
1	Kin	20
2	Hallon	20
#load data
load data local infile "D:\\Mycodelibrary\\Mycodelibrary\\mylibrary\\Note\\2111\\student.txt" into table student2;

#The simplest form of SELECT retrieves everything from a table:
SELECT * from student2;
#There are at least two ways to fix errors in tables
DELETE FROM student2;
#修改源文件
LOAD DATA LOCAL INFILE 'D:\\Mycodelibrary\\Mycodelibrary\\mylibrary\\Note\\2111\\student.txt' INTO TABLE student;
#Fix only the erroneous record with an UPDATE statement:
UPDATE student2 SET age=21 where id=2;

# Selecting Particular Rows
SELECT * FROM student2 WHERE name = 'Kin';
#You can specify conditions on any column
SELECT * FROM student2 WHERE age >= 20;
#You can combine conditions
insert into student2 values(3,'Kin',23);
SELECT * FROM student2 WHERE name = 'Kin' AND age = '20';
# Selecting Particular Columns
SELECT name, age FROM student2;
# To minimize the output, retrieve each unique output record just once by adding the keyword DISTINCT:
SELECT DISTINCT name FROM student2;

#You can use a WHERE clause to combine row selection with column selection
SELECT name, age FROM student2 WHERE name = 'Kin' AND age >= 22;

#Sorting Rows
SELECT name, age FROM student2 ORDER BY age DESC;

#You can sort on multiple columns, and you can sort different columns in different directions.
insert into student2 values(4,'Ain',20);
SELECT name, age FROM student2 ORDER BY name, age DESC;
The DESC keyword applies only to the column name immediately preceding it

#Pattern Matching
#To find names beginning with K:
SELECT * FROM student2 WHERE name LIKE 'K%';

#To find names ending with on:
SELECT * FROM student2 WHERE name LIKE '%on';

#To find names containing a l:
SELECT * FROM student2 WHERE name LIKE '%l%';

#To find names containing exactly three characters, use three instances of the _ pattern character:
SELECT * FROM student2 WHERE name LIKE '___';

#You could also write the previous query using the {n} (“repeat-n-times”) operator:
SELECT * FROM student2 WHERE name REGEXP '^.{3}$';

#Counting Rows
#counts the number of rows
SELECT COUNT(*) FROM student2;

SELECT name, COUNT(*) FROM student2 GROUP BY name;

参考文献

[1]MySQL :: MySQL 8.0 Reference Manual :: 3.1 Connecting to and Disconnecting from the Server[EB/OL]. [2021-11-02]. https://dev.mysql.com/doc/refman/8.0/en/connecting-disconnecting.html.
2 mysql中grant all privileges on赋给用户远程权限_李在奋斗的博客-CSDN博客[EB/OL]. [2021-11-02]. https://blog.csdn.net/qq_31725371/article/details/83019856.
3 MySQL5.7创建用户并授权_qpzkobe的博客-CSDN博客_mysql5.7创建用户[EB/OL]. [2021-11-02]. https://blog.csdn.net/qpzkobe/article/details/102833458.
4 MySQL-5.7创建及查看数据库_weixin_38166557的博客-CSDN博客[EB/OL]. [2021-11-02]. https://blog.csdn.net/weixin_38166557/article/details/98202591?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163586344816780255273585%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=163586344816780255273585&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allfirst_rank_ecpm_v1~rank_v31_ecpm-1-98202591.pc_search_result_cache&utm_term=mysql5.7%E5%88%9B%E5%BB%BA%E6%95%B0%E6%8D%AE%E5%BA%93.
5 MySQL5.7，创建数据库，创建表并插入内容_ysc111k的博客-CSDN博客_mysql5.7创建数据库[EB/OL]. [2021-11-02]. https://blog.csdn.net/ysc111k/article/details/108831814?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163586344816780255273585%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=163586344816780255273585&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allfirst_rank_ecpm_v1~rank_v31_ecpm-2-108831814.pc_search_result_cache&utm_term=mysql5.7%E5%88%9B%E5%BB%BA%E6%95%B0%E6%8D%AE%E5%BA%93.
6 mysql中添加字段，添加默认值，删除字段，删除默认值_Mr_chen的博客-CSDN博客_删除字段默认值[EB/OL]. [2021-11-02]. https://blog.csdn.net/Mrs_chens/article/details/103307489?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163586421216780274133335%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=163586421216780274133335&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allfirst_rank_ecpm_v1~rank_v31_ecpm-1-103307489.pc_search_result_cache&utm_term=mysql5.7%E5%88%A0%E9%99%A4%E5%AD%97%E6%AE%B5%EF%BC%8C%E6%B7%BB%E5%8A%A0%E6%95%B0%E6%8D%AE.
7 MySQL数据库表怎么进行添加数据、更新数据和删除数据操作——MySQL 5.7数据库_雨云21的博客-CSDN博客_mysql表怎么增加数据[EB/OL]. [2021-11-02]. https://blog.csdn.net/hyh17808770899/article/details/105080474?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163586421216780274133335%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=163586421216780274133335&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allfirst_rank_ecpm_v1~rank_v31_ecpm-2-105080474.pc_search_result_cache&utm_term=mysql5.7%E5%88%A0%E9%99%A4%E5%AD%97%E6%AE%B5%EF%BC%8C%E6%B7%BB%E5%8A%A0%E6%95%B0%E6%8D%AE.
8 MySQL修改字符集编码的几种方式_会当凌绝顶1080的博客-CSDN博客_mysql更改字符集编码[EB/OL]. [2021-11-02]. https://blog.csdn.net/qq_36127031/article/details/79437873?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163586590616780271525880%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=163586590616780271525880&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2_allfirst_rank_ecpm_v1~rank_v31_ecpm-7-79437873.pc_search_result_cache&utm_term=mysql5.7%E4%BF%AE%E6%94%B9%E5%AD%97%E7%AC%A6%E9%9B%86.