MySql命令使用基础篇

MySql默认端口 3306   Mysql中的超级用户叫 root

一、数据库启动操作

1、启动：net start mysql      停止：net stop mysql

2、查看版本：mysql -V（大写）

3、进入数据库：mysql -uroot（用户名） -p（密码） -P3306（端口） -h192.168.60.23（链接域名或地址）

4、退出：exit  quit   \q 

5、修改登录密码：mysqladmin -uroot -p123456（旧密码） password

     出现提示后输入新密码

 

二、数据库常用命令

1、show databases      显示所有数据库

2、use test                   打开test数据库

3、select database()    显示当前连接的数据库

4、select version()       显示当前数据库版本

5、select now()            显示当前日期时间

6、select user()            显示当天登录的用户

7、create database【 if not exists】 guo【character set utf8】  创建数据库

8、show variables like 'character_set_database' ;     show create database guo1 ;  查看数据库编码格式

9、drop database【if exists】 guo1  删除数据库

10、 alter database guo1 character set gbk    修改编码格式

11、show warnings   查看错误信息

 

三、数据库表操作

1、create table【 if not exists】table_name(

       column_name    date_type，

       ......

     )      创建表

2、show tables 【from database_name】    查看数据库下的表

3、show columns from table_name  &   desc table_name     查看表结构

     show create table table_name   查看创建表时的语句结构

4、alter table table_name add  column_name  column_type【first最前面 | after column_name指定在哪个字段后面】 给表添加字段

5、alter table table_name change email1 email_bak varchar(30)      修改表的字段名，也可以同时修改它的类型

6、alter table table_name modify emial_bak varchar(30)      修改列的数据类型

7、alter table table_name modify email_bak varchar(30) first  |  after emial      修改列的顺序，放到表最前面或者那一列的后面

8、alter table table_name drop email_bak   删除表中的列

9、alter table table_name rename to new_table_name  修改表名称

10、drop table【if exists】 table_name,table_name2,......   删除表，可以多个表

 

四、表分区

1、show plugins   查看数据库是否支持表分区       显示partition为ACTIVE则支持

2、create table bookinfo(

     book_id int,

     book_name varchar(20)

    )        --------------------------------------------------------------需要先定义表

    partition by range(book_id)(

    partition p1 values less than(20109999),

    partition p2 values less than(20159999),

    partition p3 values less than MAXVALUE

    );---------------------------------------------------------------------定义分区

partition by + 分区类型 +（需要分区的字段）

range范围分区：

         1、根据指定的某列范围值进行分区

         2、使用values less than 操作符

select * from bookinfo partition(p1)    按照分区去查询

 

五、约束

1、NOT NULL  非空约束  字段值不可为空    创建表时直接在字段类型后加NOT NULL即可

      alter table bookinfo modify book_name varchar(20) NOT NULL      修改表的字段为非空约束

      alter table bookinfo modify book_name varchar(20)；或者加NULL    删除非空约束

 

2、PRIMARY KEY  主键约束要求数据唯一、不允许为空、能够唯一标识一条数据，一张表最多一个主键约束

单字段主键：

    （1）直接在字段类型后加上PRIMARY KEY 

    （2）constraint <约束名>  PRIMARY KEY（列名）

多字段联合主键：

     PRIMARY KEY(book_id,card_id,......)

添加主键

alter table bookinfo add primary key(book_id)     

alter table bookinfo add constraint pk_id primary key(book_id) 

alter table bookinfo modify book_id int primary key

删除主键

alter table bookinfo drop primary key

 

3、UNIQUE 唯一约束 要求该列唯一、允许为空        

    （1）直接在字段类型后加上UNIQUE

    （2）constraint <约束名>  UNIQUE（列名）

添加唯一约束：

alter table bookinfo modify book_name varchar(20) unique

alter table bookinfo add unique(book_name)

alter table bookinfo add constraint pk_name  unique(book_name)

