MySQL语句

- 日理万妓 2022-09-28 01:37 21阅读 0赞

## SQL语句 ##

MYSQL查询语句大全集锦

1:使用SHOW语句找出在服务器上当前存在什么数据库：  
mysql> SHOW DATABASES;

2:创建一个数据库MYSQLDATA  
mysql> CREATE DATABASE MYSQLDATA;

3:选择你所创建的数据库  
mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功！)

4:查看现在的数据库中存在什么表  
mysql> SHOW TABLES;

5:创建一个数据库表  
mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

6:显示表的结构：  
mysql> DESCRIBE MYTABLE;

7:往表中加入记录  
mysql> insert into MYTABLE values (”hyq”,”M”);

8:用文本方式将数据装入数据库表中（例如D:/mysql.txt）  
mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;

9:导入.sql文件命令（例如D:/mysql.sql）  
mysql>use database;  
mysql>source d:/mysql.sql;

10:删除表  
mysql>drop TABLE MYTABLE;

11:清空表  
mysql>delete from MYTABLE;  
12:更新表中数据  
mysql>update MYTABLE set sex=”f” where name=’hyq’;  
以下是无意中在网络看到的使用MySql的管理心得,  
在windows中MySql以服务形式存在，在使用前应确保此服务已经启动，未启动可用net start mysql命令启动。而Linux中启动时可用“/etc/rc.d/init.d/mysqld start”命令，注意启动者应具有管理员权限。  
刚安装好的MySql包含一个含空密码的root帐户和一个匿名帐户，这是很大的安全隐患，对于一些重要的应用我们应将安全性尽可能提高，在这里应把匿名帐户删除、 root帐户设置密码，可用如下命令进行：  
use mysql;  
delete from User where User=””;  
update User set Password=PASSWORD(’newpassword’) where User=’root’;  
如果要对用户所用的登录终端进行限制，可以更新User表中相应用户的Host字段，在进行了以上更改后应重新启动数据库服务，此时登录时可用如下类似命令：  
mysql -uroot -p;  
mysql -uroot -pnewpassword;  
mysql mydb -uroot -p;  
mysql mydb -uroot -pnewpassword;  
上面命令参数是常用参数的一部分，详细情况可参考文档。此处的mydb是要登录的数据库的名称。  
在 进行开发和实际应用中，用户不应该只用root用户进行连接数据库，虽然使用root用户进行测试时很方便，但会给系统带来重大安全隐患，也不利于管理技 术的提高。我们给一个应用中使用的用户赋予最恰当的数据库权限。如一个只进行数据插入的用户不应赋予其删除数据的权限。MySql的用户管理是通过 User表来实现的，添加新用户常用的方法有两个，一是在User表插入相应的数据行，同时设置相应的权限；二是通过GRANT命令创建具有某种权限的用 户。其中GRANT的常用用法如下：  
grant all on mydb.\* to NewUserName@HostName identified by “password” ;  
grant usage on *.* to NewUserName@HostName identified by “password”;  
grant select,insert,update on mydb.\* to NewUserName@HostName identified by “password”;  
grant update,delete on mydb.TestTable to NewUserName@HostName identified by “password”;  
若 要给此用户赋予他在相应对象上的权限的管理能力，可在GRANT后面添加WITH GRANT OPTION选项。而对于用插入User表添加的用户，Password字段应用PASSWORD 函数进行更新加密，以防不轨之人窃看密码。对于那些已经不用的用户应给予清除，权限过界的用户应及时回收权限，回收权限可以通过更新User表相应字段， 也可以使用REVOKE操作。  
下面给出本人从其它资料(www.cn-java.com)获得的对常用权限的解释：  
全局管理权限：  
FILE: 在MySQL服务器上读写文件。  
PROCESS: 显示或杀死属于其它用户的服务线程。  
RELOAD: 重载访问控制表，刷新日志等。  
SHUTDOWN: 关闭MySQL服务。  
数据库/数据表/数据列权限：  
ALTER: 修改已存在的数据表(例如增加/删除列)和索引。  
CREATE: 建立新的数据库或数据表。  
DELETE: 删除表的记录。  
DROP: 删除数据表或数据库。  
INDEX: 建立或删除索引。  
INSERT: 增加表的记录。  
SELECT: 显示/搜索表的记录。  
UPDATE: 修改表中已存在的记录。  
特别的权限：  
ALL: 允许做任何事(和root一样)。  
USAGE: 只允许登录–其它什么也不允许做。

