MySQL基本语句

发表于 | 分类于 |

MySQL基本语句

一、用户创建、权限、删除

连接数据库

1
mysql -u username -p password

1.创建用户

1
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

2.授权

1
GRAET privileges ON databasename.tanlename TO 'username@localhost';

例：

1
GRANT ALL ON school.* TO 'knoix'@'localhost';

3.删除用户

1
DROP USER 'username'@'host';

二、数据库与表显示、创建、删除

  • 创建数据库 CREATE DATABASE 数据库名

  • 显示所有数据库 SHOW DATABASES;

  • 使用库(选中库) USE 库名;

  • 删除库 DROP DATABASE 库名;

  • 创建数据表 CREATE 表明

  • 显示数据库中的表 SHOW TABLES

  • 显示表结构：describe 表名;或者desc 表名 describe teacher;或desc teacher;

  • 创建表：create table 表名 (字段设定列表);

1
2
3
4
5
6
CREATE TABLE teacher
(id TINYINT UNSIGNED NOT NULL,
 name CHAR(16) NOT NULL,
 tele NUMERIC(8),
 sex ENUM("F","M") DEFAULT "M"
)
  • 删除表：DROP table 表名；
    句法：DROP DATABASE [IF EXISTS] db_name
    功能：DROP DATABASE删除数据库中的所有表和数据库。要小心地使用这个命令！

DROP DATABASE返回从数据库目录被删除的文件的数目。通常，这3倍于表的数量，因为每张表对应于一个“.MYD”文件、一个“.MYI”文件和一个“.frm”文件。

三、像表中插入数据

1
INSERT INTO `teacher` (`id`, `name`, `tele`, `sex`) VALUES ('5', '45', '5', 'M');

四、表复,制备份,还原

利用 teacher表实验

1.复制表

1.1 含有主键等信息的完整结构

1
CREATE table teacher2 LIKE teacher;

1.2 只有表结构，没有主键等信息

1
2
3
4
5
CREATE table 新表名　ｓelect * from books;
或
create table 新表名 as(select * from book);
或
create table 新表名 select * from books where1=2;

1.3 将旧表中的数据灌入新表

1
INSERT INTO 新表　SELET * FROM 旧表；

注：新表必须已经存在

1.4 输入创建表的DDL 语句

1
show create table 表名;

1.5 清空表数据

1
truncate table 表名;

五、数据库表中数据操作

1. 清除MySQL表中数据

1
2
delete from 表名
truncate table 表名

不带where参数的delete语句可以删除mysql表中所有内容，使用truncate table也可以清空mysql表中所有内容。

效率上truncate比delete快，但truncate删除后不记录mysql日志，不可以恢复数据。

delete的效果有点像将mysql表中所有记录一条一条删除到删完，而truncate相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。

2.删除表中的某些数据

delete from 命令格式：delete from 表名 where 表达式
例如，删除表teacher 中编号为1的记录：

1
delete from teacher where id=1;

六、修改表的列名与表名

1. 给列更名

1
alter table 表名称 change 字段名称 字段名称

例：

1
alter table pet change weight wei;

2. 给表更名

1
alter table 表名 rename 表名

例：

1
alter table tbl_name rename nwe_tbl

七、修改表中的数据

1. 增加一个字段（一列）

1
alter table table_name add column column_name type default value;

type指该字段的类型,value指该字段的默认值
例：

1
alter table mybook add column publish_house varchar(10) default;

2. 更改一个字段名字(也可以改变类型和默认值)

1
alter 谈了table_name change sorce_col_name dest_col_name type default value;

source_col_name 指原来的字段名称,
dest_col_name 指改后的字段名称

例：

1
alter table Board_Info change IsMobile IsTelphone int(3) unsigned default 1;

3. 改变一个字段的默认值

1
alter tabletable_name alter column_name set default value;

例：

1
alter table book alter flag set default 0;

4. 改变一个字段的数据类型

1
alter table table_name change column column_name column_name type;

例：

1
alter table userinfo change column username username varchar(20) not null;

5. 向表中增加一个列作为主键

1
alter table table_name add column colume_name type auto_increment PRIMARY KEY;

例：

1
alter table book add column id int(10) auto_increment PRIMARY KEY;

6. 数据库某表的备份,在命令行中输入:

1
mysqldump -u root -p database_name table_name > bak_file_name;

例：

1
mysqldump -u root -p f_info user_info > user_info.dat;

7. 到处数据

1
select_statment into outfile”dest_file”;

例：

1
select cooperatecode,createtime from publish limit 10 intooutfile”/home/mzc/temp/tempbad.txt”;

8. 导入数据

1
load data infile”file_name” into table table_name;

例：

1
load data infile”/home/mzc/temp/tempbad.txt” into table pad;

9. 将两个表里的数据拼接后插入到另一个表里。下面的例子说明将t1表中的com2和t2表中的com1字段的值拼接后插入到tx表对应的字段里。

例:

1
insert into tx select t1.com1,concat(t1.com2,t2.com1) from t1,t2;

10. 删除字段

1
alter table 表名 drop column 列名;

八、查询表

MySQL查询的五种子句

where(条件查询)，having（筛选语句），group by（分组），order by（排序），limit（限制结果数）

查询谓词：>,<,=,<>,!=,=>,=<

1. 查询数值型数据：

1
SELECT * FROM 表名 WHERE sum>100;

2. 查询字符串

1
2
3
4
SELECT * FROM tb_stu WHERE sname = '小刘'
SELECT * FROM tb_stu WHERE sname like '刘%'
SELECT * FROM tb_stu WHERE sname like '%程序员'
SELECT * FROM tb_stu WHERE sname like '%PHP%'

3. 查询日期型数据

1
SELECT * FROM tb_name WHERE date = '2011-04-18'

注:不同数据库对日期型数据存在差异: ：

(1)MySQL:SELECT from tb_name WHERE birthday = ‘2011-04-08’
(2)SQL Server:SELECT from tb_name WHERE birthday = ‘2011-04-08’
(3)Access:SELECT * from tb_name WHERE birthday = #2011-04-08#

4. 查询逻辑型数据

1
2
SELECT * FROM tb_name WHERE type = 'T'
SELECT * FROM tb_name WHERE type = 'F'

逻辑运算符：nd or not

5. 查询非空数据

1
SELECT *FROM tb_name WHERE address <>" order by addtime desc;

注：<>相当于PHP中的!=

6. 利用变量查询字符串数据

1
SELECT * FROM tb_name WHERE id='$_POST[text]'

注:利用变量查询数据时，传入SQL的变量不必用引号括起来，因为PHP中的字符串与数值型数据进行连接时，程序会自动将数值型数据转变成字符串，然后与要连接的字符串进行连接