删除唯一约束：约束名称可以通过 show create table bookinfo来查看

alter table bookinfo drop index  约束名称

alter table bookinfo drop key   约束名称

 

4、DEFAULT 默认约束   指定某列默认值       直接在字段类型后加DEFAULT  默认值

添加默认约束

alter table bookinfo modify press varchar(20) default '机械工业出版社'

alter table bookinfo alter column press set default '机械工业出版社'

删除默认约束

alter table bookinfo alter column press drop default;

alter table bookinfo modify press varchar(20);

 

5、FOREIGN KEY 外键约束    constraint  fk_id  foreign key(book_category_id)  references  bookcategory(category_id)     

      用来将两个表的数据建立连接，可以是一列或者多列，一个表可以有多个外键

     一个表的外键可以为空值，若不为空值，则每一个外键必须等于另一个表中主键的某个值

     外键保持数据的一致和完整性

添加外键约束

alter table bookinfo add foreign key (book_category_id) references bookcategory(category_id)

删除外键约束

alter table bookinfo drop foreign key fk_id（约束名称）

级联删除on delete cascade 

constraint  fk_id  foreign key(book_category_id)  references  bookcategory(category_id)   on delete cascade 父级删除，子级也会跟着删除

 

六、数据表操作语句

 

插入语句：

1、insert into table_name (column1,column2,......)values(value1,value2,......)    向表中插入所有列的数据

2、insert into table_name (column1,column3)values(value1,value3)     插入部分数据，没有插入的数据则会显示默认信息或者NULL

3、insert into table_name(columnlist) values(valuelist1),(valuelist2),......  插入多条数据

4、insert into table_name1( columnlist1) select ( columnlist2) from table_name2 where (condition)   插入查询的内容

 

设置属性值自动增加

AUTO_INCREMENT      自增列初始值为1，每增加一条自动增长1

可用AUTO_INCREMENT=n来指定自增初始值

1、CREATE TABLE bookcategory_tmp(
category_id INT PRIMARY KEY AUTO_INCREMENT,
category VARCHAR(20) NOT NULL UNIQUE,
parent_id INT NOT NULL
);

2、CREATE TABLE bookcategory_tmp(
category_id INT PRIMARY KEY AUTO_INCREMENT,
category VARCHAR(20) NOT NULL UNIQUE,
parent_id INT NOT NULL
)AUTO_INCREMENT=5;

3、ALTER TABLE bookcategory_tmp MODIFY category_id INT AUTO_INCREMENT    为已有的表添加自增

4、ALTER TABLE bookcategory_tmp AUTO_INCREMENT=5  添加自增初始值

5、ALTER TABLE bookcategory_tmp MODIFY category_id INT     去掉自增列

6、如果有外键，则需要先删除外键再添加自增。

 

更新&删除数据表

1、update table_name set  column=value  where (condition)    更新表内容

2、delete from table_name (  where  condition)    删除表的内容

3、truncate table table_name 删除表中的所有记录可以用truncate，过程为删除表后再新建一个表

 

查询数据表

1、select * from bookcategory         select category_id,category,parent_id from table_name     查询所有列

2、select category from bookcategory   查询单个列

3、select category, category_id from bookcategory   查询多个列

4、select book_id,book_name,price from bookinfo where press="机械工业出版社"    通过where子句过滤条件

5、select distinct press from bookinfo    distinct消除查询重复的值

6、select * from readerinfo where age is null      查询空记录

7、SELECT COUNT(*) FROM readerinfo WHERE sex="女"    查询女的个数

8、SELECT sex,COUNT(*) FROM readerinfo GROUP BY sex     查询每个性别的个数

9、SELECT sex FROM readerinfo GROUP BY sex HAVING COUNT(sex)<2   查询性别小于2个人的性别

10、SELECT * FROM readerinfo ORDER BY age    默认升序

11、SELECT * FROM readerinfo ORDER BY age,balance    先按照age升序排列，age相同的再按照balance升序排序