--------------------

mysqld -install 安装mysql到计算机服务在中  
mysqld -remove 卸载计算机服务中的mysql  
net start mysql 开启mysql服务  
net stop mysql 停止mysql服务  
mysql -h localhost -u root -p  
mysql -u 用户名 -p 密码

> 要求你输入自己连接数据库的用户名和密码。  
> 考虑密码如果直接明文写在这条命令行上，有些不方便（怕被别人看到），可以像你写的那样，只输入：mysql -u 用户名 -p 然后回车，此时提示你输入密码，这时候输入的密码就不再是明文的了。

show databases;  
show create database mydb2;  
show tables;

use 数据库名;

--------------------

create database 数据库名;
    create database 数据库名 character set UTF8;
    create database if not exists 数据库名 character set UTF8;
    
    create table if not exists 表名 (
        varchar(20) 字段名 not null,
        varchar(20) 字段名 not null
    );
    create table if not exists employer(
        id int,
        name varchar(20) primary key auto_increment,//mysql特有自动增长
        gender varchar(6) unique,//唯一约束
        brithday date,
        entry_date date,
        jop varchar(30),
        salary float(5,1),
        resume text
    )；

//外键

create table if not exists person(
        id int primary key auto_increment,wa
        name carchar(20) not null
    );
    create table if not exists card(
        id int primary key auto_increment,
        location varchar（20） not null,
        pid int,
        constraint pid_FK for foreign key(pid) references person(id)
    )

--------------------

//修改表数据(表内的数据修改)

update user set salary='12133';
    update user set salary='12133',sex='男';
    update user set salary='8000' where username='玛丽';
    update user set salary=salary+1000 where username='jack';

//插入数据

insert into user(id,name,password,birthday) values(1,'jack','111111','2015-4-3');

//删除

drop database if exists mydb1;
    drop table if exists mydb1;

//删除表中数据

delete from user where name='jack';
    delete from user;

//truncate删除表中记录

truncate table user;

--------------------

//查询表中数据

select * from 表名;
    select id,name,math,chinese from student;
    select distinct english,name from student;//去除重复的数据
    select name,math+10 from student;//在所有学生数学成绩加10分
    select name as 姓名,math+10 as 数学 from student;//使用别名
    select name,math+chinese+english as 总分 from student;
    select * from student where name='晓明';
    select * from student where english>90;
    select name chinese,english,math as 总分 from student where chinese+math+englis>120；

//分页查询

select * from user limit 0,10;//前一个参数代表第几条记录的索引号，后一个是每页显示的记录数
    
    select * from student where english<>80;
    select * from student where english>=80 and english<=90;
    select * from student where english between 80 and 90;
    select * from student where math=89 or math=90 or math=91;
    select * from student where math in(89,90,91);
    select * from student where math not in(89,80,91);
    select * from student where name like '张%';//(%代表零个或多个任意字符，_代表一个字符)
    
    select * from student where name like '%%';//在网站中，多条件查询中使用
    
    select * from student where math is null;
    select * from student where math is not null;
    select * from student where math>80 and chinese>80;
    select * from student where math>80 && chinese>80;
    select * from student order by math;//查询后 升序排序
    select * from student order by math  msc;//查询后 升序排序
    select * from student order by math desc;//查询后 降序排序
    select * from student where name like '李%’ order by math+chinese+english as desc;
    
    select count(*)  as 总人数 from student;//求出行数即记录数
    select count(*) from student where math>80;
    select count(*) from student where (chinese+english+math)>100;
    
    select sum(math) from student;//求和
    select sum(math) as 数学总分,sum(chinese) as 语文总分,sum(english) as 英语总分 from student;//求和
    select sum(math)+sum(chinese)+sum(english) as 总分 from student;//求和
    
    select sum(chinese)/count(*) from student;//求平均分
    select avg(math) as 数学平均分 from student;
    select sum(chinese)+sum(math)+sum(english)/count(*) from stident;
    select avg(chinese+math+english)  from student;
    select max(math) from student;
    select min(english),max(chinese) from student;
    
    select sum(price) as from user group by product;//分组查询
    select product ,sum(price) from orders group by product having sum(price)>100;

