Detailed explanation of the basic knowledge of MySQL

This article mainly introduces the basic knowledge of MySQL. Interested friends can refer to it. I hope it will be helpful to everyone.

1. How to start MySql

1. Select "Services" in "Computer Management" and choose to open mysql

2. Use the command line #net start mysql

2. Close MySql method

1. Select "Service" in "Computer Management" and choose to close mysql

2. Use the command line #net stop mysql

3. Log in to the mysql server

1. Log in to MySql, use the command line #mysql -uroot -p123

2. Log out using the command line #exit|quit

4. DDL statement (data definition language)

Data definition language: commonly used to define database objects: libraries, tables, fields. Create, modify, delete libraries and table structures

1.查询数据库
#show databases;
2.切换数据库
#use 数据库名称;
#use test;
3.创建新的数据库
#create database if not exits 数据库名称;
#create database if not exits mydb4;
4.删除数据库
#drop database if exits 数据库名称;
#drop database if exits mydb4;
5,修改数据库编码格式
#alter database 数据库名称 character set 编码格式;
#alter database mydb3 character set utf8;
=======================================
1.创建表
#create table 表名称(列名 列类型,列名 列类型,列名 列类型,列名 列类型);
#create table tb_stu(pid char(20),name varchar(50),age int,gender varchar(1));
2.查看表
#show tables;
3.删除表
#drop table 删除表名称;
#drop table tb_stu1;
4.查看表结构
#desc tb_stu;
=======================================
1.修改之添加列
#alter table 表名称 add(列名称 列类型,列名称 列类型);
#alter table tb_stu add(phone varchar(13),class varchar(5));
2.修改之修改列类型
#alter table 表名称 modify 列名称 新列类型;
#alter table tb_stu modify phone varchar(11)
3.修改之修改列名称
#alter table 表名称 change phone 新列名称 新列类型;
#alter table tb_stu change phone phoneNum varchar(11);
4.修改之删除列
#alter table 表名称 drop 列名称;
#alter table tb_stu drop class;
5.修改之修改表名称
#alter table 表名称 rename to 新表名称;
#alter table tb_stu rename to tb_student;

5.DML statement (data manipulation language)

Data manipulation language: Define database records. Add, delete, and modify table records

1.插入数据
#INSERT INTO tb_student(number,NAME,age,gender,phonenum)VALUES('0001','zhangsan',20,'man','123456789');
2.修改数据
where运算符 = ，!=,>=,<=,BETWEEN...AND,IN(...) OR,AND,IS NULL,NOT#UPDATE tb_student SET number=&#39;0002&#39;,NAME=&#39;lisi&#39; ,
age=age+1 WHERE NAME=&#39;lisi&#39;;#UPDATE tb_student SET age=age+1 WHERE number=&#39;0003&#39; &#39;name&#39;=&#39;wangwu&#39; AND gender is null;
3.删除数据
#DELETE FROM tb_student WHERE number=&#39;0002&#39;;

6.DCL(data controller language)

1.创建新用户
用户只能在指定的IP上登录
#create user 名称@IP identified by '密码';
用户可以在所有的IP上登录
#create user 用户名@'%' identified by'密码';
2.给用户授权
#grant all on 数据库名.* to 用户名@IP地址;
3.撤销权限
#revoke delete on 数据库名.* from 用户名@IP地址;
4.查看权限
#show grants for 用户名@IP;
5.删除授权用户
#drop user 用户名@IP;

6.DQL statement (data query language)

Data query language: used Query table records

1.指定列查询
#select number,name from stu;
2.去重查询（重复的只记录一次）
#select distinct age from stu;(年龄相同的只记录一次)
3.列运算
（1）数量类型的列可以做加减乘除运算  
   #select *,salary*1.5 from stu;
  #select name,salary+comm from stu;
  （2）转换null的值(如果comm为空，按0计算)  
  #select salary+ifnull(comm,0) from stu;
  （3）字符串连接  
  #select number,concat(job,'haha') from stu;
  （4）给列起别名  
  #select number 别名，job 别名 from stu;
4.模糊查询
查询名字为三个字并且是以‘明’结尾#select *from stu where name like '__明';查询名字中带‘明’的数据
#select *from stu where name like '%明%';
5.排序
(1).升序（年龄升序）
#select *from stu order by age asc;
(2).降序 （年龄降序）
#select * from stu order by age desc;
(3).多列排序(年龄升序，分数降序)
#select * from stu order by age asc,score desc;
6.聚合函数
（1）.查询所有列不全为空的个数
#select count(*) from stu;
（2）.查询得分总数
#select sum(score) from stu;
（3）.查询平均分数
#select avg(score) from stu;
（4）.查询最高分数
#select max(score) from stu;
（5）.查询最低分数
#select min(score) from stu;
7.分组查询
按性别分组，查询不同性别的人数
#select gender，count(*) from stu group by gender;
查看不同性别的得分大于60分的人数
#select gender,count(*) from stu where score>60 group by gender;
查看不同性别的得分大于60分并且人数大于30人的分组
#select gender,count(*) from stu where score>60 group by gender having count(*)>30;
8.limit(方言)
#select *from stu limit 4,10;

Related recommendations:

Detailed explanation of steps for PHP MySQL to process high-concurrency locking transactions

Steps for PHP MySQL to implement message queue Detailed explanation

How to check the MySQL version?

The above is the detailed content of Detailed explanation of the basic knowledge of MySQL. For more information, please follow other related articles on the PHP Chinese website!