12、SELECT * FROM readerinfo ORDER BY age,balance DESC     先按照age升序排列，age相同的再按照balance降序排序

13、SELECT * FROM readerinfo LIMIT 3 显示前三条记录

14、SELECT * FROM readerinfo LIMIT 2,2  等同于 SELECT * FROM readerinfo LIMIT 2 OFFSET 2              从第三条记录开始显示后面的2条记录

SELECT store,COUNT(*) FROM bookinfo GROUP BY store ORDER BY store DESC LIMIT 4     总和语句

 

运算符和函数

 1、算术运算符

select store,store-1,store+1,store/2,store*2,store%2 from bookinfo;    查询库存   库存加1    库存减1   库存除以2    库存乘以2   库存除以2的余数

select card_id,name,tel, balance  from readerinfo where balance-200>=0   查询余额减去押金大于等于0的读者的身份证号，姓名，电话，余额

2、比较运算度

查询余额大于200的读者信息

SELECT * FROM readerinfo WHERE balance>200;

查询余额不等于200的读者信息   

SELECT * FROM readerinfo WHERE balance!=200;
SELECT * FROM readerinfo WHERE balance<>200;

查询年龄不为空的读者信息

SELECT * FROM readerinfo WHERE age IS NOT NULL;

查询余额在350和450之间的读者信息

SELECT * FROM readerinfo WHERE balance BETWEEN 350 AND 450;

查询姓名是王鹏,张飞,李月的读者信息

SELECT * FROM readerinfo WHERE NAME IN("王鹏","张飞","李月");

查询姓张，名字是两个字的读者信息

SELECT * FROM readerinfo WHERE NAME LIKE "张_";

查询手机号以135开头的读者信息

SELECT * FROM readerinfo WHERE tel LIKE "135%";

3、逻辑运算符

查询价格大于50且库存大于5的图书信息
SELECT * FROM bookinfo WHERE price>50 AND store<5

查询价格大于80或出版社为机械工业出版社的图书信息

SELECT * FROM bookinfo WHERE price>80 OR press = "机械工业出版社";

查询图书价格不在50和100之间的图书信息

SELECT * FROM bookinfo WHERE price NOT BETWEEN 50 AND 100;

4、数值函数

向上距离它最近的整数ceil

SELECT CEIL(28.55)   

向下距离它最近的整数floor     
SELECT FLOOR(28.55)   

四舍五入round 

SELECT ROUND(28.55)       直接四舍五入到整数   
SELECT ROUND(28.55,1)     四舍五入小数点1位

SELECT ROUND(28.55,0)      四舍五入到整数
SELECT ROUND(28.55,-1)     四舍五入到小数点左边第一位30

截断truncate

SELECT TRUNCATE(28.55,1)     截断到小数点一位
SELECT TRUNCATE(28.55,0)     截断到整数
SELECT TRUNCATE(28.55,-1)    截断到小数点左边第一位 20

取余数mod

SELECT MOD(11,2)    11除以2的余数1

SELECT book_id,book_name,price,ROUND(price) FROM bookinfo;     查询图书价格四舍五入后的图书id，名称，价格
SELECT * FROM bookinfo WHERE MOD(book_id,2)=0;     查询图书id为偶数的图书信息

5、字符函数

连接字符的函数concat和concat_ws

SELECT CONCAT("hello","Avril"); 
SELECT CONCAT_WS("-","hello","Avril");     带有分割符号

SELECT CONCAT_WS("-",book_id,book_name,store) FROM bookinfo;    查询图书id name store信息且用横杠分割

字符大写和小写函数upper和lower

SELECT LOWER("Hello Avril")     小写
SELECT UPPER("Hello Avril")      大写

SELECT book_id,book_name,UPPER(book_name) FROM bookinfo;    查询图书id name 且name要全部大写

字符长度函数length

SELECT LENGTH(" hello ") 

