数据库mysql入门

随便记录一下

一、Ubuntu下载安装MySQL

在虚拟机Ubuntu终端执行命令：sudo apt-get install mysql-server         #下载安装MySQL

如果出现 lock 的问题，终端执行下面这条命令。

sudo rm /var/lib/dpkg/lock-frontend

二、MySQL常用命令

进入 mysql 界面

sudo mysql -u root -p     #进入数据库

mysql> show databases; 查看数据库

mysql> use mysql； 使用 mysql 这个数据库

mysql> create database mytest; 自己创建一个 数据库 （mytest）

mysql> show tables; 显示当前数据库 有哪些表

mysql> help

List of all MySQL commands:

Note that all text commands must be first on line and end with ';'

? (\?) Synonym for `help'.

clear (\c) Clear the current input statement.

connect (\r) Reconnect to the server. Optional arguments are db and host.

delimiter (\d) Set statement delimiter.

edit (\e) Edit command with $EDITOR.

ego (\G) Send command to mysql server, display result vertically.

exit (\q) Exit mysql. Same as quit.

go (\g) Send command to mysql server.

help (\h) Display this help.

nopager (\n) Disable pager, print to stdout.

notee (\t) Don't write into outfile.

pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.

print (\p) Print current command.

prompt (\R) Change your mysql prompt.

quit (\q) Quit mysql. rehash (\#) Rebuild completion hash.

source (\.) Execute an SQL script file. Takes a file name as an argument.

status (\s) Get status information from the server.

system (\!) Execute a system shell command.

tee (\T) Set outfile [to_outfile]. Append everything into given outfile.

use (\u) Use another database. Takes database name as argument.

charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.

warnings (\W) Show warnings after every statement.

nowarning (\w) Don't show warnings after every statement.

resetconnection(\x) Clean session context. For server side help, type 'help contents'

=========================================================================

•show databases   #显示所有的数据库

•show tables   #显示指定数据库下所有的表

•desc tablename   #显示表的结构

•show table status 显示表的状态信息

数据表三种类型：

1. MyISAM 特点是成熟，稳定和易于管理,只要没有特殊要求

2. InnoDB 增加事物，外键约束，崩溃恢复，锁定机制

3. HEAP 只存在内存中。临时表

create table tbl_test(id int) engine=InnoDB;

MYSQL数据类型

数值类型

•整数

–tinyint,1个字节

–smallint, 2个字节

–Mediumint, 3个字节

–int,integer,4个字节

–bigint, 8个字节

–serial 是bigint auto_increment not null primary key 的缩写

•浮点数

–float(m,d), 4个字节

–double(m,d), 8个字节

–real(m,d)是 double(m,d)的同义词

•定点数

–decimal(p,s),数字个数不限(p<=65,s<=30)

–numeric,dec

日期与时间类型：

date //2010-12-12

time //21:32:32

datetime//sysdate()

字符串类型

•char(n) //固定长度的字符串 n<=255

•varchar(n) //可变长度的字符串 n<=6535

•text //可变长度的字符串 ,2^16 -1

•longtext//可变长度的字符串 ,2^32 -1 二进制数据类型

•bit(n) //二进制数据，n是2进制位的个数,n<=64

•blob //可变长度的二进制数据 ,2^16 -1

•longblob//可变长度的二进制数据,2^32 -1 其他类型

•enum//枚举数据 create table tbl_emp(id int,sex enum('f','m'));

•set//集合数据

create table tbl_emp(

id int,

favi set('game','reader','basketball')

);

插入数据：

insert into 表名(colName1,colName2) values(值1,值2);

insert into 表名 values(值1,值2);

insert into tbl_test(id,name) values(1,'annie');

insert into tbl_test values(2,'denny');

修改数据 :

update 表名 set colName=值 where ColName=值;

update tbl_test set id=3 where id=2;

删除数据：

delete from 表名 where Colname = 值;

delete from tbl_test where id=1;

查询数据： 所有的列

select * from 表名;

select * from tbl_test;

查询数据： 只显示 name

select name from tbl_test;

•select titleid,title from titles limit 2

•select titleid,title from titles limit 2,2

•2，2 从第二个开始显示2条记录

排序： order by colName

select * from authors order by authname;

筛选：

select * from tbl_test where salary > 5000;

•关系运算符

        =, =, >, , !=

•逻辑运算符

        and ,or,not

•算术运算符

        +,-,*,/,%

•其他运算符

        is ,like,in, between ...and…

统计函数：

        sum,avg,min,max,count

select count(year) from titles;

select min(year) from titles;

select max(year) from titles;

select avg(year) from titles;