//多表联合查询

select d.name as 部门名,e.name as 员工名  from department as d,employer as e where d.name='软件部';

//嵌入查询

select name as 员工名 
        from employer
        where did=(
            select id
            from department
            where name='软件部'

//一些函数

select datediff('2015-12-31',now());//距离年底多少天
    select substring('mysql',1,2);//截取字符串，从1开始到2
    select format(3.141592653,3);//保留小数点后2位（四舍五入）
    select floor(3.14);//向下取整
    select rand();//取随机数select format(rand(),2);

//随机产生’a’-‘z’之间的随机整数

(1)查询'a'-'z'的对应的Unicode值
        select ascii('a');//97
        select ascii('z');//122
    (2)产生97-122之间的随机整数
        select floor(rand()*26)+97;
    (3)产生97-122之间的随机整数
        select char(floor(rand()*26+97);

//加密  
select md5(‘123456’);

//流程控制函数

1）select if(salary>=5000,'高薪','起薪') from user;
    2) select naem,ifnull(salary,'无薪') from user;
    3) select 
        case when salary>=5000 then '高薪'
        else '起薪' end
       from user;
    4) select
        case salary when 7000 then '高薪'
                when 6000 then '中薪'
       from user;

--------------------

//查看数据库的编码方式  
status  
//查看表的结构  
desc 表名;

//删除主键  
alter table user drop primary kry;  
//修改数据库的编码方式  
alter database if exists mydb character set gbk;  
//修改表结构

alter table employer add image blob;//增加列
    alter table employer modify jop varchar(60) default 'techer';//修改列属性
    alter table employer drop gender;//删除列
    alter tale user change column name username varchar(20);//修改列名

//改表名  
rename table employer to user;  
//修改表的字符集

alter table user character set UTF8;
    alter employer

//备份(不要在mysql中使用)  
mysqldump -u root -p mydb1 > d:\\mydb1.sql  
//还原（在mysql环境中使用)  
source d:\\mydb1.sql;

--------------------

**一些数据类型**  
（1）Date/Datetime/TimeStamp  
（2）varchar(变长)/char(定长)  
（3）  
tinyblob/blob/mediumblob/longblob–>255B/64K/16M/4G大小的图片、音乐数据等二进制数据  
tinytext/text/mediumtext/longtext–>255B/64K/16M/4G大小的文本数据  
（4）bit(M)  
int  
float(M,D)M位数，D小数位

--------------------

**jdbc中的存储过程**  
drop procedure if exists add\_pro;//删除存储过程,如果存在

delimiter // //把mysql默认的结束符;改成//  
//创建存储过程，前两个为参数，后一个sum为返回值  
create procedure add\_pro(a int ,b int ,out sum int)  
begin  
set sum=a+b;  
end;  
//

delimiter ; //结束符修改回来

**事务的概念**  
启动事物  
start transaction;

……一连串的sql语句

提交事物(以上sql要么一起成功，要么一起失败)  
commit;

回滚事物  
rollback;

设置事物的隔离级别  
set transaction isolation level ***\****;  
隔离级别别 read uncommitted /read committed/ repeatable read/ serializable  
脏读 解决 解决 解决  
不可重复读 解决 解决  
虚读 解决  
select @@tx\_isolation;//查看当前事物的隔离级别