去掉空格函数rtrim   ltrim   trim

SELECT RTRIM(" hello ")       去掉右侧空格
SELECT LTRIM(" hello ")        去掉左侧空格
SELECT TRIM(" hello ")          去掉两边空格

截取字符串substring   left   right

SELECT SUBSTRING("Hello Avril",1,5)     从第一个字符开始截取到第5个字符Hello 
SELECT SUBSTRING("Hello Avril",-5,2)    从右侧数第5个字符开始截图向右截取两个字符  Av

SELECT LEFT("Hello Avril",5)        从左侧获取5个字符Hello 
SELECT RIGHT("Hello Avril",7)     从右侧获取5个字符Avril

替换字符串

SELECT REPLACE('Hello Avril','Avril','Mysql')     将字符串Hello Avril中的Avril换成Mysql

格式化函数format

SELECT FORMAT(1234.56,2)    保留2位小数

FORMAT(1234.5,2)    保留2位小数

FORMAT(1234.5678,0)      不保留小数
SELECT book_id,book_name,price,FORMAT(price,0) FROM bookinfo;      查询图书id name 和价格格式化整数的信息

6、日期时间函数

当天日期和时间 curdate和curtime

SELECT CURDATE()     2020-07-05         如果加入计算会直接显示成数字20200705
SELECT CURTIME()       21:34:45             213445

当前系统日期和时间

SELECT NOW()
SELECT SYSDATE()       2020-07-05  21:34:45

增加时间date_add

SELECT DATE_ADD("2020-02-02",INTERVAL 5 YEAR)      给这个时间增加5年/month/day

计算时间间隔多少天 datediff

SELECT DATEDIFF('2020-02-05','2020-02-02')        3天

时间格式化

%b    月份缩写   Jan......Dec

%c     月份数字0......12

%m    月份数字00......12

%M   月份的英文全拼January......December

%d    日期数字01......31

%e    日期数字1......31

%Y    4位数字年份

%y     2位数字年份

SELECT DATE_FORMAT('2020-02-05','%Y%M%e')

INSERT INTO borrowinfo VALUES(20190701,'130429199007025628',  【CURDATE(), 借书日期为当前日期】 ，【DATE_ADD(CURDATE(),INTERVAL 1 MONTH)还书日期为1个月后】,    '否')

SELECT book_id,book_category_id,book_name,anthur,price,press,DATE_FORMAT(pubdate,'%Y-%m'),store FROM bookinfo;   出版日期按照2015-05格式来显示

7、聚合函数

SELECT AVG(price) FROM bookinfo;      求价格的平均值
SELECT SUM(price) FROM bookinfo;      求价格的总和
SELECT MAX(store) FROM bookinfo;      求库存的最大值
SELECT MIN(store) FROM bookinfo;       求库存的最小值
SELECT COUNT(*) FROM bookinfo;        求图书的数量

SELECT book_category_id ,COUNT(book_id) ,SUM(store)  FROM bookinfo GROUP BY book_category_id   按照图书种类分组，求每个种类下的图书数量和库存和

8、信息函数和加密函数

查看数据库当前版本

SELECT VERSION()

查看数据库连接次数

SELECT CONNECTION_ID()

查看当前连接的数据库

SELECT DATABASE()
SELECT SCHEMA()

查看当前数据库连接用户

SELECT USER()

加密函数MD5和password()

SELECT MD5('test')       以32位十六进制数字的二进制字符转形式显示

INSERT INTO userinfo VALUES("root",MD5('1234'))     插入MD5加密的密码
SELECT * FROM userinfo WHERE username="root" AND PASSWORD=MD5('1234')     查询md5加密的密码

INSERT INTO userinfo VALUES("root1",PASSWORD("1234pw"))      插入password加密

SELECT * FROM userinfo WHERE PASSWORD=PASSWORD('1234pw')    查询password加密 

搜索结果来创建表

create table table_name1 as select * from table_name2