9 . 查询 : 查询所有数据 : select * from table_name; 查询指定字段的数据 : select 字段1 , 字段2 from table_name; 例 : select id,username from mytable where id=1 order by desc;多表查询语句------------参照第17条实例
10 . 更新指定数据 , 更新某一个字段的数据(注意,不是更新字段的名字) Update table_name set 字段名=’新值’ [, 字段2 =’新值’ , …..][where id=id_num] [order by 字段 顺序] 例 : update mytable set username=’lisi’ where id=1; Order语句是查询的顺序 , 如 : order by id desc(或asc) , 顺序有两种 : desc倒序(100—1,即从最新数据往后查询),asc(从1-100),Where和order语句也可用于查询select 与删除delete
11 . 删除表中的信息 : 删除整个表中的信息 : delete from table_name; 删除表中指定条件的语句 : delete from table_name where 条件语句 ; 条件语句如 : id=3;
12 . 创建数据库用户 一次可以创建多个数据库用户如: CREATE USER username1 identified BY ‘password’ , username2 IDENTIFIED BY ‘password’….
13 . 用户的权限控制:grant 库,表级的权限控制 : 将某个库中的某个表的控制权赋予某个用户 Grant all ON db_name.table_name TO user_name [ indentified by ‘password’ ];
14 . 表结构的修改 (1)增加一个字段格式: alter table table_name add column (字段名 字段类型); ----此方法带括号 (2)指定字段插入的位置: alter table table_name add column 字段名 字段类型 after 某字段; 删除一个字段: alter table table_name drop字段名; (3)修改字段名称/类型 alter table table_name change 旧字段名 新字段名 新字段的类型; (4)改表的名字 alter table table_name rename to new_table_name; (5)一次性清空表中的所有数据 truncate table table_name; 此方法也会使表中的取号器(ID)从1开始
15 . 增加主键,外键,约束,索引。。。。(使用方法见17实例) ① 约束(主键Primary key、唯一性Unique、非空Not Null) ② 自动增张 auto_increment ③外键Foreign key-----与reference table_name(col_name列名)配合使用,建表时单独使用 ④ 删除多个表中有关联的数据----设置foreign key 为set null ---具体设置参考帮助文档
16 . 查看数据库当前引擎 SHOW CREATE TABLE table_name; 修改数据库引擎 ALTER TABLE table_name ENGINE=MyISAM | InnoDB;
17 . SQL语句运用实例: --1 建users表 create table users (id int primary key auto_increment,nikename varchar(20) not null unique,password varchar(100) not null,address varchar(200), reg_date timestamp not null default CURRENT_TIMESTAMP);
--2 建articles表,在建表时设置外键 create table articles (id int primary key auto_increment,content longtext not null,userid int,constraint foreign key (userid) references users(id) on delete set null);
----------------------------------------------------------------------- --2.1 建articles表,建表时不设置外键 create table articles (id int primary key auto_increment,content longtext not null,userid int); --2.2 给articles表设置外键 alter table articles add constraint foreign key (userid) references users(id) on delete set null; ------------------------------------------------------------------------
--3. 向users表中插入数据,同时插入多条 insert into users (id,nikename,password,address) values (1,'lyh1','1234',null),(10,'lyh22','4321','湖北武汉'),(null,'lyh333','5678', '北京海淀');
--4. 向article中插入三条数据 insert into articles (id,content,userid) values (2,'hahahahahaha',11),(null,'xixixixixix',10),(13,'aiaiaiaiaiaiaiaiaiaiaiaia',1),(14,'hohoahaoaoooooooooo',10);
--5. 进行多表查询,选择users表中ID=10的用户发布的所有留言及该用户的所有信息 select articles.id,articles.content,users.* from users,articles where users.id=10 and articles.userid=users.id order by articles.id desc;
--6. 查看数据库引擎类型 show create table users;
--7. 修改数据库引擎类型 alter table users engine=MyISAM; ---因为users表中ID被设置成外键,执行此句会出错
--8. 同表查询,已知一个条件的情况下.查询ID号大于用户lyh1的ID号的所有用户 select a.id,a.nikename,a.address from users a,users b where b.nikename='lyh1' and a.id>b.id; ------也可写成 select id,nikename,address from users where id>(select id from users where nikename='lyh1');
9. 显示年龄比领导还大的员工: select a.name from users a,users b where a.managerid=b.id and a.age>b.age;
查询编号为2的发帖人: 先查articles表,得到发帖人的编号,再根据编号查users得到的用户名。 接着用关联查询. select * from articles,users得到笛卡儿积,再加order by articles.id以便观察
使用select * from articles,users where articles.id=2 筛选出2号帖子与每个用户的组合记录
再使用select * from articles,users where articles.id=2 and articles.userid=users.id选出users.id等于2号帖的发帖人id的记录.
只取用户名:select user where user.id=(select userid from articles where article.id =2)
找出年龄比小王还大的人:假设小王是28岁,先想找出年龄大于28的人 select * from users where age>(select age from users where name='xiaowang'); *****要查询的记录需要参照表里面的其他记录: select a.name from users a,users b where b.name='xiaowang' and a.age>b.age
表里的每个用户都想pk一下.select a.nickname,b.nickname from users a,users b where a.id>b.id ;
更保险的语句:select a.nickname,b.nickname from (select * from users order by id) a,(se lect * from users order by id) b where a.id>b.id ;
再查询某个人发的所有帖子. select b.* from articles a , articles b where a.id=2 and a.userid=b.userid
说明: 表之间存在着关系,ER概念的解释,用access中的示例数据库演示表之间的关系.只有innodb引擎才支持foreign key,mysql的任何引擎目前都不支持check约束。 四、字符集出现错误解决办法 出现的问题: mysql> update users -> set username='关羽' -> where userid=2; ERROR 1366 (HY000): Incorrect string value: '/xB9/xD8/xD3/xF0' for column 'usern ame' at row 1 向表中插入中文字符时,出现错误。
mysql> select * from users; +--------+----------+ | userid | username | +--------+----------+ | 2 | ???? | | 3 | ???? | | 4 | ?í?ù | +--------+----------+ 3 rows in set (0.00 sec) 表中的中文字符位乱码。 解决办法: 使用命令: mysql> status; -------------- mysql Ver 14.12 Distrib 5.0.45, for Win32 (ia32)
Connection id: 8 Current database: test Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.0.45-community-nt MySQL Community Edition (GPL) Protocol version: 10 Connection: localhost via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: gbk Conn. characterset: gbk TCP port: 3306 Uptime: 7 hours 39 min 19 sec Threads: 2 Questions: 174 Slow queries: 0 Opens: 57 Flush tables: 1 Open ta bles: 1 Queries per second avg: 0.006 -------------- 查看mysql发现Server characterset,Db characterset的字符集设成了latin1,所以出现中文乱码。
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | users | +----------------+ 1 row in set (0.00 sec)
更改表的字符集。 mysql> alter table users character set GBK; Query OK, 3 rows affected (0.08 sec) Records: 3 Duplicates: 0 Warnings: 0
查看表的结构: mysql> show create users; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'users ' at line 1 mysql> show create table users; +-------+----------------------------------------------------------------------- ------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------- ------------------------------------------------------------------------------+ | users | CREATE TABLE `users` ( `userid` int(11) default NULL, `username` char(20) character set latin1 default NULL ) ENGINE=InnoDB DEFAULT CHARSET=gbk | +-------+----------------------------------------------------------------------- ------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> desc users; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | userid | int(11) | YES | | NULL | | | username | char(20) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 2 rows in set (0.02 sec)
这时向表中插入中文然后有错误。 mysql> insert into users values(88,'中文'); ERROR 1366 (HY000): Incorrect string value: '/xD6/xD0/xCE/xC4' for column 'usern ame' at row 1 mysql> insert into users values(88,'中文'); ERROR 1366 (HY000): Incorrect string value: '/xD6/xD0/xCE/xC4' for column 'usern ame' at row 1
还要更改users表的username的字符集。 mysql> alter table users modify username char(20) character set gbk; ERROR 1366 (HY000): Incorrect string value: '/xC0/xEE/xCB/xC4' for column 'usern ame' at row 1 mysql> alter table users modify username char(20) character set gbk; ERROR 1366 (HY000): Incorrect string value: '/xC0/xEE/xCB/xC4' for column 'usern ame' at row 1
三、增加新用户。 (注意:和上面不同,下面的因为是MYSQL环境中的命令,所以后面都带一个分号作为命令结束符) 格式:grant select on 数据库.* to 用户名@登录主机 identified by “密码” 1、增加一个用户test1密码为abc,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用root用户连入MYSQL,然后键入以下命令: grant select,insert,update,delete on *.* to [email=test1@”%]test1@”%[/email]” Identified by “abc”; 但增加的用户是十分危险的,你想如某个人知道test1的密码,那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的数据可以为所欲为了,解决办法见2。
2、增加一个用户test2密码为abc,让他只可以在localhost上登录,并可以对数据库mydb进行查询、插入、修改、删除的操作(localhost指本地主机,即MYSQL数据库所在的那台主机), 这样用户即使用知道test2的密码,他也无法从internet上直接访问数据库,只能通过MYSQL主机上的web页来访问了。 grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “abc”; 如果你不想test2有密码,可以再打一个命令将密码消掉。 grant select,insert,update,delete on mydb.* to [email=test2@localhost]test2@localhost[/email] identified by “”; 下篇我是MYSQL中有关数据库方面的操作。注意:你必须首先登录到MYSQL中,以下操作都是在MYSQL的提示符下进行的,而且每个命令以分号结束。
10、往表中插入记录: mysql> INSERT INTO 表名 VALUES (”hyq”,”M”);
11、更新表中数据: mysql-> UPDATE 表名 SET 字段名1=’a',字段名2=’b’ WHERE 字段名3=’c';
12、用文本方式将数据装入数据表中: mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE 表名;
13、导入.sql文件命令: mysql> USE 数据库名; mysql> SOURCE d:/mysql.sql;
14、命令行修改root密码: mysql> UPDATE mysql.user SET password=PASSWORD(’新密码’) WHERE User=’root’; mysql> FLUSH PRIVILEGES;
15、显示use的数据库名: mysql> SELECT DATABASE();
16、显示当前的user: mysql> SELECT USER();
三、一个建库和建表以及插入数据的实例 drop database if exists school; //如果存在SCHOOL则删除 create database school; //建立库SCHOOL use school; //打开库SCHOOL create table teacher //建立表TEACHER ( id int(3) auto_increment not null primary key, name char(10) not null, address varchar(50) default ‘深圳’, year date ); //建表结束
//以下为插入字段 insert into teacher values(”,’allen’,'大连一中’,'1976-10-10′); insert into teacher values(”,’jack’,'大连二中’,'1975-12-23′);
四、将文本数据转到数据库中 1、文本数据应符合的格式:字段数据之间用tab键隔开,null值用[url=file:n]//n[/url]来代替.例: 3 rose 大连二中 1976-10-10 4 mike 大连一中 1975-12-23 假设你把这两组数据存为school.txt文件,放在c盘根目录下。 2、数据传入命令 load data local infile “c://school.txt” into table 表名; 注意:你最好将文件复制到[url=file:mysql//bin]//mysql//bin[/url]目录下,并且要先用use命令打表所在的库。
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;
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”;